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

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 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
相关文章
|
16天前
|
SQL 关系型数据库 MySQL
深入解析MySQL的EXPLAIN:指标详解与索引优化
MySQL 中的 `EXPLAIN` 语句用于分析和优化 SQL 查询,帮助你了解查询优化器的执行计划。本文详细介绍了 `EXPLAIN` 输出的各项指标,如 `id`、`select_type`、`table`、`type`、`key` 等,并提供了如何利用这些指标优化索引结构和 SQL 语句的具体方法。通过实战案例,展示了如何通过创建合适索引和调整查询语句来提升查询性能。
118 9
|
9天前
|
存储 缓存 关系型数据库
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
MySQL的存储引擎是其核心组件之一,负责数据的存储、索引和检索。不同的存储引擎具有不同的功能和特性,可以根据业务需求 选择合适的引擎。本文详细介绍了MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案。
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
|
1天前
|
存储 关系型数据库 MySQL
MySQL中为什么要使用索引合并(Index Merge)?
通过这些内容的详细介绍和实际案例分析,希望能帮助您深入理解索引合并及其在MySQL中的
17 10
|
14天前
|
存储 Oracle 关系型数据库
索引在手,查询无忧:MySQL索引简介
MySQL 是一款广泛使用的关系型数据库管理系统,在2024年5月的DB-Engines排名中得分1084,仅次于Oracle。本文介绍MySQL索引的工作原理和类型,包括B+Tree、Hash、Full-text索引,以及主键、唯一、普通索引等,帮助开发者优化查询性能。索引类似于图书馆的分类系统,能快速定位数据行,极大提高检索效率。
48 8
|
14天前
|
存储 关系型数据库 MySQL
MySQL存储引擎详述:InnoDB为何胜出?
MySQL 是最流行的开源关系型数据库之一,其存储引擎设计是其高效灵活的关键。InnoDB 作为默认存储引擎,支持事务、行级锁和外键约束,适用于高并发读写和数据完整性要求高的场景;而 MyISAM 不支持事务,适合读密集且对事务要求不高的应用。根据不同需求选择合适的存储引擎至关重要,官方推荐大多数场景使用 InnoDB。
60 7
|
8天前
|
存储 关系型数据库 MySQL
【MYSQL】 ——索引(B树B+树)、设计栈
索引的特点,使用场景,操作,底层结构,B树B+树,MYSQL设计栈
|
23天前
|
存储 关系型数据库 MySQL
Mysql索引:深入理解InnoDb聚集索引与MyisAm非聚集索引
通过本文的介绍,希望您能深入理解InnoDB聚集索引与MyISAM非聚集索引的概念、结构和应用场景,从而在实际工作中灵活运用这些知识,优化数据库性能。
99 7
|
2月前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
本文介绍了MySQL InnoDB存储引擎中的数据文件和重做日志文件。数据文件包括`.ibd`和`ibdata`文件,用于存放InnoDB数据和索引。重做日志文件(redo log)确保数据的可靠性和事务的持久性,其大小和路径可由相关参数配置。文章还提供了视频讲解和示例代码。
158 11
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
|
29天前
|
存储 关系型数据库 MySQL
MySQL引擎InnoDB和MyISAM的区别?
InnoDB是MySQL默认的事务型存储引擎,支持事务、行级锁、MVCC、在线热备份等特性,主索引为聚簇索引,适用于高并发、高可靠性的场景。MyISAM设计简单,支持压缩表、空间索引,但不支持事务和行级锁,适合读多写少、不要求事务的场景。
57 9
|
2月前
|
存储 Oracle 关系型数据库
【赵渝强老师】MySQL InnoDB的表空间
InnoDB是MySQL默认的存储引擎,主要由存储结构、内存结构和线程结构组成。其存储结构分为逻辑和物理两部分,逻辑存储结构包括表空间、段、区和页。表空间是InnoDB逻辑结构的最高层,所有数据都存放在其中。默认情况下,InnoDB有一个共享表空间ibdata1,用于存放撤销信息、系统事务信息等。启用参数`innodb_file_per_table`后,每张表的数据可以单独存放在一个表空间内,但撤销信息等仍存放在共享表空间中。