mysql中SQL语句优化的常见技巧与实践

select * 在高并发下易拖垮性能，因强制读全行、解析所有字段并传输，尤其含TEXT/BLOB时I/O和内存开销剧增；应只查所需列，避免函数操作、确保索引有效、JOIN字段类型一致且有索引。

为什么 SELECT * 在高并发场景下容易拖垮查询性能

不是所有字段都需要读取，但 SELECT * 会强制 mysql 加载整行数据、走完整的字段解析和网络传输流程。尤其当表里有 TEXT、BLOB 或多个大字段时，I/O 和内存开销会指数级上升。

• 用 EXPLaiN 查看 rows 和 Extra 字段：如果出现 using filesort 或 Using temporary，大概率是字段过多或没走索引导致的
• 只查真正需要的列，比如把 SELECT * 改成 SELECT id, name, status
• 避免在 SELECT 列表中使用函数或表达式（如 UPPER(name)），这会让索引失效且增加 CPU 开销

如何判断 WHERE 条件是否命中了索引

索引不是建了就一定生效，MySQL 优化器会根据统计信息、数据分布、条件写法决定是否走索引。常见失效场景比想象中多。

• LIKE 查询以通配符开头（如 WHERE name LIKE '%abc'）无法使用 B+ 树索引的前缀匹配能力
• 对索引列做函数操作（如 WHERE YEAR(create_time) = 2024）会导致索引失效，应改写为范围查询：WHERE create_time >= '2024-01-01' AND create_time
• 隐式类型转换：比如 user_id 是 int，但写成 WHERE user_id = '123'，MySQL 可能放弃索引转为全表扫描
• 联合索引顺序不匹配：若建了 (a, b, c)，WHERE b = 1 AND c = 2 就无法利用该索引；必须从最左列开始连续使用

ORDER BY 和 LIMIT 组合为什么有时反而更慢

看起来加了 LIMIT 应该更快，但如果 ORDER BY 没走索引，MySQL 仍需先排序全部结果再截断——数据量越大，越明显。

• 确保 ORDER BY 字段上有索引，且与 WHERE 条件共同构成“覆盖索引”最佳
• 避免 ORDER BY RAND()：它强制全表扫描+临时表排序，千万级表可能卡死
• 分页深翻问题（如 LIMIT 1000000, 20）本质是 MySQL 还是得跳过前 100 万行。可改用游标分页：WHERE id > last_seen_id ORDER BY id LIMIT 20

哪些 JOIN 写法容易引发性能雪崩

多表关联本身不危险，危险的是没控制驱动表、没加限制条件、或 JOIN 字段类型/字符集不一致。

• 小表驱动大表：让数据量小的表作为 LEFT JOIN 的左表，减少嵌套循环次数；可用 EXPLAIN 看 rows 值验证
• JOIN 字段必须类型一致、字符集一致、都加了索引；否则会触发隐式转换，索引失效
• 避免 SELECT * FROM A JOIN B ON 1=1 这类笛卡尔积写法，哪怕只有几百行也可能生成上百万组合
• 用 STRAIGHT_JOIN 强制连接顺序（慎用），适用于优化器选错驱动表且你非常确定物理执行路径时

EXPLAIN SELECT u.name, o.amount  FROM users u  JOIN orders o ON u.id = o.user_id  WHERE u.status = 1  ORDER BY o.created_at DESC  LIMIT 20;

真正卡住你的往往不是单条语句多复杂，而是 WHERE 条件是否触发索引、ORDER BY 是否复用同一索引、以及 JOIN 后的数据膨胀是否被意识到。这些地方一松懈，QPS 上去后慢查询日志就立刻爆满。