当SQL Query跑得很慢的时候...--主要瓶颈

当SQL Query跑得很慢的时候， 这是件多么美妙的事情...

 

每天清晨，慵懒地来到公司，机械化的点开SQL客户端，华丽地按下更新数据按钮。然后倒水泡茶，上开心，逛论坛，吃饭聊天。。。转眼夕阳西下。哇！数据更新完毕。套用模板，发出Report，回家吃饭。

 

多么令人艳羡的工作。但是我的Query没有跑得那么慢啊！没关系，看完本文你一定能让你的Query跑得更慢！(#大误)

 

言归正传，Butter将为你逐步揭示SQL语句性能调优办法。首先让我们来看影响SELECT语句性能的三座大山 (排名绝对分先后，影响力以指数级下降):

 

 

Random IO

 

Random IO是性能的最大杀手，一次Random IO往往意味着一次HardDisk 的Seek Time。一般桌面电脑的seek time在9ms左右取决于硬盘的RPM。至于服务器seek time 可以下降到3ms。

 

3ms以为着什么？ 以现在较为常规服务器磁盘每秒300MB的传输率计算，3ms可以读0.9MB数据，也就是900K。以一个Page 8KB计算，假设每一个page存100条记录，3ms 可以大约读取10,000条连续数据记录~~~~10,000条连续数据记录~~~~~回音

 

换句话说，每当服务器进行一次Random IO，需要省下读取10,000条连续数据的时间才有赚不亏。(画外音1: 不考虑空间问题，Non-Cluster Index实际上就是做了这样一个取舍。以后将写文章专门就此展开)

 

 

Sequential IO

继续上面的假设，通常情况服务器，每秒可以访问3,000,000条的连续数据记录(画外音2:这里的数据记录不包括大对象field，比如text) 。这意味着Scan 一张亿级的数据表，完成一次Table Scan至少需要30秒。

 

如果是为了寻找某一条特定记录，你是愿意scan整张表查询，还是通过index进行几次Key Lookup(Random IO)直接定位呢？

 

如果是寻找某一组记录呢？Scan好，还是Key Lookup好呢？

 

 

CPU Bottleneck

以现在的处理器的能力，CPU在99%的情况下不会成为瓶颈。一旦出现CPU 100%的情况，往往意味着SELECT语句的某些缺陷导致了SQL Server很二地得出了很差的Execution Plan。Butter曾经碰到过16个CPU持续100%长达半小时以上的情况。而这中情况往往是由不合理的Hash和Sort 产生 （继续画外音3:可以就此出篇文章展开~）

 

One more thing to consider : BAD SELECT Algorithm

也许你不信，写SQL也能有Algorithm的问题？难道能用SELECT写出个NP问题来？

然而事实上，有时候我们不得不去思考改进算法 (画外音4：对于算法能力有信心的同学，可以去尝试下TSQL Challenge)

 

举个非常简单例子如何用SQL找出第二大的数 (假设没重复)

 

A：俄...我知道怎么拿最大的数...俄...我只要找出比最大数小的最大数就是行了，bingo! (复杂度是N²)

 

B：恩...那如果我要第10大的数呢

 

A: 我可以找到第二大的...然后就是第三大的...第四大...啊 如果是第100大怎么办... (复杂度是N^k)

 

B：第十大的数 就是最大的10个数里的最小的阿 (使用TOP)

 

A：怎么写？

 

B: 你懂的... (复杂度是NLogN + K, 考虑到SQL对Top的优化可以接近理论值N)

 

相信很多人在遇到找第二大数的时候是不会多思考的，只有碰到了第100大数才会考虑，然而往往多思考下就能省出一半的时间

 

Summary

说了那么多废话，只是希望大家在做SQL语句性能调优的时候，不要死记硬背那些所谓的Tips，而是从本源出发，分析服务器在执行Query中所进行的操作，进而减少不必要的时间消耗。我将在以后的文章中逐步展开，分析SQL Server的基本操作是如何影响这三座性能大山的。而针对于前两项的优化将贯穿于整个SQL语句的调优过程中。