• 欢迎访问DBA的辛酸事儿,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站
  • 欢迎大家关注博主公众号:DBA的辛酸事儿
  • 博文中若有错误的地方,请大家指正,大家的指正是我前进的动力

MySQL表字段字符集不同导致的索引失效问题

MySQL SEian.G 2年前 (2019-05-19) 505次浏览 已收录 0个评论
文章目录[隐藏]

之前的一篇文件中《分析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)

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),还会出现索引失效问题吗?为什么?这个问题就留给大家去思考吧!

 

MySQL表字段字符集不同导致的索引失效问题


如果您觉得本站对你有帮助,那么可以收藏和推荐本站,帮助本站更好地发展,在此谢过各位网友的支持。
转载请注明原文链接:MySQL表字段字符集不同导致的索引失效问题
喜欢 (4)
SEian.G
关于作者:
用心去记录工作,用心去感受生活,用心去学着成长;座右铭:苦练七十二变,笑对八十一难
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址