SQL调优之字符集-Range checked for each record (index map: 0x2)

目录

一、背景

二、复盘

三、参考文献 

---

一、背景

项目中有些业务涉及到多个表的关联查询，每个表都有大量数据，因此查询起来很慢，需要对其进行优化。通过explain进行分析过程中发现，有个关联查询增加索引的字段居然没有起作用，并且有提示信息：Range checked for each record (index map: 0x2)。通过查询相关资料，发现是两个数据表字符集不匹配的问题。复盘如下:

二、复盘

测试环境准备：

-- ----------------------------
-- Table structure for t_t_user
-- ----------------------------
DROP TABLE IF EXISTS `t_t_user`;
CREATE TABLE `t_t_user`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `code` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '编码',
  `user_id` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '用户编码',
  `username` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '用户名',
  `password` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '密码',
  `status` tinyint(2) NOT NULL DEFAULT 0 COMMENT '状态',
  `create_time` datetime(0) NULL DEFAULT NULL,
  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `code`(`code`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 35 CHARACTER SET = utf8 COLLATE = utf8_bin COMMENT = '测试用户信息表' ROW_FORMAT = COMPACT;

-- ----------------------------
-- Records of t_t_user
-- ----------------------------
INSERT INTO `t_t_user` VALUES (1, 'C_1001', '001', 'll', 'll', 1, '2021-12-16 11:50:56', '2021-12-15 11:51:03');
INSERT INTO `t_t_user` VALUES (2, 'C_1002', '002', 'SS', 'SS', 1, '2021-12-16 11:51:26', '2021-12-16 11:51:28');
INSERT INTO `t_t_user` VALUES (3, 'C_1003', '003', 'DD', 'DD', 1, '2021-12-16 11:51:47', '2021-12-16 11:51:50');

-- ----------------------------
-- Table structure for t_t_user_info
-- ----------------------------
DROP TABLE IF EXISTS `t_t_user_info`;
CREATE TABLE `t_t_user_info`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `code` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '编码',
  `user_id` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '用户编码',
  `username` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '用户名',
  `password` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL COMMENT '密码',
  `status` tinyint(2) NOT NULL DEFAULT 0 COMMENT '状态',
  `create_time` datetime(0) NULL DEFAULT NULL,
  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `code`(`code`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 35 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '测试用户信息表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t_t_user_info
-- ----------------------------
INSERT INTO `t_t_user_info` VALUES (1, 'C_1001', 'QQ', 'QQ', 'QQ', 1, '2021-12-16 11:52:20', '2021-12-16 11:52:23');
INSERT INTO `t_t_user_info` VALUES (2, 'C_1002', 'AA', 'AA', 'AA', 1, '2021-12-16 11:52:39', '2021-12-16 11:52:42');
INSERT INTO `t_t_user_info` VALUES (3, 'C_1003', 'ZZ', 'ZZ', 'ZZ', 1, '2021-12-16 11:53:01', '2021-12-16 11:53:04');

对联合查询语句进行分析：

explain
select * from 
t_t_user tt
LEFT JOIN t_t_user_info tti ON tt.`code` = tti.`code`
order by tt.id asc limit 100

分析结果：

 关键语句：Range checked for each record (index map: 0x2)

解决方案：

在navicat里面选择数据表，然后点击“设计表”，修改表和字段的字符集

 也可以使用语句（不过这种方式，如果数据量很大的话，需要执行比较久，建议使用工具）：

ALTER TABLE `t_t_user` 
MODIFY COLUMN `code` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '编码';

 修改后：

 但是，测试结果如图：

Using temporary与Using filesort 这个什么意思？
Using temporary表示排序没有走索引。
Using filesort表示没有使用索引的排序。
Why?原因很简单嘛，数据量不够。所以我们需要使用循环语句进行大量造数据。

-- t_t_user_info新增1000条数据
DELIMITER //
DROP PROCEDURE if EXISTS ‘test1’;    
CREATE procedure test1() 
BEGIN
DECLARE i INT;  
SET i = 5;  
WHILE i<1000 DO 
INSERT INTO `t_t_user_info` VALUES (i, i, '001', 'll', 'll', 1, '2021-12-16 11:50:56', '2021-12-15 11:51:03');
SET i = i+1;   
END WHILE;  
SELECT * FROM t_t_user_info; 
END
//
CALL test1();    
DELIMITER ;
-- t_t_user新增1000条数据
DELIMITER //
DROP PROCEDURE if EXISTS ‘test’;    
CREATE procedure test() 
BEGIN
DECLARE i INT;  
SET i = 5;  
WHILE i<1000 DO 
INSERT INTO `t_t_user` VALUES (i, i, '001', 'll', 'll', 1, '2021-12-16 11:50:56', '2021-12-15 11:51:03');
SET i = i+1;   
END WHILE;  
SELECT * FROM t_t_user; 
END
//
CALL test();    
DELIMITER ;

再次执行explain语句，效果出现了：

我们反向重新将t_t_user表修改为utf8_bin，同样的问题再次出现，复盘完毕。 

 三、参考文献 

1、SQL调优—Range checked for each record (index map: 0x1)
https://blog.csdn.net/hzr0523/article/details/118928185
2、Using temporary与Using filesort
https://blog.csdn.net/sz85850597/article/details/91907988
3、MySQL循环语句
http://blog.itpub.net/69955379/viewspace-2753943/