是否有一个订购MYSQL复合索引的最佳方法？ [英] Is there an optimal method for ordering a MYSQL composite index?

问题描述

我正在考虑向MYSQL数据库中的表添加一个复合索引，该数据库的大小可能是几百万行。复合材料将包含两个varchar列以及三个int列。我的问题如标题所述：是否存在创建此综合指数的最佳顺序？例如，其中一个int行可能只有6个可能的值，那么该列是否更接近索引定义的前端？同样，其中一个varchar列可能有数百万个不同的值，如果它们接近索引定义的前面或后面？

I am looking into adding a composite index to a table in a MYSQL database which will likely be several million rows in size. The composite will be comprised of two varchar columns as well as three int columns. My question is as stated in the title: is there an optimal order in which to create this composite index? For instance, one of the int rows will likely only have 6 possible values, would it better for that column to be closer to the front of the index definition? Likewise, one of the varchar columns will likely have millions of different values, should that be near the front or back of the index definition?

推荐答案

根据经验，在多列索引中，您希望具有最高基数的列，或者换句话说，最大数量的不同值，在索引中排在第一位。

As a rule of thumb, in a multi-column index, you want the columns that have the highest cardinality, or in other words, the highest number of distinct values, to come first in the index.

为了更准确，您希望首先匹配搜索条件最少的列，以便尽可能缩小结果集，但一般来说，它与最高基数。

To be more accurate, you want the column with the fewest possible matches to your search criteria first so you can narrow the result set down as much as possible, but in general, it's the same as the highest cardinality.

因此，在您的示例中，您将希望具有数百万个不同值的列在仅具有6个不同值的列之前位于索引中值。

So, in your example, you'll want the column that will have millions of distinct values to be in the index before the one with only 6 distinct values.

假设您只选择数百万个值中的一行，它可以让您更快地消除更多行。

Assuming you're selecting only one row out of the millions of values, it allows you to eliminate more rows faster.

考虑两列相似的红衣主教y，将较小的一个放在第一个（VARCHAR列之前的INTEGER列），因为MySQL可以更快地比较和迭代它们。

When considering two columns of similar cardinality, put the smaller one first (INTEGER columns before VARCHAR columns) because MySQL can compare and iterate over them faster.

一个警告是，如果你选择范围（例如。 WHERE datecol> NOW（）），然后你想要最右边的范围列，你的列有一个常量（例如 WHERE id = 1 ） 往左边。这是因为您的索引只能用于搜索和排序到第一个范围值的点。

One caveat is that if you are selecting with ranges (eg. WHERE datecol > NOW()), then you want the range columns farthest to the right, and your columns with a single constant (eg. WHERE id = 1) to the left. This is because your index can only be used for searching and ordering up to the point of the first range value.

这篇关于是否有一个订购MYSQL复合索引的最佳方法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！