Mysql partition experiment Overview: 1. create partitions based on the partition type 2. manage partitions 3. partition and unpartitioned table example 1. create partitions using hash partitions as an example (1) non-linear hash partition 1. Test table t_hash structure createtablet_hash
Mysql partition experimentBrief: 1. create partitions based on the partition type 2. manage partitions 3. Examples of partitions and unpartitioned tables
1. create a partition. use hash partition as an example.
(1) non-linear hash partition
1. test the t_hash structure of the table.
Create table t_hash (Id int unsigned, Name char (1) ) Engine = myisam charset = utf8 Partition by hash (id) Partitions 5; |
|
2. t_hash table physical file structure
3. Insert data to the t_hash table
4. view the physical structure of the t_hash table
Summary: hash partitions are evenly distributed. Therefore, the data file size of each partition is equal.
II. manage partitions
1. hash partitions
2. adjust the number of partitions 5 to 2.
3. adjust the number of partitions 2 to 1.
Conclusion: once hash partition is performed, at least one partition exists.
III. Examples of partitioned and unpartitioned tables
1. Test table
Unpartitioned table: Create table t2 (Id int unsigned, Name char (1) ) Engine = myisam charset = utf8; |
|
Partition table: Create table t1 (Id int unsigned, Name char (1) ) Engine = myisam charset = utf8 Partition by hash (id) Partitions 4; |
|
2. Insert data of the same number
3. search for data with name
(Note: When no index is added, the partition takes less time than the unpartitioned one)