MySQL分区表原理的示例分析
发布时间：2022-02-08 10:36:54 所属栏目：MySql教程 来源：互联网
导读：副标题#e# 这篇文章主要为大家展示了MySQL分区表原理的示例分析，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下MySQL分区表原理的示例分析这篇文章吧。 1、分区表含义 分区表定义指根据可以设置为任意大小的规
|
副标题[/!--empirenews.page--]
这篇文章主要为大家展示了“MySQL分区表原理的示例分析”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“MySQL分区表原理的示例分析”这篇文章吧。 1、分区表含义 分区表定义指根据可以设置为任意大小的规则，跨文件系统分配单个表的多个部分。实际上，表的不同部分在不同的位置被存储为单独的表。用户所选择的、实现数据分割的规则被称为分区函数，这在MySQL中它可以是模数，或者是简单的匹配一个连续的数值区间或数值列表，或者是一个内部HASH函数，或一个线性HASH函数。 分表与分区的区别在于：分区从逻辑上来讲只有一张表，而分表则是将一张表分解成多张表 2、分区表优点 1)分区表更容易维护。对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程又可以通过为那些新数据专门增加一个新的分区，来很方便地实现。 2)一些查询可以得到极大的优化，这主要是借助于满足一个给定WHERE语句的数据可以只保存在一个或多个分区内，这样在查找时就不用查找其他剩余的分区。因为分区可以在创建了分区表后进行修改，所以在第一次配置分区方案时还不曾这么做时，可以重新组织数据，来提高那些常用查询的效率。 3)优化查询。涉及到例如SUM()和COUNT(),可以在多个分区上并行处理，最终结果只需通过总计所有分区得到的结果。 4)通过跨多个磁盘来分散数据查询，来获得更大的查询吞吐量。 3、分区表限制 1)一个表最多只能有1024个分区； 2) MySQL5.1中，分区表达式必须是整数，或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持； 3)如果分区字段中有主键或者唯一索引的列，那么多有主键列和唯一索引列都必须包含进来。即：分区字段要么不包含主键或者索引列，要么包含全部主键和索引列； 4)分区表中无法使用外键约束； 5)MySQL的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区，也不能只对索引分区而不对表分区，也不能只对表的一部分数据分区。 6)分区键必须是INT类型，或者通过表达式返回INT类型，可以为NULL。唯一的例外是当分区类型为KEY分区的时候，可以使用其他类型的列作为分区键(BLOB or TEXT 列除外) 7)如果表中有主键和唯一索引，按主键字段进行分区时，唯一索引列应该包含分区键。 8)目前mysql不支持空间类型和临时表类型进行分区。不支持全文索引。 9)对象限制(分区表达式不能出现Stored functions, stored procedures, UDFs, orplugins,Declared variables or user variables.) 10)运算限制(支持加减乘等运算出现在分区表达式，但是运算后的结果必须是一个INT或者NULL。支持DIV,不支持/,|, &, ^, <<, >>, and ~ 不允许出现在分区表达式中) 11)sql_mode限制(官方强烈建议你在创建分区表后，永远别改变mysql的sql_mode。因为在不同的模式下，某些函数或者运算返回的结果可能会不一样) 12)不支持query_cache和INSERT DELAYED 13)分区键不能是一个子查询(即使是子查询返回的是int值或者null.) 14)子分区限制(只有RANG和LIST分区能进行子分区。HASH和KEY分区不能进行子分区并且子分区必须是HASH 或 KEY类型) 4、分区类型 1)水平分区(根据列属性按行分) 如:一个包含十年发票记录的表可以被分区为十个不同的分区，每个分区包含的是其中一年的记录。 水平分区的几种模式： * Range（范围）：这种模式允许DBA将数据划分不同范围。 如:可以将一个表通过年份划分成三个分区，80年代（1980's）的数据，90年代（1990's）的数据以及任何在2000年（包括2000年）后的数据。 * Hash（哈希）：这中模式允许DBA通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。 如：可以建立一个对表主键进行分区的表。 * Key（键值）：上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。 * List（预定义列表）：这种模式允许系统通过DBA定义的列表的值所对应的行数据进行分割。例如：DBA建立了一个横跨三个分区的表，分别根据2004年2005年和2006年值所对应的数据。 * Columns分区是对range，list分区的补充，弥补了后两者只支持整型数分区（或者通过转换为整型数），使得支持数据类型增加很多（所有整数类型，日期时间类型，字符类型），还支持多列分区。 注:在多列分区表上插入数据时，采用元组的比较，即多列排序，先根据field1排序，再根据field2排序，根据排序结果来来分区存储数据。 * Composite（复合模式）：以上模式的组合使用。 如：在初始化已经进行了Range范围分区的表上，可以对其中一个分区再进行hash哈希分区。 垂直分区（按列分）： 如：一个包含了大text和BLOB列的表，这些text和BLOB列又不经常被访问，可以把这些不经常使用的text和BLOB划分到另一个分区，在保证它们数据相关性的同时还能提高访问速度。 注意：子分区（关键字subparttition）：使用RANGE或LIST分区可以再次分割形成子分区，子分区可以是HASH分区或者KEY分区。建议在多磁盘上使用。 查看是否有支持Partition分区表 mysql> SHOW PLUGINS ; +----------------------------+----------+--------------------+---------+---------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+---------+---------+ | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | +----------------------------+----------+--------------------+---------+---------+ 或使用 mysql> SELECT PLUGIN_NAME as Name, PLUGIN_VERSION as Version, PLUGIN_STATUS as Status -> FROM INFORMATION_SCHEMA.PLUGINS -> WHERE PLUGIN_TYPE='STORAGE ENGINE'; 注意:MySQL 5.6.1 之前的版本，可以下命令查看 have_partitioning 参数，新的版本已移除该参数。 mysql> SHOW VARIABLES LIKE '%partition%'; 5、实战常用分区表几种模式 1)使用RANGE分区模式 ####创建测试表t1，并插入接近400万行数据，再没有分区的情况下，对查询某一条件耗时 mysql> CREATE TABLE `t1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主键', `pid` int(10) unsigned NOT NULL COMMENT '产品ID', `price` decimal(15,2) NOT NULL COMMENT '单价', `num` int(11) NOT NULL COMMENT '购买数量', `uid` int(10) unsigned NOT NULL COMMENT '客户ID', `atime` datetime NOT NULL COMMENT '下单时间', `utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '修改时间', `isdel` tinyint(4) NOT NULL DEFAULT '0' COMMENT '软删除标识', PRIMARY KEY (`id`,`atime`) ) INSERT INTO t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP()); INSERT INTO t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2016-05-01 00:00:00'); INSERT INTO t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2017-05-01 00:00:00'); INSERT INTO t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,'2018-05-01 00:00:00'); INSERT INTO t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2015-05-01 00:00:00'); INSERT INTO t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2016-05-01 00:00:00'); INSERT INTO t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2017-05-01 00:00:00'); INSERT INTO t1(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89756,'2018-05-01 00:00:00'); /**********************************主从复制大量数据******************************/ mysql> INSERT INTO `t1`(`pid`,`price`,`num`,`uid`,`atime`) SELECT `pid`,`price`,`num`,`uid`,`atime` FROM `t1`; mysql> SELECT * FROM `t1` WHERE `uid`=89757 AND `atime`< CURRENT_TIMESTAMP(); 1048576 rows in set (5.62 sec) #没有分区表情况耗时5.62s 如果是针对已有的表进行表分区，可以使用ALTER TABLE来进行更改表为分区表，这个操作会创建一个分区表，然后自动进行数据copy然后删除原表。 注: 这种会使服务器资源消耗比较大(400多万数据要1分多钟) mysql> ALTER TABLE t1 PARTITION BY RANGE (YEAR(atime)) -> ( -> PARTITION p0 VALUES LESS THAN (2016), -> PARTITION p1 VALUES LESS THAN (2017), -> PARTITION p2 VALUES LESS THAN (2018), -> PARTITION p3 VALUES LESS THAN MAXVALUE ); Query OK, 4194304 rows affected (1 min 8.32 sec) mysql> EXPLAIN PARTITIONS SELECT * FROM `t1`; #查看分区情况 +----+-------------+-------+-------------+------+---------------+------+---------+------+---------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+-------------+------+---------------+------+---------+------+---------+----------+-------+ | 1 | SIMPLE | t1 | p0,p1,p2,p3 | ALL | NULL | NULL | NULL | NULL | 4180974 | 100.00 | NULL | +----+-------------+-------+-------------+------+---------------+------+---------+------+---------+----------+-------+ 1 row in set, 2 warnings (0.00 sec) 同样用上面的查询测试结果 mysql> SELECT * FROM `t1` WHERE `uid`=89757 AND `atime`< CURRENT_TIMESTAMP(); 1048576 rows in set (4.46 sec) #与上面没有分区查询执行的时间相比少了接近1s mysql> EXPLAIN PARTITIONS SELECT * FROM `t1` WHERE `uid`=89757 AND `atime`< CURRENT_TIMESTAMP(); #查看查询使用的分区情况 +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | 1 | SIMPLE | t1 | p0,p1,p2 | ALL | NULL | NULL | NULL | NULL | 3135804 | 3.33 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+ 1 row in set, 2 warnings (0.00 sec) 同时也要注意，进行表分区以后，mysql存放的数据文件夹中该表的存放文件也被拆分为多个 -rw-r----- 1 mysql mysql 8.7K 2月 14 14:49 t1.frm -rw-r----- 1 mysql mysql 36M 2月 14 14:50 t1#P#p0.ibd -rw-r----- 1 mysql mysql 64M 2月 14 14:50 t1#P#p1.ibd -rw-r----- 1 mysql mysql 92M 2月 14 14:50 t1#P#p2.ibd -rw-r----- 1 mysql mysql 64M 2月 14 14:50 t1#P#p3.ibd 实际生产环境中，大多是采用另外一种方式:新建一个和原来表一样的分区表，然后把数据从原表导出，接着导入新表，最后建立普通索引。 mysql> CREATE TABLE `t2` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主键', `pid` int(10) unsigned NOT NULL COMMENT '产品ID', `price` decimal(15,2) NOT NULL COMMENT '单价', `num` int(11) NOT NULL COMMENT '购买数量', `uid` int(10) unsigned NOT NULL COMMENT '客户ID', `atime` datetime NOT NULL COMMENT '下单时间', `utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '修改时间', `isdel` tinyint(4) NOT NULL DEFAULT '0' COMMENT '软删除标识', PRIMARY KEY (`id`,`atime`) ) PARTITION BY RANGE COLUMNS(atime) ( PARTITION p0 VALUES LESS THAN ('2016-01-01'), PARTITION p1 VALUES LESS THAN ('2016-02-01'), PARTITION p2 VALUES LESS THAN ('2016-03-01'), PARTITION p3 VALUES LESS THAN ('2016-04-01'), PARTITION p4 VALUES LESS THAN ('2016-05-01'), PARTITION p5 VALUES LESS THAN ('2016-06-01'), PARTITION p6 VALUES LESS THAN ('2016-07-01'), PARTITION p7 VALUES LESS THAN ('2016-08-01'), PARTITION p8 VALUES LESS THAN ('2016-09-01'), PARTITION p9 VALUES LESS THAN ('2016-10-01'), PARTITION p10 VALUES LESS THAN ('2016-11-01'), PARTITION p11 VALUES LESS THAN ('2016-12-01'), PARTITION p12 VALUES LESS THAN ('2017-01-01'), PARTITION p13 VALUES LESS THAN ('2017-02-01'), PARTITION p14 VALUES LESS THAN ('2017-03-01'), PARTITION p15 VALUES LESS THAN ('2017-04-01'), PARTITION p16 VALUES LESS THAN ('2017-05-01'), PARTITION p17 VALUES LESS THAN ('2017-06-01'), PARTITION p18 VALUES LESS THAN ('2017-07-01'), PARTITION p19 VALUES LESS THAN ('2017-08-01'), PARTITION p20 VALUES LESS THAN ('2017-09-01'), PARTITION p21 VALUES LESS THAN ('2017-10-01'), PARTITION p22 VALUES LESS THAN ('2017-11-01'), PARTITION p23 VALUES LESS THAN ('2017-12-01'), PARTITION p24 VALUES LESS THAN ('2018-01-01'), PARTITION p25 VALUES LESS THAN ('2018-02-01'), PARTITION p26 VALUES LESS THAN ('2018-03-01'), PARTITION p27 VALUES LESS THAN ('2018-04-01'), PARTITION p28 VALUES LESS THAN ('2018-05-01'), PARTITION p29 VALUES LESS THAN ('2018-06-01'), PARTITION p30 VALUES LESS THAN ('2018-07-01'), PARTITION p31 VALUES LESS THAN ('2018-08-01'), PARTITION p32 VALUES LESS THAN ('2018-09-01'), PARTITION p33 VALUES LESS THAN ('2018-10-01'), PARTITION p34 VALUES LESS THAN ('2018-11-01'), PARTITION p35 VALUES LESS THAN ('2018-12-01'), PARTITION p36 VALUES LESS THAN MAXVALUE ); 注:表主键只有id，而分区字段是atime, 这里主键要修改为 id,stsdate 联合主键，分区表要求分区字段要是主键或者是主键的一部分!!! mysql> EXPLAIN PARTITIONS SELECT * FROM `t2`G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t2 partitions: p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16,p17,p18,p19,p20,p21,p22,p23,p24,p25,p26,p27,p28,p29,p30,p31,p32,p33,p34,p35,p36 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 2 warnings (0.00 sec) *******************************************插入数据************************************************* INSERT INTO `t2`(`pid`,`price`,`num`,`uid`,`atime`) SELECT `pid`,`price`,`num`,`uid`,`atime` FROM `t1`; Query OK, 4194304 rows affected (1 min 18.54 sec) Records: 4194304 Duplicates: 0 Warnings: 0 或采用导出数据再导入数据，可再添加索引 mysqldump -u dbname -p --no-create-info dbname t2 > t2.sq 修改表名，导入数据，测试下ok，删除原来的表。 2)使用LIST分区模式(如果原表存在主键强烈创建新表时，把原主键和要分区字段作为联合主键一并创建) mysql> CREATE TABLE `tb01` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主键', `pid` int(10) unsigned NOT NULL COMMENT '产品ID', `price` decimal(15,2) NOT NULL COMMENT '单价', `num` int(11) NOT NULL COMMENT '购买数量', `uid` int(10) unsigned NOT NULL COMMENT '客户ID', `atime` datetime NOT NULL COMMENT '下单时间', `utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '修改时间', `isdel` tinyint(4) NOT NULL DEFAULT '0' COMMENT '软删除标识', PRIMARY KEY (`id`,`num`) ); *****************************插入测试数据****************************************************** INSERT INTO `tb01`(`pid`,`price`,`num`,`uid`,`atime`) SELECT `pid`,`price`,`num`,`uid`,`atime` FROM `tb`; Query OK, 3145728 rows affected (46.26 sec) Records: 3145728 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE tb01 PARTITION BY LIST(num) ( PARTITION pl01 VALUES IN (1,3), PARTITION pl02 VALUES IN (2,4), PARTITION pl03 VALUES IN (5,7), PARTITION pl04 VALUES IN (6,8), PARTITION pl05 VALUES IN (9,10) ); Query OK, 3145728 rows affected (48.86 sec) Records: 3145728 Duplicates: 0 Warnings: 0 存放mysql数据文件中生成，以下文件 -rw-r----- 1 mysql mysql 8.7K 2月 15 11:35 tb01.frm -rw-r----- 1 mysql mysql 56M 2月 15 11:36 tb01#P#pl01.ibd -rw-r----- 1 mysql mysql 32M 2月 15 11:36 tb01#P#pl02.ibd -rw-r----- 1 mysql mysql 36M 2月 15 11:36 tb01#P#pl03.ibd -rw-r----- 1 mysql mysql 36M 2月 15 11:36 tb01#P#pl04.ibd -rw-r----- 1 mysql mysql 52M 2月 15 11:36 tb01#P#pl05.ibd mysql> EXPLAIN PARTITIONS SELECT * FROM `tb01`; +----+-------------+-------+--------------------------+------+---------------+------+---------+------+---------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+--------------------------+------+---------------+------+---------+------+---------+----------+-------+ | 1 | SIMPLE | tb01 | pl01,pl02,pl03,pl04,pl05 | ALL | NULL | NULL | NULL | NULL | 3136392 | 100.00 | NULL | +----+-------------+-------+--------------------------+------+---------------+------+---------+------+---------+----------+-------+ 1 row in set, 2 warnings (0.00 sec) 3)COLUMNS分区 创建多列分区表tb02，这里两列都不是联合主键 mysql> CREATE TABLE tb02( -> a int not null, -> b int not null -> ) -> 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) -> ); mysql> EXPLAIN PARTITIONS SELECT * FROM `tb02`; #查看 +----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | tb02 | p0,p1,p2,p3 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL | +----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 2 warnings (0.00 sec) mysql> insert into tb02 values (11,13); #手工插入测试数据 Query OK, 1 row affected (0.01 sec) mysql> select PARTITION_NAME,PARTITION_EXPRESSION,TABLE_ROWS from information_schema.partitions where table_schema=schema() and table_name='tb02'; +----------------+----------------------+------------+ | PARTITION_NAME | PARTITION_EXPRESSION | TABLE_ROWS | +----------------+----------------------+------------+ | p0 | `a`,`b` | 0 | | p1 | `a`,`b` | 0 | | p2 | `a`,`b` | 0 | | p3 | `a`,`b` | 1 | +----------------+----------------------+------------+ 4 rows in set (0.03 sec) 4)Hase分区 HASH主要是为了让数据在设定个数的分区中尽可能分布平均，执行哈希分区时，mysql会对分区键执行哈希函数，以确定数据放在哪个分区中。HASH分区分为常规HASH分区和线性HASH分区，前者使用取模算法，后者使用线性2的幂的运算规则。 CREATE TABLE `tb03` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '表主键', `pid` int(10) unsigned NOT NULL COMMENT '产品ID', `price` decimal(15,2) NOT NULL COMMENT '单价', `num` int(11) NOT NULL COMMENT '购买数量', `uid` int(10) unsigned NOT NULL COMMENT '客户ID', `atime` datetime NOT NULL COMMENT '下单时间', `utime` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '修改时间', `isdel` tinyint(4) NOT NULL DEFAULT '0' COMMENT '软删除标识', PRIMARY KEY (`id`) ) PARTITION BY HASH(id) partitions 4; 插入2行数据： INSERT INTO tb03(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP()); INSERT INTO tb03(`pid`,`price`,`num`,`uid`,`atime`) VALUES(1,12.23,1,89757,CURRENT_TIMESTAMP()); mysql> explain partitions select * from tb03 where id=1; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id |& （编辑：包头站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐