分析MySQL表上没有或使用次数少的索引

2017-07-03 10:08 Kevin.hhl 阅读(1323) 评论(0) 收藏举报

1. 摘要：

DBA经常会有新接手的业务数据库，之前大多事RD自己维护，没有专职DBA来维护MySQL，建索引随心所欲，导致好多表上有大量的索引，有的表索引大小比表数据都要大很多，作为一名DBA看到这种情况是不是会头大，很想把那些没用的index全部干掉，但也不能随便就干掉，怎么办，表示很需要耗时间。还有一种情况，DBA建了索引，怎么查看这些已经建完的索引就一定使用到了呢？有些业务可能过了一段时间就更改了业务需求或者已经下线部分功能，这时候DBA也需要及时找出哪些"僵尸"索引，若发现有比较长的时间都该索引都没有使用，是不是自动发个邮件要找RD确认下，然后决定索引要不要删除。

下面这些表索引都是比较大的，是不是合理呢？暂不下结论：

1.1 预备知识：

1）查看某个表某个索引的大小：

比如查看db:hhl table_name:t1 上索引的大小。

前提：~~MySQL 5.6~~ 开始可以开启自动计算统计信息并持久化到上面两张表，相关参数: innodb_stats_persistent 。

mysql> set global innodb_stats_persistent=on ;
ANALYZE table t;
SELECT sum(stat_value) pages,index_name, sum(stat_value) * @@innodb_page_size size
FROM mysql.innodb_index_stats
WHERE database_name = 'hhl' AND table_name = 't1' AND stat_description = 'Number of pages in the index'
GROUP BY index_name;

2. 怎么扫描MySQL表上无用的索引

2.1 通过performance_schema.table_io_waits_summary_by_index_usage

前提：打开performance_schema=ON

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_schema not in('mysql','performance_schema','information_schema');

2.2 通过INDEX_STATISTICS、INNODB_INDEX_STATS

下面找出没有被使用过的索引：

SELECT distinct INNODB_INDEX_STATS.TABLE_NAME, INNODB_INDEX_STATS.INDEX_NAME
FROM mysql.INNODB_INDEX_STATS as INNODB_INDEX_STATS
WHERE CONCAT(INNODB_INDEX_STATS.index_name, INNODB_INDEX_STATS.table_name) NOT IN(SELECT CONCAT(index_statistics.index_name, index_statistics.table_name) FROM information_schema.index_statistics as index_statistics) AND INNODB_INDEX_STATS.database_name in('hhl') AND INNODB_INDEX_STATS.INDEX_NAME<>'GEN_CLUST_INDEX';

2.3 通过userstat

Percoa Server和MariaDB 在information_schema库下增加表：USER_STATISTICS

mysql> set global userstat=on;
mysql> SELECT * FROM INDEX_STATISTICS;
+--------------+--------------------+------------------------------------------+-----------+
| TABLE_SCHEMA | TABLE_NAME         | INDEX_NAME                               | ROWS_READ |
+--------------+--------------------+------------------------------------------+-----------+
| hydra        | users_profile      | PRIMARY                                  |         5 |
| mysql        | innodb_index_stats | PRIMARY                                  |       468 |
| hydra        | users_profile      | users_profile_name_430080dfe590327b_uniq |         6 |
+--------------+--------------------+------------------------------------------+-----------+

2.4 通过pt-index-usage

pt-index-usage可以分析log文件(slowlog), 通常可以开启slowlog,一段时间内把long_query_time=0，使得sql都记录到slowlog中，再用pt-index-usage分享slowlog文件，具体使用如下：

[root@bj-b-mongo-demo01-c.hb2 log]#/usr/bin/pt-index-usage /data/servers/data/my3306/log/mysqld-slow.log --noversion-check --host=localhost
 —user=xx —password=xxx --socket=/data/servers/data/my3306/run/mysqld.sock
ALTER TABLE `hhl`.`api_order` DROP KEY `api_order_already_repay_penalty_3c96f07a0ce49c70_uniq`, DROP KEY `api_order_auto_repay_time_4eae0f5
44f40b0d7_uniq`, DROP KEY `api_order_created_time_12bbdaa144debf81_uniq`, DROP KEY `api_order_f5bf48aa`, DROP KEY `api_order_finish_repay_t
ime_896d8b11358405_uniq`, DROP KEY `api_order_identity_4c1f2f8da4bcb385_uniq`, DROP KEY `api_order_last_modified_49172199acc53382_uniq`, DR
OP KEY `api_order_loan_special_time_a71739c13f51a22_uniq`; -- type:non-unique
ALTER TABLE `hhl`.`users_profile` DROP KEY `users_profile_authorized_at_467eaf80734c4140_uniq`, DROP KEY `users_profile_bank_card_num_522fe
0d01e89edd_uniq`, DROP KEY `users_profile_changed_time_584581e73814c578_uniq`, DROP KEY `users_profile_city_73a774073db52f2f_uniq`, DROP KE
Y `users_profile_name_430080dfe590327b_uniq`, DROP KEY `users_profile_province_62f5b6fc04eda9ac_uniq`; -- type:non-unique

总结：推荐使用pt-index-usage,在业务低峰或从库上取slowlog来分析。

参考文献：

［1］https://www.percona.com/doc/percona-toolkit/LATEST/pt-index-usage.html#cmdoption-pt-index-usage--progress

刷新页面返回顶部

Kevin.hhl