본문 바로가기
DB

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

Just a devlog 2023. 10. 24. 16:12

데이터 흐름을 이해하자

조인 성능 테스트

조인은 여러 테이블에서 하나의 결과를 가져올 수 있다는 점에서 편리하지만, 데이터가 누적됨에 따라 쿼리 성능이 점진적으로 저하된다.

 

SELECT 
    a.*
    , d.text_field AS text_field1
    , e.text_field AS text_field2
FROM tab01 a
INNER JOIN tab02 b ON b.tab01_seq = a.seq
INNER JOIN tab03 c ON c.tab02_seq = b.seq
LEFT JOIN tab04 d ON d.tab03_seq = c.seq
LEFT JOIN tab05 e ON e.tab03_seq = c.seq
WHERE 
    a.status IN ('01', '02')
    AND b.status NOT IN ('99')
ORDER BY c.regdate, b.regdate DESC
LIMIT 0, 20;
+----+------------+-------+-----------+------+-----------------------------------+
| ID | Select_type| Table | Key       | Rows | Extra                             |
+----+------------+-------+-----------+------+-----------------------------------+
| 1  | SIMPLE     | a     | NULL      | 8652 | Using where; Using temporary; Using filesort |
| 1  | SIMPLE     | b     | tab01_seq | 1    | Using where                       |
| 1  | SIMPLE     | c     | tab02_seq | 1    |                                   |
| 1  | SIMPLE     | d     | tab03_seq | 1    |                                   |
| 1  | SIMPLE     | e     | tab03_seq | 1    |                                   |
+----+------------+-------+-----------+------+-----------------------------------+

ORDER BY를 처리하기 위해 Temporary Table과 Filesort가 발생한다. 텍스트 혹은 대용량 데이터와 같은 덩치가 큰 칼럼이 있다면 성능이 급격히 저하된다.

 

SELECT
    aa.*
    , dd.text_field AS text_field1
    , ee.text_field AS text_field2
FROM(
    SELECT
        a.seq tab01_seq
        , c.seq tab03_seq
    FROM tab01 a
    INNER JOIN tab02 b ON b.tab01_seq = a.seq
    INNER JOIN tab03 c ON c.tab02_seq = b.seq
    WHERE
        a.status IN ('01', '02') 
        AND b.status NOT IN ('99')
    ORDER BY c.regdate, b.regdate DESC
    LIMIT 0, 20
) step01
INNER JOIN tab01 aa ON aa.seq = step01.tab01_seq
LEFT JOIN tab04 dd ON dd.tab03_seq = step01.tab03_seq
LEFT JOIN tab05 ee ON ee.tab03_seq = step01.tab03_seq;
+----+-------------+------------+-----------+------+--------------------------------+
| ID | Select_type | Table      | Key       | Rows | Extra                          |
+----+-------------+------------+-----------+------+--------------------------------+
| 1 | PRIMARY      | <derived2> | NULL      | 20   |                                |
| 1 | PRIMARY      | aa         | PRIMARY   | 1    |                                |
| 1 | PRIMARY      | dd         | tab03_seq | 1    |                                |
| 1 | PRIMARY      | ee         | tab03_seq | 1    |                                |
| 2 | DERIVED      | a          | NULL      | 8652 | Using where; Using temporary; Using ilesort |
| 2 | DERIVED      | b          | tab01_seq | 1    | Using where                    |
| 2 | DERIVED      | c          | tab02_seq | 1    |                                |
+----+-------------+------------+-----------+------+--------------------------------+

FROM 절의 서브쿼리에서 조인에 필요한 데이터만 가져와서 외부에서 다시 조인한다.

 

테이블 사이즈가 횡적으로 거대(단일 Row 크기가 큰 경우)하다면 쿼리 성능은 크게 향상된다. 다수의 조인을 피할 수 없다면 필요한 데이터만 가져와서 데이터를 처리하고 다시 조인을 수행하는 것이 성능 향상의 포인트다.

데이터 연산을 최소한으로 유도

상용 DBMS에서 흔히 제공하는 해시 조인, 머지 조인 같은 조인 알고리즘이 MySQL에는 구현되어 있지 않다. 따라서 Nested Loop Join 횟수를 줄이는 것이 쿼리 성능을 향상시키는 첫걸음이다.

 

2012년 이전에 bad user와 blacked user로 등록된 회원 10명을 조회하는 쿼리

SELECT ui.*
FROM (
    SELECT aa.user_no
    FROM ( 
        (
            SELECT user_no
            FROM bad_user
            WHERE insert_date < '2012-01-01 00:00:00'
        )
        UNION ALL 
        (
            SELECT user_no
            FROM blacked_user
            WHERE insert_date < '2012-01-01 00:00:00'
        )
    ) aa
) bad_case
INNER JOIN user_info ui ON bad_case.user_no = ui.userno
LIMIT 10;
+------+-------------+-------------+--------+--------+------------------------+
| ID   | Select_type | Table       | Type   | Rows   | Extra                  |
+------+-------------+-------------+--------+--------+------------------------+
| 1    | PRIMARY     | <derived2>  | ALL    | 67857  |                        |
| 1    | PRIMARY     | ui          | eq_ref | 1      |                        |
| 2    | DERIVED     | bad_user    | index  | 131373 |Using where; Using index|
| 3    | UNION       | blacked_user| index  | 131373 |Using where; Using index|
| NULL | UNION RESULT| <union2,3>  | ALL    | NULL   |                        |
+------+-------------+-------------+--------+--------+------------------------+

데이터가 적다면 문제없지만 UNION ALL의 결과 데이터가 크다면 문제가 발생한다. 위 예제에선 6만 건에 해당하는 데이터를 Temporary Table에 저장하고 외부에서 조인으로 데이터를 처리하고 있다.

 

SELECT ui.*
FROM (
    SELECT aa.user_no
    FROM ( 
        (
            SELECT user_no
            FROM bad_user
            WHERE insert_date < '2012-01-01 00:00:00'
            LIMIT 10
        )
        UNION ALL 
        (
            SELECT user_no
            FROM blacked_user
            WHERE insert_date < '2012-01-01 00:00:00'
            LIMIT 10
        )
    ) aa
) good_case
INNER JOIN user_info ui ON good_case.user_no = ui.userno
LIMIT 10;
+----+--------------+--------------+--------+--------+-------------------------+
| ID | Select_type  | Table        | Type   | Rows   | Extra                   |
+----+--------------+--------------+--------+--------+-------------------------+
| 1  | PRIMARY      | <derived2>   | ALL    | 10     |                         |
| 1  | PRIMARY      | ui           | eq_ref | 1      |                         |
| 2  | DERIVED      | bad_user     | index  | 131373 | Using where; Using index|
| 3  | UNION        | blacked_user | index  | 131373 | Using where; Using index|
|NULL| UNION RESULT | <union2,3>   | ALL    | NULL   |                         |
+----+--------------+--------------+--------+--------+-------------------------+

최종적인 데이터 결과는 절대 10건을 넘을 수 없다. 그렇다면 UNION ALL로 취합되는 결과를 제한하자. 최종 10건의 데이터를 가져오기 위해서는 데이터 전체가 아닌 최대 20건 만으로도 충분하다.

 

조인 시 반드시 필요한 데이터만 가져와 데이터를 처리한다면 INNER JOIN에서 6만 번 발생할 Nested Loop Join을 20건 정도로 대폭 줄일 수 있다.

 

Outer Join이 반드시 필요한 지 파악하자

SELECT
    M.MASTER_NO
    , M.TITLE
    , MI.PATH
    , M.REGDATE
    , CM.TYPE
FROM MAIN M
INNER JOIN TAB01 CM ON CM.MASTER_NO = M.MASTER_NO
LEFT OUTER JOIN TAB02 MI ON M.MASTER_NO = MI.MASTER_NO
WHERE M.DEL_YN = 'N'
ORDER BY M.MASTER_NO DESC
LIMIT 10000, 10;
+----+-------------+---------+------------+--------+
| ID | Select_type | Table   | Key        | Rows   |
+----+-------------+---------+------------+--------+
| 1  | SIMPLE      | M       | PRIMARY    | 5005   |
| 1  | SIMPLE      | CM      | master_no  | 1      |
| 1  | SIMPLE      | MI      | master_no  | 2      |
+----+-------------+---------+------------+--------+

+-----------------------------------------+-----------+
| Status                                  | Duration  |
+-----------------------------------------+-----------+
| ...                                     | ...       |
| sorting result                          | 0.070724  |
| sending data                            | 0.153553  |
| ...                                     | ...       |
+-----------------------------------------+-----------+

데이터를 10,000번째 위치부터 10건 가져오는 쿼리의 예시이다. 결과적으로는 불필요한 10,000번의 Outer Join이 발생한다.

 

SELECT
    A.MASTER_NO
    , A.TITLE
    , MI.PATH
    , A.REGDATE
    , A.TYPE
FROM (
    SELECT
        M.MASTER_NO
        , M.TITLE
        , M.REGDATE
        , CM.TYPE
    FROM MAIN M
    INNER JOIN tab01 CM ON CM.MASTER_NO = M.MASTER_NO
    ORDER BY M.MASTER_NO DESC
    LIMIT 10000, 10
) A
LEFT OUTER JOIN tab02 MI ON A.MASTER_NO = MI.MASTER_NO;
+----+-------------+--------------+------------+--------+
| ID | select_type | table        | key        | rows   |
+----+-------------+--------------+------------+--------+
| 1  | PRIMARY     | <derived2>   | NULL       | 10     |
| 1  | PRIMARY     | MI           | master_no  | 2      |
| 1  | DERIVED     | M            | PRIMARY    | 22901  |
| 1  | DERIVED     | CM           | master_no  | 1      |
+----+-------------+--------------+------------+--------+

+-----------------------------------------+-----------+
| Status                                  | Duration  |
+-----------------------------------------+-----------+
| ...                                     | ...       |
| sorting result                          | 0.000010  |
| sending data                            | 0.062445  |
| ...                                     | ...       |
+-----------------------------------------+-----------+

쿼리 실행 계획 상 더 안 좋은 것처럼 보이지만 프로파일링 결과를 보면 성능이 더 좋아진 것을 확인할 수 있다. 따라서 필요한 데이터만 가져와서 Outer Join을 적용한다면 성능을 높일 수 있다.

 

서브쿼리를 적극 활용하자

서브쿼리는 DB 내부적으로 Temporary Table을 생성하여 데이터를 처리하기 때문에 과용하면 좋지 않지만, 적절하게 사용한다면 성능 향상에 도움이 된다.

 

사용자 테이블과 사용자 접속 내용을 기록하는 테이블이 있고, 이번 달 각 사용자의 접속 현황 통계를 일자별로 조회하는 쿼리를 작성해 보자.

SELECT
    date_format(l.access_date, '%Y-%m-%d') access_date,
    u.user_no,
    u.user_name,
    count(*) access_count
FROM user u
INNER JOIN user_access_log l ON u.user_no = l.user_no
WHERE l.access_date >= date_format(now(), '%Y-%m-01')
GROUP BY date_format(l.access_date, '%Y-%m-%d'), u.user_no, u.user_name;

user 테이블과 user_access_log 테이블을 조인한 후 GROUP BY 구문을 처리하기 때문에 데이터 누적에 따라 쿼리 성능이 급격히 떨어진다.

 

SELECT
    l.access_date,
    u.user_no,
    u.user_name,
    l.access_count
FROM (
    SELECT
        date_format(access_date, '%Y-%m-%d') access_date,
        user_no,
        count(*) access_count
    FROM user_access_log
    WHERE access_date >= date_format(now(), '%Y-%m-%d')
    GROUP BY date_format(access_date, '%Y-%m-%d')
) l
INNER JOIN user u ON u.user_no = l.user_no;

user 테이블과 무관하게 GROUP BY 구문을 먼저 처리하기 때문에 서브 쿼리 결과 수를 줄여 성능을 향상 시킬 수 있다.

 

서브쿼리를 맹신하지 말자

무분별한 서브쿼리는 최악의 사태로 가는 시작

'일주일 사이에 방문 고객 중 3일 전에 방문한 고객'과 관련된 정보를 찾기 위한 쿼리를 살펴보자.

SELECT
    date_format(date_sub(now(), interval 3 day), '%Y%m%d') statDate,
    visitor_cnt1,
    visitor_cnt2,
    visitor_cnt2 / visitor_cnt1 * 100 pst
FROM (
    SELECT
        count(sub01.userId) visitor_cnt1,
        sum(if(sub02.userId is not null, 1, 0)) visitor_cnt2
    FROM (
        SELECT distinct(userId) userId
        FROM log
        WHERE
            userId is NOT NULL
            AND date >= date_format(date_sub(now(), interval 7 day), '%Y%m%d') # 7일간 방문한 사람
    ) sub01
    LEFT JOIN (
        SELECT distinct(userId) userId
        FROM log
        WHERE
            userId is NOT NULL
            AND date >= date_format(date_sub(now(), interval 3 day), '%Y%m%d') # 3일전 방문한 사람
            AND date < date_format(date_sub(now(), interval 2 day), '%Y%m%d')
    ) sub02 ON sub01.userId = sub02.userId
) result
+----+------------+-----------+-------+------+------+---------------------------+
| ID | Select_type| Table     | Type  | Key  | Rows | Extra                     |
+----+------------+-----------+-------+------+------+---------------------------+
| 1  | PRIMARY    | <derived2>| system| NULL |1     |                           |
| 2  | DERIVED    | <derived3>| ALL   | NULL |17467 |                           |
| 2  | DERIVED    | <derived4>| ALL   | NULL |8561  |                           |
| 4  | DERIVED    | log       | ALL   | NULL |125715|Using where;Using temporary|
| 3  | DERIVED    | log       | ALL   | NULL |125715|Using where;Using temporary|
+----+------------+-----------+-------+------+------+---------------------------+

+----------------------+-----------+
| Status               | Duration  |
+----------------------+-----------+
| ...                  |           |
| sending data         | 60.624725 |
| ...                  |           |
+----------------------+-----------+

userID 칼럼에 인덱스가 설정되어 있지만 서브쿼리 간 조인 시 인덱스를 활용하지 않고 있다. 이는 서브쿼리의 결과를 Temporary Table에 넣어서 데이터를 처리하기 때문이다. (Temporary Table에는 인덱스가 없다.)

 

또한 프로파일링 결과를 보면 sending data 과정에서 많은 시간을 소모한다. 즉 대부분의 실행 시간은 스토리지 엔진과 서버 엔진의 통신 부분에서 발생한다.

 

SELECT
    date_format(date_sub(now(), interval 3 day), '%Y%m%d') statDate,
    visitor_cnt1,
    visitor_cnt2,
    visitor_cnt2 / visitor_cnt1 * 100 pst
FROM (
    SELECT
        count(useId1) visitor_cnt1,
        sum(if(useId2 is not null, 1, 0)) visitor_cnt2
    FROM (
        SELECT
            distinct a.useId useId1,
            b.useId useId2
        FROM (
            SELECT distinct useId
            FROM log
            WHERE
                useId is NOT NULL
                AND date >= date_format(date_sub(now(), interval 7 day), '%Y%m%d')
        ) a
        LEFT JOIN log b on a.useId = b.useId
        AND date >= date_format(date_sub(now(), interval 3 day), '%Y%m%d')
        AND date < date_format(date_sub(now(), interval 2 day), '%Y%m%d')
    ) aa
) result
+----+-------------+------------+--------+--------+--------+-------------------+
| ID | Select_type | Table      | Type   | Key    | Rows   | Extra             |
+----+-------------+------------+--------+--------+--------+-------------------+
| 1  | PRIMARY     | <derived2> | system | NULL   | 1      |                   |
| 2  | DERIVED     | <derived3> | ALL    | NULL   | 17467  |                   |
| 3  | DERIVED     | <derived4> | ALL    | NULL   | 17467  | Using temporary   |
| 3  | DERIVED     | b          | ref    | userId | 3      | Distinct          |
| 4  | DERIVED     | log        | ALL    | NULL   | 125715 | Using where; Using temporary |
+----+-------------+------------+--------+--------+--------+-------------------+

+----------------------+-----------+
| Status               | Duration  |
+----------------------+-----------+
| ...                  |           |
| sending data         | 0.015774  |
| ...                  |           |
+----------------------+-----------+

불필요한 서브쿼리(sub02)를 제거했고, 그 자리에 log 테이블로 대체하여 userId의 인덱스를 활용하고 있다.

 

MySQL의 경우 서브쿼리로 만들어진 Temporary Table까지 조인에 활용하는 것은 좋지 않은 습관이다. 서브쿼리가 생성한 Temporary Table은 MyISAM 스토리지 엔진의 인덱스가 없는 테이블이며, 테이블 간 데이터 처리를 Nested Loop Join으로만 처리하기 때문이다.

 

Temporary Table을 사용한다면 테이블 간 조인은 서브쿼리에서 시작하도록 유도하고, 기존 인덱스를 최대한 활용할 수 있도록 쿼리를 작성해야 한다.

 

데이터 처리량을 줄이는 것과 인덱스 활용을 극대화하는 것이 성능 향상의 주요 포인트이다.

 

성능 저하를 유발하는 불필요한 서브쿼리

오라클은 ROWNUM 키워드를 통해 데이터 결과 행에 번호를 쉽게 붙일 수 있지만, MySQL에는 ROWNUM 개념이 없다.

 

다음 쿼리는 ROWNUM 효과를 내기 위해 불필요한 서브쿼리를 사용한 예시이다.

SELECT
    @RNUM := @RNUM + 1 RNUM,
    ROW.*
FROM (
    SELECT @RNUM := 0
) R,
(
    SELECT M.*, MI.PATH, CM.TYPE
    FROM MAIN M
    INNER JOIN tab01 CM ON M.MASTER_NO = CM.MASTER_NO
    LEFT JOIN tab02 MI ON M.MASTER_NO = MI.MASTER_NO
    WHERE M.DEL_YN = 'N'
    ORDER BY M.MASTER_NO DESC
) ROW
LIMIT 100, 10
+----+-------------+------------+------------+--------+----------------------+
| ID | Select_type | Table      | Key        | Rows   | Extra                |
+----+-------------+------------+------------+--------+----------------------+
| 1  | PRIMARY     | <derived2> | NULL       | 1      |                      |
| 2  | PRIMARY     | <derived3> | NULL       | 9773   |                      |
| 3  | DERIVED     | M          | del_yn     | 15339  | Using filesort       |
| 3  | DERIVED     | CM         | master_no  | 1      |                      |
| 3  | DERIVED     | MI         | master_no  | 2      |                      |
| 4  | DERIVED     | NULL       | NULL       | NUll   | No tables used       |
+----+-------------+------------+------------+--------+----------------------+

+----------------------+-----------+
| Status               | Duration  |
+----------------------+-----------+
| ...                  |           |
| sorting result       | 0.110762  |
| sending data         | 0.203739  |
| ...                  |           |
+----------------------+-----------+

페이지 번호를 가져오기 위해 서브쿼리에서는 테이블 간 조인을 처음부터 끝까지 수행하면서 결과 값을 Temporary Table에 저장한다. 결과적으로는 엄청난 횟수의 조인 연산이 내부적으로 발생하는 것이다.

 

SELECT M.*, MI.PATH, CM.TYPE
FROM MAIN AS M
INNER JOIN tab01 AS CM ON M.MASTER_NO = CM.MASTER_NO
LEFT JOIN tab02 AS MI ON M.MASTER_NO = MI.MASTER_NO
WHERE M.DEL_YN = 'N'
ORDER BY M.MASTER_NO DESC
LIMIT 100, 10
+----+-------------+-------+------------+--------+----------------------------+
| ID | Select_type | Table | Key        | Rows   | Extra                      |
+----+-------------+-------+------------+--------+----------------------------+
| 1  | SIMPLE      | M     | del_yn     | 3748   | Using where; Using filesort|
| 1  | SIMPLE      | CM    | master_no  | 1      |                            |
| 1  | SIMPLE      | MI    | master_no  | 2      |                            |
+----+-------------+-------+------------+--------+----------------------------+

+----------------------+-----------+
| Status               | Duration  |
+----------------------+-----------+
| ...                  |           |
| sorting result       | 0.000019  |
| sending data         | 0.000009  |
| ...                  |           |
+----------------------+-----------+

ROWNUM을 구하기 위한 서브쿼리를 제거했다. 스캔하는 데이터 수와 sending data 처리 시간이 현저히 줄어든 것을 확인할 수 있다.

 

행 번호가 필요하다면 DB에서 무리하게 생성하는 것보다 애플리케이션 서버에서 생성하는 것이 MySQL의 성능을 높이는 데 유리하다.

 

때로는 Temporary Table도 적극 활용하자

동시에 많은 데이터를 일괄 변경할 때 Temporay Table은 유용하게 사용된다.

테스트 환경

CREATE TABLE dbatest (
    i int(11) NOT NULL AUTO_INCREMENT,
    c1 int(11) NOT NULL,
    c2 int(11) NOT NULL,
    c3 varchar(255) DEFAULT NULL,
    primary key (i)
) engine = InnoDB;
+-------------+----------+-------+-------+------------+
| Table_name  | Rows     | Data  | Idx   | Total_size |
+-------------+----------+-------+-------+------------+
| dba.dbatest | 10000105 | 1283M | 0.00M | 1283.00M   |
+-------------+----------+-------+-------+------------+

테이블에는 약 1,000만건의 데이터가 있으며 PK 외에 추가 인덱스는 생성하지 않았다. (PK 외 인덱스에서 발생할 수 있는 성능 저하 요소를 배제하기 위해서이다.)

UPDATE 성능 테스트

데이터 변경 작업을 무작위로 20건씩 수행하도록 테스트 시나리오를 구성했다.

 

Temporary Table을 사용하지 않고 UPDATE 성능을 측정

# Step 01: 조회 대상이 되는 데이터 20건을 무작위로 추출. 단, 데이터는 1 ~ 10,000,000 사이의 값
# Step 02: PreparedStatement를 사용하여 다음 쿼리를 20번 반복하여 수행
UPDATE dbatest 
SET 
    c1 = c1 + 10
    , c2 = c2 + 10000 
WHERE i = ?

 

Temporary Table을 사용하여 UPDATE 성능을 측정

# Step 01: Temporary Table 제거(DROP)
DROP TEMPORARY TABLE if exists tmp_ dbatest_12;

# Step 02: Temporary Table 생성(CREATE)
CREATE TEMPORARY TABLE tmp_dbatest_12 (
    i int not null,
    primary key(i) 
) engine = memory;

# Step 03: Temporary Table에 무작위로 데이터 20건을 입력(INSERT). 단, 데이터는 1 ~ 10,000,000 사이의 값
INSERT INTO tmp_dbatest_12 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15), (16), (17), (18), (19), (20);

# Step 04: Temporary Table과 테이블을 조인하여 데이터 변경
UPDATE dbatest a
INNER JOIN tmp_dbatest_12 b ON a.i = b.i
SET 
    a.c1 = a.c1 + 10
    , a.c2 = a.c2 + 10000

 

Temporary Table 사용 유무에 따른 UPDATE 성능 비교

매번 UPDATE 쿼리를 수행하는 것보다 Temporary Table을 사용하는 것이 상당한 효과를 보였다.

 

데이터를 READ / WRITE 처리할 때는 Permission Check → Open Table → Process → Close Table 과 같은 일련의 과정이 필요하다. 매번 UPDATE를 실행한다면 위 과정을 매번 반복하지만, Temporary Table을 사용한다면 단 한번만 위 과정이 발생한다.

 

10건 이상 UPDATE 하는 경우 Temporary Table을 활용하는 것이 효과적이다. (단, 트랜잭션 Isolation 레벨에 따른 데이터 처리 시 주의)

 

트랜잭션의 Isolation 레벨에서 테이블 잠금이 발생할 수 있음을 기억하자

MySQL의 InnoDB 스토리지 엔진을 사용할 때, 전체 데이터를 스캔하는 쿼리를 실행할 경우 Lock이 발생할 수 있다. 쿼리 실행이 종료될 때까지 다른 세션에서는 테이블 안의 데이터를 변경하지 못하기 때문에 서비스에 직접적인 영향을 미친다.


이는 InnoDB 스토리지 엔진의 기본 Isolation 레벨이 REPEATABLE-READ이기 때문에 발생하는 현상으로, 세션 변수 일부를 변경하여 사전에 문제를 해결할 수 있다.

현상

SHOW VARIABLES LIKE 'tx_isolation';

+-------------+---------------+
|Variable_name|Value          |
+-------------+---------------+
|tx_isolation |REPEATABLE-READ|
+-------------+---------------+

Isolation 레벨이 REPEATABLE-READ인 상태에서 INSERT INTO SELECT .. 혹은 CREATE TABLE AS SELECT .. 로 데이터를 처리하면 SELECT가 참조하는 테이블에 테이블 단위 잠금이 발생할 수 있다.

INSERT INTO SELECT

SELECT 결과를 특정 테이블에 바로 넣는 쿼리이다.

 

세션1 - INSERT INTO SELECT

INSERT INTO test
SELECT
    act_type,
    to_uid,
    act_time,
    to_user_name,
    before_user_name,
    count(*) cnt
FROM activity_test
GROUP BY act_type, to_uid, act_time, to_user_name, before_user_name;

 

세션2 - 테이블 특정 데이터 변경

UPDATE activity_test SET ACT_TYPE = 105 LIMIT 10;

 

세션3 - SHOW PROCESSLIST 결과 (UPDATE 쿼리는 Updating 상태)

************************* 1. row *************************
Id: 255867
User: root
Host: localhost
db: snsfeed
Command: Query
Time: 1
State: Updating
Info: update activity_test set ACT_TYPE = 105 limit 10
************************* 2. row *************************
Id: 255962
User: root
Host: localhost
db: snsfeed
Command: Query
Time: 2
State: Copying to tmp table
Info: insert into activity_test_stat2 SELECT act_type, ...

 

위 상황에서 DELETE 작업 시 대기 현상 또는 데드락 오류가 발생한다.

DELETE FROM activity_test LIMIT 10;

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

CREATE TABLE AS SELECT

SELECT 결과로 테이블을 생성하는 쿼리로, 데이터 결과를 Temporary Table로 생성할 때 자주 활용된다.

 

세션1 - CREATE TABLE AS SELECT

CREATE TABLE activity_test_stat AS
SELECT
    act_type,
    to_uid,
    act_time,
    to_user_name,
    before_user_name,
    count(*) cnt
FROM activity_test
GROUP BY act_type, to_uid, act_time, to_user_name, before_user_name;

 

세션2 - 테이블 특정 데이터 변경

UPDATE activity_test SET ACT_TYPE = 105 LIMIT 10;

 

세션3 - SHOW PROCESSLIST 결과 (UPDATE 쿼리는 Updating 상태)

************************* 1. row *************************
Id: 255867
User: root
Host: localhost
db: snsfeed
Command: Query
Time: 2
State: Updating
Info: update activity_test set ACT_TYPE = 105 limit 10
************************* 2. row *************************
Id: 255962
User: root
Host: localhost
db: snsfeed
Command: Query
Time: 4
State: Copying to tmp table
Info: CREATE table activity_test_stat as SELECT act_type, ...

 

위 상황에서 DELETE 작업 시 대기 현상 또는 데드락 오류가 발생한다.

DELETE FROM activity_test LIMIT 10;

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

 

오류 발생 원인

REPEATABLE-READ 레벨에서는 트랜잭션을 처리할 때 SELECT 결과의 일관성을 보장하기 위해 스냅샷을 이용한다.

 

위와 같이 INSERT-SELECT 가 동시에 있는 경우 SELECT 결과를 보장하기 위해 암묵적으로 SELECT 임에도 불구하고 데이터 잠금과 비슷한 효과가 나타난다. 즉 SELECT 작업이 종료될 때까지 해당 데이터 변경 작업을 할 수 없다.

트랜잭션 Isolation 레벨

트랜잭션 Isolation 레벨이란 트랜잭션을 처리할 때 어느 수위까지 데이터의 일관성을 보장해줄 것이냐 하는 것이다.

 

  • READ UNCOMMITTED
    • 다른 트랜잭션이 Commit 전의 상태를 볼 수 있음
    • 바이너리 로그가 자동으로 Row Based로 기록됨 (Statement 설정 불가, Mixed 설정 시 자동 변환)
  • READ COMMITTED
    • Commit된 내역을 읽을 수 있는 상태
    • 트랜잭션이 다르더라도 다른 특정 트랜잭션이 Commit을 수행하면 해당 데이터를 Read 할 수 있음
    • 바이너리 로그가 자동으로 Row Based로 기록됨 (Statement 설정 불가, Mixed 설정 시 자동 변환)
  • REPEATABLE READ
    • MySQL InnoDB 스토리지 엔진의 기본 Isolation 레벨
    • SELECT 시 현재 데이터 버전의 스냅샷을 만들고 만든 스냅샷에서 데이터를 조회
    • 동일 트랜잭션 내에서 데이터 일관성을 보장
    • 데이터를 다시 읽으려면 트랜잭션을 다시 시작해야 됨
  • SERIALIZABLE
    • 가장 높은 Isolation 레벨로 트랜잭션이 완료될 때까지 SELECT 문장이 사용하는 모든 데이터에 공유 락 (Shared Lock)이 걸림
    • 다른 트랜잭션에서는 해당 영역에 관한 데이터 변경 뿐만 아니라 입력도 불가

오류 해결 방법

앞 테스트의 INSERT INTO SELECT .. 에서 발생한 테이블 잠금은 Isolation 레벨을 READ COMMITTED나 READ UNCOMMITTED로 변경하여 해결할 수 있다.

 

SET SESSION transaction isolation level READ COMMITTED;
SET SESSION transaction isolation level READ UNCOMMITTED;

 

단 Isolation 레벨이 READ COMMITTED인 경우에 바이너리 로그 포맷 MIXED로 설정해서 바이너리 로그가 Row 기반으로 기록된다는 점을 반드시 기억해야 한다.

 

따라서 MySQL 리플리케이션 환경에서 시스템을 운영하고 있다면 설정을 변경한 후 반드시 데이터가 정상적으로 동기화되는 지 주기적으로 체크해야 한다.

'DB' 카테고리의 다른 글

MySQL 퍼포먼스 최적화를 읽고(6) - 스토리지 엔진 레벨에서의 접근법  (1) 2023.10.24
MySQL 퍼포먼스 최적화를 읽고(5) - 스키마 레벨에서의 접근법  (0) 2023.10.24
MySQL 퍼포먼스 최적화를 읽고(3) - WHERE 조건 이해  (1) 2023.10.24
MySQL 퍼포먼스 최적화를 읽고(2) - 쿼리 성능 진단은 최적화의 기초  (1) 2023.10.24
MySQL 퍼포먼스 최적화를 읽고(1) - MySQL의 특징  (0) 2023.10.21

'DB' Related Articles

티스토리툴바