For example, if I have a table with a city and a state column, what is the best way to use the index?
例如，如果我有一个包含城市和州列的表，那么使用该索引的最佳方法是什么？
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?
显然城市将具有最高的基数，所以我应该将该列放在索引中，我应该放置状态还是不重要？
2 个解决方案
#1
1
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.
MySQL复合索引查找必须按索引中定义列的顺序进行。由于您希望MySQL能够通过执行尽可能少的比较来区分记录，而在所有其他条件相同的情况下，您将从复合索引中获益最多，其中列从最高到最低基数排序。
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?
也就是说，假设最终必须针对最高基数列进行比较以区分记录，为什么在最终可能不必要的情况下首先对最低基数列进行强制比较？
#2
2
It does not matter in this case:
在这种情况下无关紧要：
INDEX cs (city, state),
INDEX sc (state, city)
WHERE city = 'Atlanta'
AND state = 'Georgia'
With either index, the drill-down in the BTree will be the same effort, and you will get to the one row just as fast.
使用任一索引，BTree中的向下钻取将是同样的努力，并且您将以同样快的速度进入一行。
(The order of clauses in WHERE does not matter.)
（WHERE中的子句顺序无关紧要。）
(If you are using a "range" test instead of = test, well, that's a different Question.)
（如果你使用的是“范围”测试而不是= test，那么这是一个不同的问题。）
#1
1
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.
MySQL复合索引查找必须按索引中定义列的顺序进行。由于您希望MySQL能够通过执行尽可能少的比较来区分记录，而在所有其他条件相同的情况下，您将从复合索引中获益最多，其中列从最高到最低基数排序。
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?
也就是说，假设最终必须针对最高基数列进行比较以区分记录，为什么在最终可能不必要的情况下首先对最低基数列进行强制比较？
#2
2
It does not matter in this case:
在这种情况下无关紧要：
INDEX cs (city, state),
INDEX sc (state, city)
WHERE city = 'Atlanta'
AND state = 'Georgia'
With either index, the drill-down in the BTree will be the same effort, and you will get to the one row just as fast.
使用任一索引，BTree中的向下钻取将是同样的努力，并且您将以同样快的速度进入一行。
(The order of clauses in WHERE does not matter.)
（WHERE中的子句顺序无关紧要。）
(If you are using a "range" test instead of = test, well, that's a different Question.)
（如果你使用的是“范围”测试而不是= test，那么这是一个不同的问题。）