如何识别MySQL数据库中未使用的索引？ [英] How do you identify unused indexes in a MySQL database?

问题描述

我最近完全重写了一个大项目。在这样做的过程中，我整合了大量随机MySQL查询。我记得在开发以前的代码库的过程中，我随心所欲地创建了索引，并且我确信有很多不再使用的索引。

I have recently completely re-written a large project. In doing so, I have consolidated great number of random MySQL queries. I do remember that over the course of developing the previous codebase, I created indexes on a whim, and I'm sure there are a great number that aren't used anymore.

有没有办法监控MySQL的索引使用情况，以确定哪些索引正在使用，哪些索引不使用？

Is there a way to monitor MySQL's index usage to determine which indexes are being used, and which ones are not?

推荐答案

我不认为这个信息在MySQL安装库存中可用。

I don't think this information is available in a stock MySQL installation.

Percona为MySQL提供工具和补丁来收集索引使用数据。

Percona makes tools and patches for MySQL to collect index usage data.

参见：

• 用户统计（和索引统计）


• < a href =http://www.mysqlperformanceblog.com/2012/06/02/how-expensive-is-user_statistics/\"rel =noreferrer> USER_STATISTICS有多贵？


• pt-index-usage

• User Statistics (and Index Statistics)
• How expensive is USER_STATISTICS?
• pt-index-usage

参见：

• 新 INDEX_STATISTICS info_schema中的表


• check-unused-keys：与INDEX_STATISTICS交互的工具


• < a href =http://dev.mysql.com/doc/refman/5.6/en/table-waits-summary-tables.html =noreferrer>新 table_io_waits_summary_by_index_usage MySQL 5.6中的performance_schema中的表

• New INDEX_STATISTICS table in the information_schema
• check-unused-keys: A tool to interact with INDEX_STATISTICS
• New table_io_waits_summary_by_index_usage table in performance_schema in MySQL 5.6

您可能还对名为 MySQLIndexAnalyzer ，有助于查找冗余索引。但是这个工具不知道哪些索引未被使用。

You may also be interested in a Java app called MySQLIndexAnalyzer, which helps to find redundant indexes. But this tool doesn't have any idea which indexes are unused.

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