MySQL 테이블 파티셔닝은 데이터베이스 처리량을 향상시키기 위한 데이터베이스 및 테이블 세분화와 동일합니다. 파티셔닝은 테이블 파티셔닝과 유사합니다. 테이블 파티셔닝은 대용량 데이터가 포함된 테이블을 논리적으로 여러 테이블로 분할하여 수평 또는 수직으로 분할하는 것입니다. 파티셔닝은 테이블의 데이터 파일을 여러 개의 데이터 파일로 분할하는 것입니다. 다른 데이터는 다른 파일로 분할됩니다. 이와 같이 데이터 양이 매우 많은 테이블의 경우 여러 데이터 파일을 저장용으로 사용하므로 데이터베이스의 IO 성능이 향상됩니다.
데이터 테이블의 파일을 다루기 때문에 먼저 MySQL 테이블의 저장에 대해 이해해야 합니다. 우리는 MySQL에 여러 스토리지 엔진이 있고 다양한 스토리지 엔진이 다양한 파일 형식을 저장한다는 것을 알고 있습니다. 여기서는 주로 설명을 위해 InnoDB와 MyISAM 두 가지 스토리지 엔진을 사용합니다.
InnoDB
.frm 파일 데이터 테이블 구조
.idb 파일 테이블 데이터 파일, 전용 테이블 공간, 각 테이블에는 .idb 파일이 있음
.ibdata 파일 테이블 데이터 파일, 공유 테이블 space, 모든 테이블은 이 데이터를 사용합니다.
우선 현재 데이터베이스 버전이 파티셔닝을 지원하는지 확인해야 합니다
1 show variables like '%partition%';
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;”
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) */;
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 중국어 웹사이트의 기타 관련 기사를 참조하세요!