Modify the partition field online in MySQL partition table partition, and learn more about partition later (1)-Mysql Tutorial-php.cn

The company is using partition online, and the partition field of one table is wrong and needs to be rebuilt. It turns out that there is no way to do it directly with a SQL like modifying the primary key field or modifying the index field. Instead, we need to build a temporary table and have down time, so I read the documentation carefully and studied the details of the partition.

When my company took it online, during the low business peak period at 1 am, I executed:

Create a temporary table

CREATE TABLE tbname_TMP (    
SHARD_ID INT NOT NULL,    
...
    xxx_DATE DATETIME NOT NULL,    
    PRIMARY KEY (xxx_DATE,shard_id)) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_binPARTITION BY LIST(MONTH(xxx_DATE)) (    
    PARTITION m1 VALUES IN (1),    
    PARTITION m2 VALUES IN (2),    
    PARTITION m3 VALUES IN (3),    
    PARTITION m4 VALUES IN (4),    
    PARTITION m5 VALUES IN (5),    
    PARTITION m6 VALUES IN (6),    
    PARTITION m7 VALUES IN (7),    
    PARTITION m8 VALUES IN (8),    
    PARTITION m9 VALUES IN (9),    
    PARTITION m10 VALUES IN (10),    
    PARTITION m11 VALUES IN (11),    
    PARTITION m12 VALUES IN (12)
    );

Copy after login

Switch the table name and modify the table structure

RENAME TABLE xxx TO xxx_DELETED, xxx_TMP TO xxx;

Copy after login

Import the original data

insert into xxx select * from xxx_DELETEDxxx_DELETED;

Copy after login

OK, everything is done, the whole process takes 50 minutes, the outline operation table structure changes and data import after MMM failover switching, The actual downtime does not include the time to modify the table structure partition field, only the failover switching time is 30 seconds

MySQL Partition, the official English information I read, the translation level is limited, some are not translated into Chinese, and are posted directly in English.
1 list partition table

mysql> CREATE TABLE `eh` (
    ->   `id` int(11) NOT NULL,
    ->   `ENTITLEMENT_HIST_ID` bigint(20) NOT NULL,
    ->   `ENTITLEMENT_ID` bigint(20) NOT NULL,
    ->   `USER_ID` bigint(20) NOT NULL,
    ->   `DATE_CREATED` datetime NOT NULL,
    ->   `STATUS` smallint(6) NOT NULL,
    ->   `CREATED_BY` varchar(32) COLLATE utf8_bin DEFAULT NULL,
    ->   `MODIFIED_BY` varchar(32) COLLATE utf8_bin DEFAULT NULL,
    ->   `DATE_MODIFIED` datetime NOT NULL,
    ->   PRIMARY KEY (`DATE_MODIFIED`,`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
    -> /*!50100 PARTITION BY LIST (MONTH(DATE_MODIFIED))
    -> (PARTITION m1 VALUES IN (1) ENGINE = InnoDB,
    ->  PARTITION m2 VALUES IN (2) ENGINE = InnoDB,
    ->  PARTITION m3 VALUES IN (3) ENGINE = InnoDB,
    ->  PARTITION m4 VALUES IN (4) ENGINE = InnoDB,
    ->  PARTITION m5 VALUES IN (5) ENGINE = InnoDB,
    ->  PARTITION m6 VALUES IN (6) ENGINE = InnoDB,
    ->  PARTITION m7 VALUES IN (7) ENGINE = InnoDB,
    ->  PARTITION m8 VALUES IN (8) ENGINE = InnoDB,
    ->  PARTITION m9 VALUES IN (9) ENGINE = InnoDB,
    ->  PARTITION m10 VALUES IN (10) ENGINE = InnoDB,
    ->  PARTITION m11 VALUES IN (11) ENGINE = InnoDB,
    ->  PARTITION m12 VALUES IN (12) ENGINE = InnoDB) */;
Query OK, 0 rows affected (0.10 sec)

Copy after login

2 rang partition table

mysql> CREATE TABLE rcx (
    ->     a INT,
    ->     b INT,
    ->     c CHAR(3),
    ->     d INT
    -> )
    -> PARTITION BY RANGE COLUMNS(a,d,c) (
    ->     PARTITION p0 VALUES LESS THAN (5,10,&#39;ggg&#39;),
    ->     PARTITION p1 VALUES LESS THAN (10,20,&#39;mmmm&#39;),
    ->     PARTITION p2 VALUES LESS THAN (15,30,&#39;sss&#39;),
    ->     PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
    -> );
Query OK, 0 rows affected (0.15 sec)

Copy after login

3 create range use less character

CREATE TABLE employees_by_lname (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT &#39;1970-01-01&#39;,
    separated DATE NOT NULL DEFAULT &#39;9999-12-31&#39;,
    job_code INT NOT NULL,
    store_id INT NOT NULL
)

Copy after login

PARTITION BY RANGE COLUMNS (lname)  (
    PARTITION p0 VALUES LESS THAN (&#39;g&#39;),
    PARTITION p1 VALUES LESS THAN (&#39;m&#39;),
    PARTITION p2 VALUES LESS THAN (&#39;t&#39;),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

Copy after login

alter table structure, add a new partition block

ALTER TABLE employees_by_lname PARTITION BY RANGE COLUMNS (lname)  (
    PARTITION p0 VALUES LESS THAN (&#39;g&#39;),
    PARTITION p1 VALUES LESS THAN (&#39;m&#39;),
    PARTITION p2 VALUES LESS THAN (&#39;t&#39;),
 PARTITION p3 VALUES LESS THAN (&#39;u&#39;),
    PARTITION p4 VALUES LESS THAN (MAXVALUE)
);

Copy after login

4 List columns partitioning

character column
CREATE TABLE customers_1 (
    first_name VARCHAR(25),
    last_name VARCHAR(25),
    street_1 VARCHAR(30),
    street_2 VARCHAR(30),
    city VARCHAR(15),
    renewal DATE
)

Copy after login

PARTITION BY LIST COLUMNS(city) (
    PARTITION pRegion_1 VALUES IN(&#39;Oskarshamn&#39;, &#39;H?gsby&#39;, &#39;M?nster?s&#39;),
    PARTITION pRegion_2 VALUES IN(&#39;Vimmerby&#39;, &#39;Hultsfred&#39;, &#39;V?stervik&#39;),
    PARTITION pRegion_3 VALUES IN(&#39;N?ssj?&#39;, &#39;Eksj?&#39;, &#39;Vetlanda&#39;),
    PARTITION pRegion_4 VALUES IN(&#39;Uppvidinge&#39;, &#39;Alvesta&#39;, &#39;V?xjo&#39;)
);

Copy after login

date column

CREATE TABLE customers_2 (
    first_name VARCHAR(25),
    last_name VARCHAR(25),
    street_1 VARCHAR(30),
    street_2 VARCHAR(30),
    city VARCHAR(15),
    renewal DATE
)

Copy after login

PARTITION BY LIST COLUMNS(renewal) (
    PARTITION pWeek_1 VALUES IN(&#39;2010-02-01&#39;, &#39;2010-02-02&#39;, &#39;2010-02-03&#39;,
        &#39;2010-02-04&#39;, &#39;2010-02-05&#39;, &#39;2010-02-06&#39;, &#39;2010-02-07&#39;),
    PARTITION pWeek_2 VALUES IN(&#39;2010-02-08&#39;, &#39;2010-02-09&#39;, &#39;2010-02-10&#39;,
        &#39;2010-02-11&#39;, &#39;2010-02-12&#39;, &#39;2010-02-13&#39;, &#39;2010-02-14&#39;),
    PARTITION pWeek_3 VALUES IN(&#39;2010-02-15&#39;, &#39;2010-02-16&#39;, &#39;2010-02-17&#39;,
        &#39;2010-02-18&#39;, &#39;2010-02-19&#39;, &#39;2010-02-20&#39;, &#39;2010-02-21&#39;),
    PARTITION pWeek_4 VALUES IN(&#39;2010-02-22&#39;, &#39;2010-02-23&#39;, &#39;2010-02-24&#39;,
        &#39;2010-02-25&#39;, &#39;2010-02-26&#39;, &#39;2010-02-27&#39;, &#39;2010-02-28&#39;)
);

Copy after login

5 HASH Partitioning

int column,it can use digital function
CREATE TABLE employeesint (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT &#39;1970-01-01&#39;,
    separated DATE NOT NULL DEFAULT &#39;9999-12-31&#39;,
    job_code INT,
    store_id INT
)
PARTITION BY HASH(MOD(store_id,4))
PARTITIONS 4;

Copy after login

If you do not include a PARTITIONS clause, the number of partitions defaults to 1. as below :

CREATE TABLE employeestest (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT &#39;1970-01-01&#39;,
    separated DATE NOT NULL DEFAULT &#39;9999-12-31&#39;,
    job_code INT,
    store_id INT
)
PARTITION BY HASH(store_id);

Copy after login

date colum

CREATE TABLE employees2 (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT &#39;1970-01-01&#39;,
    separated DATE NOT NULL DEFAULT &#39;9999-12-31&#39;,
    job_code INT,
    store_id INT
)
PARTITION BY HASH( YEAR(hired) )
PARTITIONS 4;

Copy after login

truncate all data rows: alter table rcx truncate PARTITION;

6 LINE AR HASH Partitioning

CREATE TABLE employees_linear (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT &#39;1970-01-01&#39;,
    separated DATE NOT NULL DEFAULT &#39;9999-12-31&#39;,
    job_code INT,
    store_id INT
)

Copy after login

PARTITION BY LINEAR HASH( YEAR(hired) )
PARTITIONS 4;

Given an expression expr, the partition in which the record is stored when linear hashing is used is partition number N from among num partitions, where N is derived according to the following algorithm:
(1) Find the next power of 2 greater than num. We call this value V; it can be calculated as:
V = POWER(2, CEILING(LOG(2, num)))
(Suppose that num is 13. Then LOG(2,13) is 3.7004397181411. CEILING(3.7004397181411) is 4, and V = POWER(2,4), which is 16.)
(2) Set N = F(column_list) & (V - 1).
(3) While N >= num:
Set V = CEIL(V / 2)
Set N = N & (V - 1)

[Note] The calculation principle of & in SQL is: For example
Convert decimal to binary and you get http://www.php.cn/

First align to the right, for example, become 0011 and 1000. Judge according to the number of each digit. If both are 1, the corresponding position of the result is 1, otherwise It is 0
If it is 1011 and 1000, the result is 1000
If it is 0110 and 1010, the result is 0010
But 3 is 0011 and 8 is 1000, so the result of 3&8 is 0

CEILING(X) CEIL(X): Return The smallest integer value that is not less than X.
LOG(X) LOG(B,X): If called with one parameter, this function will return the natural logarithm of X.
POWER(X,Y): Returns the result value of X raised to the power of Y.

Calculation method of which slice the data is distributed in:
Suppose that the table t1, using linear hash partitioning and having 6 partitions, is created using this statement:

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
    PARTITION BY LINEAR HASH( YEAR(col3) )
    PARTITIONS 6;

Copy after login

Now assume that you want to insert two records into t1 having the col3 column values '2003-04-14' and '1998-10-19'. The partition number for the first of these is determined as follows:

 V = POWER(2, CEILING( LOG(2,6) )) = 8
 N = YEAR(&#39;2003-04-14&#39;) & (8 - 1)
    = 2003 & 7
    = 3
 (3 >= 6 is FALSE: record stored in partition #3)

Copy after login

The number of the partition where the second record is stored is calculated as shown here:

 V = 8 N = YEAR(&#39;1998-10-19&#39;) & (8-1)   = 1998 & 7   = 6
 (6 >= 6 is TRUE: additional step required)
 N = 6 & CEILING(8 / 2)   = 6 & 3   = 2
 (2 >= 6 is FALSE: record stored in partition #2)

Copy after login

The advantage in partitioning by linear hash is that the adding, dropping, merging, and splitting of partitions is made much faster, which can be beneficial when dealing with tables containing extremely large amounts ( terabytes) of data. The disadvantage is that data is less likely to be evenly distributed between partitions as compared with the distribution obtained using regular hash partitioning.

One of the questions: How does MySQL use a SQL to delete partition fields without using a temporary table? Convert a partitioned table into a normal table?

The above is the MySQL partition table partition online modification of the partition field. Later, we will further learn the content of partition (1). For more related content, please pay attention to the PHP Chinese website (www.php.cn)!