Example demonstration of MySQL index failure scenario

Article Directory


In this article, the various effects of MySQL index failure, write sample SQL for each situation and view the execution plan in the database.

1. Environmental Information

CentOS 7.4
Mysql 5.7.32

Two, table, data preparation

Create a user table

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 = '用户信息表';

Create a stored procedure and insert 100,000 pieces of test data

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', '[email protected]', '15251831704', 'avatar.jpg', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 0);
    END WHILE;
		COMMIT;
END $$


CALL insert_t_user_test(100000);

Three, the failure of the composite index

Create a composite index

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

Failure to meet the leftmost prefix rule will invalidate the index. You must start the query from the leftmost column of the index, and do not skip the middle column, regardless of the order of conditions specified by where.

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

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

-- 走索引 命中两个字段 user_name, email
explain select * from t_user where user_name = 'rkyao-1' and email = '[email protected]';

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

-- where查询字段不包含索引最左边的列
-- 不走索引
explain select * from t_user where email = '[email protected]' and phone_number = '15251831704';
Insert picture description here
or query

The or query will invalidate the index.

-- 不走索引
explain select * from t_user where user_name = 'rkyao-1' or email = '[email protected]';
Insert picture description here

Fourth, the failure of a single-column index

Create a single-column index on the user_nameand emailfield separately

-- 删除联合索引
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`);
Wildcard before using like fuzzy query

The use of pre-wildcard matching in fuzzy query will invalidate the index.

-- 不走索引
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';
Insert picture description here
Use functions on index columns

Use functions on the index column, or perform operations, do not take the index

-- 不走索引
explain select * from t_user where concat(user_name, '123') = '555';
Insert picture description here
String index is not quoted

When querying a string type field, the condition value is not quoted, and the index is not taken

-- 不走索引
explain select * from t_user where user_name = 555;
Insert picture description here
Use !=, <>, >, <

Use unequal, do not take the index.

-- 不走索引
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';
Insert picture description here
is null or is not null

is null or is not null, no index is taken.

-- 不走索引
explain select * from t_user where user_name is null;
-- 不走索引
explain select * from t_user where user_name is not null;
Insert picture description here
in or not in

In takes the index, using not in does not take the index.

-- 走索引
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');
Insert picture description here
Two single-column indexes and queries

When querying two index columns in the where condition, only one index will take effect, that is, the index with the earlier creation time will take effect.

-- 走索引 命中user_name字段
explain select * from t_user where email = '[email protected]' and user_name = '555';
Insert picture description here
Two single-column index or query

When querying two index columns in the or in the where condition, both indexes take effect.

-- 走索引 命中user_name字段
explain select * from t_user where email = '[email protected]' or user_name = '555';
Insert picture description here