MySQL索引系列-最左前缀法则

MySQL索引系列-最左前缀法则

1. 概述

• 索引的最左前缀原则，也称为最左匹配原则，是数据库查询优化中的一个重要概念，这个原则的核心在于，当查询数据库时，系统会从联合索引的最左边开始匹配查询条件
• 索引使用了复合索引，也就是联合索引。是查询从索引的最左列开始（必须包含最左列）

2. 准备

• 创建用户user表

create table user(
		id int auto_increment primary key comment '主键ID',
		name varchar(10) comment '姓名',
		age int comment '年龄',
		sex tinyint(1) comment '1:男，2:女',
		phone varchar(11) comment '手机号',
		email varchar(20) comment '邮件',
		work varchar(20) comment '工作'
)comment '用户信息表';

• 生成测试数据，可以使用chatgpt或其他工具生成

• 创建符合索引idx_name_age_phone

create index idx_name_age_phone on user(name,age,phone);

• 查看索引

show index from user

3. 执行计划分析

• 包含最左列name字段，计算name、age、phone三个索引字段的长度

explain select * from user where name ='张三' and age = 20 and  phone ='18200389045';

可以看到type=ref说明没有进行全表查询，走了索引，key_len=95

explain select * from user where name ='张三' and age = 20;

可以看到type=ref说明没有进行全表查询，走了索引。key_len=48，说明phone的长度为47

explain select * from user where name ='张三';

可以看到type=ref说明没有进行全表查询，走了索引，key_len=43，说明name的长度为43，age的长度为5，phone的长度是47。

• 几种没有使用索引的情况

explain select * from user where phone ='18200389045';
explain select * from user where age = 20;
explain select * from user where age = 20 and  phone ='18200389045'

• 包含最左列name字段，不包含age，包含phone

explain select * from user where name ='张三' and phone ='18200389045';

可以看到type=ref说明没有进行全表查询，走了索引。key_len=43，说明只有name走了索引。

• 三个字段同时存在，顺序不一致

explain select * from user where name ='张三' and age = 20 and  phone ='18200389045';
explain select * from user where name ='张三' and phone ='18200389045' and age = 20 ;
explain select * from user where age = 20  and phone ='18200389045' and name ='张三';
explain select * from user where age = 20  and name ='张三' and phone ='18200389045';
explain select * from user where phone ='18200389045' and age = 20  and name ='张三';
explain select * from user where phone ='18200389045' and name ='张三'  and age = 20;

可以看到type=ref说明没有进行全表查询，走了索引。key_len=95，说明三个字段同时存在，和顺序没关系

4. 总结

上述name、age、phone三个字段创建联合索引，相当于创建了name、(name、age)、(name、age、phone)三个索引，始终要包含最左列name字段，才能走索引