Table of contents

First check whether MySQL supports partitioning

Before actually operating the partition, we have to understand some limitations of the partition:

RANGE partition practice

How to query partitioned data in SQL

First check whether MySQL supports partitioning

show  plugins;

the

When the query result shows that the status of the partition is active, it means that the current MySQL version supports partitioning. There are generally four partitioning schemes:

1. RANGE partition: RANGE partition is mainly used for partitioning the date column, which is based on the column values ​​of continuous intervals. RANGE partition is also the most commonly used partition method.
2. LIST partition: The value of the LIST partition column is hashed, and it is precisely because the value of the partition column is hashed that it is necessary to manually define the value of the partition column.
3. HASH partition: HASH partition is to evenly distribute data to different partitions through a custom algorithm to ensure that the data in all partitions is roughly the same. HASH partitions only support integer partitions.
4. KEY partition: The key partition uses the database's own functions for partitioning. KEY partitioning supports data column partitioning other than BLOB and TEXT columns. the

Since RANGE partitioning is the most commonly used partitioning scheme, the other three partitioning schemes are relatively rarely used. Here we mainly introduce the RANGE scheme.

Before actually operating the partition, we have to understand some limitations of the partition:

1. The data table partition limit can only have a maximum of 1024 partitions.
2. All partitions of the same table must use the same storage engine.
3. If the database uses partitions, foreign key constraints cannot be used, and an error will be reported. the

RANGE partition practice

Tables partitioned by RANGE are partitioned in one of the following ways, each partition containing those partitions expression Rows whose values ​​lie within a given continuous interval. the

CREATE TABLE order_info (
id int not NULL PRIMARY KEY auto_increment,
goods_title VARCHAR(50) NOT NULL,
created_at date
) 
PARTITION BY RANGE (id) (
PARTITION order1 VALUES less than (10),
PARTITION order2 VALUES less than (20),
PARTITION order3 VALUES less than (30),
PARTITION order4 VALUES less than (40),
PARTITION order5 VALUES less than (50)
);

Create an order_info table and create 5 partitions. Next, we check the order_info table partitions to see if 5 partitions are successfully formed:

SELECT partition_name part,
partition_expression expr,
partition_description descr
,table_rows FROM information_schema.`PARTITIONS` 
WHERE table_schema=SCHEMA() AND table_name='order_info';

the 

You can see that we use the values ​​less than statement above to create partitions. We save the data with id<10 in the order1 partition, save the id range from 10-20 in the order2 partition, and so on to create five partitions.

Now we can write a stored procedure to insert 50 pieces of data for testing:

CREATE PROCEDURE test010()
BEGIN
DECLARE i INT;
SET i=1;
START TRANSACTION;
WHILE i<=50 DO
	INSERT INTO order_info VALUES(NULL,'Test Data',NOW());
SET i=i+1;
END WHILE;
COMMIT;
END
//
delimiter;
CALL test010();

It can be clearly seen that an error is reported, and the error message that there is no partition that can store the value with the id of 50 is reported. Because the range of the fifth partition we set is 40-50, and the right side does not include it, so if it exceeds 50, it will directly report an error and cannot be stored. This is very unreasonable, so we have to make some adjustments to the partition  

We use the alter table command to add a new partition order6 to save values ​​greater than 50:

ALTER TABLE order_info ADD PARTITION(PARTITION order6 VALUES less than (MAXVALUE));

  

We have added the order6 partition, and the value is set to maxvalue, which means that if the value is greater than the upper limit set by order5, it will be saved into the order6 partition.

We insert 500 pieces of data into the database to test the effect:

   

Let's check how many pieces of data each partition contains

   

We can see that the data is stored in different partitions completely according to the partitions we set. Some people may say how do I confirm that the 9 pieces of data stored in the order1 partition must have an id of 1-10?
This test is actually very simple, we directly delete the order1 partition, and then check the database data:

ALTER TABLE order_info DROP PARTITION order1;

   

It can be seen that after we delete the order1 partition, the data below id10 is also deleted, so it can be said that the data with id below 10 is indeed stored in the partition order1. And we can partition according to time, and then move the data of more than half a year to the standby table and delete the partition, which can improve the performance of our single table. Deleting partitions directly when deleting data is much more efficient than deleting data.  

How to query partitioned data in SQL

When performing select/update/delete, if the restriction condition behind where contains the partition field id, it will automatically search in the corresponding partition, otherwise it will still scan the whole table.

EXPLAIN SELECT * FROM order_info WHERE id = 20;

You can see that only the order3 partition is queried in the above figure.

EXPLAIN SELECT * FROM order_info WHERE goods_title = 'Test data 2';

You can see that the order2, order3, order4, order5, and order6 partitions have been queried in the above figure, and a total of 491 rows have been queried.

The range partition field only supports integers. If you need to perform range partitions on fields such as time and date, you can use related functions to convert the type to an integer and then partition.