您的位置:首页 > 数据库 > > 正文

深入理解mysql索引(MySQL中冗余和重复索引的区别说明)

更多 时间:2021-10-15 00:01:06 类别:数据库 浏览量:172

深入理解mysql索引

MySQL中冗余和重复索引的区别说明

MySQL允许在单个列上创建多个索引,无论是有意还是无意,MySQL需要单独维护这些重复索引,优化器在优化查询时也需要逐个考虑这会影响MySQL的性能

概念阐述

重复索引: 在相同的列上按照相同的顺序创建的相同类型的索引。应该避免创建这样的重复索引,发现之后也应该立即移除。

冗余索引: 两个索引按照相同的顺序覆盖了相同的列。

创建的原因

一般来说,我们有时候会在不经意间创建了重复索引,例如下面的例子:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • CREATE TABLE test(
  •  ID INT NOT NULL PRIMARY KEY,
  •  A INT NOT NULL,
  •  B INT NOT NULL,
  •  UNIQUE(ID),
  •  INDEX(ID)
  • )ENGINE=InnoDB;
  • 因为MySQL的唯一限制和主键限制都是通过索引实现的,所以事实上使用如上代码创建的表实际上会在ID列上创建3个索引。通常没有理由这样做,除非是在同一列上为了满足不同的查询需求创建不同类型的索引。比如KEY(col)和FULLTEXT KEY(col)两种索引。

    冗余索引和重复索引有些不同。如果创建了索引(A,B),再创建索引(A)就是冗余索引,因为这只是前一个索引的前缀索引。但是如果创建了索引(B,A),则不是冗余索引,索引(B)也不是冗余索引,因为它不是索引(A,B)的最左前缀列。除此之外,不同类型的索引也不会是B树索引的冗余索引,无论覆盖的索引列是什么。

    在大多数情况下都不需要使用冗余索引,应该尽可能拓展已有的索引而不是创建新的索引。但有时候出于性能的考虑,比如拓展已有的索引会使得其变得太大,从而影响其他使用该索引的查询的性能。

    影响

    创建冗余索引作为覆盖索引可以提高我们对于部分查询的QPS,但是存在两个索引也有缺点,即索引成本更高。

    当表中的索引越来越多时,表的插入速度会变慢。一般而言,增加新索引将会导致INSERT、UPDATE、DELETE等操作的速度变慢,特别是当新增索引之后导致达到了内存的瓶颈的时候。

    解决冗余索引和重复索引的方法很简单,删除这些索引就可以 ,但是首先要做的就是找出这样的索引。

    补充:MySQL中重复索引和重复外键清理

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

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

    重复索引检测

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

    可以检测到的冗余/重复索引类型:若某个索引和另外某个索引以同样的顺序包含同样的列,或者该索引包含的列是另外某个索引的最左前缀列,则被认为是重复/冗余的索引。

    默认情况下只在同类型的索引间(如BTREE索引)进行比较,不同类型的索引即使符合上述描述也不会被认为是重复/冗余,但这一行为可以通过参数改变。

    除此之外,还可检测重复的外键,即引用的表和列均相同的外键。对于聚簇索引的表,在辅助索引后添加主键列的索引也被认为是冗余的,因为这种情况下,辅助索引末尾本身就包含有主键信息。

    基本用法以及样例输出如下

  • ?
  • 1
  • [root@VM_8_180_centos packages]# pt-duplicate-key-checker A=utf8, F=/etc/my.cnf, h=localhost, u=root, P=3306 –ask-pass
  • 样例输出:

  • ?
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • # ########################################################################
  • # 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并加以分析)

    以上为个人经验,希望能给大家一个参考,也希望大家多多支持开心学习网。如有错误或未考虑完全的地方,望不吝赐教。

    原文链接:https://blog.csdn.net/qq_35499060/article/details/85606681

    标签:mysql 索引 冗余
    您可能感兴趣