MySQL教程：一条查询语句到底是怎么执行的？（MySQL体系结构）

痛定思痛。 2024-02-20 10:37 59阅读 0赞

## 一条查询语句到底是怎么执行的？ ##

*  语法解析 确保语句的正确性 解析器
 *  查询优化器 考虑使用哪些索引、连接方式、表的顺序等来提高查询性能 生成一个执行计划
 *  执行计划生成
 *  数据获取 内存 or 磁盘
 *  数据处理 排序 聚合 连接
 *  结果返回

## MySQL体系结构 ##

![image.png][]

*  客户端 命令行工具、图形化界面、应用程序
 *  连接管理器 连接的简历、断开和连接池管理
 *  查询解析器 检查语法、生成查询执行树
 *  查询优化器 where 1=1 去掉
 *  存储引擎 innodb myisam memory
 *  缓存管理 buffer pool
 *  锁管理
 *  日志管理

### 查询缓存（Query Cache） ###

>  *  要求SQL语句必须一模一样
>  *  表中任何一条数据发生变化，这张表所有的缓存都会失效

*  缓存查询结果
 *  如果存在并且查询的参数完全一致（sql语句），命中缓存
 *  mysql `8.0`版本已经移除
 *  在高并发、写入频繁的应用中，关闭查询缓存有助于提高性能
 *  大小限制 `query_cache_size`参数控制
 *  缓存的失效 更新操作，缓存被标记为无效
 *  缓存的粒度 完整的查询语句作为缓存的单位 参数值不同，会导致缓存失效
 *  更新多的场景下，建议禁用查询缓存，推荐使用redis memcached外部缓存
 *  使用参数化查询，避免直接在语句中拼接参数值
 *  检查查询缓存的当前状态

*  开启/禁用查询缓存

mysql> set global query_cache_type=ON/OFF;
    
    my.cnf
    [mysqld]
    query_cache_type=ON/OFF;

### 语法解析器和预处理器 ###

假如我们随便输一个字符串`abdcjhhhc` 1064错误  
SQL语法正确，但是表不存在，它是怎么发现的？  
对SQL语句进行词法和语法分析、语义的解析

*  词法解析

select name from users where id=1;

这条sql语句会被打碎成8个符号，记录每个符号是什么类型，从哪里开始，到哪里结束。

*  语法解析
    
     *  语法检查 单引号闭合没有
     *  生成解析树
 *  预处理器  
    表名不存在，由预处理器发现  
    检查生成的解析树

#### 查询优化器与执行计划 ####

一条SQL语句是不是只有一种执行方式？  
数据库最终执行的SQL是不是就是我们发送的SQL？  
答案是否定的。  
mysql的查询优化器来判断具体该怎么执行SQL  
根据解析树生成不同的执行计划  
使用基于开销`cost`的优化器  
使用如下命令查看查询的开销 需要随机读取几个4K的数据页才能完成查找

开启优化器的追踪，默认是关闭的

mysql> show variables like 'optimizer_trace';
    +-----------------+--------------------------+
    | Variable_name   | Value                    |
    +-----------------+--------------------------+
    | optimizer_trace | enabled=off,one_line=off |
    +-----------------+--------------------------+
    1 row in set (0.00 sec)
    
    mysql> 
    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.00 sec)
    
    mysql>

会消耗性能，关闭的sql

mysql> set optimizer_trace="enabled=off";
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> 
    mysql> 
    mysql> show variables like 'optimizer_trace';
    +-----------------+--------------------------+
    | Variable_name   | Value                    |
    +-----------------+--------------------------+
    | optimizer_trace | enabled=off,one_line=off |
    +-----------------+--------------------------+
    1 row in set (0.00 sec)
    
    mysql>

expanded\_query 是优化后的SQL语句  
considered\_execution\_plans 里面列出了所有的执行计划

[image.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/20/733f19b6946a4708973ead7542622d55.png