MySQL Partition table Management

Range,list Partition Management 1: Creating partitions for non-partitioned tables

ALTER TABLE trb3 PARTITION by KEY (ID) partitions 2;

2: Delete data for a partition

ALTER TABLE tr DROP PARTITION p2;

3: Add a partition to the partition table

ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES less THAN (2000));

ALTER TABLE tt ADD PARTITION (PARTITION p2 VALUES in (7, 14, 21));

ALTER TABLE Employees ADD PARTITION (    PARTITION P5 values less THAN),    PARTITION P6 values less THAN MAXVALUE );

4: Divide the first partition of the partitioned table into two new partitions

ALTER TABLE members    REORGANIZE PARTITION p0 to (        PARTITION n0 values less THAN (1960),        PARTITION N1 values L ESS THAN (1970));

5: You can also merge two partitions into a single partition, or you can understand to reorganize the partitions

ALTER TABLE members REORGANIZE PARTITION s0,s1 to (    PARTITION p0 VALUES less THAN (1970));

tbl_name    partition_list    Into ( partition_definitions );

ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 to (    PARTITION M0 VALUES less THAN (1980),    PARTITION M1 VAL UES less THAN (2000));

ALTER TABLE tt ADD PARTITION (PARTITION np VALUES in (4, 8)); ALTER TABLE tt REORGANIZE PARTITION p1,np into (    PARTITION P1 values in (6),    PARTITION NP values in (4, 8, 1 2));

Delete partition table: ALTER TABLE tb_user remove partitioning;

Hash,key Partition Management 1: Create a hash partition table

CREATE TABLE clients (    ID INT,    fname varchar (),    lname varchar (),    signed DATE) PARTITION by HASH ( MONTH (signed)) partitions 12;

To change a partitioned table from 12 partitions to 8 partitions

ALTER TABLE clients COALESCE PARTITION 4;

The same table with the following statement about the key partition:

CREATE TABLE clients_lk (    --      id INT,      - fname VARCHAR(30),       lname VARCHAR(30),       signed DATE    Query OK )     , 0 rows affected (0.06 sec)  PARTITION BY LINEAR KEY(signed)      PARTITIONS 12;   ALTER TABLE clients_lk COALESCE PARTITION 4;  records:0  duplicates:0  warnings:0

And of course there are restrictions.

ALTER TABLE clients COALESCE PARTITION 18;ERROR 1478 (HY000): Cannot remove all partitions, use DROP TABLE instead

If the number of partitions is greater than the number of existing partitions, you can only add the number of partitions by using Add. The following is an increase of 6 partitions

ALTER TABLE Clients ADD PARTITION partitions 6;

Swap partition, sub-partition management

Swap partition, ALTER TABLE PT Exchange PARTITION P with table NT

Where PT is the partitioned table and P are the partition or Subpartition of PT to being exchanged with unpartitioned table NT , provided that the following statements is true: 1:PT is a partitioned table, NT is not a temp table 2: The table structure of the two tables must be exactly the same. 3:NT cannot have foreign key constraints. It is also not possible to have foreign key constraints on other tables. The data in the 4:nt table does not have data other than partition p. Without validation when you specify it, you can ignore it. Another important point is that you must have drop permissions on the entire table to execute if you want to have Exchange permissions. . ALTER TABLE ... Exchange partition will not invoke any triggers, and the self-increment column of the table that is being replaced by Exchange will be re-assigned to the initial value. For example:

pt     p     nT with VALIDATION;

1: Partition and a table without partitions exchange creates a table INSERT statement:

CREATE TABLE e (    ID INT not NULL,    fname varchar (+),    lname varchar ())    PARTITION by RANGE (ID) (        part Ition p0 Values less THAN (+),        PARTITION p1 values less THAN (+),        PARTITION p2 values less THAN ($),        part Ition P3 Values less THAN (MAXVALUE)); INSERT into e values     (1669, "Jim", "Smith"), (    337, "Mary", "Jones"),    (1 6, "Frank", "white"),    (2005, "Linda", "Black");

View the number of partitions and partitions of the row SELECT Partition_name, table_rows from INFORMATION_SCHEMA. partitions WHERE table_name = ' e ';

WHERE TABLE_SCHEMA = ‘p‘ AND TABLE_NAME LIKE ‘e‘;

To create a new table:

CREATE TABLE e2 LIKE e;

Then the swap partition begins:

ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;

This is a very strange statement., if there is no data inside the E2 is cut out of the partition, if the E2 inside the data is exchangedHowever, if the data inside the E2 does not meet the requirements of the partition P0, the cut partition will fail. 1737-found a row that does not match the partition only if you specify not to verify the error

ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2 WITHOUT VALIDATION;

Without VALIDATION is more efficient when specified, because row-wise verification is no longer done.

Sub-partitions and tables that are not partitioned are toggled

1: Suppose you create a partitioned table with sub-partitions

 >   CREATE TABLE es ( ,   ID INT not NULL,  -    >   fname varchar (in),  ,   lname varchar (in)    )  ,   PARTITION by RANGE (ID)  , 
    
     subpartition by KEY (lname)  , 
      subpartitions 2 ( ->         ; 
      PARTITION p0 values less THAN (+),  , 
      PARTITION p1 values Less THAN,  , 
      PARTITION p2 VALUES less THAN,   
      PARTITION P3 VALUES less THAN (MAXVALUE)  -
     );  
   

Then you can cut the partition, first look at the partition, SELECT partition_name, Subpartition_name, table_rows from INFORMATION_SCHEMA. partitions WHERE table_name = ' es '; then cut out the partition:

ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es2;

You must do the following for the new table before you currently perform the cut-out partition:

ALTER TABLE es2 REMOVE PARTITIONING;

to modify the default engine for a table:

ALTER TABLE es3 ENGINE = MyISAM;

Maintaining table partition 1: Rebuilding partitions

ALTER TABLE T1 REBUILD PARTITION p0, p1;

2: Reorganize partitions

ALTER TABLE T1 OPTIMIZE PARTITION p0, p1;

3: Analyze a partition, mainly see the number of rows and names and status

ALTER TABLE T1 ANALYZE PARTITION P3;

4: Repair the partition, when there are duplicate values will be error.

ALTER TABLE T1 REPAIR PARTITION p0,p1;

5: Check the status of the partition

ALTER TABLE trb3 CHECK PARTITION p1;

6:truncate partition ALTER TABLE ... TRUNCATE PARTITION.
ALTER TABLE ... TRUNCATE PARTITION All7: Get information about the table

Using the SHOW CREATE TABLE statement to view the partitioning clauses used in creating a partitioned Ta ble.

Using the SHOW table STATUS statement to determine whether a table is partitioned.

Querying the information_schema. Partitions table.

Using the statement EXPLAIN partitions Select to see which partitions is used by a given SELECT.

See the following information:

EXPLAIN PARTITIONS SELECT * FROM trb1\G1. Row ***************************           id:1  select_type:simple        table:trb1   partitions:p0,p1,p2,p3         Type:ALLpossible_keys:NULL          key:null      key_len:null          ref:null         rows:10        extra:using filesort

Classic examples of partitioning:

Mysql>CREATE TABLE employees_sub (-id INT NOT NULL AUTO_INCREMENT,-fname VARCHAR(25) NOT NULL,-lname VARCHAR(25) NOT NULL,-store_id INT NOT NULL,-department_id INT NOT NULL,-PRIMARY KEY pk (id, lname)-)-PARTITION BY RANGE(id)-SUBPARTITION BY KEY (lname)-SUBPARTITIONS 2 (-PARTITION p0 VALUES LESS THAN (5),-PARTITION p1 VALUES LESS THAN (10),-PARTITION p2 VALUES LESS THAN (15),-PARTITION p3 VALUES LESS THAN MAXVALUE-);

Some additions and deletions to the partition:

DELETE FROM employees PARTITION (p0, p1)          Query OK, 2 rows affected (0.09 sec) WHERE fname LIKE ‘j%‘; 

UPDATE employees PARTITION (p2)    -     SET store_id = 2 WHERE fname = ‘Jill‘;

SELECT * FROM employees PARTITION (p2);

INSERT INTO employees PARTITION (p2) VALUES (20, ‘Jan‘, ‘Jones‘, 1, 3);INSERT INTO employees PARTITION (p3) VALUES (20, ‘Jan‘, ‘Jones‘, 1, 3);Query OK, 1 row affected (0.07 sec)

There are not enough partitions to add partitions:

ALTER TABLE employees    --      REORGANIZE PARTITION p3 INTO (          - PARTITION p3 VALUES LESS THAN (20),           PARTITION p4 VALUES LESS THAN (25),           PARTITION p5 VALUES LESS THAN MAXVALUE     Query OK, 6 rows affected (2.09 sec) records:6 duplicates:0 warnings:0 );   

MySQL Partition table Management