Mysql Partition Introduction (ix)--Partition management

One, partition operation 1. To change a table without partitions to a partitioned table

ALTER TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2;

2. Deleting a partition

# 删除所有分区, 同时数据丢失ALTER TABLE es2 REMOVE PARTITIONING;# 删除指定分区, 数据丢失ALTER TABLE tr DROP PARTITION p2;

3. Select to specify a partition query

select * from daily_rank_1_1 partition (p2015_04_24) limit 10;

4. Adding partitions

If MAXVALUE is set, the new partition cannot be added, and the MAXVALUE can is used in the last partition definition, the modified partition is used to resolve

ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2010));

5. Repartition partitions

ALTER TABLE table1 REORGANIZE PARTITION 要修改的分区名(可以多个, 逗号分隔) INTO (    PARTITION 新分区1的名字 VALUES LESS THAN (值),    PARTITION 新分区2的名字 VALUES LESS THAN (值)    ...)

Modify a partition into two partitions

alter table daily_rank_1_1 reorganize partition p2015_04_28 into(partition p2015_04_28 values less than (to_days(‘2015-04-28‘)),partition pmax values less than(MAXVALUE));

Re-partitioning multiple partitions

ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO (    PARTITION m0 VALUES LESS THAN (1980),    PARTITION m1 VALUES LESS THAN (2000));

分区修改的原则: <br/>

• 1. Cannot overlap with the original scheme
• 2. Partitioning must be contiguous for multiple partitions at the same time
• 3. The partition type cannot be changed, you can pass alter TABLE ... PARTITION by ... Realize

6. Modify the number of partitions (Hash/key partitions)

ALTER TABLE clients COALESCE PARTITION 4;

Second, swap partitions and sub-partitions

支持交换分区的条件

• 1. Table itself is not a partitioned table
• 2. Not a temp table.
• 3. Two tables have the same structure
• 4. Table does not contain foreign keys
• 5. Table data is not out of bounds

If you want to perform an action, you must have the drop permission <br/>

• 1. Execute ALTER TABLE ... EXCHANGE partition does not invoke any triggers on a partitioned table or interchange table
• 2. Auto_increment will happen reset

Specific operation:
PT is a partitioned table, p is a partition or sub-partition

1. Swap partitions with non-partitioned tables

CREATE TABLE e (    id INT NOT NULL,    fname VARCHAR(30),    lname VARCHAR(30))    PARTITION BY RANGE (id) (        PARTITION p0 VALUES LESS THAN (50),        PARTITION p1 VALUES LESS THAN (100),        PARTITION p2 VALUES LESS THAN (150),        PARTITION p3 VALUES LESS THAN (MAXVALUE));INSERT INTO e VALUES    (1669, "Jim", "Smith"),    (337, "Mary", "Jones"),    (16, "Frank", "White"),    (2005, "Linda", "Black");mysql> CREATE TABLE e2 LIKE e;Query OK, 0 rows affected (1.34 sec)mysql> ALTER TABLE e2 REMOVE PARTITIONING;Query OK, 0 rows affected (0.90 sec)Records: 0  Duplicates: 0  Warnings: 0# 将p0分区的数据写入e2ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;

If there is no match to the data, then prompt found row, does not match the partition

2. Swap a sub-partition to a table with no partitions

mysql> CREATE TABLE es (-id INT not NULL, fname varchar), lname varchar (30)    ), PARTITION by RANGE (ID), subpartition by KEY (lname), Subpartitions 2 ( PARTITION p0 Values less THAN (+), PARTITION p1 values less THAN (+), P Artition P2 values less THAN,-PARTITION P3 values less THAN (MAXVALUE)); Query OK, 0 rows affected (2.76 sec) mysql> INSERT into Es VALUES, (1669, "Jim", "Smith"), (337 , "Mary", "Jones"), (+, "Frank", "White"), (2005, "Linda", "Black"); Query OK, 4 rows affected (0.04 sec) records:4 duplicates:0 warnings:0mysql> CREATE TABLE es2 like es; Query OK, 0 rows affected (1.27 sec) mysql> ALTER TABLE es2 REMOVE partitioning; Query OK, 0 rows affected (0.70 sec) records:0 duplicates:0 warnings:0# Exchange P3SP0 data to Es2 table mysql> ALTER table esEXCHANGE PARTITION p3sp0 with TABLE es2; Query OK, 0 rows affected (0.29 sec)

If a table has child partitions, you cannot move the parent partition into the table

Third, partition maintenance

1. Rebuilding partitions
   Delete all the records that are stored in the partition, and then reinsert them. Organize the Fragments

   ALTER TABLE t1 REBUILD PARTITION p0, p1;

2. Optimizing partitions
   Optimize partitions to reclaim unused space and organize data files in a partition

   ALTER TABLE t1 OPTIMIZE PARTITION p0, p1;

3. Parsing partitions

ALTER TABLE t1 ANALYZE PARTITION p3;

4. Check partition

   ALTER TABLE trb3 CHECK PARTITION p1;

5. Repairing partitions

   ALTER TABLE t1 REPAIR PARTITION p0,p1;

6. Get partition valid information

   mysql> SHOW CREATE TABLE trb3\G*************************** 1. row ***************************   Table: trb3Create Table: CREATE TABLE `trb3` (`id` int(11) default NULL,`name` varchar(50) default NULL,`purchased` date default NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1PARTITION BY RANGE (YEAR(purchased)) (PARTITION p0 VALUES LESS THAN (1990) ENGINE = MyISAM,PARTITION p1 VALUES LESS THAN (1995) ENGINE = MyISAM,PARTITION p2 VALUES LESS THAN (2000) ENGINE = MyISAM,PARTITION p3 VALUES LESS THAN (2005) ENGINE = MyISAM)1 row in set (0.00 sec)

select *from INFORMATION_SCHEMA.PARTITIONS

Iv. Partition Trimming

When you execute SQL, the optimizer automatically makes partitioning selections based on the conditions of the partition to improve performance.

分区修剪的条件: <br/>

• 1. Partition_column = constant
• 2. Partition_column in (Constant1, Constant2, ..., CONSTANTN)
     You can use partition pruning when the Where condition contains a range query between <,>,< =,> =, and < > etc.
     SELECT, update, and delete can trim the partition, but insert cannot trim the partition

V. Selection of partitions

The optimizer automatically trims according to the statement while performing the operation, but in some cases it is different:

1. The partition to be inspected is specified by the publisher of the statement, and it is automatic, unlike the partition pruning.
2. While partition pruning is only available for queries, partition-specific selection is supported by queries and multiple DML statements.
   Supported statements: SELECT, DELETE, INSERT, REPLACE, UPDATE, load DATA, load XML.

The specific statement:

      PARTITION (partition_names)      partition_names:          partition_name, ...

SELECT * FROM Employees PARTITION (p1);mysql> SELECT * FROM Employees PARTITION (P0, p2)--WHERE lname like ' s% '; +----+-------+-------+----------+---------------+| ID | fname | lname | store_id |  department_id |+----+-------+-------+----------+---------------+| 4 | Jim |        Smith |             2 | 4 | | 11 | Jill |        Stone |             1 |  4 |+----+-------+-------+----------+---------------+2 rows in Set (0.00 sec) mysql> SELECT ID, CONCAT (fname, ", lname) As name, from Employees PARTITION (p0) ORDER by lname;+----+----------------+| ID |  Name |+----+----------------+| 3 |  Ellen Johnson | | 4 |  Jim Smith | | 1 |  Bob Taylor | | 2 | Frank Williams |+----+----------------+4 rows in Set (0.06 sec) mysql> SELECT store_id, COUNT (department_id) as C-&G     T From Employees PARTITION (P1,P2,P3), GROUP by store_id have C > 4;+---+----------+| C | store_id |+---+----------+|        5 | 2 | |        5 | 3 |+---+----------+2 rows in Set (0.00 sec) 

You can also use partition in the Insert ... On the SELECT statement

mysql> CREATE TABLE employees_copy LIKE employees;Query OK, 0 rows affected (0.28 sec)mysql> INSERT INTO employees_copy    ->     SELECT * FROM employees PARTITION (p2);Query OK, 5 rows affected (0.04 sec)Records: 5  Duplicates: 0  Warnings: 0mysql> SELECT * FROM employees_copy;+----+--------+----------+----------+---------------+| id | fname  | lname    | store_id | department_id |+----+--------+----------+----------+---------------+| 10 | Lou    | Waters   |        2 |             4 || 11 | Jill   | Stone    |        1 |             4 || 12 | Roger  | White    |        3 |             2 || 13 | Howard | Andrews  |        1 |             2 || 14 | Fred   | Goldberg |        3 |             3 |+----+--------+----------+----------+---------------+5 rows in set (0.00 sec)

can also be used in tables

CREATE TABLE stores (ID INT not NULL auto_increment PRIMARY KEY, City VARCHAR (+) NOT null) PARTITION by HASH (ID ) partitions 2;insert into stores VALUES (' ', ' Nambucca '), (' ', ' Uranga '), (', ' Bellingen '), (', ' ' Grafton '); CREATE TABLE departments (ID INT not NULL auto_increment PRIMARY KEY, name VARCHAR (+) NOT null) PARTITION by K EY (ID) partitions 2;insert into departments VALUES (' ', ' Sales '), (', ' Customer Service '), (', ' Delivery '), (' , ' Accounting ');mysql> SELECT, e.id as ' Employee id ', CONCAT (E.fname, ', e.lname) as Name, S . City as city, d.name as department-from employees as e-JOIN stores PARTITION (p1) as S on e.store_i D=s.id, JOIN Departments PARTITION (P0) as D on E.department_id=d.id, ORDER by e.lname;+-------------+ ---------------+-----------+------------+| Employee ID | Name | City | Department |+-------------+---------------+-----------+------------+| 14 | Fred Goldberg | Bellingen |           Delivery | | 5 | Mary Jones | Nambucca |          Sales | | 17 | Mark Morgan | Bellingen |           Delivery | | 9 | Andy Smith | Nambucca |           Delivery | | 8 | June Wilson | Bellingen | Sales |+-------------+---------------+-----------+------------+5 rows in Set (0.00 sec)

Use partition selection in delete

mysql> SELECT * FROM employees WHERE fname LIKE ‘j%‘;+----+-------+--------+----------+---------------+| id | fname | lname  | store_id | department_id |+----+-------+--------+----------+---------------+|  4 | Jim   | Smith  |        2 |             4 ||  8 | June  | Wilson |        3 |             1 || 11 | Jill  | Stone  |        1 |             4 |+----+-------+--------+----------+---------------+3 rows in set (0.00 sec)mysql> DELETE FROM employees PARTITION (p0, p1)    ->     WHERE fname LIKE ‘j%‘;Query OK, 2 rows affected (0.09 sec)mysql> SELECT * FROM employees WHERE fname LIKE ‘j%‘;+----+-------+-------+----------+---------------+| id | fname | lname | store_id | department_id |+----+-------+-------+----------+---------------+| 11 | Jill  | Stone |        1 |             4 |+----+-------+-------+----------+---------------+1 row in set (0.00 sec)

Using partition selection in updates

 mysql> UPDATE Employees PARTITION (p0), SET store_id = 2 WHERE fname = ' Jill '; Query OK, 0 rows Affected (0.00 sec) rows matched:0 changed:0 warnings:0mysql> SELECT * FROM Employees WHERE fname = ' Jill '; +----+-------+-------+----------+---------------+| ID | fname | lname | store_id | department_id |+----+-------+-------+----------+---------------+| 11 | Jill | Stone | 1 | 4 |+----+-------+-------+----------+---------------+1 row in Set (0.00 sec) mysql> UPDATE Employees PARTITION (p2)-& Gt SET store_id = 2 WHERE fname = ' Jill '; Query OK, 1 row affected (0.09 sec) Rows matched:1 changed:1 warnings:0mysql> SELECT * FROM employees WHERE fname = ' Jill '; +----+-------+-------+----------+---------------+| ID | fname | lname | store_id | department_id |+----+-------+-------+----------+---------------+| 11 | Jill | Stone | 2 | 4 |+----+-------+-------+----------+---------------+1 row in Set (0.00 sec) 

Insert and replace into use partition selection

mysql> INSERT INTO employees PARTITION (p2) VALUES (20, ‘Jan‘, ‘Jones‘, 1, 3);ERROR 1729 (HY000): Found a row not matching the given partition setmysql> INSERT INTO employees PARTITION (p3) VALUES (20, ‘Jan‘, ‘Jones‘, 1, 3);Query OK, 1 row affected (0.07 sec)mysql> REPLACE INTO employees PARTITION (p0) VALUES (20, ‘Jan‘, ‘Jones‘, 3, 2);ERROR 1729 (HY000): Found a row not matching the given partition setmysql> REPLACE INTO employees PARTITION (p3) VALUES (20, ‘Jan‘, ‘Jones‘, 3, 2);Query OK, 2 rows affected (0.09 sec)

Vi. Restrictions on zoning

• 1. Cannot use stored procedures, storage features, UDFs, and plugins
• 2. Cannot user variable or declare variable
• 3. Do not allow bit operations

Vii. Performance Impact

• 1. The creation, modification, and deletion of partitions depends on the file system. You should ensure that Large_files_support is enabled, Open_files_limit is set correctly
• 2. Write locks are required to perform partitioning operations, but do not affect queries, and insert and update operations are performed immediately after partitioning operations are completed
• 3. Partitioning operations, queries, updates are often myisam faster than InnoDB
• 4. Use indexes to improve performance on non-partitioned tables, and use partition pruning to significantly improve performance
• 5. Load data uses buffering to improve performance. You should know that buffers use 130KB of memory per partition to achieve this.
• 6. Before Mysql5.6.7, the maximum number of partitions is 1024, starting with 5.6.7, the number of partitioned tables is up to 8,192, including sub-partitions
• 7. Partitioned tables do not support query caching

Mysql Partition Introduction (ix)--Partition management