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

- 日理万妓 2023-07-17 04:41 10阅读 0赞

## 概述 ##

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 |
    +----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-----------------------+