為了示範，首先建兩個包含不良索引的表，并弄點資料。
<code>mysql> show create table test1\g</code>
<code>*************************** 1. row ***************************</code>
<code>table: test1</code>
<code>create table: create table `test1` (</code>
<code>`id` int(11) not null,</code>
<code>`f1` int(11) default null,</code>
<code>`f2` int(11) default null,</code>
<code>`f3` int(11) default null,</code>
<code>primary key (`id`),</code>
<code>key `k1` (`f1`,`id`),</code>
<code>key `k2` (`id`,`f1`),</code>
<code>key `k3` (`f1`),</code>
<code>key `k4` (`f1`,`f3`),</code>
<code>key `k5` (`f1`,`f3`,`f2`)</code>
<code>) engine=innodb default charset=latin1</code>
<code>1 row in set (0.00 sec)</code>
<code></code>
<code>mysql> show create table test2\g</code>
<code>table: test2</code>
<code>create table: create table `test2` (</code>
<code>`id1` int(11) not null default '0',</code>
<code>`id2` int(11) not null default '0',</code>
<code>`b` int(11) default null,</code>
<code>primary key (`id1`,`id2`),</code>
<code>key `k1` (`b`)</code>
<code>mysql> select count(*) from test2 group by b;</code>
<code>+----------+</code>
<code>| count(*) |</code>
<code>| 32 |</code>
<code>| 17 |</code>
<code>2 rows in set (0.00 sec)</code>
<a target="_blank"></a>
innodb 本身是聚簇表，每個二級索引本身就包含主鍵，類似 f1, id 的索引實際雖然沒有害處，但反映了使用者對 mysql 索引不了解。而類似 id, f1 的是多餘索引，會浪費存儲空間，并影響資料更新性能。包含主鍵的索引用這樣一句 sql 就能全部找出來。
<code>mysql> select c.*, pk from</code>
<code>-> (select table_schema, table_name, index_name, concat('|', group_concat(column_name order by seq_in_index separator '|'), '|') cols</code>
<code>-> from information_schema.statistics</code>
<code>-> where index_name != 'primary' and table_schema != 'mysql'</code>
<code>-> group by table_schema, table_name, index_name) c,</code>
<code>-> (select table_schema, table_name, concat('|', group_concat(column_name order by seq_in_index separator '|'), '|') pk</code>
<code>-> where index_name = 'primary' and table_schema != 'mysql'</code>
<code>-> group by table_schema, table_name) p</code>
<code>-> where c.table_name = p.table_name and c.table_schema = p.table_schema and c.cols like concat('%', pk, '%');</code>
<code>+--------------+------------+------------+---------+------+</code>
<code>| table_schema | table_name | index_name | cols | pk |</code>
<code>| test | test1 | k1 | |f1|id| | |id| |</code>
<code>| test | test1 | k2 | |id|f1| | |id| |</code>
<code>2 rows in set (0.04 sec)</code>
包含重複字首的索引，索引能由另一個包含該字首的索引完全代替，是多餘索引。多餘的索引會浪費存儲空間，并影響資料更新性能。這樣的索引同樣用一句 sql 可以找出來。
<code>mysql> select c1.table_schema, c1.table_name, c1.index_name,c1.cols,c2.index_name, c2.cols from</code>
<code>-> where table_schema != 'mysql' and index_name!='primary'</code>
<code>-> group by table_schema,table_name,index_name) c1,</code>
<code>-> (select table_schema, table_name,index_name, concat('|', group_concat(column_name order by seq_in_index separator '|'), '|') cols</code>
<code>-> where table_schema != 'mysql' and index_name != 'primary'</code>
<code>-> group by table_schema, table_name, index_name) c2</code>
<code>-> 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;</code>
<code>+--------------+------------+------------+------------+------------+---------+</code>
<code>| table_schema | table_name | index_name | cols | index_name | cols |</code>
<code>| test | test1 | k1 | |f1|id| | k3 | |f1| |</code>
<code>| test | test1 | k4 | |f1|f3| | k3 | |f1| |</code>
<code>| test | test1 | k5 | |f1|f3|f2| | k3 | |f1| |</code>
<code>| test | test1 | k5 | |f1|f3|f2| | k4 | |f1|f3| |</code>
<code>4 rows in set (0.02 sec)</code>
這樣的索引由于仍然會掃描大量記錄，在實際查詢時通常會被忽略。但是在某些情況下仍然是有用的。是以需要根據實際情況進一步分析。這裡是區分度小于 10% 的索引，可以根據需要調整參數。
<code>mysql> select p.table_schema, p.table_name, c.index_name, c.car, p.car total from</code>
<code>-> (select table_schema, table_name, index_name, max(cardinality) car</code>
<code>-> where index_name != 'primary'</code>
<code>-> group by table_schema, table_name,index_name) c,</code>
<code>-> (select table_schema, table_name, max(cardinality) car</code>
<code>-> where index_name = 'primary' and table_schema != 'mysql'</code>
<code>-> group by table_schema,table_name) p</code>
<code>-> 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;</code>
<code>+--------------+------------+------------+------+-------+</code>
<code>| table_schema | table_name | index_name | car | total |</code>
<code>| test | test2 | k1 | 4 | 49 |</code>
<code>1 row in set (0.04 sec)</code>
由于 innodb 是聚簇表，每個二級索引都會包含主鍵值。複合主鍵會造成二級索引龐大，而影響二級索引查詢性能，并影響更新性能。同樣需要根據實際情況進一步分析。
<code>mysql> select table_schema, table_name, group_concat(column_name order by seq_in_index separator ',') cols, max(seq_in_index) len</code>
<code>-> from information_schema.statistics</code>
<code>-> where index_name = 'primary' and table_schema != 'mysql'</code>
<code>-> group by table_schema, table_name having len>1;</code>
<code>+--------------+------------+-----------------------------------+------+</code>
<code>| table_schema | table_name | cols | len |</code>
<code>| test | test2 | id1,id2 | 2 |</code>
<code>1 rows in set (0.01 sec)</code>
<code>本文來自雲栖社群合作夥伴“linux中國”，原文釋出日期：2015-08-18</code>
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)