用一条 DDL 偷袭老同志
背景
线上有一张表用来保存那些从上游数据公司买回来的数据，之前列 j 对应的值都是数字类型；现在因为规则上的变化，它的值变成了两个字母后面加 6 个数字。另外打头的两个字母可能有，也可能没有，整体上可以用这样一个表达正则描述 `([a-z]{2,2}|)[0-9]{6,6}` 。
问题在于 MySQL 库表层面也要改，并且为了防止以后这一列会变得更长，所以这次放大一点字符串的长度。要把列 j 的数据类型由 int 改成现在的 varchar(16) 。
业务把背景说清理了，那 DBA 就配合改吧。
库表变更
1、以前的库表结构
CREATE TABLE t (
id int NOT NULL AUTO_INCREMENT,
i int DEFAULT NULL,
j int DEFAULT NULL,
PRIMARY KEY (id),
KEY idx_i (i),
KEY idx_j (j)
) ENGINE=InnoDB
2、变更的语句
alter table t modify column j varchar(16) default null;3、变更后的表结构
CREATE TABLE t (
id int NOT NULL AUTO_INCREMENT,
i int DEFAULT NULL,
j varchar(16) DEFAULT NULL,
PRIMARY KEY (id),
KEY idx_i (i),
KEY idx_j (j)
) ENGINE=InnoDB使用真实的库表名会给我带来一些不必要的麻烦，上面的库表名本着在技术上够用的原则起的。
问题
刚开始灰度的时候，DBA 这边观察到 CPU 的使用率上去了，到日志里一看，这不正是刚才改的那张表吗！赶快看一眼执行计划。
mysql> explain format=tree select * from t where j = 1966462891;
+----------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------------------------------+
| -> Filter: (t.j = 1966462891) (cost=40209.25 rows=39945)
-> Table scan on t (cost=40209.25 rows=399450)
|
+----------------------------------------------------------------------------------------------------------------+
1 row in set, 2 warnings (0.00 sec)还有两个警告，看一下是什么内容。
mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------------------------------+
| Warning | 1739 | Cannot use ref access on index 'idx_j' due to type or collation conversion on field 'j' |
| Warning | 1739 | Cannot use range access on index 'idx_j' due to type or collation conversion on field 'j' |
+---------+------+-------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)可以看到由于列的类型已经从 int 改到了 varchar ，但是 select 查询并没有改写，还是在 where 条件里把 j 当成 int 来用，导致有类型转换用不了索引。
解决办法
和业务沟通后确认是 select 没有更新的问题，业务更新 SQL 发布新版本解决。
更新之前的 SQL
select * from t where j = 1966462891;更新之后的 SQL
select * from t where j = '1966462891';后记
1、业务并没有写 ` select * ` 我这里为了方便就这样写了。
2、如果 DBA 多确认一下业务代码有没有一变更，应该能提前把这个事防出去。另一个我觉得如果是用的 ORM ，应该也不会出这个事。
3、之前见过一个设计，表里面的列都是 varchar ，时间也是用 varchar 存的 ，算了都是泪。
本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2022-02-27，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
腾讯云开发者
