数据库优化往往是项目发展到一定阶段绕不开的话题。业务早期数据量小，随便写一条SQL都能秒出结果，可当表数据量涨到几百万甚至上千万时，各种问题就暴露出来了：页面加载变慢、慢查询日志暴增、CPU被数据库进程打满。我在多个项目中踩过不少MySQL性能的坑，这篇文章把这些经验系统整理出来，覆盖索引设计、SQL调优、执行计划分析以及一些容易被忽视的细节，希望能给正在做优化的同学一套可落地的思路。
一、定位问题：慢查询日志与EXPLAIN执行计划
优化之前必须先定位瓶颈，凭感觉改SQL是优化的大忌。MySQL提供了慢查询日志这个利器，通过配置long_query_time可以记录所有执行超过阈值的SQL。建议在项目中期就把阈值设置成1秒甚至0.5秒，别等到线上出问题才想起来开启，因为开启慢查询日志本身开销很小，却能持续积累有价值的优化线索。
拿到慢SQL之后，第一步永远是使用EXPLAIN分析执行计划。重点关注几个核心字段：type表示访问类型，从好到差大致是const、eq_ref、ref、range、index、ALL，一旦看到ALL就说明发生了全表扫描；key字段显示实际用到的索引，如果为NULL说明索引没生效；rows是预估扫描行数，这个数字越大说明扫描代价越高；Extra字段里的Using filesort和Using temporary则是排序和临时表的警告信号，意味着你的排序或分组逻辑没有走索引。
-- 开启慢查询日志并设置阈值 SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 0.5; -- 分析执行计划 EXPLAIN SELECT order_no, amount FROM t_order WHERE user_id = 10086 AND status = 2 ORDER BY create_time DESC LIMIT 20;
实践中有个经验值得记住：rows是优化器基于统计信息的估算值，并不完全精确。如果统计信息过期（比如表经过大量增删改之后），估算可能严重偏离实际，这时候可以用ANALYZE TABLE重新收集统计信息，再观察执行计划是否变化。我就遇到过一次案例，SQL执行计划时好时坏，根因就是统计信息陈旧导致优化器在两个索引之间来回摇摆。
二、索引设计：最左前缀原则与联合索引的列顺序
索引设计是MySQL优化的核心，其中联合索引的列顺序又是重中之重。最左前缀原则指的是，联合索引(a, b, c)只能支持以a开头的查询条件组合：查询a、a+b、a+b+c都能命中索引，但只查b或只查b+c则无法使用。设计索引时应该把哪些列放在前面？一般原则是：等值查询的列放前面，范围查询的列放最后，同时把区分度高（列值重复度低）的列尽量前置。
举个例子，订单表常见查询是按用户查某状态下的订单并按时间倒序，那么索引设计成(user_id, status, create_time)就非常合适。这样WHERE user_id = ? AND status = ?的过滤走索引前两列，ORDER BY create_time直接利用索引第三列完成排序，完全避免了filesort。如果索引设计成(create_time, user_id, status)，排序虽然是天然有序的，但过滤条件只能用到第一列，扫描量会大幅增加，这就是列顺序带来的巨大差异。
-- 反例：排序在前，过滤条件利用不充分 ALTER TABLE t_order ADD INDEX idx_bad (create_time, user_id, status); -- 正例：等值条件在前，范围或排序列在后 ALTER TABLE t_order ADD INDEX idx_good (user_id, status, create_time);
另一个重要概念是覆盖索引。如果查询的字段全部包含在索引中，MySQL无需回表读取整行数据，直接从索引就能返回结果，Extra字段会显示Using index。对于高频的列表查询，把SELECT字段收窄到索引覆盖的范围内，性能提升往往立竿见影。当然索引也不是越多越好，每个索引都会占用存储空间，还会拖慢写入速度，建议单表索引控制在5到6个以内，并且定期审查是否存在从未被使用的冗余索引。
三、索引失效的常见场景与规避方法
索引建好了不代表一定会被使用，很多写法会导致索引失效。最典型的几类：一是在索引列上使用函数或运算，比如WHERE DATE(create_time) = '2024-01-01'会让create_time上的索引完全失效，改写成范围条件WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02'即可恢复；二是隐式类型转换，比如字符串列用数字查询WHERE order_no = 12345，MySQL会把列转成数字比较，索引直接报废；三是模糊查询以通配符开头，LIKE '%abc'无法走索引，而LIKE 'abc%'可以。
-- 索引失效：对索引列使用函数 SELECT * FROM t_order WHERE DATE(create_time) = CURDATE(); -- 优化改写：使用范围查询 SELECT * FROM t_order WHERE create_time >= CONCAT(CURDATE(), ' 00:00:00') AND create_time < CONCAT(CURDATE(), ' 00:00:00') + INTERVAL 1 DAY; -- 索引失效：隐式类型转换（order_no是varchar类型） SELECT * FROM t_order WHERE order_no = 20240101; -- 正确写法：保持类型一致 SELECT * FROM t_order WHERE order_no = '20240101';
此外还要注意OR条件和联合索引的配合问题。如果OR两边的条件分别属于不同索引，MySQL可以走index merge，但如果其中一边没有索引，整个查询就会退化为全表扫描。NULL值判断、NOT IN、不等于这些写法在旧版本中也常常导致索引失效。规避这些坑的关键在于团队建立SQL规范，上线前统一走EXPLAIN审查，把索引失效问题拦截在测试阶段。
四、深分页优化与大表变更的工程化处理
分页越深越慢是MySQL的经典问题。LIMIT 1000000, 20这样的SQL，MySQL实际要扫描一百万零二十行然后丢弃前一百万行，代价极高。优化的核心思路是记住上一页的边界值，用游标方式翻页：WHERE id > 上一页最大id LIMIT 20，这样每次都从索引定位点直接开始取数，扫描量恒定。如果业务必须支持随机跳页，可以用延迟关联的写法，先在覆盖索引上完成分页拿到主键，再回表取完整数据。
-- 深分页反例：扫描并丢弃大量行
SELECT * FROM t_order ORDER BY id LIMIT 1000000, 20;
-- 优化一：游标分页，记录上一页边界
SELECT * FROM t_order WHERE id > 1000000 ORDER BY id LIMIT 20;
-- 优化二：延迟关联，先在索引内分页再回表
SELECT t.* FROM t_order t
INNER JOIN (
SELECT id FROM t_order ORDER BY id LIMIT 1000000, 20
) tmp ON t.id = tmp.id;
除了查询优化，大表的线上变更也需要工程化手段。给千万级大表直接执行ALTER TABLE加索引或加字段，可能在主库上锁表数小时，造成线上事故。推荐的做法是使用MySQL 8.0自带的在线DDL（大部分加索引操作支持INPLACE算法），或者借助gh-ost、pt-online-schema-change这类工具，通过新建影子表加触发器同步数据的方式平滑切换。变更前务必评估主从延迟，选择业务低峰期执行，并预留足够的磁盘空间。
最后总结一下我的优化心法：先用慢日志圈定问题范围，再用EXPLAIN理解优化器的选择，然后从索引设计和SQL写法两个层面动手，最后用工程化手段保证变更安全。优化没有银弹，每次改动都应该用数据验证效果，形成定位、分析、改造、验证的闭环，这套方法论比记住任何具体技巧都更有长期价值。