文章目錄
- MySQL的 ORDER BY 執行流程
- 示例表和查詢語句
- 執行流程
- 全字段排序
- Rowid 排序
- 全字段排序 VS rowid排序
- 聯合索引優化
- 覆蓋索引優化
- 小結
- 思考題
- 問題
- 執行過程中是否需要排序？
- 如何在數據庫端實現不排序？
- 實現分頁需求
- 使用`ORDER BY RAND()`
- 內存臨時表與磁盤臨時表
- 隨機選擇算法的優化
- 實際應用和進一步優化
- 小結
- 思考題
- 回答
- 示例優化：
- 誤區
- 案例一：條件字段函數操作
- 案例二：隱式類型轉換
- 案例三：隱式字符編碼轉換
- 總結優化策略
- 行動建議
- 為什么單行查詢在MySQL中執行緩慢
- 背景
- 第一類：查詢長時間不返回
- 等待MDL鎖
- 等待表Flush
- 等待行鎖
- 第二類：查詢執行慢
- 全表掃描
- 一致性讀導致慢查詢
- 解決查詢慢的策略
- 小結
- 思考題
- 幻讀
- 幻讀的定義
- 幻讀的問題
- 解決幻讀的策略
- 幻讀的業務影響
- 總結
MySQL的 ORDER BY 執行流程
在開發應用時，經常需要根據指定字段排序來顯示結果。以下是關于 MySQL 中 ORDER BY 的執行流程及影響因素的總結。
示例表和查詢語句
假設有一個市民表，定義如下：
CREATE TABLE `t` (`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;
查詢城市為“杭州”的市民名字，并按姓名排序返回前1000個結果的SQL語句：
SELECT city, name, age FROM t WHERE city='杭州' ORDER BY name LIMIT 1000;
執行流程
全字段排序
當執行一個包含ORDER BY的查詢時，如果不使用適當的索引，MySQL會使用全字段排序：
- 初始化排序緩沖區：
sort_buffer被用來存儲需要排序的列。 - 從索引中檢索數據：基于
city索引檢索滿足條件的記錄，然后加載相應的字段到sort_buffer。 - 執行排序：在
sort_buffer中對數據進行排序。 - 輸出結果：根據排序結果輸出前1000條數據。
Rowid 排序
當單行數據量過大時，MySQL可能采用rowid排序以節約內存：
- 排序緩沖區較小：只存儲關鍵的排序字段和主鍵id。
- 數據檢索和排序：檢索并排序關鍵字段，使用主鍵再次查詢以獲取完整數據。
- 結果輸出：根據排序后的id順序輸出結果。
全字段排序 VS rowid排序
在內存足夠的情況下優先選擇全字段排序，否則rowid排序
體現了MySQL的一個設計思想： 如果內存夠，就要多利用內存，盡量減少磁盤訪問。
聯合索引優化
- 創建聯合索引：
ALTER TABLE t ADD INDEX city_user(city, name);
- 查詢流程簡化：利用聯合索引
city_user(city, name)，天然保證按 name 遞增排序，避免排序操作，流程如下：- 從索引(city, name)中找到第一個滿足 city=‘杭州’ 條件的記錄。
- 取出該記錄中的 city、name、age 直接返回。
- 重復上述步驟，直到取到1000條記錄或不滿足條件時停止。
覆蓋索引優化
- 創建覆蓋索引：
ALTER TABLE t ADD INDEX city_user_age(city, name, age);
- 查詢流程進一步簡化：利用覆蓋索引避免回表操作，流程如下：
- 從索引(city, name, age)中找到第一個滿足 city=‘杭州’ 條件的記錄，直接取出并返回。
- 重復上述步驟，直到取到1000條記錄或不滿足條件時停止。
小結
MySQL 的排序操作有多種實現方式，具體選擇取決于查詢條件和索引情況。為了提高性能，建議合理利用聯合索引和覆蓋索引，減少排序操作和磁盤訪問。
思考題
問題
假設表中已有聯合索引 city_name(city, name)，查詢杭州和蘇州兩個城市的市民名字，并按名字排序，顯示前100條記錄：
SELECT * FROM t WHERE city IN ('杭州', '蘇州') ORDER BY name LIMIT 100;
執行過程中是否需要排序？
由于查詢條件包含多個城市，合并后可能無序，無法利用單一的聯合索引順序，MySQL 需要對結果集進行排序。
如何在數據庫端實現不排序？
每個城市單獨查詢
可以創建聯合索引 city_name(city, name) 并結合分區查詢：
(SELECT * FROM t WHERE city = '杭州' ORDER BY name LIMIT 100)
UNION ALL
(SELECT * FROM t WHERE city = '蘇州' ORDER BY name LIMIT 100)
ORDER BY name LIMIT 100;
實現分頁需求
對于第101頁，即查詢第10000至10099條記錄：
(SELECT * FROM t WHERE city = '杭州' ORDER BY name LIMIT 10000, 100)
UNION ALL
(SELECT * FROM t WHERE city = '蘇州' ORDER BY name LIMIT 10000, 100)
ORDER BY name LIMIT 10000, 100;
這種方式避免了單次大數據量排序，提高查詢效率。
本文主要講述了如何在MySQL中實現隨機選擇單詞的功能，并分析了使用ORDER BY RAND()導致的性能問題，提出了幾種優化方法。
使用ORDER BY RAND()
在初步嘗試中，可以使用SELECT word FROM words ORDER BY RAND() LIMIT 3;來隨機選擇三個單詞。這種方法雖簡單，但隨著數據量的增大，性能問題逐漸顯現：
- MySQL需要在內存中創建臨時表并對其進行隨機排序，這一過程涉及大量的數據讀取和排序操作。
- 根據
EXPLAIN命令的輸出，此查詢需要使用臨時表和文件排序，顯著增加了查詢的資源消耗。
內存臨時表與磁盤臨時表
文章進一步解釋了MySQL在處理ORDER BY RAND()時可能使用的兩種臨時表：
- 內存臨時表：如果臨時表的大小未超過
tmp_table_size配置的限制，則使用內存中的臨時表。 - 磁盤臨時表：如果臨時表大小超過限制，則轉為在磁盤上創建臨時表，這會進一步降低性能，由
internal\_tmp\_disk\_storage\_engine控制。
隨機選擇算法的優化
文章提出了兩種優化隨機選擇單詞的方法：
-
隨機算法1：
- 查詢表的主鍵ID的最大和最小值。
- 生成一個介于最大和最小ID之間的隨機數。
- 查詢ID大于或等于該隨機數的第一條記錄。
- 這種方法雖然快速，但不夠均勻，特別是當ID存在空洞時。
-
隨機算法2：
- 計算表中的總行數。
- 生成一個隨機數來選擇行號。
- 使用
LIMIT語句直接跳過前N行，返回第N+1行。 - 這種方法提供了更均勻的隨機性，但性能開銷大于算法1，盡管依然優于
ORDER BY RAND()。
實際應用和進一步優化
對于實際應用，尤其是行數較多的表，推薦使用隨機算法2，并在應用層進行SQL語句的構建和執行。此外，對于隨機選擇多個單詞的需求，可以通過生成多個隨機行號并分別查詢來實現，盡管這會增加數據庫的訪問次數。
小結
在設計數據庫交互時，考慮查詢的性能影響是非常關鍵的。直接使用ORDER BY RAND()可能引起嚴重的性能問題，尤其是在數據量較大的情況下。通過理解MySQL的內部工作原理和利用優化的查詢方法，可以顯著提高應用的響應速度和整體性能。
思考題
- 如何進一步減少隨機算法3的掃描行數？
- 是否有可能通過改進SQL結構或使用特定的數據庫功能（如更復雜的索引策略）來優化隨機選擇的效率？
回答
要減少隨機算法3的掃描行數，并優化隨機選擇的效率，可以采取以下措施：
-
使用單查詢優化：
- 在一次查詢中獲取多個隨機行。例如：
SELECT * FROM words WHERE id >= @X ORDER BY id LIMIT 3; - 這樣可以減少多次掃描。
- 在一次查詢中獲取多個隨機行。例如：
-
利用表統計信息：
- 獲取表的總行數并計算隨機偏移量。
SELECT COUNT(*) INTO @C FROM words; SET @Y = FLOOR(@C * RAND()); SELECT * FROM words LIMIT @Y, 1; - 更準確地生成隨機數以減少空洞。
- 獲取表的總行數并計算隨機偏移量。
-
索引優化：
- 創建復合索引，提高查詢速度。
-
應用層優化：
- 緩存部分單詞表，應用層隨機選擇，減少數據庫訪問。
- 預加載單詞列表到應用層進行隨機選擇。
-
利用數據庫特性：
- 使用數據庫特定的優化功能，如隨機選擇函數。
示例優化：
使用單查詢獲取多個隨機行，并利用表統計信息：
SELECT COUNT(*) INTO @C FROM words;
SET @Y1 = FLOOR(@C * RAND());
SET @Y2 = FLOOR(@C * RAND());
SET @Y3 = FLOOR(@C * RAND());
SELECT * FROM words WHERE id >= @Y1 LIMIT 1;
SELECT * FROM words WHERE id >= @Y2 LIMIT 1;
SELECT * FROM words WHERE id >= @Y3 LIMIT 1;
這樣可以減少掃描行數，提高效率。
誤區
案例一：條件字段函數操作
- 問題描述：在使用函數處理索引字段（如
month(t_modified)）時，可能會破壞值的有序性，優化器會放棄走樹搜索。MySQL無法利用索引進行快速查找，導致性能下降。 - 解決方案：避免在查詢條件中對索引字段使用函數。改用基于字段本身的范圍查詢，例如使用
t_modified BETWEEN '2016-7-1' AND '2016-7-31'。
案例二：隱式類型轉換
- 問題描述：當查詢條件中的數據類型不匹配字段類型時（如整數與字符串比較
tradeid = 110717），會觸發類型轉換，從而導致索引失效。 - 解決方案：確保查詢條件中的數據類型與數據庫字段的類型一致，避免隱式類型轉換。
案例三：隱式字符編碼轉換
- 問題描述：不同表之間的字段字符編碼不一致（如
utf8與utf8mb4），在進行連接查詢時無法使用索引。
這個設定很好理解，utf8mb4是utf8的超集。類似地，在程序設計語言里面，做自動類型轉換的時候，為了避免數據在轉換過程中由于截斷導致數據錯誤，也都是“按數據長度增加的方向”進行轉換的。
- 解決方案：
- 調整編碼：調整相關字段的字符編碼，使其一致。
- 顯式轉換：在查詢時使用
CONVERT函數顯式進行字符編碼轉換。
總結優化策略
- 避免在索引字段上使用函數：使用索引字段本身而非經過函數處理的結果作為查詢條件。
- 保持數據類型一致：在查詢中使用與數據庫字段相同的數據類型。
- 統一字符編碼：確保關聯查詢中涉及的字段具有相同的字符編碼，或在查詢中指定正確的字符編碼轉換。
行動建議
- 在業務代碼升級時，對可能出現的新SQL語句進行
EXPLAIN分析，是一個避免性能問題的好習慣。 - 共享實際遇到的性能問題及其解決方案，可以幫助團隊成員學習并提高處理類似問題的能力。
為什么單行查詢在MySQL中執行緩慢
背景
- 常見的理解是，復雜查詢或大數據量返回會導致性能問題，但某些單行查詢同樣會非常緩慢。
- 排除數據庫整體負載高的情況，本文專注于單行查詢的特定性能問題。
第一類：查詢長時間不返回
等待MDL鎖
- 問題：查詢被表的元數據鎖（MDL）阻塞。
- 診斷：使用
SHOW PROCESSLIST查看狀態為Waiting for table metadata lock。 - 解決：通過
sys.schema_table_lock_waits找到阻塞的線程ID并KILL掉。
等待表Flush
- 問題：查詢被
FLUSH TABLES操作阻塞。 - 診斷：
SHOW PROCESSLIST顯示狀態為Waiting for table flush。 - 解決：找到并終止執行
FLUSH TABLES的會話。
等待行鎖
- 問題：查詢被另一個事務持有的行鎖阻塞。
- 診斷：
SHOW PROCESSLIST顯示狀態為Waiting for lock. - 解決：使用
sys.innodb_lock_waits查找持有鎖的事務，并KILL掉。
第二類：查詢執行慢
全表掃描
- 問題：查詢沒有使用索引，導致全表掃描。
- 示例：
SELECT * FROM t WHERE c=50000 LIMIT 1; - 解決：添加適當的索引。
一致性讀導致慢查詢
- 問題：一致性讀導致掃描大量回滾日志（undo log）。
- 示例：
SELECT * FROM t WHERE id=1; - 復現：一個事務進行大量更新，導致查詢需要回滾大量操作。
- 解決：優化事務設計，避免生成大量的undo log。
解決查詢慢的策略
- 使用索引：確保查詢條件使用適當的索引。
- 優化事務設計：避免長事務和大事務，減少鎖爭用。
- 監控和診斷工具：使用
SHOW PROCESSLIST、sys.innodb_lock_waits等工具診斷問題。
小結
即使是簡單的單行查詢也可能因多種原因導致性能問題。了解和識別這些問題的原因是關鍵，合理使用工具和策略可以顯著提升查詢效率。
思考題
考慮查詢SELECT * FROM t WHERE c = 5 FOR UPDATE;，分析它如何加鎖以及鎖釋放的時機，討論可能的性能影響。
幻讀
幻讀的定義
幻讀是在數據庫事務處理中遇到的一種現象，它發生在一個事務（Transaction）在執行過程中進行兩次相同的查詢，卻得到了不同的結果。這種情況通常是由于在這兩次查詢間有其他事務插入或修改了數據所致。
幻讀在“當前讀”下才會出現
幻讀的問題
幻讀主要問題在于它違反了事務的隔離性，特別是在可重復讀（Repeatable Read）隔離級別下。在這個級別，預期事務能夠多次讀取同樣的數據行并得到相同的結果，但幻讀破壞了這一期望。
在上文中提到的例子中，盡管在事務開始時鎖定了d=5的行（id=5），事務中途對其他行（如id=0或id=1）的修改和新增行（id=1新增），導致了幻讀，因為這些修改和添加的數據行在后續的查詢中被看到了，違反了預期的事務隔離性。
解決幻讀的策略
為了解決幻讀問題，MySQL的InnoDB存儲引擎引入了間隙鎖（Gap Locks）的概念，除了對查詢到的數據行加鎖外，還對數據行之間的間隙加鎖。這種鎖策略不僅鎖定數據行，也鎖定行之間的間隙，防止其他事務在這些間隙中插入新的行。間隙鎖和行鎖合稱next-key lock，每個next-key lock是前開后閉區間。
例如，如果事務A查詢了所有d=5的數據行并對其加鎖，通過間隙鎖機制，事務B將無法插入一個新的d=5的行，因為這將需要在已鎖定的間隙中插入數據，而這是被禁止的。
幻讀的業務影響
幻讀可能會導致數據不一致性，影響業務邏輯的正確執行。例如，如果一個事務基于查詢結果執行操作（如計算總和或更新數據），由于幻讀導致的數據變化可能會使得最終結果不正確或事務失敗。
總結
在處理數據庫事務時，了解和應對幻讀是確保數據一致性和事務隔離性的關鍵。通過使用適當的隔離級別和理解數據庫的鎖機制，可以有效地管理和減少幻讀帶來的問題。在設計數據庫操作和事務邏輯時，合理選擇隔離級別和理解其對應的鎖策略對于開發穩定可靠的應用程序至關重要。
