mysql 为什么无法使用范围列后的其他索引

Question

数据表中，我建立了一个组合索引(a,b)，然后使用如下查询

select a, b from TableA from a > 1000 and b = 2

在这种情况下，只能按照最左前缀使用索引，但b=2这个条件无法使用索引了，
这是为什么？

Answer 1

MySQL version 5.6 did not add index condition pushdown before, so the index logic is still like this:

Even for a compound index, start from the first column to determine the index range of the first column. If the range has an = sign, then for the = sign case, determine the index range of the second column and add it to the index result set. How to process each column It's all the same.

After determining the index range, return the table to query the data, and then use the remaining where conditions to filter and judge.

ICP was added after mysql5.6. After the index range is determined, the remaining where conditions will be used to filter the index range again, and then the table will be returned, and the remaining where conditions will be used for filtering judgment. (Reduce the number of records returned to the table).

In addition, I estimate that the amount of your test data should not be large. Otherwise, if the proportion of the index range you determine is too large in the total number of records, the entire table query will not use the index.

Answer 2

Probably the amount of your test data is too small, so there is no point in caring about whether the index is used.

Put 100,000 pieces of data and try again

Answer 3

One more note:
When MySQL once estimates that the number of rows to be checked may be "too many", the range search optimization will not be used.

select a, b from TableA from a > 1000
如果返回的行数太多，将不会使用索引。