mysql水平拓展的瓶颈(MySQL令人大跌眼镜的隐式转换)
mysql水平拓展的瓶颈
MySQL令人大跌眼镜的隐式转换目录
- MySQL的隐式转换
- 一、问题描述
- 二、源码解释
- 三、结论
一、问题描述
show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` varchar(255) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) select * from t1; +--------------------+ | id | +--------------------+ | 204027026112927605 | | 204027026112927603 | | 2040270261129276 | | 2040270261129275 | | 100 | | 101 | +--------------------+ 6 rows in set (0.00 sec)
奇怪的现象:
select * from t1 where id=204027026112927603; +--------------------+ | id | +--------------------+ | 204027026112927605 | | 204027026112927603 | +--------------------+ 2 rows in set (0.00 sec)
什么鬼,明明查的是204027026112927603,为什么204027026112927605也出来了
二、源码解释
堆栈调用关系如下所示:
其中JOIN::exec()是执行的入口,Arg_comparator::compare_real()是进行等值判断的函数,其定义如下
int Arg_comparator::compare_real() { /* Fix yet another manifestation of Bug#2338. 'Volatile' will instruct gcc to flush double values out of 80-bit Intel FPU registers before performing the comparison. */ volatile double val1, val2; val1= (*a)->val_real(); if (!(*a)->null_value) { val2= (*b)->val_real(); if (!(*b)->null_value) { if (set_null) owner->null_value= 0; if (val1 < val2) return -1; if (val1 == val2) return 0; return 1; } } if (set_null) owner->null_value= 1; return -1; }
比较步骤如下图所示,逐行读取t1表的id列放入val1,而常量204027026112927603存在于cache中,类型为double类型(2.0402702611292762E+17),所以到这里传值给val2后val2=2.0402702611292762E+17。
当扫描到第一行时,204027026112927605转成doule的值为2.0402702611292762e17,等式成立,判定为符合条件的行,继续往下扫描,同理204027026112927603也同样符合
如何检测string类型的数字转成doule类型是否溢出呢?这里经过测试,当数字超过16位以后,转成double类型就已经不准确了,例如20402702611292711会表示成20402702611292712(如图中val1)
MySQL string转成double的定义函数如下:
{ char buf[DTOA_BUFF_SIZE]; double res; DBUG_ASSERT(end != NULL && ((str != NULL && *end != NULL) || (str == NULL && *end == NULL)) && error != NULL); res= my_strtod_int(str, end, error, buf, sizeof(buf)); return (*error == 0) ? res : (res < 0 ? -DBL_MAX : DBL_MAX); }
真正转换函数my_strtod_int位置在dtoa.c(太复杂了,简单贴个注释吧)
/* strtod for IEEE--arithmetic machines. This strtod returns a nearest machine number to the input decimal string (or sets errno to EOVERFLOW). Ties are broken by the IEEE round-even rule. Inspired loosely by William D. Clinger's paper "How to Read Floating Point Numbers Accurately" [Proc. ACM SIGPLAN '90, pp. 92-101]. Modifications: 1. We only require IEEE (not IEEE double-extended). 2. We get by with floating-point arithmetic in a case that Clinger missed -- when we're computing d * 10^n for a small integer d and the integer n is not too much larger than 22 (the maximum integer k for which we can represent 10^k exactly), we may be able to compute (d*10^k) * 10^(e-k) with just one roundoff. 3. Rather than a bit-at-a-time adjustment of the binary result in the hard case, we use floating-point arithmetic to determine the adjustment to within one bit; only in really hard cases do we need to compute a second residual. 4. Because of 3., we don't need a large table of powers of 10 for ten-to-e (just some small tables, e.g. of 10^k for 0 <= k <= 22). */
既然是这样,我们测试下没有溢出的案例
select * from t1 where id=2040270261129276; +------------------+ | id | +------------------+ | 2040270261129276 | +------------------+ 1 row in set (0.00 sec) select * from t1 where id=101; +------+ | id | +------+ | 101 | +------+ 1 row in set (0.00 sec)
结果符合预期,而在本例中,正确的写法应当是
select * from t1 where id='204027026112927603'; +--------------------+ | id | +--------------------+ | 204027026112927603 | +--------------------+ 1 row in set (0.01 sec)
三、结论
避免发生隐式类型转换,隐式转换的类型主要有字段类型不一致、in参数包含多个类型、字符集类型或校对规则不一致等
隐式类型转换可能导致无法使用索引、查询结果不准确等,因此在使用时必须仔细甄别
数字类型的建议在字段定义时就定义为int或者bigint,表关联时关联字段必须保持类型、字符集、校对规则都一致
最后贴一下官网对于隐式类型转换的说明吧
1、If one or both arguments are NULL, the result of the comparison is NULL, except for the NULL-safe
<=> equality comparison operator. For NULL <=> NULL, the result is true. No conversion is needed.
2、If both arguments in a comparison operation are strings, they are compared as strings.
3、If both arguments are integers, they are compared as integers.
4、Hexadecimal values are treated as binary strings if not compared to a number.
5、If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a
constant, the constant is converted to a timestamp before the comparison is performed. This is
done to be more ODBC-friendly. This is not done for the arguments to IN(). To be safe, always
use complete datetime, date, or time strings when doing comparisons. For example, to achieve best
results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to
the desired data type.
A single-row subquery from a table or tables is not considered a constant. For example, if a subquery
returns an integer to be compared to a DATETIME value, the comparison is done as two integers.
The integer is not converted to a temporal value. To compare the operands as DATETIME values,
use CAST() to explicitly convert the subquery value to DATETIME.
6、If one of the arguments is a decimal value, comparison depends on the other argument. The
arguments are compared as decimal values if the other argument is a decimal or integer value, or as
floating-point values if the other argument is a floating-point value.
7、In all other cases, the arguments are compared as floating-point (real) numbers.
以上就是MySQL令人大跌眼镜的隐式转换的详细内容,更多关于MySQL的隐式转换的资料请关注开心学习网其它相关文章!,希望大家以后多多支持开心学习网!
- mysql中mergeinto的用法(MySQL中exists、in及any的基本用法)
- mysql完整整理(Mysql隔离性之Read View的用法说明)
- mysql触发器怎么添加(MySQL触发器的使用场景及方法实例)
- mysql数据类型图解(MySQL数据库主从技术GTID大揭秘)
- mysql 临时表
- 如何正常查看mysql存储数据文件(Mysql文件存储图文详解)
- mysql主从复制忽略问题(MySQL复制问题的三个参数分析)
- mysql缓冲池(详解MySQL中的缓冲池buffer pool)
- mysql为什么优选innodb(如何区分MySQL的innodb_flush_log_at_trx_commit和sync_binlog)
- 执行mysqldump命令后数据库无反应(关于xampp启动不了mysql数据库的解决方法)
- mysql怎么和sqlyog连接(Mysql桌面工具之SQLyog资源及激活使用方法告别黑白命令行)
- mysql的存储方法(MySQL中的binary类型使用操作)
- mysql详细学习笔记(Mysql常用命令 详细整理版)
- mysql主从复制配置(Mysql实现主从配置和多主多从配置)
- mysql的连接代码(MySQL 连接查询的原理和应用)
- mysqlint后面的4是什么意思(MySQL中intn后面的n到底代表的是什么意思)
- 四月新番CP人气榜公布,《剃须》两度上榜,沙优不是女朋友(四月新番CP人气榜公布)
- 2019年外媒秋季新番动画角色CP排行榜,桐人和爱丽丝落榜(2019年外媒秋季新番动画角色CP排行榜)
- 新一小兰领衔 盘点动漫中的那些 远距离恋爱情侣(盘点动漫中的那些)
- 大事件 合肥四中火了(大事件合肥四中火了)
- 翼龙贷组织出借人调研 感受鄱阳 借 来的致富路(翼龙贷组织出借人调研)
- 2023新国风戏曲教育寒假集训班汇报演出《戏娃闹元宵》图文报道(2023新国风戏曲教育寒假集训班汇报演出戏娃闹元宵图文报道)
热门推荐
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9