Mysql 组合索引最左原则的疑惑

Question

如果test表有一个组合索引(a,b)，执行如下两条语句。
explain select * from test where a=1 and b=1;
explain select * from test where b=1 and a=1;
执行结果显示这两条语句都用索引。根据最左原则，只有第一条使用索引，第二条不使用索引。不明白为什么，求解惑

Answer 1

In the situation listed by the questioner, mysql will optimize the condition order of the where clause to make the query comply with the index order.

To be more specific, the above sql is an intersection query (both are and), and the Index Merge intersection algorithm algorithm is used in mysql to adjust the order of conditional clauses. See the official documentation for a more detailed explanation.