MySQL表字符集不同导致关联查询索引失效

3 years ago

source link: https://wakzz.cn/2020/03/18/mysql/MySQL%E8%A1%A8%E5%AD%97%E7%AC%A6%E9%9B%86%E4%B8%8D%E5%90%8C%E5%AF%BC%E8%87%B4%E5%85%B3%E8%81%94%E6%9F%A5%E8%AF%A2%E7%B4%A2%E5%BC%95%E5%A4%B1%E6%95%88/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

MySQL表字符集不同导致关联查询索引失效

祈雨的博客

2020-03-18

mysql

mysql在多表之间做关联查询时，需要注意各个表的字符集是否一致。如果在字符集不一致的场景下做关联查询，会出现关联字段即使有索引，但却索引失效的问题。

建表语句，创建两个字符集不同的表，如下：

CREATE TABLE `school` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `school_code` varchar(255) DEFAULT NULL,
  `school_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `code_index` (`school_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `school_code` varchar(255) DEFAULT NULL,
  `student_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `code_index` (`school_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

初始化数据

delimiter  //
CREATE PROCEDURE init_school() 
BEGIN 
	DECLARE schid INT;
	SET schid = 0;
	START TRANSACTION; 
	WHILE schid < 100 DO
		insert into school(school_code,school_name) values (concat('code',schid),concat('name',schid));
		SET schid = schid + 1;
	END WHILE;
	COMMIT; 
END //
delimiter ;

delimiter  //
CREATE PROCEDURE init_student() 
BEGIN 
	DECLARE schid INT;
	DECLARE stuid INT;
	SET schid = 0;
	WHILE schid < 100 DO
		SET stuid = 0;
		START TRANSACTION; 
		WHILE stuid < 50 DO
			insert into student(school_code,student_name) values (concat('code',schid),concat('stu_name',stuid));
			SET stuid = stuid + 1;
		END WHILE;
		SET schid = schid + 1;
		COMMIT; 
	END WHILE;
END //
delimiter ;

call init_school();
call init_student();

在两个表都在school_code字段上建立索引的情况下，以该字段关联查询预期应该使用索引，但实际效果如下，通过explain命令发现mysql的执行计划并没有使用索引。

explain select * from school s1,student s2 where s1.school_code = s2.school_code and s2.school_code = 'code10';

+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | s2    | NULL       | ref  | code_index    | code_index | 1023    | const |   50 |      100 | NULL                                               |
|  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL       | NULL    | NULL  |  100 |      100 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+----------------------------------------------------+

将两个表的编码改为一致，均为utf8mb4后，在执行该关联查询语句后，发现效果与预期相同，mysql的执行计划使用了索引。

explain select * from school s1,student s2 where s1.school_code = s2.school_code and s2.school_code = 'code10';

+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | s1    | NULL       | ref  | code_index    | code_index | 1023    | const |    1 |      100 | NULL                  |
|  1 | SIMPLE      | s2    | NULL       | ref  | code_index    | code_index | 1023    | const |   50 |      100 | Using index condition |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-----------------------+

Recommend