See if the index is being used in MySQL

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 ',  &nbsp ; 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