四时宝库

程序员的知识宝库

mysql索引使用规则:最左前缀法则

如果索引引用了多列(联合索引),要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始, 并且不跳过索引中的列。如果跳跃某一列,索引将会部分失效(后面的字段索引失效)。

现有一张用户表tb_user,具体结构如下:

Bash
CREATE TABLE `tb_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(50) NOT NULL COMMENT '用户名',
  `phone` varchar(11) NOT NULL COMMENT '手机号',
  `email` varchar(100) DEFAULT NULL COMMENT '邮箱',
  `profession` varchar(11) DEFAULT NULL COMMENT '专业',
  `age` tinyint(3) unsigned DEFAULT NULL COMMENT '年龄',
  `gender` char(1) DEFAULT NULL COMMENT '性别 , 1: 男, 2: 女',
  `status` char(1) DEFAULT NULL COMMENT '状态',
  `createtime` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

1、在 tb_user 表中,我们创建一个联合索引,这个联合索引涉及到三个字段,顺序分别为:profession(专业), age(年龄),status(状态)。

Bash
create? ?index? ?index_user_pro_age_sta? on? tb_user(profession,age,status)?;

2、查看tb_user表的全部索引

Bash
show? index? from? tb_user;

3、对于最左前缀法则指的是,查询时,最左变的列,也就是profession必须存在,否则索引全部失效。 而且中间不能跳过某一列,否则该列后面的字段索引将失效。 接下来,我们来演示几组案例,看一下 具体的执行计划:

Bash
explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';
Bash
explain select * from tb_user where profession = '软件工程' and age = 31
Bash
explain select * from tb_user where profession = '软件工程';

以上的这三组测试中,我们发现只要联合索引最左边的字段 profession存在,索引就会生效,只不 过索引的长度不同。 而且由以上三组测试,我们也可以推测出profession字段索引长度为36、age 字段索引长度为2、status字段索引长度为4。


以下两条sql索引会生效吗?

Bash
explain select * from tb_user where age = 31 and status = '0';
Bash
explain select * from tb_user where status = '0';

而通过上面的这两组测试,我们也可以看到索引并未生效,原因是因为不满足最左前缀法则,联合索引 最左边的列profession不存在。


Bash
explain select * from tb_user where profession = '软件工程' and status = '0';

上述的SQL查询时,存在profession字段,最左边的列是存在的,索引满足最左前缀法则的基本条 件。但是查询时,跳过了age这个列,所以后面的列索引是不会使用的,也就是索引部分生效,所以索 引的长度就是36。


当执行SQL语句: explain select * from tb_user where age = 31 and status = '0' and profession = '软件工程'; 时,是否满足最左前缀法则,走不走 上述的联合索引,索引长度?

可以看到,是完全满足最左前缀法则的,索引长度42,联合索引是生效的。 注意 : 最左前缀法则中指的最左边的列,是指在查询时,联合索引的最左边的字段(即是 第一个字段)必须存在,与我们编写SQL时,条件编写的先后顺序无关。

发表评论:

控制面板
您好,欢迎到访网站!
  查看权限
网站分类
最新留言
    友情链接