前阵子帮朋友排查一个线上接口，页面转圈转了十几秒才出数据。一看日志，好家伙，一条SQL跑了8万多毫秒。DBA甩过来一句“这SQL写的，得回炉重造”。我盯着那条SQL看了半天，单表才20万数据，怎么就慢成这样？问题出在关联了7张表，每张表都用了函数包裹索引字段，优化器直接放弃索引，全表扫描加嵌套循环，不慢才怪。
这事让我想起很多开发同学的日常：功能跑通了，SQL能查出结果，就万事大吉。等到数据量涨上来，或者并发一高，数据库先扛不住。MySQL的SQL优化，从来不是DBA一个人的事，写SQL的人才是第一道防线。咱们今天就掰开揉碎聊聊，那些真正能落地的优化思路。
先说最基础的，索引到底该怎么建。很多同学知道要建索引，但要么建多了，要么建偏了。有个同事把表里每个字段都加了索引，结果写入慢得离谱，因为每次INSERT都要维护所有索引。索引不是越多越好，而是要跟着查询走。你查WHERE条件里的字段，ORDER BY排序的字段，JOIN的关联字段，这些才是索引该待的地方。还有最左前缀原则，联合索引(a,b,c)能匹配(a)、(a,b,c)，但如果你直接查b字段，索引就废了。很多人栽在这上面，建了联合索引却发现查询还是慢，一看执行计划，type是ALL，全表扫描，索引压根没被用上。
再来说说SELECT 这个老生常谈的问题。有次看一个报表查询，SELECT 从一张30个字段的表里取数据，实际业务只用到其中5个字段。InnoDB存储引擎是按行存储的，SELECT 会把整行数据都加载到内存，包括那些大字段比如TEXT、BLOB。数据量一上来，内存和IO开销成倍增长。更麻烦的是，如果表结构后面加了字段，SELECT 的结果集也会跟着变，代码里按位置取值的话，分分钟出bug。正确做法是只SELECT需要的列，既能减少IO，又能避免隐式依赖。
LIMIT深分页也是个坑。前端要做分页，页码翻到100页以后，LIMIT 9900, 20这种写法，MySQL要扫描前9900行再丢弃，越到后面越慢。优化方案有两个：一是记录上一页一条数据的ID，用WHERE id > 上一页最大ID LIMIT 20来翻页，走主键索引，效率提升明显；二是如果业务允许，用延迟关联，先查出ID列表再回表取数据。有个电商后台的订单列表，原来深分页要2秒多，改成ID条件翻页后，直接降到几十毫秒。
再说说隐式类型转换这个隐蔽杀手。有一回排查一个慢查询，WHERE userid = '12345'，userid是int类型，但传参是字符串。MySQL会自动把字符串转成数字，这本身没问题，但如果你在索引字段上用了函数或者运算，索引就失效了。还有个常见案例，字段是varchar，查询时条件写成了数字，比如WHERE phone = 138000，phone是varchar类型，MySQL会把两边都转成浮点数比较，结果就是索引失效，全表扫描。解决办法很简单，传参类型跟字段类型保持一致，别让MySQL帮你做隐式转换。
COUNT()的优化也值得单独说说。很多人统计行数直接COUNT()，在MyISAM引擎下这个操作是O(1)的，因为引擎维护了行数计数器。但InnoDB不一样，它支持事务，MVCC机制导致每行数据对每个事务的可见性不同，所以COUNT(*)必须逐行扫描判断。要优化的话，可以建一个很小的辅助表，用事务维护计数，或者用EXPLAIN估算行数（不精确但够用），实在不行就用缓存。之前有个统计页面，每次打开要等3秒，后面改成定时任务把统计结果刷到一张汇总表，前端直接查汇总表，秒开。
EXPLAIN这个工具，好多同学用过但不会看。重点看几个字段：type，从好到差依次是system、const、eqref、range、index、ALL。ALL是全表扫描，一定要避免；index是全索引扫描，虽然比ALL好点，但也不理想。key字段看实际用了哪个索引，如果为NULL说明没用上索引。rows字段是预估扫描行数，这个数字越小越好。Extra字段里出现Using filesort或者Using temporary，说明排序或去重用了临时表，性能堪忧，得想办法通过索引消除。
说个容易被忽略的——分页查询里的ORDER BY。有次看一个慢SQL，ORDER BY createtime DESC LIMIT 20，createtime没建索引，MySQL先把全表数据查出来，再排序，再取20条。如果表有50万数据，这个排序的开销可想而知。解决办法是在createtime上建索引，让排序走索引，或者用覆盖索引。还有个技巧，如果查询条件过滤性很强，先过滤再排序，也能大幅减少排序的数据量。
优化SQL这事，说到底是理解MySQL怎么工作的。它是个关系型数据库，擅长处理结构化数据的关联和事务，但它的索引结构、存储引擎、优化器都有各自的脾气。你顺着它的脾气写SQL，它给你飞一般的速度；你逆着来，它就让你体验什么叫等待。平时写SQL养成好习惯，多看一眼执行计划，多想想数据量大了会怎样，很多性能问题根本不会等到线上爆发。毕竟，等用户抱怨慢的时候再优化，成本可就不是改几行SQL那么简单了。
