mysql的速度依赖之索引的原理以及如何利用好索引

名字是乱打的

发布于 2021-12-24 09:28:48

4920

发布于 2021-12-24 09:28:48

文章被收录于专栏：软件工程

一查询sql的执行过程

一条sql查询的语句执行过程

MySQL 可以分为 Server 层和存储引擎层两部分。

Server 层包括连接器、查询缓存（SQL_CACHE）、分析器、优化器、执行器等，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能，比如存储过程、触发器、视图等。
存储引擎层负责数据的存储和提取。InnoDB、MyISAM、Memory(内存) 等多个存储引擎。InnoDB 在5.5.5后成为默认存储引擎

缓存 show variables可以查看我们mysql的许多配置，我们查一些需要的参数可以使用类似于模糊匹配的方式如下：

show variables

show variables like '%cache%'; 我们可以指定查询缓存的开关

临时关闭mysql查询缓存 set global query_cache_size=0 set global query_cache_type=0
永久的修改配置文件my.cnf ,添加下面的配置即可 query_cache_type=0 quey_cache_size=0
按需使用query_cache_type DEMAND

mysql> select SQL_CACHE（sql_no_cache） * from T where ID=10；

MySQL 8.0 版本直接将查询缓存的整块功能删掉了，也就是说 8.0 开始彻底没有这个功能了。

语法分析举例： 根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法，如果我们输入的不满足就会报错。如下我们selecr少输入一个s

这里着重说一下优化器经过了分析器，MySQL 就知道你要做什么了。在开始执行之前，还要先经过优化器的处理。

优化器负责选择执行计划，比如在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

举个例子你执行下面这样的语句，这个语句是执行两个表的 join：

 select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;

既可以先从表 t1 里面取出 c=10 的记录的 ID 值，再根据 ID 值关联到表 t2，再判断 t2 里面 d 的值是否等于 20。
也可以先从表 t2 里面取出 d=20 的记录的 ID 值，再根据 ID 值关联到 t1，再判断 t1 里面 c 的值是否等于 10。

这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。如果你还有一些疑问，比如优化器是怎么选择索引的，有没有可能选择错等等

二索引

对于查询效率影响至关重要的索引，想必大家都知道这个词。但是可能还不是非常了解。 索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。

索引的常见模型：哈希表、有序数组和搜索树。

1. 哈希表是一种以键 - 值（key-value）存储数据的结构，我们只要输入待查找的键即 key，就可以找到其对应的值即 Value。哈希的思路很简单，把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。不可避免地，多个 key 值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的一种方法是，拉出一个链表。需要注意的是，图中四个 ID_card_n 的值并不是递增的，这样做的好处是增加新的 User 时速度会很快，只需要往后追加。但缺点是，因为不是有序的，所以哈希索引做区间查询的速度是很慢的。你可以设想下，如果你现在要找身份证号在[ID_card_X, ID_card_Y]这个区间的所有用户，就必须全部扫描一遍了。所以，哈希表这种结构适用于只有等值查询的场景，比如 Memcached 及其他一些 NoSQL 引擎。
1. 有序数组在等值查询和范围查询场景中的性能就都非常优秀。还是上面这个根据身份证号查名字的例子，如果我们使用有序数组来实现的话，示意图如下所示：
我们假设身份证号没有重复，这个数组就是按照身份证号递增的顺序保存的。这时候如果你要查 ID_card_n2 对应的名字，用二分法就可以快速得到。如果仅仅看查询效率，有序数组就是最好的数据结构了。但是，在需要更新数据的时候就麻烦了，你往中间插入一个记录就必须得挪动后面所有的记录，成本太高。
1. 平衡二叉树，（普通二叉搜索树容易链化就不说了）

缺点： 太高了 数据处的（高）深度决定着他的IO操作次数，IO操作耗时大 太小了 每一个磁盘块（节点/页）保存的数据量太小了没有很好的利用操作磁盘IO的数据交换特性(我们可以一次读4K数据的,现在只读了1个结点进去,而且这个结点只存了一个数据,非常浪费操作系统资源) 也没有利用好磁盘IO的预读能力（空间局部性原理）(预读；每一次IO时，不仅仅把当前磁盘地址的数据加载到内存，同时也把相邻数据也加载到内存缓冲区中。) 从而带来频繁的IO操作 操作系统方面具体细节可以百度,百度百科比我说的好...

4.B+树 B树和B+树类似，区别于其他树的最大区别是:B树和B+树,每个结点中不再只有左右两个孩子了,而是我们可以定义为任意个孩子,其中m个孩子就是m阶树。

4.1 B-Tree

m阶B树定义

根节点至少包括两个孩子
树中每个节点最多含有m个孩子（m>=2）(m个孩子就称之为m阶树)
关键字个数最多为m-1个(根据孩子结点来的,比孩子结点少一个)
除根节点和叶节点外，其他每个节点至少有ceil（m/2）个孩子
所有叶子节点都位于同一层

4.2为什么用B-树可以很矮,很胖，速度很快呢?

这是因为,我们mysql一般把一个结点数据定义为一页,一页数据是16K=16*1024byte,如果我们用的平衡二叉树,假如定义的索引为int型id,一个id 4byte,加上其他数据一个id索引可能页就8byte左右,这才占了一次io的多少?这何等的浪费资源??而我们用B树后,B树是多路平衡查找树,我们可以定义 16*1024/8 大概两千多路(索引或者孩子结点),这样效率提升了何止一点半点呢!

这其实也就是为啥我们一般慎用uuid做主键,因为它长度太长了,如果用uuid,太占用空间,我们索引的路数会变少,层数变多,效率会有所下降.

4.3 B+Tree(Mysql使用的索引数据结构)

B+树是B树的变体，其结构定义基本与B树相同，除了：

非叶子节点的子树指针与关键字

B+非叶节点不保存数据相关信息，只保存关键字和子节点的引用所有搜索均在叶子结点结束
所有叶子节点均有一个链指针指向下一个叶子结点,相邻节点具有顺序引用的关系(便于范围查找)
B+节点关键字搜索采用闭合区间,就算我们中途知道到了相等的关键字也要一直到叶子结点

B+Tree结构图

4.3.0 B+Tree结论

B+Tree更适合用来做存储索引

B+树的磁盘读写代价更低 B+Tree内部结构并没有指向关键字具体信息的指针,关键字不存放数据,只存放索引信息,因此内部结点相对B+Tree更小; 如果把所有内部结点的关键字存放同一盘块中,这个磁盘块所能容纳的关键字也更多,一次性读入内存中的所需要查找的关键字也就越多,相对来说IO读写次数也就降低了
B+树的查询效率更加稳定 由于内部结点并不是最终指向文件内容的结点而只是叶子结点中关键字的索引,所以任何关键字的查找必须走一条从根节点到叶子结点的路,所有查询的长度相同,导致每个数据的查询效率也几乎是相同的
B+树天然有序,更有利于对数据库的扫描 B-Tree树在提高IO性能时候,并没有解决元素遍历效率底下问题.而B+Tree只需要遍历叶子结点就可以解决对全部关键字信息的扫描,做范围查询相当方便(所有叶子节点均有一个链指针指向下一个叶子结点)

5 如何使我们查询效率更高呢？

5.1 避免回表

5.1.1什么是回表查询？

这先要从InnoDB的索引实现说起，InnoDB有两大类索引：

聚集索引(clustered index)
普通索引(secondary index)

InnoDB聚集索引和普通索引有什么差异？ 聚集索引的叶子节点存储行记录，因此， InnoDB必须要有，且只有一个聚集索引 普通索引的叶子节点存储主键值。

表里哪个字段代表聚集索引呢？

（1）如果表定义了PK，则PK就是聚集索引；（2）如果表没有定义PK，则第一个not NULL unique列是聚集索引；（3）否则，InnoDB会创建一个隐藏的row-id作为聚集索引；

举个栗子具体说明一下索引的建立结构，不妨设有表： t(id PK, name KEY, sex, flag); id是主键（聚集索引），name是普通索引。

表中有四条记录： 1, shenjian, m, A 3, zhangsan, m, A 5, lisi, m, A 9, wangwu, f, B

该表两个索引结构如上图所示

如果我们要根据普通索引name查一条数据，如select * from t where name='lisi';，那么过程应该是咋样的呢？

（1）先通过普通索引定位到主键值id=5；

（2）在通过聚集索引定位到行记录；

这就是所谓的回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低。因此我们平常应该多用主键进行sql操作。

解惑：

用不用的到索引其实不一定是用就是用不到啊，比如我们select * from user where id=3如果id是主键我们就可以用用到索引，怎么证明呢? 可以用explain查看执行计划*，直接在sql语句之前加explain就行，如EXPLAIN SELECT * FROM user WHERE id =3

可以看到这里是用了主键索引的，而且只扫描了一行就搞定了，但是我们还是不建议使用select * 这是因为我们往往需要的数据并没有那么多，但是我们平常为了追求开发速度好多查询功能都复用了以前的sql，增加返回字段，这给别人的业务加大了相应速度，也增加了自己业务的相应时间；另外也建议我们如果在别人代码里建新流程，要把自己的业务提成方法，业务功能划分要清晰，如果开发流程不是非常一致建议尽量别在别人业务里改动。

比如：

上述兴业的类型的我们就可以单独写个方法去处理，这样更清晰点

下面我们看下执行计划输出参数的释义。

5.2 explain

expain出来的信息概要描述：

msql执行计划输出参数详细解析中文文档地址https://www.docs4dev.com/docs/zh/mysql/5.7/reference/explain-output.html，其他技术中文文档https://www.docs4dev.com/docs/zh# type结果值从好到坏依次是： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

system：查询对象表只有一行数据，且只能用于MYISAM和Memary引擎的表，这是最好的情况
const：基于主键或唯一索引查询，最多返回一条结果
eq_ref：表连接时基于主键或非null的唯一索引完成扫描
ref：基于普通索引的等值查询，最多返回一条结果
fulltext：全文检索
ref_or_null：表连接类型时ref，但进行扫描的索引列中可能包含null值
index_merge：利用多个索引
index_subquery：子查询中使用唯一索引
range：利用索引进行范围搜索
index：全索引扫描
all：全表扫描

一般来说，得保证查询至少达到range级别，最好能达到ref，type出现index和all时，表示走的是全表扫描没有走索引，效率低下，这时需要对sql进行调优。这里有点疑问,我们在查询列表接口通常要根据新旧进行排序返回，比如快讯列表根据更新时间排序每次分页取20条，如果我们用自增id代替更新时间,是不是效率会高许多呢?看下面图1，图一是咱们一个有自增主键的表,我们真实使用explain来分析一下两种查询的执行计划

图1

6索引失效的情况,为啥会失效?

其实所有索引失效的原因,我们明白索引的结构就已经知道了,即本质来说,索引失效原因就是搜索条件模糊,搜索范围广,搜索顺序不能按照索引走

为了描述的更清晰点,咱们直接对着图说好了

1.前导模糊查询不能利用索引(like '%XX'或者like '%XX%')

　　比如我们上面的name的值为表'ls','sj','ww','zs' ,如果where name like '%s'条件，由于前面是
　　模糊的，我们不知道从哪里查,所以不能利用索引的顺序，必须一个个去找,，看是否满足条件。这样会导致全索引扫描或者全表扫
　　描。
    如果是这样的条件where name like 'l% '，就可以查找name中l开头的name的位置，当碰到s开头的
　　数据时，就可以停止查找了，因为后面的数据一定不满足要求。这样就可以利用索引了

2.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

3.如果是组合索引的话，如果不按照索引的顺序进行查找，比如直接使用第三个位置上的索引而忽略第一二个位置上的索引时，则会进行全表查询

索引为c1,c2,c3,c4 如果我们直接where x=c3则是全表查询，无法使用该索引的，因为c3字段使用索引的前提是c1，c2两字段均使用了索引。建议大家下来看下最左匹配原则

4.条件中使用or 我们不要感觉用or就一定不走索引,许多情况索引没有必定用到或者不用到,这跟我们条件信息有比较大的关系,比如如下图2所示的一个or,咱们实时说话