14. mysql Partition HASH && KEY

1. Hash partition PS:: Personally think that the hash partition is very good and powerful, simple and accurate distribution extremely evenly created instances:CREATE TABLEhash_emp (Tidint, TnameChar(255)) PARTITION byHASH (TID) partitions8; The TID for hash_emp is hashed and divided into 8 regions to query partition data distribution:SelectPartition_name,partition_expression,partition_description,table_rows fromInformation_schema.partitionswhereTable_schema= Schema() andtable_name= 'hash_emp'; +----------------+----------------------+-----------------------+------------+    |Partition_name|Partition_expression|Partition_description|Table_rows|    +----------------+----------------------+-----------------------+------------+    |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 1 event to write data without interruption, test distribution:CreateEvent Hash_emp_event onScheduler every1Second doInsert  intoHash_empValues(NULL, now ()); SetGLOBAL Event_scheduler= 1;//turn on the scheduler to see the distribution of partition data again:+----------------+----------------------+-----------------------+------------+    |Partition_name|Partition_expression|Partition_description|Table_rows|    +----------------+----------------------+-----------------------+------------+    |P0|Tid| NULL                  |          A |     |P1|Tid| NULL                  |          the |     |P2|Tid| NULL                  |          the |     |P3|Tid| NULL                  |          the |     |P4|Tid| NULL                  |          the |     |P5|Tid| NULL                  |          the |     |P6|Tid| NULL                  |          the |     |P7|Tid| NULL                  |          the |     +----------------+----------------------+-----------------------+------------+It can be seen that the hash distribution is extremely uniform:;2. Key partition PS:: The so-called key partition refers to MySQL by default using the table's primary key or unique partition management to create an instance:CREATE TABLEkey_emp (Tidint, TnameChar(255)) PARTITION by KEY(TID) Partitions8; PS: Because it is similar to hash, it is not tested too much!!!3. Sub-partition PS:: As the name suggests is to re-build the partition on the partition PS:: Sub-partition support partition mode has a range||list, 2 of them can support a hash or list of sub-partitions to create an instance:CREATE TABLEzi_emp (Tidint, TnameChar(255)) PARTITION byRANGE (tid) subpartition byHASH (tid) subpartitions2(PARTITION p0ValuesLess Than (1990), PARTITION p1ValuesLess Than (2028), PARTITION p2ValuesLess than (MAXVALUE)); The zi_emp is divided into 3 range partitions, each partitioned into 2 sub-partitions, if so, with the following partition structure:+----------------+----------------------+-----------------------+------------+    |Partition_name|Partition_expression|Partition_description|Table_rows|    +----------------+----------------------+-----------------------+------------+    |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 , if the TID is less than 1990, then the data will be hashed into the 2 sub-partitions of the P0