Shulou(Shulou.com)06/01 Report--

First, partition operation 1. Change the unpartitioned table to the partition table ALTER TABLE trb3 PARTITION BY KEY (id) PARTITIONS 2. Delete partition # Delete all partitions, while data loss ALTER TABLE es2 REMOVE PARTITIONING;# deletes the specified partition, data loss ALTER TABLE tr DROP PARTITION p2scape 3. SELECT specified partition query select * from daily_rank_1_1 partition (p2015404partition 24) limit 10 X 4. Add a partition

If MAXVALUE is set, you cannot add a new partition. You will be prompted for MAXVALUE can only be used in last partition definition, and you can modify the partition to solve the problem.

ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (2010)); Repartition ALTER TABLE table1 REORGANIZE PARTITION the name of the partition to be modified (can be multiple, separated by commas) INTO (name of PARTITION new partition 1 VALUES LESS THAN (value), name of PARTITION new partition 2 VALUES LESS THAN (value).)

Modify a partition into two partitions

Alter table daily_rank_1_1 reorganize partition p201504 / 28 into (partition p201504 / 28 values less than (to_days ('2015-04-28')), partition pmax values less than (MAXVALUE))

Repartition multiple partitions

ALTER TABLE members REORGANIZE PARTITION p0, VALUES LESS THAN p1, p2, INTO (PARTITION M0 VALUES LESS THAN (1980), PARTITION M1 VALUES LESS THAN (2000))

Principles for zoning modification:

Cannot overlap with the original scheme while dividing multiple partitions must be contiguous partition type can not be changed, you can use ALTER TABLE. PARTITION BY... Achieve 6. Modify the number of partitions (HASH/Key partition) ALTER TABLE clients COALESCE PARTITION 4; 2. Swap partitions and subpartitions

Conditions that support swapping partitions

The table itself is not a partition table, not a temporary table. The structure of two tables is the same. The data of the table that does not contain foreign keys is not out of bounds.

If you want to perform an operation, you must have DROP permission

Execute ALTER TABLE... EXCHANGE PARTITION does not call any triggers on partitioned or swapped tables. Auto_increment will reset.

Specific operations:

Pt is a partition table, p is a partition or sub-partition

1. Swap partition 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)) with non-partitioned tables INSERT INTO e VALUES (1669, "Jim", "Smith"), (337, "Mary", "Jones"), (16, "Frank", "White"), (2005, "Linda", "Black"); mysql > CREATE TABLE e2 LIKE 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: write data from p0 partition to e2ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2

If no data is matched, prompt Found row that does not match the partition

two。 Swap a subpartition to a non-partitioned table mysql > CREATE TABLE es (- > id INT NOT NULL,-> fname VARCHAR (30),-> lname VARCHAR (30)-> PARTITION BY RANGE (id)-> SUBPARTITION BY KEY (lname)-> SUBPARTITIONS 2 (- > PARTITION p0 VALUES LESS THAN (50),-> PARTITION p1 VALUES LESS THAN (100) -> PARTITION p2 VALUES LESS THAN (150),-> 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"),-> (16, "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: exchange p3sp0 data to es2 table mysql > ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es2;Query OK, 0 rows affected (0.29 sec)

If a table has a child partition, the parent partition cannot be moved to the table

III. Zoning maintenance

Rebuild Partition

Delete all records stored in the partition, and then reinsert them. Defragment

ALTER TABLE t1 REBUILD PARTITION p0, p1

Optimized zoning

Optimize partitions to reclaim unused space and data files in collated partitions

ALTER TABLE T1 OPTIMIZE PARTITION p0, p1; analysis partition ALTER TABLE T1 ANALYZE PARTITION p3

Check the partition

ALTER TABLE trb3 CHECK PARTITION p1

Repair partition

ALTER TABLE t1 REPAIR PARTITION p0,p1 Obtain valid information of partition mysql > SHOW CREATE TABLE trb3\ gateway * 1. Row * * Table: trb3Create Table: CREATE TABLE `trb3` (`id` int (11) default NULL, `name` varchar (50) default NULL, `uploased` 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 (2005 sec) select * from INFORMATION_ SCHEMA.Partition IV, partition pruning

When performing sql, the optimizer automatically selects partitions according to the conditions of the partition to improve performance.

Conditions for zone pruning:

Partition_column = constantpartition_column IN (constant1, constant2,..., constantN)

The where condition contains

=, and

When querying the range between, you can use partition pruning

SELECT, UPDATE, and DELETE can all trim partitions, but INSERT cannot trim partitions. 5. Partition selection

The optimizer automatically prunes according to the statement when the operation is performed, but sometimes it is different:

The partition to be checked is specified by the publisher of the statement, and unlike partition pruning, it is automatic. Partition pruning is only applicable to queries, and the clear choice for partitioning is query and multiple DML statement support.

Supported statements: SELECT, DELETE, INSERT, REPLACE, UPDATE, LOAD DATA., LOAD XML.

Specific statements:

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-> FROM employees PARTITION (p1m p2m p3)-> GROUP BY store_id HAVING c > 4 +-+ | c | store_id | +-+-+ | 5 | 2 | | 5 | 3 | +-+-- + 2 rows in set (0.00 sec)

You can also use PARTITION on the INSERT...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 concatenated tables

CREATE TABLE stores (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, city VARCHAR (30) NOT NULL) PARTITION BY HASH (id) PARTITIONS 2 to insert INTO stores VALUES (', 'Nambucca'), ('', 'Uranga'), ('', 'Bellingen'), ('', 'Grafton'); CREATE TABLE departments (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR (30) NOT NULL) PARTITION BY KEY (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_id=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 deletion

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)

Use 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)-> 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.07sec) 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. Partition restrictions cannot use stored procedures, storage functions, UDF and plug-ins cannot allow user variables or declare variables that do not allow bit operations. VII, performance impact partition creation, modification, deletion depends on the file system. You should make sure that large_files_support is enabled and that open_files_limit settings are correct when performing partition operations that require write locks, but do not affect the query. Insert and update operations will be performed immediately after the partition operation is completed. Queries and updates tend to be that MYISAM uses indexes faster than INNODB to improve performance in non-partition tables, and using partition pruning can also significantly improve performance load data using buffering to improve performance. You should know that each partition of the buffer uses 130KB memory to do this. Before Mysql5.6.7, the maximum number of partitions is 1024. Starting from 5.6.7, the maximum number of partition tables is 8192. Query caching is not supported for sub-partition tables.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.