范围查询对联合索引和最左匹配的影响

参考资料地址1: 联合索引遇到范围查询(＞、＜、between、like)的情况

错误：联合索引的最左匹配原则会一直向右匹配直到遇到范围查询(>、<、between、like) 就会停止匹配。

目录

• 准备
  • 创建表及联合索引
  • 插入20万条数据
  • ps:
• 测试
  • like对联合索引的影响
  • where 后的条件字段顺序多联合索引的影响
  • 大于号对联合索引的影响(<,<=同)
  • 大于等于号对联合索引的影响
  • 测试并不充分,仅证明联合索引遇到范围查询就会停止匹配的说法是错误的
• 分析
  • 为什么大于号和大于等于号对联合索引的影响不同呢?
• 总结

准备

创建表及联合索引

CREATE TABLE `t_user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8mb4 NOT NULL,
  `age` int(4) NOT NULL,
  `weight` double(4,0) NOT NULL,
  `hobby` varchar(20) NOT NULL,
  `remark` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name_age_weight` (`age`,`name`,`weight`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=200002 DEFAULT CHARSET=utf8;

插入20万条数据

DELIMITER //
CREATE PROCEDURE InsertUsers () BEGIN
	DECLARE
		i INT DEFAULT 1;
	DECLARE
		random_age INT;
	DECLARE
		random_weight INT;
	DECLARE
		random_hobby VARCHAR ( 10 );-- 假设hobby是一个可以容纳'1'到'100'的字符串字段
	WHILE
			i <= 200000 DO-- 生成20到100之间的随机数
			
			SET random_age = FLOOR(
				20 + (
				RAND() * ( 100 - 20 + 1 )));-- 生成1到100之间的随机数
		
		SET random_weight = FLOOR(
		1 + ( RAND() * 100 ));-- 生成1到100的随机数字字符串作为爱好（这里简化处理）
		
		SET random_hobby = CAST( FLOOR( 1 + ( RAND() * 100 )) AS CHAR );-- 插入数据
		INSERT INTO `test`.`t_user` ( `name`, `age`, `weight`, `hobby` )
		VALUES
			( CONCAT( 'user', i ), random_age, random_weight, random_hobby );
		
		SET i = i + 1;
		
	END WHILE;
	
END // 
DELIMITER;-- 调用存储过程
CALL InsertUsers ();

ps:

DELIMITER 命令来临时更改语句的结束符。例如，你可以将结束符更改为 // 或其他任何不常用的字符序列，然后编写你的存储过程或函数，使用新的结束符来分隔语句。完成定义后，你可以将结束符重新设置为分号。

DELIMITER //  
  
CREATE PROCEDURE MyProcedure()  
BEGIN  
    SELECT 'Hello, World!';  
    -- 注意这里有一个分号，但由于我们更改了结束符，所以它不会结束存储过程的定义  
END //  
  
DELIMITER ;

测试

like对联合索引的影响

只指%不在开头的

EXPLAIN SELECT
	* 
FROM
	`t_user` 
WHERE
	NAME LIKE 'user6666%' 
	AND age = 10 
	AND weight > 20;

ps:

• key_len 为 94 字节（如果字段允许为 NULL，就在字段类型占用的字节数上加 1，也就是 5 字节），说明 name，age，weight字段都用到了联合索引进行索引查询
• 对于VARCHAR字段，索引条目通常包含字段值的实际内容（前缀或完整值，取决于索引是否是前缀索引）以及指向数据行的指针。
• 如果使用了字符集如utf8mb4（每个字符最多4个字节），那么VARCHAR(20)字段最多可以占用20 * 4 = 80字节（但实际值可能少于这个，如果字符串中的字符不需要完整的4个字节）。

where 后的条件字段顺序多联合索引的影响

EXPLAIN SELECT
	* 
FROM
	`t_user` 
WHERE
	age = 10 
	AND NAME LIKE 'user6666%' 
	AND weight > 20;

说明 name，age，weight字段都用到了联合索引进行索引查询;与字段的顺序无关;mysql优化器会根据索引对sql优化,

大于号对联合索引的影响(<,<=同)

EXPLAIN SELECT
	* 
FROM
	`t_user` 
WHERE
	NAME like 'user6666%' 
	AND age > 10 
	AND weight > 20;

说明说明 name，age，用到了联合索引进行索引查询,而weight字段没有

大于等于号对联合索引的影响

EXPLAIN SELECT
	* 
FROM
	`t_user` 
WHERE
	NAME like 'user6666%' 
	AND age >= 10 
	AND weight > 20;

说明 name，age，weight字段都用到了联合索引进行索引查询

测试并不充分,仅证明联合索引遇到范围查询就会停止匹配的说法是错误的

分析

首先我们需要了解联合索引的原理,它是先排序最左面的那个字段,再依次对后面的字段排序

为什么大于号和大于等于号对联合索引的影响不同呢?

网上的说法,(提供一个思路,感觉并不正确及严谨)

详情见参考资料地址1:

根据本例,name虽然在符合 age >= 10条件的二级索引记录的范围里，weight 字段的值是「无序」的，但是对于符合 age = 10 的二级索引记录的范围里，weight 字段的值是「有序」的（因为对于联合索引，是先按照 a 字段的值排序，然后在 a 字段的值相同的情况下，再按照 b 字段的值进行排序）。

总结

联合索引的最左匹配原则会一直向右匹配直到遇到范围查询(>、<、between、like) 就会停止匹配。说法是错误的;

1. 联合索引的最左匹配原则会一直向右匹配直到遇到范围查询(>、<、between、like) 可能会停止匹配,但并不绝对;实际使用时,用explain验证
2. where 后的条件字段顺序对多联合索引的影响不大,MySQL的优化器会自动调整查询条件的顺序，以尽可能地利用联合索引
3. 了解联合索引是怎样排序的(这个是关键)