MySQL 隐式转换必知必会

原创
05/21 17:10
阅读数 2.4K

在生产环境中经常会有一些隐式类型转换导致SQL索引失效,性能极差,进而影响影响集群负载和业务的情况。本文总结了隐式转换常见的场景,在生产中要尽量避免 SQL 隐式转换的出现。

作者:张洛丹,热衷于数据库技术,不断探索,期望未来能够撰写更有深度的文章,输出更有价值的内容!

爱可生开源社区出品,原创内容未经授权不得随意使用,转载请联系小编并注明来源。

本文约 3000 字,预计阅读需要 10 分钟。

常见的 SQL 产生隐式转换的场景有:

  1. 数据类型的隐式转换
  2. 字符集的隐式转换

其中,特别是在表连接场景和存储过程中的字符集转换很容易被忽略。

说明:字符集是针对字符类型数据的编码规则,对于数值类型则不需要进行转换字符集。

数据类型的隐式转换

测试表结构

t1 表字段 a 为 VARCHAR 类型,t2 表字段 a 为 INT 类型。

mysql> show create database test1\G
*************************** 1. row ***************************
       Database: test1
Create Database: CREATE DATABASE `test1` /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)
mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `a` varchar(20) DEFAULT NULL,
  `b` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

单表示例

这里需要说明的是,有以下两种类型的转换:

  1. 当字段类型为字符串类型,参数为整型时,会导致索引失效
  2. 而字段类型为整型,传入的参数为字符串类型时,不会导致索引失效

这是因为在字符串与数字进行比较时,MySQL 会将字符串类型转换为数字进行比较,因此当字段类型为字符串时,会在字段上加函数,而导致索引失效。

官方文档说明:Strings are automatically converted to numbers and numbers to strings as necessary.

-- 字段类型为varchar,传参为整数,无法走到索引
mysql> explain select * from t1 where a=1000;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | a             | NULL | NULL    | NULL | 498892 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)
mysql> show warnings;
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                           |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1739 | Cannot use ref access on index 'a' due to type or collation conversion on field 'a'                                                               |
| Warning | 1739 | Cannot use range access on index 'a' due to type or collation conversion on field 'a'                                                             |
| Note    | 1003 | /* select#1 */ select `test1`.`t1`.`id` AS `id`,`test1`.`t1`.`a` AS `a`,`test1`.`t1`.`b` AS `b` from `test1`.`t1` where (`test1`.`t1`.`a` = 1000) |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
-- 字段类型为int,传参为字符串,可以走到索引
mysql> explain select * from t2 where a='1000';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t2    | NULL       | ref  | a             | a    | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

至于为什么不能将数字转换为字符串进行比较呢?

下面的比较结果:

  • 字符串的比较是逐个比较字符串的大小,直到找到不同的字符,这样的比较结果和数字的比较结果是不同的。
mysql> select '2000' <'250';
+---------------+
| '2000' <'250' |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)

表连接中的数据类型转换

当两个表的连接字段类型不一致时会导致隐式转换(MySQL 内部增加 cast() 函数),无法走到连接字段索引,进而可能无法使用最优的表连接顺序。

原本作为被驱动表的表由于无法使用到索引,而可能作为驱动表。

示例:

  • 如下,正常情况下会选择 t2 表作为驱动表,但由于数据类型不同,实际上执行的 SQL 是:select * from t1 join t2 on cast(t1.a as unsigned)=t2.a where t2.id<1000
  • 如果 t1 作为被驱动表,则没有办法走到 t1.a 的索引,因此选择 t1 表作为驱动表
mysql> explain select * from t1 join t2 on t1.a=t2.a where t2.id<1000;
+----+-------------+-------+------------+------+---------------+------+---------+------------+--------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref        | rows   | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+------+---------+------------+--------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | a             | NULL | NULL    | NULL       | 498892 |   100.00 | Using where           |
|  1 | SIMPLE      | t2    | NULL       | ref  | PRIMARY,a     | a    | 5       | test1.t1.a |      1 |     5.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+------+---------+------------+--------+----------+-----------------------+
2 rows in set, 2 warnings (0.00 sec)
mysql> show warnings;
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                                                                                                                                    |
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1739 | Cannot use ref access on index 'a' due to type or collation conversion on field 'a'                                                                                                                                                                                                        |
| Note    | 1003 | /* select#1 */ select `test1`.`t1`.`id` AS `id`,`test1`.`t1`.`a` AS `a`,`test1`.`t1`.`b` AS `b`,`test1`.`t2`.`id` AS `id`,`test1`.`t2`.`a` AS `a`,`test1`.`t2`.`b` AS `b` from `test1`.`t1` join `test1`.`t2` where ((`test1`.`t2`.`id` < 1000) and (`test1`.`t1`.`a` = `test1`.`t2`.`a`)) |
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)

字符集的隐式转换

当参数字符集和字段字符集不同时,无法直接进行比较,而需要进行字符集转换,则可能需要在转换字段上加 convert() 函数来转换字符集,导致索引失效。

测试表结构

  • 数据库字符集是 UTF8MB4
  • t1 表字符集是 UTF8
  • t2 表字符集是 UTF8MB4
mysql> show create database test\G
*************************** 1. row ***************************
       Database: test
Create Database: CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 */
mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `a` varchar(20) DEFAULT NULL,
  `b` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `a` varchar(20) DEFAULT NULL,
  `b` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.01 sec)

单表示例

-- 正常执行时,匹配字段的字符集(没有单独指定时继承表的字符集)
mysql> explain select * from t1 where a='1000';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ref  | a             | a    | 63      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

-- 将参数转换不同的字符集,无法走到索引,而是全表扫描
mysql> explain select * from t1 where a=convert('1000' using utf8mb4);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 2000 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)


-- show warnings可以看到优化器进行了转换,在t1.a上加了convert函数,从而无法走到索引
mysql> show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                               |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where (convert(`test`.`t1`.`a` using utf8mb4) = <cache>(convert('1000' using utf8mb4))) |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

另外,需要注意的是:

MySQL 内部会优先将低级的字符集转换为更高级的字符集,例如将 UTF8 转换为 UTF8MB4。

在前面的示例中,convert() 函数加在 t1.a 上,而下面这个示例,convert() 函数加在参数上,而非 t2.a 字段上,这种情况则没有导致性能变差:

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `a` varchar(20) DEFAULT NULL,
  `b` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
mysql> explain select * from t2 where a=convert('1000' using utf8);
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t2    | NULL       | ref  | a             | a    | 83      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                   |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `test`.`t2`.`id` AS `id`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t2` where (`test`.`t2`.`a` = convert(convert('1000' using utf8) using utf8mb4)) |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

综上:

  • 在当表字段字符集为更低级的字符集(如 UTF8),而传入的值为更高级的字符集(如 UTF8MB4),则此时会转换表字段的字符集,相当于字段上使用了函数,索引失效。
  • 当表字段为更高级的字符集(如 UTF8MB4),而传入的值为更低级的字符集(如 UTF8),则此时会将传入的值进行字符集转换,并不会导致索引失效。

但我们通常不会去手工使用 convert() 函数转换参数的字符集,在后文两种场景中可能会出现比较容易忽略的隐式类型转换,引发生产问题。

表连接中的字符集转换

当两个表的连接字段字符集不一致时会导致隐式转换(MySQL 内部增加 convert() 函数),无法走到连接字段索引,进而可能无法使用最优的表连接顺序。

原本作为被驱动表的表由于无法使用到索引,而可能作为驱动表。

示例:

  • 正常情况下,MySQL 会优先小结果集的表作为驱动表,在本例中即为 t2 为驱动表,t1 为被驱动表。
  • 但是由于字符集不同,实际上执行的 SQL 为 show warnings 看到的,对 t1.a 字段加了 convert() 函数进行转换字符集,则无法走到 t1.a 字段的索引而不得不改变连接顺序。
mysql> explain select * from t1 left join t2 on t1.a=t2.a where t2.id<1000;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 498649 |   100.00 | NULL                  |
|  1 | SIMPLE      | t2    | NULL       | ref  | PRIMARY,a     | a    | 83      | func |      1 |     4.79 | Using index condition |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-----------------------+
2 rows in set, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                                |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`id` AS `id`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t2` where ((`test`.`t2`.`id` < 1000) and (convert(`test`.`t1`.`a` using utf8mb4) = `test`.`t2`.`a`)) |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)




-- 在下面示例中,虽然也发生了类型转换,但是效率并没有变差,因为原本最优的连接顺序就是t1作为驱动表
mysql> explain select * from t1 left join t2 on t1.a=t2.a where t1.id<1000;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |  999 |   100.00 | Using where |
|  1 | SIMPLE      | t2    | NULL       | ref   | a             | a       | 83      | func |    1 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)


mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                                   |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`id` AS `id`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` left join `test`.`t2` on((convert(`test`.`t1`.`a` using utf8mb4) = `test`.`t2`.`a`)) where (`test`.`t1`.`id` < 1000) |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

存储过程中的字符集转换

这也是比较容易忽略的一种场景,问题的发现是在生产环境存储过程中根据主键更新,但却需要执行 10s+。

存储过程中变量的字符集默认继承自 database 的字符集(也可以在创建时指定),当表字段字符集和 database 的字符集不一样时,就会出现类似前面的隐式字符集类型转换。

示例:

  • database 的字符集是 UTF8MB4
  • character_set_clientcollation_connection 是创建存储过程时会话的 character_set_clientcollation_connection 的值
  • 经测试存储过程中的变量的字符集是和数据库级别的字符集一致
-- 存储过程信息: Database Collation: utf8mb4_general_ci
mysql> show create procedure update_data\G
*************************** 1. row ***************************
           Procedure: update_data
            sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`%` PROCEDURE `update_data`()
begin
  declare j int;
  declare n varchar(100);
   select charset(n);
  set j=1;
  while(j<=2000)do
set n = cast(j as char);
select 1,now();
    update t1 set b=concat(b,'1') where a=n;
select 2,now();
select sleep(1);
    set j=j+1;
  end while;
end
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8mb4_general_ci
1 row in set (0.00 sec)
如下,在执行存储过程后,看到打印的变量n的字符集是utf8mb4


mysql> call update_data();
+------------+
| charset(n) |
+------------+
| utf8mb4    |
+------------+
1 row in set (0.00 sec)

根据索引字段 a 更新的语句实际上是变成了下面这样,走的是全表扫描(type:index,key:primary)。

mysql> explain update t1 set b=concat(b,'1') where a=convert('1000' using utf8mb4);
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | UPDATE      | t1    | NULL       | index | NULL          | PRIMARY | 4       | NULL | 498649 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
1 row in set (0.00 sec)


-- 而正常情况下,执行计划为:
mysql> explain update t1 set b=concat(b,'1') where a='1000';
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------------+
|  1 | UPDATE      | t1    | NULL       | range | a             | a    | 63      | const |    1 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------------+
1 row in set (0.00 sec)

更新时间也由 0.00sec 变为 0.60sec,在表数据量很大的情况下,全表扫描将会对生产产生较大影响。

mysql> update t1 set b=concat(b,'1') where a='1000';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> update t1 set b=concat(b,'1') where a=convert('1000' using utf8mb4);
Query OK, 1 row affected (0.60 sec)
Rows matched: 1  Changed: 1  Warnings: 0

如何避免隐式转换

对于数据类型的隐式转换:

  1. 规范数据类型的选择
  2. SQL 传参与字段数据类型匹配

对于字符集的隐式转换:客户端字符集、服务器端字符集、数据库字符集、表字符集、字段字符集保持一致。

更多技术文章,请访问:https://opensource.actionsky.com/

关于 SQLE

SQLE 是一款全方位的 SQL 质量管理平台,覆盖开发至生产环境的 SQL 审核和管理。支持主流的开源、商业、国产数据库,为开发和运维提供流程自动化能力,提升上线效率,提高数据质量。

SQLE 获取

类型 地址
版本库 https://github.com/actiontech/sqle
文档 https://actiontech.github.io/sqle-docs/
发布信息 https://github.com/actiontech/sqle/releases
数据审核插件开发文档 https://actiontech.github.io/sqle-docs/docs/dev-manual/plugins/howtouse
展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部