MySQL表字段字符集不同导致的索引失效问题学习心得分享

文章目录[隐藏]

1、问题背景
2、问题重现
3、问题解决
4、注意点
5、问题讨论

之前的一篇文件中《分析MySQL中隐式转换导致查询结果错误及索引不可用》分析了MySQL中隐式转换导致索引不可用的问题，最近又遇到一个索引不可用的案例；

1、问题背景

最近在使用MySQL上面发现了这样一个问题：MySQL两张表做left join时，执行计划里面显示有一张表使用了全表扫描，扫描全表近100万行记录，大并发的这样的SQL过来数据库变得几乎不可用了，今天和大家一起分享下这个问题的原因及解决办法，一起来看看吧！
（备注：MySQL版本为官方5.7.19）

2、问题重现

首先，创建测试表，表结构和表记录如下：

root@localhost [wjqtest]>CREATE TABLE `wt1` (
    -> `id` int(11) NOT NULL AUTO_INCREMENT,
    -> `name` varchar(20) DEFAULT NULL,
    -> `code` varchar(50) DEFAULT NULL,
    -> PRIMARY KEY (`id`),
    -> KEY `idx_code` (`code`),
    -> KEY `idx_name` (`name`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.10 sec)

root@localhost [wjqtest]>CREATE TABLE `wt2` (
    -> `id` int(11) NOT NULL AUTO_INCREMENT,
    -> `name` varchar(20) DEFAULT NULL,
    -> `code` varchar(50) DEFAULT NULL,
    -> PRIMARY KEY (`id`),
    -> KEY `idx_code` (`code`),
    -> KEY `idx_name` (`name`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.01 sec)

插入一些测试数据

root@localhost [wjqtest]>select * from wt1;
+----+------+----------------------------------+
| id | name | code                             |
+----+------+----------------------------------+
|  1 | aaaa | 95c823adfda3696e3308a69130fabe49 |
|  2 | bbbb | a39f105dfbe523ef39e21e05d14ed11f |
|  3 | cccc | d77968d3698e3ad4ef427bd6abeb8030 |
|  4 | dddd | 4997a466f833240c5f7f9624496d6138 |
|  5 | eeee | 8124104b2cc935438e80e6108d6e9817 |
|  6 | ffff | f44167000abe8e8f79095ae8ee276fcb |
+----+------+----------------------------------+
6 rows in set (0.00 sec)

root@localhost [wjqtest]>select * from wt2;
+----+------+----------------------------------+
| id | name | code                             |
+----+------+----------------------------------+
|  1 | aaaa | 50abfdaf12fb882093b1008366358265 |
|  2 | bbbb | 38bcd0c86b10e7b014460c277f6e6f0a |
|  3 | cccc | 21ab69a662a683dec668f81ca3231dad |
|  4 | dddd | 4b3d8e5024e2352a118d31c504f9d560 |
|  5 | eeee | 4b2fd4577600b4556092b230b49d4901 |
|  6 | ffff | 50f559c0ddc6fd747a56a0cc32a4ee24 |
+----+------+----------------------------------+
6 rows in set (0.00 sec)

2张表left join的执行计划如下：

root@localhost [wjqtest]>explain select * from wt2 left join wt1 on wt1.code = wt2.code where wt2.name = 'dddd';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | wt2   | NULL       | ref  | idx_name      | idx_name | 83      | const |    1 |   100.00 | NULL                                               |
|  1 | SIMPLE      | wt1   | NULL       | ALL  | NULL          | NULL     | NULL    | NULL  |    6 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

可以明显地看到，wt2.name = ‘dddd’使用了索引，而wt1.code = wt2.code这个关联条件没有使用到wt1.code上面的索引，为什么会出现这样的情况呢？难道是执行计划有问题？但是机器不会骗人。看到上面的有一个告警信息，用show warnings查看改写后的执行计划如下：

root@localhost [wjqtest]>show warnings\G;
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `wjqtest`.`wt2`.`id` AS `id`,`wjqtest`.`wt2`.`name` AS `name`,`wjqtest`.`wt2`.`code` AS `code`,`wjqtest`.`wt1`.`id` AS `id`,`wjqtest`.`wt1`.`name` AS `name`,`wjqtest`.`wt1`.`code` AS `code` from `wjqtest`.`wt2` left join `wjqtest`.`wt1` on((convert(`wjqtest`.`wt1`.`code` using utf8mb4) = `wjqtest`.`wt2`.`code`)) where (`wjqtest`.`wt2`.`name` = 'dddd')
1 row in set (0.00 sec)

在发现了convert(wjqtest.wt1.code using utf8mb4)之后，发现2个表的字符集不一样。wt1为utf8，wt2为utf8mb4。但是为什么表字符集不一样（实际是字段字符集不一样）就会导致wt1全表扫描呢？下面来做分析:

（1）首先wt2 left join t1决定了wt2是驱动表，这一步相当于执行了select * from wt2 where wt2.name = ‘dddd’，取出code字段的值，这里为’4b3d8e5024e2352a118d31c504f9d560’;

（2）然后拿wt2查到的code的值根据join条件去wt1里面查找，这一步就相当于执行了select * from wt1 where wt1.code = ‘4b3d8e5024e2352a118d31c504f9d560’;

（3）但是由于第（1）步里面wt2表取出的code字段是utf8mb4字符集，而wt1表里面的code是utf8字符集，这里需要做字符集转换，字符集转换遵循由小到大的原则，因为utf8mb4是utf8的超集，所以这里把utf8转换成utf8mb4，即把wt1.code转换成utf8mb4字符集，转换了之后，由于wt1.code上面的索引仍然是utf8字符集，所以这个索引就被执行计划忽略了，然后wt1表只能选择全表扫描。更糟糕的是，如果wt2筛选出来的记录不止1条，那么wt1就会被全表扫描多次，性能之差可想而知。

3、问题解决

既然原因已经清楚了，如何解决呢？当然是改字符集了，把wt1改成和wt2一样或者把wt2改成wt1都可以，这里选择把wt1转成utf8mb4。那怎么转字符集呢？

有人可能会说用alter table wt1 charset utf8mb4;但这是错的，这只是改了表的默认字符集，即新的字段才会使用utf8mb4，已经存在的字段仍然是utf8。

root@localhost [wjqtest]>alter table wt1 charset utf8mb4;  
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@localhost [wjqtest]>show create table wt1\G;
*************************** 1. row ***************************
       Table: wt1
Create Table: CREATE TABLE `wt1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
  `code` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_code` (`code`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

只有用alter table wt1 convert to charset utf8mb4;才是正确的。

但是还要注意一点，alter table 改字符集的操作是阻塞写的（用lock = none会报错）所以业务高峰时请不要操作，即使在业务低峰时期，大表的操作仍然建议使用pt-online-schema-change在线修改字符集。

关于MySQL字符集问题详细可参考：

深入理解MySQL字符集及校对规则(一)

深入理解MySQL字符集及校对规则(二)

root@localhost [wjqtest]>alter table wt1 convert to charset utf8mb4,lock=none; 
ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: Cannot change column type INPLACE. Try LOCK=SHARED.

root@localhost [wjqtest]>alter table wt1 convert to charset utf8mb4,lock=shared;
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0


root@localhost [wjqtest]>show create table wt1\G;
*************************** 1. row ***************************
       Table: wt1
Create Table: CREATE TABLE `wt1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `code` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_code` (`code`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)


root@localhost [wjqtest]>explain select * from wt2 left join wt1 on wt1.code = wt2.code where wt2.name = 'dddd';
+----+-------------+-------+------------+------+---------------+----------+---------+------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref              | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+------------------+------+----------+-------+
|  1 | SIMPLE      | wt2   | NULL       | ref  | idx_name      | idx_name | 83      | const            |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | wt1   | NULL       | ref  | idx_code      | idx_code | 203     | wjqtest.wt2.code |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)


root@localhost [wjqtest]>show warnings\G;
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `wjqtest`.`wt2`.`id` AS `id`,`wjqtest`.`wt2`.`name` AS `name`,`wjqtest`.`wt2`.`code` AS `code`,`wjqtest`.`wt1`.`id` AS `id`,`wjqtest`.`wt1`.`name` AS `name`,`wjqtest`.`wt1`.`code` AS `code` from `wjqtest`.`wt2` left join `wjqtest`.`wt1` on((`wjqtest`.`wt1`.`code` = `wjqtest`.`wt2`.`code`)) where (`wjqtest`.`wt2`.`name` = 'dddd')
1 row in set (0.00 sec)