Beckham _ mysql partition instance _ MySQL

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)