目錄
全字段排序
排序執行流程？
如何确定排序語句是否使用了臨時檔案？
rowid 排序
排序執行流程？
全字段排序 vs rowid排序
優化1：需排序字段本身有序
優化2：覆寫索引
平時在開發過程中，清單排序使用的挺多的。
例如：要查詢城市是“杭州”的所有人名字，并且按照姓名排序傳回前 100 個人的姓名、年齡。
表結構如下：
sql實作：
select city, name, age from myuser where city='杭州' order by name limit 100;
資料情況：
explain執行情況
Using filesort：需要排序，MySQL 會給每個線程配置設定一塊記憶體用于排序，稱為 sort_buffer。
全字段排序
排序執行流程？
1、初始化sort_buffer，确定放入select字段（name、city、age）
2、從city的普通索引樹找到一個滿足city = '杭州' 的主鍵ID
3、根據ID 回表主鍵索引樹，拿到name、city、age三個字段的值，存入sort_buffer中
4、重複2、3步驟，直到city值不滿足查詢條件為止
5、對sort_buffer中的資料按照字段name排序
6、按照排序結果取前1000行傳回用戶端
其中，步驟5排序，根據sort_buffer_size與目前排序所需要的記憶體的關系，來決定是在記憶體中完成，還是需要外部檔案排序。
排序需要的記憶體 < sort_buffer_size，在記憶體中完成；反之，就需要利用磁盤臨時檔案輔助排序
如何确定排序語句是否使用了臨時檔案？
執行下方sql
/* 打開optimizer_trace，隻對本線程有效 */
SET optimizer_trace='enabled=on';
/* @a儲存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into @a from performance_schema.session_status where variable_name = 'Innodb_rows_read';
/* 執行語句 */
select city, name,age from t where city='杭州' order by name limit 1000;
/* 檢視 OPTIMIZER_TRACE 輸出，主要關注filesort_summary的内容*/
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G
/* @b儲存Innodb_rows_read的目前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';
/* 計算Innodb_rows_read內插補點 */
select @[email protected];
mysql> SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G
*************************** 1. row ***************************
QUERY: select city, name,age from myuser where city='杭州' order by name limit 100
TRACE: {
"steps": [
...// 省略
]
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.01 sec)
摘出filesort_summary
select @[email protected] 的傳回結果是 221，表示整個執行過程隻掃描了 221 - 1 行。 (備注：因為查詢 OPTIMIZER_TRACE 這個表時，需要用到臨時表，而 internal_tmp_disk_storage_engine 的預設值是 InnoDB。如果使用的是 InnoDB 引擎的話，把資料從臨時表取出來的時候，會讓 Innodb_rows_read 的值加 1)
rowid 排序
如果排序後需要傳回的字段太多，那麼sort_buffer裡能同時放下的行數很少，就要分成很多個臨時檔案，排序的性能會很差。
max_length_for_sort_data，是 MySQL 中專門控制用于排序的行資料的長度（也就是city、name、age總長36）的一個參數。它的意思是，如果單行的長度超過這個值，MySQL 就認為單行太大，要換一個算法。
排序執行流程？
1、初始化sort_buffer，确定放入需要排序字段與ID（name、id）id
2、從city的普通索引樹找到一個滿足city = '杭州' 的主鍵ID
3、根據ID 回表主鍵索引樹，拿到name、id兩個字段的值，存入sort_buffer中
4、重複2、3步驟，直到city值不滿足查詢條件為止
5、對sort_buffer中的資料按照字段name排序
6、周遊排序結果，取前100行，并根據ID 回表主鍵索引樹，取出city、name、age三個字段傳回用戶端（不用存儲到sort_buffer了）
全字段排序 vs rowid排序
如果排序記憶體太小，mysql認為會影響排序效率，就會使用rowid排序算法，優點：一次可以排序更多行；缺點：最後結果集需要再次回表
如果記憶體足夠大，mysql 會優先選擇全字段排序，優點：将需要的字段都放入sort_buffer中，不需要二次回表；缺點：對于rowid來說，需要的臨時檔案較多
優化1：需排序字段本身有序
如果本身排序字段就有索引？執行sql給name添加索引
alter table myuser add index city_user(city, name);
再看下explain結果
extra中沒有using filesort，也就是說不需要排序了
并且由于name本身有序，隻需要根據索引樹找到前100條資料就完成了，是以隻需要掃描100次
優化2：覆寫索引
使用覆寫索引不需要回表，提高查詢效率。
執行sql，添加索引
alter table myuser add index city_user_age(city, name, age);
再看下explain結果
可見，使用到了覆寫索引
備注：因為測試資料量少，對于優化1與優化2的執行時間沒有明顯的差距。