為了演示，首先建兩個包含不良索引的表，并弄點(diǎn)數(shù)據(jù)。

mysql> show create table test1/G *************************** 1. row ***************************Table: test1 Create 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=latin1 1 row in set (0.00 sec)mysql> show create table test2/G *************************** 1. row ***************************Table: test2 Create 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=latin1 1 row in set (0.00 sec)mysql> select count(*) from test2 group by b; +----------+ | count(*) | +----------+ | 32 | | 17 | +----------+ 2 rows in set (0.00 sec)

1. 包含主鍵的索引

innodb 本身是聚簇表，每個二級索引本身就包含主鍵，類似 f1, id 的索引實(shí)際雖然沒有害處，但反映了使用者對 mysql 索引不了解。而類似 id, f1 的是多余索引，會浪費(fèi)存儲空間，并影響數(shù)據(jù)更新性能。包含主鍵的索引用這樣一句 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)

2. 重復(fù)索引前綴

包含重復(fù)前綴的索引，索引能由另一個包含該前綴的索引完全代替，是多余索引。多余的索引會浪費(fèi)存儲空間，并影響數(shù)據(jù)更新性能。這樣的索引同樣用一句 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)

3. 低區(qū)分度索引

這樣的索引由于仍然會掃描大量記錄，在實(shí)際查詢時通常會被忽略。但是在某些情況下仍然是有用的。因此需要根據(jù)實(shí)際情況進(jìn)一步分析。這里是區(qū)分度小于 10% 的索引，可以根據(jù)需要調(diào)整參數(shù)。

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)

4. 復(fù)合主鍵

由于 innodb 是聚簇表，每個二級索引都會包含主鍵值。復(fù)合主鍵會造成二級索引龐大，而影響二級索引查詢性能，并影響更新性能。同樣需要根據(jù)實(shí)際情況進(jìn)一步分析。

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)

原帖地址：http://www.codeceo.com/article/find-mysql-bad-index.html

轉(zhuǎn)載于:https://www.cnblogs.com/kzcdqbz/p/4753657.html

總結(jié)

以上是生活随笔為你收集整理的【转】找到 MySQL 数据库中的不良索引的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。