基于二重模式的SQL查询优化教学实践

　　摘要：查询优化在关系数据库系统中有着非常重要的地位，是影响RDBMS性能的关键因素。本文主要归纳总结实现查询优化应遵循的一些原则和使用技巧，介绍二重查询优化教学模式，以培养学生编写出高质量的SQL语句，提高应用系统的性能。
　　关键词：数据库；SQL查询；教学模式
　　
　　SQL语言的学习是数据库应用技术课程教学的重点内容，学生能否灵活熟练掌握SQL语句，编写出高质量的SQL句子，是学好该课程的关键[1]，也是开发与数据库应用相关软件的基础。因为SQL语句可以有多种不同的写法，所以它易学，但难于精通，是教学难点内容。在此，我们根据多年教学工作经验，在教学中以实际数据库应用系统中数据设计和数据查询的使用为例，分析探索查询优化的方法，采用“数据库设计→设计优化→编写SQL语句→语句优化”的二重优化模式方法，教学效果良好。本文通过归纳总结学习掌握数据设计和SQL查询优化的方法，阐述了SQL优化教学的关键所在。
　　1二重查询优化模式
　　学生学习SQL语言，通常只停留在能查询出所要求的结果，这样的水平并不深究查询效率，更不进行查询优化测试，其原因在于学生还没有开发过数据库应用系统，未能体会到通过优化查询提高系统性能的意义；例如：一个基于B/S(或C/S)模式的数据库应用系统，用户对数据库连接和响应速度很敏感，如果数据库响应速度太慢，不能及时反馈用户要查询的数据，用户就会难以忍受，从而直接影响用户对整个系统的评价。因此，设计、编写高效合理的查询语句，提高数据库响应速度就显得非常重要，当然，这对编程和设计人员的要求也更高一层。在教学中，要阐明
　　
　　优化查询的重要意义，以实际应用讲授查询优化的方法，激发学生的学习兴趣，使学生在编写基本SQL语句的基础上，尽快掌握查询优化技术[2]。
　　1.1查询优化知识体系
　　在大多数实际数据库应用系统中，查询操作在各种数据库操作中所占比重最大，而每个查询操作都会有许多可供选择的执行策略和操作算法，查询优化就是选择一个高效的执行的查询处理策略。查询优化的方法多种多样，按照优化层次可分为代数优化和物理优化[3]。代数优化是指关系代数表达式的优化，即按照一定的规则，改变代数表达式中操作的次序和组合，使查询执行更高效；物理优化是指存取路径和底层操作算法的选择，其选择依据可以是基于规则的，也可以是基于代价的，还可以是基于语义的。查询优化的理论基础知识体系如图1所示。
　　1.2二重查询优化流程
　　依据查询优化的理论，在数据库应用系统的开发中，为了能够系统地进行查询优化，我们提出二重查询优化模式：数据库设计→设计优化→编写SQL语句→语句优化，即设计优化和语句优化，两次优化，先后顺序并无固定，可循环多次，以达到最好的优化效果。整个模式操作方便、思路清晰，实施关键在于第一重优化和第二重优化的具体方法，文中第2部分、第3部分有详细介绍。查询优化的教学过程流程可以进一步扩展，如图2所示。
　　二重查询优化方法，第一重优化，即设计优化，实施在数据库设计阶段的关系模式优化之后，要通过修改、调整、和重构模式，经过反复尝试和比较，得到最终的优化的关系模式，它是进行语句优化的基础。第二重优化过程是应用程序设计层面，应按照系统支持的各种应用分别试验它们在数据库操作的效率，弄清它们在实际运行中能否完成预定的功能。但并不意味着设计过程结束，就不能再进行调整数据库，在实际运行和维护过程中，调整、修改数据库及其应用的事是常常发生的，因此，二重查询优化，在实施中，并没有固定顺序，且可反复进行，直至达到理想效果。
　　
　　图2二重查询优化教学流程图
　　2设计优化
　　二重查询优化模式，首先是通过数据库合理设计进行优化，对应于优化层次的物理优化，选择合理的存取路径和底层操作算法。一般遵循以下策略和技巧来改善查询计划，提高应用系统的性能。
　　2.1合理的索引设计
　　索引是数据库设计中最重要的数据结构，利用索引可以快速访问数据库表中的特定信息，它是对数据库表中的一个或多个列的值进行排序的结构，数据库性能问题都可以采用索引技术得到解决。在设计数据库关系图中，可以为选定的表创建、编辑或删除索引/键属性页中的每个索引类型，当保存附加在此索引上的表或包含此表数据关系图时，同时被保存。
　　对索引操作，学生一般较容易掌握，学生往往在学习使用索引时，不清楚在何种情况下需使用索引，这是因为学生对应用问题理解不够透彻。所以，教学中要强调说明，在考虑是否为一个列创建索引时，应考虑被索引的列是否以及如何用于查询。
　　2.1.1索引的作用
　　建立索引，基本思路是考虑索引对哪些查询有帮助，通常有如下所列的情况。
　　1) 搜索符合特定搜索关键字值的行(精确匹配查询)；
　　2) 搜索其搜索关键字值为范围值的行(范围查询)；
　　3) 根据联接谓词，在一个表中搜索与另一个表中的某行匹配的行(索引嵌套循环连接)；
　　4) 在不进行显式排序操作的情况下产生经排序的查询输出，尤其是经排序的动态游标；
　　5) 在不进行显式排序操作的情况下，按一种有序的顺序对行进行扫描，以允许基于顺序的操作，如合并联接和流聚合；
　　6) 以优于表扫描的性能对表中所有的行进行扫描，性能提高是由于减少了要扫描的列集和数据总量；
　　7) 搜索插入和更新操作中重复的新搜索关键字值，以实施PRIMARY KEY和UNIQUE约束；
　　8) 搜索已定义了FOREIGN KEY约束的两个表之间匹配的行。
　　2.1.2使用索引要遵循的原则
　　我们要考虑到索引的使用要恰到好处，指导学生使用索引要遵循如下原则。
　　1) 主键上建立索引是首选，另外对连接中频繁使用的列(包括外键)也应作为建立索引考虑的选项。不经常连接的字段则由优化器自动生成索引；引导学生学会使用SQL事件探查器和索引优化向导帮助分析查询，确定要创建的索引。
　　2) 覆盖的查询可以提高性能。覆盖的查询是指查询中所有指定的列都包含在同一个索引中。
　　3) 对于不需要修改数据的查询(SELECT语句)，大量的索引有助于提高性能。而如果一个表创建有大量的索引会影响INSERT、UPDATE和DELETE语句的性能，因为在表中的数据更改时，所有的索引都须进行适当的调整。
　　4) 在频繁进行排序或分组(即进行GROUP BY分组或ORDER BY排序)的列上建立索引；在不同值较多的列上建立检索，而在不同值很少的列上不建立索引。
　　5) 可以在视图和计算列上指定索引，这样显得更方便和快捷。
　　2.1.3对于索引种类使用策略
　　如果需要排序的列有多个，可以在这些列上建立组合索引。经常同时存取多列，且每列都含有重复值可考虑建立组合索引；可考虑建立聚簇索引的情况有：包含有大量非重复值的列；使用BETW