Mysql学习笔记三：索引

weixin_42397604

已于 2022-09-05 17:19:20 修改

阅读量215

点赞数

分类专栏： Mysql 文章标签： mysql 学习数据结构

于 2022-09-05 17:17:44 首次发布

本文链接：https://blog.csdn.net/weixin_42397604/article/details/126399031

版权

Mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

	索引的作用什么？
	索引常见的数据结构有哪些？
	索引是如何实现快速查询数据的呢?

含义

索引的出现是为了提高数据的查询效率，就像字典里用到的偏旁部首或者音节索引查询一样，都是效率的体现。

结构

索引的实现方式有很多种，常见主要分为哈希表，有序数组以及搜索树。

哈希表

哈希表有数组和链表组成，是以键-值（key-value）形式进行存储的，键就是索引，value对应的是表的数据，key经过hash函数的运算得到对应的位置，如果位置上没有值，就把value存储进去，如果有冲突就拉链表。

对于索引结构为哈希表的查询，如果通过索引找到的位置上存在链表，则会从前往后一直查找，直到找到对应的索引数据。对于区间查询效率会差些，比较适用于等值查询（用等号的查询条件，与等值查询，对应的模糊查询，比较查询等等）。

有序数组

有序数组比较适用于静态的数据的查询，在等值查询及范围查询中，效率都比较高，比如户口信息等等

搜索树

二叉搜索时是比较经典的数据结构，对于二叉树，树节点的值是总大于左侧叶子节点的值，右侧叶子节点的值总大于树节点的值。二叉树是查询效率比较高的，查询和修改的时间复杂度为O(log(n))，但实际上很多数据库不会用二叉树作为存储引擎的结构，因为索引不止存在于内存，也存储于硬盘，如果二叉树节点比较高的话，磁盘IO的成本太高了。

一棵 100 万节点的平衡二叉树，树高 20。一次查询可能需要访问 20 个数据块。在机械硬盘时代，从磁盘随机读一个数据块需要 10 ms 左右的寻址时间。也就是说，对于一个 100 万行的表，如果使用二叉树来存储，单独访问一个行可能需要 20 个 10 ms 的时间。

为了检查磁盘的访问，就不能用二叉树，可以用‘N叉’树，N取决于数据块的大小，InnoDB用的就是‘N叉’数，B+Tree.下面就以InnoDB的索引结构进行学习。

InnoDB的索引结构

索引采用的B+树的结构，每一个索引对应一个B+树，索引分为主键索引和非主键索引，也可称为聚簇索引（clustered index）和非聚簇索引，二级索引（secondary index）。不同的索引，叶子节点存储的值会有差别，如果是主键索引树，叶子节点存储的是页（page），也是整行数据的对应的信息；非主键索引树的叶子节点存储的主键的信息。
主键索引树和非主键索引树有什么区别呢，如果是非主键索引查询，先去非主键索引树查询到主键的信息后，再去主键索引数，查询对应的行数据，相当于查询了两次索引数据，回到主键索引树查询的这种情况叫做回表。

索引维护的成本

索引是有序性，这就要求值必须要按照大小规律进行排列，这样的好处是增加的查询的效率，又有哪些影响呢？

当增加数据的时候，如果是索引的值不是递增的，需要对索引的值进行重新排序，如果是递增的，直接往后存储即可，如果存储的值超过上限，需要对页进行拆分，新建一个数据页，将原数据页上的数据迁移过去，叫做页分裂。
当删除数据的时候，也需要对索引的数据结构进行处理，当页的空闲数据比较多的时候，需要对页进行合并，叫做分裂页的逆过程。

索引相关概念

有一个user表，id是主键索引，name+age是组合索引，有数据（1，李三，2），（2，张武，5）

索引覆盖

分析一下语句：select id，name from user where age between 3 and 5;
如果age是非主键索引，它会进行几次回表呢，查询三次非主键索引（查询到5这个数据的时候，会继续往下查一次），回表两次，如果只查询如果修改为select id from user where age between 3 and 5;，不会进行回表，因为非主键索引树存储了主键的信息，所以会直接返回结果为客户端，这种情况叫做索引覆盖。

最左前缀查询

分析下语句：
1.select id，name from user where name = '李三';，
2.select id，name from user where name like '李%';，
3.select id，name from user where age= '3';，
这种情况下索引是否起作用弄了呢？
根据最左前缀原则，1和2都是起作用的，3是不起作用的，组合索引的最左侧的索引单独使用是起作用的，字符串的最左侧字符也是起作用的。

索引下推

分析下语句：
select id，name from user where name like '李%'; and age= '3';，
这种情况下回表了几次呢？
通过非主键索引树找到满足name的节点，并找到主键的信息。
在Mysql5.6之前，找到主键信息后，回去主键索引树找到行数据后，再去对比满足age的信息。
在Mysql5.7后，引入了索引下推优化（index condition pushdown)，找到索引信息后，会对满足索引的数据进行优化，把不满足条件信息过滤掉，也就是找到name的同时过滤age不满足的的情况，减少回表的次数。

如何重建索引

索引经过长期的增加或者删除，导致数据页有空洞，重建索引的过程会重新创建一个索引树，并按顺序把数据赋值过去，提高数据页的利用率，建议用 alter table T engine=InnoDB进行索引的重建

普通索引和唯一索引如何选择呢？

这里提到的问题主要涉及对 chagne buffer 的理解，先说结论，如果是写多读少的情况，建议用普通索引，如果写入之后立刻进行读取的情况，建议用唯一索引。change buffer 的作用，主要是减少随机读取IO磁盘的次数。

具体说明：在不影响数据一致性的前提下，一个更新语句对应两条数据的操作，其中一条数据所在的数据页在内存中，直接在内存中进行数据的更新，一条数据在磁盘中，会将操作记录在change buffer中，并在redo log中记录下这两项操作（内存中的更新，change buffer的操作），该语句操作完成。当有change buffer中数据的读操作时，数据进入内存后，调用change buffer中所有与改数据页相关的操作进行数据的更新，此步骤叫做merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。

change buffer中的记录除了在内存中，也会在磁盘中存储，可以持久化数据。存储在change buffer可以避免在写入操作的情况下，多次磁盘IO的操作，将随机的操作组合成一次性的操作，也可以优化内存的利用率。

唯一索引在写入数据之前，确保数据是否违反唯一性的约束，需要将磁盘数据存入内存后进行判断，这种情况，直接更新内存的数据范围更快，change buffer反而需要进行操作了；普通索引不需要判断唯一性约束，所以比较适合进行change buffer优化，适合写入操作多读取少的情况。