MySQL 是如何實現資料的排序的？

李东平|一线码农發表於2024-08-05
MySql排序

1.背景

或許你面試的時候被問到了mysql的排序問題

又或許你在學習排序演算法的時候想到了資料庫的排序是如何實現的呢

下面重點從面試的角度來回答這個問題

2.面試回答

1.普通面試者回答

普通面試者的回答通常是點對點的回答,如下:

MySQL 實現資料的排序主要透過排序演算法和索引結構來完成。排序操作是資料庫查詢中非常常見的操作，尤其是在執行 SELECT 語句時帶有 ORDER BY 子句時。下面詳細介紹 MySQL 如何實現資料的排序：

1. 使用索引進行排序

當查詢中使用的排序鍵（ORDER BY 子句中的列）上有索引時，MySQL 可以直接利用這個索引來返回有序的資料，而不需要進行額外的排序操作。這可以極大地提高查詢效率，因為索引本身就是有序的。

  • 索引掃描：如果查詢可以完全透過索引來滿足（即覆蓋索引），MySQL 可以透過索引掃描直接返回有序的資料行，而無需讀取表中的實際資料行。
  • 索引覆蓋掃描：當查詢列和排序列都包含在索引中時，MySQL 可以僅透過索引來執行查詢，這稱為索引覆蓋掃描。

2. 檔案排序（Filesort）

如果查詢中的排序鍵沒有索引支援，或者雖然存在索引但 MySQL 決定不使用（例如，因為索引選擇性的原因），MySQL 將不得不執行檔案排序（Filesort）操作。

  • 記憶體排序：首先，MySQL 會嘗試在記憶體中完成排序。它讀取查詢結果集，將它們儲存在記憶體中，並使用排序演算法（如快速排序、歸併排序等）對它們進行排序。
  • 磁碟排序：如果排序操作所需的資料量超過了 MySQL 的排序緩衝區大小（sort_buffer_size），MySQL 將不得不將資料分成多個塊，對每塊資料進行排序，然後將排序後的塊合併成一個有序的結果集。這個過程中，資料將被寫入到臨時檔案中，並可能需要多次磁碟I/O操作，這會導致效能下降。

3. 排序演算法

MySQL 在執行排序時，可能會使用多種排序演算法，具體取決於資料量、可用記憶體和其他因素。常用的排序演算法包括快速排序、歸併排序等。

4. 最佳化排序操作

為了提高排序操作的效率，可以採取以下一些最佳化措施：

  • 建立合適的索引：確保在經常用於排序的列上建立索引。
  • 調整排序緩衝區大小：透過調整 sort_buffer_size 配置項，為排序操作分配更多的記憶體，以減少磁碟I/O。
  • 最佳化查詢：儘量使查詢能夠利用索引，避免全表掃描。
  • 使用 EXPLAIN 分析查詢：使用 EXPLAIN 語句來檢視查詢的執行計劃，瞭解 MySQL 如何執行查詢，包括是否使用了索引和是否進行了檔案排序。

透過這些方法，可以有效地提高 MySQL 排序操作的效能。

2.成功面試者的回答

面試回答思路:
面試本質:不是點對點回答問題,而是面試官透過提出一個話題(問題),獲取到面試者的思維和技術水平,
因此在回答問題時一定要體現出思考過程(包括如何想的和開發經驗)和技術深度;
可以圍繞如下3個方向回答
1.回答基本定義
2.說說實際生產中的運用
3.引導到自己擅長的技術點上深入探討

基本定義

從sql層面來說的話實現排序就是在order by 欄位,升序或降序,

mysql 服務要實現這個排序功能的話主要是依靠排序演算法和索引來實現;

實際生產

在實際開發中我們一般會用主鍵或建立時間來排序,特別是資料量大的表,

一般不建議使用經常變動的欄位來排序,比如更新時間這個欄位排序;

為什麼呢?這就會涉及到一個欄位建立索引後對修改和新增的影響;

我們都知道,索引雖然提高了查詢速度,但是在新增和修改的時候效率會降低;

而實際開發中排序的欄位一般來說都要建立索引;

索引排序

索引排序的話,又要分為2種情況

1.索引掃描

2.索引覆蓋掃描

索引掃描,透過索引排序,然後讀取表中的實際行;

索引覆蓋掃描:當查詢列和排序列都包含在索引中時，MySQL 可以僅透過索引來執行查詢，而無需讀取表中的實際資料行,這樣效率會高得多。

因此,實際開發中我們一般儘量只取需要的欄位返回,不要囫圇吞棗每一列都返回,這樣不但用不到覆蓋索引,而且可能增大磁碟IO.

檔案排序(filesort)

如果不是索引欄位排序的話,其實就是常說的檔案排序(filesort),這時候也要分為2中情況

1.記憶體排序

2.磁碟排序

記憶體排序:顧名思義就是把資料讀取到記憶體中進行排序,使用排序演算法進行排序,但是如果資料量大呢,記憶體放不下,會出現什麼情況呢?記憶體溢位,報錯

當然不會,mysql服務還不至於那麼傻,記憶體不夠時就會轉入磁碟排序

磁碟排序：如果排序操作所需的資料量超過了 MySQL 的排序緩衝區大小（sort_buffer_size），MySQL 將不得不將資料分成多個塊，對每塊資料進行排序，

然後將排序後的塊合併成一個有序的結果集。這個過程中，資料將被寫入到臨時檔案中，並可能需要多次磁碟I/O操作，這會導致效能下降。

由此可見排序緩衝區這個引數的設定是mysql調優的重要部分

當然,這些都是理論,實際開發中如果發現一條帶有排序的sql執行慢,我們應該使用explain來檢視具體原因

備註:

1.explain是最佳化sql很重要的一個工具,這個一定要會....

2.關於排序演算法,大家如果之前有研究過的話,可以深入探討一下

3.總結&評論

上面2種回答方式:

第一種,更偏向餘點對點的回答,類似我們讀書時候的回答試卷的方式;

第二種,更偏向於把理論之前與實際開發結合回答,並且更注重得出結論的思考過程;

如果你是面試官,你會覺得那種回答更能得到你的青睞呢?

歡迎在評論區給出你的觀點!

完美

相關文章