MySQL之SQL语句执行过程

目录

一条查询SQL的执行过程

连接器

查询缓存

分析器

优化器

执行器

一条更新SQL的执行过程

redo log

binlog

order by的执行过程

rowId排序

count(?)执行过程

---

我们可能每天都或多或少的写一些SQL语句，但是你有没有思考过MySQL接收到SQL的执行命令之后底层是如何执行的呢，从这篇文章开始我们开始揭开MySQL底层执行过程的面纱。

首选创建表

create table user_info (
	id int not null primary key,
  username varchar(16) not null,
  city varchar(16) not null,
  salary int not null default 0,
  createtime timestamp not null default current_timestamp,
  updatetime timestamp null,
  key city(city)
)engine=Innodb;

一条查询SQL的执行过程

首先看一下一条sql语句的执行所经历的步骤，如下图所示

连接器

连接器负责跟客户端简历连接、获取权限、管理连接，例如：

mysql -h 127.0.0.1 -P 3306 -u root -p #生产环境不建议将密码直接输出到命令行中

连接超时时间由wait_timeout控制，默认8个小时，这里建立的连接为长连接，客户端持续请求使用的是同一个连接。

长连接的优缺点：

优点：避免了频繁的建立连接断开连接，降低了网络I/O成本

缺点：占用较多的内存，MySQL在执行过程中临时使用的内存管理在连接对象里，这些资源等到连接断开才会释放。

解决方案

1、定期断开长连接

2、对于MySQL5.7以上通过执行mysql_reset_connection初始化连接资源。

查询缓存

MySQL接收到SQL查询请求之后会先查询缓存看一下之前有没有执行过这条语句，当请求的SQL中有更新操作或者日期函数等都不会走缓存。 查询缓存可以看做是SQL文本和查询结果的映射。如果第二次查询的SQL和第一次查询的SQL完全相同（注意必须是完全相同，即使多一个空格或者大小写不同都认为不同）

清空缓存

1、FLUSH QUERY CACHE; // 清理查询缓存内存碎片。
2、RESET QUERY CACHE; // 从查询缓存中移出所有查询。
3、FLUSH TABLES; //关闭所有打开的表，同时该操作将会清空查询缓存中的内容 

在实际工作中不建议大家使用缓存，在实际工作场景中缓存的命中非常低，往往查询缓存的弊大于利。MySQL在8.0已经把查询缓存功能彻底干掉了。

分析器

对SQL语句按照空格拆分，然后区分中哪些是关键字、哪些是表名、字段名，然后分析你的语句有没有语法错误，如果有错误会提示You have an error in your SQL syntax.

优化器

如果在你的SQL里面使用了多个查询条件和多个索引或者多表关联查询，优化器会通过调整查询条件的执行顺序选择一种效率最高的查询方式。

执行器

经过优化器之后然后开始执行，先对当前表是否有操作权限，如果没有会返回没有操作权限。如果有权限打开表的时候执行会根据表的引擎定义去使用这个引擎提供的接口。例如执行

select * from user_info where username='张三';

1、调用 InnoDB 引擎接口取这个表的第一行，判断 username 值是不是 张三，如果不是则跳过，如果是则将这行存在结果集中；

2、调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。

3、执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

一条更新SQL的执行过程

我们以下面一条更新SQL语句为例

update user_info set salary=salary+2000 where id=10086;-- id是主键

更新语句同样会把连接器、查询缓存、分析器、优化器、执行器都走一遍。

1、对于更新操作会把这个表的缓存都清空。

2、分析器进行词法、语法分析

3、优化器决定使用id这个索引

4、执行器找到这一行然后执行更新操作。

与查询操作不同的是更新操作会涉及到redo log和binlog，下面我们来具体说说这两种日志。

redo log

对于MySQL如果每一次更新操作都实时的写入磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。为了解决这个问题，MySQL采用了 WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。

具体来说，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么这块日志总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写，如下面这个图所示

write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。write pos 和 checkpoint 之间还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示文件写满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。

binlog

首先biglog和redo log区别如下：

1、redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。

2、redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。

3、redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

结合着redo log和binlog我们再来看一下上面更新SQL语句的执行过程

1、执行器先找引擎取 id=10086 这一行。id 是主键，引擎直接用树搜索找到这一行。如果 id=10086 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。

2、执行器拿到引擎给的行数据，把这个值加上2000，比如原来是20000，现在就是 20000+2000，得到新的一行数据，再调用引擎接口写入这行新数据。

3、引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。

4、执行器生成这个操作的 binlog，并把 binlog 写入磁盘。

5、执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

对于步骤三的分阶段提交是为了保证两份日志之间的逻辑一致，如果不使用两阶段提交

先写 redo log 后写 binlog。

假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于我们前面说过的，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 salary 的值是22000。但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行salary的值就是20000，与原库的值不同。

先写 binlog 后写 redo log。

如果在 binlog 写完之后 crash，由于redo log还没写，崩溃恢复以后这个事务无效，所以这一行salary的值是20000。但是 binlog 里面已经记录了“把salary 从20000 改成22000”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行salary 的值就是22000，与原库的值不同。

order by的执行过程

假如我们北京市查询工资最高的100个人

select username,city,salary from user_info where city='杭州' order by salary limit 100;

首先来看下这个SQL的执行计划

Using filesort表示需要排序，MySQL会给每一个线程分配一块内存用于排序，称为sort_buffer.

city的索引结构如下（图片源自MySQL实战）：

语句的执行流程如下：

1、初始化 sort_buffer，确定放入 username、city、salary 这三个字段；

2、从索引 city 找到第一个满足 city='杭州’条件的主键 id，也就是图中的 ID_X；

3、到主键 id 索引取出整行，取 username、city、 三个字段的值，存入 sort_buffer 中；

4、从索引 city 取下一个记录的主键 id；

5、重复步骤 3、4 直到 city 的值不满足查询条件为止，对应的主键 id 也就是图中的 ID_Y；

6、对 sort_buffer 中的数据按照字段 name 做快速排序；

7、按照排序结果取前 1000 行返回给客户端。

按name排序”这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数sort_buffer_size。sort_buffer_size，就是 MySQL 为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序(归并排序)。因此我们在工作中尽可能将需要排序的数据进行条件过滤，一旦使用磁盘排序此时的性能会降低很多。

rowId排序

如果我们要返回的字段很多，那么 sort_buffer 里面要放的字段数太多，这样内存里能够同时放下的行数很少，要分成很多个临时文件，排序的性能会很差。此时MySQL会只把需要排序的salary和id放到sort_buffer中，执行流程变成了：

1、初始化 sort_buffer，确定放入两个字段，即 salary 和 id；

2、从索引 city 找到第一个满足 city='杭州’条件的主键 id，也就是图中的 ID_X；

3、到主键 id 索引取出整行，取 salary、id 这两个字段，存入 sort_buffer 中；

4、从索引 city 取下一个记录的主键 id；重复步骤 3、4 直到不满足 city='杭州’条件为止，也就是图中的 ID_Y；

5、对 sort_buffer 中的数据按照字段 salary 进行排序；遍历排序结果，取前 100 行，并按照 id 的值回到原表中取出 city、username 和 salary 三个字段返回给客户端。

这时候rowid排序有一次回表操作，最多会根据主键多扫描100行数据。

如果我们在city和username上创建联合索引

alter table user_info add index city_username(city, username);

图片源自MySQL实战

这样整个查询过程的流程就变成了：

1、从索引 (city,username) 找到第一个满足 city='杭州’条件的主键 id；

2、到主键 id 索引取出整行，取 username、city、salary 三个字段的值，作为结果集的一部分直接返回；

3、从索引 (city,username) 取下一个记录主键 id；重复步骤 2、3，直到查到第 100 条记录，或者是不满足 city='杭州’条件时循环结束。

这时候不需要排序，因为联合索引已经是排序好的。

如果我们在city,username,salary创建联合索引（覆盖索引）

这样整个查询语句的执行流程就变成了：

1、从索引 (city,username,salary) 找到第一个满足 city='杭州’条件的记录，取出其中的 city、username 和 salary 这三个字段的值，作为结果集的一部分直接返回；

2、从索引 (city,username,salay) 取下一个记录，同样取出这三个字段的值，作为结果集的一部分直接返回；

3、重复执行步骤 2，直到查到第 100 条记录，或者是不满足 city='杭州’条件时循环结束。

count(?)执行过程

MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高；但是如果count(*)后面有where条件，也需要一行一行的计算。

InnoDB 引擎需要把数据一行一行地从引擎里面读出来，然后累积计数。

InnoDB之所以没有把行数存起来是因为InnoDB的MVCC机制同一时刻返回的行数也可能不同，所以没法事先将行数保存。例如：

假设表 t 中现在有 10000 条记录，我们设计了三个用户并行的会话。会话 A 先启动事务并查询一次表的总行数；会话 B 启动事务，插入一行后记录后，查询表的总行数；会话 C 先启动一个单独的语句，插入一行记录后，查询表的总行数。

图片源自MySQL实战

count(*)扫描的是普通索引树，因为普通索引树的叶子节点只存了主键值，而主键索引索引树上的叶子节点存的是数据，这样普通索引树比主键索引树会小很多。并不会把全部字段取出来，而是专门做了优化，不取值。count(*) 肯定不是 null，按行累加。

count(主键id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。

count(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。

count(字段)如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。

count(*)并不会把全部字段取出来，而是专门做了优化，不取值。count(*) 肯定不是 null，按行累加。

索引几种count方式的效率如下：

count(字段)<count(主键 id)<count(1)≈count(*)，所以我们尽量使用 count(*)。