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;
ANALYZEtablet;SELECT sum(stat_value) pages,index_name, sum(stat_value) * @@innodb_page_sizesizeFROMmysql.innodb_index_statsWHERE 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_FETCHfromperformance_schema.table_io_waits_summary_by_index_usagewhere object_schema not in('mysql','performance_schema','information_schema');
2.2 通过INDEX_STATISTICS、INNODB_INDEX_STATS
下面找出没有被使用过的索引:
SELECT distinctINNODB_INDEX_STATS.TABLE_NAME, INNODB_INDEX_STATS.INDEX_NAMEFROM mysql.INNODB_INDEX_STATS asINNODB_INDEX_STATSWHERE 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 * FROMINDEX_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 |
+--------------+--------------------+------------------------------------------+-----------+
相关的表:
mysql> show tables like '%STATISTICS%';+---------------------------------------------+
| Tables_in_information_schema (%STATISTICS%) |
+---------------------------------------------+
| CLIENT_STATISTICS |
| INDEX_STATISTICS |
| STATISTICS |
| TABLE_STATISTICS |
| THREAD_STATISTICS |
| USER_STATISTICS |
+---------------------------------------------+
目前还不支持分区表。
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
OPKEY `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`, DROPKE
Y `users_profile_name_430080dfe590327b_uniq`,DROP KEY `users_profile_province_62f5b6fc04eda9ac_uniq`; --type:non-unique
总结:推荐使用pt-index-usage,在业务低峰或从库上取slowlog来分析。
参考文献: