Mysql内部在索引层面的优化

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: Mysql内部在索引层面的优化

MRR

 

     mysql每次从二级索引中读取到一条记录后,就会根据该记录的主键值 执行回表操作。而在某个扫描区间中的二级索引记录的主键值是无序的,也就是说这些 二级索引记录对应的聚簇索引记录所在的页面的页号是无序的。 每次执行回表操作时都相当于要随机读取一个聚簇索引页面,而这些随机IO带来的 性能开销比较大。MySQL中提出了一个名为Disk-Sweep Multi-Range Read(MRR,多范围 读取)的优化措施,即先读取一部分二级索引记录,将它们的主键值排好序之后再统一执 行回表操作。 相对于每读取一条二级索引记录就立即执行回表操作,这样会节省一些IO开销。使用这 个 MRR优化措施的条件比较苛刻,所以我们直接认为每读取一条二级索引记录就立即执行回表操作。MRR的详细信息,可以查询官方文档。

自适应哈希索引

InnoDB存储引擎除了我们常说的那些索引,还有一种自适应哈希索引,我们知 道B+树的查找次数,取决于B+树的高度,在生产环境中,B+树的高度一般为3~4层,故 需要3~4次的IO查询。 所以在InnoDB存储引擎内部自己去监控索引表,如果监控到某个索引经常用,那么 就认为是热数据,然后内部自己创建一个hash索引,称之为自适应哈希索引( Adaptive Hash Index,AHI),创建以后,如果下次又查询到这个索引,那么直接通 过hash算法推导出记录的地址,直接一次就能查到数据,比重复去B+tree索引中查 询三四次节点的效率高了不少。 InnoDB存储引擎使用的哈希函数采用除法散列方式,其冲突机制采用链表方式。注意,对于自适应哈希索引仅是数据库自身创建并使用的,我们并不能对其进行干 预。通过命令show engine innodb status\G 可以看到当前自适应哈希索引的使用 状况,如:

哈希索引只能用来搜索等值的查询,如 SELECT* FROM table WHERE index co=xxx。而对于其他查找类型,如范围查找,是不能使用哈希索引的, 因此这里会显示non- hash searches/s的统计情况。通过 hash searches: non- hash searches可以大概了解使用哈希索引后的效率。 由于AHI是由 InnoDB存储引擎控制的,因此这里的信息只供我们参考。不过我们可以通过观察 SHOW ENGINE INNODB STATUS的结果及参数 innodb_adaptive_hash_index来考虑是禁用或启动此特性,默认AHI为开启状态。

什么时候需要禁用呢?如果发现监视索引查找和维护哈希索引结构的额外开销远远

超过了自适应哈希索引带来的性能提升就需要关闭这个功能。

同时在MySQL 5.7中,自适应哈希索引搜索系统被分区。每个索引都绑定到一个特定的分区,每个分区都由一个单独的 latch 锁保护。分区由 innodb_adaptive_hash_index_parts 配置选项控制 。在早期版本中,自适应哈希 索引搜索系统受到单个 latch 锁的保护,这可能成为繁重工作负载下的争用点。 innodb_adaptive_hash_index_parts 默认情况下,该 选项设置为8。最大设置为512。当然禁用或启动此特性和调整分区个数这个应该是DBA的工作,我们了解即可。

全文检索之倒排索引

    mysql5.6之前只有MyISAM支持全文索引,5.6及以后的版本Innodb也支持全文索引。只有字段的数据类型为 char、varchar、text 及其系列才可以建 全文索引,并且每张表只能有一个全文检索的索引, 不支持没有单词界定符( delimiter)的语言,如中文、日语、韩语等

索引合并

   因为二级索引的值相等时索引最底下的ID才是有序的,只有id是有序的两个集合去取交集,计算成本才比较小。所有索引合并的必要条件就是二级索引的值相同的前提下进行。

交集合并(Intersection):

  两个必要条件必须满足一个:

     1. 二级索引等值查询。联合索引必须每个列都等值匹配到。

     2. 主键的范围查询与二级索引的等值查询同时出现。

 Union合并

出现or的索引查询三个必要条件:

     1. 二级索引等值查询。联合索引必须每个列都等值匹配到。

     2. 主键的范围查询与二级索引的等值查询同时出现。

     3. 出现交集索引的结果合并搜索。

Sort-Union合并

  前两种的使用条件都比较荷刻,如果二级索引不是等值查询而是范围查询,那么有可能使用有序并集索引合并。它先根据二级索引的范围查询到各自的id集合,然后将id排好序后再取并集。接下来的操作和合并索引合并一致。它比并集索引合并多一步id排序操作,所以效率相对较差些。

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
11天前
|
SQL 监控 关系型数据库
MySQL优化: CPU高 处理脚本 pt-kill脚本
MySQL优化: CPU高 处理脚本 pt-kill脚本
|
11天前
|
缓存 关系型数据库 MySQL
在Linux中,如何优化MySQL性能,包括索引优化和查询分析?
在Linux中,如何优化MySQL性能,包括索引优化和查询分析?
|
11天前
|
SQL 关系型数据库 MySQL
MySQL索引你用对了吗?
本文从遇到的问题出发,分析了tddl优化器、MySQL索引、分表拆分键的选择相关知识。
|
11天前
|
关系型数据库 MySQL
MySQl优化:使用 jemalloc 分配内存
MySQl优化:使用 jemalloc 分配内存
|
12天前
|
存储 关系型数据库 MySQL
MySQL bit类型增加索引后查询结果不正确案例浅析
【8月更文挑战第17天】在MySQL中,`BIT`类型字段在添加索引后可能出现查询结果异常。表现为查询结果与预期不符,如返回错误记录或遗漏部分数据。原因包括索引使用不当、数据存储及比较问题,以及索引创建时未充分考虑`BIT`特性。解决方法涉及正确运用索引、理解`BIT`的存储和比较机制,以及合理创建索引以覆盖各种查询条件。通过`EXPLAIN`分析执行计划可帮助诊断和优化查询。
|
15天前
|
SQL 存储 关系型数据库
mysql加索引真的会锁表吗?揭秘背后的技术细节与规避策略
【8月更文挑战第16天】在数据库管理中,添加索引能大幅提升查询效率。MySQL执行此操作时的锁定行为常引起关注。文章详细解析MySQL中索引添加时的锁定机制及其原理。不同存储引擎及SQL语句影响锁定策略:MyISAM需全表锁定;InnoDB提供更灵活选项,如使用`ALTER TABLE... LOCK=NONE`可在加索引时允许读写访问,尽管可能延长索引构建时间。自MySQL 5.6起,在线DDL技术可进一步减少锁定时间,通过`ALGORITHM=INPLACE`和`LOCK=NONE`实现近乎无锁的表结构变更。合理配置这些选项有助于最小化对业务的影响并保持数据库高效运行。
30 4
|
15天前
|
SQL JavaScript 关系型数据库
Mysql索引不当引发死锁问题
本文通过真实案例解析了MySQL在高并发环境下出现死锁的问题。数据库表`t_award`包含多个索引,但在执行特定SQL语句时遭遇索引失效,导致更新操作变慢并引发死锁。分析发现,联合索引`(pool_id, identifier, status, is_redeemed)`因`identifier`允许为空值而导致索引部分失效。此外,`pool_id`上的普通索引产生的间隙锁在高并发下加剧了死锁风险。为解决此问题,文中提出了调整索引顺序至`(pool_id, status, is_redeemed, identifier)`等方案来优化索引使用,进而减轻死锁现象。
|
17天前
|
缓存 NoSQL Redis
一天五道Java面试题----第九天(简述MySQL中索引类型对数据库的性能的影响--------->缓存雪崩、缓存穿透、缓存击穿)
这篇文章是关于Java面试中可能会遇到的五个问题,包括MySQL索引类型及其对数据库性能的影响、Redis的RDB和AOF持久化机制、Redis的过期键删除策略、Redis的单线程模型为何高效,以及缓存雪崩、缓存穿透和缓存击穿的概念及其解决方案。
|
20天前
|
存储 关系型数据库 MySQL
MySQL 上亿大表,如何深度优化?
【8月更文挑战第11天】随着大数据时代的到来,MySQL 作为广泛使用的关系型数据库管理系统,经常需要处理上亿级别的数据。当数据量如此庞大时,如何确保数据库的查询效率、稳定性和可扩展性,成为了一个亟待解决的问题。本文将围绕 MySQL 上亿大表的深度优化,分享一系列实用的技术干货,帮助你在工作和学习中应对挑战。
34 1
|
1天前
|
SQL 关系型数据库 MySQL
深入探索MySQL索引策略
本文旨在深入探讨MySQL(8.0.26)数据库中索引的设计与优化方法。

热门文章

最新文章

下一篇
云函数