|
|
 Blogs
Toad World blogs are a mix of insightful how-tos from Quest experts as well as their commentary on experiences with new database technologies. Have some views of your own to share? Post your comments! Note: Comments are restricted to registered Toad World users.
 Do you have a topic that you'd like discussed? We'd love to hear from you. Send us your idea for a blog topic.
Feb
17
Written by:
中文技术资料库
2/17/2012 3:06 PM
原文：“10 COMMON MISCONCEPTIONS IN SQL TUNING #1” 原文投稿日期：2009年3月13日
作者：虹天软件(珠海)有限公司 杜伟业(Richard To）
这篇博客是关于SQL调优过程中十分常见的误解系列的第一篇。第一篇涉及的误解是，你能够使用数据库SQL优化器提供的估计成本来精确地判断SQL和重写SQL的性能。
SQL调优是一个非常有趣的话题，大部分DBA或者开发员至少都有一些调优SQL的经验。他们从不同的来源学习了一些调优概念，例如因特网，调优书籍，或者他们的同事。在这个领域里没有唯一科学的指导原则或者方法。这给我们的印象是SQL调优更像是一门艺术，而不是一门科学知识。我在这个领域工作了差不多20年，至少有4个数据库平台的SQL调优经验。基本上，我发现每一个数据库内部的优化器行为都与众不同。例如，IBM LUW(Unix)有一个非常强的内部重写能力，它能将你的SQL转换成相对标准的内部语法，此时你无法控制最终的查询计划的生成。相反，Oracle SQL优化器是市场上对语法最敏感的SQL优化器，它允许你影响优化器选择一个更好的执行计划。Microsoft SQL Server 和Sybase在开始时源自同一个架构。因此，它们的SQL优化器行为相似，但是它们现在是一个版本比一个版本更加不同。
我不能说哪个数据库平台的SQL优化方法更好，但是我能够告诉你的是Oracle是所有数据库中最活跃和开放的数据库SQL优化器。我相信Oracle的架构师是最早的少数几个承认他们的SQL优化技术有限制的人。他们没有假装给你提供一个无比强大的数据库内部SQL优化器，相反他们给你提供了提示，SQL Outlines, SQL Profiles，甚至是最新的SQL Plan Baselines特性来帮助你校正他们的SQL优化问题。实际上，这个趋势变得越来越流行。现在，每一个数据库平台都至少给你提供一些特性来影响或者稳定SQL查询计划。
毫无疑问，SQL优化仍然是今天数据库研究的挑战之一。我们每天仍然在忍受低下的SQL性能之苦。因为在SQL优化方面流传着很多常见的误解，我愿意借这个机会做一些澄清，希望在你调优SQL语句时能有所帮助。
第一个误解:
估计的成本是一个判断SQL和重写SQL性能的好方法
一个常见的误解是,人们应该使用成本来判断SQL语句的性能。从统计上来讲，如果我们谈论的是数百条SQL语句的话，这个判断是对的。例如，我们有一个由200条在同一个数据库上执行的SQL组成的池，如果我们从池中选择100条估计成本最高的SQL语句的集合，并且用执行时间与剩下的100条估计成本最低的SQL语句集合比较，结果可能符合预期，成本最低的集合拥有较好的性能。（这个解释是假设不存在任何一条占据绝大部分总执行时间的SQL语句。）但是如果你随机选择两条SQL语句来比较它们的成本和执行时间的话，除非成本相差达到数倍，根据成本估计很难说哪条SQL执行得更快。原因是数据库SQL优化器的成本估计算法不想我们希望的那样精确。不精确有很多原因，特别是针对复杂的SQL语句。成本估计中最常见的错误是对过滤和连接基数的估计。
下面是一个演示数据库优化器将遇到问题的例子..
select emp_name
from employee, manager
where emp_mgr_id=mgr_id
and mgr_name like “% Peter %”
这条SQL获取经理名字匹配字符串“% Peter %”的所有员工的姓名。因为在执行之前没有信息给数据库SQL优化器来估计有多少个经理的名字能匹配字符串“% Peter %”，所以SQL优化器在执行计划的第一步不能为过滤步骤做出精确的估计。它将会在这一步给出一个主观的数字。另外一个问题是对join连接基数的估计。在一个非常理想的情况下，emp_mgr_id在employee表中均匀分布，SQL优化器能够使用emp_mgr_id的统计柱状图来初略地估计匹配的记录数，但是精确度却依赖于第一步做出的主观数字。相反，在真实生活中，emp_mgr_id在employee表中的分布可能是倾斜的，因此，SQL优化器能够对join基数做出精确的成本估计只能靠运气。
让我给你另外一个更加数学上的例子：
select *
from A,B
where A.f1 = B.key
在这个例子中，数据库SQL优化器在精确估计join连接结果的基数时没有问题，因为从两个表获取数据都没有经过过滤条件。理论上，数据库优化器做出100%精确的成本估计不应该有任何问题，但是一个正确的基数估计并不意味着100%精确的成本估计。为什么呢？
为了简化讨论，让我假设SQL优化器只能为这条SQL产生两个查询计划。第一个查询计划是一个嵌套循环连接（Nested Loop Join），A表使用B.key来通过索引查找B表。另外一个计划是排序和并连接（Sort Merge Join），A表和B表被排序后和并在一起。让我们假设两个表有相同的结构和相同的记录数N来进一步简化讨论。
嵌套循环的成本是 ~ N*log2N/2 ~ O(NlogN),
其中log2N/2是平衡B数索引的平均深度并且假设每一个父节点有两个孩子。
排序和并的成本将是取决于自然顺序，最多O(NlogN)。
因此，你会发现两个join连接方法有相似的排序速度。不同的将是单个操作的规模和成本。让我们进一步简化条件并假设两个join连接的规模和操作成本都一样，然后看看我们能否辨别哪个join连接方法将会使用更少的资源或者执行得更快。答案仍然是“并非100% 准确” !为什么呢？
你可能意识到排序算法的速度取决于输入数据的自然顺序。排号序的数据对合并排序有利，对快速排序有负面影响。因此，尽管我做了很多假设并且将所有事情孤立考虑，我仍然无法为这条简单的SQL 语句100%精确地估计嵌套循环连接和排序合并连接的成本，当然，我这里说的是100%精确的估计。但是对于商业数据库，我们不需要那样严格的要求，也许70%的准确性就足够了，但是请记住这个精确度不是一个常量；你的SQL复杂度越高，精确度就会相应地越低。
结论
上面提到的例子只是数据库优化器不能为你的SQL语句做出精确的成本估计的部分原因。还有很多其它的原因没有在这里讨论。因此，当你下次调优SQL时，如果你测试运行重写SQL或者创建物理索引来代替依赖虚拟索引的成本估计去判断哪个SQL重写或者新索引更好的话，你将会得到最好的结果。我将它作为讨论的第一个话题，因为它是一个非常常见的误解，甚至我们团队的一些高级成员有时也会有这个误解！
|
|