MySQL删除重复的索引-CSDN博客

冗余和重复索引

　　mysql允许在相同列上创建多个索引，无论是有意还是无意，mysql需要单独维护重复的索引，并且优化器在优化查询的时候也需要逐个地进行考虑，这会影响性能。重复索引是指的在相同的列上按照相同的顺序创建的相同类型的索引，应该避免这样创建重复所以，发现以后也应该立即删除。但，在相同的列上创建不同类型的索引来满足不同的查询需求是可以的。

　　冗余索引和重复索引有一些不同，如果创建了索引（a,b），再创建索引（a）就是冗余索引，因为这只是前面一个索引的前缀索引，因此（a,b）也可以当作(a)来使用，但是（b,a）就不是冗余索引，索引(b)也不是，因为b不是索引（a,b）的最左前缀列，另外，其他不同类型的索引在相同列上创建（如哈希索引和全文索引）不会是btree索引的冗余索引。

　　另外：对于二级索引（a,id），id是主键，对于innodb来说，主键列已经包含在二级索引中了，所以这个也是冗余索引。大多数情况下都不需要冗余索引，应该尽量扩展已有的索引而不是创建新索引，但也有时候处于性能方面的考虑需要冗余索引，因为扩展已有的索引会导致其变得太大，从而影响其他使用该索引的查询性能。如：如果在整数列上有一个索引，现在需要额外增加一个很长的varchar列来扩展该索引，那么性可能会急剧下降，特别是有查询把这个索引当作覆盖索引，或者这是myisam表并且有很多范围查询的时候(由于myisam的前缀压缩)

进入正题：如何删除重复索引和冗余索引

一个优秀的运维人员，在做删除操作之前必先备份，so，进行删除索引之前，先来备份一下所有的索引（本例中备份cloudcc库中的所有索引，备份的sql语句存放在/tmp/indexbak.sql中）：

SELECT
CONCAT('ALTER TABLE `',TABLE_NAME,'` ', 'ADD ', 
 IF(NON_UNIQUE = 1,
 CASE UPPER(INDEX_TYPE)
 WHEN 'FULLTEXT' THEN 'FULLTEXT INDEX'
 WHEN 'SPATIAL' THEN 'SPATIAL INDEX'
 ELSE CONCAT('INDEX `',
  INDEX_NAME,
  '` USING ',
  INDEX_TYPE
 )
END,
IF(UPPER(INDEX_NAME) = 'PRIMARY',
 CONCAT('PRIMARY KEY USING ',
 INDEX_TYPE
 ),
CONCAT('UNIQUE INDEX `',
 INDEX_NAME,
 '` USING ',
 INDEX_TYPE
)
)
),'(', GROUP_CONCAT(DISTINCT CONCAT('`', COLUMN_NAME, '`') ORDER BY SEQ_IN_INDEX ASC SEPARATOR ', '), ');') AS 'Show_Add_Indexes'
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'cloudcc'
GROUP BY TABLE_NAME, INDEX_NAME
ORDER BY TABLE_NAME ASC, INDEX_NAME ASC into outfile '/tmp/indexbak.sql';

删除指定库中的所有索引：

SELECT
CONCAT(
'ALTER TABLE `',
TABLE_NAME,
'` ',
GROUP_CONCAT(
DISTINCT
CONCAT(
'DROP ',
IF(UPPER(INDEX_NAME) = 'PRIMARY',
'PRIMARY KEY',
CONCAT('INDEX `', INDEX_NAME, '`')
)
)
SEPARATOR ', '
),
';'
)
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA ='cloudcc'
GROUP BY TABLE_NAME
ORDER BY TABLE_NAME ASC into outfile '/tmp/deletindex.sql';

View Code

进入正题：如何删除重复索引和冗余索引？使用pt-duplicate-key-checker工具

pt-duplicate-key-checker是percona-toolkit的组件，percona-toolkit下载地址：

https://www.percona.com/downloads/percona-toolkit/LATEST/
https://www.percona.com/downloads/percona-toolkit/3.0.5/binary/redhat/6/x86_64/percona-toolkit-3.0.5-1.el6.x86_64.rpm
https://www.percona.com/downloads/percona-toolkit/3.0.5/binary/redhat/7/x86_64/percona-toolkit-3.0.5-1.el7.x86_64.rpm

安装步骤：安装percona-toolkit-3.0.5-1.el6.x86_64.rpm需要安装如下依赖包
yum install -y perl perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-TermReadKey perl-Digest-MD5
rpm -ivh percona-toolkit-3.0.5-1.el7.x86_64.rpm

使用步骤：
[root@localhost tmp]# pt-duplicate-key-checker -uroot -pljzsdut -h192.168.5.154 --databases=cloudcc

[root@localhost tmp]# pt-duplicate-key-checker -uroot -pljzsdut -h192.168.5.154 --databases=cloudcc
# ########################################################################
# cloudcc.tp_c_datatable1                                                 
# ########################################################################

# TP_C_DATATABLE1_NAME_NAM is a duplicate of INDEX_2015001000734661Z3DH
# Key definitions:
#   KEY `TP_C_DATATABLE1_NAME_NAM` (`NAME`(255))
#   KEY `INDEX_2015001000734661Z3DH` (`NAME`(255)),
# Column types:
#         `name` varchar(300) collate utf8_bin default null
# To remove this duplicate index, execute:
ALTER TABLE `cloudcc`.`tp_c_datatable1` DROP INDEX `TP_C_DATATABLE1_NAME_NAM`;

# ########################################################################
# cloudcc.tp_c_datatable10                                                
# ########################################################################

# index_ffe2017C34C3CA43QrDe is a duplicate of TCD10_LF1_LOO
# Key definitions:
#   KEY `index_ffe2017C34C3CA43QrDe` (`LOOKUP_FIELD1`),
#   KEY `TCD10_LF1_LOO` (`LOOKUP_FIELD1`)
# Column types:
#         `lookup_field1` varchar(60) collate utf8_bin default null
# To remove this duplicate index, execute:
ALTER TABLE `cloudcc`.`tp_c_datatable10` DROP INDEX `index_ffe2017C34C3CA43QrDe`;

# ########################################################################
# cloudcc.tp_c_datatable18                                                
# ########################################################################

# index_ffe201759F41E7B8tUvZ is a duplicate of TCD18_LF1_LOO
# Key definitions:
#   KEY `index_ffe201759F41E7B8tUvZ` (`LOOKUP_FIELD1`),
#   KEY `TCD18_LF1_LOO` (`LOOKUP_FIELD1`)
# Column types:
#         `lookup_field1` varchar(60) collate utf8_bin default null
# To remove this duplicate index, execute:
ALTER TABLE `cloudcc`.`tp_c_datatable18` DROP INDEX `index_ffe201759F41E7B8tUvZ`;

# ########################################################################
# cloudcc.tp_c_datatable3                                                 
# ########################################################################

# index_201740D6F860897eJAPo is a duplicate of TP_C_DATATABLE3_NAME_NAM
# Key definitions:
#   KEY `index_201740D6F860897eJAPo` (`NAME`(255)),
#   KEY `TP_C_DATATABLE3_NAME_NAM` (`NAME`(255))
# Column types:
#         `name` varchar(300) collate utf8_bin default null
# To remove this duplicate index, execute:
ALTER TABLE `cloudcc`.`tp_c_datatable3` DROP INDEX `index_201740D6F860897eJAPo`;

# ########################################################################
# cloudcc.tp_c_datatable5                                                 
# ########################################################################

# index_20175AE4672E776ok7eO is a duplicate of TP_C_DATATABLE5_NAME_NAM
# Key definitions:
#   KEY `index_20175AE4672E776ok7eO` (`NAME`(255)),
#   KEY `TP_C_DATATABLE5_NAME_NAM` (`NAME`(255))
# Column types:
#         `name` varchar(300) collate utf8_bin default null
# To remove this duplicate index, execute:
ALTER TABLE `cloudcc`.`tp_c_datatable5` DROP INDEX `index_20175AE4672E776ok7eO`;

# ########################################################################
# cloudcc.tp_c_datatable9                                                 
# ########################################################################

# index_201711F595E9CA3zq1EX is a duplicate of TP_C_DATATABLE9_NAME_NAM
# Key definitions:
#   KEY `index_201711F595E9CA3zq1EX` (`NAME`(255)),
#   KEY `TP_C_DATATABLE9_NAME_NAM` (`NAME`(255))
# Column types:
#         `name` varchar(300) collate utf8_bin default null
# To remove this duplicate index, execute:
ALTER TABLE `cloudcc`.`tp_c_datatable9` DROP INDEX `index_201711F595E9CA3zq1EX`;

# ########################################################################
# cloudcc.tp_std_datatable1                                               
# ########################################################################

# idx_name_std1 is a duplicate of TP_STD_DATATABLE1_NAME_NAM
# Key definitions:
#   KEY `idx_name_std1` (`NAME`(255)),
#   KEY `TP_STD_DATATABLE1_NAME_NAM` (`NAME`(255))
# Column types:
#         `name` varchar(300) collate utf8_bin default null
# To remove this duplicate index, execute:
ALTER TABLE `cloudcc`.`tp_std_datatable1` DROP INDEX `idx_name_std1`;

# idx_createdate_std1 is a duplicate of idx_createdate_1
# Key definitions:
#   KEY `idx_createdate_std1` (`CREATEDATE`),
#   KEY `idx_createdate_1` (`CREATEDATE`),
# Column types:
#         `createdate` datetime default null
# To remove this duplicate index, execute:
ALTER TABLE `cloudcc`.`tp_std_datatable1` DROP INDEX `idx_createdate_std1`;

# ########################################################################
# cloudcc.tp_sys_button                                                   
# ########################################################################

# TP_SYS_BUTTON_NAME_NAM is a duplicate of UNIQUE_BUTTON_NAM
# Key definitions:
#   KEY `TP_SYS_BUTTON_NAME_NAM` (`NAME`(255)),
#   KEY `UNIQUE_BUTTON_NAM` (`NAME`(255))
# Column types:
#         `name` varchar(300) collate utf8_bin default null
# To remove this duplicate index, execute:
ALTER TABLE `cloudcc`.`tp_sys_button` DROP INDEX `TP_SYS_BUTTON_NAME_NAM`;

# ########################################################################
# cloudcc.tp_sys_ccpeak                                                   
# ########################################################################

# Uniqueness of CCPEAK_UNIQUE_API ignored because ccpeak_unique is a duplicate constraint
# CCPEAK_UNIQUE_API is a duplicate of ccpeak_unique
# Key definitions:
#   KEY `CCPEAK_UNIQUE_API` (`APINAME`)
#   UNIQUE KEY `ccpeak_unique` (`APINAME`),
# Column types:
#         `apiname` varchar(50) collate utf8_bin default null
# To remove this duplicate index, execute:
ALTER TABLE `cloudcc`.`tp_sys_ccpeak` DROP INDEX `CCPEAK_UNIQUE_API`;

# ########################################################################
# cloudcc.tp_sys_code                                                     
# ########################################################################

# idx_codetype_code is a duplicate of UNIQUE_SYS_CODE_REPEAT_COD
# Key definitions:
#   KEY `idx_codetype_code` (`codetype`),
#   KEY `UNIQUE_SYS_CODE_REPEAT_COD` (`codetype`),
# Column types:
#         `codetype` varchar(36) collate utf8_bin default null
# To remove this duplicate index, execute:
ALTER TABLE `cloudcc`.`tp_sys_code` DROP INDEX `idx_codetype_code`;

# ########################################################################
# cloudcc.tp_sys_fag                                                      
# ########################################################################

# Uniqueness of FAG_UNIQUE_API ignored because fag_unique is a duplicate constraint
# FAG_UNIQUE_API is a duplicate of fag_unique
# Key definitions:
#   KEY `FAG_UNIQUE_API` (`APINAME`)
#   UNIQUE KEY `fag_unique` (`APINAME`),
# Column types:
#         `apiname` varchar(80) collate utf8_bin default null
# To remove this duplicate index, execute:
ALTER TABLE `cloudcc`.`tp_sys_fag` DROP INDEX `FAG_UNIQUE_API`;

# ########################################################################
# cloudcc.tp_sys_multi_lang                                               
# ########################################################################

# TSML_RI_REL is a duplicate of UNIQUE_MULTI_LANG_REL
# Key definitions:
#   KEY `TSML_RI_REL` (`RELATION_ID`)
#   KEY `UNIQUE_MULTI_LANG_REL` (`RELATION_ID`),
# Column types:
#         `relation_id` varchar(60) collate utf8_bin default null
# To remove this duplicate index, execute:
ALTER TABLE `cloudcc`.`tp_sys_multi_lang` DROP INDEX `TSML_RI_REL`;

# ########################################################################
# cloudcc.tp_sys_object                                                   
# ########################################################################

# TSO_P_PRE is a duplicate of OBJECT_PREFIX_INDEX
# Key definitions:
#   KEY `TSO_P_PRE` (`PREFIX`)
#   UNIQUE KEY `OBJECT_PREFIX_INDEX` (`PREFIX`),
# Column types:
#         `prefix` varchar(200) collate utf8_bin default null
# To remove this duplicate index, execute:
ALTER TABLE `cloudcc`.`tp_sys_object` DROP INDEX `TSO_P_PRE`;

# ########################################################################
# cloudcc.tp_sys_profile_infoset                                          
# ########################################################################

# idx_profile_id_infoset is a duplicate of UNIQUE_PROFILE_INFOSET_PRO
# Key definitions:
#   KEY `idx_profile_id_infoset` (`PROFILE_ID`),
#   KEY `UNIQUE_PROFILE_INFOSET_PRO` (`PROFILE_ID`),
# Column types:
#         `profile_id` varchar(60) collate utf8_bin default null
# To remove this duplicate index, execute:
ALTER TABLE `cloudcc`.`tp_sys_profile_infoset` DROP INDEX `idx_profile_id_infoset`;

# idx_relate_id_infoset is a duplicate of UNIQUE_PROFILE_INFOSET_REL
# Key definitions:
#   KEY `idx_relate_id_infoset` (`RELATE_ID`),
#   KEY `UNIQUE_PROFILE_INFOSET_REL` (`RELATE_ID`)
# Column types:
#         `relate_id` varchar(60) collate utf8_bin default null
# To remove this duplicate index, execute:
ALTER TABLE `cloudcc`.`tp_sys_profile_infoset` DROP INDEX `idx_relate_id_infoset`;

# ########################################################################
# cloudcc.tp_sys_recent_items                                             
# ########################################################################

# idx_operator_items is a duplicate of TSRI_O_OPE
# Key definitions:
#   KEY `idx_operator_items` (`OPERATOR`),
#   KEY `TSRI_O_OPE` (`OPERATOR`)
# Column types:
#         `operator` varchar(60) collate utf8_bin default null
# To remove this duplicate index, execute:
ALTER TABLE `cloudcc`.`tp_sys_recent_items` DROP INDEX `idx_operator_items`;

# ########################################################################
# cloudcc.tp_sys_schemetable                                              
# ########################################################################

# TSS_SI_SCH is a duplicate of UNIQUE_SCHEMEFIELD_NAME_SCH
# Key definitions:
#   KEY `TSS_SI_SCH` (`SCHEMETABLE_ID`)
#   KEY `UNIQUE_SCHEMEFIELD_NAME_SCH` (`SCHEMETABLE_ID`),
# Column types:
#         `schemetable_id` varchar(60) collate utf8_bin default null
# To remove this duplicate index, execute:
ALTER TABLE `cloudcc`.`tp_sys_schemetable` DROP INDEX `TSS_SI_SCH`;

# SCHEMETABLEID_INDEX is a duplicate of UNIQUE_SCHEMEFIELD_NAME_SCH
# Key definitions:
#   KEY `SCHEMETABLEID_INDEX` (`SCHEMETABLE_ID`),
#   KEY `UNIQUE_SCHEMEFIELD_NAME_SCH` (`SCHEMETABLE_ID`),
# Column types:
#         `schemetable_id` varchar(60) collate utf8_bin default null
# To remove this duplicate index, execute:
ALTER TABLE `cloudcc`.`tp_sys_schemetable` DROP INDEX `SCHEMETABLEID_INDEX`;

# ########################################################################
# cloudcc.tp_sys_tab                                                      
# ########################################################################

# idx_obj_id_tab is a duplicate of TST_OI_OBJ
# Key definitions:
#   KEY `idx_obj_id_tab` (`OBJ_ID`),
#   KEY `TST_OI_OBJ` (`OBJ_ID`)
# Column types:
#         `obj_id` varchar(60) collate utf8_bin default null
# To remove this duplicate index, execute:
ALTER TABLE `cloudcc`.`tp_sys_tab` DROP INDEX `idx_obj_id_tab`;

# ########################################################################
# cloudcc.tp_sys_trigger                                                  
# ########################################################################

# Uniqueness of CODE_UNIQUE_API ignored because code_unique is a duplicate constraint
# CODE_UNIQUE_API is a duplicate of code_unique
# Key definitions:
#   KEY `CODE_UNIQUE_API` (`APINAME`)
#   UNIQUE KEY `code_unique` (`APINAME`),
# Column types:
#         `apiname` varchar(50) collate utf8_bin default null
# To remove this duplicate index, execute:
ALTER TABLE `cloudcc`.`tp_sys_trigger` DROP INDEX `CODE_UNIQUE_API`;

# ########################################################################
# Summary of indexes                                                      
# ########################################################################

# Size Duplicate Indexes   44726749
# Total Duplicate Indexes  21
# Total Indexes            2640

View Code

如果只是获取sql语句：

[root@localhost tmp]# pt-duplicate-key-checker -uroot -pljzsdut -h192.168.5.154 --databases=cloudcc |grep "ALTER TABLE"
ALTER TABLE `cloudcc`.`tp_c_datatable1` DROP INDEX `TP_C_DATATABLE1_NAME_NAM`;
ALTER TABLE `cloudcc`.`tp_c_datatable10` DROP INDEX `index_ffe2017C34C3CA43QrDe`;
ALTER TABLE `cloudcc`.`tp_c_datatable18` DROP INDEX `index_ffe201759F41E7B8tUvZ`;
ALTER TABLE `cloudcc`.`tp_c_datatable3` DROP INDEX `index_201740D6F860897eJAPo`;
ALTER TABLE `cloudcc`.`tp_c_datatable5` DROP INDEX `index_20175AE4672E776ok7eO`;
ALTER TABLE `cloudcc`.`tp_c_datatable9` DROP INDEX `index_201711F595E9CA3zq1EX`;
ALTER TABLE `cloudcc`.`tp_std_datatable1` DROP INDEX `idx_name_std1`;
ALTER TABLE `cloudcc`.`tp_std_datatable1` DROP INDEX `idx_createdate_std1`;
ALTER TABLE `cloudcc`.`tp_sys_button` DROP INDEX `TP_SYS_BUTTON_NAME_NAM`;
ALTER TABLE `cloudcc`.`tp_sys_ccpeak` DROP INDEX `CCPEAK_UNIQUE_API`;
ALTER TABLE `cloudcc`.`tp_sys_code` DROP INDEX `idx_codetype_code`;
ALTER TABLE `cloudcc`.`tp_sys_fag` DROP INDEX `FAG_UNIQUE_API`;
ALTER TABLE `cloudcc`.`tp_sys_multi_lang` DROP INDEX `TSML_RI_REL`;
ALTER TABLE `cloudcc`.`tp_sys_object` DROP INDEX `TSO_P_PRE`;
ALTER TABLE `cloudcc`.`tp_sys_profile_infoset` DROP INDEX `idx_profile_id_infoset`;
ALTER TABLE `cloudcc`.`tp_sys_profile_infoset` DROP INDEX `idx_relate_id_infoset`;
ALTER TABLE `cloudcc`.`tp_sys_recent_items` DROP INDEX `idx_operator_items`;
ALTER TABLE `cloudcc`.`tp_sys_schemetable` DROP INDEX `TSS_SI_SCH`;
ALTER TABLE `cloudcc`.`tp_sys_schemetable` DROP INDEX `SCHEMETABLEID_INDEX`;
ALTER TABLE `cloudcc`.`tp_sys_tab` DROP INDEX `idx_obj_id_tab`;
ALTER TABLE `cloudcc`.`tp_sys_trigger` DROP INDEX `CODE_UNIQUE_API`;

转载于:https://www.cnblogs.com/lijuzhang/p/7942681.html