order by 查詢語句使用也是非常頻繁，有時候數(shù)據(jù)量大了會發(fā)現(xiàn)排序查詢很慢，本文就介紹一下 MySQL 是如何進(jìn)行排序的，以及如何利用其原理來優(yōu)化 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;

建完表之后，再創(chuàng)建一個腳本，在腳本中插入 2000 條數(shù)據(jù)到前面建好的表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 ='傳統(tǒng)';
    END IF;
    INSERT INTO cc4 VALUES(i, CONCAT('孤狼',i), CONCAT('程序員',i),company);
    SET i=i+1;
  END WHILE;
END;;
DELIMITER ;
CALL cc4_data();

這時候我們?nèi)绻胍獙δ骋患夜纠锩娴娜税凑彰诌M(jìn)行排序，一般會這么寫：

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

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

全字段排序法

首先我們對上面的語句執(zhí)行 explain 語句，看看是怎么執(zhí)行的：

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

可以看到，在最后一列 Extra 中顯示 Using filesort，也就是說用到了文件排序，這個文件排序是如何執(zhí)行的呢？

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

上圖中顯示 company 字段為普通索引，再加上主鍵索引，這張表一共有兩個索引，所以這條語句是這么執(zhí)行的：

• 初始化 sort_buffer，并確定好需要放入 user_name ，job，company 這三個字段。
• 從 company 索引中找到第一個滿足 company='科技’ 條件的主鍵 id，也就是上圖中的 ID-3。
• 然后執(zhí)行回表操作，根據(jù) id 值到主鍵索引中取出整行，然后取出 user_name ，job，company 三個字段的值，并存入sort_buffer 中。
• 從 company 索引中取下一個滿足條件記錄的主鍵 id，重復(fù)步驟 3 。
• 繼續(xù)重復(fù) 步驟 4 和 3，直到 company 的值不滿足查詢條件為止。
• 對 sort_buffer 中的數(shù)據(jù)按照字段 user_name 做快速排序，最后按照排序結(jié)果取前 1000 行返回給客戶端。

這種排序方式稱之為全字段排序法。

上面步驟中的第 6 步，排序可以在內(nèi)存中進(jìn)行，如果內(nèi)存足夠的話，而內(nèi)存是否足夠則取決于 sort_buffer_size 的值，但是我們想一下，如果排序的數(shù)據(jù)量太大，我們不可能提供足夠的內(nèi)存，那么這時候就不得不使用磁盤的臨時文件來進(jìn)行排序。

那么我們?nèi)绾沃喇?dāng)前的排序語句是使用文件完成排序還是使用內(nèi)存來完成排序呢？

接下來我們執(zhí)行下面兩句話：

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：

這里面有幾個信息比較關(guān)鍵：

• memory_available：表示當(dāng)前可以用于排序的內(nèi)存
• num_rows_found：表示有多少條數(shù)據(jù)參與排序。
• num_initial_chunks_spilled_to_disk：表示產(chǎn)生了多少個臨時文件用于排序，0表示當(dāng)前是全部采用內(nèi)存排序，這里為什么會產(chǎn)生多個文件的原因是當(dāng)數(shù)據(jù)量過大時，MySQL會分散到多個文件進(jìn)行處理，最后通過歸并排序算法來完成完整的排序。
• sort_mode：最后這一列代表當(dāng)前排序模式，packed_additional_fields代表的就是采用了全字段排序法，而且啟用了 pack。

接下來我們把默認(rèn)的排序內(nèi)存改小一點：

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

執(zhí)行之后可以看到排序大小已經(jīng)被修改為 32k：

接下來我們再來執(zhí)行排序查詢跟蹤

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

這時候會發(fā)現(xiàn)這時候使用到了 6 個臨時文件進(jìn)行排序：

主鍵排序法

在前面的全字段排序法中其實有些浪費，因為排序只用到了 user_name 字段，而我們卻同時查詢了其他字段，這些字段查詢出來都是會占用空間的，尤其是當(dāng)查詢的字段很多，或者有些字段又特別長的時候，會占用很大空間，導(dǎo)致不得不使用文件排序，而由于字段多又長，就會造成文件個數(shù)增多，從而導(dǎo)致排序性能會更差。

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

在 MySQL 中提供了一個字段 max_length_for_sort_data，默認(rèn)是 4096

show variables like 'max_length_for_sort_data';

這個字段是控制用于排序的行數(shù)據(jù)的長度的一個參數(shù)。如果用于排序的單行數(shù)據(jù)長度超過這個值，MySQL 就認(rèn)為單行數(shù)據(jù)太大了，要換一個算法，采用 rowid 算法。

采用 rowid 算法的步驟如下：

• 初始化 sort_buffer，并確定好需要放入 user_name ，id 這兩個字段。
• 從 company 索引中找到第一個滿足 company='科技’ 條件的主鍵 id，也就是上圖中的 ID-3。
• 然后執(zhí)行回表操作，根據(jù) id 值到主鍵索引中查找出整行數(shù)據(jù)，然后取出 user_name ，id 這兩個字段的值，并存入sort_buffer 中。
• 從 company 索引中取下一個滿足條件記錄的主鍵 id，重復(fù)步驟 3 。
• 繼續(xù)重復(fù) 步驟 4 和 3，直到 company 的值不滿足查詢條件為止。
• 對 sort_buffer 中的數(shù)據(jù)按照字段 user_name 做快速排序。
• 遍歷排序結(jié)果，取前 1000 行數(shù)據(jù)，并根據(jù)主鍵 id 進(jìn)行回表查詢，取出 user_name，job 和 company三個字段返回給客戶端。

這種排序方式對比前面一種全字段排序，我們發(fā)現(xiàn)存的數(shù)據(jù)更少了，所以需要的內(nèi)存空間更少，但是又有一個更大的問題就是這里需要進(jìn)行兩次回表操作，當(dāng)數(shù)據(jù)量過大，這也會造成性能影響。

所以我們再結(jié)合前面學(xué)習(xí)的知識，如果排序的時候可以采用覆蓋索引，那么就不需要進(jìn)行回表操作，從而大幅度提升性能，這也是覆蓋索引的威力。

如何避免 filesort

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

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

執(zhí)行結(jié)果如下：

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

因為我們創(chuàng)建了一個聯(lián)合索引，而 MySQL 中的 B+ 樹索引是天然有序的，所以當(dāng)指定了 company，按順序找到的數(shù)據(jù)，就是按照 user_name 進(jìn)行的排序，也就不需要再執(zhí)行一次排序操作了。

到此這篇關(guān)于MySQL中如何優(yōu)化order by語句的文章就介紹到這了,更多相關(guān)MySQL優(yōu)化order by內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論