Oracle 选择查询性能
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3131784/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle select query performance
提问by ajay
I am working on a application. It is in its initial stage so the number of records in table is not large, but later on it will have around 1 million records in the same table.
我正在处理一个应用程序。处于初始阶段，所以表中的记录数量并不多，但稍后它会在同一张表中大约有100万条记录。
I want to know what points I should consider while writing select query which will fetch a huge amount of data from table so it does not slow down performance.
我想知道在编写 select 查询时应该考虑哪些要点，该查询将从表中获取大量数据，因此不会降低性能。
回答by Jonathan Leffler
First rule:
第一条规则：
- Don't fetch huge amounts of data back to the application.
- 不要将大量数据取回应用程序。
Unless you are going to display every single one of the items in the huge amount of data, do not fetch it. Communication between the DBMS and the application is (relatively) slow, so avoid it when possible. It isn't so slow that you shouldn't use the DBMS or anything like that, but if you can reduce the amount of data flowing between DBMS and application, the overall performance will usually improve.
除非您要显示大量数据中的每一项，否则不要获取它。DBMS 和应用程序之间的通信（相对）较慢，因此请尽可能避免。并不是很慢以至于您不应该使用 DBMS 或类似的东西，但是如果您可以减少 DBMS 和应用程序之间的数据流量，整体性能通常会提高。
Often, one easy way to do this is to list only those columns you actually need in the application, rather than using 'SELECT *' to retrieve all columns when you'll only use 4 of the 24 that exist.
通常，一种简单的方法是仅列出您在应用程序中实际需要的那些列，而不是在您仅使用 24 个现有列中的 4 个时使用“SELECT *”来检索所有列。
Second rule:
第二条规则：
- Try to ensure that the DBMS does not have to look at huge amounts of data.
- 尽量确保 DBMS 不必查看大量数据。
To the extent possible, minimize the work that the DBMS has to do. It is busy, and typically it is busy on behalf of many people at any given time. If you can reduce the amount of work that the DBMS has to do to process your query, everyone will be happier.
尽可能减少 DBMS 必须完成的工作。它很忙，通常在任何给定时间都代表许多人忙。如果您可以减少 DBMS 处理查询所需的工作量，那么每个人都会更开心。
Consider things like ensuring you have appropriate indexes on the table - not too few, not too many. Designed judiciously, indexes can greatly improve the performance of many queries. Always remember, though, that each index has to be maintained, so inserts, deletes and updates are slower when there are more indexes to manage on a given table.
考虑诸如确保表上有适当的索引之类的事情——不要太少，不要太多。如果设计得当，索引可以极大地提高许多查询的性能。但是，请始终记住，每个索引都必须维护，因此当给定表上有更多索引需要管理时，插入、删除和更新会更慢。
(I should mention: none of this advice is specific to Oracle - you can apply it to any DBMS.)
（我应该提一下：这些建议都不是针对 Oracle 的——您可以将其应用于任何 DBMS。）
回答by user373455
To get good performance with a database there is a lot of things you need to have in mind. At first, it is the design, and here you should primary think about normalization and denormalization (split up tables but still not as much as performance heavy joins are required).
要获得良好的数据库性能，您需要牢记很多事情。首先，它是设计，在这里您应该主要考虑规范化和非规范化（拆分表，但仍然没有像需要大量性能的连接那样多）。
There are often a big bunch of tuning when it comes to performance. However, 80% of the performance is determined from the SQL-code. Below are some links that might help you.
在性能方面，通常需要进行大量调整。然而，80% 的性能是由 SQL 代码决定的。以下是一些可能对您有所帮助的链接。
http://www.smart-soft.co.uk/Oracle/oracle-performance-tuning-part7.htm
http://www.orafaq.com/wiki/Oracle_database_Performance_Tuning_FAQ
http://www.smart-soft.co.uk/Oracle/oracle-performance-tuning-part7.htm
http://www.orafaq.com/wiki/Oracle_database_Performance_Tuning_FAQ
回答by Jason
A few points to remember:
要记住的几点：
- Fetch only the columns you need to use on the client side.
- Ensure you set up the correct indexes that are going to help you find records. These can be done later, but it is better to plan for them if you can.
- Ensure you have properly accounted for column widths and data sizes. Don't use an INT when a TINYINT will hold all possible values. A row with 100 TINYINT fields will fetch faster than a row with 100 INT fields, and you'll also be able to fetch more rows per read.
- Depending on how clean you need the data to be, it may be permissable to do a "dirty read", where the database fetches data while an update is in progress. This can speed things up significantly in some cases, though it means the data you get might not be the absolute latest.
- Give your DBA beer. And hugs.
- 仅获取您需要在客户端使用的列。
- 确保您设置了正确的索引以帮助您查找记录。这些可以稍后完成，但如果可以，最好为它们做好计划。
- 确保您已正确考虑列宽和数据大小。当 TINYINT 将保存所有可能的值时，不要使用 INT。具有 100 个 TINYINT 字段的行比具有 100 个 INT 字段的行读取速度更快，而且每次读取时您还可以获取更多行。
- 根据您需要数据的清洁程度，可能允许进行“脏读”，即数据库在更新过程中获取数据。在某些情况下，这可以显着加快速度，尽管这意味着您获得的数据可能不是绝对最新的。
- 给你的 DBA 啤酒。和拥抱。
Jason
杰森
