监控MySQL中未使用的索引 [英] Monitor unused indexes in 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.

推荐答案

对于mysql 5.6之前的版本

For mysql 5.6 before

默认情况下，mysql的软件包没有太多统计信息可用于分析，但是有一些非正式的补丁可用于分析.

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

喜欢 userstats 之类的 http://ourdelta.org/docs/userstats

通过此补丁，您可以分析统计信息和未使用的索引.

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

按照以下链接进行操作

https://www.percona.com/blog/2012/06/30/find-unused-indexes/

在MySQL 5.6和更高版本中，PERFORMANCE_SCHEMA跟踪索引IO.如果索引没有IO活动，则该索引未被使用.

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

sys模式现在提供了一个方便的视图，可以更轻松地识别未使用的索引:

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

http://www.markleith.co.uk/2011/04/18/monitoring-table-and-index-io-with-performance_schema/

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;

请注意，这显示自上次重启mysqld以来未使用过的索引. PERFORMANCE_SCHEMA统计信息将在启动时重置.

这篇关于监控MySQL中未使用的索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！