Http://blog.sina.com.cn/s/blog_5037eacb0102vm5c.html
See if the index is used in official MySQL:
in the Percona version or Marida can be seen through the information_schea.index_statistics, in the official version of how to view it? select Object_type,object_schema,object_name,index_name,count_star,count_read,count_fetch from Performance_schema.table_io_waits_summary_by_index_usage should be available through the SQL above. If the number of Read,fetch is 0, it should not have been used. With the following example, you can confirm that: starts mysql:mysql> show CREATE table a.t3;+-------+--------------------------------------- --------------------------------------------------------------------------------------------------------------- ---------------------------------+| Table | Create Table |+-------+---------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- --------------+| T3 | CREATE TABLE ' T3 ' ( ' id ' int (one) ' Default null, ' name ' varchar (TEN) not NULL default ' BB ',   ; KEY ' idx_t3 ' (' id '), key ' Idx_t3_name ' (' name ')) Engine=innodb DEFAult Charset=utf8 |+-------+------------------------------------------------------------------------------------ ---------------------------------------------------------------------------------------------------+1 row in Set ( 0.01 sec) mysql> Select Object_type,object_schema,object_name,index_name,count_star,count_read,count_ FETCH from Performance_schema.table_io_waits_summary_by_index_usage where object_name= ' t3 '; +-------------+-------- -------+-------------+-------------+------------+------------+-------------+| object_type | Object_schema | object_name | Index_name | Count_star | Count_read | Count_fetch |+-------------+---------------+-------------+-------------+------------+------------+------------- +| TABLE | A | T3 | Idx_t3 | 0 | 0 | 0 | | TABLE | A | T3 | Idx_t3_name | 0 | 0 | 0 | | TABLE | A | T3 | NULL | 0 | 0 | 0 |+-------------+---------------+-------------+--- ----------+------------+------------+-------------+3 rows in Set (0.01 sec) mysql> explain select id from T3 where id=1; ERROR 1046 (3d000): No database selectedmysql> explain select ID from A.T3 where id=1;+----+-------------+-------+----- -+---------------+--------+---------+-------+------+-------------+| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |+----+-------------+-------+------+---------------+--------+-------- -+-------+------+-------------+| 1 | Simple | T3 | Ref | Idx_t3 | Idx_t3 | 5 | Const | 1 | Using index |+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+ 1 row in Set (0.00 sec) mysql> Select Object_type,object_schema,object_name,index_name,count_star,count_read, Count_fetch from Performance_schema.table_io_waits_summary_by_index_usage where object_name= ' t3 '; +-------------+---------------+------------ -+-------------+------------+------------+-------------+| object_type | Object_schema | object_name | Index_name | Count_star | Count_read | Count_fetch |+-------------+---------------+-------------+-------------+------------+------------+------------- +| TABLE | A | T3 | Idx_t3 | 0 | 0 | 0 | | TABLE | A | T3 | Idx_t3_name | 0 | 0 | 0 | | TABLE | A | T3 | NULL | 0 | 0 | 0 |+-------------+---------------+-------------+--- ----------+------------+------------+-------------+3 rows in Set (0.01 sec) mysql> use A; Reading table information for completion of table and column namesyou can turn off this feature to get a quicker startup W Ith-a database changedmysql> Select id from t3 where id=1; Empty Set (0.00 sec) mysql> Select Object_type,object_schema,object_name,index_name,count_star,count_read, Count_fetch from Performance_schema.table_io_waits_summary_by_index_usage where object_name= ' t3 '; +-------------+- --------------+-------------+-------------+------------+------------+-------------+| object_type | Object_schema | object_name | Index_name | Count_star | Count_read | Count_fetch |+-------------+---------------+-------------+-------------+------------+------------+------------- +| TABLE | A | T3 | Idx_t3 | 1 | 1 | 1 | | TABLE | A | T3 | Idx_t3_name | 0 | 0 | 0 | | TABLE | A | T3 | NULL | 0 | 0 | 0 |+-------------+---------------+-------------+--- ----------+------------+------------+-------------+3 rows in Set (0.00 sec) mysql> Select IDs from T3 where id=10; +------+ | ID |+------+| 10 | |   10 |+------+2 rows in Set (0.00 sec) mysql> Select Object_type,object_schema,object_name,index_ Name,count_star,count_read,count_fetch from Performance_schema.table_io_waits_summary_by_index_usage where object_ Name= ' T3 ', +-------------+---------------+-------------+-------------+------------+------------+-------------+| object_type | Object_schema | object_name | Index_name | Count_star | Count_read | Count_fetch |+-------------+---------------+-------------+-------------+------------+------------+------------- +| TABLE | A | T3 | Idx_t3 | 4 | 4 | 4 | | TABLE | A | T3 | Idx_t3_name | 0 | 0 | 0 | | TABLE | A | T3 | NULL | 0 | 0 | 0 |+-------------+---------------+-------------+--- ----------+------------+------------+-------------+3 rows in Set (0.01 sec) mysql> select name from T3 where name = ' a '; Empty Set (0.00 sec) mysql> Select Object_type,object_schema,object_name,index_name,count_star,count_read, Count_fetch from Performance_schema.table_io_waits_summary_by_index_usage where object_name= ' t3 '; +-------------+-- -------------+-------------+-------------+------------+------------+-------------+| object_type | Object_schema | object_name | Index_name | Count_star | Count_read | Count_fetch |+-------------+---------------+-------------+-------------+------------+------------+------------- +| TABLE | A | T3 | Idx_t3 | 4 | 4 | 4 | | TABLE | A | T3 | Idx_t3_name | 1 | 1 | 1 | | TABLE | A | T3 | NULL | 0 | 0 | 0 |+-------------+---------------+-------------+-------------+------------+------------+-------------+3 rows in Set (0.01 sec) mysql> select name from T3 where name= ' name1 '; +-------+| Name |+-------+| name1 |+-------+1 row in Set (0.01 sec) mysql> Select Object_type,object_schema,object_name,index_name,count_ Star,count_read,count_fetch from Performance_schema.table_io_waits_summary_by_index_usage where object_name= ' t3 '; + -------------+---------------+-------------+-------------+------------+------------+-------------+| object_type | Object_schema | object_name | Index_name | Count_star | Count_read | Count_fetch |+-------------+---------------+-------------+-------------+------------+------------+------------- +| TABLE | A | T3 | Idx_t3 | 4 | 4 | 4 | | TABLE | A | T3 | Idx_t3_name | 3 | 3 | 3 | | TABLE | A | T3 | NULL | 0 | 0 | 0 |+-------------+---------------+-------------+--- ----------+------------+------------+-------------+3 rows in Set (0.01 sec) After the index is used, the corresponding value is incremented.
See if the index is used in MySQL