infoqoch's 개발 블로그

sql join 절과 index의 관계

• • •

join의 성능 문제

  • 기존 프로젝트를 개선하는 과정에서 inner join으로 두 테이블을 합성해야 할 일이 있었다.
    • 두 테이블은 특정 칼럼으로 연관관계를 가지고 있었다. 각 테이블의 연관관계 칼럼은 unique, pk, fk가 아니며, 인덱스가 존재하지 않았고, 최대 10개의 영문자와 숫자로 이뤄져 있었다.
    • 첫 번째 테이블은 1만개 정도 레코드가 있었고 두 번째 테이블은 2천여개의 레코드가 있었다.
    • db는 mysql 5 버전이었다.
  • 레코드의 양이 얼마 되지 않았으므로 성능 문제가 없을 것이라 생각했다. 완전한 오판이었다. 10초가 지나도록 테이블 조회를 하지 못했으며 중도에 중단했다.
  • join의 성능 관련하여 고민하게 된 계기가 되었다. 관련한 테스트를 아래와 같이 진행해봤다.

join과 index

  • 조인과 인덱스의 성능 테스트를 위하여 다음과 같이 tb1 과 tb2 테이블을 생성하였으며, 각 칼럼은 pk - 인덱스된 칼럼 - 인덱스되지 않은 칼럼 - fk 등으로 구성된다.
USE test_index;

DROP TABLE tb1;

CREATE TABLE tb1(
	pk int PRIMARY KEY AUTO_INCREMENT
	, co1_indexed varchar(100) 
	, co2_not_indexed varchar(100)
);

CREATE INDEX IDX_co1_indexed ON tb1(co1_indexed);

INSERT INTO tb1 (co1_indexed, co2_not_indexed) VALUES 
("hi", "hi"), 
("hello", "hello"),
("gooday", "gooday"),
("seeyou", "seeyou"),
("bye!", "bye!"); 

DROP TABLE tb2;

CREATE TABLE tb2(
	pk int PRIMARY KEY AUTO_INCREMENT
	, co1_indexed varchar(100) 
	, co2_not_indexed varchar(100)
	, co3_fk_tb1_pk int 
	, FOREIGN KEY (co3_fk_tb1_pk) REFERENCES tb1(pk)
);

CREATE INDEX IDX_co1_indexed ON tb2(co1_indexed);

INSERT INTO tb2 (co1_indexed, co2_not_indexed, CO3_FK_TB1_PK) VALUES 
("hi", "hi", 1), 
("hello", "hello", 2),
("gooday", "gooday", 3),
("seeyou", "seeyou", 4), 
("bye!", "bye!", 5);

SELECT * FROM tb1;
SELECT * FROM tb2;
SHOW INDEX FROM tb1;
SHOW INDEX FROM tb2;
  • explain을 활용하여 join 시 index의 사용 여부를 확인하였다.
  • 참고로 explain 으로 출력된 레코드의 순서는, 쿼리에 따른 데이터 탐색 순서와 일치한다.

    It lists the tables in the output in the order that MySQL would read them while processing the statement. This means that MySQL reads a row from the first table, then finds a matching row in the second table, and then in the third table, and so on. (https://dev.mysql.com/doc/refman/8.0/en/explain-output.html)

-- pk - fk  인덱스 한다 
EXPLAIN SELECT * 
FROM TB1
JOIN TB2 ON TB1.pk = TB2.CO3_FK_TB1_PK 

-- inx - idx 
-- TB1을 전체탐색(ALL) 한 후 TB2를 인덱스를 참조(ref)하여 탐색한다. 
-- where절에 인덱스가 있는 칼럼을 조건으로 할 경우, TB1에서도 ref 타입으로 탐색한다. 
EXPLAIN SELECT * 
FROM TB1
JOIN TB2 ON TB1.co1_indexed = TB2.co1_indexed
-- WHERE TB1.CO2_NOT_INDEXED = 'gooday'; -- type이 all을 유지한다.
-- WHERE TB1.CO1_INDEXED = 'gooday'; -- type이 all에서 ref로 변경된다.

-- idx - not idx
-- TB2를 먼저 탐색한다.
EXPLAIN SELECT * 
FROM TB1
JOIN TB2 ON TB1.co1_indexed = TB2.co2_not_indexed

-- not idx - idx
-- TB1을 먼저 탐색한다.
-- 드라이빙 테이블보다 드리븐 테이블에 대한 리소스를 더 소모한다. 드리븐 테이블을 인덱스 된 테이블로 하기 위하여 나중에 탐색한다. 
EXPLAIN SELECT * 
FROM TB1
JOIN TB2 ON TB1.co2_not_indexed = TB2.co1_indexed 

-- noidx - noidx   
EXPLAIN SELECT * 
FROM TB1
JOIN TB2 ON TB1.co2_not_indexed = TB2.co2_not_indexed
  • 위의 테스트를 진행하면 주석에 작성한 내용처럼 결과를 확인할 수 있다.
  • join의 경우 이중반복문의 특성을 띈다. 밖의 반복문에 해당하는 테이블을 드라이빙 테이블이라 한다. 안의 반복문에 해당하는 테이블을 드리븐 테이블이라 한다. 드라이빙 테이블보다 드리븐 테이블이 성능 상 더 많은 자원을 소모한다. 이로 인하여 드리븐 테이블을 최적화 하는 것에 초점을 맞춘다. 이로 인하여 on 절의 칼럼 중 인덱스가 있는 테이블이 하나만 있다면, 해당 테이블을 드리븐 테이블로 두는 것을 확인할 수 있다.
  • 결론적으로 join을 통한 테이블을 합성할 때 인덱스를 활용함을 확인할 수 있다.

정리

  • 테이블 두 개를 join으로 연결할 때 on 절을 사용한다. join의 성능은 on 절에 각 칼럼의 검색 성능에 의존한다. 이때 index가 적극 활용된다.
  • 레거시 프로젝트의 경우 검색 대상이 되는 칼럼을 인덱스 생성하는 것으로 해결할 수 있었다. 위의 예시에 따르면 tb1과 tb2의 ‘co2_not_indexed’ 에 해당하는 칼럼에 대해 각각 인덱스를 생성하였다. 속도가 0.1 초 이내로 축소되었다. 문제 상황을 해결!

  • 보통 join은 연관관계가 존재할 때 사용한다. 그러므로 ddl을 작성할 때 fk로 연결하지 않더라도 최소한 부모 테이블에는 인덱스가 존재하였고 이로 인하여 join으로 인한 극심한 성능 문제를 경험하지 않았다. 이번 기회에 명시적으로 on 절의 성능 문제에 대해 다룰 수 있어서 좋았다.

  • 참고
    • https://cheese10yun.github.io/mysql-explian/
    • https://dev.mysql.com/doc/refman/8.0/en/explain-output.html