MySQL 索引和顺序
这是我一直以来的一个问题.
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:
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_name 而 first_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_name 和 last_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.
相关文章