MySQL query redundant indexes and unused index operations

MySQL 5.7 and above versions provide direct query of redundant indexes, duplicate indexes, and views that have not used indexes. You can query directly.

Query redundant indexes and duplicate indexes

select * sys.from schema_redundant_indexes;

Query unused indexes

select * from sys.schema_unused_indexes;

If you want to use it in versions 5.6 and 5.5, just convert the view into a SQL statement query

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`

Supplement: mysql ID remainder index_mysql duplicate index, redundant index, unused index definition and search

1. Redundant and duplicate indexes

MySQL allows you to create multiple indexes on the same column. Whether intentional or unintentional, MySQL needs to maintain duplicate indexes separately, and the optimizer also needs to consider them one by one when optimizing queries, which affects performance. Duplicate indexes refer to indexes of the same type created on the same columns in the same order. Such creation of duplicates should be avoided and should be deleted immediately upon discovery. However, it is possible to create different types of indexes on the same column to meet different query requirements.

There are some differences between redundant indexes and duplicate indexes. If you create an index (a,b), then creating an index (a) is a redundant index because it is only a prefix index of the previous index. Therefore, (a,b) can also be used as (a). However, (b,a) is not a redundant index, and neither is index (b) because b is not the leftmost prefix column of index (a,b). In addition, other different types of indexes created on the same columns (such as hash indexes and full-text indexes) will not be redundant indexes of the btree index.

In addition: For the secondary index (a, id), id is the primary key. For innodb, the primary key column is already included in the secondary index, so this is also a redundant index. In most cases, redundant indexes are not needed, and you should try to expand existing indexes instead of creating new ones. However, sometimes redundant indexes are needed for performance reasons, because expanding an existing index will cause it to become too large, thus affecting the performance of other queries that use the index. For example, if you have an index on an integer column and now need to add an additional long varchar column to extend the index, the performance may drop dramatically, especially if there are queries that use this index as a covering index, or if this is a MyISAM table and there are many range queries (due to MyISAM prefix compression).

For example, the table userinfo, myisam engine, has 1 million rows, each state_id value has about 20,000 rows, and there is an index on the state_id column that is useful for the following query: For example: select count(*) from userinfo where state_id=5; Tested at 115 QPS per second

The index on the state_id column is not very useful for the following query, with a QPS of 10 per second:

select state_id,city,address from userinfo where state_id=5;

If the state_id index is expanded to (state_id, city, address), the performance of the second query will be faster, but the first query will be slower. If both queries are to be fast, the state_id column index must be made redundant. However, if it is an InnoDB table, the effect of non-redundant state_id column index on the first query is not obvious, because InnoDB does not use index compression. The QPS test results of select queries using different index strategies for MyISAM and InnmodB tables (the following test data is for reference only) are as follows:

Only the state_id column is indexed. Only the state_id_2 column is indexed. There are two indexes at the same time.

myisam, first query 114.96 25.40 112.19

myisam, second query 9.97 16.34 16.37

innodb, first query 108.55 100.33 107.97

innodb, second query 12.12 28.04 28.06

As can be seen from the above figure, when both indexes are used, the disadvantage is that the cost is higher. The following is the speed of inserting 1 million rows of data into the InnoDB and MyISAM tables under different index strategies (the following test data is for reference only):

Only the state_id column index has two indexes at the same time

InnoDB, 80 seconds 136 seconds when both indexes have enough content

MyISAM, when only one index has enough content 72 seconds 470 seconds

As you can see, regardless of the engine, the more indexes there are, the slower the insertion speed, especially when the memory bottleneck is reached after adding new indexes. The solution to redundant and duplicate indexes is simple. Just delete them. But the first thing to do is to find such indexes. You can find them through some complex queries to access the information_schema table. However, there are two simpler methods. Use some views in common_schema of shlomi noach to locate them. You can also use the pt-dupulicate-key-checker tool in percona toolkit. This tool finds redundant and duplicate indexes by analyzing the table structure. For large servers, it is more appropriate to use external tools. If there is a large amount of data or a large number of tables on the server, querying the information_schema table may cause performance problems. It is recommended to use the pt-dupulicate-key-checker tool.

Be very careful when dropping indexes:

If there is a query like where a=5 order by id on the InnoDB engine table, then index (a) will be very useful. The index (a,b) is actually the (a,b,id) index. For queries like where a=5 order by id, this index cannot be used for sorting and can only use file sorting. Therefore, it is recommended to use the pt-upgrade tool from the Percona Toolbox to double-check the planned index changes.

2. Unused indexes

In addition to redundant and duplicate indexes, there may be some indexes that are never used by the server. Such indexes are completely redundant and it is recommended to consider deleting them. There are two tools that can help locate unused indexes:

A: First turn on the userstat=ON server variable in Percona Server or MariaDB. It is turned off by default. Then let the server run for a while, and then query information_schema.index_statistics to find the usage frequency of each index.

B: Use the pt-index-usage tool in the Percona toolkit. This tool can read the query log, explain each query in the log, and then print out a report on the Guanyu index and query. This tool can not only find out which indexes are unused, but also understand the execution plan of the query. For example, in some cases, some similar queries are executed differently, which can help locate those queries that occasionally have poor server quality. The tool can also write the results to a MySQL table to facilitate query results.

The above is my personal experience. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM. If there are any mistakes or incomplete considerations, please feel free to correct me.