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

概述

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

复现

建表语句,创建两个字符集不同的表,如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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;

初始化数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
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的执行计划并没有使用索引。

1
2
3
4
5
6
7
8
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的执行计划使用了索引。

1
2
3
4
5
6
7
8
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 |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-----------------------+