通过下面这条 SQL 语句来检视创建的索引,大部分情况下可以定位出 索引创建不合理的情况:
SELECT
t.TABLE_SCHEMA,
t.TABLE_NAME,
INDEX_NAME,
CARDINALITY,
TABLE_ROWS,
CARDINALITY / TABLE_ROWS AS SELECTIVITY
FROM
information_schema.TABLES t,
(SELECT
table_schema,
table_name,
index_name,
cardinality
FROM
information_schema.STATISTICS
WHERE (
table_schema,
table_name,
index_name,
seq_in_index
) IN
(SELECT
table_schema,
table_name,
index_name,
MAX(seq_in_index)
FROM
information_schema.STATISTICS
GROUP BY table_schema,
table_name,
index_name)) s
WHERE t.table_schema = s.table_schema
AND t.table_name = s.table_name
AND t.table_rows != 0
AND t.table_schema NOT IN (
'mysql',
'performance_schema',
'information_schema'
)
ORDER BY SELECTIVITY ;
若 CARDINALITY / TABLE_ROWS 小于10%(经验值),那么表示数据重复率较高,通常需要考虑是否有必要创建该索引。该语句运行的结果如下所示,列 SELECTIVITY 表示的就是选择性 。
上述 SQL 语句并不能工作在 MySQL 5.6 版本下(即使最新的 MySQL 5.6.28 版本),因为目前 5.6 的 STATISTICS 表中关于 Cardinality 的统计是错误的。具体可见 MySQL bugs #78066。但是,表 innodb_index_stats 中关于 Cardinality 值得统计依然是正确的。