千家信息网

请输入关键字词

热门搜索排行

最新搜索排行

导航： 首页 > 数据库 >

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

发表于：2024-11-23 作者：千家信息网编辑
千家信息网最后更新 2024年11月23日，这篇文章将为大家详细讲解有关如何深入解析MySQL分区Partition功能，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。自5.1开始对分区(Part
千家信息网最后更新 2024年11月23日如何深入解析MySQL分区Partition功能

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

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

= 水平分区（根据列属性按行分）=
举个简单例子：一个包含十年发票记录的表可以被分区为十个不同的分区，每个分区包含的是其中一年的记录。

=== 水平分区的几种模式：===
* Range（范围） - 这种模式允许DBA将数据划分不同范围。例如DBA可以将一个表通过年份划分成三个分区，80年代（1980's）的数据，90年代（1990's）的数据以及任何在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

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

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

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

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

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

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

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

  8. PARTITION p11 VALUES LESS THAN MAXVALUE );

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

*创建未分区表

[sql] view plain copy

  1. 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

  1. mysql> CREATE PROCEDURE load_part_tab()

  2. begin

  3. declare v int default 0;

  4. while v < 8000000

  5. do

  6. insert into part_tab

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

  8. set v = v + 1;

  9. end while;

  10. end

  11. //

  12. mysql> delimiter ;

  13. mysql> call load_part_tab();

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

[sql] view plain copy

  1. 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

  1. 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

  1. 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

  1. 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

  1. 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

  1. 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

  1. 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

  1. 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

  1. 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

  1. 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 plain copy

  1. 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 plain copy

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

  2. '1996-12-31' and c2='hello';

+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.75 sec)

[sql] view plain copy

  1. 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

  1. CREATE TABLE users (

  2. uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

  3. name VARCHAR(30) NOT NULL DEFAULT '',

  4. email VARCHAR(30) NOT NULL DEFAULT ''

  5. )

  6. PARTITION BY RANGE (uid) (

  7. PARTITION p0 VALUES LESS THAN (3000000)

  8. DATA DIRECTORY = '/data0/data'

  9. INDEX DIRECTORY = '/data1/idx',

  10. PARTITION p1 VALUES LESS THAN (6000000)

  11. DATA DIRECTORY = '/data2/data'

  12. INDEX DIRECTORY = '/data3/idx',

  13. PARTITION p2 VALUES LESS THAN (9000000)

  14. DATA DIRECTORY = '/data4/data'

  15. INDEX DIRECTORY = '/data5/idx',

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

  17. INDEX DIRECTORY = '/data7/idx'

  18. );

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

* LIST 类型

[sql] view plain copy

  1. CREATE TABLE category (

  2. cid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

  3. name VARCHAR(30) NOT NULL DEFAULT ''

  4. )

  5. PARTITION BY LIST (cid) (

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

  7. DATA DIRECTORY = '/data0/data'

  8. INDEX DIRECTORY = '/data1/idx',

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

  10. DATA DIRECTORY = '/data2/data'

  11. INDEX DIRECTORY = '/data3/idx',

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

  13. DATA DIRECTORY = '/data4/data'

  14. INDEX DIRECTORY = '/data5/idx',

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

  16. DATA DIRECTORY = '/data6/data'

  17. INDEX DIRECTORY = '/data7/idx'

  18. );

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

* HASH 类型

[sql] view plain copy

  1. CREATE TABLE users (

  2. uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

  3. name VARCHAR(30) NOT NULL DEFAULT '',

  4. email VARCHAR(30) NOT NULL DEFAULT ''

  5. )

  6. PARTITION BY HASH (uid) PARTITIONS 4 (

  7. PARTITION p0

  8. DATA DIRECTORY = '/data0/data'

  9. INDEX DIRECTORY = '/data1/idx',

  10. PARTITION p1

  11. DATA DIRECTORY = '/data2/data'

  12. INDEX DIRECTORY = '/data3/idx',

  13. PARTITION p2

  14. DATA DIRECTORY = '/data4/data'

  15. INDEX DIRECTORY = '/data5/idx',

  16. PARTITION p3

  17. DATA DIRECTORY = '/data6/data'

  18. INDEX DIRECTORY = '/data7/idx'

  19. );

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

例子：

[sql] view plain copy

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

  2. ENGINE=myisam

  3. PARTITION BY HASH( MONTH(tr_date) )

  4. PARTITIONS 6;

  5. CREATE PROCEDURE load_ti2()

  6. begin

  7. declare v int default 0;

  8. while v < 80000

  9. do

  10. insert into ti2

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

  12. set v = v + 1;

  13. end while;

  14. end

  15. //

* KEY 类型

[sql] view plain copy

  1. CREATE TABLE users (

  2. uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

  3. name VARCHAR(30) NOT NULL DEFAULT '',

  4. email VARCHAR(30) NOT NULL DEFAULT ''

  5. )

  6. PARTITION BY KEY (uid) PARTITIONS 4 (

  7. PARTITION p0

  8. DATA DIRECTORY = '/data0/data'

  9. INDEX DIRECTORY = '/data1/idx',

  10. PARTITION p1

  11. DATA DIRECTORY = '/data2/data'

  12. INDEX DIRECTORY = '/data3/idx',

  13. PARTITION p2

  14. DATA DIRECTORY = '/data4/data'

  15. INDEX DIRECTORY = '/data5/idx',

  16. PARTITION p3

  17. DATA DIRECTORY = '/data6/data'

  18. INDEX DIRECTORY = '/data7/idx'

  19. );

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

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

[sql] view plain copy

  1. CREATE TABLE users (

  2. uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

  3. name VARCHAR(30) NOT NULL DEFAULT '',

  4. email VARCHAR(30) NOT NULL DEFAULT ''

  5. )

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

  7. PARTITION p0 VALUES LESS THAN (3000000)

  8. DATA DIRECTORY = '/data0/data'

  9. INDEX DIRECTORY = '/data1/idx',

  10. PARTITION p1 VALUES LESS THAN (6000000)

  11. DATA DIRECTORY = '/data2/data'

  12. INDEX DIRECTORY = '/data3/idx'

  13. );

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

[sql] view plain copy

  1. CREATE TABLE users (

  2. uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

  3. name VARCHAR(30) NOT NULL DEFAULT '',

  4. email VARCHAR(30) NOT NULL DEFAULT ''

  5. )

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

  7. PARTITION p0 VALUES LESS THAN (3000000)

  8. DATA DIRECTORY = '/data0/data'

  9. INDEX DIRECTORY = '/data1/idx',

  10. PARTITION p1 VALUES LESS THAN (6000000)

  11. DATA DIRECTORY = '/data2/data'

  12. INDEX DIRECTORY = '/data3/idx'

  13. );

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

= 分区管理 =

* 删除分区

[sql] view plain copy

  1. ALERT TABLE users DROP PARTITION p0;

删除分区 p0。

* 重建分区
o RANGE 分区重建

[sql] view plain copy

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

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

[sql] view plain copy

  1. 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

  1. ALTER TABLE users REORGANIZE PARTITION COALESCE PARTITION 2;

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

[sql] view plain copy

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

  2. DATA DIRECTORY = '/data8/data'

  3. INDEX DIRECTORY = '/data9/idx');

新增一个RANGE分区。
o 新增 HASH/KEY 分区

[sql] view plain copy

  1. ALTER TABLE users ADD PARTITION PARTITIONS 8;

将分区总数扩展到8个。

[ 给已有的表加上分区 ]

[sql] view plain copy

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

  2. (PARTITION p0 VALUES LESS THAN (1),

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

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

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

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

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

  8. PARTITION p11 VALUES LESS THAN (12),

  9. PARTITION P12 VALUES LESS THAN (13) );



默认分区限制分区字段必须是主键（PRIMARY KEY)的一部分，为了去除此
限制：
[方法1] 使用ID

[sql] view plain copy

  1. mysql> ALTER TABLE np_pk

  2. -> PARTITION BY HASH( TO_DAYS(added) )

  3. -> PARTITIONS 4;

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

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

[sql] view plain copy

  1. mysql> ALTER TABLE np_pk

  2. -> PARTITION BY HASH(id)

  3. -> PARTITIONS 4;

Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0

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

[sql] view plain copy

  1. 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 plain copy

  1. 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功能就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。

很赞哦！
数据 文件 索引 模式 类型 分区表 语句 再次 字段 查询 例子 时间 范围 过程 功能 不同 性能 方法 存储 测试 数据库的安全要保护哪些东西 数据库安全各自的含义是什么 生产安全数据库录入 数据库的安全性及管理 数据库安全策略包含哪些 海淀数据库安全审计系统 建立农村房屋安全信息数据库 易用的数据库客户端支持安全管理 连接数据库失败ssl安全错误 数据库的锁怎样保障安全 浙江企业软件开发成本价 wget 服务器搭建 乡镇网络安全年度计划 登陆服务器可以干嘛 数据库建立故障统计表 淘宝数据库设计价格表 ftp服务器输路径 计量经济学变量类型数据库 电力网络安全招标 嵩明智能化软件开发咨询报价 数据库中R-S是什么意思 服务器安全在线扫描 计算机网络技术叫专科吗 动画图片下载软件开发 哪些状态是数据库的实例状态 数据库代码not null 阿里云服务器与数据安全 CORBA软件开发 新浪 软件开发外包协议 延庆区推广网络技术咨询 用友服务器可以多装一台电脑吗 电子表格怎么连在数据库 双服务器分流 mas代理服务器 网络安全法 ITU 奎屯互联网科技能上高职吗 kk软件开发中间层 湖北统一软件开发检测中心 mysql数据库多大空间 中国国家统计局失业率数据库

相关文章

0