索引的最左匹配原则

案例

CREATE TABLE test(
	id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  col1 INT,
  col2 INT,
  col3 INT);

CREATE INDEX inx_c1c2 ON test(col1, col2);

# 递归生成测试数据
SET SESSION cte_max_recursion_dept = 999999;

INSERT INTO test(col1, col2, col3)
WItH RECURSIVE d AS (
  SELECT 1 n, 1000*rand() c2, 1000*rand() c3
  UNION ALL
  SELECt n+1, 1000*rand(), 1000*rand()
  FROM d
  WHERE n<100000
)
SELECT c1, c2, c3 FROM d;

# 查询数据
explain
SELECT *
FROM test t
WHERE col1 = 100 AND col2 = 100;

# 没有col2
explain
SELECT *
FROM test t
WHERE col2 = 100;

# 没有col2
explain
SELECT *
FROM test t
WHERE col2 = 100 AND col2 < 100;

# 无法走索引
explain
SELECT *
FROM test t
WHERE col1 > 100;

# 可以走索引
explain
SELECT *
FROM test t
WHERE col1 > 10000;

# 无法走索引
explain
SELECT *
FROM employee e
WHERE email like '%bc'; -- abc, abd, abe, bbc, bbd

总结

复合索引按第一个字段，第二个字段。。。排序

字符串按第一个字母，第二个字母。。。排序

因为索引结构是一个从左到右建立顺序的过程，构造索引的时候要按照查询条件的顺序构造索引