mysql查询未使用索引,监控MySQL中未使用的索引

I have a set of servers where the maintenance query execution takes long. This is due to the indexes that are created by the developers at different points. Is there any way to monitor the unused indexes that adversely affect the execution.

解决方案

For mysql 5.6 before

By Default package of mysql does not have much statistics for analysis but there are some unofficial patches by which you can analyze.

By This patch you can analyze the stats and unused indexes.

Follow the below links to do that

In MySQL 5.6 and later, the PERFORMANCE_SCHEMA tracks index IO. If an index has no IO activity, it has not been used.

The sys schema now provides a convenient view to make it easier to identify unused indexes:

DROP VIEW IF EXISTS unused_indexes;

CREATE VIEW unused_indexes AS

SELECT object_schema,

object_name,

index_name

FROM performance_schema.table_io_waits_summary_by_index_usage

WHERE index_name IS NOT NULL

AND count_star = 0

ORDER BY object_schema, object_name;

Note that this shows indexes that haven't been used since the last restart of mysqld. The PERFORMANCE_SCHEMA statistics are reset at startup time.