Mysql内部在索引层面的优化

本文涉及的产品
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: 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排序操作,所以效率相对较差些。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
14天前
|
关系型数据库 MySQL 索引
mysql 分析5语句的优化--索引添加删除
mysql 分析5语句的优化--索引添加删除
12 0
|
20天前
|
存储 关系型数据库 MySQL
轻松入门MySQL:数据库设计之范式规范,优化企业管理系统效率(21)
轻松入门MySQL:数据库设计之范式规范,优化企业管理系统效率(21)
|
20天前
|
存储 SQL 关系型数据库
轻松入门MySQL:加速进销存!利用MySQL存储过程轻松优化每日销售统计(15)
轻松入门MySQL:加速进销存!利用MySQL存储过程轻松优化每日销售统计(15)
|
20天前
|
存储 关系型数据库 MySQL
轻松入门MySQL:优化进销存管理,掌握MySQL索引,提升系统效率(11)
轻松入门MySQL:优化进销存管理,掌握MySQL索引,提升系统效率(11)
|
22天前
|
存储 SQL 关系型数据库
mysql优化一
mysql优化一
16 0
|
14天前
|
SQL 缓存 关系型数据库
mysql性能优化-慢查询分析、优化索引和配置
mysql性能优化-慢查询分析、优化索引和配置
80 1
|
20天前
|
存储 关系型数据库 MySQL
MySQL数据库性能大揭秘:表设计优化的高效策略(优化数据类型、增加冗余字段、拆分表以及使用非空约束)
MySQL数据库性能大揭秘:表设计优化的高效策略(优化数据类型、增加冗余字段、拆分表以及使用非空约束)
|
20天前
|
缓存 关系型数据库 MySQL
MySQL查询优化:提速查询效率的13大秘籍(合理使用索引合并、优化配置参数、使用分区优化性能、避免不必要的排序和group by操作)(下)
MySQL查询优化:提速查询效率的13大秘籍(合理使用索引合并、优化配置参数、使用分区优化性能、避免不必要的排序和group by操作)(下)
|
20天前
|
缓存 关系型数据库 MySQL
MySQL 查询优化:提速查询效率的13大秘籍(索引设计、查询优化、缓存策略、子查询优化以及定期表分析和优化)(中)
MySQL 查询优化:提速查询效率的13大秘籍(索引设计、查询优化、缓存策略、子查询优化以及定期表分析和优化)(中)
|
17小时前
|
SQL Oracle 关系型数据库
下次老板问你MySQL如何优化时,你可以这样说,老板默默给你加工资
现在进入国企或者事业单位做技术的网友越来越多了,随着去O的力度越来越大,很多国企单位都开始从Oracle向MySQL转移,相对于Oracle而言,MySQL最大的问题就是性能,所以,这个时候,在公司如果能够处理好MySQL的性能瓶颈,那么你也就很容易从人群中脱颖而出,受到老板的青睐。
7 1