Detailed MySQL partition table

When the amount of database data rises to a certain amount, performance becomes the problem that we can't help but how to optimize? There are a few common ways:

1, sub-table, that is, a large expression of data into several tables, so that each table data are not many.

Pros: Increase concurrency and reduce lock granularity

Cons: High code maintenance costs, related SQL needs to be changed

2, partition, all the data is also in a table, but the physical storage data according to certain rules in different files, files can also be placed on another disk

Advantages: Low code maintenance, basic no changes, improve IO throughput

Disadvantage: There is no increase in the level of concurrency for tables

3, split business, this essence is a sub-table.

Pros: Better long-term support

Cons: Code logic refactoring, a lot of work

Of course, each case has a suitable application scenario and needs to be tailored to the specific business. Since the relationship between the sub-table and the split business and MySQL itself is not at the business level, we only say the way that the database is most closely related: Table partitioning. However, using table partitioning is a prerequisite that your database must support. So, how do you know if my database supports table partitioning? Please execute the following command

Show plugins;  ---performed in the MySQL console

It is said that the 5.4-bit version is another command, but I did not test

Show variables like '%part% ';

Table partitioning for a database generally has two ways: portrait and landscape. Portrait is the separation of different fields in the table into different data files. Landscape is to put the first part of the table data into one file, the other part of the data into a file. MySQL only supports the latter one way, horizontal split.

1. Create a partitioned table

If you want to use the table's partitioning advantage, not only the database version support partition, the key to build a partitioned table, this table is not the same as the normal table, and must be built when the table to specify the partition, otherwise you cannot change the normal table to a partitioned table. So, what if you create a partitioned table? The other is very simple, please see the following table statement

1 CREATE TABLE ' T_part ' (2     ' f_id ' INT default null,3     ' f_name ' VARCHAR) default null,4     PRIMARY KEY (' f_id ') 5) ENGINE = MyISAM DEFAULT CHARSET = UTF8 6 PARTITION by RANGE (f_id) (        -----Specify partition Mode 7     PARTITION p0  VALUES Less THAN,--divided two districts 8     PARTITION p1  VALUES less THAN (20) 9)

The above statement builds a "t_part" table with two fields f_id and F_name, and divides the table into two extents p0, p1, when f_id is less than 10 into the P0 partition, when f_id is greater than 0 less than 20 into a partition P1. So where does the data in f_id greater than 20 fit into the partition? You guessed it, the INSERT statement will be an error.

You see, it's so easy to create a partitioned table! Of course, you can always add a delete partition, but be aware that deleting the partition will delete all the data under the current partition.

ALTER TABLE T_part ADD partition (partition P2 values less than (MAXVALUE));  ---new partition ALTER TABLE T_part DROP partition p2; ----Delete a partition

2. Table Partitioning methods

MySQL supports 5 different partitioning methods: Range partition, list partition, hash partition, LINEAR hash partition, and key partition. Each of these partitions has its own usage scenario.

1) Range Partition:

A range partition's table is partitioned in one of the following ways, with each partition containing the rows of those partition expressions whose values are within a given contiguous interval. These intervals are contiguous and cannot overlap each other, and are defined using the values less than operator.

The above example is the range partition.

2) List partition:

The list partition in MySQL is similar to a range partition in many ways. As with the range partition, each partition must be clearly defined. The main difference is that the definition and selection of each partition in the list partition is based on a value from a column that belongs to a value in a value list, and the range partition is a collection that belongs to a continuous interval value. The LIST partition is implemented by using PARTITION by LIST, where "expr" is a column value or an expression that is based on a column value, returns an integer value, and then defines each partition by means of "values in (value_list)". where "Value_list" is a comma-delimited list of integers.

CREATE TABLE ' t_list ' (    ' f_id ' INT default null,    ' F_name ' VARCHAR () default null,    PRIMARY KEY (' f_id ')) ENG INE = MyISAM Default CHARSET = UTF8 PARTITION by list (f_id) (PARTITION  p0),  ----interval value cannot be repeated  Partit ION p1 VALUES in (4,5,6));

3) Hash Partition:

Hash partitioning is primarily used to ensure that data is evenly distributed over a predetermined number of partitions. In the range and list partitions, you must explicitly specify which partition a given column value or set of column values should be kept in, and in the hash partition, MySQL does the work automatically, all you have to do is to specify a column value or expression based on the column value that will be hashed. and the number of partitions that the partitioned table will be divided into. To use a hash partition to split a table, add a PARTITION by HASH clause to the CREATE TABLE statement, where "expr" is an expression that returns an integer. It can just be the name of a column with the field type of MySQL integer. In addition, you will probably need to add a "partitions num" clause later, where NUM is a non-negative integer that represents the number of partitions the table will be partitioned into.

CREATE TABLE ' T_hash ' (    ' f_id ' INT default null,    ' F_name ' VARCHAR () default null,    PRIMARY KEY (' f_id ')) EN Gine = MyISAM DEFAULT CHARSET = UTF8 PARTITION by HASH (f_id)---can specify multi-column partitions 4;---Number of partitions

"Expr" can also be any function or other expression that is valid in MySQL, as long as they return an integer that is both a number and a non-random number. (In other words, it is both a change but a certainty). It should be remembered, however, that whenever a row is inserted or updated (or possibly deleted), the expression is evaluated once, which means that very complex expressions can cause performance problems, especially when performing operations that affect a large number of rows simultaneously, such as bulk insertions. The most efficient hash function is to evaluate only a single table column, and its value increases or decreases consistently with the column values, because this takes into account the "trimming" on the partition range. In other words, the closer the value of the expression value to the column it is based on, the more efficiently MySQL can use the expression for hash partitioning.

4) LINEAR Hash partition:

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

5) Key partition:

Partitioning by key is similar to a user-defined expression that is used in addition to the hash partition, except for hash partitioning, while the key partition's hashing function is provided by the MySQL server. The MySQL cluster (Cluster) uses the function MD5 () to implement the key partition, and for tables using other storage engines, the server uses its own internal hash function, which is based on the same algorithms as password ().

The syntax of the key partition is similar to the hash syntax, except that the keyword is changed to key.

CREATE TABLE ' T_key ' (    ' f_id ' INT default null,    ' F_name ' VARCHAR () default NULL,    PRIMARY key (' f_id ')) ENGI NE = MyISAM DEFAULT CHARSET = UTF8 PARTITION by LINEAR key (f_id) partitions 3;

6) Sub-partition:

The sub-partition means to partition again on the basis of the partition. And each partition must have the same number of sub-partitions.

CREATE TABLE ' T_part ' (    ' f_id ' INT default null,    ' F_name ' VARCHAR () default null,    PRIMARY KEY (' f_id ')) PAR Tition by RANGE (f_id) subpartition by HASH (f_id) subpartitions 2 (    PARTITION p0     VALUES less         THAN),    PARTITION p1    VALUES less        THAN (20))

The above statement means that two range partitions are created and each partition has two sub-partitions according to the hash, in fact the entire table is divided into 2x2=4 partitions. Of course, it is also possible to define each partition property in detail

CREATE TABLE ' T_part ' (    ' f_id ' INT default null,    ' F_name ' VARCHAR () default null,    PRIMARY KEY (' f_id ')) PAR Tition by RANGE (f_id) subpartition by HASH (f_id) (    PARTITION p0     VALUES less THAN (        subpartition s0< C7/>data directory = '/disk0/data '             INDEX directory = '/disk0/idx ',        subpartition s1             DATA directory = '/disk1 /data '             INDEX DIRECTORY = '/disk1/idx '    ),    PARTITION p1    VALUES less THAN (        Subpartition s2            DATA directory = '/disk0/data '             INDEX directory = '/disk0/idx ',        subpartition S3             DATA directory = '/disk1/data '             INDEX directory = '/disk1/idx '    )

This allows you to specify a specific storage disk for each partition. The prerequisite disk is present.

　　

A partition in MySQL is not processed on a null value (NULL), whether it is a column value or a user-defined expression value. Generally speaking, MySQL treats null as 0 in this case. If you want to avoid this, you should not allow null values when designing the table, and the most likely way to do this is by declaring the column "not NULL".

Detailed MySQL partition table