SQL 语句执行流程

简图：
image.png
完整流程：
image.png

通信协议：

  • 基于 TCP 的半双工协议 ，同一时刻要么 Client 在向 Server 发送消息，或反之。
  • 客户端取数据，不是 PULL 模型，而是 PUSH 模型，像是“从消防水管喝水”

查询状态：可以通过 show full processlist 查看

  • Sleep：线程正在等待客户端发送新的请求
  • Query：线程正在执行查询或者正在将结果发送给客户端
  • Sorting Result：线程正在对结果集进行排序
  • Sending Data：这表示多种情况：现场可能在多个状态之间传送数据、或正在生成结果集、或在向客户端返回数据

查询缓存：对查询语句进行 Hash 来定位缓存数据

解析器：Mysql 语法特性的解析

预处理器：检测数据表和数据列是否存在，检查权限是否合法

查询优化器：
可以通过 show status like 'Last_query_cost'; 命令查看当前查询的成本，单位是数据页

  1. +-----------------+----------+
  2. | Variable_name   | Value    |
  3. +-----------------+----------+
  4. | Last_query_cost | 8.803390 |
  5. +-----------------+----------+

不一定总能找出最优方案，有如下原因：

  • 执行引擎没有给出正确的统计信息
  • 没有考虑到并发执行问题
  • 存储过程和用户自定义函数的成本没有考虑进去

对于用户来说：慎用 Hint 语法，这会给查询优化器后续的优化和维护带来困难。

调优思路

参考 20丨当我们思考数据库调优的时候，都有哪些维度可以选择？

第一步：定义目标

  • 来源用户反馈，从表象探寻数据库底层的问题
  • 日志监控
    • 硬件层面：CPU、内存、IO
    • 数据库内部：活跃会话、事务、锁 等指标的监控
    • 响应时间：执行时间 + 等待时间
    • 扫描行数和返回行数的比值

常用的查看命令：查看Mysql正在执行的事务、锁、等待

第二步：选择优化维度

  • 选择适合的 DBMS
    • 比如业务数据用 Mysql，在线报表分析使用 Hive + Presto 等。
  • 优化表设计
    • 简单表需要遵循三范式
    • 如果分析查询应用比较多，尤其是需要进行多表联查的时候，可是采用反范式来利用空间换取时间。
    • 字段类型尽量紧凑，提升 IO 效率
  • 优化逻辑查询：改变 SQL 的语句内容让 SQL 执行效率更高
    • 避免 SELECT * 语句
    • EXISTS 子查询和 IN 子查询：使用小表驱动大表，更好的方式是转换成 JOIN 的方式
    • WHERE 子句中会尽量避免对字段进行函数运算
      • SUBSTRING(comment_text, 1,3)='abc' = comment_text LIKE 'abc%'
    • 切分查询
      • 分而治之：功能一样，每次只操作一部分数据。可用于删除场景，防止一下子锁定过多数据，同时降低复制延迟
      • 分解关联查询：在业务层进行数据组装
        • 提升查询缓存命中率，减少锁竞争
    • 关联查询
      • 确认任何的 GROUP BY 和 ORDER BY 中的表达式只涉及一个表中的列，这样 Mysql 才有可能使用索引来优化这个过程
    • 优化 GROUP BY
      • 通过对唯一键进行 GROUP BY 操作，来替代对唯一键关联的多个字段进行 GROUP BY 操作
    • Limit 优化
      • 延迟关联：先通过覆盖索引查出需要偏移的 ID 列表，再与需要取出的数据做 INNER JOIN
      • 业务层面使用确定的取数范围：例如 where id > 20 limit 10
  • 优化物理查询：构建高效索引
    • 高频查询可以考虑实现覆盖索引
    • 如果数据重复度超过 10%，就不需要创建索引
    • 索引不是越多越好，过多的索引会降低写入性能、占用过多存储空间、降低索引优化器评估效率
    • 表连接：
      • 嵌套循环连接
      • 合并连接
        • 没有数据去重需求的话，可以通过 UNION ALL 代替 UNION
  • 使用 Redis 或 Memcached 作为缓存
    • 常见的 3 种缓存模型
      • 旁路缓存模式（可以理解为读更新）
        • 需要先删除 DB，再删除 Cache
      • 读写穿透（需要 Cache 提供更新 DB 的能力）
      • 异步缓存写入
        • 基础设施：消息队列、各种 DB 的数据持久化机制
        • 业务中多用于：数据经常变化又对数据一致性要求没那么高的场景，比如浏览量、点赞量
    • 接入组件
      • 简单的 CURD 可以使用 Jedis
      • 需要拓展的数据结构支撑，考虑 Redison
  • 库级优化：但也会增加相应的维护和使用成本
    • 分区表：可以应对单表 10 亿量级的数据请求
      • 属于水平切分，mysql 5.7 最多拥有 8192 分区
      • 唯一索引和主键必须包含所有分区键
      • 针对锁表的问题，可以通过指定分区的方式来减小影响范围
      • 分区字段包含 NULL 值都会落到 p0 分区，尽量避免 NULL 值
    • 读写分离，主从架构
    • 表结构的垂直分割，减少索引表大小
    • 分库分表
      • 会带来事务问题

补充：
EXISTS 和 IN 的区别：https://cloud.tencent.com/developer/article/1144244

调优路径：
image.png

常用调优命令

show warnings

案例列表：

  • 字符集问题导致的索引失效

Mysql 8.0 执行 explain ，Mysql 5.7 执行 explain extended 之后使用 show warnings 命令，可以看到 Mysql 优化的语句，错误码为 1003

mysql> explain SELECT * FROM user_info  WHERE id in (10,11) and age = 20;

mysql> show warnings \G;
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `learn-java`.`user_info`.`id` AS `id`,`learn-java`.`user_info`.`name` AS `name`,`learn-java`.`user_info`.`age` AS `age` from `learn-java`.`user_info` where ((`learn-java`.`user_info`.`age` = 20) and (`learn-java`.`user_info`.`id` in (10,11)))
1 row in set (0.01 sec)

explain

参考资料：

  • MySQL 性能优化神器 Explain 使用分析
  • 最官方的 mysql explain type 字段解读 | 字段 | 含义 | | —- | —- | | id | SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符. | | select_type | SELECT 查询的类型，具备如下类型
    - SIMPLE, 表示此查询不包含 UNION 查询或子查询
    - PRIMARY, 表示此查询是最外层的查询
    - UNION, 表示此查询是 UNION 的第二或随后的查询
    - DEPENDENT UNION, UNION 中的第二个或后面的查询语句, 取决于外面的查询
    - UNION RESULT, UNION 的结果
    - SUBQUERY, 子查询中的第一个 SELECT
    - DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果.
    | | table | 表示查询涉及的表或衍生表 | | partitions | 匹配的分区 | | type | 扫描类型：
    - system：表中只有一条数据. 这个类型是特殊的 const 类型.
    - const：针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可.
    - eq_ref：此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较操作通常是 =, 查询效率较高
    - ref：此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了 最左前缀 规则索引的查询.
    - range：表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中. 当 type 是 range 时, 那么 EXPLAIN 输出的 ref 字段为 NULL, 并且 key_len 字段是此次查询中使用到的索引的最长的那个
    - index：表示全索引扫描(full index scan), 和 ALL 类型类似, 只不过 ALL 类型是全表扫描, 而 index 类型则仅仅扫描所有的索引, 而不扫描数据. index 类型通常出现在: 所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据. 当 是这种情况时, Extra 字段 会显示 Using index
    - ALL：表示全表扫描
    | | possible_keys | 此次查询中可能选用的索引 | | key | 此次查询中确切使用到的索引 | | key_len | 索引长度，单位是 byte | | ref | 哪个字段或常数与 key 一起被使用：
    - const 常指用户指定的条件固定值
    | | rows | 显示此查询一共扫描了多少行. 这个是一个估计值. | | filtered | 表示此查询条件所过滤的数据的百分比，见：What is the meaning of filtered in MySQL explain?

    如果是 100%，所以你的索引已经完全覆盖了查询条件；如果这个值很小，那么可以考虑优化索引配置了。 | | extra | 额外的信息:
    - Using filesort：当 Extra 中有 Using filesort 时, 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大
    - Using index：“覆盖索引扫描”, 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错
    - Using where：A WHERE clause is used to restrict which rows to match against the next table or send to the client。简单来说：对结果集进行过滤后再继续传递
    - Using temporary：查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化。例如 UNION ALL 不对数据去重，不回用到临时表，但 UNION 会对数据去重，会用到临时表。
    |

InnoDB MVCC 锁

原文见：InnoDB对MVCC的实现

读类型

MVCC 实现了一致性非锁定读：

  • RC：同一个事务内，每次 SELECT 都会重新生成一个 Read View

  • RR：在事务开启后，第一次 SELECT 生成一个 Read View

    锁定读：

  • select … lock in share mode

  • select … for update
  • insert、update、delete 操作

防止幻读是通过：MVCC + Next-Key Lock 实现的，Next-Key Lock 是 Index Record Lock 和 Gap Lock 整合体，当记录存在的时候使用 Index Record Lock，不存在则使用 Gap Lock。

如何复现幻读：在 RR 级别的事务中，明明查询不到一个数据，Insert 的时候却又爆主键冲突错误。

关键三元素

隐藏字段

  • DB_TRX_ID（6字节）：表示最后一次插入或更新该行的事务 id。此外，delete 操作在内部被视为更新，只不过会在记录头 Record header 中的 deleted_flag 字段将其标记为已删除
  • DB_ROLL_PTR（7字节）：回滚指针，指向该行的 undo log 。如果该行未被更新，则为空
  • DB_ROW_ID（6字节）：如果没有设置主键且该表没有唯一非空索引时，InnoDB 会使用该 id 来生成聚簇索引


ReadView

class ReadView {
    /* ... */
    private:
    trx_id_t m_low_limit_id;      /* 大于等于这个 ID 的事务均不可见 */

    trx_id_t m_up_limit_id;       /* 小于这个 ID 的事务均可见 */

    trx_id_t m_creator_trx_id;    /* 创建该 Read View 的事务ID */

    trx_id_t m_low_limit_no;      /* 事务 Number, 小于该 Number 的 Undo Logs 均可以被 Purge */

    ids_t m_ids;                  /* 创建 Read View 时的活跃事务列表 */

    m_closed;                     /* 标记 Read View 是否 close */
}

Read View 主要是用来做可见性判断，里面保存了 “当前对本事务不可见的其他活跃事务”

主要有以下字段：

  • m_low_limit_id：目前出现过的最大的事务 ID+1，即下一个将被分配的事务 ID。大于等于这个 ID 的数据版本均不可见
  • m_up_limit_id：活跃事务列表 m_ids 中最小的事务 ID，如果 m_ids 为空，则 m_up_limit_id 为 m_low_limit_id。小于这个 ID 的数据版本均可见
  • m_ids：Read View 创建时其他未提交的活跃事务 ID 列表。创建 Read View时，将当前未提交事务 ID 记录下来，后续即使它们修改了记录行的值，对于当前事务也是不可见的。m_ids 不包括当前事务自己和已提交的事务（正在内存中）
  • m_creator_trx_id：创建该 Read View 的事务 ID

image.png

Undo Log

Undo Log 的清理由专门的 purge 线程进行清理。

Undo log 主要有两个作用：

  • 当事务回滚时用于将数据恢复到修改前的样子
  • 另一个作用是 MVCC ，当读取记录时，若该记录被其他事务占用或当前版本对该事务不可见，则可以通过 undo log 读取之前的版本数据，以此实现非锁定读

image.png

数据可见性算法

image.png

索引类型

Hash 索引

InnoDB 引擎内部有一个特殊功能叫做 “自适应哈希索引”，但他不受用户控制。
用户可以通过 hash 函数来实现伪 Hash 索引。

B+ 树

参考资料：为什么 MySQL 使用 B+ 树 参考资料：24丨索引的原理：我们为什么用B+树来做索引？ 参考资料：27丨从数据页的角度理解B+树查询

无论是表中的数据（主键索引）还是辅助索引最终都会使用 B+ 树来存储数据，其中前者在表中会以 的方式存储，而后者会以 的方式进行存储

核心需求：MySQL 作为 OLTP（Online Transaction Processing） 的数据库不仅需要具备事务的处理能力，而且要保证数据的持久化并且能够有一定的实时数据查询能力

与 Hash 比较：

与 B 树和 B+ 树相比，哈希作为底层的数据结构的表能够以 O(1) 的速度处理单个数据行的增删改查，但是面对范围查询或者排序时就会导致全表扫描的结果，而 B 树和 B+ 树虽然在单数据行的增删查改上需要 O(log n) 的时间，但是它会将索引列相近的数据按顺序存储，所以能够避免全表扫描

与 B 比较：

计算机在读写文件时会以页为单位将数据加载到内存中。页的大小可能会根据操作系统的不同而发生变化，不过在大多数的操作系统中，页的大小都是 4KB，你可以通过如下的命令获取操作系统上的页大小:

$ getconf PAGE_SIZE
4096

对硬盘进行数据读取会产生随机 I/O，而随机 I/O 的性能在 ms 级别，而内存的读取速度在 ns 级别，由此我们也应该尽量减少随机 I/O 的次数，这样才能提高性能。

B 树与 B+ 树的最大区别就是，B 树可以在非叶结点中存储数据，但是 B+ 树的所有数据其实都存储在叶子节点中，当一个表底层的数据结构是 B 树时，假设我们需要访问所有『大于 4，并且小于 9 的数据』：
image.png

如果不考虑任何优化，在上面的简单 B 树中我们需要进行 4 次磁盘的随机 I/O 才能找到所有满足条件的数据行：

  1. 加载根节点所在的页，发现根节点的第一个元素是 6，大于 4；
  2. 通过根节点的指针加载左子节点所在的页，遍历页面中的数据，找到 5；
  3. 重新加载根节点所在的页，发现根节点不包含第二个元素；
  4. 通过根节点的指针加载右子节点所在的页，遍历页面中的数据，找到 7 和 8；

由于所有的节点都可能包含目标数据，我们总是要从根节点向下遍历子树查找满足条件的数据行，这个特点带来了大量的随机 I/O，也是 B 树最大的性能问题。

B+ 树中就不存在这个问题了，因为所有的数据行都存储在叶节点中，而这些叶节点可以 通过『指针』依次按顺序连接，当我们在如下所示的 B+ 树遍历数据时可以直接在多个子节点之间进行跳转，这样能够节省大量的磁盘 I/O 时间，也不需要在不同层级的节点之间对数据进行拼接和排序；通过一个 B+ 树最左侧的叶子节点，我们可以像链表一样遍历整个树中的全部数据，我们也可以引入双向链表保证倒序遍历时的性能。
image.png
B+ 数是一个多叉平衡二叉树，高度一般控制在个位数，防止过多的层级带来过多的随机 I/O

具体结构

先看 B 树：如果一个磁盘块中包括了 x 个关键字，那么指针数就是 x+1
image.png

B+ 树基于 B 树做出了改进，主流的 DBMS 都支持 B+ 树的索引方式，比如 MySQL。B+ 树和 B 树的差异在于以下几点：

  1. 有 k 个孩子的节点就有 k 个关键字。也就是孩子数量 = 关键字数，而 B 树中，孩子数量 = 关键字数 +1
  2. 所有关键字都在叶子节点出现，叶子节点构成一个有序链表，而且叶子节点本身按照关键字的大小从小到大顺序链接。

image.png

磁盘的 I/O 操作次数对索引的使用效率至关重要。虽然传统的二叉树数据结构查找数据的效率高，但很容易增加磁盘 I/O 操作的次数，影响索引使用的效率。因此在构造索引的时候，我们更倾向于采用“矮胖”的数据结构。

数据页

参见：https://time.geekbang.org/column/article/116766

常见误区

COUNT 统计的列行数？

当执行 count(column) 的时候，统计的是该列中的非 NULL 行数。
如果我们想统计行数，需要使用 count(*)，它会忽略所有的列而直接统计所有的行数。