mysql查询索引相关信息查询

#索引数大于7的表

select table_schema,table_name,count(*) count from  (select distinct TABLE_NAME,table_schema,index_NAME from information_schema.STATISTICS)A  group by table_schema,TABLE_NAME  HAVING count>7 order by count desc ;

#组合索引中字段多于5的表

select table_schema,table_name,index_NAME,count(1)  count  from information_schema.STATISTICS group by table_schema,TABLE_NAME,index_NAME  having count>5 order by count desc ;

#未使用过的索引

SELECT    a.object_schema,a.object_name,a.index_name FROM `performance_schema`.`table_io_waits_summary_by_index_usage` a  join  information_schema.tables b on  a.object_name=b.table_name WHERE a.COUNT_STAR = 0 AND a.object_schema not in ('mysql','information_schema','performance_schema`') AND a.index_name <> 'PRIMARY' AND a.SUM_TIMER_WAIT = 0  and a.index_name IS NOT NULL and b.TABLE_ROWS>100000   group by object_schema,object_name,index_name;