快速定位不合理的索引——MySQL索引调优（一）

快速定位不合理的索引——MySQL索引调优（一）

2016-01-05 姜承尧 InsideMySQL
创建索引是门技术活，开发DBA的工作之一就是配合应用创建最优的索引。然大部分公司并没有开发DBA一职，大多数的索引创建需要由程序开发人员自己完成，这导致的一个后果是，索引创建的好与坏大部分情况下需要看这个程序猿的气质。

通常，Inside君通过下面这条SQL语句来检视创建的索引（同时喝着咖啡，听着音乐），大部分情况下可以定位出90%的索引创建不合理情况：

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;

上述的SQL语句利用了information_schema数据库下的元数据表TABLES、STATISTICS。表TABLES记录了表的基本信息，例如库名，表名，行数等。表STATISTICS记录了各个索引的CARDINALITY值。那么CARDINALITY / TABLE_ROWS表示的就是索引的选择性。在Inside君的《MySQL技术内幕：InnoDB存储引擎》一书中明确指出的是，在OLTP的应用场景下，创建的索引是要求高选择性的。若CARDINALITY / TABLE_ROWS小于10%（经验值），那么表示数据重复率较高，通常需要考虑是否有必要创建该索引。该语句运行的结果如下所示，列SELECTIVITY表示的就是选择性：

 

可惜的是上述SQL语句并不能工作在MySQL 5.6版本下（即使最新的MySQL 5.6.28版本），因为目前5.6的STATISTICS表中关于Cardinality的统计是错误的！！！具体可见MySQL bugs #78066。但是，表innodb_index_stats中关于Cardinality值得统计依然是正确的，那么问题来了：
有谁知道5.6下上述SQL该如何改写？
如何修复5.6下的Cardinality Bug？