Basic Mysql partition management operations _ MySQL

Basic Mysql partition management operations:

I haven't written a blog for a long time. I have been learning Mysql partition for the past two days. Summary:

Mysql supports Horizontal partitioning and does not support Vertical partitioning;

Horizontal partitioning: Records of different rows in the same table are allocated to different physical files;

Vertical partitioning: Records of different columns in the same table are allocated to different physical files;

CSV, FEDORATED, MERGE and other engines do not support partitions, while MYISAM, InnoDB, and NDB engines support partitions.

Purpose:

A table or index is divided into several smaller and more manageable parts. logically, there is only one table or index, however, the physical table or index may consist of dozens of physical partitions. no partitions are independent objects and can be processed independently, it can also be processed as a part of a larger object (if the partition table is large, partitions can also be allocated to different disks); when performing a query, the optimizer will filter partitions that do not have the data we need according to the partition definition, so that the query does not need to scan all the partitions in the table, just find the partitions that contain the data.

Applicable scenarios:

1. the table is so large that it cannot all be stored in the memory, or there is only hotspot data in the last part of the table. Others are historical data.
2. partition table data is easier to maintain (you can optimize, check, repair, and batch delete partitions independently. you can use drop partitions to delete big data)
3. data in the partition table can be distributed across different physical devices to efficiently utilize multiple hardware devices.
4. partition tables can avoid some special bottlenecks (ps: mutex access to a single index of InnoDB, inode lock competition of ext3 file system, etc)
5. independent partitions can be backed up and restored, which is very suitable for scenarios with large datasets.

Partition Table restrictions:

1. A single table supports up to 1024 partitions.
2. MySQL5.1 can only partition integer columns of a data table, or data columns can be converted to integer columns through the partition function. The range list type of MySQL5.5 can be directly partitioned using columns.
3. If a partition field contains a primary key or a column with a unique index, all primary key columns and unique index columns must be included.
4. The partition table cannot use the foreign key constraint.
5. The same Engine must be used for partitioning.
6. For MyISAM partition tables, you cannot use the load index into cache operation.
7. For a MyISAM partition table, more file descriptors will be opened during use (a single partition is an independent file)

Partition policy:

1. Full data scanning without any index: you can use the where condition to locate which partition. you must limit the number of partitions to be scanned to a very small number.
2. Create a partition index to separate the hotspot: for example, to separate the obvious hotspot data into a partition, so that it can be cached to the memory as much as possible, so that the index and cache can be fully used.
   
   Note:: All of the above policies are filtered by queries, and additional partitions are discarded. The Partitions themselves do not produce additional costs]

Difficulties in using partitioned tables:

1. The NULL value will invalidate partition filtering:
   
   The table sharding expression can be NULL. the First Partition stores NULL or invalid values for a special partition.
   
   For example, if partition by range year (order_date) is partitioned, the value of order_date is NULL or invalid, and the record is stored in the first PARTITION:
   
   WHERE order_date BETWEEN '2017-01-01 'AND '2017-01-31' check two partitions:
   
   The first partition and the December partition avoid a high query cost when the data in the first partition is too large. you can use: create the first partition to store records whose order_date is NULL and invalid values.
   PARTITION p_nulls values less than (0)
   
   After MySQL5.5, you can use the following syntax to solve the problem:
   Partition by range columns (order_date)

2. the partition column and index column do not match

In this case, the query cannot perform partition filtering, and the partition is invalid unless the query contains conditions for filtering partitions.

3. as the number of partitions increases, the pressure on MYSQL to query the partition definition list (which partition meets the conditions) is increased. limit the number of partitions as much as possible; key and hash partitions do not exist.

4. reorganizing partitions or statements similar to alter statements may cause large overhead.

The operation to create or delete a partition is very fast. a new partition or similar ALTER statement will first create a temporary partition, copy the data, and then delete the original partition.

Partition table type:

1. RANGE partitioning: row data is partitioned based on the column values that belong to a given continuous interval.

MySQL5.5 began to support range columns partitions (the introduction of Columns partitions solved the problem that RANGE partitions and LIST partitions supported only integer partitions before MySQL 5.5, this leads to the need for additional function calculations to obtain integers or to convert them to integers and then partition through an additional conversion table. Columns partitions can be subdivided into RANGE Columns partitions and LIST Columns partitions. RANGE Columns partitions and LIST Columns partitions support three data types: integer, date and time, and string)

2. LIST partitioning: Similar to partitioning by RANGE, the difference is that LIST partitioning is based on column values matching a value in a discrete value set.

MySQL5.5 began to support range columns partitions
3. HASH partition: partitions are based on the return values of user-defined expressions. The return value cannot be negative.
4. KEY partitioning: partitions are based on the hash function provided by the MySQLS database.
[Note: No matter what type of partition you create, if a table contains a primary key or a unique index, the partition column must be a part of a unique index]

Partition query:

Check whether the current database supports partitioning mysql> show variables like '% partition % '; + partitions + ------- + | Variable_name | Value | + partitions + ------- + | have_partitioning | YES | innodb_adaptive_hash_index_partitions | 1 | + partitions + ------- + 2 rows in set view the CREATE statement for creating a partition table mysql> show create table operation_log; check whether the table is a partition table (Create_options) mysql> show Table status (status of all tables in the current database) mysql> show table status from lockrank like '% operation_log %'; (status of the operation_log table in the lockrank database) * *************************** 1. row *************************** Table: operation_logCreate Table: create table 'Operation _ log' ('id' int (11) unsigned not null AUTO_INCREMENT, 'CID' mediumint (7) unsigned not null, 'accountid' mediumint (8) not null default '0', 'flag' tinyint (1) unsigned NOT Null default '0', 'addtime' int (11) 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 Records '/*! 50100 partition by range (addtime) (PARTITION '2017-05 'values less than (2013) ENGINE = InnoDB, PARTITION '2017-06' values less than (1370016000) ENGINE = InnoDB, PARTITION '2014-07 'values less than (2013) ENGINE = InnoDB, PARTITION '2014-08' values less than (1375286400) ENGINE = InnoDB, PARTITION '2017-09 'values less than (2013) ENGINE = InnoDB, PARTITION '2017-10' values less than (1380556800) ENGINE = InnoDB, PARTITION '2017-11' values less than (2013) ENGINE = InnoDB, PARTITION '2017-12' values less than (1385827200) ENGINE = InnoDB, PARTITION '2014-01 'values less than (2014) ENGINE = InnoDB, PARTITION '2014-02' values less than (1391184000) ENGINE = InnoDB, PARTITION '2014-03 'values less than (2014) ENGINE = InnoDB, PARTITION '2014-04' values less than (1396281600) ENGINE = InnoDB, PARTITION '2017-05 'values less than (2014) ENGINE = InnoDB, PARTITION '2017-06' values less than (1401552000) ENGINE = InnoDB, PARTITION '2014-07 'values less than (2014) ENGINE = InnoDB, PARTITION '2014-08' values less than (1406822400) ENGINE = InnoDB, PARTITION '2017-09' values less than maxvalue engine = InnoDB) */1 row in set (2014 sec) view how the select statement uses the PARTITION mysql> explain partitions select id, accountid, cid, flag from operation_log where addtime = "1369362524 USD/g; ***************************** 1. row ************************* id: 1 select_type: SIMPLEtable: operation_log partitions: 2013-05 type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4384356 Extra: Using where1 row in set (0.00 sec) ''' partition table metadata statistical table: explain to view the partition information of the partition table operation_log mysql> SELECT partition_name part, partition_expression expr, partition_description descr, table_rows FROM region WHERE TABLE_SCHEMA = schema () AND TABLE_NAME = 'Operation _ log '; + --------- + ------------ + | part | expr | descr | table_rows | + --------- + ------------ + ---------- + | 2013-05 | addtime | 1370016000 | 5999642 | 2013 -06 | addtime | 1372608000 | 4579263 | 2013-07 | addtime | 1375286400 | 3223772 | 2013-08 | addtime | 1377964800 | 1995058 | 2013-09 | addtime | 1380556800 | 2497406 | 2013-10 | addtime | 1383235200 | 4106974 | 2013-11 | addtime | 1385827200 | 6209559 | 2013-12 | addtime | 1388505600 | 6415349 | 2014-01 | addtime | 1391184000 | 3953594 | 2014-02 | addtime | 1393603200 | 0 | 2014-03 | addtime | 1396281600 | 0 | 2014-04 | addtime | 1398873600 | 0 | 2014-05 | addtime | 1401552000 | 0 | 2014-06 | addtime | 1404144000 | 0 | 2014-07 | addtime | 1406822400 | 0 | 2014-08 | addtime | 1409500800 | 0 | 2014-09 | addtime | MAXVALUE | 0 | + --------- + ------------ + 17 rows in set (1.48 sec)

Create a partition

RANGE partitioning: mysql> create table 'Operation _ log' (-> 'id' int (11) unsigned not null AUTO_INCREMENT,-> 'CID' mediumint (7) unsigned not null, -> 'accountid' mediumint (8) not null default '0',-> 'flag' tinyint (1) unsigned not null default '0 ', -> 'addtime' int (11) 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 Records'-> /*! 50100 partition by range (addtime)-> (PARTITION '2017-05 'values less than (2013) ENGINE = InnoDB,-> PARTITION '2017-06' values less than (1370016000) ENGINE = InnoDB,-> PARTITION '2014-07 'values less than (2013) ENGINE = InnoDB,-> PARTITION '2014-08' values less than (1375286400) ENGINE = InnoDB, -> PARTITION '2017-09' values less than maxvalue engine = InnoDB) */; 1 row in set (2013 0 sec) (less than maxvalue takes into account the maximum possible value) list partition // this method fails mysql> create table if not exists 'list _ part' (-> 'id' int (11) not null AUTO_INCREMENT COMMENT 'user ID ', -> 'Province _ id' int (2) not null default 0 COMMENT 'Province ',-> 'name' varchar (50) not null default ''comment 'name ', -> 'sex' int (1) not null default '0' COMMENT '0 is male, 1 is female,-> primary key ('id')->) ENGINE = innodb default charset = utf8 AUTO_INCREMENT = 1 -> Partition by list (province_id) (-> PARTITION p0 values in (1, 3, 4, 5, 6, 7, 8),-> PARTITION p1 values in (9, 10, 11, 12, 16, 21 ), -> PARTITION p2 values in (1503,),-> PARTITION p3 values in (,)->); ERROR (HY000 ): a primary key must include all columns in the table's partitioning function // this method succeeds. mysql> create table if not exists 'list _ part' (-> 'id' int (11) NOT NULL COM MENT 'user ID',-> 'Province _ id' int (2) not null default 0 COMMENT 'Province ',-> 'name' varchar (50) not null default ''comment' name',-> 'sex' int (1) not null default '0' COMMENT '0 is male, 1 is 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),-> PARTITIO N p3 values in (17,18, 20,22, 0.33)->); Query OK, 0 rows affected (sec) when you create a list partition, if there is a primary shard, the primary key must be in the partition, or an error will be reported. If I do not need a primary key, the partition is successfully created. generally, a table will have a primary key, this is a partition's limitation. hash partition mysql> create table if not exists 'hash _ part' (-> 'id' int (11) not null AUTO_INCREMENT COMMENT 'Comment ID ', -> 'comment' varchar (1000) not null default ''comment 'comment',-> 'IP' varchar (25) not null default ''comment 'source IP ', -> primary key ('id')->) ENGINE = innodb default charset = utf8 AUTO_INCREMENT = 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 (11) not null comment 'newsid',-> 'content' varchar (1000) not null default ''comment' news content',-> 'U _ id' varchar (25) not null default ''comment' source IP address ',-> 'Create _ time' date not null default '2017-00-00 00:00:00 'comment' time'->) ENGINE = innodb default charset = utf8-> partition by linear hash (YEAR (create_time)-> PARTITIONS 3; Query OK, 0 rows affected (0.07 sec)

Add sub-partition operation:

A subpartition is the re-division of each partition in a partition table. a subpartition can use both HASH and KEY partitions. This is also called composite partitioning ).

1. if a subpartition is created in one partition, other partitions must have subpartitions. if a partition is created, the number of subpartitions in each partition must be the same. subpartitions in the same partition have different names. subpartitions in different partitions can have the same names (5.1.50 is not applicable) mysql> create table if not exists 'sub _ part' (-> 'news _ id' int (11) not null comment 'news ID ', -> 'content' varchar (1000) not null default ''' COMMENT 'news content',-> 'U _ id' int (11) not null default 0 s COMMENT 'source IP',-> 'Create _ time' date not null default '2017-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) (SUBPARTITION s0, SUBPARTITION s1, SUBPARTITION s2),-> PARTITION p1 values less than (2000) (SUBPARTITION s3, SUBPARTITION s4, SUBPARTITION good ), -> PARTITION p2 values less than maxvalue (SUBPARTITION tank0, SUBPARTITION tank1, SUBPARTITION tank3)->); Query OK, 0 rows affected (0.07 sec)

Partition management:

Add partition operation (for setting MAXVALUE) range add partition mysql> alter table operation_log add partition (partition '2017-10' values less than (2013 )); ---> apply to add ERROR 1481 (HY000) to partitions without MAXVALUE settings ): MAXVALUE can only be used in last partition definitionmysql> alter table operation_log REORGANIZE partition '2017-09 'into (partition '2017-09' values less than (2013 ), partition '2017-10' values less than (2013), partition '2017-11' values less than maxvalue ); list add partition mysql> alter table list_part add partition (partition p4 values in (0.01, 28); Query OK, 0 rows affected (sec) Records: 0 Duplicates: 0 Warnings: 0 hash re-partition mysql> alter table list_part add partition (partition p4 values in (0.01, 28); Query OK, 0 rows affected (sec) Records: 0 Duplicates: 0 Warnings: 0 key re-partitioning mysql> alter table key_part add partition partitions 4; Query OK, 1 row affected (0.06 sec) // The Records will also be reassigned if data is found: 1 Duplicates: 0 Warnings: 0 subpartition add new partition, although I do not refer to the subpartition, however, the system will add partition (partition p3 values less than MAXVALUE) to the mysql> alter table subpartition part named in the subpartition; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0

Delete partitions

alter table user drop partition `2013-05`;

Other partition table operations

Re-partition (official: same effect as dropping all records first and then reinsert; used to sort table fragments) alter table operation_log rebuild partition '2017-01 '; recreate multiple partitions: alter table operation_log rebuild partition '2017-01', '2017-02 '. the procedure is as follows: pro optimized partitions (if you delete a large number of records for a partition or update many fields of the varchar blob text data type for a partition, at this time, you can optimize the partition to recycle unused space and sort out the partition data files.) alter table operation_log optimize partition '2017-01'; the optimization operation is equivalent to check partition, analyze partition and repair patition analysis partitions alter table operation_log analyze partition '2017-01 '; repair partition alter table operation_log repair partition '2017-01 '; check the partition alter table operation_log check partition '2017-01 ';

Note:

1. Mysqlcheck and myisamchk do not support partition tables, analyze, check, optimize, rebuild, repair, and truncate.
2. In MySQL5.6, you can clear a partition: alter table operation_log truncate partition2014-01;
3. Clear all partition data of the partition table: alter table operation_log truncate partition all;

Reference:

Http://blog.51yip.com/mysql/1013.html

Https://dev.mysql.com/doc/refman/5.6/en/partitioning-maintenance.html

Http://dev.mysql.com/doc/refman/5.6/en/index.html