MySQL中重复索引和重复外键清理

超、凢脫俗 2024-02-18 20:22 148阅读 0赞

MySQL允许在相同列上创建重复的索引，但这样做对数据库却是有害而无利的，需要定期检查此类重复索引以改善数据库性能。

可减少磁盘空间占用、减少磁盘IO、减少优化器优化查询时需要比较的索引个数、减少数据库维护冗余索引的各类开销、提高数据库性能（插入、更新、删除）

**重复索引检测**  
pt-duplicate-key-checker：通过SHOW CREATE TABLE输出的表定义检测MySQL表中重复或者冗余的索引或外键

可以检测到的冗余/重复索引类型：若某个索引和另外某个索引以同样的顺序包含同样的列，或者该索引包含的列是另外某个索引的最左前缀列，则被认为是重复/冗余的索引。默认情况下只在同类型的索引间（如BTREE索引）进行比较，不同类型的索引即使符合上述描述也不会被认为是重复/冗余,但这一行为可以通过参数改变。除此之外，还可检测重复的外键，即引用的表和列均相同的外键。对于聚簇索引的表，在辅助索引后添加主键列的索引也被认为是冗余的，因为这种情况下，辅助索引末尾本身就包含有主键信息。

首先看我的这张表的索引结构  
mysql> show indexes from curs;

**\[sql\]** [view plain][] [copy][view plain]

1.  \+\-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2.  | Table | Non\_unique | Key\_name | Seq\_in\_index | Column\_name | Collation | Cardinality | Sub\_part | Packed | Null | Index\_type | Comment | Index\_comment |
3.  \+\-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4.  | curs | 0 | PRIMARY | 1 | id | A | 1 | NULL | NULL | | BTREE | | |
5.  | curs | 1 | name | 1 | name | A | 1 | NULL | NULL | | BTREE | | |
6.  | curs | 1 | age | 1 | age | A | 1 | NULL | NULL | | BTREE | | |
7.  | curs | 1 | name\_age | 1 | name | A | 1 | NULL | NULL | | BTREE | | |
8.  | curs | 1 | name\_age | 2 | age | A | 1 | NULL | NULL | | BTREE | | |
9.  | curs | 1 | age\_name | 1 | age | A | 1 | NULL | NULL | | BTREE | | |
10. | curs | 1 | age\_name | 2 | name | A | 1 | NULL | NULL | | BTREE | | |
11. | curs | 1 | name\_1 | 1 | name | A | 1 | NULL | NULL | | BTREE | | |
12. | curs | 1 | age\_1 | 1 | age | A | 1 | NULL | NULL | | BTREE | | |
13. \+\-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

里面有大量的重复索引，比如name与name\_age重复，也与name\_1重复，这3个索引都是相同的索引。下面看下怎么使用/usr/local/bin/pt-duplicate-key-checker来检查重复的索引。  
  
\[root@localhost ~\]\# /usr/local/bin/pt-duplicate-key-checker --host=192.168.1.106 --user='mysql' --password='123456' --databases=test --tables=curs

**\[sql\]** [view plain][] [copy][view plain]

1.  \# \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#
2.  \# test.curs
3.  \# \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#
4.  
5.  \# name is a left\-prefix of name\_age
6.  \# Key definitions:
7.  \# KEY \`name\` (\`name\`),
8.  \# KEY \`name\_age\` (\`name\`,\`age\`),
9.  \# Column types:
10. \# \`name\` varchar(100) not null default ''
11. \# \`age\` smallint(3) unsigned not null default '0'
12. \# To remove this duplicate index, execute:
13. ALTER TABLE \`test\`.\`curs\` DROP INDEX \`name\`;
14. 
15. \# name\_1 is a left\-prefix of name\_age
16. \# Key definitions:
17. \# KEY \`name\_1\` (\`name\`),
18. \# KEY \`name\_age\` (\`name\`,\`age\`),
19. \# Column types:
20. \# \`name\` varchar(100) not null default ''
21. \# \`age\` smallint(3) unsigned not null default '0'
22. \# To remove this duplicate index, execute:
23. ALTER TABLE \`test\`.\`curs\` DROP INDEX \`name\_1\`;
24. 
25. \# age is a left\-prefix of age\_name
26. \# Key definitions:
27. \# KEY \`age\` (\`age\`),
28. \# KEY \`age\_name\` (\`age\`,\`name\`),
29. \# Column types:
30. \# \`age\` smallint(3) unsigned not null default '0'
31. \# \`name\` varchar(100) not null default ''
32. \# To remove this duplicate index, execute:
33. ALTER TABLE \`test\`.\`curs\` DROP INDEX \`age\`;
34. 
35. \# age\_1 is a left\-prefix of age\_name
36. \# Key definitions:
37. \# KEY \`age\_1\` (\`age\`)
38. \# KEY \`age\_name\` (\`age\`,\`name\`),
39. \# Column types:
40. \# \`age\` smallint(3) unsigned not null default '0'
41. \# \`name\` varchar(100) not null default ''
42. \# To remove this duplicate index, execute:
43. ALTER TABLE \`test\`.\`curs\` DROP INDEX \`age\_1\`;
44. 
45. \# \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#
46. \# Summary of indexes
47. \# \#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#
48. 
49. \# Size Duplicate Indexes 608
50. \# Total Duplicate Indexes 4
51. \# Total Indexes 7

[view plain]: https://blog.csdn.net/z1547840014/article/details/10198237#