SQL优化-隐式字符编码转换

数据库 其他数据库
对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能。

MySQL中我们知道有:

  • 如果对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能。
  • 隐式类型转换也会导致放弃走树搜索。

因为类型转换等价于在条件字段上使用了函数比如:

假设tradeid字段有索引,且为varchar类型:
mysql> select * from tradelog where tradeid=110717;
等价于:
mysql> select * from tradelog where CAST(tradid AS signed int) = 110717;

下面来看看隐式字符编码转换导致的一个慢SQL::

业务上有个SQL执行需要1.31秒:

看看执行计划:

从执行计划分析看出问题出在r表也就是 h_merge_result_new_indicator 表全表扫描,查看该表的表结有联合索引。但是联合索引范围后会失效,于是打算新建一个联合索引:

查看预新建联合索引的字段选择性:

结合选择性来看:

create index idx_hmrni on h_merge_result_new_indicator(keyName,module,BATCH_NO);

创建后,再次查看执行计划依然无效:

查看表结构:

另外3个表结构其中有2个utf8mb4,1个utf8:

字符集 utf8mb4 是 utf8 的超集,所以当这两个类型的字符串在做比较的时候,MySQL 内部的操作是:先把 utf8 字符串转成 utf8mb4 字符集,再做比较。

因此:

这部分会转换后再与h_merge_result_new_indicator关联。

优化就只需要将字符集编码转为utf8再和h_merge_result_new_indicator关联就能用上索引:

再看查询只需要0.02秒了:

但是还有个问题,如上执行计划key_len是606 =(100*3+3)+(100*3+3)

也就是说,没有用上BATCH_NO字段上的索引,我们知道索引少一个字段,占用会减少,不会太臃肿。因此,联合索引只需要包含r(keyName,module):

drop index idx_hmrni on h_merge_result_new_indicator;
create index idx_hmrni on h_merge_result_new_indicator(keyName,module);

结论

对索引字段做函数操作,可能会破坏索引值的有序性,因此优化器就决定放弃走树搜索功能。该例子是隐式字符编码转换,它们都跟其他条件索引上使用函数一样,因为要求在索引字段上做函数操作而导致了全索引扫描。

MySQL 的优化器确实有“偷懒”的嫌疑,即使简单地把 where id+1=1000 改写成 where id=1000-1 就能够用上索引快速查找,也不会主动做这个语句重写。

保证在条件索引上不做破坏索引值的有序性,是优化索引的利器。


责任编辑:武晓燕 来源: 数据和云
相关推荐

2009-09-04 10:49:19

C#隐式转换

2010-07-19 09:40:59

SQL Server数

2010-02-04 16:47:04

C++隐式转换

2009-07-21 13:54:55

Scala重载方法隐式转换

2023-08-14 08:35:36

2017-12-20 14:14:16

数据库MySQL数据类型

2010-09-14 17:08:11

SQL函数

2012-12-13 10:58:41

IBMdW

2010-05-11 17:45:17

MySQL字符

2009-02-12 10:14:16

2021-12-01 06:03:24

JS TrueJavaScript

2017-09-13 10:58:51

JavaScript转换规则

2017-09-05 16:17:35

JavaScript运算转换

2023-09-22 09:04:00

C++编程

2010-11-16 15:11:52

Oracle隐式游标

2023-12-12 08:50:22

MySQL隐式转换varchar

2021-03-01 10:01:22

开发技能编码

2019-09-28 22:41:18

OracleMySQL隐式数据

2010-07-06 09:39:20

SQL Server分

2010-09-06 17:22:01

SQL函数
点赞
收藏

51CTO技术栈公众号