mysql监控无用索引_找出mysql中无用的索引

select

t.TABLE_SCHEMA

,t.TABLE_NAME

,s.INDEX_NAME

,s.COLUMN_NAME

,s.SEQ_IN_INDEX

,(

SELECT MAX(SEQ_IN_INDEX)

FROM INFORMATION_SCHEMA.STATISTICS s2

WHERE s.TABLE_SCHEMA = s2.TABLE_SCHEMA

AND s.TABLE_NAME = s2.TABLE_NAME

AND s.INDEX_NAME = s2.INDEX_NAME

) AS `COLS_IN_INDEX`

, s.CARDINALITY AS "CARD"

,t.TABLE_ROWS AS "ROMS"

,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

WHERE t.TABLE_SCHEMA != 'mysql'

and t.TABLE_ROWS > 10

AND s.CARDINALITY IS NOT NULL

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

ORDER BY `SEL %`,TABLE_SCHEMA,TABLE_NAME

LIMIT 10;

SELECT DISTINCT s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME

FROM information_schema.statistics `s` LEFT JOIN information_schema.index_statistics INDXS

ON (s.TABLE_SCHEMA = INDXS.TABLE_SCHEMA AND

s.TABLE_NAME=INDXS.TABLE_NAME AND

s.INDEX_NAME=INDXS.INDEX_NAME)

WHERE INDXS.TABLE_SCHEMA IS NULL;

SELECT concat('alter table ',d.table_schema,'.',d.table_name,' drop index ',group_concat(index_name separator ',drop index '),';') stmt FROM (SELECT DISTINCT s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME FROM information_schema.statistics s LEFT JOIN information_schema.index_statistics iz ON (s.TABLE_SCHEMA = iz.TABLE_SCHEMA AND s.TABLE_NAME=iz.TABLE_NAME AND s.INDEX_NAME=iz.INDEX_NAME) WHERE iz.TABLE_SCHEMA IS NULL AND s.NON_UNIQUE=1 AND s.INDEX_NAME!='PRIMARY' AND (SELECT rows_read+rows_changed FROM information_schema.table_statistics ts WHERE ts.table_schema=s.table_schema AND ts.table_name=s.table_name)>0) d GROUP BY table_schema,table_name;

SELECT DISTINCT s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME

FROM information_schema.statistics `s`

LEFT JOIN information_schema.index_statistics IST

ON CONCAT_WS('.', s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME) = IST.INDEX_NAME

WHERE IST.INDEX_NAME IS NULL;