按子句在索引上创建索引 [英] Create Index ON Column in order by clause

问题描述

一个简单的问题:我应该在 ORDER BY 子句中的select语句中的列上创建索引吗?为什么是?为什么不呢?

Simple question: Should I create index on column which is in select statement in ORDER BY clause? Why yes? Why no?

推荐答案

这不是那么简单.这取决于maaaaany的东西.

这个问题没有那么复杂的方法.
复杂的方法涉及查询优化器和查询计划.优化器可能会决定不使用索引.因此，创建索引以及确切创建内容的决定应取决于优化器的工作方式，但是每个RDBMS都有其自己的查询优化器.但是您只用"SQL"标记了您的问题.
对于SQL Server，请参见以下内容: http://www.qdpma.com/CBO/SQLServerCostBasedOptimizer.html [ ^ ]，这是针对Oracle的: ^ ]， ...但是还有更多.

但是，如果您不想考虑优化器，请为oracle阅读以下内容: http ://docs.oracle.com/cd/E11882_01/server.112/e16638/data_acc.htm#i2769 [http://blogs.technet.com/b/josebda/archive/2009/03/17/indexing-best-practices-for-sql-server-2008.aspx [

It is not that simple. It depends on maaaaany things.

There is a not so complicated and a complicated approach to this question.
The complicated approach involves the query optimizer, and the query plan. The optimizer might decide not to use an index for good. Thus the decision to create an index, and what to create exactly should depend on how the optimizer works, but every RDBMS has it''s own query optimizer. But you tagged your question with "SQL" only.
See this for SQL Server: http://www.qdpma.com/CBO/SQLServerCostBasedOptimizer.html[^], and this one for Oracle: http://docs.oracle.com/cd/B28359_01/server.111/b28274/optimops.htm[^], ... but there are more out there.

But if you don''t want to think about optimizers, read this one for oracle: http://docs.oracle.com/cd/E11882_01/server.112/e16638/data_acc.htm#i2769[^], and this one for SQL server: http://blogs.technet.com/b/josebda/archive/2009/03/17/indexing-best-practices-for-sql-server-2008.aspx[^].

To be short: indexes are really common, but also part of the performance-tuning. An index has it''s expenses: every DML (that) affects it, will cost you, thus you have to keep a balance between what it will bring to you while querying, and what it will cost while modifying the data. Thus it is really a practical decision based on the table schema, the queries you will issue and how the data is modified over time.

这篇关于按子句在索引上创建索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！