mysql使用数据量少的索引_分析MySQL表上没有或使用次数少的索引-CSDN博客

本文链接：https://blog.csdn.net/weixin_34982065/article/details/113252044

1. 摘要：

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

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

tb_index.png?version=1&modificationDate=1498730579000&api=v2

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;+--------------+--------------------+------------------------------------------+-----------+

+--------------+--------------------+------------------------------------------+-----------+

+--------------+--------------------+------------------------------------------+-----------+