Note: the content of the article originated from the network and was obtained through our own experiment; however, the reference article address was not recorded at that time. If you find any infringement problem, please leave a message.
~
~
When the partition is MySQL version 5.1, support for partition has been added.
The process of partitioning is to decompose a table or index into smaller and more manageable parts. Each partition is an independent object, which can be processed independently or as a part of a larger object.
MySQL database supports horizontal partition and does not support vertical partition.
In addition, the partition of MySQL database is the index of the local area. In one partition, both data and index are stored. Global partition means that data is stored in each partition, while index is stored in an object. At present, MySQL database does not support global partition.
MySQL 5.6 makes partition exchange a reality. You only need to use the alter table... Exchange partition statement, that is, you can move the data in a partition to other (independent) tables in a short time.
If there are partitioned table P and non partitioned table t, except for the different partition attributes, the other column attributes are the same. Then EXCHANGE PARTITION can be used to exchange the data in a partition of P and t table; that is, the data in a partition of P will be cut and stored in T, and the data in T will be cut and stored in P partition. With this function, t table data can be loaded into the partition or exported to t table.
This operation requires forever ALTER, INSERT, CREATE, DROP permission.
1, Experiment
Experiment 1: exchange primary partition
Note: the partition viacom in matchedVideo has 2410806 rows of records. If you want to back up this part of data, MySQL 5.1 needs to create a new temporary table, and then export this part of data;
1> Backup a partition data
mysql> create table matchedVideo4 like matchedVideo; Query OK, 0 rows affected (1 min 13.84 sec) mysql> insert into matchedVideo4 select * from matchedVideo where company_id = 14; Query OK, 2410806 rows affected (12 min 28.41 sec) Records: 2410806 Duplicates: 0 Warnings: 0
2> Delete a partition data (partition reservation)
mysql> delete from matchedVideo4 where company_id = 14; Query OK, 2410806 rows affected (8 min 47.76 sec)
--The execution time of insert+delete is about 20 minutes.
However, MySQL 5.6 has greatly improved its performance and greatly shortened its time.
mysql> create table matchedVideo2 like matchedVideo; Query OK, 0 rows affected (1 min 13.84 sec) mysql> ALTER TABLE matchedVideo2 REMOVE PARTITIONING; Query OK, 0 rows affected (16.88 sec) Records: 0 Duplicates: 0 Warnings: 0
--Delete partition information so that matchedvideo 2 is called a common table with the same column attribute, so as to put single partition data into a single table for archiving.
mysql> select * from matchedVideo where company_id = 14; +----------+ | count(*) | +----------+ | 2410806 | +----------+ 1 row in set (0.00 sec) mysql> ALTER TABLE matchedVideo EXCHANGE PARTITION Viacom WITH TABLE matchedVideo2; Query OK, 0 rows affected (0.67 sec)
--Exchange data of partition Viacom to normal table
mysql> select count(*) from matchedVideo2; +----------+ | count(*) | +----------+ | 2410806 | +----------+ 1 row in set (4.24 sec) mysql> select * from matchedVideo where company_id = 14; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec)
2, The exchange partition shall follow the following principles:
1> The exchanged table has no partition but has the same structure
2> Records in an unpartitioned table must be within the partition or subpartition scope of another table
mysql> ALTER TABLE matchedVideo EXCHANGE PARTITION Viacom WITH TABLE matchedVideo2; Query OK, 0 rows affected (0.58 sec) mysql> update matchedVideo2 set company_id = 10 where id = 75537347; Query OK, 1 row affected (0.23 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> ALTER TABLE matchedVideo EXCHANGE PARTITION Viacom WITH TABLE matchedVideo2; ERROR 1737 (HY000): Found a row that does not match the partition
3> Foreign key is not allowed in exchange table
Partition in different engines:
The partition function is not completed in the storage engine layer, so it is not only the InnoDB storage engine that supports partition. The common storage engines, such as MyISAM and NDB, support partition, but not all of them. CSV, FEDORATED and MERGE are not supported.
Let's look at the differences between InnoDB and MyISAM:
Whether it's version 5.1 or 5.6, it's the same when the engine processes data without partitions
Innodb
mysql> create table t (
-> id int) engine=innodb
-> partition by range (id) (
-> partition p0 values less than (10),
-> partition p1 values less than (20));
Query OK, 0 rows affected (3.25 sec)
root@test 11:30:42>insert into t(id) values(3),(4),(10),(13),(21);
ERROR 1526 (HY000): Table has no partition for value 21
root@test 11:32:18>select * from t;
Empty set (0.00 sec)MyISAM
mysql> create table tt (
-> id int) engine=myisam
-> partition by range (id) (
-> partition p0 values less than (10),
-> partition p1 values less than (20));
Query OK, 0 rows affected (0.10 sec)
root@test 11:31:10>insert into tt(id) values(3),(4),(10),(13),(21);
ERROR 1526 (HY000): Table has no partition for value 21
root@test 11:31:59>select * from tt;
+------+
| id |
+------+
| 3 |
| 4 |
| 10 |
| 13 |
+------+
4 rows in set (0.00 sec)It can be seen that when inserting multi row record data, it does not exist in the partition, InnoDB will roll back all of it, and MyISAM will roll back from the wrong row, and the previous rows will be inserted.
3, Division and classification
Partition classification includes: Range, List, Hash, Key partition
mysql5.5 started to support columns partition, which is regarded as an evolution of RANGE partition and LIST partition. Columns partition can directly use non integer data for partition, and the partition can be directly compared according to the type, and does not need to be converted to integer. In addition, columns partition can partition the values of multiple columns to support INT,SMALLINT,TINYINT,BIGINT.DATE,DATETIME,CHAR,VARCHAR,BINARY,VARBINARY type
MySQL5.1
root@test 11:57:19> CREATE TABLE t_columns_range51(
-> a INT,
-> b DATETIME) ENGINE=INNODB
-> PARTITION BY RANGE (TO_DAYS(b))
-> (PARTITION p0 VALUES LESS THAN(TO_DAYS('2009-01-01')),
-> PARTITION p1 VALUES LESS THAN(TO_DAYS('2010-01-01')));
Query OK, 0 rows affected (0.42 sec)
root@test 11:57:19>insert into t_columns_range51(a,b) values(1,'2009-02-01');
Query OK, 1 row affected (0.00 sec)
root@test 11:58:18>select table_name,partition_name,table_rows from information_schema.partitions where table_name ='t_columns_range51';
+-------------------+----------------+------------+
| table_name | partition_name | table_rows |
+-------------------+----------------+------------+
| t_columns_range51 | p0 | 0 |
| t_columns_range51 | p1 | 1 |
+-------------------+----------------+------------+
2 rows in set (0.00 sec)MySQL5.6
mysql> CREATE TABLE t_columns_range(
-> a INT,
-> b DATETIME) ENGINE=INNODB
-> PARTITION BY RANGE COLUMNS (b)
-> (PARTITION p0 VALUES LESS THAN('2009-01-01'),
-> PARTITION p1 VALUES LESS THAN('2010-01-01'));
Query OK, 0 rows affected (0.26 sec)
mysql> insert into t_columns_range(a,b) values (1,'2009-02-01');
Query OK, 1 row affected (0.05 sec)
mysql> select table_name,partition_name,table_rows from information_schema.partitions where table_name ='t_columns_range';
+-----------------+----------------+------------+
| table_name | partition_name | table_rows |
+-----------------+----------------+------------+
| t_columns_range | p0 | 0 |
| t_columns_range | p1 | 1 |
+-----------------+----------------+------------+
2 rows in set (0.03 sec)
mysql> create table c1(
-> a varchar(25),
-> b varchar(25),
-> city varchar(15)
-> )
-> partition by list columns(city)(
-> partition p1 values in('xx','yy'),
-> partition p2 values in('uu','vv'));
Query OK, 0 rows affected (0.46 sec)
mysql> insert into c1(a,b,city) values('a','a','xx');
Query OK, 1 row affected (5.41 sec)
mysql> select table_name,partition_name,table_rows from information_schema.partitions where table_name ='c1';
+------------+----------------+------------+
| table_name | partition_name | table_rows |
+------------+----------------+------------+
| c1 | p1 | 1 |
| c1 | p2 | 0 |
+------------+----------------+------------+
2 rows in set (0.00 sec)For the range columns partition, multiple columns can be used for partitioning, such as
mysql> create table c2(
-> a int,
-> b int,
-> c varchar(15)
-> )
-> partition by range columns(a,b,c)(
-> partition p1 values less than (5,10,'xbb'),
-> partition p2 values less than (10,20,'icey'),
-> partition p5 values less than (maxvalue,maxvalue,maxvalue));
Query OK, 0 rows affected (0.16 sec)
mysql> insert into c2(a,b,c) values(3,20,'xbb');
Query OK, 1 row affected (0.04 sec)
mysql> select table_name,partition_name,table_rows from information_schema.partitions where table_name ='c2';
+------------+----------------+------------+
| table_name | partition_name | table_rows |
+------------+----------------+------------+
| c2 | p1 | 1 |
| c2 | p2 | 0 |
| c2 | p5 | 0 |
+------------+----------------+------------+
3 rows in set (0.00 sec)
mysql> insert into c2(a,b,c) values(12,22,'xbb');
Query OK, 1 row affected (0.02 sec)
mysql> select table_name,partition_name,table_rows from information_schema.partitions where table_name ='c2';
+------------+----------------+------------+
| table_name | partition_name | table_rows |
+------------+----------------+------------+
| c2 | p1 | 1 |
| c2 | p2 | 0 |
| c2 | p5 | 1 |
+------------+----------------+------------+
3 rows in set (0.00 sec)When more than half of the rows and columns meet a certain partition, they are stored in that partition
4, Note:
1. The partition optimizer only optimizes and selects functions such as YEAR(), to days (), to seconds (), UNIX ﹣ timestamp (). If a partition is defined artificially, such as year (201102), year(date)+month(date), the optimizer will not select according to the partition and will scan all partitions. The correct way is to use "to" days to partition.
2. Different types of null in the partition handle differently
1> For a Range partition, if you insert a null value into the partition column, the MySQL database places the value in the leftmost partition. But when you delete a partition, you will delete a value smaller than the partition, and there is a null value.
2> For List partition, if you want to use NULL value, you must indicate which partition to put NULL value in the List, otherwise the insertion will report an error..
3> Hash and key partitions do not handle null values in the same way as list and range partitions. Any function will return a record with a null value of 0
~
~
complete!