数据库技术与应用 教学课件 作者 张千帆chapter4.ppt

《数据库技术与应用 教学课件 作者 张千帆chapter4.ppt》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、数据库技术及应用华中科技大学管理学院第四章查询优化查询优化是提高查询效率和系统性能的关键。本章介绍查询优化的重要性，查询优化的基本方法和技术，重点是使用SQL时常用的查询优化方法。主要内容4.1查询优化概述4.2基于索引的优化技术4.3SQL中常用的优化规则4.1查询优化概述SQL语句优化的主要原因（1）SQL语句的效率对数据库系统的性能起到了决定性的作用。（2）SQL语句消耗了70%~90%的数据库资源，所以要优化SQL语句，合理使用数据库资源。（3）对SQL语句进行优化不会影响程序逻辑，SQL语句的优化在时间成本和风险上的代价都很低。（4）不需要硬件的投资和额外的开销，但却会产

2、生明显的效果。（5）采用好的查询策略会给查询技术带来极大的优化，还可以节省极大的经济开销。4.2基于索引的优化技术4.2.1聚集索引和非聚集索引聚集索引可以实现以最快的速度缩小查询范围和最快的速度进行字段排序，这对于范围查询和查询中的数据必须被排序来匹配聚集键的情况而言是非常有用的。非聚集索引更适于处理从表中只返回一行或几行的查询。例4-2在Card表中，查询饭卡余额在150到200之间的饭卡。执行这个查询的SQL语句如下：SELECT*FROMCardWHEREBalanceBETWEEN150AND200当Card表上的Balance列上有非聚集索引时，这条语句的执行时间如图4

3、.1所示。删除Card表上的Balance列上的非聚集索引，建立聚集索引重新执行例4-2，这时SQL语句的执行时间如图4.2所示。图4.1有非聚集索引时执行情况图4.2有聚集索引时执行情况4.2.2合理使用索引1）在经常被作为查询条件使用的列上建立索引2）在频繁进行排序或分组的列上建立索引3）在值域很大的列上建立索引4）当数据表更新大量数据时，删除并重建索引可以提高更新速度5）组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。6）在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不要建立索引。1）在经常被作为查询条件使用的列上建立索引例4-3利用消

4、费记录表Table_Salebill，统计每张饭卡的消费总额。SELECTS.CardID,SUM(Payamount)FROMTable_Card1CJOINTable_SalebillSONC.CardID=S.CardIDGROUPBYS.CardID图4.3饭卡号列上都没有索引的执行情况图4.4饭卡号列上都有索引的执行情况2）在频繁进行排序或分组的列上建立索引例4-4查询每个学院的学生数目。SELECTSchool,COUNT(*)FROMTable_StudentGROUPBYSchool图4.5School上无索引的执行情况图4.6School上有索引的执行情况3）在值

5、域很大的列上建立索引例4-6查询Card表中余额少于5元的饭卡号和余额。SELECTCardID,BalanceFROMTable_card1WHEREbalance<5图4.9Balance上无索引的执行情况图4.10Balance上有索引的执行情况4）当数据表更新大量数据时，删除并重建索引可以提高更新速度例4-8：已知学生表中有一百万条数据，若再向该表中插入十万条数据。学生表中的已经建立的索引对效率影响很大。图4.11Sex上无索引的执行情况图4.12Sex上有索引的执行情况5）组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。例4-9查询学院“1”内的男生数

6、量。SELECTCOUNT(*)FROMTable_StudentWHERESchool='1'ANDSex='M'图4.13School和Sex上有组合索引的执行情况图4.14Sname和CardID上有组合索引的执行情况6）在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不要建立索引。比如在雇员表的“性别”列上只有“男”与“女”两个不同值，因此就没必要建立索引。例4-10查询“挂失”的饭卡的饭卡号及余额。SELECTCardID,BalanceFROMTable_CardWHEREState='1'图4.15State上无索引的执行情况图4.16State上有

7、索引的执行情况4.3SQL中常用的优化规则1）提高SQL语句的易读性2）避免使用逻辑运算OR，用IN来代替OR3）避免相关子查询4）避免使用含LIKE关键字的表达式5）避免在索引的字段上进行任何操作(包括函数操作)6）使用正逻辑操纵，不使用非逻辑操纵4.3SQL中常用的优化规则7）用集合运算UNION代替逻辑运算OR8）使用临时表，加快查询速度9）避免使用外连接10）尽量避免使用非打头字母搜索11)EXISTS比IN性能好12）存储过程13）不要随意使用游标2）避免使