乐趣区

关于mysql:MySQL-慢查询-值类型与字段类型不一致的隐式转换

jiezi

示例慢 SQL

SELECT * FROM task_engine_base_data WHERE outer_task_id = 1001871

场景上下文

  • 数据表构造现状

为缩小浏览烦扰，已去除与本案例无关的字段及索引定义 

CREATE TABLE `task_engine_base_data` (`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键 ID',
  `task_type_id` int(11) NOT NULL DEFAULT '0' COMMENT '工作类型 ID',
  `outer_task_id` varchar(64) NOT NULL DEFAULT ''COMMENT' 内部工作 ID',
  PRIMARY KEY (`id`),
  KEY `idx_outer_id_type_id` (`outer_task_id`,`task_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='子工作规定表';

起因剖析

慢 SQL 的 EXPLAIN

表中存在索引 idx_outer_id_type_id(outer_task_id,task_type_id)。

字段定义 outer_task_id varchar(64) NOT NULL DEFAULT ''。

字段类型为字符串，但查问条件的值为整型，导致 MySQL 隐式转换。

列值会被转换成浮点数类型，即 CAST(col_name AS DOUBLE) = XXX，对列值进行了计算， 无奈应用索引 。

解决方案

程序中强制转换值的类型，与数据库字段类型放弃完全一致，防止隐式转换。

SELECT * FROM task_engine_base_data WHERE outer_task_id = '1001871'

或者应用 CAST 转换查问值：

SELECT * FROM task_engine_base_data WHERE outer_task_id=CAST(1001871 AS CHAR)

优化后的 SQL

SELECT * FROM task_engine_base_data WHERE outer_task_id = '1001871'

再看优化后的 EXPLAIN

常识总结

两个须要比拟的值，根本类型不一样时，会产生转换。

整型与字符串的比拟，两个值都会被转换成浮点数后再比拟。

对索引列进行计算，会导致无奈应用索引。

参考资料

  • 《MySQL 5.7 Reference Manual》Type Conversion in Expression Evaluation

感谢您的浏览，感觉内容不错，点个赞吧 😆
原文地址: https://shockerli.net/post/mysql-slow-sql-implicit-type-conversion/

退出移动版