MySQL SQL語句執行過程詳解
作為面試官,我來詳細剖析一條SQL語句在MySQL中的完整執行過程,這是每個后端開發者都應該掌握的核心知識。
一、連接階段
- 建立連接
- 客戶端通過TCP/IP協議與MySQL服務器建立連接(默認3306端口)
- 服務器驗證用戶名、密碼和權限
- 連接管理器創建線程處理該連接(線程池優化)
- 查詢緩存(MySQL 8.0已移除)
- 如果是SELECT語句,先檢查查詢緩存
- 命中緩存則直接返回結果(緩存key是完整SQL)
- 注意:表數據變更加粗樣式會使相關緩存失效
二、解析與優化階段
- 解析器處理
- 詞法分析:將SQL拆分為token(關鍵字、標識符等)
- 語法分析:檢查SQL是否符合語法規則
- 生成解析樹(語法樹)
- 預處理器
- 檢查表和列是否存在
- 檢查權限
- 視圖展開等轉換操作
- 查詢優化器
- 基于成本優化(CBO)選擇最優執行計劃加粗樣式
- 考慮因素:索引、表大小、JOIN順序等
- 生成執行計劃(可通過EXPLAIN查看)
三、執行階段
- 執行引擎處理
- 調用存儲引擎API執行計劃
- 不同存儲引擎(如InnoDB)實現不同
- InnoDB引擎處理流程
- 緩沖池(Buffer Pool)檢查:首先檢查所需數據頁是否在內存中
- 磁盤讀取:若不在緩沖池,從磁盤加載數據頁到內存
- 鎖機制:根據隔離級別加鎖(共享鎖/排他鎖)
- 事務處理:寫入undo log(用于回滾)和redo log(用于恢復)
- 返回結果:將結果集放入網絡緩沖區
四、返回結果
- 結果返回客戶端
- 結果集通過網絡協議返回
- 客戶端逐步接收并處理數據
- 連接保持或關閉(取決于配置)
MySQL存儲引擎區別詳解
一、MySQL主要存儲引擎
- InnoDB (MySQL 5.5+默認引擎)
- MyISAM (MySQL 5.5前默認引擎)
- MEMORY (內存引擎)
- ARCHIVE (歸檔引擎)
- CSV (CSV文件引擎)
- BLACKHOLE (黑洞引擎)
- FEDERATED (聯邦引擎)
- MERGE (MyISAM集合引擎)
二、核心引擎對比(InnoDB vs MyISAM)
特性 | InnoDB | MyISAM |
---|---|---|
事務支持 | 支持ACID事務 | 不支持 |
鎖粒度 | 行級鎖 | 表級鎖 |
外鍵支持 | 支持 | 不支持 |
崩潰恢復 | 有redo log保證 | 無保證 |
MVCC | 支持多版本并發控制 | 不支持 |
存儲限制 | 64TB | 256TB |
緩存機制 | 緩沖池緩存數據和索引 | 只緩存索引 |
全文索引 | MySQL 5.6+支持 | 支持 |
壓縮表 | 支持 | 支持 |
適用場景 | OLTP、高并發寫 | OLAP、讀密集型、數據倉庫 |
三、引擎特點
引自騰訊云 MySQL 的存儲引擎有哪些?它們之間有什么區別?
- InnoDB
- 事務支持:InnoDB 是默認的存儲引擎,支持事務處理(ACID 屬性)。
- 行級鎖:使用行級鎖,適合高并發環境。
- 外鍵支持:支持外鍵約束。
- 崩潰恢復:具有自動崩潰恢復功能。
- 性能:在讀寫混合加粗樣式負載下表現良好。
- 存儲:數據存儲在表空間中,支持表空間的動態擴展加粗樣式。
- MyISAM
- 不支持事務:不支持事務處理。
- 表級鎖:使用表級鎖,不適合高并發寫操作。
- 全文索引:支持全文索引,適合搜索引擎等應用。
- 性能:在只讀或讀多寫少的場景下性能較好。
- 存儲:數據存儲在磁盤文件中,每個表對應一個 .MYD 文件(數據文件)和一個 .MYI 文件(索引文件)。
- Memory
- 內存存儲:數據存儲在內存中,速度快但不持久。
- 臨時數據:適合存儲臨時數據,如緩存、中間結果等。
- 不支持事務:不支持事務處理。
- 表級鎖:使用表級鎖。
- 性能:在需要高速讀取的場景下性能非常好。
- Archive
- 壓縮存儲:數據以壓縮格式存儲,占用空間小。
- 只讀:主要用于歸檔和日志記錄,不支持更新操作。
- 不支持索引:不支持索引,查詢性能較差。
- 性能:適合存儲大量歷史數據,節省存儲空間。
- CSV
- CSV 文件:數據存儲在 CSV 文件中,可以直接用文本編輯器打開。
- 簡單:適合簡單的數據導入導出操作。
- 不支持事務:不支持事務處理。
- 性能:性能較低,不適合大規模數據操作。
- Blackhole
- 黑洞存儲:所有寫入的數據都會被丟棄,不實際存儲任何數據。
- 日志記錄:主要用于測試和日志記錄。
- 性能:寫操作非常快,因為數據實際上沒有被存儲。
- Federated
- 遠程表:允許訪問其他 MySQL 服務器上的表,實現分布式數據庫。
- 性能:性能受網絡延遲影響較大。
- 不支持事務:不支持事務處理。
- Merge
- 合并多個表:可以將多個 MyISAM 表合并成一個邏輯表。
- 性能:適合需要對多個表進行統一查詢的場景。
- 不支持事務:不支持事務處理。
https://github.com/0voice