SQL optimized MySQL table partitioning

A partitioned table is available for the following scenarios

1: The table is so big that it can't be put all in memory, or only the last part of the tag has hot data, the others are historical data

2: Partitioned table data is easier to maintain. For example, to bulk delete large amounts of data can be used to clear the entire partition way. In addition, a separate partition can be optimized, checked, repaired, and other operations.

3: Partitioned table data can be distributed across different physical devices to efficiently utilize multiple hardware devices

4: You can use a partitioned table to avoid some special bottlenecks. For example, mutually exclusive access to a single index of InnoDB, the Ext3 file system's Inode lock competition, and so on.

5: You can also back up and restore separate partitions if needed, which works well in very large datasets.

Two-partition principle and limitations

MySQL database data in the situation of the file exists on the disk, the default is placed under/mysql/data (can be viewed through the datadir in my.cnf), a table mainly corresponds to three files, one is the FRM storage table structure, one is myd storage table data,
One is the myi index of the saved table. If the data volume of a table is too large, then the myd,myi will become very large, the search data will become very slow, this time we can use the MySQL partition function,
In the physics of this table corresponding to the three files, divided into a number of small pieces, so that, we find a piece of data, we do not have to find all, as long as we know where this piece of data, and then find it on the line.
If the table's data is too large, it may not fit on a disk, so we can allocate the data to different disks.

Limitations of partitioned Tables

The principle of partitioned tables

SELECT query: When querying a partitioned table, the partition layer opens and locks all the underlying tables first, and the optimizer first determines whether a partial partition can be filtered and then calls the corresponding storage engine interface to access the data for each partition;

Insert operation: When writing a piece of data, the partition layer opens and locks all the underlying tables, then determines which partition receives the data, and then writes the records to the corresponding underlying table;

Delete operation: When deleting a record, the partition layer modern open and lock all the underlying tables, then determine the data corresponding to the partition, and finally to the corresponding bottom of the delete operation;

Update operation: When a record is updated, the partition layer opens and locks all the underlying tables first, MySQL first determines which partition the record needs to be updated, then takes out the data and updates, determines which partition the updated data should be placed in, and finally writes to the underlying table. and delete the underlying table where the original data resides.

Of course, some of these operations are filter-enabled. For example, when deleting a record, MYSQL needs to find the record first, and if the Where condition matches the partition expression, then all partitions that do not contain the record can be filtered out. The same operation is also valid for update. If it is an insert operation, it is itself hitting only one partition, and the other partitions will be filtered out. MySQL first determines which partition this record belongs to, and then writes the record to the corresponding underlying partition table without having to operate on any other partition. (Although each operation will "" "opens and locks all underlying tables", it does not mean that the partitioned table locks the full table during processing, and if the storage engine is able to implement row-level locks on its own, such as InnoDB, the corresponding table lock is released at the partition layer).

Type of partition table: MySQL supports multiple partition tables

The most common is partitioning by scope, where each partition stores records that fall within a range, and the partition expression can be either a column or a column expression. The following example stores sales for each year in separate partitions.

Three     create partition operation range partition:mysql> create TABLE ' Operation_log ' (-id ' int ') unsigned not NULL auto_increment,- > ' CID ' mediumint (7) unsigned not null,-> ' AccountId ' mediumint (8) Not NULL DEFAULT ' 0 ',-> ' flag ' tinyint (1) UNS igned NOT NULL default ' 0 ',-> ' addtime ' int (one) unsigned not null,-> ' device ' tinyint (1) unsigned NOT NULL default ' 1 ',-> PRIMARY key (' id ', ' addtime '),-> key ' Idx_accountid_addtime ' (' AccountId ', ' addtime '),-> key ' Idx_ Accountid_flag ' (' accountid ', ' flag '),->) engine=innodb auto_increment=50951039 DEFAULT Charset=utf8 comment= ' operation record '->/*!50100 PARTITION by RANGE (addtime), PARTITION ' 2013-05 ' VALUES less THAN (1370016000) ENGINE = innodb,-> P Artition ' 2013-06 ' values less THAN (1372608000) ENGINE = innodb,-> PARTITION ' 2013-07 ' values less THAN (1375286400) E Ngine = innodb,-> PARTITION ' 2013-08 ' values less THAN (1377964800) ENGINE = innodb,-> PARTITION ' 2013-09 ' values LE SS THAN MAXVALUE ENGINE = InnoDB) */;1 row in Set (0.00 sec) (less THAN maxvalue considering possible maximum)  list partition//This mode fails mysql> CREATE TABLE IF not EXISTS ' List_part ' (-& Gt ' id ' int (one) not NULL auto_increment COMMENT ' user ID ',-> ' province_id ' int (2) NOT null DEFAULT 0 COMMENT ' province ',-> ' name ' varchar (+) NOT null default ' COMMENT ' name ',-> ' sex ' int (1) NOT null default ' 0 ' COMMENT ' 0 for male, 1 for female ',-> PRIMARY KEY (' id ') engine=innodb DEFAULT Charset=utf8 auto_increment=1-> PARTITION by LIST (province_id) (PARTITION p0 Values in (1,2,3,4,5,6,7,8),-> PARTITION p1 values in (9,10,11,12,16,21),-> PARTITION p2 values in (13,14,15,19),-& Gt PARTITION P3 VALUES in (17,18,20,22,23,24); ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table ' s partitioning function //this way successfully mysql> CRE ATE TABLE IF not EXISTS ' List_part ' (-id ' int ') NOT NULL COMMENT ' user ID ',-> ' province_id ' int (2) NOT NULL Defaul T 0 COMMENT ' province ',-> ' name ' varchar (a) Not NULL DEFAULT ' COMMENT ' name ',-> ' sex ' inT (1) Not NULL default ' 0 ' COMMENT ' 0 for male, 1 for female '--engine=innodb default charset=utf8-> PARTITION by LIST (province_id) (PARTITION p0 values in (1,2,3,4,5,6,7,8),-> PARTITION p1 values in (9,10,11,12,16,21),-> PARTITION p2 values In (13,14,15,19),-> PARTITION P3, VALUES in (17,18,20,22,23,24)); Query OK, 0 rows affected (0.33 sec) above the creation of the list partition, if there is a master 銉, the partition when the primary key must be in it, otherwise it will be an error. If I do not use the primary key, the partition is created successfully, in general, a table will certainly have a primary key, which is considered a partition of the limitations of  hash partition mysql> CREATE table IF not EXISTS ' Hash_part ' (-ID ' Int (one) not null auto_increment COMMENT ' Comment id ',-> ' COMMENT ' varchar (+) NOT null DEFAULT ' COMMENT ' comment ',-> ' IP ' varchar (+) not NULL default ' COMMENT ' source IP ',-> PRIMARY KEY (' id ')--engine=innodb default Charset=utf8 auto_in Crement=1-> PARTITION by HASH (ID), partitions 3; Query OK, 0 rows affected (0.06 sec)  key partition mysql> CREATE TABLE IF not EXISTS ' Key_part ' (--' news_id ' int (one) NO T NULL COMMENT ' news ID ',-> ' content ' varchar ($) NOT NULL DefauLT ' COMMENT ' news content ',-> ' u_id ' varchar ' NOT null default ' COMMENT ' source IP ',-> ' create_time ' DATE NOT NULL default ' 0000-00-00 00:00:00 ' COMMENT ' time ') Engine=innodb DEFAULT charset=utf8-> PARTITION by LINEAR HASH (year (Create_ti ME))-Partitions 3; Query OK, 0 rows affected (0.07 sec)    adding sub-partitioning operations

子分区是分区表中每个分区的再次分割，子分区既可以使用HASH希分区，也可以使用KEY分区。这 也被称为复合分区（composite partitioning）

1. If a sub-partition is created in a partition, the other partitions also have sub-partitions
2. If you create a partition, the number of sub-partitions in each partition must be the same
3. Sub-partitions within the same partition, names are different, sub-partition names can be the same in various partitions (5.1.50 not applicable)

12345678910111213

mysql> CREATE TABLE IF not EXISTS ' Sub_part ' (--"news_id ' int (one) not NULL COMMENT ' news id ', ' content ' varchar (+) NOT null default ' COMMENT ' news content ', ' u_id ' int (one) not null default 0s COMMENT ' source ip ', ' create_time ' DA TE not NULL default ' 0000-00-00 00:00:00 ' COMMENT ' time ') Engine=innodb DEFAULT Charset=utf8-PARTITION by RANGE (Year (Create_time)), subpartition by HASH (To_days (Create_time)), PARTITION p0 VALUES less THAN (1990) (Subparti tion s0,subpartition s1,subpartition s2), PARTITION p1 VALUES less THAN (subpartition s3,subpartition S4,SUBPA Rtition good), PARTITION p2 VALUES less THAN MAXVALUE (subpartition tank0,subpartition tank1,subpartition tank3)-> ; ); Query OK, 0 rows affected (0.07 sec)

Partition Management

Increase partition operation (for set MaxValue)
Range Add partition

123

Mysql>alter table Operation_log Add partition (partition ' 2013-10 ' values less than (1383235200)); ---> For partitions without setting MAXVALUE add ERROR 1481 (HY000): MAXVALUE can only is used in last partition Definitionmysql>alter table Operation_log REORGANIZE partition ' 2013-09 ' into (partition ' 2013-09 ' values less than (1380556800), partition ' 2013-10 ' V Alues less than (1383235200), partition ' 2013-11 ' values less than maxvalue);

List Add partition

123	Mysql> ALTER TABLE List_part add partition (partition P4 values in (25,26,28)); Query OK, 0 rows affected (0.01 sec) records:0 duplicates:0 warnings:0

Hash re-partitioning

123	Mysql> ALTER TABLE List_part add partition (partition P4 values in (25,26,28)); Query OK, 0 rows affected (0.01 sec) records:0 duplicates:0 warnings:0

Key re-partitioning

123	Mysql> ALTER TABLE Key_part add partition partitions 4; Query OK, 1 row affected (0.06 sec)//data will also be reassigned records:1 duplicates:0 warnings:0

Sub-partition to add a new partition, although I do not specify a sub-partition, but the system will give the sub-partition named

123	Mysql> ALTER TABLE Sub1_part ADD partition (partition P3 values less than MAXVALUE); Query OK, 0 rows affected (0.02 sec) records:0 duplicates:0 warnings:0

Delete partition operation

alter table user drop partition2013-05;

Partitioning table Other Operations

1234567891011121314151617181920

Rebuild partition (official: With drop all records then reinsert is the same effect; Used to defragment table fragments) ALTER TABLE Operation_log rebuild partition ' 2014-01 '; rebuild multiple partitions ALTER TABLE Operation_log rebuild partition ' 2014-01 ', ' 2014-02 '; the process is as follows: Pro optimizes partitions (if you delete a large number of records for a partition or a varchar blob to a partition) There are many updates to the fields of the text data type, at which time the partitions can be optimized to reclaim unused space and defragment the partition data file) ALTER TABLE Operation_log optimize partition ' 2014-01 '; Optimized operation equivalent to check partition,analyze partition and repair patition analysis partition ALTER TABLE Operation_log analyze partition ' 2014-01 '; Repair partition ALTER TABLE Operation_log repair partition ' 2014-01 '; Check partition ALTER TABLE Operation_log check partition ' 2014-01 ';

SQL optimized MySQL table partitioning