精华内容
下载资源
问答
  • 表分区
    千次阅读
    2021-07-20 15:11:56

    一、什么是分区表

    表分区有以下优点:
    1、数据查询:数据被存储到多个文件上,减少了I/O负载,查询速度提高。
    2、数据修剪:保存历史数据非常的理想。
    3、备份:将大表的数据分成多个文件,方便备份和恢复。
    4、并行性:可以同时向表中进行DML操作,并行性性能提高,均衡I/O:可以把不同的分区映射到磁盘以平衡I/O,改善整个系统性能。
    5、增强可用性:如果表的某个分区出现故障,表在其他分区的数据仍然可用;
    6、维护方便:如果表的某个分区出现故障,需要修复数据,只修复该分区即可;
    7、改善查询性能:对分区对象的查询可以仅搜索自己关心的分区,提高检索速度。
    8、需要注意的是包含LONG、LONGRAW数据类型的表不能分区,如果表格大于2G需要考虑分区。

    二、分区表的种类

    1、RANGE 范围分区

    说明:针对记录字段的值在某个范围。
    规则:
    (1)、每一个分区都必须有一个VALUES LESS THEN子句,它指定了一个不包括在该分区中的上限值。
    分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。
    (2)、所有分区,除了第一个,都会有一个隐式的下限值,这个值就是此分区的前一个分区的上限值。
    (3)、在最高的分区中,MAXVALUE被定义。MAXVALUE代表了一个不确定的值。这个值高于其它分区中的任何分区键的值,
    也可以理解为高于任何分区中指定的VALUE LESS THEN的值,同时包括空值。若不添加maxvalue的分区插入数值一旦超过设置的最大上限会报错。

    例一,按date范围创建分区表

    CREATE TABLE PART_TAB_CUSTOMER_BY_RANGE 
    ( 
      CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY, 
      FIRST_NAME VARCHAR2(30) NOT NULL, 
      LAST_NAME VARCHAR2(30) NOT NULL, 
      PHONE VARCHAR2(15) NOT NULL, 
      EMAIL VARCHAR2(80), 
      SEX VARCHAR2(10),
      STATUS VARCHAR2(10),
      INSERT_DATE DATE 
    ) 
    PARTITION BY RANGE (INSERT_DATE) --按时间分区
    ( 
      PARTITION DATE_RANGE1 VALUES LESS THAN (TO_DATE(' 2001-01-01', 'YYYY-MM-DD')) TABLESPACE part_Data1, 
      PARTITION DATE_RANGE2 VALUES LESS THAN (TO_DATE(' 2007-01-01', 'YYYY-MM-DD')) TABLESPACE part_Data2,
      PARTITION DATE_RANGE3 VALUES LESS THAN (maxvalue) TABLESPACE part_Data3 
    ) 
    

    例二、按照number范围分区

    PARTITION BY RANGE (CUSTOMER_ID) --按id分区
    ( 
     PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE part_Data1, 
     PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE part_Data2,
     PARTITION CUS_PART2 VALUES LESS THAN (maxvalue) TABLESPACE part_Data3
    )
    

    2、LIST 列表分区

    说明:该分区的特点是某列的值只有有限个值,基于这样的特点我们可以采用列表分区。
    规则:默认分区为DEFAULT,若不添加DEFAULT的分区插入数值不属于所设置的分区会报错。
    例一、姓氏

    CREATE TABLE PART_TAB_CUSTOMER_BY_LIST
    ( 
      CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY, 
      PHONE VARCHAR2(15) NOT NULL, 
      EMAIL VARCHAR2(80), 
      SEX VARCHAR2(10),
      CORP_ID VARCHAR2(3),
      INSERT_DATE DATE,
      SUM_DATE varchar2(4)
    ) 
    PARTITION BY LIST (SEX) 
    ( 
      PARTITION MALE      VALUES ('男')    TABLESPACE part_Data1, 
      PARTITION FEMALE    VALUES ('女')    TABLESPACE part_Data2 
    ) 
    

    例二、varchar2的日期

    PARTITION BY LIST (SUM_DATE) 
    ( 
      PARTITION SUM_DATE1    VALUES ('2012')    TABLESPACE part_Data1, 
      PARTITION SUM_DATE2    VALUES ('2013')    TABLESPACE part_Data2,
      PARTITION SUM_DATE2    VALUES (DEFAULT)    TABLESPACE part_Data2 
    ) 
    

    3、HASH 散列分区

    说明:这类分区是在列值上使用散列算法,以确定将行放入哪个分区中。
    规则:当列的值没有合适的条件,没有范围的规律,也没有固定的值,建议使用散列分区。
    散列分区为通过指定分区编号来均匀分布数据的一种分区类型,因为通过在I/O设备上进行散列分区,
    使得这些分区大小一致。建议分区的数量采用2的n次方,这样可以使得各个分区间数据分布更加均匀。

    创建hash分区有两种方法:一种方法是指定分区的名字,另一种方法是指定分区数量。

    例一、常规方法指定分区名字

    CREATE TABLE PART_TAB_CUSTOMER_BY_HASH
    ( 
      CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY, 
      FIRST_NAME VARCHAR2(30) NOT NULL, 
      LAST_NAME VARCHAR2(30) NOT NULL, 
      PHONE VARCHAR2(15) NOT NULL, 
      EMAIL VARCHAR2(80), 
      SEX VARCHAR2(10),
      STATUS VARCHAR2(10),
      INSERT_DATE DATE 
    ) 
    PARTITION BY HASH (CUSTOMER_ID) --按id散列
    ( 
      PARTITION hash1 TABLESPACE part_Data1, 
      PARTITION hash2 TABLESPACE part_Data2
    ) 
    

    例二、指定分区数量
    PARTITION BY HASH (empno) PARTITIONS 2 STORE IN (part_Data1,part_Data2);
    –往往我们不需要知道bash分区的名字,因为数据放在哪个分区是oracle根据bash算法存放的,并不是用户指定,
    所以当用户插入一条记录,并不能确定放在哪个分区,这个不同于range和list

    3、复合分区

    说明:顾名思义,复合分区就由range+list+hash两两组合而来,一般分为range+list,list+range,range+hash,list+bash,这里指列出几种常用组合。
    规则:如果组合中存在hash,皆把hash分区作为子分区,原因大家可以通过hash分区的性质知道。
    例一、range+list,这种分区是基于范围分区和列表分区,表首先按某列进行范围分区,然后再按某列进行列表分区。

    CREATE TABLE PART_TAB_SALE_RANGE_LIST 
    ( 
      PRODUCT_ID VARCHAR2(5), 
      SALES_DATE DATE, 
      SALES_COST NUMBER(10), 
      STATUS VARCHAR2(10),
    ) 
    PARTITION BY RANGE(SALES_DATE) 
      SUBPARTITION BY LIST (STATUS) 
    ( 
      PARTITION P1 VALUES LESS THAN (TO_DATE('2003-01-01','YYYY-MM-DD')) TABLESPACE part_Data1 
      ( 
        SUBPARTITION P1SUB1 VALUES ('ACTIVE') TABLESPACE part_Data1, 
        SUBPARTITION P1SUB2 VALUES ('INACTIVE') TABLESPACE part_Data2,
        SUBPARTITION P1SUB3 VALUES (DEFAULT) TABLESPACE part_Data3
      ), 
      PARTITION P2 VALUES LESS THAN (TO_DATE('2003-03-01','YYYY-MM-DD')) TABLESPACE part_Data2 
      ( 
        SUBPARTITION P2SUB1 VALUES ('ACTIVE') TABLESPACE part_Data1, 
        SUBPARTITION P2SUB2 VALUES ('INACTIVE') TABLESPACE part_Data2,
        SUBPARTITION P2SUB3 VALUES (DEFAULT) TABLESPACE part_Data3 
      ),
      PARTITION P3 VALUES LESS THAN (maxvalue) TABLESPACE part_Data3
      ( 
        SUBPARTITION P3SUB1 VALUES ('ACTIVE') TABLESPACE part_Data1,
        SUBPARTITION P3SUB2 VALUES ('INACTIVE') TABLESPACE part_Data2,
        SUBPARTITION P3SUB3 VALUES (DEFAULT) TABLESPACE part_Data3
      ) 
    ) 
    

    例二、range+bash,这种分区是基于范围分区和散列分区,表首先按某列进行范围分区,然后再按某列进行散列分区。

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

    三、分区表的操作

    1.DML操作

    说明:DML操作和平常的表一样,有异于的地方是增加了可以指定表的特定分区才执行DML操作。

    例如,查询分区表

    SELECT * FROM PART_TAB_SALE_RANGE_LIST;--不指定分区直接查询
    SELECT * FROM PART_TAB_SALE_RANGE_LIST PARTITION(P2);--指定分区查询
    SELECT * FROM PART_TAB_SALE_RANGE_LIST SUBPARTITION(P1SUB2);--指定小分区查询
    

    对于分区表,指定分区执行DML效率更高,但,如果指定了分区,而条件中的数据又不在该分区中时,将不会产生任何DML操作。

    2.DDL操作

    1)添加分区

    (1)对range分区表添加分区
    ALTER TABLE PART_TAB_SALE_RANGE_LIST ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2009-06-01','YYYY-MM-DD')); 
    

    注意:增加一个分区的时候,增加的分区的条件必须大于现有分区的最大值,否则系统将提示ORA-14074 partition bound must collate higher than that of the last partition 错误。

    (2)对range分区表list子分区添加分区
    ALTER TABLE PART_TAB_SALE_RANGE_LIST MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES('COMPLETE'); 
    

    2)删除分区

    (1)对range分区表删除分区

    ALTER TABLE PART_TAB_SALE_RANGE_LIST DROP PARTITION P3;

    (2)对range分区表list子分区删除子分区

    ALTER TABLE PART_TAB_SALE_RANGE_LIST DROP SUBPARTITION P4SUB1;

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

    3)截断分区

    说明:截断某个分区是指清空某个分区中的数据,并不会删除分区,也不会删除其它分区中的数据。当表中即使只有一个分区时,也可以截断该分区。
    注意:如果截断的分区表有约束,需要先关闭约束。alter table sales disable/enable constraint restraint_name,截断分区会使全局索引无效,需要重建。

    (1)清空分区:
       ALTER TABLE SALES TRUNCATE PARTITION P2;--这种方式使全局分区索引无效
       ALTER TABLE SALES TRUNCATE PARTITION P2 update indexes;--这种方式使全局分区索引有效UPDATE GLOBAL INDEXES
    
    (2)清空子分区:
     ALTER TABLE PART_TAB_SALE_RANGE_LIST TRUNCATE SUBPARTITION P2SUB2; 
     ALTER TABLE PART_TAB_SALE_RANGE_LIST TRUNCATE SUBPARTITION P2SUB2 update indexes;
    

    4)合并分区

    说明:合并分区是将相邻的分区合并成一个分区,结果分区将采用较高分区的界限,值得注意的是,不能将分区合并到界限较低的分区。

    ALTER TABLE PART_TAB_SALE_RANGE_LIST MERGE PARTITIONS P1,P2 INTO PARTITION P2; 
    

    注意:在本例中将原有的表的part_01分区和part_02分区进行了合并,合并后的分区为part_02,
    如果在合并的时候把合并后的分区定为part_01的时候,系统将提示ORA-14275 cannot reuse lower-bound partition as resulting partition 错误。

    5)拆分分区

    说明:拆分分区将一个分区拆分两个新分区,拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分。

    ALTER TABLE PART_TAB_SALE_RANGE_LIST SBLIT PARTITION P2 AT(TO_DATE('2003-03-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22); 
    

    6)接合分区

    说明:接合分区是将散列分区中的数据接合到其它分区中,当散列分区中的数据比较大时,可以增加散列分区,然后进行接合,
    值得注意的是,接合分区只能用于散列分区中。

    ALTER TABLE PART_TAB_SALE_RANGE_LIST COALESCA PARTITION;
    

    7)重命名表分区

    ALTER TABLE SALES RENAME PARTITION P21 TO P2;
    

    8)移动分区

    说明:把分区移动到令一个表空间,移动后要重建索引

    alter table sales move partiton sp1 tablespace tablespace_name;
    alter index index_name rebuild;
    

    –查询是否移动成功 SELECT TABLE_OWNER,TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,SUBPARTITION_COUNT FROM DBA_TAB_PARTITIONS WHERE TABLE_OWNER='SCOTT';

    四、表分区相关的数据字典表

    –显示表分区信息 显示数据库所有分区表的详细分区信息: select * from DBA_TAB_PARTITIONS
    –显示子分区信息 显示数据库所有组合分区表的子分区信息: select * from DBA_TAB_SUBPARTITIONS
    –显示数据库所有分区表的信息: select * from DBA_PART_TABLES
    –显示数据库可访问的所有分区表的分区列信息:select * from DBA_PART_KEY_COLUMNS

    DBA_IND_PARTITIONS
    DBA_IND_SUBPARTITIONS

    –查询索引信息

    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 
    

    五、局部索引与全局索引

    表可以按range,hash,list分区,表分区后,其上的索引和普通表上的索引有所不同,oracle对于分区表上的索引分为2类,即局部索引和全局索引。

    更多相关内容
  • 表分区详解

    千次阅读 2022-03-09 11:19:30
    一、什么是表分区? 二、表分区与分表的区别 三、表分区有什么好处? 四、分区表的限制因素 五、如何判断当前MySQL是否支持分区? 六、MySQL支持的分区类型有哪些? 1、RANGE分区 2、LIST分区 3、HASH分区 ...

    目录

    一、什么是表分区?

    二、表分区与分表的区别

    三、表分区有什么好处?

    四、分区表的限制因素

    五、如何判断当前MySQL是否支持分区?

    六、MySQL支持的分区类型有哪些?

    1、RANGE分区

    2、LIST分区

    3、HASH分区

    4、KEY分区


    一、什么是表分区?

    mysql数据库中的数据是以文件的形势存在磁盘上的,默认放在/mysql/data下面(可以通过my.cnf中的datadir来查看),一张表主要对应着三个文件,一个是frm存放表结构的,一个是myd存放表数据的,一个是myi存表索引的。

    如果一张表的数据量太大的话,那么myd,myi就会变的很大,查找数据就会变的很慢,这个时候我们可以利用mysql的分区功能,在物理上将这一张表对应的三个文件,分割成许多个小块,这样呢,我们查找一条数据时,就不用全部查找了,只要知道这条数据在哪一块,然后在那一块找就行了。如果表的数据太大,可能一个磁盘放不下,这个时候,我们可以把数据分配到不同的磁盘里面去。

    表分区,是指根据一定规则,将数据库中的一张表分解成多个更小的,容易管理的部分。从逻辑上看,只有一张表,但是底层却是由多个物理分区组成。

    二、表分区与分表的区别

    分表:指的是通过一定规则,将一张表分解成多张不同的表。比如将用户订单记录根据时间成多个表。 分表与分区的区别在于:分区从逻辑上来讲只有一张表,而分表则是将一张表分解成多张表。

    三、表分区有什么好处?

    (1)、与单个磁盘或文件系统分区相比,可以存储更多的数据。

    (2)、对于那些已经失去保存意义的数据,通常可以通过删除与那些数据有关的分区,很容易地删除那些数据。相反地,在某些情况下,添加新数据的过程又可以通过为那些新数据专门增加一个新的分区,来很方便地实现。

    (3)、一些查询可以得到极大的优化,这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内,这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改,所以在第一次配置分区方案时还不曾这么做时,可以重新组织数据,来提高那些常用查询的效率。

    (4)、涉及到例如SUM()和COUNT()这样聚合函数的查询,可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id;”。通过“并行”,这意味着该查询可以在每个分区上同时进行,最终结果只需通过总计所有分区得到的结果。

    5)、通过跨多个磁盘来分散数据查询,来获得更大的查询吞吐量。

    四、分区表的限制因素

    (1)、一个表最多只能有1024个分区。

    (2)、 MySQL5.1中,分区表达式必须是整数,或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持。

    (3)、如果分区字段中有主键或者唯一索引的列,那么多有主键列和唯一索引列都必须包含进来。即:分区字段要么不包含主键或者索引列,要么包含全部主键和索引列。

    (4)、分区表中无法使用外键约束。

    (5)、MySQL的分区适用于一个表的所有数据和索引,不能只对表数据分区而不对索引分区,也不能只对索引分区而不对表分区,也不能只对表的一部分数据分区。

    五、如何判断当前MySQL是否支持分区?

    mysql> show variables like '%partition%';
    +-------------------+-------+
    | Variable_name     | Value |
    +-------------------+-------+
    | have_partitioning | YES   |
    +-------------------+-------+
    1 row in set (0.00 sec)

    have_partintioning 的值为YES,表示支持分区。

    六、MySQL支持的分区类型有哪些?

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

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

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

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

    说明:在MySQL5.1版本中,RANGE,LIST,HASH分区要求分区键必须是INT类型,或者通过表达式返回INT类型。但KEY分区的时候,可以使用其他类型的列(BLOB,TEXT类型除外)作为分区键。

    1、RANGE分区

    根据范围分区,范围应该连续但是不重叠,使用PARTITION BY RANGE, VALUES LESS THAN关键字。不使用COLUMNS关键字时RANGE括号内必须为整数字段名或返回确定整数的函数。

    (1)根据数值范围

    drop table if exists employees;
    create table employees(
        id int not null,
        fname varchar(30),
        lname varchar(30),
        hired date not null default '1970-01-01',
        separated date not null default '9999-12-31',
        job_code int not null default 0,
        store_id int not null default 0
    )engine=myisam default charset=utf8
    partition by range(store_id)(
        partition p0 values less than (6),
        partition p1 values less than (11),
        partition p2 values less than (16),
        partition p3 values less than (21)
    );
    

     

    insert into employees (id,fname,lname,hired,store_id) values(1,'张三','张','2015-05-04',1);
    insert into employees (id,fname,lname,hired,store_id) values(2,'李四','李','2016-10-01',5);
    insert into employees (id,fname,lname,hired,store_id) values(3,'王五','王','2016-11-14',10);
    insert into employees (id,fname,lname,hired,store_id) values(4,'赵六','赵','2017-08-24',15);
    insert into employees (id,fname,lname,hired,store_id) values(5,'田七','田','2018-05-20',20);

     

    按照这种分区方案,在商店1到5工作的雇员相对应的所有行被保存在分区P0中,商店6到10的雇员保存在P1中,依次类推。注意,每个分区都是按顺序进行定义,从最低到最高。这是PARTITION BY RANGE 语法的要求。

    对于包含数据(6,'亢八','亢','2018-06-24',13)的一个新行,可以很容易地确定它将插入到p2分区中。

    insert into employees (id,fname,lname,hired,store_id) values(6,'亢八','亢','2018-06-24',13);

     

     

    但是如果增加了一个编号为第21的商店(7,'周九','周','2018-07-24',21),将会发生什么呢?在这种方案下,由于没有规则把store_id大于20的商店包含在内,服务器将不知道把该行保存在何处,将会导致错误。

    insert into employees (id,fname,lname,hired,store_id) values(7,'周九','周','2018-07-24',21);
    
    ERROR 1526 (HY000): Table has no partition for value 21

     要避免这种错误,可以通过在CREATE TABLE语句中使用一个“catchall” VALUES LESS THAN子句,该子句提供给所有大于明确指定的最高值的值:

    create table employees(
        id int not null,
        fname varchar(30),
        lname varchar(30),
        hired date not null default '1970-01-01',
        separated date not null default '9999-12-31',
        job_code int not null default 0,
        store_id int not null default 0
    )engine=myisam default charset=utf8
    partition by range(store_id)(
        partition p0 values less than (6),
        partition p1 values less than (11),
        partition p2 values less than (16),
        partition p3 values less than (21),
      partition p4 values less than MAXVALUE 
    );

    (2)根据TIMESTAMP范围

    drop table if exists quarterly_report_status;
    create table quarterly_report_status(
      report_id int not null,
      report_status varchar(20) not null,
      report_updated timestamp not null default current_timestamp on update current_timestamp
    )
    partition by range(unix_timestamp(report_updated))(
          partition p0 values less than (unix_timestamp('2008-01-01 00:00:00')),
          partition p1 values less than (unix_timestamp('2008-04-01 00:00:00')),
          partition p2 values less than (unix_timestamp('2008-07-01 00:00:00')),
          partition p3 values less than (unix_timestamp('2008-10-01 00:00:00')),
          partition p4 values less than (unix_timestamp('2009-01-01 00:00:00')),
          partition p5 values less than (unix_timestamp('2009-04-01 00:00:00')),
          partition p6 values less than (unix_timestamp('2009-07-01 00:00:00')),
          partition p7 values less than (unix_timestamp('2009-10-01 00:00:00')),
          partition p8 values less than (unix_timestamp('2010-01-01 00:00:00')),
          partition p9 values less than maxvalue
    );

    (3)根据DATE、DATETIME范围

    添加COLUMNS关键字可定义非integer范围及多列范围,不过需要注意COLUMNS括号内只能是列名,不支持函数;多列范围时,多列范围必须呈递增趋势:

    drop table if exists member;
    create table member(
      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 columns(joined)(
      partition p0 values less than ('1960-01-01'),
      partition p1 values less than ('1970-01-01'),
      partition p2 values less than ('1980-01-01'),
      partition p3 values less than ('1990-01-01'),
      partition p4 values less than maxvalue
    )

    (4)根据多列范围

    drop table if exists rc3;
    create table rc3(
      a int,
      b int
    )
    partition by range columns(a,b)(
      partition p0 values less than (0,10),
      partition p1 values less than (10,20),
      partition p2 values less than (20,30),
      partition p3 values less than (30,40),
      partition p4 values less than (40,50),
      partition p5 values less than (maxvalue,maxvalue)
    )
    

    (5)RANGE分区在如下场合特别有用

    drop table if exists staff;
    create table staff(
      id int not null,
      fname varchar(30),
      lname varchar(30),
      hired date not null default '1970-01-01',
      separated date not null default '9999-12-31',
      job_code int not null default 0,
      store_id int not null default 0
    )engine=myisam default charset=utf8
    partition by range(year(separated))(
      partition p0 values less than (1991),
      partition p1 values less than (1996),
      partition p2 values less than (2001),
      partition p4 values less than MAXVALUE
    );

    (1)、当需要删除一个分区上的“旧的”数据时,只删除分区即可。如果你使用上面最近的那个例子给出的分区方案,你只需简单地使用”alter table staff drop partition p0;”来删除所有在1991年前就已经停止工作的雇员相对应的所有行。对于有大量行的表,这比运行一个如”delete from staff WHERE year(separated) <= 1990;”这样的一个DELETE查询要有效得多。

    (2)、想要使用一个包含有日期或时间值,或包含有从一些其他级数开始增长的值的列。

    (3)、经常运行直接依赖于用于分割表的列的查询。例如,当执行一个如”select count(*) from staff where year(separated) = 200 group by store_id;”这样的查询时,MySQL可以很迅速地确定只有分区p2需要扫描,这是因为余下的分区不可能包含有符合该WHERE子句的任何记录。

    2、LIST分区

    根据具体数值分区,每个分区数值不重叠,使用PARTITION BY LIST、VALUES IN关键字。跟Range分区类似,不使用COLUMNS关键字时List括号内必须为整数字段名或返回确定整数的函数。

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

    LIST分区通过使用“PARTITION BY LIST(expr)”来实现,其中“expr”是某列值或一个基于某个列值、并返回一个整数值的表达式,然后通过“VALUES IN (value_list)”的方式来定义每个分区,其中“value_list”是一个通过逗号分隔的整数列表。

    假定有20个音像店,分布在4个有经销权的地区,如下表所示:

    ====================
    地区      商店ID 号
    北区      3, 5, 6, 9, 17
    东区      1, 2, 10, 11, 19, 20
    西区      4, 12, 13, 14, 18
    中心区   7, 8, 15, 16
    drop table if exists staff;
    create table staff(
      id int not null,
      fname varchar(30),
      lname varchar(30),
      hired date not null default '1970-01-01',
      separated date not null default '9999-12-31',
      job_code int not null default 0,
      store_id int not null default 0
    )
    partition by list(store_id)(
      partition pNorth values in (3,5,6,9,17),
      partition pEast values in (1,2,10,11,19,20),
      partition pWest values in (4,12,13,14,18),
      partition pCentral values in (7,8,15,16)
    );

    这使得在表中增加或删除指定地区的雇员记录变得容易起来。例如,假定西区的所有音像店都卖给了其他公司。那么与在西区音像店工作雇员相关的所有记录(行)可以使用查询“ALTER TABLE staff DROP PARTITION pWest;”来进行删除,它与具有同样作用的DELETE(删除)“DELETE FROM staff WHERE store_id IN (4,12,13,14,18);”比起来,要有效得多。

    如果试图插入列值(或分区表达式的返回值)不在分区值列表中的一行时,那么“INSERT”查询将失败并报错。

    当插入多条数据出错时,如果表的引擎支持事务(Innodb),则不会插入任何数据;如果不支持事务,则出错前的数据会插入,后面的不会执行。

    与Range分区相同,添加COLUMNS关键字可支持非整数和多列。

    3、HASH分区

     Hash分区主要用来确保数据在预先确定数目的分区中平均分布,Hash括号内只能是整数列或返回确定整数的函数,实际上就是使用返回的整数对分区数取模。

    要使用HASH分区来分割一个表,要在CREATE TABLE 语句上添加一个“PARTITION BY HASH (expr)”子句,其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL整型的一列的名字。此外,你很可能需要在后面再添加一个“PARTITIONS num”子句,其中num是一个非负的整数,它表示表将要被分割成分区的数量。

    如果没有包括一个PARTITIONS子句,那么分区的数量将默认为1

    drop table if exists staff;
    create table staff(
      id int not null,
      fname varchar(30),
      lname varchar(30),
      hired date not null default '1970-01-01',
      separated date not null default '9999-12-31',
      job_code int not null default 0,
      store_id int not null default 0
    )
    partition by hash(store_id)
    partitions 4;
    drop table if exists staff;
    create table staff(
      id int not null,
      fname varchar(30),
      lname varchar(30),
      hired date not null default '1970-01-01',
      separated date not null default '9999-12-31',
      job_code int not null default 0,
      store_id int not null default 0
    )
    partition by hash(year(hired))
    partitions 4;

    Hash分区也存在与传统Hash分表一样的问题,可扩展性差。MySQL也提供了一个类似于一致Hash的分区方法-线性Hash分区,只需要在定义分区时添加LINEAR关键字。

    drop table if exists staff;
    create table staff(
      id int not null,
      fname varchar(30),
      lname varchar(30),
      hired date not null default '1970-01-01',
      separated date not null default '9999-12-31',
      job_code int not null default 0,
      store_id int not null default 0
    )
    partition by linear hash(year(hired))
    partitions 4;

    线性哈希功能,它与常规哈希的区别在于,线性哈希功能使用的一个线性的2的幂(powers-of-two)运算法则,而常规哈希使用的是求哈希函数值的模数。

    4、KEY分区

    Key分区与Hash分区很相似,只是Hash函数不同,定义时把Hash关键字替换成Key即可,同样Key分区也有对应与线性Hash的线性Key分区方法。

    drop table if exists staff;
    create table staff(
      id int not null,
      fname varchar(30),
      lname varchar(30),
      hired date not null default '1970-01-01',
      separated date not null default '9999-12-31',
      job_code int not null default 0,
      store_id int not null default 0
    )
    partition by key(store_id)
    partitions 4;

    在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的作用,分区的编号是通过2的幂(powers-of-two)算法得到,而不是通过模数算法。

    另外,当表存在主键或唯一索引时可省略Key括号内的列名,Mysql将按照主键-唯一索引的顺序选择,当找不到唯一索引时报错。

    展开全文
  • 表分区的具体作用 3.表分区的优缺点 4.表分区的几种类型及操作方法 5.对表分区的维护性操作. 表空间及分区表的概念 表空间:是一个或多个数据文件的集合,所有的数据对象都存放在指定的表空间中,但主要存放的是表,...

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

    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 表分区?涨知识了

    千次阅读 2022-03-27 22:07:57
    什么是表分区2. 分区的两种方式2.1 水平切分2.2 垂直切分3. 为什么需要表分区4. 分区实践4.1 RANGE 分区4.2 LIST 分区4.3 HASH 分区4.4 KEY 分区4.5 COLUMNS 分区5. 常见分区命令6. 小结 松哥之前写过文章跟大家...


    松哥之前写过文章跟大家介绍过用 MyCat 实现 MySQL 的分库分表,不知道有没有小伙伴研究过,MySQL 其实也自带了分区功能,我们可以创建一个带有分区的表,而且不需要借助任何外部工具,今天我们就一起来看看。

    1. 什么是表分区

    小伙伴们知道,MySQL 数据库中的数据是以文件的形势存在磁盘上的,默认放在 /var/lib/mysql/ 目录下面,我们可以通过 show variables like '%datadir%'; 命令来查看:

    我们进入到这个目录下,就可以看到我们定义的所有数据库了,一个数据库就是一个文件夹,一个库中,有其对应的表的信息,如下:

    在 MySQL 中,如果存储引擎是 MyISAM,那么在 data 目录下会看到 3 类文件:.frm.myi.myd,作用如下:

    1. *.frm:这个是表定义,是描述表结构的文件。
    2. *.myd:这个是数据信息文件,是表的数据文件。
    3. *.myi:这个是索引信息文件。

    如果存储引擎是 InnoDB, 那么在 data 目录下会看到两类文件:.frm.ibd,作用分别如下:

    1. *.frm:表结构文件。
    2. *.ibd:表数据和索引的文件。

    无论是哪种存储引擎,只要一张表的数据量过大,就会导致 *.myd*.myi 以及 *.ibd 文件过大,数据的查找就会变的很慢。

    为了解决这个问题,我们可以利用 MySQL 的分区功能,在物理上将这一张表对应的文件,分割成许多小块,如此,当我们查找一条数据时,就不用在某一个文件中进行整个遍历了,我们只需要知道这条数据位于哪一个数据块,然后在那一个数据块上查找就行了;另一方面,如果一张表的数据量太大,可能一个磁盘放不下,这个时候,通过表分区我们就可以把数据分配到不同的磁盘里面去。

    MySQL 从 5.1 开始添加了对分区的支持,分区的过程是将一个表或索引分解为多个更小、更可管理的部分。对于开发者而言,分区后的表使用方式和不分区基本上还是一模一样,只不过在物理存储上,原本该表只有一个数据文件,现在变成了多个,每个分区都是独立的对象,可以独自处理,也可以作为一个更大对象的一部分进行处理。

    需要注意的是,分区功能并不是在存储引擎层完成的,常见的存储引擎如 InnoDBMyISAMNDB 等都支持分区。但并不是所有的存储引擎都支持,如 CSVFEDORATEDMERGE 等就不支持分区,因此在使用此分区功能前,应该对选择的存储引擎对分区的支持有所了解。

    2. 分区的两种方式

    不同于 MyCat 中既可以垂直切分又可以水平切分,MySQL 数据库支持的分区类型为水平分区,它不支持垂直分区。

    2.1 水平切分

    先来一张简单的示意图,大家感受一下什么是水平切分:

    假设我的 DB 中有 table-1、table-2 以及 table-3 三张表,水平切分就是拿着我 40 米大刀,对准黑色的线条,砍一剑或者砍 N 剑!

    砍完之后,将砍掉的部分放到另外一个数据库实例中,变成下面这样:


    这样,原本放在一个 DB 中的 table 现在放在两个 DB 中了,观察之后我们发现:

    1. 两个 DB 中表的个数都是完整的,就是原来 DB 中有几张表,现在还是几张。
    2. 每张表中的数据是不完整的,数据被拆分到了不同的 DB 中去了。

    这就是数据库的水平切分,也可以理解为按照数据行进行切分,即按照表中某个字段的某种规则来将表数据分散到多个库之中,每个表中包含一部分数据,即水平切分不改变表结构。

    2.2 垂直切分

    先来一张简单的示意图,大家感受一下垂直切分:

    所谓的垂直切分就是拿着我 40 米大刀,对准了黑色的线条砍。砍完之后,将不同的表放到不同的数据库实例中去,变成下面这个样子:



    这个时候我们发现如下几个特点:

    1. 每一个数据库实例中的表的数量都是不完整的。
    2. 每一个数据库实例中表的数据是完整的。

    这就是垂直切分。一般来说,垂直切分我们可以按照业务来划分,不同业务的表放到不同的数据库实例中。

    MySQL 数据库支持的分区类型为水平分区。

    此外,MySQL 数据库的分区是局部分区索引,即一个分区中既存放了数据又存放了索引,目前,MySQL数据库还不支持全局分区(数据存放在各个分区中,但是所有数据的索引放在一个对象中)。

    3. 为什么需要表分区

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

    分区的限制和缺点:

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

    4. 分区实践

    说了这么多,来个例子看一下。

    首先我们先来查看一下当前的 MySQL 是否支持分区。

    在 MySQL5.6.1 之前可以通过命令 show variables like '%have_partitioning%' 来查看 MySQL 是否支持分区。如果 have_partitioning 的值为 YES,则表示支持分区。

    从 MySQL5.6.1 开始,have_partitioning 参数已经被去掉了,而是用 SHOW PLUGINS 来代替。若有 partition 行且 STATUS 列的值为 ACTIVE,则表示支持分区,如下所示:

    确认我们的 MySQL 支持分区后,我们就可以开始分区啦!

    接下来我们来看几种不同的分区策略。

    4.1 RANGE 分区

    RANGE 分区比较简单,就是根据某一个字段的值进行分区。不过这个字段有一个要求,就是必须是主键或者是联合主键中的某个字段。

    例如根据 user 表的 id 进行分区:

    1. 当 id 小于 100,数据插入 p0 分区;
    2. 当 id 大于等于 100 小于 200 的时候,插入 p1 分区;
    3. 如果 id 大于等于 200 则插入 p2 分区。

    上面的规则涉及到了 id 的所有范围了,如果没有第三条规则,那么插入一个 id 为 300 的记录时,就会报错。

    建表 SQL 如下:

    create  table  user(
      id int primary key,
      username varchar(255)
    )engine=innodb
      partition by range(id)(
         partition  p0  values  less  than(100),
         partition  p1  values  less  than(200),
         partition  p2  values  less  than maxvalue  
    );
    

    表创建成功后,我们进入到 /var/lib/mysql/test08 文件夹中,来看刚刚创建的表文件:

    可以看到,此时的数据文件分为好几个了。

    information_schema.partitions 表中,我们可以查看分区的详细信息:

    也可以自己写个 SQL 去查询:

    select * from information_schema.partitions where table_schema='test08' and table_name='user'\G
    

    每一行展示一个分区的信息,包括分区的方式、该区的范围、分区的字段、该区目前有几条记录等等。

    RANGE 分区有一个比较典型的使用场景,就是按照日期对表进行分区,例如同一年注册的用户放在一个分区中,如下:

    create  table  user(
      id int,
      username varchar(255),
      password varchar(255),
      createDate date,
      primary key (id,createDate)
    )engine=innodb
      partition by range(year(createDate))(
         partition  p2022  values  less  than(2023),
         partition  p2023  values  less  than(2024),
         partition  p2024  values  less  than(2025)  
    );
    

    **注意,createDate 是联合主键的一员。**如果 createDate 不是主键,只是一个普通字段,那么创建时就会抛出如下错误:

    现在,如果我们要查询 2022 年注册的用户,系统就只会去搜索 p2022 这个分区,通过 explain 执行计划可以证实我们的想法:

    如果想要删除 2022 年注册的用户,则只需要删除该分区即可:

    alter table user drop partition p2022;
    

    由上图可以看到,删除之后,数据就没了。

    4.2 LIST 分区

    LIST 分区和 RANGE 分区类似,区别在于 LIST 分区是基于列值匹配一个离散值集合中的某个值来进行选择,而非连续的。举个例子大家看下就明白了:

    假设我有一个用户表,用户有性别,现在想按照性别将用户分开存储,男性存储在一个分区中,女性存储在一个分区中,SQL 如下:

    create  table  user(
      id int,
      username varchar(255),
      password varchar(255),
      gender int,
      primary key(id, gender)
    )engine=innodb
      partition by list(gender)(
         partition  man  values  in  (1),
         partition  woman  values  in  (0));
    

    这个表将来就两个分区,分别存储男性和女性,gender 的取值为 1 或者 0,gender 如果取其他值,执行就会出错,最终执行结果如下:

    这样分区之后,将来查询男性或者查询女性效率都会比较高,删除某一性别的用户时删除效率也高。

    4.3 HASH 分区

    HASH 分区的目的是将数据均匀地分布到预先定义的各个分区中,保证各分区的数据量大致都是一样的。在 RANGE 和 LIST 分区中,必须明确指定一个给定的列值或列值集合应该保存在哪个分区中;而在 HASH 分区中,MySQL 自动完成这些工作,用户所要做的只是基于将要进行哈希分区的列指定一个表达式,并且分区的数量。

    使用 HASH 分区来分割一个表,要在 CREATE TABLE 语句上添加 PARTITION BY HASH (expr),其中 expr 是一个字段或者是一个返回整数的表达式;另外通过 PARTITIONS 属性指定分区的数量,如果没有指定,那么分区的数量默认为 1,另外,HASH 分区不能删除分区,所以不能使用 DROP PARTITION 操作进行分区删除操作。

    create  table  user(
      id int,
      username varchar(255),
      password varchar(255),
      gender int,
      primary key(id, gender)
    )engine=innodb partition by hash(id) partitions 4;
    

    4.4 KEY 分区

    KEY 分区和 HASH 分区相似,但是 KEY 分区支持除 text 和 BLOB 之外的所有数据类型的分区,而 HASH 分区只支持数字分区。

    KEY 分区不允许使用用户自定义的表达式进行分区,KEY 分区使用系统提供的 HASH 函数进行分区。

    当表中存在主键或者唯一索引时,如果创建 KEY 分区时没有指定字段系统默认会首选主键列作为分区字段,如果不存在主键列会选择非空唯一索引列作为分区字段。

    举个例子:

    create  table  user(
      id int,
      username varchar(255),
      password varchar(255),
      gender int,
      primary key(id, gender)
    )engine=innodb partition by key(id) partitions 4;
    

    4.5 COLUMNS 分区

    COLUMN 分区是 5.5 开始引入的分区功能,只有 RANGE COLUMN 和 LIST COLUMN 这两种分区;支持整形、日期、字符串;这种分区方式和 RANGE、LIST 的分区方式非常的相似。

    COLUMNS Vs RANGE Vs LIST 分区:

    1. 针对日期字段的分区不需要再使用函数进行转换了。
    2. COLUMN 分区支持多个字段作为分区键但是不支持表达式作为分区键。

    COLUMNS 支持的类型

    • 整形支持:tinyint、smallint、mediumint、int、bigint;不支持 decimal 和 float。
    • 时间类型支持:date、datetime。
    • 字符类型支持:char、varchar、binary、varbinary;不支持text、blob。

    举个例子看下:

    create  table  user(
      id int,
      username varchar(255),
      password varchar(255),
      gender int,
      createDate date,
      primary key(id, createDate)
    )engine=innodb PARTITION BY RANGE COLUMNS(createDate) (
        PARTITION p0 VALUES LESS THAN ('1990-01-01'),
        PARTITION p1 VALUES LESS THAN ('2000-01-01'),
        PARTITION p2 VALUES LESS THAN ('2010-01-01'),
        PARTITION p3 VALUES LESS THAN ('2020-01-01'),
        PARTITION p4 VALUES LESS THAN MAXVALUE
    );
    

    这是 RANGE COLUMNS,分区值是连续的。

    再来看 LIST COLUMNS 分区,这个就类似于枚举了:

    create  table  user(
      id int,
      username varchar(255),
      password varchar(255),
      gender int,
      createDate date,
      primary key(id, createDate)
    )engine=innodb PARTITION BY LIST COLUMNS(createDate) (
        PARTITION p0 VALUES IN ('1990-01-01'),
        PARTITION p1 VALUES IN ('2000-01-01'),
        PARTITION p2 VALUES IN ('2010-01-01'),
        PARTITION p3 VALUES IN ('2020-01-01')
    );
    

    5. 常见分区命令

    1. 添加分区:
    alter table user add partition (partition p3 values less than (4000)); -- range 分区
    
    alter table user add partition (partition p3 values in (40));  -- lists分区
    
    1. 删除表分区(会删除数据):
    alter table user drop partition p30;
    
    1. 删除表的所有分区(不会丢失数据):
    alter table user remove partitioning; 
    
    1. 重新定义 range 分区表(不会丢失数据):
    alter table user partition by range(salary)(
    partition p1 values less than (2000),
    partition p2 values less than (4000)); 
    
    1. 重新定义 hash 分区表(不会丢失数据):
    alter table user partition by hash(salary) partitions 7; 
    
    1. 合并分区:把 2 个分区合并为一个,不会丢失数据:
    alter table user  reorganize partition p1,p2 into (partition p1 values less than (1000));
    

    6. 小结

    不知道小伙伴们是否还记得松哥 2019 年写的 MyCat 教程(公众号江南一点雨后台回复 2019 有文章索引),这些分区策略是不是和 MyCat 中的策略非常相似呀?感兴趣的小伙伴赶紧去试一把吧~

    参考资料:

    https://www.cnblogs.com/dw3306/p/12620O42.html

    展开全文
  • Oracle 表分区详解(partition table)

    万次阅读 多人点赞 2020-10-27 22:33:26
    文章目录1 概述1.1 思维导图2 分类2.1 传统表分区2.1.1 范围分区 range2.1.2 列表分区 list2.1.3 哈希分区 hash2.1.4 复合分区 range + list or hash2.2 11g 新特性分区2.1.1 引用分区 reference2.1.2 间隔分区 ...
  • 加速数据库的方法很多,如添加特定的索引,将日志目录换到单独的磁盘分区,调整数据库引擎的参数等。这些方法都能将数据库的查询性能提高到一定程度。 对于许多应用数据库来说,许多数据是历史数据并且随着时间的...
  • mysql表分区(partition)

    千次阅读 2022-03-14 09:13:07
    比如我们可以让用户有权去访问某个视图,但是不能访问原,这样就可以起到保护原中某些数据的作用。 3.我们之后会接触到管理权限,权限是无法细致到某一个列的,通过视图,则很容易实现。 4.第三个显著优点就是...
  • 表分区

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

    千次阅读 2021-01-18 18:54:46
    1.测试添加分区和删除分区###添加删除range分区(1)创建一个分区:CREATETABLEtitles(emp_noINTNOTNULL,titleVARCHAR(50)NOTNULL,from_dateDATENOTNULL,to_dateDATE,KEY(emp_no),PRIMA...
  • Oracle增加表分区

    千次阅读 2022-04-01 14:28:19
    Oracle增加空间 alter table DSGC_LOG_BODY_PAYLOAD add partition P_202204 values less than (TO_DATE('2022-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace DSGC_LOG_REC_...
  • MySQL的表分区详解

    千次阅读 2021-01-28 05:54:06
    一、什么是表分区通俗地讲表分区是将一大表,根据条件分割成若干个小表。mysql5.1开始支持数据表分区了。如:某用户表的记录超过了600万条,那么就可以根据入库日期将表分区,也可以根据所在地将表分区。当然也可...
  • mysql表分区

    千次阅读 2021-07-20 16:36:32
    一、MySQL分区简介 ...这种形式的分区是对根据的行进行分区,通过这样的方式不同分组里面的物理列分割的数据集得以组合,从而进行个体分割(单分区)或集体分割(1个或多个分区)。 所有在中定义
  • TiDB 表分区

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

    千次阅读 2019-07-29 14:04:58
    从以下几个方面来整理关于分区... 表分区的具体作用 表分区的优缺点 表分区的几种类型及操作方法 对表分区的维护性操作 1.表空间及分区表的概念 表空间:  是一个或多个数据文件的集合,所有的数据对象都存放...
  • clickhouse中如何查询分区表分区及表容量查询

    万次阅读 多人点赞 2021-04-26 11:54:27
    clickhouse中如何查询分区表分区及表容量–这个问题相信大家在使用clickhouse分区表时都会经常遇到,其实clickhouse表的元数据信息都存储在system.parts表中,可以通过该表对clickhouse上所有表进行查询表容量大小、...
  • mysql 表分区 查看表分区 修改表分区

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

    千次阅读 2022-02-24 16:33:22
    遂采用表分区的方式来进行优化 分区基本介绍: Mysql存储数据的时候,对于InnoDB存储引擎来说,一般存储了:.frm 表定义文件 .rdb:数据文件 两种文件格式。 但是当表的数据文件太大的时候,我们可以对表进行...
  • 表分区含义和使用场景1.1 作用和含义1.2 传统表分区的类型1.2 .1 范围分区1.2 .2 散列分区1.2 .3 列表分区1.2 .4 复合分区1.3 11g新增分区1.3 .1 引用分区 一.表分区含义和使用场景 1.1 作用和含义 允许用户将表分成...
  • mysql 表分区的几种方式: RANGE分区:基于属于一个给定连续区间的列值,把多行分配给分区。 LIST分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。 HASH分区:基于...
  • Oracle表分区(12.2 automatic 表自动分区)

    千次阅读 2020-08-21 09:58:33
    查看分区数据: select * from test partition(p1) 在Oracle Database 12.2 之前,如果使用列表分区,当插入的数据超过了分区列表值设定,则会抛出异常;而如果存在大量的列表值需要定义,则可能需要一一设置。 在...
  • MYSQL数据库中表的进阶玩法——表分区

    万次阅读 多人点赞 2022-02-16 15:35:00
    什么是表分区表分区的有点有哪些?怎么创建表的分区?
  • Mysql按天自动表分区教程

    千次阅读 2019-10-11 10:24:37
    Mysql按天自动表分区教程前言什么是表分区?创建测试数据库创建测试表手动进行分区批量进行分区单条增加分区删除分区命令插入数据查看表分区创建增加分区和删除分区代码手动调用存储程序命令开启事件新建event事件 ...
  • mysql 分区查询(mysql表分区查询)

    千次阅读 2021-01-18 18:28:54
    mysql 分区查询(mysql表分区查询)2020-07-24 11:39:07共10个回答可以看mysql的data文件夹下面的数据库文件,就可以查看当前分区情况.还有几种获取mysql分区表信息的常用方法showcreatetable可以查看创建分区表的...
  • MySql表分区的创建与使用

    万次阅读 2019-11-20 13:43:35
    一、创建表分区 MySql默认是支持表分区的,可以通过语句查询是否开启表分区功能:show plugins ; 创建表分区只需要在创建表的语句后面加上分区语句就可以,例如: create table user(id int(11) not null...
  • Mysql表分区状态查询

    万次阅读 2021-01-20 14:44:41
    一、查询mysql是否为分区表:可以查看具有哪几个分区分区的方法、分区中数据的记录数等信息SELECT PARTITION_NAME,PARTITION_METHOD,PARTITION_EXPRESSION,PARTITION_DESCRIPTION,TABLE_ROWS,SUBPARTITION_...
  • SQL SERVER 表分区技术

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

    千次阅读 2017-11-15 15:50:46
    介绍了关于表分区和表空间的基本内容。
  • 分表和表分区

    万次阅读 2018-05-15 11:03:00
    在我看来分表和表分区在物理结构上都是一样的,只不过在逻辑结构上不同,分表在逻辑上是多张表,多张实体表,每个表都对应三个文件,MYD数据文件,.MYI索引文件,.frm表结构文件。分区表在逻辑上是一张表,而数据...
  • 表分区优点

    千次阅读 2020-10-16 21:04:08
    1、存储更多数据 2、优化查询 3、分区表更容易维护 4、避免某些特殊的瓶颈
  • Hive的基本操作之表分区

    千次阅读 2022-04-28 20:05:47
    由于hive在查询时会做全表扫描,有些情况下我们只需要查询部分数据,为了避免全表扫描消耗资源和性能,我们可以实现表分区使之扫描部分表。 在hive中分区字段是一个伪字段,并不实际存储数据,可以作为条件用于查询...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 376,923
精华内容 150,769
关键字:

表分区