SQL语句是如何执行的？（MySQL篇）

Jun 3, 2023 1 min read

MySQL体系架构

MySQL是典型的C/S架构数据库管理系统，数据库服务器和客户端分别运行在服务器和终端计算机上，通过网络进行通信和数据交换。默认情况下，MySQL服务通过3306端口开放。

类似采用C/S架构的数据库系统还包括：

Oracle
PostgreSQL
MongoDB
SQL Server

MySQL服务器端使用mysqld程序，其职责范围可总结为三个方面：连接层、SQL层、存储层。其中连接层主要是负责与客户端建立连接，接收来自客户端的SQL；SQL层负责对SQL语句进行处理；存储层负责数据的读取和存储。

MySQL SQL处理层

SQL处理层包括缓存查询、解析器、优化器、执行器等。

解析器

解析器负责验证和解析传入的命令，包括语法和语义上的分析。如果语法不正确或语义不合理，则直接报错，若符合语法且语义合理则将命令（解析出的中间结果，如SQL语法分析树）交由优化器进行优化。

优化器

优化器负责把关系表达式转换为查询执行计划。常用优化策略：无用条件去除。
执行器

执行器负责对查询执行计划进行执行，涉及与存储引擎的交互。执行器模块拆解开包括表维护模块(Table Maintainence)、表修改模块(TableModification)、状态报告模块（StatusReport）等等。

MySQL存储引擎

InnoDB

MyISAM

NDA Cluster

Memory

SQL执行原理

如何分SQL性能:以MySQL为例

从数据库优化层面，了解现有SQL语句性能是优化的重要基础，MySQL提供了profiling参数进行SQL剖析。至少截止到MySQL8.0.32（我目前使用过的最新版本MySQL），还可以通过以下语句查询剖析模式是否开启：

select @@profiling;

通过以下语句设定开启剖析模式（若profiling为0则是关闭剖析模式）：

set profiling=1

开启剖析模式之后，当你执行了一条SQL语句，就可以通过show profile语句查询（最近一条）语句的性能剖析数据：

profiling之后: 基于performance schema进行性能剖析

需要注意，当你执行show profile或select @@profiling语句，会收到DBMS的友情提示：@@profiling和show profile已被废弃，在未来的MySQL发行版中将被移除。

'@@profiling' is deprecated and will be removed in a future release.

'SHOW PROFILE' is deprecated and will be removed in a future release. Please use Performance Schema instead

替代的性能剖析方案是通过performance schema来达成通用的目的，详情可参考MySQL :: MySQL 8.0 Reference Manual :: 27.19.1 Query Profiling Using Performance Schema。

按照官方文档的介绍，性能剖析主要涉及几个表：setup_actors表、

setup_actors表可用于限制主机、用户或帐户对历史事件的收集，以减少运行时开销和在历史表中收集的数据量。以下例子展示了如何限制特定用户收集SQL历史事件。先查询用户对历史事件的收集权限
```
SELECT * FROM performance_schema.setup_actors;
```
为了禁止所有前台操作都被监控和记录，需要通过如下语句进行限制。
```
UPDATE performance_schema.setup_actors
    SET ENABLED = 'NO', HISTORY = 'NO'
    WHERE HOST = '%' AND USER = '%';
```
为了使特定用户具备历史事件收集、监控的权限，通过如下语句为用户开放权限：
```
INSERT INTO performance_schema.setup_actors
    (HOST,USER,ROLE,ENABLED,HISTORY)
    VALUES('localhost','YOUR_USER','%','UPDATE performance_schema.setup_instruments
    SET ENABLED = 'YES', TIMED = 'YES'
    WHERE NAME LIKE '%stage/%';
YES','YES');
```

setup_instruments表列出了可以收集的事件类。通过更新setup_instruments表，确保启用语句和阶段检测。有些工具可能已经默认启用。

UPDATE performance_schema.setup_instruments
    SET ENABLED = 'YES', TIMED = 'YES'
    WHERE NAME LIKE '%statement/%';

UPDATE performance_schema.setup_instruments
    SET ENABLED = 'YES', TIMED = 'YES'
    WHERE NAME LIKE '%stage/%';

确保启用events_statements_*和events_stages_*消费者。一些消费者可能在默认情况下已经启用。

UPDATE performance_schema.setup_consumers
    SET ENABLED = 'YES'
    WHERE NAME LIKE '%events_statements_%';

UPDATE performance_schema.setup_consumers
       SET ENABLED = 'YES'
       WHERE NAME LIKE '%events_stages_%';

完成上述配置后，就可以使用performance schema进行SQL性能剖析了。执行需要剖析的SQL语句，如：

select FK_TASK_ID as T, count(*) as count from t_image ti group by ti.FK_TASK_ID ;

通过查询events_statements_history_long表来确定语句的EVENT_ID。这一步类似于运行SHOW PROFILES来识别Query_ID。下面的查询产生的输出类似于SHOW PROFILES:

通过查询events_stages_history_long表以检索语句的阶段事件，得到语句的性能剖析报告

mysql> SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT
       FROM performance_schema.events_statements_history_long WHERE SQL_TEXT like '%FK_TASK_ID%';

一条SQL语句背后的操作

从events_stages_history_long表中检索出的事件反映了一条SQL语句背后需要执行多少操作（不同版本的MySQL操作会有所不同）。MySQL8.0.32版本下，操作包含：

starting
executing hook on transaction begin
staring
checking permission
opening tables
init
system lock
optimizing
statistics
preparing
executing
end
query end
waiting for handler commit
closing tables
freeing items
cleaning up

参考文档

练习数据

GitHub - cystanford/sql_heros_data: SQL课程-王者荣耀heros数据表