找到多列索引的最优顺序 [英] finding the optimal order of multi-column index

问题描述

所以，假设我有一个包含两个索引的表 - 一个在a列上，另一个在a，b和c列上。

So let's say I have a table with two indexes - one on column a and one on columns a, b and c.

我注意到了这一点，具体取决于在索引定义中的列的顺序上，MySQL可能会使用单列索引而不是多列索引，即使多列索引中的所有三列都在JOIN的ON部分中被引用。

I've noticed that, depending on the order of the columns in the index definition that MySQL might wind up using the single column index instead of the multi column index, even if all three columns in the multi column index are being referenced in the ON part of the JOIN.

这有点问题......如何找出理想的列排序？你只需要暴力吗？

This kinda begs the question... how does one figure out the ideal ordering of the columns? Do you just have to brute force it?

推荐答案

索引中列的顺序是不是无关。有一种方法。

The order of columns in an index is not irrelevant. There is a method.

• 首先，相等条款中涉及的一个或多个列合并与AND。

• First, one or more columns that are involved in equality terms combined with AND.

WHERE a = 1 AND b = 2

其次，参与范围术语的单个列。要么是这样，要么是一个或多个列参与排序。

Second, a single column involved in a range term. Either that, or else one or more columns involved in sorting.

WHERE a = 1 AND b = 2 AND c > 3

或

WHERE a = 1 AND b = 2
ORDER BY c, d

第三，select-list中引用的列，但未通过搜索或排序引用。

Third, columns referenced in the select-list, but which aren't referenced by searching or sorting.

SELECT x, y, z
. . .
WHERE a = 1 AND b = 2 AND c > 3

这将导致（a，b，c，x，y，z）。

我在演讲中更详细地解释了这一点如何设计索引，真的。

I explain this in more detail in my presentation How to Design Indexes, Really.

这篇关于找到多列索引的最优顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！