第41期：MySQL 雜湊分割槽表

愛可生雲資料庫發表於2022-05-17
MySql

提到分割槽表，一般按照範圍（range）來對資料拆分居多，以雜湊來對資料拆分的場景相來說有一定侷限性，不具備標準化。接下來我用幾個示例來講講 MySQL 雜湊分割槽表的使用場景以及相關改造點。

對於雜湊分割槽表，最通俗的方法就是 hash 單個欄位，比如下面表 hash_t1（存有500W行記錄），按照自增 ID 來做 HASH ，分割槽數目為 1024 ：

mysql:ytt_new> show create table hash_t1\G
*************************** 1. row ***************************
       Table: hash_t1
Create Table: CREATE TABLE `hash_t1` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `r1` int DEFAULT NULL,
  `log_date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY HASH (`id`)
PARTITIONS 1024 */
1 row in set (0.00 sec)

mysql:ytt_new> select count(*) from hash_t1;
+----------+
| count(*) |
+----------+
|  5000000 |
+----------+
1 row in set (2.43 sec)

表 hash_t1 的分割槽方式很好理解，按照 ID 來對資料進行 HASH 拆分，也就是按照分割槽數量求模， 類似於 hash(mod(id,1024)) ，資料分佈非常均勻。

mysql:ytt_new> select max(table_rows),min(table_rows) from information_schema.partitions where table_name = 'hash_t1';
+-----------------+-----------------+
| max(table_rows) | min(table_rows) |
+-----------------+-----------------+
|            4883 |            4882 |
+-----------------+-----------------+
1 row in set (0.04 sec)

接下來考慮以下幾條 SQL 語句：

SQL 1：select count(*) from hash_t1 where id = 1;

SQL 2：select count(*) from hash_t1 where id in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15);

SQL 3：select count(*) from hash_t1 where  id <=1；

SQL 4：select count(*) from hash_t1 where id <=15;

SQL 1 和 SQL 2 非常適合檢索雜湊分割槽表，SQL 3 和 SQL 4 就不太適合。

SQL 1 的執行計劃：對於雜湊分割槽表來說為最優場景，能具體到某單個分割槽，過濾值為常量。

mysql:ytt_new> explain select count(*) from hash_t1 where id = 8\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: hash_t1
   partitions: p8
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

SQL 2 針對欄位 ID 的過濾條件為15個常量組合，具體到15個分割槽，對比總分割槽數來講比例很小，也很優化。不過從執行計劃來看，還有優化空間，可以考慮改變分割槽表的雜湊方式，後面介紹。

SQL 2 的執行計劃：

mysql:ytt_new> explain select count(*) from hash_t1 where id  in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: hash_t1
   partitions: p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 15
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

SQL 3 、SQL 4 與 SQL 1 、SQL 2 實現的效果一樣，不過卻要掃描所有分割槽才能拿到結果。

來同樣看下 SQL 3 執行計劃：

mysql:ytt_new> explain select count(*) from hash_t1 where  id <=1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: hash_t1
   partitions: p0,p1,p2，...，p1021,p1022,p1023
...

所以需要注意的是雜湊分割槽表僅限於等值過濾檢索，類似對普通表基於雜湊索引的檢索。

之前我們有看到 SQL 2 掃描了不必要的分割槽， 那能否減少 SQL 2 掃描的分割槽數量呢？答案是可以的。

得重新對錶資料進行雜湊拆分，由需求到定義反著來：

建立一張新表 hash_t2 , 按照 Id div 1024 來分割槽，每個分割槽就能嚴格按照 ID 順序存放前 1024 個值：

mysql:ytt_new> create table hash_t2 (id bigint unsigned auto_increment primary key, r1 int, log_date date) partition by hash(id div 1024) partitions 1024;
Query OK, 0 rows affected (10.54 sec)

mysql:ytt_new>load data infile '/var/lib/mysql-files/hash_sample.csv' into table hash_t2;
Query OK, 5000000 rows affected (3 min 20.11 sec)
Records: 5000000  Deleted: 0  Skipped: 0  Warnings: 0

來看看效果：此時 SQL 2 可以基於單個分割槽 p0 來檢索資料。

mysql:ytt_new> explain select count(*) from hash_t2 where id in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: hash_t2
   partitions: p0
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 15
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

此外，雜湊分割槽還適合特定的日期類等值查詢場景， 分割槽定義比按照範圍要簡單，實現效果一樣。比如按照日期來檢索的 SQL 5 ：

SQL 5： select count(*) from hash_t1 where log_date= '2020-08-05';

建立新表 hash_t3 ，分割槽欄位為 year(log_date) ：

mysql:ytt_new>create table hash_t3 (id bigint unsigned , r1 int,log_date date, key idx_log_date(log_date));
Query OK, 0 rows affected (0.04 sec)

mysql:ytt_new>alter table hash_t3 partition by hash(year(log_date)) partitions 11;
Query OK, 0 rows affected (0.32 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql:ytt_new>load data infile '/var/lib/mysql-files/hash_sample.csv' into table hash_t3;
Query OK, 5000000 rows affected (2 min 4.59 sec)
Records: 5000000  Deleted: 0  Skipped: 0  Warnings: 0

定義好新表，來看下 SQL 5 的執行計劃： 基於日期的檢索也能限定在單個分割槽。


mysql:ytt_new>explain  select count(*) from hash_t3 where log_date = '2020-08-05'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: hash_t3
   partitions: p7
         type: r
possible_keys: idx_log_date
          key: idx_log_date
      key_len: 4
          ref: const
         rows: 1405
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

這裡用 year 還不夠優化，後期隨著資料量增加，每個分割槽的資料會有增長，可以考慮按照 month 來拆分資料。

MySQL 還有一個特殊的雜湊分割槽：不限制輸入資料型別的KEY 分割槽，雜湊函式預定義為系統函式 PASSWORD ，定義更加簡單，適合主鍵非整型欄位的表。

以上這些都只是考慮查詢效能，如果後期分割槽經常擴容，縮容等，可以考慮線性雜湊分割槽。

線性雜湊是一致性雜湊在 MySQL 裡的具體實現，其目的就是為了解決分割槽表後期擴縮容效能問題。不過會帶來分割槽資料分佈不均勻、出現資料熱點、相同 SQL 掃描記錄數被放大等新問題。

用一個簡單例子來對比下兩者的差異：把表 hash_t1 分割槽數量縮減到10個，總花費時間2分鐘46秒：

mysql:ytt_new>alter table hash_t1 coalesce partition 1014;
Query OK, 0 rows affected (2 min 46.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

新建線性雜湊表 hash_linear_t1 ，初始分割槽數目也是 1024 ，同樣把分割槽數目減少到10個。 縮減分割槽的時間為1分鐘28秒，比操作表 hash_t1 時間上少了一半左右。

mysql:ytt_new>create table hash_linear_t1 (id bigint unsigned auto_increment primary key, r1 int,log_date date) partition by linear hash(id) partitions 1024;
Query OK, 0 rows affected (34.13 sec)

mysql:ytt_new>load data infile '/var/lib/mysql-files/hash_sample.csv' into table hash_linear_t1 ;
Query OK, 5000000 rows affected (2 min 7.78 sec)
Records: 5000000  Deleted: 0  Skipped: 0  Warnings: 0

mysql:ytt_new>alter table hash_linear_t1 coalesce partition 1014;
Query OK, 0 rows affected (1 min 28.29 sec)
Records: 0  Duplicates: 0  Warnings: 0

來看下兩張分割槽表的資料分佈情況：很明顯，線性雜湊表資料分佈不是很均勻，存在嚴重的資料熱點問題。

mysql:ytt_new>select table_rows from information_schema.partitions where table_name = 'hash_t1';
+------------+
| TABLE_ROWS |
+------------+
|     485723 |
|     537704 |
|     523017 |
|     470724 |
|     478982 |
|     512272 |
|     483190 |
|     455829 |
|     520512 |
|     461572 |
+------------+
10 rows in set (0.00 sec)

mysql:ytt_new>select table_rows from information_schema.partitions where table_name = 'hash_linear_t1 ';
+------------+
| TABLE_ROWS |
+------------+
|     269443 |
|     340989 |
|     611739 |
|     584321 |
|     566181 |
|     624040 |
|     637801 |
|     688467 |
|     331397 |
|     317695 |
+------------+
10 rows in set (0.01 sec)

本篇介紹了 MySQL 雜湊分割槽表的使用場景以及一些細微差異。切記：雜湊分割槽不能用於範圍查詢，只能用作等值查詢場景。

相關文章