為了演示，首先建兩個包含不良索引的表，並弄點數據。
mysql> show create table test1\G*************************** 1. row *************************** Table: test1Create Table: CREATE TABLE `test1` ( `id` int(11) NOT NULL, `f1` int(11) DEFAULT NULL, `f2` int(11) DEFAULT NULL, `f3` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `k1` (`f1`,`id`), KEY `k2` (`id`,`f1`), KEY `k3` (`f1`), KEY `k4` (`f1`,`f3`), KEY `k5` (`f1`,`f3`,`f2`)) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)mysql> show create table test2\G*************************** 1. row *************************** Table: test2Create Table: CREATE TABLE `test2` ( `id1` int(11) NOT NULL DEFAULT '0', `id2` int(11) NOT NULL DEFAULT '0', `b` int(11) DEFAULT NULL, PRIMARY KEY (`id1`,`id2`), KEY `k1` (`b`)) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)mysql> select count(*) from test2 group by b;+----------+| count(*) |+----------+| 32 || 17 |+----------+2 rows in set (0.00 sec)innodb 本身是聚簇表，每個二級索引本身就包含主鍵，類似 f1, id 的索引實際雖然沒有害處，但反映了使用者對 mysql 索引不了解。而類似 id, f1 的是多余索引，會浪費存儲空間，並影響數據更新性能。包含主鍵的索引用這樣一句 sql 就能全部找出來。
mysql> select c.*, pk from -> (select table_schema, table_name, index_name, concat('|', group_concat(column_name order by seq_in_index separator '|'), '|') cols -> from INFORMATION_SCHEMA.STATISTICS -> where index_name != 'PRIMARY' and table_schema != 'mysql' -> group by table_schema, table_name, index_name) c, -> (select table_schema, table_name, concat('|', group_concat(column_name order by seq_in_index separator '|'), '|') pk -> from INFORMATION_SCHEMA.STATISTICS -> where index_name = 'PRIMARY' and table_schema != 'mysql' -> group by table_schema, table_name) p -> where c.table_name = p.table_name and c.table_schema = p.table_schema and c.cols like concat('%', pk, '%');+--------------+------------+------------+---------+------+| table_schema | table_name | index_name | cols | pk |+--------------+------------+------------+---------+------+| test | test1 | k1 | |f1|id| | |id| || test | test1 | k2 | |id|f1| | |id| |+--------------+------------+------------+---------+------+2 rows in set (0.04 sec)包含重復前綴的索引，索引能由另一個包含該前綴的索引完全代替，是多余索引。多余的索引會浪費存儲空間，並影響數據更新性能。這樣的索引同樣用一句 sql 可以找出來。
mysql> select c1.table_schema, c1.table_name, c1.index_name,c1.cols,c2.index_name, c2.cols from -> (select table_schema, table_name, index_name, concat('|', group_concat(column_name order by seq_in_index separator '|'), '|') cols -> from INFORMATION_SCHEMA.STATISTICS -> where table_schema != 'mysql' and index_name!='PRIMARY' -> group by table_schema,table_name,index_name) c1, -> (select table_schema, table_name,index_name, concat('|', group_concat(column_name order by seq_in_index separator '|'), '|') cols -> from INFORMATION_SCHEMA.STATISTICS -> where table_schema != 'mysql' and index_name != 'PRIMARY' -> group by table_schema, table_name, index_name) c2 -> where c1.table_name = c2.table_name and c1.table_schema = c2.table_schema and c1.cols like concat(c2.cols, '%') and c1.index_name != c2.index_name;+--------------+------------+------------+------------+------------+---------+| table_schema | table_name | index_name | cols | index_name | cols |+--------------+------------+------------+------------+------------+---------+| test | test1 | k1 | |f1|id| | k3 | |f1| || test | test1 | k4 | |f1|f3| | k3 | |f1| || test | test1 | k5 | |f1|f3|f2| | k3 | |f1| || test | test1 | k5 | |f1|f3|f2| | k4 | |f1|f3| |+--------------+------------+------------+------------+------------+---------+4 rows in set (0.02 sec)這樣的索引由於仍然會掃描大量記錄，在實際查詢時通常會被忽略。但是在某些情況下仍然是有用的。因此需要根據實際情況進一步分析。這裡是區分度小於 10% 的索引，可以根據需要調整參數。
mysql> select p.table_schema, p.table_name, c.index_name, c.car, p.car total from -> (select table_schema, table_name, index_name, max(cardinality) car -> from INFORMATION_SCHEMA.STATISTICS -> where index_name != 'PRIMARY' -> group by table_schema, table_name,index_name) c, -> (select table_schema, table_name, max(cardinality) car -> from INFORMATION_SCHEMA.STATISTICS -> where index_name = 'PRIMARY' and table_schema != 'mysql' -> group by table_schema,table_name) p -> where c.table_name = p.table_name and c.table_schema = p.table_schema and p.car > 0 and c.car / p.car < 0.1;+--------------+------------+------------+------+-------+| table_schema | table_name | index_name | car | total |+--------------+------------+------------+------+-------+| test | test2 | k1 | 4 | 49 |+--------------+------------+------------+------+-------+1 row in set (0.04 sec)由於 innodb 是聚簇表，每個二級索引都會包含主鍵值。復合主鍵會造成二級索引龐大，而影響二級索引查詢性能，並影響更新性能。同樣需要根據實際情況進一步分析。
mysql> select table_schema, table_name, group_concat(column_name order by seq_in_index separator ',') cols, max(seq_in_index) len -> from INFORMATION_SCHEMA.STATISTICS -> where index_name = 'PRIMARY' and table_schema != 'mysql' -> group by table_schema, table_name having len>1;+--------------+------------+-----------------------------------+------+| table_schema | table_name | cols | len |+--------------+------------+-----------------------------------+------+| test | test2 | id1,id2 | 2 |+--------------+------------+-----------------------------------+------+1 rows in set (0.01 sec)（題圖來自：webfish.se）
原文：http://xiezhenye.com/2015/01/%E6%89%BE%E5%88%B0-mysql-%E6%95%B0%E6%8D%AE%E5%BA%93%E4%B8%AD%E7%9A%84%E4%B8%8D%E8%89%AF%E7%B4%A2%E5%BC%95.html作者： 謝振業
碼農必須要加班？NO！
知道碼農們都想擺脫加班狗、外賣臉的稱號，所以我們來了！
我們做了一個能讓程序員之間共享知識技能的APP，覺得可以顛覆程序員的工作方
式！
有人說我們癡心妄想，但我們不那麼認為。
為了能煽爛說我們癡心妄想的人的臉，現在我們急需程序員業內的牛哔-人物來給
我們“號脈”！“診斷費”豐厚！畢竟我們不差錢兒，只是想做到最好！
圈圈字典中講到，牛哔-人物是指群成員數高於1000人的QQ群主或關注人數高於
2000人的貼吧吧主或粉絲人數高於10000人的微博博主或成員數高於2000主題貼的版主
或單帖閱讀量高於2000博客主或人脈超級廣的圈內紅人。
對於未能達標的未來大神們，我們只能含淚表示：蜀黍，咱們來日方長，這次暫
時不約好嗎？待他日你立地成神，我必生死相依！
來？還是不來？
圈圈互動 接頭暗號：1955246408 （QQ）
