目录
InnoDB索引
什么是索引?
索引的分类
数据存储结构 B 树和 B+ 树区别
索引的优缺点
各索引结构及优缺点
聚簇索引与非聚簇索引
哈希索引
唯一索引
联合索引
索引下推
使用索引下推对比
索引下推使用条件
索引失效
索引失效情况
索引排序内部流程
排序流程
拓展 row_id 排序
前言
我们知道mysql的有着不同的存储引擎,其中包括:InnoDB、MyISAM、Memory等
本篇文章以 InnoDB索引 为例介绍索引,重在理解原理。希望本篇文章能够帮助你们更进一步理解索引
InnoDB索引
什么是索引?
在关系型数据库中,索引是一种单独的、物理的 对数据库表的一列或者多列的值进行排序 的一种存储结构。
索引的作用相当于图书的目录,能够快速定位到所需的内容。
能实现快速定位数据的一种存储结构,其设计思想是以 空间换时间。
索引的分类
按数据结构分:B+tree索引、Hash索引、Full-text索引
按物理存储分:聚簇索引(主键索引)、二级索引(辅助索引)
按字段特性分:主键索引、唯一索引、普通索引、前缀索引
按字段个数分:单列索引、联合索引(组合索引)
数据存储结构 B 树和 B+ 树区别
B- tree
特点:
- 树是进行排序的
- 一个节点有多个元素,内部也是进行排序的
B+ tree
B+ tree是 B的升级版,有着B树相同的特点
额外特点:
- 叶子节点之间有指针了,在mysql中是一个双向的指针
- 非叶子节点的数据冗余了一份在叶子节点
索引的优缺点
优点
·索引能够提高数据检索的效率,降低数据库的IO成本。
优点
- 索引能够提高数据检索的效率,降低数据库的IO成本。
- 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性
- 在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间
- 加速两个表之间的连接,一般是在外键上创建索引
缺点
- 需要占用物理空间,建立的索引越多需要的空间越大
- 创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加
- 会降低表的增删改的效率,因为每次增删改索引需要进行动态维护,导致时间变长
各索引结构及优缺点
聚簇索引与非聚簇索引
聚簇索引
1.聚簇索引将数据存储在索引树的叶子节点上。
2 . 聚簇索引可以减少一次查询,因为查询索引树的同时就能获取到数据。
3 . 聚簇索引的缺点是,对数据进行修改或删除操作时需要更新索引树,会增加系统的开销。
4.聚簇索引通常用于数据库系统中,主要用于提高查询效率。
非聚簇索引(又称二级索引/辅助索引)
1.非聚簇索引不将数据存储在索引树的叶子节点上,而是存储在数据页中。
2.非聚簇索引在查询数据时需要两次查询,一次查询索引树,获取数据页的地址,再通过数据页的地址查询数据(通常情况下来说是的,但如果 索引覆盖 的话实际上是不用回表的)。
索引覆盖:查询的信息被建立索引的列包括,例 select username from user where username = 'lucy'
3.非聚簇索引的优点是,对数据进行修改或删除操作时不需要更新索引树,减少了系统的开销。
4.非聚簇索引通常用于数据库系统中,主要用于提高数据更新和删除操作的效率。
哈希索引
什么是哈希索引?
哈希索引(hash index)基于哈希表实现。哈希索引通过Hash算法将数据库的索引列数据转换成定长的哈希码作为key,将这条数据的行的地址作为value一并存入Hash表的对应位置。
在MySQL中,只有Memeory引擎显式的支持哈希索引,这也是Memory引擎表的默认索引结构,Memeory同时也支持B-Tree索引。并且,Memory引擎支持非唯一哈希索引,如果多个列的哈希值相同(或者发生了Hash碰撞),索引会在对应Hash键下以链表形式存储多个记录地址。
InnoDB到底支不支持哈希索引?
对于InnoDB的哈希索引,确切的应该这么说:
- InnoDB用户无法手动创建哈希索引,这一层上说,InnoDB确实不支持哈希索引
- InnoDB会自调优(self-tuning),如果判定建立自适应哈希索引(Adaptive Hash index,AHI),能够提升查询效率,InnoDB自己会建立相关哈希索引,这一层上说,InnoDB又是支持哈希索引的
唯一索引
其实和"普通索引"类似,不同的就是:索引列的值必须唯一,但允许有空值。可以是单列唯一索引,也可以是联合唯一索引。
最大的所用就是确保写入数据库的数据是唯一值。(业务:身份证案例)
- 普通索引 查到满足条件的第一条记录,继续查找下一条记录,直到找到不满足条件的记录
- 唯一索引 查到第一个满足条件的记录,就停止搜索。
InnoDB 它是以 数据页 为单位进行读写的,我们读一条记录,并不是从磁盘加载一条记录,而是以页为单位整体读到内存里面来的。
普通索引比唯一索引就多了一次 查找和判断下一条 记录的操作,也就是一次指针寻找数据和一次计算。当然还有一种特殊情况,读取到的这条数据正好是数据页的最后一条。但是这种概率也是非常低,几乎可以忽略不计。
来看第二个更新的性能,我们按照上面图上的例子在 2 和 6 之间插入一个3
在内存中
- 普通索引 找到2和6之间的位置 >插入值 >结束.
- 唯一索引 找到2和6之间的位置> 当判断有没有冲突 插入值 结束
不在内存中
- 普通索引 将更新记录在 change buffer > 结束.
- 唯一索引 将数据页读入内存> 当判断到没有冲突 >插入值 >结束
数据读取到内存涉及了随机0 访问,这是在数据库里面成本最高的操作之一,而 change buffer 就可以减少这种随机磁盘访问,所以性能提示比较明显。所以在这一块来说,如果两者在业务场景下都能满足时可以优先考虑使用普通索引,如果有唯一性要求就选择唯一索引
联合索引
一个索引包含多个列
- 单列索引 一个索引只包含了一个列,一个表里面可以有多个单列索引,但是这不叫组合索引。
- 组合索引 (联合索引& 复合索引)一个索引包含多个列。看上去感觉这组合索引并没有太大作用是吧,我一个列已经有一个索引了,我还要这组合索引干嘛?真相往往不那么简单,首先我们得承认我们的业务千变万化,我们的查询语句条件肯定是非常多的。高效率 如果说只有单列索引,那就会涉及多次二级索引树查找,再加上回表,性能相对于联合索引来说是比较低的。
- 减少开销 我们要记得创建索引是存在空间开销的,对于大数据量的表,使用联合索引会降低空间开销。
- 索引覆盖 如果组合索引索引值已经满足了我们的查询条件,那么就不会进行回表,直接返回。
但是我们按照我们的查询条件去创建一个联合索引的话,就避免了上面的问题。那么联合索引是怎么工作的呢?这里涉及到了一个重点,叫做 最左前缀 ,简单理解就是只会从最左边开始组合,组合索引的第一个字段必须出现在查询组句中,还不能跳跃,只有这样才能让索引生效,比如说我查询条件里面有组合索引里面的第二个字段,那么也是不会走组合索引的。举个例子
必须是先查username(select条件里有)因为创建索引时username是最左的
特殊情况:
select * from user age = 18 and username = '张三' 也是可以执行的。无论username是否写在前面,都会先查username满足条件的
索引下推
索引下推(ICP)目标是减少全行记录读取,从而减少IO操作,只能用于非聚簇索引。聚簇索引本身包含的表的数据,也就不存在下推一说
使用索引下推对比
使用前
假设name和age建立了联合索引
1、通过 %张 在二级索引中查找到性张的用户
2,3、将找到的数据进行回表,拿到行数据
4、将数据回表到服务层
5、根据条件过滤数据
使用后
把其它的查询条件下推到引擎层
1、通过 %张 在二级索引中查找到性张的用户,在引擎层就过滤掉不符合条件的行
2、进行回表查行数据
3、回表到服务层,减少了条件过滤
对比优点
- 减少回表次数
- 在服务层减少条件过滤
- 减少全行记录读取,从而减少IO操作
索引下推使用条件
索引失效
索引失效情况
特殊情况
- 左边带%,如果查询的字段是索引列,也可以正常走索引
- order by 情况
sql查询分为三步,1、根据条件查询数据
2、查看执行计划是否使用索引
3、如果使用了直接引用索引的排序,如果没有使用则在得到的数据进行排序
所以order by 排序是否走索引不取决于order by之后的字段,限于查询数据时使用的执行计划,执行计划将根据 成本 来决定此查询是否走索引等信息
索引排序内部流程
排序流程
前置知识:mysql会为每一个线程分配一个 sort buffer,用作当前操作用与排序的内存空间。可以通过 sort_buffer_size 来控制大小,默认为256kb。
- 首先会初始化一个sort buffer,将需要查询的字段放到sort buffer里
- 根据第一个字段找到主键值
- 进行查询将数据放到sort buffer里
拓展 row_id 排序
首先需要了解一个参数 *max_length_for_sort_data * 用户进行排序的默认行数。如果需要排序的字段超过了这个数量,将自动升级为 row_id 排序,默认为4096。
row_id 排序的思想为 将不需要的字段不放入sort buffer ,而只放入需要排序的字段来节省空间。但因为row_id并没有放入所有字段,所以row_id有一个回表的过程,这是row_id排序最大的区别