14. mysql partition HASH & amp; KEY_MySQL

Mysql partition HASHamp; amp; KEY

1. hash partition ps: I personally think the HASH PARTITION is very good and powerful, and the simple distribution is extremely even to CREATE an instance: CREATE TABLE HASH_EMP (tid int, tname char (255) PARTITION BY HASH (tid) PARTITIONS 8; perform hash partitioning on The tid of hash_emp, and query the data distribution of the partition in eight zones: select partition_name, partition_expression, partition_description, table_rows from partition where table_schema = schema () and table_name = 'hash _ emp'; + ------------------ + ---------------------- + ------------ ----------- + ------------ + | Partition_name | partition_expression | partition_description | table_rows | + ---------------- + tables + ------------ + | p0 | tid | NULL | 0 | p1 | tid | NULL | 0 | | p2 | tid | NULL | 0 | p3 | tid | NULL | 0 | p4 | tid | NULL | 0 | p5 | tid | NULL | 0 | p6 | tid | NULL | 0 | p7 | tid | NULL | 0 | + -------------- + ------ ---------------- + --------------------- + ------------ + Create an event to continuously write data. test distribution: create event hash_emp_event on scheduler every 1 second do insert into hash_emp values (NULL, now (); set GLOBAL event_scheduler = 1; // enable the scheduler to view the partition data distribution again: + ---------------- + ---------------------- + ----------------------- + ------------ + | partition_name | partition_expression | partition_description | table_rows | + --- ------------- + ---------------------- + ----------------------- + ------------ + | P0 | tid | NULL | 41 | p1 | tid | NULL | 42 | p2 | tid | NULL | 42 | p3 | tid | NULL | 42 | p4 | tid | NULL | 42 | p5 | tid | NULL | 42 | p6 | tid | NULL | 42 | p7 | tid | NULL | 42 | + ---------------- + ---------------------- + ------------------------- + ------------ + can be seen, hash distribution is extremely even:; 2. key partition PS: the so-called key partition refers to mysql Use the primary KEY or unique TABLE creation for PARTITION management to CREATE an instance: create table KEY_EMP (tid int, tname char (255) partition by key (tid) PARTITIONS 8; PS :: because it is similar to hash, too many tests will not be conducted !!! 3. sub-partition PS: As the name implies, it is to re-create a partition on the partition PS: Supports the sub-partition mode range | list, both of them can support creating instances BY hash or list subpartitions: create table ZI_EMP (tid int, tname char (255) partition by range (tid) subpartition by hash (tid) SUBPARTITIONS 2 (PARTITION p0 values less than (1990), PARTITION p1 values less than (2028), PARTITION p2 values less than (MAXVALUE )); divide zi_emp into three range partitions. each partition is divided into two subpartitions. if so, the following partition structure is available: + partition + ------------ + | partition_name | partition_expression | partition_description | table_rows | + ---------------- + partition + ---------- + | p0 | tid | 1990 | 0 | p0 | tid | 1990 | 0 | p1 | tid | 2028 | 0 | p1 | tid | 2028 | 0 | p2 | tid | MAXVALUE | 0 | p2 | tid | MAXVALUE | 0 | + ---------------- + ---------------------- + ----------------------- + ------------ + that is to say, if tid is less than 1990, the data is allocated to the p0 subpartition by hash.