> ## Content Index
> Fetch the complete content index at: https://blog.vercanti.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# MySQL 高级优化
- URL: https://blog.vercanti.com/mysql-gao-ji-you-hua/
- Published: 2026-08-28T14:35:37.000Z
- Updated: 2026-08-28T14:59:11.000Z
- Description: 生产环境要求：至少达到 range，核心查询要求 ref 及以上。 JSON 输出中重点关注： 输出示例（树状格式）： actual time=开始..结束 单位为毫秒，loops 为该节点执行次数。 联合索引 INDEX idx_abc (a, b, c) 的命中规则： 规律：从索引最左列开始，遇到范围查询（>、<、BETWEEN、LIKE 'prefix%'）则后续列失效。 查询所需的列全部包含在索引中，无需回表读取行数据，EXPLAIN 的 Extra 显示 Using index。 设计覆盖索引的思路：将 SELECT 列表中频繁出现的列追加到联
- Author: yellowdog
- Tags: 数据库

> 官方文档：<https://dev.mysql.com/doc/refman/8.0/en/optimization.html>  
> 最后更新：2026-04-11

---

## 一、执行计划深度解读

### EXPLAIN 基本用法

```sql
EXPLAIN SELECT * FROM orders WHERE user_id = 100 AND status = 1;

```

### EXPLAIN 各列含义

| 列名             | 说明                              |
| -------------- | ------------------------------- |
| id             | 查询序号。id 相同表示同一级；id 越大优先级越高，越先执行 |
| select\_type   | 查询类型，见下表                        |
| table          | 当前行访问的表名（或别名、派生表标识）             |
| partitions     | 命中的分区（非分区表为 NULL）               |
| type           | 访问类型，性能关键列，见下表                  |
| possible\_keys | 优化器认为可用的索引列表                    |
| key            | 实际选用的索引；NULL 表示全表扫描             |
| key\_len       | 使用索引的字节数，可推算命中了联合索引的哪几列         |
| ref            | 与索引比较的列或常量                      |
| rows           | 估算需要扫描的行数，不是精确值                 |
| filtered       | 条件过滤后剩余行的百分比估算                  |
| Extra          | 附加信息，见下表                        |

### select\_type 值说明

| 值                  | 说明                   |
| ------------------ | -------------------- |
| SIMPLE             | 简单查询，无子查询或 UNION     |
| PRIMARY            | 最外层查询                |
| SUBQUERY           | SELECT 列表中的子查询       |
| DERIVED            | FROM 子句中的派生表（子查询）    |
| UNION              | UNION 中第二个及后续 SELECT |
| UNION RESULT       | UNION 结果集            |
| DEPENDENT SUBQUERY | 依赖外部查询的子查询，性能较差      |

### type 访问类型（性能从高到低）

| 值             | 说明                    | 典型场景                         |
| ------------- | --------------------- | ---------------------------- |
| system        | 表只有一行，const 的特例       | 系统表                          |
| const         | 主键或唯一索引等值查询，最多一行      | WHERE id = 1                 |
| eq\_ref       | 联表时被驱动表用主键或唯一索引匹配     | JOIN ON 主键                   |
| ref           | 非唯一索引等值查询，可能返回多行      | WHERE user\_id = ?           |
| fulltext      | 全文索引扫描                | MATCH ... AGAINST            |
| ref\_or\_null | ref 的变体，额外处理 NULL 值   | WHERE col = ? OR col IS NULL |
| range         | 索引范围扫描                | WHERE id BETWEEN 1 AND 100   |
| index         | 全索引扫描（遍历索引树），比 ALL 稍好 | 覆盖索引但无条件过滤                   |
| ALL           | 全表扫描，性能最差             | 无索引可用                        |

生产环境要求：至少达到 `range`，核心查询要求 `ref` 及以上。

### Extra 字段重要值解读

| 值                            | 含义                           | 是否需要优化         |
| ---------------------------- | ---------------------------- | -------------- |
| Using index                  | 覆盖索引，无需回表                    | 良好，无需优化        |
| Using where                  | 从存储引擎取回行后在 Server 层过滤        | 视情况，可考虑索引优化    |
| Using index condition        | 索引下推（ICP），部分过滤在引擎层完成         | 良好             |
| Using filesort               | 无法用索引排序，在内存/磁盘排序             | 需要优化，添加合适索引    |
| Using temporary              | 使用临时表（GROUP BY、ORDER BY 不同列） | 需要优化，代价较高      |
| Using join buffer            | JOIN 时被驱动表无索引，使用 join buffer | 需要为 JOIN 字段加索引 |
| Impossible WHERE             | WHERE 条件恒为 false，不会查询        | 检查业务逻辑         |
| Select tables optimized away | 仅用索引即可得出聚合结果（如 MIN/MAX）      | 良好             |

### EXPLAIN FORMAT=JSON

```sql
-- 输出 JSON 格式，包含 cost 估算信息
EXPLAIN FORMAT=JSON
SELECT o.id, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 1;

```

JSON 输出中重点关注：

- `cost_info.query_cost`：整体代价估算
- `rows_examined_per_scan`：每次扫描的行数
- `using_index`：是否使用覆盖索引

### EXPLAIN ANALYZE（MySQL 8.0+）

```sql
-- 实际执行并返回每步的真实耗时和行数
EXPLAIN ANALYZE
SELECT * FROM orders WHERE created_at > '2026-01-01';

```

输出示例（树状格式）：

```
-> Filter: (orders.created_at > '2026-01-01')  (cost=102.50 rows=312) (actual time=0.045..1.234 rows=300 loops=1)
    -> Index range scan on orders using idx_created_at  (cost=102.50 rows=312) (actual time=0.040..0.980 rows=300 loops=1)

```

`actual time=开始..结束` 单位为毫秒，`loops` 为该节点执行次数。

---

## 二、索引优化

### 最左前缀原则

联合索引 `INDEX idx_abc (a, b, c)` 的命中规则：

| 查询条件                            | 命中情况        | 说明              |
| ------------------------------- | ----------- | --------------- |
| WHERE a = 1                     | 命中 a        | 正常命中            |
| WHERE a = 1 AND b = 2           | 命中 a, b     | 正常命中            |
| WHERE a = 1 AND b = 2 AND c = 3 | 命中 a, b, c  | 全命中             |
| WHERE a = 1 AND c = 3           | 命中 a        | b 断开，c 无法用索引    |
| WHERE b = 2                     | 未命中         | 不满足最左前缀         |
| WHERE b = 2 AND c = 3           | 未命中         | 不满足最左前缀         |
| WHERE a = 1 AND b > 2 AND c = 3 | 命中 a, b     | b 是范围查询，c 无法用索引 |
| WHERE a = 1 ORDER BY b          | 命中 a，b 用于排序 | 可避免 filesort    |

规律：从索引最左列开始，遇到范围查询（`>`、`<`、`BETWEEN`、`LIKE 'prefix%'`）则后续列失效。

### 覆盖索引（Covering Index）

查询所需的列全部包含在索引中，无需回表读取行数据，EXPLAIN 的 Extra 显示 `Using index`。

```sql
-- 假设有索引 INDEX idx_user_status (user_id, status, created_at)
-- 以下查询可完全走覆盖索引，不回表
SELECT user_id, status, created_at
FROM orders
WHERE user_id = 100;

```

设计覆盖索引的思路：将 SELECT 列表中频繁出现的列追加到联合索引末尾。注意不要把大字段（TEXT、BLOB）放入索引。

### 索引下推（ICP，Index Condition Pushdown）

MySQL 5.6+ 默认开启。在存储引擎层利用索引中的列提前过滤，减少回表次数。

```sql
-- 索引 INDEX idx_name_age (last_name, age)
-- 未开启 ICP：引擎先用 last_name 找到所有行，回表，Server 层再过滤 age
-- 开启 ICP：引擎用 last_name 定位后，在索引中直接判断 age 条件，减少回表
SELECT * FROM users WHERE last_name LIKE 'Zhang%' AND age > 25;

```

EXPLAIN 的 Extra 显示 `Using index condition` 即表示 ICP 生效。

```sql
-- 手动关闭/开启
SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';

```

### 联合索引字段顺序选择原则

| 原则                | 说明                                             |
| ----------------- | ---------------------------------------------- |
| 等值查询列放前，范围查询列放后   | 范围查询会截断后续列的索引利用                                |
| 区分度高的列放前          | 区分度 = COUNT(DISTINCT col) / COUNT(\*)，越接近 1 越好 |
| ORDER BY 列放在等值列之后 | 可避免 filesort                                   |
| 覆盖索引优先            | 高频查询的 SELECT 列全部纳入索引                           |

```sql
-- 查询区分度
SELECT
    COUNT(DISTINCT status) / COUNT(*) AS status_selectivity,
    COUNT(DISTINCT user_id) / COUNT(*) AS user_id_selectivity
FROM orders;
-- user_id 区分度远高于 status，应放在前面
-- 推荐：INDEX (user_id, status) 而非 INDEX (status, user_id)

```

### 索引失效场景

| 场景                  | 示例                                         | 原因               |
| ------------------- | ------------------------------------------ | ---------------- |
| 对索引列使用函数            | WHERE YEAR(created\_at) = 2026             | 函数破坏索引 B+ 树有序性   |
| 隐式类型转换              | WHERE phone = 13812345678（phone 为 VARCHAR） | MySQL 自动转换相当于加函数 |
| 隐式字符集转换             | JOIN 两表字段字符集不同                             | 引发隐式转换           |
| != 或 <>             | WHERE status != 0                          | 优化器通常选择全表扫描      |
| OR 连接非索引列           | WHERE id = 1 OR name = 'a'（name 无索引）       | OR 两侧必须都有索引才能合并  |
| LIKE 前缀通配           | WHERE name LIKE '%zhang'                   | 前缀不确定，无法走 B+ 树   |
| NOT IN / NOT EXISTS | WHERE id NOT IN (1,2,3)                    | 通常全表扫描           |
| 索引列参与计算             | WHERE id + 1 = 100                         | 等同于对列加函数         |

修复方案：

```sql
-- 函数 -> 改写为范围查询
-- 原：WHERE YEAR(created_at) = 2026
-- 改：
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'

-- 隐式类型转换 -> 显式转换或统一类型
WHERE phone = '13812345678'

```

### FORCE INDEX 与 IGNORE INDEX

```sql
-- 强制使用指定索引（优化器选错索引时使用）
SELECT * FROM orders FORCE INDEX (idx_user_id)
WHERE user_id = 100 ORDER BY created_at DESC;

-- 忽略某个索引（避免优化器使用不合适的索引）
SELECT * FROM orders IGNORE INDEX (idx_status)
WHERE user_id = 100;

-- 查看表上所有索引
SHOW INDEX FROM orders;

```

---

## 三、慢查询分析

### 开启慢查询日志

```sql
-- 查看当前配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 动态开启（重启后失效，生产建议写入配置文件）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;           -- 超过 1 秒记录
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL log_queries_not_using_indexes = ON;  -- 未使用索引的查询也记录

```

my.cnf 配置：

```ini
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 10  -- 每分钟最多记录 10 条无索引查询，避免日志膨胀

```

### mysqldumpslow 分析

| 参数          | 说明               |
| ----------- | ---------------- |
| \-s t       | 按总执行时间排序         |
| \-s at      | 按平均执行时间排序        |
| \-s c       | 按执行次数排序          |
| \-s l       | 按锁等待时间排序         |
| \-t N       | 只显示前 N 条         |
| \-g pattern | 过滤匹配 pattern 的语句 |

```bash
# 按总耗时取前 10 条
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 过滤含 user 的语句，按次数排序
mysqldumpslow -s c -g "user" /var/log/mysql/slow.log

```

### pt-query-digest

Percona Toolkit 中功能最强的慢查询分析工具，支持按指纹分类聚合。

```bash
# 安装
apt install percona-toolkit

# 分析慢查询日志
pt-query-digest /var/log/mysql/slow.log

# 只显示最慢的 5 类查询
pt-query-digest --limit 5 /var/log/mysql/slow.log

# 分析 tcpdump 抓包
pt-query-digest --type tcpdump traffic.cap

# 将结果写入数据库便于对比
pt-query-digest \
    --review h=127.0.0.1,D=percona,t=query_review \
    --history h=127.0.0.1,D=percona,t=query_history \
    /var/log/mysql/slow.log

```

### performance\_schema 查询分析

```sql
-- 开启 performance_schema（MySQL 5.6+ 默认开启）
SHOW VARIABLES LIKE 'performance_schema';

-- 查看执行次数最多的 TOP 10 语句
SELECT
    digest_text,
    count_star,
    avg_timer_wait / 1e9 AS avg_ms,
    sum_timer_wait / 1e9 AS total_ms,
    sum_rows_examined,
    sum_rows_sent
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC
LIMIT 10;

-- 查看某条语句的详细等待事件
SELECT event_name, count_star, avg_timer_wait / 1e9 AS avg_ms
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE event_name NOT LIKE 'idle%'
ORDER BY sum_timer_wait DESC
LIMIT 20;

-- sys schema 提供更友好的视图（MySQL 5.7.7+）
SELECT * FROM sys.statements_with_full_table_scans LIMIT 10;
SELECT * FROM sys.statements_with_sorting LIMIT 10;
SELECT * FROM sys.statements_with_temp_tables LIMIT 10;

```

---

## 四、锁与事务优化

### 锁的类型

| 锁类型                          | 粒度       | 说明                        | 触发场景                    |
| ---------------------------- | -------- | ------------------------- | ----------------------- |
| 表锁（Table Lock）               | 整张表      | MyISAM 默认锁；InnoDB DDL 时使用 | ALTER TABLE、LOCK TABLES |
| 行锁（Record Lock）              | 单行       | InnoDB 默认，高并发性能好          | 等值查询命中索引                |
| 间隙锁（Gap Lock）                | 索引间隙     | 锁定两个索引值之间的区间，防止幻读         | RR 隔离级别下范围查询            |
| 临键锁（Next-Key Lock）           | 行 + 左侧间隙 | 行锁 + 间隙锁的组合，InnoDB 默认     | RR 隔离级别下范围查询            |
| 意向锁（Intention Lock）          | 表级       | 表明某行已被锁定的意图，兼容性检查用        | 自动添加                    |
| 插入意向锁（Insert Intention Lock） | 间隙       | INSERT 时获取，多个 INSERT 不互斥  | INSERT 操作               |

间隙锁只在 RR（REPEATABLE READ）隔离级别下存在，降级为 RC（READ COMMITTED）可消除间隙锁，但会引入幻读问题。

```sql
-- 查看当前事务隔离级别
SELECT @@transaction_isolation;

-- 修改为 READ COMMITTED（减少间隙锁）
SET SESSION transaction_isolation = 'READ-COMMITTED';

```

### 死锁检测与解决

```sql
-- 查看最近一次死锁详情
SHOW ENGINE INNODB STATUS\G

```

重点关注 `LATEST DETECTED DEADLOCK` 段落，格式说明：

```
*** (1) TRANSACTION:
TRANSACTION 123456, ACTIVE 5 sec starting index read
-- 事务 1 的 SQL 语句
*** (1) HOLDS THE LOCK(S):
-- 事务 1 持有的锁
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
-- 事务 1 等待的锁

*** (2) TRANSACTION:
-- 事务 2 信息（结构同上）

*** WE ROLL BACK TRANSACTION (1)
-- InnoDB 选择回滚代价较小的事务

```

```sql
-- 查看当前所有锁等待（MySQL 8.0）
SELECT
    r.trx_id AS waiting_trx_id,
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx_id,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query AS blocking_query
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

-- 强制终止阻塞事务
KILL <blocking_thread_id>;

```

死锁预防原则：

1. 多个事务访问多张表时，保持相同的加锁顺序
2. 减少事务粒度，尽快提交
3. 使用 `SELECT ... FOR UPDATE` 时加 `NOWAIT` 或 `SKIP LOCKED`

### 长事务危害与排查

长事务会导致：undo log 无法回收（磁盘膨胀）、锁长时间持有（阻塞其他事务）、主从延迟加大。

```sql
-- 查看当前运行超过 60 秒的事务
SELECT
    trx_id,
    trx_started,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_sec,
    trx_mysql_thread_id,
    trx_query,
    trx_rows_locked,
    trx_rows_modified
FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60
ORDER BY duration_sec DESC;

-- 设置最大事务执行时间（MySQL 5.7.4+）
SET GLOBAL innodb_trx_rollback_on_timeout = ON;
SET GLOBAL innodb_lock_wait_timeout = 10;  -- 秒

```

应用层规范：

- 不在事务中调用外部 HTTP 接口
- 不在事务中执行复杂的业务逻辑循环
- 框架层配置事务超时（如 Spring `@Transactional(timeout=30)`）

### 锁等待超时配置

| 参数                          | 默认值         | 说明                 |
| --------------------------- | ----------- | ------------------ |
| innodb\_lock\_wait\_timeout | 50（秒）       | 行锁等待超时，超时后当前语句报错回滚 |
| lock\_wait\_timeout         | 31536000（秒） | MDL 锁等待超时          |
| innodb\_deadlock\_detect    | ON          | 死锁自动检测，关闭后死锁依赖超时机制 |

```sql
-- 生产建议：降低锁等待超时，快速失败
SET GLOBAL innodb_lock_wait_timeout = 10;

```

---

## 五、Buffer Pool 与 IO 优化

### innodb\_buffer\_pool\_size

Buffer Pool 是 InnoDB 最重要的内存区域，缓存数据页和索引页。

| 场景        | 建议值                                           |
| --------- | --------------------------------------------- |
| 专用数据库服务器  | 物理内存的 70%\~80%                                |
| 与应用共用服务器  | 物理内存的 40%\~50%                                |
| 内存 >= 4GB | 设置多个 pool 实例（innodb\_buffer\_pool\_instances） |

```sql
-- 查看当前配置
SHOW VARIABLES LIKE 'innodb_buffer_pool%';

-- 查看 Buffer Pool 命中率（应 > 99%）
SELECT
    (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100
    AS buffer_pool_hit_rate
FROM (
    SELECT
        VARIABLE_VALUE AS Innodb_buffer_pool_reads
    FROM performance_schema.global_status
    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
) a,
(
    SELECT
        VARIABLE_VALUE AS Innodb_buffer_pool_read_requests
    FROM performance_schema.global_status
    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
) b;

```

```ini
# my.cnf 配置示例（16GB 内存服务器）
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 8      -- 每个实例约 1.5GB
innodb_buffer_pool_dump_at_shutdown = ON  -- 关机时持久化热点页列表
innodb_buffer_pool_load_at_startup = ON   -- 启动时预热，避免冷启动

```

### innodb\_flush\_log\_at\_trx\_commit

控制 redo log 的刷盘策略，直接影响性能与数据安全的权衡：

| 值 | 行为               | 数据安全                | 性能 | 适用场景               |
| - | ---------------- | ------------------- | -- | ------------------ |
| 0 | 每秒刷盘一次，事务提交不主动刷  | 最低（宕机丢失 1 秒数据）      | 最高 | 不建议用于生产            |
| 1 | 每次提交都刷盘（fsync）   | 最高（完全符合 ACID）       | 较低 | 金融、订单等对数据完整性要求高的场景 |
| 2 | 每次提交写 OS 缓存，每秒刷盘 | 中等（OS 崩溃丢数据，进程崩溃安全） | 中等 | 可接受少量数据丢失的高性能场景    |

```sql
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

```

```ini
# my.cnf
innodb_flush_log_at_trx_commit = 1  -- 生产推荐
sync_binlog = 1                     -- 与上面配合，保证 binlog 也每次刷盘

```

---

## 六、最佳实践

### 查询优化检查清单

1. EXPLAIN 确认 type 不是 ALL，Extra 无 Using filesort / Using temporary
2. 确认 key 列使用了预期的索引
3. rows 列的值合理（不应扫描全表行数）
4. 对高频查询考虑覆盖索引
5. 慢查询日志持续监控，阈值设置为 1 秒

### 索引维护

```sql
-- 查看索引使用情况（performance_schema）
SELECT
    object_schema,
    object_name,
    index_name,
    count_read,
    count_write
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'your_db'
ORDER BY count_read DESC;

-- 找出未使用的索引（count_read = 0 且存在较长时间）
SELECT object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'your_db'
  AND index_name IS NOT NULL
  AND count_star = 0
ORDER BY object_name;

-- 重建索引（消除碎片）
ALTER TABLE orders ENGINE = InnoDB;
-- 或（MySQL 5.6+ 在线操作）
ALTER TABLE orders DROP INDEX idx_old, ADD INDEX idx_new (col1, col2), ALGORITHM=INPLACE, LOCK=NONE;

```

---

## 七、踩坑与注意事项

### 分页大偏移性能问题

```sql
-- 坏写法：LIMIT 100000, 20 需要扫描 100020 行，只返回 20 行
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;

-- 方案一：游标分页（适合顺序翻页）
-- 记录上一页最后一条记录的 id
SELECT * FROM orders WHERE id > 100050 ORDER BY id LIMIT 20;

-- 方案二：延迟关联（先查主键，再 JOIN 回原表）
SELECT o.*
FROM orders o
JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 100000, 20
) t ON o.id = t.id;

```

游标分页的限制：只能顺序翻页，不能跳页；适合下拉加载、滚动分页等场景。

### COUNT(\*) vs COUNT(1) vs COUNT(col)

| 写法         | 说明                          | 性能           |
| ---------- | --------------------------- | ------------ |
| COUNT(\*)  | 统计所有行（包含 NULL），MySQL 对此专门优化 | 最优，推荐        |
| COUNT(1)   | 等价于 COUNT(\*)，MySQL 内部处理相同  | 等同 COUNT(\*) |
| COUNT(col) | 统计 col 非 NULL 的行数，语义不同      | 稍慢（需判断 NULL） |

结论：统计总行数始终用 `COUNT(*)`，仅当需要排除 NULL 时用 `COUNT(col)`。

InnoDB 的 `COUNT(*)` 没有 MyISAM 那样的计数器，必须扫描索引树。对超大表的频繁 COUNT，可以：

- 使用单独的计数表维护总数
- 使用 Redis 缓存计数

### 其他常见坑

```sql
-- 坑 1：UPDATE/DELETE 忘加 WHERE，全表修改
-- 防护：SET SQL_SAFE_UPDATES = 1

-- 坑 2：IN 子查询返回 NULL 导致整个结果为空
-- NOT IN (1, 2, NULL) 永远返回空结果，因为 x != NULL 无法确定
-- 改用 NOT EXISTS 或确保子查询结果无 NULL

-- 坑 3：JOIN 两张大表不走索引
-- 检查 JOIN 字段的数据类型是否一致、字符集是否一致

-- 坑 4：ORDER BY RAND() 极慢
-- 原因：对全表每行计算随机数，再排序
-- 改写：
SELECT * FROM users
WHERE id >= (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM users)))
ORDER BY id
LIMIT 1;

```

---

## 最佳实践

**在修改查询前先用 EXPLAIN 分析，确认命中了索引**：养成先 `EXPLAIN SELECT ...` 的习惯，确认 `type` 不是 `ALL`（全表扫描），`key` 列显示了使用的索引，再评估是否需要优化。

**复合索引按选择性从高到低、按查询条件顺序排列**：选择性高的列（值种类多）放在最左，利用"最左前缀"规则；并非越多索引越好，每个索引增加写入开销。

**避免在 WHERE 子句中对索引列做函数运算或隐式类型转换**：`WHERE DATE(created_at) = '2026-01-01'` 会让索引失效，改为范围条件 `WHERE created_at >= '2026-01-01' AND created_at < '2026-01-02'`。

**大量写入时关闭唯一索引检查（批量导入场景）**：`SET unique_checks = 0; SET foreign_key_checks = 0;` 在批量 `INSERT` 前关闭检查，完成后再开启，可大幅提升写入速度，但必须确保数据本身无重复。

**定期用 `ANALYZE TABLE` 更新统计信息**：优化器的执行计划依赖表统计信息，大量数据变动后（如导入、删除大量记录），统计信息可能过期导致优化器选错索引，手动 `ANALYZE` 触发更新。

---

## 常见陷阱

### 陷阱：SELECT \* 查询走了索引但仍然很慢

**现象：** `EXPLAIN` 显示用了索引，但实际查询依然耗时数秒。

**原因：** `SELECT *` 选取了索引以外的列，触发"回表"（从索引叶子节点回到主键索引取完整行数据）。若数据量大，回表次数与全表扫描相当，反而不如覆盖索引快。

**解决：** 只 SELECT 需要的列；或为高频查询建立覆盖索引（把查询所需列都包含在索引中，避免回表）。

```sql
-- 回表查询（慢）
SELECT * FROM orders WHERE status = 'pending';

-- 覆盖索引（把 status, id, created_at 都加入联合索引）
SELECT id, created_at FROM orders WHERE status = 'pending';

```

### 陷阱：OR 条件导致索引失效

**现象：** 查询含 `WHERE a = 1 OR b = 2`，`EXPLAIN` 显示全表扫描，即使 `a` 和 `b` 各自都有索引。

**原因：** MySQL 对 `OR` 的处理取决于优化器：若两侧列不在同一个联合索引里，优化器可能选择全表扫描而非 Index Merge。

**解决：** 改用 `UNION ALL` 把两个条件分开查询，让每个子查询独立命中索引，然后合并结果。

```sql
-- 可能全表扫描
SELECT * FROM t WHERE a = 1 OR b = 2;

-- 各自命中索引
SELECT * FROM t WHERE a = 1
UNION ALL
SELECT * FROM t WHERE b = 2 AND a != 1;

```

### 陷阱：更新高频表后执行计划突然变慢

**现象：** 某张表在批量导入或大量删除后，原本快的查询突然变得极慢，重建索引也没效果。

**原因：** MySQL 优化器依赖统计信息（`information_schema.STATISTICS`）选择索引，批量操作后统计信息严重失真，优化器选错执行计划。

**解决：** 执行 `ANALYZE TABLE table_name;` 强制更新统计信息；对于 InnoDB，也可 `OPTIMIZE TABLE` 重建表和索引（会锁表，生产谨慎使用）。

---

## 参见

- [MySQL基础完全指南](https://blog.vercanti.com/mysql-ji-chu-wan-quan-zhi-nan/)
- [数据库设计规范](https://blog.vercanti.com/shu-ju-ku-she-ji-gui-fan/)
- [Redis完全指南](https://blog.vercanti.com/redis-wan-quan-zhi-nan/)
- [PostgreSQL完全指南](https://blog.vercanti.com/postgresql-wan-quan-zhi-nan/)