Sql 查询优化和分析
假设我的查询性能不佳。您通常如何进行sql优化？在查询执行计划中我首先应该查看哪些内容？有关于这方面的好文章或书吗？
Let's say I have poorly performing query. How do you usually go about sql optimization? What are the first things I should look in query execution plan? Is there a good article or book about this?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
为了利用执行计划（数据库如何执行您的请求的描述），我们必须了解它（数据库）可以使用哪些选项，并判断优化器是否做出了选择是正确的。这需要大量的知识和经验。
对于我正在做的工作（ETL 处理），性能“问题”通常属于两类之一：
对于 (1)，我必须决定是否可以以不同的方式重组数据，以便扫描更少的数据。索引很少使用，因为我对足够大的子集感兴趣，以使索引访问比全表扫描慢。例如，我可能存储数据的水平子集（最近 30 天）或数据的垂直子集（10 列而不是 80 列）或数据的聚合。无论哪种方式都会减少数据的大小以提高处理速度。当然，如果数据只使用一次，我只是将问题转移到其他地方。
对于 (2)，我通常首先检查 xplan 顶行的“基数”（行数）。如果我知道我的查询返回 5,000,000 行，但它显示的是 500，我可以非常确定优化器在某个地方搞砸了。如果总基数在正确的范围内，我会从另一端开始，检查计划的每一步，直到找到第一个大的估计错误。如果基数错误，则该表和下一个表之间的连接方法可能是错误的，并且此错误将级联到计划的其余部分。
谷歌搜索“通过基数反馈进行调整”，你会发现一篇由 Wolfgang Breitling 撰写的论文，他（以更好的方式）描述了上述方法。这真是一本好书！
另外，请务必关注Jonathan Lewis 博客。如果有关 Oracle 优化器的某些内容他不知道，那么就不值得了解。他还写了有关该主题的最好的书。查看基于成本的 Oracle 基础知识。如果我能及时送一本书给自己，那就是这本书了。
Oracle 数据库架构专家，作者：Tom Kyte（“Ask tom”背后的人） ，也是一本很棒的读物。我第一次读到这本书时很失望，因为我一直在寻找“调整技巧”，但没有找到。在我第二次阅读时，我意识到，通过了解数据库的工作原理，您可以通过从一开始“设计性能”而不是在最后“添加”性能来消除整个类别的性能问题:)
SQL Tuning 是另一本很棒的读物，介绍了如何确定最佳执行计划的基础知识。这些知识可以用作对执行计划进行故障排除的一种方法（或强制优化器按照您的方式执行）。
如果你已经做到了这一步，那么现在就是摆弄提示的好时机。以前没有。
In order to make use of the execution plan (which is a description of how the database will implement your request), one has to understand what options are available to it (the database) and make a judgement on whether the choice made by the optimizer was the correct one. This requires a lot of knowledge and experience.
For the work I'm doing (ETL processing), performance "problems" usually falls into one of two categories:
For (1), I have to decide whether I can restructure the data differently so I scan less data. Indexes are seldom of use since I'm interested in large enough subsets to make indexed access slower than a full table scan. For example, I might store a horizontal subset of the data (last 30 days) or a vertical subset of the data (10 columns instead of 80), or an aggregate of the data. Either way will reduce the size of the data to make an increase in processing speed. Of course, if the data is used only once, I've just moved the problem elsewhere.
For (2), I usually start by checking "Cardinality" (num rows) at the top line in the xplan. If I know that my query returns 5,000,000 rows, but it says 500, I can be pretty sure that the optimizer messed up somewhere. If total cardinality is in the right ball park, I start from the other end instead and check each step of the plan until I find the first big estimation error. If the cardinality is wrong, the join method is probably wrong between that table and the next, and this error will cascade through the rest of the plan.
Google for "Tuning by cardinality feedback", and you will find a paper written by Wolfgang Breitling who describes (in a much better way) the abobve approach. It's a really good read!
Also, be sure to hang around Jonathan Lewis Blog. if there is something about the Oracle optimizer he doesn't know, it's not worth knowing. He has written the best book on the subject as well. Check out Cost-Based Oracle fundamentals. If I could send one book back in time to myself, this would be it.
Expert Oracle Database Architecture by Tom Kyte, (the man behind "Ask tom"), is an awesome read as well. My first read of this book was a disappointment, because I was looking for "tuning tips" and found none. On my second read I realized that by knowing how the database works, you can eliminate entire classes of performance problems by "designing for performance" from start instead of "adding" performance at the end :)
SQL Tuning by Dan Tow, is another awesome read for the fundamentals of how exactly one could determine the optimal execution plan. That knowledge can be used as a way of troubleshooting an execution plan (or forcing the optimizer into doing it your way).
If you made it this far, now would be a good time to fiddle around with hints. Not before.
性能调优指南是一个很棒的地方首先，Jonathan Lewis 的 Cost Based Oracle Fundamentals 是关于以下内容的规范参考：优化器正在做什么以及为什么。不过，根据问题的复杂程度，《CBO 基本原理》可能有点过分了。
作为第一步，如果您查看计划中每个步骤的估计基数，询问该基数是否合理是有用的。如果基数合理，Oracle 可能会选择最有效的计划，您需要寻找其他方法来调整查询。另一方面，如果基数非常不正确，则 Oracle 可能选择了性能较差的查询计划，并且需要调整有关统计信息的某些内容。
The Performance Tuning Guide is a great place to start but Jonathan Lewis's Cost Based Oracle Fundamentals is the canonical reference on what the optimizer is doing and why. Depending on the complexity of the problem, CBO Fundamentals may be radical overkill, though.
As a first pass, if you look at the estimated cardinality of each step in the plan, it's useful to ask whether that cardinality is reasonable. If the cardinalities are reasonable, it's likely that Oracle is picking the most efficient plan and you need to look for other approaches to tuning the query. If the cardinalities are wildly incorrect, on the other hand, it's likely that Oracle has chosen a poorly performing query plan and that something about the statistics needs to be adjusted.
Oracle 的 性能调优指南 会是一个不错的选择开始的地方。
Performance tuning guide from Oracle would be a good place to start.