哪个列放在索引中？更高还是更低的基数？ [英] Which column to put first in index? Higher or lower cardinality?

问题描述

例如，如果我的表有一个 city 和一个州列，那么最好的方法是什么使用索引？

For example, if I have a table with a city and a state column, what is the best way to use the index?

显然 city 将具有最高的基数，所以我应该将该列放在第一位索引，我应该放状态还是不重要？

Obviously city will have the highest cardinality, so should I put that column first in the index, should I put state or doesn't it matter much?

推荐答案

MySQL复合索引查找必须按顺序进行列是在索引中定义的。由于您希望MySQL能够通过执行尽可能少的比较来区分记录，并且在所有其他条件相同的情况下，您将从复合索引中获益最多，其中列从最高到最低基数排序。

MySQL composite index lookups must take place in the order in which the columns are defined within the index. Since you want MySQL to be able to discriminate between records by performing as few comparisons as possible, with all other things being equal you will benefit most from from a composite index in which the columns are ordered from highest- to lowest-cardinality.

也就是说，假设最终必须对最高基数列进行比较以区分记录，为什么在最终可能的情况下首先对最低基数列进行强制比较？不必要的？

That is, assuming comparisons must eventually be performed against the highest cardinality column in order to discriminate records, why force comparisons to take place first against the lowest cardinality column when ultimately that may be unnecessary?

这篇关于哪个列放在索引中？更高还是更低的基数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！