Shulou(Shulou.com)06/01 Report--

Blog outline:

1. Differences between RANGE Partition 2, LIST Partition 3, HASH Partition 4, key Partition 5, MySQL Subtable and Partition 6. Additional: how to store partitions in different directories

The MySQL partition types are as follows:

RANFGE partition LIST partition HASH partition key partition

The condition of the above four partitions must be shaping, and if it is not shaping, it needs to be converted to shaping by function.

1. RANGE partition

A RANGE partition assigns multiple rows to a partition based on column values that belong to a given contiguous interval. These intervals should be contiguous and cannot overlap each other and are defined using the values less than operator.

Create a RANGE partitioned table:

Mysql > create table employees (- > id int not null,-> fname varchar (30),-> lname varchar (30),-> hired date not null default '1970-01-01-01,-> separated date not null default' 9999-12-31),-> job_code int not null,-> store_id int not null->)-> partition by range (store_id) (- > partition p0 values less than (6) -> partition p1 values less than (11),-> partition p2 values less than (16),-> partition p3 values less than (21)->)

According to this partition scheme, when the inserted record store_id is less than 6, it will be saved in the partition p0, and if the store_id is less than 11, the data will be saved in the partition p1. and so on. But in the above partition scheme, the last partition defined is less than 21, then, if there is a record with a store_id greater than or equal to 21, the insert will fail, because the database does not know which partition should be inserted, and to avoid this situation, you need to specify a partition scheme or add a new partition, and the last partition should specify a range of maxvalue, not a specific value.

Based on the table created above, you can add a partition with a range of maxvalue, as follows:

Mysql > alter table employees add partition (partition p4 values less than maxvalue)

If the last partition specifies a range of maxvalue, then if you want to add a partition later, you need to use the following methods (using instructions similar to partition merge):

Mysql > alter table employees reorganize partition p4 into-> (partition p03 values less than (25),-> partition p04 values less than maxvalue->)

The instructions to delete the partition are as follows (Note: when the partition is deleted, then the data stored by the partition will also be deleted, use with caution! ):

Mysql > alter table employees drop partition p2ter2, LIST partition

LIST partitions are similar to RANGE partitions, except that LIST partitions are selected based on column values matching a value in a collection of discrete values. LIST partitions are implemented by using "partition by list (expr)", where "expr" is an expression based on a column value and returns an integer value, and then defines each partition as "values in (value_list)", where "value_list" is a comma-separated list of integers.

Example of creating a LIST partition type:

Mysql > create table employees (- > id int not null,-> fname varchar (30),-> lname varchar (30),-> hired date not null default '1970-01-01-01,-> separated date not null default' 2100-12-31),-> job_code int,-> store_id int->)-> partition by list (store_id) (- > partition pNorth values in (3jue 5, 6, 9),-> partition pEast values in (1) 10, 11, 19, 20),-> partition pWest values in (4, 12, 13, 14, 18),-> partition pCentral values in (7, 8, 15, 16)

In the table created above, if you insert a data with a store_id of 22 (not in the defined sub-list), the insert will fail, as follows:

The reason for the failure is that the LIST partition does not have a definition like "VALUES LESS THAN MAXVALUE" that includes other values. Any value to be matched is

Must be found in the values list.

The solution is to increase the partition with this value, as follows:

Mysql > alter table employees add partition (partition p4 values ins (22j 23jue 24); 3. HASH partition

This mode allows DBA to compute the Hash Key of one or more columns of the table and finally partition the data regions corresponding to different values of the Hash code. The purpose of hash partition is to distribute the data evenly among the predefined partitions to ensure that the amount of data in each partition is roughly the same. In range and list partitions, you must specify which partition a given column value or set of column values should be stored in; in hash partitions, MySQL does this automatically, specifying a column value or expression that the user wants, and specifying the number of partitions that the partitioned table will be divided into.

1) create hash partitioned table mysql > create table t_hash (an int (11), b datetime) partition by hash (year (b)) partitions 4

In the above command to create the table, the year function is used to extract the year in column b as the basis for partitioning, and four partitions are specified through partitions.

2) insert test data mysql > insert into t_hash values (1meme 2010-04-01)

The data inserted by the above instruction will be stored in the p2 partition and calculated as follows:

If you look at the partitions in the information_schema library, you can also see that there is a record in the p2 partition, as follows:

Mysql > select * from information_schema.partitions where table_schema='test001' and table_name='t_hash'\ G

The returned result is as follows:

The above example does not distribute the data evenly among the partitions, because according to the YEAR function, the value itself is discrete. If you partition consecutive values by HASH, such as a self-growing primary key, you can better distribute the data evenly.

4. Key partition

Key partition is similar to hash partition, except that hash partition is partitioned by user-defined functions, key partition uses functions provided by mysql database, NDB cluster uses MD5 function to partition, and internal hash function is used for other storage engine mysql.

Create a key partitioned table:

Mysql > create table t_key (an int (11), b datetime) partition by key (b) partitions 4

If you need to add new partitions later, use the following directive:

Mysql > alter table t_key add partition partitions 5

Five new partitions have been added to the above directive, that is, there are now nine partitions in Table 1 of t_key.

Note: mysql-5.5 began to support COLUMNS partitions, which can be seen as the evolution of RANGE and LIST partitions, and COLUMNS partitions can be partitioned directly using non-shaping data. The COLUMNS partition supports the following data types: all shaping, such as INT SMALLINT

TINYINT BIGINT . FLOAT and DECIMAL do not support it. Date types, such as DATE and DATETIME. The remaining date types are not supported. String types, such as CHAR, VARCHAR, BINARY, and VARBINARY. BLOB and TEXT types are not supported. COLUMNS can use multiple columns for partitioning.

5, MySQL sub-table and partition difference 1) the implementation of mysql sub-table is the real sub-table, a table is divided into many tables, each small table is a complete table, corresponding to three files, a .MYD data file, .MYI index file, .frm table structure file. Partition is different, after a large table is partitioned, it is still a table and will not become two tables, but it stores more blocks of data. 2) after data processing, the data is stored in a sub-table, and the total table is just a shell. Access data occurs in a sub-table. Partition, there is no concept of sub-table, partition only divides the file storing data into many small blocks, the table after partition is still a table, the data processing is still done by itself. 3) after improving the performance of sub-table, the concurrency ability of single table is improved, and so is the performance of disk Imax O. Because it takes less time to search, if there is high concurrency, the summary table can divide the concurrent pressure into different small tables according to different queries. Mysql puts forward the concept of partition, which mainly wants to break through the bottleneck of disk Imax O and improve the read and write ability of disk, so as to increase mysql performance. At this point, the emphasis of partition and sub-table is different, the focus of sub-table is how to improve the concurrency ability of mysql when accessing data, while partition, how to break through the read and write ability of disk, so as to improve the performance of mysql. 4) there are many ways to divide the table in the degree of difficulty, and using merge to divide the table is the easiest way. This approach is similar to the difficulty of partitioning and can be transparent to the program code. If you use other sub-table methods, it will be more troublesome than partitioning. Partitioning implementation is relatively simple, creating a partitioned table is no different from a normal table, and is transparent to the open code side. 5) other differences can improve the high performance of mysql and have a good performance in the state of high concurrency. Sub-tables and partitions do not contradict each other, and can cooperate with each other. For those tables with large access volume and more table data, we can adopt the combination of sub-tables and partitions. For tables with small access volume but a lot of table data, we can adopt the way of partition and so on. The sub-table technology is troublesome, it needs to create the child table manually, and the app server needs to calculate the child table name when reading and writing. It is better to use merge, but you also need to create union relationships between child tables and configure child tables. Compared with the sub-table, the table partition is easy to operate, and there is no need to create child tables. 6. Attach: how to store partitions in different directories

First create the desired directory locally:

[root@mysql ~] # mkdir / data [root@mysql ~] # chown-R mysql.mysql / data

Then, when you create a table, you can specify data directory, as follows:

Mysql > create table user (- > id int not null auto_increment,-> name varchar (30) not null default',-> primary key (id)) default charset=utf8 auto_increment=1-> partition by range (id) (- > partition p1 values less than (3) data directory'/ data/area1',-> partition p2 values less than (6) data directory'/ data/area2',-> partition p3 values less than (9) data directory'/ data/area3')

If you look at the local directory, you will find that the corresponding directory for storing data has been automatically created, as follows:

Note: when using inodb, mysql's default storage engine, you only need to specify data directory, because the data and indexes of inodb are in one file. However, when you specify engine=myisam when you create a table, you need to specify both data directory and index directory to modify the storage location of the partition.

-this is the end of this article. Thank you for reading-

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.