mysql索引失效的N种情况

一、环境准备

　　sql脚本

CREATE TABLE `user` (
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`),
  KEY `index_age` (`age`),
  KEY `index_address` (`address`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `job` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) DEFAULT NULL,
  `job` varchar(255) DEFAULT NULL,
  `name` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_index` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

　　脚本说明：

　　主要是创建了两张表user和job

二、失效场景分类

　　1、查询条件中有or

SQL:

explain SELECT name,age,address FROM user where name = '光头强' or age=9

结果为：

简单说明：

　　select_type:值为SIMPLE,说明是没有子查询或union查询

　　type：值为ALL，说明走的是全表扫描

　　key：NULL，表示实际使用的索引，这里的NULL表示没有走索引

小结：

　　　虽然user表建了name，age和adress三个普通索引，但是where后面的查询条件会是索引失效

　　2、like查询是以'%'开头

SQL:

explain SELECT name,age,address FROM user where name like '%头强'

结果：

简单说明：

type为All，仍然是走的全表扫描。但是如果想让以‘%’开头仍然使用索引，则需要使用覆盖索引，即只查询带索引字段的列

　3、对查询的列上有运算或者函数的

SQL：

explain SELECT name,age,address FROM user where substr(name,-2)='头强';

结果：

再比如:

备注:

如果想让查询的列使用函数或计算仍然使用索引，则需要使用覆盖索引

　　4、如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

SQL:

explain SELECT name,age,address FROM user where name = 10

结果：

简单说明：

type：值为ALL，仍然走的是全表扫描

key：值为NULL,没有走索引

name字段定义的数据类型是varchar类型说明的是字符串，但是传值的类型是数值型

修改为1：

此时的type为ref，说明最左缀匹配，非主键和唯一索引查询，返回多条数据，key有值，说明走的是普通索引

备注：

这里和示例3一样，因为mysql有个类型转换规则就是将“字符转成数字”，等价于下面的sql

explain SELECT name,age,address FROM user where cast(name as signed)= 10

　　5、左连接查询或者右连接查询查询关联的字段编码格式不一样

环境准备：

CREATE TABLE `user` (
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`),
  KEY `index_age` (`age`),
  KEY `index_address` (`address`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `job` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) DEFAULT NULL,
  `job` varchar(255) DEFAULT NULL,
  `name` varchar(255) CHARACTER SET gbk COLLATE gbk_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

注意user 表的name字段编码是utf8mb4，而job表的name字段编码为gbk。执行左外连接查询

EXPLAIN select a.name,b.name,b.job
from
user a
left JOIN job b
ON a.name =b.name;

执行结果：

简单说明：

b表的type的值仍为ALL，说明走的还是全变扫描，但是假如使用的是覆盖索引的话，则走的是索引,如下：

　　6、如果mysql估计使用全表扫描要比使用索引快,则不使用索引

举例还是使用最开始的建表语句，只不过是分别往里面插入了两条数据，前后执行的语句为：

EXPLAIN select a.name,b.name,b.job
from
user a
left JOIN job b
ON a.name =b.name

执行计划如下：

简单说明：

由于要查询b.name，mysql需要回表，mysql认为走全表扫描会快一些，所以即使b表的name有索引，也不会走

　　7、连接查询中，按照优化器顺序的第一张表不会走索引

在最开始的建表语句中，对两张表批量插入一定量的数据，这里选择使用存储过程，如下：

BEGIN
    SET @i=20001;

    WHILE @i<=21000 DO
    
        
        INSERT INTO `USER`(name,age,address,id) VALUES(CONCAT("test",@i),FLOOR(RAND()*30),CONCAT("狗熊岭",@i),@i);

     if @i%2 = 0 then
            INSERT INTO `job`(id,userId,job,name) VALUES(@i,@i, "c",CONCAT("test",@i));
     end if;      

     if @i%2 != 0 then
            INSERT INTO `job`(id,userId,job,name) VALUES(@i,@i, "c+",CONCAT("test",@i));
     end if;  

        SET @i=@i+1; 

        END WHILE; 

END $$
DELIMITER;

CALL batch_insert_user_job;

备注：

set @i = ${value}------表示，从哪里开始

while循环里面------表示要写入到哪个值

调用存储过程

CALL batch_insert_user_job;

结果：

user表：

job表：

左连接执行计划如下：

右连接执行计划如下：

内连接执行计划如下:

　　8、如果查询中没有用到联合索引的第一个字段，则不会走索引

建表语句如下：

CREATE TABLE `job` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) DEFAULT NULL,
  `job` varchar(255) DEFAULT NULL,
  `name` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_job_index` (`name`,`job`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

查询语句：

explain SELECT * from job where job='java'

执行计划：

type的值为ALL，走的还是全表扫描

　　9、违反最左前缀法则

数据准备：

Create Table: CREATE TABLE `user` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int(10) DEFAULT '0',
  `pos` varchar(30) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL,
  `created_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name_age_pos_phone` (`name`,`age`,`pos`,`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

备注:

这里使用了两个索引，一个是主键，为id，自增长，另外一个是联合索引

查询语句

如果索引有多列，要遵守最左前缀法则
即查询从索引的最左前列开始并且不跳过索引中的列
explain select * from user where age = 20 and phone = '18730658760' and pos = 'cxy';

执行计划：

　　10、order by

数据准备：

还是使用示例9

查询语句：

正常（索引参与了排序）
explain select * from user where name = 'zhangsan' and age = 20 order by age,pos;
备注：索引有两个作用：排序和查找

执行计划：

导致额外的文件排序（会降低性能）
explain select name,age from user where name = 'zhangsan' order by pos;//违反最左前缀法则
explain select name,age from user where name = 'zhangsan' order by pos,age;//违反最左前缀法则
explain select * from user where name = 'zhangsan' and age = 20 order by created_time,age;//含非索引字段

　　11、group by

数据准备：

还是和示例9一样

正常（索引参与了排序）
explain select name,age from user where name = 'zhangsan' group by age;
备注：分组之前必排序（排序同order by）

导致产生临时表（会降低性能）
explain select name,pos from user where name = 'zhangsan' group by pos;//违反最左前缀法则
explain select name,age from user where name = 'zhangsan' group by pos,age;//违反最左前缀法则
explain select name,age from user where name = 'zhangsan' group by age,created_time;//含非索引字段