MySQL 筆記整理（17） --如何正確地顯示隨機消息？

筆記記錄自林曉斌（丁奇）老師的《MySQL實戰45講》（本篇內圖片均來自丁奇老師的講解，如有侵權，請聯繫我刪除） 17） --如何正確地顯示隨機消息？如果有這麼一個英語單詞表，需要每次訪問時隨機顯示三個單詞。但實際使用中發現，隨著單詞表越變越大，選單詞這個邏輯變得越來越慢。建表語句如下：為了便 ...

筆記記錄自林曉斌（丁奇）老師的《MySQL實戰45講》

（本篇內圖片均來自丁奇老師的講解，如有侵權，請聯繫我刪除）

17） --如何正確地顯示隨機消息？

　　如果有這麼一個英語單詞表，需要每次訪問時隨機顯示三個單詞。但實際使用中發現，隨著單詞表越變越大，選單詞這個邏輯變得越來越慢。建表語句如下：

mysql> CREATE TABLE `words` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `word` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=0;
  while i<10000 do
    insert into words(word) values(concat(char(97+(i div 1000)), char(97+(i % 1000 div 100)), char(97+(i % 100 div 10)), char(97+(i % 10))));
    set i=i+1;
  end while;
end;;
delimiter ;

call idata();

　　為了便於量化說明，我們在這個表裡面插入了10000行記錄。

記憶體臨時表：

　　你可能會首先想到order by rand()來實現這個邏輯。

mysql> select word from words order by rand() limit 3;

　　這個語句的語義很簡單，隨機排序取前3個。但這個語句的執行流程確有點複雜。如果你使用explain命令來查看的話會發現，這個語句需要臨時表，並且要在臨時表上排序。上一篇中我們提到過order by的兩種方式，全欄位排序和rowid排序。對於這條語句，會選擇哪種方式進行排序呢？在上一篇我們有個結論，對於InnoDB表來說，執行全欄位排序會減少磁碟訪問，因此會被優先選擇。此處我們強調了“InnoDB表”，你肯定想到了，對於記憶體表，回表過程只是簡單地根據數據行的位置，直接訪問記憶體得到數據，根本不會導致多訪問磁碟。優化器沒有了這一層顧慮，那麼它會優先考慮的就是用於排序的行越小越好(行越小，sort_buffer可以存儲的行數就越多，排序就越快)，所以，MySQL這時就會選擇rowid排序。

　　我們再來看看這個演算法的執行流程：

創建一個臨時表，這個臨時表使用的是memory引擎，表裡有兩個欄位，第一個是double類型，為了後面描述方便，記為欄位R。第二個欄位是varchar(64)類型，記為欄位W。並且，這個表沒有索引。
從words表中，按主鍵順序取出所有的word值。對於每個word值，調用rand()函數生成一個大於0小於1的隨機數，並把這個隨機小數和word分別存入臨時表的R和W欄位中，到此，掃描行數是10000.
現在臨時表中有10000行數據，接下來你要在這個沒有索引的記憶體臨時表上，按照欄位R排序。
初始化sort_buffer。sort_buffer有兩個欄位，一個是double類型，另一個是整形。
從記憶體臨時表中一行一行地取出R值和位置信息(後面我們會解釋為什麼是位置信息)。分別存入sort_buffer中的兩個欄位里。這個過程要對記憶體臨時表做全表掃描，此時掃描行數增加10000，變成了20000.
在sort_buffer中根據R的值進行排序。註意，這個過程沒有涉及表操作，所以不會增加掃描行數。
排序完成後，取出錢三個結果的位置信息，依次到記憶體臨時表中取出word的值，返回給客戶端。這個過程中，訪問了表的三行數據。總掃描行數變成了20003.

　　　因此，完整的排序執行流程圖就如下所示：

　　圖中的pos就是位置信息，你可能有個疑問，這個位置信息是指的什麼呢？並且，我們在之前order by是如何排序的內容里，對InnoDB表排序的時候，明明用的還是ID欄位。這時候，我們要來談一談一個基本概念：MySQL的表是用什麼方法來定位“一行數據”的。

　　如果你創建一個表沒有主鍵，或者把一個表的主鍵刪掉了，那麼InnoDB會自己生成一個長度為6位元組的rowid來作為主鍵。這也就是排序模式裡面，rowid名字的來歷。實際上它表示的是：每個引擎用來唯一標識數據行的信息。

對於有主鍵的InnoDB表來說，這個rowid就是主鍵ID；
對於沒有主鍵的InnoDB表來說，這個rowid就是有系統生成的。
MEMORY引擎不是索引組織表。在這個例子裡面，你可以認為它就是一個數組。因此，這個rowid其實就是數組的下標。

　　這裡我們稍微小結一下：order by rand()使用里記憶體臨時表，記憶體臨時表排序的時候使用了rowid排序方法。

磁碟臨時表：

　　那麼，是不是所有的臨時表都是記憶體表呢？其實不是的。tmp_table_size這個配置限制了記憶體臨時表的大小，預設值是16M.如果臨時表大小超過了tmp_table_size，那麼記憶體臨時表就會轉成磁碟臨時表。磁碟臨時表使用的引擎預設是InnoDB，是由參數internal_tmp_disk_storage_engine控制的。當使用磁碟臨時表時，對應的就是一個沒有顯示索引的InnoDB表的排序過程。

隨機排序方法：

　　回到我們文章開頭的問題，怎麼正確地隨機排序呢？我們先把問題再簡化一下，如果只隨機選擇1個word的值，可以怎麼做呢？思路上是這樣的：

取得這個表的主鍵id的最大值M和最小值N。
用隨機函數生成一個最大值到最小值之間的數X=（M-N）* rand() + N
取不小於X的第一個ID的行。

　　mysql語句如下：

mysql> select max(id),min(id) into @M,@N from t ;
set @X= floor((@M-@N+1)*rand() + @N);
select * from t where id >= @X limit 1;

　　這個方法效率很高，因為取max(id)和min(id)都不需要掃描索引，而第三步的select也可以用索引快速定位，可以認為只掃描了3行。但實際上，這個演算法本身並不嚴格滿足題目的隨機要求，因為ID中間可能有空洞，因此選擇不同行的概率不一樣，不是真正的隨機。比如你有4個id,分別是1,2,4,5.如果按照上面的方法，那麼取得id=4的這一行的概率就會比取得其他行的概率高。如果空洞再大一些，如這4個的id分別是1,2,40000,40001，那麼這個演算法基本就是個bug了，取得id=40000的概率會很大很大。所以，為了取得嚴格隨機的結果，你可以用下麵這個流程：

取得整個表的行數，記為C
取得Y=floor(C*rand())。floor函數在這裡的作用，就是取整數部分。
再用limit Y,1取得一行。

　　我們把這個演算法，稱為隨機演算法2.下麵這段代碼就是上面流程的執行語句序列：

mysql> select count(*) into @C from t;
set @Y = floor(@C * rand());
set @sql = concat("select * from t limit ", @Y, ",1");
prepare stmt from @sql;
execute stmt;
DEALLOCATE prepare stmt;

　　由於limit後面的參數不能直接跟變數，所以上面的代碼中使用了預處理語句prepare+execute的方法。當然，養成好習慣，每一次執行完execute時，須執行DEALLOCATE prepare語句，這樣可以釋放執行中所使用的資料庫資源。當然，你可以把拼接SQL語句的方法寫在應用程式中，會更簡單一些。這個方法解決了第一個方法出現的概率不均勻的問題。MySQL處理Limit Y,1的做法就是按順序一個一個地讀出來，丟掉前Y個，然後把下一個記錄作為返回結果，因此這一步需要掃描Y+1行。再加上，第一部掃描的C行，總共需要C+Y+1行，執行代價要比第一個演算法高。當然這個代價還是遠小於order by rand()的。

　　當然，如果你要取3個隨機word值，那麼可以這麼寫。

mysql> select count(*) into @C from t;
set @Y1 = floor(@C * rand());
set @Y2 = floor(@C * rand());
set @Y3 = floor(@C * rand());
select * from t limit @Y1，1； // 在應用代碼裡面取 Y1、Y2、Y3 值，拼出 SQL 後執行
select * from t limit @Y2，1；
select * from t limit @Y3，1；