查找mysql中性能最差的10个索引

1. /*
2. SQL script to grab the worst performing indexes
3. in the whole server
4. */
5. SELECT
6.   t.TABLE_SCHEMA AS `db`
7. , t.TABLE_NAME AS `table`
8. , s.INDEX_NAME AS `inde name`
9. , s.COLUMN_NAME AS `field name`
10. , s.SEQ_IN_INDEX `seq in index`
11. , s2.max_columns AS `# cols`
12. , s.CARDINALITY AS `card`
13. , t.TABLE_ROWS AS `est rows`
14. , ROUND(((s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) * 100), 2) AS `sel %`
15. FROM INFORMATION_SCHEMA.STATISTICS s
16. INNER JOIN INFORMATION_SCHEMA.TABLES t
17.   ON s.TABLE_SCHEMA = t.TABLE_SCHEMA
18.   AND s.TABLE_NAME = t.TABLE_NAME
19. INNER JOIN (
20.   SELECT
21.      TABLE_SCHEMA
22.    , TABLE_NAME
23.    , INDEX_NAME
24.    , MAX(SEQ_IN_INDEX) AS max_columns
25.   FROM INFORMATION_SCHEMA.STATISTICS
26.   WHERE TABLE_SCHEMA != 'mysql'
27.   GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME
28. ) AS s2
29. ON s.TABLE_SCHEMA = s2.TABLE_SCHEMA
30. AND s.TABLE_NAME = s2.TABLE_NAME
31. AND s.INDEX_NAME = s2.INDEX_NAME
32. WHERE t.TABLE_SCHEMA != 'mysql'                         /* Filter out the mysql system DB */
33. AND t.TABLE_ROWS > 10                                   /* Only tables with some rows */
34. AND s.CARDINALITY IS NOT NULL                           /* Need at least one non-NULL value in the field */
35. AND (s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) < 1.00 /* Selectivity < 1.0 b/c unique indexes are perfect anyway */
36. ORDER BY `sel %`, s.TABLE_SCHEMA, s.TABLE_NAME          /* Switch to `sel %` DESC for best non-unique indexes */
37. LIMIT 10;

待测试。