被遗忘的索引（二）

　　上篇文章了解了以上数据存储的结构，索引为什么查询速度快，基本就没什么疑问了。 然而有时候我们虽然建立了索引却发现效能仍然得不到解决。类似这样的问题。需要从多个方面去入手。比如数据库操作的需求、索引的设计是否合理、执行计划的选择。为了便于问题的逐一解决我们仍然需要进一步分析。对于OLTP系统一次请求的时效性是关键。影响时效性的因素一般有阻塞、死锁、锁的数量。运行SELECT、UPDATE、INSERT、DELETE语句，会申请什么样的锁，以及了解执行计划对锁申请数量的影响，了解全面方能够清楚要缓解阻塞和死锁，须在数据库调优上下什么样的功夫。这跟索引有密不可分的关系。

本文我们观察一下SELECT、UPDATE、INSERT、DELETE动作申请的锁和索引有什么关系。

　　了解SELECT、UPDATE、INSERT、DELETE动作申请的锁，工具必不可少。笔者习惯用DMV查询。当然你也可以用SQL Trace。相比较而言DMV提供的比较详细一些。贴一段DMV查询锁代码

SELECT
    request_session_id
    , resource_type
    , resource_associated_entity_id
    , request_status
    , request_mode
    , resource_description
    , p.object_id
    , OBJECT_NAME(p.object_id) AS object_name
    , p.*
FROM 
    sys.dm_tran_locks
LEFT JOIN
    sys.partitions p
ON
    sys.dm_tran_locks.resource_associated_entity_id = p.hobt_id
WHERE
    resource_database_id = DB_ID('XXXX')
ORDER BY
    request_session_id
    , resource_type
    , resource_associated_entity_id

本文中经常会提到各种锁，如果您对数据库各种锁还不太了解，请参照TechNet：http://technet.microsoft.com/zh-cn/library/ms190615(v=sql.105).aspx

一个常见的SELECT动作要申请的锁

在可重复读的级别下，共享锁要保留到事务提交的时候才释放，所以如果在这个隔离级别下开启一个事务，再运行一个查询语句。能够看到这个查询所申请的主要共享锁。因此可以使用这种简单的方法分析一个查询语句会申请哪些锁。

规律如下

1. 查询在运行的过程中，会对每一条读到的记录或键值加共享锁。如果记录不用返回，那锁就会被释放。如果记录需要被返回，则视隔离级别而定。如果是“已提交读“，则也释放，否则不释放。
2. 对每一个使用到的索引，SQL Server也会对上面的键值加共享锁。
3. 对每个读过的页面，SQL Server会加一个意向锁。
4. 查询需要扫描的页面和记录越多，锁的数目也会越多。查询用到的索引越多，锁的数目也会越多。
5. 选择执行计划时，如果执行计划是全表扫描，则对表申请意向共享锁，如果执行计划仅仅是索引扫描。比较而言锁的范围缩小了很多。

 所以，如果想要减少一个查询被别人阻塞或阻塞别人的几率，数据库设计者能做的事情有：

1. 尽量返回少的记录集。返回结果越多，需要的锁也就越多。
2. 如果返回结果集只是表格所有记录的小部分，要尽量使用索引Index Seek，避免全表扫描这种执行计划。
3. 可能的话，设计好合适的索引。避免SQL Server通过多个索引才找到数据。

当然那这些都是对于“已提交读“以上的隔离级别而言。如果选用”未提交读“，SQL Server就不会申请这些共享锁，阻塞也就不会发生。然而带来的问题是”脏读“。选择什么样的隔离级别需要项目需求来界定。 

一个常见的UPDATE动作要申请的锁

对于UPDATE语句，可以简单理解为SQL Server先做查询，把需要修改的记录给找到，然后在这个记录上做修改。找记录的动作要加S锁，找到要修改的记录后先加U锁，再将U锁升级成X锁。

规律如下：

1. 对每一个使用到的索引，SQL Server会对上面的键值加U锁。
2. SQL Server只对要做修改的记录或键值加X锁。
3. 使用到要修改的列索引越多，锁的数据也会越多。
4. 扫描过的页面越多，意向锁也会越多。在扫描过程中，对所有扫描到的记录也会加锁，哪怕上面没有修改。

所以，如果想降低一个UPDATE被别人阻塞住的几率，除了注意它的查询部分以外，数据库设计者还要做的事情有：

1. 尽量修改少的记录集。修改的记录越多，需要的锁也就越多。
2. 尽量减少无谓的索引。索引的数目越多，需要的锁也可能越多。
3. 但是也要严格避免表扫描的发生。如果只是修改表格记录的一小部分，要尽量使用Index Seek，避免全表扫描这种执行计划。 

一个常见的DELETE动作要申请的锁

规律如下：

1. DELETE的过程是先找到符合条件的记录，然后做删除。可以理解成先是一个SELECT然后是DELETE。所以，如果有合适的索引，第一步申请的锁就会比较少。
2. DELETE不但是吧数据行本身删除，还要删除所有相关的索引键。所以一张表上索引数目越多，锁的数目就会越多，也就越容易发生阻塞。

所以综合上面两点，为了防止阻塞，我们既不能绝对地不建索引，也不能随随便便地建很多索引。而是要建对查找有利的索引。对于没有使用到的索引，还是去掉比较好。

另外对于聚集索引来说执行删除之后会对索引进行重新的排列。而非聚集索引则会产生一部分存储碎片。 

一个常见的UPDATE动作要申请的锁

相对于SELECT、UPDATE和DELETE，单条记录的INSERT操作对锁的申请比较简单。SQL Server会为新插入的数据本身申请一个X锁，在发生变化的页面上申请一个IX锁。由于这条记录是新插入的，被其他连接引用到的概率会相对小一些，所以出现阻塞的几率也要小。

规律如下

1. 数据库会申请S锁
2. 表会申请IX锁
3. 每个索引都要插入一条新数据，所以有一个Key上面的X锁
4. 在每个索引上发生版画的那个页面，申请一个IX锁

唯一不同的，是在Heap结构上还得申请一个RID锁。因为真正的数据不是放在索引上，而是放在Heap上。

总结

合适的索引能使SQL Server在读取尽可能少的数据量的前提下，把需要处理的数据找到。如果没有合适的索引，SQL Server在做SELECT、UPDATE和DELETE的时候，会申请比要处理的目标数据量多得多的锁，从而导致阻塞或者死锁。这种情形可以通过加索引的方式提高并发度。

     同时，SQL Server在做UPDATE、INSERT和DELETE的时候，会对有关联的所有索引都做修改，在它们上面申请锁。所以从这个角度讲，索引越多，产生的锁的数目也就越多，阻塞和死锁的几率也会越高。

     所以数据库设计员需要做的，是要确保有足够的索引，防止语句做全表扫描，但是也要去掉那些对语句运行共享不大的索引。不能随便往表格上加索引。