SQL 优化方法论

SQL 优化肯定比 SQL 编写本身要难很多，但也存在一些优化的基础知识，如 SQL 执行计划、索引原理等等。这些都比编写 SQL 本身要复杂得多，因此要成为 SQL 优化高手仅知道一些优化基础知识是远远不够的，还需要经验的沉淀，并且要转化成你的方法论。

SQL 悲催故事

我们先看几个 SQL 优化的小故事：

故事 1

话说某天上午小王被告知某系统的一个菜单访问非常慢，于是他开始介入优化，他跟踪到该菜单调用的具体 SQL 语句，接下来他通过观察该 SQL 的执行计划后发现该 SQL 访问某张表时没走索引。他发现问题后非常兴奋，于是马上动手开始建立索引。建索引大概花费了几分钟时间，随后他发现 SQL 走索引了，并且真的快了许多。于是测试一下该菜单，果然快了不少。

故事 2

话说小王优化后正自鸣得意时，被告知虽然这个菜单变快了，不过刚才持续几分钟时间出现访问该菜单一直报错的情况。

故事 3

当天下午小王又接到电话，被告知那菜单访问又变慢了。小王有点吃惊，于是赶紧登录系统运行该 SQL，发现确实变慢了，但是奇怪的是该 SQL 正常走索引。

故事 4

几天后小王又迎来一个新任务，开发项目组中有一条 SQL 很慢，希望能优化一下。小王看了一眼 SQL，觉得写得歪瓜裂枣很不舒服，于是挽起袖子对 SQL 进行重写，但发现改造后的语句跑得比之前的 SQL 还要慢。

故事 5

在小王崩溃之前，公司的 SQL 优化大师老丁正好路过，他分析了之后，建议将 SQL 语句涉及的某表的外键加上索引。果然性能迅速提升!老丁告诉小王，优化前可通过各种手段先观察观察 SQL 涉及的表结构、索引等，看它们有无不合理之处，急于动手改造 SQL 太盲目了，是没有抓住主要矛盾的体现，而且改代码需要测试、打补丁、上线。

故事 6

一周后小王又面对一条 SQL 需要优化，这次他不动手改写了，尝试了加索引，又尝试了调整表结构，结果提升效果非常不明显。

好在老丁又及时出现了，他这次没有修改表结构，而是和开发人员进行了半小时的交谈，然后居然对 SQL 进行重写。

过不了多久，SQL 就变得飞快了。小王傻眼了，不是说尽量不先动手改 SQL 吗？怎么老丁一看到这 SQL 就改改改。老丁的新 SQL 看上去并不是很复杂，可是小王居然看不懂老丁为什么能这么改写。

故事解读

好吧，让我们来解读一下这些故事吧。

先说明一下，这几个故事没有真相，只是解读一下。真相不重要，猜测的过程就是进步的过程。

故事 1

小王能定位到具体 SQL，并且能根据 SQL 执行计划进行 SQL 优化，这至少说明了小王还是掌握了一定的 SQL 优化基础技能的，否则估计执行计划是什么都没有听过。不过接下来的故事 2 和故事 3 说明在这次优化上他是失败的。

首先，小王他接到电话后就开始动手优化了。而不是多问一句这问题是一直以来就存在呢，还是今天忽然出现的。

如果是第一次出现，小王就需要重点关注一下是否昨晚系统做了什么动作，比如昨晚打了一个补丁，这补丁引发这次故障的可能性就非常大，于是目标就很清晰了。在实在无法解决的情况下，回退补丁也是一个思路。

而如果是经常有这故障，那应该有其他同事处理应对过，获取他们之前的分析成果，或者收集之前的日志和现在进行比对，这些也有较大的帮助。

故事 2

菜单业务曾经出错了几分钟，这是因为建索引会锁全表，这时候更新数据肯定会失败。小王犯了一个大错，在业务高峰期做 DDL 操作，严重地影响了生产，问题解决者成了麻烦制造者。

注：在 MySQL 5.6 之前，创建索引时会锁表，但在 MySQL 5.6.7 之后，因为新增了 Online DDL 技术，所以此时在添加索引时，是可以和 DML 数据操作语言 INSERT、UPDATE、DELETE、SELECT 等命令一起执行的。

故事 3

当前故事没有具体答案，我们可以不断进行分析。

假设一下根本不只是这个菜单慢。而是整个系统都慢。

求助者没有提出其他模块慢或许只是因为他平时仅用这个菜单，如果整个平台都瘫痪了，局部能快起来吗？

故事 4

小王动手改 SQL 语句但是效果很差。这是因为小王没有具体分析而直接进行改造。

此时最重要的是应该是观察慢在什么地方，比如可以通过执行计划看出最大的开销在某全表扫描上，而该全表扫描完全可以通过索引减少访问路径，这时加索引就可以解决问题，改写 SQL 是不可能解决问题的。

故事 5

优化 SQL 不一定非要改写才可以优化，有时根据数据库的体系逻辑结构不改写 SQL 也可完成优化。改写 SQL 的代价更高，因为现实中如果你要改 SQL 肯定需要经过测试、打补丁、上线等多个过程，不可能直接就在生产环境中直接进行修改。

故事 6

小王学老丁不改 SQL，但是效果并不好。而老丁则修改 SQL，效果又是杠杠的。小王欲哭无泪，不知自己该如何做了。

这里小王生搬硬套了，他没有找到本质原因，比如此时可能是由于冗长的写法导致表访问了多次，而老王改写 SQL 将表访问次数大幅度降低下来。这时不改写 SQL 语句是无法优化的。又或者是老丁根据业务需求，砍掉了某些多余的逻辑，这就更需要改写 SQL 了。

总结

由于不改写通常来说比改写高效，而不改写的优化一般都和数据库的体系逻辑架构有关。

而且改写其实分成两个部分，一个是等价改写，一个是根据业务改写。

比如小王看某 SQL 写得很不顺眼，然后动手改，这显然是等价改写。而老丁和开发人员交谈了半个多小时，改造后的 SQL 连小王都看不明白，这就很可能是根据业务改写的。

业务改写是优化的最高境界，老丁通过和开发人员交流后发掘出真正的需求，然后写出来的代码表面上看和当前代码逻辑完全不等价，实际却是等价的。

关于需求的本质，讲个生动点的例子吧。

有一天，小王请大家去他家吃饭，小王妈妈一个劲地让小余买鱼招待客人，条件不允许的时候还坚持如此。后来小王让妈妈用冰箱里的牛肉来代替鱼，妈妈也猛然醒悟了，她忘记了需求的本质是做美味给大家分享。站在做美昧这角度上看，去老远的地方买鱼和拿冰箱里的牛肉，又有什么区别呢？

这里顺便再说一点，小王看不懂老丁写的 SQL，也可能是因为老丁用了某些小王没见过的 SQL 语法来改写，这里称之为高级 SQL。比如 WITH 子句、树形递归、分析函数等等。

做事要有方法论，要先整体后局部，解决问题要注重效率，先尽量考虑不改写的优化，再考虑改写的优化。

不改写的优化靠的是体系结构知识的沉淀
改写则需要考虑、逻辑等价改写和业务改写两大思路

其中业务改写是 SQL 优化的最高境界。另外还是要有一定的知识沉淀。