MySQL中如何優化order by語句

order by 查詢語句使用也是非常頻繁，有時候資料量大了會發現排序查詢很慢，本文就介紹一下 MySQL 是如何進行排序的，以及如何利用其原理來優化 order by 語句。

建立一張表：

CREATE TABLE `cc4` (
  `id` INT(11) NOT NULL,
  `user_name` VARCHAR(16) NOT NULL,
  `job` VARCHAR(16) NOT NULL,
  `company` VARCHAR(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `company_index` (`company`)
) ENGINE=INNODB;

建完表之後，再建立一個指令碼，在指令碼中插入 2000 條資料到前面建好的表cc4 中：

DROP PROCEDURE IF EXISTS cc4_data;
DELIMITER ;;
CREATE PROCEDURE cc4_data()
BEGIN
  DECLARE i INT;
  DECLARE company VARCHAR(128);
  SET i=1;
  WHILE(i<=2000) DO
    IF i%6 = 0
      THEN SET company= '證券';
    ELSEIF i%6 = 1
      THEN SET company= '銀行';
    ELSEIF i%6 = 2
      THEN SET company= '保險';
    ELSEIF i%6 = 3
      THEN SET company= '科技';
    ELSEIF i%6 = 4
      THEN SET company= '金融';
    ELSE
      SET company ='傳統';
    END IF;
    INSERT INTO cc4 VALUES(i, CONCAT('孤狼',i), CONCAT('程式設計師',i),company);
    SET i=i+1;
  END WHILE;
END;;
DELIMITER ;
CALL cc4_data();

這時候我們如果想要對某一家公司裡面的人按照名字進行排序，一般會這麼寫：

SELECT user_name,job,company FROM cc4 WHERE company='科技' ORDER BY user_name LIMIT 1000;

這是一條非常簡單且常見的 sql 語句，但是就是這麼簡單的一條 sql，它到底是如何被執行的呢？

全欄位排序法

首先我們對上面的語句執行 explain 語句，看看是怎麼執行的：

explain SELECT user_name,job,company FROM cc4 WHERE company='科技' ORDER BY user_name LIMIT 1000;

可以看到，在最後一列 Extra 中顯示 Using filesort，也就是說用到了檔案排序，這個檔案排序是如何執行的呢？

大概畫出如下一個草圖表示表 cc4 中的索引示意圖：

上圖中顯示 company 欄位為普通索引，再加上主鍵索引，這張表一共有兩個索引，所以這條語句是這麼執行的：

• 初始化 sort_buffer，並確定好需要放入 user_name ，job，company 這三個欄位。
• 從 company 索引中找到第一個滿足 company='科技’ 條件的主鍵 id，也就是上圖中的 ID-3。
• 然後執行回表操作，根據 id 值到主鍵索引中取出整行，然後取出 user_name ，job，company 三個欄位的值，並存入sort_buffer 中。
• 從 company 索引中取下一個滿足條件記錄的主鍵 id，重複步驟 3 。
• 繼續重複 步驟 4 和 3，直到 company 的值不滿足查詢條件為止。
• 對 sort_buffer 中的資料按照欄位 user_name 做快速排序，最後按照排序結果取前 1000 行返回給使用者端。

這種排序方式稱之為全欄位排序法。

上面步驟中的第 6 步，排序可以在記憶體中進行，如果記憶體足夠的話，而記憶體是否足夠則取決於 sort_buffer_size 的值，但是我們想一下，如果排序的資料量太大，我們不可能提供足夠的記憶體，那麼這時候就不得不使用磁碟的臨時檔案來進行排序。

那麼我們如何知道當前的排序語句是使用檔案完成排序還是使用記憶體來完成排序呢？

接下來我們執行下面兩句話：

SET optimizer_trace='enabled=on';-- 開啟optimizer_trace，只對本執行緒有效
SELECT user_name,job,company FROM cc4 WHERE company='科技' ORDER BY user_name LIMIT 1000;
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`G -- 檢視 OPTIMIZER_TRACE 輸出

最後這條查詢語句會返回非常多的資訊，包括了具體的查詢步驟，我們看到最後的 filesort_summary：

這裡面有幾個資訊比較關鍵：

• memory_available：表示當前可以用於排序的記憶體
• num_rows_found：表示有多少條資料參與排序。
• num_initial_chunks_spilled_to_disk：表示產生了多少個臨時檔案用於排序，0表示當前是全部採用記憶體排序，這裡為什麼會產生多個檔案的原因是當資料量過大時，MySQL會分散到多個檔案進行處理，最後通過歸併排序演演算法來完成完整的排序。
• sort_mode：最後這一列代表當前排序模式，packed_additional_fields代表的就是採用了全欄位排序法，而且啟用了 pack。

接下來我們把預設的排序記憶體改小一點：

SET sort_buffer_size=32768; -- 8.0 版本最小值，無法設定成更小，不同版本之間有差異
show variables like 'sort_buffer_size';

執行之後可以看到排序大小已經被修改為 32k：

接下來我們再來執行排序查詢跟蹤

SELECT user_name,job,company FROM cc4 WHERE company='科技' ORDER BY user_name LIMIT 1000;
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`G -- 檢視 OPTIMIZER_TRACE 輸出

這時候會發現這時候使用到了 6 個臨時檔案進行排序：

主鍵排序法

在前面的全欄位排序法中其實有些浪費，因為排序只用到了 user_name 欄位，而我們卻同時查詢了其他欄位，這些欄位查詢出來都是會佔用空間的，尤其是當查詢的欄位很多，或者有些欄位又特別長的時候，會佔用很大空間，導致不得不使用檔案排序，而由於欄位多又長，就會造成檔案個數增多，從而導致排序效能會更差。

上面的查詢語句中，我們有沒有辦法不把一些無用的欄位也放到 sort_buffer 中呢？

在 MySQL 中提供了一個欄位 max_length_for_sort_data，預設是 4096

show variables like 'max_length_for_sort_data';

這個欄位是控制用於排序的行資料的長度的一個引數。如果用於排序的單行資料長度超過這個值，MySQL 就認為單行資料太大了，要換一個演演算法，採用 rowid 演演算法。

採用 rowid 演演算法的步驟如下：

• 初始化 sort_buffer，並確定好需要放入 user_name ，id 這兩個欄位。
• 從 company 索引中找到第一個滿足 company='科技’ 條件的主鍵 id，也就是上圖中的 ID-3。
• 然後執行回表操作，根據 id 值到主鍵索引中查詢出整行資料，然後取出 user_name ，id 這兩個欄位的值，並存入sort_buffer 中。
• 從 company 索引中取下一個滿足條件記錄的主鍵 id，重複步驟 3 。
• 繼續重複 步驟 4 和 3，直到 company 的值不滿足查詢條件為止。
• 對 sort_buffer 中的資料按照欄位 user_name 做快速排序。
• 遍歷排序結果，取前 1000 行資料，並根據主鍵 id 進行回表查詢，取出 user_name，job 和 company三個欄位返回給使用者端。

這種排序方式對比前面一種全欄位排序，我們發現存的資料更少了，所以需要的記憶體空間更少，但是又有一個更大的問題就是這裡需要進行兩次回表操作，當資料量過大，這也會造成效能影響。

所以我們再結合前面學習的知識，如果排序的時候可以採用覆蓋索引，那麼就不需要進行回表操作，從而大幅度提升效能，這也是覆蓋索引的威力。

如何避免 filesort

首先我們看下面一個例子，執行以下語句：

DROP INDEX company_index ON cc4;-- 刪除索引
CREATE INDEX company_user_index ON cc4 (company,user_name);-- 建立聯合索引
explain SELECT user_name,job,company FROM cc4 WHERE company='科技' ORDER BY user_name LIMIT 1000;

執行結果如下：

可以看到，這次就沒有用到 filesort 了，這是為什麼呢？

因為我們建立了一個聯合索引，而 MySQL 中的 B+ 樹索引是天然有序的，所以當指定了 company，按順序找到的資料，就是按照 user_name 進行的排序，也就不需要再執行一次排序操作了。

到此這篇關於MySQL中如何優化order by語句的文章就介紹到這了,更多相關MySQL優化order by內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！