【MYSQL】一个关于最左匹配原则的问题

最左匹配原则

最左原则顾名思义就是从最左边开始匹配的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式，其针对的是组合索引（又名联合索引）。

问题

数据库里有四个字段,id,a,b,c，其中id是主键，有两个 联合索引，ab，ac下面几个查询都会用到哪些索引?

• select * from t where a=xx and b=xx;
• select * from t where b=xx and c=xx;
• select * from t where a=xx and b=xx and c==xx;
• select * from t where a=xx and c=xx and b==xx;
• select * from t where a=xx and (b=xx or b==xx);

SQL脚本

CREATE TABLE test2 (
  id INT PRIMARY KEY,
  a VARCHAR(255),
  b VARCHAR(255),
  c VARCHAR(255),
  INDEX idx_ab (a, b),
  INDEX idx_ac (a, c)
);

INSERT INTO test2 (id, a, b, c) VALUES
(1, 'Alice', 'Bob', 'Hello, Bob!'),
(2, 'Alice', 'Charlie', 'Hi, Charlie!'),
(3, 'Bob', 'Alice', 'Hey, Alice!'),
(4, 'Bob', 'Charlie', 'Hello, Charlie!'),
(5, 'Charlie', 'Alice', 'Hey, Alice!'),
(6, 'Charlie', 'Bob', 'Hi, Bob!'),
(7, 'Alice', 'David', 'Hello, David!'),
(8, 'Bob', 'Eve', 'Hi, Eve!'),
(9, 'Charlie', 'Frank', 'Hey, Frank!'),
(10, 'David', 'Alice', 'Hi, Alice!'),
(11, 'Eve', 'Bob', 'Hello, Bob!'),
(12, 'Frank', 'Charlie', 'Hi, Charlie!'),
(13, 'Alice', 'George', 'Hey, George!'),
(14, 'Bob', 'Helen', 'Hello, Helen!'),
(15, 'Charlie', 'Ivy', 'Hi, Ivy!'),
(16, 'David', 'Jack', 'Hey, Jack!'),
(17, 'Eve', 'Kate', 'Hi, Kate!'),
(18, 'Frank', 'Lucy', 'Hello, Lucy!'),
(19, 'George', 'Mary', 'Hi, Mary!'),
(20, 'Helen', 'Nancy', 'Hey, Nancy!');

SELECT * FROM test2;

使用explain分析SQL计划

情况1：select * from t where a=xx and b=xx;

EXPLAIN SELECT a FROM test2 WHERE a='Alice' AND b='David';

 符合最左匹配原则，走联合索引idx_ab。

情况2：select * from t where b=xx and c=xx;

EXPLAIN SELECT * FROM test2 WHERE b='George' AND c='Hey, George!'

key为NULL，不符合最左匹配原则，不走索引。

情况3：select * from t where a=xx and b=xx and c==xx;

EXPLAIN SELECT * FROM test2 WHERE a='Alice' AND b='George' AND c='Hey, George!';

同时满足2个索引的最左匹配原则，走了两个索引。type中标识了这是一次index_merge（索引合并），using intersect代表对两边数据求交集。

情况4：select * from t where a=xx and c=xx and b==xx;

EXPLAIN SELECT a,b FROM test2 WHERE a='Alice' AND c='Hey, George!' AND b='George';

与abc在sql中的顺序无关，与情况3一样。  

情况5：select * from t where a=xx and (b=xx or b==xx);

EXPLAIN SELECT a FROM test2 WHERE a='Alice' AND (b='David' OR b='George');

 走索引idx_ab