MySQL如何遍历IN（）和IN（）搜索的复合B树索引(How does MySQL traverse a composite B-tree index for IN() AND IN() searches)

 我一直在研究索引，无法完全理解MYSQL如何能够使用索引来完成索引 
 
IN() AND IN() ... AND IN()
 
 我正在阅读的书中提到，当我们有索引（ a ， b ，...）但用户想要搜索b并且我们在a中的基数较低时，我们可以使用技巧并简单地添加IN（） 
 
WHERE a IN ('x1', 'x2', ... 'all possible values go here') AND `b`>123
 
 假设我们有以下数据 
 
x1 1
x1 4
x1 456
x2 5
x3 1
x3 2
x3 3
x4 1234
 
 它如何遍历此树以满足上面的查询？ 它是否只是为IN-s创建所有可能的组合，并且几乎遍历每个查询的树？ 
 
WHERE `a`='x1' AND `b`>123
WHERE `a`='x2' AND `b`>123
...
 
 因此，由于IN-s的数量增加，所有可能的IN组合的数量急剧增加，我们将不得不为这些组合中的每一个运行B树，从而使这个技巧的用处有限？ 如果上述情况属实，这是否意味着有一些理论上的观点，即用IN输入索引会比不使用索引慢？ 

I've been studying indexes and can't completely understand how MYSQL is capable of using indexes at all for statements like 
 
IN() AND IN() ... AND IN()
 
The book I am reading suggests that when we have an index (a, b, ...) but user wants to search for b and we have a low cardinality in a, we can use a trick and simply add IN()
 
WHERE a IN ('x1', 'x2', ... 'all possible values go here') AND `b`>123
 
Let's say we have the following data
 
x1 1
x1 4
x1 456
x2 5
x3 1
x3 2
x3 3
x4 1234
 
How can it traverse this tree to satisfy the query above? Will it simply create all possible combinations for IN-s and will pretty much traverse tree for each of these queries?
 
WHERE `a`='x1' AND `b`>123
WHERE `a`='x2' AND `b`>123
...
 
therefore making this trick of limited usefulness because as number of IN-s increases, the number of all possible IN combinations increases dramatically and we'll have to run through the B-tree for each of these combinations? If the above is true, does this mean that there is some theoretical point where tricking an index with IN will get slower than not using index at all?

原文：https://stackoverflow.com/questions/9568589