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

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/zyz511919766/article/details/49451629

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

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

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

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

基本用法以及样例输出如下
[root@VM_8_180_centos packages]# pt-duplicate-key-checker A=utf8, F=/etc/my.cnf, h=localhost, u=root, P=3306 –ask-pass

样例输出:

# ########################################################################
# dcf.privilege                                                           
# ########################################################################

# Uniqueness of UQI_IDX_1 ignored because PRIMARY is a duplicate constraint
# UQI_IDX_1 is a duplicate of PRIMARY
# Key definitions:
#   UNIQUE KEY `UQI_IDX_1` (`privilege_id`),
#   PRIMARY KEY (`privilege_id`),
# Column types:
#     `privilege_id` varchar(50) collate utf8_bin not null comment '权限id'
# To remove this duplicate index, execute:
ALTER TABLE `dcf`.`privilege` DROP INDEX `UQI_IDX_1`;

# ########################################################################
# dcf.t_game_config                                                       
# ########################################################################

# Uniqueness of pkey ignored because PRIMARY is a duplicate constraint
# pkey is a duplicate of PRIMARY
# Key definitions:
#   UNIQUE KEY `pkey` (`pkey`)
#   PRIMARY KEY (`pkey`),
# Column types:
#     `pkey` bigint(20) not null auto_increment
# To remove this duplicate index, execute:
ALTER TABLE `dcf`.`t_game_config` DROP INDEX `pkey`;

# ########################################################################
# dcf.t_project_institution                                               
# ########################################################################

# index_1 is a left-prefix of index_2
# Key definitions:
#   KEY `index_1` (`project_id`),
#   KEY `index_2` (`project_id`,`institution_id`,`delete_flag`)
# Column types:
#     `project_id` bigint(20) not null comment '项目id'
#     `institution_id` varchar(20) not null comment '机构id'
#     `delete_flag` tinyint(4) not null
# To remove this duplicate index, execute:
ALTER TABLE `dcf`.`t_project_institution` DROP INDEX `index_1`;

# ########################################################################
# dcf_commons.bank_cnaps                                                  
# ########################################################################

# idx is a duplicate of PRIMARY
# Key definitions:
#   KEY `idx` (`cnaps`)
#   PRIMARY KEY (`cnaps`),
# Column types:
#     `cnaps` varchar(255) not null comment '电子联行号'
# To remove this duplicate index, execute:
ALTER TABLE `dcf_commons`.`bank_cnaps` DROP INDEX `idx`;

# ########################################################################
# dcf_contract.customer_bank_account                                      
# ########################################################################

# IDX_CUSTOMER_ID is a left-prefix of UQI_IDX_1
# Key definitions:
#   KEY `IDX_CUSTOMER_ID` (`customer_id`)
#   UNIQUE KEY `UQI_IDX_1` (`customer_id`,`account_no`,`branch_bank`,`account_type`,`account_name`) USING BTREE,
# Column types:
#     `customer_id` varchar(20) collate utf8_bin not null comment '客户id'
#     `account_no` varchar(40) collate utf8_bin default null comment '银行账号'
#     `branch_bank` varchar(100) collate utf8_bin default null comment '开户支行'
#     `account_type` tinyint(4) default null comment '账户类型:比如收款账户,还款账户等\n0-收款账户\n1-还款账户'
#     `account_name` varchar(100) collate utf8_bin default null comment '银行账户户名'
# To remove this duplicate index, execute:
ALTER TABLE `dcf_contract`.`customer_bank_account` DROP INDEX `IDX_CUSTOMER_ID`;

# ########################################################################
# dcf_contract.t_contract_account                                         
# ########################################################################

# IDX_CONTRACT_ID is a left-prefix of t_contract_account_uq1
# Key definitions:
#   KEY `IDX_CONTRACT_ID` (`contract_id`)
#   UNIQUE KEY `t_contract_account_uq1` (`contract_id`,`account_type`),
# Column types:
#     `contract_id` bigint(20) not null comment '合同id'
#     `account_type` tinyint(4) not null comment '账户类 型:globalconstant.bankaccounttypec常数 \n0-收款账户\n1-还款账户 等'
# To remove this duplicate index, execute:
ALTER TABLE `dcf_contract`.`t_contract_account` DROP INDEX `IDX_CONTRACT_ID`;

......
......

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

# Size Duplicate Indexes   173317386
# Total Duplicate Indexes  18
# Total Indexes            562

会给出重复/冗余类型、索引/外键定义、索引包含的列类型、移除重复/冗余索引/外键的SQL、最后会给出有关索引的统计信息。

重复索引删除
直接执行工具输出结果中的ALTER TABLE语句即可,但是执行前一定要仔细评估可能造成的影响。比如,表非常非常大的情况下可能造成主从复制延迟,又比如SQL中若包含索引提示的话直接删除索引可能导致报SQL语法错误,最好事先查一下是不是包含此类SQL(可通过general log或者tcpdump工具获取SQL并加以分析)

展开阅读全文

关于mysql索引

01-21

我使用的是mysql 5.5, 使用的engine是innodb, 在创建外键的时候,innodb帮着自动创建了索引. rn在创建外键但不指定其约束名字的情况下,外键名字和索引名字是不一样的;rn在创建外键且指定约束名字的情况下,外键名字和索引名字是一样的。rn我的问题是,创建外键的时候不指定约束名字,通过什么途径能够知道这个外键关联的索引是哪个?rn目前我知道的方法是show create table tableName; 请问有没有其他的方法?非常感谢。rnrn[code=sql]rncreate database test; rnuse test; rncreate table t1(a int, b int, primary key(a,b)); rncreate table t2(a int, b int, foreign key(a,b) references t1(a,b)); rncreate table t3(a int, b int, constraint f_t3 foreign key(a, b) references t1(a,b)); rnshow create table t2; rnshow create table t3; rn[/code]rnrnrnmysql> show create table t2; rn+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+rn| Table | Create Table |rn+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+rn| t2 | CREATE TABLE `t2` (rn `a` int(11) DEFAULT NULL,rn `b` int(11) DEFAULT NULL,rn KEY [color=#FF0000]`a`[/color] (`a`,`b`),rn CONSTRAINT [color=#FF0000]`t2_ibfk_1`[/color] FOREIGN KEY (`a`, `b`) REFERENCES `t1` (`a`, `b`)rn) ENGINE=InnoDB DEFAULT CHARSET=gbk |rn+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+rn1 row in set (0.00 sec)rnrnmysql> show create table t3; rn+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+rn| Table | Create Table |rn+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+rn| t3 | CREATE TABLE `t3` (rn `a` int(11) DEFAULT NULL,rn `b` int(11) DEFAULT NULL,rn KEY [color=#99CC00]`f_t3`[/color] (`a`,`b`),rn CONSTRAINT [color=#99CC00]`f_t3`[/color] FOREIGN KEY (`a`, `b`) REFERENCES `t1` (`a`, `b`)rn) ENGINE=InnoDB DEFAULT CHARSET=gbk |rn+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+rn1 row in set (0.00 sec)rn 论坛

没有更多推荐了,返回首页