iis服务器助手广告广告
返回顶部
首页 > 资讯 > 数据库 >MySQL中冗余和重复索引的区别说明
  • 523
分享到

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

MySQL冗余重复索引 2022-05-23 06:05:59 523人浏览 薄情痞子
摘要

Mysql允许在单个列上创建多个索引,无论是有意还是无意,mysql需要单独维护这些重复索引,优化器在优化查询时也需要逐个考虑这会影响Mysql的性能 概念阐述 重复索引: 在相同的列上按照相同的顺序创建的相同类型的

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

概念阐述

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

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

创建的原因

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


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索引)进行比较,不同类型的索引即使符合上述描述也不会被认为是重复/冗余,但这一行为可以通过参数改变。

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

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


[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并加以分析)

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

您可能感兴趣的文档:

--结束END--

本文标题: MySQL中冗余和重复索引的区别说明

本文链接: https://www.lsjlt.com/news/10257.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

本篇文章演示代码以及资料文档资料下载

下载Word文档到电脑,方便收藏和打印~

下载Word文档
猜你喜欢
  • mysql中怎么实现重复索引与冗余索引
    这篇文章将为大家详细讲解有关mysql中怎么实现重复索引与冗余索引,文章内容质量较高,因此小编分享给大家做个参考,希望大家阅读完这篇文章后对相关知识有一定的了解。重复索引:表示一个列或者顺序相同的几个列上建...
    99+
    2024-04-02
  • Python如何识别MySQL中的冗余索引
    目录前言脚本介绍表结构MySQL 元数据DEMO 演示SQL 查询冗余索引后记前言 最近在搞标准化巡检平台,通过 MySQL 的元数据分析一些潜在的问题。冗余索引也是一个非常重要的巡...
    99+
    2024-04-02
  • MySQL中怎么利用pt-duplicate-key-checker找出冗余、重复索引
    MySQL中怎么利用pt-duplicate-key-checker找出冗余、重复索引,相信很多没有经验的人对此束手无策,为此本文总结了问题出现的原因和解决方法,通过这篇文章希望你能解决这个问题。 ...
    99+
    2024-04-02
  • MySQL查询冗余索引和未使用过的索引操作
    MySQL5.7及以上版本提供直接查询冗余索引、重复索引和未使用过索引的视图,直接查询即可。 查询冗余索引、重复索引 select * sys.from schema_...
    99+
    2024-04-02
  • Mysql中MyISAM和InnoDB的区别及说明
    目录MyISAM和InnoDB的区别1. 定义2. 区别3. 使用MyISAM和InnoDB索引结构分析存储引擎作用于什么对象MyISAM和InnoDB对索引和数据的存储在磁盘上是如何体现的MyISAM主键索引与辅助索引...
    99+
    2022-12-26
    Mysql中MyISAM Mysql中InnoDB MyISAM和InnoDB的区别
  • MySQL中复合索引和覆盖索引的区别详解
    目录前言准备复合索引覆盖索引总结前言准备 我们先准备一张表和几个字段,方便介绍覆盖索引和复合索引。 创建一个user表,表中有id、name、school、age字段。 字段名字段类型idintnamevarcharsc...
    99+
    2023-11-23
    MySQL 复合索引 MySQL 覆盖索引
  • mysql索引合并的说明和使用
    本篇内容介绍了“mysql索引合并的说明和使用”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!什么是索引合并...
    99+
    2024-04-02
  • mysql中insertignore、insert和replace的区别及说明
    目录insert ignore、insert和replace的区别测试代码insertreplaceinsert ignoreinsert ignore、insert和re...
    99+
    2024-04-02
  • Python中列表索引A[:2]与A[:,2]的区别说明
    目录列表索引 A[ : 2 ]与A[ : , 2]区别创建一个列表访问列表中的值A[ : 2 ]与A[ : , 2]的区别python中[::]的含义[:-1][::-1][:,]列...
    99+
    2024-04-02
  • Collection中的size()和isEmpty()区别说明
    目录Collection中的size()和isEmpty()区别说明Collection集合类介绍与实验list.size()和list.isEmpty()区别和效率及Collect...
    99+
    2024-04-02
  • vue中data和data()的区别说明
    目录data和data()的区别Vue实例中data属性组件化的项目中使用详解vue.js中的data文档之一文档之二文档之三文档之四data和data()的区别 Vue实例中dat...
    99+
    2024-04-02
  • Dockerfile中ENTRYPOINT 和 CMD的区别说明
    ENTRYPOINT 和 CMD 都是 Dockerfile 中的指令,用于指定容器启动时要执行的命令。区别如下:- ENTRYPO...
    99+
    2023-08-11
    区别
  • Scala中Array和List的区别说明
    目录Scala Array和List的区别Scala快排List和Array数组效率实测Scala Array和List的区别 Difference between Array an...
    99+
    2024-04-02
  • mysql联合索引和普通索引的区别
            MySQL中,联合索引和普通索引都是用于加速查询的索引类型。它们之间的区别在于索引的列数和列的顺序。         普通索引只对单个列进行索引,而联合索引则同时对多个列进行索引,这些列可以按照特定的顺序组合在一起。例如,可...
    99+
    2023-09-07
    mysql 数据库 java
  • MySQL单列索引和组合索引的区别
    这篇文章主要讲解了“MySQL单列索引和组合索引的区别”,文中的讲解内容简单清晰,易于学习与理解,下面请大家跟着小编的思路慢慢深入,一起来研究和学习“MySQL单列索引和组合索引的区别”吧!  MySQL单...
    99+
    2024-04-02
  • MySQL中B树索引和B+树索引的区别详解
    目录1. 多路搜索树2. B树-多路平衡搜索树3. B树索引4. B+树索引总结如果用树作为索引的数据结构,每查找一次数据就会从磁盘中读取树的一个节点,也就是一页,而二叉树的每个节点...
    99+
    2024-04-02
  • python中isoweekday和weekday的区别及说明
    目录isoweekday和weekday的区别datetime.date类介绍isoweekday和weekday的区别 import datetime datetime.datet...
    99+
    2024-04-02
  • pytorch中.to(device) 和.cuda()的区别说明
    原理 .to(device) 可以指定CPU 或者GPU device = torch.device("cuda:0" if torch.cuda.is_available() ...
    99+
    2024-04-02
  • Vuex中mutations和actions的区别及说明
    目录mutationMutation 必须是同步函数Action在实际开发的store文件中总结mutation 我们知道,在使用vuex对项目状态进行管理时,只能使用commit来...
    99+
    2022-12-09
    Vuex中mutations Vuex中actions mutations和actions区别
  • PyTorch中Tensor和tensor的区别及说明
    目录Tensor和tensor的区别pytorch Tensor变形函数Tensor的排序与取极值Tensor与NumPy转换Tensor和tensor的区别 本文列举的框架源码基于...
    99+
    2024-04-02
软考高级职称资格查询
编程网,编程工程师的家园,是目前国内优秀的开源技术社区之一,形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容,为IT开发者提供了一个发现、使用、并交流开源技术的平台。
  • 官方手机版

  • 微信公众号

  • 商务合作