作者：张洛丹，热衷于数据库技术，不断探索，期望未来能够撰写更有深度的文章，输出更有价值的内容！
爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。
本文约 3000 字，预计阅读需要 10 分钟。
在生产环境中经常会有一些隐式类型转换导致SQL索引失效，性能极差，进而影响影响集群负载和业务的情况。本文总结了隐式转换常见的场景，在生产中要尽量避免 SQL 隐式转换的出现。
常见的 SQL 产生隐式转换的场景有：
- 数据类型的隐式转换
- 字符集的隐式转换
其中，特别是在表连接场景和存储过程中的字符集转换很容易被忽略。
说明：字符集是针对字符类型数据的编码规则，对于数值类型则不需要进行转换字符集。
1数据类型的隐式转换
测试表结构
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)
单表示例
这里需要说明的是，有以下两种类型的转换：
- 当字段类型为字符串类型，参数为整型时，会导致索引失效
- 而字段类型为整型，传入的参数为字符串类型时，不会导致索引失效
这是因为在字符串与数字进行比较时，MySQL 会将字符串类型转换为数字进行比较，因此当字段类型为字符串时，会在字段上加函数，而导致索引失效。
官方文档说明[1]：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' 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) = (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 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` explain select * from t1 left join t2 on t1.a=t2.a where t1.id 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` 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 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
3如何避免隐式转换
对于数据类型的隐式转换：
- 规范数据类型的选择
- SQL 传参与字段数据类型匹配
对于字符集的隐式转换：客户端字符集、服务器端字符集、数据库字符集、表字符集、字段字符集保持一致。
参考资料
[1]
comparison-operators.html: https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html
本文关键字：#MySQL# #字符集# #数据类型#
故障分析 | 如何解决由触发器导致 MySQL 内存溢出？
技术分享 | MySQL VARCHAR 最佳长度评估实践
故障分析 | TCP 缓存超负荷导致的 MySQL 连接中断
故障分析 | 一则 MySQL 从节点 hung 死问题分析
新特性解读 | MySQL 8.0 支持对单个数据库设置只读
技术分享 | MySQL 授权表运维注意事项
技术分享 | 如何通过 binlog 定位大事务？
新特性解读 | MySQL 8.3 可以打标签的 GTID
故障分析 | MySQL 执行 Online DDL 操作报错空间不足？
技术分享 | 如何缩短 MySQL 物理备份恢复时间？
✨ Github：https://github.com/actiontech/sqle
📚 文档：https://actiontech.github.io/sqle-docs/
💻 官网：https://opensource.actionsky.com/sqle/
👥 微信群：请添加小助手加入 ActionOpenSource
🔗 商业支持：https://www.actionsky.com/sqle
