一条SQL查询语句的执行过程（一）

mysql> select * from T where ID=10;

1.1 MySQL有哪些“零件”?

首先看下MySQL的一个逻辑架构图：

总体来说，主要分为Server层和存储引擎层两个部分

• Server层:包含MySQL的核心服务功能，所有的内置函数，所有跨存储引擎的功能（存储过程、触发器、视图等）；
• 存储引擎层：负责数据的存储和提取。支持InnoDB、MyISAM、Memory等多个存储引擎，是插件式的。MySQL 5.5.5版本InnoDB开始成为了默认存储引擎

1.2 连接器

负责跟客户端建立连接、获取权限、维持和管理连接。

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

1. 建立通信连接，完成TCP握手；
2. 连接器开始认证身份：
   • 若用户名或密码错误，客户端收到“Access denied for user”提示，结束执行；
   • 若用户名密码认证通过，连接器上权限表查询该用户所拥有的权限；在此之后，该连接所有权限相关的判断都依赖于此时读到的用户权限。
     1. show processlist查看空闲连接，wait_timeout默认是8小时（空闲连接的断开时间）；
     2. 连接断开后再发送请求，客户端收到“ Lost connection to MySQL server during query”提示；
     3. 连接分为长连接（客户端持续请求一直使用同一个连接）和短连接（每次执行完几次查询就断开连接，下次请求再重新建立连接）；建立连接的过程比较复杂，尽量使用长连接。
     4. 如果全用长连接，MySQL占用内存可能会上涨很快：MySQL执行过程中临时使用的内存是管理在连接对象中的，只有断开连接才释放，内存占用太大，会被强行杀掉（OOM），表象上就是MySQL重启了。如何解决呢？
        1. 定期断开长连接。使用一段时间或在程序中执行了一个占用内存大的查询后就断开连接；
        2. MySQL 5.7及以上，可以执行 mysql_reset_connection来重新初始化连接资源，该操作不需要重连和权限验证，会将连接对象恢复到刚创建时的状态。

1.3 查询缓存

1. MySQL拿到一个查询请求后会看缓存中是否有这条查询语句，缓存以key-value形式存储，key是查询语句，value是查询结果；若命中则直接返回查询结果；

2. 若未命中则继续后面的执行阶段，最终的执行结果会被存入到查询缓存。

3. 查询缓存弊大于利，绝大部分情况不使用。

   • 常更新的表，查询缓存会失效的很频繁（对一个表进行更新，这个表的所有查询缓存都会失效）

   • 长时间不更新的表适合查询缓存，如配置表。

   • 将参数query_cache_type设置成DEMAND，默认不使用查询缓存，需要使用的地方可以用SQL_CACHE显式指定：

     select SQL_CACHE * from T where ID=10；
     

   • MySQL 8.0版本直接将查询缓存的整块功能删掉了。

1.4 分析器

没有命中查询缓存，开始真正执行SQL语句了。

1. 词法分析：

   • SQL语句由多个字符串和空格组成，MySQL需要识别每个字符串代表什么；
   • 当输入"select"，识别这是一个查询语句。从而将"T"也识别为表名，把字符串"ID"也识别为"列ID"；

2. 语法分析：

   • 根据词法分析的结果，语法分析器会根据语法规则，判断你输入的SQL是否满足MySQL语法；

   • 若语法错误，会收到"You have an error in your SQL syntax"的错误提醒；

     mysql> elect * from t where ID=1;
     
     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 'elect * from t where ID=1' at line 1
     

     一般语法错误会出现在第一个出错的位置，所有需要关注的是"use near"后面的内容

1.5 优化器

经过分析器，MySQL知道你要做什么了。但在真正执行前，还要经过优化器的处理。

表里有多个索引时，决定使用哪个索引；

一个语句有多表关联时，决定各个表的连接顺序：

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。

两种方案执行逻辑一样，但执行效率有不同，优化器的作用就是决定使用哪种方案。

优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。

1.6 执行器

MySQL通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。

1. 判断你对这个表T有没有执行查询的权限；没有，则返回没有权限的错误（工程实现上，如果命中查询缓存，会在查询缓存返回时做权限验证。查询也会在优化器之前调用precheck验证权限）。

   mysql> select * from T where ID=10;
   
   ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'
   

2. 若有权限，则打开表继续执行。执行器会根据表的引擎的定义，去使用这个引擎提供的接口。

   若ID没有索引：

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

   若ID有索引：

   • 调用InnoDB引擎接口“取满足条件的第一行”这个接口；
   • 循环取“满足条件的下一行”这个接口，

3. 在数据库的慢查询日志中看到一个rows_examined的字段，表示这个语句执行过程中扫描了多少行。