前言
本文介紹 MySQL 隨機查詢的作業流程、優化隨機查詢的方式、和臨時表，
作業流程
根據下表結構建立 words 表，并通過程序插入 10000 條模擬資料，
CREATE TABLE `words` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`word` varchar(64) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
隨機查詢場景，“從上表中隨機查詢三個 wrod”，簡單直接的查詢方式就是，SELECT * FROM words ORDER BY rand() LIMIT 3;，下面分析下這條陳述句的作業流程，
記憶體臨時表
這個排序需要使用臨時表，當臨時表資料量小于 tmp_table_size 時，會使用記憶體臨時表，tmp_table_size 默認是 16M，
一般情況下，會優先采用全欄位排序方式，因為可以減少一次回表查詢，不過，因為是使用的記憶體臨時表，所以 rowid 方式回表也是直接查詢記憶體，并且 rowid 方式因為只存盤 rowid 和排序欄位，在相同大小的 sort_buffer 下，可以存盤更多的行，避免或減少外存檔案的使用，所以，MySQL 會選擇 rowid 排序方式，
如果不存在 LIMIT 關鍵字，作業流程：
1、創建一個臨時表，采用記憶體 memory 引擎，包含一個 double 欄位，表示 rand() 函式生成的亂數，另一個是 word 欄位
2、從原表中根據主鍵取出 word 值，并執行 rand 函式生成一個 0 ~ 1 的數字寫入到臨時表，直至寫完 10000 行，掃描行數 10000
3、初始化 sort_buffer，確定只有一個 double 和一個整形欄位
4、從臨時表中按行讀取 double 欄位，和位置資訊，寫入到 sort_buffer，掃描行數變為 20000
5、根據 double 欄位排序
6、排序完后，根據前 3 行的位置資訊到臨時表中取對應的 word，回傳客戶端，掃描行數變為 20003
位置資訊含義：
- 對于有主鍵的InnoDB表來說，這個rowid就是主鍵ID；
- 對于沒有主鍵的InnoDB表來說，這個rowid就是由系統生成的；
- MEMORY引擎不是索引組織表，在這個例子里面，你可以認為它就是一個陣列，因此，這個rowid其實就是陣列的下標，
如果陳述句中存在 LIMIT 關鍵字，所以還會使用 MySQL5.6 提供的優先佇列的排序方式進行優化，
大致作業流程：
1、根據主鍵從原資料表中，先取前 3 行，構成一個堆
2、取一行剩余的資料添加到堆中，再調整
3、重復第二步，直至所有剩余的行插入完成
按優先佇列的方式排序，只需要掃描 10003 行，
磁盤臨時表
當臨時表的資料量大小大于 tmp_table_size 時，則會采用磁盤臨時表，并且，這時候回表意味著隨機讀檔案，所以會搭配全欄位排序方式，
優化
對于 SELECT * FROM words ORDER BY rand() LIMIT 3; 需要掃描 20003 行，隨著資料量增大，它執行速度會越來越慢，有什么優化方式么？
方案1：
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;
計算主鍵 id 的范圍，然后在 id 范圍內進行隨機，再取不小于隨機值的行（注意不能用 id = @X 的判斷邏輯，否則可能 id 不存在），
雖然這個方案可以隨機，但是隨機是不公平的，因為表可能會洗掉資料，id 不連續，存在空洞，
方案2：
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；
為了保證隨機是公平的，那么可以隨機 [1, 總行數] 中的一個數，然后通過分頁操作拿到對應的行，此時需要掃描 C + Y1 + Y2 + Y3 + 3 行，
進一步優化，可以采用下面的查詢方式，只需要掃描 C + max(Y1, Y2, Y3) + 3 行，
# 前提，對 Y1、Y2、Y3 提前排序好
id1 = select * from t limit @Y1，1；
id2 = select * from t where id > id1 limit @Y2 - @Y1，1；
id3 = select * from t where id > id2 limit @Y3 - @Y2，1；
參考
- [1] 如何正確地顯示隨機訊息
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/301196.html
標籤：其他