创建mysql表分区的方法
编程学习 2021-07-05 15:27www.dzhlxh.cn编程入门
我来给大家介绍一下mysql表分区创建与使用吧，希望对各位同学会有所帮助。表分区的测试使用，主要内容来自于其他博客文章以及mysql5.1的参考手册。
表分区是最近才知道的哦 ，以前自己做都是分表来实现上亿级别的数据了，狼蚁网站SEO优化我来给大家介绍一下mysql表分区创建与使用吧，希望对各位同学会有所帮助。
表分区的测试使用，主要内容来自于其他博客文章以及mysql5.1的参考手册
mysql测试版本：mysql5.5.28
mysql物理存储文件（有mysql配置的datadir决定存储路径）格式简介
数据库engine为MYISAM
frm表结构文件，myd表数据文件，myi表索引文件。
INNODB engine对应的表物理存储文件
innodb的数据库的物理文件结构为：
.frm文件
.ibd文件和.ibdata文件：
这两种文件都是存放innodb数据的文件，之所以用两种文件来存放innodb的数据，是因为innodb的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是用独享表空间存放存储数据。
独享表空间存储方式使用.ibd文件，并且每个表一个ibd文件
共享表空间存储方式使用.ibdata文件，所有表共同使用一个ibdata文件
创建分区
分区的一些优点包括：
· 与单个磁盘或文件系统分区相比，可以存储更多的数据。
· 对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。
通常和分区有关的其他优点包括狼蚁网站SEO优化列出的这些。MySQL 分区中的这些功能目前还没有实现，但是在我们的优先级列表中，具有高的优先级；我们希望在5.1的生产版本中，能包括这些功能。
· 一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE 语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。
· 涉及到例如SUM() 和 COUNT()这样聚合函数的查询，可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT(orders) as order_total FROM sales GROUP BY salesperson_id；”。通过“并行”， 这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。
· 通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。
简而言之就是 数据管理优化，查询更快，数据查询并行
检测mysql是否支持分区
mysql> show variables like
"%partition%";
+-------------------+-------+
| Variable_name   | Value |
+-------------------+-------+
| have_partitioning | YES  |
+-------------------+-------+
1 row in set
RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区。
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) */;
range分区就是 partition by range(id) 表示按id 1-7的数据存储在p0分区；如果id大于7了则数据不能写入了，因为没有对应的数据分区来存储；
所以这时在创建分区时需要使用maxvalues关键字了
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”这样的包含其他值在内的定义。将要匹配的任何值都必须在值列表中找到。
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) */;
HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在HASH分区中，MySQL 自动完成这些工作，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。
要使用HASH分区来分割一个表，要在CREATE TABLE 语句上添加一个“PARTITION BY HASH (expr)”子句，其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL 整型的一列的名字。此外，你很可能需要在后面再添加一个“PARTITIONS num”子句，其中num 是一个非负的整数，它表示表将要被分割成分区的数量。如果没有包括一个PARTITIONS子句，那么分区的数量将默认为1。
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
表分区的测试使用，主要内容来自于其他博客文章以及mysql5.1的参考手册
mysql测试版本：mysql5.5.28
mysql物理存储文件（有mysql配置的datadir决定存储路径）格式简介
数据库engine为MYISAM
frm表结构文件，myd表数据文件，myi表索引文件。
INNODB engine对应的表物理存储文件
innodb的数据库的物理文件结构为：
.frm文件
.ibd文件和.ibdata文件：
这两种文件都是存放innodb数据的文件，之所以用两种文件来存放innodb的数据，是因为innodb的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是用独享表空间存放存储数据。
独享表空间存储方式使用.ibd文件，并且每个表一个ibd文件
共享表空间存储方式使用.ibdata文件，所有表共同使用一个ibdata文件
创建分区
分区的一些优点包括：
· 与单个磁盘或文件系统分区相比，可以存储更多的数据。
· 对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。
通常和分区有关的其他优点包括狼蚁网站SEO优化列出的这些。MySQL 分区中的这些功能目前还没有实现，但是在我们的优先级列表中，具有高的优先级；我们希望在5.1的生产版本中，能包括这些功能。
· 一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE 语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。
· 涉及到例如SUM() 和 COUNT()这样聚合函数的查询，可以很容易地进行并行处理。这种查询的一个简单例子如 “SELECT salesperson_id, COUNT(orders) as order_total FROM sales GROUP BY salesperson_id；”。通过“并行”， 这意味着该查询可以在每个分区上同时进行，最终结果只需通过总计所有分区得到的结果。
· 通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。
简而言之就是 数据管理优化，查询更快，数据查询并行
检测mysql是否支持分区
代码如下:
mysql> show variables like
"%partition%";
+-------------------+-------+
| Variable_name   | Value |
+-------------------+-------+
| have_partitioning | YES  |
+-------------------+-------+
1 row in set
RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区。
代码如下:
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) */;
range分区就是 partition by range(id) 表示按id 1-7的数据存储在p0分区；如果id大于7了则数据不能写入了，因为没有对应的数据分区来存储；
所以这时在创建分区时需要使用maxvalues关键字了
代码如下:
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”这样的包含其他值在内的定义。将要匹配的任何值都必须在值列表中找到。
代码如下:
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) */;
HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
HASH分区主要用来确保数据在预先确定数目的分区中平均分布。在RANGE和LIST分区中，必须明确指定一个给定的列值或列值集合应该保存在哪个分区中；而在HASH分区中，MySQL 自动完成这些工作，你所要做的只是基于将要被哈希的列值指定一个列值或表达式，以及指定被分区的表将要被分割成的分区数量。
要使用HASH分区来分割一个表，要在CREATE TABLE 语句上添加一个“PARTITION BY HASH (expr)”子句，其中“expr”是一个返回一个整数的表达式。它可以仅仅是字段类型为MySQL 整型的一列的名字。此外，你很可能需要在后面再添加一个“PARTITIONS num”子句，其中num 是一个非负的整数，它表示表将要被分割成分区的数量。如果没有包括一个PARTITIONS子句，那么分区的数量将默认为1。
代码如下:
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-5.5.28源码安装过程中错误总结
下一篇：浅析MySQL之字符串函数