Chinaunix

标题: 同时使用order by和limit有时候返回错误的结果集的奇怪问题 [打印本页]
作者: yj1804    时间: 2008-09-25 14:30
标题: 同时使用order by和limit有时候返回错误的结果集的奇怪问题
查询语句和结果如下:
mysql> SELECT * FROM ACE_URL_2008092417 WHERE CREATE_TIME >= 1222246800 AND CREATE_TIME <= 1222250399 AND IP_LOCAL = 3232249376 AND MAIN_URL LIKE 'http%' ORDER BY CREATE_TIME DESC limit 10000;
Empty set (0.00 sec)

mysql> select count(1) from ACE_URL_2008092417;
+----------+
| count(1) |
+----------+
| 20024977 |
+----------+
1 row in set (0.00 sec)

mysql> select count(1) from ACE_URL_2008092417 WHERE CREATE_TIME >= 1222246800 AND CREATE_TIME <= 1222250399 AND IP_LOCAL = 3232249376 AND MAIN_URL LIKE 'http%';
+----------+
| count(1) |
+----------+
|  2507612 |
+----------+
1 row in set (1 min 27.69 sec)

从上面可以看出, 第一个查询的结果集不应该为空.

mysql的版本为5.1.23-rc.
请问大家有遇到过类似的情况么? 如何解决

[ 本帖最后由 yj1804 于 2008-9-25 16:23 编辑 ]
作者: yj1804    时间: 2008-09-25 14:50
标题: 补充几个测试例子
mysql> show index from ACE_URL_2008092417;
+--------------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table              | Non_unique | Key_name           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| ACE_URL_2008092417 |          1 | INDX_CREATETIME    |            1 | CREATE_TIME | A         |        1014 |     NULL | NULL   | YES  | BTREE      |         |
| ACE_URL_2008092417 |          1 | INDX_URL           |            1 | MAIN_URL    | A         |     1001248 |       32 | NULL   | YES  | BTREE      |         |
| ACE_URL_2008092417 |          1 | INDX_SOURCEIP      |            1 | IP_LOCAL    | A         |           8 |     NULL | NULL   | YES  | BTREE      |         |
| ACE_URL_2008092417 |          1 | INDX_DESTINATIONIP |            1 | IP_WEB      | A         |      870651 |     NULL | NULL   | YES  | BTREE      |         |
| ACE_URL_2008092417 |          1 | INDX_DEVICE_IP     |            1 | DEVICE_IP   | A         |     1001248 |     NULL | NULL   | YES  | BTREE      |         |
+--------------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set (0.00 sec)
mysql> SELECT * FROM ACE_URL_2008092417 USE INDEX(INDX_SOURCEIP) WHERE CREATE_TIME >= 1222246800 AND CREATE_TIME <= 1222250399  AND MAIN_URL LIKE 'http%' ORDER BY CREATE_TIME DESC limit 1;
+------------+------------+-----------+--------------------------------------------------------------------+-----------------------------------+-------------+---------------+------------------------------------------------+--------+-----------+-------------+
| IP_LOCAL   | IP_WEB     | DEVICE_IP | URL                                                                | MAIN_URL                          | RETURN_CODE | TRANSFER_SIZE | SYS_INFO                                       | BRIDGE | DIRECTION | CREATE_TIME |
+------------+------------+-----------+--------------------------------------------------------------------+-----------------------------------+-------------+---------------+------------------------------------------------+--------+-----------+-------------+
| 3232249383 | 3230310904 | 641414459 | http://tp/iof.gu/yscfso/njpys/zj.mvfr/basvfxp.tdh/hxgggnpdzk.nkrqd | http://om.pk/iiz/yu.dvrbgzli/aojh |         200 |          3206 | vccotajwjuaibzqkfebkfmxyckitanfxsjnllxjutlcwkt |      1 | out       |  1222249472 |
+------------+------------+-----------+--------------------------------------------------------------------+-----------------------------------+-------------+---------------+------------------------------------------------+--------+-----------+-------------+
1 row in set (1 min 56.49 sec)

强制使用IP_LOCAL的索引以后,可以查询出结果
作者: ZHOU830210    时间: 2008-09-25 14:54
什么意思我不太明白

















星际之亡灵帝国 重活 流氓高手2
作者: yj1804    时间: 2008-09-25 14:58
标题: 回复 #3 ZHOU830210 的帖子
mysql> select count(1) from ACE_URL_2008092417 WHERE CREATE_TIME >= 1222246800 AND CREATE_TIME <= 1222250399 AND IP_LOCAL = 3232249376 AND MAIN_URL LIKE 'http%';
这个查询的结果显示符合条件的有2507612条记录
但是下面这个查询的结果集确是空的. 下面的查询和上面的查询条件完全一样, 正确结果应该是可以查询出10000条记录.
mysql> SELECT * FROM ACE_URL_2008092417 WHERE CREATE_TIME >= 1222246800 AND CREATE_TIME <= 1222250399 AND IP_LOCAL = 3232249376 AND MAIN_URL LIKE 'http%' ORDER BY CREATE_TIME DESC limit 10000;
Empty set (0.00 sec)
作者: mokai    时间: 2008-09-25 16:09
这个有数据自己还不会调呀，先把limit的值设小一点。
如果还是没有，把条件一个个的拆了查。再没有就可能是select * 的问题。
作者: yj1804    时间: 2008-09-25 16:13
原帖由 mokai 于 2008-9-25 16:09 发表
这个有数据自己还不会调呀，先把limit的值设小一点。
如果还是没有，把条件一个个的拆了查。再没有就可能是select * 的问题。


呵呵,如果你仔细看了我贴的sql就不会这么说了.
2个查询的条件都是一样的, 区别是一个加了order by和limit, 一个没有加而已, 结果就导致了一个有结果,一个没有结果.
limit的值设置成1也是取不出数据的.

后续我又做了几个测试, 初步推断是mysql自身存在问题, 在数据集比较大, 并且mysql自身选择的索引列过滤出来的结果集仍然非常大(通过explain计划看到的rows比较大,在几十万记录的级别时候),  基本上一直出这个问题,  数据量小的时候没有出现过这样的问题

另外, 在mysql的bug列表里面有看到这个bug, 有没有人可以看一下是否和我遇到的问题相关?
http://bugs.mysql.com/bug.php?id=32933

[ 本帖最后由 yj1804 于 2008-9-25 16:21 编辑 ]
作者: yj1804    时间: 2008-09-25 16:41
* When using indexed ORDER BY sorting, incorrect query results
       could be produced if the optimizer switched from a covering
       index to a non-covering index.
       (Bug#37548: http://bugs.mysql.com/37548)

在mysql 5.1.28rc版的发布说明里面列出了这个bug. 不知道和我的问题没有关系...
作者: yueliangdao0608    时间: 2008-09-25 18:49
只能是BUG了。
作者: baijzjqiq    时间: 2009-02-10 10:46
我也遇到了类似的问题，在搜索时看到了这个帖子。

我的SQL很简单
我想从一个表中检索所有标题含有“中国”的数据，将它们按id排序，取前5条，所以我写了以下语句
SQL语句1：

select fi_id, fi_url, fi_title
from InfoTable
where fi_title like '%中国%'
order by fi_id desc
limit 5

当我只使用order by时，能够返回13条结果
只使用limit时，能够返回其中的5条结果
但是当我同时使用order by和limit时，却返回0条结果

同时我们还发现，这种情况并不是在所有检索词上都会出现，如把检索词由“中国”换为“足球”等就不会出现这种现象。

在和同事的讨论中，有人提出了以下方法
SQL语句2：

select * from
(select fi_id, fi_url, fi_title
from InfoTable
where fi_title like '%中国%'
order by fi_id desc ) a
limit 5

语句2能够正确的得到5条结果

这给了我们一些启发：语句1和语句2的执行并不相同，所以语句1很可能与我们最初设想不符，所以，我们对语句1进行了以下修改
limit 5 ---> limit 50000

得到SQL语句3：

select fi_id, fi_url, fi_title
from InfoTable
where fi_title like '%中国%'
order by fi_id desc
limit 50000

此SQL语句能够正确得到13条结果

由此我们推测：在同时使用order by和limit时，MySQL进行了某些优化，将语句执行逻辑从"where——order by——limit"变成了"order by——limit——where"，导致在某些情况下，同时使用order by和limit会导致查无结果（或结果数量不足）。具体出现问题与否是与表中数据有关的。

这仅仅是我们的推测，希望抛砖引玉，大家多多讨论，得到最终正确的结论。

[ 本帖最后由 baijzjqiq 于 2009-2-10 13:21 编辑 ]
作者: 枫影谁用了    时间: 2009-02-10 10:52
类似的BUG还是有的。
作者: 枫影谁用了    时间: 2009-02-10 10:53
我也说个例子

  1. mysql> select count(*) from t_prehandle_qeesoo_visit;  
  2. +----------+
  3. | count(*) |
  4. +----------+
  5. |       31 |
  6. +----------+
  7. 1 row in set (0.00 sec)

  9. mysql> truncate t_prehandle_qeesoo_visit;
  10. Query OK, 0 rows affected (0.05 sec)

  12. mysql> select count(*) from t_prehandle_qeesoo_visit;
  13. +----------+
  14. | count(*) |
  15. +----------+
  16. |       31 |
  17. +----------+
  18. 1 row in set (0.00 sec)

  20. mysql>
复制代码
作者: 枫影谁用了    时间: 2009-02-10 10:56
这个版本已经是5.1.30的咯。

好像朋友帮提过bug咯



欢迎光临 Chinaunix (http://bbs.chinaunix.net/) Powered by Discuz! X3.2