MySQL Advanced 15 - Table Partitioning

1. Why use partitioning?

When the amount of data is too large (usually millions or tens of millions of data), it is necessary to divide the data of one table into several tables for storage. Some queries can be greatly optimized, mainly because the data that satisfies a given WHERE statement can be stored in only one or more partitions, so that the remaining partitions do not need to be searched during the search.

2. Check whether mysql supports partitioning

Use the following command to check whether mysql supports partitioning? ?

show variables like '%partition%';

——If the output variable is yes, it means mysql supports partitioning.

3. Range partition

CREATE TABLE employees (
　　id INT NOT NULL,
　　fname VARCHAR(30),
　　lname VARCHAR(30),
　　hired DATE NOT NULL DEFAULT '1970-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)
);

In this example, all rows related to store workers will be saved in partition p0, all rows related to office and support staff will be saved in partition p1, and management All relevant rows are saved in partition p2.

View partition

show create table employees;

4. List partition

is similar to RANGE partition, the difference is that LIST partition is based on column value matching a discrete Select a value from a set of values.

LIST partitioning is achieved by using "PARTITION BY LIST(expr)", where "expr" is a certain column value or a value based on a certain column value, and returns an integer Value expression, and then define each partition by "VALUES IN (value_list)", where "value_list" is a comma-separated list of integers.

create table category_part( cid int unsigned not null auto_increment,cname varchar(64) not null,parent_id int not null,primary key (cid,parent_id))
partition by list(parent_id)(
partition p1 values in (1,2,3,6,9),
partition p2 values in (4,5,10,22,23),
partition p3 values in (7,8,11,12,13),
partition p4 values in (14,15,16,17,20),
partition p5 values in (18,19,21,24,25)
);

5. HASH partition

The purpose of hash partition is to evenly distribute data into predefined partitions to ensure the amount of data in each partition roughly the same. In RANGE and LIST partitions, you must explicitly specify in which partition a given column value or set of column values ​​should be saved; in HASH partitioning, MYSQL automatically completes these tasks, and all the user has to do is based on the data to be hashed. Column value specifies a column value or expression, and specifies the number of partitions into which the partitioned table will be divided.

To use HAHS partitioning to split a table, add a PARTITION BY HASH (expr) clause to the CREATE TABLE statement, where "expr" is an expression that returns an integer. expr can just be a column name whose field type is integer. In addition, the user is likely to need to add a PARTITITIONS num clause later, where num is a non-negative integer, which represents the number of partitions to be divided into. If a PARTITIONS clause is not included, the number of partitions defaults to 1.

create table t_hash (
a int,
b datetime)
partition by hash (YEAR(b))
partitions 4;

The advantage of LINEAR HASH partitioning is that adding, deleting, merging and splitting partitions will become faster, which is beneficial to processing tables containing large amounts of data. . The disadvantage is that compared with the data distribution obtained by using HASH partitioning, the distribution of data among each partition may not be balanced.

6. Keys partition

create table orders_key
(
id int auto_increment,
customer_surname varchar(30),
store_id int,
alesperson_id int,
order_Date date,
note varcahr(500),
index_idx(id)
) engine=myisam partition by key(order_date) partitions 4;

This partition is similar to hash partition, except that the MySQL server uses its own hash expression. Unlike other types of partitions, it does not require the use of an int or null. expression.

The above is the content of MySQL Advanced Fifteen - Table Partitioning. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!