mysql 5.6 in 索引_MySQL 5.6 版本未使用的索引和冗余的索引

1. 未使用的索引

select distinct

mysql.innodb_index_stats.table_name,

mysql.innodb_index_stats.index_name

from

mysql.innodb_index_stats

where

concat(mysql.innodb_index_stats.index_name,

mysql.innodb_index_stats.table_name) not in (select

concat(information_schema.statistics.index_name,

information_schema.statistics.table_name)

from

information_schema.statistics)

and mysql.innodb_index_stats.index_name <> 'GEN_CLUST_INDEX';

2.重复的索引

sELECT a.table_schema AS '数据库', a.table_name AS '表名', a.index_name AS '索引1', b.index_name AS '索引2', a.column_name AS '重复列名'

FROM information_schema.statistics a

JOIN information_schema.statistics b ON a.table_schema = b.table_schema

AND a.table_name = b.table_name

AND a.seq_in_index = b.seq_in_index

AND a.column_name = b.column_name

WHERE a.seq_in_index = 1

AND a.index_name != b.index_name;