MySQL索引和顺序

Question

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

据我所知，索引的顺序很重要。因此，像[first_name，last_name]这样的索引与[last_name，first_name]不同，对吗？

如果我只定义了第一个索引，那么它只用于什么呢？

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列上呢？

- Julien Genestoux

Answer 1

当你的查询条件只适用于索引的一部分时，索引的顺序很重要。考虑以下内容：

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

2. SELECT * FROM table WHERE first_name="john"

3. SELECT * FROM table WHERE last_name="doe"

如果你的索引是 (first_name, last_name)，则查询1和2将使用它，查询#3不会使用它。
如果你的索引是 (last_name, first_name)，则查询1和3将使用它，查询#2不会使用它。在任一情况下，更改WHERE子句中条件的顺序都没有影响。

详情请参见这里

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

查询中条件的顺序无关紧要，查询#1可以完全使用(last_name, first_name)索引，因为它的条件是first_name和last_name，并且在一起，它们形成了(last_name, first_name)索引的最左前缀。

- ChssPly76

---

那么我猜SELECT * FROM table WHERE last_name="doe" AND first_name="john"不行？如果是这样的话，由于我的ORM隐藏了所有查询构建，我可能必须定义这2个索引...这真的很糟糕，因为我需要定义3列的索引... - Julien Genestoux

不，您误解了。last_name="doe" AND first_name="john" 的查询将使用 (first_name, last_name) 或 (last_name, first_name) 索引非常好。 - ChssPly76

一个问题：对于带有2个AND条件的查询，如果索引与查询中的条件相比是“反向”的，速度方面是否会有影响？还是这取决于这些列的基数？ - Mihai

一个常见问题的很好的总结 - Andrew Cetinic

在这种情况下，INDEX(last_name, first_name)比其他选项更好。这是因为在first_name上有“范围”。请参考 Cookbook。 - Rick James

如果我有索引(first_name, last_name, full_name)和查询: where first_name like"doe" and full_name like "john_doe"，这会使用索引吗？ - Chinh Nguyen

Answer 2

ChssPly76正确指出布尔表达式的顺序不必与索引中列的顺序匹配。布尔运算符是交换律的，MySQL优化器通常能够知道如何将表达式与索引匹配。

我还想补充一点，你应该学习如何使用MySQL的EXPLAIN功能，这样你就可以自己看到优化器为给定查询选择了哪些索引。

- Bill Karwin

---

MySQL Workbench在“执行计划”下显示了EXPLAIN信息的图形表示。 - Sayo Oladeji

Answer 3

为了让一切都变得清晰明了，为什么不扩展答案呢。

如果表有一个多列索引，优化器可以使用索引的左前缀来查找行。例如，如果您在 (col1, col2, col3) 上有一个三列索引，则可以在 (col1)、(col1, col2) 和 (col1, col2, col3) 上进行索引搜索。

如果列不形成索引的左前缀，则 MySQL 无法使用索引。假设您有以下 SELECT 语句：

SELECT * FROM tbl_name WHERE col1=val1;
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

SELECT * FROM tbl_name WHERE col2=val2;
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

如果在 （col1，col2，col3） 上存在索引，则仅前两个查询使用该索引。第三个和第四个查询确实涉及索引列，但是 （col2） 和 （col2，col3） 不是 （col1，col2，col3） 的左前缀。- MySQL dev

- Berky

---

这个查询是否也使用了索引？SELECT * FROM tbl_name WHERE col1=val1 AND col3=val3; - Chinh Nguyen