姜总给的SQL。

1
2
3
4
5
6
7
8
9
10
11
12
13
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