쿼리의 WHERE 조건이나 GROUP BY, ORDER BY 절에서 어떠한 방식으로 Index를 사용하는지 알아보겠습니다.
비교 조건의 종류와 효율성
조건절의 컬럼은 2가지 작업 범위 결정 조건, 체크 조건로 설명됩니다.
조건이 작업 범위를 결정하면 작업 범위 결정 조건으로 표현하고 조건이 작업범위를 줄이지 못하고 단순히 거르는 역할만 한다면 체크 조건이라고 표현합니다.
인덱스와 조건의 사용방식에 따라 컬럼이 작업 범위 결정 조건, 체크 조건중 선택이 됩니다.
SELECT * FROM dept_emp WHERE dpt_no ='D002' AND emp_no >= 10114;
Case A : Index(dept_no, emp_no)
Case B : Index(emo_no, dept_no)
Case A에서는 dept_no, emp_no 모두 작업 범위 결정 조건이 됩니다.
dept_no, emp_no 모두 Index Table의 모든 컬럼을 Scan하기전에 조건에 부합하는 모든 레코드를 조회할 가능성이 존재합니다.
Case B에서는 emp_no만 작업 범위 결정 조건이 됩니다.
Index Table의 emp_no의 모든 열을 조회하기 전에 emp_no 탐색이 종료되지만 dept_no열은 레코드를 끝까지 탐색해야지만 결과를 알 수 있습니다.
Multi Column Index
https://velog.io/@kma95278/Real-Mysql-8.0-Index-Scan-Direction
인덱스의 가용성
- SELECT * FROM employees WHERE first_name LIKE '%mer'
해당 쿼리는 레인지 스캔방식으로 인덱스를 이용할 수 없습니다.
Like문의 왼쪽에는 상숫값으로 사용해야합니다.
- SELECT * FROM dept_emp WHERE emp_no >= 10144
만약 Index가 dept_no, emp_no순 으로 설정되어 있다면 인덱스를 사용할 수 없습니다.
emp_no은 dept_no의 정렬에 종속되어 있기 때문에 emp_no 자체만으로는 사용이 불가능합니다.
가용성과 효율성 판단
아래의 경우에는 B-TREE Index 특성상 작업 범위 결정 조건으로 사용할 수 없습니다.
경우에 따라서 체크 조건 인덱스는 가능
- Not Equal - "<>", "NOT IN", "NOT BETWEEN", "IS NOT NULL"
- Like '%??' - 문자열 패턴이 앞 부분이 변수인 경우
- 데이터 타입이 서로 다른 비교
- 콜레이션이 다른 경우
- 스토어드 함수나 다른 연산자로 인덱스 칼럼이 변경된 후 비교
- NOT-DETERMINISTIC 속성의 스토어드 함수가 비교 조건에 사용되는 경우
일반적인 DBMS에서는 NULL값이 인덱스에 저장되지 않지만 MySQL에서는 NULL값 도 인덱스에 저장됩니다.
WHERE column IS NULL
다중 컬럼 인덱스의 작업 범위 결정 조건을 알아봅니다.
Index ix_test ( column 1, column 2, column 3 .. )
🟢작업 범위 결정 조건으로 인덱스를 사용하는 경우(i는 2보다 크고 n보다 작은 임의의 값)
- column i에 대해 다음 연산자 중 하나로 비교
- 동등 비교
- 크다 작다 형태
- Like 좌측 일치 패턴
🔴작업 범위 결정 조건으로 인덱스를 사용하지 못하는 경우
- column 1 컬럼에 대한 조건이 없는 경우
- column 2 컬럼의 비교 조건이 우의 인덱스 사용 불가 조건 중 하나인 경우
🌊 간단한 테스트
긍정, 부정의 차이
Not Equal = Table Full Scan
Equal = Use Index(Ref)
Using Index Condition
WHERE 절의 인덱스를 이용한 조건에 체크 조건이 존재하는 경우
Extra = Using Index Condition 값이 출력
체크 조건 처리를 스토리지엔진이 하도록 명령
MySQL 엔진과 스토리지 엔진은 TCP 통신을 하는데 체크 조건 처리를 안하고
작업 범위 결정 조건만 처리한 뒤 넘겨주면 조건에 부합하지 않는 데이터를 넘기는 것이기 때문에 낭비입니다.
책에서는 범위 결정 조건으로 실제 결과는 체크 조건으로..
이러한 내용들은 InnoDB Storage Engine의 B-Tree 자료구조에 관한 내용입니다.
해당 자료구조를 상용하는 시스템에서 동일하게 적용됩니다.
