SQL 优化
为什么优化很重要
查询优化是应用程序性能的核心：
- 用户体验：慢查 询直接导致页面加载缓慢甚至挂起。
- 系统负载：未经优化的查询会消耗过度的 CPU、I/O 和内存资源。
- 可扩展性：糟糕的 SQL 会迅速触及单机瓶颈，限制系统并发能力。
- 成本控制：低效查询意味着需要采购更昂贵的服务器硬件。
现实影响：
- 为
orders表的user_id列添加一个索引，能让查询速度提升 1000 倍。 - 采用“延迟关联”优化深分页，能将 10 秒的延迟降至 100 毫秒。
EXPLAIN 执行计划分析
什么是 EXPLAIN？
EXPLAIN 是分析 MySQL 如何执行 SQL 的核心工具，它能揭示：
- 访问类型：是全表扫描还是索引查找？
- 索引使用：具体使用了哪个索引？
- 表关联顺序：多表联查时的先后路径。
- 预估扫描行数：大概需要读取多少行数据。
核心列详解
1. type (访问类型) —— 最重要的指标
从优到劣排列：
- system/const：表中仅有一行匹配（如主键查找），速度极快。
- eq_ref：唯一索引关联。
- ref：非唯一索引匹配。
- range：索引范围扫描(如使用
>,<,BETWEEN)。 - index：全索引扫描（通常是为了获取覆盖索引）。
- ALL：全表扫描 ❌。应尽量避免，至少优化至
range级别。
2. key (实际使用的索引)
如果此列为 NULL，说明没有使用索引。
3. rows (预估扫描行数)
数值越小代表查询效率越高。
4. Extra (额外信息)
- Using index：✅ 最佳。使用了覆盖索引，无需回表 。
- Using where：正常，说明在 Server 层执行了过滤。
- Using index condition：✅ 索引下推 (ICP) 优化。
- Using filesort：❌ 警告。说明排序没有利用索引，在大数据量下非常耗时。
- Using temporary：❌ 严重警告。说明使用了临时表，通常出现在复杂的 GROUP BY 或 DISTINCT 查询中。
核心优化技巧
1. 消除全表扫描
- 重写函数查询：不要在索引列上使用函数（如
WHERE DATE(time) = ...），应改为范围查询。 - 避免隐式转换：确保查询参数类型与列定义一致。
- 移除前导通配符：
LIKE '%key'无法使用索引，应改为LIKE 'key%'。
2. 深分页优化 (Deep Pagination)
问题：LIMIT 1000000, 10 会导致 MySQL 扫描并丢弃前 100 万行，效率极低。
方案 A：延迟关联 (Delayed Association) 先通过覆盖索引仅查出主键 ID，再根 据 ID 关联原表获取整行数据。
SELECT o.* FROM orders o
INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 10) tmp ON o.id = tmp.id;
方案 B：寻找锚点 (Seek Method) 记录上一页最后一条记录的 ID，下一页从该 ID 之后开始找。
SELECT * FROM orders WHERE id > last_seen_id ORDER BY id LIMIT 10;
3. JOIN 优化
- 小表驱动大表：确保 JOIN 条件的右侧列有索引。
- STRAIGHT_JOIN：在优化器判断失误时，强制指定驱动顺序。
4. COUNT 优化
- COUNT(*) vs COUNT(1)：在现代 MySQL 中性能完全一致，均会选择最小的索引树统计。
- 近似统计：若对精度要求不高，可从
information_schema或SHOW TABLE STATUS获取预估行数。 - 实时计数：对于超大规模表，建议单独维护一个计数表（利用 Redis 或专用表）。
Schema 设计最佳实践
数据类型选型原则
- 越小越好：尽量使用
TINYINT、SMALLINT代替INT。 - 简单胜过复杂：使用
INT存储 IP 地址（inet_aton），使用TIMESTAMP代替DATETIME节省 4 字节。 - 避免 NULL：尽可能设置列为
NOT NULL并提供默认值，这有助于优化器更好地利用索引。
范式与反范式 (Normalization vs Denormalization)
- 范式 (3NF)：减少数据冗余，保证一致性，适合写频繁的场景。
- 反范式：通过增加冗余字段（如在订单表存用户名）来减少 JOIN 操作，适合读压力巨大的场景。
面试高频题
Q1: 怎么定位并优化慢查询？
回答：
- 开启慢查询日志 (
slow_query_log) 捕捉耗时超过阈值的 SQL。 - 使用
EXPLAIN分析执行计划，观察type是否为 ALL，以及是否命中了预期索引。 - 检查是否存在索引失效、回表次数过多或不合理的深分页。
- 针对性地通过添加索引、SQL 改写或引入缓存进行优化。
Q2: 为什么 SELECT * 被视为反模式？
回答：
- 网络开销：传输多余的列会浪费带宽。
- I/O 压力：无法利用覆盖索引优化，必须执行回表操作读取所有字段。
- 扩展性风险：当表结构变更（如增加大字段）时，可能导致应用 OOM。