mysql은 파티셔닝을 지원하나요?

mysql은 버전 5.1부터 파티셔닝 기능을 지원합니다. MySQL 5.1에서 파티션 표현식은 정수이거나 정수를 반환하는 표현식이어야 하며, MySQL 5.5에서는 정수가 아닌 표현식 파티셔닝을 지원합니다. MySQL 데이터베이스의 파티션은 로컬 파티션 인덱스입니다. 파티션은 데이터와 인덱스를 모두 저장합니다. 즉, 각 영역의 클러스터형 인덱스와 비클러스터형 인덱스는 해당 영역(다른 물리적 파일)에 배치됩니다. MySQL은 RANGE 파티션, LIST 파티션, HASH 파티션, KEY 파티션의 4가지 파티션 유형을 지원합니다.

이 튜토리얼의 운영 환경: windows7 시스템, mysql8 버전, Dell G3 컴퓨터.

mysql은 파티셔닝을 지원합니다.

mysql 파티셔닝 개요

MySQL은 5.1에서 수평 파티셔닝 지원을 추가했습니다. 파티셔닝은 테이블이나 인덱스를 더 작고 관리하기 쉬운 부분으로 나누는 것입니다. 각 영역은 독립적이며 독립적으로 처리되거나 더 큰 개체의 일부로 처리될 수 있습니다. 이는 MySQL에서 지원하는 기능으로, 비즈니스 코드를 변경할 필요가 없습니다. MySQL은 TIDB 등 다른 DB와 달리 OLTP 중심의 데이터라는 점을 아셔야 합니다. 그렇다면 파티션을 사용할 때 매우 주의해야 합니다. 파티션 사용 방법을 모르면 성능에 부정적인 영향을 미칠 수 있습니다.

MySQL 데이터베이스의 파티션은 로컬 파티션 인덱스입니다. 파티션은 데이터와 인덱스를 모두 저장합니다. 즉, 각 영역의 클러스터형 인덱스와 비클러스터형 인덱스는 해당 영역(다른 물리적 파일)에 배치됩니다. 현재 MySQL 데이터베이스는 전역 파티셔닝을 지원하지 않습니다.

파티셔닝 유형에 관계없이 테이블에 기본 키나 고유 인덱스가 있는 경우 파티셔닝 열은 고유 인덱스의 구성 요소여야 합니다.

파티션 테이블의 제한 요소

(1) 테이블은 최대 1024개의 파티션만 가질 수 있습니다.

(2) MySQL5.1에서 파티션 표현식은 정수이거나 정수를 반환하는 표현식이어야 합니다. 정수가 아닌 표현식 파티셔닝에 대한 지원은 MySQL 5.5에서 제공됩니다.

(3) 파티션 필드에 기본 키 또는 고유 인덱스 열이 있는 경우 기본 키 열 및 고유 인덱스 열이 많이 포함되어야 합니다. 즉, 파티션 필드에 기본 키 또는 인덱스 열이 포함되어 있지 않거나 모든 기본 키 및 인덱스 열이 포함되어 있습니다.

(4) 분할된 테이블에서는 외래 키 제약 조건을 사용할 수 없습니다.

(5) MySQL 파티셔닝은 테이블의 모든 데이터와 인덱스에 적용됩니다. 테이블 데이터만 파티셔닝할 수 있고 인덱스는 파티셔닝할 수 없으며, 인덱스만 파티셔닝하고 테이블은 파티셔닝할 수 없으며, 데이터의 일부만 파티셔닝할 수 없습니다. 테이블의.

파티션 유형

현재 MySQL은 RANGE 파티션, LIST 파티션, HASH 파티션, KEY 파티션 등 여러 유형의 파티션을 지원합니다. 테이블에 기본 키나 고유 인덱스가 있는 경우 파티션 열은 고유 인덱스의 구성 요소여야 합니다. 실제 전투에서는 RANGE 파티셔닝이 가장 많이 사용됩니다.

RANGE 파티셔닝

RANGE 파티셔닝은 실제로 가장 일반적으로 사용되는 파티셔닝 유형입니다. 행 데이터는 주어진 연속 간격에 속하는 열 값을 기준으로 파티션에 배치됩니다. 그러나 삽입된 데이터에 파티션에 정의된 값이 없으면 예외가 발생한다는 점을 기억하십시오.

RANGE 파티션은 거래 테이블, 판매 테이블 등 날짜 열 파티셔닝에 주로 사용됩니다. 데이터를 연도별, 월별로 저장할 수 있습니다. 날짜형 데이터를 고유 인덱스로 분할하는 경우, 옵티마이저는 YEAR(), TO_DAYS(), TO_SECONDS(), UNIX_TIMESTAMP() 등의 함수만 최적화할 수 있다는 점에 유의하시기 바랍니다. 실제 전투에서는 int형을 사용할 수 있으므로 yyyyMM만 저장하면 된다. 더 이상 기능에 대해 걱정할 필요가 없습니다.

CREATE TABLE `m_test_db`.`Order` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `partition_key` INT NOT NULL,
    `amt` DECIMAL(5) NULL,
    PRIMARY KEY (`id` , `partition_key`)
) PARTITION BY RANGE (partition_key) PARTITIONS 5 (
PARTITION part0 VALUES LESS THAN (201901) , 
PARTITION part1 VALUES LESS THAN (201902) , 
PARTITION part2 VALUES LESS THAN (201903) , 
PARTITION part3 VALUES LESS THAN (201904) , 
PARTITION part4 VALUES LESS THAN (201905));

이번에는 먼저 일부 데이터를 삽입합니다

INSERT INTO `m_test_db`.`Order` (`id`, `partition_key`, `amt`) VALUES ('1', '201901', '1000');
INSERT INTO `m_test_db`.`Order` (`id`, `partition_key`, `amt`) VALUES ('2', '201902', '800');
INSERT INTO `m_test_db`.`Order` (`id`, `partition_key`, `amt`) VALUES ('3', '201903', '1200');

이제 EXPLAIN PARTITION 명령을 통해 쿼리하여 SQL 옵티마이저가 해당 영역만 검색하고 모든 파티션을 검색하지는 않는다는 것을 알아냅니다

문제가 있는 경우 sql 문을 입력하면 모든 지역을 방문할 수 있습니다. 그것은 위험할 것입니다. 따라서 테이블을 분할한 후 select 문은 파티션 키를 사용해야 합니다.

다음 3가지 유형은 많이 사용되지 않으므로 여기서는 간단히 언급하겠습니다.

LIST 파티셔닝

LIST 파티셔닝은 파티셔닝 열의 값이 연속적이지 않고 불연속적이라는 점을 제외하면 RANGE 파티셔닝과 매우 유사합니다. LIST 파티셔닝은 각 파티션의 값이 이산적이어서 값만 정의할 수 있기 때문에 VALUES IN을 사용합니다.

HASH 파티션

해싱에 대해 말하자면, 데이터를 미리 정의된 파티션에 균등하게 분배하여 각 파티션의 수가 거의 동일하도록 하는 목적이 분명합니다.

KEY 파티션

KEY 파티션은 HASH 파티션과 유사하지만 HASH 파티션은 파티셔닝을 위해 사용자 정의 함수를 사용하고, KEY 파티션은 파티셔닝을 위해 데이터베이스에서 제공하는 기능을 사용한다는 점이 다릅니다.

分区和性能

一项技术，不是用了就一定带来益处。比如显式锁功能比内置锁强大，你没玩好可能导致很不好的情况。分区也是一样，不是启动了分区数据库就会运行的更快，分区可能会给某些sql语句性能提高，但是分区主要用于数据库高可用性的管理。

数据库应用分为2类，一类是OLTP（在线事务处理），一类是OLAP（在线分析处理）。对于OLAP应用分区的确可以很好的提高查询性能，因为一般分析都需要返回大量的数据，如果按时间分区，比如一个月用户行为等数据，则只需扫描响应的分区即可。在OLTP应用中，分区更加要小心，通常不会获取一张大表的10%的数据，大部分是通过索引返回几条数据即可。

比如一张表1000w数据量，如果一句select语句走辅助索引，但是没有走分区键。那么结果会很尴尬。如果1000w的B+树的高度是3，现在有10个分区。那么不是要(3+3)*10次的逻辑IO？（3次聚集索引，3次辅助索引，10个分区）。所以在OLTP应用中请小心使用分区表。

在日常开发中，如果想查看sql语句的分区查询结果可以使用explain partitions + select sql来获取，partitions标识走了哪几个分区。

mysql> explain partitions select * from TxnList where startTime>&#39;2016-08-25 00:00:00&#39; and startTime<&#39;2016-08-25 23:59:00&#39;;  
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+  
| id | select_type | table             | partitions | type | possible_keys | key  | key_len | ref  | rows  | Extra       |  
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+  
|  1 | SIMPLE      | ClientActionTrack | p20160825  | ALL  | NULL          | NULL | NULL    | NULL | 33868 | Using where |  
+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+-------------+  
1 row in set (0.00 sec)

注：

1.MySQL Workbench下添加分区的截图

2. Table has no partition for value 12

在12月的某一天，我查看了生产的日志文件，忽然发现系统一直在报错：Table has no partition for value 12。仔细检查分区sql发现分区的时候用的是less than

也就是说我在注释1截图里面的分区是不包括12月的区的。执行以下命令增加分区：

ALTER TABLE table_name ADD PARTITION (PARTITION p_12 VALUES LESS THAN (13));

如果没有进行适当的处理，将会报错。所以在进行 RANGE 分区时，要思考这种情况。一般情况下，就时在最后添加一个 MAXVALUE 分区，如下：

PARTITION p_max VALUES LESS THAN MAXVALUE

【相关推荐：mysql视频教程】

위 내용은 mysql은 파티셔닝을 지원하나요?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!