查看mysql数据库表上的重复索引列

coverl

有二种方法（http://qdjalone.blog.iyunv.com/1222376/1308620）
1. 借助pt-duplicate-key-checker，这是percona toolkit中的一个小工具

pt-duplicate-key-checker --host=localhost --user=xxx--password=xxx--database=xxx
以报表形式显示有重复索引的表，以及给出建议的删除语句
2. 利用information_schema.STATISTICS 数据字典
SELECT table_name,column_name,COUNT(1) ct
FROM information_schema.`STATISTICS`
WHERE seq_in_index=1
AND table_schema='test'
GROUP BY table_name,column_name
HAVING ct >=2;
其中 ct 位该索引列出现的次数！

利用这二种方法中的其中一种，逐个表的把重复索引列删除！
3.删除所有的重复索引
BEGIN
DECLARE tableNameXX VARCHAR(100);
DECLARE indexNameXX VARCHAR(100);
DECLARE sqll VARCHAR(1000);
DECLARE countIndexXX INT;
DECLARE done int;
DECLARE needUpdateC CURSOR FOR SELECT table_name,INDEX_NAME,COUNT(1) ct
FROM information_schema.`STATISTICS`
WHERE seq_in_index=1
AND table_schema='test' AND INDEX_NAME!='PRIMARY'
GROUP BY table_name,column_name
HAVING ct >=2;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
OPEN needUpdateC;
cursor_loop:LOOP
FETCH needUpdateC into tableNameXX,indexNameXX,countIndexXX;
SET sqll=CONCAT_ws(' ','drop index ',indexNameXX ,' on ',tableNameXX);
IF done=1 THEN
LEAVE cursor_loop;
END IF;
SET @sql=sqll;
PREPARE s1 FROM @sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
END LOOP cursor_loop;
CLOSE needUpdateC;
END