mysql select自变量的一个案例

案例：

  表test

 

 test数据

 

-- sql 1 没有order by,  返回2行, @rownum=2. 
set @rownum:=0;
select a,b,@rownum,@rownum:=@rownum+1 as cnt,@rownum
from test
where @rownum<=1;

 

-- sql2 有order by b(无索引) , 返回所有行
#观察 where 执行了 5 次，每次@rownum=0,然后order by，然后实现@rownum自增

#推测order by 将select 的列一分为二，先只select b列，然后排序，然后 select @rownum列。https://blog.csdn.net/qq_42604176/article/details/115409970，可能内部用的是rowid排序。

select b,@rownum:=@rownum+1 as cnt,@n
from test,(select @rownum:=0, @n :=1,@p :=0) t
where @rownum <=1 and @n :=@rownum +1 and @p := @p + 1
order by b;

 

-- sql3,order by b (有索引),返回两行

create index ind_b on test(b);
-- explain 
select b,@rownum:=@rownum+1 as cnt,@n
from test,(select @rownum:=0, @n :=1,@p :=0) t
where @rownum <=1 and @n :=@rownum +1 and @p := @p + 1
order by b;

总结：按索引去select的时候是一行行包括@rownum列去读取的，而using filesort排序的时候，不是拿整行的列然后去order by , 由于不懂mysql的后台执行步骤，只能推测sql2的奇怪现象，是由于@rownum列的值是在order by 实现之后赋值的，即rowid排好序后回表拿其他列的时候给@rownum赋值的。理论依据是order by的排序逻辑(全字段排序和rowid排序)。还有order by影响select性能，order by字段最好是索引列、联合索引列