高性能MySQL 248页

关于书中 MySQL自定义变量确定取值顺序的一些实验和猜测.
并不能保证正确.

实验数据,test表 a是主键,b字段没有任何索引.
CREATE TABLE test (
a int(11) NOT NULL,
b int(11) DEFAULT NULL,
PRIMARY KEY (a)
) ENGINE=InnoDB;

INSERT INTO test VALUES (1,100),(2,20),(3,5),(4,40),(5,50);
commit;

第一个例子
查看如下SQL,按道理应该只有一条记录,但是却返回了两条记录.
set @rownum:=0;
select a,b,@rownum:=@rownum+1 as cnt
from test
where @rownum<=1;

这是因为自定义变量在SQL执行的不同阶段被查询或运算.
以上面的SQL为例
第一步先获取一条数据,此时@rownum为0,通过了where条件,在select语句自增后等于1
第二步获取第二条数据,此时@rownum为1,通过了where条件,在select语句自增后等于2
第三步获取数据,由于@rownum等于2,不能通过where条件,SQL结束.

第二个例子
如果上面的SQL增加排序,居然返回了全部的数据
set @rownum:=0;
select a,b,@rownum:=@rownum+1 as cnt
from test
where @rownum<=1
order by b;

它的执行计划如下

这是因为order by引入了文件排序,而where条件是在文件排序操作之前取值的.
也就是说,
先执行where条件,这时@rownum为0,所有的记录都符合条件.
然后排序,并执行select语句对@rownum自增.

第三个例子
有意思的是,如果这时对b字段创建一个索引,则返回的结果截然不同.

此时的执行计划

由于b字段有索引,
先根据索引获取第一条记录,此时@rownum为0,通过where条件,然后在select语句对@rownum自增,
然后根据索引获取第二条数据,此时@rownum为1,通过where条件,然后在select语句自增,这时@rownum已经为2
由于@rownum已经为2,不能通过where条件,SQL结束.

第四个例子
删除之前例子对字段b创建的索引
mysql> drop index inx_1 on test;

查看如下SQL
set @rownum:=0;
select a,b,@rownum as cnt
from test
where (@rownum:=@rownum+1)<=2;

这个结果应该是符合预期的
因为自增@rownum和where条件判断都在SQL执行的同一阶段.

第五个例子
set @rownum:=0;
select a,b,@rownum as cnt
from test
where (@rownum:=@rownum+1)<=1 order by b desc;

它的执行计划,又见文件排序.

首先,它获取第一条记录,自增@rownum然后判断where条件,a=1的记录通过.之后所有的记录都不能通过where条件.
虽然后续的记录没有通过,但是@rownum也会自增.转入order by 阶段之前,@rownum已经等于5了.
然后进入order by 文件排序阶段
最后进入select语句,显示@rownum为5.

在where中不过滤的结果是这样的。

如果再次创建字段b的索引,由于没有文件排序的过程,where条件之后,直接进入select语句阶段,所以结果如下

第六个例子
再次删除字段b上的索引.查看如下SQL语句
drop index inx_1 on test;
set @rownum:=0;
select a,b,@rownum as cnt
from test
where @rownum<=1
order by b desc,least(0,@rownum:=@rownum+1);

这个结果应该不符合预期
先获取一个记录,通过where条件(@rownum=0)，进入order by 语句?
再获取一个记录,通过where条件(@rownum=1)，进入order by 语句?
然后不能通过where条件,SQL结束?

不确定.

但是如果b字段有索引.

因为MySQL只有升序索引,所以他先从最小值读取,先通过where条件，然后进入order by语句

所以,order by .. least 的方式,只能用于升序的情况.

这个猫好像15所的小漂啊.

ITPub博客

MySQL自定义变量执行顺序

最新文章