精华内容
下载资源
问答
  • mysql 分区表
    2022-04-08 16:56:06

    1.定义

    分区表通过分区函数,把物理表结构相同的多张表组成逻辑上的一张大表,分区函数包括RANGE、LIST、HASH、KEY、COLUMNS。

    2.设计

    分区表的主键一定要包含分区函数的列,每张表有自己的索引,从逻辑上看是一张表,但物理上存储在不同文件中。
    分区表的索引都是局部,而非全局。也就是说,索引在每个分区文件中都是独立的,所以分区表上的唯一索引必须包含分区列信息,否则创建会报错。
    唯一索引使用全局唯一的字符串来保证全局唯一。

    3.性能

    分区表技术不是用于提升 MySQL 数据库的性能,而是方便数据的管理。
    分区表的查询务必使用到分区列作为条件,否则会增加io的次数,性能变差。
    在server层,认为这是同一张表,因此所有分区共用同一MDL锁;
    在引擎层,认为这是不同表,因此MDL锁之后的执行过程,会根据分区表规则,只访问必要的分区。

    更多相关内容
  • MySQL分区表概述 我们经常遇到一张表里面保存了上亿甚至过十亿的记录,这些表里面保存了大量的历史记录。 对于这些历史数据的清理是一个非常头疼事情,由于所有的数据都一个普通的表里。所以只能是启用一个或多个带...
  • mysql分区表LIST

    2020-12-14 10:15:13
    1.新建普通表 CREATE TABLE data(id int(30) ,time int(12), sarlay decimal(12,2)); 2.插入数据 INSERT INTO data VALUES (1,201911,10000), (2,201912, 12000)...3.建立LIST分区表 CREATE TABLE data_list(id int(30)
  • 用存储过程实现了MySQL数据库分区表的自动创建和自动删除功能。亲测有效。希望有用。
  • 详解MySQL分区表

    2020-12-14 07:23:59
    本篇文章给大家带来的内容是关于MySQL分区表的介绍及使用场景,有需要的朋友可以参考一下,希望对你有所帮助。 1.分区的目的及分区类型 MySQL在创建表的时候可以通过使用PARTITION BY子句定义每个分区存放的数据。...
  • 禁止构建 分区表达式不支持以下几种构建: 存储过程,存储函数,UDFS或者...在MySQL 5.7.1之前的分区表不支持HANDLER语句,以后的版本取消了这一限制。 服务器SQL模式 如果要用用户自定义分区的表的话,需要注意的是
  • 在最近的项目中,我们需要保存大量的数据,而且这些数据是有有效期的,为了提供查询效率以及快速删除过期数据,我们选择了MySQL分区机制。把数据按照时间进行分区分区类型 Range分区:最为常用,基于属于一个...
  • 本篇文章给大家带来的内容是关于MySQL分区表的介绍及使用场景,有需要的朋友可以参考一下,希望对你有所帮助。 1.分区的目的及分区类型 MySQL在创建表的时候可以通过使用PARTITION BY子句定义每个分区存放的数据...
  • 互联网公司为啥不使用mysql分区表
  • 文章目录优化索引为索引列选择合适的数据类型一般原则建立索引,但是不走索引的情况表分区表分区的功能范围分区(Range Partition列表分区(List Partition)哈希分区(Hash Partition)复合分区 优化索引 MySQL中,...
  • 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分区注意事项:

    函数相关:

    展开全文
  • 我一直想着用mysql的分区表解决,而乔楚(乔大姐)则认为是采用sphinx来解决。但后来我找了一个资料才发现,...--START--Mysql5.1已经发行很久了,本文根据官方文档的翻译和自己的一些测试,对Mysql分区表的局限性做了...

    昨天晚上asers.z问我怎么样使得数据在搜索的时候和58.com差不多,而且展示数据的速度要快。我一直想着用mysql的分区表解决,而乔楚(乔大姐)则认为是采用sphinx来解决。

    但后来我找了一个资料才发现,原来分区表还是有局限性的,比如他就不支持全文索引。我是看这里看到的。。

    --START--

    Mysql5.1已经发行很久了,本文根据官方文档的翻译和自己的一些测试,对Mysql分区表的局限性做了一些总结,因为个人能力以及测试环境的 原因,有可能有错误的地方,还请大家看到能及时指出,当然有兴趣的朋友可以去官 方网站查阅。

    本文测试的版本

    一、关于Partitioning Keys, Primary Keys, and Unique Keys的限制

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

    这句话也许不好理解,我们做几个实验:

    二、关于存储引擎的限制

    2.1 MERGE引擎不支持分区,分区表也不支持merge。

    2.2 FEDERATED引擎不支持分区。这限制可能会在以后的版本去掉。

    2.3 CSV引擎不支持分区

    2.4 BLACKHOLE引擎不支持分区

    2.5 在NDBCLUSTER引擎上使用分区表,分区类型只能是KEY(or LINEAR KEY) 分区。

    2.6 当升级MYSQL的时候,如果你有使用了KEY分区的表(不管是什么引擎,NDBCLUSTER除外),那么你需要把这个表dumped在 reloaded。

    2.7 分区表的所有分区或者子分区的存储引擎必须相同,这个限制也许会在以后的版本取消。

    不指定任何引擎(使用默认引擎)。

    所有分区或者子分区指定相同引擎。

    三、关于函数的限制

    在mysql5.1中建立分区表的语句中,只能包含下列函数:

    ABS()

    CEILING() and FLOOR() (在使用这2个函数的建立分区表的前提是使用函数的分区键是INT类型),例如

    mysql>CREATETABLEt(cFLOAT)PARTITIONBYLIST(FLOOR(c))(

    ->PARTITIONp0VALUESIN(1,3,5),

    ->PARTITIONp1VALUESIN(2,4,6)

    ->);;

    ERROR1491(HY000):ThePARTITIONfunctionreturnsthewrongtype

    mysql>CREATETABLEt(cint)PARTITIONBYLIST(FLOOR(c))(

    ->PARTITIONp0VALUESIN(1,3,5),

    ->PARTITIONp1VALUESIN(2,4,6)

    ->);

    QueryOK,0rowsaffected(0.01sec)

    DAY()

    DAYOFMONTH()

    DAYOFWEEK()

    DAYOFYEAR()

    DATEDIFF()

    EXTRACT()

    HOUR()

    MICROSECOND()

    MINUTE()

    MOD()

    MONTH()

    QUARTER()

    SECOND()

    TIME_TO_SEC()

    TO_DAYS()

    WEEKDAY()

    YEAR()

    YEARWEEK()

    四、其他限制

    4.1 对象限制

    下面这些对象在不能出现在分区表达式

    Stored functions, stored procedures, UDFs, or plugins.

    Declared variables or user variables.

    4.2 运算限制

    支持加减乘等运算出现在分区表达式,但是运算后的结果必须是一个INT或者NULL。 |, &, ^, <>, , ~ 等不允许出现在分区表达式。

    本文来自电脑杂谈,转载请注明本文网址:

    http://www.pc-fly.com/a/jisuanjixue/article-25161-1.html

    展开全文
  • 表分区的测试使用,主要内容来自于其他博客文章以及mysql5.1的参考手册mysql测试版本:mysql5.5.28mysql物理存储文件(有mysql配置的datadir决定存储路径)格式简介数据库engine为MYISAMfrm结构文件,myd数据...
  • 我们试着想一想, 在生产环境中什么最重要? 我感觉在生产环境中应该没有什么比数据跟更为重要. 那么我们该如何保证数据不丢失、或者丢失后可以快速恢复呢?只要看完这篇大家应该就能对MySQL中数据备份有一定了解
  • MySQL分区表详解

    千次阅读 2022-01-04 15:31:58
    分区表的优点: 数据可以跨磁盘/文件系统存储,适合存储大量数据。 数据的管理非常方便,以分区为单位操作数据,不会影响其他分区的正常运行。 数据查询上在某些条件可以利用分区裁剪(partition pruning)特性,将...

    通常情况下,同一张表的数据在物理层面都是存放在一起的。随着业务增长,当同一张表的数据量过大时,会带来管理上的不便。而分区特性可以将一张表从物理层面根据一定的规则将数据划分为多个分区,多个分区可以单独管理,甚至存放在不同的磁盘/文件系统上,提升效率。

    分区表的优点:

    • 数据可以跨磁盘/文件系统存储,适合存储大量数据。
    • 数据的管理非常方便,以分区为单位操作数据,不会影响其他分区的正常运行。
    • 数据查询上在某些条件可以利用分区裁剪(partition pruning)特性,将搜索范围快速定位到特性分区,提升查询性能。

    对于应用来说,表依然是一个逻辑整体,但数据库可以针对不同的数据分区独立执行管理操作,不影响其他分区的运行。而数据划分的规则即称为分区函数,数据写入表时,会根据运算结果决定写入哪个分区。

    MySQL的分区插件与存储引擎运行在不同的层,因此大部分存储引擎都可以利用MySQL的分区特性,只有少数存储引擎(merge,CSV,federated)不支持分区特性。若某张表使用的分区特性,则所有的分区都需要使用相同的存储引擎,且分区特性会同时应用到数据和索引上。

    MySQL的分区类型:

    一、分区的类型

    1. Range partition(范围分区)

    2. List partition(列表分区)

    3. Hash partition(哈希分区)

    4. Key partition(键值分区)

    二、subparitioning(子分区)

    三、分区的基本维护


    一、分区的类型

    1. Range partition(范围分区)

    Range partition是按照分区表达式的运算结果,判断结果落在某个范围内,从而将数据存储在对应的分区。各个分区定义之间需要连续且不能重叠,范围分区通过partition by range子句定义,而分区的范围通过values less than子句划分。

    例:定义一个员工表,根据员工ID分区,1~10号员工一个分区,11~20号员工一个分区,依次类推,共建立4个分区:

    create table employees (
        id int not null primary key,
        first_name varchar(30),
        last_name varchar(30))
        partition by range(id)(
        partition p0 values less than (11),
        partition p1 values less than (21),
        partition p2 values less than (31),
        partition p3 values less than (41)
        );

    现在随便插入几条数据:

    insert into employees values(1,'Vincent','Chen');
    insert into employees values(6,'Victor','Chen');
    insert into employees values(11,'Grace','Li');
    insert into employees values(16,'San','Zhang');
    commit;

    分区查询:

    如果在查询时候明确的知道数据所在的分区,我们可以直接指定分区:

    select * from employees partition(p0);  -- 查询p0分区
    select * from employees partition(p0,p1);  -- 查询p0和p1分区

     分区删除:

    如果某分区数据不再需要的时候,我们可以用alter table ... drop partition来删除指定分区,例如删除分区p1,采用drop partition的方式可以快速清除历史数据:

    alter table employees drop partition p1;

     分区p1被删除后,所有p1分区的数据都已丢失,此时原p1分区的范围将由p2覆盖。

    分区新增:

    对于range分区来说,分区新增只能在最大范围之上增加分区,因此p1分区被删除后就无法通过新增分区的方式加回了,下例试图对ID10~20的员工新增一个分区,系统会返还错误。

    alter table employees add partition (partition n1 values less than(21));

    而在最大的分区范围之上是可以的:

    alter table employees add partition (partition p4 values less than(51));

     分区重组织:

    如果一定要在分区之间插入新的分区,则可以采用重组织的方式,将已有分区的数据重新划分,达到创建新分区的效果:

     例如我要将p2划分为2个分区,分别是11~20,21~30:

    alter table employees reorganize partition p2 into (
    partition p1 values less than(21),
    partition p2 values less than(31));

     

     此时原p2分区被拆分为了p1,p2,数据也在2个分区间重新分布,保证不会丢失。效果就像我们在中间插入了一个分区一样。

    目前定义的分区都是有上限的,如果有大于分区上限的值想插入表中,系统会返还错误,为了兼容这种情况,我们可以新增一个分区,上限为maxvalue。所有大于当前上限的值都会放入这个分区:

    alter table employees add partition(partition pmax values less than(maxvalue));

    范围分区的条件除了直接用值,还可以用函数来定义。一个常用的场景就是按时间分区,例如:在create table中使用partition by range(year(hire_date)),可以按照年份来进行分区。这种分区方式在需要定期清理过期数据的场景会非常方便。

    Range columns分区:

    Range分区还有一个变种,叫做range columns分区。此分区方式允许使用多个column来作为分区范围条件。但是此分区方式不能接受函数,只能直接用列的名称。但是对分区列的类型不再限制为整数,可以使用string,date等类型。

    使用range columns对多个列进行分区:

    CREATE TABLE rc2 (
        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 (10,30),
        PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE) );

    使用range columns直接对date类型进行分区:

    CREATE TABLE members (
        firstname VARCHAR(25) NOT NULL,
        lastname VARCHAR(25) NOT NULL,
        username VARCHAR(16) NOT NULL,
        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);
    

    Range partition和null:

    对range分区来说,如果插入数据分区键为null,是可以成功的,数据会被放到第一个分区中。

    2. List partition(列表分区)

    列表分区和范围分区类似,主要区别是list partition的分区范围是预先定义好的一系列值,而不是连续的范围。列表分区采用partition by list和values in子句定义。

    示例,创建一张员工表按照ID进行列表分区:

    CREATE TABLE employees (
        id INT NOT NULL,
        fname VARCHAR(30),
        lname VARCHAR(30)
    )
    PARTITION BY LIST(id) (
        PARTITION p0 VALUES IN (1,3,5,7,9),
        PARTITION p1 VALUES IN (2,4,6,8,10)
    );

    和range分区一样,可以使用alter table ... add/drop partition新增/删除分区:

    ALTER TABLE employees ADD PARTITION(PARTITION p2 VALUES IN (11,12,13,14,15));
    ALTER TABLE employees DROP PARTITION p0;

    List partition和非事务引擎:

    如果插入的值在list分区范围中不存在的话,语句会返还错误。如果表使用的是事务型引擎,如innodb。则这个事务会完全回滚。如果使用的是非事务引擎,若MyISAM,虽然也会报错,但是已插入的行无法回滚。

    下面新建两张表,一张使用innodb,一张使用myisam:

    CREATE TABLE employees_innodb (
        id INT NOT NULL,
        fname VARCHAR(30),
        lname VARCHAR(30)
    ) ENGINE=InnoDB   -- 事务型引擎
    PARTITION BY LIST(id) (
        PARTITION p0 VALUES IN (1,3,5,7,9),
        PARTITION p1 VALUES IN (2,4,6,8,10));
    
    CREATE TABLE employees_myisam (
        id INT NOT NULL,
        fname VARCHAR(30),
        lname VARCHAR(30)
    ) ENGINE=MyISAM   -- 非事务型引擎
    PARTITION BY LIST(id) (
        PARTITION p0 VALUES IN (1,3,5,7,9),
        PARTITION p1 VALUES IN (2,4,6,8,10));
    insert into employees_innodb values(1,'Vincent','Chen'),(11,'Grace','Li'),(2,'Victor','Chen');
    insert into employees_myisam values(1,'Vincent','Chen'),(11,'Grace','Li'),(2,'Victor','Chen');
    
    select * from employees_innodb;
    select * from employees_myisam;

    我们在中间放了一个不存在对应分区的记录来触发错误, 可以看到innodb引擎数据没有数据插入(回滚)。但MyISAM引擎报错前的数据已经保留了下来,但是报错之后的记录未执行插入。

    如果要忽略此错误,可以在insert语句中使用ignore关键字来忽略此错误,只插入符合分区条件的值:

    insert ignore into employees_innodb values(1,'Vincent','Chen'),(11,'Grace','Li'),(2,'Victor','Chen');
    insert ignore into employees_myisam values(1,'Vincent','Chen'),(11,'Grace','Li'),(2,'Victor','Chen');
    

     可以看到符合条件的记录被插入,不符合条件的自动被过滤,不返回错误。

    List columns分区:

    和range分区类似,list partition也有一个变种的list columns 分区,此分区类型可以使用多个列同时作为分区条件,且不再限制整数。并且可以使用string,date等数据类型作为分区条件。

    list partition和null:

    对于列表分区来说,必须有1个分区显示的指定可以包含null,否则插入会失败。

    3. Hash partition(哈希分区)

    Hash partition主要的应用场景是将数据平均的分布在指定数量的hash分区中。在range和list分区类型中,根据分区条件的计算结果,数据可以确定存储在哪个分区,而在hash分区中,数据存储在某个分区是由数据库自己决定的,你只需要指定分区的数量。

    要创建hash分区,你需要使用create table的partition by hash(expr)子句,其中expr是整数类型的列或返还整数的表达式。另外还需要使用partions num来指定hash分区的数量(若忽略partitions子句则默认只创建1个hash分区)。

    例:创建一个具有4个hash分区的表,按照ID进行分区:

    CREATE TABLE employees (
        id INT NOT NULL,
        first_name VARCHAR(30),
        last_name VARCHAR(30)
    )
    PARTITION BY HASH(id)
    PARTITIONS 4;

    Hash分区的性能考虑:

    对于hash分区,数据库决定将数据存储在哪个分区是采用取余的方式进行运算的。存储分区的编号n = mod(expr, num),其中expr是分区值,num为定义的分区数量。

    例如:对于4个hash分区,ID为5的记录,会存储在mod(5,4)=1,即1号分区中。

    因此,最佳的分区键值的变化方式应该是线性变化,此时使用hash分区的效率最高,分布也会均匀。由于分区键值expr在每次insert/update/delete时都会运算,太复杂的表达式也会带来负面的性能影响,在选择时也需要考虑。

    Liner hash partition(线性哈希分区):

    以上的分区类型即普通hash分区,另外还有一类变种叫做liner hash partition(线性哈希分区),线性哈希分区的区别是其使用了更复杂的计算方法来确定数据的分布:

    1. 对于分区数量为num的分区表,先计算V:
       V = POWER(2, CEILING(LOG(2, num)))
    
    2. 对于分区值与V-1进行位与运算
       N = expr & (V - 1)   -- 位与运算
    
    3. 对于N>num的情况,再次计算
       N = N & ((v/2)-1)  

    例:假设分区数量为4

    1. 先根据分区数量num计算V的值,先log再power,如果分区数量如果是2的次方,则此公式计算结果不变。

    V = power(2,ceilling(log(2,4)))  = power(2,ceillling(2)) = power(2,2) = 4

    2. 对于ID为5的记录:

    N = 5 & (V-1) = 5 & 3 = 1

    3. 第二步计算出的结果为1,N<=num,不再需要第三步计算,数据存储在1号分区。

    计算机的位计算效率是非常高的,因此Liner hash在新增/删除/合并/分裂分区场景(需要重新计算并分布数据)速度会快很多,非常适合特别大的数据存储场景(TB级别)。

    Liner hash的缺点是数据的分布可能没有普通hash均匀。

    管理hash分区数量:

    Hash分区无法像range和list那样添加和删除分区,但是你可以用alter table的coalesce partition子句来减少hash分区的数量,用add partition partitions N来增加指定数量的分区。

    例:将employees的hash分区数量由4调整为3:

    ALTER TABLE employees COALESCE PARTITION 1;  -- 移除一个分区

    例:为employees表新增3个hash分区:

    ALTER TABLE employees ADD PARTITION PARTITIONS 3;  -- 新增3个hash分区

    hahs partition和null:

    对于hash partition和key partition,任何表达式对null运算,都会被当做返回为0.

    4. Key partition(键值分区)

    Key paritition与hash分区类似,主要区别在于key partition的hash函数是由MySQL server提供的,且使用主键(或非空唯一键)作为分区列:

    例如:创建一个2个分区的key partition table:

    CREATE TABLE k1 (
        id INT NOT NULL PRIMARY KEY,
        name VARCHAR(20)
    )
    PARTITION BY KEY()  -- 未指定分区列,自动使用主键
    PARTITIONS 2;
    CREATE TABLE k1 (
        id INT NOT NULL,  -- 如果未定义not null,创建表会失败
        name VARCHAR(20),
        UNIQUE KEY (id)
    )
    PARTITION BY KEY()  -- 未定义主键,自动使用unique key
    PARTITIONS 2;

    另外对于key partition,paritition key也不像其他分区类型那样限制为整数类型,例如,可以使用字符型作为分区键:

    CREATE TABLE tm1 (
        s1 CHAR(32) PRIMARY KEY   -- 字符型主键,同时作为partition key
    )
    PARTITION BY KEY(s1)
    PARTITIONS 10;

    对于key partition,由于primary key需要同时作为partition key,所以执行alter table ... drop partition会报错(NDB引擎表会重组织并生成隐藏的primary key,不受此限制)。

    mysql> alter table k1 drop primary key;
    ERROR 1488 (HY000): Field in list of fields for partition function not found in table
    -- 主键删除失败

    Key partition和hash partition一样,也有liner key分区。

    CREATE TABLE tk (
        col1 INT NOT NULL,
        col2 CHAR(5),
        col3 DATE
    )
    PARTITION BY LINEAR KEY (col1) -- Liner key partition
    PARTITIONS 3;

    Key partition数量的管理方法与hash partition相同。

    二、subparitioning(子分区)

    subpartitioning可以在原有分区表的基础上,对每个分区再次进行分区(子分区)。子分区的分区类型可以和父分区不同,因此也叫复合分区。

    下面的示例即对range partition的每个分区再次进行hash partition。父分区p0,p1,p2将各包含2个子分区,因此最终分区的数量为3*2=6个

    CREATE TABLE ts (id INT, purchased DATE)
        PARTITION BY RANGE( YEAR(purchased) )        -- 父分区采用range partition
        SUBPARTITION BY HASH( TO_DAYS(purchased) )   -- 子分区采用hash partition
        SUBPARTITIONS 2                              -- 子分区数量为2
        (
            PARTITION p0 VALUES LESS THAN (1990),
            PARTITION p1 VALUES LESS THAN (2000),
            PARTITION p2 VALUES LESS THAN MAXVALUE
        );

    你也可以显示的定义每个子分区的名称,但如果采用这种方式定义,则必须显示指定所有子分区。且子分区的数量必须相同,子分区名称不能重复:

    CREATE TABLE ts (id INT, purchased DATE)
        PARTITION BY RANGE( YEAR(purchased) )         --父分区为range partition
        SUBPARTITION BY HASH( TO_DAYS(purchased) )    --子分区为hash partition
       (
            PARTITION p0 VALUES LESS THAN (1990) (
                SUBPARTITION s0,                      -- 显式指定子分区名称和数量
                SUBPARTITION s1
            ),
            PARTITION p1 VALUES LESS THAN (2000) (
                SUBPARTITION s2,
                SUBPARTITION s3
            ),
            PARTITION p2 VALUES LESS THAN MAXVALUE (
                SUBPARTITION s4,
                SUBPARTITION s5
            )
        );

    三、分区的基本维护

    分区表的一大优势就是各个分区可以独立存储和管理。因此大部分在表级别上的管理操作都可以应该在分区上。

    重建分区:

    重建分区功能相当于清除分区中所有记录,然后在重新插入。对于频繁的更新的分区,可以定期使用重建的方式清除碎片。

    ALTER TABLE employees REBUILD PARTITION p0, p1;  -- 重建p0,p1分区
    ALTER TABLE employees REBUILD PARTITION ALL;     -- 重建所有分区

    检查分区:

    你可以用check table语句来检查指定分区是否存在损坏:

    ALTER TABLE employees CHECK PARTITION p1;  -- 检查p1分区
    ALTER TABLE employees CHECK PARTITION all; -- 检查所有分区

    键值分布统计:

    使用alter table ... analyze partition可以统计指定分区的键值分布,已便更好的生成执行计划:

    ALTER TABLE employees ANALYZE PARTITION p0;  -- 分析p0分区
    ALTER TABLE employees ANALYZE PARTITION ALL; -- 分析所有分区

    分区修复:

    使用alter table ... repair partition来修复损坏的分区:

    ALTER TABLE employees REPAIR PARTITION p0; -- 分析p0分区
    ALTER TABLE employees REPAIR PARTITION ALL; -- 修复所有分区

    优化分区:

    如果分区存在大量的数据更新,你可以使用optimize partition来回收空间,收集统计信息。其效果相当于在分区上执执行check partition、analyze partition 和 repair partition一样。

    ALTER TABLE employees OPTIMIZE PARTITION p0;

    但是对于innodb存储引擎来说,并不支持optimize partition操作,对单一分区执行会导致所有的分区都重建,谨慎使用(Table does not support optimize on partitions. All partitions will be rebuilt and analyzed.)。

     

    展开全文
  • mysql分区表创建思路

    2021-01-18 18:50:11
    mysql分区表有range,list,hash,key,其中每个分区又有特殊的类型。对于RANGE分区,有RANGE COLUMNS分区。对于LIST分区,有LIST COLUMNS分区。对于HASH分区,有LINEAR HASH分区。对于KEY分区,有LINEAR KEY分区。具体...
  • 浅谈MySQL分区表优点和限制

    千次阅读 2021-01-31 07:20:31
    分区是将一个表的数据按照某种方式,逻辑上仍是一个表,也就是所谓的分区表。...作为MySQL数据库中的一个重要机制,MySQL分区表优点和限制也是一目了然的,然而又能够同时实现共存。一、我们先来看MySQL分区...
  • 背景:在工作中遇到MySQL分区表需要新增多个字段的情况,尝试用一下语法,但是半个小时过去了仍然没有反应 alter table test_20220115 add column clk_pv bigint(20) comment '次数' AFTER icon_clk_dev_cnt_1d; ...
  • mysql分区后每个分区成了独立的文件,虽然从逻辑上还是一张其实已经分成了多张独立的,从“information_schema.INNODB_SYS_TABLES”系统可以看到每个分区都存在独立的TABLE_ID,由于Innodb数据和索引都是保存在...
  • MySQL分区表实现按月份归类

    千次阅读 2022-04-22 12:52:38
    最近接手了一个项目,单表数据超7000W行,一条简单的查询语句等了50多分钟都没出结果,实在是难受,最终,我们决定用分区表。 建表 一般的表(innodb)创建后只有一个 idb 文件: create table normal_table(id int...
  • 创建分区表1.首先-(找到原表的创建语句)show create table bitmap_enterprise.activity_pageuser_all;结果如下示例:CREATE TABLE`activity_pageuser_all`(`developerid` int(11) NOT NULL DEFAULT'0',`productid` ...
  • MySQL分区表是一种简单有效的处理极大数据表的特性,通过它可以使应用程序几乎很少改动就能达成对极大数据表的高效处理,但由于 Rails ActiveRecord 设计上一些惯例,可能导致一些数据处理不能利用分区表特性,...
  • 1. 表空间及分区表的概念 1.1 表空间 是一个或多个数据文件的集合,所有的数据对象都存放在指定的表空间中,但主要存放的是表, 所以称作表空间。 1.2 分区表 当表中的数据量不断增大,查询数据的速度就会变慢,应用...
  • Mysql 分区表删除

    千次阅读 2021-01-19 01:22:49
    分区表删除部分分区使用场景:从 MySQL 5.1 开始,支持分区创建日志表时建议使用分区方式use test;drop table if exists t29_log ;CREATE TABLE t29_log (id INT,log_info VARCHAR (100),date datetime) ENGINE = ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 101,985
精华内容 40,794
关键字:

mysql 分区表

mysql 订阅
友情链接: table.zip