MySQL中SQL执行流程与架构

一、数据SQL的执行流程

我们操作数据库的方式：在服务器中中使用命令行来操作，数据库工具Navicat，JDBC API或者ORM框架

在这些操作方式向服务器发起数据操作的之后，MySQL服务器又是如何执行接收到的SQL语句呢？

SQL语句在MySQL中的执行流程：

1、通信方式

MySQL 支持多种的通信协议，支持长连接和短连接，支持同步/异步的方式；

查看当前的连接数量：

show global status like 'Thread%'

查询的结果为： 

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 1     |
| Threads_connected | 2     |
| Threads_created   | 3     |
| Threads_running   | 2     |
+-------------------+-------+

Threads_cached :缓存中线程的连接数

Threads_connected: 当前打开的连接数

Threads_created: 为处理连接创建的线程数

Threads_running: 并发连接数

 

查看连接的状态：

mysql> show processlist;
+----+-----------------+-----------+------+---------+--------+------------------------+------------------+
| Id | User            | Host      | db   | Command | Time   | State                  | Info             |
+----+-----------------+-----------+------+---------+--------+------------------------+------------------+
|  5 | event_scheduler | localhost | NULL | Daemon  | 577142 | Waiting on empty queue | NULL             |
| 16 | root            | localhost | NULL | Sleep   |  63775 |                        | NULL             |
| 17 | root            | localhost | NULL | Query   |      0 | starting               | show processlist |
+----+-----------------+-----------+------+---------+--------+------------------------+------------------+
3 rows in set (0.00 sec)

 

MySQL使用了半双工的通信方式，所以要么是客户端向服务端发送数据，要么是服务端向客户端发送数据，两个动作不能同时发生；所以在一次连接里面，客户端在发送SQL数据给服务端的时候，不能把数据分为小块的，不论SQL语句有多大，都得一次性发送。我们可以调整参数max_allowed_packet来扩大或缩小数据包的大小：

mysql> show variables like 'max_allowed_packet';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 67108864 |
+--------------------+----------+
1 row in set (0.00 sec)

 

2、缓存

在MySQL 8.0以前的版本中是带有缓存功能的（默认是关闭的），将数据以KV的形式存储到内存，加快数据的读取速度，减少服务器处理的时间；但是由于MySQL自带的缓存使用场景有限，当SQL多了一个空格或者有大小写的差异，都会认为是不同的SQL进行重新存储，并且当数据库中的数据发生了变化，缓存中的所有数据都会失效；因此8.0版本之后就舍弃了缓存功能；

 

3、词法解析

就是将完整的SQL语句打碎成一个个的单词

 

4、语法解析

就是对SQL进行语法上对检查，例如 查询语句有没有指定哪张数据表，引号有没有闭合等等，并根据MySQL定义的语法规则，将SQL语句解析成数据结构（解析树），图片来源于网络：

 

5、预处理器

会检查生成的解析树，解决解析器无法解析的语义，例如，检查表名，列名，别名是否存在，保证没有歧义；

 

6、查询优化器

根据解析树生成不同的执行计划，也就是一条SQL语有不同的执行方式，然后选择一种最优的执行计划，最终得到的结果是等价的；MySQL中使用的是基于开销cost的优化器，哪一种执行开销小就用哪种执行计划；

可以使用以下命令来查看开销：

mysql> show status like 'Last_query_cost';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| Last_query_cost | 0.849000 |
+-----------------+----------+
1 row in set (0.01 sec)

通过命令，我们可以开启优化器追踪，但是会消耗性能，因为它会将优化分析的结果写到表里面；

mysql> show variables like 'optimizer_trace';
+-----------------+--------------------------+
| Variable_name   | Value                    |
+-----------------+--------------------------+
| optimizer_trace | enabled=off,one_line=off |
+-----------------+--------------------------+
1 row in set (0.01 sec)

mysql> set optimizer_trace='enabled=on';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'optimizer_trace';
+-----------------+-------------------------+
| Variable_name   | Value                   |
+-----------------+-------------------------+
| optimizer_trace | enabled=on,one_line=off |
+-----------------+-------------------------+
1 row in set (0.01 sec)

mysql> select * from customer;

开启之后优化器分析的过程就记录到来系统表里面，此时通过命令就可以查看：

mysql> select * from information_schema.optimizer_trace;

使用完成之后记得关闭：

mysql> set optimizer_trace='enabled=off';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'optimizer_trace';
+-----------------+--------------------------+
| Variable_name   | Value                    |
+-----------------+--------------------------+
| optimizer_trace | enabled=off,one_line=off |
+-----------------+--------------------------+
1 row in set (0.01 sec)

 

我们还可以使用explain来查看执行计划信息：

mysql> explain select * from customer;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | customer | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from customer where id='6666';
+----+-------------+----------+------------+-------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys | key    | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+--------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | customer | NULL       | const | idx_id        | idx_id | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+-------+---------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

 

7、存储引擎

是存储数据的形式；

查看MySQL支持的存储引擎：

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

从以上列表可以看出，InnoDB支持事务，XA协议，Savepoints;

XA 协议是用来实现分布式事务，分为本地资源管理器和事务管理器

Savepoints用来实现子事务（事务嵌套），即在创建了Savepoints之后，事务就会回滚到这个点，不会影响到创建Savepoints之前的操作。

默认的情况下，每个数据库都有自己的文件夹；任何的存储引擎都有自己的frm文件，用来定义表结构；InnoDB使用ibd文件来存储数据；

 

Storage engines are MySQL components that handle the SQL operations for different table types. InnoDB is the default and most general-purpose storage engine, and Oracle recommends using it for tables except for specialized use cases. (The CREATE TABLE statement in MySQL 8.0 creates InnoDB tables by default.)

官方说明，MySQL中每一张表都可以使用不同的存储引擎，并且在8.0版本中使用InnoDB 作为默认的存储引擎；

InnoDB: The default storage engine in MySQL 8.0. InnoDB is a transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data. InnoDB row-level locking (without escalation to coarser granularity locks) and Oracle-style consistent nonlocking reads increase multi-user concurrency and performance. InnoDB stores user data in clustered indexes to reduce I/O for common queries based on primary keys. To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints.

综合以上数据，InnoDB具有以下特点：

支持事务和外键，使得数据的一致性，安全性更高；支持行锁和表锁；支持读写并发，写不阻塞读MVCC；使用B+tree的索引存储方式减少了IO次数，提高了查询的效率；

 

8、执行引擎

使用执行计划去操作存储引擎，利用存储引擎提供的相应的API来完成操作；不同的存储引擎实现的API是相同的，因此在修改来表的存储引擎之后，我们的操作不需要做相应的改变；

 

二、MySQL架构

图片来源与网络

Connections：支持各种语言与SQL交互

Enterprise Management services & utillties： 系统管理和控制工具，包括数据备份，恢复，复制，集群等等

Connection Pool：数据库连接池；管理缓冲的资源，用户登录数据，连接的线程，连接限制等等；

SQL Interface：用来接收用户的SQL命令，返回用户需要的数据

Parser：SQL解析器

Optimizer：SQL优化器

Cache & buffer：缓冲数据；

Pluggable Storage Engines：插件式存储引擎；

 

InnoDB Architecture

图片来源：https://dev.mysql.com/doc/refman/8.0/en/innodb-architecture.html

 

InnoDB In-Memory Structures 内存结构

The buffer pool is an area in main memory where InnoDB caches table and index data as it is accessed. The buffer pool permits frequently used data to be processed directly from memory, which speeds up processing. On dedicated servers, up to 80% of physical memory is often assigned to the buffer pool.

For efficiency of high-volume read operations, the buffer pool is divided into pages that can potentially hold multiple rows. For efficiency of cache management, the buffer pool is implemented as a linked list of pages; data that is rarely used is aged out of the cache using a variation of the LRU algorithm.