BENET3.0第二学期课程-第三章SQL查询优化--理论部分.ppt

BENET3.0第二学期课程,第三章SQL查询优化,理论部分,2,课程回顾,会使用函数处理查询结果会使用聚合函数查询统计数值会使用GroupBy进行分组查询掌握多表联接查询,3,技能展示,理解索引的作用和分类会正确使用索引来提高查询效率会通过查看执行计划来分析查询效率,4,本章结构,SQL查询优化,索引常见类型,使用索引,索引基本原理,管理索引,维护索引统计信息,基本概念,使用索引的查询语句写法,禁用与重建索引,主要特性,整理索引数据碎片,创建索引的过程,执行计划,客户端统计,强制使用指定索引,查询优化概述,5,查询优化概述,对数据库查询操作进行性能的优化以提升查询效率的行为就称为查询优化,办法1:让我走的步数最短，也就是路径最短,办法2:让我经过的堵点最少,问题:如果将查询比为一次旅行的话，那么查询优化是如何让旅行到达目的地时间最短？,6,索引基本原理,基本概念索引的主要特性创建索引的过程,7,索引的基本概念,索引就是通过对数据表的数据建立类似图书目录和图书馆图书索引卡一样的“数据目录”。,1.给图书进行分类，同类的图书放在同一个书架；2.给图书进行编号，编号由类别编号+书编号+书架号组成；3.将编号制作成卡片放到卡片柜里，一个卡片柜放置一个类别的卡片；,1.根据图书类别查找到相应的卡片柜；2.在卡片柜里按照图书名称查找对应卡片和书籍编号；3.根据卡片的书籍编号，找到图书馆里的书架号，从而找到相应书籍；,案例需求:图书馆最新购买了100万本图书.为了更便捷的查找图书,管理员应该如何去做?,案例需求:读者如果希望找到自己需要的图书该如何做呢?,8,索引原理示意,索引字段(姓名),记录地址,Amy,Andy,Bruce,Candy,索引表,学生表,案例需求:希望在数据库中查找Candy的信息,在索引表里，查找姓名Candy的。因为索引里的姓名是顺序排列，在C开头的姓名里查找到Candy,然后根据Candy对应的记录地址，定位到数据表的记录,9,索引主要特性,索引特征当表中的数据发生变化的时候，变化的数据可能同时涉及索引中定义的列，那么索引中的数据也会产生更变。这样会导致数据变更维护的开销增加。所以，索引的建立应该按需而定，不能盲目使用索引的时机数据量越大越适合用索引选择性越好越适合用索引数据表数据变化小适合使用索引,10,创建索引的过程,新建索引为索引命名选择索引列完成索引的创建,11,索引常见类型,聚集索引和非聚集索引组合索引唯一索引主键索引,12,索引,学生表,聚集索引,聚集索引不是通过表中记录地址来定位到表，索引本身就包含表，表中的数据按照索引字段进行排序,13,聚集索引,创建聚集索引选择索引类型为索引命名,14,索引字段(学号),记录地址,Amy,Andy,Bruce,Candy,索引表,学生表,非聚集索引,非聚集索引，是通过索引表中记录地址定位到数据表，,15,组合索引,组合索引，就是包含多个字段的索引创建组合索引,选择索引字段为索引命名,16,唯一索引,可以确保索引列中不包含重复的值,创建唯一索引减少了查询所需遍历的记录，提高了查询效率,勾选唯一索引,17,主键索引,在SQLServer中为表定义主键时将自动创建主键索引主键索引的特征主键索引是唯一索引的特殊类型如果表未创建聚集索引，且在创建主键约束时未指定索引类型，主键约束会自动创建聚集索引主键索引要求主键中的每个值都是唯一的。,18,索引表,建立索引表的目的无需二次定位,可以直接定位数据建立索引表,19,小结,请思考:查询优化的常见方法有哪些?请说出使用索引的时机,即什么情况下使用索引?什么情况下不使用索引?索引的常见类型有哪些?建立索引表的目的是什么?,20,使用索引,执行计划客户端统计使用索引的查询语句的写法强制使用指定的索引,21,执行计划3-1,执行计划的作用执行计划可以是根据SQLServer查询优化器估算的计划,不需要查询语句真正执行,减少数据库负荷使用执行计划的方式不实际执行,只估算出执行计划实际执行SQL语句后,根据实际情况总结出执行计划,22,执行计划3-2,执行计划的常用图标,23,执行计划3-3,案例需求:通过两种执行计划查看在数据库adventureWorks上的查询语句:Select*FromPRODUCTION.WORKORDERWherePRODUCTID=732的执行情况。实施方法一:事先查看执行计划实施方法二:执行查询后显示执行计划,24,客户端统计,客户端统计信息功能获取查询后的实际花销信息,与查询计划一起配合使用分析查询效率更加方便有效实施方法:选择客户统计信息按钮输入SQL语句选择执行计划的全局效果图查看显示的执行计划查看执行计划图查看节点内容,25,使用索引的查询语句的写法2-1,查询语句基本写法:Select字段列表Fromtable1Where条件表达式1and|or条件表达式2and|or条件表达式n;,条件表达式的样式为索引字段条件运算符值,查询语句使用索引的最基本要求就是要以索引字段为查询条件,26,使用索引的查询语句的写法2-2,使用查询语句的注意事项:查找条件中不要将字段进行运算LIKE模糊查询字段的通配符是第一个字符，查询不会使用索引查询条件使用not或者!=操作符时，查询不会使用索引使用组合索引，查询条件必须包含组合索引里第一个索引字段如果查询结果字段与查询条件的字段总是成对出现，建议使用索引表查询查询结果数据大于全部数据20%以上时，数据库不会使用索引,27,查找条件中不要将字段进行运算,案例需求:对比下列语句,分析索引使用情况:1、Select*FromPRODUCTION.WORKORDERWhereProductID+10=7422、Select*FromPRODUCTION.WORKORDERWhereProductID=732,查询没有使用索引的情况查询使用索引的情况,SELECT*FROMPRODUCTION.WORKORDERWHEREProductID+10=742,SELECT*FROMPRODUCTION.WORKORDERWHEREProductID=732,28,LIKE模糊查询字段通配符是第一个字符,案例需求:查询语句Select*FromPRODUCTION.PRODUCTWhereNameLIKE%ecal1分析该查询是否会使用索引？,Select*FromPRODUCTION.PRODUCTWhereNameLikeDecal%执行计划,Select*FromPRODUCTION.PRODUCTWhereNameLike%ecal1执行计划,查询没有使用索引的情况查询使用索引的情况,29,查询条件使用not或者!=操作符时,案例需求:查看Select*FromPRODUCTION.WORKORDERWhereProductID!=732的索引使用情况实施效果:,输入查询语句发现并未应用索引,30,使用组合索引的效果,案例需求:数据表PRODUCTION.WORKORDER建立两个组合索引：索引1:IX_WorkOrder_ProductIDStartDateEndDate包含三个字段(ProductID，StartDate，EndDate)索引2:IX_Workorder_StartdateEnddate包含字段两个字段(StartDate，EndDate)分别执行以下语句,Select*FromPRODUCTION.WORKORDERWherestartdatecast(2001-07-12Asdatetime)Andstartdatecast(2001-07-14Asdatetime)Andenddatecast(2001-07-12Asdatetime)Andstartdatecast(2001-07-12Asdatetime)Andstartdatecast(2001-07-12Asdatetime)Andstartdatecast(2001-07-14Asdatetime),34,禁用与重建索引,案例需求:禁用PRODUCTION.WORKORDER表上的聚集索引，访问表时会提示不允许访问。,实施步骤:选择聚集索引禁用索引尝试打开WorkOrder表,查看效果重建索引重新打开WokrOrder表,查看效果,35,整理索引数据碎片,什么是索引数据碎片,重新组织索引重新生成索引重新组织索引,查看索引碎片,36,维护索引统计信息,索引统计信息的作用手动更新索引的统计信息查看统计信息手动更新统计信息选择更新统计信息复选框完成更新设置数据库关闭自动更新统计信息操作,37,本章结构,SQL查询优化,索引常见类型,使用索引,索引基本原理,管理索引,维护索引统计信息,基本概念,使用索引的查询语句写法,禁用与重建索引,主要特性,整理索引数据碎片,创建索引的过程,执行计划,客户端统计,强制使用指定索引,查询优化概述,BENET3.0第二学期课程,第三章SQL查询优化,上机部分,39,实验案例1：比较查询语句使用索引情况,需求描述：电信公司服务器的默认实例中已经建立了一个数据库Tariffsmall，为了提高数据库的查询速度,需要在数据库中建立并维护索引.在Adventrueworks库的Production.WorkOrder表中分别按照要求创建相应的索引根据要求执行相应的查询语句，比较使用索引的状态,40,实验案例1：比较查询语句使用索引情况,实现思路：根据需要创建正确的索引类型并命名观察查询语句应用创建的索引效果(是否应用了索引)强制查询语句使用索引，观察执行计划的结果禁止索引的使用，观察查询语句的执行效果,41,实验案例1：比较查询语句使用索引情况,学员练习：创建非聚集、非唯一值索引IX_Startdate(Startdate)、IX_WorkOrder_ProductID(ProductID)创建索引IX_StartDate(Startdate,Enddate),观察效果创建索引IX_Workorder_ProductID2(ProductID),观察效果观察实验步骤4的语句执行后使用了哪些索引观察实验步骤5的语句执行后使用了哪些索引观察实验步骤6的语句执行后的执行计划情况观察实验步骤7的语句执行后使用了哪些索引观察按照实验步骤8完成后有什么现象？,40分钟内完成,42,实验案例2：查询语句使用索引的情况,需求描述：电信公司服务器的默认实例中已经建立了一个数据库Tariffsmall，为了提高数据库的查询速度,需要在数据库中建立并维护索引.为Production.WorkOrder表创建并命名索引按要求执行相对应的查询语句，分析使用情况并说明原因,43,实验案例2：查询语句使用索引的情况,实现思路：根据需要创建并命名索引通过执行计划观察查询语句使用索引的情况比较几组语句使用索引的情况并记录,44,实验案例2：查询语句使用索引的情况,学员练习：使用SSMS为数据库中的Production.Workorder表创建索引在SSMS的任务栏上启用预计的执行计划按要求输入教材中提供的查询语句通过“执行计划”窗口观察索引的使用情况将三组语句的执行结果截图并进行比较提交截图、执行过程和分析结果,25分钟内完成,45,实验案例3：索引表对查询计划的影响,需求描述：电信公司服务器的默认实例中已经建立了一个数据库Tariffsmall，为了提高数据库的查询速度,需要在数据库中建立并维护索引.比较相同查询语句在创建索引表前后使用索引的情况,46,实验案例3：索引表对查询计划的影响,实现思路：执行教材提供的查询分析