[index] index 성능 확인

이번 포스트에서는 index를 적용하지 않았을 때, cluster index를 사용했을 때, 보조 index를 사용했을 때 성능 비교를 통해 효율적인 인덱스의 사용에 대해 고민해보자.

 

 

샘플 데이터베이스 설치

 

employees 스키마 설치

 

먼저 효율적인 테스트를 위해 많은 데이터를 포함한 데이터베이스 스키마를 설치해보자.

https://dev.mysql.com/doc/employee/en/employees-installation.html

MySQL :: Employees Sample Database :: 3 Installation

안내대로 git repository를 clone 하거나 zip으로 다운로드 한 후 압축 해지하고 해당 폴더에서 mysql을 command line으로 실행해주자.

mysql -u 계정 -p
비밀번호 입력

로그인이 완료되면 mysql prompt 상에서 source 명령을 이용해 employee schema를 설치한다.

mysql> source employees.sql;

# 다량의 스크립트 실행 후 database 확인
show databases;

테이블 복제 및 index 추가

 

원본 데이터를 망가트리면 문제가 될 수 있으므로 create select 문장을 이용해서 employees 테이블을 복제해보자. 이때 order by rand()를 이용해서 데이터의 순서를 무작위로 만들어준다.(기본적으로는 emp_no로 정렬되어있음)

create table emp_none_idx select * from employees order by rand();
create table emp_cluster_idx select * from employees order by rand();
create table emp_secondary_idx select * from employees order by rand();

commit;

create select 구문은 not null 을 제외한 모든 제약조건을 삭제하기 때문에 새로 index를 잡아 주자. 이때 emp_cluster_idx에는 primary key를 설정해서 cluster index를 생성하고 emp_secondary_idx는 add index 문장으로 secondary index를 추가한다.

alter table emp_cluster_idx add primary key (emp_no);
alter table emp_secondary_idx add index idx_emp(emp_no);

위 처리가 끝나면 emp_cluster_idx는 emp_no로 정렬된다.(cluster index를 잡았기 때문에)

 

테이블별 index 확인

이제 show index 명령을 이용해서 각 테이블의 index 정보를 확인해보자.

일단 생성한 index가 적용된 메타 정보를 얻기 위해서는 다음 문장을 실행해준다.

analyze table emp_none_idx, emp_cluster_idx, emp_secondary_idx;

 

먼저 emp_none_idx는 당연히 아무런 정보가 없다.

show index from emp_none_idx;

emp_cluster_idx에는 PRIMARY index가 생성되어있다.

show index from emp_cluster_idx;

마지막으로 emp_secondary_idx는 secondary index가 생성되어있다.

show index from emp_secondary_idx;

 

그리고 index가 적용된 후 테이블의 상태를 점검해보자.

show table status where name like 'emp\_%';

모든 테이블의 데이터 건수가 같은 상태(data_length)에서 emp_cluster_idx와 emp_secondary_idx의 data_free 공간이 확 줄어든 것을 볼 수 있다. 각각 index가 점유하고 있는 공간이다. secondary index는 heap 영역에 별도의 데이터 페이지를 구성하는데 이 길이가 index_length 만큼이다. cluster index는 데이터페이지에 인덱스가 포함된다.

 

특정 값을 이용한 정보 조회

 

데이터베이스들은 성능 향상을 위해 캐시등 다양한 기술을 사용하기 때문에 인덱스의 성능을 그나마 정확히 측정하기 위해서는 매번 테스트를 진행할 때마다 서비스를 다시 시작해주어야 한다.

 

emp_none_idx

먼저 emp_none_idx 테이블에서 emp_no=100000번을 조회하는데 얼마나 많은 페이지를 찾아보는지 조회해보자.

show global status like 'Innodb_pages_read'; # 읽은 페이지 수(쿼리 실행 전 -  865)
select * from emp_none_idx where emp_no=100000;
show global status like 'Innodb_pages_read'; # 읽은 페이지 수(쿼리 실행 후 - 1926)

쿼리 실행 전/후의 읽은 페이지 수를 비교해보면 1926-865 = 1061 페이지 정도를 검색해서 원하는 결과를 찾았다. 

이때의 실행 계획을 살펴보면 Full Table Scan으로 전체 데이터를 다 찾아봤음을 의미한다. 쿼리 실행 비용도 30,193으로 매우 높다.(비용은 단순한 시간은 아니지만 높을수록 비 효율적이다.)

 

emp_cluster_idx

이번에는 동일한 쿼리를 emp_cluster_idx에서 실행해보자.

show global status like 'Innodb_pages_read'; # 읽은 페이지 수(쿼리 실행 전 -  2002)
select * from emp_cluster_idx where emp_no=100000;
show global status like 'Innodb_pages_read'; # 읽은 페이지 수(쿼리 실행 후 - 2004)

이번에는 2002-2004=2 페이지만 검색해서 결과를 찾아 냈다. 

실행 결과도 Single Row로 cost가 무려 1이다! (0.1 인데 오류가 아닌가 의심 ㅜㅜ)

emp_secondary_idx

마지막으로 emp_secondary_idx에서의 결과를 확인해보자.

show global status like 'Innodb_pages_read'; # 읽은 페이지 수(쿼리 실행 전 -  2004)
select * from emp_secondary_idx where emp_no=100000;
show global status like 'Innodb_pages_read'; # 읽은 페이지 수(쿼리 실행 후 - 2006)

여기서도 역시 2006-2004=2 페이지를 검색해서 결과를 가져왔다. 여기서는 Non-Unique Key Lookup으로 실행 비용은 0.35 이다.

 

범위 기반의 조회

 

emp_none_idx

이번에는 특정 값이 아닌 범위를 기반으로 조회해보자. 

workbench는 기본적으로 limit 쿼리를 적용하기 때문에 Don't Limit로 변경 후 처리해보자.

show global status like 'Innodb_pages_read'; # 읽은 페이지 수(쿼리 실행 전 -  1054)
select * from emp_none_idx where emp_no<11000;
show global status like 'Innodb_pages_read'; # 읽은 페이지 수(쿼리 실행 후 - 2116)

이번에는 999건의 데이터를 읽었으며 2116-1054=1062 페이지를 읽어서 결과를 가져왔다. 즉 인덱스가 없으므로 1건을 읽으나 999건을 읽으나 읽어야 하는 페이지는 거의 비슷하다. Full Table Scan을 mouse over 해보면 읽은 데이터의 양이 나오는데 12M이다.

 

emp_cluster_idx

다음은 emp_cluster_idx에서 범위 기반으로 조회한 결과이다.

show global status like 'Innodb_pages_read'; # 읽은 페이지 수(쿼리 실행 전 -  1048)
select * from emp_cluster_idx where emp_no<11000;
show global status like 'Innodb_pages_read'; # 읽은 페이지 수(쿼리 실행 후 - 1059)

읽은 페이지는 약 11페이지이며 쿼리 비용은 201, 검색 방식은 index range scan이다. 이때 읽어들인 데이터의 양은 겨우 132K이다.

 

여기서 만약 전체 데이터를 다 조회하면 어떤 일이 발생할까?

다음의 쿼리는 전체 데이터를 범위로 조회한다. 따라서 읽어야 하는 페이지(1948-1053=895)도 엄청나다.

show global status like 'Innodb_pages_read'; # 읽은 페이지 수(쿼리 실행 전 -  1053)
select * from emp_cluster_idx where emp_no>0;
show global status like 'Innodb_pages_read'; # 읽은 페이지 수(쿼리 실행 후 - 1948)

이 상황에서 읽은 데이터의 양은 무려 19M이다. 참고로 emp_none_idx에서 동일한 조회를 했을 때는 12M이다. 왜 이런 일이 발생할까?

당연히 index 페이지를 추가로 읽었기 때문이다. 이 상황에서는 index를 이용하는 조회가 오히려 부담이 되는 것이다.

아래처럼 index 사용을 중지한 상태에서 쿼리를 해보자.

select * from emp_cluster_idx ignore index(primary) where emp_no>0;

여기서는 Full Table Scan을 하고 있고 데이터를 읽은 양이 오히려 12M로 줄어든 것을 볼 수 있다. 따라서 "index를 사용한다고 해서 성능에 언제나 효율적이다"라는 말은 잘못되었다는 것을 확인할 수 있다.

 

emp_secondary_idx

마지막으로 emp_secondary_idx를 이용한 결과를 살펴보자.

show global status like 'Innodb_pages_read'; # 읽은 페이지 수(쿼리 실행 전 -  1054)
select * from emp_secondary_idx where emp_no<11000;
show global status like 'Innodb_pages_read'; # 읽은 페이지 수(쿼리 실행 후 - 1691)

전체적으로 읽은 페이지는 1691-1054=637페이지로 cluster idx 보다는 많지만 index가 없는 것 보다는 유리하다.

select * from emp_secondary_idx where emp_no<11000;	select * from emp_secondary_idx where emp_no<400000;

이번에는 조회되는 데이터의 건수를 대폭 증가시켜서(전체 보다는 작게) 실행 계획을 살펴보자. 둘다 똑같이 emp_no를 통해 조회하고 있는데 한번은 index range scan을, 한번은 full table scan을 처리하고 있다.

secondary index의 경우 mysql optimizer가 검사해보고  index를 타는 것과 전체를 검색하는 것에 별 차이가 없거나 오히려 인덱스 사용이 불필요하다고 생각되는 경우 index를 사용하지 않을 수도 있다. (참고로 cluster index는 계속 index range scan으로 처리한다.)

 

기타

 

인덱스를 조작해서 사용하는 경우

현재 가지고 있는 emp_no에 10을 뺀 값이 10000 번인 사용자를 조회한다고 생각해보자.

show global status like 'Innodb_pages_read'; # 읽은 페이지 수(쿼리 실행 전 -  1053)
select * from emp_secondary_idx where emp_no-10=10000;
show global status like 'Innodb_pages_read'; # 읽은 페이지 수(쿼리 실행 후 - 2114)

사실 1명의 사용자를 조회하는 경우이므로 몇 페이지만 검색하면 결과가 나와야 하지만 1000페이지 넘게 검색을 하고 있다. 실행 계획을 살펴봐도 Full Table Scan이다.

이처럼 인덱스 컬럼을 조작해서 쿼리를 작성한 경우는 인덱스를 타지 않는다. 위의 쿼리는 다음과 같이 수정해야 한다.

select * from emp_secondary_idx where emp_no=10000+10;