关于order by limit主键优化问题--大神请进

[IceArmour]

建了一张表，主键为自增id，name可以为空，插入6条数据

create table t(id int unsigned not null primary key auto_increment,name varchar(20));

insert into t select 0,'1';
insert into t select 0,null;
insert into t select 0,'2';
insert into t select 0,'3';
insert into t select 0,null;
insert into t select 0,'4';

第一行查询全表扫描，这没有问题

加了order by id limit 3之后居然只扫描了3行


我的问题是，扫描的时候limit 3，而这里只有2条null应该也是从6开始扫描到1才停止呀，mysql究竟是怎么对这条SQL进行优化的？求大神解答

[bonlog]

EXPLAIN并不是优化啊。 执行顺序：where,group,order,limit 1。你告诉他，name=null 他找出2条， 2。order 一下， 他也是2条， 3。limit 他是3知，虽然有空行。

[petrie]

顶.. 没人答 分就给我哈

[IceArmour]

引用 1 楼 bonlog 的回复:

EXPLAIN并不是优化啊。
执行顺序：where,group,order,limit
1。你告诉他，name=null
他找出2条，
2。order 一下，
他也是2条，
3。limit
他是3知，虽然有空行。

explain分析执行计划，你给的执行顺序只是逻辑查询的，理论上是这么回事。物理查询会根据索引进行优化，你看这条还是全表扫描

这几条SQL都能查出这两条null
如果是where id = 1这种的很好理解，这个order by 索引 limit n怎么优化的。。。不解

[rucypli]

本来这个就是个预估值

[fxxyaya]

这片帖子不错http://isky000.com/database/mysql_order_by_implement，应该对lz有帮助

[IceArmour]

引用 7 楼 WWWWA 的回复:

rows 字段显示了MySQL认为在查询中应该检索的记录数。 请注意，EXPLAIN 结果中的 rows 字段的值也是MySQL的连接优化程序大致猜测的 估计要看MYSQL的源码才能知道原因

恩，我用几万条数据测了一下，发现explain还是这个结果，只估计了几行~

[petrie]

楼主 好人 结贴给分吧

[ACMAIN_CHM]

limit 3 本身就是限制MYSQL仅取前3条啊。

[IceArmour]

引用 5 楼 ACMAIN_CHM 的回复:

limit 3 本身就是限制MYSQL仅取前3条啊。

是呀，查询结果预估3条记录，结果是2条null，我很不解究竟是怎么走主键把2，3，4过滤掉的。。。求大神赐教

[WWWWA]

rows 字段显示了MySQL认为在查询中应该检索的记录数。 请注意，EXPLAIN 结果中的 rows 字段的值也是MySQL的连接优化程序大致猜测的 估计要看MYSQL的源码才能知道原因

[ACMAIN_CHM]

引用 6 楼 IceArmour 的回复:

引用 5 楼 ACMAIN_CHM 的回复:limit 3 本身就是限制MYSQL仅取前3条啊。 是呀，查询结果预估3条记录，结果是2条null，我很不解究竟是怎么走主键把2，3，4过滤掉的。。。求大神赐教

楼主的疑问到底是什么？ 是返回结果不对？ 还是对EXPLAIN中这个 ROW=3 有疑问？

[IceArmour]

引用 11 楼 ACMAIN_CHM 的回复:

引用 6 楼 IceArmour 的回复:引用 5 楼 ACMAIN_CHM 的回复:limit 3

本身就是限制MYSQL仅取前3条啊。
是呀，查询结果预估3条记录，结果是2条null，我很不解究竟是怎么走主键把2，3，4过滤掉的。。。求大神赐教

楼主的疑问到底是什么？ 是返回结果不对？ 还是对EXPLAIN中这个 ROW=3 有疑问？

我的疑问是
这里SQL语句查询出的结果是2条null，分析执行计划预估查询6条


而下面这条在后面加上order by id desc之后查询结果是2条null，预估却为3条，是怎么仅仅分析3条数据就得到2条null的？

我觉得order by id desc limit 3的话应该会从后面倒着查询到匹配行后才停止检索

[IceArmour]

引用 11 楼 ACMAIN_CHM 的回复:

引用 6 楼 IceArmour 的回复:引用 5 楼 ACMAIN_CHM 的回复:limit 3 本身就是限制MYSQL仅取前3条啊。 是呀，查询结果预估3条记录，结果是2条null，我很不解究竟是怎么走主键把2，3，4过滤掉的。。。求大神赐教 楼主的疑问到底是什么？ 是返回结果不对？ 还是对EXPLAIN中这个 ROW=3 有疑问？

小弟不太明白，还望版主大人解答

[fxxyaya]

楼主用的是哪个版本的mysql？我做这个测试，explain加上order by之后显示没有用到key

[ACMAIN_CHM]

楼主请贴出你的确 show create table t; show index from t;

[IceArmour]

引用 15 楼 ACMAIN_CHM 的回复:

楼主请贴出你的确 show create table t; show index from t;

看一楼 create table t(id int unsigned not null primary key auto_increment,name varchar(20));

[IceArmour]

引用 14 楼 fxxyaya 的回复:

楼主用的是哪个版本的mysql？我做这个测试，explain加上order by之后显示没有用到key

mysql版本5.5.21

[ACMAIN_CHM]

引用 16 楼 IceArmour 的回复:

引用 15 楼 ACMAIN_CHM 的回复:楼主请贴出你的确 show create table t; show index from t; 看一楼 create table t(id int unsigned not null primary key auto_increment,name varchar(20));

贴个东西这么难吗？ show create table , show index 会多出很多有用信息啊。 毕竟如果想找到原因不可能让大家来猜测，而是提供必要的信息以供分析。

[IceArmour]

引用 18 楼 ACMAIN_CHM 的回复:

引用 16 楼 IceArmour 的回复:引用 15 楼 ACMAIN_CHM 的回复:楼主请贴出你的确

show create table t;
show index from t;
看一楼
create table t(id int unsigned not null primary key auto_increment,name varchar(20)……

斑竹大人我错了。。。