什么是最左匹配原则

最左列必须出现在查询条件中可以选择性地查询后续列，但必须按顺序

为什么前缀能利用索引，但是后缀和中缀不能使用索引？

这是因为索引的工作方式决定了它如何与不同类型的 LIKE 查询进行配合。MySQL 的 B+ 树索引（常用于字符类型的列）按照字典顺序存储数据并进行查找。让我们一步一步深入理解为什么前缀匹配能利用索引，而后缀和中缀匹配则不能。

1. 前缀匹配可以利用索引：

假设我们有如下索引：

1

CREATE INDEX idx_location ON School(location);

B+ 树索引的结构是有序的，它可以高效地根据前缀查找数据。当你使用 LIKE 'C%' 这样的查询时，MySQL 会直接利用索引扫描，以字典顺序从 C 开始查找所有以 C 开头的字符串，直到找到不以 C 开头的字符串。B+ 树非常适合这种范围查询。

具体来说：

LIKE 'C%' 会查询所有以 C 开头的字符串。
B+ 树索引能高效地查找所有符合条件的值，并快速定位到起始位置，继而返回符合条件的记录。

索引利用：

查询时，MySQL 只需要根据 location 字段的索引定位到 C，然后顺着索引查找匹配的记录。这个过程是线性的，但由于数据有序，所以查找速度非常快。

2. 后缀匹配和中缀匹配无法使用索引：

后缀匹配：

假设查询是 LIKE '%ia'，这个查询需要查找以 ia 结尾的所有字符串。由于 % 出现在字符串的开头，MySQL 无法通过索引的有序特性来帮助定位。具体原因是：

B+ 树索引是按照顺序排列的，查询条件是 LIKE '%ia'，意味着我们不知道字符串的开始部分是什么，所以 MySQL 需要从每一个字符串的末尾进行匹配。
索引不能直接“倒着”查找，它只能按照从左到右的顺序进行查找，因此必须遍历整个表来匹配 '%ia'。
由于没有固定的开始部分，MySQL 就无法通过索引来高效过滤数据，只能进行 全表扫描。

中缀匹配：

类似于后缀匹配，查询 LIKE '%li%' 查找包含 li 的所有字符串。这个查询也是无法利用索引的，因为：

%li% 的中间部分要求数据库查找所有含有 li 的字符串，但索引是按顺序排列的，而无法在字符串的任意位置进行匹配。
由于 % 出现在字符串的两端，MySQL 无法提前知道匹配的前缀或后缀是什么，因此也无法有效利用索引，必须进行 全表扫描。

验证最左匹配原则

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18


CREATE TABLE School (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    location VARCHAR(100) NOT NULL,
    established_year INT NOT NULL,
    principal_name VARCHAR(100) NOT NULL
);
INSERT INTO School (name, location, established_year, principal_name) 
VALUES
('Greenwood High', 'California', 1990, 'John Doe'),
('Riverdale Academy', 'New York', 1985, 'Jane Smith'),
('Sunnydale School', 'Texas', 2000, 'Alice Johnson');

CREATE INDEX idx_location_year ON School (location,established_year);

EXPLAIN SELECT * FROM School WHERE location = 'California' AND established_year = 1990;
EXPLAIN SELECT * FROM School WHERE established_year = 1990;
EXPLAIN SELECT * FROM School WHERE location = 'California';

在联合索引中，最左匹配原则意味着查询必须按照索引的顺序使用前缀的列。例如，如果索引是 location, established_year，那么查询必须至少使用 location 列。如果只查询 established_year，MySQL 不会使用该索引。

Mysql最左匹配原则