[Daily] mysql index usage test and mysql index test

[Daily] mysql index usage test and mysql index test

1. index is a data structure that helps MySQL efficiently obtain data. It can be understood as "sorting the data structure in order", outside of the data, the database system also maintains data structures that meet specific search algorithms. These data structures reference (point to) data in a certain way, so that advanced search algorithms can be implemented on these data structures.

 

2. Create an index when creating a table and create a mass mailing table:
Create table mass_mail_send (
Id int auto_increment primary key,
Sender varchar (125) not null default '',
Mass_id int not null default 0,
Subject varchar (255) not null default '',
Location varchar (255) not null default '',
Send_time int not null default 0,
User_email_id int not null default 0,
Index (mass_id ),
Index (user_email_id)
) Engine = innodb charset utf8;

 

3. Create a common index on mass_id and user_email_id

 

4. Use explain to check whether the index is used

 

Select_type: SIMPLE (Common select), PRIMARY (with subqueries), UNION (with UNION queries)
Table: The table used to output rows.
Type: the connection types from the best to the worst are const, eq_reg, ref, range, indexhe, and all.
Possible_keys: displays indexes that may be applied to this table.
Key: actually used Index
Key_len: the length of the index used. The shorter the length, the better.

5. View index usage statistics

1. show status like 'handler _ read % '; View index usage
Handler_read_first full index Scan
The higher the Handler_read_key value, the better. The index is used efficiently.
The smaller the value of Handler_read_next, the better.
Handler_read_rnd does not use indexes or uses too many sorting
Handler_read_prev indicates the upper column of the index to be read, which usually occurs in order... DESC.
Handler_read_rnd_next: scan data files. The smaller the value, the better.