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

本文涉及的产品
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: 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,就不能用了,查询就慢啦。这就是什么左缀原则。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
10天前
|
关系型数据库 MySQL 索引
mysql 分析5语句的优化--索引添加删除
mysql 分析5语句的优化--索引添加删除
11 0
|
16天前
|
存储 关系型数据库 MySQL
轻松入门MySQL:优化进销存管理,掌握MySQL索引,提升系统效率(11)
轻松入门MySQL:优化进销存管理,掌握MySQL索引,提升系统效率(11)
|
20天前
|
存储 算法 关系型数据库
深入理解InnoDB索引数据结构和算法
1. **索引定义**:索引是提升查询速度的有序数据结构,帮助数据库系统快速找到数据。 2. **索引类型**:包括普通索引、唯一索引、主键索引、空间索引和全文索引,每种有特定应用场景。 3. **数据结构**:InnoDB使用B+树作为索引结构,确保所有节点按顺序排列,降低查询时的磁盘I/O。 4. **B+树特性**:所有数据都在叶子节点,非叶子节点仅存储索引,提供高效范围查询。 5. **索引优势**:通过减少查找数据所需的磁盘I/O次数,显著提高查询性能。 **总结:**InnoDB索引通过B+树结构,优化了数据访问,使得查询速度快,尤其适合大数据量的场景。
27 0
深入理解InnoDB索引数据结构和算法
|
22天前
|
存储 自然语言处理 关系型数据库
ElasticSearch索引 和MySQL索引那个更高效实用那个更合适
ElasticSearch索引 和MySQL索引那个更高效实用那个更合适
35 0
|
9天前
|
存储 关系型数据库 MySQL
MySQL引擎对决:深入解析MyISAM和InnoDB的区别
MySQL引擎对决:深入解析MyISAM和InnoDB的区别
28 0
|
10天前
|
SQL 缓存 关系型数据库
mysql性能优化-慢查询分析、优化索引和配置
mysql性能优化-慢查询分析、优化索引和配置
76 0
|
16天前
|
缓存 关系型数据库 MySQL
MySQL查询优化:提速查询效率的13大秘籍(合理使用索引合并、优化配置参数、使用分区优化性能、避免不必要的排序和group by操作)(下)
MySQL查询优化:提速查询效率的13大秘籍(合理使用索引合并、优化配置参数、使用分区优化性能、避免不必要的排序和group by操作)(下)
|
16天前
|
缓存 关系型数据库 MySQL
MySQL 查询优化:提速查询效率的13大秘籍(索引设计、查询优化、缓存策略、子查询优化以及定期表分析和优化)(中)
MySQL 查询优化:提速查询效率的13大秘籍(索引设计、查询优化、缓存策略、子查询优化以及定期表分析和优化)(中)
|
7天前
|
存储 关系型数据库 MySQL
【MySQL实战笔记】 04 | 深入浅出索引(上)-02
【4月更文挑战第9天】InnoDB数据库使用B+树作为索引模型,其中主键索引的叶子节点存储完整行数据,非主键索引则存储主键值。主键查询只需搜索一棵树,而非主键查询需两次搜索,因此推荐使用主键查询以提高效率。在插入新值时,B+树需要维护有序性,可能导致数据页分裂影响性能。自增主键在插入时可避免数据挪动和页分裂,且占用存储空间小,通常更为理想。然而,如果场景仅需唯一索引,可直接设为主键以减少查询步骤。
13 1
【MySQL实战笔记】 04 | 深入浅出索引(上)-02
|
9天前
|
关系型数据库 MySQL 数据库
6. 了解过Mysql的索引嘛 ?
了解MySQL的索引类型,包括单列索引(普通、唯一、主键和全文索引)和组合索引。单列索引用于一列,如普通索引允许重复值,唯一索引和主键索引不允许,后者不允许空值。全文索引适用于特定文本字段。组合索引是多列的,遵循左前缀原则,通常推荐用于提高查询效率,除非是主键。
12 0