MySQL自定義變數執行順序

壹頁書發表於2015-01-26
原文網址 : http://blog.itpub.net/29254281/viewspace-1415038/
MySql變數
高效能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部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-1415038/，如需轉載，請註明出處，否則將追究法律責任。

相關文章