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

开发人员对数据库性能优化的深入理解易车网王泊weibo.com/brantwong提纲性能问题的出现性能问题的定位性能问题的分析、解决开发人员对数据库性能优化的深入理解性能问题的出现性能问题的出现访问量过多SQL语句性能差业务复杂硬件问题开发人员对数据库性能优化的深入理解性能问题的定位性能问题的定位来源IO(setstatisticsIOon)逻辑读取物理读取CPU(setstatisticstimeon)编译时间执行时间性能问题的定位逻辑读取查询所需访问从缓存区页的次数物理读取从磁盘读取的页数性能问题的定位编译时间执行时间setstatisticstimeon开发人员对数据库性能优化的深入理解性能问题的分析执行计划SQL语句被编译成执行计划迭代器内存消耗分类、哈希聚合、哈希链接非阻断迭代器与阻断迭代器计划缓存和重编译计划缓存即席查询自动化参数预定义查询，使用sp_executesql或API存储过程或其他编译对象（触发器、TVF等等）索引结构聚集索引一表一个非聚集索引一表多个扫描和查找扫描查找堆表扫描聚集索引聚集索引扫描聚集索引查找非聚集索引索引扫描索引查找索引覆盖查询（单列）可覆盖Col=1Col100ColBETWEEN1AND10Collike‘100%’ColIN(2,6,7,9)不可覆盖ABS(Col)=1Col+1=9Collike‘%abc’索引覆盖查询（多列）查找命中Col1=1andCol2=2Col1=1andCol24剩余谓词Col1100andCol2=2Col1like‘abc%’andCol2=4查找未命中Col2=2Col1+1=9andCol2Between1and9Col1like‘%abc’andCol2in(1,2,3)索引覆盖查询（多列）关键列已覆盖列书签查找输出列决定书签查找索引查找聚集索引查找（RID查找）INNERJOIN连接嵌套循环合并连接哈希连接嵌套循环（内连接）ForeachrowR1intheoutertableForeachrowR2intheinnertableIfR1joinswithR2Return(R1,R2)嵌套循环（外连接）ForeachrowR1intheoutertableBeginForeachrowR2intheinnertableIfR1joinswithR2Output（R1,R2）IfR1didnotjoinOutput（R1,null）End合并连接GetfirstrowR1frominput1GetfirstrowR2frominput2WhilenotattheendofeitherinputBeginIfR1JOINSWITHR2BEGINOUTPUT(R1,R2)GETNEXTROWR2FORMR2EndElseifR1R2GetnextrowR1frominput1ElseGetnextrowR2frominput2End哈希连接ForeachrowR1inthebuildtableBeginCalculatehashvalueonR1joinkeysInsertR1intotheappropriatehashbucketEndForeachrowR2intheprobetableBeginCalculatehashvalueonR2joinkeysForeachrowR1inthecorrespondinghashbucketifR1joinwithR2Output(R1,R2)End连接特点对比嵌套循环合并连接哈希连接最佳用于相对小的输入，同时又索引在内表的连接键上中大型输入，同时有索引在等值连接键上提供排序或者在连接后排序数据仓库需要中型或大型输入，可扩展的并发执行并发性支持大的并发有索引提供排序支持大并发小并发阻断和继续否否是需要等值否是是外连接半连接只有左链接全部全部使用tempdb否是（对多对多）是需要排序否是否保留排序是（只对于外输入）是否支持游标是否否聚合聚合函数聚合种类流聚合哈希聚合聚合聚合函数COUNTSUMAVGMINMAX流聚合ClearthecurrentaggregateresultsClearthecurrentgroupbycolumnsForeachinputrowIftheinputrowdoesnotmatchthecurrentgroupbycolumnsBeginOutputthecurrentaggregateresultClearthecurrentaggregateresultsSetthecurrentgroupbycolumnstotheinputrowEndUpdatetheaggregateresultwiththeinputrowend哈希聚合ForeachinputrowBeginCalculatehashvalueongroupbycolumnsCheckforamatchingrowinthehashtableIfmatchingrownotfoundInsertanewrowintothehashtableElseUpdatethematchingrowwiththeinputrowEndOutputallrowsinthehashtable联合UnionAllUnion子查询不相关vs.相关子查询标量vs.多行子查询高级索引运算动态索引运算索引联合索引交叉索引视图增删改INSERTDELETEUPDATE锁（rowlock、tablelock、slock、xlock）自连接的应用表设计三范式反范式约束聚集索引的位置非聚集索引的位置计算列综合算法分页TOP颠覆法链接方式IN的问题*Hints查询Hint表Hint*架构数据库架构应用程序架构总结性能问题出现的原因计划缓存与重编译执行计划与迭代器表设计分页迭代器表扫描聚集索引扫描索引扫描索引查找聚集索引查找排序标量计算嵌套循环合并连接哈希连接流聚合哈希聚合串联判定脱机Q&A谢谢