多列上的索引顺序 [英] Order of index on multiple columns

问题描述

只是一个简单的问题，但是当你的索引跨越多个列时，你的索引的顺序是否重要？

Just a simple question, but does the order of your index matter when it spans over multiple columns?

例如，查询：

SELECT * FROM my_table WHERE (column_1 = 1 AND column_2 = 2)

如果我想为这样的查询添加索引，这是否重要，如果我的索引创建像这样：

If I want to add an index for a query like this, does it matter if my index is created like this:

CREATE INDEX my_index
ON my_table (column_1, column_2)

或这样：

CREATE INDEX my_index
ON my_table (column_2, column_1)

帮助！

推荐答案

在给出的示例中，列顺序无关紧要。

In the example you give, the column order does not matter.

如果你在一个列上订单就很重要; （col1，col2）上的索引可用于 ORDER BY col1，col2 ，但不能用于 ORDER BY col2，col1 。

It would matter if you order on a column; an index on (col1,col2) can be used for ORDER BY col1, col2 but not for ORDER BY col2, col1.

对于 WHERE （col1，col2）适用于 WHERE col1 = 1 AND col2 = 1 。它也适用于 WHERE col1 = 1 。但它不能帮助 WHERE col2 = 1 。

For WHERE clauses, an index on (col1, col2) works for WHERE col1 = 1 AND col2 = 1. It also works for WHERE col1 = 1. But it can't help with WHERE col2 = 1.

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