通过下面这条 SQL 语句来检视创建的索引,大部分情况下可以定位出 90% 的索引创建不合理情况:

    1. SELECT
    2. t.TABLE_SCHEMA,
    3. t.TABLE_NAME,
    4. INDEX_NAME,
    5. CARDINALITY,
    6. TABLE_ROWS,
    7. CARDINALITY / TABLE_ROWS AS SELECTIVITY
    8. FROM
    9. information_schema.TABLES t,
    10. (SELECT
    11. table_schema,
    12. table_name,
    13. index_name,
    14. cardinality
    15. FROM
    16. information_schema.STATISTICS
    17. WHERE (
    18. table_schema,
    19. table_name,
    20. index_name,
    21. seq_in_index
    22. ) IN
    23. (SELECT
    24. table_schema,
    25. table_name,
    26. index_name,
    27. MAX(seq_in_index)
    28. FROM
    29. information_schema.STATISTICS
    30. GROUP BY table_schema,
    31. table_name,
    32. index_name)) s
    33. WHERE t.table_schema = s.table_schema
    34. AND t.table_name = s.table_name
    35. AND t.table_rows != 0
    36. AND t.table_schema NOT IN (
    37. 'mysql',
    38. 'performance_schema',
    39. 'information_schema'
    40. )
    41. ORDER BY SELECTIVITY ;

    上述的 SQL 语句利用了 information_schema 数据库下的元数据表 TABLES、STATISTICS。

    • 表 TABLES 记录了表的基本信息,例如库名,表名,行数等。
    • 表 STATISTICS 记录了各个索引的 CARDINALITY 值。
    • 那么 CARDINALITY / TABLE_ROWS 表示的就是索引的选择性。

    在 Inside 君的《MySQL技术内幕:InnoDB存储引擎》一书中明确指出的是,在 OLTP 的应用场景下,创建的索引是要求高选择性的。

    若 CARDINALITY / TABLE_ROWS 小于10%(经验值),那么表示数据重复率较高,通常需要考虑是否有必要创建该索引。该语句运行的结果如下所示,列 SELECTIVITY 表示的就是选择性:
    MEpoEwcicyJkyDjxnUcdO9utquuRNrzcnWSnfIsaVQMmoeicTCbib2FmUryop4wmQ4Dpt1n5icPNpBLCerEwfl4Acg.jpg
    上述 SQL 语句并不能工作在 MySQL 5.6 版本下(即使最新的 MySQL 5.6.28 版本),因为目前 5.6 的 STATISTICS 表中关于 Cardinality 的统计是错误的。具体可见 MySQL bugs #78066。但是,表 innodb_index_stats 中关于 Cardinality 值得统计依然是正确的。

    作者:殷建卫 链接:https://www.yuque.com/yinjianwei/vyrvkf/yh2y71 来源:殷建卫 - 架构笔记 著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。