我入行那会儿，第一次被DBA叫去谈话，就是因为一条SQL把生产库拖垮了。那是个简单得不能再简单的查询，就是统计当天的订单数，我随手写了个COUNT()，没加索引，结果十几万条数据硬生生搞了个全表扫描。那次之后我才明白，SQL优化这事儿，真不是啥高大上的技术活，而是每个开发都得掌握的保命技能。
先说索引，这是最基础也是最容易出幺蛾子的地方。很多人觉得建了索引就万事大吉，但索引不是越多越好，也不是随便建在哪都行。比如你有个用户表，经常按用户名查询，那给username字段建个B+树索引准没错。可要是你写的SQL是SELECT FROM users WHERE status = 1 AND createdat > '2024-01-01'，结果status字段选择性特别低，就两个值：0和1，那索引基本白建，MySQL还是会走全表扫描，因为回表成本太高了。正确的做法是把createdat放在前面，或者建个联合索引(createdat, status)，让筛选条件更精确。
再说说那些常见的“坑”。很多人写JOIN的时候，习惯把小表当驱动表，这没错，但更关键的是要确保JOIN的字段上有索引。我见过一个经典案例：两个表关联查询，每个表才几万条数据，结果跑了十几秒。一查EXPLAIN，发现驱动表用的全表扫描，被驱动表也没索引，生生搞了个嵌套循环。加了索引之后，秒出结果。还有个更隐蔽的问题，就是用了函数。比如WHERE DATE(createdat) = '2024-01-01'，这会让索引失效，因为MySQL对函数结果没法做B+树查找。改成WHERE createdat >= '2024-01-01' AND createdat < '2024-01-02'，索引就能用上了。
分页查询也是重灾区。传统的LIMIT 100, 20写法，MySQL会先扫描10万条数据，再扔掉前10万条，返回20条。数据量一大，这种操作就是找死。我见过一个后台管理系统，用户翻到第500页，直接超时。后来改成基于游标的分页，比如WHERE id > 100 LIMIT 20，利用主键索引直接定位，性能提升了几百倍。当然，这要求数据能按某个有序字段分页，但大部分场景都适用。
说到数据量，不得不提分区表。当一张表数据量突破千万级别，即便索引建得再好，查询效率也会明显下降。这时候就可以考虑按时间、按地区等维度做分区。比如订单表按月分区，查询某个月的订单时，MySQL只扫那个分区，数据量瞬间少了一个数量级。但分区表也有坑，比如分区键的选择要慎重，不能用主键以外的字段做分区键，否则查询容易跨分区扫描。还有个容易被忽视的问题是，分区表的统计信息更新不及时，导致优化器走错执行计划，这时候需要手动ANALYZE TABLE。
除了索引和分区，SQL写法本身也大有讲究。比如避免使用SELECT *，只取需要的字段，减少数据传输和内存占用。再比如用EXISTS代替IN，用UNION ALL代替UNION（如果确定没有重复数据），都能带来小幅但稳定的性能提升。还有个容易被忽略的点是排序：ORDER BY字段如果没索引，MySQL会用filesort，数据量大时特别慢。所以尽量让排序字段在索引中，或者把排序和查询条件做成联合索引。
优化这事儿离不开监控和测试。别光靠直觉，用EXPLAIN看执行计划，用慢查询日志抓问题SQL，用performance_schema分析锁等待。我习惯在测试环境压测时，把慢查询阈值设成100毫秒，跑完一轮就抓那些超时的SQL，逐个优化。生产环境不敢这么搞，但可以定期分析慢查询日志，把执行时间超过1秒的捞出来，按频率排序，优先处理那些高频低效的SQL。
说到底，SQL优化不是一次性的事，而是贯穿整个开发周期的习惯。从写SQL的那一刻起，就要考虑数据量、索引、执行计划，而不是等出问题了再救火。毕竟，一条好的SQL，能让你的应用活得更久，也让DBA少骂你几次。
