關於 order By 函數，你可以知道更多

一個非常類似的問題，可以看這裡：

這句簡單的sql，如何加索引？顛覆了我多年的認知

。這篇可以算是擴展閱讀，希望對你有所幫助。

order by 是我們常用的 SQL 函數之一，如果我們需要返回結果集為有序的，則我們需要使用 order by 函數。

假設我們現在有一個需求，查詢用戶表中來自江西的用戶，並且按照名字排序返回前 20 位，面對這種需求我們就需要利用 order by 函數來幫我們實現。

用戶表定義如下：

<code>CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;
/<code>

我們的 SQL 語句就可以這樣寫：

<code>select city,name,age from user where city='江西' order by name limit 20;
/<code>

這樣返回來的數據就是有序的，order by 函數背後是如何運行的？幫助我們完成結果集排序。

order by 函數要滿足結果集排序要求，在 MySQL 數據庫中，有兩種方式來實現：

使用文件排序

先使用 explain 查看上述語句的執行情況

可以看到 Extra 這一行，顯示為 Using filesort，說明使用了文件排序。對於文件排序 MySQL 數據庫會給每個線程分配一塊排序內存（sort_buffer）。sort_buffer 的大小可以通過 sort_buffer_size 來控制，默認值為 262144。

在文件排序中根據 max_length_for_sort_data 情況，MySQL 提供了兩種算法，如果我們返回的字段長度總和小於 max_length_for_sort_data 的值，這會將所有的字段都放入到 sort_buffer 中，我們稱之為全字段排序。

如果返回的字段長度總和大於 max_length_for_sort_data 設置的值，則只會將 id，排序字段放入到 sort_buffer 中，最後排序後再回主鍵索引中查詢所有數據，這種稱之為 rowid 排序。

全字段排序和 rowid排序 的區別在於數據在 sort_buffer 排序內存中排序後，全字段排序算法在sort_buffer 的臨時表中已經有所有的返回字段，不需要再回主鍵索引中查詢信息，可以直接返回給用戶，而 rowid排序算法在sort_buffer 中只有部分字段，需要再回主鍵索引中查詢數據，才能返回給用戶。

可能看了還是有點懵逼，可以參考下面這張圖，加深理解：

文件排序優先在 sort_buffer 內存塊中完成，如果需要排序的數據量大於 sort_buffer_size 的值，那麼就需要藉助磁盤臨時文件來完成排序，那麼性能就回有所下降，所以在內存允許的情況下，可以適當的增大 sort_buffer_size 的值，來減少使用磁盤文件排序的機率。

對於全字段排序和rowid排序，優選選擇使用全字段排序，因為rowid排序需要回表查詢，會造成磁盤讀，不會被 MySQL 優選使用。在 MySQL8 中好像已經取消了。

利用索引排序

我們還可以利用聯合索引來滿足排序要求，因為索引在存儲的時候就是有序的，所以在讀取的時候自然就有序了。

對於這條 SQL 語句：

<code>select city,name,age from user where city='江西' order by name limit 20;
/<code>

我們可以創建一個city、name、age的聯合索引，添加索引的SQL 語句為：

<code>alter table t add index city_user_age(city, name, age);
/<code>

再次使用 explain 查看 SQL 語句執行過程：

圖片描述

還是看 Extra 這一行，不再是 Using filesort，而是 Using index了，說明使用聯合索引。

此時這條語句的執行流程大概如下：

• 1、從索引 (city,name,age) 找到第一個滿足 city='江西’條件的記錄，取出其中的 city、name 和 age 這三個字段的值，作為結果集的一部分直接返回；
• 2、從索引 (city,name,age) 取下一個記錄，同樣取出這三個字段的值，作為結果集的一部分直接返回；
• 3、重複執行步驟 2，直到查到第 1000 條記錄，或者是不滿足 city='江西’條件時循環結束。

圖片來源極客時間

使用索引來滿足排序性能要好不少，但是維護索引需要不少的代價，任何一種技術沒有最好，只有最合適。

文章來源於互聯網平頭哥 ，作者互聯網平頭哥

閱讀更多 小姐姐味道架構設計 的文章

關鍵字: 索引 算法 數據庫