Home > database, mysql, sql优化 > SQL优化的一些总结

SQL优化的一些总结

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 ;

+—-+————-+——————————+——-+—————+———————————+———+—–
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——————————+——-+—————+———————————+———+—–
| 1 | SIMPLE | xxxx_nickapp_09_29 | range | NULL |ind_nick_appkey | 67 | NULL | 2124695 | Using index for group-by |
+—-+————-+——————————+——-+—————+———————————+———+—–
原始写法:
root@DB 09:41:50>explain select count(distinct nick) from xxxx_nickapp_09_29;
+—-+————-+——————————+——-+—————+—————————-+———+——+–
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——————————+——-+—————+—————————-+———+——+–
| 1 | SIMPLE | xxxx_nickapp_09_29 | index | NULL | ind_nick_appkey | 177 | NULL | 19546123 | Using index |
+—-+————-+——————————+——-+————–+—————————-+———+——+–

可以看到我们的路程由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

            在前面我们提到,提升V的一些方法,通常可以采用提升服务器硬件的方式来达到,但是很多中小型企业来说,现在比较高的成本对于他们来说还是望尘莫及,同时没有成熟的使用经验,对于他们可能还是一件坏事情。总的来说,你的服务器硬件无论在牛,如果SQL写的烂,索引建的不好,那还是不行的。

真实线上案例:在我们的一个核心产品库上,承载着非常大量的随机读,就叫它读库好了。一天读库的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其实可以下线了,那时候真的哭笑不得了 ^_^.

Categories: database, mysql, sql优化 Tags:
  1. No comments yet.
  1. No trackbacks yet.
You must be logged in to post a comment.