MySQL의 테이블 파티셔닝에 대한 자세한 소개

MySQL 테이블 파티셔닝은 데이터베이스 처리량을 향상시키기 위한 데이터베이스 및 테이블 세분화와 동일합니다. 파티셔닝은 테이블 파티셔닝과 유사합니다. 테이블 파티셔닝은 대용량 데이터가 포함된 테이블을 논리적으로 여러 테이블로 분할하여 수평 또는 수직으로 분할하는 것입니다. 파티셔닝은 테이블의 데이터 파일을 여러 개의 데이터 파일로 분할하는 것입니다. 다른 데이터는 다른 파일로 분할됩니다. 이와 같이 데이터 양이 매우 많은 테이블의 경우 여러 데이터 파일을 저장용으로 사용하므로 데이터베이스의 IO 성능이 향상됩니다.

데이터 테이블의 파일을 다루기 때문에 먼저 MySQL 테이블의 저장에 대해 이해해야 합니다. 우리는 MySQL에 여러 스토리지 엔진이 있고 다양한 스토리지 엔진이 다양한 파일 형식을 저장한다는 것을 알고 있습니다. 여기서는 주로 설명을 위해 InnoDB와 MyISAM 두 가지 스토리지 엔진을 사용합니다.

InnoDB

.frm 파일 데이터 테이블 구조

.idb 파일 테이블 데이터 파일, 전용 테이블 공간, 각 테이블에는 .idb 파일이 있음

.ibdata 파일 테이블 데이터 파일, 공유 테이블 space, 모든 테이블은 이 데이터를 사용합니다.

우선 현재 데이터베이스 버전이 파티셔닝을 지원하는지 확인해야 합니다

1 show variables like '%partition%';

파티션 방법은 무엇인가요? 데이터베이스의 수평 분할을 수행할 때 수평 분할이 지정된 필드의 모듈러스에 따라 다른 테이블로 분할될 수 있거나 날짜에 따라 분할되거나 ID에 따라 분할될 수 있다는 것을 알고 있습니다. 첫 번째 테이블에서 , 두 번째 테이블에는 100만, 1~200만 등이 있습니다. 간단히 말해서, 분할을 수행하는 방법에는 여러 가지가 있습니다. 그런 다음 데이터베이스는 테이블 파티션에서 선택할 수 있는 다양한 옵션도 제공합니다.

MySQL 테이블 파티셔닝 전략

RANGE 파티셔닝 주어진 연속 범위에 속하는 열 값을 기반으로 파티션에 여러 행 할당

1 DROP TABLE IF EXISTS `p_range`;
2 CREATE TABLE `p_range` (
3 `id` int(10) NOT NULL AUTO_INCREMENT,
4 `name` char(20) NOT NULL,
5 PRIMARY KEY (`id`)
6 ) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
7 /*!50100 PARTITION BY RANGE (id)
8 (PARTITION p0 VALUES LESS THAN (8) ENGINE = MyISAM) */;

최대

1 PARTITION BY RANGE (id)
2 (
3 PARTITION p0 VALUES LESS THAN (8),
4 PARTITION p1 VALUES LESS THAN MAXVALUE)

적용 가능한 시나리오:

이는 ID가 7보다 큰 모든 데이터 레코드가 p1 파티션에 존재함을 의미합니다.

RANGE 파티셔닝은 다음 상황에서 특히 유용합니다.

·"오래된" 데이터를 삭제해야 하는 경우. 위의 가장 최근 예에 표시된 분할 구성표를 사용하는 경우 "ALTER TABLE 직원 DROP PARTITION p0;"을 사용하여 1991년 이전에 근무를 중단한 직원의 모든 행을 삭제할 수 있습니다. 행 수가 많은 테이블의 경우 이는 "DELETE FROM 직원 WHERE YEAR(구분)

1990;"과 같은 DELETE 쿼리를 실행하는 것보다 훨씬 더 효율적입니다.

·날짜나 시간 값 또는 다른 계열에서 증가하는 값이 포함된 열을 사용하고 싶습니다.

· 테이블을 분할하는 데 사용된 열에 직접적으로 의존하는 쿼리를 자주 실행합니다. 예를 들어,

“SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BY store_id；”

와 같은 쿼리를 실행하면 나머지 파티션에는 WHERE 절과 일치하는 레코드가 포함될 수 없기 때문에 MySQL은 파티션 p2만 스캔해야 한다고 신속하게 결정할 수 있습니다.

LIST 파티셔닝은 RANGE별 파티셔닝과 유사하지만 차이점은 LIST 파티셔닝은 개별 값 집합의 특정 값과 일치하는 열 값을 기준으로 선택한다는 것입니다.

1 DROP TABLE IF EXISTS `p_list`;
2 CREATE TABLE `p_list` (
3 `id` int(10) NOT NULL AUTO_INCREMENT,
4 `typeid` mediumint(10) NOT NULL DEFAULT '0',
5 `typename` char(20) DEFAULT NULL,
6 PRIMARY KEY (`id`,`typeid`)
7 ) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
8 /*!50100 PARTITION BY LIST (typeid)
9 (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은 이를 자동으로 수행하며 사용자가 해야 할 일은 해당 값에 따라 결정됩니다. 해시됨. 열 값은 열 값 또는 표현식을 지정하고, 분할된 테이블이 분할될 파티션 수를 지정합니다

1 DROP TABLE IF EXISTS `p_hash`;
2 CREATE TABLE `p_hash` (
3 `id` int(10) NOT NULL AUTO_INCREMENT,
4 `storeid` mediumint(10) NOT NULL DEFAULT '0',
5 `storename` char(255) DEFAULT NULL,
6 PRIMARY KEY (`id`,`storeid`)
7 ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
8 /*!50100 PARTITION BY HASH (storeid)9 PARTITIONS 4 */;

简单点说就是数据的存入可以按 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 分区里面了。

 

分区注意点

1、重新分区时，如果原分区里面存在 maxvalue 则新的分区里面也必须包含

maxvalue 否则就错误。

alter table p_range2x
reorganize partition p1,p2
into (partition p0 values less than (5), partition p1 values less than maxvalue);
[Err] 1520 – Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range

2、分区删除时，数据也同样会被删除 alter table p_range drop partition p0;

3、如果 range 分区列表里面没有 maxvalue 则如有新数据大于现在分区 range 数据值那么这个数据是无法写入到数据库表的。

 

4、修改表名不需要 删除分区后在进行更改，修改表名后分区存储 myd myi 对应也会自动更改。

 

如果希望从所有分区删除所有的数据，但是又保留表的定义和表的分区模式，使用 TRUNCATE TABLE 命令。（请参见 13.2.9 节，“TRUNCATE 语法”）。

 

如果希望改变表的分区而又不丢失数据，使用“ALTER TABLE … REORGANIZE PARTITION”语句。参见下面的内容，或者在 13.1.2 节，“ALTER TABLE 语法” 中参考关于 REORGANIZE PARTITION 的信息。

 

5、对表进行分区时，不论采用哪种分区方式如果表中存在主键那么主键必须在分区列中。表分区的局限性。

 

6、list 方式分区没有类似于 range 那种 less than maxvalue 的写法，也就是说 list 分区表的所有数据都必须在分区字段的值列表集合中。

 

7、在 MySQL 5.1 版中，同一个分区表的所有分区必须使用同一个存储引擎；例如，不能对一个分区使用 MyISAM，而对另一个使用 InnoDB。

 

8、分区的名字是不区分大小写的，myp1 与 MYp1 是相同的。

  

위 내용은 MySQL의 테이블 파티셔닝에 대한 자세한 소개의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!