点击上方蓝色字体,选择“标星公众号”
优质文章,第一时间送达
大家好,我是燕
作者:猿来是八阿哥
链接:https://www.jianshu.com/p/e2926d6a808f
一个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就可以开始向客户端逐步返回结果集了。