MySQL 퍼포먼스 최적화를 읽고(5) - 스키마 레벨에서의 접근법

Just a devlog 2023. 10. 24. 17:11

인덱스는 적재적소에 배치하자

InnoDB에서 PK는 성능에 직접적인 영향을 준다

InnoDB에서는 PK순으로 데이터가 저장된다. PK는 데이터에 접근하는 물리적인 주소로 사용된다고 봐도 된다.

MSSQL의 클러스터 인덱스, 오라클의 Index Organized Table 개념이 MySQL에서는 PK로 구현된 것이다. 즉, 인덱스 순서로 데이터가 정렬되어 디스크에 저장된다.

(칼럼1, 칼럼2) 복합키를 PK로 하는 테이블이 있다면 위와 같은 형태로 데이터가 저장된다. 여기서 PK가 (21, 31)인 데이터가 입력된다면?

1건의 데이터를 저장하기 위해 4건의 데이터가 물리적으로 이동하게 된다. 위처럼 인덱스 칼럼의 변경이 자주 일어나면 DB 성능이 상당히 저하된다.

만약 테이블의 PK가 설정되어 있지 않은 경우 InnoDB에선 아래와 같이 처리된다.

인덱스 중 Unique 속성이 있는 Key를 PK로 사용
PK와 UK도 없다면 내부적으로 6Byte 크기의 대체 PK를 생성하여 사용

불필요한 인덱스는 과감하게 삭제하자

인덱스도 데이터라는 것을 명심해야 한다. 일반적인 데이터와는 다르게 메모리 상에서 수시로 구조가 변화하는 동적인 데이터이다.

CREATE table `test_0_index` (
    `i` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `j` int(10) unsigned NOT NULL,
    `s` varchar(64) NOT NULL,
    `d` datetime NOT NULL,
    PRIMARY KEY (`i`),
    key idx_j(j),
    key idx_d(d),
    key cidx_jd(j, d),
    key cidx_dj(d, j)
);

여기서 idx_j와 idx_d는 불필요한 인덱스일 가능성이 높다. j 혹은 d 만으로 데이를 조회하는 쿼리 요청이 와도 cidx_jd와 cidx_dj가 복합 인덱스로 구성되어 있기 때문에 활용이 가능하다.

하지만 d로 데이터를 검색한 결과를 i로 정렬해서 가져오는 게 목적이라면 cidx_dj 키가 오히려 불필요한 인덱스이다. idx_d 인덱스로 검색한 결과는 PK 순으로 조회되지만, cidx_dj 인덱스로 검색한 결과는 j 순서로 나오기 때문이다.

따라서 인덱스를 정리하기 전 인덱스의 사용 목적을 명확히 해두어야 한다.

인덱스 개수에 따른 데이터 변경 테스트

인덱스 하나가 테이블 데이터 입력 성능에 얼마나 영향을 미칠까?

## 테이블 1 - Primary Key만 있는 테이블 생성
CREATE TABLE `test_0_index` (
    `i` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `j` int(10) unsigned NOT NULL,
    `s` varchar(64) NOT NULL,
    `d` datetime NOT NULL,
    PRIMARY KEY (`i`)
);

## 인덱스 개수별 테스트를 위한 스키마 복제
CREATE TABLE test_1_index LIKE test_0_index;
CREATE TABLE test_2_index LIKE test_0_index;
CREATE TABLE test_3_index LIKE test_0_index;

## 테이블 1 - 인덱스 1개 생성
ALTER TABLE test_1_index ADD key j1(j);

## 테이블 2 - 인덱스 2개 생성
ALTER TABLE test_2_index ADD key j1(j), ADD key j2(j);

## 테이블 3 - 인덱스 3개 생성
ALTER TABLE test_3_index ADD key j1(j), ADD key j2(j), ADD key j3(j);

** 동일 칼럼에 중복해서 인덱스를 생성하더라도 인덱스명이 겹치치 않는다면 에러가 발생하지 않는다.

위처럼 생성한 테이블에 테스트 데이터 131,072건의 데이터를 INSERT 한 결과는 아래와 같다.

인덱스가 늘어날수록 데이터 INSERT 성능은 크게 떨어진다. (인덱스에 따른 재정렬 필요)

인덱스도 CPU와 메모리 자원을 소비하는 데이터라는 점을 반드시 기억해야 한다.

테이블 파티셔닝을 활용해 대용량 데이터를 관리하자

파티셔닝 간단히 이해하기

파티셔닝은 하나의 커다란 데이터를 여러 개의 데이터로 분할 저장함으로써 데이터 선택 효율을 높이는 기법이다.

그림과 같이 데이터를 분할 저장하면 특정 데이터를 찾을 때 필요한 데이터 파일에만 접근하기 때문에 효율이 높아진다.

InnoDB에서는 PK 순으로 데이터가 저장되기 때문에 무작위로 PK를 생성하여 INSERT를 수행하면 성능이 급격히 저하될 수 있다. 이 경우에 테이블에 파티셔닝을 적용하면 비효율을 어느 정도 극복할 수 있다.

PK 외에 Unique 속성이 반드시 필요하다면 파티셔닝 때문에 로직이 복잡해질 수 있다. 또한 PK의 크기가 커질 수 있기 때문에 전체적인 인덱스 사이즈도 커질 수 있다. InnoDB에서는 PK 외에 보조 인덱스는 PK를 값으로 가지기 때문이다. (인덱스는 인덱스 키 필드 + PK로 구성돼 있음)

파티셔닝 제약사항

파티셔닝 키는 PK와 연관된 칼럼이어야 한다.
파티셔닝 사용 시 Unique Key 혹은 Foreign Key와 같은 제약 조건을 추가할 수 없다. (UK가 PK로 사용되는 경우 제외)
파티셔닝 적용 시 풀텍스트 인덱싱 혹은 스파셜 인덱싱 기능은 사용할 수 없다.
데이터 조회 시 반드시 파티셔닝 키가 포함되어야 하며 그렇지 않은 경우 전체 데이터 파일을 스캔한다.

파티셔닝을 적절히 사용하여 성능을 유지하자

InnoDB에서 PK가 순차적으로 들어올 수 없는 환경에서 파티셔닝은 큰 효과가 있다. PK 순으로 데이터가 저장되므로 무작위로 PK가 들어오면 디스크 I/O를 최대한 줄일 수 있기 때문이다.

무작위 PK를 가지고, 날짜 단위로 저장되는 테이블을 효과적으로 파티셔닝하는 방안에 대해서 알아보자.

CREATE TABLE partition_test (
    ID varchar(50) NOT NULL,
    NAME varchar(40) NOT NULL,
    TYPE varchar(20) NOT NULL,
    DETAIL varchar(255) NOT NULL,
    REG_TIME datetime NOT NULL,
    KEY(ID)
) ENGINE = InnoDB PARTITION BY RANGE (to_days(REG_TIME)) (
    PARTITION P_MIN 	 VALUES LESS THAN (to_days('19000101')), # 불필요한 파티셔닝 데이터 스캔 방지
    PARTITION P_20110516 VALUES LESS THAN (to_days('20120517')), 
    PARTITION P_20110517 VALUES LESS THAN (to_days('20120518')), 
    PARTITION P_20110518 VALUES LESS THAN (to_days('20120519')), 
    PARTITION P_20110519 VALUES LESS THAN (to_days('20120520')), 
    PARTITION P_20110520 VALUES LESS THAN (to_days('20120521')), 
    PARTITION P_20110521 VALUES LESS THAN (to_days('20120522')), 
    PARTITION P_20110522 VALUES LESS THAN (to_days('20120523')), 
    PARTITION P_MAX   	 VALUES LESS THAN MAXVALUE
);

날짜 데이터를 파티셔닝한 상태에서 RANGE 파티셔닝 옵션으로 BETWEEN과 같은 범위 검색을 하면 가장 앞 단의 파티셔닝 파일을 우선 스캔한다. 그런데 가장 앞단의 파티셔닝 파일에 수천만건의 데이터가 저장되어 있다면 범위 검색을 할 때마다 대용량 데이터를 매번 스캔해야 하는 비효율이 발생한다. 이러한 불필요한 스캔을 방지하기 위해 P_MIN이라는 깡통 파티셔닝을 생성해 두었다.

파티셔닝 실행 계획 확인

EXPLAIN PARTITIONS
SELECT * FROM partition_test
WHERE REG_TIME BETWEEN '20120517' AND '20120518';

+----+-------------+----------------+-----------------------------+
| ID | Select_type | Table          | Partitions                  |
+----+-------------+----------------+-----------------------------+
| 1  | SIMPLE      | partition_test | P_MIN,P_20110517,P_20110518 |
+----+-------------+----------------+-----------------------------+

파티셔닝 추가
파티셔닝을 하나 더 추가하기 위해 P_MAX 파티셔닝 테이블을 재정의한다. P_MAX 파티션에 데이터가 많으면 파티셔닝이 추가되는 동안 테이블 잠금이 발생하여 서비스에 영향을 줄 수 있으므로 재정의 전에 반드시 확인해야 한다.

ALTER TABLE partition_test REORGANIZE PARTITION P_MAX INTO (
    PARTITION P_20120523 VALUES LESS THAN (to_days('20120524')),
    PARTITION P_MAX 	 VALUES LESS THAN MAXVALUE
);

파티셔닝 삭제

ALTER TABLE partition_test DROP partition P_20110516;

파티셔닝 관리 스크립트

파티셔닝 테이블을 일일이 수동으로 생성하거나 삭제할 수 있지만, 아래와 같이 스크립트를 이용하여 하루에 한 번씩 파티셔닝 파일을 자동으로 생성할 수도 있다.

#!/bin/sh

MYSQL_HOME = "/usr/local/mysql"
EXECUTE_QUERY = "$MYSQL_HOME/bin/mysql -u아이디 -p패스워드 -e " 

## 서버 목록 배열 "TABLE명:파티셔닝필드"로 정의
SERVERS = (
    "table_name01:column01" 
    "table_name02:column02" 
    "table_name03:column03"
) 

## 신규 파티셔닝 날짜
new_sdays = $1
new_edays = `expr $new_sdays + 1` 

## 지울 파티셔닝 날짜
del_sdays = $2
del_edays = `expr $del_sdays - 1` 

new_sdate = `date -d " + $new_sdays days" + %Y%m%d` 
new_edate = `date -d "+$new_edays days" + %Y%m%d` 
del_sdate = `date -d "-$del_sdays days" + %Y%m%d` 
del_edate = `date -d "-$del_edays days" + %Y%m%d` 

for INFO in ${SERVERS [@]}

do

    tbl = `echo $INFO | cut -d: -f1` 
    col = `echo $INFO | cut -d: -f2`

    ## 신규 파티션 생성 쿼리
    query = " ALTER table testdb.$tbl REORGANIZE PARTITION P_MAX into (
                PARTITION P_$new_sdate VALUES LESS THAN (TO_DAYS('$new_edate')),
                PARTITION P_MAX VALUES LESS THAN MAXVALUE
            )" 

    ## 적용
    $EXECUTE_QUERY "$query" 
        
    ## 데이터를 다른 DB로 복사하는 쿼리
    query = " INSERT INTO backupdb.$tbl
                SELECT * 
                FROM testdb.$tbl
                WHERE $col >= '$del_sdate'
                AND $col < '$del_edate' 
            " 

    ## 적용
    $EXECUTE_QUERY "$query" 
    
    ## 파티션 삭제 쿼리
    query = " ALTER table testdb.$tbl DROP PARTITION P_$del_sdate"
    
    ## 적용
    $EXECUTE_QUERY "$query" 

done

트리거로 제약을 뛰어넘는 날개를 달아주자

CRC32로 인덱스 사이즈를 줄여보자

인덱스도 메모리, 디스크, CPU 연산이 필요한 데이터이므로 사이즈가 커질수록 메모리 효율은 떨어지고 디스크 I/O도 많아질 수밖에 없다.

InnoDB에서는 인덱스가 실제 데이터보다 큰 경우가 자주 있다. 따라서 인덱스 사이즈를 확인하고 이를 최대한 줄이는 것이 성능상 유리하다.

현재 사용 테이블 현황 조회

SELECT
    CONCAT(TABLE_SCHEMA,'.',TABLE_NAME) TABLE_NAME,
    CONCAT(ROUND(TABLE_ROWS/1000000, 2),'M') ROWS,
    CONCAT(ROUND(DATA_LENGTH/(1024*1024), 2),'M') DATA,
    CONCAT(ROUND(INDEX_LENGTH/(1024*1024), 2),'M') IDX,
    CONCAT(ROUND((DATA_LENGTH+INDEX_LENGTH)/(1024*1024), 2),'M') TOTAL_SIZE,
    ROUND(INDEX_LENGTH/DATA_LENGTH, 2) IDXFRAC,
    ENGINE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema')
ORDER BY DATA_LENGTH+INDEX_LENGTH DESC;

인덱스 사이즈를 줄이는 첫 번째는 필요 없는 인덱스를 제거하는 것이고, 두 번째는 인덱스 데이터의 사이즈를 줄이는 것이다. 문자열 인덱스의 경우 트리거 + CRC32로 사이즈를 줄일 수 있다.

트리거는 테이블에 특정 액션이 발생하는 경우 동시에 처리해야 하는 작업을 의미하고, CRC(Cyclic Redundancy Check)는 일반적으로 네트워크를 통해 데이터를 전송하는 경우 전송하는 데이터에 오류 여부를 확인하기 위해 사용하는 체크값 결정 방식을 말한다.

MySQL에서는 CRC32 함수를 제공하며, 이는 특정 문자열을 넣었을 때 32비트의 UNSIGNED 타입으로 코드를 리턴해준다.

테이블 생성(1) - ASIS

CREATE TABLE test (
    seq int NOT NULL primary key auto_increment,
    user_no int NOT NULL,
    url varchar(128) NOT NULL,
    reg_date datetime NOT NULL,
    key ix_url(url)
)

위 테이블에서는 데이터 한건이 쌓일 때마다 url을 인덱싱 하기 위해 url 데이터 사이즈 + pk 4 Byte 공간을 더 소모한다. 데이터가 지속적으로 누적되면 DB 성능이 급격히 저하된다.

테이블 생성(2) - TOBE

CREATE TABLE test (
    seq int NOT NULL primary key auto_increment,
    user_no int NOT NULL,
    url varchar(128) NOT NULL,
    reg_date datetime NOT NULL,
    url_crc unsigned int NULL,
    key ix_url(url_crc)
)

CRC32 함수의 결괏값을 저장하기 위한 UNSIGNED INT 타입의 칼럼을 추가하고, 이 칼럼을 인덱스로 지정해 준다.

트리거 생성 - 데이터 INSERT/UPDATE 시 CRC32 함수 실행

DELIMITER $$

## INSERT 트리거
CREATE TRIGGER trg_test_insert
BEFORE INSERT ON test
FOR EACH ROW
BEGIN
    SET NEW.url_crc = CRC32(LOWER(TRIM(NEW.url)));
END$$

## UPDATE 트리거
CREATE TRIGGER trg_test_update
BEFORE UPDATE ON test
FOR EACH ROW
BEGIN
    SET NEW.url_crc = CRC32(LOWER(TRIM(NEW.url)));
END$$

DELIMITER;

트리거를 사용하여 애플리케이션 레벨에서 누락할 수 있는 상황을 방지할 수 있다.

데이터 조회

SELECT * 
FROM test
WHERE 
    url = 'http://gywn.net'
    AND url_crc = CRC32(LOWER(TRIM('http://gywn.net')))
;

URL에 대응되는 CRC 코드는 4바이트의 UNSIGNED 정수 값이기 때문에 낮은 확률이지만 중복될 수도 있다. 때문에 기존 url 조건을 통해 한번 더 필터링해준다. (중복 빈도는 약 43억 번에 한번)

통계 테이블을 적절히 사용하자

서비스에서 매번 데이터의 총 갯수를 세는 로직이 필요한 경우, 통계 테이블을 적절히 도입한다면 효율적으로 데이터 현황을 조회할 수 있다.

위 그림처럼 데이터를 처리하기 위해서는 로직의 일부를 변경해야 한다. 기존 로직에서 데이터를 INSERT 하는 로직만 있었다면, 데이터 INSERT 이후 데이터 카운트를 변경하는 로직을 추가해야 한다.

검색 기록 테이블

CREATE TABLE log (
    no int NOT NULL primary key auto_increment,
    name varchar('16') NOT NULL,
    cont text NOT NULL,
    regdate date NOT NULL
);

특정 검색어의 통계 데이터 조회

SELECT count(*) FROM log WHERE name = 'RED';

만약 애플리케이션에서 위 쿼리를 자주 호출한다면 서비스 성능에 큰 영향을 준다. 이 문제를 해결하기 위해 통계성 테이블을 추가해 준다.

검색 기록 통계 테이블 생성

CREATE TABLE log_stat (
    name varchar('16') NOT NULL,
    name_count int NOT NULL,
    primary key(name)
);

그리고 검색 기록을 INSERT 할 때 통계 테이블에서 관련 건수를 업데이트해준다.

INSERT INTO log_stat (name, name_count) VALUES ('RED', 1)
ON DUPLICATE KEY UPDATE name_count = name_count + 1;

이후 특정 검색어에 대한 통계는 아래와 같이 수행할 수 있다.

SELECT name_count FROM log_stat WHERE name = 'RED';