一个sql的执行过程
一、 组成部分
PDO_MYSQL is a driver that implements the PHP Data Objects (PDO) interface to enable access from PHP to MySQL databases.
-
客户端,如:php 的 pdo_mysql 扩展。
-
MySQL 服务:
-
server 层,主要包含:连接器、查询缓存、解析器、预处理器、优化器、执行器等,涵盖 MySQL 的大多数核心服务功能,以及所有的内置函数(如日期、时间、数学和加密函数等)。所有跨存储引擎的功能都在这一层实现,比如存储过程、触发器、视图等
-
存储引擎层,主要负责:数据的存储和提取。
二、 大概流程
-
MySQL server 层的 连接器 对来自客户端的连接进行验证,包含:
-
`用户名密码` 或 `SSL 证书` 验证
-
`库-database`、`表-table`、`读写权限` 验证
-
MySQL server 层的 查询缓存 对客户源原始SQL进行 缓存命中检测:命中则直接返回,未命中则进一步执行查询。
-
MySQL server 层的 解析器 对查询语句进行解析,得到查询语句的 解析树。
-
MySQL server 层的 预处理器 对 解析树 进一步验证。
-
MySQL server 层的 优化器 将 解析树 转化为 执行计划。
-
MySQL server 层的 执行器 通过 API 与底层的 存储引擎 进行交互,执行 执行计划。
-
MySQL 存储引擎 层得到执行结果,返回给 MySQL server 层。MySQL server 层将结果交由 查询缓存 进行缓存,并返回给客户端
三、 查询缓存
-
关键逻辑:
-
包含 `now()` `current_date()` 等日期函数
-
包含 `用户自定义函数`、`存储函数` `用户变量` `临时表` 等
-
涉及 mysql 数据库的表或者字段
-
`select * from user where id=1;`
-
`select * from user where id="1";`
-
`select username from user where id=1;`
-
`SELECT username FROM user WHERE id=1;`
-
`查询缓存` 缓存了 `执行计划` 的完整结果,当缓存命中时,直接返回缓存中的结果,从而跳过了 `解析-优化-执行` 的过程。
-
`查询缓存` 基于不变的表结构和表数据,`当表结构或表数据发生变化时,其表上的所有缓存都将失效`。
-
`查询缓存` 可以理解将 `执行计划` 的结果缓存在 hashtable 中,key 是 `客户端发来的原始查询sql` 的 hash 值,因此:
-
的 hash 值并不相同。即:`即使同一条SQL,如果大小写、空格、单引号、双引号、注释等不同,都会使用不同的缓存 key`
-
[结果不 set 缓存的情况] 当查询语句中包含以下情况是,查询结果不会被缓存:
-
因为在 `查询缓存` 阶段,还没有进行 `解析器` 解析的工作,因此:`所有查询都会尝试去 get 缓存,但总是不命中`。
-
相关配置:
-
如果查询结果比较大,超过了query_cache_min_res_unit的值,MySQL将一边检索结果,一边进行保存结果。
-
根据自身情况设置合适的大小:太大会造成大量的 `内存碎片`,太小又需要 `频繁的申请内存`。
-
`have_query_cache`,当前的MYSQL版本是否支持“查询缓存”功能。
-
`query_cache_limit`,能够缓存的最大查询结果,查询结果大于该值时不会被缓存,默认值是 1MB
-
`query_cache_min_res_unit`,查询缓存分配的最小块(字节)。默认值是4096(4KB)。
-
`query_cache_size`,为缓存查询结果分配的总内存。
-
`query_cache_type`,默认为on,可以缓存除了以 `select sql_no_cache` 开头的所有查询结果。
-
`query_cache_wlock_invalidate`,如果该表被锁住,是否返回缓存中的数据,默认是关闭的。
-
优缺点:
-
对于频繁变动(`修改表结构、新增、删除、修改数据`)的表,由于一旦 `变动` 就会清除该表的所有缓存,导致:命中率极低,每次SQL还增加了 `查询缓存` 的额外工作。
-
参与 hash 计算的是客户端发来的原始SQL,还未经过 `解析器` 解析,`完全一样` 的sql才能命中缓存。
-
`查询缓存` 实质上是缓存 `SQL的hash值` 和 `该SQL的查询结果`,省去了大量重复SQL查询的 `解析-优化-执行` 过程。
-
优点:
-
缺点:
四、 `解析器` 和 `预处理器`
解析器 和 预处理器 的工作主要包含:
-
对 原始SQL 进行语法解析,验证语法规则,如:
-
关键字是否正确
-
关键字顺序是否正确
-
语句是否有语法错误,如:缺少逗号等
-
得到 `语法解析树`
-
进一步验证 语法解析树,如:
-
库、表是否存在
-
字段、类型是否正确
-
是否使用了禁止的关键字等
-
调用函数、识别别名等
五、 优化器
-
优化器 是基于 Cost-Based Optimizer 模型,预估 每条执行方式的 成本,选择 成本最小 的执行方式,转化为 执行计划。
-
选择最优的执行方式 比较好使,优化器 维护了一个 执行计划缓存,当缓存命中时,直接使用上次的 执行计划。
-
每种执行方式的成本 cost 预估包含几个方面:
-
`io_cost`,对IO操作的成本预估
-
`cpu_cost`,对CPU操作的成本预估
-
`import_cost`,对远程操作的成本预估
-
`mem_cost`,对内存消耗的成本预估
-
Cost-Based Optimizer 对复杂语句的 成本预估 会产生偏差,这时候就需要用到 我们 了,哈哈。
六、 存储引擎
具体的 执行计划 如何执行,依赖于各种不同的 存储引擎 的索引算法,如:
-
B-Tree 从根节点开始,沿着向下的指针,找到存储了行数据位置的叶子节点,再判断是否满足 覆盖查询,访问行数据。
-
Hash 则根据直接计算 hash 值,如果冲突,再遍历链表。
在此不再赘述,可转阅:MySQL之 B-Tree / B+Tree 索引
七、 结果返回客户端
MySQL将结果集返回给客户端是一个 增量、逐步返回 的过程。即:在查询生成第一条结果时,MySQL就可以开始向客户端逐步返回结果集了。
如果您也喜欢它,动动您的小指点个赞吧