Partitioning is to divide a table into multiple blocks for operation and storage, thus reducing the data of each operation and improving performance. It is transparent to the application. Logically, there is only one table, but physically, this table may be composed of multiple physical partitions, each of which is an independent object and can be handled independently.
Second, the role of zoning.
1. Logical data can be partitioned, and partitioned data can have multiple different physical file paths.
2. It can store more data and break through the maximum limit of a single file in the system.
3. Improve the performance, improve the reading and writing speed of each partition, and improve the query speed of the partition range.
4. You can delete data quickly by deleting related partitions.
5. By distributing data query to multiple disks, the performance of disk I/O is improved.
6. Queries involving aggregate functions such as SUM () and COUNT () can be easily processed in parallel.
7. Independent partition can be backed up and restored, which is beneficial to large data volume.
Third, the engines that the partition can support.
MySQL supports most engines to create partitions, such as MyISAM and InnoDB. MERGE and CSV are not supported to create partitions. All partitions in the same partition table must be the same storage engine. It is worth noting that in MySQL8 version 8, the MyISAM table engine does not support partitioning.
Fourth, confirm that MySQL supports partitioning.
Partition function has been introduced since MySQL5. 1. You can check whether it is supported in the following ways:
Old version used: Display variables like' %partition%';
New version: display plug-in;
Verb (abbreviation for verb) partition type
1. Range Partition: Allocate multiple rows to partitions according to the column values belonging to a given continuous interval.
For example, a table can be divided into two parts, 200 1 -20 10, 20 1 1-2020.
2. List partitioning: Similar to range partitioning, a list is an option, in which column values match values in a set of discrete values.
For example, by field, put the values of 1, 3 and 5 together, put the others of 2, 4 and 6 together, and so on. ...
3.HASH partition: Select partition according to the return value of the user-defined expression, and insert the column values of these rows into the table for calculation. This function must produce a non-negative integer value.
Partition by hash operation, and the distribution is relatively uniform.
4. Key partition: Similar to hash partition, MySQL server provides its own hash function.
Key partitioning is similar to partitioning by hash.
Matters needing attention in creating partition with intransitive verbs
1. If there is a primary key or unique key in the table, the partitioned column must be part of the primary key or unique key, that is, the column of the partition function can only take a subset from pk or uk.
2. If there is no primary key or unique key in the table, you can designate any column as a partitioned column.
Range, list and hash partition before version 3. 5.5 partition key must be int；; MySQL5.5 and above supports non-integer range and list partitioning, namely, range columns and list columns (which can be partitioned by strings).
Seven, partition naming
The 1. partition name basically follows the principles that other MySQL identifiers should follow, such as the identifiers used for table and database names. It should be noted that the names of partitions are case-insensitive.
2. No matter what type of partition is used, the partition is always numbered automatically when it is created, and the record starts from 0.
Eight, create a partition
1. Range partition:
Create the table "test0 1" (
Dayid' int( 1 1) defaults to NULL.
` mac` varchar(32) NOT NULL DEFAULT ' '，
` dtype ` varchar(50)NOT NULL DEFAULT ' '
)ENGINE = InnoDB DEFAULT CHARSET = utf8
/*! 50 100 partition by list (date)
(PARTITION p 20 17 1205 VALUES IN(20 17 1205)ENGINE = InnoDB，
Partition P20171204 value in (20171204) engine = innodb,
Partition P20171206 value in (20171206) engine = innodb,
(20171207) engine = partition p20 17 1207 value in innodb) */
Explanation: The above is put under partition p0 when uuid is less than 5, under partition P 10 when uuid is greater than 5, under partition p2 when uuid is greater than 15, and under partition p3 when uuid is greater than 15.
2. List partition:
Create table tbl_test (
Uuid INT is not empty.
title VARCHAR(20)
)
)
Partition by list (uuid) (
The partition p0 value in (1, 2, 3, 5),
Partition p 1 value in (7,9, 10),
P2 value of partition in (1 1, 15)
)
);
Explanation: When uuid is equal to 1/2/3/5, it is placed in partition p0, 7/9/ 10 in partition p 1 0, and115 in partition p2. If the value of uuid does not exist in p0/p 1/p2 partition when using insert into at this time, the insert will fail and report an error.
3. Hash partition:
Hash partition is mainly used to ensure that data is evenly distributed in a predetermined number of partitions. You must explicitly specify one or a set of specified column values in the range partition and the list partition to specify which partition should be saved. In HASH partition, MySQL will automatically complete these tasks. All it needs to do is to specify an expression according to the column value to be hashed, and specify how many partitions the partition table will be divided into, such as:
Create table tbl_test (
Uuid INT is not empty.
title VARCHAR(20)
))
Hash partition (uuid) (
Partition 3
));
Interpretation: MySQL automatically creates three partitions, and automatically allocates partitions according to the inserted uuid through the algorithm when inserting into.
note:
(1) Because the expression is evaluated every time a row is inserted, updated or deleted, this means that a very complex expression may cause performance problems, especially when performing operations that affect a large number of rows at the same time (such as bulk insertion).
(2) The most efficient hash function is to calculate only a single table column, and its value increases or decreases in accordance with the column value, because it considers the "pruning" on the partition range. That is to say, the closer the value of an expression is to the value change of the column on which it is based, the more effectively it can be used for hash partitioning.
3. 1: linear hash partition
LINEAR hash partition adds the keyword "linear" in the "PARTITION BY" clause.
The advantage of linear hash partition is that it is faster to add, delete, merge and split partitions, which is beneficial to deal with tables containing a large amount of data. Its disadvantage is that the data distribution between partitions is unlikely to be balanced.
4. Key partition
Similar to HASH partition, HASH partition allows user-defined expressions, while KEY partition does not allow user-defined expressions. HASH partition only supports integer partition, and KEY partition supports other data types except blob and text partition.
Unlike hash partitioning, you do not need to specify a partition key when creating a key partition table. By default, you will choose to use primary key or unique key as partition key. If there is no primary key or unique key, you must specify a partition key.
Create table tbl_test (
Uuid INT is not empty.
title VARCHAR(20)
))
Partition by linear key (uuid)
Partition 3;
Explanation: partition according to partition key.
5. Sub-partition
Sub-partition is the re-partition of each partition in the partition table, which is suitable for storing a very large amount of data.
Create table tbl_test (
Registration time and date
))
By fighting (year (registration time))
Partition by hash (TO_DAYS(registerTime))
Sub-partition 2
(
The partition p0 value is less than (20 17),
Partition p 1 value is less than (2020),
The value of partition p2 is less than the maximum value.
);
Interpretation: The main partition is divided into three sections by year. These three partitions are further divided into two sub-partitions. In fact, the whole table is divided into 3 * 2 = 6 partitions. Each subpartition is hashed by day. Those less than 20 17, those less than 20 17-2020 and those greater than 2020 are put together.
note:
(1) In MySQL5. 1, you can partition a table that has been partitioned by range or list. A child partition can use a hash partition or a key partition. This is also called compound partition.
(2) Each partition must have the same number of sub-partitions.
(3) If subdivision is used to explicitly define any sub-partition on any partition of the partition table, all sub-partitions must be defined.
(4) Each partition clause must contain (at least) the name of one sub-partition.
(5) In each sub-partition, the name of the sub-partition must be unique, and it should be unique in the whole table at present. For example:
YEAR(registerTime)
Partition by hash (TO_DAYS(registerTime))
(
Partition p0 value is less than (20 17) (
Sub-partition s0,
Sub-partition s 1
),
Partition p 1 value is less than (2020) (
Sub-partition s2,
Sub-partition s3
),
The value of partition p2 is less than the maximum value (
Sub-partition s4,
Sub-partition s5
)
)
Sub-partitions can be used for very large tables, and data and indexes can be distributed on multiple disks. For example:
Subpartition s0
Data directory = '/disk0/data'
Index directory = '/disk0/idx'
,
,
Sub-partition s 1
Data directory = '/disk 1/data'
Index directory = '/disk 1/idx'
Nine, MySQL partition processing null values
Partition in MySQL prohibits processing NULL values, whether it is a column value or a user-defined expression value. Generally speaking, MySQL will treat null as 0 in this case. If you want to avoid this situation, you should declare the column as "NOT NULL" when designing the table.
X. Overview of partition management
You can add, delete, redefine, merge or split partitions.
(1) scope and list partition management
1. Delete partition statements, such as alter table TBL _ test drop partition P0;
note:
(1) When deleting a partition, all data in the partition will also be deleted.
(2) You can use show create table tbl _ test to view the newly created statement to create a table.
(3) If it is a list partition, deleted data cannot be added, because the column values of these rows are included in the value list of the deleted partition.
2.Add partition statements such as: alter table TBL _ test add partition (partition P3 value is less than (50));
note:
(1) For range partitioned tables, only new partitions can be added to the top of the partition list.
(2) For the list partition table, you can't add any values that are already included in the existing partition value list.
3. If you want to redefine the partition without losing data, you can use the following statement:
ALTER TABLE tbl_name reorganizes partition_list into (partition_definitions).
(1) Partition as follows:
ALTER TABLE tbl_name reorganizes partition_list into (partition s0 value is less than (5), partition s 1 value is less than (10));
Or as follows:
ALTER TABLE tbl_name reorganizes partition p0 into (partition s0 has a value of (1, 2,3) and partition s 1 has a value of (4,5));
(2) merging partitions, such as: alter table TBL _ name reorganizes partition s0, s 1 into (partition P0 has a value of (1, 2,3,4,5));
4. Delete all partitions, but keep the data, in the form of: alter table TBL _ name remove partition;
② Management of hash and key partition
1. statement to reduce the number of partitions, such as: alter tabletbl _ name coalescipartition2;
2.Add partition number statement, such as: alter table TBL _ name add partition partitions2;
③ Other partition management statements
1. Rebuild partition: similar to deleting all the records saved in a partition and then reinserting them, it can be used to defragment the partition. For example, Alter Table TBL _ Name rebuilds partitions P2 and P3;
2. Optimize partition: If a large number of rows are deleted from the partition, or if a large number of changes are made to rows with variable length (that is, columns with VARCHAR, BLOB or TEXT types), you can use Alter Table TBL _ Name to optimize the partition to recover unused space and defragment the partition data file. Such as: alter table TBL _ name optimization partition P2, P3;
3. Analyze partitions: read and save the key distribution of partitions, such as: alter table TBL _ name analyze partitions P2 and P3;
4. Check the partition: check whether the data or index in the partition has been damaged, such as: alter table TBL _ name check the partitions P2 and P3;
5. Repair partitions: repair damaged partitions, such as alter table TBL _ name to repair partitions P2 and P3;
X. View partition information
1. View partition information: select * from information _ schema.partitions where table _ schema =' arch 1' and table _ name =' TBL _ test' g;
2. Check the data on the partition: select * from TBL _ test partition (P0);
3. Check the partitions that MySQL can operate: explain the partition Select * From TBL _ Test where UUID = 2；;
XI。 limit
1. The maximum number of partitions cannot exceed 1024, and it is generally recommended that the number of partitions in a single table should not exceed 50.
2. If there is a unique index or primary key, the partitioned column must be included in all unique indexes or primary keys.
3. Foreign keys are not supported.
4. Full-text indexing is not supported. If an index is created on the partition key of a partitioned table, the index will also be partitioned.
5. It is appropriate to partition by date, because many date functions can be used. But there are not many partition functions suitable for strings.
6. Only range and list partitions can be sub-partitioned, while hash and key partitions cannot be sub-partitioned.
7. Temporary tables cannot be partitioned.
8. Partitioned tables have no advantage for querying a single record.
9. Pay attention to the cost of selecting partitions. Without inserting a row of data, you need to filter the inserted partition according to the expression.
10. The partition field should be as empty as possible.