目录
前言
我们知道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排序最大的区别
1128
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
