mysql 查询结果异常分析-阿里云开发者社区

开发者社区> zysql> 正文

mysql 查询结果异常分析

简介: --- title: MySQL · mysql · mysql 查询结果异常分析 author: 张远 --- # 现象 查询条件类型变化后,查询出了不正确的结果。 ``` create table t1(id int primary key, a varchar(50) DEFAULT NULL, key idx_a(a)) engine=innodb; sho
+关注继续查看

title: MySQL · mysql · mysql 查询结果异常分析

author: 张远

现象

查询条件类型变化后,查询出了不正确的结果。

create table t1(id int primary key,  a  varchar(50) DEFAULT NULL, key idx_a(a)) engine=innodb;

show create table t1;
insert into t1 values(1,'6036000240201612190005565273');
insert into t1 values(2,'6036000240201611150005564192');

select * from t1 where a='6036000240201612190005565273';
+----+------------------------------+
| id | a                            |
+----+------------------------------+
|  1 | 6036000240201612190005565273 |
+----+------------------------------+

//多了一行不一致的数据
select * from t1 where a=6036000240201612190005565273;
+----+------------------------------+
| id | a                            |
+----+------------------------------+
|  2 | 6036000240201611150005564192 |
|  1 | 6036000240201612190005565273 |
+----+------------------------------+

分析

索引问题

首先我们要确定数据是否存在问题,我们注意到字段a上有索引idx_a,而且两个查询都走了此索引。

explain select * from t1 where a='6036000240201612190005565273';
+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | t1    | ref  | idx_a         | idx_a | 153     | const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+

explain select * from t1 where a=6036000240201612190005565273;
+----+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key   | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+
|  1 | SIMPLE      | t1    | index | idx_a         | idx_a | 153     | NULL |    2 | Using where; Using index |
+----+-------------+-------+-------+---------------+-------+---------+------+------+--------------------------+

一种可能的情况,由于BUG导致二级索引与主键不一致,此种情况我们可通过重建索引修复。

于是删除索引idx_a,再来通过主键索引查询看看

alter table t1 drop key idx_a;


explain select * from t1 where a=6036000240201612190005565273;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

 explain select * from t1 where a='6036000240201612190005565273';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

select * from t1 where a=6036000240201612190005565273;
+----+------------------------------+
| id | a                            |
+----+------------------------------+
|  1 | 6036000240201612190005565273 |
|  2 | 6036000240201611150005564192 |
+----+------------------------------+
2 rows in set (0.00 sec)

select * from t1 where a='6036000240201612190005565273';
+----+------------------------------+
| id | a                            |
+----+------------------------------+
|  1 | 6036000240201612190005565273 |
+----+------------------------------+
1 row in set (0.00 sec)

然而,结果与删除索引前一致。排除了索引的问题,我们只能从源码中来寻找答案了。

查源码

问题出在where条件上,我可以把断点放在条件检查的总入口evaluate_join_record这里,然后一步步跟进下去。

  • 先看条件 a=6036000240201612190005565273

根据比较表达式参数的类型来决定比较时内部使用的比较函数,a:STRING_RESUL b: DECIMAL_RESULT最后得到按REAL_RESULT类型进行比较

Item_result item_cmp_type(Item_result a,Item_result b)
{
  if (a == STRING_RESULT && b == STRING_RESULT)
    return STRING_RESULT;
  if (a == INT_RESULT && b == INT_RESULT)
    return INT_RESULT;
  else if (a == ROW_RESULT || b == ROW_RESULT)
    return ROW_RESULT;
  if ((a == INT_RESULT || a == DECIMAL_RESULT) &&
      (b == INT_RESULT || b == DECIMAL_RESULT))
    return DECIMAL_RESULT;
  return REAL_RESULT;
}

这里REAL_RESULT类型比较对应的比较函数为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;
}

compare_real 会把a值转化为double类型再比较((*a)->val_real()),最终得到的转化函数为my_strtod
由于精度问题最后字符串'6036000240201612190005565273'会转化为6.0360002402016117e+27,会损失精度。
同时对于比较表达式的右值数字6036000240201612190005565273在内部表示为Item_decimal,在compare_real时也会通过(*b)->val_real(),调用Item_decimal::val_real,最终也是调用my_strtod,转化后的值也为6.0360002402016117e+27

而对于表中另外一个值'6036000240201611150005564192'通过上述转化也6.0360002402016117e+27

因此对于条件 a=6036000240201612190005565273最后返回了两行。

  • 再看条件 a='6036000240201612190005565273'

这个两边都是Field_varstring类型,最终使用的比较函数是Arg_comparator::compare_string。此函数比较时字符串精度不会丢失,比较操作是精确的,因此最终只返回了一行。

结论

最终问题的原因是比较时做类型转化时丢失了精度,导致比较出错。对于字符串转double的情况下,只保留了16位小数。
可以做个实验

insert into t1 values(3,'6036000240201611');
insert into t1 values(4,'60360002402016111');

select * from t1 where a=60360002402016111;
+----+-------------------+
| id | a                 |
+----+-------------------+
|  4 | 60360002402016111 |
+----+-------------------+

elect * from t1 where a=6036000240201611;
+----+------------------+
| id | a                |
+----+------------------+
|  3 | 6036000240201611 |
+----+------------------+
1 row in set (0.01 sec)

//小数位16位,出现异常
select * from t1 where a=60360002402016112;
+----+-------------------+
| id | a                 |
+----+-------------------+
|  4 | 60360002402016111 |
+----+-------------------+
1 row in set (0.01 sec)

//小数位15位,没有问题
mysql>  select * from t1 where a=6036000240201612;
Empty set (0.00 sec)

实际上mysql 对于float,double小数的处理是不精确的,使用时应格外注意。官方也有很有意思的例子,有兴趣的可以看看。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
魏红斌冬季实战营第一期:玩转云服务器(有干货)
本文均为本人实操整理,请勿搬运,学习交流可以随时评论---魏红斌
13 0
【Typescript入门手册】类型进阶
【Typescript入门手册】类型进阶
8 0
面试题:说一说es6新增方法
面试题:说一说es6新增方法
13 0
祝贺!我的同事李飞飞当选ACM Fellow、IEEE Fellow
因在数据库查询处理和优化以及云数据库系统方面所做出的卓越贡献而入选
7 0
数据库中间件 MyCAT源码分析 —— PreparedStatement 重新入门
数据库中间件 MyCAT源码分析 —— PreparedStatement 重新入门
8 0
关于“冬季实战营第一期”的学习报告
冬奥云小宝带你玩转五期实战营,初识上云基础,非常适合新手上路,上云必备环境准备让您轻松上云。
58 0
云起冬季实战营第一期期学习报告——使用PolarDB和ECS搭建门户网站
云起冬季实战营第一期期学习报告——使用PolarDB和ECS搭建门户网站
6 0
完美避坑!记一次Elasticsearch集群迁移架构实战
Elastic自身设计了集群分片的负载平衡机制,当有新数据节点加入集群或者离开集群,集群会自动平衡分片的负载分布。
6 0
冬季实战营第一期:从零到一上手玩转云服务器实验报告
搭建LAMP、部署MySQL数据库、使用PolarDB和ECS搭建门户网站操作手册
7 0
冬季实战营第一期:从零到一上手玩转云服务器场景体验报告之使用 PolarDB 和 ECS 搭建门户网站
冬季实战营第一期:从零到一上手玩转云服务器场景体验报告之使用 PolarDB 和 ECS 搭建门户网站
15 0
+关注
zysql
张远 MySQL/MyRocks
34
文章
10
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载