第39期：MySQL 時間類分割槽寫 SQL 注意事項

愛可生雲資料庫發表於2022-02-22
MySql

上篇《MySQL 時間類分割槽具體實現》介紹了時間類分割槽的實現方法，本篇是對上篇的一個延伸，介紹基於此類分割槽的相關 SQL 編寫注意事項。

對於分割槽表的檢索無非有兩種，一種是帶分割槽鍵，另一種則不帶分割槽鍵。一般來講檢索條件帶分割槽鍵則執行速度快，不帶分割槽鍵則執行速度變慢。這種結論適應於大多數場景，但不能以偏概全，要針對不同的分割槽表定義來寫最合適的 SQL 語句。用分割槽表的目的是為了減少 SQL 語句檢索時的記錄數，如果沒有達到預期效果，則分割槽表只能帶來副作用。 接下來我列舉幾個經典的 SQL 語句：

細心的讀者在閱讀完上篇可能心中就有一些疑問，基於表 ytt_p1 的 SQL 語句如下：

select count(*) from ytt_pt1 where log_date >='2018-01-01' and log_date <'2019-01-01';

同樣是分割槽表 ytt_pt1_month1 ，基於這張表的 SQL 語句如下：

select count(*) from ytt_pt1_month1 where log_date in ('2020-01-01','2020-01-02','2020-01-03','2020-01-04','2020-01-05','2020-01-06','2020-01-07','2020-01-08','2020-01-09','2020-01-10','2020-01-11','2020-01-12','2020-01-13','2020-01-14','2020-01-15');

兩張表的檢索需求類似，為何寫法差異不小？ 後者為何要寫成列表形式而不繼續寫成簡單的範圍檢索形式？帶著這點疑問，我們繼續。

MySQL 針對分割槽表有一項優化技術叫 partition pruning ，翻譯過來就是分割槽裁剪。其大致含義是 MySQL 會根據 SQL 語句的過濾條件對應的分割槽函式進行計算，並把計算結果穿透到底層分割槽表從而減小掃描記錄數的一種優化策略。對於時間型別（DATE,TIMESTAMP,TIME，DATETIME），MySQL 僅支援部分函式的分割槽裁剪：to_days，to_seconds，year，unix_timestamp。那麼我們再來看之前的疑問：表 ytt_pt1_month1 分割槽函式為 month ，MySQL 分割槽表雖然支援 month 函式，但是分割槽裁剪技術卻不包含這個函式。 接下來，分兩部分來介紹本篇內容。

第一、來體驗下 MySQL 的分割槽裁剪技術，新建一張表 pt_pruning：分割槽函式為 to_days 。
create table pt_pruning (
id int,
r1 int,
r2 int,
log_date date)
partition by range(to_days(log_date))
(
PARTITION p_01 VALUES LESS THAN (to_days('2020-02-01')) ENGINE = InnoDB,
 PARTITION p_02 VALUES LESS THAN (to_days('2020-03-01')) ENGINE = InnoDB,
 PARTITION p_03 VALUES LESS THAN (to_days('2020-04-01')) ENGINE = InnoDB,
 PARTITION p_04 VALUES LESS THAN (to_days('2020-05-01')) ENGINE = InnoDB,
 PARTITION p_05 VALUES LESS THAN (to_days('2020-06-01')) ENGINE = InnoDB,
 PARTITION p_06 VALUES LESS THAN (to_days('2020-07-01')) ENGINE = InnoDB,
 PARTITION p_07 VALUES LESS THAN (to_days('2020-08-01')) ENGINE = InnoDB,
 PARTITION p_08 VALUES LESS THAN (to_days('2020-09-01')) ENGINE = InnoDB,
 PARTITION p_09 VALUES LESS THAN (to_days('2020-10-01')) ENGINE = InnoDB,
 PARTITION p_10 VALUES LESS THAN (to_days('2020-11-01')) ENGINE = InnoDB,
 PARTITION p_11 VALUES LESS THAN (to_days('2020-12-01')) ENGINE = InnoDB,
 PARTITION p_12 VALUES LESS THAN (to_days('2021-01-01')) ENGINE = InnoDB,
 PARTITION p_max VALUES LESS THAN MAXVALUE ENGINE = InnoDB
)
此表包含2020年一整年的資料，大概100W條，此處省略造資料過程。
(localhost:ytt)<mysql>select min(log_date),max(log_date),count(*) from pt_pruning;
+---------------+---------------+----------+
| min(log_date) | max(log_date) | count(*) |
+---------------+---------------+----------+
| 2020-01-02    | 2020-12-31    |  1000000 |
+---------------+---------------+----------+
1 row in set (0.72 sec)
分別執行下面幾條 SQL :

SQL 1：求日期包含 '2020-01-02' 的記錄條數。

SQL 1： select count(*) from pt_pruning where log_date <= '2020-01-02';

SQL 2 和 SQL 3 ： 求2020年1月份的記錄條數。

SQL 2： select count(*) from pt_pruning where log_date < '2020-02-01';

SQL 3:  select count(*) from pt_pruning where log_date between '2020-01-01' and '2020-01-31';

SQL 1 和 SQL 2 執行時間為0.04秒，SQL 3 執行時間為0.06秒。 在沒有使用索引的條件下效果還是比較理想的。

(localhost:ytt)<mysql> select count(*) from pt_pruning where log_date <= '2020-01-02';
+----------+
| count(*) |
+----------+
|     2621 |
+----------+
1 row in set (0.04 sec)

(localhost:ytt)<mysql>select count(*) from pt_pruning where log_date < '2020-02-01';
+----------+
| count(*) |
+----------+
|    82410 |
+----------+
1 row in set (0.04 sec)

(localhost:ytt)<mysql>select count(*) from pt_pruning where log_date between '2020-01-01' and '2020-01-31';
+----------+
| count(*) |
+----------+
|    82410 |
+----------+
1 row in set (0.06 sec)
所以切記使用 MySQL 分割槽裁剪技術規定的分割槽函式來建立分割槽表，這樣寫 SQL 就會相對隨意些。如果由於歷史原因，分割槽表沒有使用以上規定的分割槽函式，可以有以下兩項可能的優化策略：
  1. 手工改 SQL 語句讓其達到最優。
  2. 加 HINT 來提示 MySQL 使用具體的分割槽。
第二、如果分割槽表使用的分割槽函式未滿足 MySQL 分割槽裁剪技術的規則，該如何優化此類 SQL 語句？

為避免和上篇內容混淆，建張新表 pt_month，複製表 ytt_pt1_month1 的表定義。表 pt_month 和表 pt_pruning 一樣，存放了2020年一整年的記錄，總條數也為100W。

(localhost:ytt)<mysql>select min(log_date),max(log_date),count(*) from pt_month;
+---------------+---------------+----------+
| min(log_date) | max(log_date) | count(*) |
+---------------+---------------+----------+
| 2020-01-02    | 2020-12-31    |  1000000 |
+---------------+---------------+----------+
1 row in set (0.72 sec)

再次執行之前的三條 SQL ，並把表名替換為 pt_month ：

SQL 1 執行時間為1.26秒，相比之前慢了不少。檢視執行計劃，發現未使用 MySQL 分割槽裁剪技術，掃描了不必要的表分割槽。（這裡是全部表分割槽）

(localhost:ytt)<mysql>select count(*) from pt_month where log_date <= '2020-01-02';
+----------+
| count(*) |
+----------+
|     2621 |
+----------+
1 row in set (1.26 sec)

(localhost:ytt)<mysql>explain 
    -> select count(*) from pt_month where log_date <= '2020-01-02'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pt_month
   partitions: p_01,p_02,p_03,p_04,p_05,p_06,p_07,p_08,p_09,p_10,p_11,p_max
...
         rows: 992805
     filtered: 33.33
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

接下來對 SQL 1 進行一個簡單的優化：既然是求日期為’2020-01-02‘ 那天的記錄，那就不要使用<=來過濾，直接用=過濾：執行時間0.03秒。 檢視執行計劃，改後的 SQL 直接定位到表分割槽 p_01 ，達到了分割槽裁剪的效果。

(localhost:ytt)<mysql>select count(*) from pt_month where log_date = '2020-01-02';
+----------+
| count(*) |
+----------+
|     2621 |
+----------+
1 row in set (0.03 sec)

(localhost:ytt)<mysql>explain 
    -> select count(*) from pt_month where log_date = '2020-01-02'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pt_month
   partitions: p_01
         type: ALL
...
         rows: 82522
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

繼續執行 SQL 2 和 SQL 3 ：執行時間都是1秒到2秒之間，效率很差，也未使用 MySQL 分割槽裁剪技術。

(localhost:ytt)<mysql>select count(*) from pt_month where log_date < '2020-02-01';
+----------+
| count(*) |
+----------+
|    82410 |
+----------+
1 row in set (1.35 sec)

(localhost:ytt)<mysql>select count(*) from pt_month where log_date between '2020-01-01' and '2020-01-31';
+----------+
| count(*) |
+----------+
|    82410 |
+----------+
1 row in set (1.93 sec)

來繼續優化 SQL 2 和 SQL 3，由於兩個需求一致，可以把範圍檢索改為指定列表檢索：執行時間僅為0.04秒。

(localhost:ytt)<mysql>select count(*) from pt_month where log_date in ('2020-01-01','2020-01-02','2020-01-03','2020-01-04','2020-01-05','2020-01-06','2020-01-07','2020-01-08','2020-01-09','2020-01-10','2020-01-11','2020-01-12','2020-01-13','2020-01-14','2020-01-15','2020-01-16','2020-01-17','2020-01-18','2020-01-19','2020-01-20','2020-01-21','2020-01-22','2020-01-23','2020-01-24','2020-01-25','2020-01-26','2020-01-27','2020-01-28','2020-01-29','2020-01-30','2020-01-31');
+----------+
| count(*) |
+----------+
|    82410 |
+----------+
1 row in set (0.04 sec)

把範圍查詢改為 IN 列表後，效率得到很大提升，查詢計劃顯示 MySQL 優化器只在分割槽 p_01 上檢索記錄。

...
   partitions: p_01
...

除了改造 SQL 語句，還可以給語句加 HINT 的方式來讓 MySQL 使用分割槽裁剪技術：比如給 SQL 2 加上 HINT 後，執行時間為0.04秒，和之前改造後的語句執行效率相當。

(localhost:ytt)<mysql>select count(*) from pt_month partition (p_01) where log_date < '2020-02-01';
+----------+
| count(*) |
+----------+
|    82410 |
+----------+
1 row in set (0.04 sec)
總結：

如果由於歷史原因分割槽表未使用 MySQL 分割槽裁剪技術，可以按照以下規則來手動對分割槽表進行裁剪優化：

  1. select * from tbname where partition_key = value;
  2. select * from tbname where partition_key in (value1,value2,...,valueN);
  3. 以上兩種規則對於多表 JOIN 依然適用。

相關文章