MySQL实战45讲之一条SQL查询语句是如何执行的

一条SQL查询语句是如何执行的

SQL语句

select * from T where ID=10;

MySQL基本架构

总的来说分为两层：server层与存储引擎层

有了这个基础的认知下面将 select * from T where ID=10;的执行流程在下面走一遍。

连接器

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

连接命令

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

注意：

• 密码虽然可以在p后写，但这样会导致密码泄露。

• 完成TCP握手后，就认证身份。

• 密码错误，收到"Access denied for user"提示信息。

• 认证通过，连接器到权限表里面查你拥有的权限。

• 客户端太久没动静，连接器就会断开，默认8h。

  断开后客户端发起请求，就会收到一个错误提醒：

  Lost connection to MySQL server during query。

长连接与短连接

长连接： 指的是连接成功后，如果客户端持续发请求，则一直使用一个连接。

短连接： 是指每次执行完几个很少的命令后就断开连接。

问题： 全是用长连接内存涨得很快。最终OOM系统强行杀掉，现象就是MySQL重启了。

解决方案：

1. 定期断开长连接。使用一段时间或是程序中判断执行过一个占用内存的大查询后，断开连接，之后要查询重新连接。
2. 可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。

查询缓存

MySQL拿到一个查询请求后，会先到缓存看看，之前是不是执行过这条语句。

之前执行过的语句可能会以key-value对的形式，被直接缓存在内存中。

建议： 大多情况建议不要查询缓存，因为查询缓存往往弊大于利。

查询缓存的失效非常频繁，只要有一个表进行更新，这个表上所有的查询缓存都会被清空。

select SQL_CACHE * from T where ID=10；

可以使用此代码不用缓存。MySQL8.0开始缓存被删除。

分析器

对SQL语法做解析。

执行过程

1. 首先会做“词法分析”MySQL需要识别出字符串分别是什么，代表什么。

2. 做完后进行“语法分析”，语法分析会判断你输入的MySQL语句是否满足MySQL语法。

   若不满足，则会有下列语法：

   ERROR 1064 (42000): You have an error in your SQL syntax;…

优化器

优化器是在表有多个索引的时候，决定用哪个索引；

或者在一个语句有多表关联(join)的时候，决定各个表的连接顺序。

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

有两个方案：

可以先从t1表中找到c＝10在和t2表的d＝20匹配。
也可以先找到t2表的d＝20，再找到t1表的c＝10。

优化器作用就是决定使用哪个方案。

执行器

开始执行时候，首先要判断你对这个表T有没有执行权限。

(如果命中查询缓存，会在查询缓存返回结果的时候做权限验证)

如果没有权限，返回权限错误。

ERROR 1142 (42000): SELECT command denied to user ‘b’@‘localhost’ for table ‘T’

有权限，打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，使用引擎提供的接口。

引擎执行流程

执行语句select * from T where ID=10;

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