很奇怪的问题，explain 和 slowquery,谁在撒谎？

P-Linux

5.0下也有类似的问题

棉花糖ONE

我不知道这个是不是mysql的bug,你这个sql条件和order by的字段是一致的,所以只要走索引都是不需要排序的,不管是走index full scan 和index range scan都是不需要做排序的,正常应该是先定位出MeiShiID的条件,然后根据limit再去做stopkey,这样不应该做index full scan就不会有你那的问题了


explain select MeiShiID from fc_meishi_old where MeiShiID>12586 order by MeiShiID ASC LIMIT 0,1;


看看这个的执行计划

如果你的MeiShiID是连续的数字,建议你直接根据MeiShiID的值和后面的Limit来算出MeiShiID的上边界,语句转换成

explain select MeiShiID from fc_meishi_old where MeiShiID>12586 and MeiShID<=计算出来的值

[ 本帖最后由 棉花糖ONE 于 2011-3-17 19:13 编辑 ]

wanan_YLF

mysql> explain select MeiShiID from fc_meishi_old where MeiShiID>12586 order by MeiShiID ASC LIMIT 0,1;

+----+-------------+---------------+-------+---------------+---------+---------+------+-------+--------------------------+
| id | select_type | table         | type  | possible_keys | key     | key_len | ref  | rows  | Extra                    |
+----+-------------+---------------+-------+---------------+---------+---------+------+-------+--------------------------+
|  1 | SIMPLE      | fc_meishi_old | range | PRIMARY       | PRIMARY | 4       | NULL | 68904 | Using where; Using index |
+----+-------------+---------------+-------+---------------+---------+---------+------+-------+--------------------------+


我发现只要是order by ID （ID是主键）的这种情况下，查询都会很慢。。。

[ 本帖最后由 wanan_YLF 于 2011-3-18 10:41 编辑 ]

jinguanding

我发现只要是order by ID （ID是主键） 你要是ORDER BY 为主键，而WHERE部分不包含主键，很容易误走主键先排序

这是优化器的缺陷.....

但是你的这个SQL不一样，WHERE与ORDER BY部分为同一个字段，且是主键的一部分，还是自增字段，按优化器优先是选择 WHERE部分走，再加上LIMIT 1很容易拿到值


还有一个担心就是，你InnoDB引擎的表，可能出现统计信息不准确导致的，这个跑久了的系统，容易偶尔出现此问题

wanan_YLF

不好意思，忘了说，这个是MyISAM 引擎的表。我也觉得我这个SQL很诡异啊。

你要是ORDER BY 为主键，而WHERE部分不包含主键，很容易误走主键先排序    举下面的例子

SELECT * FROM TABLEA  WHERE catid IN (15,16,17,18,19,20) ORDER BY id DESC LIMIT 8

即使有复合索引catid（catid，id），引擎也是使用主键索引。
explain的结果是：type 是index  ，extra是using where

强制使用catid索引。
explain的结果是：type 是range  ，extra是using where，using filesort

即使使用复合索引，也只是使用了第一个字段。真不知道mysql为什么死命这样！


我只能修改为
SELECT * FROM TABLEA  WHERE  id  in  (SELECT id FROM TABLEA   WHERE  catid  IN (15,16,17,18,19,20))  order by  id  limit 8 ;

不知除此之外还有什么办法。
因为这个语句是为了得到最新的8条记录，只有通过自增id来得到

[ 本帖最后由 wanan_YLF 于 2011-3-18 11:24 编辑 ]

棉花糖ONE

SELECT * FROM TABLEA  WHERE  id  in  (SELECT id FROM TABLEA   WHERE  catid  IN (15,16,17,18,19,20))  order by  id  limit 8 ;

select * from A where id in (
select id tableA where catid=15 order by id limit 8
union all
select id tableA where catid=16 order by id limit 8
....
)  order by id limit 8

wanan_YLF

这个我也想过，但15,16,17,18,19,20是另外一个查询的结果，通过程序赋值进来的，遇到几十个数字的话，那就比较复杂了。

也想过新增一列可以起到ID作用的列。

而且有一点很奇怪，对于子查询，引擎选择使用的索引也是主键。该表有41万的数据。catid in （15,16,17,18,19,20）的结果是4514个。

mysql> explain SELECT *  TABLEA  WHERE  id  in  (SELECT id FROM   TABLEA  WHERE  catid  IN (15,16,17,18,19,20))  order by  id  limit 8 ;
+----+--------------------+--------+-----------------+--------------------------------+---------+---------+------+------+-------------+
| id | select_type        | table  | type            | possible_keys                  | key     | key_len | ref  | rows | Extra       |
+----+--------------------+--------+-----------------+--------------------------------+---------+---------+------+------+-------------+
|  1 | PRIMARY            | TABLEA | index           | NULL                           | PRIMARY | 3       | NULL |    8 | Using where |
|  2 | DEPENDENT SUBQUERY | TABLEA | unique_subquery | PRIMARY,listorder,catid,catid2 | PRIMARY | 3       | func |    1 | Using where |
+----+--------------------+--------+-----------------+--------------------------------+---------+---------+------+------+-------------+

这个子查询的rows最近搞得我是真不敢相信了。
我的疑惑是，子查询走主键索引真的比较快吗？还是又是引擎的错误判断。

[ 本帖最后由 wanan_YLF 于 2011-3-18 11:48 编辑 ]

jinguanding

这种情况不是误判，而是算法限制....

wanan_YLF

算法限制？怎么说？

wanan_YLF

了解了一下你们的水平和工作，真的是佩服你们，年复一年地坚持在这里，耐心得回答一些菜鸟问题。