【学习笔记】mysql索引原理之InnoDB-阿里云开发者社区

开发者社区> 般若灵芝> 正文

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

简介: 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,就不能用了,查询就慢啦。这就是什么左缀原则。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
重新学习Mysql数据库1:无废话MySQL入门
开始使用 我下面所有的SQL语句是基于MySQL 5.6+运行。 MySQL 为关系型数据库(Relational Database Management System),一个关系型数据库由一个或数个表格组成, 如图所.
851 0
《Python编程从0到1》笔记4——你分得清“索引和切片”吗?
Python为序列类型(sequence types) 提供了独特的索引(indexing)和切片(slicing)机制以访问序列的某个元素或某一部分。
479 0
InnoDB索引概述,二分查找法,平衡二叉树
索引是应用程序设计和开发的一个重要方面。如果索引太多,应用的性能可能会受到影响;如果索引太少,对查询性能又会产生影响。要找到一个合适的平衡点,这对应用的性能至关重要。 如果知道数据的使用,从一开始就应该在需要处添加索引。
1059 0
这38个小技巧告诉你如何快速学习MySQL数据库
无论是运维、开发、测试,还是架构师,数据库技术是一个必备加薪神器,那么,一直说学习数据库、学MySQL,到底是要学习它的哪些东西呢?此文为你揭晓,你值得一看!看过别忘记点赞+转发支持哦。
1083 0
重新学习MySQL数据库6:浅谈MySQL的中事务与锁
『浅入深出』MySQL 中事务的实现   在关系型数据库中,事务的重要性不言而喻,只要对数据库稍有了解的人都知道事务具有 ACID 四个基本属性,而我们不知道的可能就是数据库是如何实现这四个属性的;在这篇文章中,我们将对事务的实现进行分析,尝试理解数据库是如何实现事务的,当然我们也会在文章中简单对 MySQL 中对 ACID 的实现进行简单的介绍。
1431 0
重新学习Mysql数据库3:Mysql存储引擎与数据存储原理
转自:https://draveness.me/mysql-innodb 作为一名开发人员,在日常的工作中会难以避免地接触到数据库,无论是基于文件的 sqlite 还是工程上使用非常广泛的 MySQL、PostgreSQL,但是一直以来也没有对数据库有一个非常清晰并且成体系的认知,所以最近两个月的时间看了几本数据库相关的书籍并且阅读了 MySQL 的官方文档,希望对各位了解数据库的、不了解数据库的有所帮助。
2130 0
聊聊Mysql索引和redis跳表
聊聊Mysql索引和redis跳表 摘要 面试时,交流有关mysql索引问题时,发现有些人能够涛涛不绝的说出B+树和B树,平衡二叉树的区别,却说不出B+树和hash索引的区别。这种一看就知道是死记硬背,没有理解索引的本质。
5190 0
+关注
13
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载