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

建立一張表：

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)建一個(gè)腳本，在腳本中插入 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= '保險(xiǎn)';
    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();

這時(shí)候我們?nèi)绻胍獙?duì)某一家公司里面的人按照名字進(jìn)行排序，一般會(huì)這么寫(xiě)：

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

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

全字段排序法

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

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

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

大概畫(huà)出如下一個(gè)草圖表示表 cc4 中的索引示意圖：

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

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

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

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

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

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

SET optimizer_trace='enabled=on';-- 打開(kāi)optimizer_trace，只對(duì)本線程有效
SELECT user_name,job,company FROM cc4 WHERE company='科技' ORDER BY user_name LIMIT 1000;
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G -- 查看 OPTIMIZER_TRACE 輸出

最后這條查詢語(yǔ)句會(huì)返回非常多的信息，包括了具體的查詢步驟，我們看到最后的 filesort_summary：

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

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

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

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

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

接下來(lái)我們?cè)賮?lái)執(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 輸出

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

主鍵排序法

在前面的全字段排序法中其實(shí)有些浪費(fèi)，因?yàn)榕判蛑挥玫搅?user_name 字段，而我們卻同時(shí)查詢了其他字段，這些字段查詢出來(lái)都是會(huì)占用空間的，尤其是當(dāng)查詢的字段很多，或者有些字段又特別長(zhǎng)的時(shí)候，會(huì)占用很大空間，導(dǎo)致不得不使用文件排序，而由于字段多又長(zhǎng)，就會(huì)造成文件個(gè)數(shù)增多，從而導(dǎo)致排序性能會(huì)更差。

上面的查詢語(yǔ)句中，我們有沒(méi)有辦法不把一些無(wú)用的字段也放到 sort_buffer 中呢？

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

show variables like 'max_length_for_sort_data';

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

采用 rowid 算法的步驟如下：

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

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

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

如何避免 filesort

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

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é)果如下：

可以看到，這次就沒(méi)有用到 filesort 了，這是為什么呢？

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

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