MySQL INDEX 在不同列上连接同一个表

Question

我的数据集的简化版本:

我有一个包含 2 列 col1 和 col2

的表格

我想优化这个查询:

SELECT * FROM mytable a
LEFT JOIN mytable b
ON a.col1 = b.col2

在此表上创建的最佳索引是什么？

• 两个索引:col1 上的索引和 col2 上的另一个索引
• 一个双索引:col1,col2上的索引

让我们把它复杂一点:(我的真实数据结构)

假设我的表中还有一列 extract_date:

SELECT * FROM mytable a
LEFT JOIN mytable b
ON a.col1 = b.col2 AND a.extract_date=b.extract_date

在此表上创建的最佳索引是什么？

• 两个双索引:index1 在 col1,extract_date 上，另一个在 col2,extract_date
• 一个三重索引:col1,col2,extract_date 上的索引

Answer 1

What is the best index to create on this table?

• Two indices: index on col1 and another on col2
• One double index: index on both col1,col2

两列索引不会像两个单列索引那样优化您的查询。

From MySQL manual , 大胆强调我的:

MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first two columns, the first three columns, and so on. If you specify the columns in the right order in the index definition, a single composite index can speed up several kinds of queries on the same table.

从上面可以看出，当前导(最左边)列有约束时，MySQL 引擎可以使用多列索引。

因此您的特定查询不会受益于 col1,col2 上的索引多达两个单独的索引，因为考虑到 = b.col2，该索引不会用于查找你的一部分 JOIN条款。

SELECT * FROM mytable a
LEFT JOIN mytable b
ON a.col1 = b.col2

至于您的“真实”数据结构，上述内容仍然适用。

注意:经验法则是先为相等索引，然后为范围索引。 Markus Winand 在 his book 中支持我处理索引。