Mariadb row constructor expression으로 단일 row 대상의 delete 쿼리
Header
사내에서는 RDS Mariadb 10.6.14 버전을 사용하고 있다. 구성 테이블 중에서는 2개 이상의 컬럼으로 구성된 복합키를 PK로 사용하는 테이블도 있었는데 그러한 테이블에 row constructor expression으로 bulk delete 쿼리를 사용하면서 발견한 내용에 대해서 정리해보고자한다.
Body
1
2
SELECT * FROM t1 WHERE (column1, column2) = (1, 1); -- single row
SELECT * FROM t1 WHERE (column1, column2) IN ((1, 1), (2, 2)); -- multiple rows
row constructor expression은 위 처럼 2개 이상의 컬럼을 대상으로 작성하는 조건 절에서 괄호안에 컬럼과 그 값들을 묶어서 표현하는 것인데 생성자를 사용하는 방식과 동일해서 그렇게 부르는 것 같다.
1
2
SELECT * FROM t1 WHERE (column1, column2) IN ((1, 1), (2, 2)); -- range scan 불가능
SELECT * FROM t1 WHERE (column1 = 1 AND column2 = 1) OR (column1 = 2 AND column2 = 2); -- range scan 가능
mysql 5.7.3 버전 이전에는 row constructor expression을 optimizer가 기본 표현으로 변환할 수 없어 range scan을 사용할 수 없었다고 한다. 그래서 range scan을 사용하기 위한 유일한 방법은 동등 조건으로 비교하는 형식으로 재작성해야만했다. 5.7.3 버전부터는 row constructor expression 최적화가 이루어져 range scan을 사용할 수 있게 되었다.
1
DELETE FROM t1 WHERE (column1, column2) IN ((1, 1), (2, 2)); -- range
Mariadb는 일부 시스템 변수, 기능들을 제외하고는 Mysql과 호환이 되는데 Mariadb 10.6 버전은 Mysql 8.0 버전과 호환된다. Mariadb에서도 row constructor expression 최적화는 적용되어 있기 때문에 range scan을 사용할 수 있다. 그래서 bulk delete 쿼리를 작성하면 항상 위와 같이 작성해왔다.
그런데 운영 데이터베이스에서 bulk delete 쿼리 조건절의 대상 row constructor가 1개인 경우에는 쿼리의 실행 속도가 많이 느렸고 slow query 로그를 통해서 이를 확인할 수 있었다.
원인은 쿼리의 실행 계획에서 range scan을 사용하지 못했고 full scan을 사용했기 때문이었다.
full scan을 사용했다고해서 전체 테이블의 row를 대상으로 lock을 설정한 것은 아니지만 삭제 대상 row를 찾는 과정에서 full scan을 사용한 것이다. 해당 테이블에 동일 컬럼으로 구성된 인덱스가 많아서 옵티마이저가 실행 계획을 잘못 계획하는 것은 아닌지 의구심이 들었고 테스트 환경에서 동일한 성격을 가지는 테이블을 만들어서 확인해보고 싶어졌다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
-- 테이블 생성
CREATE TABLE composite_unique_key_table
(
a bigint not null,
b bigint not null,
c date not null,
d varchar(255),
e varchar(255),
f varchar(255),
g varchar(255),
h varchar(255),
primary key (a, b, c)
);
-- 더미 데이터 생성
CREATE PROCEDURE prepare_dummy_data()
BEGIN
DECLARE i int;
DECLARE j int;
DECLARE k int;
SET i = 1;
WHILE i <= 100 DO
SET j = 1;
WHILE j <= 100 DO
SET k = 1;
WHILE k <= 100 DO
INSERT INTO composite_unique_key_table (a, b, c, d, e, f, g, h) VALUES (i, j, DATE_ADD(NOW(), INTERVAL k day), 'd', 'e', 'f', 'g', 'h');
SET k = k + 1;
END WHILE;
SET j = j + 1;
END WHILE;
SET i = i + 1;
END WHILE;
END
CALL prepare_dummy_data()
동일한 현상을 재현 및 테스트해보기 위해서 테이블과 더미 데이터를 생성한다.
1
2
3
4
5
EXPLAIN SELECT * FROM composite_unique_key_table WHERE (a, b, c) = (1,1,'2024-02-12'); -- const
EXPLAIN SELECT * FROM composite_unique_key_table WHERE (a, b, c) IN ((1,1,'2024-02-12')); -- const
EXPLAIN SELECT * FROM composite_unique_key_table WHERE (a, b, c) IN ((1,1,'2024-02-10'), (2,4,'2024-05-02')); -- range
단일 또는 여러 row의 row contrusctor expression 조건으로 select 쿼리를 해보면 const, range scan으로 실행 계획이 잘 계획된다. select는 옵티마이저가 표현식을 잘 해석하는 것으로 보인다.
1
2
3
4
5
EXPLAIN DELETE FROM composite_unique_key_table WHERE (a, b, c) = (1,1,'2024-02-12'); -- ALL
EXPLAIN DELETE FROM composite_unique_key_table WHERE (a, b, c) IN ((1,1,'2024-02-12')); -- ALL
EXPLAIN DELETE FROM composite_unique_key_table WHERE (a, b, c) IN ((1,1,'2024-02-10'), (2,4,'2024-05-02')); -- range
단일 또는 여러 row의 row contrusctor expression 조건으로 delete 쿼리를 해보면 여러 row 조건을 제외한 쿼리들은 실행 계획이 full scan으로 계획된다.
1
EXPLAIN DELETE FROM composite_unique_key_table WHERE a = 1 AND b = 1 AND c = '2024-02-12'; -- range
row constructor expression 조건을 사용하지 않고 동등 조건으로 풀어서 작성하면 실행 계획이 full scan이 아닌 range scan으로 계획된다. 동일한 조건을 사용해도 select에서는 const가 계획된 반면에 range scan이 설정된 것은 이유를 잘 모르겠다.
full scan이 발생하는 상황을 회피하기 위해서 삭제 대상이 단일 row인 경우에는 row constructor expression 조건의 쿼리를 사용하지 않고 동등 조건의 쿼리를 사용하도록 적용하여 문제를 해결했다.
Footer
사실 개인적으로는 복잡도, 인덱스 사이즈, 실행 계획 등 여러가지면에서 복합키를 사용하는 것을 선호하지 않는데 앞으로도 그럴 것 같다.
운영하면서 실제로 문제가 발생했던 버전은 10.6.14이지만 최신 버전에서도 동일한 현상이 발생하는지 확인하기위해 11.2.2 버전을 사용했고 동일한 현상이 발생했다.
반면 Mysql 8.0 버전에서는 동일한 현상이 발생하지않았는데, 이전까지는 Mariadb를 사용해본 경험이 없어서 최근까지도 Mysql과 어떤 차이점이 있는지 궁금해하지도 않았고 잘 알지도 못했다. 호환성이 매우 높다고하지만 Mysql로부터 fork되어 자체적으로 개발되는 순간부터는 완전히 다른 이기종 데이터베이스라고 봐도 과하지 않다는 생각이 든다.
앞으로는 사용하고 운영해야하는 입장이니 동일하게 동작할 것이라는 생각을 버리고 공부해야할 듯하다. 그리고 당연하다고 생각되는 것들도 실행 계획을 꼭 확인해보고 모니터링하는 습관을 가져야겠다.