SQL语法与查询优化

回目录： 《面试笔记：MySQL 相关目录》
上一篇： 《面试笔记：MySQL 相关02 – 索引》

01. 子查询与连接(join)查询

子查询：是嵌套在主查询中的查询，按“返回结果类型”和“是否依赖主查询”分类，核心是 “子结果驱动主查询”；
连接查询：是多表关联，核心是 “通过关联条件合并表数据”，按“是否保留不匹配行”分类，底层依赖驱动表和被驱动表的关联逻辑。

以下给出了4种核心子查询和5种核心连接查询，以及一种内部优化查询。

01. 标量子查询

返回单个值。

• 写法格式：子查询返回 1 行 1 列，可作为主查询的字段或条件值；

  -- 示例：查询订单数最多的用户名称（子查询返回最大订单数）
  SELECT username FROM user WHERE user_id = (SELECT MAX(user_id) FROM order WHERE status = 1);

• 性能开销：极低，仅执行 1 次子查询，结果直接代入主查询；
• 适用场景：主查询需依赖单个聚合值（如最大值、平均值）或特定单行数据。

02. 列子查询

返回单列多行。

• 写法格式：子查询返回 1 列 N 行，主查询用IN/NOT IN/ANY/ALL匹配；

  -- 示例：查询有未支付订单的用户（子查询返回未支付订单的用户ID列表）
  SELECT username FROM user WHERE user_id IN (SELECT DISTINCT user_id FROM order WHERE status = 0);

• 性能开销：中低，MySQL 5.7 + 会将其优化为 “半连接”（避免逐行匹配），子查询结果会被物化（存入临时表）;
• 适用场景：主查询需匹配多个候选值（如 “在某个集合中”），子查询结果集不宜过大（建议 1 万行内）。

03. 行子查询

返回多行多列。

• 写法格式：子查询返回 N 行 N 列（通常 1-2 列），主查询用IN匹配行数据；

  -- 示例：查询与“用户张三的上海地址”完全匹配的用户（返回id和city两列）
  SELECT username FROM user WHERE (user_id, city) IN (SELECT user_id, city FROM user_addr WHERE username = '张三' AND city = '上海');

• 性能开销：中，子查询结果集需按行匹配，建议结果集控制在 1 千行内；
• 适用场景：主查询需匹配 “多字段组合条件”（如联合主键、多维度筛选）。

04. 关联子查询

依赖主查询字段。

• 写法格式：子查询用主查询的字段作为条件（EXISTS是典型用法），需用表别名区分；

  -- 示例：查询有有效订单的用户（子查询依赖主查询的user_id，找到匹配即停止）
  SELECT username FROM user u WHERE EXISTS (SELECT 1 FROM order o WHERE o.user_id = u.user_id AND o.status = 1);

• 性能开销：中高，主查询每扫描 1 行，子查询触发 1 次（类似 “嵌套循环”）；若子查询关联字段无索引，会导致全表扫描（如 10 万行主表触发 10 万次子查询）；
• 适用场景：主查询需 “存在性校验”（如 “是否有相关数据”），优先用EXISTS（找到匹配行立即停止）替代IN（需遍历全量结果集）。

05. 内连接

INNER JOIN，默认连接方式。

• 写法格式：仅保留两表中 “关联条件完全匹配” 的行（取交集），INNER可省略；

  -- 示例：查询用户及其有效订单（仅返回有订单的用户和匹配的订单）
  SELECT u.username, o.order_no FROM user u
  INNER JOIN order o ON u.user_id = o.user_id  -- 关联条件（必须写ON），INNER 可省略
  WHERE o.status = 1;

• 性能开销：低，MySQL 会自动选择 “小表作为驱动表”，若关联字段有索引，被驱动表可快速匹配（避免全表扫描）；
• 适用场景：需获取多表 “交集数据”（如用户 + 关联订单、商品 + 关联分类），是业务中最常用的连接方式。

06. 左外连接

LEFT JOIN / LEFT OUTER JOIN

• 写法格式：保留左表所有行，右表无匹配时用NULL填充，OUTER可省略；

  -- 示例：查询所有用户及其订单（无订单的用户也会显示，订单字段为NULL）
  SELECT u.username, o.order_no FROM user u
  LEFT JOIN order o ON u.user_id = o.user_id
  WHERE o.status = 1 OR o.order_no IS NULL;  -- 注意：过滤右表需包含NULL

• 性能开销：中低，驱动表是左表（需全扫左表），右表关联字段有索引则性能接近内连接；无索引则性能下降明显；
• 适用场景：需保留主表全量数据（如用户表），关联从表（如订单表）的可选数据（如 “统计所有用户的订单情况，包括无订单用户”）。

07. 右外连接

RIGHT JOIN / RIGHT OUTER JOIN

• 写法格式：保留右表所有行，左表无匹配时用NULL填充，逻辑与左连接相反；

  -- 示例：查询所有订单及其所属用户（无匹配用户的订单也显示，用户字段为NULL）
  SELECT u.username, o.order_no FROM user u
  RIGHT JOIN order o ON u.user_id = o.user_id
  WHERE o.create_time > '2024-01-01';

• 性能开销：与左连接一致，驱动表是右表（需全扫右表）；
• 适用场景：需保留从表全量数据（如订单表），关联主表（如用户表）的可选数据（如 “统计所有近 3 个月订单，包括已删除用户的订单”）。

08. 全外连接

FULL JOIN / FULL OUTER JOIN

• 写法格式：保留左右两表所有行，无匹配时用NULL填充；MySQL 不直接支持FULL JOIN，需用UNION合并左连接和右连接；

  -- 示例：查询所有用户和所有订单的关联数据（保留双方无匹配的行）
  SELECT u.username, o.order_no FROM user u
  LEFT JOIN order o ON u.user_id = o.user_id
  UNION  -- 去重合并
  SELECT u.username, o.order_no FROM user u
  RIGHT JOIN order o ON u.user_id = o.user_id;

• 性能开销：高，需执行两次连接 + 去重操作，数据量大时效率低；
• 适用场景：极少用，仅需 “完整保留两表所有数据” 的特殊场景（数据对账、全量统计）。

09. 交叉连接

CROSS JOIN，笛卡尔积

• 写法格式：无关联条件，两表数据完全组合（行数 = 左表行数 × 右表行数），CROSS JOIN可省略；

  -- 示例：用户表（3行）和商品表（2行）交叉连接，结果为6行
  SELECT u.username, p.product_name FROM user u
  CROSS JOIN product p;

• 性能开销：极高，行数呈指数级增长（1 万行 ×1 万行 = 1 亿行），几乎不用；
• 适用场景：仅用于 “全组合场景”（如生成所有用户 + 所有商品的测试数据），必须配合WHERE过滤（否则会撑爆内存）。

10. 半连接

半连接不是独立的查询语法，而是 MySQL 5.7 + 默认启用的对列子查询（如IN子查询）的内部优化手段，目的是减少子查询与主查询的匹配开销。

用于优化“主查询匹配子查询结果集”的场景（如IN/EXISTS列子查询）。

核心逻辑是 “只判断匹配与否，不返回子查询的完整数据”，避免逐行匹配的低效问题。

简单说：普通子查询是 “子查询返回所有结果→主查询逐行比对”，半连接是 “主查询与子查询表直接关联→找到匹配行就停止”，本质是将子查询转化为类似JOIN的关联逻辑，提升性能。

触发条件：子查询是“列子查询”（返回单列多行），且子查询表与主查询表无关联（非关联子查询），例如WHERE id IN (SELECT uid FROM t2 WHERE status=1)。

写法格式：无特殊语法，仍用普通IN/EXISTS子查询写法，MySQL 会自动触发半连接优化（可通过EXPLAIN查看type列是否有SIMPLE/HASH JOIN，而非SUBQUERY）。

-- 示例：查询有未支付订单的用户（MySQL会自动用半连接优化）
SELECT username FROM user u WHERE u.user_id IN (SELECT DISTINCT o.user_id FROM order o WHERE o.status = 0);

性能开销

• 低 – 中：远优于未优化的子查询（避免子查询结果集物化后逐行匹配）。
• 优化逻辑：MySQL 会选择以下方式之一执行半连接：
  • 哈希半连接：对小表（子查询表）建哈希表，主表逐行匹配哈希键（适合大表）。
  • 嵌套循环半连接：小表驱动大表，找到匹配行立即停止扫描（适合小表）。
  • 物化半连接：子查询结果存入临时表并建索引，主表关联临时表（适合子查询结果集较小）。

适用场景

• 主查询需判断 “字段是否在子查询结果集中”（如IN/EXISTS）。
• 子查询表数据量适中（1 万行内最佳），且子查询有过滤条件（如WHERE status=1），能减少结果集大小。
• 替代场景：若子查询结果集极大（10 万行 +），建议手动改写为JOIN（半连接优化效果会下降）。

注意项：

• 半连接是 MySQL 自动优化，无需专门手动去写语法，但需知道 “IN子查询在 5.7 + 会被优化为半连接”，避免面试官问 “IN和JOIN哪个快” 时只说表面结论。
• 实践中，若EXPLAIN显示子查询类型为SUBQUERY（未触发半连接），可通过 “子查询加DISTINCT” 或 “改写为JOIN” 强制优化（如上述示例加DISTINCT让结果集唯一，更易触发半连接）。

总结

1. 子查询 vs 连接查询：

• 简单场景（如单值匹配、存在性校验）用子查询（写法简洁）；
• 复杂多表关联（如 3 表以上、需筛选多字段）用连接查询（性能更优，易优化）；
• 关联子查询尽量改写为JOIN（避免嵌套循环，如EXISTS适合小表，JOIN适合大表）。

2. 性能优化关键：

• 连接查询：关联字段必须建索引（如order.user_id），小表驱动大表（减少循环次数）；
• 子查询：避免NOT IN（NULL 值会导致结果异常），用NOT EXISTS替代；
• 外连接：右表过滤条件写在ON（关联时过滤），左表过滤条件写在WHERE（关联后过滤）。

3. Java 开发实践场景：

• 列表查询（如用户列表 + 订单数）：用LEFT JOIN + GROUP BY；
• 详情查询（如订单详情 + 用户信息）：用INNER JOIN；
• 存在性校验（如判断用户是否有未支付订单）：用EXISTS子查询。

02. 子查询与join性能对比

子查询与join性能对比及适用场景

关键结论：

• 简单场景（单表校验、单值匹配）用子查询（简洁）；
• 多表关联、复杂聚合、主表数据量大时，优先用 JOIN（性能更稳定）。

03. 复杂查询的执行逻辑

1. group by/having

GROUP BY 和 HAVING 是 MySQL 中用于数据分组聚合的核心语法，执行逻辑： “数据过滤→分组→聚合→二次过滤” 。

1. 核心执行逻辑

GROUP BY 的作用是 “按指定字段将数据分组”；
HAVING 则是 “对分组后的结果进行过滤”。

整体执行流程分四步，顺序不可颠倒：

1) 原始数据过滤（WHERE 子句）；

• 先通过 WHERE 筛选符合条件的原始数据（如 WHERE status = 1 过滤无效数据），减少后续处理的数据量。
• 原理：WHERE 是 “分组前过滤”，仅保留满足条件的行，不涉及聚合操作，可利用索引快速筛选（如 status 字段有索引时，直接定位有效行）。

2) 分组操作（GROUP BY 子句）；

• 按 GROUP BY 后的字段（如 GROUP BY user_id）将上一步过滤后的数据分组，相同值的行被归为一组。
• 底层处理：
  • 若分组字段有索引，MySQL 会直接按索引顺序分组（无需额外排序，效率高）；
  • 若无索引，会创建临时表存储分组数据，或对数据进行文件排序（Using temporary 或 Using filesort，可通过 EXPLAIN 查看）。

3) 聚合计算（聚合函数）；

• 对每个分组执行聚合函数（如 COUNT(*) 统计行数、SUM(amount) 计算总和），生成每个分组的聚合结果。
• 原理：聚合函数仅作用于分组内的数据，每个分组最终输出一行结果（包含分组字段和聚合值）。

4) 分组结果过滤（HAVING 子句）；

• 用 HAVING 筛选聚合后的分组（如 HAVING COUNT(*) > 5 保留行数超 5 的组），最终返回符合条件的分组。
• 原理：HAVING 是 “分组后过滤”，可直接使用聚合函数（因聚合结果已生成）。

2. 关键区别与实践注意

1) WHERE 与 HAVING 的核心区别

• WHERE 作用于分组前的原始数据，不能使用聚合函数（如 WHERE COUNT(*) > 5 会报错）；
• HAVING 作用于分组后的结果，可以使用聚合函数（如 HAVING SUM(amount) > 1000 合法）。

2) 性能优化实践（重点）

• 优先用 WHERE 过滤：尽量在分组前通过 WHERE 减少数据量（如 GROUP BY user_id HAVING user_id > 100 可改为 WHERE user_id > 100 GROUP BY user_id，减少分组计算量）。
• 分组字段加索引：避免临时表和文件排序（如 GROUP BY create_time 时，给 create_time 建索引，EXPLAIN 中无 Using temporary 即为优化生效）。
• 避免 SELECT 非分组字段：MySQL 5.7+ 默认开启 ONLY_FULL_GROUP_BY 模式，SELECT 后只能出现 GROUP BY 字段或聚合函数（如 SELECT user_id, username, COUNT(*) 若 username 未在 GROUP BY 中，会报错，需规范写法）。

3. 典型场景举例

• 统计高频用户：查询 “订单数超 10 单的用户及其总消费”，用 GROUP BY user_id HAVING COUNT(*) > 10。
• 按时间分组分析：查询 “每月订单金额超 10 万的月份”，用 GROUP BY month(create_time) HAVING SUM(amount) > 100000。

2. limit 分页

LIMIT 分页是 MySQL 中用于从查询结果中截取指定范围数据的核心语法（如 LIMIT offset, size 取第 offset + 1 到 offset + size 行），其执行逻辑围绕 “全量查询→排序→截取” 展开，实际使用中需重点关注性能问题。

1. 基本执行逻辑

LIMIT 本身不参与数据筛选，而是对 “查询 + 排序后” 的结果集进行截取，核心流程分三步：

1) 执行主查询获取原始数据

• 先执行 WHERE、JOIN 等条件筛选，得到所有符合条件的行（如 SELECT * FROM order WHERE status=1 筛选有效订单）。

2) 排序处理（若有 ORDER BY）

• 若包含 ORDER BY（分页几乎必带，否则结果无序），MySQL 会按指定字段排序：
  • 若排序字段有索引（如 ORDER BY create_time 且 create_time 有索引），直接利用索引顺序获取有序数据（效率高）；
  • 若无索引，需在内存或磁盘中进行 “文件排序”（Using filesort，可通过 EXPLAIN 查看），排序过程会消耗额外 CPU/IO。

3) 截取指定范围数据

• 从排序后的完整结果集中，跳过前 offset 行，返回接下来的 size 行（如 LIMIT 20, 10 即跳过前 20 行，返回 10 行）。

2. 核心性能问题：offset 过大导致效率低下

当 offset 很大时（如 LIMIT 100000, 10），性能会急剧下降，原因是：

• MySQL 必须先扫描并排序前 100010 行数据，然后丢弃前 100000 行，仅返回最后 10 行，大量计算被浪费；
• 若排序无索引（依赖文件排序），会产生临时文件，进一步加剧性能损耗（10 万行数据排序可能耗时数百毫秒）。

3. 实践优化方案（重点，体现工程经验）

针对 offset 过大的问题，结合 Java 开发中常见的分页场景（如列表页、历史记录查询），优化手段如下：

1) 基于 “主键 / 唯一索引” 分页（最常用）

利用主键或唯一索引的有序性，通过 WHERE 条件直接定位起始位置，避免全量扫描：

-- 代替 LIMIT 100000, 10（低效）
SELECT * FROM order 
WHERE id > 100000  -- 上一页最后一条数据的id
ORDER BY id 
LIMIT 10;

原理：主键索引是有序的，id > 100000 可直接定位到起始行，无需扫描前 10 万行，性能提升 10 倍以上。

2) 避免 SELECT *，只查必要字段

减少数据传输量和内存占用，尤其大表（如包含 text 字段的表）：

-- 只查需要的字段（如订单号、金额、时间）
SELECT order_no, amount, create_time FROM order 
WHERE id > 100000 
LIMIT 10;

3) 确保排序字段有索引

分页必须带 ORDER BY，且排序字段需建索引（如 create_time 索引），避免文件排序：

-- 给 create_time 建索引：CREATE INDEX idx_order_create_time ON order(create_time)
SELECT * FROM order 
WHERE status=1 
ORDER BY create_time DESC  -- 利用索引排序
LIMIT 20, 10;

4) 限制最大分页页数

业务上避免允许用户访问过大页数（如 “只显示前 100 页”），超过则提示 “数据过多，请缩小范围”（如电商平台常见做法），从源头减少大 offset 场景。

04. order by 的排序原理

MySQL 的ORDER BY用于对查询结果按指定字段排序，其核心原理是 “利用索引有序性直接取数” 或 “无索引时通过内存 / 磁盘排序”，性能差异主要源于是否能借助索引避免额外排序操作。

1. 核心排序原理

1. 利用索引排序

Using index，高效。

若排序字段（或多字段排序的前缀）有有序索引（如 B + 树索引，本身按字段值有序存储），MySQL 会直接沿索引顺序读取数据，无需额外排序，这是最优情况。

• 原理：B + 树索引的叶子节点按索引字段值升序（或降序，取决于建索引时的指定）排列，ORDER BY字段与索引顺序一致时，可直接通过索引定位并返回有序数据，避免排序开销。
• 示例：
  订单表order有索引idx_create_time（create_time升序），执行SELECT id, create_time FROM order ORDER BY create_time时，MySQL 直接沿idx_create_time的叶子节点顺序取数，无需排序（EXPLAIN中Extra列显示Using index）。

2. 文件排序

Using filesort，低效。

若排序字段无索引，或排序顺序与索引顺序不一致（如索引是升序，ORDER BY用降序且无对应降序索引），MySQL 会触发 “文件排序”：先将数据加载到内存 / 临时文件，完成排序后再返回结果。

• 执行步骤：

  1. 从表中读取符合WHERE条件的行（无索引时全表扫描）；
  2. 将 “排序字段值 + 行指针（指向原始数据位置）” 存入sort_buffer（排序缓冲区）；
  3. 若sort_buffer装不下所有数据，会将数据分块，先在内存中排序，再写入临时文件（磁盘）；
  4. 最后合并所有分块的排序结果，得到全局有序的结果集，再通过行指针取原始数据返回。

• 示例：
  订单表order的create_time无索引，执行SELECT * FROM order ORDER BY create_time时，MySQL 会先扫描全表，将create_time和行指针存入sort_buffer，排序后再取完整数据（EXPLAIN中Extra列显示Using filesort）。

2. 影响排序性能的关键因素

1. 是否使用索引：索引排序性能远高于文件排序（毫秒级 vs 秒级，数据量大时差距更大）。
2. sort_buffer大小：由sort_buffer_size参数控制（默认 256KB），若数据量超过该值，会触发磁盘临时文件排序（IO 开销剧增）。
3. 查询字段多少：SELECT *会导致sort_buffer中存储更多字段（增加内存占用），而只查必要字段可减少sort_buffer压力，加速排序。

3. 实践优化建议

1. 给排序字段建合适的索引：

• 单字段排序：直接给排序字段建索引（如ORDER BY create_time → 建idx_create_time）。
• 多字段排序（如ORDER BY a, b DESC）：建联合索引(a, b DESC)，索引顺序与排序顺序完全一致（避免索引失效）。

2. 避免SELECT *，只查必要字段：

• 例如，列表页只需id、name、create_time，则SELECT id, name, create_time ...，减少sort_buffer中存储的数据量，避免磁盘排序。

3. 控制排序数据量：
先用WHERE过滤无效数据（如WHERE status=1），减少进入排序阶段的行数（如从 100 万行滤到 1 万行，排序效率提升 100 倍）。

4. 大结果集排序用分页配合索引：
如 “按时间排序分页查询订单”，建(create_time, id)联合索引，用WHERE create_time > ?定位分页起点，避免全量排序。

4. 总结

ORDER BY的优化核心是 “能用上索引就避免文件排序”：索引排序依赖 B + 树的有序性，高效低耗；文件排序需内存 / 磁盘排序，性能差。实际开发中，通过 “建匹配索引 + 精简查询字段 + 提前过滤数据” 可显著提升排序性能，这也是处理订单列表、用户日志等需排序场景的常规优化手段。

05. 慢查询的定位与分析

MySQL 的慢查询（执行时间超过阈值的 SQL）是性能优化的核心切入点，定位与分析需结合日志工具、执行计划和业务场景。

1. 慢查询的定位

慢查询的定位：通过日志捕获目标 SQL。

定位慢查询的核心是开启慢查询日志，记录执行时间超标的 SQL，为后续分析提供依据。

1. 慢查询日志配置（5.7 版本）
通过修改my.cnf（或my.ini）配置，开启并定制日志规则：

slow_query_log = 1  # 开启慢查询日志（默认：0，关闭）
slow_query_log_file = /var/log/mysql/slow.log  # 日志文件路径（需MySQL有写入权限）
long_query_time = 1  # 慢查询阈值（单位秒，默认10秒，建议生产设1-2秒）
log_queries_not_using_indexes = 1  # 记录未使用索引的查询（即使未超阈值，可选）
log_output = FILE  # 日志输出方式（FILE/table，5.7默认：FILE）

配置后重启 MySQL 生效，也可通过SET GLOBAL动态开启（无需重启，适合临时排查）：

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;

2. 日志收集工具

• mysqldumpslow（自带工具）：简单统计慢查询，适合快速定位高频问题：

  # 查看访问次数最多的10条慢查询
  mysqldumpslow -s c -t 10 /var/log/mysql/slow.log
  # 查看总执行时间最长的10条慢查询
  mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

  优势：轻量，适合初步筛选；
  不足：无法分析趋势或复杂统计。

• pt-query-digest（Percona 工具包）：生产环境常用，可按执行时间、频率、用户等维度分析，输出 SQL 模板、平均耗时、扫描行数等关键信息：

  pt-query-digest /var/log/mysql/slow.log > slow_analysis.txt

  优势：能识别重复 SQL（如参数不同但结构相同的查询），定位 “隐形高频慢查询”（单条耗时 0.8 秒，但每秒执行 100 次，总耗时 80 秒）。

2. 慢查询的分析

慢查询的分析：通过执行计划定位根因。

捕获慢查询后，需用EXPLAIN分析其执行逻辑，重点关注是否全表扫描、是否用对索引、是否有额外排序 / 临时表。

1. 核心分析工具：EXPLAIN

对慢查询执行EXPLAIN，查看关键字段：

• type：访问类型（性能从好到差：const > eq_ref > ref > range > ALL）。ALL表示全表扫描（需优先优化）。
• key：实际使用的索引（NULL表示未用索引）。
• rows：预估扫描行数（值越大，效率越低）。
• Extra：额外信息（关键警告：Using filesort（需排序且无索引）、Using temporary（需创建临时表）、Using where; Using filesort（全表扫描后排序，性能极差））。

2. 常见慢查询原因及分析（结合EXPLAIN）

• 全表扫描（type=ALL，key=NULL）：
  • 原因：查询条件无索引（如：WHERE status=1但status无索引），或索引失效（如：WHERE name LIKE '%abc'左模糊匹配导致索引失效）。
• JOIN 关联无索引（rows极大）：
  • 原因：被驱动表的关联字段无索引（如t1 JOIN t2 ON t2.uid = t1.id，t2.uid无索引），导致被驱动表全表扫描。
• 排序无索引（Extra=Using filesort）：
  • 原因：ORDER BY字段无索引，或排序顺序与索引不一致（如索引是create_time ASC，但查询用ORDER BY create_time DESC且无降序索引）。
• 子查询嵌套（type=DEPENDENT SUBQUERY）：
  • 原因：关联子查询导致主表每行触发一次子查询（如WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.uid = t1.id)），无索引时性能骤降。

3. 慢查询的优化方向

分析出原因后，针对性优化：

1. 加索引：

• 为查询条件、JOIN 关联字段、排序字段建索引（如：WHERE+ORDER BY字段建联合索引，如idx_status_create_time (status, create_time)）。

2. 改写 SQL：

• 子查询改 JOIN（如关联子查询改写为INNER JOIN，减少嵌套循环）；
• 避免左模糊匹配（LIKE '%abc'改为LIKE 'abc%'，或用全文索引）；
• 大分页改范围查询（LIMIT 100000,10改为WHERE id > 100000 LIMIT 10）。

3. 控制数据量：

• 用WHERE提前过滤无效数据（如WHERE create_time > '2024-01-01'），减少扫描和排序行数。

4. 8.0 版本的优化

• 慢查询日志支持表存储：除文件外，可将日志写入mysql.slow_log表（log_output = TABLE），支持 SQL 查询分析（如按时间范围统计）。
• 日志内容更丰富：新增query_id（唯一标识）、rows_affected等字段，方便关联性能模式（Performance Schema）数据。
• 动态调整更灵活：long_query_time支持小数（如 0.5 秒），且修改后立即生效（无需重启连接）。

5. 总结

慢查询定位与分析的核心流程是：开启日志捕获 → 工具统计筛选 → EXPLAIN分析执行计划 → 针对性优化（索引 / 改写 SQL）。

实践中，需结合业务场景（如订单表慢查询多与user_id、create_time索引相关），优先解决 “高频 + 高耗时” 的慢查询。

06. 复杂SQL的拆分与改写

大表分页优化：延迟关联、书签分页。

MySQL 大表分页是业务中高频痛点（如订单列表、日志查询），普通LIMIT offset, size在offset较大时（如LIMIT 100000,10）性能极差：因 MySQL 需扫描前 10 万行再丢弃，仅取最后 10 行，IO 开销剧增。

复杂 SQL 拆分与改写的核心思路是减少无效扫描行数，常用优化手段包括延迟关联、书签分页。

1. 普通分页的性能瓶颈

普通分页 SQL 示例（订单表order，按create_time逆序）：

SELECT * FROM `order` ORDER BY create_time DESC LIMIT 100000, 10;

问题：当offset=100000时，MySQL 需先遍历索引找到第 10 万行的位置（即使有create_time索引），再回表读取 10 行数据，前 10 万行的扫描属于无效开销，数据量越大越慢。

2. 关键优化方法

1. 延迟关联（延迟回表）

概念：先通过覆盖索引获取目标数据的主键（或唯一键），再关联原表获取完整字段，避免 “早期回表” 带来的大量 IO。

原理：利用索引的 “覆盖查询” 特性（只查主键，无需回表）快速定位目标行，再通过主键关联原表取数，减少扫描和回表的数据量。

优化后 SQL 示例：

-- 子查询用索引获取主键（覆盖索引，无需回表）
SELECT o.* 
FROM `order` o 
JOIN (SELECT id FROM `order` ORDER BY create_time DESC LIMIT 100000, 10) tmp 
ON o.id = tmp.id;

• 优势：子查询仅扫描索引获取id（轻量），关联原表时仅取 10 行完整数据，性能比普通分页提升 5-10 倍（10 万 + offset 场景）。
• 适用场景：需展示完整字段、支持任意跳页（如第 100 页、第 200 页）的列表。

2. 书签分页（游标分页 / 键集分页）

概念：用 “上一页最后一条记录的唯一标识”（书签，如id或create_time+id）作为条件，替代offset直接定位下一页起点，完全避免无效扫描。

原理：利用主键 / 唯一索引的有序性，通过WHERE条件跳过前面数据，直接从书签位置取数。

优化后 SQL 示例：假设上一页最后一条记录的create_time='2024-01-01 10:00:00'、id=100000，下一页查询：

SELECT * 
FROM `order` 
WHERE create_time < '2024-01-01 10:00:00' 
   OR (create_time = '2024-01-01 10:00:00' AND id < 100000) 
ORDER BY create_time DESC, id DESC 
LIMIT 10;

• 关键：用create_time+id作为书签（create_time可能重复，需加id保证唯一性），通过索引直接定位，无offset开销。
• 优势：性能极致（无论翻多少页，扫描行数固定为LIMIT size）；
• 局限：不支持 “跳页”（如直接到第 100 页），仅适合 “上一页 / 下一页”“加载更多” 场景（APP / 移动端常用）。

3. 覆盖索引分页

覆盖索引分页：若查询字段（如id, order_no, create_time）全部包含在索引中，直接用索引查询，无需回表：

-- 索引`idx_create_time_order_no (create_time DESC, order_no, id)`覆盖所有查询字段
SELECT id, order_no, create_time 
FROM `order` 
ORDER BY create_time DESC 
LIMIT 100000, 10;

4. 限制最大 Offset

业务上禁止offset超过阈值（如 10000），引导用户通过筛选条件（如 “按时间范围筛选”）缩小数据范围，避免大 offset 查询。

5. 分表分页

超大规模表（亿级）需水平分表（如按create_time分表），分页时先定位分表，再在分表内分页，避免跨表扫描。

3. 总结

大表分页优化的核心是 “用索引定位替代无效扫描”：

• 需支持跳页选延迟关联，通过覆盖索引减少回表；
• 移动端 / 加载更多选书签分页，用唯一键直接定位；
• 极致性能选覆盖索引分页，避免回表开销。

07. JOIN优化

小表驱动大表、 避免 cross join。

1. JOIN 优化的核心逻辑

MySQL 中 JOIN 的底层实现以Nested Loop Join（嵌套循环连接）为主：外层循环遍历 “驱动表” 的数据，内层循环用驱动表的字段去 “被驱动表” 中匹配数据。。就像Java里的双层for循环。

优化的核心是减少外层循环次数 + 降低内层匹配成本，这也是 “小表驱动大表” 的原理基础。

2. 优化1：小表驱动大表

1. 原理

“小表” 指参与 JOIN 的数据集更小的表（而非物理表大小）。
用小表做驱动表（外层循环），大表做被驱动表（内层循环），可显著减少外层循环次数，从而降低整体 IO 和匹配开销。

• 举例：小表有 100 行，大表有 100 万行
  • 小表驱动大表：外层循环 100 次，内层每次匹配大表 → 总匹配 100 次；
  • 大表驱动小表：外层循环 100 万次，内层每次匹配小表 → 总匹配 100 万次；
  • 性能差异一目了然。

2. 实践判断与示例

• 如何判断 “小表”：
  • 通过EXPLAIN看rows字段（预估扫描行数），行数少的就是 “小表”；
  • 或业务逻辑中明确数据量更小的表（如字典表、配置表）。
• SQL 示例（用户表user是小表，订单表order是大表）：

-- 推荐：小表user驱动大表order（INNER JOIN中MySQL会自动优化，但LEFT JOIN需显式控制）
SELECT u.username, o.order_no 
FROM user u 
INNER JOIN `order` o ON u.id = o.user_id;

-- LEFT JOIN需注意：左表是驱动表，因此左表必须是小表
SELECT u.username, o.order_no 
FROM user u  -- 小表放左表（驱动表）
LEFT JOIN `order` o ON u.id = o.user_id;

3. 注意点

• INNER JOIN：MySQL 优化器会自动选择小表作为驱动表，无需手动调整；
• LEFT JOIN：驱动表固定为左表，因此必须将小表放在左表位置（否则会用大表驱动小表，性能暴跌）；
• RIGHT JOIN：驱动表固定为右表，需将小表放在右表位置（实际开发中建议用LEFT JOIN替代，更符合阅读习惯）。

3. 优化2：避免 CROSS JOIN（笛卡尔积）

1. CROSS JOIN 的危害

CROSS JOIN会返回两张表的笛卡尔积（行数 = 表 A 行数 × 表 B 行数），若表 A 有 1 万行、表 B 有 10 万行，结果会有 10 亿行，直接导致数据库 CPU/IO 打满、查询超时。

2. 常见触发场景

• 显式使用CROSS JOIN关键字且无ON条件；
• JOIN时遗漏ON关联条件（如SELECT * FROM A JOIN B）；
• WHERE条件无法过滤笛卡尔积（如关联字段值全为 NULL）。

3. 如何避免

• 任何JOIN必须加ON关联条件（即使是INNER JOIN）；
• 禁止显式使用无关联条件的CROSS JOIN；
• 若需关联但无直接字段，通过业务逻辑补充关联条件（如时间范围、状态过滤）。

4. 优化补充

1. 被驱动表的关联字段必须加索引
被驱动表的ON字段（如order.user_id）需建索引，否则内层循环会全表扫描，即使小表驱动大表也会很慢。

2. 避免SELECT *
只查询需要的字段，减少数据传输量（尤其大表有大字段如TEXT时）。

3. 提前过滤数据
用WHERE先过滤驱动表和被驱动表的无效数据（如WHERE o.status=1），减少参与 JOIN 的行数。

5. 总结

MySQL JOIN 优化的核心是：

• 小表驱动大表：利用嵌套循环的特性减少外层循环次数，LEFT JOIN需手动控制驱动表；
• 杜绝笛卡尔积：JOIN必须加ON条件，避免CROSS JOIN；
• 索引兜底：被驱动表的关联字段加索引，是 JOIN 性能的基础保障。

08. count的性能差异

count(1)、count(*)、count(字段)

1. count 的本质

count 的本质：统计 “非 NULL 行数”。

count()是聚合函数，核心作用是统计查询结果集中符合条件的 “非 NULL 行数”，不同参数的差异在于 “统计范围” 和 “是否需要判断 NULL”，这直接决定性能。

2. 三种 count 的区别与性能对比

1. count(*)

统计所有行数（含 NULL）。

逻辑：专门用于统计表 / 结果集的总行数。
MySQL 对其有特殊优化：会优先选择最小的非聚集索引（覆盖索引）扫描，若没有则用聚集索引，避免全表扫描。

特点：包含 NULL 值（因为不判断字段，只统计行数），性能最优。

2. count(1)

统计所有行数（占位符）。

逻辑：用常量1作为占位符，统计所有行数（无论字段是否为 NULL）。
MySQL 优化器会将其与count(*)视为等价，底层执行计划完全相同。

特点：性能与count(*)几乎无差异，属于 “语法糖”。

3. count(字段)

统计字段非 NULL 的行数。

逻辑：需逐行检查该字段是否为 NULL，仅统计非 NULL 的行数。性能分两种情况：

• 字段是索引字段：用索引扫描（比全表快），但需判断 NULL；
• 字段是非索引字段：全表扫描 + 判断 NULL，性能最差。

特点：性能低于count(*)/count(1)，且结果可能不等于总行数（因为字段可能为 NULL）。

3. 性能排序

从快到慢：count(*) ≈ count(1) > ount(索引字段) > count(非索引字段)

关键原因：

• count(*)/count(1)无需判断字段 NULL，且 MySQL 会选最优索引；
• count(字段)需额外判断 NULL，非索引字段还需全表扫描。

4. 实践建议

• 统计总行数：优先用count(*)（语义最清晰，MySQL 优化最好），而非count(1)或count(主键)；
• 统计字段非 NULL 行数：若需此逻辑，确保字段加索引（如count(user_name)需user_name有索引）；
• 避免误区：
  • 认为count(主键)更快：实际count(*)会选更小的索引，比主键索引（聚集索引）扫描更快；
  • 认为count(1)比count(*)快：MySQL 优化后两者无区别，count(*)更符合语义。

5. 总结

• 核心差异：count(*)/count(1)统计总行数（含 NULL），性能最优；count(字段)统计非 NULL 行数，性能较差；
• 实践选择：统计总数用count(*)，统计非 NULL 字段用count(索引字段)；
• 优化关键：利用 MySQL 对count(*)的索引优化，避免count(非索引字段)的全表扫描。

6. 扩展

统计user表email非null的总行数，email是普通索引，是SELECT COUNT(email) FROM user快，还是SELECT COUNT(*) FROM user WHERE email IS NOT NULL快？

答：基本相同，但是不推荐使用后者，推荐使用COUNT(email)。因为这个全程是索引，虽然需要索引扫描，但是后者使用了WHERE条件，同样也是需要进行索引扫描，同时还多出了一步条件过滤。同时COUNT(email)语义更明确。

09. IN和JOIN哪个快

先明确两者的底层逻辑

• IN：属于 “子查询 / 值列表匹配”，本质是将子查询结果加载到内存后做匹配（或直接匹配值列表），适合 “单字段匹配” 场景；
• JOIN：属于 “多表关联”，底层以Nested Loop Join为主（小表驱动大表），通过关联字段索引直接匹配，适合 “多字段关联取值” 场景。

• IN的优势：语法简单，适合 “单字段匹配” 的简单场景；
• JOIN的优势：支持多字段关联，大结果集下利用索引更高效，是复杂关联的首选。

10. MySQL的函数

MySQL 函数按功能可分为内置函数（系统提供，重点）和自定义函数（用户编写）。

1. 核心内置函数

1. 字符串函数

处理文本数据。

用于字符串拼接、截取、替换等，是业务中最常用的函数类别。

• CONCAT(str1, str2,...)：字符串拼接（如：拼接用户姓名和手机号：CONCAT(username, '-', mobile)）；
• SUBSTRING(str, pos, len)：截取子串（如：取手机号后 4 位：SUBSTRING(mobile, 8, 4)）；
• REPLACE(str, old, new)：替换字符串（如：清理内容中的特殊字符：REPLACE(content, '<', '')）；
• LENGTH(str)：获取字符串长度（如：校验用户名长度：WHERE LENGTH(username) > 6）。

实践：用户信息格式化、文本内容清洗场景高频使用。

2. 数值函数

处理数字运算。

用于数值计算、取整、进制转换等。

• ROUND(num, n)：四舍五入（如：金额保留 2 位小数：ROUND(amount, 2)）；
• ABS(num)：取绝对值（如：计算差值的绝对值：ABS(score1 - score2)）；
• CEIL(num)/FLOOR(num)：向上 / 向下取整（如：订单数量向上取整：CEIL(total/10)）；
• SUM(num)/AVG(num)：求和 / 平均值（报表统计核心函数）。

实践：金额计算、数据统计场景必备。

3. 日期时间函数

处理时间数据。

用于时间获取、格式化、差值计算，是业务中仅次于字符串函数的高频函数。

• NOW()/CURDATE()：获取当前时间（含时分秒）/ 当前日期（仅年月日）；
• DATE_FORMAT(date, fmt)：时间格式化（如：订单时间转字符串：DATE_FORMAT(create_time, '%Y-%m-%d %H:%i:%s')）；
• DATEDIFF(date1, date2)：计算日期差值（如：用户注册天数：DATEDIFF(NOW(), register_time)）；
• DATE_ADD(date, INTERVAL expr unit)：时间增减（如：计算 7 天后的日期：DATE_ADD(NOW(), INTERVAL 7 DAY)）。

实践：订单时间筛选、会员有效期计算、报表时间维度统计。

4. 聚合函数

数据统计分析。

用于对多行数据进行聚合计算，需结合GROUP BY使用。

• COUNT(*)：统计总行数（用户数、订单数统计）；
• SUM(field)：求和（销售额、销量统计）；
• MAX(field)/MIN(field)：最大值 / 最小值（最高订单金额、最早注册时间）；
• GROUP_CONCAT(field)：分组拼接字符串（如：查询用户的所有订单号：GROUP_CONCAT(order_no)）。

实践：报表系统、数据看板的核心函数。

5. 条件函数

逻辑判断。

用于实现 SQL 中的条件分支，替代复杂的WHERE判断。

• IF(condition, val1, val2)：简单条件判断（如：判断用户状态：IF(status=1, '正常', '禁用')）；
• CASE WHEN：复杂条件分支（如：订单状态映射：CASE status WHEN 0 THEN '待支付' WHEN 1 THEN '已支付' ELSE '已取消' END）；
• IFNULL(val, default)：NULL 值替换（如：用户昵称为空时显示默认值：IFNULL(nickname, '游客')）。

实践：查询结果格式化、动态状态展示。

6. 其他常用函数

• UUID()：生成唯一标识符（分布式场景临时 ID 生成）；
• INSTR(str, substr)：查找子串位置（如：判断内容是否含关键词：INSTR(content, 'MySQL') > 0）；
• CAST(val AS type)：类型转换（如：字符串转数字：CAST(score AS UNSIGNED)）。

2. 面试高频考点

函数使用的性能陷阱。

1. 函数操作索引字段会导致索引失效

这是面试必问点！若对索引字段使用函数，MySQL 无法使用索引，会触发全表扫描。

• 反例：WHERE DATE(create_time) = '2024-01-01'（对索引字段create_time用DATE()函数，索引失效）；
• 正例：WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02'（直接匹配字段范围，利用索引）。这个之所以能利用索引，是因为没有使用函数破坏索引的有序性，同时MySQL将给出的查询条件值'2024-01-01'和'2024-01-02'隐式的转换为了DATETIME类型，从而避免了将整个表的create_time字段转成其他类型，保证了原索引的可用性。

2. 聚合函数的NULL处理

• COUNT(field)会忽略 NULL 值，COUNT(*)统计所有行（含 NULL）；
• SUM(field)中 NULL 值会被视为 0，但建议用IFNULL(field, 0)显式处理（避免语义歧义）。

3. 自定义函数的慎用场景

• 自定义函数（CREATE FUNCTION）可实现复杂逻辑，但执行效率低（无法并行执行），且可能导致锁表，高并发场景建议用存储过程或应用层代码替代。

3. 总结

MySQL 函数是提升 SQL 灵活性的核心工具。

• 分类记忆：重点掌握字符串、日期、聚合、条件函数的常用用法；
• 性能陷阱：索引字段避免使用函数，否则索引失效；
• 实践优先：简单逻辑用内置函数，复杂逻辑优先应用层处理（避免 SQL 臃肿）。

回目录： 《面试笔记：MySQL 相关目录》
上一篇： 《面试笔记：MySQL 相关02 – 索引》