java面试必问19：MySQL优化思路：从表设计到SQL编写，性能翻手起飞

MySQL 优化思路：从表设计到 SQL 编写，一篇讲透

面试官：“谈谈你对 MySQL 优化的理解。”
你：“可以从表设计、索引、SQL 编写、事务控制几个方面入手：字段类型合理、避免冗余索引、不用 select *、避免前置模糊查询、大分页用延迟关联、少用 join、控制事务大小。”
面试官：“那你能具体说说为什么大分页要用延迟关联？还有哪些常见坑？”

很多人能列出几点，但一问到“为什么”就卡壳了。本文从实战角度，把 MySQL 优化的核心思路讲清楚，并给出可落地的建议。

一、表设计优化

表结构是 SQL 执行的根基，设计不合理的表，后期优化事倍功半。

1. 选择合适的数据类型

• 越小越好：能使用 TINYINT 不用 INT，能使用 VARCHAR(20) 不用 VARCHAR(255)。更小的数据类型占用更少的磁盘、内存，CPU 处理也更快。
• 使用内置类型：存储日期用 DATE/DATETIME/TIMESTAMP，而不是字符串。IP 地址用 INET_ATON 转为整数存储。
• 避免 NULL：索引列允许 NULL 会让索引更复杂（B+Tree 需特殊标记），且 NULL 比较效率低。建议字段设置 NOT NULL DEFAULT ...。

2. 合理范式与反范式

• 三范式：消除数据冗余，避免更新异常，但查询可能需要多表 join。
• 适当冗余：对于高频查询的字段，可以冗余到主表，减少关联。例如订单表冗余商品名称，虽然更新商品名时需要同步，但查询性能大大提升。

3. 分表策略

• 垂直分表：将不常用的大字段（如 TEXT、BLOB）拆分到扩展表，减少主表的行宽度，提高缓存命中率。
• 水平分表：数据量极大（千万级以上）时，按时间、ID 范围或哈希拆分到多个物理表，配合中间件或应用层路由。

二、索引优化

索引是查询加速的核心武器，但滥用索引会拖慢写入。

1. 合理建索引，避免冗余

• 选择性高的列优先：索引列的区分度（COUNT(DISTINCT col)/COUNT(*)）越高越好，如主键、唯一ID。性别等低区分度列建索引意义不大。
• 复合索引遵循最左前缀：根据查询条件设计索引顺序，等值查询列在前，范围查询列在后。
• 避免冗余索引：已有 (a,b) 索引，再建 (a) 就是冗余的。MySQL 5.6+ 可借助 sys.schema_redundant_indexes 查询冗余索引。

2. 覆盖索引

对于高频查询，尽量让索引包含查询需要的所有列，避免回表。例如：

-- 查询只需要 name 和 age，索引 (name, age) 就是覆盖索引
SELECT name, age FROM user WHERE name = '张三';

3. 索引失效场景（回顾）

• 函数操作：WHERE UPPER(name) = 'ZHANG'
• 隐式类型转换：WHERE phone = 13800138000（phone 是 varchar）
• 前置模糊查询：LIKE '%abc'
• 使用 != 或 IS NULL（大多数情况）
• 复合索引跳过最左列

三、SQL 编写优化

1. 避免 SELECT *

• 只返回需要的列，减少网络传输和内存消耗。
• 更容易形成覆盖索引，避免回表。
• 表结构变更时，* 可能带来意外列。

坏习惯：

SELECT * FROM user WHERE id = 1;

推荐：

SELECT id, name, age FROM user WHERE id = 1;

2. 避免 LIKE '%xxx%' 前置模糊查询

前置 % 会让 B+Tree 索引失效，只能全表扫描。如果需要模糊查询，可考虑：

• 使用 LIKE 'xxx%' 后置匹配，索引有效。
• 使用全文索引（FULLTEXT）配合 MATCH AGAINST。
• 如果业务允许，使用 Elasticsearch 等搜索引擎。

3. 大分页用延迟关联

传统的 LIMIT offset, limit 在 offset 很大时性能极差，因为 MySQL 需要先扫描 offset+limit 行，然后丢弃前 offset 行。例如：

-- 偏移量 100000，取出 10 条，实际扫描 100010 行
SELECT * FROM user ORDER BY id LIMIT 100000, 10;

延迟关联优化：先通过覆盖索引查出主键（只扫描少量数据），再回表取完整行。

SELECT * FROM user 
INNER JOIN (
    SELECT id FROM user ORDER BY id LIMIT 100000, 10
) AS tmp USING(id);

或者记住上一次查询的最大 id（适用于递增且无间隙的场景）：

SELECT * FROM user WHERE id > 100000 ORDER BY id LIMIT 10;

4. 少用 JOIN，控制关联表数量

• 多表 JOIN 会让优化器选择难度增加，且可能产生临时表或文件排序。
• 一般建议关联表不超过 3 张，如果超过可考虑在应用层多次查询组装。
• 确保关联条件使用索引，且尽量让驱动表是小结果集。

5. 使用 EXPLAIN 分析执行计划

关键字段：

• type：ALL（全表扫描）-> index（全索引扫描）-> range -> ref -> eq_ref -> const（好到差）
• possible_keys：可能使用的索引
• key：实际使用的索引
• rows：预估扫描行数
• Extra：Using filesort（需优化）、Using temporary（需优化）、Using index（覆盖索引，好）

四、事务优化

1. 避免长事务

长事务会：

• 持有很多锁，阻塞其他事务。
• 导致 undo log 版本链过长，影响查询性能，甚至撑爆磁盘。
• 增加死锁风险。

解决：

• 尽量将事务控制在单一操作或少量操作内。
• 不要将用户交互放在事务中（如请求外系统）。
• 编程式事务明确边界，及时提交。

2. 合理选择隔离级别

• 默认 RR 级别在大部分场景可用，但间隙锁可能带来死锁。如果业务允许不可重复读，可降级为 RC，减少锁冲突。
• 对于只读事务，显式设置 SET TRANSACTION READ ONLY，优化器会优化。

五、其他优化思路

1. 优化 COUNT(*)

• COUNT(*) 在 InnoDB 中需要扫描索引或表，如果业务允许，可使用 information_schema 中的近似值。
• 对于条件计数，确保过滤列有索引。

2. 优化 ORDER BY / GROUP BY

• 让排序字段使用索引顺序，避免 Using filesort。
• GROUP BY 默认会排序，如果不需要排序，用 ORDER BY NULL 取消。

3. 使用 UNION ALL 代替 UNION

• UNION 会去重，产生临时表，性能差；UNION ALL 不删除重复行，效率高。

4. 批量操作

• 批量插入、更新、删除比循环单条执行效率高得多。
• 例如 INSERT INTO t VALUES (1),(2),(3)...，减少日志刷盘次数。

5. 配置优化

• 调整 innodb_buffer_pool_size（通常设置为物理内存的 50%~80%）。
• 设置 query_cache_type=0（MySQL 8.0 已移除查询缓存）。
• 合理设置 max_connections、thread_cache_size 等。

六、常见误区与排查思路

排查步骤：

1. 开启慢查询日志，定位问题 SQL。
2. 用 EXPLAIN 分析执行计划。
3. 检查索引是否合理，是否被使用。
4. 观察数据库状态（SHOW PROCESSLIST、SHOW ENGINE INNODB STATUS）。
5. 考虑业务逻辑是否可以改写 SQL 或引入缓存。

七、总结

一句话记住 MySQL 优化：表设计要合理，索引覆盖防回表；大分页用延迟，事务短小性能高。

MySQL 优化是一门实践科学，需要结合具体业务和数据量反复验证。希望这篇文章能帮你建立系统化的优化思路，在面试和工作中游刃有余，欢迎继续讨论。