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

    2021-01-18 19:03:09
    编辑推荐:本文来自于csdn,本文介绍使用表分区有个前提就是你的数据库必须支持。那么,怎么知道我的数据库是否支持表分区呢?请看下面详解。当数据库数据量涨到一定数量时,性能就成为我们不能不关注的问题,如何...

    编辑推荐:

    本文来自于csdn,本文介绍使用表分区有个前提就是你的数据库必须支持。那么,怎么知道我的数据库是否支持表分区呢?请看下面详解。

    当数据库数据量涨到一定数量时,性能就成为我们不能不关注的问题,如何优化呢?

    常用的方式不外乎那么几种:

    1、分表,即把一个很大的表达数据分到几个表中,这样每个表数据都不多。

    优点:提高并发量,减小锁的粒度

    缺点:代码维护成本高,相关sql都需要改动

    2、分区,所有的数据还在一个表中,但物理存储数据根据一定的规则存放在不同的文件中,文件也可以放到另外磁盘上

    优点:代码维护量小,基本不用改动,提高IO吞吐量

    缺点:表的并发程度没有增加

    3、拆分业务,这个本质还是分表。

    优点:长期支持更好

    缺点:代码逻辑重构,工作量很大

    当然,每种情况都有合适的应用场景,需要根据具体业务具体选择。由于分表和拆分业务和mysql本身关系不大属于业务层面,我们只说和数据库关系最紧密的方式:表分区。不过使用表分区有个前提就是你的数据库必须支持。那么,怎么知道我的数据库是否支持表分区呢

    ? 请执行下面命令

    a6db4330c55f33791001f727e71408e0.png

    据说5.4一下的版本是另外一个命令,不过我没有测试

    show variables

    like '%part%';

    数据库的表分区一般有两种方式:纵向和横向。纵向就是把表中不同字段分到不同数据文件中。横向是把表中前一部分数据放到一个文件中,另一部分数据放到一个文件中。mysql只支持后后一种方式,横向拆分。

    1、创建分区表

    如果要使用表的分区优势,不但要数据库版本支持分区,关键要建分区表,这个表和普通表不一样,并且必须建表的时候就要指定分区,否则无法把普通表改成分区表。那么,如果创建一个分区表呢?

    其他很简单,请看下面建表语句

    1 CREATE TABLE

    `T_part` (

    2 `f_id` INT DEFAULT NULL,

    3 `f_name` VARCHAR (20) DEFAULT NULL,

    4 PRIMARY KEY (`f_id`)

    5 ) ENGINE = myisam DEFAULT CHARSET = utf8

    6 PARTITION BY RANGE (f_id)( -----指定分区方式

    7 PARTITION p0 VALUES less THAN (10),-- 分了两个区

    8 PARTITION p1 VALUES less THAN (20)

    9 )

    上面语句建了一个“T_part”表,有两个字段f_id和f_name,并且根据RANGE方式把表分成两个区p0、p1,当f_id小于10放入p0分区,当f_id大于0小于20放入分区p1.

    那么当f_id大于20的数据放入哪个分区呢? 你猜对了,insert语句会报错。

    看到了吧,创建分区表就这么简单!当然,你随时可以添加删除分区,不过要注意,删除分区的时候会把当前分区下所有数据都删除。

    alter table T_part

    add partition(partition p2 values less than (MAXVALUE));

    ---新增分区

    alter table T_part DROP partition p2; ----删除分区

    2、表分区的几种方式

    mysql支持5种分区方式:RANGE分区、LIST分区、HASH分区、LINEAR HASH分区和KEY分区。每种分区都有自己的使用场景。

    1)RANGE分区:

    RANGE分区的表是通过如下一种方式进行分区的,每个分区包含那些分区表达式的值位于一个给定的连续区间内的行。这些区间要连续且不能相互重叠,使用VALUES

    LESS THAN操作符来进行定义。

    上面的例子就是RANGE分区.

    2)LIST分区:

    MySQL中的LIST分区在很多方面类似于RANGE分区。和按照RANGE分区一样,每个分区必须明确定义。它们的主要区别在于,LIST分区中每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值,而RANGE分区是从属于一个连续区间值的集合。LIST分区通过使用“PARTITION

    BY LIST(expr)”来实现,其中“expr” 是某列值或一个基于某个列值、并返回一个整数值的表达式,然后通过“VALUES

    IN (value_list)”的方式来定义每个分区,其中“value_list”是一个通过逗号分隔的整数列表。

    CREATE TABLE

    `T_list` (

    `f_id` INT DEFAULT NULL,

    `f_name` VARCHAR (20) DEFAULT NULL,

    PRIMARY KEY (`f_id`)

    ) ENGINE = myisam DEFAULT CHARSET = utf8

    PARTITION by list(f_id)

    (

    PARTITION p0 VALUES in(1,2,3), ----区间值不能重复

    PARTITION p1 VALUES in(4,5,6)

    );

    3)HASH分区:

    HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中,必须明确指定一个给定的列值或列值集合应该保存在哪个分区中;而在HASH分区中,MySQL

    自动完成这些工作,你所要做的只是基于将要被哈希的列值指定一个列值或表达式,以及指定被分区的表将要被分割成的分区数量。要使用HASH分区来分割一个表,要在CREATE

    TABLE 语句上添加一个“PARTITION BY HASH (expr)”子句,其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL

    整型的一列的名字。此外,你很可能需要在后面再添加一个“PARTITIONS num”子句,其中num

    是一个非负的整数,它表示表将要被分割成分区的数量。

    CREATE TABLE

    `T_hash` (

    `f_id` INT DEFAULT NULL,

    `f_name` VARCHAR (20) DEFAULT NULL,

    PRIMARY KEY (`f_id`)

    ) ENGINE = myisam DEFAULT CHARSET = utf8

    PARTITION BY HASH(f_id) ---可以指定多列

    PARTITIONS 4;---分区个数

    “expr”还可以是MySQL 中有效的任何函数或其他表达式,只要它们返回一个既非常数、也非随机数的整数。(换句话说,它既是变化的但又是确定的)。但是应当记住,每当插入或更新(或者可能删除)一行,这个表达式都要计算一次;这意味着非常复杂的表达式可能会引起性能问题,尤其是在执行同时影响大量行的运算(例如批量插入)的时候。最有效率的哈希函数是只对单个表列进行计算,并且它的值随列值进行一致地增大或减小,因为这考虑了在分区范围上的“修剪”。也就是说,表达式值和它所基于的列的值变化越接近,MySQL就可以越有效地使用该表达式来进行HASH分区。

    4)LINEAR HASH分区:

    MySQL还支持线性哈希功能,它与常规哈希的区别在于,线性哈希功能使用的一个线性的2的幂(powers-oftwo)运算法则,而常规

    哈希使用的是求哈希函数值的模数。线性哈希分区和常规哈希分区在语法上的唯一区别在于,在“PARTITION

    BY” 子句中添加“LINEAR”关键字.

    5)KEY分区:

    按照KEY进行分区类似于按照HASH分区,除了HASH分区使用的用户定义的表达式,而KEY分区的

    哈希函数是由MySQL 服务器提供。MySQL 簇(Cluster)使用函数MD5()来实现KEY分区;对于使用其他存储引擎的表,服务器使用其自己内部的

    哈希函数,这些函数是基于与PASSWORD()一样的运算法则。

    KEY分区的语法和HASH语法类似,只是把关键字改成KEY。

    CREATE TABLE

    `T_key` (

    `f_id` INT DEFAULT NULL,

    `f_name` VARCHAR (20) DEFAULT NULL,

    PRIMARY KEY (`f_id`)

    ) ENGINE = myisam DEFAULT CHARSET = utf8

    PARTITION BY LINEAR key(f_id)

    PARTITIONS 3;

    6)子分区:

    子分区的意思就是在分区的基础上再次分区。且每个分区必须有相同个数的子分区。

    CREATE TABLE

    `T_part` (

    `f_id` INT DEFAULT NULL,

    `f_name` VARCHAR (20) DEFAULT NULL,

    PRIMARY KEY (`f_id`)

    )

    PARTITION BY RANGE (f_id)

    SUBPARTITION BY HASH(F_ID)

    SUBPARTITIONS 2

    (

    PARTITION p0 VALUES less THAN (10),

    PARTITION p1 VALUES less THAN (20)

    )

    上面语句的意思是,建立两个range分区,每个分区根据hash有分别有两个子分区,实际上整个表分成2×2=4个分区。当然,要详细定义每个分区属性也是可以的

    CREATE TABLE

    `T_part` (

    `f_id` INT DEFAULT NULL,

    `f_name` VARCHAR (20) DEFAULT NULL,

    PRIMARY KEY (`f_id`)

    )

    PARTITION BY RANGE (f_id)

    SUBPARTITION BY HASH(F_ID)

    (

    PARTITION p0 VALUES less THAN (10)

    (

    SUBPARTITION s0

    DATA DIRECTORY = '/disk0/data'

    INDEX DIRECTORY = '/disk0/idx',

    SUBPARTITION s1

    DATA DIRECTORY = '/disk1/data'

    INDEX DIRECTORY = '/disk1/idx'

    ),

    PARTITION p1 VALUES less THAN (20)

    (

    SUBPARTITION s2

    DATA DIRECTORY = '/disk0/data'

    INDEX DIRECTORY = '/disk0/idx',

    SUBPARTITION s3

    DATA DIRECTORY = '/disk1/data'

    INDEX DIRECTORY = '/disk1/idx'

    )

    )

    这样可以对每个分区指定具体存储磁盘。前提磁盘是存在的。

    MySQL 中的分区在禁止空值(NULL)上没有进行处理,无论它是一个列值还是一个用户定义表达式的值。一般而言,在这种情况下MySQL

    把NULL视为0。如果你希望回避这种做法,你应该在设计表时不允许空值;最可能的方法是,通过声明列“NOT

    NULL”来实现这一点。

    展开全文
  • 1.分区表的原理分区表是由多个相关的底层表实现,这些底层表也是由句柄对象表示,所以我们也可以直接访问各个分区,存储引擎管理分区的各个底层表和管理普通表一样(所有的底层表都必须使用相同的存储引擎),分区表的...

    1.分区表的原理

    分区表是由多个相关的底层表实现,这些底层表也是由句柄对象表示,所以我们也可以直接访问各个分区,存储引擎管理分区的各个底层表和管理普通表一样(所有的底层表都必须使用相同的存储引擎),分区表的索引只是在各个底层表上各自加上一个相同的索引,从存储引擎的角度来看,底层表和一个普通表没有任何不同,存储引擎也无须知道这是一个普通表还是一个分区表的一部分。

    在分区表上的操作按照下面的操作逻辑进行:

    select查询:

    当查询一个分区表的时候,分区层先打开并锁住所有的底层表,优化器判断是否可以过滤部分分区,然后再调用对应的存储引擎接口访问各个分区的数据

    insert操作:

    当写入一条记录时,分区层打开并锁住所有的底层表,然后确定哪个分区接受这条记录,再将记录写入对应的底层表

    delete操作:

    当删除一条记录时,分区层先打开并锁住所有的底层表,然后确定数据对应的分区,最后对相应底层表进行删除操作

    update操作:

    当更新一条数据时,分区层先打开并锁住所有的底层表,mysql先确定需要更新的记录在哪个分区,然后取出数据并更新,再判断更新后的数据应该放在哪个分区,然后对底层表进行写入操作,并对原数据所在的底层表进行删除操作

    虽然每个操作都会打开并锁住所有的底层表,但这并不是说分区表在处理过程中是锁住全表的,如果存储引擎能够自己实现行级锁,如:innodb,则会在分区层释放对应的表锁,这个加锁和解锁过程与普通Innodb上的查询类似。

    2.在下面的场景中,分区可以起到非常大的作用:

    A:表非常大以至于无法全部都放在内存中,或者只在表的最后部分有热点数据,其他都是历史数据

    B:分区表的数据更容易维护,如:想批量删除大量数据可以使用清除整个分区的方式。另外,还可以对一个独立分区进行优化、检查、修复等操作

    C:分区表的数据可以分布在不同的物理设备上,从而高效地利用多个硬件设备

    D:可以使用分区表来避免某些特殊的瓶颈,如:innodb的单个索引的互斥访问,ext3文件系统的inode锁竞争等

    E:如果需要,还可以备份和恢复独立的分区,这在非常大的数据集的场景下效果非常好

    F:优化查询,在where字句中包含分区列时,可以只使用必要的分区来提高查询效率,同时在涉及sum()和count()这类聚合函数的查询时,可以在每个分区上面并行处理,最终只需要汇总所有分区得到的结果。

    3.分区本身也有一些限制:

    A:一个表最多只能有1024个分区(mysql5.6之后支持8192个分区)

    B:在mysql5.1中分区表达式必须是整数,或者是返回整数的表达式,在5.5之后,某些场景可以直接使用字符串列和日期类型列来进行分区(使用varchar字符串类型列时,一般还是字符串的日期作为分区)。

    C:如果分区字段中有主键或者唯一索引列,那么所有主键列和唯一索引列都必须包含进来,如果表中有主键或唯一索引,那么分区键必须是主键或唯一索引

    D:分区表中无法使用外键约束

    E:mysql数据库支持的分区类型为水平分区,并不支持垂直分区,因此,mysql数据库的分区中索引是局部分区索引,一个分区中既存放了数据又存放了索引,而全局分区是指的数据库放在各个分区中,但是所有的数据的索引放在另外一个对象中

    F:目前mysql不支持空间类型和临时表类型进行分区。不支持全文索引

    4.子分区的建立需要注意以下几个问题:

    A:每个子分区的数量必须相同

    B:只要在一个分区表的任何分区上使用subpartition来明确定义任何子分区,就必须在所有分区上定义子分区,不能漏掉一些分区不进行子分区。

    C:每个subpartition子句必须包括子分区的一个名字

    D:子分区的名字必须是唯一的,不能在一张表中出现重名的子分区

    E:mysql数据库的分区总是把null当作比任何非null更小的值,这和数据库中处理null值的order by操作是一样的,升序排序时null总是在最前面,因此对于不同的分区类型,mysql数据库对于null的处理也各不相同。对于range分区,如果向分区列插入了null,则mysql数据库会将该值放入最左边的分区,注意,如果删除分区,分区下的所有内容都从磁盘中删掉了,null所在分区被删除,null值也就跟着被删除了。在list分区下要使用null,则必须显式地定义在分区的散列值中,否则插入null时会报错。hash和key分区对于null的处理方式和range,list分区不一样,任何分区函数都会将null返回为0.

    展开全文
  • 分区表,顾名思义。就是将一张大表,按照一定的规则分散成更小型的更容易管理的小片; 每一个小片,对于存储来说,就对应一个段;每个小片,也就是一个分区; 分区表的特点: 每个分区,拥有相同的逻辑结构; 每个...

    分区表,顾名思义。就是将一张大表,按照一定的规则分散成更小型的更容易管理的小片; 每一个小片,对于存储来说,就对应一个段;每个小片,也就是一个分区; 分区表的特点: 每个分区,拥有相同的逻辑结构; 每个分区,拥有不同的物理属性; 每个分区,可以

    分区表,顾名思义。就是将一张大表,按照一定的规则分散成更小型的更容易管理的小片;

    每一个小片,对于存储来说,就对应一个段;每个小片,也就是一个分区;

    分区表的特点:

    每个分区,拥有相同的逻辑结构;

    每个分区,拥有不同的物理属性;

    每个分区,可以放置到不同的表空间上去;

    oracle10g的分区表的分区规则:5种

    1.range

    2.list

    3.hash

    4.range list

    5.range hash

    这里只说明这几种分区表的创建,至于什么时候使用,后续分析

    1、RANGE PARTITION

    创建:

    分区表创建之前,为了后续方便管理数据,建议分区表的各个分区创建在不同的表空间中,如下:

    sql>create table obj_range

    2 (object_id number,

    3 object_name varchar2(128),

    4 created date,

    5 object_type varchar2(19)

    6 )

    7 partition by range(object_id)

    8 (

    9 partition range_p1 values less than(3000) tablespace part1,

    10 partition range_p2 values less than(6000) tablespace part2,

    11 partition range_p3 values less than(maxvalue) tablespace part3

    12 );

    Table created.

    所以创建这个分区表之前你得创建 part1, part2, part3三个表空间来存储分区表各个分区!这里需要注意一下,建立范围分区的分区表时,一定需要有maxvalue值,否则数据溢出会报错!

    插入数据查询数据的方法和普通表一样,如

    sql>insert into obj_range

    2 select object_id,object_name,created,object_type

    3 from obj where object_id < 10000;

    9580 rows created.

    sql>select count(*) from obj_range partition (range_p1);

    COUNT(*)

    ----------

    2953

    2、 LIST PARTITION

    sql>create table obj_list

    2 (object_id number,

    3 object_name varchar2(128),

    4 created date,

    5 object_type varchar2(19)

    6 )

    7 partition by list(object_type)

    8 (

    9 partition list_p1 values('TABLE','VIEW') tablespace part1,

    10 partition list_p2 values('SEQUENCE','SYNONYM') tablespace part2,

    11 partition list_p3 values(default) tablespace part3

    12 );

    Table created.

    sql> insert into obj_list

    2 select object_id,object_name,created,object_type

    3 from obj where object_id < 10000;

    9580 rows created.

    PU@orcl10g> commit;

    Commit complete.

    sql> select count(*) from obj_list partition (list_p1);

    COUNT(*)

    ----------

    3734

    3、HASH PARTITION

    sql>

    1 create table obj_hash

    2 (object_id number,

    3 object_name varchar2(128),

    4 created date,

    5 object_type varchar2(19)

    6 )

    7 partition by hash(object_name)

    8 (

    9 partition hash_p1 tablespace part1,

    10 partition hash_p2 tablespace part2,

    11 partition hash_p3 tablespace part3

    12* )

    sql> /

    Table created.

    sql> insert into obj_hash select object_id,object_name,created,object_type from all_objects where rownum <=10000;

    10000 rows created.

    SQL> commit;

    Commit complete.

    sql> select count(*) from obj_hash partition(hash_p1);

    COUNT(*)

    ----------

    2430

    后面还有2中是前面三种的符合分区,创建比较复杂,实际生产库中可能也比较少用!

    4、RANGE、LIST 复合分区

    range list分区规则,首先按照range进行分区,然后,每个range里面再作嵌套list分区:

    sql> create table obj_range_list

    2 (

    3 object_id number,

    4 object_name varchar2(128),

    5 created date,

    6 object_type varchar2(19)

    7 )

    8 partition by range(object_id) subpartition by list(object_type)

    9 (

    10 partition range_list_p1 values less than(3000)

    11 (

    12 subpartition rlp_sub1 values('TABLE','VIEW') tablespace part1,

    13 subpartition rlp_sub2 values('SEQUENCE','SYNONYM') tablespace part2,

    14 subpartition rlp_sub3 values(default) tablespace part3

    15 ),

    16 partition range_list_p2 values less than(6000)

    17 (

    18 subpartition rlp2_sub1 values('TABLE','VIEW') tablespace part1,

    19 subpartition rlp2_sub2 values('SEQUENCE','SYNONYM') tablespace part2,

    20 subpartition rlp2_sub3 values(default) tablespace part3

    21 ),

    22 partition range_list_p3 values less than(maxvalue)

    23 (

    24 subpartition rlp3_sub1 values('TABLE','SYNONYM') tablespace part1,

    25 subpartition rlp3_sub2 values(default) tablespace part2

    26 )

    27* )

    sql> /

    Table created.

    sql> insert into obj_range_list

    2 select object_id,object_name,created,object_type

    3 from obj where object_id < 10000;

    9580 rows created.

    PU@orcl10g> commit;

    sql> select count(*) from obj_range_list partition(range_list_P1);

    COUNT(*)

    ----------

    2953

    5、RANG、HASH分区

    首先,按照range分区,在每个range范围内,再作hash分区

    sql> create table obj_range_hash

    2 (

    3 object_id number,

    4 object_name varchar2(128),

    5 created date,

    6 object_type varchar2(19)

    7 )

    8 partition by range(object_id) subpartition by hash(object_name) subpartitions 3 store in(part1,part2,part3)

    9 (

    10 partition rh_p1 values less than(3000),

    11 partition rh_p2 values less than(6000),

    12 partition rh_p3 values less than(maxvalue)

    13* )

    sql> /

    Table created.

    sql> insert into obj_range_hash

    2 select object_id,object_name,created ,object_type

    3 from obj where object_id < 10000;

    9580 rows created.

    sql> commit;

    Commit complete.

    sql> select count(*) from obj_range_hash;

    COUNT(*)

    ----------

    10000

    sql> select count(*) from obj_range_hash partition(rh_p1);

    COUNT(*)

    ----------

    2952

    分区表创建就写那么多了,快去试试创建一个吧!

    展开全文
  • MySQL 分区表

    2021-02-02 06:07:02
    MySQL 分区表 :逻辑上是一个表,物理上是由多个子表组合而成的一个表集合,每个子表相对独立,各自存储着自己的数据索引。这种分区表又称局部分区表。mysql暂时不支持全局分区表(各个分区存储数据,索引存在其他...

    MySQL 分区表 :逻辑上是一个表,物理上是由多个子表组合而成的一个表集合,每个子表相对独立,

    各自存储着自己的数据和索引。这种分区表又称局部分区表。mysql暂时不支持全局分区表(各个分区

    存储数据,索引存在其他对象中)

    在以前的老版本(mysql5.6之前)中有一个变量

    have_partitioning 开关控制着是否开启分区,默认为开启的,,mysql5.6将这个变量去掉了,

    自动开启。

    表分区的优点:

    1.数据管理方便。单独管理某些分区,例如:删除历史数据,优化、检查、修复个别分区,备份,恢复个别分区

    2.对某些特定的查询起到极大的优化作用

    3.涉及到例如SUM() 和 COUNT()这样聚合函数的查询,可以很容易地进行并行处理

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

    5.分散热点(hotpage)

    分区表的缺点:

    1.每张表最大分区数为1024

    2.分区函数返回值必须是int类型,mysql5.6开始可以直接对列进行分区。

    3.不能使用任何外间约束。

    4.所有的主键或者唯一索引必须被保函在分区表达式中。

    5.在分区表达式中,不允许子查询

    分区表类型:

    range分区表:根据一个列的值的范围分布存储数据。

    list分区表:和range分区表相似,但是list分区面向的是离散的值。

    hash分区表:根据用户提供的分区表达式的返回值来进行分布存储数据。

    key分区表:根据数据库提供的哈希函数来进行分区。

    columns分区:可以直接使用非整型的数据进行分区但只能在range和list上使用

    子分区:又称符合分区,mysql允许在range和list的分区上再进行hash或key的字分区。

    range分区表:根据一个列的值的范围分布存储数据。

    这种分区方式的需要用户定义分区表达式,分区临界值,而且对于分区表达式返回的值

    必须是一个非负整数。如果表中定义了主键,那么分区列必须也包含在主键中。

    这种分区模式常用与按月存储数据的场景中。

    drop table if EXISTS tt ;

    create table tt(

    tid  int  not null   auto_increment ,

    tname varchar(100) not null ,

    tage TINYINT  default 0 ,

    tadd varchar(100) default  '' ,

    tel varchar(20)  default  0,

    tmob varchar(20) DEFAULT '' ,

    tsfz varchar(100) default  '',

    taddtime datetime default now(),

    primary key(tid ,taddtime))

    ENGINE=InnoDB DEFAULT CHARSET=utf8

    PARTITION by RANGE(year(taddtime))(

    PARTITION p0 VALUES less than (2009) ,

    PARTITION p1 VALUES less than (2010) ,

    PARTITION p2 VALUES less than  (2011) ,

    PARTITION p3 VALUES less than (2012) ,

    PARTITION p4 VALUES less than  MAXVALUE /*大于2012的值将会存储在p4分区中*/

    )

    list分区表:和range分区表相似,但是list分区面向的是离散的值。

    range分区表的一些限制list分区也同样受限。

    drop table if EXISTS tt ;

    create table tt(

    tid  int  not null   auto_increment ,

    tname varchar(100) not null ,

    tage TINYINT  default 0 ,

    tadd varchar(100) default  '' ,

    tel varchar(20)  default  0,

    tmob varchar(20) DEFAULT '' ,

    tsfz varchar(100) default  '',

    tdeptId int default 0 ,

    taddtime datetime default now(),

    primary key(tid ,tdeptId))

    ENGINE=InnoDB DEFAULT CHARSET=utf8

    PARTITION by list(tdeptId)(

    partition p0 VALUES in(1,2,3),

    PARTITION p1 VALUES in(4,5,6),

    PARTITION p2 VALUES in(7,8,9)

    )

    hash分区表:根据用户提供的分区表达式的返回值来进行分布存储数据。分区表达式返回的值必须为非负整数。

    然后用户指定一个分区个数,系统自动使用分区表达式的返回的非负整数除以分区数,然后取余数,存放在对应的

    分区中

    drop table if EXISTS tt ;

    create table tt(

    tid  int  not null   auto_increment ,

    tname varchar(100) not null ,

    tage TINYINT  default 0 ,

    tadd varchar(100) default  '' ,

    tel varchar(20)  default  0,

    tmob varchar(20) DEFAULT '' ,

    tsfz varchar(100) default  '',

    tdeptId int default 0 ,

    taddtime datetime default now(),

    primary key(tid,taddtime))

    ENGINE=InnoDB DEFAULT CHARSET=utf8

    PARTITION by HASH(year(taddtime))

    PARTITIONS 4;

    key分区表:根据数据库提供的哈希函数对给定的列进行来进行分区。和hash最大的区别在于不需要用户提供分区函数。

    drop table if EXISTS tt ;

    create table tt(

    tid  int  not null   auto_increment ,

    tname varchar(100) not null ,

    tage TINYINT  default 0 ,

    tadd varchar(100) default  '' ,

    tel varchar(20)  default  0,

    tmob varchar(20) DEFAULT '' ,

    tsfz varchar(100) default  '',

    tdeptId int default 0 ,

    taddtime datetime default now(),

    primary key(tid,taddtime))

    ENGINE=InnoDB DEFAULT CHARSET=utf8

    PARTITION by key(taddtime)

    PARTITIONS 4;

    columns分区:mysql5.6可以直接使用非整型的数据进行分区但只能在range和list上使用。

    在mysql5.6之前必须使用函数将列换成成整型才能对表进行分区,可以用来支持分区的常用函数有year(),to_days(),month()等。

    mysql5.6开始对整列进行分区,不需要函数进行转换,但是这个功能仅仅限于range和list分区

    range columns 分区

    drop table if EXISTS tt ;

    create table tt(

    tid  int   not null   auto_increment ,

    tname varchar(100) not null ,

    tage TINYINT  default 0 ,

    tadd varchar(100) default  '' ,

    tel varchar(20)  default  0,

    tmob varchar(20) DEFAULT '' ,

    tsfz varchar(100) default  '',

    tdeptId int default 0 ,

    taddtime datetime DEFAULT now(),

    PRIMARY key(tid,taddtime)

    )

    ENGINE=InnoDB DEFAULT CHARSET=utf8

    PARTITION by range COLUMNS(taddtime)(

    PARTITION p0 VALUES less than ('2009-01-01') ,

    PARTITION p1 VALUES less than ('2010-01-01') ,

    PARTITION p2 VALUES less than ('2011-01-01'),

    PARTITION p3 VALUES less than ('2012-01-01'),

    PARTITION p4 VALUES less than MAXVALUE

    )

    list columns 分区:

    drop table if EXISTS tt ;

    create table tt(

    tid  int   not null   auto_increment ,

    tname varchar(100) not null ,

    tage TINYINT  default 0 ,

    tadd varchar(100) default  '' ,

    tel varchar(20)  default  0,

    tmob varchar(20) DEFAULT '' ,

    tsfz varchar(100) default  '',

    tdeptId int default 0 ,

    taddtime datetime DEFAULT now(),

    PRIMARY key(tid,tname)

    )

    ENGINE=InnoDB DEFAULT CHARSET=utf8

    PARTITION by list COLUMNS(tname)(

    PARTITION p0 VALUES in ('张三疯','张无忌') ,

    PARTITION p1 VALUES in ('郭靖','杨康') ,

    PARTITION p2 VALUES in ('李四','张三'),

    PARTITION p3 VALUES in ('甲鱼','乌龟')

    )

    Q1:list分区中,出现定义表达式以外的值

    mysql> insert into  tt(tname,tage,tadd,tel,tmob,tsfz) VALUES('朱元璋',120,'武当山' ,18099001122,'012-46319976','') ;

    ERROR 1526 (HY000): Table has no partition for value from column_list

    直接插入失败了,原因是mysql不知道将这条数据存储在哪个分区中。

    分区管理与维护:

    删除分区:

    alter table tt drop PARTITION p0 ;

    alter table tt drop PARTITION p0,p1 ;

    note:不可以删除hash和key分区

    添加分区:

    range 分区:

    mysql> alter table tt add  PARTITION ( PARTITION  p0 VALUES less than (2009));

    ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition

    range分区添加分区报错了。,把maxvalues 的分区去掉

    mysql> alter table tt  drop PARTITION p4 ;

    Query OK, 0 rows affected (0.05 sec)

    Records: 0  Duplicates: 0  Warnings: 0

    添加分区,在最小临界值的前面添加分区

    mysql> alter table tt

    -> add  PARTITION (

    -> PARTITION  p5 VALUES less than (2008)

    -> );

    ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

    还是报错了 ,在最大临界值的后面添加分区

    mysql> alter table tt  add  PARTITION ( PARTITION  p5 VALUES less than (2013) );

    Query OK, 0 rows affected (0.05 sec)

    Records: 0  Duplicates: 0  Warnings: 0

    成功添加分区

    对range分区添加新的分区,range分区不能使用maxvalues,并且只能在最大临界值的后面添加。

    list分区添加的新的分区:

    alter table tt add PARTITION (PARTITION p3 VALUES in (10))

    不能对hash和key分区添加新的分区

    拆分分区:

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

    下面是一个拆分 range columns 分区的实例

    将p1分区拆分p5和p6两个分区:

    alter table  tt

    REORGANIZE PARTITION p1 into (

    PARTITION p5 VALUES less than ('2009-06-01'),

    PARTITION p6 VALUES less than ('2010-01-01')

    )

    拆分p4最后一个分区

    alter table  tt

    REORGANIZE PARTITION p4 into (

    PARTITION p7 VALUES less than ('2013-01-01'),

    PARTITION p8 VALUES less than ('2014-01-01'),

    PARTITION p9 VALUES less than MAXVALUE

    )

    合并p5,p6分区:

    alter table tt

    REORGANIZE PARTITION p5,p6  into (

    PARTITION p1 VALUES less than ('2010-01-01')

    )

    合并p7,p8,p9分区:

    alter table tt

    REORGANIZE PARTITION p7,p8,p9  into (

    PARTITION p4 VALUES less than MAXVALUE

    )

    重新定义分区表:可以利用这种方法对一张新表创建分区,而不需要另外的数据导入导出

    alter table tt

    PARTITION by range COLUMNS(taddtime)(

    PARTITION p0 VALUES less than ('2010-01-01') ,

    PARTITION p1 VALUES less than ('2010-05-01') ,

    PARTITION p2 VALUES less than ('2011-01-01'),

    PARTITION p3 VALUES less than ('2011-05-01'),

    PARTITION p4 VALUES less than MAXVALUE

    )

    删除表的所有分区: #不会丢失数据,表分变成普通表

    ALTER TABLE tt REMOVE PARTITIONING

    重建分区:

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

    ALTER TABLE tt REBUILD PARTITION p0, p1;

    优化分区:

    如果从分区中删除了大量的行,或者对一个带有可变长度的行(也就是说,有VARCHAR,BLOB,或TEXT类型的列)作了许多修改,

    可以使用“ALTER TABLE ... OPTIMIZE PARTITION”来收回没有使用的空间,并整理分区数据文件的碎片。

    ALTER TABLE tt OPTIMIZE PARTITION p0, p1;

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

    ALTER TABLE tt ANALYZE PARTITION p1;

    修复分区:修复损坏的分区。

    ALTER TABLE tt REPAIR PARTITION p0,p1;

    检查分区:

    ALTER TABLE tt CHECK PARTITION all;

    截断分区:就是删除分区中的数据

    ALTER TABLE tt TRUNCATE PARTITION p1;

    置换分区:从mysql5.6之后才开始引入的,以前的老版本不支持置换分区。

    将指定分区的记录置换到普通表中,普通表的定义应该与分区表相同,包括约束、索引等

    ALTER TABLE tt exchange PARTITION p0 WITH TABLE t_exch;

    普通表里面的数据和分区里面的数据相互交换。

    查看分区表的属性:

    select * from  information_schema.partitions where TABLE_SCHEMA='TestDB' and  table_name ='tt' \G   ;

    在对表进行过拆分,合并,或重新定义之后 建议最好是优化一下

    alter table  tt  optimize  parition all

    否则 有可能从information_schema.partitions中读到的信息是错误的。

    分区的基本语法以及简单管理:

    分区的创建,新增,删除,合并,拆分

    分区的管理

    分区的相关讨论

    分区性能讨论

    分区的性能测试

    mysql分区相关介绍

    mysql分区注意事项:

    函数相关:

    展开全文
  • INTERVAL分区其实是一种比较特殊的范围分区,因此可以很方便的将RANGE分区表转化为INTERVAL分区表,同样可以将INTERVAL分区表转化为RANGE分区表。对于一个普通的范围分区表:SQL> CREATE TABLE T_PART2 (ID ...
  • 为了更好地理解分区和分段的工作原理,您应该了解数据如何存储在配置单元中 . 假设你有一张 tableCREATE TABLE mytable (name string,city string,employee_id int )PARTITIONED BY (year STRING, month STRING, ...
  • 查看的分布状况mysql> select count(*) from justin;+----------+| count(*) |+----------+| 5845246 |+----------+1 row in set (0.00 sec)mysql> select month(create_time),count(*) from justin group ...
  • 禁止构建分区表达式不支持以下几种构建:存储过程,存储函数,UDFS或者插件声明变量或者用户变量可以参考分区不支持的SQL函数算术逻辑运算符分区表达式支持+,-,*算术运算,但是不支持DIV/运算(还存在,可以查看...
  • 浅谈MySQL分区表优点限制

    千次阅读 2021-01-31 07:20:31
    分区是将一个表的数据按照某种方式,逻辑上仍是一个表,也就是所谓的分区表。...作为MySQL数据库中的一个重要机制,MySQL分区表优点限制也是一目了然的,然而又能够同时实现共存。一、我们先来看MySQL分区...
  • 一、分区表分区表的几种分类:1、Range(范围)分区是应用范围比较广的表分区方式,它是以列的的范围来做为分区的划分条件,将记录存放到列所在的range分区中。比如按照时间划分,2012 年1 季度的数据放到a 分区,...
  • 创建分区表 以及拆分分区表(修改分区)

    多人点赞 热门讨论 2021-06-09 09:11:36
    postgresql数据库 创建分区表 以及拆分分区表(修改分区) 前言 在日常维护啊数据库的过程中,往往发现一部分表由于日积月累的原因,表内的的数据量越来越大,会影响我们的查询效率,特别是往往只查询部分数据时...
  • 详解MySQL分区表

    2021-02-02 05:32:03
    但是对于应用程序来讲,分区的表和没有分区的表是一样的。换句话来讲,分区对于应用是透明的,只是数据库对于数据的重新整理。本篇文章给大家带来的内容是关于MySQL中分区表的介绍及使用场景,有需要的朋友可以参考...
  • 1.分区表Hive中的分区就是分目录,把一个大的数据集根据业务需要分割成小的数据集。例如按日期分区-- log_info-- 201901-- 201902-- 201903在查询时通过WHERE子句中的表达式选择查询所需要的指定的分区,这样的查询...
  • 自动创建新的分区表-- 创建一张管理分区的表CREATE TABLE `t_bfi_partition` (`sysId` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键自增序列',`table_schema` varchar(100) DEFAULT NULL COMMENT '库',`table_...
  • 分区表导入数据库

    2021-04-20 06:40:57
    如果是10g及以上版本,可以使用参数REMAP_TABLESPACE 来映射空间1.在ascii数据库中建立3个tablespaces:tbs1,tbs2,tbs3;SQL> create tablespace tbs1 datafile 'tbs1_data1' size 10M;Tablespace created.SQL>...
  • (1)分区分桶都是细化数据管理,但是分区表是手动添加区分,由于hive是读模式,所以对添加进分区的数据不做模式校验。分桶表的数据时按住某些分桶字段进行hash散列 相乘的多个文件,所以数据的准确性高很多 (2...
  • 进行分区后,逻辑上仍然是一张完整的,只是将中的数据在物理上存放到多个空间(物理文件上),这样查询数据时,不至于每次都扫描整张。创建一个按字段数据范围分区分区置于指定的不同空间中。一般...
  • mysql分区表

    2021-01-20 01:58:53
    一个或多个列的Hash Key进行计算,通过这个Hash码不同数值对应的数据区域进行分区Key,Hash模式的一种延伸ListComposite,复合模式#子分区(复合分区)#在每个分区内,子分区的名字必须是唯一的,目前在整个中,也要...
  • 1 背景说明从Oracle 11g 开始,在使用数据泵导出导入分区表时,默认会按照原来的表进行操作,即原来是分区表导出的,那么导入的也是分区表。 如果想在导入的过程中,转成非分区表,那么需要impdp命令指定选项...
  • MySQL分区表原理详解

    2021-01-19 07:06:56
    分区表是将大表的数据分成称为分区的许多小的子集,分区是将一个表的数据按照某种方式,比如按照时间上的月份,分成多个较小的,更容易管理的部分,但是逻辑上仍是一个表。由于在MySQL数据库中,我们对MySQL分区表的...
  • ORACLE分区表详解

    2021-04-30 07:57:20
    此文从以下几个方面来整理关于分区表的概念及操作:1.表空间及分区表的概念2.表分区的具体作用3.表分区的优缺点4.表分区的几种类型及操作方法5.对表分区的维护性操作.(1.) 表空间及分区表的概念表空间:是一个或多个...
  • 禁止构建分区表达式不支持以下几种构建:存储过程,存储函数,UDFS或者插件声明变量或者用户变量算术逻辑运算符分区表达式支持+,-,*算术运算,但是不支持DIV/运算(还存在,可以查看Bug #30188, Bug #33182)。...
  • Hive分区表实战

    2021-01-14 16:01:06
    1. Hive分区表PartitionBucket,为了提升查询效率,前者是粗粒度的划分,后者是细粒度的划分。建表语句中使用partitioned by指定分区字段分区表有静态分区动态分区两种。若分区的是确定的,那么称为静态分区...
  • 1、合并表和分区表1.1、合并表:把多个结果相同的表合并成为一个容器。表类型:Myisam 存储引擎:merge合并:unionCreate table packtable (Id int not null primary key) engine=merge union=(table1,table2)存在的...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 200,458
精华内容 80,183
关键字:

和值分区表