直接执行语句
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表示的就是索引的选择性。在OLTP的应用场景下,创建的索引是要求高选择性的。若CARDINALITY / TABLE_ROWS小于10%(经验值),那么表示数据重复率较高,通常需要考虑是否有必要创建该索引。该语句运行的结果如下所示,列SELECTIVITY表示的就是选择性。