每次开始前的絮叨

恒奥中心，凌晨3点多。这已经是今年第二次通宵测试了，不能把此时身心俱疲的中年危机全部甩锅给发烧，承认生命的老去才是顺利通往退休之路的心理基础嘛。趁着等数据的间隙，写写拖延了好久的总结笔记吧，从最简单的开始，因为此时脑子不是那么清楚…希望这个tag可以坚持写完。

一条最简单的sql语句

工作中使用MySQL，业务逻辑中可能要写sql、封装sql甚至利用工具生成配置sql，也对sql的执行过程大概知道一二，认真听习了林老师（淘宝核心系统数据库组技术专家）的演讲之后，梳理一下sql的执行，就用老师的例子从一条最简单的sql开始吧。

mysql> select * from T where ID = user1

比如有一个最简单的表，只有一个ID字段，我们输入上面的查询语句就可以返回一个结果，如果我们知道了这条语句的内部执行过程或者流程，就会方便我们使用内部视野更深入去分析sql异常等问题。在这之前，还是先来简单地画个基本架构图。

MySQL 可以分为 Server 层和存储引擎层两部分。Server 层包括连接器、查询缓存、分析器、优化器、执行器等。涵盖了MySQL大部分的服务功能、所有内置函数，所有跨存储引擎的功能均在此层实现，例如存储过程、触发器、视图等等。存储引擎层负责数据的存储和提取，架构是插件式的，支持 InnoDB、MyISAM、Memory等多个存储引擎。从MySQL 5.5.5版本开始默认的存储引擎为InnoDB，当我们 create table 时如果不指定引擎类型，默认使用的就是InnoDB，当然也可以通过显示指定engine项的方式使用其他引擎创建表。不同的引擎表数据存储方式不一样，支持的功能也不同。

连接器

当我们与数据库产生直接接触时，即连接到数据库时，与我们直面的就是连接器。连接器负责与客户端建立连接、获取权限、维持和管理连接，一条简单的连接语句长这样：

mysql -h$ip -P$port -u$user -p

输入完命令后，在交互对话中输入密码（虽然密码也可以显示的写在-p参数后面，但这样可能会导致密码泄露，如果连接生产环境的数据库，更不要介样做！）

连接命令中的mysql是客户端工具，用来与服务器建立连接，在完成TCP握手后，连接器就要开始认证你的身份：

• 如果用户名或密码不对，你会收到一个“Access denied for user”的错误，然后客户端程序结束执行
• 如果用户名和密码认证通过，连接器会到权限表里面查出你拥有的权限，之后这个连接里的权限判断逻辑都将依赖此时读到的权限

也就是说，一个用户成功建立连接后，即使我们使用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建连接才会使用新的权限设置。连接完成后如果没有新的动作，这个连接就处于sleep状态，我们可以在 show processlist 中看到它。

AlfreddeMacBook-Pro:~ alfred$ mysql -h127.0.0.1 -P3306 -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 34
Server version: 5.7.10 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 
mysql> 
mysql> show processlist;
+----+------+-----------------+------+---------+------+----------+------------------+
| Id | User | Host            | db   | Command | Time | State    | Info             |
+----+------+-----------------+------+---------+------+----------+------------------+
| 34 | root | localhost:50235 | NULL | Query   |    0 | starting | show processlist |
| 49 | root | localhost:50252 | sys  | Sleep   |   16 |          | NULL             |
| 57 | root | localhost:50270 | sys  | Sleep   |   16 |          | NULL             |
+----+------+-----------------+------+---------+------+----------+------------------+
3 rows in set (0.00 sec)

mysql> 

在上面我们可以看到Command列有显示Sleep状态，即代表此时系统里有两个空闲状态的连接，如果太长时间没动静，连接器就会自动将它断开，这个时间由参数 wait_timeout 控制，默认为8小时。如果一般生产系统不会特别改该值的话，空闲的客户端往往会被操作人员忽略，尤其是登录数据库查业务数据的场景，这其实是很危险的行为，即使调整了该值也要即使登出客户端，或主动关闭连接。

如果在连接被断开之后，客户端再次发送请求的话，就会收到这样的错误提醒：lost connection to MySQL server during query, 这时如果你要继续就要重连再执行请求了。

数据库里面，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接；短连接则指每次执行完几次很少的查询就断开连接，下次查询再重新建立

通常建立连接的过程稍微复杂，所以建议尽量减少建立连接的动作，尽量使用长连接，但全部使用长连接时可能我们又会发现有时候MySQL占用内存会涨得很快，这是因为MySQL在执行过程中临时使用的内存是管理在连接对象里的，这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行kill掉（by Out Of Memory killer），从现象上看就是MySQL异常重启了（当然了，生产系统中一般都会做主备方案，所以当主库挂掉时从实例会升级为主继续为业务提供服务，但发生切主的情况就很有必要分析原因了，甚至拿业务场景进行类比，这些都是潜在的风险）。

How to figure it out ？ 两种解决策略：

• 定期断开长连接，使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
• 如果使用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行mysql_reset_connection 来重新初始化连接资源，这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完成时的状态。

查询缓存

连接建立完成后，我们会习惯性地使用select语句，那么就在这一步，执行逻辑就到了图中的第二步，查询缓存。

MySQL拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以key-value对的形式，被直接缓存在内存中。key是查询的语句，value是查询的结果，如果你的查询能够直接在这个缓存中找到key，那么这个value就会被直接返回给客户端。

如果语句不再查询缓存中，就会继续后面的查询阶段，执行完成后，执行结果会被存入查询缓存中。所以如果查询命中了缓存，MySQL就可以直接返回结果而不用执行后面的操作，效率会很高。

但是，建议不要使用查询缓存。。

查询缓存失效非常频繁，只要有对一个表的更新，这个表上的所有查询缓存都会被清空，因此很可能费劲存起来的结果没等命中呢，就被一条update全清空了，对于更新压力大的数据库来说，查询缓存的命中率会非常低，除非你的业务就是一张静态表，很长时间才更新一次。

所以MySQL也提供了这种按需使用的方式：你可以将参数query_cache_type设置成DEMAND，这样对于默认的SQL语句都不使用查询缓存，而对于你确定要使用查询缓存的语句，可以用SQL_CACHE显示指定，比如生产中一般各个技术中台或子系统的系统配置表不会更新太频繁，就可以这样：

mysql> select SQL_CACHE * from sysMonInlet where sysID = 1000;

但貌似MySQL 8.0版本中直接将查询缓存的整块功能去掉了，大势所趋。。

分析器

如果没有命中缓存，那么是时候开始真正执行语句了，MySQL首先要知道你要做什么，也就是对你的sql进行解析。

首先是词法分析：输入的字符串空格组成的SQL语句中，MySQL需要识别出里面的字符串分别是什么，代表什么。从 select中识别出来这是一个查询语句，sysMonInlet识别出来是表名，字符串 sysID 识别成列。

其次是语法分析：根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个SQL语句是否满足SQL语法，如果语句不对一般会收到 You have an error in your SQL syntax 的提示，例如select写错为selec：

mysql> 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kari               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> selec * from kari;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'selec * from kari' at line 1
mysql> 

优化器

MySQL已经知道了你要做什么，但在开始之前，还要经过优化器的处理。优化器是在表里面有多个索引时，决定使用哪个索引；或者在一个语句有多表关联的时候决定各个表的连接顺序。比如当我们执行下面这样的语句，这个语句是执行两个表的join：

mysql> 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

两种策略的逻辑结果是一样的，但是执行效率会有所不同，优化器的作用就是决定选择使用哪一种方案。

优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段，但对于优化器是怎么选择索引的，有没有可能选择错等，这个小节就不铺开，单独在后面讨论吧~

执行器

MySQL通过分析器知道了你要做什么，通过优化器知道了怎么做，开始执行吧！

开始执行的时候，要判断一下你对表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误，如果有权限，就打开表继续执行，打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。

回到类似开始的那条最简单的语句

mysql> select * from T where ID = 10

比如在表 T 中 ID 字段没有索引，那么执行器的执行流程是这样的：

1. 调用InnoDB引擎接口，取这个表的第一行，判断 ID 的值是不是10，如果不是则跳过，如果是则将这行存在结果集中；
2. 调用引擎接口取”下一行“，重复相同的判断逻辑，直到取到这个表的最后一行；
3. 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端；
4. 至此，语句就执行完了。

对于有索引的情况，执行逻辑类似，第一次调用的是”取满足条件的第一行“这个接口，之后循环取”满足条件的下一行“这个接口，这些接口都是引擎中定义好的。

你会在慢查询日志中看到一个 rows_examined 的字段，表示这个语句执行过程中扫描了多少行，这个值就是在执行器每次调用引擎获取数据行的时候累加的，在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟 rows_examined 并不是完全相同的。

小结

这篇就把MySQL的逻辑架构和SQL执行总结完啦，后面的细节学习，跟上！