通过单个查询发现所有未使用的索引
Unused indexes by single query
通过单个查询发现所有未使用的索引
原文: http://www.mysqlperformanceblog.com/2008/09/12/unused-indexes-by-single-query/
Usually unused indexes are devil, they waste diskspace, cache, they make INSERT / DELETE / UPDATE operations slower and what makes them worse - it is hard to find them.
通常，未使用的索引是魔鬼，他们浪费磁盘空间，缓存，使INSERT/DELETE/UPDATE操作更慢，再加上，很难发现他们使情况更糟。
But now ( with userstatsV2.patch) you can find all unused indexes (since last restart of mysqld) by single query
但是现在(使用 userstatsV2.patch) 你可以通过一个查询找到所有的未使用 的索引(从上次重启mysqld之后)
1. SELECT DISTINCT s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME
2. FROM information_schema.statistics `s` LEFT JOIN information_schema.index_statistics INDXS
3. ON (s.TABLE_SCHEMA = INDXS.TABLE_SCHEMA AND
4. s.TABLE_NAME=INDXS.TABLE_NAME AND
5. s.INDEX_NAME=INDXS.INDEX_NAME)
6. WHERE INDXS.TABLE_SCHEMA IS NULL;
1. +--------------+---------------------------+-----------------+
2. | TABLE_SCHEMA | TABLE_NAME | INDEX_NAME |
3. +--------------+---------------------------+-----------------+
4. | art100 | article100 | ext_key |
5. | art100 | article100 | site_id |
6. | art100 | article100 | hash |
7. | art100 | article100 | forum_id_2 |
8. | art100 | article100 | published |
9. | art100 | article100 | inserted |
10. | art100 | article100 | site_id_2 |
11. | art100 | author100 | PRIMARY |
12. | art100 | author100 | site_id |
13. ...
14. +--------------+---------------------------+-----------------+
15. 1150 rows IN SET (1 min 44.23 sec)
As you see query is not fast, mainly because information_schema.statistics is slow by itself, but in any case very helpful.
可以看到，查询并不快，主要是因为information_schema.statistics 比较慢(?) , 但是很有用.
For some versions of the patch, where the table has only the INDEX_NAME and the ROWS_READ columns, you can use this query:
对于patch的一些版本，表中只有INDEX_NAME和 ROWS_READ 字段，可以使用这个查询
1. SELECT DISTINCT s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME
2. FROM information_schema.statistics `s`
3. LEFT JOIN information_schema.index_statistics IST
4. ON CONCAT_WS('.', s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME) = IST.INDEX_NAME
5. WHERE IST.INDEX_NAME IS NULL;