MySQL 5.7以后怎么查看索引使用情况?
0.在sys库中查看没用的索引
root@localhost [sys]>select * from schema_unused_indexes;
+-------------------+-------------+------------+
| object_schema | object_name | index_name |
+-------------------+-------------+------------+
| sysbench_testdata | sbtest1 | k_1 |
| sysbench_testdata | sbtest10 | k_10 |
| sysbench_testdata | sbtest3 | k_3 |
| sysbench_testdata | sbtest4 | k_4 |
| sysbench_testdata | sbtest5 | k_5 |
| sysbench_testdata | sbtest6 | k_6 |
| sysbench_testdata | sbtest7 | k_7 |
| sysbench_testdata | sbtest8 | k_8 |
| sysbench_testdata | sbtest9 | k_9 |
+-------------------+-------------+------------+
9 rows in set (0.00 sec)
- 通过show status like '%Handler_read%'方法查看:整体的
root@localhost [sysbench_testdata]>show status like '%Handler_read%';
+-----------------------+---------+
| Variable_name | Value |
+-----------------------+---------+
| Handler_read_first | 7 |
| Handler_read_key | 29 |
| Handler_read_last | 0 |
| Handler_read_next | 8446377 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 20 |
| Handler_read_rnd_next | 8344612 |
+-----------------------+---------+
7 rows in set (0.00 sec)
-
Handler_read_key这个值代表了一个行将索引值读的次数，很低的值表明增加索引得到的性能改善不高，因为索引并不经常使用。
-
Handler_read_rnd_next 的值高则查询低效，并且应该建立索引补救。这个值是指在数据文件中读下一行的请求数。如果正进行大量的表扫描，Handler_read_rnd_next的值较高，则通常说明表索引不正确或查询没有利用索引
2.查看具体某一个sql的索引使用情况 :
root@localhost [sysbench_testdata]>explain select k from sbtest2 where k=432 limit 2;
+----+-------------+---------+------------+------+---------------+------+---------+-------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+-------+--------+----------+-------------+
| 1 | SIMPLE | sbtest2 | NULL | ref | k_2 | k_2 | 4 | const | 110944 | 100.00 | Using index |
+----+-------------+---------+------------+------+---------------+------+---------+-------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
字段说明:
Type：告诉我们对表所使用的访问方式，主要包含如下集中类型；
◇ all：全表扫描
◇ const：读常量，且最多只会有一条记录匹配，由于是常量，所以实际上只需要读一次；
◇ eq_ref：最多只会有一条匹配结果，一般是通过主键或者唯一键索引来访问；
◇ fulltext：
◇ index：全索引扫描；
◇ index_merge：查询中同时使用两个（或更多）索引，然后对索引结果进行merge 之后再读取表数据；
◇ index_subquery：子查询中的返回结果字段组合是一个索引（或索引组合），但不是一个主键或者唯一索引；
◇ rang：索引范围扫描；
◇ ref：Join 语句中被驱动表索引引用查询；
◇ ref_or_null：与ref 的唯一区别就是在使用索引引用查询之外再增加一个空值的查询；
◇ system：系统表，表中只有一行数据；
◇ unique_subquery：子查询中的返回结果字段组合是主键或者唯一约束；
possible_keys：可能可以利用的索引的名字。这里的索引名字是创建索引时指定的索引昵称；如果索引没有昵称，则默认显示的是索引中第一个列的名字（在本例中，它是“firstname”）。默认索引名字的含义往往不是很明显。
key：它显示了MySQL实际使用的索引的名字。如果它为空（或NULL），则MySQL不使用索引。
key_len：索引中被使用部分的长度，以字节计
ref：列出是通过常量（const），还是某个表的某个字段（如果是join）来过滤（通过key）
的；
rows：MySQL所认为的它在找到正确的结果之前必须扫描的记录数。显然，这里最理想的数字就是1。
3.通过performance_schema可以查询到.查看sbtest2表索引情况的查询语句:
root@localhost [sysbench_testdata]>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='sbtest2';
- 具体查看过程:
root@localhost [sysbench_testdata]>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='sbtest2';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 1697669
Current database: sysbench_testdata
+-------------+-------------------+-------------+------------+------------+------------+-------------+
| object_type | object_schema | object_name | index_name | count_star | count_read | COUNT_FETCH |
+-------------+-------------------+-------------+------------+------------+------------+-------------+
| TABLE | sysbench_testdata | sbtest2 | PRIMARY | 0 | 0 | 0 |
| TABLE | sysbench_testdata | sbtest2 | k_2 | 76287298 | 76287298 | 76287298 |
| TABLE | sysbench_testdata | sbtest2 | NULL | 8344631 | 8344631 | 8344631 |
+-------------+-------------------+-------------+------------+------------+------------+-------------+
3 rows in set (0.00 sec)
root@localhost [sysbench_testdata]>select k from sbtest2 where k=432 limit 2;
+-----+
| k |
+-----+
| 432 |
| 432 |
+-----+
2 rows in set (0.00 sec)
root@localhost [sysbench_testdata]>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='sbtest2';
+-------------+-------------------+-------------+------------+------------+------------+-------------+
| object_type | object_schema | object_name | index_name | count_star | count_read | COUNT_FETCH |
+-------------+-------------------+-------------+------------+------------+------------+-------------+
| TABLE | sysbench_testdata | sbtest2 | PRIMARY | 0 | 0 | 0 |
| TABLE | sysbench_testdata | sbtest2 | k_2 | 76287300 | 76287300 | 76287300 |
| TABLE | sysbench_testdata | sbtest2 | NULL | 8344631 | 8344631 | 8344631 |
+-------------+-------------------+-------------+------------+------------+------------+-------------+
3 rows in set (0.01 sec)
root@localhost [sysbench_testdata]>select k from sbtest2 where id=432 limit 2;
+-------+
| k |
+-------+
| 49866 |
+-------+
1 row in set (0.00 sec)
root@localhost [sysbench_testdata]>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='sbtest2';
+-------------+-------------------+-------------+------------+------------+------------+-------------+
| object_type | object_schema | object_name | index_name | count_star | count_read | COUNT_FETCH |
+-------------+-------------------+-------------+------------+------------+------------+-------------+
| TABLE | sysbench_testdata | sbtest2 | PRIMARY | 1 | 1 | 1 |
| TABLE | sysbench_testdata | sbtest2 | k_2 | 76287300 | 76287300 | 76287300 |
| TABLE | sysbench_testdata | sbtest2 | NULL | 8344631 | 8344631 | 8344631 |
+-------------+-------------------+-------------+------------+------------+------------+-------------+
3 rows in set (0.00 sec)
root@localhost [sysbench_testdata]>