找出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;