MySQL 索引和顺序

2021-12-21 00:00:00 indexing mysql sql-order-by

这是我一直以来的一个问题.

This is a question that I've had forever.

据我所知,索引的顺序很重要.所以像 [first_name, last_name] 这样的索引和 [last_name, first_name] 是不一样的,对吧?

As far as I know the order of indices matter. So an index like [first_name, last_name] is not the same as [last_name, first_name], right?

如果我只定义了第一个索引,是否意味着它只会用于

If I only define the first index, does it mean that it will only used for

SELECT * FROM table WHERE first_name="john" AND  last_name="doe"; 

而不是为了

SELECT * FROM table WHERE  last_name="doe" AND first_name="john";

因为我使用的是 ORM,所以我不知道这些列的调用顺序.这是否意味着我必须在所有排列上添加索引?如果我有 2 列索引,这是可行的,但是如果我的索引位于 3 或 4 列上会发生什么?

Since I am using a ORM, I have no idea in which order these columns are going to be called. Does that mean that I have to add indices on all permutations? That is doable if I have a 2 column index, but what happens if my index is on 3 or 4 columns?

推荐答案

当您的查询条件仅适用于索引的 PART 时,索引顺序很重要.考虑:

Index order matters when your query conditions only apply to PART of the index. Consider:

  1. SELECT * FROM table WHERE first_name="john" AND last_name="doe"

SELECT * FROM table WHERE first_name="john"

SELECT * FROM table WHERE last_name="doe"

如果您的索引是 (first_name, last_name),则查询 1 和 2 将使用它,查询 #3 则不会.如果您的索引是 (last_name, first_name) 查询 1 和 3 将使用它,查询 #2 不会.在 WHERE 子句中更改条件顺序在任何一种情况下都不起作用.

If your index is (first_name, last_name) queries 1 and 2 will use it, query #3 won't. If your index is (last_name, first_name) queries 1 and 3 will use it, query #2 won't. Changing the condition order within WHERE clause has no effect in either case.

详情在这里

更新:
如果上述内容不清楚 - MySQL 只能在查询条件中的列形成索引最左边的前缀时使用索引.上面的查询 #2 不能使用 (last_name, first_name) 索引,因为它仅基于 first_namefirst_name 是不是 (last_name, first_name) 索引的最左边前缀.

Update:
In case the above is not clear - MySQL can only use an index if the columns in query conditions form a leftmost prefix of the index. Query #2 above can not use (last_name, first_name) index because it's only based on first_name and first_name is NOT the leftmost prefix of the (last_name, first_name) index.

查询中的条件顺序无关紧要;上面的查询 #1 将能够使用 (last_name, first_name) 索引就好了,因为它的条件是 first_namelast_name 并且它们一起构成 (last_name, first_name) 索引的最左边前缀.

The order of conditions WITHIN the query does not matter; query #1 above will be able to use (last_name, first_name) index just fine because its conditions are first_name and last_name and, taken together, they DO form a leftmost prefix of (last_name, first_name) index.

相关文章