How to Find Unused Indexes in MySQL: Evaluating the Necessity of Indexes Based on Usage Frequency

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 如何查找mysql里面没有用到的索引。根据索引使用次数评估索引的必要性

| username: tidb狂热爱好者

【TiDB Usage Environment】Production Environment / Testing / Poc
【TiDB Version】6.5
【Reproduction Path】What operations were performed when the issue occurred
【Encountered Issues: Issue Phenomenon and Impact】
【Resource Configuration】
【Attachments: Screenshots / Logs / Monitoring】

2
| username: Kongdom | Original post link

Baidu’s, let’s verify it together https://www.jb51.net/article/208560.htm

Query redundant indexes and duplicate indexes

select a.`table_schema`,a.`table_name`,a.`index_name`,a.`index_columns`,b.`index_name`,b.`index_columns`,concat('ALTER TABLE `',a.`table_schema`,'`.`',a.`table_name`,'` DROP INDEX `',a.`index_name`,'`') from ((select `information_schema`.`statistics`.`TABLE_SCHEMA` AS `table_schema`,`information_schema`.`statistics`.`TABLE_NAME` AS `table_name`,`information_schema`.`statistics`.`INDEX_NAME` AS `index_name`,max(`information_schema`.`statistics`.`NON_UNIQUE`) AS `non_unique`,max(if(isnull(`information_schema`.`statistics`.`SUB_PART`),0,1)) AS `subpart_exists`,group_concat(`information_schema`.`statistics`.`COLUMN_NAME` order by `information_schema`.`statistics`.`SEQ_IN_INDEX` ASC separator ',') AS `index_columns` from `information_schema`.`statistics` where ((`information_schema`.`statistics`.`INDEX_TYPE` = 'BTREE') and (`information_schema`.`statistics`.`TABLE_SCHEMA` not in ('mysql','sys','INFORMATION_SCHEMA','PERFORMANCE_SCHEMA'))) group by `information_schema`.`statistics`.`TABLE_SCHEMA`,`information_schema`.`statistics`.`TABLE_NAME`,`information_schema`.`statistics`.`INDEX_NAME`) a join (select `information_schema`.`statistics`.`TABLE_SCHEMA` AS `table_schema`,`information_schema`.`statistics`.`TABLE_NAME` AS `table_name`,`information_schema`.`statistics`.`INDEX_NAME` AS `index_name`,max(`information_schema`.`statistics`.`NON_UNIQUE`) AS `non_unique`,max(if(isnull(`information_schema`.`statistics`.`SUB_PART`),0,1)) AS `subpart_exists`,group_concat(`information_schema`.`statistics`.`COLUMN_NAME` order by `information_schema`.`statistics`.`SEQ_IN_INDEX` ASC separator ',') AS `index_columns` from `information_schema`.`statistics` where ((`information_schema`.`statistics`.`INDEX_TYPE` = 'BTREE') and (`information_schema`.`statistics`.`TABLE_SCHEMA` not in ('mysql','sys','INFORMATION_SCHEMA','PERFORMANCE_SCHEMA'))) group by `information_schema`.`statistics`.`TABLE_SCHEMA`,`information_schema`.`statistics`.`TABLE_NAME`,`information_schema`.`statistics`.`INDEX_NAME`) b on(((a.`table_schema` = b.`table_schema`) and (a.`table_name` = b.`table_name`)))) where ((a.`index_name` <> b.`index_name`) and (((a.`index_columns` = b.`index_columns`) and ((a.`non_unique` > b.`non_unique`) or ((a.`non_unique` = b.`non_unique`) and (if((a.`index_name` = 'PRIMARY'),'',a.`index_name`) > if((b.`index_name` = 'PRIMARY'),'',b.`index_name`))))) or ((locate(concat(a.`index_columns`,','),b.`index_columns`) = 1) and (a.`non_unique` = 1)) or ((locate(concat(b.`index_columns`,','),a.`index_columns`) = 1) and (b.`non_unique` = 0))));

Query unused indexes

select `information_schema`.`statistics`.`TABLE_SCHEMA` AS `table_schema`,`information_schema`.`statistics`.`TABLE_NAME` AS `table_name`,`information_schema`.`statistics`.`INDEX_NAME` AS `index_name`,max(`information_schema`.`statistics`.`NON_UNIQUE`) AS `non_unique`,max(if(isnull(`information_schema`.`statistics`.`SUB_PART`),0,1)) AS `subpart_exists`,group_concat(`information_schema`.`statistics`.`COLUMN_NAME` order by `information_schema`.`statistics`.`SEQ_IN_INDEX` ASC separator ',') AS `index_columns` from `information_schema`.`statistics` where ((`information_schema`.`statistics`.`INDEX_TYPE` = 'BTREE') and (`information_schema`.`statistics`.`TABLE_SCHEMA` not in ('mysql','sys','INFORMATION_SCHEMA','PERFORMANCE_SCHEMA'))) group by `information_schema`.`statistics`.`TABLE_SCHEMA`,`information_schema`.`statistics`.`TABLE_NAME`,`information_schema`.`statistics`.`INDEX_NAME`
3
| username: Mark | Original post link

Method 1: You can use the open-source tool pt-index-usage to generate a report of unused indexes.

pt-index-usage --host=10.113.1.187 -P 3306 --user=dbazl --password=Dbazl_2023 /data/mysql/3306/logs/mysql.slow

The tool can print results in the terminal or generate a report to be stored in the database for summary analysis.

Method 2:
Use MySQL’s built-in system data dictionary table

performance_schema.table_io_waits_summary_by_index_usage

to find unused indexes in the business database. In later versions of 5.7, you can directly query the dictionary table under the sys schema.
Example: (excluding the mysql system database)

select object_schema, object_name, index_name from sys.schema_unused_indexes where object_schema not in('sys','mysql','performance_schema','information_schema');

The above is for reference.

4
| username: FutureDB | Original post link

There is generally no problem with checking redundant indexes and duplicate indexes, but there is a minor flaw. For example, if the primary key or unique key is column1, and a regular index (column1, column2) is created, this will also be scanned.

5
| username: FutureDB | Original post link

In this case, the generated statement for dropping the index is also problematic.

6
| username: Kongdom | Original post link

:thinking: It may need some improvement~

7
| username: zhang_2023 | Original post link

Is it possible to check the usage frequency of an index?