mysql 能通過分區裁剪和并行查詢提升性能。1. 分區裁剪使查詢僅掃描必要分區，可通過 explain 輸出的 partitions 列確認，若顯示具體分區則表示生效；2. 并行查詢利用多核 cpu 加速處理，需 mysql 8.0+、innodb 引擎及滿足查詢類型與資源條件；3. 分區裁剪失效常見于無分區鍵條件、使用函數表達式、or 條件復雜、數據類型不匹配或動態 sql；4. 優化并行查詢包括設置 max_parallel_degree、簡化查詢、使用索引和定期分析表。
分區表查詢的核心在于，MySQL能否聰明地只掃描必要的分區，以及能否利用多個核心并行處理查詢。
解決方案
MySQL 分區表查詢優化的關鍵在于分區裁剪和并行查詢。分區裁剪是指查詢優化器能夠識別出只需要掃描部分分區，從而避免全表掃描，提升查詢效率。并行查詢則是指利用多核 CPU 并行處理查詢任務，進一步縮短查詢時間。
如何確認MySQL使用了分區裁剪？
要確認 MySQL 是否使用了分區裁剪，最簡單的方法是查看 EXPLaiN 語句的輸出。在 EXPLAIN 的輸出中，如果 partitions 列顯示了被掃描的分區列表，而不是 ALL，則表示 MySQL 成功使用了分區裁剪。如果 partitions 列顯示為 NULL，則表示沒有使用分區表。
例如，假設我們有一個名為 orders 的分區表，按照 order_date 列進行范圍分區，分區名為 p202301，p202302，p202303 等。執行以下查詢：
EXPLAIN SELECT * FROM orders WHERE order_date BETWEEN '2023-02-01' AND '2023-02-28';
如果 EXPLAIN 輸出的 partitions 列顯示為 p202302，則表示 MySQL 只掃描了 p202302 分區，實現了分區裁剪。
此外，還可以通過 optimizer_trace 功能更詳細地了解優化器的決策過程，但這通常需要更深入的 MySQL 知識。
哪些情況下分區裁剪會失效？
分區裁剪并非總是有效，在某些情況下可能會失效，導致全表掃描。常見的原因包括：
-
查詢條件不包含分區鍵： 如果查詢條件中沒有包含分區鍵，MySQL 無法確定需要掃描哪些分區，只能掃描所有分區。例如，如果 orders 表按照 order_date 分區，但查詢條件是 WHERE customer_id = 123，則分區裁剪會失效。
-
分區鍵使用了函數或表達式： 如果查詢條件中對分區鍵使用了函數或表達式，MySQL 難以進行優化。例如，WHERE YEAR(order_date) = 2023 可能會導致分區裁剪失效，因為 MySQL 需要計算每個分區的 YEAR(order_date) 值才能確定是否需要掃描。
-
查詢條件包含 OR： 復雜的 OR 條件可能會導致分區裁剪失效，尤其是在 OR 連接的條件涉及不同的分區鍵時。
-
數據類型不匹配： 如果查詢條件中數據類型與分區鍵的數據類型不匹配，MySQL 可能會進行隱式類型轉換，導致分區裁剪失效。
-
動態 SQL： 在某些動態 SQL 場景下，MySQL 無法在編譯時確定需要掃描哪些分區，導致運行時分區裁剪失效。
如何開啟或優化MySQL并行查詢？
MySQL 8.0 引入了并行查詢功能，可以利用多核 CPU 并行處理查詢任務。但并非所有查詢都能自動并行化，需要滿足一定的條件。
默認情況下，并行查詢可能是關閉的。可以通過以下參數進行控制：
- optimizer_switch：控制優化器行為的開關，其中包含 prefer_ordering_index 和 mrr_cost_based 等選項。并行查詢的啟用與這些選項有關。
- max_parallel_degree：設置并行查詢的最大線程數。
要開啟并行查詢，需要確保以下條件滿足：
- MySQL 版本： 必須是 MySQL 8.0 或更高版本。
- 存儲引擎： 僅支持 InnoDB 存儲引擎。
- 查詢類型： 并非所有查詢都能并行化。通常，涉及大量數據掃描的查詢（例如全表掃描、大表連接）更容易并行化。
- 系統資源： 需要有足夠的 CPU 核心和內存資源。
即使滿足上述條件，MySQL 也可能不會自動并行化查詢。優化器會根據查詢的成本估算來決定是否進行并行化。可以通過以下方式進行優化：
- 增加 max_parallel_degree： 適當增加并行查詢的最大線程數，可以提高并行度。但需要注意，過高的線程數可能會導致資源競爭，反而降低性能。
- 優化查詢語句： 盡量簡化查詢語句，避免復雜的子查詢和連接。
- 使用索引： 合理使用索引可以減少數據掃描量，提高查詢效率，也更有利于并行化。
- 分析表： 定期執行 ANALYZE table 語句，更新表的統計信息，幫助優化器做出更準確的決策。
此外，還可以通過 optimizer_trace 功能查看優化器是否選擇了并行查詢，以及并行化的程度。
