MySQL-optimized partition table: mysql Partition Table

MySQL-optimized partition table: mysql Partition Table

When the database data volume increases to a certain amount, performance becomes a problem that cannot be ignored. How can we optimize it? There are several common methods:

1. Table sharding: divides a large expression of data into several tables, so that there is not much data in each table.

Advantage: Improves concurrency and reduces lock Granularity
Disadvantage: Code maintenance costs are high and related SQL needs to be modified.

2. partitions. All data is stored in one table, but physical storage data is stored in different files according to certain rules. files can also be stored on another disk.

Advantage: the code maintenance is small, and there is no need to change it, improving IO Throughput
Disadvantage: The table concurrency is not increased.

3. Split the business. This is essentially table sharding.

Advantage: better long-term support
Disadvantage: Code logic reconstruction, heavy workload

Of course, each scenario has a suitable application scenario, which needs to be selected based on the specific business. Since the table sharding and splitting services are not closely related to mysql itself, we only say that the most closely related method is table partitioning. However, a prerequisite for Table Partitioning is that your database must support it. So, how do I know if my database supports Table Partitioning? Run the following command:

Copy codeThe Code is as follows:
Show plugins; --- run in the mysql Console

It is said that version 5.4 is another command, but I did not test it.
Copy codeThe Code is as follows:
Show variables like '% part % ';

There are two ways to partition a database table: vertical and horizontal. Vertical means to divide different fields in the table into different data files. In the horizontal mode, the first part of the table is put into a file, and the other part of the data is put into a file. Mysql supports only the latter method for horizontal splitting.

1. Create a partition table

To use the partition advantages of a table, you must not only support partitions in the database version, but also create a partition table. This table is different from a common table and you must specify partitions when creating a table, otherwise, you cannot change a common table to a partitioned table. What if I create a partition table? The other statements are simple. See the following table creation statements.

Create table 't_ part' ('f _ id' int default null, 'f _ name' VARCHAR (20) default null, primary key ('f _ id ')) ENGINE = myisam default charset = utf8 partition by range (f_id) (----- specify the PARTITION method PARTITION p0 VALUES less THAN (10 ), -- PARTITION p1 VALUES less THAN (20 ))

The preceding statement creates a "T_part" table, which has two fields: f_id and f_name. The table is divided into p0 and p1 Based on the RANGE method. When f_id is less than 10, the table is partitioned into p0, when f_id is greater than 0 and less than 20, put it into the partition p1. Which partition should data with f_id greater than 20 be put? If you have guessed it, the insert statement will report an error.

As you can see, it's so easy to create a partition table! Of course, you can add or delete partitions at any time, but note that all data in the current partition will be deleted when you delete the partition.

Copy codeThe Code is as follows:
Alter table T_part add partition (partition p2 values less than (MAXVALUE); --- add partition
Alter table T_part DROP partition p2; ---- delete a partition

2. Table Partitioning Methods
Mysql supports five partitioning Methods: RANGE partitioning, LIST partitioning, HASH partitioning, linear hash partitioning, and KEY partitioning. Each partition has its own application scenario.

1) RANGE partition:

A range Partition Table is partitioned in one of the following ways. Each partition contains rows whose partition expression values are located in a given continuous interval. These intervals must be continuous and do not overlap with each other, and are defined using the values less than operator.

The preceding example is a RANGE partition.

2) LIST partition:

LIST partitions in MySQL are similar to RANGE partitions in many aspects. Like partitioning by RANGE, each partition must be clearly defined. The main difference between them is that the definition and selection of each partition in LIST partitions are based on the value of a column from a value in a Value LIST set, the RANGE partition is a set of continuous RANGE values. LIST partitions are implemented BY using "partition by list (expr)", where "expr" is a column value or an expression based on a column value and returns an integer, then, each partition is defined using the "values in (value_list)" method. "value_list" is a list of integers separated by commas.

Create table 't_list' ('f _ id' int default null, 'f _ name' VARCHAR (20) default null, primary key ('f _ id ')) ENGINE = myisam default charset = utf8 PARTITION by list (f_id) (PARTITION p0 VALUES in (, 3), ---- the range value cannot be repeated PARTITION p1 VALUES in (, 6 ));

3) HASH partition:

HASH partitions are mainly used to ensure that data is evenly distributed in pre-defined partitions. In the RANGE and LIST partitions, you must specify the partition in which a given column value or column value set should be stored. In the HASH partition, MySQL automatically completes these tasks, all you need to do is specify a column value or expression based on the column value to be hashed, and specify the number of partitions to be split into in the partitioned table. To use HASH partitions to split a TABLE, add a "partition by hash (expr)" clause to the create table statement. "expr" is an expression that returns an integer. It can only be the name of a column whose field type is MySQL integer. In addition, you may need to add a "PARTITIONS num" clause later. num is a non-negative integer, which indicates the number of PARTITIONS to be split in the table.

Create table 't_ hash' ('f _ id' int default null, 'f _ name' VARCHAR (20) default null, primary key ('f _ id ')) ENGINE = myisam default charset = utf8 partition by hash (f_id) --- you can specify multiple columns of PARTITIONS 4; --- Number of PARTITIONS

"Expr" can also be any function or other expressions that are valid in MySQL, as long as they return an integer that is both a very number and a non-random number. (In other words, it is both changing but definite ). However, remember that this expression is calculated once every time a row is inserted or updated (or may be deleted). This means that a very complex expression may cause performance problems, especially when a large number of rows (such as batch insert) are affected during execution. The most efficient hash function is to calculate a single table column, and its value increases or decreases with the column value, because it considers "trim" on the partition range ". That is to say, the closer the expression value is to the value of the column It is based on, the more effective MySQL can use this expression for HASH partitioning.

4) linear hash partition:

MySQL also supports linear hash, which is different from conventional hash in that the linear hash function uses a linear power 2 (powers-oftwo) algorithm, the general hash uses the modulus of the hash function value. The only difference in syntax between a LINEAR hash PARTITION and a conventional hash PARTITION is that the "LINEAR" keyword is added to the "partition by" clause.

5) KEY partition:

Partitioning by KEY is similar to partitioning by HASH. Except for the User-Defined expression used by HASH partition, the HASH function of KEY partition is provided by the MySQL server. MySQL Cluster uses the MD5 () function to implement KEY partitioning. For tables using other storage engines, the server uses its own internal hash function, which is based on () the same algorithm.

The KEY partition syntax is similar to the HASH syntax, but the KEY is changed to the KEY.

CREATE TABLE `T_key` (  `f_id` INT DEFAULT NULL,  `f_name` VARCHAR (20) DEFAULT NULL,  PRIMARY KEY (`f_id`)) ENGINE = myisam DEFAULT CHARSET = utf8 PARTITION BY LINEAR key(f_id)PARTITIONS 3；

6) subpartition:

Subpartition refers to re-Partitioning Based on the partition. Each partition must have the same number of subpartitions.

CREATE TABLE `T_part` (  `f_id` INT DEFAULT NULL,  `f_name` VARCHAR (20) DEFAULT NULL,  PRIMARY KEY (`f_id`)) PARTITION BY RANGE (f_id)SUBPARTITION BY HASH(F_ID)SUBPARTITIONS 2(  PARTITION p0   VALUES     less THAN (10),  PARTITION p1  VALUES    less THAN (20))

The preceding statement creates two range partitions. Each partition has two subpartitions Based on the hash. In fact, the entire table is divided into 2 × 2 = 4 partitions. Of course, you can define the attributes of each partition in detail.

CREATE TABLE `T_part` (  `f_id` INT DEFAULT NULL,  `f_name` VARCHAR (20) DEFAULT NULL,  PRIMARY KEY (`f_id`)) PARTITION BY RANGE (f_id)SUBPARTITION BY HASH(F_ID)(  PARTITION p0   VALUES less THAN (10)  (    SUBPARTITION s0       DATA DIRECTORY = '/disk0/data'       INDEX DIRECTORY = '/disk0/idx',    SUBPARTITION s1       DATA DIRECTORY = '/disk1/data'       INDEX DIRECTORY = '/disk1/idx'  ),  PARTITION p1  VALUES less THAN (20)  (    SUBPARTITION s2      DATA DIRECTORY = '/disk0/data'       INDEX DIRECTORY = '/disk0/idx',    SUBPARTITION s3       DATA DIRECTORY = '/disk1/data'       INDEX DIRECTORY = '/disk1/idx'  ))

In this way, you can specify a storage disk for each partition. The premise disk exists.

Partitions in MySQL are not processed with a NULL value, whether it is a column value or a user-defined expression value. In general, MySQL treats NULL as 0 in this case. If you want to avoid this practice, you should NOT allow NULL values during table design. The most possible method is to achieve this by declaring the column "not null.