SQL优化的一些总结（T=S/V）

布满荆棘的人生 2022-04-23 08:16 153阅读 0赞

SQL的优化是DBA日常工作中不可缺少的一部分，记得在学生时期，曾经在ITPUB上看到一篇帖子，当时楼主在介绍SQL优化的时候，用一个公式来讲解他在做sql优化的时候遵循的原则：

> T=S/V(T代表时间，S代表路程，V代表速度)

S指SQL所需访问的资源总量，V指SQL单位时间所能访问的资源量，T自然就是SQL执行所需时间了;我们为了获得SQL最快的执行时间，可以根据公式定义上去反推：

1.  在S不变的情况下，我们可以提升V来降低T：通过适当的索引调整，我们可以将大量的速度较慢的随机IO转换为速度较快的顺序IO；通过提升服务器的内存，使得将更多的数据放到内存中，会比数据放到磁盘上会得到明显的速度提升；采用电子存储介质进行数据存储和读取的SSD，突破了传统机械硬盘的性能瓶颈，使其拥有极高的存储性能;在提升V上我们可以采用较高配置的硬件来完成速度的提升；
2.  在V不变的情况下，我们可以减小S来降低T：这是SQL优化中非常核心的一个环节，在减小S环节上，DBA可以做的可以有很多，通常可以在查询条件中建立适当的索引，来避免全表扫描；有时候可以改写SQl，添加一些适当的提示符，来改变SQL的执行计划，使SQL以最少的扫描路径完成查询；当这些方法都使用完了之后，你是否还有其他方案来优化喃？在阿里系的DBA职位描述中有条就是要求DBA需要深入的了解业务，当DBA深入的了解业务之后，这个时候能站在业务上，又站DB角度上考虑，这个时候在去做优化，有时候能达到事半功倍的效果。

### 案例一：通过降低S，来提升T ###

> 原理介绍：  
> 我们知道B+索引叶子节点的值是按照索引字段升序的，比如我们对（nick，appkey）两个字段做了索引，那么在索引中的则是按照nick，appkey的升序排列；如果我们现在的一条sql：  
> select count(distinct nick) from xxxx\_nickapp\_09\_29;  
> 用于查询统计某天日志表中的UV，优化器选择了该表上索引ind\_nick\_appkey（nick，appkey）来完成查询，则开始从nick1开始一条条扫描下来，直到扫描到最后一个nick\_n,那么中间过程会扫描很多重复的nick（最左边普通扫描），如果我们能够跳过中间重复的nick，则性能会优化非常多（最右边的松散扫描）：

从上面的可以得到一个结论：

如果这条统计uv的sql能够按照右边的loose index scan的方式来扫描话，会大大的减小我们上面提到的S；所以需要通过改写sql来达到伪loose index scan：（MySql优化器不能直接的对count(distinct column)做优化）

root@DB 09:41:30>select count(\*) from ( select distinct(nick) from xxxx\_nickapp\_09\_29) t ;

\+———-+  
| count(\*) |  
\+———-+  
| 806934 |  
\+———-+  
Sql内查询中先选出不同的nick，最后在外面套一层count，就可以得到nick的distinct值总和；  
最重要的是在子查询中：select distinct(nick) 实现了上图中的伪loose index scan，优化器在这个时候的执行计划为Using index for group-by ，这样mysql就把distinct优化为group by，首先利用索引来分组，然后扫描索引，对需要的nick只扫描一条记录。

真实案例：

该案例选自我们的一个线上的生产系统，该系统每天有大量的日志数据入库，单表的容量在10G-50G之间，然后做汇总分析，计算日志数据中的uv就是其中一个逻辑，sql如下：

select count(distinct nick) from xxxx_nickapp_09_29;

即使在\_xxxx分表上加上nick的索引，通过查看执行计划，为全索引扫描，由于单表的数据量过大，sql在执行的时候，会对整个服务器带来抖动，需要对原来的SQL进行改写，使其支持loose index scan；

优化前：

root@DB 09:41:30>select count(distinct nick) from xxxx\_nickapp\_09\_29;  
\+———-+  
| count(\*) |  
\+———-+  
| 806934 |

1 row in set (52.78 sec)  
执行一次sql需要花费52.78s

优化后：

root@DB 09:41:30>select count(\*) from ( select distinct(nick) from xxxx\_nickapp\_09\_29)t ;

\+———-+  
| count(\*) |  
\+———-+  
| 806934 |  
\+———-+  
1 row in set (5.81 sec)

由52.78秒降至5.81秒，速度提升了差不多10倍；

查看SQL的执行计划：

优化写法：

root@DB 09:41:30>explain select count(\*) from ( select distinct(nick) from xxxx\_nickapp\_09\_29)t ;

<table> 
 <thead> 
  <tr> 
   <th align="left">id</th> 
   <th align="right">select_type</th> 
   <th align="right">table</th> 
   <th align="right">type</th> 
   <th align="right">possible_keys</th> 
   <th align="right">key</th> 
   <th align="right">key_len</th> 
   <th align="right">ref</th> 
   <th align="right">rows</th> 
   <th align="center">Extra</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">1</td> 
   <td align="right">SIMPLE</td> 
   <td align="right">xxxx_nickapp_09_29</td> 
   <td align="right">range</td> 
   <td align="right">NULL</td> 
   <td align="right">ind_nick_appkey</td> 
   <td align="right">67</td> 
   <td align="right">NULL</td> 
   <td align="right">2124695</td> 
   <td align="center">Using index for group-by</td> 
  </tr> 
 </tbody> 
</table>

原始写法：  
root@DB 09:41:50>explain select count(distinct nick) from xxxx\_nickapp\_09\_29;

<table> 
 <thead> 
  <tr> 
   <th align="left">id</th> 
   <th align="right">select_type</th> 
   <th align="right">table</th> 
   <th align="right">type</th> 
   <th align="right">possible_keys</th> 
   <th align="right">key</th> 
   <th align="right">key_len</th> 
   <th align="right">ref</th> 
   <th align="right">rows</th> 
   <th align="center">Extra</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">1</td> 
   <td align="right">SIMPLE</td> 
   <td align="right">xxxx_nickapp_09_29</td> 
   <td align="right">index</td> 
   <td align="right">NULL</td> 
   <td align="right">ind_nick_appkey</td> 
   <td align="right">177</td> 
   <td align="right">NULL</td> 
   <td align="right">19546123</td> 
   <td align="center">Using index</td> 
  </tr> 
 </tbody> 
</table>

可以看到我们的路程由19546123减小到2124695，减小了9倍多.

### 案例二：结合业务递增的写入特点，巧妙优化UV统计count(\*) ###

> 有时候觉得，优化一条sql的最高境界就是让这sql能够从把这条从系统中拿掉，不管怎样，这些都是建立在你足够的了解业务上，就能够推动一些业务产品的升级或者下线，这样的DBA你能做到吗？

下面看一个案例：  
应用每天都会对入库的分表统计一个总数：select count(*) from xx\_01\_01;  
随着单表的数据量越来越大（单表在20G左右），每次进行count的时候，速度越来越慢，同时需要扫描较多的数据页块，导致整个数据库性能的抖动，通过分析业务的特点，由于每张表采用自增id的方式进行插入，并且没有数据的删除，所以统计全表的总数就可以变通一下：  
所以这条sql：select count(*) from xx\_01\_01;  
可以变通为： select max(id)-min(id)+1 from xx\_01\_01;  
执行速度可以得到质的飞跃.

### 案例三：通过提升V，来降低T—随机IO VS 顺序IO ###

真实线上案例：在我们的一个核心产品库上，承载着非常大量的随机读，就叫它读库好了。一天读库的load非常的高，通过慢日志发现，有一条sql频繁的出现在慢日中，这条sql的查询条件很复杂，同时该表上的类似相同的索引也非常的多，当时是怀疑索引走错，通过explain 来查看SQL的执行计划：发现执行计划中的using where代表查询回表了，同时由于回表的记录rows较大，所以带来了大量的随机IO：

所以我们只需要在原来的索引冗余掉is\_detail字段就可以通过覆盖索引的方法优化掉该sql，避免了查询回表而导致的随机io，用顺序io替换了原来的随机io，SQL的执行速度得到极大提升：（下图会去掉is\_detail字段的测试）

总结：SQL优化是很有趣的一件事情，我们在日常工作中可以按照t=s/v的思路来进行优化，也许你第一次运用它的时候有些陌生，但是只要不断的练习，善于总结，你也会发现其中的规律，真是妙哉妙哉。还有一点很重要的是，你的SQL优化不要脱离实际业务，也许你在哪里优化一条sql花了1个小时，但是去和开发同学讨论优化成果的时候，开发同学说这条sql其实可以下线了，那时候真的哭笑不得了 .

原文：玄惭http://hidba.org/?p=498