问题描述
假设我正在运行一个查询：
Suppose I'm running a query that has:
WHERE column1 = "value1"
AND column2 = "value2"
column1 已编入索引，并且 column2 不是。我的 WHERE 子句的顺序是否重要？我应该首先在索引列上运行子查询吗？或者，SQL是否足够智能，可以首先自动查询索引列？
column1 is indexed, and column2 is not. Does the order of my WHERE clause matter? Should I run a subquery over the indexed column first? Or, is SQL smart enough to automatically query over the indexed column first?
推荐答案
SQL语句中的顺序无关紧要，当然也不适用于未覆盖索引的索引（多个列） 。
The order in the SQL statement does not matter, certainly not for indexes that are not covering indexes (more than one column).
覆盖索引要求查询中至少有一列的引用，从列表的左侧开始。 IE：定义为column1，column2，column3的覆盖索引需要查询至少引用column1才能使用索引。只引用column2或column2和column3组合的查询不会使用覆盖索引。
Covering indexes require that there be a reference in the query for at least one column, starting from the left of the list. IE: A covering index defined as "column1, column2, column3" needs queries to at least reference column1 in order to use the index. A query that only has references to either column2, or a combination of column2 and column3 would not use the covering index.
也就是说，优化程序的索引决策由表统计和索引在查询时的碎片程度。这些都不是自我维护的，因为根据数据量可能非常耗时（因此您不希望它一直发生）。索引不能保证索引永远都会被使用。
That said, index decisions by the optimizer are determined by table statistics & how fragmented the index is at the time of the query. Neither of these is self-maintaining, because depending on the amount of data can be very time consuming (so you wouldn't want it happening all the time). Having an index doesn't guarantee the index will always be used.
索引也不是ANSI，但令人惊讶的是供应商（MySQL，Oracle等）具有相对类似的语法和放大器;命名。
Indexes are also not ANSI, but surprisingly vendors (MySQL, Oracle, etc) have relatively similar syntax & naming.
这篇关于WHERE子句中的Column Order对于Index Selection是否重要？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！