Benefits of MySQL using partitioned tables:
1, you can put some of the data in the collation of a partition, you can reduce the number of server check data to speed up the query.
2, easy maintenance, delete the old data by deleting the partition.
3, partition data can be distributed to different physical locations, can do distributed effective use of multiple hard drives.
MySQL can establish partitions of four partition types:
RANGE partition: Assigns multiple rows to a partition based on a column value that belongs to a given contiguous interval.
List partition: Similar to a range partition, the difference is that the list partition is selected based on a column value matching a value in a discrete-value collection. Www.jb51.net
Hash partition: A partition that is selected based on the return value of a user-defined expression, calculated using the column values of the rows that will be inserted into the table. This function can contain any expression that is valid in MySQL that produces a non-negative integer value.
Key partitions: Similar to a hash partition, except that the key partition only supports the calculation of one or more columns, and the MySQL server provides its own hash function. You must have one or more columns that contain integer values.
Most commonly used are range partitions and list partitions.
Range partition
This is a test for a sales business.
Sales table has date/goods/Sales three fields
Test data from January 1, 2010 to September 31, 2010
Partition in "month"
Initial partition definition
First you need to see if the current database supports partitioning
Copy Code code as follows:
Mysql>show VARIABLES like '%partition% ';
+-------------------+-------+
| variable_name | Value |
+-------------------+-------+
| have_partitioning | YES |
+-------------------+-------+
1 row in Set (0.03 sec)
Create a partitioned table that is partitioned in chronological order.
Copy Code code as follows:
Mysql> CREATE TABLE Sale_data (
-> sale_date DATETIME not NULL,
-> Sale_item VARCHAR (2) Not NULL,
-> Sale_money DECIMAL (10,2) not NULL
->) www.jb51.net
-> PARTITION by RANGE (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
->);
Query OK, 0 rows affected (0.20 sec)
New Partition
Copy Code code as follows:
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
Delete Partition
Copy Code code as follows:
--When a partition is deleted, all the data in that partition is also deleted.
mysql> ALTER TABLE sale_data DROP PARTITION p201010;
Query OK, 0 rows affected (0.22 sec) www.jb51.net
records:0 duplicates:0 warnings:0
Merge of Partitions
The following SQL merges the p201001-p201009 into 3 partitions p2010q1-p2010q3
Copy Code code as follows:
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
Split of partitions
The following SQL, P2010Q1 partitions, split into s2009 and s2010 two partitions
Copy Code code as follows:
mysql> ALTER TABLE sale_data REORGANIZE PARTITION p2010q1 into (
-> PARTITION s2009 VALUES less THAN (201001),
Www.jb51.net
-> PARTITION s2010 VALUES less THAN (201004)
->);
Query OK, 0 rows affected (0.36 sec)
records:0 duplicates:0 warnings:0
An example of partitioning using different physical location data sources:
Copy Code code as follows:
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//small partitions under large partitions
directory= '/usr/local/mysql/data0 '//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, each unique index of the partitioned table must contain the columns referenced by the partition function.
3,mysql can avoid querying all partitions, but still locks all partitions.
4, the functions and expressions that can be used by partitioning functions are limited, for example, there are 4 kinds of functions.
5, the partition does not support foreign keys. Www.jb51.net
6, cannot use load INDEX into CACHE
7, partitioning does not always improve performance, performance evaluation.
For example, you can use Expalin partitions to see whether a query statement uses partitions to filter data:
Copy code code as follows:
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)
