思享工具箱

MySQL分区表管理

摘要：
RANGE，LIST分区管理1：为未分区表ALTERABLEtrb3PARTITIONBYKEY（id）PARTITIONS2创建分区；2： 删除分区ALTERTABLEtrDROPPARTITIONp2的数据；3： 为分区表添加分区ALTERTABLEmembersADPARTITION（PARTITIONp3VALUELESTHAN（2000））；备用表DDPA
RANGE,LIST分区管理
1:为未分区表创建分区
ALTER TABLE trb3 PARTITION BY KEY(id) PARTITIONS 2;
2:删除某个分区的数据
ALTER TABLE tr DROP PARTITION p2;
3:为分区表添加一个分区
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 (2010),
    PARTITION p6 VALUES LESS THAN MAXVALUE
);
4:将分区表的第一个分区分为两个新的分区
ALTER TABLE members
    REORGANIZE PARTITION p0 INTO (
        PARTITION n0 VALUES LESS THAN (1960),
        PARTITION n1 VALUES LESS THAN (1970)
);
5:也可以将两个分区合并为一个分区,也可以理解为重新组织分区
ALTER TABLE members REORGANIZE PARTITION s0,s1 INTO (
    PARTITION p0 VALUES LESS THAN (1970)
);
ALTER TABLE tbl_name
    REORGANIZE PARTITION partition_list
    INTO (partition_definitions);
ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO (
    PARTITION m0 VALUES LESS THAN (1980),
    PARTITION m1 VALUES 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, 18),
    PARTITION np VALUES in (4, 8, 12)
);
 

删除分区表：alter table tb_user remove partitioning;

 
HASH,KEY 分区管理
1:创建一个hash分区表
CREATE TABLE clients (
    id INT,
    fname VARCHAR(30),
    lname VARCHAR(30),
    signed DATE
)
PARTITION BY HASH( MONTH(signed) )
PARTITIONS 12;
将分区表从12个分区变为8个分区
ALTER TABLE clients COALESCE PARTITION 4;
同样的有以下的语句关于KEY分区的表:
mysql> CREATE TABLE clients_lk (
    ->     id INT,
    ->     fname VARCHAR(30),
    ->     lname VARCHAR(30),
    ->     signed DATE
    -> )
    -> PARTITION BY LINEAR KEY(signed)
    -> PARTITIONS 12;
Query OK, 0 rows affected (0.03 sec)

mysql> ALTER TABLE clients_lk COALESCE PARTITION 4;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0
当然还有有限制的
mysql> ALTER TABLE clients COALESCE PARTITION 18;
ERROR 1478 (HY000): Cannot remove all partitions, use DROP TABLE instead
要是分区数比现有的分区数多的话,只能使用 ADD来添加分区数.下面就表示增加了6个分区数
ALTER TABLE clients ADD PARTITION PARTITIONS 6;
交换分区,子分区的管理

交换分区,ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt

where pt is the partitioned table and p is the partition or subpartition of pt to be exchanged with unpartitioned table nt, provided that the following statements are true:
要满足以下的条件:
1:PT是已经分区表,nt不是临时表
2:两张表的表结构必须是一模一样的
3:nt不能有外键约束,也不能有关于其他表的外键约束.
4:nt表中的数据没有分区P以外的数据.WITHOUT VALIDATION指定的时候这条就可以忽视调
另外很重要的一点就是想要拥有EXCHANGE的权限的话必须对全表有DROP的权限才可以执行.
 
alter table  ......EXCHANGE partition  将不会调用任何的触发器,执行完以后被EXCHANGE 的表的自增列就会重新赋初始值.
例如: 
ALTER TABLE pt 
    EXCHANGE PARTITION p 
    WITH TABLE nt with VALIDATION ;
 
1:将分区和一个没有分区的表EXCHANGE
 
创建表插入语句:
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");
查看分区和分区的行数
 
 SELECT PARTITION_NAME, TABLE_ROWS 
    FROM INFORMATION_SCHEMA.PARTITIONS 
    WHERE TABLE_NAME = 'e';
 
SELECT TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA = 'p' AND TABLE_NAME LIKE 'e';
创建新表:
CREATE TABLE e2 LIKE e;
然后交换分区开始了:
ALTER TABLE e EXCHANGE PARTITION p0 WITH TABLE e2;
这个语句是很奇怪的,如果e2里面没有数据的话就是切出分区,如果e2里面有数据的话就是相互交换
但是如果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 trb1G
*************************** 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 | 浏览：195 | 发布日期：

免责声明：文章转载自《MySQL分区表管理》仅用于学习参考。如对内容有疑问，请及时联系本站处理。

上篇Eclipse 常用快捷键window配合虚拟机VMware搭建虚拟ubuntu服务器入坑集锦下篇

宿迁高防，2C2G15M，22元/月；香港BGP，2C5G5M，25元/月 雨云优惠码：MjYwNzM=

相关文章

MySQL会话控制限制登录次数

MySQL 5.7 以后提供了Connection-Control插件用来控制客户端在登录操作连续失败一定次数后的响应的延迟。 该插件可有效的防止客户端暴力登录的风险(攻击)。该插件包含以下两个组件： connection_control：控制失败次数以及延迟时间 connection_control_failed_login_attempts：将登录失...

将 excel文件数据导入MySQL数据库中

    第一步：先将Excel文件另存为文本文件（制表符分割） 第二步：将生成的txt文件另存，并修改编码格式utf8; 第三步：将文件放到指定位置，或自己想要的位置： G:city.txt 第四步：避免创建数据库及表出现中文乱码和查看编码方法： 1、创建数据库的时候：CREATE DATABASE `test` CHARACTER SET 'utf8...

mysql灾备演练问题

前期写的mysql热备份脚本恢复，还没有正式用到过，但是今天演练灾备恢复，但是遇到几个问题。 测试环境： 搭建mysql，安装xtrabackup vim /etc/yum.repos.d/Percona.repo [percona] name = CentOS $releasever - Percona baseurl=http://repo.per...

Mac安装PHP运行环境

先安装brew ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)" nginx的安装与配置 brew install nginx 修改配置文件 sudo vim /usr/local/etc/nginx/nginx.conf #修...

Druid 架构

本篇译自 Druid项目白皮书部分内容（ https://github.com/apache/incubator-druid/tree/master/publications/whitepaper/druid.pdf），如果有兴趣可看细看原pdf【初次翻译多多包涵】 一个 Druid 集群包含多种特定功能的节点， 我们相信这种设计能够分散业务并且简化整个系...

Mysql批量更新的一个坑-&allowMultiQueries=true允许批量更新

前言        实际上，我们经常会遇到这样的需求，那就是利用Mybatis批量更新或者批量插入，但是，实际上即使Mybatis完美支持你的sql，你也得看看你说操作的数据库是否支持，而阿福，最近就遇到这样的一个坑。 问题        先带大家来看一段sql的配置， <update id="updateAllAvailable"> <...

最新文章

随机推荐

思享工具箱导航