线上问题-我就加了个索引怎么就导致线上事故了

18,939 阅读5分钟

前言

不好啦❗ 天塌了❗ 系统崩了❗

快看啊,一个上线5年的业务,今天发个版突然就崩了

生产问题群爆炸了

image.png

我的心里活动:“太好了😀太好了😀终于给我碰上了,这个问题可很少发生啊,又积累血琳琳的生产一个问题”

不想看废话的直接看【解决过程和方案】 吧

排查过程

image.png

image.png

image.png

// 问题sql

select * from w_location where 
location_type = 'SORTING_TEMPORARY' 
AND 
status = '1'

问题就是这个sql 在线上查不到数据从而导致这个业务异常抛出。 但是我直连线上数据库发现这个条件是有数据的

然后查看发版记录,本次版本迭代改的跟这个功能毫无关系,甚至都没有发这个服务。真是奇了个怪,但是线上这里突然还是个必现的问题

接下来开始研究这个线上sql


CREATE TABLE `w_location` (
  `id` bigint(19) NOT NULL COMMENT '主键id',
  `warehouse_id` bigint(19) DEFAULT NULL COMMENT '仓库id',
  `warehouse_code` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '仓库code',
  `location_code` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '库位编码',
  `tunnel_id` bigint(19) DEFAULT NULL COMMENT '巷道id',
  `entity_type` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '实体类型',
  `high_low_type` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '高低类型',
  `row_number` tinyint(4) unsigned DEFAULT NULL COMMENT '排',
  `column_number` tinyint(4) unsigned DEFAULT NULL COMMENT '列',
  `floor_number` tinyint(4) unsigned DEFAULT NULL COMMENT '层',
  `grid` char(1) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '格',
  `emp_loc` bit(1) DEFAULT NULL COMMENT '是否空库位 0:不是空库位,1:是空库位',
  `location_classify_id` bigint(19) DEFAULT NULL COMMENT '库位分类id',
  `status` bit(1) DEFAULT NULL COMMENT '状态(1:生效,0:失效)',
  `load_id` bigint(19) DEFAULT NULL COMMENT '库位承载id',
  `area_id` bigint(19) DEFAULT NULL COMMENT '工作区id',
  `zone_id` bigint(19) DEFAULT NULL COMMENT '库区id',
  `location_type` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '库位类型',
  `picking_sequence` mediumint(7) DEFAULT NULL COMMENT '拣货动线号',
  `is_deleted` bit(1) DEFAULT NULL COMMENT '是否删除',
  `inventory_sequence` mediumint(7) DEFAULT NULL COMMENT '盘点动线号',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `create_user` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '创建人',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `update_user` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '更新人',
  `tenant_code` varchar(32) COLLATE utf8mb4_bin NOT NULL COMMENT '租户编码',
  `actual_temp_id` bigint(19) DEFAULT NULL COMMENT '实际温层 ID',
  `actual_temp_code` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '实际温层 CODE',
  `create_nick_name` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '创建用户姓名',
  `update_nick_name` varchar(50) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '更新用户姓名',
  `production_line_id` bigint(19) DEFAULT NULL COMMENT '生产线ID',
  `production_line_name` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '生产线名称',
  `instance_code` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '实例编码',
  `empty_flag` varchar(1) COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Y=空库位,N=非空库位;供点检使用',
  `picking_sequence_rearrangement` bigint(19) DEFAULT NULL COMMENT '重排的拣货动线号',
  `inventory_sequence_rearrangement` bigint(19) DEFAULT NULL COMMENT '重排的盘点动线号',
  `location_purpose` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '库位用途',
  `casual_pick_loc` bit(1) DEFAULT b'0' COMMENT '是否临时拣货位',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_wh_location_type` (`warehouse_code`,`location_code`,`location_type`),
  KEY `index_location_type_status` (`location_type`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC COMMENT='库位';


select * from w_location where location_type = 'SORTING_TEMPORARY' AND status = '1'


这个 status 字段是int类型 怎么就参数还搞个字符串
然后还有 KEY index_location_type_status (location_type,status) 这个索引

这让我一下子就联想到之前学习mysql时的一个知识点, 索引+隐式数据转换会带来意想不到的问题

快速解决线上问题

紧急联系DBA 把发版的新增的索引给干掉

索引+隐式数据转换

MySQL中隐式转换详细查看官方文档相关的说明:

dev.mysql.com/doc/refman/…

复现线上问题

  • 下面这三张图: 就可以很清晰的说明问题了: 索引+隐式数据转换会带来意想不到的问题

image.png

image.png

image.png

索引+隐式数据转换 理论解析

根据现象分析,根本原因是MySQL的bit类型字段在索引查询时的隐式类型转换规则导致的。以下是具体解释:

问题核心原因:

bit类型存储特性:

status字段bit(1)实际存储的是二进制值: TRUE存储为 b'1'(二进制值) FALSE存储为 b'0'

索引影响下的隐式转换:

  • 有索引时:

status='1' → 需要将bit转为字符串比较(b'1' → "1") status=1 → 将bit转为整数比较(b'1' → 1)

  • 无索引时:

两者都会做全表扫描,MySQL统一进行类型转换

  • 这个case 只有bit(1)的时候 才会出现bug

下面这俩sql 你可以执行下 bit(1) 虽然存储的是 0 1 但他在mysql中 不是int类型

image.png

image.png

  • 下面的介绍更全面 来源网友

image.png

总结

存在索引的时候:status = '1':字符串'1'的 ASCII 码为 49,与bit(1)的b'1'(二进制值 1)不匹配,导致索引无法命中。

mysql 隐式转换的坑还有很多,并且隐式转化有很多默认规则,这个我们控制不来。 我们能做到的就是尽量避免隐式转换