데이터가 적을 때는 쿼리를 어떻게 실행 시키든 결과가 빨리빨리 나오지만 몇십만 건 이상이 되면 점점 조회 성능이 느려지기 시작한다. 회사에서 일을 할 때도 DB 조회 속도가 나오지 않아서 고객사 프로그램 전체 실행 속도가 무척 느려졌던 적이 있었다. 인덱스를 걸면 속도가 빨라진다는 건 배워서 알고 있었지만 정확히 왜 빨라지는지, 어떤 부분에서 빨라지는지, 어디에 인덱스를 걸어야 하는지는 모르는 상태였기 때문에 거기서부터 공부를 해야 했다.
1. 인덱스는 목차라던데
많은 곳에서 인덱스는 책의 목차라고 생각하면 된다고 표현한다. 당연히 목차가 있으면 페이지를 일일이 넘겨가며 찾는 것보다 원하는 내용을 빨리 찾을 수 있는 건 알겠는데, PK에 인덱스를 걸면 겹치는 내용이 없을테니까 결국 전체 목차를 또 한번 확인한 후에 내용을 찾는 거 아닌가? 하는 생각도 했었다(같은 테이블 내에서 인덱스를 여러개 걸어서 활용할 때의 이점을 몰랐을 때의 생각이다). 내 경우에는 이런 식으로 데이터를 이미지화 해서 비교해 보는 것이 이해에 도움이 되었다.
위 테이블처럼 데이터가 무작위로 들어와 있고, 인덱스는 이름에만 걸려있다고 생각해보자. 이 중에서 개발팀 이민정 씨를 찾으려면 이름에서 이민정을 한 번 스캔해야 하고, 여러 이민정 씨들의 부서를 다시 한 번 스캔해야 할 것이다.
이 표는 위와 같은 데이터에서 이름, 부서, 거주지에 인덱스가 걸려있다고 가정하고 만든 목록이다. 눈으로도 개발팀 이민정 씨를 훨씬 빨리 찾을 수 있다. 자주 사용하는 조회 조건들에 적절한 인덱스가 걸려 있으면 이와 같이 조회 성능을 향상 시킬 수 있다.
인덱스를 걸면 다음 항목들에서 성능 향상을 기대 해 볼 수 있다.
- 검색 성능 향상 : where 절에서 특정 컬럼을 이용한 조건 검색이 빨라진다(인덱스가 없으면 Full Scan 필요)
- 조인 성능 향상 : join 절에서 조인 할 때 인덱스가 있는 경우, 매칭되는 행을 효율적으로 찾아준다.
- 정렬 및 그룹화 성능 향상 : order by나 group by절에서 인덱스를 사용하면 정렬/그룹화 작업이 빨라진다.
- 범위 검색 : 날짜 범위나 숫자 범위 검색에서 인덱스를 사용하면 효율적으로 원하는 결과를 얻을 수 있다.
2. 인덱스 생성과 확인
1 | |
3. 어디에 인덱스를 걸어야 할까
인덱스를 잘 걸면 좋다는 건 알겠다. 하지만 여러 컬럼들 중 어디에 인덱스를 걸어야 적절한 걸까? 아래 기준에 부합하는 컬럼을 인덱스로 설정하는 것이 좋고, 한 테이블 당 3~5개 정도가 적당하다고 한다.
- 카디널리티 수치(고유한 정도=유니크한 값의 개수)가 높으면 인덱스 설정에 좋은 컬럼이다(이름, 주민번호, 이메일…). row 100개가 있는 개인정보 테이블에서 주민번호의 카디널리티는 100, 성별의 카디널리티는 2(남,여)이므로 주민번호에 인덱스를 거는 것이 더 적절하다고 볼 수 있다.
아래처럼 쿼리를 통해 각 컬럼의 카디널리티 수치를 계산 해볼 수 있다.1
2
3
4
5
6
7
8SELECT CONCAT(ROUND(COUNT(DISTINCT id) / COUNT(*) * 100, 2), '%') AS id_cardinality, CONCAT(ROUND(COUNT(DISTINCT title) / COUNT(*) * 100, 2), '%') AS title_cardinality, CONCAT(ROUND(COUNT(DISTINCT content) / COUNT(*) * 100, 2), '%') AS content_cardinality, CONCAT(ROUND(COUNT(DISTINCT who) / COUNT(*) * 100, 2), '%') AS who_cardinality, CONCAT(ROUND(COUNT(DISTINCT createDate) / COUNT(*) * 100, 2), '%') AS createDate_cardinality, CONCAT(ROUND(COUNT(DISTINCT updateDate) / COUNT(*) * 100, 2), '%') AS updateDate_cardinality FROM notice;
- 선택도가 낮으면 인덱스 설정에 좋은 컬럼이다(5~10%가 적당함). 선택도가 낮을수록 한 컬럼이 가진 값 하나로 적은 row가 찾아진다.
선택도 계산법 : (특정 값의 row 수 / 총 레코드 수 * 100)
ex : 100개의 사원 정보 데이터에서 고유한 사번 컬럼, 5명씩 같은 직책 컬럼, 10명씩 같은 부서 컬럼일 경우- 사번 선택도 : (1/100)*100 = 1%
- 직책 선택도 : (5/100)*100 = 5%
- 부서 선택도 : (10/100)*100 = 10%
※ 선택도가 낮다고 반드시 그 컬럼에 인덱스를 걸어야 하는 건 아니다. 인덱스를 걸기 전/후의 성능도 비교해보고, 왜 이 컬럼에 인덱스를 걸면 좋을 것 같은지 잘 생각해보고 걸자.
- where절에서 많이 사용하는 컬럼일수록(자주 조회하는 컬럼일 수록) 인덱스 설정에 좋은 컬럼이다.
- 수정 빈도가 낮을수록 인덱스 설정에 좋은 컬럼이다.
인덱스로 지정된 컬럼의 값이 바뀌면 인덱스 테이블도 새로 갱신되어야 한다.
4. 실행계획을 통해 인덱스 확인하기
1 | |
쿼리 앞에 explain을 붙이면 쿼리 결과가 아니라 쿼리 실행 계획을 확인 할 수 있다.
- id: 각 쿼리 블록 또는 서브쿼리에 대해 부여된 고유한 식별자. 여러 서브쿼리가 있는 경우 계층적으로 표현됨
- select_type: 쿼리의 유형. “SIMPLE”은 단순한 SELECT 쿼리를 나타냄
- table: 쿼리가 참조하는 테이블명
- type: 테이블에서 레코드를 읽는 방법. “range”는 인덱스를 사용하는 범위 스캔을 의미
- possible_keys: 쿼리에서 사용될 수 있는 인덱스 목록
- key: 실제로 선택된 인덱스
- Extra: 기타 정보. 여기서
"Using index condition"은 인덱스 조건을 사용하여 필터링 되었음을 의미
즉, 위 결과는 notice 테이블에 대한 select 쿼리가 있으며, idx_notice_createDate 인덱스를 사용하여 range 스캔을 수행할 거라는 뜻이다.
5. 인덱스가 있는데 인덱스를 타지 않는 경우
위 이미지는 4번과 거의 비슷하고, 검색 범위만 더 늘어난 쿼리이다. 분명히 인덱스가 걸려 있는데도 key 부분을 보면 인덱스를 타지 않은 게 보인다. 무언가 잘못 되어서가 아니라, 데이터베이스 엔진이 Full Scan이 더 낫다고 판단했다면 인덱스를 태우지 않고 전체 검색을 진행할 수도 있다. 이 이미지에서 설정한 조회 범위가 전체 row에 가까웠기 때문에 그런 것 같다.
읽어야 할 row 수가 전체 테이블 row의 20~25%를 넘어가면 인덱스를 이용하지 않는 것이 효율적이라고 한다.
그 밖에도 다양한 케이스가 존재한다.
- 쿼리에 함수나 연산자를 사용하는 경우
- NULL값을 비교하는 경우
- 컬럼의 자료형이 다른 검색을 하는 경우
- OR 절을 사용하는 경우
- LIKE문에서 와일드카드가
%내용%내용%같은 형식으로 사용될 경우(내용%는 인덱스를 탄다.)인덱스는 B-Tree 구조로 되어 있는데, B-Tree 알고리즘은 데이터를 정렬된 순서로 저장하기 때문에 앞에서부터 차근차근 데이터를 검색 할 수 있어야 한다. 사전에서 단어를 찾을 때 ‘내용’이라는 단어를 찾으려면 ‘나’부터 검색을 하기 시작하는 것처럼 말이다.
%내용처럼 앞에 와일드카드가 있으면 어디에서부터 ‘내용’을 찾아야 할 지 모르기 때문에 결국 Full Scan을 하게 된다.