SQL 优化(二):避免隐式转换
今天介绍另外一个很容易踩坑的 SQL 例子,那就是隐式转换,隐式转换分为两种:隐式类型转换和隐式字符编码转换,下面将分别介绍这两种隐式转换对 SQL 执行效率的影响
隐式类型转换
假设有这样一张订单表
现在需要根据订单号查询订单信息,如果你的 SQL 语句这样写
从表结构来看,order_no 字段是有加索引的,但是 explain 的结果显示,这条 SQL 走的是全表扫描,并没有用到索引
这是因为,order_no 是 varchar 类型,而我们查询语句中的 where 条件,却是将 order_no 与整型进行对比,这里就涉及到一个问题,字符类型不能直接与整型进行对比,就需要做类型转换,需要将字符串转成整型,然后再进行对比,所以对于优化器来说,原本的 SQL 相当于
可以看到,这条 SQL 实际上对索引字段做了函数操作,上一条推文有讲到,如果对索引字段做了函数操作,那么 MySQL 优化器会放弃走索引
函数操作对索引的影响,可以看我上一篇推文《SQL 优化(一):慎用 SQL 函数》
到这里你可能会有个疑问,为什么是字符串转整型,而不是整型转字符串呢?如果是整型转字符串,那 SQL 会变成下面这样,这不就会走索引了吗
对于这个问题,我们有个简单的方法可以验证,执行select '10' > 9
,看看 SQL 的执行结果
如果执行结果是 1,那么就是将字符串转为数字,做的是数字比较
如果执行结果是 0,那么就是将数字转为字符串,做的事字符串比较
在 MySQL 验证的结果如下:
证明在 MySQL 中,如果将字符串跟数字做比较的话,是会将字符串转换成数字
隐式字符编码转换
相对于隐式类型转换,隐式字符编码转换则更隐蔽一点。假如有另外一张订单详情表,用于记录订单详情,表结构如下
当我们想要查询某个订单的详情的时候,SQL 可能这样写
执行计划如下
从执行计划可以看出,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 等同于
知道了不走索引的原因后,我们对上面的 SQL 进行优化也很好优化,有下面两种优化方案
比较简单的方法是将 t_order_detail 表也改成 utf8mb4 编码
如果数据量比较大,或者暂时不能做 DDL 的话,可以将 SQL 修改成下面这种写法
手动将 utf8mb4 的 t_order 表的 order_no 字段转换成 utf8 字符集,就避免了被驱动表 t_order_detail 上的字符编码转换,通过 explain 结果可以看到,这次两张表都走索引了
关注公众号:huangxy,一起学习,一起进步
版权声明: 本文为 InfoQ 作者【hungxy】的原创文章。
原文链接:【http://xie.infoq.cn/article/d98034470357be6951cf732a7】。文章转载请联系作者。
评论