从一条查询sql执行过程了解mysql索引

mysql索引

sql执行过程

首先我们看一下一条查询sql在mysql中的大致流程

首先简单阐述一下前置处理的流程

• 开始 客户端向mysql服务端发送请求；
• 连接器 请求到达连接器，连接器进行资源分配和权限验证；
• 命令分发器 建立连接后，mysql收到SQL语句，命令分发器判断sql是查询还是更新；
• 查询缓存 检查是否开启缓存验证，如果开启，检查是否存在缓存，存在的话就直接返回结果，不存在的话继续向下执行(这里的缓存是用hash的，所以任何sql语句的变化，即便是空格也会导致缓存不命中)；
• 分析器 语法分析器，检查sql语法；
• 预处理器 检查数据库表、列、别名是否存在或合法；
• 优化器 在这一步生成执行计划，即选什么索引等，可以用explain查看；
• 执行器 执行sql；

本篇文章是了解索引的，所以重点是右边泳池的后置处理内容

• 如果不启用索引

• 启用聚集索引

• 启用非聚集索引

索引类型

1.主键索引

结构：B+树结构
特点：中间节点保存的是主键的值对于InnoDB引擎，叶子节点保存的是主键对应的那一行数据；对于MyIsam引擎，叶子节点保存的是数据对应的地址。上述差异是因为MyIsam引擎中数据和索引是分开存储的，而InnoDB是放一起的。以下分别是MyIsam和InnoDB的主键索引结构

2.非主键索引

结构：多样化
特点：对于MyIsam引擎来说，主键索引和非主键索引是没区别的；对于InnoDB引擎来说，非主键索引中存储的不再是数据了，而是主键的值。所以如果通过非主键索引去查询其他字段的值，比直接通过主键索引查询多一次回表操作，要操作两颗索引树

InnoDB索引结构/方法

1.hash

特点：key-value形式的索引结构
优点：速度快、时间复杂度底 O(1)
缺点：不适合范围查询，不满足一些的业务需求

2.二叉树

特点：左子树小于根节点，右子树大于根节点
优点：适合范围查询、时间复杂度 O(log2n)，取决于树的高度
缺点：在一定情况下，会退化成链表结构

退化：

3.二叉平衡树

特点：相较于二叉树，增加了自平衡的效果，左右子树高度相差最大不超过1
优点：避免了二叉树退化成链表的情况
缺点：如果数据量多，插入数据的时候平衡树也是一个很耗时的操作，而且树的高度也不会很低，每查一个节点就是一次IO

4.B树

特点：相当于多叉树，保留了右>左
优点：空间利用率大大提高(假若，p1、p2为指针占4byte，key为bigint占8byte，一个节点的大小很明显是有限的，但是从磁盘读取一页的大小是16K,所以二叉树的空间利用率相较于多叉树是很低的)
缺点
缺点：做范围查询效率较低

5.B+树

特点：相较于B树，它的数据都存在了子节点，而且子节点之间做成了双向链表,左边<key<=右边
优点：改进了B+树范围查询效率差的不足，子节点之间的双向指针减少了范围查询带来的磁盘IO

6.B树和B+树的对比

B树的索引项分布在整棵树，B+树的最底层叶子结点包含所有的索引项，所以如果要查找上图中key为17的数据。对于B树来说，查找到磁盘块2就可以返回数据了，对于B+树则需要找到磁盘块6中。
下面详细说一下，如果要查询值为15(等值查询)的数据，B+树的查询路径

• 第一次磁盘IO，将磁盘块1加载到内存中。在内存中从头遍历比较，发现15<28,磁盘根据p1寻址到磁盘块2
• 第二次磁盘IO，将磁盘块2加载到内存中。在内存中从头遍历比较，发现10<15<17,磁盘根据p2寻址到磁盘块5
• 第三次磁盘IO，将磁盘块5加载到内存中。在内存中从头遍历比较，发现15=15,取出data，如果data存储的是行记录，则取出data，查询结束。如果存储的是磁盘地址，说明不是最后节点，里面存储的还是磁盘地址，需要继续向下遍历
  如果是查28的话
• 第一次磁盘IO，将磁盘块1加载到内存中。在内存中从头遍历比较，发现28=28,磁盘根据p2寻址到磁盘块3(如果是B树，在这一步已经返回结果了)
• 第二次磁盘IO，将磁盘块3加载到内存中。在内存中从头遍历比较，发现28<36,磁盘根据p1寻址到磁盘块7
• 第三次磁盘IO，将磁盘块7加载到内存中。在内存中从头遍历比较，发现28=28,取出data，如果data存储的是行记录，则取出data，查询结束。如果存储的是磁盘地址，说明不是最后节点，里面存储的还是磁盘地址，需要继续向下遍历
  对于等值查询，只有一个B树存在中间返回的情况的区别。但是对于范围查询的话，B+树就比B树的性能好太多了，因为B+树是双向链表，叶子结点是有序的。所以mysql索引都是B+树。

索引的使用

组合索引

哪些字段创建：我们可以把常用的字段设置成组合索引
怎么创建：区分度高的字段可以放在最左边
为什么要创建：如果创建了一个{id,name,code}的组合索引，相当于创建了{id},{id,name},{id,name,code}三种索引，节约了磁盘空间，减少了插入数据的维护成本
之后怎么使用：注意最左匹配原则，按顺序来，因为组合索引查找的时候就是按序来的。也就是说索引创建是{id,name,code}，使用必须也是这个顺序，不按顺序的话就不会走索引

覆盖索引

哪怕使用了组合索引，仍然少不了找到主键再去回表，还是要遍历两颗索引树。
仍然以id,code,name为例，这种组合不光经常出现在where、order by中，而且经常出现在select后面，所以对于以下例子，我们可以建一个{animal_type,sex,animal_species}索引，这样在遍历组合索引树的时候，我们所需要的数据也都正好是索引的key，不需要再去遍历主键索引树了。
这种叫做覆盖索引，减少回表操作

// 查询动物类别、性别、种类
select animal_type,sex,animal_species from animal where animal_type = '哺乳动物' and sex= '雄性' and animal_species ='虎类';

in索引

当表中数据量小，而且in中的数据包含了大部分数据时，是不会走索引的，因为此时全表扫描和索引的效率差不多
当表中数据量大的话，是会走索引的。