mysql USES partitioned tables in the tutorial of deletes partitioned tables
- 2020-06-03 08:38:11
- OfStack
Benefits of MySQL using partitioned tables:
1. Some classified data can be placed in one partition, which can reduce the amount of data checked by the server and speed up the query.
2. Easy to maintain, delete old data by deleting partitions.
3, the partition data can be distributed to different physical locations, can do the distributed efficient use of multiple hard drives.
MySQL can create four partition types:
RANGE partition: Assigns multiple rows to a partition based on column values belonging to a given continuous interval.
LIST partitioning: Similar to RANGE partitioning, except that LIST partitioning is selected based on column values matching one of a set of discrete values. www. ofstack. com
HASH partition: A partition selected based on the return value of a user-defined expression that evaluates to the column value of the rows to be inserted into the table. This function can contain any expression in MySQL that is valid and produces non-negative integer values.
KEY partitioning: Similar to HASH partitioning, except that the KEY partition only supports computing one or more columns, and the MySQL server provides its own hash function. One or more columns must contain integer values.
The range and list partitions are commonly used.
RANGE partition
Here is a test with a sales business
The sales table has three fields: date/goods/sales
Test data from January 1, 2010 solstice September 31, 2010
Divide by "month"
Initial zoning definition
You first need to see if the current database supports partitioning
mysql>SHOW VARIABLES LIKE '%partition%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| have_partitioning | YES |
+-------------------+-------+
1 row in set (0.03 sec)
Create partition tables and partition them on a monthly basis.
mysql> CREATE TABLE sale_data (
-> sale_date DATETIME NOT NULL,
-> sale_item VARCHAR(2) NOT NULL ,
-> sale_money DECIMAL(10,2) NOT NULL
-> ) www.ofstack.com
-> PARTITION BY RANGE (YEAR(sale_date)*100+MONTH(sale_date)) (
-> PARTITION p201001 VALUES LESS THAN (201002),
-> PARTITION p201002 VALUES LESS THAN (201003),
-> PARTITION p201003 VALUES LESS THAN (201004),
-> PARTITION p201004 VALUES LESS THAN (201005),
-> PARTITION p201005 VALUES LESS THAN (201006),
-> PARTITION p201006 VALUES LESS THAN (201007),
-> PARTITION p201007 VALUES LESS THAN (201008),
-> PARTITION p201008 VALUES LESS THAN (201009),
-> PARTITION p201009 VALUES LESS THAN (201010),
-> PARTITION pcatchall VLAUES LESS THAN MAXVALUE
-> );
Query OK, 0 rows affected (0.20 sec)
The new partition
mysql> ALTER TABLE sale_data
-> ADD PARTITION (PARTITION p201010 VALUES LESS THAN (201011));
Query OK, 0 rows affected (0.36 sec)
Records: 0 Duplicates: 0 Warnings: 0
Deleted partitions
-- When removed 1 A partition that also deletes all data in that partition.
mysql> ALTER TABLE sale_data DROP PARTITION p201010;
Query OK, 0 rows affected (0.22 sec) www.ofstack.com
Records: 0 Duplicates: 0 Warnings: 0
Partition merging
On SQL below, merge p201001-p201009 into 3 partitions p2010Q1-p2010Q3
mysql> ALTER TABLE sale_data
-> REORGANIZE PARTITION p201001,p201002,p201003,
-> p201004,p201005,p201006,
-> p201007,p201008,p201009 INTO
-> (
-> PARTITION p2010Q1 VALUES LESS THAN (201004),
-> PARTITION p2010Q2 VALUES LESS THAN (201007),
-> PARTITION p2010Q3 VALUES LESS THAN (201010)
-> );
Query OK, 0 rows affected (1.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
Partition splitting
In SQL below, partition p2010Q1 into s2009 and s2010
mysql> ALTER TABLE sale_data REORGANIZE PARTITION p2010Q1 INTO (
-> PARTITION s2009 VALUES LESS THAN (201001),
www.ofstack.com
-> PARTITION s2010 VALUES LESS THAN (201004)
-> );
Query OK, 0 rows affected (0.36 sec)
Records: 0 Duplicates: 0 Warnings: 0
An example of partitioning with data sources in different physical locations:
CREATE TABLE ts (id INT, purchased DATE)
ENGINE=innodb
PARTITION BY RANGE(YEAR(purchased))
SUBPARTITION BY HASH(id)
(
PARTITION p0 VALUES LESS THAN (1990)
(
SUBPARTITION s0 // There are smaller partitions under the larger one
DATA DIRECTORY='/usr/local/mysql/data0' // The data source
INDEX DIRECTORY='/usr/local/mysql/index0', // Indexed data source
SUBPARTITION s1
DATA DIRECTORY='/usr/local/mysql/data1'
INDEX DIRECTORY='/usr/local/mysql/index1'
),
PARTITION p1 VALUES LESS THAN (MAXVALUE)
(
SUBPARTITION s2
DATA DIRECTORY='/usr/local/mysql/data1'
INDEX DIRECTORY='/usr/local/mysql/index1',
SUBPARTITION s3
DATA DIRECTORY='/usr/local/mysql/data2'
INDEX DIRECTORY='/usr/local/mysql/index2'
)
);
Limitations of partitioned indexes:
1. All partitions should use the same engine.
2, every single index of a partitioned table must contain columns referenced by partitioning functions.
3. mysql avoids querying all partitions, but still locks them all.
4, The partition function can use a limited number of functions and expressions, such as the above four functions.
5. Partitioning does not support foreign keys. www. ofstack. com
6, LOAD INDEX INTO CACHE cannot be used
7. Partitioning does not always improve performance; performance measurement is required.
For example, you can use expalin partitions to see if the query is using partitions to filter the data:
mysql> explain partitions select * from fenqubiao where day<'2011-09-12';
+----+-------------+-----------+---------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+---------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | fenqubiao | p_2010,p_2011 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
+----+-------------+-----------+---------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)