【学习笔记】mysql索引原理之InnoDB

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
简介: InnoDB是事务安全的MySQL存储引擎,在OLTP的应用中,InnoDB应该作为核心应用表的首选存储引擎。 1、线程 后台的线程主要负责:刷新内存池中的数据,保证缓冲池中的内存缓存的是最近的数据;将已修改的数据文件刷新到磁盘文件;保证在数据库发生异常情况下InnoDB能恢复到正常状态。

InnoDB是事务安全的MySQL存储引擎,在OLTP的应用中,InnoDB应该作为核心应用表的首选存储引擎。

1、线程

后台的线程主要负责:刷新内存池中的数据,保证缓冲池中的内存缓存的是最近的数据;将已修改的数据文件刷新到磁盘文件;保证在数据库发生异常情况下InnoDB能恢复到正常状态。

InnoDB存储引擎在一个叫master thread的线程上几乎完成了所有的功能。默认情况下, InnoDB存储引擎的后台线程有7个——4个IO thread、1个master thread、1个lock监控线程、1个错误监控线程。4个IO线程分别是insert buffer thread、log thread、read thread、write thread,线程数可以通过文件配置。

2、数据结构

InnoDB的所有数据的逻辑存储空间成为表空间,表空间由段组成。段主要分为数据段、索引段等,数据段其实就是B+Tree的叶子节点,而索引段就是内节点。

段里面又分为区,每个区由64个连续的页组成,每页16K,即每个区1M。对于大的数据段,最多可申请4个区,以保证数据的连续性。
a7554600baa1cd11d998626eb912c8fcc2ce2ddf

页的数据结构如图所示,File Header、Page Header、File Trailer的大小是固定的,用来标示该页的一些信息,如Checksum、数据所在的索引层、该页的前一个页和后一个页的地址等。

Page Directory,页字典,存放的是记录在页中的相对地址,这是个稀疏目录,即并不是每条记录都会在目录中。因为即使在同一页中,记录的存放也是顺序存放的,比如想找到"H",即使"H"不在目录中,那么只需找到"G",再按"G"记录的指针顺序往下查找即可,所以无需每条记录都放入目录中。

因为页是最小存储单位,IO操作都是按页来的,所以聚簇索引的B+Tree叶子节点存放的其实并非记录本身,而是记录所在的页,然后把页读到内存中,再根据Page Directory二叉查找到记录,这个二叉查找是在内存中进行的,所以时间几乎可以忽略。

3、索引操作
对索引的插入、修改、删除操作,自然就得提到innodb的缓冲池。主键的索引是聚簇索引,所以聚簇索引的插入都是顺序读取,而非随机读取的,这样的操作无需使用缓冲池。但非聚簇索引的插入,由于B+树的特性,就需要离散的访问非聚集索引页,而且很大可能会涉及到页分裂等操作,插入的性能相对较低。缓冲池就是为了解决这类问题,非聚簇索引的插入,并非每一次都直接插入到索引页中,会先放入缓冲池,然后以一定的频率进行合并操作,再插入到索引页。

索引的添加或删除操作,会先创建一个新的临时表,把数据导入到临时表,在临时表上创建完或删除完索引,再把原表删除,最后重命名临时表。因此添加或删除索引,对大表而言,代价是比较巨大的,更有可能,会出现表丢失等现象。针对非聚簇索引,InnoDB支持一种快捷方式,对表加锁,这样就不需要临时表。

4、索引查询优化

InnoDB会监控对表索引的查找,建立hash索引,即自适应索引,这个索引并非对全表建索引,而是自动根据访问频率和模式来对某些页建立索引,不能人为干预的,用于等于查询,以提高查询效率。

多个索引的聚合查询,MYSQL会有一个统计机制来记录全表数据记录条数与各个索引的索引条数的比值,比如:全表记录10000条,city索引条数为1000条,那么其比值为10000/1000=10,status索引条数为10条,其比值为10000/10=1000,那么如果where city=1 and status=2,MYSQL会使用city索引,因为city索引的比值比status小很多,查出结果集自然会小很多。但如果两个索引的比值相差不大,MYSQL会把两个索引查出的结果集进行聚合。不过通常的查询都只会使用一个索引,毕竟两个索引查出的结果集进行聚合的代价也不小。

非聚簇索引的查询,最后一步通常是根据主键再到聚簇索引上查一次拿到记录,如果非聚簇索引查出1000条数据,就需要做1000次聚簇索引的查找。当然,MYSQL在这一步也进行了一些优化,比如先对主键排一下序再进行聚簇索引的查询,减少I/O。但如果只需要非聚簇索引上的数据,就无需再走这一步了。

涉及排序时,也是一样的,如果排序的字段与where条件的字段一起建立联合索引,那么查询速度会快很多。比如,where status=1 order by city,联合索引为status,city,索引本来就是有序的,拿到status=1条件过滤之后的结果,再到聚簇索引上查找一下就可以拿到最终结果了。但,如果联合索引是city,status,就不能用了,查询就慢啦。这就是什么左缀原则。

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
23天前
|
存储 关系型数据库 MySQL
阿里面试:MySQL 一个表最多 加几个索引? 6个?64个?还是多少?
阿里面试:MySQL 一个表最多 加几个索引? 6个?64个?还是多少?
阿里面试:MySQL 一个表最多 加几个索引? 6个?64个?还是多少?
|
2月前
|
存储 网络协议 关系型数据库
MySQL8.4创建keyring给InnoDB表进行静态数据加密
MySQL8.4创建keyring给InnoDB表进行静态数据加密
81 1
|
3月前
|
关系型数据库 MySQL 数据库
Mysql的索引
MYSQL索引主要有 : 单列索引 , 组合索引和空间索引 , 用的比较多的就是单列索引和组合索引 , 空间索引我这边没有用到过 单列索引 : 在MYSQL数据库表的某一列上面创建的索引叫单列索引 , 单列索引又分为 ● 普通索引:MySQL中基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和空值,纯粹为了查询数据更快一点。 ● 唯一索引:索引列中的值必须是唯一的,但是允许为空值 ● 主键索引:是一种特殊的唯一索引,不允许有空值 ● 全文索引: 只有在MyISAM引擎、InnoDB(5.6以后)上才能使⽤用,而且只能在CHAR,VARCHAR,TEXT类型字段上使⽤用全⽂文索引。
|
19天前
|
机器学习/深度学习 关系型数据库 MySQL
对比MySQL全文索引与常规索引的互异性
现在,你或许明白了这两种索引的差异,但任何技术决策都不应仅仅基于理论之上。你可以创建你的数据库实验环境,尝试不同类型的索引,看看它们如何影响性能,感受它们真实的力量。只有这样,你才能熟悉它们,掌握什么时候使用全文索引,什么时候使用常规索引,以适应复杂多变的业务需求。
56 12
|
2月前
|
SQL 存储 关系型数据库
MySQL选错索引了怎么办?
本文探讨了MySQL中因索引选择不当导致查询性能下降的问题。通过创建包含10万行数据的表并插入数据,分析了一条简单SQL语句在不同场景下的执行情况。实验表明,当数据频繁更新时,MySQL可能因统计信息不准确而选错索引,导致全表扫描。文章深入解析了优化器判断扫描行数的机制,指出基数统计误差是主要原因,并提供了通过`analyze table`重新统计索引信息的解决方法。
|
2月前
|
SQL 缓存 关系型数据库
使用温InnoDB缓冲池启动MySQL测试
使用温InnoDB缓冲池启动MySQL测试
52 0
|
3月前
|
自然语言处理 关系型数据库 MySQL
MySQL索引有哪些类型?
● 普通索引:最基本的索引,没有任何限制。 ● 唯一索引:索引列的值必须唯一,但可以有空值。可以创建组合索引,则列值的组合必须唯一。 ● 主键索引:是特殊的唯一索引,不可以有空值,且表中只存在一个该值。 ● 组合索引:多列值组成一个索引,用于组合搜索,效率高于索引合并。 ● 全文索引:对文本的内容进行分词,进行搜索。
|
4月前
|
存储 SQL 关系型数据库
MySQL底层概述—2.InnoDB磁盘结构
InnoDB磁盘结构主要包括表空间(Tablespaces)、数据字典(Data Dictionary)、双写缓冲区(Double Write Buffer)、重做日志(redo log)和撤销日志(undo log)。其中,表空间分为系统、独立、通用、Undo及临时表空间,分别用于存储不同类型的数据。数据字典从MySQL 8.0起不再依赖.frm文件,转而使用InnoDB引擎存储,支持事务原子性DDL操作。
349 100
MySQL底层概述—2.InnoDB磁盘结构
|
4月前
|
缓存 算法 关系型数据库
MySQL底层概述—1.InnoDB内存结构
本文介绍了InnoDB引擎的关键组件和机制,包括引擎架构、Buffer Pool、Page管理机制、Change Buffer、Log Buffer及Adaptive Hash Index。
361 97
MySQL底层概述—1.InnoDB内存结构
|
4月前
|
SQL 关系型数据库 MySQL
MySQL底层概述—10.InnoDB锁机制
本文介绍了:锁概述、锁分类、全局锁实战、表级锁(偏读)实战、行级锁升级表级锁实战、间隙锁实战、临键锁实战、幻读演示和解决、行级锁(偏写)优化建议、乐观锁实战、行锁原理分析、死锁与解决方案
238 24
MySQL底层概述—10.InnoDB锁机制

推荐镜像

更多
下一篇
oss创建bucket