004-MYSQL的查询过程

1. sql慢查询（优化），如果没有索引怎么办？加了索引也比较慢怎么办

原因讲解
首先，我们来创建一个存储引擎为 InnoDB 的 User 表，这个表包含三个字段分别是 id，name 和 age。其中 id 为主键， name 上添加了一个普通索引名字叫 n，接着向这条表中插入 10 亿条数据。表和数据都创建完了，下面我们就来说说为什么加上了索引还是查询很慢，以及解决方案。
MySQL 会根据语句的执行时间来判断 SQL 语句是否是慢查询语句。
当一个 SQL 语句在执行时，MySQL 把语句执行时间和系统参数 long_query_time（这个参数的默认值是 10 秒，但是在实际项目中我们会将这个参数值设置为1秒甚至更短的时间） 作比较。如果执行时间大于这个参数的值，那么就把这个语句记录到慢查询日志中。那么在语句执行过程中我们如何得知是否使用了索引呢？
这时我们就可以使用 explain 语句来查看数据结果中 Key 的值是否 null ，如果是 null 则说明没有使用索引。

下面我们来看一个例子：

explain select* from user;

explain select* from user where id= 1;

explain select name from user;

上面三个 explain 语句返回的 key 如下表所示：

从上表我们可以看出第一个语句没有使用索引，第二个使用了主键索引，第三个语句使用 n 这个索引。我们的 user 表有 10 亿条数据，可想而知第一条查询语句执行效率肯定低，第二个查询语句看似执行效率高，其实在极端环境下（比如 CPU 高负载）也会出现查询效率低的问题。
最后一个查询语句呢虽然使用了 n 这个索引，但是它实际上执行了扫描整个索引树的操作，因此查询效率也高不到哪去。
综上所述，我们可知索引是否使用和是否被记录到慢查询中几乎没有联系，索引只是 SQL 的一个执行过程，SQL 的执行时间才是决定是否被记录到慢查询中的关键。
前面一小节我们只是简单的分析了一下问题，下面我们进一步看这个问题。我们知道 InnoDB 是索引组织表，所有数据都存储在索引树上。

在 InnoDB 中数据放在主键索引里，因此理论上来说所有在 InnoDB 表中的查询至少使用了一个索引。比如这个 SQL 查询语句 select * from user where id > 1000，很明显它使用主键索引，并且这个语句一定执行了整个索引树的扫描(范围查询走下面的链表)。

在 InnoDB 中只有一种情况叫不使用索引，就是从主键索引的最左边的叶子结点开始向右扫描整个索引树。到目前为止我们已经知道了全索引扫描会造成查询变慢，下面我们就来说一下另一个知识点过滤性 。
假如我们要查询 user 表中 age 在 70 岁以上 80 岁以下的人员信息，你一定会在 age 字段上加入索引来避免全局扫描。

不错，这是个好的想法，但是当你运行查询语句时就会发现它依然执行的很慢，这是为什么呢？

要解答这个问题我们先来看一下 SQL 查询语句的执行流程：

• 搜索 age 索引树，获取到第一个 age 为 70 的记录。
• 拿到主键值，根据主键值去主键索引树上获取对应的信息，并将信息加入结果集。
• 在 age 索引树上向右侧扫描，获取到下一个主键值，执行第二部的操作。
• 不断执行上面的步骤，直到遇到第一个 age 大于 80 的记录为止。

从上面的步骤中我们可以看出虽然使用了索引，但是查询过程中扫描了上万行甚至上亿行。
因此我们可以得出结论：对于这种数据非常多的表，我们所要做的不仅仅是加入索引，还要保证索引的过滤性足够优秀。假如说我们把索引的过滤性也处理好了，是不是查询时要扫描的行数就一定会表少呢？
这个答案是否定的，比如说我们的 user 表中的 name 和 age 字段共同组成了联合索引并处理好了过滤性，这时当我们查询姓李的并且年龄是 60 岁的数据时查询效率依然很低。

我们先来看一下查询语句的执行流程：

• 首先从联合索引上找到姓名字段是李字开头的数据记录。
• 拿到主键值，根据主键值在主键索引书上去除匹配的数据。
• 接着根据 age 字段去判断年龄是否等于 60，如果符合就加入结果集。
• 然后再联合索引上向右侧遍历，并不断做回表和判断，直到遇到 name 的第一个字不是李的为止。

Tip： 所谓的回表就是根据主键值去主键索引树上查找对应的数据。

从上面的步骤中我们可以看出最耗时的就是回表，如果姓李的数据有 2 亿条那么就要回表 2 亿次，并且 SQL 在定位第一行数据时只能使用最左前缀原则。

这种耗时的回表操作步骤在 MySQL 5.6 及其以后的版本中已经做了 index condition pushdown 优化。

优化后的流程很简单：

• 首先从联合索引上找到姓名字段是李字开头的数据记录，并判断这个记录里 age 是不是 60，如果是就执行回表取出数据加入结果集。
• 重复步骤1，直到配当第一个字不是李字的记录为止。

优化后和优化前的区别是把 age 的对比步骤放在了遍历联合索引树上，减少了回表次数。
但是虽然减少了回表次数，联合索引树的遍历去没有减少依然要遍历 2 亿次，那么有没有更好的优化方案呢？答案是有的，我们可以实虚拟列来进行处理。

首先我们需要把 name 的第一个字和 age 做一个联合索引，让虚拟列的值总是等于 name 字段的前两个字节，这里需要注意的是虚拟列不随着 insert 和 update 变化，它的值是自定义生成的。

语句如下：

alter table user add name_first varchar( 2) generated(left( name, 1)), add index(name_first,age);

经过上述的优化后联合索引树的查询次数也降低了，本质上就是创建一个紧凑的索引加快查询。

总结
这篇文章主要介绍了查询优化的基本思路，只要记住优化查询的过程都是减少扫描行数的过程，就可以做到在 SQL 查询面前百战百胜。

2. 一条SQL语句在MySQL中执行过程全解析

本篇文章会分析一个 sql 语句在 MySQL 中的执行流程，包括 sql 的查询在 MySQL 内部会怎么流转，sql 语句的更新是怎么完成的。在分析之前我会先带着你看看 MySQL 的基础架构，知道了 MySQL 由那些组件组成以及这些组件的作用是什么，可以帮助我们理解和解决这些问题。

一 MySQL 基础架构分析

1.1 MySQL 基本架构概览

下图是 MySQL 的一个简要架构图，从下图你可以很清晰的看到用户的 SQL 语句在 MySQL 内部是如何执行的。先简单介绍一下下图涉及的一些组件的基本作用帮助大家理解这幅图，在 1.2 节中会详细介绍到这些组件的作用。

连接器： 身份认证和权限相关(登录 MySQL 的时候)。
查询缓存: 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。
分析器: 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
优化器：按照 MySQL 认为最优的方案去执行。
执行器: 执行语句，然后从存储引擎返回数据。

简单来说 MySQL 主要分为 Server 层和存储引擎层：

Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog 日志模块。
存储引擎： 主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自有的日志模块 redo log 模块。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始就被当做默认存储引擎了。

1.2 Server 层基本组件介绍

1) 连接器
连接器主要和身份认证和权限相关的功能相关，就好比一个级别很高的门卫一样。

主要负责用户登录数据库，进行用户的身份认证，包括校验账户密码，权限等操作如果用户账户密码已通过，连接器会到权限表中查询该用户的所有权限，之后在这个连接里的权限逻辑判断都是会依赖此时读取到的权限数据，也就是说，后续只要这个连接不断开，即时管理员修改了该用户的权限，该用户也是不受影响的。

2) 查询缓存(MySQL 8.0 版本后移除)
查询缓存主要用来缓存我们所执行的 SELECT 语句以及该语句的结果集。

连接建立后，执行查询语句的时候，会先查询缓存，MySQL 会先校验这个 sql 是否执行过，以 Key-Value 的形式缓存在内存中，Key 是查询预计，Value 是结果集。如果缓存 key 被命中，就会直接返回给客户端，如果没有命中，就会执行后续的操作，完成后也会把结果缓存起来，方便下一次调用。当然在真正执行缓存查询的时候还是会校验用户的权限，是否有该表的查询条件。

MySQL 查询不建议使用缓存，因为查询缓存失效在实际业务场景中可能会非常频繁，假如你对一个表更新的话，这个表上的所有的查询缓存都会被清空。对于不经常更新的数据来说，使用缓存还是可以的。

所以，一般在大多数情况下我们都是不推荐去使用查询缓存的。

MySQL 8.0 版本后删除了缓存的功能，官方也是认为该功能在实际的应用场景比较少，所以干脆直接删掉了。

3) 分析器
MySQL 没有命中缓存，那么就会进入分析器，分析器主要是用来分析 SQL 语句是来干嘛的，分析器也会分为几步：

** 第一步，词法分析， **一条 SQL 语句有多个字符串组成，首先要提取关键字，比如 select，提出查询的表，提出字段名，提出查询条件等等。做完这些操作后，就会进入第二步。

**第二步，语法分析，**主要就是判断你输入的 sql 是否正确，是否符合 MySQL 的语法。

完成这 2 步之后，MySQL 就准备开始执行了，但是如何执行，怎么执行是最好的结果呢？这个时候就需要优化器上场了。

4) 优化器
优化器的作用就是它认为的最优的执行方案去执行（有时候可能也不是最优，这篇文章涉及对这部分知识的深入讲解），比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序等。

可以说，经过了优化器之后可以说这个语句具体该如何执行就已经定下来。

5) 执行器
当选择了执行方案后，MySQL 就准备开始执行了，首先执行前会校验该用户有没有权限，如果没有权限，就会返回错误信息，如果有权限，就会去调用引擎的接口，返回接口执行的结果。

这个时候执行权限校验的原因如下：
sql执行过程中可能会有触发器，这种在运行时才能确定的过程，分析器工作结束后的precheck是不能对这种运行时涉及到的表进行权限校验的，所以需要在执行器阶段进行权限检查

二 语句分析

2.1 查询语句

说了以上这么多，那么究竟一条 sql 语句是如何执行的呢？其实我们的 sql 可以分为两种，一种是查询，一种是更新（增加，更新，删除）。我们先分析下查询语句，语句如下：

select * from tb_student  A where A.age='18' and A.name=' 张三 ';

结合上面的说明，我们分析下这个语句的执行流程：

• 先检查该语句是否有权限，如果没有权限，直接返回错误信息，如果有权限，在 MySQL8.0 版本以前，会先查询缓存，以这条 sql 语句为 key 在内存中查询是否有结果，如果有直接缓存，如果没有，执行下一步。

• 通过分析器进行词法分析，提取 sql 语句的关键元素，比如提取上面这个语句是查询 select，提取需要查询的表名为 tb_student,需要查询所有的列，查询条件是这个表的 id=‘1’。然后判断这个 sql 语句是否有语法错误，比如关键词是否正确等等，如果检查没问题就执行下一步。

• 接下来就是优化器进行确定执行方案，上面的 sql 语句，可以有两种执行方案：

 a. 先查询学生表中姓名为“张三”的学生，然后判断是否年龄是18。
 b. 先找出学生中年龄 18 岁的学生，然后再查询姓名为“张三”的学生。

• 那么优化器根据自己的优化算法进行选择执行效率最好的一个方案（优化器认为，有时候不一定最好）。那么确认了执行计划后就准备开始执行了。

• 进行权限校验，如果没有权限就会返回错误信息，如果有权限就会调用数据库引擎接口，返回引擎的执行结果。

2.2 更新语句

以上就是一条查询 sql 的执行流程，那么接下来我们看看一条更新语句如何执行的呢？sql 语句如下：

update tb_student A set A.age='19' where A.name=' 张三 ';

我们来给张三修改下年龄，在实际数据库肯定不会设置年龄这个字段的，不然要被技术负责人打的。其实该条语句也基本上会沿着上一个查询的流程走，只不过执行更新的时候肯定要记录日志啦，这就会引入日志模块了，MySQL 自带的日志模块式 binlog（归档日志） ，所有的存储引擎都可以使用，我们常用的 InnoDB 引擎还自带了一个日志模块 redo log（重做日志），我们就以 InnoDB 模式下来探讨这个语句的执行流程。流程如下：

• 先查询到张三这一条数据，如果有缓存，也是会用到缓存。然后拿到查询的语句，把 age 改为 19，然后调用引擎 API 接口，写入这一行数据，InnoDB 引擎把数据保存在内存中，同时记录 redo log，此时 redo log 进入 prepare 状态，然后告诉执行器，执行完成了，随时可以提交。执行器收到通知后记录 binlog，然后调用引擎接口，提交 redo log 为提交状态。更新完成。

这里肯定有同学会问，为什么要用两个日志模块，用一个日志模块不行吗?

这是因为最开始 MySQL 并没与 InnoDB 引擎( InnoDB 引擎是其他公司以插件形式插入 MySQL 的) ，MySQL 自带的引擎是 MyISAM，但是我们知道 redo log 是 InnoDB 引擎特有的，其他存储引擎都没有，这就导致会没有 crash-safe 的能力(crash-safe 的能力即使数据库发生异常重启，之前提交的记录都不会丢失)，binlog 日志只能用来归档。

并不是说只用一个日志模块不可以，只是 InnoDB 引擎就是通过 redo log 来支持事务的。那么，又会有同学问，我用两个日志模块，但是不要这么复杂行不行，为什么 redo log 要引入 prepare 预提交状态？这里我们用反证法来说明下为什么要这么做？

先写 redo log 直接提交，然后写 binlog，假设写完 redo log 后，机器挂了，binlog 日志没有被写入，那么机器重启后，这台机器会通过 redo log 恢复数据，但是这个时候 binlog 并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。
先写 binlog，然后写 redo log，假设写完了 binlog，机器异常重启了，由于没有 redo log，本机是无法恢复这一条记录的，但是 binlog 又有记录，那么和上面同样的道理，就会产生数据不一致的情况。

• 如果采用 redo log 两阶段提交的方式就不一样了，写完 binglog 后，然后再提交 redo log 就会防止出现上述的问题，从而保证了数据的一致性。那么问题来了，有没有一个极端的情况呢？假设 redo log 处于预提交状态，binglog 也已经写完了，这个时候发生了异常重启会怎么样呢？ 这个就要依赖于 MySQL 的处理机制了，MySQL 的处理过程如下：

• 判断 redo log 是否完整，如果判断是完整的，就立即提交。

• 如果 redo log 只是预提交但不是 commit 状态，这个时候就会去判断 binlog 是否完整，如果完整就提交 redo log, 不完整就回滚事务。这样就解决了数据一致性的问题。

三 总结

MySQL 主要分为 Server 层和引擎层，Server 层主要包括连接器、查询缓存、分析器、优化器、执行器，同时还有一个日志模块（binlog），这个日志模块所有执行引擎都可以共用，redolog 只有 InnoDB 有。
引擎层是插件式的，目前主要包括，MyISAM,InnoDB,Memory 等。
SQL 等执行过程分为两类：

• 对于查询过程：权限校验—》查询缓存—》分析器—》优化器—》权限校验—》执行器—》引擎
• 更新语句流程：分析器----》权限校验----》执行器—》引擎—redo log prepare—》binlog—》redo log commit
  ————————————————

原文链接：https://blog.csdn.net/weter_drop/article/details/93386581

3. 回表

1、聚族索引

就是表主键索引，索引节点里面包含了数据整条记录值

2、非聚族索引

不是主键索引都叫非聚族索引，索引节点存的主键字段的值和索引本身的值
那么再来解析下什么是回表，总结字面意思是再查表的意思，二次查询操作。

3、回表

如果查询通过主键查询，因为主键索引节点包含当行数据所有记录值，所有一次就能查询出来。
那么如果不是主键索引，查询索引以外的字段值，索引节点并没有存储它，所有会通过索引节点存储的主键值再去查询一次。这就是回表操作。

原文链接：https://blog.csdn.net/galaxy_zh/article/details/123408022