修改字符集导致全表扫案例一分析

DBA入坑指南 2021-04-18

261

一、现象描述

2019/08/05 23:34:51 [E] [xxxx.go:396] [grep_info_oid:xxxxxxx grep_info_sid:xxxxxxx]插入xxxx出错, sql: expected 1 arguments, got 10
2019/08/05 23:34:51 [E] [xxxx.go:381] [grep_info_oid:xxxxxxx grep_info_sid:xxxxxxx]更新xxxx出错, sql: expected 1 arguments, got 2

二、问题分析

出问题前，笔者对grep_test_info表做了变更，变更的步骤是：

删除三个触发器
把表的字符集从utf8改为utf8mb4
把触发器加回来

问题发生在把触发器加回来后。此时主库活动线程数达到了近200，紧接着因为负载过高，自动切换agent执行命令失败，判定为主库不可用，触发了主备切换。

主备切换后，业务仍然没有恢复，通过orzdba查看到的信息如下：

orzdba -H xx.xx.xx.xx -com -innodb_rows -lazy -T -t -hit 2>/dev/null
-------- -----load-avg---- ---cpu-usage--- ---swap---                     -QPS- -TPS-         -Hit%- ---innodb rows status--- ------threads------ 
  time  |  1m    5m   15m |usr sys idl iow|   si   so|  ins   upd   del    sel   iud|     lor    hit|  ins   upd   del   read| run  con  cre  cac|
00:38:46| 0.12  0.14  0.09|  1   1  98   0|    0    0|    1     0     0     30     1| 2423699 100.00|    1     0     0 4459896|   6   49    0   2|
00:38:47| 0.12  0.14  0.09|  1   1  98   0|    0    0|    0    14     0     71    14| 2456563 100.00|    0    12     0 4382821|   6   53    0   2|
00:38:48| 0.12  0.14  0.09|  1   1  98   0|    0    0|    0     0     0     27     0| 2438989 100.00|    0     0     0 4483396|   7   54    0   2|
00:38:49| 0.12  0.14  0.09|  1   0  99   0|    0    0|    0     0     0     20     0| 2420252 100.00|    0     0     0 4496494|   7   54    0   2|
00:38:50| 0.12  0.14  0.09|  1   1  96   1|    0    0|    0     0     0     22     0| 2417326 100.00|    0     2     0 4514574|   7   55    0   2|
00:38:51| 0.11  0.14  0.09|  2   1  97   0|    0    0|    0     0     0     38     0| 2418516 100.00|    0     0     0 4519165|   7   57    0   2|
00:38:52| 0.11  0.14  0.09|  1   1  98   0|    0    0|    0     0     0     45     0| 2409699 100.00|    0     0     0 4528086|   7   57    0   2|
00:38:53| 0.11  0.14  0.09|  1   1  98   0|    0    0|    0     0     0     31     0| 2417106 100.00|    0     2     0 4517124|   7   57    0   2|
00:38:54| 0.11  0.14  0.09|  1   0  98   0|    0    0|    0     0     0     33     0| 2411289 100.00|    0     0     0 4470164|   7   57    0   2|

‍

从这些信息可以得到的结论是，数据库正在执行大量的数据扫描，每秒扫描行数达到400多万行，而QPS/TPS非常低，说明在做全表扫描。

此时show processlist看到的信息如下：

+----------+-----------+--------------------+----------------+---------+------+--------------+--------------------------------------------------------------------------------------+----------+
| Id       | User      | Host               | db             | Command | Time | State        | Info                                                                                 | Progress |
+----------+-----------+--------------------+----------------+---------+------+--------------+--------------------------------------------------------------------------------------+----------+
......
| 19939958 | xxxx_user | xxxxxx:39254 | grep_db_test | Execute |   24 | Sending data | SET  @grep_val= (select grep_vv_id from grep_info_stat where grep_info_sid=new.grep_info_sid) |   0.000 |
| 19939959 | xxxx_user | xxxxxx:38182 | grep_db_test | Execute |   26 | Sending data | SET  @grep_val= (select grep_vv_id from grep_info_stat where grep_info_sid=new.grep_info_sid) |   0.000 |                                                                                |    0.000 |
| 19939961 | xxxx_user | xxxxxx:52218 | grep_db_test | Query   |   24 | Sending data | SET  @grep_val= (select grep_vv_id from grep_info_stat where grep_info_sid=new.grep_info_sid) |   0.000 |

上面的SQL是grep_test_info表触发器里调用的。可以看到，很简单的一个SQL都要执行几十秒还没有完成，极有可能这些SQL在做全表扫描。结合之前了解到的变更操作，问题基本定位了，很可能跟字符集变更导致的隐式转换有关。

我们看看这两个表的字符集情况：

mysql> show create table grep_info_stat\G
*************************** 1. row ***************************
       Table: grep_info_stat
Create Table: CREATE TABLE grep_info_stat (
  id bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  grep_info_sid varchar(32) NOT NULL COMMENT '测试xxx',
  ......
  PRIMARY KEY (id),
  UNIQUE KEY uniq_gi_sid (grep_info_sid),
  ......
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='grep状态测试表'
1 row in set (0.00 sec)
mysql> show create table grep_test_info\G
*************************** 1. row ***************************
       Table: grep_test_info
Create Table: CREATE TABLE grep_test_info (
  id bigint(20) NOT NULL,
  grep_info_sid varchar(32) NOT NULL COMMENT '测试xxx',
  ......
  PRIMARY KEY (id),
  KEY idx_gi_sid (grep_info_sid),
  ......
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='grep信息测试表';

可以看到，两个表的字符集是不一致的，grep_test_info是utf8mb4，而grep_info_stat是utf8，我们知道，当变量值(来自表grep_test_info)和varchar类型的字段(来自grep_info_stat)字符集不一致时，会产生隐式转换，会把变量和字段都统一转为优先级更高的字符集(如果是子集和超集的关系，则会都转为超集)，通俗点说就是大鱼吃小鱼。

思考：如果grep_test_info是utf8，而grep_info_stat是utf8mb4呢？留给读者思考一下

所以之前show processlist看到的触发器部分的查询的一个实际的例子如下：

mysql> explain select grep_vv_id from grep_info_stat where grep_info_sid=convert('xxxxxxxxxxx' using utf8mb4);
+------+-------------+--------------+------+---------------+------+---------+------+----------+-------------+
| id   | select_type | table        | type | possible_keys | key  | key_len | ref  | rows     | Extra       |
+------+-------------+--------------+------+---------------+------+---------+------+----------+-------------+
|    1 | SIMPLE      |grep_info_stat| ALL  | NULL          | NULL | NULL    | NULL | 13823382 | Using where |
+------+-------------+--------------+------+---------------+------+---------+------+----------+-------------+

这个SQL会发生隐式转换，实际相当于：

select grep_vv_id from grep_info_stat where convert(grep_info_sid using utf8mb4) =convert('xxxxxxxx' using utf8mb4);

这就导致了用不到索引走全表扫描了。

知道原因，解决就比较简单了，想办法把变量的字符集修改为字段的字符集一致就可以了：

mysql> explain select grep_vv_id from grep_info_stat where grep_info_sid=convert('xxxxxxxxxxx' using utf8);
+------+-------------+--------------+-------+-----------------+-----------------+---------+-------+------+-------+
| id   | select_type | table        | type  | possible_keys   | key             | key_len | ref   | rows | Extra |
+------+-------------+--------------+-------+-----------------+-----------------+---------+-------+------+-------+
|    1 | SIMPLE      |grep_info_stat| const | uniq_gi_sid     | uniq_gi_sid     | 98      | const |    1 |       |
+------+-------------+--------------+-------+-----------------+-----------------+---------+-------+------+-------+
1 row in set (0.00 sec)
mysql> select grep_vv_id from grep_info_stat where grep_info_sid=convert('xxxxxxxxxxx' using utf8);
+-----------+
| grep_vv_id|
+-----------+
| xxxxxxxxx |
+-----------+
1 row in set (0.01 sec)

可以看到，进行强制转换后，可以走索引了，执行非常快速，因为对于utf8和utf8mb4来说，除了emoji表情外，一般的字符都是可以相互转换的，查询结果也没有问题。

用这个思路修改了触发器，把触发器重建后恢复了。触发器代码如下：

CREATE TRIGGER xxxxx_after_insert_grep_test_info AFTER INSERT ON grep_test_info FOR EACH ROW BEGIN
 set @grep_val= (select  grep_vv_id from grep_info_stat where grep_info_sid=convert(new.grep_info_sid using utf8));
if (@grep_val = 'xxxxxx') then
......
END
/

注：这只是暂时解决了问题，后期还是要把grep_info_stat表的字符集转换为utf8mb4。

三、反思

DBA在转换字符集的时候，要特别注意一下可能由于相关联的表字符集不一致，导致隐式转换的问题，建议跟开发反复核对，不重要的业务也要检查一遍，不要吃这种小亏，防患于未然。

四、数据库规范

【强制】线上库表字符集统一为utf8mb4

注：以前utf8的表由于历史原因可根据实际情况决定是否处理，建议和开发一起检查一下是否有不同字符集的表关联的情况，utf8mb4比utf8多一点空间，笔者觉得完全可以接受，MySQL的utf8是阉割版，对于我们来说避坑更重要。

喜欢作者，可以关注一下

数据库

文章转载自DBA入坑指南，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

修改字符集导致全表扫案例一分析

一、现象描述

二、问题分析

三、反思

四、数据库规范

评论