如何深入解析MySQL分区Partition功能

网友投稿 271 2023-12-25

如何深入解析MySQL分区Partition功能

这篇文章将为大家详细讲解有关如何深入解析MySQL分区Partition功能，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。

自5.1开始对分区(Partition)有支持

= 水平分区（根据列属性按行分）=

举个简单例子：一个包含十年发票记录的表可以被分区为十个不同的分区，每个分区包含的是其中一年的记录。

=== 水平分区的几种模式：===

* Range（范围）

– 这种模式允许DBA将数据划分不同范围。例如DBA可以将一个表通过年份划分成三个分区，80年代（1980s）的数据，90年代（1990s）的数据以及任何在2000年（包括2000年）后的数据。

* Hash（哈希）

– 这中模式允许DBA通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区，。例如DBA可以建立一个对表主键进行分区的表。

* Key（键值）

– 上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。

* List（预定义列表）

– 这种模式允许系统通过DBA定义的列表的值所对应的行数据进行分割。例如：DBA建立了一个横跨三个分区的表，分别根据2004年2005年和2006年值所对应的数据。

* Composite（复合模式）

- 很神秘吧，哈哈，其实是以上模式的组合使用而已，就不解释了。举例：在初始化已经进行了Range范围分区的表上，我们可以对其中一个分区再进行hash哈希分区。

= 垂直分区（按列分）=

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

[分区表和未分区表试验过程]

*创建分区表,按日期的年份拆分

[sql] view plain copy

mysql> CREATE TABLE part_tab ( c1 int default NULL, c2 varchar(30) default NULL, c3 date default NULL) engine=myisam

PARTITION BY RANGE (year(c3)) (PARTITION p0 VALUES LESS THAN (1995),

PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) ,

PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) ,

PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) ,

PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) ,

PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010),

PARTITION p11 VALUES LESS THAN MAXVALUE );

注意最后一行，考虑到可能的最大值

*创建未分区表

[sql] view plain copy

mysql> create table no_part_tab (c1 int(11) default NULL,c2 varchar(30) default NULL,c3 date default NULL) engine=myisam;

*通过存储过程灌入800万条测试数据

mysql> set sql_mode=; /* 如果创建存储过程失败，则先需设置此变量, bug? */

MySQL> delimiter // /* 设定语句终结符为 //，因存储过程语句用;结束 */

[sql] view plain copy

mysql> CREATE PROCEDURE load_part_tab()

begin

declare v int default 0;

while v < 8000000

insert into part_tab

values (v,testing partitions,adddate(1995-01-01,(rand(v)*36520) mod 3652));

set v = v + 1;

end while;

end

mysql> delimiter ;

mysql> call load_part_tab();

Query OK, 1 row affected (8 min 17.75 sec)

[sql] view plain copy

mysql> insert into no_part_tab select * from part_tab;

Query OK, 8000000 rows affected (51.59 sec)

Records: 8000000 Duplicates: 0 Warnings: 0

* 测试SQL性能

[sql] view plain copy

mysql> select count(*) from part_tab where c3 > date 1995-01-01 and c3 < date 1995-12-31;

+----------+

| count(*) |

+----------+

| 795181 |

+----------+

1 row in set (0.55 sec)

[sql]view plain copy

mysql> select count(*) from no_part_tab where c3 > date 1995-01-01 and c3 < date 1995-12-31;

+----------+

| count(*) |

+----------+

| 795181 |

+----------+

1 row in set (4.69 sec)

结果表明分区表比未分区表的执行时间少90%。

* 通过explain语句来分析执行情况

[sql] view plain copy

mysql > explain select count(*) from no_part_tab where c3 > date 1995-01-01 and c3 < date 1995-12-31\G

/* 结尾的\G使得mysql的输出改为列模式 */

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: no_part_tab

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 8000000

Extra: Using where

1 row in set (0.00 sec)

[sql]view plain copy

mysql> explain select count(*) from part_tab where c3 > date 1995-01-01 and c3 < date 1995-12-31\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: part_tab

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 798458

Extra: Using where

1 row in set (0.00 sec)

explain语句显示了SQL查询要处理的记录数目

* 试验创建索引后情况

[sql] view plain copy

mysql> create index idx_of_c3 on no_part_tab (c3);

Query OK, 8000000 rows affected (1 min 18.08 sec)

Records: 8000000 Duplicates: 0 Warnings: 0

[sql] view plain copy

mysql> create index idx_of_c3 on part_tab (c3);

Query OK, 8000000 rows affected (1 min 19.19 sec)

Records: 8000000 Duplicates: 0 Warnings: 0

创建索引后的数据库文件大小列表：

2008-05-24 09:23 8,608 no_part_tab.frm

2008-05-24 09:24 255,999,996 no_part_tab.MYD

2008-05-24 09:24 81,611,776 no_part_tab.MYI

2008-05-24 09:25 0 part_tab#P#p0.MYD

2008-05-24 09:26 1,024 part_tab#P#p0.MYI

2008-05-24 09:26 25,550,656 part_tab#P#p1.MYD

2008-05-24 09:26 8,148,992 part_tab#P#p1.MYI

2008-05-24 09:26 25,620,192 part_tab#P#p10.MYD

2008-05-24 09:26 8,170,496 part_tab#P#p10.MYI

2008-05-24 09:25 0 part_tab#P#p11.MYD

2008-05-24 09:26 1,024 part_tab#P#p11.MYI

2008-05-24 09:26 25,656,512 part_tab#P#p2.MYD

2008-05-24 09:26 8,181,760 part_tab#P#p2.MYI

2008-05-24 09:26 25,586,880 part_tab#P#p3.MYD

2008-05-24 09:26 8,160,256 part_tab#P#p3.MYI

2008-05-24 09:26 25,585,696 part_tab#P#p4.MYD

2008-05-24 09:26 8,159,232 part_tab#P#p4.MYI

2008-05-24 09:26 25,585,216 part_tab#P#p5.MYD

2008-05-24 09:26 8,159,232 part_tab#P#p5.MYI

2008-05-24 09:26 25,655,740 part_tab#P#p6.MYD

2008-05-24 09:26 8,181,760 part_tab#P#p6.MYI

2008-05-24 09:26 25,586,528 part_tab#P#p7.MYD

2008-05-24 09:26 8,160,256 part_tab#P#p7.MYI

2008-05-24 09:26 25,586,752 part_tab#P#p8.MYD

2008-05-24 09:26 8,160,256 part_tab#P#p8.MYI

2008-05-24 09:26 25,585,824 part_tab#P#p9.MYD

2008-05-24 09:26 8,159,232 part_tab#P#p9.MYI

2008-05-24 09:25 8,608 part_tab.frm

2008-05-24 09:25 68 part_tab.par

* 再次测试SQL性能

[sql]view plain copy

mysql> select count(*) from no_part_tab where c3 > date 1995-01-01 and c3 < date 1995-12-31;

+----------+

| count(*) |

+----------+

| 795181 |

+----------+

1 row in set (2.42 sec) /* 为原来4.69 sec 的51%*/

重启mysql ( net stop mysql, net start mysql)后，查询时间降为0.89 sec,几乎与分区表相同。

[sql] view plain copy

mysql> select count(*) from part_tab where c3 > date 1995-01-01 and c3 < date 1995-12-31;

+----------+

| count(*) |

+----------+

| 795181 |

+----------+

1 row in set (0.86 sec)

* 更进一步的试验

** 增加日期范围

[sql]view plain copy

mysql> select count(*) from no_part_tab where c3 > date 1995-01-01 and c3 < date 1997-12-31;

+----------+

| count(*) |

+----------+

| 2396524 |

+----------+

1 row in set (5.42 sec)

[sql] view plaincopy

mysql> select count(*) from part_tab where c3 > date 1995-01-01 and c3 < date 1997-12-31;

+----------+

| count(*) |

+----------+

| 2396524 |

+----------+

1 row in set (2.63 sec)

** 增加未索引字段查询

[sql] view plaincopy

mysql> select count(*) from part_tab where c3 > date 1995-01-01 and c3 < date

1996-12-31 and c2=hello;

+----------+

| count(*) |

+----------+

| 0 |

+----------+

1 row in set (0.75 sec)

[sql]view plain copy

mysql> select count(*) from no_part_tab where c3 > date 1995-01-01 and c3 < date 1996-12-31 and c2=hello;

+----------+

| count(*) |

+----------+

| 0 |

+----------+

1 row in set (11.52 sec)

= 初步结论 =

* 分区和未分区占用文件空间大致相同（数据和索引文件）

* 如果查询语句中有未建立索引字段，分区时间远远优于未分区时间

* 如果查询语句中字段建立了索引，分区和未分区的差别缩小，分区略优于未分区。

= 最终结论 =

* 对于大数据量，建议使用分区功能。

* 去除不必要的字段

* 根据手册，增加myisam_max_sort_file_size 会增加分区性能

[分区命令详解]

= 分区例子 =

* RANGE 类型

[sql] view plain copy

CREATE TABLE users (

uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(30) NOT NULL DEFAULT ,

email VARCHAR(30) NOT NULL DEFAULT

)

PARTITION BY RANGE (uid) (

PARTITION p0 VALUES LESS THAN (3000000)

DATA DIRECTORY = /data0/data

INDEX DIRECTORY = /data1/idx,

PARTITION p1 VALUES LESS THAN (6000000)

DATA DIRECTORY = /data2/data

INDEX DIRECTORY = /data3/idx,

PARTITION p2 VALUES LESS THAN (9000000)

DATA DIRECTORY = /data4/data

INDEX DIRECTORY = /data5/idx,

PARTITION p3 VALUES LESS THAN MAXVALUE DATA DIRECTORY = /data6/data

INDEX DIRECTORY = /data7/idx

);

在这里，将用户表分成4个分区，以每300万条记录为界限，每个分区都有自己独立的数据、索引文件的存放目录，与此同时，这些目录所在的物理磁盘分区可能也都是完全独立的，可以提高磁盘IO吞吐量。

* LIST 类型

[sql] view plaincopy

CREATE TABLE category (

cid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(30) NOT NULL DEFAULT

)

PARTITION BY LIST (cid) (

PARTITION p0 VALUES IN (0,4,8,12)

DATA DIRECTORY = /data0/data

INDEX DIRECTORY = /data1/idx,

PARTITION p1 VALUES IN (1,5,9,13)

DATA DIRECTORY = /data2/data

INDEX DIRECTORY = /data3/idx,

PARTITION p2 VALUES IN (2,6,10,14)

DATA DIRECTORY = /data4/data

INDEX DIRECTORY = /data5/idx,

PARTITION p3 VALUES IN (3,7,11,15)

DATA DIRECTORY = /data6/data

INDEX DIRECTORY = /data7/idx

);

分成4个区，数据文件和索引文件单独存放。

* HASH 类型

[sql] view plain copy

CREATE TABLE users (

uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(30) NOT NULL DEFAULT ,

email VARCHAR(30) NOT NULL DEFAULT

)

PARTITION BY HASH (uid) PARTITIONS 4 (

PARTITION p0

DATA DIRECTORY = /data0/data

INDEX DIRECTORY = /data1/idx,

PARTITION p1

DATA DIRECTORY = /data2/data

INDEX DIRECTORY = /data3/idx,

PARTITION p2

DATA DIRECTORY = /data4/data

INDEX DIRECTORY = /data5/idx,

PARTITION p3

DATA DIRECTORY = /data6/data

INDEX DIRECTORY = /data7/idx

);

分成4个区，数据文件和索引文件单独存放。

例子：

[sql] view plain copy

CREATE TABLE ti2 (id INT, amount DECIMAL(7,2), tr_date DATE)

ENGINE=myisam

PARTITION BY HASH( MONTH(tr_date) )

PARTITIONS 6;

CREATE PROCEDURE load_ti2()

begin

declare v int default 0;

while v < 80000

insert into ti2

values (v,3.14,adddate(1995-01-01,(rand(v)*3652) mod 365));

set v = v + 1;

end while;

end

* KEY 类型

[sql] view plain copy

CREATE TABLE users (

uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(30) NOT NULL DEFAULT ,

email VARCHAR(30) NOT NULL DEFAULT

)

PARTITION BY KEY (uid) PARTITIONS 4 (

PARTITION p0

DATA DIRECTORY = /data0/data

INDEX DIRECTORY = /data1/idx,

PARTITION p1

DATA DIRECTORY = /data2/data

INDEX DIRECTORY = /data3/idx,

PARTITION p2

DATA DIRECTORY = /data4/data

INDEX DIRECTORY = /data5/idx,

PARTITION p3

DATA DIRECTORY = /data6/data

INDEX DIRECTORY = /data7/idx

);

分成4个区，数据文件和索引文件单独存放。

* 子分区

子分区是针对 RANGE/LIST 类型的分区表中每个分区的再次分割。再次分割可以是 HASH/KEY 等类型。例如：

[sql] view plain copy

CREATE TABLE users (

uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(30) NOT NULL DEFAULT ,

email VARCHAR(30) NOT NULL DEFAULT

)

PARTITION BY RANGE (uid) SUBPARTITION BY HASH (uid % 4) SUBPARTITIONS 2(

PARTITION p0 VALUES LESS THAN (3000000)

DATA DIRECTORY = /data0/data

INDEX DIRECTORY = /data1/idx,

PARTITION p1 VALUES LESS THAN (6000000)

DATA DIRECTORY = /data2/data

INDEX DIRECTORY = /data3/idx

);

对 RANGE 分区再次进行子分区划分，子分区采用 HASH 类型。

或者

[sql] view plain copy

CREATE TABLE users (

uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(30) NOT NULL DEFAULT ,

email VARCHAR(30) NOT NULL DEFAULT

)

PARTITION BY RANGE (uid) SUBPARTITION BY KEY(uid) SUBPARTITIONS 2(

PARTITION p0 VALUES LESS THAN (3000000)

DATA DIRECTORY = /data0/data

INDEX DIRECTORY = /data1/idx,

PARTITION p1 VALUES LESS THAN (6000000)

DATA DIRECTORY = /data2/data

INDEX DIRECTORY = /data3/idx

);

对 RANGE 分区再次进行子分区划分，子分区采用 KEY 类型。

= 分区管理 =

* 删除分区

[sql] view plain copy

ALERT TABLE users DROP PARTITION p0;

删除分区 p0。

* 重建分区

o RANGE 分区重建

[sql] view plain copy

ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES LESS THAN (6000000));

将原来的 p0,p1 分区合并起来，放到新的 p0 分区中。

o LIST 分区重建

[sql] view plain copy

ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES IN(0,1,4,5,8,9,12,13));

将原来的 p0,p1 分区合并起来，放到新的 p0 分区中。

o HASH/KEY 分区重建

[sql] view plain copy

ALTER TABLE users REORGANIZE PARTITION COALESCE PARTITION 2;

用 REORGANIZE 方式重建分区的数量变成2，在这里数量只能减少不能增加。想要增加可以用 ADD PARTITION 方法。

* 新增分区

o 新增 RANGE 分区

[sql] view plain copy

ALTER TABLE category ADD PARTITION (PARTITION p4 VALUES IN (16,17,18,19)

DATA DIRECTORY = /data8/data

INDEX DIRECTORY = /data9/idx);

新增一个RANGE分区。

o 新增 HASH/KEY 分区

[sql] view plain copy

ALTER TABLE users ADD PARTITION PARTITIONS 8;

将分区总数扩展到8个。

[ 给已有的表加上分区 ]

[sql] view plain copy

alter table results partition by RANGE (month(ttime))

(PARTITION p0 VALUES LESS THAN (1),

PARTITION p1 VALUES LESS THAN (2) , PARTITION p2 VALUES LESS THAN (3) ,

PARTITION p3 VALUES LESS THAN (4) , PARTITION p4 VALUES LESS THAN (5) ,

PARTITION p5 VALUES LESS THAN (6) , PARTITION p6 VALUES LESS THAN (7) ,

PARTITION p7 VALUES LESS THAN (8) , PARTITION p8 VALUES LESS THAN (9) ,

PARTITION p9 VALUES LESS THAN (10) , PARTITION p10 VALUES LESS THAN (11),

PARTITION p11 VALUES LESS THAN (12),

PARTITION P12 VALUES LESS THAN (13) );

默认分区限制分区字段必须是主键（PRIMARY KEY)的一部分，为了去除此

限制：

[方法1] 使用ID

[sql] view plain copy

mysql> ALTER TABLE np_pk

-> PARTITION BY HASH( TO_DAYS(added) )

-> PARTITIONS 4;

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the tables partitioning function

However, this statement using the id column for the partitioning column is valid, as shown here:

[sql] view plain copy

mysql> ALTER TABLE np_pk

-> PARTITION BY HASH(id)

-> PARTITIONS 4;

Query OK, 0 rows affected (0.11 sec)

Records: 0 Duplicates: 0 Warnings: 0

[方法2] 将原有PK去掉生成新PK

[sql] view plain copy

mysql> alter table results drop PRIMARY KEY;

Query OK, 5374850 rows affected (7 min 4.05 sec)

Records: 5374850 Duplicates: 0 Warnings: 0

[sql] view plaincopy

mysql> alter table results add PRIMARY KEY(id, ttime);

Query OK, 5374850 rows affected (6 min 14.86 sec)

Records: 5374850 Duplicates: 0 Warnings: 0

关于如何深入解析MySQL分区Partition功能就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。

标签：解析输出管理属性计算

暂时没有评论，来抢沙发吧~

如何深入解析MySQL分区Partition功能

如何深入解析MySQL分区Partition功能

小程序开发设计如何实现跨平台开发与管理满足企业多元化需求

操作系统寒武纪 - 会让企业IT高兴吗？

小程序三方平台开发: 解析小程序开发的未来趋势和机遇

最近发表

更多内容

小程序SDK

Finclip技术文档

小程序开发

小程序容器

小程序框架

Finclip小程序平台

Finclip用户投稿

车联网

推荐文章

小程序SDK是什么意思？小程序sdk和插件有什么区别？

小程序支付功能怎么实现？

企业app开发流程是什么？

app运营模式有哪些？

小程序多端引流怎么做？

小程序生态分析的机会和威胁

Flutter入门这一篇效率文章就够了

原生与跨平台解决方案分析,跨平台软件开发技术方案

热更新技术：让软件更新变得更加轻松快速

解决方案

银行解决方案

证券解决方案

互联网解决方案

政企OA解决方案

科技解决方案

loT解决方案

信任解决方案

热评文章

AppCan:基于混合模式的移动应用开发,移动混合模

Hybrid App混合模式开发的了解

小程序容器技术助力券商数字营销突围，小程序容器化的意

用mpvue开发微信小程序基础知识（vue.js开发

小程序多端框架全面测评对比，强烈推荐！

券商app架构 - 解析券商应用程序的构建与设计