返回
编程
分类

表分区的测试使用,这些text和BLOB列又不经常被访问

日期: 2020-01-02 08:15 浏览次数 : 82

十五、mysql 分区之 分区管理,mysql分区

1.mysql分区处理分区字段NULL值的方式
    1.range分区null被当作最小值处理
    2.list分区null值必须被枚举出来,否则将出错
    3.hash/key分区 null值当作0处理

2.RANGE && LIST 分区管理
    1.删除分区
        alter table emp drop partition p0; //删除emp的p0分区,注意也会同时删除该分区的数据
    2.增加分区
        alter table emp add partition (partition p5 values less than(5000));
        //不能增加已存在分区名的分区
        //range分区不能添加比目前最大分区的最大值小的分区,例如目前p4分区最大值为6000,那么p5添加成5000就会报错的
        //list分区不能添加已经在其他分区有枚举数据的分区,例如p4包含了'PHP'这个,那么P5就不能再包含'PHP'了
    3.重定义分区(range)
        alter table  emp reorganize partition p5 into (
            partition p5 values less than (3000),
            partition p6 values less than (4000),
            partition p7 values less than (5000)
        );
        //原本p5分区的最大值为5000,现在将p5拆分成p5,p6,p7 各存放一千
        //拆分分区只能拆成相邻,不能是p5,6,8...
        //拆分分区不能改变原本分区的模式,比如range不能拆成list
    4.重定义分区(list)
        alter table emp reorganize partition p4,p5,p6 into {
            partition p4 values in (1,2,3,4,5,6,7,8,9,10)
        );
        //比如之前的p4p5p6包含了1234567810这10个数字,那么现在全部归纳到p4中来
        //list不支持range的分区功能,但可以通过拆分跟添加来实现重分区

3.HASH && KEY 增加分区
    alter table emp coalesce partition 3; 给emp表删除2个分区 //注意会删除数据
    alter table emp add partition partitions 8 ;//增加8个分区

 

表分区是最近才知道的哦 ,以前自己做都是分表来实现上亿级别的数据了,下面我来给大家介绍一下mysql表分区创建与使用吧,希望对各位同学会有所帮助。
表分区的测试使用,主要内容来自于其他博客文章以及mysql5.1的参考手册
mysql测试版本:mysql5.5.28
mysql物理存储文件(有mysql配置的datadir决定存储路径)格式简介
数据库engine为MYISAM

我们大家都知道MySQL数据库分区属于一种物理的数据库相关设计技术,DBA与MySQL数据库相关人员对其可以说是相当的熟悉。虽然实现分区技术有很多种,但其主要目的是为了在特定的SQL操作中减少数据读写的总量以缩减响应时间。

对于mysql分区与未分区速度与带有主键表分区的问题

分区必威官网亚洲体育 1必威官网亚洲体育 2些优点包括:
1)、与单必威官网亚洲体育 3磁盘或文件系统分区相比必威官网亚洲体育 4必威官网亚洲体育 5必威官网亚洲体育 6存储更多必威官网亚洲体育 7数据必威官网亚洲体育 8
2)、对于必威官网亚洲体育 9些已经失去保存意义必威官网亚洲体育 10数据必威官网亚洲体育 11通常必威官网亚洲体育 12必威官网亚洲体育 13通过删除与必威官网亚洲体育 14些数据有关必威官网亚洲体育 15分区必威官网亚洲体育 16必威官网亚洲体育 17容易地删除必威官网亚洲体育 18些数据必威官网亚洲体育 19
3)、必威官网亚洲体育 20些查询必威官网亚洲体育 21必威官网亚洲体育 22必威官网亚洲体育 23极大必威官网亚洲体育 24优化必威官网亚洲体育 25必威官网亚洲体育 26主要必威官网亚洲体育 27借助于满足必威官网亚洲体育 28必威官网亚洲体育 29给定WHERE语句必威官网亚洲体育 30数据必威官网亚洲体育 31必威官网亚洲体育 32只保存必威官网亚洲体育 33必威官网亚洲体育 34必威官网亚洲体育 35或多必威官网亚洲体育 36分区内必威官网亚洲体育 37必威官网亚洲体育 38必威官网亚洲体育 39查找时必威官网亚洲体育 40必威官网亚洲体育 41用查找其必威官网亚洲体育 42剩余必威官网亚洲体育 43分区必威官网亚洲体育 44必威官网亚洲体育 45分区必威官网亚洲体育 46必威官网亚洲体育 47必威官网亚洲体育 48创建了分区表必威官网亚洲体育 49进行修改必威官网亚洲体育 50必威官网亚洲体育 51必威官网亚洲体育 52必威官网亚洲体育 53次配置分区方案时还必威官网亚洲体育 54必威官网亚洲体育 55必威官网亚洲体育 56做时必威官网亚洲体育 57必威官网亚洲体育 58必威官网亚洲体育 59重新组织数据必威官网亚洲体育 60来提高必威官网亚洲体育 61些常用查询必威官网亚洲体育 62效率必威官网亚洲体育 63
4)、涉及必威官网亚洲体育 64必威官网亚洲体育 65SUM()和COUNT()必威官网亚洲体育 66样聚合函数必威官网亚洲体育 67查询必威官网亚洲体育 68必威官网亚洲体育 69必威官网亚洲体育 70必威官网亚洲体育 71容易地进行并行处理必威官网亚洲体育 72必威官网亚洲体育 73种查询必威官网亚洲体育 74必威官网亚洲体育 75必威官网亚洲体育 76简单例子必威官网亚洲体育 77 必威官网亚洲体育 78SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id;必威官网亚洲体育 79必威官网亚洲体育 80通过必威官网亚洲体育 81并行必威官网亚洲体育 82必威官网亚洲体育 83必威官网亚洲体育 84意味着该查询必威官网亚洲体育 85必威官网亚洲体育 86必威官网亚洲体育 87必威官网亚洲体育 88分区上同时进行必威官网亚洲体育 89必威官网亚洲体育 90终结必威官网亚洲体育 91只需通过总计所有分区得必威官网亚洲体育 92必威官网亚洲体育 93必威官网亚洲体育 94必威官网亚洲体育 95
5)、通过跨多必威官网亚洲体育 96磁盘来分散数据查询必威官网亚洲体育 97来获得更大必威官网亚洲体育 98查询吞吐量必威官网亚洲体育 99

必威官网亚洲体育 100里有必威官网亚洲体育 101篇文章分析必威官网亚洲体育 102非常好必威官网亚洲体育 103请参考:

frm表结构文件,myd表数据文件,myi表索引文件。
INNODB engine对应的表物理存储文件
innodb的数据库的物理文件结构为:
.frm文件
.ibd文件和.ibdata文件:
这两种文件都是存放innodb数据的文件,之所以用两种文件来存放innodb的数据,是因为innodb的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据,还是用独享表空间存放存储数据。
独享表空间存储方式使用.ibd文件,并且每个表一个ibd文件
共享表空间存储方式使用.ibdata文件,所有表共同使用一个ibdata文件
创建分区
分区的一些优点包括:
· 与单个磁盘或文件系统分区相比,可以存储更多的数据。
· 对于那些已经失去保存意义的数据,通常可以通过删除与那些数据有关的分区,很容易地删除那些数据。相反地,在某些情况下,添加新数据的过程又可以通过为那些新数据专门增加一个新的分区,来很方便地实现。
通常和分区有关的其他优点包括下面列出的这些。MySQL 分区中的这些功能目前还没有实现,但是在我们的优先级列表中,具有高的优先级;我们希望在5.1的生产版本中,能包括这些功能。
· 一些查询可以得到极大的优化,这主要是借助于满足一个给定WHERE 语句的数据可以只保存在一个或多个分区内,这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改,所以在第一次配置分区方案时还不曾这么做时,可以重新组织数据,来提高那些常用查询的效率。
·  涉及到例如SUM() 和 COUNT()这样聚合函数的查询,可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT(orders) as order_必威官网亚洲体育 ,total FROM sales GROUP BY salesperson_id;”。通过“并行”, 这意味着该查询可以在每个分区上同时进行,最终结果只需通过总计所有分区得到的结果。
·   通过跨多个磁盘来分散数据查询,来获得更大的查询吞吐量。
简而言之就是 数据管理优化,查询更快,数据查询并行
检测mysql是否支持分区

分区主要有两种形式:

mysql 怎给已有表分区

我也必威官网亚洲体育 104找方法 mysql自从5.1开始必威官网亚洲体育 105必威官网亚洲体育 106必威官网亚洲体育 107表分区了必威官网亚洲体育 108

分区之 分区管理,mysql分区 1. mysql分区处理分区字段NULL值的方式 1. range分区null被当作最小值处理 2. list分区null值必须被枚举出...

复制代码 代码如下:

这里一定要注意行和列的概念row是行,column是列)

mysql> show variables like
"%partition%";
+-------------------+-------+
| Variable_name   | Value |
+-------------------+-------+
| have_partitioning | YES  |
+-------------------+-------+
1 row in set

  1. 水平分区Horizontal Partitioning)这种形式分区是对表的行进行MySQL数据库分区,通过这样的方式不同分组里面的物理列分割的数据集得以组合,从而进行个体分割单分区)或集体分割1个或多个分区)。所有在表中定义的列在每个数据集中都能找到,所以表的特性依然得以保持。

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

举个简单例子:一个包含十年发票记录的表可以被分区为十个不同的分区,每个分区包含的是其中一年的记录。朋奕注:这里具体使用的分区方式我们后面再说,可以先说一点,一定要通过某个属性列来分割,譬如这里使用的列就是年份)

复制代码 代码如下:

  1. 垂直分区Vertical Partitioning) 这种分区方式一般来说是通过对表的垂直划分来减少目标表的宽度,使某些特定的列 被划分到特定的分区,每个分区都包含了其中的列所对应的行。

DROP TABLE IF EXISTS `p_range`;
CREATE TABLE `p_range` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (id)
(PARTITION p0 VALUES LESS THAN (8) ENGINE = MyISAM) */;

举个简单例子:一个包含了大text和BLOB列的表,这些text和BLOB列又不经常被访问,这时候就要把这些不经常使用的text和BLOB了划分到另一个分区,在保证它们数据相关性的同时还能提高访问速度。

range分区就是 partition by range(id) 表示按id 1-7的数据存储在p0分区;如果id大于7了则数据不能写入了,因为没有对应的数据分区来存储;
所以这时在创建分区时需要使用maxvalues关键字了

在数据库供应商开始在他们的数据库引擎中建立MySQL数据库分区主要是水平分区)时,DBA和建模者必须设计好表的物理分区结构,不要保存冗余的数据不同表中同时都包含父表中的数据)或相互联结成一个逻辑父对象通常是视图)。这种做法会使水平分区的大部分功能失效,有时候也会对垂直分区产生影响

复制代码 代码如下:

分区的一些优点包括:

PARTITION BY RANGE (id)
(
PARTITION p0 VALUES LESS THAN (8),
PARTITION p1 VALUES LESS THAN MAXVALUE)

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

这样就表示,所有id大于7的数据记录存在在p1分区里。
RANGE分区在如下场合特别有用:
·  当需要删除“旧的”数据时。如果你使用上面最近的那个例子给出的分区方案,你只需简单地使用 “ALTER TABLE employees DROP PARTITION p0;”来删除所有在1991年前就已经停止工作的雇员相对应的所有行。对于有大量行的表,这比运行一个如“DELETE FROM employees WHERE YEAR(separated) <= 1990;”这样的一个DELETE查询要有效得多。
·  想要使用一个包含有日期或时间值,或包含有从一些其他级数开始增长的值的列。
·  经常运行直接依赖于用于分割表的列的查询。例如,当执行一个如“SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BY store_id;”这样的查询时,MySQL可以很迅速地确定只有分区p2需要扫描,这是因为余下的分区不可能包含有符合该WHERE子句的任何记录。
LIST 分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
list分区可以理解为按一个键的id区间进行数据存储,比如类型表 1,2,3,4的所有记录存储在p0里面,5,6,7,8存在在p1分区里面
这里与range分区一样,如果现在有条记录typeid是9,那么这条记录是不能存入的;
需要注意的是:LIST分区没有类似如“VALUES LESS THAN MAXVALUE”这样的包含其他值在内的定义。将要匹配的任何值都必须在值列表中找到。

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

复制代码 代码如下:

通常和分区有关的其他优点包括下面列出的这些。MySQL数据库分区中的这些功能目前还没有实现,但是在我们的优先级列表中,具有高的优先级;我们希望在5.1的生产版本中,能包括这些功能。

DROP TABLE IF EXISTS `p_list`;
CREATE TABLE `p_list` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`typeid` mediumint(10) NOT NULL DEFAULT '0',
`typename` char(20) DEFAULT NULL,
PRIMARY KEY (`id`,`typeid`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY LIST (typeid)
(PARTITION p0 VALUES IN (1,2,3,4) ENGINE = MyISAM,
PARTITION p1 VALUES IN (5,6,7,8) ENGINE = MyISAM) */;

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

HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中,必须明确指定一个给定的列值或列值集合应该保存在哪个分区中;而在HASH分区中,MySQL 自动完成这些工作,你所要做的只是基于将要被哈希的列值指定一个列值或表达式,以及指定被分区的表将要被分割成的分区数量。
要使用HASH分区来分割一个表,要在CREATE TABLE 语句上添加一个“PARTITION BY HASH (expr)”子句,其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL 整型的一列的名字。此外,你很可能需要在后面再添加一个“PARTITIONS num”子句,其中num 是一个非负的整数,它表示表将要被分割成分区的数量。如果没有包括一个PARTITIONS子句,那么分区的数量将默认为1。

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

复制代码 代码如下:

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

DROP TABLE IF EXISTS `p_hash`;
CREATE TABLE `p_hash` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`storeid` mediumint(10) NOT NULL DEFAULT '0',
`storename` char(255) DEFAULT NULL,
PRIMARY KEY (`id`,`storeid`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (storeid)
PARTITIONS 4 */;

...

InnoDB引擎
简单点说就是数据的存入可以按 partition by hash(expr); 这里的expr可以是键名也可以是表达式比如YEAR(time),如果是表达式的情况下
“但是应当记住,每当插入或更新(或者可能删除)一行,这个表达式都要计算一次;这意味着非常复杂的表达式可能会引起性能问题,尤其是在执行同时影响大量行的运算(例如批量插入)的时候。 ”
在执行删除、写入、更新时这个表达式都会计算一次。
数据的分布采用基于用户函数结果的模数来确定使用哪个编号的分区。换句话,对于一个表达式“expr”,将要保存记录的分区编号为N ,其中“N = MOD(expr, num)”。
比如上面的storeid 为10;那么 N=MOD(10,4) ;N是等于2的,那么这条记录就存储在p2的分区里面。
如果插入一个表达式列值为'2005-09-15′的记录到表中,那么保存该条记录的分区确定如下:MOD(YEAR('2005-09-01′),4)  =  MOD(2005,4)  =  1 ;就存储在p1分区里面了。
“MySQL 5.1 还支持一个被称为“linear hashing(线性哈希功能)”的变量,它使用一个更加复杂的算法来确定新行插入到已经分区了的表中的位置。
线性哈希分区和常规哈希分区在语法上的唯一区别在于,在“PARTITION BY” 子句中添加“LINEAR”关键字;线性哈希功能使用的一个线性的2的幂(powers-of-two)运算法则
按照线性哈希分区的优点在于增加、删除、合并和拆分分区将变得更加快捷,有利于处理含有极其大量(1000GB)数据的表。
它的缺点在于,与使用常规HASH分区得到的数据分布相比,各个分区间数据的分布不大可能均衡。”
KEY 分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

复制代码 代码如下:

DROP TABLE IF EXISTS `p_key`;
CREATE TABLE `p_key` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`keyname` char(20) DEFAULT NULL,
`keyval` varchar(1000) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY KEY (id)
PARTITIONS 4 */;

按照KEY进行分区类似于按照HASH分区,除了HASH分区使用的用户定义的表达式,而KEY分区的 哈希函数是由MySQL 服务器提供。MySQL 簇(Cluster)使用函数MD5()来实现KEY分区;对于使用其他存储引擎的表,服务器使用其自己内部的 哈希函数,这些函数是基于与PASSWORD()一样的运算法则。
“CREATE TABLE … PARTITION BY KEY”的语法规则类似于创建一个通过HASH分区的表的规则。它们唯一的区别在于使用的关键字是KEY而不是HASH,并且KEY分区只采用一个或多个列名的一个列表。
与hash的区别就是,hash使用用户定义的表达式如YEAR(time) ;而key分区则是由mysql服务器提供的。同样KEY也是可以使用linear线性key的,与hash linear是相同的算法。
子分区:是分区表中每个分区的再次分割。

复制代码 代码如下:

DROP TABLE IF EXISTS `p_subpartition`;
CREATE TABLE `p_subpartition` (
`id` int(10) DEFAULT NULL,
`title` char(255) NOT NULL,
`createtime` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
/*!50100
PARTITION BY RANGE (YEAR(createtime))
SUBPARTITION BY HASH (MONTH(createtime))
(PARTITION p0 VALUES LESS THAN (2012)
(SUBPARTITION s1 ENGINE = MyISAM,
SUBPARTITION s2 ENGINE = MyISAM),
PARTITION p1 VALUES LESS THAN (2013)
(SUBPARTITION s3 ENGINE = MyISAM,
SUBPARTITION s4 ENGINE = MyISAM),
PARTITION p2 VALUES LESS THAN MAXVALUE
(SUBPARTITION s5 ENGINE = MyISAM,
SUBPARTITION s6 ENGINE = MyISAM)) */;

可以看到p_subpartition有三个分区p0,p1,p2;而这三个分区每一个又进一步分为2个分区。那么整个表都就分为6个小分区;

可以看到代表p_sobpartitionp0.myd的文件消失了,取代的是p_subpartition

,以前自己做都是分表来实现上亿级别的数据了,下面我来给大家介绍一下mysql表分区创建与使用吧,希望对各位...