Issue 41: MySQL Hash Partitioned Tables

When it comes to partition tables, data is generally split according to the range (range), and the use of hashes for data splitting has certain limitations and does not have standardization. Next, I will use a few examples to talk about the usage scenarios of MySQL hash partition tables and related transformation points.

For the hash partition table, the most popular method is to hash a single field, such as the following table hash_t1 (with 500W row records), do HASH according to the self-incrementing ID, and the number of partitions is 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)

The partition method of the table hash_t1 is well understood. HASH splits the data according to the ID, that is, modulo the number of partitions, similar to hash(mod(id,1024)), and the data distribution is very uniform.

 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)

Next consider the following SQL statements:

 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 and SQL 2 are great for retrieving hash partitioned tables, SQL 3 and SQL 4 are not.

The execution plan of SQL 1: It is the optimal scenario for the hash partition table, which can be specific to a single partition, and the filter value is constant.

 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's filter conditions for field IDs are 15 constant combinations, specific to 15 partitions. Compared with the total number of partitions, the proportion is small and very optimized. However, from the perspective of the execution plan, there is still room for optimization. You can consider changing the hashing method of the partition table, which will be described later.

Execution plan for 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 and SQL 4 have the same effect as SQL 1 and SQL 2, but they have to scan all partitions to get the results.

Let's also look at the SQL 3 execution plan:

 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
...

Therefore, it should be noted that the hash partition table is limited to equal-value filtering retrieval, which is similar to the retrieval based on the hash index of ordinary tables.

We have seen that SQL 2 scans unnecessary partitions before, so can we reduce the number of partitions that SQL 2 scans? The answer is yes.

It is necessary to hash and split the table data again, from requirements to definitions:

Create a new table hash_t2 and partition it according to Id div 1024. Each partition can store the first 1024 values in strict order of ID:

 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

Let's see the effect: SQL 2 can now retrieve data based on a single partition 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)

In addition, hash partitioning is also suitable for specific date class equivalent query scenarios. Partition definition is simpler than by range, and the implementation effect is the same. For example, SQL 5 to retrieve by date:

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

Create a new table hash_t3 with a partition field of 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

Define the new table, look at the SQL 5 execution plan: Date-based retrieval can also be limited to a single partition.

 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)

The use of year here is not optimized enough. As the amount of data increases in the later stage, the data of each partition will increase. You can consider splitting the data according to month.

MySQL also has a special hash partition: the KEY partition that does not limit the input data type, the hash function is predefined as the system function PASSWORD, the definition is simpler, and it is suitable for tables with non-integer fields of the primary key.

All of the above are only considering query performance. If the later partition is frequently expanded or reduced, linear hash partitioning can be considered.

Linear hashing is the specific implementation of consistent hashing in MySQL, and its purpose is to solve the performance problem of later expansion and contraction of partitioned tables. However, it will bring new problems such as uneven distribution of partition data, data hotspots, and magnification of the same SQL scan records.

Use a simple example to compare the difference between the two: reducing the number of partitions in table hash_t1 to 10, the total time spent is 2 minutes and 46 seconds:

 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

Create a new linear hash table hash_linear_t1, the initial number of partitions is also 1024, and the number of partitions is also reduced to 10. The time to shrink the partition is 1 minute and 28 seconds, which is about half less than the operation table hash_t1 time.

 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

Let's take a look at the data distribution of the next two partition tables: Obviously, the data distribution of the linear hash table is not very uniform, and there is a serious data hotspot problem.

 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)

This article introduces the usage scenarios of MySQL hash partitioned tables and some minor differences. Remember: Hash partitions cannot be used for range queries, only for equivalent query scenarios.