MySQL 中的最左前缀法则

“最左前缀法则” 是什么？

在 MySQL 中，最左前缀法则是针对复合索引（也称为联合索引）的一个重要规则。复合索引是指在表的多个列上创建的索引。最左前缀法则规定，在使用复合索引进行查询时，MySQL 会从索引的最左边的列开始，依次向右匹配，直到遇到范围查询（如 >、<、BETWEEN、LIKE 以通配符开头）等不能使用索引的条件为止。只有符合最左前缀的查询条件才能有效利用复合索引来提高查询效率。

注意：

• 最左前缀法则规定使用联合索引不能跳过其中的某一列，如果跳过其中某一列，索引将部分失效（该列后面的字段索引失效）；
• 最左前缀法则是要求联合索引最左边的列必须被使用，而不是要求要把查询条件放在 where 语句的最左边，实际上最左前缀法则与查询条件放置的位置没有关系。

MySQL 中的最左前缀法则的原理在于索引是按照索引列的顺序进行排序的，查询时会根据索引的顺序进行搜索。如果查询条件不满足最左前缀法则，那么数据库无法利用索引的有序性，可能需要进行全表扫描，导致查询性能下降。

最左前缀法则是 MySQL 中复合索引使用的重要规则。在设计复合索引时，需要根据实际的查询需求，将经常一起使用的列按照查询条件的顺序从左到右创建索引，以确保查询能够有效利用复合索引，提高查询性能。同时，在编写查询语句时，也需要注意遵循最左前缀法则，避免跳过最左列或在范围查询后使用后续索引列。

以下是最左前缀法则的案例说明。

环境准备

创建示例表：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    last_name VARCHAR(50),
    first_name VARCHAR(50),
    age INT
);

创建复合索引：

CREATE INDEX idx_last_first_age ON users (last_name, first_name, age);

创建并执行存储过程：

-- 首先设置 delimiter 为 //，这样可以在存储过程中使用分号
DELIMITER //

-- 创建存储过程 insert_users_data
CREATE PROCEDURE insert_users_data()
BEGIN
    -- 声明循环计数器变量 i，初始值为 1
    DECLARE i INT DEFAULT 1;
    -- 定义可能的姓氏数组
    DECLARE last_names TEXT DEFAULT 'Smith,Jones,Johnson,Brown,Wilson,Taylor,Davis,Miller,White,Clark';
    -- 定义可能的名字数组
    DECLARE first_names TEXT DEFAULT 'John,Michael,David,William,Richard,Joseph,Thomas,Charles,Christopher,Daniel';
    
    -- 开始循环，当 i 小于等于 100000 时执行循环体
    WHILE i <= 100000 DO
        -- 随机选择一个姓氏
        SET @last_name = SUBSTRING_INDEX(SUBSTRING_INDEX(last_names, ',', FLOOR(RAND() * 10) + 1), ',', -1);
        -- 随机选择一个名字
        SET @first_name = SUBSTRING_INDEX(SUBSTRING_INDEX(first_names, ',', FLOOR(RAND() * 10) + 1), ',', -1);
        -- 随机生成一个 18 到 60 岁之间的年龄
        SET @age = FLOOR(RAND() * 43) + 18;
        
        -- 向 users 表中插入一条记录
        INSERT INTO users (last_name, first_name, age) VALUES (@last_name, @first_name, @age);
        
        -- 计数器 i 加 1
        SET i = i + 1;
    END WHILE;
END //

-- 将 delimiter 恢复为默认的分号
DELIMITER ;

-- 调用存储过程
CALL insert_users_data();

符合最左前缀法则的查询

以下是符合最左前缀法则的查询示例。

示例 1：使用最左列查询

SELECT * FROM users WHERE last_name = 'Smith';

在这个查询中，只使用了复合索引的最左列 last_name。根据最左前缀法则，MySQL 可以直接使用 idx_last_first_age 索引来快速定位 last_name 为 ‘Smith’ 的记录，从而提高查询效率。

执行计划分析：

示例 2：使用最左两列查询

SELECT * FROM users WHERE last_name = 'Smith' AND first_name = 'John';

这里使用了复合索引的前两列 last_name 和 first_name。MySQL 会先根据 last_name 进行筛选，然后在筛选结果中再根据 first_name 进行进一步筛选。由于符合最左前缀法则，这个查询也可以有效地利用复合索引。

执行计划分析：

不符合最左前缀法则的查询

以下是不符合最左前缀法则的查询示例。

示例 1：跳过最左列

SELECT * FROM users WHERE first_name = 'John';

在这个查询中，跳过了复合索引的最左列 last_name，直接使用了 first_name。根据最左前缀法则，MySQL 无法使用 idx_last_first_age 索引来优化这个查询，只能进行全表扫描，查询效率会比较低。

执行计划分析：

示例 2：范围查询后使用索引列

SELECT * FROM users WHERE last_name = 'Smith' AND first_name > 'Charles' AND age = 18;

在这个查询中，first_name > 'Charles' 是一个范围查询。根据最左前缀法则，MySQL 在遇到范围查询后，不会再使用后续的索引列（即 age）。因此，虽然查询中使用了 age 列，但它无法利用复合索引的这一部分，只能对 last_name 和 first_name 进行索引查找，然后对剩余的记录进行全表扫描来筛选 age = 18 的记录。

执行计划分析：

实际上上述 SQL 的执行计划等同于：

SELECT * FROM users WHERE last_name = 'Smith' AND first_name > 'Charles'

这两个 SQL 的执行计划是完全相同的。即：