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

有二种方法（http://qdjalone.blog.51cto.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