写点什么

SQL 优化(二):避免隐式转换

作者:hungxy
  • 2023-06-28
    广东
  • 本文字数:1835 字

    阅读完需:约 6 分钟

SQL 优化(二):避免隐式转换

今天介绍另外一个很容易踩坑的 SQL 例子,那就是隐式转换,隐式转换分为两种:隐式类型转换隐式字符编码转换,下面将分别介绍这两种隐式转换对 SQL 执行效率的影响

隐式类型转换

假设有这样一张订单表


CREATE TABLE `t_order` (  `order_id` bigint(20) NOT NULL AUTO_INCREMENT,  `order_no` varchar(45) NOT NULL,  `amount` decimal(9,2) DEFAULT NULL,  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,  PRIMARY KEY (`order_id`),  KEY `idx_order_no` (`order_no`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
复制代码


现在需要根据订单号查询订单信息,如果你的 SQL 语句这样写


select * from t_order where order_no = 64267814;
复制代码


从表结构来看,order_no 字段是有加索引的,但是 explain 的结果显示,这条 SQL 走的是全表扫描,并没有用到索引



这是因为,order_no 是 varchar 类型,而我们查询语句中的 where 条件,却是将 order_no 与整型进行对比,这里就涉及到一个问题,字符类型不能直接与整型进行对比,就需要做类型转换,需要将字符串转成整型,然后再进行对比,所以对于优化器来说,原本的 SQL 相当于


select * from t_order where cast(order_no as signed int) = 64267814;
复制代码


可以看到,这条 SQL 实际上对索引字段做了函数操作,上一条推文有讲到,如果对索引字段做了函数操作,那么 MySQL 优化器会放弃走索引


函数操作对索引的影响,可以看我上一篇推文《SQL 优化(一):慎用 SQL 函数》


到这里你可能会有个疑问,为什么是字符串转整型,而不是整型转字符串呢?如果是整型转字符串,那 SQL 会变成下面这样,这不就会走索引了吗


select * from t_order where order_no = '64267814';
复制代码


对于这个问题,我们有个简单的方法可以验证,执行select '10' > 9,看看 SQL 的执行结果


  1. 如果执行结果是 1,那么就是将字符串转为数字,做的是数字比较

  2. 如果执行结果是 0,那么就是将数字转为字符串,做的事字符串比较


在 MySQL 验证的结果如下:



证明在 MySQL 中,如果将字符串跟数字做比较的话,是会将字符串转换成数字

隐式字符编码转换

相对于隐式类型转换,隐式字符编码转换则更隐蔽一点。假如有另外一张订单详情表,用于记录订单详情,表结构如下


CREATE TABLE `t_order_detail` (  `order_detail_id` bigint(20) NOT NULL AUTO_INCREMENT,  `order_no` varchar(45) NOT NULL,  `product_name` varchar(100) DEFAULT NULL,  `product_amount` decimal(9,2) DEFAULT NULL,  `create_time` datetime DEFAULT CURRENT_TIMESTAMP,  PRIMARY KEY (`order_detail_id`),  KEY `idx_order_no` (`order_no`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8
复制代码


当我们想要查询某个订单的详情的时候,SQL 可能这样写


select od.*from t_order oinner join t_order_detail od on od.order_no = o.order_nowhere o.order_no = '64267814';
复制代码


执行计划如下



从执行计划可以看出,t_order 表用了 idx_order_no 索引,根据 where 条件中的 order_no 找到具体的记录,但是 t_order_detail 表并没有走索引,对 t_order_detail 表进行了全表扫描


这是因为,t_order 表跟 t_order_detail 表的字符编码不一样,t_order 表是 utf8mb4 编码,t_order_detail 表是 utf8 编码,两张表通过 order_no 关联的时候,要做字符编码转换,因为 utf8 不能直接与 utf8mb4 进行比较


因为 utf8mb4 是 utf8 的超集,所以 MySQL 内部在做类型转换的时候,会将 utf8 转换成 utf8mb4 再进行比较,这个很好理解,在做类型转换的时候,都是“按数据长度增长”的方向进行转换的,这样才能避免因为大数据集转换成小数据集导致数值失真。所以原本的 SQL 等同于


select od.*from t_order oinner join t_order_detail od on convert(od.order_no using utf8) = o.order_nowhere o.order_no = '64267814';
复制代码


知道了不走索引的原因后,我们对上面的 SQL 进行优化也很好优化,有下面两种优化方案


  • 比较简单的方法是将 t_order_detail 表也改成 utf8mb4 编码

  • 如果数据量比较大,或者暂时不能做 DDL 的话,可以将 SQL 修改成下面这种写法


select od.*from t_order oinner join t_order_detail od on od.order_no = convert(o.order_no using utf8)where o.order_no = '64267814';
复制代码


手动将 utf8mb4 的 t_order 表的 order_no 字段转换成 utf8 字符集,就避免了被驱动表 t_order_detail 上的字符编码转换,通过 explain 结果可以看到,这次两张表都走索引了



关注公众号:huangxy,一起学习,一起进步

发布于: 2023-06-28阅读数: 27
用户头像

hungxy

关注

还未添加个人签名 2018-07-19 加入

还未添加个人简介

评论

发布
暂无评论
SQL 优化(二):避免隐式转换_hungxy_InfoQ写作社区