MySQL索引失效的情况

目录

• 一、环境信息
• 二、表、数据准备
• 三、复合索引的失效情况
  • • 最左前缀法则
    • or查询
• 四、单列索引的失效情况
  • • like模糊查询使用前通配符
    • 索引列上使用函数
    • 字符串索引没加引号
    • 使用 != 、 <>、>、<
    • is null 或 is not null
    • in 或 not in
    • 两个单列索引and查询
    • 两个单列索引or查询

本文MySQL索引失效的各种效情况，对每种情况写出示例SQL并在数据库中查看执行计划。

一、环境信息

CentOS 7.4

Mysql 5.7.32

二、表、数据准备

建立一张用户表

CREATE DATABASE IF NOT EXISTS `test` DEFAULT CHARACTER SET utf8;
USE `test`;

DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user`  (
  `id` bigint NOT NULL DEFAULT 0 COMMENT '主键，用户唯一id',
  `user_name` varchar(32) NOT NULL DEFAULT '' COMMENT '用户名',
  `password` varchar(64) NOT NULL DEFAULT '' COMMENT '密码',
  `email` varchar(32) NOT NULL DEFAULT '' COMMENT '邮箱',
  `phone_number` varchar(16) NOT NULL DEFAULT '' COMMENT '电话号码',
  `avatar` varchar(256) NOT NULL DEFAULT '' COMMENT '头像',
  `create_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '用户账号创建时间',
  `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '上次更新记录时间',
  `last_login_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '上次登录时间',
  `status` int(2) NOT NULL DEFAULT 0 COMMENT '用户状态 0-正常 1-封禁',
  PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_bin COMMENT = '用户信息表';

创建存储过程，插入10万条测试数据

DROP PROCEDURE if exists insert_t_user_test;

DELIMITER $$
CREATE PROCEDURE insert_t_user_test(IN loop_times INT)
BEGIN
    DECLARE var INT DEFAULT 0;
    WHILE var < loop_times DO
        SET var = var + 1;
    	  INSERT INTO `t_user` VALUES (var, CONCAT('rkyao-', var), '123456', 'rkyao@163.com', '15251831704', 'avatar.jpg', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 0);
    END WHILE;
		COMMIT;
END $$


CALL insert_t_user_test(100000);

三、复合索引的失效情况

创建复合索引

CREATE INDEX `idx_user` ON `t_user` (`user_name`, `email`, `phone_number`);

最左前缀法则

不符合最左前缀法则会使索引失效，必须从索引的最左列开始查询，且不跳过中间列，和where指定的条件顺序无关。

-- where查询字段顺序和索引定义一致
-- 走索引 命中三个字段 user_name, email, phone_number
explain select * from t_user where user_name = 'rkyao-1' and email = 'rkyao@163.com' and phone_number = '15251831704';

-- where查询字段顺序和索引定义不一致
-- 走索引 命中三个字段 user_name, email, phone_number
explain select * from t_user where phone_number = '15251831704' and email = 'rkyao@163.com' and user_name = 'rkyao-1';

-- 走索引 命中两个字段 user_name, email
explain select * from t_user where user_name = 'rkyao-1' and email = 'rkyao@163.com';

-- 走索引 命中一个字段 user_name
explain select * from t_user where user_name = 'rkyao-1' and phone_number = '15251831704';

-- where查询字段不包含索引最左边的列
-- 不走索引
explain select * from t_user where email = 'rkyao@163.com' and phone_number = '15251831704';

or查询

or查询会使索引失效。

-- 不走索引
explain select * from t_user where user_name = 'rkyao-1' or email = 'rkyao@163.com';

四、单列索引的失效情况

在user_name和email字段上分别创建单列索引

-- 删除联合索引
DROP INDEX `idx_user` ON `t_user`;
-- 创建单列索引
CREATE INDEX `idx_user_name` ON `t_user` (`user_name`);
CREATE INDEX `idx_user_email` ON `t_user` (`email`);

like模糊查询使用前通配符

模糊查询时使用前通配符匹配会使索引失效。

-- 不走索引
explain select * from t_user where user_name like '%555%';
-- 不走索引
explain select * from t_user where user_name like '%555';
-- 走索引
explain select * from t_user where user_name like '555%';
-- 走索引
explain select * from t_user where user_name like '555';

索引列上使用函数

索引列上使用函数，或者进行运算，不走索引

-- 不走索引
explain select * from t_user where concat(user_name, '123') = '555';

字符串索引没加引号

查询字符串类型字段时，条件值没加引号，不走索引

-- 不走索引
explain select * from t_user where user_name = 555;

使用 != 、 <>、>、<

使用不等于，不走索引。

-- 不走索引
explain select * from t_user where user_name != '555';
-- 不走索引
explain select * from t_user where user_name <> '555';
-- 不走索引
explain select * from t_user where user_name > '555';

is null 或 is not null

is null 或 is not null，不走索引。

-- 不走索引
explain select * from t_user where user_name is null;
-- 不走索引
explain select * from t_user where user_name is not null;

in 或 not in

in走索引，使用not in不走索引。

-- 走索引
explain select * from t_user where user_name in ('rkyao-6222', 'rkyao-5678');
-- 不走索引
explain select * from t_user where user_name not in ('rkyao-6222', 'rkyao-5678');

两个单列索引and查询

where条件里and查询两个索引列时，只会有一个索引生效，也就是创建时间早的索引生效。

-- 走索引 命中user_name字段
explain select * from t_user where email = '123@163.com' and user_name = '555';

两个单列索引or查询

where条件里or查询两个索引列时，两个索引都生效。

-- 走索引 命中user_name字段
explain select * from t_user where email = '123@163.com' or user_name = '555';