如何优化此SQL查询（使用索引）？ [英] How can I optimize this SQL query (Using Indexes)?
问题描述
有一个查询运行速度比我想要的慢。查询已经是正确的，我已经重构了它，但我不能让它运行得足够快。谓词已尽可能 Sarg-able 。它已经正确地使用了连接，不需要额外的表，并且不会不必要地复制行或使用嵌套的子查询以减慢它的速度。
There is a query which runs more slowly than I would like. The query is correct already, and I have refactored it, but I cannot get it to run quickly enough. The predicates are already Sarg-able where possible. It is already properly using joins, requests no extra tables, and does not unnecessarily replicate rows or use nested subqueries in ways that would slow it down.
我不是DBA，我还不知道如何选择一个好的索引来加快查询速度。我可以改变桌子结构;没有DBA负责数据库，我有权在数据库服务器上这样做。
I’m not a DBA, and I don’t already know how to pick a good index to make my query faster. I can alter the table structures; there is no DBA in charge of the database, and I have the permissions on the database server to do so.
如何优化我的SQL查询？
How do I optimize my SQL Query?
推荐答案
简介：这里有很多要讨论的内容，由于SQL的复杂性，任何人都无法完全帮助您查询 - 您的查询是什么，表的大小是多么重要，以及正在使用的数据库系统是什么。如果您不知道索引是什么，或者如何使用它们，请参阅此处：数据库如何索引工作？。
Intro: There is a lot to talk about here, and because of the complexity of SQL, it's going to be impossible for anyone to help with your query fully – it matters what your Query is, how large the tables are, and what the database system being used is. If you don’t know what indexes are, or how to use them, see here: How does database indexing work?.
预防措施：同样，如果您的系统有DBA，请在索引任何内容之前检查它们，尤其是在实时系统上。如果你对他们很好，他们甚至可以提供帮助。如果系统被许多其他人使用，在更改索引之类的任何内容之前要小心。如果数据用于多种查询类型，请确保您没有在它们上创建大量冲突或重叠的索引。
Precaution: Again, if you have a DBA for your system, check with them before indexing anything, especially on a live system. They can even help, if you're nice to them. If the system is used by many others, be careful before changing anything like indexes. If the data is being used for multiple query types, make sure you aren't creating tons of indexes on them that conflict or overlap.
语法。标准（SQL92）使用： CREATE INDEX [索引名称] ON [表名]（[列名]）。此语法几乎适用于任何系统。如果表上只需要一个索引，并且还没有聚簇索引，则可以使用： CREATE [Unique] Clustered INDEX [索引名称] ON [表名]（[列名]） - 如果不能有多个具有相同值的项目，它应该是唯一的。如果您无法使用此功能，请参阅此帖子以获取更多详细信息：如何对数据库列进行索引。
Syntax. The standard (SQL92) uses: CREATE INDEX [index name] ON [table name] ( [column name] ). This syntax should work on almost any system. If you need only one index on the table, and there is not already a clustered index, you can use: CREATE [Unique] Clustered INDEX [index name] ON [table name] ( [column name] ) - it should be unique if there cannot be multiple items with the same values. If you can't get this to work, see this post for more details: How do I index a database column.
应将哪些表编入索引？任何用于查询的表，尤其是数据是静态的或只获得新值，是一个很好的候选者。如果表在您的查询中，并且有一个连接语句，您可能希望在要连接的列上有索引。
Which tables should be indexed? Any table that is being used for querying, especially if the data is static or only gets new values, is a great candidate. If the table is in your query, and has a join statement, you probably want to have an index on the column(s) being joined.
哪些列应该编入索引吗？有关于选择最佳索引以及如何正确索引数据库的完整书籍。如果您不想深入研究问题，索引的基本经验法则是：按以下顺序索引：
What columns should be indexed? There are full books written on choosing the best indexes, and how to properly index a database. A basic rule of thumb for indexing, if you don't want to dive deep into the problem, is: index by the following, in this order:
- 加入谓词（Table1.columnA上的
= Table2.ColumnA和Table1.columnB = Table2.ColumnQ） - 过滤的列（
其中Table1.columnN ='Bob'和Table1.columnS< 20） - 按/ Group By /等排序（任何如果可能，用于订单/分组的列应该在索引中。）
另外：
- 使用有意义的数据类型 - 如果它是整数或日期，则不作为varchar存储。 （列宽很重要。如果可能，请使用最小的数据类型。）
- 确保您的连接是相同的数据类型 - int到int，varchar到varchar，依此类推。
- 如果可能，请在每个表中的每个连接谓词上使用唯一的非空索引。
-
确保可能的列不是非-空值。 （如果它们不能包含空值，则可以使用以下语法。
- Use data types that make sense - store nothing as varchar if it's an integer or date. (Column width matters. Use the smallest data type you can, if possible.)
- Make sure your joins are the same data type - int to int, varchar to varchar, and so on.
- If possible, use unique, non-null indexes on each join predicate in each tables.
Make sure whatever columns possible are non-null. (If they cannot contain null values, you can use the following syntax.
Alter table Table1
alter column columnN int not null
做所有这些，还有你你会很顺利的。但如果你经常需要这些东西，那就去学习吧！买书，在线阅读，找到信息。那里有很多信息，这是一个很好的信息。深刻的话题，但如果你知道自己在做什么，你可以更好地进行查询。
Do all of this, and you'll be well on your way. But if you need this stuff regularly, learn it! Buy a book, read online, find the information. There is a lot of information out there, and it is a deep topic, but you can make queries MUCH better if you know what you are doing.
这篇关于如何优化此SQL查询（使用索引）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
