• Mysql 分区表-分区操作


    一、查看MySQL是否支持分区

    1、MySQL5.6以及之前版本

    show variables like '%partition%';

    2、MySQL5.7

    show plugins;

    二、分区表的分类与限制

    1、分区表分类

    RANGE分区:基于属于一个给定连续区间的列值,把多行分配给分区。

    LIST分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。

    HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。

    KEY分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

    复合分区:在MySQL 5.6版本中,只支持RANGE和LIST的子分区,且子分区的类型只能为HASH和KEY。

    2、分区表限制

    1)分区键必须包含在表的所有主键、唯一键中。

    2)MYSQL只能在使用分区函数的列本身进行比较时才能过滤分区,而不能根据表达式的值去过滤分区,即使这个表达式就是分区函数也不行。

    3)最大分区数: 不使用NDB存储引擎的给定表的最大可能分区数为8192(包括子分区)。如果当分区数很大,但是未达到8192时提示 Got error … from storage engine: Out of resources when opening file,可以通过增加open_files_limit系统变量的值来解决问题,当然同时打开文件的数量也可能由操作系统限制。

    4)不支持查询缓存: 分区表不支持查询缓存,对于涉及分区表的查询,它自动禁用。 查询缓存无法启用此类查询。

    5)分区的innodb表不支持外键。

    6)服务器SQL_mode影响分区表的同步复制。 主机和从机上的不同SQL_mode可能会导致sql语句; 这可能导致分区之间的数据分配给定主从位置不同,甚至可能导致插入主机上成功的分区表在从库上失败。 为了获得最佳效果,您应该始终在主机和从机上使用相同的服务器SQL模式。

    7)ALTER TABLE … ORDER BY: 对分区表运行的ALTER TABLE … ORDER BY列语句只会导致每个分区中的行排序。

    8)全文索引。 分区表不支持全文索引,即使是使用InnoDB或MyISAM存储引擎的分区表。
    9)分区表无法使用外键约束。
    10)Spatial columns: 具有空间数据类型(如POINT或GEOMETRY)的列不能在分区表中使用。
    11)临时表: 临时表不能分区。
    12)subpartition问题: subpartition必须使用HASH或KEY分区。 只有RANGE和LIST分区可能被分区; HASH和KEY分区不能被子分区。
    13)分区表不支持mysqlcheck,myisamchk和myisampack。
     

    三、创建分区表

    1、range分区

    行数据基于一个给定的连续区间的列值放入分区。
    CREATE TABLE `test_11` (
      `id` int(11) NOT NULL,
      `t` date NOT NULL,
      PRIMARY KEY (`id`,`t`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
     PARTITION BY RANGE (to_days(t))
    (PARTITION p20170801 VALUES LESS THAN (736907) ENGINE = InnoDB,
     PARTITION p20170901 VALUES LESS THAN (736938) ENGINE = InnoDB,
     PARTITION pmax VALUES LESS THAN maxvalue ENGINE = InnoDB);123456789
    然后插入4条数据:
    insert into test_11 values (1,"20170722"),(2,"20170822"),(3,"20170823"),(4,"20170824");1
    然后查看information下partitions对分区别信息的统计:
    select PARTITION_NAME as "分区",TABLE_ROWS as "行数" from information_schema.partitions where table_schema="mysql_test" and table_name="test_11";
    +-----------+--------+
    | 分区      | 行数   |
    +-----------+--------+
    | p20170801 |      1 |
    | p20170901 |      3 |
    +-----------+--------+
    2 rows in set (0.00 sec)12345678
    可以看出分区p20170801插入1行数据,p20170901插入的3行数据。
    可以是用year、to_days、unix_timestamp等函数对相应的时间字段进行转换,然后分区。

    2、list分区

    和range分区一样,只是list分区面向的是离散的值
    mysql> CREATE TABLE h2 (
        ->   c1 INT,
        ->   c2 INT
        -> )
        -> PARTITION BY LIST(c1) (
        ->   PARTITION p0 VALUES IN (1, 4, 7),
        ->   PARTITION p1 VALUES IN (2, 5, 8)
        -> );
    Query OK, 0 rows affected (0.11 sec)123456789
    与RANGE分区的情况不同,没有“catch-all”,如MAXVALUE; 分区表达式的所有预期值应在PARTITION … VALUES IN(…)子句中涵盖。 包含不匹配的分区列值的INSERT语句失败并显示错误,如此示例所示:
    mysql> INSERT INTO h2 VALUES (3, 5);
    ERROR 1525 (HY000): Table has no partition for value 312

    3、hash分区

    根据用户自定义表达式的返回值来进行分区,返回值不能为负数
    CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
        PARTITION BY HASH( YEAR(col3) )
        PARTITIONS 4;123
    如果你插入col3的数值为’2005-09-15’,那么根据以下计算来选择插入的分区:
    MOD(YEAR('2005-09-01'),4)
        =  MOD(2005,4)
        =  1123

    4、key分区

    根据MySQL数据库提供的散列函数进行分区
    CREATE TABLE k1 (
        id INT NOT NULL,
        name VARCHAR(20),
        UNIQUE KEY (id)
    )
    PARTITION BY KEY()
    PARTITIONS 2;1234567
    KEY仅列出零个或多个列名称。 用作分区键的任何列必须包含表的主键的一部分或全部,如果该表具有一个。 如果没有列名称作为分区键,则使用表的主键(如果有)。如果没有主键,但是有一个唯一的键,那么唯一键用于分区键。但是,如果唯一键列未定义为NOT NULL,则上一条语句将失败。
    与其他分区类型不同,KEY使用的分区不限于整数或空值。 例如,以下CREATE TABLE语句是有效的:
    CREATE TABLE tm1 (
        s1 CHAR(32) PRIMARY KEY
    )
    PARTITION BY KEY(s1)
    PARTITIONS 10;12345
    注意:对于key分区表,不能执行ALTER TABLE DROP PRIMARY KEY,因为这样做会生成错误 ERROR 1466 (HY000): Field in list of fields for partition function not found in table. 

    5、Column分区

    COLUMN分区是5.5开始引入的分区功能,只有RANGE COLUMN和LIST COLUMN这两种分区;支持整形、日期、字符串;RANGE和LIST的分区方式非常的相似。
    COLUMNS和RANGE和LIST分区的区别
    1)针对日期字段的分区就不需要再使用函数进行转换了,例如针对date字段进行分区不需要再使用YEAR()表达式进行转换。
    2)COLUMN分区支持多个字段作为分区键但是不支持表达式作为分区键。
    column支持的数据类型:
    1)所有的整型,float和decimal不支持
    2)日期类型:date和datetime,其他不支持
    3)字符类型:CHAR, VARCHAR, BINARY和VARBINARY,blob和text不支持 
    单列的column range分区mysql> show create table list_c;
     CREATE TABLE `list_c` (
      `c1` int(11) DEFAULT NULL,
      `c2` int(11) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    /*!50500 PARTITION BY RANGE  COLUMNS(c1)
    (PARTITION p0 VALUES LESS THAN (5) ENGINE = InnoDB,
     PARTITION p1 VALUES LESS THAN (10) ENGINE = InnoDB) */
    多列的column range分区mysql> show create table list_c;
     CREATE TABLE `list_c` (
      `c1` int(11) DEFAULT NULL,
      `c2` int(11) DEFAULT NULL,
      `c3` char(20) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    /*!50500 PARTITION BY RANGE  COLUMNS(c1,c3)
    (PARTITION p0 VALUES LESS THAN (5,'aaa') ENGINE = InnoDB,
     PARTITION p1 VALUES LESS THAN (10,'bbb') ENGINE = InnoDB) */
    单列的column list分区mysql> show create table list_c;
     CREATE TABLE `list_c` (
      `c1` int(11) DEFAULT NULL,
      `c2` int(11) DEFAULT NULL,
      `c3` char(20) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    /*!50500 PARTITION BY LIST  COLUMNS(c3)
    (PARTITION p0 VALUES IN ('aaa') ENGINE = InnoDB,
     PARTITION p1 VALUES IN ('bbb') ENGINE = InnoDB) */ 

    6、子分区(组合分区)

    在分区的基础上再进一步分区,有时成为复合分区;
    MySQL数据库允许在range和list的分区上进行HASH和KEY的子分区。例如:
    CREATE TABLE ts (id INT, purchased DATE)
        PARTITION BY RANGE( YEAR(purchased) )
        SUBPARTITION BY HASH( TO_DAYS(purchased) )
        SUBPARTITIONS 2 (
            PARTITION p0 VALUES LESS THAN (1990),
            PARTITION p1 VALUES LESS THAN (2000),
            PARTITION p2 VALUES LESS THAN MAXVALUE
        );
    [root@mycat-3 ~]# ll /data/mysql_data_3306/mysql_test/ts*
    -rw-r----- 1 mysql mysql  8596 Aug  8 13:54 /data/mysql_data_3306/mysql_test/ts.frm
    -rw-r----- 1 mysql mysql 98304 Aug  8 13:54 /data/mysql_data_3306/mysql_test/ts#P#p0#SP#p0sp0.ibd
    -rw-r----- 1 mysql mysql 98304 Aug  8 13:54 /data/mysql_data_3306/mysql_test/ts#P#p0#SP#p0sp1.ibd
    -rw-r----- 1 mysql mysql 98304 Aug  8 13:54 /data/mysql_data_3306/mysql_test/ts#P#p1#SP#p1sp0.ibd
    -rw-r----- 1 mysql mysql 98304 Aug  8 13:54 /data/mysql_data_3306/mysql_test/ts#P#p1#SP#p1sp1.ibd
    -rw-r----- 1 mysql mysql 98304 Aug  8 13:54 /data/mysql_data_3306/mysql_test/ts#P#p2#SP#p2sp0.ibd
    -rw-r----- 1 mysql mysql 98304 Aug  8 13:54 /data/mysql_data_3306/mysql_test/ts#P#p2#SP#p2sp1.ibd
    1234567891011121314151617
    ts表根据purchased进行range分区,然后又进行了一次hash分区,最后形成了3*2个分区,可以从物理文件证实此分区方式。可以通过subpartition语法来显示指定子分区名称。
    注意:每个子分区的数量必须相同;如果一个分区表的任何子分区已经使用subpartition,那么必须表明所有的子分区名称;每个subpartition子句必须包括子分区的一个名字;子分区的名字必须是一致的
    另外,对于MyISAM表可以使用index directory和data direactory来指定各个分区的数据和索引目录,但是对于innodb表来说,因为该存储引擎使用表空间自动的进行数据和索引的管理,因此会忽略指定index和data的语法。
     
     

    四、普通表转换为分区表

    1、用alter table table_name partition by命令重建分区表

    alter table jxfp_data_bak PARTITION BY KEY(SH) PARTITIONS 8;

    五、分区表操作

    CREATE TABLE t1 (
        id INT,
        year_col INT
    )
    PARTITION BY RANGE (year_col) (
        PARTITION p0 VALUES LESS THAN (1991),
        PARTITION p1 VALUES LESS THAN (1995),
        PARTITION p2 VALUES LESS THAN (1999)
    );

    1、ADD PARTITION (新增分区)

    ALTER TABLE t1 ADD PARTITION (PARTITION p3 VALUES LESS THAN (2002));

    2、DROP PARTITION (删除分区)

    ALTER TABLE t1 DROP PARTITION p0, p1;

    3、TRUNCATE PARTITION(截取分区)

    ALTER TABLE t1 TRUNCATE PARTITION p0;

    ALTER TABLE t1 TRUNCATE PARTITION p1, p3;

    4、COALESCE PARTITION(合并分区)

    CREATE TABLE t2 (
        name VARCHAR (30),
        started DATE
    )
    PARTITION BY HASH( YEAR(started) )
    PARTITIONS 6;

    ALTER TABLE t2 COALESCE PARTITION 2;

    5、REORGANIZE PARTITION(拆分/重组分区)

    1)拆分分区

    ALTER TABLE table ALGORITHM=INPLACE, REORGANIZE PARTITION;

    ALTER TABLE employees ADD PARTITION (
        PARTITION p5 VALUES LESS THAN (2010),
        PARTITION p6 VALUES LESS THAN MAXVALUE
    );

    2)重组分区

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

    6、ANALYZE 、CHECK PARTITION(分析与检查分区)

    1)ANALYZE 读取和存储分区中值的分布情况

    ALTER TABLE t1 ANALYZE PARTITION p1, ANALYZE PARTITION p2;

    ALTER TABLE t1 ANALYZE PARTITION p1, p2;

    2)CHECK 检查分区是否存在错误

    ALTER TABLE t1 ANALYZE PARTITION p1, CHECK PARTITION p2;

    7、REPAIR分区

    修复被破坏的分区

    ALTER TABLE t1 REPAIR PARTITION p0,p1;

    8、OPTIMIZE

    该命令主要是用于回收空闲空间和分区的碎片整理。对分区执行该命令,相当于依次对分区执行 CHECK PARTITION, ANALYZE PARTITION,REPAIR PARTITION命令。

    譬如:

    ALTER TABLE t1 OPTIMIZE PARTITION p0, p1;

    9、REBUILD分区

    重建分区,它相当于先删除分区中的数据,然后重新插入。这个主要是用于分区的碎片整理。

    ALTER TABLE t1 REBUILD PARTITION p0, p1;

    10、EXCHANGE PARTITION(分区交换)

    分区交换的语法如下:

    ALTER TABLE pt EXCHANGE PARTITION p WITH TABLE nt

    其中,pt是分区表,p是pt的分区(注:也可以是子分区),nt是目标表。

    其实,分区交换的限制还是蛮多的:

    1) nt不能为分区表

    2)nt不能为临时表

    3)nt和pt的结构必须一致

    4)nt不存在任何外键约束,即既不能是主键,也不能是外键。

    5)nt中的数据不能位于p分区的范围之外。

    具体可参考MySQL的官方文档

    11、迁移分区(DISCARD 、IMPORT )

    ALTER TABLE t1 DISCARD PARTITION p2, p3 TABLESPACE;

    ALTER TABLE t1 IMPORT PARTITION p2, p3 TABLESPACE;

    实验环境:(都是mysql5.7)
    源库:192.168.2.200      mysql5.7.16    zhangdb下的emp_2分区表的
    目标库:192.168.2.100    mysql5.7.18    test下  (将zhangdb的emp表,导入到目标库的test schema下)
    --:在源数据库中创建测试分区表emp_2,然后导入数据
    MySQL [zhangdb]> CREATE TABLE emp_2(
    id BIGINT unsigned NOT NULL AUTO_INCREMENT,
    x VARCHAR(500) NOT NULL,
    y VARCHAR(500) NOT NULL,
    PRIMARY KEY(id)
    )
    PARTITION BY RANGE COLUMNS(id)
    (
    PARTITION p1 VALUES LESS THAN (1000), 
    PARTITION p2 VALUES LESS THAN (2000), 
    PARTITION p3 VALUES LESS THAN (3000)    
    ); 
    (接着创建存储过程,导入测试数据)
    DELIMITER //
    CREATE PROCEDURE insert_batch()
    begin
    DECLARE num INT;
    SET num=1;
    WHILE num < 3000 DO
    IF (num%10000=0) THEN
    COMMIT;
    END IF;
    INSERT INTO emp_2 VALUES(NULL, REPEAT('X', 500), REPEAT('Y', 500));
    SET num=num+1;
    END WHILE;
    COMMIT;
    END //
    DELIMITER ;
    mysql> select TABLE_NAME,PARTITION_NAME from information_schema.partitions where table_schema='zhangdb';
    +------------+----------------+
    | TABLE_NAME | PARTITION_NAME |
    +------------+----------------+
    | emp        | NULL           |
    | emp_2      | p1             |
    | emp_2      | p2             |
    | emp_2      | p3             |
    +------------+----------------+
    4 rows in set (0.00 sec)
    mysql> select count(*) from emp_2 partition (p1);
    +----------+
    | count(*) |
    +----------+
    |      999 |
    +----------+
    1 row in set (0.00 sec)
    mysql> select count(*) from emp_2 partition (p2);
    +----------+
    | count(*) |
    +----------+
    |     1000 |
    +----------+
    1 row in set (0.00 sec)
    mysql> select count(*) from emp_2 partition (p3);
    +----------+
    | count(*) |
    +----------+
    |     1000 |
    +----------+
    1 row in set (0.00 sec)
    从上面可以看出,emp_2分区表已经创建完成,并且有3个子分区,每个分区都有一点数据。
    --:在目标数据库中,创建emp_2表的结构,不要数据(要在源库,使用show create table emp_2G 的方法 查看创建该表的sql)
    MySQL [test]> CREATE TABLE `emp_2` (
      `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      `x` varchar(500) NOT NULL,
      `y` varchar(500) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=3000 DEFAULT CHARSET=utf8mb4
    /*!50500 PARTITION BY RANGE  COLUMNS(id)
    (PARTITION p1 VALUES LESS THAN (1000) ENGINE = InnoDB,
     PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
     PARTITION p3 VALUES LESS THAN (3000) ENGINE = InnoDB) */ ;
    [root@localhost test]# ll
    -rw-r----- 1 mysql mysql 98304 May 25 15:58 emp_2#P#p0.ibd
    -rw-r----- 1 mysql mysql 98304 May 25 15:58 emp_2#P#p1.ibd
    -rw-r----- 1 mysql mysql 98304 May 25 15:58 emp_2#P#p2.ibd
    注意:
    ※约束条件、字符集等等也必须一致,建议使用show create table t1; 来获取创建表的SQL,否则在新服务器上导入表空间的时候会提示1808错误。
    --:在目标数据库上,丢弃分区表的表空间
    MySQL [test]> alter table emp_2 discard tablespace;
    Query OK, 0 rows affected (0.12 sec)
    [root@localhost test]# ll    ---这时候在看,刚才的3个分区的idb文件都没有了
    -rw-r----- 1 mysql mysql  8604 May 25 04:14 emp_2.frm
    --:在源数据库上运行FLUSH TABLES … FOR EXPORT 锁定表并生成.cfg元数据文件,最后将cfg和ibd文件传输到目标数据库中
    mysql> flush tables emp_2 for export;
    Query OK, 0 rows affected (0.00 sec)
    [root@localhost zhangdb]# scp emp_2* root@192.168.2.100:/mysql/data/test/   --将文件cp到目标数据库
    mysql> unlock tables;   ---最后将表的锁是否
    --:在目标数据库中对文件授权,然后导入表空间查看数据是否完整可用
    [root@localhost test]# chown mysql.mysql emp_2#*
    MySQL [test]> alter table emp_2 import tablespace;
    Query OK, 0 rows affected (0.96 sec)
    MySQL [test]> select count(*) from emp_2;
    +----------+
    | count(*) |
    +----------+
    |     2999 |
    +----------+
    1 row in set (0.63 sec)
    从上面的查看得知,分区表都已经导入到目标数据库中了,
    另外,也可以将部分子分区导入到目标数据库中,(往往整个分区表会很大,可用只将需要用到的子分区导入到目标数据库中),
    将部分子分区导入到目标数据库的方法是:
    1)在创建目标表的时候,只需要创建要导入的分区即可,如: 只创建了p2 p3两个分区
     CREATE TABLE `emp_2` (
      `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      `x` varchar(500) NOT NULL,
      `y` varchar(500) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=3000 DEFAULT CHARSET=utf8mb4
    /*!50500 PARTITION BY RANGE  COLUMNS(id)
    (
     PARTITION p2 VALUES LESS THAN (2000) ENGINE = InnoDB,
     PARTITION p3 VALUES LESS THAN (3000) ENGINE = InnoDB) */
    2)从源库cp到目标库的文件,当然也就是这俩的,就不需要其他分区的了,
    3)其他的操作方法都一样了。

    六、如何获取分区的相关信息

    1. 通过 SHOW CREATE TABLE 语句来查看分区表的分区子句

    譬如:mysql> show create table e/G

    2. 通过 SHOW TABLE STATUS 语句来查看表是否分区对应Create_options字段

    譬如:
    mysql> show table status/G

    *************************** 1. row ***************************

    Name: e Engine: InnoDB Version: 10 Row_format: Compact Rows: 6 Avg_row_length: 10922 Data_length: 65536Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 2015-12-07 22:26:06 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: partitioned Comment:

    3. 查看 INFORMATION_SCHEMA.PARTITIONS表

    4. 通过 EXPLAIN PARTITIONS SELECT 语句查看对于具体的SELECT语句,会访问哪个分区。

     
     

    七、MySQL5.7对于partition表的改进

    HANDLER statements:MySQL 5.7.1分区表开始支持HANDLER语句;
    index condition pushdown:MySQL5.7.3分区表开始支持ICP;
    load data:MySQL5.7开始使用缓存来实现性能提升,每个分区使用130KB缓冲区来实现这一点;
    Per-partition索引缓存:MySQL5.7开始支持使用CACHE INDEX和LOAD INDEX INTO CACHE语句对分区的MyISAM表支持索引缓存;
    FOR EXPORT选项(FLUSH TABLES):MySQL 5.7.4分区的InnoDB表开始支持FLUSH TABLES语句FOR EXPORT选项;
    从MySQL 5.7.2开始,子分区支持ANALYZE,CHECK,OPTIMIZE,REPAIR和TRUNCATE操作;
     
    整理自:
    https://dev.mysql.com/doc/refman/5.7/en/alter-table-partition-operations.html
    https://www.2cto.com/database/201708/666142.html
    http://blog.51cto.com/fengfeng688/2149492
    https://www.aliyun.com/jiaocheng/1137826.html
     
  • 相关阅读:
    数据结构 B/B+树
    Hadoop的目录结构
    安装JDK
    OSTEP-projects concurrency-webserver
    第二十四章(制作HTTP服务器端)学习笔记
    day4_生成小数的程序
    day4_用集合生成8位密码的程序
    day4_集合操作
    day3_homework
    day6_random模块的用法、break和continue
  • 原文地址:https://www.cnblogs.com/xibuhaohao/p/10154281.html
Copyright © 2020-2023  润新知