开发人员对数据库性能优化的深入理解.

1、开发人员对数据库性能优化的深入理解易车网 王泊 statistics IO on)逻辑读取物理读取CPU(set statistics time on)编译时间执行时间性能问题的定位逻辑读取查询所需访问从缓存区页的次数物理读取从磁盘读取的页数性能问题的定位编译时间执行时间set statistics time on开发人员对数据库性能优化的深入理解性能问题的分析执行计划SQL语句被编译成执行计划迭代器内存消耗分类、哈希聚合、哈希链接非阻断迭代器与阻断迭代器计划缓存和重编译计划缓存即席查询自动化参数预定义查询，使用sp_executesql或API存储过程或其他编译对象（触发器、TVF等等）索引

2、结构聚集索引一表一个非聚集索引一表多个扫描和查找扫描扫描查找查找堆表扫描聚集索引聚集索引扫描聚集索引查找非聚集索引索引扫描索引查找索引覆盖查询（单列）可覆盖Col=1Col100Col BETWEEN 1 AND 10Col like 100%Col IN (2,6,7,9)不可覆盖ABS(Col)=1Col+1=9Col like %abc索引覆盖查询（多列）查找命中Col1=1 and Col2=2Col1=1 and Col24剩余谓词Col1100 and Col2=2Col1 like abc% and Col2=4查找未命中Col2=2Col1+1=9 and Col2 Betwe

3、en 1 and 9Col1like %abc and Col2 in (1,2,3)索引覆盖查询（多列）关键列已覆盖列书签查找输出列决定书签查找索引查找聚集索引查找（RID查找）INNER JOIN连接嵌套循环合并连接哈希连接嵌套循环（内连接）For each row R1 in the outer tableFor each row R2 in the inner tableIf R1 joins with R2Return (R1,R2)嵌套循环（外连接）For each row R1 in the outer tableBeginFor each row R2 in the inner

4、 tableIf R1 joins with R2Output（R1,R2）If R1 did not joinOutput（R1,null）End合并连接Get first row R1 from input 1Get first row R2 from input 2While not at the end of either inputBeginIf R1 JOINS WITH R2BEGINOUTPUT(R1,R2)GET NEXT ROW R2 FORM R2EndElse if R1R2Get next row R1 from input 1ElseGet next row R2

5、from input 2End哈希连接For each row R1 in the build tableBeginCalculate hash value on R1 join keysInsert R1 into the appropriate hash bucketEndFor each row R2 in the probe tableBeginCalculate hash value on R2 join keysFor each row R1 in the corresponding hash bucketif R1 join with R2Output (R1,R2)End连接特

6、点对比嵌套循环嵌套循环合并连接合并连接哈希连接哈希连接最佳用于相对小的输入，同时又索引在内表的连接键上中大型输入，同时有索引在等值连接键上提供排序或者在连接后排序数据仓库需要中型或大型输入，可扩展的并发执行并发性支持大的并发有索引提供排序支持大并发小并发阻断和继续否否是需要等值否是是外连接 半连接只有左链接全部全部使用tempdb否是（对多对多）是需要排序否是否保留排序是（只对于外输入）是否支持游标是否否聚合聚合函数聚合种类流聚合哈希聚合聚合聚合函数COUNTSUMAVGMINMAX流聚合Clear the current aggregate resultsClear the current

7、group by columnsFor each input rowIf the input row does not match the current group by columnsBeginOutput the current aggregate resultClear the current aggregate resultsSet the current group by columns to the input rowEndUpdate the aggregate result with the input rowend哈希聚合For each input rowBeginCal

8、culate hash value on group by columnsCheck for a matching row in the hash tableIf matching row not foundInsert a new row into the hash tableElseUpdate the matching row with the input rowEndOutput all rows in the hash table联合Union AllUnion子查询不相关vs.相关子查询标量vs.多行子查询高级索引运算动态索引运算索引联合索引交叉索引视图增删改INSERTDELETEUPDATE锁（rowlock、tablelock、slock、xlock）自连接的应用表设计三范式反范式约束聚集索引的位置非聚集索引的位置计算列综合算法分页TOP颠覆法链接方式IN的问题*Hints查询Hint表Hint*架构数据库架