我刚接手一个老项目的时候,数据库三天两头报警。慢查询日志里全是几秒钟的SQL,页面转圈转到用户直接关浏览器。老板拍桌子问怎么回事,我打开数据库一看,好家伙,一张表几千万条数据,连个索引都没建,查询全靠全表扫描。后来我花了整整两周时间,把那些要命的SQL一条条揪出来改掉,数据库CPU占用率直接从90%降到15%。这活儿干完我才明白,SQL性能调优不是什么高深莫测的技术,就是肯不肯沉下心去看执行计划、去分析数据分布的事。

你可能会问,优化SQL到底该从哪儿下手?我的经验是先看执行计划,别凭感觉瞎猜。很多开发同事一遇到查询慢,第一反应就是“加索引”,但加完发现没效果,甚至更慢了。我见过一个典型的坑:有人在WHERE条件里写了,然后给createtime加了索引,结果索引完全没用上。因为你对createtime做了函数运算,索引就废了,数据库只能把所有行都算一遍。改成,索引立马生效,查询时间从2.3秒降到0.02秒。这种问题不执行计划你看不出来,光靠猜,猜一年也猜不对。
再说说分页查询。很多人写分页就是,数据少的时候没什么感觉,数据量一大就露馅了。MySQL这个写法会先扫描前100020行,然后丢掉前100行,只返回20行。你想想,这前面的10万行不全是白扫了吗?我优化过一个报表系统,翻到第500页的时候,查询要12秒。后来我改成用这种键集分页方式,查询时间直接掉到0.01秒。这就是典型的“知道原理才能优化”的例子,你光想着加索引,这个问题根本解决不了。
还有个大坑是关联查询。很多新手喜欢用子查询,觉得写起来方便,但性能往往惨不忍睹。举个例子,你要查“有订单的用户信息”,有人写成。这个SQL看着没啥问题,但MySQL处理IN子查询的时候,可能会把子查询的结果集缓存下来,然后外层逐个匹配,一旦子查询返回几万条记录,性能就直接崩了。我优化过一个类似的SQL,改成写法之后,查询时间从8秒降到0.3秒。当然也不是说子查询一定不能用,关键是要看执行计划,看它到底是怎么执行的,数据量小可能无所谓,数据量大了就必须用JOIN。
说到JOIN,这里头也有讲究。很多人写JOIN的时候不注意驱动表的选择,结果性能差好几倍。MySQL的优化器通常会选择小表作为驱动表,但有时候它也会选错,尤其是统计信息不准的时候。我遇到过一张表数据分布极度不均匀的情况,某个字段的值90%都是同一个值,剩下10%分散在几百个值里。优化器按统计信息选择了错误的驱动表,结果那个SQL跑了30秒都没出结果。后来我手动指定了驱动表,加上FORCE INDEX,把SQL改写成两段式查询,先查小结果集再关联大表,性能直接提升100倍。这个案例告诉我们,SQL优化有时候连执行计划都不能全信,你得结合业务数据分布去判断。
再聊聊索引的设计。很多人觉得索引越多越好,其实大错特错。每个索引都要占磁盘空间,而且写入的时候要维护所有索引,写性能会下降。我见过一张表建了20多个索引的,结果插入一条数据要花1秒多。索引设计的原则是“能用复合索引就不用单列索引”,而且要把区分度高的列放前面。比如查“某个城市某个状态的订单”,你可以建的复合索引,但如果你把放前面,因为status区分度太低了(只有几个值),索引效果就很差。我一般习惯用查看现有索引,用验证是否命中,用看执行细节,三个工具配合着用,基本能解决90%的索引问题。
还有一个特别容易被忽视的点:隐式类型转换。比如你有个字段是VARCHAR类型,但查询的时候传了个数字进去,,MySQL会尝试把字段转换成数字再比较,这样索引就失效了。我排查过一个线上事故,用户查询接口突然变慢,查了半天发现是有人把参数类型改了,字符串变成了数字,导致全表扫描。改回字符串类型,问题立刻解决。这种问题最难查,因为SQL看起来一模一样,只是参数类型变了,执行计划就完全不一样了。所以排查性能问题的时候,一定要把实际参数值拿过来看,别只看SQL模板。
说说批量操作。很多人写更新语句是一条条循环执行的,比如在代码里,一万条数据就是一万次网络往返,每次都有SQL解析、权限检查、事务提交的开销,慢得让人抓狂。我之前优化过一个批量导入功能,原来要跑40分钟,改成拼接批量更新,或者用,一次提交一千条,时间直接压缩到3分钟。这里面的核心思路就是减少数据库的交互次数,把多次小事务合并成一次大事务,但也要注意事务别太大,否则锁冲突和回滚日志会变成新问题。
其实做了这么多年数据库优化,我最大的感受是:大部分性能问题都不是数据库本身的问题,而是写SQL的人没有站在数据库的角度去思考。数据库不是魔法,它就是个按规则办事的工具,你给它一个能走索引的条件,它就快;你给它一个需要全表扫描的条件,它就慢,这没什么玄学。关键是养成看执行计划的习惯,每次写完SQL都花30秒一下,看看有没有全表扫描、有没有Using filesort、有没有类型转换,把这些东西变成肌肉记忆,你的SQL自然就快了。数据库优化这条路没有终点,数据量在涨,业务在变,你今天优化的SQL明天可能又慢了,但只要方法论在手里,就不怕它翻出什么浪花来。


