MySQL CARDINALITY确认索引选择性

SELECT

t.TABLE_SCHEMA,t.TABLE_NAME,INDEX_NAME, CARDINALITY, TABLE_ROWS,

CARDINALITY/TABLE_ROWS AS SELECTIVITY -- 得到选择性

FROM

TABLES t, -- 查询的表一，TABLES

(

SELECT

table_schema,

table_name,

index_name,

cardinality

FROM STATISTICS

WHERE (table_schema,table_name,index_name,seq_in_index) IN (

SELECT

table_schema,

table_name,

index_name,

MAX(seq_in_index)

FROM

STATISTICS

GROUP BY table_schema , table_name , index_name )

) s -- 查询的表二，就是上面3.2的查询结果

WHERE

t.table_schema = s.table_schema -- 通过库关联

AND t.table_name = s.table_name -- 再通过表变量

AND t.table_schema = 'employees' -- 指定某一个库名

ORDER BY SELECTIVITY;

linux技术交流群：295294329