개요
MySQL(MariaDB) 학습 중 인덱스에 관한 내용을 정리해보았습니다.
인덱스(Index)란❓
인덱스는 지정한 컬럼들을 기준으로 메모리 영역에 일종에 목차를 생성하는 것입니다.
인덱스 = 정렬
insert, update, delete의 성능을 희생하고 대신 select의 성능을 향상시킵니다. 여기서 주의해야할 점은 update, delede의 행위가 느린것이지, 위 3가지를 실행하기 위한 데이터를 조회하는것은 인덱스가 있으면 빠르게 조회됩니다.
인덱스가 없는 컬럼을 조건으로 update, delete를 실핼할 경위 많은 양의 데이터가 있는 경우 많이 느려지게 됩니다.
B-Tree 인덱스 구조
인덱스 탐색은 Root -> Branch -> Leaf -> 디스크 순으로 진행
- Branch(페이지 번호2)는 dept_no가 d001이면서 emp_no가 10017~10024까지인 Leaf의 부모로 있습니다.
dept_no=d001 and emp_no=10018로 조회하면 페이지 번호 4인 Leaf를 찾아 데이터 파일의 주소를 불러와 반환하는 과정을 하게 됩니다.
인덱스의 두번째 컬럼은 첫번째 컬럼에 의존해서 정렬
- 두번째 컬럼의 정렬은 첫번째 컬럼이 똑같은 열에서만 의미가 있습니다.
- 만약 3,4번째 인덱스 컬럼도 있다면 두번째 컬럼과 마찬가지로 앞 컬럼에 의존하는 의존관계를 가집니다.
디스크에서 읽는 것은 메모리에서 읽는것보다 성능이 떨어짐
- 인덱스의 성능을 향상시킨다는 것은 디스크 저장소에 얼마나 덜 접근하게 만드느냐, 인덱스 Root에서 Leaf까지 오고가는 횟수를 얼마나 줄이느냐에 달려있습니다.
인덱스의 갯수는 3~4개 정도가 적당
- 너무 많은 인덱스는 새로은 row를 등록할 때마다 인덱스를 추가해야하고, 수정/삭제 시마다 인덱스 수정이 필요하여 성능상 이슈가 있습니다.
- 인덱스 역시 공간을 차지하기때문에 많은 인덱스들은 그만큼 많은 공간을 차지합니다.
- 특히 많은 인덱스들로 인하여 옵티마이저가 잘못된 인덱스를 선택할 확룔이 높습니다.
인덱스 키 값의 크기
인덱스를 1개의 컬럼만 걸어야 한다면, 해당 컬럼은 카디널리티(Cardinality)가 가장 높은 것을 잡아야 합니다.
카디널리티(Cardinality) : 컬름의 중복된 수치를 나타냅니다. 예를 들면 성별, 학년 등은 카디널리티가 낮다고 얘기하고, 주민번호등록번호, 계좌번호 등은 카디널리티가 높다고 합니다.
인덱스를 최대의 효율로 뽑아내려면, 해당 인덱스로부터 많은 부분을 걸러내야 하기 때문입니다.
여러 컬럼으로 인덱스 구성
여러 컬럼으로 인덱스를 잡는다면 카디널리티가 높은 순서에서 낮은 순서로 구성하는 것이 더 성능이 뛰어 납니다.
여러 컬럼으로 인덱스 시 조건 누락
조회 쿼리 사용 시 인덱스를 태우려면 최소한 첫번째 인덱스 조건은 조회조건에 포함 되어야만 합니다. 첫번째 인덱스 컬럼이 조회 쿼리에 없으면 인덱스를 타지 않습니다.
인덱스 조회 시 주의사항
1. between, like, <, > 등의 범위 조건은 해당 컬럼은 인덱스를 타지만, 그 뒤의 인덱스 컬럼들은 인덱스가 사용되지 않습니다.
- 범위 조건으로 사용하면 인덱스가 사용되지 않습니다.
2. =, in은 다음 컬럼도 인덱스를 사용합니다.
in은=를 여러번 실행시킨 것과 동일합니다.in은 인자값으로 상수가 포함되면 문제 없지만, 서브쿼리를 넣게되면 성능상 이슈가 발생합니다.in의 인자로 서브쿼리가 들어가면 서브쿼리의 외부가 먼저 실행되고,in은 체크조건으로 실행됩니다.
3. AND연산자는 각 조건들이 읽어야할 row수를 줄이는 역할을 하지만, or 연산자는 비교해야할 row가 더 늘어나기 때문에 풀 테이블 스캔이 발생할 확률이 높습니다.
WHERE에서OR을 사용할때는 주의가 필요합니다.
4. 인덱스로 사용된 컬럼 값 그대로 사용해야만 인덱스가 사용됩니다.
- 인덱스는 가공된 데이터를 저장하지 않습니다.
where salary * 10 > 150000;는 인덱스가 사용되지 못하지만,where salary > 150000 / 10;은 인덱스를 사용합니다.- 컬럼이 문자열인데 숫자로 조회하면 타입이 달라 인덱스가 사용되지 않습니다. 정확한 타입을 사용해야만 합니다.
5. null 값의 경우 is null 조건으로 인덱스 레인지 스캔이 가능합니다.
인덱스 컬럼 순서와 조회 컬럼 순서
최근에는 이전과 같이 인덱스 순서와 조회 순서를 지킬 필요는 없습니다. 인덱스 컬럼들이 조회조건에 포함되어 있는지가 중요합니다.
조회 컬럼의 순서는 인덱스에 큰 영향을 끼치지 못합니다. 단, 옵티마이저가 조회 조건의 컬럼을 인덱스 컬럼 순서에 맞춰 재배열하는 과정이 추가 되지만 거의 차이가 없습니다.
출처🙏
