| MySQL 5.7以后怎么查看索引使用情况? | 您所在的位置：网站首页 › 查看索引是否使用 › MySQL 5.7以后怎么查看索引使用情况? |
|
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]> |
| CopyRight 2018-2019 实验室设备网 版权所有 |