索引失效的情况

数据准备

CREATE TABLE `tb_seller` (

`sellerid` VARCHAR (100),

`name` VARCHAR (100),

`nickname` VARCHAR (50),

`password` VARCHAR (60),

`status` VARCHAR (1),

`address` VARCHAR (100),

`createtime` DATETIME,

    PRIMARY KEY(`sellerid`)

)ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

 

INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('alibaba','阿里巴巴','阿里小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');

INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('baidu','百度科技有限公司','百度小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');

INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('huawei','华为科技有限公司','华为小店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');

INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('qf','微软中国','微软中国','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');

INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('qf1','千锋教育','千锋教育上海校区','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');

INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('luoji','罗技科技有限公司','罗技小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');

INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('oppo','OPPO科技有限公司','OPPO官方旗舰店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');

INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('ourpalm','掌趣科技股份有限公司','掌趣小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');

INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('qiandu','千度科技','千度小店','e10adc3949ba59abbe56e057f20f883e','2','北京市','2088-01-01 12:00:00');

INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('sina','新浪科技有限公司','新浪官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');

INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('xiaomi','小米科技','小米官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','西安市','2088-01-01 12:00:00');

INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('yijia','宜家家居','宜家家居旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');

创建复合索引 CREATE INDEX index_name_status_address ON tb_seller(name,status,address)

前置知识

最左匹配原则

在 MySQL 中最左前缀法则的应用主要涉及到复合索引。当表定义了一个由多个列组成的复合索引时，最左前缀法则规定查询时必须使用索引的最左边的列开始，并且不能跳过索引中的列。如果查询中的条件不符合最左前缀法则，那么该索引将不会被使用。

后面的分析我们通过EXPLAIN执行计划来具体分析

索引失效的情况

不满足最左匹配原则

如果索引了多列（也就是复合索引），要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。匹配最左前缀法则，走索引；例如开始创建的复合索引的顺序是 从左到右 name、status、address

下面来测试：

满足最左匹配原则

先单独根据name来查 EXPLAIN SELECT * FROM tb_seller WHERE name ='小米科技';

可以看到key确实是走了创建的复合索引，大小（key_len）是403

根据 name和status来查询EXPLAIN SELECT * FROM tb_seller WHERE name ='小米科技' AND status ='1'; 此时也是满足的，此时key_len的大小是410
根据 name和status和address来查询 EXPLAIN SELECT * FROM tb_seller WHERE NAME='小米科技' AND status ='1' AND address ='北京市';