之前的一篇文件中《分析MySQL中隐式转换导致查询结果错误及索引不可用》分析了MySQL中隐式转换导致索引不可用的问题，最近又遇到一个索引不可用的案例；
1、问题背景
最近在使用MySQL上面发现了这样一个问题：MySQL两张表做left join时，执行计划里面显示有一张表使用了全表扫描，扫描全表近100万行记录，大并发的这样的SQL过来数据库变得几乎不可用了，今天和大家一起分享下这个问题的原因及解决办法，一起来看看吧！ （备注：MySQL版本为官方5.7.19）
2、问题重现
首先，创建测试表，表结构和表记录如下：
代码语言：javascript复制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)插入一些测试数据
代码语言：javascript复制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的执行计划如下：
代码语言：javascript复制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查看改写后的执行计划如下：
代码语言：javascript复制root@localhost [wjqtest]>show warningsG;
*************************** 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。
代码语言：javascript复制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 wt1G;
*************************** 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 wt1G; *************************** 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 warningsG; *************************** 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)
4、注意点
（1）表字符集不同时，可能导致join的SQL使用不到索引，引起严重的性能问题；
（2）SQL上线前要做好SQL Review工作，尽量在和生产环境一样的环境下Review；
（3）改字符集的alter table操作会阻塞写，尽量在业务低峰操作，建议用pt-online-schema-change;
（4）表结构字符集要保持一致，发布时要做好审核工作；
（5）如果要大批量修改表的字符集，同样做好SQL的Review工作，关联的表的字符集一起做修改。
5、问题讨论
最后问一个问题，假设现在wt1和wt2表的字符集还未修改，如果上面那个问题SQL换一下left join表的位置（即把wt2 left join wt1换成wt1 left join wt2），还会出现索引失效问题吗？为什么？这个问题就留给大家去思考吧!
