The benefits of MySQL using partitioned tables: 1, you can put some classified data in a partition, you can reduce the number of server inspection data to speed up the query. 2, easy to maintain, delete the old data by deleting the partition. 3, partition data can be distributed to different physical locations and can be distributed efficiently using multiple hard drives.
MySQL can create partitions of four partition types: RANGE Partitioning: Assign multiple rows to a partition based on the column values that belong to a given contiguous interval. list partitions: similar to by range partitioning, the difference is that a list partition is selected based on a value in a set of discrete values that match a column value. www.2cto.com HASH partition: A partition that is selected based on the return value of a user-defined expression that is calculated using the column values of those 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 partitioning: Similar to partitioning by hash, the difference is 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. The range partition and list partition are generally used more often. Range partition here is a sale of the business to do the test sales table has date/goods/Sales Three field test data from January 1, 2010 to September 31, 2010 in the "month" as the unit of the initial partition definition needs to see first, whether the current database supports partitioning mysql> SHOW VARIABLES like '%partition% '; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | have_partitioning | YES | +-------------------+-------+ 1 row in Set (0.03 sec) creates a partitioned table that is partitioned by year-by-month. Mysql> CREATE TABLE sale_data ( sale_date datetime not NULL, -> ; Sale_item varchaR (2) Not NULL, - Sale_money DECIMAL (10,2) not null - www .2cto.com PARTITION by RANGE (year (sale_date) *100+month (sale_date)) ( &NBS P PARTITION p201001 values less THAN (201002), → PARTITION p201002 values Less THAN (201003), PARTITION p201003 VALUES less THAN (201004), , & nbsp PARTITION p201004 values less THAN (201005), - PARTITION p201005 values less THAN (201006) , PARTITION p201006 VALUES less THAN (201007), → partiti On p201007 values less THAN (201008), PARTITION p201008 values less THAN (201009),   ; PARTITION p201009 VALUES less THAN (201010), → PARTITION pcatch All Vlaues LESS THAN MAXVALUE ); Query OK, 0 rows affected (0.20 sec) 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 Delete partition-When a partition is deleted, it also deletes the All the data in the partition. mysql> ALTER TABLE sale_data DROP PARTITION p201010; Query OK, 0 rows affected (0.22 sec) www.2cto.com records:0 duplicates:0 warnings:0 Partitioned Merge the following SQL to merge p201001-p201009 into 3 partitions p2010q1-p2010q3 mysql> ALTER TABLE sale_data RE ORGANIZE PARTITION p201001,p201002,p201003, &N Bsp p201004,p201005,p201006, → and nbsp p201007,p201008,p201009 into → ( &nbsP PARTITION p2010q1 VALUES less THAN (201004), → PARTITION p2010q2 VALUE S less THAN (201007), PARTITION p2010q3 VALUES less THAN (201010) - >); Query OK, 0 rows affected (1.14 sec) records:0 duplicates:0 warnings:0 partition The following SQL, will p2010q1 partitions, split into s2009 and s2010 two partitions mysql> ALTER TABLE sale_data REORGANIZE PARTITION p2010q1 into ( -& Gt PARTITION s2009 VALUES less THAN (201001), www.2cto.com &nbs P 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: C reate TABLE ts (id INT, purchased DATE) engine=innodb PARTITION by RANGE (year (PURC hased))   Subpartition by hashing (ID) ( PARTITION p0 VALUES less THAN (1990)   ; ( subpartition s0 //a small partition under a large partition DATA directory= '/usr/local/mysq L/data0 ' //data sources INDEX directory= '/usr/local/mysql/index0 ', &N Bsp Index data source subpartition s1 Data DIR ectory= '/usr/local/mysql/data1 ' INDEX directory= '/usr/local/mysql/ Index1 ' , PARTITION p1 VALUES less THAN (MAXVALUE) &NB Sp ( subpartition s2 & nbsp &NBsp DATA directory= '/usr/local/mysql/data1 ' INDEX directory= '/usr/local/ Mysql/index1 ', subpartition s3 &N Bsp DATA directory= '/usr/local/mysql/data2 ' INDEX directory= '/usr/local/ Mysql/index2 ' ) ); partition index limitation: 1, all partitions will 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 partition function can use a limited number of functions and expressions, for example, the function has 4 of the above. 5, the partition does not support foreign keys. www.2cto.com 6, cannot use load INDEX into CACHE 7, partitioning does not always improve performance for performance evaluation. For example, you can use Expalin partitions to see if a query statement filtered data using partitions: mysql> explain partitions select * from Fenqubiao where day< ' 2011-09-1 2 '; +----+-------------+-----------+---------------+------+---------------+------+---------+------+------+--------- ----+ | 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 | &NBSP;2 | Using where | +----+-------------+-----------+---------------+------+---------------+------+---------+------+------+--------- ----+ 1 row in Set (0.00 sec)
Introduction to the use of MySQL partitioned tables
