| I. Overview I believe many people often ask the same question: when MySQL
When the total number of records exceeds 1 million, will the performance be greatly reduced? The answer is yes, but the performance drop-down rate is different. It depends on the system architecture, applications, and> index, server hardware, and other factors. When a netizen asks me this question, my most common answer is: Table sharding, which can be divided into multiple values based on the ID range or time sequence.
Table sharding rules. Table sharding is easy. However, the resulting changes to the application and even the architecture are not too small, but also include future scalability. In the past, a solution was to use merge
Type, which is very convenient for cooking. There is basically no need to change the architecture and program. However, its disadvantages are obvious:
- Only MyISAM tables with the same structure can be used
- You cannot enjoy all the functions of MyISAM. For example, you cannot perform Fulltext search on the merge type.
- It requires more file descriptors
- Slow index reading
At this time, the advantages of the Partition Function added in MySQL 5.1 are obvious:
- You can store more data than a single disk or file system partition.
- It is easy to delete unnecessary or outdated data.
- Some queries can be greatly optimized.
- When Aggregate functions such as sum ()/count () are involved
- Higher Io Throughput
Partitions can be set to rules of any size, and multiple parts of a single table are allocated across file systems. In fact, different parts of a table are stored as separate tables in different locations. Ii. partition type
- Range partition: multiple rows are allocated to the partition based on the column values in a given continuous interval. See section 18.2.1, range partitioning
- List partition: similar to partitioning by range, the difference is that list partition is selected based on the column value matching a value in a discrete value set. See section 18.2.2, list partitions.
- Hash partition: select a partition based on the return value of a user-defined expression. This expression uses the column values of the rows to be inserted into the table for calculation. This function can include any expressions that contain valid non-negative integer values in MySQL. See section 18.2.3, hash Partition
- Key
Partitioning: similar to partitioning by hash, the difference is that the key partition only supports computing one or more columns, and the MySQL server provides its own hash function. One or more columns must contain> integer values.
See section 18.2.4, key Partition
Iii. Partition example:
- Range type
CREATE TABLE users ( uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL DEFAULT '', email VARCHAR(30) NOT NULL DEFAULT '' ) PARTITION BY RANGE (uid) ( PARTITION p0 VALUES LESS THAN (3000000) DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data1/idx', PARTITION p1 VALUES LESS THAN (6000000) DATA DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data3/idx', PARTITION p2 VALUES LESS THAN (9000000) DATA DIRECTORY = '/data4/data' INDEX DIRECTORY = '/data5/idx', PARTITION p3 VALUES LESS THAN MAXVALUE DATA DIRECTORY = '/data6/data' INDEX DIRECTORY = '/data7/idx' ); Here, the user table is divided into four partitions, with every 3 million records as the limit, each partition has its own independent data, index file storage directory, at the same time, directory>
Physical Disk Partitions may also be completely independent, which increases the disk I/O throughput.
- List type
CREATE TABLE category ( cid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL DEFAULT '' ) PARTITION BY LIST (cid) ( PARTITION p0 VALUES IN (0,4,8,12) DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data1/idx', PARTITION p1 VALUES IN (1,5,9,13) DATA DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data3/idx', PARTITION p2 VALUES IN (2,6,10,14) DATA DIRECTORY = '/data4/data' INDEX DIRECTORY = '/data5/idx', PARTITION p3 VALUES IN (3,7,11,15) DATA DIRECTORY = '/data6/data' INDEX DIRECTORY = '/data7/idx' ); It is divided into four zones, where data files and index files are stored separately.
- Hash type
CREATE TABLE users ( uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL DEFAULT '', email VARCHAR(30) NOT NULL DEFAULT '' ) PARTITION BY HASH (uid) PARTITIONS 4 ( PARTITION p0 DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data1/idx', PARTITION p1 DATA DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data3/idx', PARTITION p2 DATA DIRECTORY = '/data4/data' INDEX DIRECTORY = '/data5/idx', PARTITION p3 DATA DIRECTORY = '/data6/data' INDEX DIRECTORY = '/data7/idx' ); It is divided into four zones, where data files and index files are stored separately.
- Key type
REATE TABLE users ( uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL DEFAULT '', email VARCHAR(30) NOT NULL DEFAULT '' ) PARTITION BY KEY (uid) PARTITIONS 4 ( PARTITION p0 DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data1/idx', PARTITION p1 DATA DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data3/idx', PARTITION p2 DATA DIRECTORY = '/data4/data' INDEX DIRECTORY = '/data5/idx', PARTITION p3 DATA DIRECTORY = '/data6/data' INDEX DIRECTORY = '/data7/idx' ); It is divided into four zones, where data files and index files are stored separately.
- Subpartition
Subpartitions are used to split each partition in a range/List table. Re-division can be Hash/Key type. For example: CREATE TABLE users ( uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL DEFAULT '', email VARCHAR(30) NOT NULL DEFAULT '' ) PARTITION BY RANGE (uid) SUBPARTITION BY HASH (uid % 4) SUBPARTITIONS 2( PARTITION p0 VALUES LESS THAN (3000000) DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data1/idx', PARTITION p1 VALUES LESS THAN (6000000) DATA DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data3/idx' ); The range partition is partitioned by subpartitions again. The subpartitions adopt the hash type. Or CREATE TABLE users ( uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL DEFAULT '', email VARCHAR(30) NOT NULL DEFAULT '' ) PARTITION BY RANGE (uid) SUBPARTITION BY KEY(uid) SUBPARTITIONS 2( PARTITION p0 VALUES LESS THAN (3000000) DATA DIRECTORY = '/data0/data' INDEX DIRECTORY = '/data1/idx', PARTITION p1 VALUES LESS THAN (6000000) DATA DIRECTORY = '/data2/data' INDEX DIRECTORY = '/data3/idx' ); The range partition is divided into sub-partitions again, and the sub-partitions adopt the key type.
Iv. Partition Management
- Delete Partition
ALERT TABLE users DROP PARTITION p0; Delete partition P0.
- Re-create a partition
- Range partition Reconstruction
ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES LESS THAN (6000000)); Combine the original P0 and P1 partitions and place them in the new P0 partition.
- List partition Reconstruction
ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES IN(0,1,4,5,8,9,12,13)); Combine the original P0 and P1 partitions and place them in the new P0 partition.
- Hash/key partition Reconstruction
ALTER TABLE users REORGANIZE PARTITION COALESCE PARTITION 2; The number of re-created partitions in the reorganize mode is changed to 2. Here, the number can only be reduced and cannot be increased. To add a partition, use the Add partition method.
- Add Partition
- Add range Partition
ALTER TABLE category ADD PARTITION (PARTITION p4 VALUES IN (16,17,18,19) DATA DIRECTORY = '/data8/data' INDEX DIRECTORY = '/data9/idx'); Adds a range partition.
- Add hash/key partitions
ALTER TABLE users ADD PARTITION PARTITIONS 8; Extend the total number of partitions to 8.
Now, the experience is here first. For more details, see chapter 18th of the MySQL manual.
|
