mysql 파티션 기능에 대한 자세한 설명과 샘플코드 분석

아래 편집기에서는 mysql 파티션 기능에 대한 자세한 설명과 예제 분석을 제공합니다. 에디터가 꽤 좋다고 생각해서 지금 공유해서 참고용으로 올려보겠습니다. 에디터를 따라가서 살펴보자.

mysql 서브테이블에 대한 글을 썼다. 얼마 전 기사에서 mysql을 예로 들어 데이터베이스 파티셔닝이 무엇인지 이야기해 보겠습니다. mysql 데이터베이스의 데이터는 디스크에 파일 형태로 저장되며, 기본적으로 /mysql/data 아래에 위치한다. (my.cnf의 datadir을 통해 볼 수 있다.) 하나의 테이블은 주로 3개의 파일에 해당한다. frm Storage 테이블 구조 중 하나는 테이블 데이터를 저장하는 myd이고, 다른 하나는 테이블 인덱스를 저장하는 myi이다. 테이블의 데이터 양이 너무 많으면 myd와 myi가 매우 커지며, 데이터 검색 속도가 매우 느려지게 됩니다. 이때 mysql의 파티션 기능을 사용하여 이 테이블에 물리적으로 대응할 수 있습니다. 세 개의 파일이 여러 개의 작은 블록으로 나누어져 있으므로, 한 데이터 조각을 검색할 때 해당 데이터가 어느 블록에 있는지만 알고 그 안에서 검색하면 됩니다. 차단하다. 테이블의 데이터가 너무 크면 하나의 디스크에 맞지 않을 수 있습니다. 이때 데이터를 다른 디스크에 할당할 수 있습니다.

파티션의 두 가지 방법

가로 파티셔닝

이란 무엇입니까? 수평 파티션에 대해? 예를 들어, 100만 개의 데이터가 있을 경우 처음 10만 개의 데이터가 첫 번째 파티션에 배치되고, 두 번째 10만 개의 데이터가 두 번째 파티션에 배치됩니다. 파티션 등이 있습니다. 즉, 테이블을 10개 부분으로 나누어서 테이블을 나누는 방식이 좀 그렇네요. 데이터 조각이 검색되면 이 데이터에는 테이블 구조의 모든 필드가 포함됩니다. 이는 수평 분할이 테이블 구조를 변경하지 않음을 의미합니다.

2. 수직분할

수직분할이란? 예를 들어 사용자 테이블을 디자인할 때 처음에는 잘 고려하지 않았으나 모든 개인 정보를 하나의 테이블에 넣기 때문에 이 테이블에는 개인 정보와 같은 비교적 큰 필드가 있게 됩니다. 프로필 그리고 이러한 프로필은 많은 사람들이 볼 수 없을 수도 있으니 누군가 읽고 싶을 때, 검색하고 테이블을 나눌 때 이렇게 큰 필드를 분리할 수 있습니다.

데이터베이스 파티셔닝은 마치 사과를 자르는 듯한 느낌이 듭니다. 가로로 잘라야 할까요, 세로로 잘라야 할까요? mysql에서 제공하는 파티션은 첫 번째 유형인 수평 파티셔닝에 속합니다. 여러 가지 방법으로 나뉩니다. 아래에 예가 나와 있습니다.

1, mysql5.1 이상에서는 파티션 기능 지원

설치

설치 시

[root@BlackGhost mysql-5.1.50]# ./configure --help |grep -A 3 Partition 
=== Partition Support === 
Plugin Name:   partition 
Description:   MySQL Partitioning Support 
Supports build:  static 
Configurations:  max, max-no-ndb

확인 가능 위의 내용을 찾으면 파티셔닝을 지원하며 기본적으로 켜져 있다는 의미입니다. mysql을 설치했다면

mysql> show variables like "%part%"; 
+-------------------+-------+ 
| Variable_name   | Value | 
+-------------------+-------+ 
| have_partitioning | YES  | 
+-------------------+-------+ 
1 row in set (0.00 sec)

변수

를 확인하세요. 지원되는 경우 위의 프롬프트가 표시됩니다.

2,

range

파티션

RANGE에 따라 파티션된 테이블은 다음과 같이 파티션되며, 각 파티션에는 다음 행이 포함됩니다. 파티션 표현식의 값이 주어진 연속 간격

//创建range分区表 
mysql> CREATE TABLE IF NOT EXISTS `user` ( 
 ->  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID', 
 ->  `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称', 
 ->  `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0为男，1为女', 
 ->  PRIMARY KEY (`id`) 
 -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 
 -> PARTITION BY RANGE (id) ( 
 ->   PARTITION p0 VALUES LESS THAN (3), 
 ->   PARTITION p1 VALUES LESS THAN (6), 
 ->   PARTITION p2 VALUES LESS THAN (9), 
 ->   PARTITION p3 VALUES LESS THAN (12), 
 ->   PARTITION p4 VALUES LESS THAN MAXVALUE 
 -> ); 
Query OK, 0 rows affected (0.13 sec) 
 
//插入一些数据 
mysql> INSERT INTO `test`.`user` (`name` ,`sex`)VALUES ('tank', '0') 
 -> ,('zhang',1),('ying',1),('张',1),('映',0),('test1',1),('tank2',1) 
 -> ,('tank1',1),('test2',1),('test3',1),('test4',1),('test5',1),('tank3',1) 
 -> ,('tank4',1),('tank5',1),('tank6',1),('tank7',1),('tank8',1),('tank9',1) 
 -> ,('tank10',1),('tank11',1),('tank12',1),('tank13',1),('tank21',1),('tank42',1); 
Query OK, 25 rows affected (0.05 sec) 
Records: 25 Duplicates: 0 Warnings: 0 
 
//到存放数据库表文件的地方看一下，my.cnf里面有配置，datadir后面就是 
[root@BlackGhost test]# ls |grep user |xargs du -sh 
4.0K  user#P#p0.MYD 
4.0K  user#P#p0.MYI 
4.0K  user#P#p1.MYD 
4.0K  user#P#p1.MYI 
4.0K  user#P#p2.MYD 
4.0K  user#P#p2.MYI 
4.0K  user#P#p3.MYD 
4.0K  user#P#p3.MYI 
4.0K  user#P#p4.MYD 
4.0K  user#P#p4.MYI 
12K  user.frm 
4.0K  user.par 
 
//取出数据 
mysql> select count(id) as count from user; 
+-------+ 
| count | 
+-------+ 
|  25 | 
+-------+ 
1 row in set (0.00 sec) 
 
//删除第四个分区 
mysql> alter table user drop partition p4; 
Query OK, 0 rows affected (0.11 sec) 
Records: 0 Duplicates: 0 Warnings: 0 
 
/**存放在分区里面的数据丢失了，第四个分区里面有14条数据，剩下的3个分区 
只有11条数据，但是统计出来的文件大小都是4.0K，从这儿我们可以看出分区的 
最小区块是4K 
*/ 
mysql> select count(id) as count from user; 
+-------+ 
| count | 
+-------+ 
|  11 | 
+-------+ 
1 row in set (0.00 sec) 
 
//第四个区块已删除 
[root@BlackGhost test]# ls |grep user |xargs du -sh 
4.0K  user#P#p0.MYD 
4.0K  user#P#p0.MYI 
4.0K  user#P#p1.MYD 
4.0K  user#P#p1.MYI 
4.0K  user#P#p2.MYD 
4.0K  user#P#p2.MYI 
4.0K  user#P#p3.MYD 
4.0K  user#P#p3.MYI 
12K  user.frm 
4.0K  user.par 
 
/*可以对现有表进行分区,并且会按規则自动的将表中的数据分配相应的分区 
中，这样就比较好了，可以省去很多事情，看下面的操作*/ 
mysql> alter table aa partition by RANGE(id) 
 -> (PARTITION p1 VALUES less than (1), 
 -> PARTITION p2 VALUES less than (5), 
 -> PARTITION p3 VALUES less than MAXVALUE); 
Query OK, 15 rows affected (0.21 sec)  //对15数据进行分区 
Records: 15 Duplicates: 0 Warnings: 0 
 
//总共有15条 
mysql> select count(*) from aa; 
+----------+ 
| count(*) | 
+----------+ 
|    15 | 
+----------+ 
1 row in set (0.00 sec) 
 
//删除一个分区 
mysql> alter table aa drop partition p2; 
Query OK, 0 rows affected (0.30 sec) 
Records: 0 Duplicates: 0 Warnings: 0 
 
//只有11条了，说明对现有的表分区成功了 
mysql> select count(*) from aa; 
+----------+ 
| count(*) | 
+----------+ 
|    11 | 
+----------+ 
1 row in set (0.00 sec)

3, list

partition

내에 있는 경우 정의 및 LIST 파티션의 각 파티션 선택은 값 목록 집합의 값에 속하는 열 값을 기반으로 하는 반면, RANGE 파티션은 연속 간격 값 집합에 속합니다.

//这种方式失败 
mysql> CREATE TABLE IF NOT EXISTS `list_part` ( 
 ->  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID', 
 ->  `province_id` int(2) NOT NULL DEFAULT 0 COMMENT '省', 
 ->  `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称', 
 ->  `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0为男，1为女', 
 ->  PRIMARY KEY (`id`) 
 -> ) ENGINE=INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 
 -> PARTITION BY LIST (province_id) ( 
 ->   PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8), 
 ->   PARTITION p1 VALUES IN (9,10,11,12,16,21), 
 ->   PARTITION p2 VALUES IN (13,14,15,19), 
 ->   PARTITION p3 VALUES IN (17,18,20,22,23,24) 
 -> ); 
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function 
 
//这种方式成功 
mysql> CREATE TABLE IF NOT EXISTS `list_part` ( 
 ->  `id` int(11) NOT NULL COMMENT '用户ID', 
 ->  `province_id` int(2) NOT NULL DEFAULT 0 COMMENT '省', 
 ->  `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称', 
 ->  `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0为男，1为女' 
 -> ) ENGINE=INNODB DEFAULT CHARSET=utf8 
 -> PARTITION BY LIST (province_id) ( 
 ->   PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8), 
 ->   PARTITION p1 VALUES IN (9,10,11,12,16,21), 
 ->   PARTITION p2 VALUES IN (13,14,15,19), 
 ->   PARTITION p3 VALUES IN (17,18,20,22,23,24) 
 -> ); 
Query OK, 0 rows affected (0.33 sec)

위에서 리스트 파티션을 생성할 때 기본 키가 있으면 파티셔닝 시 기본 키가 그 안에 있어야 하며, 그렇지 않으면 오류가 보고됩니다. 기본 키를 사용하지 않으면 파티션이 성공적으로 생성됩니다. 일반적인 상황에서는 테이블에 기본 키가 분명히 존재합니다. 이는 파티션의 제한 사항으로 간주됩니다.

데이터를 테스트한다면 Range 파티션 테스트를 참고하여

4,

hash 파티션

을 운영하시기 바랍니다.

HASH 파티셔닝은 주로 미리 정해진 개수의 파티션에 데이터가 균등하게 분산되도록 하기 위해 사용됩니다. 해싱할 열 값을 기반으로 열 값이나 표현식을 지정하고 이를 지정하기만 하면 됩니다. 분할된 테이블은 분할할 파티션 수로 해시됩니다.

mysql> CREATE TABLE IF NOT EXISTS `hash_part` ( 
 ->  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '评论ID', 
 ->  `comment` varchar(1000) NOT NULL DEFAULT '' COMMENT '评论', 
 ->  `ip` varchar(25) NOT NULL DEFAULT '' COMMENT '来源IP', 
 ->  PRIMARY KEY (`id`) 
 -> ) ENGINE=INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 
 -> PARTITION BY HASH(id) 
 -> PARTITIONS 3; 
Query OK, 0 rows affected (0.06 sec)

테스트는 범위 파티셔닝 작업을 참고하세요

5, 키 파티셔닝

KEY에 의한 파티셔닝은 HASH 파티셔닝과 유사하게 HASH 파티셔닝에서 사용하는 사용자 정의 표현식 외에 KEY 파티셔닝의 해시

함수

를 MySQL 서버에서 제공한다.

mysql> CREATE TABLE IF NOT EXISTS `key_part` ( 
 ->  `news_id` int(11) NOT NULL COMMENT '新闻ID', 
 ->  `content` varchar(1000) NOT NULL DEFAULT '' COMMENT '新闻内容', 
 ->  `u_id` varchar(25) NOT NULL DEFAULT '' COMMENT '来源IP', 
 ->  `create_time` DATE NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '时间' 
 -> ) ENGINE=INNODB DEFAULT CHARSET=utf8 
 -> PARTITION BY LINEAR HASH(YEAR(create_time)) 
 -> PARTITIONS 3; 
Query OK, 0 rows affected (0.07 sec)

测试请参考range分区的操作

6，子分区

子分区是分区表中每个分区的再次分割，子分区既可以使用HASH希分区，也可以使用KEY分区。这 也被称为复合分区（composite partitioning）。

1，如果一个分区中创建了子分区，其他分区也要有子分区

2，如果创建了了分区，每个分区中的子分区数必有相同

3，同一分区内的子分区，名字不相同，不同分区内的子分区名子可以相同（5.1.50不适用）

mysql> CREATE TABLE IF NOT EXISTS `sub_part` ( 
 ->  `news_id` int(11) NOT NULL COMMENT '新闻ID', 
 ->  `content` varchar(1000) NOT NULL DEFAULT '' COMMENT '新闻内容', 
 ->  `u_id` int(11) NOT NULL DEFAULT 0s COMMENT '来源IP', 
 ->  `create_time` DATE NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '时间' 
 -> ) ENGINE=INNODB DEFAULT CHARSET=utf8 
 -> PARTITION BY RANGE(YEAR(create_time)) 
 -> SUBPARTITION BY HASH(TO_DAYS(create_time))( 
 -> PARTITION p0 VALUES LESS THAN (1990)(SUBPARTITION s0,SUBPARTITION s1,SUBPARTITION s2), 
 -> PARTITION p1 VALUES LESS THAN (2000)(SUBPARTITION s3,SUBPARTITION s4,SUBPARTITION good), 
 -> PARTITION p2 VALUES LESS THAN MAXVALUE(SUBPARTITION tank0,SUBPARTITION tank1,SUBPARTITION tank3) 
 -> ); 
Query OK, 0 rows affected (0.07 sec)

官方网站说不同分区内的子分区可以有相同的名字，但是mysql5.1.50却不行会提示以下错误

ERROR 1517 (HY000): Duplicate partition name s1

三，分区管理

1，删除分区

1.mysql> alter table user drop partition p4;

2，新增分区

//range添加新分区 
mysql> alter table user add partition(partition p4 values less than MAXVALUE); 
Query OK, 0 rows affected (0.06 sec) 
Records: 0 Duplicates: 0 Warnings: 0 
 
//list添加新分区 
mysql> alter table list_part add partition(partition p4 values in (25,26,28)); 
Query OK, 0 rows affected (0.01 sec) 
Records: 0 Duplicates: 0 Warnings: 0 
 
//hash重新分区 
mysql> alter table hash_part add partition partitions 4; 
Query OK, 0 rows affected (0.12 sec) 
Records: 0 Duplicates: 0 Warnings: 0 
 
//key重新分区 
mysql> alter table key_part add partition partitions 4; 
Query OK, 1 row affected (0.06 sec)  //有数据也会被重新分配 
Records: 1 Duplicates: 0 Warnings: 0 
 
//子分区添加新分区，虽然我没有指定子分区，但是系统会给子分区命名的 
mysql> alter table sub1_part add partition(partition p3 values less than MAXVALUE); 
Query OK, 0 rows affected (0.02 sec) 
Records: 0 Duplicates: 0 Warnings: 0 
 
mysql> show create table sub1_part\G; 
*************************** 1. row *************************** 
 Table: sub1_part 
Create Table: CREATE TABLE `sub1_part` ( 
 `news_id` int(11) NOT NULL COMMENT '新闻ID', 
 `content` varchar(1000) NOT NULL DEFAULT '' COMMENT '新闻内容', 
 `u_id` varchar(25) NOT NULL DEFAULT '' COMMENT '来源IP', 
 `create_time` date NOT NULL DEFAULT '0000-00-00' COMMENT '时间' 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
!50100 PARTITION BY RANGE (YEAR(create_time)) 
SUBPARTITION BY HASH (TO_DAYS(create_time)) 
(PARTITION p0 VALUES LESS THAN (1990) 
 (SUBPARTITION s0 ENGINE = InnoDB, 
 SUBPARTITION s1 ENGINE = InnoDB, 
 SUBPARTITION s2 ENGINE = InnoDB), 
 PARTITION p1 VALUES LESS THAN (2000) 
 (SUBPARTITION s3 ENGINE = InnoDB, 
 SUBPARTITION s4 ENGINE = InnoDB, 
 SUBPARTITION good ENGINE = InnoDB), 
 PARTITION p2 VALUES LESS THAN (3000) 
 (SUBPARTITION tank0 ENGINE = InnoDB, 
 SUBPARTITION tank1 ENGINE = InnoDB, 
 SUBPARTITION tank3 ENGINE = InnoDB), 
 PARTITION p3 VALUES LESS THAN MAXVALUE 
 (SUBPARTITION p3sp0 ENGINE = InnoDB,  //子分区的名子是自动生成的 
 SUBPARTITION p3sp1 ENGINE = InnoDB, 
 SUBPARTITION p3sp2 ENGINE = InnoDB)) 
1 row in set (0.00 sec)

3，重新分区

//range重新分区 
mysql> ALTER TABLE user REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES LESS THAN MAXVALUE); 
Query OK, 11 rows affected (0.08 sec) 
Records: 11 Duplicates: 0 Warnings: 0 
 
//list重新分区 
mysql> ALTER TABLE list_part REORGANIZE PARTITION p0,p1,p2,p3,p4 INTO (PARTITION p0 VALUES in (1,2,3,4,5)); 
Query OK, 0 rows affected (0.28 sec) 
Records: 0 Duplicates: 0 Warnings: 0 
 
//hash和key分区不能用REORGANIZE，官方网站说的很清楚 
mysql> ALTER TABLE key_part REORGANIZE PARTITION COALESCE PARTITION 9; 
ERROR 1064 (42000): You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version for the right syntax to use near 'PARTITION 9' at line 1

四，分区优点

1，分区可以分在多个磁盘，存储更大一点

2，根据查找条件，也就是where后面的条件，查找只查找相应的分区不用全部查找了

3，进行大数据搜索时可以进行并行处理。

4，跨多个磁盘来分散数据查询，来获得更大的查询吞吐量

위 내용은 mysql 파티션 기능에 대한 자세한 설명과 샘플코드 분석의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!