论文部分内容阅读
摘 要：优化查询有助于提高SQL的检索速度，是一种提高应用系统性能的重要方式。对SQL查询的优化方案主要从优化原理、视图、WHERE子句、存储过程等，并结合实例阐明和验证。对优化查询的掌握，可使学生在数据库应用程序设计方面的课程中更能得心应手。
关键字：优化查询；SQL查询；关系数据库
数据库查询操作是数据库操作的核心操作，对数据的查询效率及处理速度已成为衡量数据库性能的一个重要指标。数据库查询技术也是学生对《数据库原理与应用》这门课程所必须掌握的知识和技能，数据库查询技术的精通也为以后课程的学习（如：C#.NET程序设计）中的数据库应用程序的设计打下良好的基础。
为了优化数据库的性能，简单、有效的方法就是优化查询。
1优化原理和实质
数据查询处理的代价通常取决于对磁盘的访问，因为磁盘比内存的访问速度要慢。对于一个给定的查询，有许多可能的处理策略，复杂查询更是如此[1]。查询优化的实质是在结果正确的前提下，找出与已有表达式等价，但查询效率更高的表达式。
2在教学中要注重的几种典型的优化方法
2.1建立视图
视图可简化用户操作数据的方式，使用户能着重于所感兴趣的特定数据和所负责的特定任务。如果该视图数据规模小于定义的基本表，则基于视图的查询比原查询的速度会快得多。
例：要查询每门课程成绩最高的学生的学号
SELECT 学号，课程号，成绩
FROM 成绩表 A
WHERE 成绩=（SELECT MAX（成绩）
FROM 成绩表B
WHERE A..课程号=B.课程号）
语句缺点：这属嵌套查询，子查询的结果集将作为外层查询的条件，查询效率不高。
解决方案：可先建立视图，求得每门课程的最高分，再与成绩表中比较，获取与视图中最高分数据相等的记录，这就减少了内层连接的数据量，从而提高了检索速度。
用SQL语句优化的步骤为：
首先，创建视图
GREATE VIEW V_max_cnoscore AS
SELECT 课程号，Max(成绩) AS 最高分数
FROM 成绩表 GROUP BY 课程号
其次，利用视图检索数据
SELECT 学号，A.课程号，A.成绩
FROM 成绩表A，V_max_cnoscore B
WHERE A.成绩=B.最高分数 AND A.课程号=B.课程号
2.2查询条件Where子句的优化
2.2.1合理设置WHERE条件的先后顺序。多数数据库都是按从左到右的顺序处理条件，把能过滤更多数据的条件放在前面，过滤少的放后面，便可提高检索速度。
例：SELECT * FROM student
WHERE ssex=’女’ --条件1过滤的数据较少
AND sdept=’计算机系’ --条件2过滤数据比条件1多
上面的SQL就不符合上述原则，可改为：
SELECT * FROM student
WHERE sdept=’計算机系’
AND ssex=’女’
对同一表格进行多个选择运算，选择条件的排序对性能有较大影响，不仅影响索引的选取，而且关系到临时表的大小[2]。因此，要提高查询速度，可将较严格的条件写在前面，较弱的放在后面。
2.2.2避免使用“< >”或“NOT”操作符。“< >”或“NOT”均属于排斥性操作符，不是包括性操作符，这会使系统在搜索数据时无法使用索引，只能直接搜索表中的数据，例：
（1）SELECT 学号 FROM 成绩表 WHERE 成绩< >80
（2)SELECT 学号 FROM 成绩表 WHERE NOT(成绩<>80)
（3)SELECT 学号 FROM 成绩表 WHERE成绩< 80 OR 成绩>80
比较以上三条语句，可知第3条可使用索引查询，它的查询速度最快。
2.2.3恰当运用“OR扩展”技术。该技术是把在Where子句中带有OR的查询转换成包含多个UNION ALL的查询。
例：查询计算机系县年龄不大于20岁的学生。
常规的SQL语句：
SELECT 学号，姓名 FROM 学生表 WHERE 系名=’计算机系’ OR 年龄<=20
假定表中“系名”和“年龄”两个字段上创建了索引，以上的查询可能不会使查询优化器使用索引，从而查询效率降低，可改进为：
SELECT 学号，姓名 FROM 学生表 WHERE 系名='计算机系'
UNION ALL SELECT 学号，姓名 FROM 学生表 WHERE 年龄<=20
2.2.4合理减少LIKE条件的使用。在SQL查询中，LIKE条件使用的神奇之处在于其与通配符匹配所提供的模糊查询功能。但在字符串的比较中，有时大量字符的逐个比较，也会大大降低查找效率。
例：图书按26个英文字母分为26类，查找所有属于‘A’类图书的销售金额。
SELECT 图书类号, SUM (数量*单价) FROM 销售
WHERE 图书类号LIKE′A%′GROUP BY图书类号
优化为精确查询：
SELECT 图书类号，SUM(数量*单价) FROM销售
WHERE 图书类号>=′A′AND图书类号<′B′
GROUP BY图书类号
2.3正确使用子查询“展平”技术子查询“展平”技术就是指将子查询转变为半连接，连接或者反连接，从而将查询优化。
例：找出成绩大于等于90分的学生的姓名。
SELECT 姓名 FROM 学生表 WHERE 学号IN
(SELECT 学号FROM 成绩表WHERE 成绩>=90)
语句缺点：需要搜索学生表的每一行，来查找所有满足子查询条件的记录。
解决方案：将学生表作为连接的内表，查询将作为通常的连接来执行。
优化后的SQL语句：
SELECT 姓名FROM (SELECT 学号 FROM 成绩表
WHERE成绩>=90) A，学生表 B WHERE A.学号=B.学号
2.4善于使用存储过程
存储过程是SQL语句和可选控制流语句的预编译集合，一个名称存储并作为一个单元处理。善于使用存储过程，将使SQL变得更加灵活和高效。
例：查询某个学生某门课程的考试成绩，列出学生的姓名、课程名及成绩
CREATE PROC pro_student_degree
@stu_name char(12)，@course_name char(18) AS
SELECT sname，cname，degree
FROM student A INNER JOIN sc B ON A.sno=B.sno
INNER JOIN course C ON C.cno=B.cno
WHERE sname=@stu_name AND cname=@course_name
以上是一个带有两个输入参数的简单存储过程，只需执行一个简单的SQL语句——“EXEC pro_student_degree‘刘娜’,’数据库原理’”就可完成查询。
编写性能优化的SQL语句是提高数据库系统的重要因素，随着数据库管理系统中数据量的增大，如何提高查询速度与效率也是计算机软件开发的一个时代要求。实现优化查询的方法很多，在使用中，要根据具体情况权衡利弊，使数据库查询性能最优。在学生的教学中要注重培养学生的动手能力，使学生养成编写优化的SQL语句的习惯。
参考文献：
[1]王振辉,吴广茂.SQL查询语句优化研究[J].计算机应用，2005，（12）:208.
[2]SQL查询优化注意where条件的顺序.http://alinw.blog.hexun.com/33996498_d.html. 2009-06-18.
[3]李增祥.数据库SQL查询技术的优化策略[J].消费导刊，2009，（05）:205.
[4]赵松涛.SQL SERVER2000应用及实例集锦[M].北京：人民邮电出版社，2002.139.
关键字：优化查询；SQL查询；关系数据库
数据库查询操作是数据库操作的核心操作，对数据的查询效率及处理速度已成为衡量数据库性能的一个重要指标。数据库查询技术也是学生对《数据库原理与应用》这门课程所必须掌握的知识和技能，数据库查询技术的精通也为以后课程的学习（如：C#.NET程序设计）中的数据库应用程序的设计打下良好的基础。
为了优化数据库的性能，简单、有效的方法就是优化查询。
1优化原理和实质
数据查询处理的代价通常取决于对磁盘的访问，因为磁盘比内存的访问速度要慢。对于一个给定的查询，有许多可能的处理策略，复杂查询更是如此[1]。查询优化的实质是在结果正确的前提下，找出与已有表达式等价，但查询效率更高的表达式。
2在教学中要注重的几种典型的优化方法
2.1建立视图
视图可简化用户操作数据的方式，使用户能着重于所感兴趣的特定数据和所负责的特定任务。如果该视图数据规模小于定义的基本表，则基于视图的查询比原查询的速度会快得多。
例：要查询每门课程成绩最高的学生的学号
SELECT 学号，课程号，成绩
FROM 成绩表 A
WHERE 成绩=（SELECT MAX（成绩）
FROM 成绩表B
WHERE A..课程号=B.课程号）
语句缺点：这属嵌套查询，子查询的结果集将作为外层查询的条件，查询效率不高。
解决方案：可先建立视图，求得每门课程的最高分，再与成绩表中比较，获取与视图中最高分数据相等的记录，这就减少了内层连接的数据量，从而提高了检索速度。
用SQL语句优化的步骤为：
首先，创建视图
GREATE VIEW V_max_cnoscore AS
SELECT 课程号，Max(成绩) AS 最高分数
FROM 成绩表 GROUP BY 课程号
其次，利用视图检索数据
SELECT 学号，A.课程号，A.成绩
FROM 成绩表A，V_max_cnoscore B
WHERE A.成绩=B.最高分数 AND A.课程号=B.课程号
2.2查询条件Where子句的优化
2.2.1合理设置WHERE条件的先后顺序。多数数据库都是按从左到右的顺序处理条件，把能过滤更多数据的条件放在前面，过滤少的放后面，便可提高检索速度。
例：SELECT * FROM student
WHERE ssex=’女’ --条件1过滤的数据较少
AND sdept=’计算机系’ --条件2过滤数据比条件1多
上面的SQL就不符合上述原则，可改为：
SELECT * FROM student
WHERE sdept=’計算机系’
AND ssex=’女’
对同一表格进行多个选择运算，选择条件的排序对性能有较大影响，不仅影响索引的选取，而且关系到临时表的大小[2]。因此，要提高查询速度，可将较严格的条件写在前面，较弱的放在后面。
2.2.2避免使用“< >”或“NOT”操作符。“< >”或“NOT”均属于排斥性操作符，不是包括性操作符，这会使系统在搜索数据时无法使用索引，只能直接搜索表中的数据，例：
（1）SELECT 学号 FROM 成绩表 WHERE 成绩< >80
（2)SELECT 学号 FROM 成绩表 WHERE NOT(成绩<>80)
（3)SELECT 学号 FROM 成绩表 WHERE成绩< 80 OR 成绩>80
比较以上三条语句，可知第3条可使用索引查询，它的查询速度最快。
2.2.3恰当运用“OR扩展”技术。该技术是把在Where子句中带有OR的查询转换成包含多个UNION ALL的查询。
例：查询计算机系县年龄不大于20岁的学生。
常规的SQL语句：
SELECT 学号，姓名 FROM 学生表 WHERE 系名=’计算机系’ OR 年龄<=20
假定表中“系名”和“年龄”两个字段上创建了索引，以上的查询可能不会使查询优化器使用索引，从而查询效率降低，可改进为：
SELECT 学号，姓名 FROM 学生表 WHERE 系名='计算机系'
UNION ALL SELECT 学号，姓名 FROM 学生表 WHERE 年龄<=20
2.2.4合理减少LIKE条件的使用。在SQL查询中，LIKE条件使用的神奇之处在于其与通配符匹配所提供的模糊查询功能。但在字符串的比较中，有时大量字符的逐个比较，也会大大降低查找效率。
例：图书按26个英文字母分为26类，查找所有属于‘A’类图书的销售金额。
SELECT 图书类号, SUM (数量*单价) FROM 销售
WHERE 图书类号LIKE′A%′GROUP BY图书类号
优化为精确查询：
SELECT 图书类号，SUM(数量*单价) FROM销售
WHERE 图书类号>=′A′AND图书类号<′B′
GROUP BY图书类号
2.3正确使用子查询“展平”技术子查询“展平”技术就是指将子查询转变为半连接，连接或者反连接，从而将查询优化。
例：找出成绩大于等于90分的学生的姓名。
SELECT 姓名 FROM 学生表 WHERE 学号IN
(SELECT 学号FROM 成绩表WHERE 成绩>=90)
语句缺点：需要搜索学生表的每一行，来查找所有满足子查询条件的记录。
解决方案：将学生表作为连接的内表，查询将作为通常的连接来执行。
优化后的SQL语句：
SELECT 姓名FROM (SELECT 学号 FROM 成绩表
WHERE成绩>=90) A，学生表 B WHERE A.学号=B.学号
2.4善于使用存储过程
存储过程是SQL语句和可选控制流语句的预编译集合，一个名称存储并作为一个单元处理。善于使用存储过程，将使SQL变得更加灵活和高效。
例：查询某个学生某门课程的考试成绩，列出学生的姓名、课程名及成绩
CREATE PROC pro_student_degree
@stu_name char(12)，@course_name char(18) AS
SELECT sname，cname，degree
FROM student A INNER JOIN sc B ON A.sno=B.sno
INNER JOIN course C ON C.cno=B.cno
WHERE sname=@stu_name AND cname=@course_name
以上是一个带有两个输入参数的简单存储过程，只需执行一个简单的SQL语句——“EXEC pro_student_degree‘刘娜’,’数据库原理’”就可完成查询。
编写性能优化的SQL语句是提高数据库系统的重要因素，随着数据库管理系统中数据量的增大，如何提高查询速度与效率也是计算机软件开发的一个时代要求。实现优化查询的方法很多，在使用中，要根据具体情况权衡利弊，使数据库查询性能最优。在学生的教学中要注重培养学生的动手能力，使学生养成编写优化的SQL语句的习惯。
参考文献：
[1]王振辉,吴广茂.SQL查询语句优化研究[J].计算机应用，2005，（12）:208.
[2]SQL查询优化注意where条件的顺序.http://alinw.blog.hexun.com/33996498_d.html. 2009-06-18.
[3]李增祥.数据库SQL查询技术的优化策略[J].消费导刊，2009，（05）:205.
[4]赵松涛.SQL SERVER2000应用及实例集锦[M].北京：人民邮电出版社，2002.139.