mysql 索引的使用情况_监控mysql索引使用效率的脚本

SELECT

t.table_schema AS db,

t.table_name   AS tab_name,

s.index_name   AS index_name,

s.column_name  AS field_name,

s.seq_in_index  AS  seq_in_index,

s2.max_columns AS max_col,

s.cardinality  AS cardinality,

t.table_rows   AS table_rows,

ROUND(((s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) * 100), 2) AS sel

FROM information_schema.statistics s

INNER JOIN information_schema.tables t

ON s.table_schema = t.table_schema

AND s.table_name = t.table_name

INNER JOIN (SELECT

table_schema,

table_name,

index_name,

MAX(seq_in_index) AS max_columns

FROM information_schema.statistics

WHERE table_schema != 'mysql'

GROUP BY table_schema, table_name, index_name) AS s2

ON s.table_schema = s2.table_schema

AND s.table_name = s2.table_name

AND s.index_name = s2.index_name

WHERE t.table_schema != 'mysql'

AND t.table_rows > 100

AND s.CARDINALITY IS NOT NULL

AND (s.CARDINALITY / IFNULL(t.TABLE_ROWS, 0.01)) < 1.00

ORDER BY sel, s.table_schema, s.table_name

LIMIT 10

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/133735/viewspace-732013/，如需转载，请注明出处，否则将追究法律责任。