이번 포스트를 보게 되면 MySQL 에서 Index 를 사용하는 방법에 대해서 배울수 있습니다.

Data Insert

데이터 증가에 따른 query 속도를 비교하기 위해서, 100만건을 우선적으로 insert 해보겠습니다.

create table POST
(
    id int auto_increment,
    memberId int not null,
    contents varchar(100) not null,
    createdDate date not null,
    createdAt datetime not null,
    constraint POST_id_uindex
        primary key (id)
);

우선 테이블을 생성합니다.

저는 EasyRandom 을 통해서 코드를 통해 Database 에 memberid 3으로 100만건 insert 을 진행합니다.

100만건 insert 시간 : 29.261983939

Select Query

select createdDate, memberId, count(id)
from POST
where memberId = 3 and createdDate between '2019-01-01' and '2019-12-31'
group by memberId, createdDate;

데이터는 1970-01-01 부터 2022-02-01 까지 데이터로 총 100만건이 insert 되었습니다.

따라서 위 쿼리를 실행해보면 결과는 다음과 같습니다.

select query 시간 : 1.002 sec
cpu usage : max 40%

좀 더 확실한 비교를 위해서 memberId 4, 5로 100만건씩 insert 하고 Select Query 속도를 비교해보겠습니다.

memberid 4으로 100만건 insert

select query 시간 : 1.730 sec
cpu usage : max 60%

memberid 5으로 100만건 insert

select query 시간 : 2.095 sec
cpu usage : max 70%

query 시간이 초단위가 넘어가면 사고입니다. 데이터가 증가함에 따라 cpu 사용률과 query 속도가 말도 안되게 느려집니다.

index

시작전에 명심해야 될 건 다음과 같습니다.

index 거는 작업은 부하가 크다!!!!!!!!!!!

1. createdDate Column 에 index 를 거는 경우

CREATE INDEX IDX_CREATED_DATE ON POST
    (createdDate);

range type 으로 생성된 index 를 탔지만, 여전히 결과는 다음과 같습니다.

select query 시간 1.300 sec
cpu usage : max 53%

여전히 대장애급 속도입니다.

2. memberId 에도 index 를 걸면?

CREATE INDEX IDX_MEMBER_ID ON POST
    (memberId);

IDX_CREATED_DATE, IDEX_MEMBER_ID 를 타게 됩니다. 하지만 결과는?

select query 시간 0.971 sec
cpu usage : max 32%

이 결과는 Database 운영 안정성에 심각하게 위험합니다.

위처럼 대량의 데이터를 대상으로 Select Query 을 실행하는 경우는 성능 개선이 필요합니다.

성능을 개선하기 위해서는 Database Engine 에서 DISK 에 대한 I/O 를 최소화하는 것이 좋습니다.

3. createDate, memberId 순으로 복합키를 걸어버리면?

CREATE INDEX IDX_CREATED_DATE_MEMBER_ID ON POST
    (createdDate, memberId);

생성된 index 를 optimizer 가 Query Plan 에서 잘 사용하는 것을 볼 수 있습니다. 결과는?

select query 시간 0.082 sec
cpu usage : max 3%

편안합니다. 속도가 엄청 개선됬네요.

다시 말하지만, query optimization 에서 중점을 둬야 하는 것은 데이터베이스의 DISK I/O 를 최소화하는 것입니다.

실제 createDate, memberId 로 index 를 생성하면 자료구조는 B+tree 구조로 클러스터링 되어 생성이 됩니다. 클러스터링 된 데이터의 각 테이블은 아래처럼 구성됩니다.

createDate, memberId, pk인 id 3가지에 대한 정보가 index table 에 포함되게 됩니다.

따라서 composite index 를 통해서 접근하여 필요한 정보가 모두 index table에 존재하기 때문에 disk i/o 가 발생하지 않고 속도가 위처럼 개선이 되는거죠. 이런 방식을 covering index 라고 합니다. 만약 count(id) 가 아닌 다른 필드로 카운팅을 하면 속도가 어떻게 되는지 보겠습니다.

explain select createdDate, memberId, count(createdAt)
from POST
where memberId = 3 and createdDate between '2019-01-01' and '2019-12-31'
group by memberId, createdDate;

다른 필드인 createdAt 에 대해서 counting 을 하게 되면 실제 query 실행 계획은 이전과 동일합니다.

하지만 결과는?

select query 시간 : 0.447 sec
cpu usage : max 24%

속도가 많이 느려졌습니다. 이유는 다음과 같습니다.

실제 Search Query 실행은 Full Scanning 이 아닌 Range Scanning 으로 돌지만, 이전의 query 와 차이점이 있다면 covering index 로 돌지 않고, disk 에 직접 접근하게 됩니다. 따라서 속도가 무척 느려지죠.

index 의 trade off

Search Query 속도를 개선했습니다. 그럼 Insert Query 에는 어떤 영향이 있는지 테스트를 해보도록 하겠습니다.

memberId 6 으로 100만건 insert

100만건 insert 시간 : 53.370179892

무려 거의 2배 가까이 증가했습니다.

마무리

index 를 사용을 한다는 것은 search query 의 성능을 높히기 위함이나 반대로 insert, delete, update query 에 성능을 저하시키는 것을 항상 명심하고 사용해야 합니다.