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();

如何随機取3個單詞

select word from words order by rand() limit 3; -- 查看上面語句的執行情況 explain select word from words order by rand() limit 3;

Extra中Using temporary表示使用臨時表，Using filesort表示需要執行排序操作。

上述默認使用的臨時表是内存表，對于内存表來說，回表過程隻是簡單地根據數據行的位置直接訪問内存得到數據，并不會導緻額外的磁盤訪問，因此MySQL會在排序時會優先使用rowid排序。

上述SQL語句的執行過程如下：

1. 創建一個臨時表（該表使用的是memory引擎），表裡有兩個字段，第一個字段是double類型（記為字段R），第二個字段是varchar(64)類型（記為字段W）,臨時表沒有索引
2. 從word表中，按照主鍵順序取出所有的word值，對于每一個word值，調用rand函數生成一個大于0小于1的随機小數，把該随機小數和word值存入臨時表的R和W字段中，至此掃描行數是10000
3. 臨時表目前有10000行數據，下面需要對這個臨時表按照字段R進行排序
4. 初始化sort_buffer，sort_buffer中有兩個字段，一個是double類型，另一個是整型
5. 從内存臨時表中逐行取出R值和位置信息，分别存入sort_buffer中的兩個字段，此時掃描行數又增加了10000行，變成了20000(MySQL8.0.12以後這裡還是10000行，應該是對内存表做了優化，有知道的朋友可以留言告訴我)
6. 在sort_buffer中根據R值進行排序
7. 排序完成以後，取出前三個結果的位置信息，到内存臨時表中取出word值，返回給客戶端。總掃描行數變為20003（MySQL8.0以後這裡是10003行）。

MySQL8.0下慢查詢日志如下圖，掃描行數為100003行：

臨時表隻能是内存表麼？

答案是NO。那什麼時候臨時表會使用内存，什麼時候又會使用磁盤呢？該選擇主要依賴于tmp_table_size參數的控制，默認是16M，如果臨時表的大小超過了tmp_table_size，那麼内存臨時表就會轉換為磁盤臨時表。

磁盤臨時表使用的默認引擎是InnoDB，由參數default_tmp_storage_engine控制。

set tmp_table_size=1024; set sort_buffer_size=32768; set max_length_for_sort_data=16; /* 打開 optimizer_trace，隻對本線程有效 */ SET optimizer_trace='enabled=on'; /* 執行語句 */ select word from words order by rand() limit 3; /* 查看 OPTIMIZER_TRACE 輸出 */ SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

在我們上圖的輸出中，我們可以看出排序算法使用了優先隊列排序算法，然後是全字段排序（也就是說不用回表）。

peak_memory_used代表排序時使用到的内存，按道理應該等于sort_buffer_size指定的值，之所以不等的原因是作者本人的MySQL是8.0.12版本。

MySQL8.0.12之前，MySQL優化器會為排序直接分配sort_buffer_size指定大小的内存，但從MySQL8.0.12開始，為排序分配内存是以增量的方式進行。