mysql的表分区partition

最新推荐文章于 2024-06-22 19:36:08 发布

qq_43382364

最新推荐文章于 2024-06-22 19:36:08 发布

阅读量496

点赞数

分类专栏： mysql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_43382364/article/details/104881981

版权

mysql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

表分区 partition
当一张表的数据非常多的时候,比如单个.myd文件都达到10G, 这时,必然读取起来效率降低.
可不可以把表的数据分开在几张表上?

1: 从业务角度可以解决. (分表)
比如, 通过id%10 , user0 , user1…user9, 这10张表
根据不同的余数,来插入或查询某张表.

2: 通过mysql的分区功能
mysql将会根据指定的规则,把数据放在不同的表文件上.
相当于在文件上,被拆成了小块.
但是,给客户的界面,还是1张表.

#使用tid的范围进行分区，
#t0小于10，t1小于20，t2小最大值
create table topic(
    tid int primary key auto_increment,
    title char(20) not null default '')
    engine myisam charset utf8
    partition by range(tid)(
    partition t0 values less than(10),
    partition t1 values less than(20),
    partition t2 values less than(MAXVALUE));

MYD是数据文件，MYI是索引结构
在这里插入图片描述
按地区分

#地区表
 create table area(
    aid int,
    zone varchar(6)
    )engine myisam charset utf8;
 #插入数据
insert into area values(1,'bj'),(2,'hb'),(3,'sx'),(4,'ah');
 #用户表按地区分表
 #list分区
 create table user(
     uid int,
     uname char(6),
     aid int)
     engine myisam charset utf8
     partition by list(aid)(
     partition bj values in (1),
     partition hb values in (2),
     partition xs values in (3),
     partition ah values in (4));

注意: 在使用分区的时候,注意,分区的那个列,值不要为NULL
(如果不小心为NULL,mysql为理解为0,尽量执行之)

注 :分区甚至可以按照表达式的返回值,计算所属区.
但用表达式,不如直接用值来得快. 根据情况而定.

比如,用 partition by range (year(regtime)) 可以按注册年份来分区

1. RANGE分区

按照RANGE分区的表是通过如下一种方式进行分区的，每个分区包含那些分区表达式的值位于一个给定的连续区间内的行。这些区间要连续且不能相互重叠，使用VALUES LESS THAN操作符来进行定义。在下面的几个例子中，假定你创建了一个如下的一个表，该表保存有20家音像店的职员记录，这20家音像店的编号从1到20。

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,
store_id INT NOT NULL
)
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 MAXVALUE
)；
MAXVALUE 表示最大的可能的整数值。现在，store_id 列值大于或等于16（定义了的最高值）的所有行都将保存在分区p3中。在将来的某个时候，当商店数已经增长到25, 30, 或更多，可以使用ALTER TABLE语句为商店21-25, 26-30,等等增加新的分区（关于如何实现的详细信息参见18.3节，“分区管理” )。

在VALUES LESS THAN 子句中使用一个表达式也是可能的。这里最值得注意的限制是MySQL 必须能够计算表达式的返回值作为LESS THAN (<)比较的一部分；因此，表达式的值不能为NULL 。由于这个原因，雇员表的hired, separated, job_code,和store_id列已经被定义为非空（NOT NULL）。
除了可以根据商店编号分割表数据外，你还可以使用一个基于两个DATE （日期）中的一个的表达式来分割表数据。例如，假定你想基于每个雇员离开公司的年份来分割表，也就是说，YEAR(separated)的值。实现这种分区模式的CREATE TABLE 语句的一个例子如下所示：
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,
store_id INT
)
PARTITION BY RANGE (YEAR(separated)) (
PARTITION p0 VALUES LESS THAN (1991),
PARTITION p1 VALUES LESS THAN (1996),
PARTITION p2 VALUES LESS THAN (2001),
PARTITION p3 VALUES LESS THAN MAXVALUE
)；

2. LIST分区

MySQL中的LIST分区在很多方面类似于RANGE分区。和按照RANGE分区一样，每个分区必须明确定义。它们的主要区别在于，LIST分区中每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值，而RANGE分区是从属于一个连续区间值的集合。LIST分区通过使用“PARTITION BY LIST(expr)”来实现，其中“expr” 是某列值或一个基于某个列值、并返回一个整数值的表达式，然后通过“VALUES IN (value_list)”的方式来定义每个分区，其中“value_list”是一个通过逗号分隔的整数列表。
注释：在MySQL 5.1中，当使用LIST分区时，有可能只能匹配整数列表。
不像按照RANGE定义分区的情形，LIST分区不必声明任何特定的顺序。关于LIST分区更详细的语法信息，请参考13.1.5节，“CREATE TABLE语法” 。
对于下面给出的例子，我们假定将要被分区的表的基本定义是通过下面的“CREATE TABLE”语句提供的：
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,
store_id INT
)；
（这和18.2.1节，“RANGE分区” 中的例子中使用的是同一个表）。
假定有20个音像店，分布在4个有经销权的地区，如下表所示：
地区
商店ID 号

北区
3, 5, 6, 9, 17

东区
1, 2, 10, 11, 19, 20

西区
4, 12, 13, 14, 18

中心区
7, 8, 15, 16

要按照属于同一个地区商店的行保存在同一个分区中的方式来分割表，可以使用下面的“CREATE TABLE”语句：
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,
store_id INT
)
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)
)；

要点：如果试图插入列值（或分区表达式的返回值）不在分区值列表中的一行时，那么“INSERT”查询将失败并报错。例如，假定LIST分区的采用上面的方案，下面的查询将失败：
INSERT INTO employees VALUES
(224, ‘Linus’, ‘Torvalds’, ‘2002-05-01’, ‘2004-10-12’, 42, 21);
这是因为“store_id”列值21不能在用于定义分区pNorth, pEast, pWest,或pCentral的值列表中找到。要重点注意的是，LIST分区没有类似如“VALUES LESS THAN MAXVALUE”这样的包含其他值在内的定义。将要匹配的任何值都必须在值列表中找到。
LIST分区除了能和RANGE分区结合起来生成一个复合的子分区，与HASH和KEY分区结合起来生成复合的子分区也是可能的。

3. HASH分区

18.2.3.1. LINEAR HASH分区
HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在HASH分区中，MySQL 自动完成这些工作，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。
要使用HASH分区来分割一个表，要在CREATE TABLE 语句上添加一个“PARTITION BY HASH (expr)”子句，其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL 整型的一列的名字。此外，你很可能需要在后面再添加一个“PARTITIONS num”子句，其中num 是一个非负的整数，它表示表将要被分割成分区的数量。
例如，下面的语句创建了一个使用基于“store_id”列进行哈希处理的表，该表被分成了4个分区：
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,
store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4；
如果没有包括一个PARTITIONS子句，那么分区的数量将默认为1。例外：对于NDB Cluster（簇）表，默认的分区数量将与簇数据节点的数量相同，这种修正可能是考虑任何MAX_ROWS 设置，以便确保所有的行都能合适地插入到分区中。（参见第17章：MySQL簇）。
如果在关键字“PARTITIONS”后面没有加上分区的数量，将会出现语法错误。
“expr”还可以是一个返回一个整数的SQL表达式。例如，也许你想基于雇用雇员的年份来进行分区。这可以通过下面的语句来实现：
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,
store_id INT
)
PARTITION BY HASH(YEAR(hired))
PARTITIONS 4；
“expr”还可以是MySQL 中有效的任何函数或其他表达式，只要它们返回一个既非常数、也非随机数的整数。（换句话说，它既是变化的但又是确定的）。但是应当记住，每当插入或更新（或者可能删除）一行，这个表达式都要计算一次；这意味着非常复杂的表达式可能会引起性能问题，尤其是在执行同时影响大量行的运算（例如批量插入）的时候。
最有效率的哈希函数是只对单个表列进行计算，并且它的值随列值进行一致地增大或减小，因为这考虑了在分区范围上的“修剪”。也就是说，表达式值和它所基于的列的值变化越接近，MySQL就可以越有效地使用该表达式来进行HASH分区。
例如，“date_col” 是一个DATE（日期）类型的列，那么表达式TO_DAYS(date_col)就可以说是随列“date_col”值的变化而发生直接的变化，因为列“date_col”值的每个变化，表达式的值也将发生与之一致的变化。而表达式YEAR(date_col)的变化就没有表达式TO_DAYS(date_col)那么直接，因为不是列“date_col”每次可能的改变都能使表达式YEAR(date_col)发生同等的改变。即便如此，表达式YEAR(date_col)也还是一个用于哈希函数的、好的候选表达式，因为它随列date_col的一部分发生直接变化，并且列date_col的变化不可能引起表达式YEAR(date_col)不成比例的变化。
作为对照，假定有一个类型为整型（INT）的、列名为“int_col”的列。现在考虑表达式“POW(5-int_col,3) + 6”。这对于哈希函数就是一个不好的选择，因为“int_col”值的变化并不能保证表达式产生成比例的变化。列 “int_col”的值发生一个给定数目的变化，可能会引起表达式的值产生一个很大不同的变化。例如，把列“int_col”的值从5变为6，表达式的值将产生“－1”的改变，但是把列“int_col”的值从6变为7时，表达式的值将产生“－7”的变化。
换句话说，如果列值与表达式值之比的曲线图越接近由等式“y=nx（其中n为非零的常数）描绘出的直线，则该表达式越适合于哈希。这是因为，表达式的非线性越严重，分区中数据产生非均衡分布的趋势也将越严重。
理论上讲，对于涉及到多列的表达式，“修剪（pruning）”也是可能的，但是要确定哪些适于哈希是非常困难和耗时的。基于这个原因，实际上不推荐使用涉及到多列的哈希表达式。
当使用了“PARTITION BY HASH”时，MySQL将基于用户函数结果的模数来确定使用哪个编号的分区。换句话，对于一个表达式“expr”，将要保存记录的分区编号为N ，其中“N = MOD(expr, num)”。例如，假定表t1 定义如下，它有4个分区：
CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
PARTITION BY HASH( YEAR(col3) )
PARTITIONS 4；
如果插入一个col3列值为’2005-09-15’的记录到表t1中，那么保存该条记录的分区确定如下：
MOD(YEAR(‘2005-09-01’),4)
= MOD(2005,4)
= 1

参考博客：https://blog.csdn.net/y19910825/article/details/94742173

qq_43382364

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql的表分区partition

表分区 partition当一张表的数据非常多的时候,比如单个.myd文件都达到10G, 这时,必然读取起来效率降低.可不可以把表的数据分开在几张表上?1: 从业务角度可以解决. (分表)比如, 通过id%10 , user0 , user1…user9, 这10张表根据不同的余数,来插入或查询某张表.2: 通过mysql的分区功能mysql将会根据指定的规则,把数据放在不同的表文件...
复制链接

扫一扫

专栏目录