mysql隐式类型转换造成的慢查询

mysql隐式类型转换造成的慢查询

  

线上业务发现了一个慢查询,经过分析,发现原因是mysql的隐式类型转换造成的索引失效。本文详细介绍了mysql 隐式类型转换的规则,最后利用这些规则,对 Laravel 框架进行了安全改造。

  

一、异常情况

  
前段时间,线上业务 php 代码出现了一个 sql 慢查询,简化为如下例子:

mysql> select * from test where name=1111;

+----+------+---------+
| id | name | randnum |
+----+------+---------+
| 31 | 1111 |     607 |
+----+------+---------+
1 row in set

  
其对应的表结构为:

CREATE TABLE `test` (
  `id`      int(11)         NOT NULL AUTO_INCREMENT,
  `name`    varchar(100)    NOT NULL,
  `randnum` int(11)         NOT NULL DEFAULT '0',

  PRIMARY KEY (`id`),
  KEY `name` (`name`)
);

  
这个 select 语句的 where 条件使用了 name = 1111 的写法,但是 name字段是 string 类型,1111 是 int 类型。explain 分析如下,type = All 表示造成了全表扫描。

mysql> explain select * from test where name=1111;

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | ALL  | name          | NULL | NULL    | NULL |   35 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set

  
这样也能查询出结果,而不是报错,说明 mysql 对等于号两边的参数,进行了 隐式的类型转换,从而做到了兼容,但是同时 失去了name 字段索引 的使用。

  

二、正常情况

  
调整 where 语句为正常的 name = "1111" 时,此次查询同样能够查出数据,且正常地应用了索引。

mysql> select * from test where name="1111";

+----+------+---------+
| id | name | randnum |
+----+------+---------+
| 31 | 1111 |     607 |
+----+------+---------+
1 row in set

  
explain 结果,此时 type = ref。

mysql> explain select * from test where name="1111";

+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | test  | ref  | name          | name | 402     | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
1 row in set

  

三、mysql 隐式类型转换的规则

  
这里基于 mysql 5.7 的官方文档,给出进行比较判断时 2 个参数类型的隐式转换规则。
  

  1. 如果 2 个参数至少有一个为 null, 那么判断结果直接判定为 null。这条规则有一个例外,就是 <=> 操作符,当计算 null <=> null,会返回 true 的结果。
  2. 如果 2 个参数都是 string 类型,那么不进行转换,按照 string 进行比较。
  3. 如果 2 个参数都是 int 类型,那么不进行转换,按照 int 进行比较。
  4. 16 进制数如果和 int 类型比较,那么它就会被转化为 int 类型;如果和 string 类型进行比较,那么它会被转化为二进制字符串。
  5. 如果有一个参数为 TIMESTAMP 或者 DATETIME 类型时,如果另一个参数为常量,那么这个常量会被转化为对应的时间类型。但是使用 IN() 语法进行判断时,不会进行这个隐式转换。mysql 推荐在跨类型比较时间时,使用 CAST() 语法进行显示类型转化。
  6. 如果有一个参数为 DECIMAL 类型,当另一个参数为 DECIMAL 类型或者 int 类型时,它们会被转化为 DECIMAL 类型进行比较;当另一个参数为 float 类型时,它们会被转化为 float 类型进行比较。
  7. 所有其他情况下,2 个参数都会被转化为 float 实数进行比较。(int 类型也包含在 float 类型中)

  

这里可以看出,本文例子中的隐式类型转换对应了第 7 条规则:字符串被转成了数字(int 包含在 float 中)

  

四、为什么 string 转化为 float 会导致索引失效?

  
mysql 转换字符串到数字时,使用的规则类似于弱类型编程语言那样:从这个 string 的前面第一个有效字符开始解析,一直到解析出错,这样就会造成不同的字符串会转化为相同的数字。
  
下面看下几个字符串转化的结果。

mysql> select "1111" = 1111; 

+---------------+
| "1111" = 1111 |
+---------------+
|             1 |   # 结果相等
+---------------+
1 row in set
mysql> select "     1111" = 1111;

+--------------------+
| "     1111" = 1111 |
+--------------------+
|                  1 |   # 结果也相等
+--------------------+
1 row in set
mysql> select "     1111aaaaaa" = 1111;

+--------------------------+
| "     1111aaaaaa" = 1111 |
+--------------------------+
|                        1 |    # 结果依然相等
+--------------------------+
1 row in set
mysql> select "0001111aaaaaa" = 1111;

+------------------------+
| "0001111aaaaaa" = 1111 |
+------------------------+
|                      1 |      # 还是相等
+------------------------+
1 row in set

  
上面 4 个例子明确表明,不同的 string 可以转化为同一个 int(int 包含在 float 中),但是这些 string 明显不相等,它们的索引条目也是分散在整个索引中。

所以 mysql 无法利用索引减少检索条目。

  

五、php 的难处

  
这种类型误传是很难被发现的,发现的时候,基本都是线上爆出了慢查询。

这里强类型语言的好处就体现出来了:先判断变量类型再决定是否拼接双引号。

但是对 php 而言,无论是手工拼接 sql 语句,还是使用 prepare binding 生成查询,都不能消除这种风险,所以最好有工具能够帮助使用弱类型语言的程序员避免这种问题。

  

六、利用规则

  
通过上面的规则,可以得知,每个类型和 string 进行比较时,都会优先把字符串转化为对应的类型,所以我们可以可以把所有的 sql 参数都转化为 string 类型,这样,就避免了 string 列在和 int 比较时,失去索引依赖的风险。同时,对正常的查询也没有什么坏影响。

因此,php 的 orm 框架实现中,可以把所有 binding 参数都转化为 string 类型。(手工拼接 sql 的情形就没办法了,推荐他们转化到 orm 框架上来)

  

七、hacking 例子

  
作者经历过的一个项目中使用了自研的 orm 框架,就使用了上面所说的技巧。

当前开源的主流 orm 框架,如 laravel 的 Eloquent ,就还未增加这个处理。

  

这里以 laravel 5.5 为例子,它的关键函数是 binding 参数的保存。

原样是:

// vendor\laravel\framework\src\Illuminate\Database\Query\Builder.php 

public function addBinding($value, $type = 'where') {
    if (!array_key_exists($type, $this->bindings)) {
        throw new InvalidArgumentException("Invalid binding type: {$type}.");
    }

    if (is_array($value)) {
        $this->bindings[$type] = array_values(array_merge($this->bindings[$type], $value));
    } else {
        $this->bindings[$type][] = $value;
    }

    return $this;
}

  
接下来,在它的 binding 参数保存前,增加一步字符串转化。

修改后是:

// vendor\laravel\framework\src\Illuminate\Database\Query\Builder.php

public function addBinding($value, $type = 'where') {
    if (!array_key_exists($type, $this->bindings)) {
        throw new InvalidArgumentException("Invalid binding type: {$type}.");
    }

    if (is_array($value)) {
        $this->bindings[$type] = array_values(array_merge($this->bindings[$type], $value));
        $tmp = [];
        // 把数组中的每个元素都转化为 string
        foreach ($this->bindings[$type] as $k => $v) {
            $tmp[$k] = "" . $v;
        }
        $this->bindings[$type] = $tmp;

    } else {
        // 把一个值转化为 string
        $this->bindings[$type][] = "" . $value;
    }
    return $this;
}

  

八、参考文档

  
《MySQL :: MySQL 5.7 Reference Manual :: 12.2 Type Conversion in Expression Evaluation》

https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html

  
  
全文完
  

阅读感受肿么样?

平均分: / 5. 打分次数:

发表评论

电子邮件地址不会被公开。 必填项已用*标注