mysql 隐式转换丢失精度隐式转换导致索引失效

转载

云端行者 2024-06-09 07:56:09

文章标签 mysql 隐式转换丢失精度类型转换字符串浮点数 文章分类 MySQL 数据库

一、现象

1.1、表结构（数据库tidb）

CREATE TABLE `daily_customer_warn_data` (
  `id` bigint(15) NOT NULL AUTO_INCREMENT,
  `biz_uid` bigint(20) NOT NULL COMMENT '商业uid',
  `zz_uid` bigint(20) NOT NULL COMMENT '转转uid',
  `warn_type` int(10) NOT NULL DEFAULT '0' COMMENT '',
  `dt` varchar(10) NOT NULL COMMENT '数据归属日期，yyyy-MM-dd'
      
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_dt_pv1_pvr_keyword` (`dt`,`biz_uid`,`warn_type`)
)  ；

1.2、SQL查询

mysql> select count(1) from daily_customer_warn_data where dt=date_sub(‘2021-04-30’,interval 8 day);
±---------+
| count(1) |
±---------+
| 39232 |
±---------+
1 row in set (15.16 sec)

没什么没有走uniq_dt_pv1_pvr_keyword来查询呢？

二、分析：

2.1、SQL的执行计划

mysql> explain
-> select
-> count(1)
-> from daily_customer_warn_data where dt=date_sub(‘2021-04-30’,interval 8 day);
±-----------------------±------------±-----±--------------------------------------------------------------------+
| id | count | task | operator info |
±-----------------------±------------±-----±--------------------------------------------------------------------+
| StreamAgg_10 | 1.00 | root | funcs:count(1) |
| └─Selection_14 | 13893928.80 | root | eq(cast(tdb_adstat.daily_customer_warn_data.dt), 2021-04-22) |
| └─TableReader_16 | 17367411.00 | root | data:TableScan_15 |
| └─TableScan_15 | 17367411.00 | cop | table:daily_customer_warn_data, range:[-inf,+inf], keep order:false |

2.2、原因：

eq(cast(tdb_adstat.daily_customer_warn_data.dt), 2021-04-22)，cast(tdb_adstat.daily_customer_warn_data.dt) 是将dt转换成时间类型，这样索引就失效了

2.3、tidb文档：

值得注意的是，TiDB 目前只支持比较符一端是列，另一端是常量，或可以计算成某一常量的情况，类似 year(birth_day) < 1992 的查询条件是不能利用索引的。还要注意应尽可能使用同一类型进行比较，以避免引入额外的 cast 操作而导致不能利用索引，如 user_id = 123456，如果 user_id 是字符串，需要将 123456 也写成字符串常量的形式。
4、优化：

explain
select
count(1)
from daily_customer_warn_data   where dt=date_sub('2021-04-30',interval 8 day);

±-----------------------±------------±-----±--------------------------------------------------------------------+
| id | count | task | operator info |
±-----------------------±------------±-----±--------------------------------------------------------------------+
| StreamAgg_10 | 1.00 | root | funcs:count(1) |
| └─Selection_14 | 13893928.80 | root | eq(cast(tdb_adstat.daily_customer_warn_data.dt), 2021-04-22) |
| └─TableReader_16 | 17367411.00 | root | data:TableScan_15 |
| └─TableScan_15 | 17367411.00 | cop | table:daily_customer_warn_data, range:[-inf,+inf], keep order:false |

三、思考

3.1 什么是隐式转换

当运算符与不同类型的操作数一起使用时，将进行类型转换以使操作数兼容。某些转换是隐式发生的。例如，MySQL会根据需要自动将字符串转换为数字，反之亦然。例如

mysql> SELECT 1+'1';
+-------+
| 1+'1' |
+-------+
|     2 |
+-------+

SELECT CONCAT(2,' test');
+-------------------+
| CONCAT(2,' test') |
+-------------------+
| 2 test            |
+-------------------+

3.2 产生的条件

两个参数至少有一个是 NULL 时，比较的结果也是 NULL，例外是使用 <=> 对两个 NULL 做比较时会返回1，这两种情况都不需要做类型转换
两个参数都是字符串，会按照字符串来比较，不做类型转换
两个参数都是整数，按照整数来比较，不做类型转换
十六进制的值和非数字做比较时，会被当做二进制串
有一个参数是 TIMESTAMP 或DATETIME，并且另外一个参数是常量，常量会被转换为 timestamp 有一个参数是 decimal 类型，
如果另外一个参数是decimal 或者整数，会将整数转换为 decimal 后进行比较，如果另外一个参数是浮点数，则会把 decimal
转换为浮点数进行比较所有其他情况下，两个参数都会被转换为浮点数再进行比较