精华内容
下载资源
问答
  • 表分区

    2019-08-04 19:19:46
    表分区简介分区的好处分区的限制和缺点分区的类型分区适合的场景 简介 MySQL在5.1版引入的分区是一种简单的水平拆分,用户需要在建表的时候加上分区参数,对应用是透明的无需修改代码 对用户来说,分区表是一个独立...

    简介

    MySQL在5.1版引入的分区是一种简单的水平拆分,用户需要在建表的时候加上分区参数,对应用是透明的无需修改代码
    对用户来说,分区表是一个独立的逻辑表,但是底层由多个物理子表组成,实现分区的代码实际上是通过对一组底层表的对象封装,但对SQL层来说是一个完全封装底层的黑盒子。MySQL实现分区的方式也意味着索引也是按照分区的子表定义,没有全局索引
    在这里插入图片描述
    用户的SQL语句是需要针对分区表做优化,SQL条件中要带上分区条件的列,从而使查询定位到少量的分区上,否则就会扫描全部分区,可以通过EXPLAIN PARTITIONS来查看某条SQL语句会落在那些分区上,从而进行SQL优化

    分区的好处

    • 可以让单表存储更多的数据
    • 分区表的数据更容易维护,可以通过清除整个分区批量删除大量数据,也可以增加新的分区来支持新插入的数据。另外,还可以对一个独立分区进行优化、检查、修复等操作
    • 部分查询能够从查询条件确定只落在少数分区上,速度会很快
    • 分区表的数据还可以分布在不同的物理设备上,从而高效利用多个硬件设备
    • 可以使用分区表来避免某些特殊瓶颈,例如InnoDB单个索引的互斥访问、ext3文件系统的inode锁竞争
    • 可以备份和恢复单个分区

    分区的限制和缺点

    • 一个表最多只能有1024个分区
    • 如果分区字段中有主键或者唯一索引的列,那么所有主键列和唯一索引列都必须包含进来
    • 分区表无法使用外键约束
    • NULL值会使分区过滤无效
    • 所有分区必须使用相同的存储引擎

    分区的类型

    • RANGE分区:基于属于一个给定连续区间的列值,把多行分配给分区
    • LIST分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择
    • HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式
    • KEY分区:类似于HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值

    分区适合的场景

    • 最适合的场景数据的时间序列性比较强,则可以按时间来分区
    CREATE TABLE members (
        firstname VARCHAR(25) NOT NULL,
        lastname VARCHAR(25) NOT NULL,
        username VARCHAR(16) NOT NULL,
        email VARCHAR(35),
        joined DATE NOT NULL
    )
    PARTITION BY RANGE( YEAR(joined) ) (
        PARTITION p0 VALUES LESS THAN (1960),
        PARTITION p1 VALUES LESS THAN (1970),
        PARTITION p2 VALUES LESS THAN (1980),
        PARTITION p3 VALUES LESS THAN (1990),
        PARTITION p4 VALUES LESS THAN MAXVALUE
    );
    

    查询时加上时间范围条件效率会非常高,同时对于不需要的历史数据能很容的批量删除。

    • 如果数据有明显的热点,而且除了这部分数据,其他数据很少被访问到,那么可以将热点数据单独放在一个分区,让这个分区的数据能够有机会都缓存在内存中,查询时只访问一个很小的分区表,能够有效使用索引和缓存

    另外MySQL有一种早期的简单的分区实现 - 合并表(merge table),限制较多且缺乏优化,不建议使用,应该用新的分区机制来替代

    展开全文
  • oracle表空间表分区详解及oracle表分区查询使用方法 此文从以下几个方面来整理关于分区表的概念及操作: 1.表空间及分区表的概念 2.表分区的具体作用 3.表分区的优缺点 4.表分区的几种类型及操作方法 5.对表分区的...

    oracle表空间表分区详解及oracle表分区查询使用方法

    此文从以下几个方面来整理关于分区表的概念及操作:

    1.表空间及分区表的概念
    2.表分区的具体作用
    3.表分区的优缺点
    4.表分区的几种类型及操作方法
    5.对表分区的维护性操作.

    表空间及分区表的概念
    表空间:是一个或多个数据文件的集合,所有的数据对象都存放在指定的表空间中,但主要存放的是表, 所以称作表空间。
    分区表:当表中的数据量不断增大,查询数据的速度就会变慢,应用程序的性能就会下降,这时就应该考虑对表进行分区。表进行分区后,逻辑上表仍然是一张完整的表,只是将表中的数据在物理上存放到多个表空间(物理文件上),这样查询数据时,不至于每次都扫描整张表。

    表分区的具体作用
    Oracle的表分区功能通过改善可管理性、性能和可用性,从而为各式应用程序带来了极大的好处。通常,分区可以使某些查询以及维护操作的性能大大提高。此外,分区还可以极大简化常见的管理任务,分区是构建千兆字节数据系统或超高可用性系统的关键工具。

    分区功能能够将表、索引或索引组织表进一步细分为段,这些数据库对象的段叫做分区。每个分区有自己的名称,还可以选择自己的存储特性。从数据库管理员的角度来看,一个分区后的对象具有多个段,这些段既可进行集体管理,也可单独管理,这就使数据库管理员在管理分区后的对象时有相当大的灵活性。但是,从应用程序的角度来看,分区后的表与非分区表完全相同,使用 SQL DML 命令访问分区后的表时,无需任何修改。

    什么时候使用分区表:

    表的大小超过2GB。
    表中包含历史数据,新的数据被增加都新的分区中。
    表分区的优缺点
    表分区有以下优点:

    改善查询性能:对分区对象的查询可以仅搜索自己关心的分区,提高检索速度。
    增强可用性:如果表的某个分区出现故障,表在其他分区的数据仍然可用;
    维护方便:如果表的某个分区出现故障,需要修复数据,只修复该分区即可;
    均衡I/O:可以把不同的分区映射到磁盘以平衡I/O,改善整个系统性能。
    缺点:

    已经存在的表没有方法可以直接转化为分区表。但是有几种方式可以间接完成这个操作,大家可以参考:oracle分区表的建立方法(包含已经存在的表要分区):http://blog.csdn.net/wanglilin/article/details/7177338
    表分区的几种类型及操作方法
    范围分区:
    范围分区将数据基于范围映射到每一个分区,这个范围是你在创建分区时指定的分区键决定的。这种分区方式是最为常用的,并且分区键经常采用日期。举个例子:你可能会将销售数据按照月份进行分区。
    当使用范围分区时,请考虑以下几个规则:

    1、每一个分区都必须有一个VALUES LESS THEN子句,它指定了一个不包括在该分区中的上限值。分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。
    2、所有分区,除了第一个,都会有一个隐式的下限值,这个值就是此分区的前一个分区的上限值。
    3、在最高的分区中,MAXVALUE被定义。MAXVALUE代表了一个不确定的值。这个值高于其它分区中的任何分区键的值,也可以理解为高于任何分区中指定的VALUE LESS THEN的值,同时包括空值。

    例一:假设有一个CUSTOMER表,表中有数据200000行,我们将此表通过CUSTOMER_ID进行分区,每个分区存储100000行,我们将每个分区保存到单独的表空间中,这样数据文件就可以跨越多个物理磁盘。下面是创建表和分区的代码,如下:

    CREATE TABLE CUSTOMER
    (
    CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY,
    FIRST_NAME VARCHAR2(30) NOT NULL,
    LAST_NAME VARCHAR2(30) NOT NULL,
    PHONEVARCHAR2(15) NOT NULL,
    EMAILVARCHAR2(80),
    STATUS CHAR(1)
    )
    PARTITION BY RANGE (CUSTOMER_ID)
    (
    PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE CUS_TS01,
    PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE CUS_TS02
    )
    例二:按时间划分

    CREATE TABLE ORDER_ACTIVITIES
    (
    ORDER_ID NUMBER(7) NOT NULL,
    ORDER_DATE DATE,
    TOTAL_AMOUNT NUMBER,
    CUSTOTMER_ID NUMBER(7),
    PAID CHAR(1)
    )
    PARTITION BY RANGE (ORDER_DATE)
    (
    PARTITION ORD_ACT_PART01 VALUES LESS THAN (TO_DATE('01- MAY -2003','DD-MON-YYYY')) TABLESPACEORD_TS01,
    PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUN-2003','DD-MON-YYYY')) TABLESPACE ORD_TS02,
    PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUL-2003','DD-MON-YYYY')) TABLESPACE ORD_TS03
    )
    例三:MAXVALUE

    CREATE TABLE RangeTable
    (
    idd INT PRIMARY KEY ,
    iNAME VARCHAR(10),
    grade INT
    )
    PARTITION BY RANGE (grade)
    (
    PARTITION part1 VALUES LESS THEN (1000) TABLESPACE Part1_tb,
    PARTITION part2 VALUES LESS THEN (MAXVALUE) TABLESPACE Part2_tb
    );
    列表分区:
    该分区的特点是某列的值只有几个,基于这样的特点我们可以采用列表分区。

    例一

    CREATE TABLE PROBLEM_TICKETS
    (
    PROBLEM_ID NUMBER(7) NOT NULL PRIMARY KEY,
    DESCRIPTION VARCHAR2(2000),
    CUSTOMER_ID NUMBER(7) NOT NULL,
    DATE_ENTERED DATE NOT NULL,
    STATUS VARCHAR2(20)
    )
    PARTITION BY LIST (STATUS)
    (
    PARTITION PROB_ACTIVE VALUES ('ACTIVE') TABLESPACE PROB_TS01,
    PARTITION PROB_INACTIVE VALUES ('INACTIVE') TABLESPACE PROB_TS02
    )
    例二

    CREATE TABLE ListTable
    (
    id INT PRIMARY KEY ,
    name VARCHAR (20),
    area VARCHAR (10)
    )
    PARTITION BY LIST (area)
    (
    PARTITION part1 VALUES ('guangdong','beijing') TABLESPACE Part1_tb,
    PARTITION part2 VALUES ('shanghai','nanjing') TABLESPACE Part2_tb
    );
    )
    散列分区:
    这类分区是在列值上使用散列算法,以确定将行放入哪个分区中。当列的值没有合适的条件时,建议使用散列分区。
    散列分区为通过指定分区编号来均匀分布数据的一种分区类型,因为通过在I/O设备上进行散列分区,使得这些分区大小一致。
    例一:

    CREATE TABLE HASH_TABLE
    (
    COL NUMBER(8),
    INF VARCHAR2(100)
    )
    PARTITION BY HASH (COL)
    (
    PARTITION PART01 TABLESPACE HASH_TS01,
    PARTITION PART02 TABLESPACE HASH_TS02,
    PARTITION PART03 TABLESPACE HASH_TS03
    )
    简写:

    CREATE TABLE emp
    (
    empno NUMBER (4),
    ename VARCHAR2 (30),
    sal NUMBER
    )
    PARTITION BY HASH (empno) PARTITIONS 8
    STORE IN (emp1,emp2,emp3,emp4,emp5,emp6,emp7,emp8);
    hash分区最主要的机制是根据hash算法来计算具体某条纪录应该插入到哪个分区中,hash算法中最重要的是hash函数,Oracle中如果你要使用hash分区,只需指定分区的数量即可。建议分区的数量采用2的n次方,这样可以使得各个分区间数据分布更加均匀。

    组合范围散列分区
    这种分区是基于范围分区和列表分区,表首先按某列进行范围分区,然后再按某列进行列表分区,分区之中的分区被称为子分区。

    CREATE TABLE SALES
    (
    PRODUCT_ID VARCHAR2(5),
    SALES_DATE DATE,
    SALES_COST NUMBER(10),
    STATUS VARCHAR2(20)
    )
    PARTITION BY RANGE(SALES_DATE) SUBPARTITION BY LIST (STATUS)
    (
    PARTITION P1 VALUES LESS THAN(TO_DATE('2003-01-01','YYYY-MM-DD'))TABLESPACE rptfact2009
    (
    SUBPARTITION P1SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009,
    SUBPARTITION P1SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009
    ),
    PARTITION P2 VALUES LESS THAN (TO_DATE('2003-03-01','YYYY-MM-DD')) TABLESPACE rptfact2009
    (
    SUBPARTITION P2SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009,
    SUBPARTITION P2SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009
    )
    )
    复合范围散列分区:
    这种分区是基于范围分区和散列分区,表首先按某列进行范围分区,然后再按某列进行散列分区。

    create table dinya_test
    (
    transaction_id number primary key,
    item_id number(8) not null,
    item_description varchar2(300),
    transaction_date date
    )
    partition by range(transaction_date)subpartition by hash(transaction_id) subpartitions 3 store in (dinya_space01,dinya_space02,dinya_space03)
    (
    partition part_01 values less than(to_date(‘2006-01-01','yyyy-mm-dd')),
    partition part_02 values less than(to_date(‘2010-01-01','yyyy-mm-dd')),
    partition part_03 values less than(maxvalue)
    );
    有关表分区的一些维护性操作:
    添加分区
    以下代码给SALES表添加了一个P3分区

    ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2003-06-01','YYYY-MM-DD'));
    注意:以上添加的分区界限应该高于最后一个分区界限。

    以下代码给SALES表的P3分区添加了一个P3SUB1子分区

    ALTER TABLE SALES MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES('COMPLETE');
    删除分区
    以下代码删除了P3表分区:

    ALTER TABLE SALES DROP PARTITION P3;
    在以下代码删除了P4SUB1子分区:

    ALTER TABLE SALES DROP SUBPARTITION P4SUB1;
    注意:如果删除的分区是表中唯一的分区,那么此分区将不能被删除,要想删除此分区,必须删除表。

    截断分区
    截断某个分区是指删除某个分区中的数据,并不会删除分区,也不会删除其它分区中的数据。当表中即使只有一个分区时,也可以截断该分区。通过以下代码截断分区:

    ALTER TABLE SALES TRUNCATE PARTITION P2;
    通过以下代码截断子分区:

    ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2;
    合并分区
    合并分区是将相邻的分区合并成一个分区,结果分区将采用较高分区的界限,值得注意的是,不能将分区合并到界限较低的分区。以下代码实现了P1 P2分区的合并:

    ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2;
    拆分分区
    拆分分区将一个分区拆分两个新分区,拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分。

    ALTER TABLE SALES SBLIT PARTITION P2 AT(TO_DATE('2003-02-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22);
    接合分区(coalesca)
    结合分区是将散列分区中的数据接合到其它分区中,当散列分区中的数据比较大时,可以增加散列分区,然后进行接合,值得注意的是,接合分区只能用于散列分区中。通过以下代码进行接合分区:

    ALTER TABLE SALES COALESCA PARTITION;
    重命名表分区
    以下代码将P21更改为P2

    ALTER TABLE SALES RENAME PARTITION P21 TO P2;
    相关查询
    -- 跨分区查询
    select sum( ) from
    (select count(
    ) cn from t_table_SS PARTITION (P200709_1)
    union all
    select count(*) cn from t_table_SS PARTITION (P200709_2)
    );

    --查询表上有多少分区
    SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME='tableName'

    --查询索引信息
    select object_name,object_type,tablespace_name,sum(value)
    from v$segment_statistics
    where statistic_name IN ('physical reads','physical write','logical reads')and object_type='INDEX'
    group by object_name,object_type,tablespace_name
    order by 4 desc

    --显示数据库所有分区表的信息:
    select * from DBA_PART_TABLES

    --显示当前用户可访问的所有分区表信息:
    select * from ALL_PART_TABLES

    --显示当前用户所有分区表的信息:
    select * from USER_PART_TABLES

    --显示表分区信息 显示数据库所有分区表的详细分区信息:
    select * from DBA_TAB_PARTITIONS

    --显示当前用户可访问的所有分区表的详细分区信息:
    select * from ALL_TAB_PARTITIONS

    --显示当前用户所有分区表的详细分区信息:
    select * from USER_TAB_PARTITIONS

    --显示子分区信息 显示数据库所有组合分区表的子分区信息:
    select * from DBA_TAB_SUBPARTITIONS

    --显示当前用户可访问的所有组合分区表的子分区信息:
    select * from ALL_TAB_SUBPARTITIONS

    --显示当前用户所有组合分区表的子分区信息:
    select * from USER_TAB_SUBPARTITIONS

    --显示分区列 显示数据库所有分区表的分区列信息:
    select * from DBA_PART_KEY_COLUMNS

    --显示当前用户可访问的所有分区表的分区列信息:
    select * from ALL_PART_KEY_COLUMNS

    --显示当前用户所有分区表的分区列信息:
    select * from USER_PART_KEY_COLUMNS

    --显示子分区列 显示数据库所有分区表的子分区列信息:
    select * from DBA_SUBPART_KEY_COLUMNS

    --显示当前用户可访问的所有分区表的子分区列信息:
    select * from ALL_SUBPART_KEY_COLUMNS

    --显示当前用户所有分区表的子分区列信息:
    select * from USER_SUBPART_KEY_COLUMNS

    --怎样查询出oracle数据库中所有的的分区表
    select * from user_tables a where a.partitioned='YES'

    --删除一个表的数据是
    truncate table table_name;

    --删除分区表一个分区的数据是
    alter table table_name truncate partition p5;

    展开全文
  • mysql 表分区的几种方式: RANGE分区:基于属于一个给定连续区间的列值,把多行分配给分区。 LIST分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。 HASH分区:基于...

    mysql 表分区的几种方式:

    1. RANGE分区:基于属于一个给定连续区间的列值,把多行分配给分区。
    2. LIST分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
    3. HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。
    4. KEY分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

    常用的按时间分区可以按照年、月、日等条件用来分区,因为是按时间条件分区所以选用RANGE分区方式。

    分区的切条件判断:

    1. LESS THAN:如果是数值就是小于等于,时间则是小于。
    2. LESS THAN MAX:不等于。
    3. IN:包含于某某区间。

    按实际分区的时间函数选用:

    1. 按日分区切分时的条件可以用day()、to_days()时间函数,需要注意的是必须是返回值是整形的。
    2. 按月分区采用month()时间函数;
    3. 其他条件分区时间函数可到我关于时间函数的博文查看:https://blog.csdn.net/qq_36216193/article/details/90404527

    其中按时间字段create_date按天分区,首先需要把date字段改成主键,后添加分区切分规则

    ALTER TABLE js_sys_log 
    DROP PRIMARY KEY,
    ADD PRIMARY KEY (id, create_date);  --之前id是主键,所以这里第2主键为联合主键
    
    ALTER TABLE  js_sys_log  ADD PRIMARY KEY (create_date);
    

    为了便于管理分区名通常以“p时间”来命名。

    示例一:
        ALTER TABLE  js_sys_log  PARTITION BY RANGE (to_days(create_date)) (
            PARTITION  p20181101  VALUES LESS THAN (to_days('20181101')),
            PARTITION  p20181102  VALUES LESS THAN (to_days('20181102')),
            PARTITION  p20181103  VALUES LESS THAN (to_days('20181103')),
            PARTITION  p20181104  VALUES LESS THAN (to_days('20181104')),
            PARTITION  p20181105  VALUES LESS THAN (to_days('20181105')),
            PARTITION  p20181106  VALUES LESS THAN (to_days('20181106')),
            PARTITION  p20181107  VALUES LESS THAN (to_days('20181107')),
            PARTITION  p20181108  VALUES LESS THAN (to_days('20181108')),
            PARTITION  p20181109  VALUES LESS THAN (to_days('20181109')),
            PARTITION  p20181110  VALUES LESS THAN (to_days('20181110')),
            PARTITION  p_other  VALUES LESS THAN MAXVALUE );
        );
    

    后期添加分区:

    示例二:
        ALTER TABLE  js_sys_log  ADD PARTITION (PARTITION  p20181111 VALUES LESS THAN (TO_DAYS ('2018-11-11')));
    

    删除分区:

    示例三:
        ALTER TABLE  js_sys_log  DROP PARTITION p20181101;
    

    查询MySQL的系统字典库得知所有的分区详情信息

    示例四:
        SELECT  *  FROM  information_schema. PARTITIONS  t  WHERE t.PARTITION_NAME IS NOT NULL
    

    在分区创建后可以通过过程和事件控制自动增加表分区。

    1、查看是否开启事件

    show variables like "event_scheduler";
    

    查询结果
    2、开启事件(本次设置中生效)

    SET GLOBAL event_scheduler = on;    
    或
    SET GLOBAL event_scheduler = 1;
    

    3、开启时间(长期生效)
    编辑 mysql 配置文件,并在【mysqld】下添加该变量:
    在这里插入图片描述
    再重启 mysqld 服务, 登录 mysql ,查看配置,确认是否生效。

    4、设置分区脚本

    use test;
    
    DELIMITER ||
    -- 删除存储过程
    drop procedure if exists 事件名称 ||
    -- 注意:使用该存储过程必须保证相应数据库表中至少有一个手动分区
    -- 创建存储过程[通过数据库名和对应表名]-建多少个分区,分区时间间隔为多少
    -- databasename:创建分区的数据库
    -- tablename:创建分区的表的名称
    -- partition_number:一次创建多少个分区
    -- partitiontype:分区类型[0按天分区,1按月分区,2按年分区]
    -- gaps:分区间隔,如果分区类型为0则表示每个分区的间隔为 gaps天;
    -- 如果分区类型为1则表示每个分区的间隔为 gaps月
    -- 如果分区类型为2则表示每个分区的间隔为 gaps年
    create procedure 事件名称 (in databasename varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,in tablename varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, in partition_number int, in partitiontype int, in gaps int)
    L_END:
    begin     
        declare max_partition_description varchar(255) default '';
        declare p_name varchar(255) default 0;       
        declare p_description varchar(255) default 0;   
        declare isexist_partition varchar(255) default 0; 
     declare i int default 1;
      
     -- 查看对应数据库对应表是否已经有手动分区[自动分区前提是必须有手动分区]
        select partition_name into isexist_partition from information_schema.partitions where table_schema = databasename  and table_name = tablename limit 1;
        -- 如果不存在则打印错误并退出存储过程
        if isexist_partition <=> "" then
           select "partition table not is exist" as "ERROR";
           leave L_END;
        end if;
     
        -- 获取最大[降序获取]的分区描述[值]
        select partition_description into max_partition_description  from information_schema.partitions where table_schema = databasename  and table_name = tablename order by partition_description desc limit 1;
       
        -- 如果最大分区没有,说明没有手动分区,则无法创建自动分区
        if max_partition_description <=> "" then
           select "partition table is error" as "ERROR";
           leave L_END;
        end if;
     
        -- 替换前后的单引号[''两个引号表示一个单引号的转义]
        -- set max_partition_description = REPLACE(max_partition_description, '''', '');
        -- 或使用如下语句
        set max_partition_description = REPLACE(max_partition_description-1, '\'', '');
     
        -- 自动创建number个分区
        while (i <= partition_number) do
                     if (partitiontype = 0) then
                         -- 每个分区按天递增,递增gaps天
                         set p_description = DATE_ADD(FROM_DAYS(max_partition_description), interval i*gaps day); 
                     elseif (partitiontype = 1) then
                         -- 每个分区按月递增,递增gaps月
                         set p_description = DATE_ADD(FROM_DAYS(max_partition_description), interval i*gaps month); 
                     else 
                         -- 每个分区按年递增,递增gaps年
                         set p_description = DATE_ADD(FROM_DAYS(max_partition_description), interval i*gaps year);
                     end if;
                     -- 删除空格
                     set p_name = REPLACE(p_description, ' ', '');
                     -- 例如10.20的记录实际是less than 10.21
                     set p_description = DATE_ADD(p_description, interval 1 day); 
                     -- 如果有横杆替换为空
              set p_name = REPLACE(p_name, '-', '');
                     -- 删除时间冒号
                     set p_name = REPLACE(p_name, ':', '');
                     -- alter table tablename add partition ( partition pname values less than ('2019-05-22 23:59:59') );
              set @sql=CONCAT('ALTER TABLE ', tablename ,' ADD PARTITION ( PARTITION p', p_name ,' VALUES LESS THAN (TO_DAYS(\'', p_description ,'\')))');
                     -- set @sql=CONCAT('ALTER TABLE ', tablename ,' ADD PARTITION ( PARTITION p', p_name ,' VALUES LESS THAN (TO_DAYS(\'', p_description ,'\')))');
                     -- 打印sql变量
              -- select @sql;
                     -- 准备sql语句
              PREPARE stmt from @sql;
                     -- 执行sql语句
              EXECUTE stmt;
                     -- 释放资源
              DEALLOCATE PREPARE stmt;
                     -- 递增变量
              set i = (i + 1) ;
     
        end while;          
    end ||
    -- 恢复语句中断符
    DELIMITER ;
    

    5、事件处理(每天执行一次)

    DELIMITER ||
    drop event if exists 事件名称 ||
    create event 事件名称
    on schedule every 1 day
    starts '2019-05-22 23:59:59'
    do
    BEGIN
        call 事件名称 ('数据库名', '分区表名', 1, 0, 1);
    END ||
    DELIMITER ;
    

    6、删除事件

    DROP EVENT [IF EXISTS] 事件名称;
    

    7、查看事件

    show events;
    

    8、表分区查看:

    explain partitions select * from 分区表名;
    
    展开全文
  • mysql 表分区 查看表分区 修改表分区

    千次阅读 2019-05-08 17:12:26
    一、mysql分区简介 数据库分区 数据库分区是一种物理数据库设计技术。虽然分区技术可以实现很多效果,但其主要目的是为了在特定的SQL操作中减少数据读写的...MySQL的分区主要有两种形式:水平分区和垂直分区...

    转载自:https://www.cnblogs.com/ruiati/p/6993539.html

     

    一、       mysql分区简介

    数据库分区

    数据库分区是一种物理数据库设计技术。虽然分区技术可以实现很多效果,但其主要目的是为了在特定的SQL操作中减少数据读写的总量以缩减sql语句的响应时间,同时对于应用来说分区完全是透明的。

    MySQL的分区主要有两种形式:水平分区和垂直分区

     

    水平分区(HorizontalPartitioning)

    这种形式的分区是对根据表的行进行分区,通过这样的方式不同分组里面的物理列分割的数据集得以组合,从而进行个体分割(单分区)或集体分割(1个或多个分区)。
    所有在表中定义的列在每个数据集中都能找到,所以表的特性依然得以保持。水平分区一定要通过某个属性列来分割。常见的比如年份,日期等。

     

    垂直分区(VerticalPartitioning)

    这种分区方式一般来说是通过对表的垂直划分来减少目标表的宽度,使某些特定的列被划分到特定的分区,每个分区都包含了其中的列所对应所有行。
    可以用  showvariables like '%partition%';

    命令查询当前的mysql数据库版本是否支持分区。

    分区的作用:数据库性能的提升和简化数据管理

    在扫描操作中,mysql优化器只扫描保护数据的那个分区以减少扫描范围获得性能的提高。

    分区技术使得数据管理变得简单,删除某个分区不会对另外的分区造成影响,分区有系统直接管理不用手工干预。

    mysql从5.1版本开始支持分区。每个分区的名称是不区分大小写。同个表中的分区表名称要唯一。

    二、       mysql分区类型

    根据所使用的不同分区规则可以分成几大分区类型。

    RANGE 分区:

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

    LIST 分区:

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

    HASH分区:

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

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

    复合分区:

    基于RANGE/LIST 类型的分区表中每个分区的再次分割。子分区可以是 HASH/KEY 等类型。

     

    三、       mysql分区表常用操作示例

    以部门员工表为例子:

    1)       创建range分区

     

    create table emp

    (empno varchar(20) not null ,

    empname varchar(20),

    deptno int,

    birthdate date,

    salary int

    )

    partition by range(salary)

    (

    partition p1 values less than (1000),

    partition p2 values less than (2000),

    partition p3 values less than maxvalue

    );

    以员工工资为依据做范围分区。

     

    create table emp

    (empno varchar(20) not null ,

    empname varchar(20),

    deptno int,

    birthdate date not null,

    salary int

    )

    partition by range(year(birthdate))

    (

    partition p1 values less than (1980),

    partition p2 values less than (1990),

    partition p3 values less than maxvalue

    );

    year(birthdate)表达式(计算员工的出生日期)作为范围分区依据。这里最值得注意的是表达式必须有返回值。

     

    2)       创建list分区

     

    create table emp

    (empno  varchar(20) not null ,

    empname varchar(20),

    deptno  int,

    birthdate date not null,

    salary int

    )

    partition by list(deptno)

    (

    partition p1 values in  (10),

    partition p2 values in  (20),

    partition p3 values  in  (30)

    );

    以部门作为分区依据,每个部门做一分区。

     

    3)       创建hash分区

    HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中,必须明确指定一个给定的列值或列值集合应该保存在哪 个分区中;而在HASH分区中,MySQL 自动完成这些工作,你所要做的只是基于将要被哈希的列值指定一个列值或表达式,以及指定被分区的表将要被分割成的分区数量。

    create table emp

    (empno varchar(20) not null ,

    empname varchar(20),

    deptno int,

    birthdate date not null,

    salary int

    )

    partition by hash(year(birthdate))

    partitions 4;

    4)       创建key分区

    按照KEY进行分区类似于按照HASH分区,除了HASH分区使用的用户定义的表达式,而KEY分区的哈希函数是由MySQL 服务器提供,服务器使用其自己内部的哈希函数,这些函数是基于与PASSWORD()一样的运算法则。“CREATE TABLE ...PARTITION BY KEY”的语法规则类似于创建一个通过HASH分区的表的规则。它们唯一的区别在于使用的关键字是KEY而不是HASH,并且KEY分区只采用一个或多个 列名的一个列表。

    create table emp

    (empno varchar(20) not null ,

    empname varchar(20),

    deptno int,

    birthdate date not null,

    salary int

    )

    partition by key(birthdate)

    partitions 4;

     

     

    5)       创建复合分区

     

    range - hash(范围哈希)复合分区

     

    create table emp

    (empno varchar(20) not null ,

    empname varchar(20),

    deptno int,

    birthdate date not null,

    salary int

    )

    partition by range(salary)

    subpartition by hash(year(birthdate))

    subpartitions 3

    (

    partition p1 values less than (2000),

    partition p2 values less than maxvalue

    );

    range- key复合分区

     

    create table emp

    (empno varchar(20) not null ,

    empname varchar(20),

    deptno int,

    birthdate date not null,

    salary int

    )

    partition by range(salary)

    subpartition by key(birthdate)

    subpartitions 3

    (

    partition p1 values less than (2000),

    partition p2 values less than maxvalue

    );

    list - hash复合分区

    CREATE TABLE emp (

    empno varchar(20) NOT NULL,

    empname varchar(20) ,

    deptno int,

    birthdate date NOT NULL,

    salary int

    )

    PARTITION BY list (deptno)

    subpartition by hash(year(birthdate))

    subpartitions 3

    (

    PARTITION p1 VALUES in  (10),

    PARTITION p2 VALUES in  (20)

    )

    ;

    list - key 复合分区

     

    CREATE TABLE empk (

    empno varchar(20) NOT NULL,

    empname varchar(20) ,

    deptno int,

    birthdate date NOT NULL,

    salary int

    )

    PARTITION BY list (deptno)

    subpartition by key(birthdate)

    subpartitions 3

    (

    PARTITION p1 VALUES in  (10),

    PARTITION p2 VALUES in  (20)

    )

    ;

    6)       分区表的管理操作

    删除分区:

    alter table emp drop partition p1;

    不可以删除hash或者key分区。

    一次性删除多个分区,alter table emp drop partition p1,p2;

     

    增加分区:

    alter table emp add partition (partition p3 values less than (4000));

    alter table empl add partition (partition p3 values in (40));

     

    分解分区:

    Reorganizepartition关键字可以对表的部分分区或全部分区进行修改,并且不会丢失数据。分解前后分区的整体范围应该一致。

    alter table te

    reorganize partition p1 into

    (

    partition p1 values less than (100),

    partition p3 values less than (1000)

    ); ----不会丢失数据

     

    合并分区:

    Merge分区:把2个分区合并为一个。
    alter table te

    reorganize partition p1,p3 into

    (partition p1 values less than (1000));

    ----不会丢失数据

     

    重新定义hash分区表:

    Alter table emp partition by hash(salary)partitions 7;

    ----不会丢失数据

    重新定义range分区表:

    Alter table emp partitionbyrange(salary)

    (

    partition p1 values less than (2000),

    partition p2 values less than (4000)

    ); ----不会丢失数据

     

    删除表的所有分区:

     

    Alter table emp removepartitioning;--不会丢失数据

     

    重建分区:

    这和先删除保存在分区中的所有记录,然后重新插入它们,具有同样的效果。它可用于整理分区碎片。

    ALTER TABLE emp rebuild partitionp1,p2;

     

    优化分区:

    如果从分区中删除了大量的行,或者对一个带有可变长度的行(也就是说,有VARCHAR,BLOB,或TEXT类型的列)作了许多修改,可以使用“ALTER TABLE ... OPTIMIZE PARTITION”来收回没有使用的空间,并整理分区数据文件的碎片。

    ALTER TABLE emp optimize partition p1,p2;

     

    分析分区:

    读取并保存分区的键分布。

    ALTER TABLE emp analyze partition p1,p2;

     

    修补分区:

    修补被破坏的分区。

    ALTER TABLE emp repairpartition p1,p2;

     

    检查分区:

    可以使用几乎与对非分区表使用CHECK TABLE 相同的方式检查分区。

    ALTER TABLE emp CHECK partition p1,p2;

    这个命令可以告诉你表emp的分区p1,p2中的数据或索引是否已经被破坏。如果发生了这种情况,使用“ALTER TABLE ... REPAIR PARTITION”来修补该分区。

     

     

    【mysql分区表的局限性】

    1.      在5.1版本中分区表对唯一约束有明确的规定,每一个唯一约束必须包含在分区表的分区键(也包括主键约束)。

     

    CREATE TABLE emptt (

    empno varchar(20) NOT NULL  ,

    empname varchar(20),

    deptno int,

    birthdate date NOT NULL,

    salary int ,

    primary key (empno)

    )

    PARTITION BY range (salary)

    (

    PARTITION p1 VALUES less than (100),

    PARTITION p2 VALUES less than (200)

    );

    这样的语句会报错。MySQL Database Error: A PRIMARY KEY must include allcolumns in the table's partitioning function;

    CREATE TABLE emptt (

    empno varchar(20) NOT NULL  ,

    empname varchar(20) ,

    deptno int(11),

    birthdate date NOT NULL,

    salary int(11) ,

    primary key (empno,salary)

    )

    PARTITION BY range (salary)

    (

    PARTITION p1 VALUES less than (100),

    PARTITION p2 VALUES less than (200)

    );

    在主键中加入salary列就正常。

     

    2.      MySQL分区处理NULL值的方式

    如果分区键所在列没有notnull约束。

    如果是range分区表,那么null行将被保存在范围最小的分区。

    如果是list分区表,那么null行将被保存到list为0的分区。

    在按HASH和KEY分区的情况下,任何产生NULL值的表达式mysql都视同它的返回值为0。

    为了避免这种情况的产生,建议分区键设置成NOT NULL。

     

    3.      分区键必须是INT类型,或者通过表达式返回INT类型,可以为NULL。唯一的例外是当分

    区类型为KEY分区的时候,可以使用其他类型的列作为分区键( BLOB or TEXT 列除外)。

     

    4.      对分区表的分区键创建索引,那么这个索引也将被分区,分区键没有全局索引一说。

    5.      只有RANG和LIST分区能进行子分区,HASH和KEY分区不能进行子分区。

    6.      临时表不能被分区。

     

    四、       获取mysql分区表信息的几种方法

    1.     show create table 表名
    可以查看创建分区表的create语句

    2.     show table status 
    可以查看表是不是分区表

    3.     查看information_schema.partitions表 
    select 
      partition_name part,  
      partition_expression expr,  
      partition_description descr,  
      table_rows  
    from information_schema.partitions  where 
      table_schema = schema()  
      and table_name='test';  
    可以查看表具有哪几个分区、分区的方法、分区中数据的记录数等信息

    4.     explain partitions select语句
    通过此语句来显示扫描哪些分区,及他们是如何使用的.

     

    五、       分区表性能比较

    1.     创建两张表: part_tab(分区表),no_part_tab(普通表)

    CREATE TABLEpart_tab

    ( c1 int defaultNULL, c2 varchar2(30) default NULL, c3 date not null)

    PARTITION BYRANGE(year(c3))

    (PARTITION p0VALUES LESS THAN (1995),

    PARTITION p1 VALUESLESS THAN (1996) ,

    PARTITION p2 VALUESLESS THAN (1997) ,

    PARTITION p3 VALUESLESS THAN (1998) ,

     PARTITION p4 VALUES LESS THAN (1999) ,

    PARTITION p5 VALUESLESS THAN (2000) ,

    PARTITION p6 VALUESLESS THAN (2001) ,

    PARTITION p7 VALUESLESS THAN (2002) ,

    PARTITION p8 VALUESLESS THAN (2003) ,

    PARTITION p9 VALUESLESS THAN (2004) ,

    PARTITION p10VALUES LESS THAN (2010),

    PARTITION p11VALUES LESS THAN (MAXVALUE) );

    CREATE TABLE no_part_tab

    ( c1 int defaultNULL, c2 varchar2(30) default NULL, c3 date not null);

     

    2.     用存储过程插入800万条数据

    CREATE PROCEDUREload_part_tab()

        begin

        declare v int default 0;

        while v < 8000000

        do

            insert into part_tab

            values (v,'testingpartitions',adddate('1995-01-01',(rand(v)*36520)mod 3652));

             set v = v + 1;

        end while;

    end;

    insert into no_part_tab  select * frompart_tab;

    3.     测试sql性能

    查询分区表:

    selectcount(*) from part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31';

    +----------+
    | count(*) |
    +----------+
    |   795181 |
    +----------+
    1 row in set (2.62 sec)

    查询普通表:

    selectcount(*) from part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31';

    +----------+
    | count(*) |
    +----------+
    |   795181 |
    +----------+
    1 row in set (7.33 sec)

    分区表的执行时间比普通表少70%。

     

    4.     通过explain语句来分析执行情况

    mysql>explain select count(*) from part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31';

    +----+-------------+----------+------+---------------+------+---------+------+---------+-------------+

    | id |select_type | table    | type |possible_keys | key  | key_len | ref  | rows   | Extra       |

    +----+-------------+----------+------+---------------+------+---------+------+---------+-------------+

    |  1 | SIMPLE      | part_tab | ALL  | NULL          | NULL | NULL    | NULL | 7980796 | Using where |

    +----+-------------+----------+------+---------------+------+---------+------+---------+-------------+

    1 rowin set

     

    mysql>explain select count(*) from no_part_tab where c3 > date '1995-01-01'and c3 < date '1995-12-31';

    +----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+

    | id |select_type | table       | type |possible_keys | key  | key_len | ref  | rows   | Extra       |

    +----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+

    |  1 | SIMPLE      | no_part_tab | ALL  | NULL          | NULL | NULL    | NULL | 8000206 | Using where |

    +----+-------------+-------------+------+---------------+------+---------+------+---------+-------------+

    1 rowin set

    mysql >

    分区表执行扫描了7980796行,而普通表则扫描了8000206行。

     

     

    分类: MYSQL数据库

    展开全文
  • TiDB 表分区

    千次阅读 2020-03-31 10:40:10
    对比MySQL的表分区 使用之前,要先读官方文档 表分区,注意事项 确认表中是否存在唯一键(包括主键) ,如果存在,那么唯一键 中,必须包含分区表达式中用到的所有列 如果表中不存在唯一键 ,那么第1条规则也就不...
  • db2 产看查看表分区,添加表分区

    千次阅读 2018-07-07 08:02:00
    --查看表分区SELECT * FROM SYSCAT.datapartitions WHERE TABNAME='table_name'--添加表分区alter table table_name add partition statis_month STARTINT'201301' INCLUSIVE ENDING '20090102' INCLUSIVE ...
  • oracle表分区增加分区

    千次阅读 2019-09-25 05:39:02
    昨天发现车辆轨迹库的表分区用完了,连夜加了分区,我们存轨迹的这张表建分区的时候按日期建只到2012-10-15,分区名从TAB_GPSBUSHIS_P1....TAB_GPSBUSHIS_P999,从2012-10-15开始的数据就默认都存在了TAB_GPSBUSHIS_...
  • sqlserver 表分区

    万次阅读 2019-12-30 10:42:50
    表分区请看如下链接 http://www.cnblogs.com/knowledgesea/p/3696912.html
  • mysql 表分区 按天分区

    万次阅读 2017-12-14 10:30:57
    MySQL表分区就是把一张表根据设定好的条件下把表切分成若干个小表相互之间,在MySQL的5.1版本以后就开始支持表分区的功能,在使用表的分区后会使MySQL中大表在平时查询统计时性能提升。使用MySQL的表分区有以下这些...
  • kudu表分区

    千次阅读 2020-03-13 14:27:35
    它的好处这里不多说啦,公司的大数据查询有一部分是用到这个技术,这里就列几个其他博客里不常见的涉及kudu表分区的语句(其实官网里都有,只是中文博客里还没见到)。 分区有两种方式,hash 和range分区, hash分....
  • 表分区与表空间

    千次阅读 2017-11-15 15:50:46
    介绍了关于表分区和表空间的基本内容。
  • Mysql数据库表分区存储到指定磁盘路径2016年10月21日 20:56:404851人阅读 评论(1) 收藏 举报 分类:【大数据技术】(13) 版权声明:本文为博主原创文章,未经博主允许不得转载。转载请务必加上原作者:铭毅...
  • --创建表分区的表 按时分秒来自动创建分区的 枚举值:'DAY'|'HOUR'|'MINUTE'|'SECOND'create table dwuser.temp_cjn( id NUMBER, deal_time DATE, name VARCHAR2(255))PARTITION BY RANGE(deal_ti...
  • mysql数据库表分区

    2018-11-19 13:18:31
    一、表分区 1、对表分区的原因  数据库数据越来越大,导致单个表中数据太多。以至于增删改查询速度变慢,而且由于表的锁机制导致应用操作也受到严重影响,出现了数据库性能瓶颈。 2、表分区  表分区就是将一个...
  • oracle自动添加分区表分区

    千次阅读 2017-12-14 18:40:00
    1、新建一个存储过程去添加分区表分区 create or replace procedureCREATE_BILL_LOGOUT_DEF_PARTION is today varchar2(20); partionsql varchar2(300); tomorrow varchar2(20); begin today :=
  • oracle表分区详解

    2017-08-01 17:01:21
    表空间及分区表的概念表分区的具体作用表分区的优缺点表分区的几种类型及操作方法对表分区的维护性操作  1.表空间及分区表的概念 表空间:  是一个或多个数据文件的集合,所有的数据对象都存放在指定的表...
  • Oracle 的表分区详解

    千次阅读 2019-07-29 14:04:58
    从以下几个方面来整理关于分区... 表分区的具体作用 表分区的优缺点 表分区的几种类型及操作方法 对表分区的维护性操作 1.表空间及分区表的概念 表空间:  是一个或多个数据文件的集合,所有的数据对象都存放...
  • 有关表分区的一些维护性操作: 注:分区根据具体情况选择。 表分区有以下优点: 1、数据查询:数据被存储到多个文件上,减少了I/O负载,查询速度提高。 2、数据修剪:保存历史数据非常的理想。 3、备份:将大表...
  • Oracle表分区:操纵已分区的表

    千次阅读 2012-05-15 22:29:09
    Oracle表分区:范围分区 Oracle.表分区:散列分区  Oracle.表分区:列表分区  Oracle.表分区:复合分区 Oracle表分区:操纵已分区的表  分区维护操作有: 添加分区 删除分区 截断分区 合并...
  • oracle表分区的实现

    万次阅读 2018-07-02 10:45:16
    前端时间新上线了一个改造系统,但是由于之前有几张大表...表分区的具体作用3.表分区的优缺点4.表分区的几种类型及操作方法5.对表分区的维护性操作.1、表空间及分区表的概念表空间:是一个或多个数据文件的集合,...
  • # 查看 分区信息 分区键 SELECT pg_get_partition_def('core.t_payments'::regclass,true); SELECT pg_get_partition_def('wallet.dwd_wallet_trans'::regclass,true);
  • SQL SERVER 表分区技术

    千次阅读 2018-04-08 08:08:54
    表分区就是把一个数据库表文件拆分成多个文件,并放到不同的文件组甚至是不同的磁盘中,但是在外界访问时看到的还是一个数据库表,这样不同的磁盘不同的文件组可以提高并发访问效率,而对于开发技术人员来说面对还...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 38,709
精华内容 15,483
关键字:

表分区