詳解Mysql Order by排序底層原理

????????MySQL 的?ORDER BY?子句實現排序是一個涉及查詢優化、內存管理和磁盤 I/O 的復雜過程。其核心目標是高效地將結果集按照指定列和順序排列

一、確定排序模式 (Sort Mode)

MySQL 根據查詢特性和系統變量決定采用哪種排序策略:

1.1?Rowid 排序

  • <sort_key, rowid>?模式 (Rowid 排序):

    • 僅將排序鍵 (ORDER BY 的列)?和?行指針 (通常是主鍵或行 ID)?放入?sort_buffer?內存緩沖區。

    • 在內存中對這些?<sort_key, rowid>?元組進行排序。

    • 排序完成后,根據行指針回表查詢完整的數據行。

    • 優點:?sort_buffer?能容納更多元組,減少內存不足時落磁盤的次數。

    • 缺點:?排序后需要額外的回表操作,可能增加隨機 I/O。

    • 觸發條件:?max_length_for_sort_data?系統變量設置較小,或者?SELECT?的列總長度較大時,優化器傾向于選擇此模式。

1.2?全字段排序

  • <sort_key, additional_fields>?模式 (全字段排序):

    • 將?排序鍵 (ORDER BY 的列)?和?查詢需要返回的所有列?放入?sort_buffer

    • 在內存中直接對包含完整數據的元組進行排序。

    • 排序完成后,直接從?sort_buffer?返回結果,無需回表

    • 優點:?避免排序后的回表操作,減少隨機 I/O。

    • 缺點:?如果查詢返回的列很多或很寬,sort_buffer?能容納的元組數量會顯著減少,更容易觸發磁盤臨時文件。

    • 觸發條件:?max_length_for_sort_data?設置較大,或者查詢返回的列總長度較小時,優化器傾向于選擇此模式。

1.3?打包排序

  • <sort_key, packed_additional_fields>?模式 (打包排序 - MySQL 8.0+ 優化):

    • MySQL 8.0.20 引入的進一步優化。

    • 類似于全字段排序,但對?sort_buffer?中存儲的額外字段進行了更緊湊的打包處理。

    • 優點:?比傳統全字段排序更節省?sort_buffer?空間,容納更多數據,減少落盤。

    • 觸發條件:?MySQL 8.0.20 及以后版本默認啟用,替代傳統的全字段排序。

二、利用?sort_buffer?內存排序

  • MySQL 分配一塊稱為?sort_buffer?的內存區域專門用于排序。

  • 服務器線程將需要排序的行(根據選擇的模式,可能是部分列或全列)放入?sort_buffer

  • 如果?sort_buffer?足夠容納所有需要排序的行

    • MySQL 直接在內存中對數據進行排序。通常使用高效的快速排序 (Quicksort)?算法。

    • 對于?ORDER BY ... LIMIT N?這類只需要前 N 條結果的查詢,MySQL 優化器可能使用優先級隊列 (Priority Queue Heap Sort)?算法。它在內存中維護一個大小為 N 的堆,只保留最終需要的 N 條有序結果,避免對所有數據進行完全排序,極大提升效率。

三、處理大數據集:外部排序 (External Sort)

  • 如果?sort_buffer?無法容納所有需要排序的行

    • 分塊排序:?MySQL 會將數據分成若干塊 (chunks)。對每一塊數據在?sort_buffer?中進行快速排序,然后將排好序的塊寫入磁盤上的臨時文件。這個過程稱為 "run generation"。

    • 多路歸并 (Multi-way Merge):?當所有塊都排序并寫入臨時文件后,MySQL 使用歸并排序 (Merge Sort)?算法將這些已排序的塊合并成一個完整有序的結果集。它使用一個緩沖區同時讀取多個臨時文件的開頭部分,找出當前最小的元素輸出,然后從相應文件補充新元素,直到所有文件處理完畢。

    • 歸并路數:?同時合并的文件數由?merge_buffer_size?控制。MySQL 會盡量多路歸并以減少磁盤 I/O 輪次。

四、返回結果

  • 無論排序是在內存中完成還是經過外部排序,最終都會得到一個完全按照?ORDER BY?要求排序的結果集。

  • 服務器按順序讀取這個有序的結果集并返回給客戶端。

五、關鍵影響因素和優化點:

  • sort_buffer_size?控制分配給每個排序操作的內存緩沖區大小。增大它可以減少甚至避免磁盤臨時文件的使用,提升排序速度。但設置過大可能導致系統內存資源緊張(尤其是在高并發排序時)。

  • max_length_for_sort_data?決定優化器選擇 Rowid 排序還是全字段/打包排序的閾值。增大它可能促使優化器選擇全字段/打包排序(避免回表),但可能導致?sort_buffer?容納行數減少(更容易落盤)。需要根據實際情況權衡。

  • tmpdir?指定磁盤臨時文件的存儲目錄。使用更快的存儲設備(如 SSD)可以顯著提升外部排序階段的速度。

  • ORDER BY ... LIMIT N?優化器會優先嘗試使用優先級隊列算法,性能通常很好。

  • 使用覆蓋索引:?如果?ORDER BY?的列和?SELECT?的列都包含在一個索引中(覆蓋索引),MySQL 可以直接按索引順序讀取數據,完全避免排序操作?(Using index)。這是性能最優的方式。

  • innodb_disable_sort_file_cache?(InnoDB 相關) 控制是否對臨時文件使用操作系統文件緩存。在某些場景下禁用可能有輕微性能提升。

  • 監控指標:

    • Sort_merge_passes: 歸并排序的次數。次數多表明外部排序發生頻繁,可能需要增大?sort_buffer_size

    • Sort_range?/?Sort_scan: 分別表示通過范圍掃描和全表掃描執行的排序次數。

    • Sort_rows: 排序的總行數。

    • Created_tmp_disk_tables?/?Created_tmp_files: 創建的磁盤臨時表和臨時文件數,反映內存排序不足的情況。

六、總結流程:

  • 合理設計索引(尤其是覆蓋索引)來避免排序。

  • 根據查詢特征和服務器配置調整?sort_buffer_size?和?max_length_for_sort_data

  • 解讀執行計劃 (EXPLAIN) 中的?Using filesort(表示發生了排序)并判斷其成本。

  • 監控服務器狀態以識別潛在的排序性能瓶頸。

  • 編寫更高效的 SQL 查詢(例如,利用?LIMIT?優化)。

本文來自互聯網用戶投稿,該文觀點僅代表作者本人,不代表本站立場。本站僅提供信息存儲空間服務,不擁有所有權,不承擔相關法律責任。
如若轉載,請注明出處:http://www.pswp.cn/diannao/92070.shtml
繁體地址,請注明出處:http://hk.pswp.cn/diannao/92070.shtml
英文地址,請注明出處:http://en.pswp.cn/diannao/92070.shtml

如若內容造成侵權/違法違規/事實不符,請聯系多彩編程網進行投訴反饋email:809451989@qq.com,一經查實,立即刪除!

相關文章

SpringBoot的介紹和項目搭建

SpringBoot是簡化Spring應用開發的一個框架&#xff0c;他是Spring技術棧的整合。優點&#xff1a;能夠快速創建獨立運行的Spring項目以及與主流框架集成使用嵌入式的Servlet容器&#xff0c;應用無需打成war包&#xff0c;內嵌tomcatStarters自動依賴和版本控制大量的自動裝配…

Selenium 攻略:從元素操作到 WebDriver 實戰

在自動化測試、網頁數據爬取、批量操作網頁等場景中&#xff0c;Selenium 無疑是最受歡迎的工具之一。作為一款強大的 Web 自動化工具&#xff0c;它能模擬人類操作瀏覽器的行為&#xff0c;實現點擊、輸入、跳轉等一系列動作。本文將從基礎到進階&#xff0c;全面解析 Seleniu…

【算法訓練營Day14】二叉樹part4

文章目錄找樹左下角的值路徑總和總結&#xff1a;遞歸函數的返回值路徑總和 II總結&#xff1a;二叉樹遞歸的思考從中序與后序遍歷序列構造二叉樹找樹左下角的值 題目鏈接&#xff1a;513. 找樹左下角的值 解題邏輯&#xff1a; 使用層序遍歷&#xff0c;將最后一層的第一個元…

工資系統如何計算工資

工資系統計算工資是一個集成數據收集、規則應用、自動核算和合規審核的自動化過程&#xff0c;以下是其核心原理和步驟&#xff0c;結合技術實現與法規要求進行說明&#xff1a;?? 一、工資系統的基本框架與數據準備系統初始化與規則配置企業信息設置&#xff1a;錄入公司名稱…

車載通信架構 --- DoIP協議通信

我是穿拖鞋的漢子,魔都中堅持長期主義的汽車電子工程師。 老規矩,分享一段喜歡的文字,避免自己成為高知識低文化的工程師: 鈍感力的“鈍”,不是木訥、遲鈍,而是直面困境的韌勁和耐力,是面對外界噪音的通透淡然。 生活中有兩種人,一種人格外在意別人的眼光;另一種人無論…

基于Event Sourcing和CQRS的微服務架構設計與實戰

基于Event Sourcing和CQRS的微服務架構設計與實戰 業務場景描述 在電商系統中&#xff0c;訂單的高并發寫入與復雜的狀態流轉&#xff08;下單、支付、發貨、退貨等&#xff09;給傳統的CRUD模型帶來了挑戰&#xff1a; 數據一致性難保證&#xff1a;跨服務事務處理復雜&#x…

初級安全課第二次作業

&#xff08;一&#xff09;xss-labs 1~8關 1、前期準備 &#xff08;1&#xff09;打開小皮面板&#xff0c;并啟動Apache和MySQL&#xff08;2&#xff09;將 xss-labs放到 phpstudy_pro 的 WWW 目錄下&#xff08;3&#xff09;訪問連接&#xff1a;http://localhost/xss-la…

從零搭建智能搜索代理:LangGraph + 實時搜索 + PDF導出完整項目實戰

傳統的AI聊天系統往往局限于預訓練數據的知識范圍&#xff0c;無法獲取實時信息。本文將詳細闡述如何構建一個基于LangGraph的智能代理系統&#xff0c;該系統能夠智能判斷何時需要進行網絡搜索、有效維護對話上下文&#xff0c;并具備將對話內容導出為PDF文檔的功能。 本系統…

C語言分支和循環語句——猜數字游戲

分支語句的語法形式1. if(表達式)語句;2. if(表達式)語句1;else語句2;3. Switch(表達式){ case 1: break;case 2: break;case 3: break; default: break; }循環語句的語法形式1. while(表達式)語句 ;2. for&#xff08;表達…

Python設計模式深度解析:原型模式(Prototype Pattern)完全指南

Python設計模式深度解析&#xff1a;原型模式&#xff08;Prototype Pattern&#xff09;完全指南前言什么是原型模式&#xff1f;模式的核心組成實際案例&#xff1a;游泳比賽管理系統游泳者數據結構原型模式的實現深拷貝 vs 淺拷貝&#xff1a;核心概念解析淺拷貝&#xff08…

SAP-ABAP:SAP萬能長度計算:DYNAMIC_OUTPUT_LENGTH 深度解析

&#x1f4cf; SAP ABAP 萬能長度計算&#xff1a;DYNAMIC_OUTPUT_LENGTH 深度解析核心作用&#xff1a;智能計算數據對象在列表/ALV中的實際顯示寬度 | 關鍵優勢&#xff1a;多字節字符處理 | 格式感知 | 動態適配&#x1f50d; 一、核心功能與技術特性 &#x1f4ca; 數據類型…

20250720-2-Kubernetes 調度-資源限制對Pod調度的影響(1)_筆記

一、創建一個Pod的工作流程&#xfeff;1. k8s架構解析&#xfeff;組件交互模式: Kubernetes采用list-watch機制的控制器架構&#xff0c;實現組件間交互的解耦。各組件通過監控自己負責的資源&#xff0c;當資源發生變化時由kube-apiserver通知相關組件。類比說明: 類似小賣鋪…

mobaxteam x11傳輸界面避坑

mobaxteam x11傳輸界面避坑 文章目錄mobaxteam x11傳輸界面避坑1 windows系統必須下載xing2 配置1 windows系統必須下載xing 因為windows系統本身沒有x服務。 2 配置 如圖

flink sql如何對hive string類型的時間戳進行排序

在 Flink SQL 中對 Hive 表的 STRING 類型時間戳進行排序&#xff0c;需要先將字符串轉換為時間類型&#xff0c;再基于時間類型排序。以下是具體方法和示例&#xff1a; 一、核心解決方案 1. 字符串轉 TIMESTAMP 后排序 若 Hive 中的時間戳格式為 yyyy-MM-dd HH:mm:ss&#xf…

Linux:線程控制

線程概念線程&#xff08;Thread&#xff09;是進程&#xff08;Process&#xff09; 中的一個執行單元&#xff0c;是操作系統能夠進行運算調度的最小單位。線程也被稱為“輕量級進程”&#xff08;Lightweight Process, LWP&#xff09;。一個進程可以包含多個線程&#xff0…

React 學習(4)

核心API———createRoot、render方法1.createRoot 方法是創建react的根容器&#xff0c;就是react元素的插入位置&#xff0c;插入的dom會被轉化成react元素&#xff0c;根容器內的內容都會被react管理&#xff0c;原有dom都會被刪除。react17 根容器創建、渲染方式&#xff0…

ASP .NET Core 8集成Swagger全攻略

Swagger (現在稱為 OpenAPI) 是一個用于描述 RESTful API 的規范&#xff0c;ASP.NET Core 內置支持通過 Swashbuckle 庫生成 Swagger 文檔。以下是在 ASP.NET Core 8 中實現 Swagger 的完整步驟。1、添加Swagger NuGet 包dotnet add package Swashbuckle.AspNetCore2、添加Swa…

【iOS】源碼閱讀(六)——方法交換

文章目錄方法交換什么是Method-Swizzling方法交換核心API**1. 獲取方法對象****2. 添加/替換方法實現****3. 交換方法實現****4. 獲取方法信息****5. 修改方法實現****使用示例&#xff1a;完整的 Method-Swizzling 流程****注意事項**使用方法交換注意事項線程安全方法交換的影…

mysql運維問題解決:MySQL主從延遲(鎖阻塞與讀寫分離)

小亦平臺會持續給大家科普一些運維過程中常見的問題解決案例&#xff0c;運維朋友們可以在常見問題及解決方案專欄查看更多案例 問題概述 告警事件&#xff1a; 2023-07-28 03:31:39.571 首次觸發主從延遲告警&#xff08;延遲1515秒&#xff09;2023-07-28 07:41:37 告警解除…

SSH 密鑰

什么是 SSH 密鑰 SSH 密鑰就像是你電腦的“身份證”和“鑰匙”&#xff0c; 用來安全登錄另一臺電腦&#xff08;服務器&#xff09;&#xff0c;而不需要每次輸入密碼。SSH 密鑰是一種安全登錄遠程服務器的方式&#xff0c;由一對加密的“鑰匙”組成&#xff1a;一個公鑰 一個…