当SQL Query跑得很慢的时候...-- Random IO

紧接上文， 我们知道Random IO是一次非常昂贵的操作，同样的时间大约可以读取10,000条连续记录。 那么SQL Server 在什么时候会发生Random IO呢

 

Index Seek

 

我们知道在SQL Server中，无论是cluster index 还是non-cluster index都最终以B tree的形式存在于存储介质中。而在index seek发生的时候，从root节点出发，经过每一层中间节点的跳转都有可能导致一次Random IO，直到到达根节点为止。

 

下面做一个简单的计算，假设是建在int上的 index, 一个page去掉头部信息可以有8096字节，每个节点占10个字节(4个字节放int，6个字节做指针)。 那么一个page最多放800个节点，由于B树不会被占满，平均来说一个page可以放400个节点。也就是说160,000条数据以下的的表需要3次跳转，64,000,000 条数据数据以下的表需要4次跳转。 4次跳转意味着12ms。这也就意味着如果随机的从一张百万级的表中取数据，每秒钟只能取100条左右!!!

 

当然实际情况不会那么糟糕，因为我们有cache，随着我们对index的访问增加，大部分的上层节点都会被cache在内存中了。另外如果我们需要进行大规模的数据扫描，SQL Server会相应的使用index scan操作，将index整个读取到内存中，以减少Random IO的发生。所以在日常的应用中，很少发生因为index seek而产生的性能问题

 

Large Object Column

 

Large Object 比方说nvarchar(max), XML，由于无法被保存在一个Page里，SQL Server会在数据记录中保存一个地址然后把Large Object保存在Page外。随着Object的变大，数据会被存放到多个Page中，而这些Page由于不是被一次性分配，所以往往也不是连续的。

所以当我读取一个Large Object Column时很有可能需要进行多次地跳转，从而导致多次的Random IO。　所以在日常应用中，我们应尽可能地避免读取Large Object Column，非到万不得已决不触碰这条高压线。

 

Key Lookup

 

Key Lookup发生在通过non-cluster index查找数据记录的过程中。 举个例子

 

SELECT A.ID, B.time,B.ID FROM A

INNER LOOP JOIN B ON A.KEY = B.KEY

 

A,B 两表的都以ID为cluster index Key, B以KEY为non-cluster key.A B中各有1,000,000条记录。SQL执行的顺序是这样的首先scan A表拿到A.KEY, A.ID 然后将A.KEY在B_KEY的index上查找，没找到一个B就进行一次Key Lookup取到B的一条记录，然后拿到B.Time,B.ID。于是悲剧就发生了，假设A和B的KEY都能对上，那么一共将发生1,000,000次Key Lookup，而每次Lookup往往意味着一次Random IO.于是将近1,000,000次的Random IO意味着3000秒的运行时间，将近一小时。如果数据集是千万级的呢，如果需要JOIN好几张表呢？？Butter曾经就看着一个类似Query跑了足足40小时。

 

那么如何来避免上述情况的发生，请记住以下几点

1. 慎用Join Hint。大多数情况下SQL Server都可以给出合理的execution plan．

2. Loop Join仅适用于规模较小的数据

3.尝试在non-cluster index中include你所需要的字段，比如本例中的B.time

4.在进行多张表的join时，可以考虑先将index union起来

 

 先睡觉。。。待续。。。