同时使用order by和limit有时候返回错误的结果集的奇怪问题

yj1804

查询语句和结果如下:
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

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

什么意思我不太明白

















星际之亡灵帝国 重活 流氓高手2

yj1804

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

这个有数据自己还不会调呀，先把limit的值设小一点。
如果还是没有，把条件一个个的拆了查。再没有就可能是select * 的问题。

yj1804

原帖由 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

* 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

只能是BUG了。

baijzjqiq

我也遇到了类似的问题，在搜索时看到了这个帖子。

我的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 编辑 ]

枫影谁用了

类似的BUG还是有的。