《CH数据库优化实用.pptx》由会员分享，可在线阅读，更多相关《CH数据库优化实用.pptx（53页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、SQL语句对效率的影响WHERE子句的规范SQL的注意事项第1页/共53页WHERE子句的规范WHERE子句常犯的错误对数据字段做运算负向查询对数据字段使用函数使用OR运算符第2页/共53页不要对数据字段做运算无运算的字段可以引用索引，有运算的字段将无法引用索引进行优化而需要扫描整个表示例比较下列语法的差异：SELECT*FROM Order Details WHERE Quantity=100SELECT*FROM Order Details WHERE Quantity+1=101还包括其它的运算，如字符连接等第3页/共53页不要使用负向查询负向查询：NOT、!=、!、NOT EXISTS
2、、NOT IN、NOT LIKE等负向查询不能充分利用索引进行二分查找，需要扫描整张表示例SELECT*FROM Order Details WHERE Quantity!=100可改成：SELECT*FROM Order Details WHERE Quantity100 OR Quantity100第4页/共53页不对数据字段使用函数数据字段使用函数就是一种运算，将使效率低比较：SELECT*FROM Order Details WHERE ABS(Quantity-100)99 AND Quantity101SELECT*FROM Employees WHERE SUBSTRING(La
3、stName,1,1)=D SELECT*FROM Employees WHERE LastName LIKE D%第5页/共53页使用OR运算符AND运算符可以充分引用索引SELECT*FROM Orders WHERE CustomerID=IS10008 AND OrderDate=20060808只需要在CustomerID上建索引就可以了OR运算符需要对参与查询的多个字段都建索引，否则将可能扫描全表SELECT*FROM Orders WHERE CustomerID=IS10008 OR OrderDate=20060808需要在CustomerID和OrderDate两个属性上都
4、建合适的索引,否则将扫描整个数据表第6页/共53页SQL的注意事项SELECT语法尽量不要传回数据表的所有字段，也不要不使用过滤条件，否则将极大地增加网络负担若使用复合索引，索引顺序上的第一个字段才适合当作过滤条件DISTINCT、ORDER BY等语法尽量等到查询需要时才使用，因为它们需要SQL SERVER的额外计算第7页/共53页SQL的注意事项大量数据加载大量加载某个数据表时，应考虑先删掉索引，加载完毕再重建索引（特别是多个用户端同时在做大量数据加载时）BULK INSERT语法通常比bcp工具程序快大量数据加载时，应设参数采用数据表锁定，而不要采用默认的记录锁如果数据表的记录需要先做
5、转换，应先导入临时表中，经过处理再大量加载到目的数据表中INSERT、DELETE和UPDATE对大量数据，SELECT INTO比INSERT快对大量数据，TRUNCATE TABLE 比DELETE TABLE快UPDATE和DELETE采用WHERE子句时，条件要符合WHERE的有效格式第8页/共53页索引与查询性能索引及其相关属性配置聚集索引与非聚集索引排序Sysindexes系统数据表索引是否值得统计联结与查询效率覆盖索引在视图与计算字段上建索引数据不连续的处理第9页/共53页索引及相关属性配置索引是有效使用数据库系统的基础索引建立是否适当是性能好坏的成功关键索引数据放在分页中，用来
6、当做索引的数据字段越小越好，也就是让分页尽量存放更多的索引项索引结点有三种结构：根结点分页叶子层非叶子层第10页/共53页索引及相关属性配置创建索引的语法格式：CREATE INDEX 索引名 ON 表名（列名）建索引时，可根据不同的需求进行选项配置：FILLFACTOR（填充因子）在建（包括重建）索引时，保留部分空间让随后的新建、修改可直接利用这些空间需要立刻对某个数据表更新所有的索引，最简单的方式是通过DBCC DBREINDEX命令重建该数据表的聚集索引，则所有的非聚集索引都会同时自动更新IGNORE_DUP_KEY对于唯一索引，当插入多条记录（包括重复记录）时，若建索引没有配置该选项，
7、将全部回滚，否则仅放弃重复记录第11页/共53页索引及相关属性配置选项配置：（续）DROP_EXISTING通过配置DROP_EXISTING可防止重建聚集索引时一并删除与重建该数据表上所有的非聚集索引，否则重建聚集索引会导致所有非聚集索引重建一次（若重建聚集索引采用相同的键值）或两次STATISTICS_NORECOMPUTE表示与该索引相关的统计信息不需要自动更新，系统管理员会手动更新SORT_IN_TEMPDB若系统的TEMPDB是建立在与该索引不同硬盘的文件组上，可通过该选项让临时空间利用另一个或一组TEMPDB所在的硬盘来做键值排序，以提升建立索引时的性能第12页/共53页聚集索引与
8、非聚集索引聚集索引对聚集索引，数据表本身就是索引的一部分，是聚集索引的叶子层，整个数据表的摆放顺序按索引项由小到大排序聚集索引的优点如果记录较小，则在记录访问中有可能可以减少磁盘存取的次数；聚簇索引有利于多点查询，因为值相同的记录放在了一起（一个页内），这样一次磁盘访问就可以了，如果是非聚簇索引，因为可能存在不同的页上，可能需要好几次磁盘访问。聚簇索引有助于在不同值较少的属性上进行的等值连接；基于B-树结构的聚簇索引，可以很好的支持范围查询、前缀匹配查询和排序查询。节省存储空间。聚簇以后，聚簇码相同的元组集中在一起了，因而聚簇码值不必在每个元组中重复存储，只要在一组中存一次就行了 第13页/共
9、53页聚集索引与非聚集索引聚集索引聚集索引的缺点建立与维护聚簇的开销相当大。如果存在大量的溢出数据页，它的性能会下降很快。原因：访问这些页面的磁盘定位需要花费很多时间。非聚集索引非聚集索引完全独立于数据表之外一个数据表可建立249个索引（具体应用时一般不超过10个）当查询条件的选择性不高，即符合条件的记录占很小比例时，通过非聚集索引查询效率非常低。适合对精确匹配，以及搜寻结果集很小的查询第14页/共53页聚集索引与非聚集索引聚集索引的选择至关重要聚集索引的索引项应该具有以下特性：数据格式为整数本身就唯一不可为NULL字段值不能太大若选择聚集索引的字段值很大，则整个数据表的各种索引都将会变得低效
10、，因为所有的非聚集索引的叶子层都会因为纳入聚集索引的键值而变大第15页/共53页排序组织数据时需要排序的情况GROUP BY、DISTINCT、ORDER BY、TOP等子句虽然这些子句只是查询结果的产生方式，但抽取与显示都需要耗费系统资源预先排序的数据要使用索引有效地排序查询数据，最直接的方式就是在要排序的字段上建立聚集索引。索引顺序SQL SERVER可使用相同的聚集索引做升序和降序排序，因为叶子层的分页存储都以双向连接串行方式连接在一起第16页/共53页排序示例聚集索引可以自动正反扫描 CREATE CLUSTERED INDEX idx_LastName ON member(LastN
11、ame)WITH DROP_EXISTING查询：SELECT*FROM Member ORDER BY lastname SELECT*FROM Member ORDER BY lastname DESC 效果一样第17页/共53页排序示例多关键字聚集索引CREATE CLUSTERED INDEX idx_LastName ON member(LastName ASC,FirstName DESC)WITH DROP_EXISTING查询：SELECT*FROM Member ORDER BY lastname ASC FirstName DESC第18页/共53页利用Sysindexes
12、系统数据表进行分析在SQL SERVER中，每个数据库都有一个Sysindexes系统数据表，用来存放数据库内所有的索引细节。在建立聚集索引或非聚集索引后可查询Sysindexes表的数据，也可以直接利用系统存储过程sp_spaceused查看数据表或索引所使用的存储空间。Sysindexes 数据表的used字段：聚集索引（非聚集索引）已使用的总分页数Sysindexes 数据表的dpage字段：聚集索引中的实际子叶，即数据表本身所占的页数（非聚集索引中叶子层所占的页数）第19页/共53页索引是否值得索引可以大大提高查询效率，若索引建少了，查找数据就效率低下索引建得太多则不利于插删改操作针对
13、SQL语法或数据类型查看是否值得建索引时，可参考的方面：选择性选择性指符合查询条件的记录占总记录的百分比。选择性越高，即该值越小，才越适合建索引在选择性很低时，通过非聚集索引存取是非常没有效率的存取方式，还不如直接做数据表扫描第20页/共53页索引是否值得是否值得建索引所参考的方面：(续)数据密度数据密度为键值唯一的记录笔数的倒数数据密度越小，该字段越适合建立索引平均查询到的记录数=数据密度*总记录数数据分布数据分布表示多笔数据记录组成的方式表示数据记录是平均散布在一段范围中还是集中在部分区块如均匀分布，正态分布等，需进一步确定其选择性第21页/共53页查看查询语法所使用的资源配置SET ST
14、ATISTICS 选项在查询分析器中配置，在SQL语句执行时返回语法：SET STATISTICS ONIO：返回扫描次数（表或索引存取次数）、逻辑读入（缓冲区读取页数）、物理读取（磁盘读取块数）、先读读入（先读机制预先将数据放到缓存）TIME：包括SQL SERVER分析与编译时间、服务器执行时间PROFILE：最优化程序如何执行SQL语法的结果集（执行计划）使用SET SHOWPLAN_TEXT选项查看查询计划语法：SET SHOWPLAN_TEXT ON返回将要执行的查询计划，不会真正执行查询STATISTICS IO 与SHOWPLAN_TEXT 是互斥第22页/共53页建立最优执行计
15、划的各阶段最优化程序的主要工作是将没有执行步骤、以集合为基础的SQL语法转换成有效率的可执行步骤建立执行计划的过程：一般计划的最优化评估是否缓存中已经存在以前建立且当前可用的执行计划对显而易见的查询要求直接建立执行计划如INSERT VALUES或SELECT的字段都包含在某个索引内,且没有其他合适的索引等单一化单一化主要做语句转换，找到语法上最有效的执行方式，处理一些不需要通过索引成本分析就可以决定有效执行步骤的工作加载统计多层次的以成本为基础的最优化最优化程序通过统计数据计算多种执行方式的成本进行选择第23页/共53页统计统计记录着数据内容的分布可以针对索引或数据的某个字段建立统计查询优化
16、程序可依据数据分布的统计信息完成下列工作：可获取某个索引对查询的选择性如何能分析索引的执行成本高低从而建立最佳的执行计划SQL SERVER获取统计的两种方式：完全扫描数据表：与建立索引时一并建立统计抽样分析：未建索引的字段建立统计，或更新已经存在的统计时第24页/共53页统计统计数据记录sysindexes系统表的statblob字段中（image格式）查看统计数据的语法：DBCC SHOW_STATISTICS(表名,统计信息的目标)示例：在查询分析器运行获得完全扫描方式的统计信息CREATE INDEX idx_product_no ON Product(PNO)DBCC SHOW_ST
17、ATISTICS(Product,idx_product_no)第25页/共53页统计统计结果以表格的形式显示，包括三部分：第一部分：索引最后被更新的时间(Updated字段)统计数据来源记录数(Row字段)抽样记录数(Row Sampled字段)分布组数(Steps字段)数据密度（Density字段）键值平均长度第二部分：多个键值字段各自的统计数据第三部分：各统计字段对应分布组的详细统计信息。起字段包括：RANGE_HI_KEY(涵盖记录上限值)、RANGE_ROW(落在其中的样本记录数)、EQ_ROWS(Step值的样本记录数)DISTINCT_RANGE_ROWS、AVG_RANGE_R
18、OWS第26页/共53页更新统计更新统计的方式有两种：手动更新、自动更新手动更新：利用CREATE STATISTICS 对未建索引的字段直接产生统计信息利用sp_createstats存储过程对字段建立统计信息利用UPDATE STATISTICS 更新某个统计利用sp_updatestats更新统计手动更新的时机：索引中的键值有大量的新建、修改或删除，而马上要用到该索引通过TRUNCATE TABLE 语法清空某个重新装入数据，而又要立即存取一般情况SQL SERVER能自动维护统计信息（属性要配置）第27页/共53页联结与查询效率查询优化程序决定联结的执行方式时，需要确定以下内容：数据表
19、之间联结最佳的先后关系两两联结时找出合适的内层数据表和外层数据表决定联结算法：嵌套循环连接、合并连接、哈希连接嵌套循环连接外部循环找到符合条件的记录后，逐列要求内部循环搜寻符合的数据列。适合只影响一小部分数据记录的查询，或外部输入相当小，内部输入已建索引，且数据记录相当大的情况。第28页/共53页联结与查询效率合并连接要求两边参与连接的输入数据必须先排序如满足上述条件，合并连接的效率最高哈希连接前两种连接不合用时，才考虑此连接建立两个输入：组建输入和探查输入组建输入将符合条件但数据较少的表的字段值计算得到哈希表放在内存中（相同键值记录链接起来）及哈希桶扫描整个探查输入，计算哈希值，扫描哈希桶，
20、产生符合项哈希连接用于集合对比作业：内部连接、外连接、半连接、交集等在没有索引的情况下，SQL SERVER默认哈希连接第29页/共53页覆盖索引聚集索引的好处：SQL SERVER找到正确的索引键值后不需要再用指针做额外的搜寻SQL SERVER将符合相同条件的数据集中放在一起聚集索引只能建一个，非聚集索引有很多个（最多可达249个）非聚集索引只能在传回数据量占总数比例很少时才有用引入覆盖索引（一种的非聚集复合索引）可同样具有聚集索引的两个好处第30页/共53页覆盖索引覆盖索引是指那些索引项中包含查询所需要的全部信息的非聚簇索引可以是单索引或复合索引，但是一般都是非聚簇的。覆盖索引之所以比较
21、快是因为索引页中包含了查询所必须的数据，不需去访问数据页。如果非聚簇索引中包含结果数据，那么它的查询速度将快于聚簇索引。什么时候建覆盖索引经常同时存取多列，且每列都含有重复值可考虑建立复合索引来覆盖一个或一组查询，并把查询引用最频繁的列作为前导列。经常查询涵盖GROUP BY或ORDER BY子句的字段如果可能尽量使关键查询形成覆盖查询。第31页/共53页覆盖索引建立覆盖索引的语法：CREATE INDEX 索引名 ON 表名(字段1，字段2，)示例EXEC spCleanIdx MemberCREATE INDEX idx_LastFirstName ON Member(Lastname、F
22、irstname)SELECT lastname,firstname FROM Member WHERE lastname BETWEEN Funk AND Lang 注意：建立覆盖查询时尽量限制索引项的大小，保持ROW/KEY越大越好，否则扫描覆盖索引与扫描数据表所花的I/O分页差不多，就失去的覆盖索引的意义第32页/共53页组合索引组合索引就是指建立在多个属性上的索引。组合索引可以是聚簇的，也可以是非聚簇的。比较在单个属性上建立的索引，组合索引具有以下优势：支持前缀匹配查询，支持的前缀就是组合索引（A，B，）的形式。更易覆盖查询条件，有时一个稠密的组合索引就可以完全回答查询。例如查姓为“罗
23、”，名为“强”的人有多少个。组合索引是支持多属性唯一性的一个有效办法第33页/共53页组合索引设计一个组合索引时，必须注意组合索引的顺序如果查询更倾向于在属性A而不是在属性B上加限定词的话，那么应该建立把A放在B前面的组合索引。组合索引的缺点：它们趋向于比较长的索引键。如果不使用压缩方法，这会引起B-树 层数的增加。因为组合索引包含多个属性，所以对其中任何属性的更新都会导致索引的更新，组合索引的维护代价将会是比较高的。第34页/共53页在视图与计算字段上建索引为视图建索引能让逻辑的数据物理化为视图建立的第一个索引一定是聚集以及唯一索引聚集是让索引的叶子层可以涵盖视图定义的所有记录唯一是让索引维
24、护比较方便相当于一个有聚集索引的数据表若删除该聚集索引将导致其他索引一起删除对计算字段可以直接建立非聚集索引，索引需要的是该字段计算后的值第35页/共53页Indexed ViewIndexed View把符合定义的数据建立好另外存放，若视图包含汇总函数，建立索引时即完成汇总计算，当更新数据表时，系统会自动维护视图索引的汇总结果通过视图（含索引）查询时不需要重新计算汇总，提高性能可以不必在查询时才做连接运算，提升性能如果偏向联机事务处理（绝大多数运算是插删改），反而因为要维护索引而降低效率第36页/共53页Indexed View示例CREATE VIEW Vdiscount WITH SCH
25、EMABINDINGASSELECT SUM(UnitPrice*Quatity*Discount)SumDiscountpriceFROM dbo.order detailsGROUP BY ProductIDGOCREATE UNIQUE CLUSTERED INDEX VDiscountInd ON Vdiscount(ProductID)查询最高折扣款前五名的产品SELECT TOP 5 ProductID,SUM(UnitPrice*Quatity*Discount)SumDisFROM order details GROUP BY ProductIDORDER BY SumDis考
26、察查询最高销售额前五名的产品第37页/共53页Indexed View查询最高销售额前五名的产品解决方法CREATE VIEW Vdiscount WITH SCHEMABINDINGASSELECT SUM(UnitPrice*Quatity)Sumprice,SUM(UnitPrice*Quatity*(1-Discount)SumDiscount,SUM(UnitPrice*Quatity*Discount)SumDiscountpriceFROM dbo.order detailsGROUP BY ProductIDGOCREATE UNIQUE CLUSTERED INDEX VDi
27、scountInd ON Vdiscount(ProductID)考察求平均值（AVG）的情况增加子句SUM(Quatity)Units第38页/共53页Indexed View的适用范围适合建立Indexed View的情况：减低决策支持查询的负载对大型数据表做连接以及汇总运算重复同一种模式的查询对某些字段重复做汇总运算重复对相同的数据表、相同的属性做连接以上方式的综合使用不适合建立Indexed View的情况：经常进行插删改的OLTP系统大量数据字段结合在一起的连接与原始数据表内容差不多大的Indexed View第39页/共53页数据不连续的处理数据经过插删改会造成不连续数据不连续分两
28、种：内部不连续：物理分页中有许多空间没有记录外部不连续：磁盘分页与扩展分页不连续，即索引或数据表可能散落在多个扩展分页中，使得其在物理上不连续。数据不连续会使硬盘读取无效率，而且读出来的数据还需要重新整理索引需要空间时需要做分割操作外部不连续只在做大量数据扫描时才影响效率，若只搜索某些记录，利用索引指针就可取得分页第40页/共53页数据不连续的处理可以执行DBCC SHOWCONTIG指令得到数据表的不连续状况。数据不连续的处理：利用DBCC INDEXDEFRAG移除逻辑扫描的外部不连续状况重建索引可以移除所有的不连续状况若只是见聚集索引，最好搭配CREATE INDEX WITH DROP
29、_EXISTING第41页/共53页事务与锁管理锁死锁观察与分析系统的锁定情况产生阻塞的原因第42页/共53页锁SQL SERVER中锁的种类：共享锁排他锁更新锁意向锁锁的相容性可锁定的资源数据库、文件、索引数据表、分页、索引键值数据行、应用程序等第43页/共53页锁锁与事务的四个隔离等级：READ UNCOMMITTEDSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED可能读到脏数据READ COMMITTEDSET TRANSACTION ISOLATION LEVEL READ COMMITTEDSQL SERVER默认配置不能读到脏数据，但
30、不可重复读REPEATABLE READ可重复读SERIALIZABLE防止幻象现象当需要事务的正确性，就会提高事务的隔离等级，但会让并发度减低第44页/共53页死锁死锁是在DBMS中的某组资源上发生了两个或多个线程之间循环相关性时，由于各个线程之间互不相让对方所需要的资源而造成的。当客户向数据库提交查询后，客户机可能会感觉到好像“死机”了，这就可能是发生了锁争夺当系统中出现锁争夺的时候，如果不想让进程永久的等待下去，解决的办法是通过设置锁超时时间间隔。可以用SET LOCK_TIMEOUT命令设置时间间隔。SQL Server中有循环死锁和转换死锁两大类。第45页/共53页死锁循环死锁由于系
31、统或用户进程之间彼此都只有得到对方持有的资源才能执行时发生转换死锁发生在两个或多个进程在事务中持有同一资源的共享锁，而且都需要将共享锁升级为独占锁，但都要待其他进程释放这一共享锁时才能升级。分布式死锁第46页/共53页观察与分析系统的锁定情况监视和跟踪SQL Server中的锁活动信息常见的方法有：使用sp_lock存储过程 使用企业管理器查看锁信息使用SQL Profile查看锁信息第47页/共53页观察与分析系统的锁定情况锁定能造成性能影响，可以从下面几个方面观察系统是否因为锁定与阻塞导致运行问题：通过企业管理器或系统存储 过程查看是否有许多进程被封锁不能执行Master.dbo.sysp
32、rocessed系统数据表内，被封锁的进程的waittime字段的值异常大SQL Profiler工具程序所获取的结果中，有许多Attention事件SQL SERVER所在的机器并没有显得相当忙碌，如CPU、内存或硬盘、网络等硬件使用率并不很高，但效率不好。或某个作业持续高度使用，但作业一直做不完，导致其占有资源无法释放第48页/共53页产生阻塞的原因产生阻塞的原因：费时的查询或事务不正确的事务或事务隔离级别配置嵌套事务未正确处理未侦测到的分布式死锁编译存储过程导致阻塞减少锁定对索引的要求数据表最好要有聚集索引聚集索引不要太大，因所有非聚集索引放有其键值不能用经常变更的字段做聚集索引，因为聚
33、集索引一改变，所有非聚集索引都需要修改键值，导致大量的锁定。索引不能建太多，浪费维护资源。第49页/共53页硬盘子系统硬盘子系统一般来说是数据库的性能瓶颈，因为它是整个系统中运作最慢的部分数据库系统时时刻刻对硬盘完成插删改和查询的操作，硬盘子系统性能好，自然能提升整体性能通常的解决方案是通过多个硬盘合作，平均分散工作量来提升效率由于存取数据模式不同，数据库文件和Log文件和操作系统的内存交换文件最好放在不同的物理硬盘。SQL SERVER与“恢复间隔”配置相关运作是检查点时间发生的频率默认“恢复间隔”配置选项配置值为0，可能导致数据库几乎每分钟要发生一次以上的检查点事件，若觉得检查太频繁而导致
34、太多的硬盘I/O有损性能，可尝试将配置加大第50页/共53页硬盘子系统设计RAID机制RAID可以不仅可以提升存取效率，还可以加强数据储存的容错能力RAID 0、RAID 1、RAID 3、RAID 5、RAID 0+1文件组文件组的运行原理是靠多个硬盘同时存取，由于数据打散在多个硬盘上，多个硬盘一起运行，可以较有效率地存取不同对象：数据表、索引等可考虑分散在不同的文件组不同使用模式的数据文件可通过文件组配置到不同的硬盘，如：系统数据库文件、用户数据库频繁操作的多个表以及日志文件可以分开存放到不同硬盘。RAID效率大于文件组，因为RAID控制卡一般有很大的缓存区第51页/共53页其它应注意的问题注意批处理工作执行的时段分布各批处理工作不要集中在相同的时段批处理一般采用均分时段的周期运行，如事务记录备份，由于周期运行，极有可能在系统的高峰时间启动了备份运行还有如周期性重建索引、复制数据、通过DTS转换数据等都可能挤在系统忙碌是执行避免前端程序直接存取基础数据表尽量让应用程序通过存储过程、视图或用户自定义函数来存取数据若直接存取数据表，当修改数据表结构，重新切割，都会因为前端代码直接存取基础数据表而带来很大麻烦。第52页/共53页感谢您的欣赏！第53页/共53页