数据库知识收集

基础问题

1.一条sql语句中各个关键字的执行顺序

但 MySQL/Oracle 等引擎的真实执行顺序如下：

  1. FROM：选择主表
  2. JOIN：连接其他表
  3. ON：连接条件过滤
  4. WHERE：行级别过滤
  5. GROUP BY：分组
  6. HAVING：分组级别过滤
  7. SELECT：提取目标列 / 计算表达式
  8. DISTINCT：去重
  9. ORDER BY：排序
  10. LIMIT / OFFSET：分页限制

2.where 和 group by走索引的过程

WHERE 子句的主要目的是过滤数据。它利用 B+ 树的查找过程分为以下两种典型情况：

  1. 等值查询（Point Query）： 例如 WHERE a = 1 AND b = 2。
    • 过程： MySQL 引擎会从 B+ 树的根节点开始，利用二分查找法，沿着多级索引页向下遍历。因为 a 和 b 都是等值，B+ 树保证了这两列是绝对有序的，引擎能够以 $O(\log N)$ 的时间复杂度，精确定位到叶子节点中满足条件的数据边界。
  2. 范围查询（Range Query）： 例如 WHERE a > 1 AND a < 10 AND b = 2。
    • 过程： 引擎首先按 a > 1 找到左边界，然后沿着叶子节点的双向链表向右顺序扫描（Range Scan），直到遇到 a >= 10 的数据停止。
    • 断点陷阱： 因为在 a 是一个范围的前提下，b 的值是全局无序但局部有序的。所以对于 b = 2 这个条件，无法再利用二分查找去树中快速跳跃，只能在扫描 a 范围的过程中，逐行比对 b 的值（这部分在 MySQL 5.6 引入了 ICP 索引下推 优化，减少了回表次数）。

GROUP BY 的本质是先排序，后聚合。 如果数据本身不是有序的，MySQL 就不得不创建一个内部临时表（Using temporary），把数据放进去，然后再通过外部排序算法（Using filesort）把相同组的数据排在一起进行聚合。这是极度消耗 CPU 和内存的。

索引对 GROUP BY 最大的贡献就是：天然排好序了，直接拿来用。

MySQL 利用索引处理 GROUP BY 有两种高级扫描方式：

  1. 松散索引扫描（Loose Index Scan）：
    • 条件： 查询只涉及索引的最左前缀，且通常配合 MIN() 或 MAX()。
    • 过程： 引擎不需要扫描满足条件的所有记录。比如 GROUP BY a，它会在 B+ 树中直接跳跃着读取每个 a 组的第一条（或最后一条）记录。效率极高，相当于只扫描组的数量，而不是数据行的数量。
  2. 紧凑索引扫描（Tight Index Scan）：
    • 条件： 无法使用松散扫描，但依然符合最左前缀。比如 WHERE a = 1 GROUP BY b。
    • 过程： 引擎会利用 a = 1 定位到数据后，因为在 a 相等的情况下 b 是严格有序的，所以引擎只需要顺着叶子节点一直往后读。遇到相同的 b 就聚合，遇到不同的 b 就开启下一个组。全程不需要额外的排序操作（无 filesort）。

3.主从同步

MySQL 的主从同步是一个异步的逻辑复制过程，主要涉及三个线程：

  1. Master - Binlog Dump Thread: 当 Slave 连接到 Master 时，Master 会创建一个线程，负责读取 binlog 内容并发送给 Slave。
  2. Slave - I/O Thread: 负责连接 Master，请求指定位置（position）之后的 binlog 内容，并将其写入到本地的 Relay Log（中继日志） 中。
  3. Slave - SQL Thread: 负责读取 Relay Log 中的事件，并在本地重新执行一遍（Redo），从而保证数据的一致性。

同步的效率和准确性很大程度上取决于 binlog_format 的选择：

  • Statement: 记录的是 SQL 原文。
    • 优点：日志量小。
    • 缺点：会导致数据不一致（如使用了 UUID(), NOW() 等非确定性函数）。
  • Row: 记录每一行数据的变更。
    • 优点：最安全，能保证数据的绝对一致。
    • 缺点：日志量巨大（比如一个 UPDATE 影响万行，就会记录万行数据）。
  • Mixed: 折中方案。普通操作用 Statement，遇到非确定性函数自动切为 Row。
    • 资深建议：在互联网高并发场景下，为了保证数据订阅（如 Canal 抽数）和一致性，通常强制建议使用 Row 格式。

同步的形式：

A. 异步复制 (Asynchronous)

Master 只要写完自己的 binlog 就返回成功，不关心 Slave 是否收到。

  • 风险：如果 Master 宕机且 binlog 还没传到 Slave，会丢失数据。

B. 半同步复制 (Semi-Synchronous)

Master 写入 binlog 后，必须等待至少一个 Slave 确认收到并写入 Relay Log 后才返回成功。

  • 权衡：提升了安全性，但增加了一个网络 RTT 的延迟，影响吞吐量。

C. 组复制 (MGR - MySQL Group Replication)

基于 Paxos 协议的多主或主从模式，提供强一致性保障。

问题：

这是生产环境中无法回避的问题。常见原因及对策：

  • 原因 1：Slave 的 SQL 线程单线程瓶颈
    • 方案：开启 并行复制 (MTS, Multi-Threaded Slave)。在 MySQL 5.7+ 中可以基于组提交 (Group Commit) 实现并行回放。
  • 原因 2：大事务
    • 方案：避免在一个事务中更新大量数据。将大任务拆分为小批次处理。
  • 原因 3：Slave 读压力过大
    • 方案：增加 Slave 节点，或者将非实时性要求的读业务切到 Slave。

4.查询太慢怎么办？

Step 1: 发现与定位（找准靶心）

  • 慢查询日志 (Slow Query Log)：看是否开启了慢查日志，利用 mysqldumpslow 工具找出发生频率最高、耗时最长的 SQL。
  • 监控链路：结合 APM 工具（如 SkyWalking、Pinpoint）或者查 show processlist 看看有没有正在卡住的查询。

Step 2: 执行计划分析（解剖 SQL）

拿到慢 SQL 后，第一步永远是在前面加上 EXPLAIN 关键字。我会重点看这几个字段：

  • type：这是核心。如果是 ALL（全表扫描）或者 index（全索引扫描），那必须要优化。我们的目标是至少达到 range（范围扫描），最好是 ref 甚至 eq_ref。

ALL
→ index
→ range
→ index_merge
→ ref
→ eq_ref
→ const
→ system
→ NULL

  • key：看 MySQL 实际有没有走我们期望的索引，有没有发生索引失效。
  • Extra：这是关键警告区。如果出现 Using filesort（说明在内存或磁盘发生了外部排序）或者 Using temporary（使用了临时表），这往往是性能杀手。

Step 3: 对症下药（常见优化手段）

  • 索引失效救援：排查是不是对索引列做了运算、用了函数、发生了隐式类型转换，或者 LIKE '%abc' 左侧加了百分号，或者是联合索引违背了“最左前缀法则”。
  • 避免回表：如果 SELECT 捞的字段不多，尽量利用覆盖索引 (Covering Index)，让查询直接在辅助索引树上拿到结果，避免回聚簇索引查数据。
  • 分页优化：对于 LIMIT 1000000, 10 的深度分页，采用延迟关联（先走索引查出主键，再去 JOIN 原表捞数据）或者记录上一页最后一条的主键来进行范围查询。