mysql - 用于搜索第一列和第三列的 3 列索引？

Question

假设我有一个索引(col1、col2、col3)，它可以对这些查询进行索引搜索:

WHERE col1 = xxx
WHERE col1 = xxx AND col2 = yyy
WHERE col1 = xxx AND col2 = yyy AND AND col3 = zzz

如果我想搜索

WHERE col2 = yyy AND col3 = zzz
WHERE col3 = zzz

我必须创建这些索引:

(col2, col3)
(col3)

对吧？

但是如果我想按以下方式搜索，我是否必须创建另一个索引:

WHERE col1 = yyy AND col3 = zzz

?

我试图搜索答案，但没有找到有关搜索索引中非连续列的任何信息，例如 here .

MySQL 是为此使用 (col1, col2, col3) 还是我必须创建 (col1, col3)？

Answer 1

If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to look up rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3).

MySQL cannot use the index to perform lookups if the columns do not form a leftmost prefix of the index. Suppose that you have the SELECT statements shown here:

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;

If an index exists on (col1, col2, col3), only the first two queries use the index. The third and fourth queries do involve indexed columns, but (col2) and (col2, col3) are not leftmost prefixes of (col1, col2, col3).

也就是说，我已经在超过 7,000 行的表 (InnoDB) 上测试了一些案例。将列重命名为示例中的 col1-3，并仅创建主 ID 索引和多列索引:

指数:

下面的解释显示了 col1、2 和 3 的所有组合都在使用索引:

---

---

---

因此，一旦索引存在，请随意使用任何列组合，因为它将用于 power set 中的任何列集。您的专栏。

---

最后，另一列没有索引，没有使用索引:

---

来源:

• MySQL 文档:Multiple-Column Indexes