mysql

一个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 层将结果交由 查询缓存 进行缓存，并返回给客户端

三、 查询缓存

• 关键逻辑：
  • 查询缓存 缓存了 执行计划 的完整结果，当缓存命中时，直接返回缓存中的结果，从而跳过了 解析-优化-执行 的过程。
  • 查询缓存 基于不变的表结构和表数据，当表结构或表数据发生变化时，其表上的所有缓存都将失效。
  • 查询缓存 可以理解将 执行计划 的结果缓存在 hashtable 中，key 是 客户端发来的原始查询sql 的 hash 值，因此：
    • 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;
  • 的 hash 值并不相同。即：即使同一条SQL，如果大小写、空格、单引号、双引号、注释等不同，都会使用不同的缓存 key
  • [结果不 set 缓存的情况] 当查询语句中包含以下情况是，查询结果不会被缓存：
    • 包含 now() current_date() 等日期函数
    • 包含 用户自定义函数、存储函数 用户变量 临时表 等
    • 涉及 mysql 数据库的表或者字段
  • 因为在 查询缓存 阶段，还没有进行 解析器 解析的工作，因此：所有查询都会尝试去 get 缓存，但总是不命中。
• 相关配置：
  • have_query_cache，当前的MYSQL版本是否支持“查询缓存”功能。
  • query_cache_limit，能够缓存的最大查询结果，查询结果大于该值时不会被缓存，默认值是 1MB
  • query_cache_min_res_unit，查询缓存分配的最小块（字节）。默认值是4096（4KB）。
    • 如果查询结果比较大，超过了query_cache_min_res_unit的值，MySQL将一边检索结果，一边进行保存结果。
    • 根据自身情况设置合适的大小：太大会造成大量的 内存碎片，太小又需要 频繁的申请内存。
  • 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就可以开始向客户端逐步返回结果集了。