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

* 图4.2 有聚集索引时执行情况 数据库技术及应用 华中科技大学管理学院 在线教务辅导网： 教材其余课件及动画素材请查阅在线教务辅导网 QQ:349134187 或者直接输入下面地址： 第四章 查询优化 查询优化是提高查询效率和系统性能的关键。本章介绍查询优化的重要性，查询优化的基本方法和技术，重点是使用SQL时常用的查询优化方法。 主要内容 4.1 查询优化概述 4.2 基于索引的优化技术 4.3 SQL中常用的优化规则 4.1 　查询优化概述 SQL语句优化的主要原因 （1）SQL语句的效率对数据库系统的性能起到了决定性的作用。 （2）SQL语句消耗了70%~90%的数据库资源，所以要优化SQL语句，合理使用数据库资源。 （3）对SQL语句进行优化不会影响程序逻辑，SQL语句的优化在时间成本和风险上的代价都很低。 （4）不需要硬件的投资和额外的开销，但却会产生明显的效果。 （5）采用好的查询策略会给查询技术带来极大的优化，还可以节省极大的经济开销。 4.2 基于索引的优化技术 4.2.1 聚集索引和非聚集索引 聚集索引可以实现以最快的速度缩小查询范围和最快的速度进行字段排序，这对于范围查询和查询中的数据必须被排序来匹配聚集键的情况而言是非常有用的。 非聚集索引更适于处理从表中只返回一行或几行的查询。 例4-2 在Card表中，查询饭卡余额在150到200之间的饭卡。 执行这个查询的SQL语句如下： SELECT * FROM Card WHERE Balance BETWEEN 150 AND 200 当Card表上的Balance列上有非聚集索引时，这条语句的执行时间如图4.1所示。 删除Card表上的Balance列上的非聚集索引，建立聚集索引重新执行例4-2，这时SQL语句的执行时间如图4.2所示。 图4.1 有非聚集索引时执行情况 图4.2 有聚集索引时执行情况 4.2.2 合理使用索引 1）在经常被作为查询条件使用的列上建立索引 2）在频繁进行排序或分组的列上建立索引 3）在值域很大的列上建立索引 4）当数据表更新大量数据时，删除并重建索引可以提高更新速度 5）组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。 6）在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不要建立索引。 1）在经常被作为查询条件使用的列上建立索引 例4-3 利用消费记录表Table_Salebill，统计每张饭卡的消费总额。 SELECT S.CardID,SUM(Payamount) FROM Table_Card1 C JOIN Table_Salebill S ON C.CardID=S.CardID GROUP BY S.CardID 图4.3 饭卡号列上都没有索引的执行情况 图4.4 饭卡号列上都有索引的执行情况 2）在频繁进行排序或分组的列上建立索引 例4-4 查询每个学院的学生数目。 SELECT School,COUNT(*) FROM Table_Student GROUP BY School 图4.5 School上无索引的执行情况 图4.6 School上有索引的执行情况 3）在值域很大的列上建立索引 例4-6 查询Card表中余额少于5元的饭卡号和余额。 SELECT CardID,Balance FROM Table_card1 WHERE balance5 图4.9 Balance上无索引的执行情况 图4.10 Balance上有索引的执行情况 4）当数据表更新大量数据时，删除并重建索引可以提高更新速度 例4-8：已知学生表中有一百万条数据，若再向该表中插入十万条数据。 学生表中的已经建立的索引对效率影响很大。 图4.11 Sex上无索引的执行情况 图4.12 Sex上有索引的执行情况 5）组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列。 例4-9 查询学院“1”内的男生数量。 SELECT COUNT(*) FROM Table_Student WHERE School=1 AND Sex=M 图4.13 School和Sex上有组合索引的执行情况 图4.14 Sname和CardID上有组合索引的执行情况 6）在条件表达式中经常用到的不同值较多的列上建立检索，在不同值少的列上不要建立索引。 比如在雇员表的“性别”列上只有“男”与“女”两个不同值，因此就没必要建立索引。 例4-10 查询“挂失”的饭卡的饭卡号及余额。 SELECT CardID ,Balance FROM Table_Card WHERE State=1 图4.15 S