MySQL - 인덱스 타는지 체크 방법 (+개선 방법, 인덱스를 타게 하는 방법)

MySQL 인덱스 타는지 확인하는 방법

mysql에서는 인덱스를 여러 개 걸었을 때 하나밖에 타지 않습니다. 그래서, 인덱스를 적절하게 걸어주지 않으면 데이터가 커졌을 때 트래픽이 튀거나 슬로우쿼리 등 문제가 될 확률이 거의 99%입니다.

 

인덱스를 타는 지 확인하는 방법은 MySQL의 Explain을 사용하는 것입니다. explain은 쿼리의 실행 계획을 반환해주므로 쿼리 튜닝, 성능 분석, 전략 수집 등의 기초가 됩니다 !

 

사용 방법은 SELECT문 앞에 explain을 붙여주면 됩니다.

explain select * from booking  where resource_id=1 and start_time > NOW() order by rand();

 

실행계획 분석

직접 쿼리문을 실행해보니 아래와 같은 결과가 나왔습니다!

 

여기서 주목해야할 부분은 possible_keys, key, Extra 입니다.

 

possible_keys는 사용 가능한 인덱스들의 목록이고, key는 그 중에서 MySQL이 봤을 때 적합하다고 판단되어서 실제로 사용하게 되는 인덱스 입니다.

 

Extra는 조회를 할 때 내부적으로 어떤 절차를 밟게 되는지, 신경 써야하는 부분이 어떤 것들이 있는지 알려줍니다 !

 

제가 생각하는 위의 SQL문의 문제점 2가지 입니다.

1. where절에 있는 resource_id와 start_time 중 한 가지는 index를 타지 않고 where 필터링을 사용한다. (Using where)
2. 정렬을 할 때 메모리공간이 부족하여, 디스크에 임시파일을 저장해서 사용한다. (Using temporary)

 

1. Using where

Using where은 InnoDB 스토리지 엔진을 통해 테이블에서 행을 가져온 뒤, MySQL 엔진에서 추가적인 체크 조건을 활용해서 행의 범위를 축소한 경우 표시됩니다.

 

MySQL은 인덱스를 1개 밖에 탈 수 없습니다. 위 SQL에서는 Where절에 두 가지 컬럼이 있죠. 그래서 Inno DB 스토리지 엔진에서 Index를 하나만 골라서 필터링 후 결과를 반환하면, MySQL 엔진에서 인덱스를 타지 않은 컬럼을 조건을 기반으로 필터링하게 됩니다.

 

즉, 아래 쿼리의 resource_id 조건은 인덱스는 타고 있지만, start_time은 인덱스와 무관하게 실제 데이터 테이블에서 필터링을 수행하고 있습니다.

• select * from booking  where resource_id=1 and start_time > NOW();

 

<참고>

이때는 두 가지 컬럼을 모두 탈 수 있게 두 개의 컬럼을 결합인덱스로 사용하는 방법이 있습니다. 하지만 사용 빈도가 잦지 않을 때 결합 인덱스를 걸어주면 Insert를 하거나, 다른 쿼리를 사용할 때 성능이 오히려 감소될 수 있습니다.

 

커버링 인덱스에서

커버링 인덱스를 사용한 쿼리에서도 Using where이 발생할 수 있습니다. Using Index와 Using where이 모두 표시되는 것이죠.

 

Where In 절이나 범위 스캔 등이 실행된다면 인덱스만으로 조회하더라도 MySQL 엔진에서 필터링을 거치게 됩니다. 그래서 Using where이 표시되게 됩니다.

 

2. Using temporary

이것도 인덱스를 타지 않아서 발생하는 문제입니다. 지금은 Order by 절에 인덱스가 없는 값이 들어있고, 그래서 임시 테이블을 만들어서 사용하게 됩니다. 임시 테이블을 만들고 읽는 것은 데이터가 몇 십만건 이상이 되면 큰 부하가 걸립니다.

 

임시 테이블은 메모리가 부족할 경우(MySQL 판단.. 거의 대부분) 디스크에 생성됩니다. 디스크는 메모리보다 훨씬 느려 I/O부하가 발생해서 문제가 발생하게 됩니다.

 

이럴 때는 인덱스를 ORDER BY 절에 배정해줄 것이 아니라면 앱 Layer나 프레젠테이션 Layer에서 효율적인 정렬 알고리즘을 돌려주는 것이 좋습니다. 디스크에서 뭔가를 읽어올 필요도 없고 기본적으로 DB서버보다 빠르기도 하고, DB 서버에 부하도 덜 줄 수 있고, DB는 데이터를 내려주는 것에만 집중할 수 있기 때문입니다!

 

참고

• https://stackoverflow.com/questions/25672552/whats-the-difference-between-using-index-and-using-where-using-index-in-the
• https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#explain-extra-information