MySQL分区表管理

但是如果e2里面的数据不能够满足分区p0的要求的话,切分区就会失败. 1737 - Found a row that does not match the partition 只有再指定不验证的时候才不会报错

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

WITHOUT VALIDATION 指定的时候效率会更高,因为不再做逐行验证了.

   

子分区和没分区的表进行切换

  1:假设创建一个分区表,带有子分区

> 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)
    ->     );

然后就可以切分区了,先查看一下分区, SELECT PARTITION_NAME, SUBPARTITION_NAME, TABLE_ROWS      FROM INFORMATION_SCHEMA.PARTITIONS      WHERE TABLE_NAME = ‘es‘; 然后切出分区:

ALTER TABLE es EXCHANGE PARTITION p3sp0 WITH TABLE es2;

当前执行切出分区前,必须要对新表做以下的处理:

ALTER TABLE es2 REMOVE PARTITIONING;

  修改表的默认引擎:

ALTER TABLE es3 ENGINE = MyISAM;

维护表分区 1:重建分区

ALTER TABLE t1 REBUILD PARTITION p0, p1;

2:重新组织分区

ALTER TABLE t1 OPTIMIZE PARTITION p0, p1;

3:分析某个分区,主要看行数和名称以及状态

ALTER TABLE t1 ANALYZE PARTITION p3;

4:修复分区,有重复值的时候就会报错.

ALTER TABLE t1 REPAIR PARTITION p0,p1;

5:检查分区的状态

ALTER TABLE trb3 CHECK PARTITION p1;

6:truncate分区   ALTER TABLE ... TRUNCATE PARTITION.
ALTER TABLE ... TRUNCATE PARTITION ALL    7:获取表的信息

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

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 are used by a given SELECT.

看以下信息:

mysql> EXPLAIN PARTITIONS SELECT * FROM trb1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: trb1
   partitions: p0,p1,p2,p3
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Using filesort

      分区的经典案例:

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
    -> );

分区的一些添删查修语句:

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

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

SELECT * FROM employees PARTITION (p2);

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

分区不够多,要添加分区:

ysql> 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分区表管理

标签：