MySQL 파티션 테이블 파티션에서 온라인으로 파티션 필드를 수정하고 나중에 파티션에 대해 자세히 알아보세요. (1)

회사에서 온라인으로 파티션을 사용하고 있는데, 한 테이블의 파티션 필드가 잘못되어 재구축이 필요한데, 기본키 필드를 수정하거나 인덱스를 수정하는 등 하나의 SQL로 직접 할 수 있는 방법이 없는 것으로 나타났습니다. 필드. 대신 임시 테이블을 구축하고 다운타임을 가져야 하기 때문에 설명서를 꼼꼼히 읽고 파티션의 세부 사항을 연구했습니다.

회사에서 온라인으로 업무를 처리할 때 업무량이 가장 적은 오전 1시에 다음을 실행합니다.

임시 테이블 만들기

CREATE TABLE tbname_TMP (    
SHARD_ID INT NOT NULL,    
...
    xxx_DATE DATETIME NOT NULL,    
    PRIMARY KEY (xxx_DATE,shard_id)) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_binPARTITION BY LIST(MONTH(xxx_DATE)) (    
    PARTITION m1 VALUES IN (1),    
    PARTITION m2 VALUES IN (2),    
    PARTITION m3 VALUES IN (3),    
    PARTITION m4 VALUES IN (4),    
    PARTITION m5 VALUES IN (5),    
    PARTITION m6 VALUES IN (6),    
    PARTITION m7 VALUES IN (7),    
    PARTITION m8 VALUES IN (8),    
    PARTITION m9 VALUES IN (9),    
    PARTITION m10 VALUES IN (10),    
    PARTITION m11 VALUES IN (11),    
    PARTITION m12 VALUES IN (12)
    );

테이블 이름 전환, 테이블 구조 수정

RENAME TABLE xxx TO xxx_DELETED, xxx_TMP TO xxx;

원본 데이터 가져오기

insert into xxx select * from xxx_DELETEDxxx_DELETED;

좋아, 모든 작업이 완료되었습니다. 전체 프로세스는 50분 정도 걸립니다. 및 MMM 장애 조치가 진행 중입니다. 아웃라인은 테이블 구조 변경 및 데이터 가져오기 작업을 수행합니다. 실제 다운타임에는 테이블 구조 파티션 필드를 수정하는 시간이 포함되지 않으며 장애 조치 전환 시간만 30초입니다

MySQL 파티션, 공식 영어 정보를 읽어보니 번역 수준이 제한되어 있고 일부는 중국어로 번역되지 않고 영어로 바로 게시되어 있습니다.
리스트 파티션 테이블 1개

mysql> CREATE TABLE `eh` (
    ->   `id` int(11) NOT NULL,
    ->   `ENTITLEMENT_HIST_ID` bigint(20) NOT NULL,
    ->   `ENTITLEMENT_ID` bigint(20) NOT NULL,
    ->   `USER_ID` bigint(20) NOT NULL,
    ->   `DATE_CREATED` datetime NOT NULL,
    ->   `STATUS` smallint(6) NOT NULL,
    ->   `CREATED_BY` varchar(32) COLLATE utf8_bin DEFAULT NULL,
    ->   `MODIFIED_BY` varchar(32) COLLATE utf8_bin DEFAULT NULL,
    ->   `DATE_MODIFIED` datetime NOT NULL,
    ->   PRIMARY KEY (`DATE_MODIFIED`,`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
    -> /*!50100 PARTITION BY LIST (MONTH(DATE_MODIFIED))
    -> (PARTITION m1 VALUES IN (1) ENGINE = InnoDB,
    ->  PARTITION m2 VALUES IN (2) ENGINE = InnoDB,
    ->  PARTITION m3 VALUES IN (3) ENGINE = InnoDB,
    ->  PARTITION m4 VALUES IN (4) ENGINE = InnoDB,
    ->  PARTITION m5 VALUES IN (5) ENGINE = InnoDB,
    ->  PARTITION m6 VALUES IN (6) ENGINE = InnoDB,
    ->  PARTITION m7 VALUES IN (7) ENGINE = InnoDB,
    ->  PARTITION m8 VALUES IN (8) ENGINE = InnoDB,
    ->  PARTITION m9 VALUES IN (9) ENGINE = InnoDB,
    ->  PARTITION m10 VALUES IN (10) ENGINE = InnoDB,
    ->  PARTITION m11 VALUES IN (11) ENGINE = InnoDB,
    ->  PARTITION m12 VALUES IN (12) ENGINE = InnoDB) */;
Query OK, 0 rows affected (0.10 sec)

rang 파티션 테이블 2개

mysql> CREATE TABLE rcx (
    ->     a INT,
    ->     b INT,
    ->     c CHAR(3),
    ->     d INT
    -> )
    -> PARTITION BY RANGE COLUMNS(a,d,c) (
    ->     PARTITION p0 VALUES LESS THAN (5,10,'ggg'),
    ->     PARTITION p1 VALUES LESS THAN (10,20,'mmmm'),
    ->     PARTITION p2 VALUES LESS THAN (15,30,'sss'),
    ->     PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
    -> );
Query OK, 0 rows affected (0.15 sec)

3 더 적은 문자를 사용하여 범위 만들기

CREATE TABLE employees_by_lname (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)

rrree

테이블 구조 변경, 새 파티션 블록 추가

PARTITION BY RANGE COLUMNS (lname)  (
    PARTITION p0 VALUES LESS THAN ('g'),
    PARTITION p1 VALUES LESS THAN ('m'),
    PARTITION p2 VALUES LESS THAN ('t'),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

4 목록 열 분할

ALTER TABLE employees_by_lname PARTITION BY RANGE COLUMNS (lname)  (
    PARTITION p0 VALUES LESS THAN ('g'),
    PARTITION p1 VALUES LESS THAN ('m'),
    PARTITION p2 VALUES LESS THAN ('t'),
 PARTITION p3 VALUES LESS THAN ('u'),
    PARTITION p4 VALUES LESS THAN (MAXVALUE)
);

character column
CREATE TABLE customers_1 (
    first_name VARCHAR(25),
    last_name VARCHAR(25),
    street_1 VARCHAR(30),
    street_2 VARCHAR(30),
    city VARCHAR(15),
    renewal DATE
)

날짜 열

PARTITION BY LIST COLUMNS(city) (
    PARTITION pRegion_1 VALUES IN('Oskarshamn', 'H?gsby', 'M?nster?s'),
    PARTITION pRegion_2 VALUES IN('Vimmerby', 'Hultsfred', 'V?stervik'),
    PARTITION pRegion_3 VALUES IN('N?ssj?', 'Eksj?', 'Vetlanda'),
    PARTITION pRegion_4 VALUES IN('Uppvidinge', 'Alvesta', 'V?xjo')
);

CREATE TABLE customers_2 (
    first_name VARCHAR(25),
    last_name VARCHAR(25),
    street_1 VARCHAR(30),
    street_2 VARCHAR(30),
    city VARCHAR(15),
    renewal DATE
)

5 HASH 분할

PARTITION BY LIST COLUMNS(renewal) (
    PARTITION pWeek_1 VALUES IN('2010-02-01', '2010-02-02', '2010-02-03',
        '2010-02-04', '2010-02-05', '2010-02-06', '2010-02-07'),
    PARTITION pWeek_2 VALUES IN('2010-02-08', '2010-02-09', '2010-02-10',
        '2010-02-11', '2010-02-12', '2010-02-13', '2010-02-14'),
    PARTITION pWeek_3 VALUES IN('2010-02-15', '2010-02-16', '2010-02-17',
        '2010-02-18', '2010-02-19', '2010-02-20', '2010-02-21'),
    PARTITION pWeek_4 VALUES IN('2010-02-22', '2010-02-23', '2010-02-24',
        '2010-02-25', '2010-02-26', '2010-02-27', '2010-02-28')
);

PARTITIONS 절을 포함하지 않으면 파티션 수는 아래와 같이 기본적으로 1로 설정됩니다.

int column,it can use digital function
CREATE TABLE employeesint (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY HASH(MOD(store_id,4))
PARTITIONS 4;

날짜 열

CREATE TABLE employeestest (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY HASH(store_id);

모든 데이터 행 자르기: alter table rcx truncate PARTITION;

6개 선형 해시 파티셔닝

CREATE TABLE employees2 (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY HASH( YEAR(hired) )
PARTITIONS 4;

PARTITION BY LINEAR HASH( 연도(고용) )
파티션 4;

expr이라는 표현식이 주어지면 선형 해싱을 사용할 때 레코드가 저장되는 파티션은 num개의 파티션 중 파티션 번호 N이며, 여기서 N은 다음 알고리즘에 따라 파생됩니다.
(1) 다음 제곱을 구합니다. num보다 큰 2입니다. 이 값은 다음과 같이 계산할 수 있습니다.
V = POWER(2, CEILING(LOG(2, num)))
(num이 13이라고 가정합니다. ,13)은 3.7004397181411입니다. CEILING(3.7004397181411)은 4이고 V = POWER(2,4), 즉 16입니다.)
(2) N = F(column_list) & (V - 1)로 설정합니다. >(3) N >= num인 경우:
V = CEIL(V / 2) 설정
N = N & (V - 1) 설정

[참고] SQL에서 &의 계산 원리는 다음과 같습니다. 예를 들어
10진수를 2진수로 변환하면 다음과 같은 결과가 나옵니다. http://www.php.cn/

먼저 오른쪽으로 정렬합니다. 예를 들어 0011과 1000이 되며 각 자릿수에 따라 판단하고 둘 다 1이면 결과가 나옵니다. 해당 위치는 1, 그렇지 않으면 0
1011과 1000이면 결과는 1000
0110과 1010이면 결과는 0010
그런데 3은 0011, 8은 1000, 따라서 3&8의 결과는 0입니다.

CEILING(X) CEIL(X): X보다 작지 않은 가장 작은 정수 값을 반환합니다.

LOG(X) LOG(B,X): 하나의 매개변수로 호출하면 이 함수는 X의 자연 로그를 반환합니다.
POWER(X,Y): X를 Y로 거듭제곱한 결과 값을 반환합니다.

데이터가 분포된 타일 계산 방법:
선형 해시 파티셔닝을 사용하고 6개의 파티션을 갖는 테이블 t1이 다음 명령문을 사용하여 생성되었다고 가정합니다.

CREATE TABLE employees_linear (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)

이제 col3 열 값이 '2003-04-14' 및 '1998-10-19'인 t1에 두 개의 레코드를 삽입하려고 한다고 가정합니다. 첫 번째에 대한 파티션 번호는 다음과 같습니다.

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
    PARTITION BY LINEAR HASH( YEAR(col3) )
    PARTITIONS 6;

두 번째 레코드가 저장된 파티션 수는 다음과 같이 계산됩니다.

 V = POWER(2, CEILING( LOG(2,6) )) = 8
 N = YEAR('2003-04-14') & (8 - 1)
    = 2003 & 7
    = 3
 (3 >= 6 is FALSE: record stored in partition #3)

선형 해시에 의한 파티셔닝의 장점은 파티션의 추가, 삭제, 병합 및 분할이 훨씬 빨라진다는 것입니다. 이는 매우 많은 양(테라바이트)의 데이터가 포함된 테이블을 처리할 때 유용할 수 있습니다. 해당 데이터는 일반 해시 파티셔닝을 사용하여 얻은 분포에 비해 파티션 간에 균등하게 분산될 가능성이 적습니다.

질문 중 하나: MySQL은 어떻게 임시 테이블을 사용하지 않고 SQL을 사용합니까? ? 파티션 필드를 삭제하시겠습니까? 분할된 테이블을 일반 테이블로 바꾸시겠습니까?

위는 파티션 필드의 MySQL 파티션 테이블 파티션 온라인 수정에 대한 자세한 내용입니다. 관련 내용은 PHP 중국어 홈페이지(www.php.cn)를 주목해주세요!