索引失效的场景

MySQL版本:8.0.32

DROP TABLE IF EXISTS `employee`;

CREATE TABLE `employee` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(512) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `address` varchar(512) DEFAULT NULL,
  `salary` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
--除了主键索引 无其他索引

-- 删除历史的存储过程	
DROP PROCEDURE IF EXISTS `insert_into_employee`;
-- 创建存储过程
DELIMITER $
	CREATE PROCEDURE insert_into_employee(IN n int)
 	BEGIN
    DECLARE i INT DEFAULT 1;
		DECLARE name VARCHAR(18);
		DECLARE age INT DEFAULT 1;
		DECLARE address VARCHAR(20);
		DECLARE salary INT DEFAULT 1000;
	WHILE i <= n DO
  	SET name = CONCAT('张三',i);
		SET age = FLOOR(10 + RAND()*2);
		SET address = CONCAT('重庆',i);
		SET salary = FLOOR(1000 + RAND()*2);
     INSERT into employee  (name,age,address,salary) VALUES (name,age,address,salary);
	SET i = i+1;
    END WHILE;
END $
-- 执行存储过程
 CALL insert_into_employee(100000);

1、联合索引不满足最左匹配原则

--添加 name  age的联合索引
alter table employee add index union_name(name,age);

explain select * from employee where name = '张三1'

由上图可以看到走了联合索引

name的字符集是utf8mb4_bin 4个字节表示一个完整的UTF-8

key_len: 512 * 4 = 2048

由于该字段类型是VARCHAR为变长数据类型，需再额外添加2个字节，此时 key_len：2048 + 2 = 2050

由于该字段默认NULL（DEFAULT NULL） 需再添加1个字节 此时 key_len：2050 + 1 = 2051

explain select * from employee where name = '张三1' and age = 10

我们发现也走了索引且ref有两个const

explain select * from employee where  age =