MySQL - WHERE 절에서의 인덱스 사용 (+인덱스를 사용하지 않는 쿼리)

Index In Where clause

SQL문 튜닝에 있어서 Where절은 중요합니다. SQL문은 Where절이 가장 먼저 실행되고 Where절을 어떻게 설계하냐에 따라 Index를 활용한 조회를 하지 않고, 무식한 Full Scan을 할 수도 있기 때문입니다.

 

개발자 분들이 흔히 놓칠 수 있는 인덱스를 안타는 Where절 예시와 튜닝에 대해 포스팅했습니다.

LIKE '%_ ' 또는 LIKE '%_%'

문자열을 검색할 때 LIKE절을 많이 사용합니다. 그런데 인덱스를 타고 있지 않을 수도 있습니다.

EXPLAIN SELECT * FROM employees WHERE first_name LIKE "David%"

이 경우에는 인덱스를 잘탑니다.

하지만 아래의 경우들에는 인덱스를 잘 타지 않습니다.

EXPLAIN SELECT * FROM employees WHERE first_name LIKE "%David"
EXPLAIN SELECT * FROM employees WHERE first_name LIKE "%David%"

인덱스에 대해 아시는 분들이라면 이미 짐작하셨을 겁니다. 인덱스를 걸면 해당 컬럼은 정렬이 됩니다. 정렬된 문자열앞부분부터 검색해야 하는데, LIKE절이 '%'로 시작하면 앞 문자열을 모르니 인덱스를 사용할 수 없게 됩니다.

 

LIKE "%_%" 대신 INSTR를 사용할 수도 있는데, Index는 똑같이 안타고 성능도 저하됩니다.

EXPLAIN SELECT * FROM employees WHERE INSTR(first_name, "David") > 0;

따라서, LIKE "%_%"를 어쩔 수 없이 사용해야 할 때는 최대한 다른 컬럼의 조건을 활용해서 해당 조건으로 충분히 필터링한 후에, AND절로 LIKE연산이 수행되도록 데이터를 설계해야 합니다.

 

<참고>

between, like, <, > 등 Range scan(범위 스캔)은 해당 컬럼은 인덱스를 타지만, 뒤 컬럼은 결합 인덱스 유무와 관계 없이 인덱스로 사용하지 않습니다!

 

IN절은 예외로 다음 컬럼도 인덱스를 사용합니다.

OR 연산자

아래와 같은 테이블이 하나 있다고 가정하겠습니다. first_name이라는 컬럼에 인덱스를 걸었습니다.

CREATE TABLE employees (
    emp_no INT(11) NOT NULL,
    first_name VARCHAR(14) NOT NULL,
    last_name VARCHAR(16) NOT NULL,
    PRIMARY KEY (no),
    KEY idx_test (first_name)
)  ENGINE=INNODB DEFAULT CHARSET=UTF8

아래의 SELECT문을 EXPLAIN을 사용해서 실행 계획을 조회하겠습니다. 어떻게 될까요?

EXPLAIN SELECT * FROM employees WHERE first_name = "David" AND last_name = "Kevin";

네. first_name 컬럼의 인덱스를 타서 조회를 하게 됩니다. 그 결과로 last_name만 인덱스를 사용하지 않고 조회하게 되겠네요!

 

하지만! AND 연산자가 아니라 OR 연산자면 어떨까요?

EXPLAIN SELECT * FROM employees WHERE first_name = "David" OR last_name = "Kevin";

possible_keys에 first_name 컬럼 인덱스가 있는데도 사용하질 않습니다.

AND 연산자를 사용하면 결과에 따라 다음 조건은 몰라도 될 수가 있습니다.

예를 들어, WHERE A = 2 AND B = 3이 조건인데 A가 2가 아니면 다음 조건은 볼 필요도 없는 것이죠. 이럴 때는 인덱스를 탑니다.

 

OR 연산자의 경우에는 다릅니다. 앞 조건이 어떻든 간에 뒷 조건도 확인을 해야합니다. 어차피 뒷 조건도 확인할꺼라면 (풀 테이블 스캔) + (인덱스 레인지 스캔)보다 (풀 테이블 스캔) 1번이 더 빠르기 때문에 인덱스를 사용하지 않고 전체 조회를 하게 되는 것입니다.

 

즉, OR 연산자를 사용하면 OR 연산자에 관계된 모든 컬럼이 복합키로 설정되어 있지 않다면 인덱스를 사용하지 않습니다.

 

OR 연산자는 조심해서 사용해야 합니다.

컬럼 변형

기존 컬럼을 변환한 결과를 조건으로 사용하면 인덱스가 걸리지 않습니다.

 

다음 SQL은 문제 없이 salary 컬럼의 인덱스가 걸립니다.

EXPLAIN SELECT * FROM salaries WHERE salary = 0;

 

# 그럼 컬럼을 CHAR로 형변환을 하면 어떨까요?

EXPLAIN SELECT * FROM salaries WHERE CAST(salary AS CHAR) = "0";

 

 

결과는 인덱스가 걸리지 않습니다.

형변환이 필요하다면 Right쪽에서 변환하는 것이 바람직합니다.

EXPLAIN SELECT * FROM salaries WHERE salary = CAST(0 AS CHAR);

 

# 조건절에 Column에 계산식을 넣으면 어떻게 될까요..?

EXPLAIN SELECT * FROM salaries WHERE salary * 100 = 10000;

인덱스가 걸리지 않습니다.

그래서 WHERE절의 Left보다는 Right에 계산식을 넣는 것이 바람직합니다.

EXPLAIN SELECT * FROM salaries WHERE salary = 10000 / 100;

# 문자열 처리 주의

 

앱단에서 SQL문을 실행할 때 아래의 실수를 하는 경우도 많습니다.

EXPLAIN SELECT * FROM salaries WHERE salary = '10000';

int 필드에 문자열로 검색을 하게 되면서 해당 쿼리도 인덱스를 타지 못합니다.

부정문

다음 SQL문은 정상적으로 salary 컬럼 인덱스를 타고 있습니다.

EXPLAIN SELECT * FROM salaries WHERE salary = 0;

하지만 아래와 같은 부정문은 인덱스를 타지 않습니다.

EXPLAIN SELECT * FROM salaries WHERE salary != 0;

 

인덱스를 사용하게 하려면 아래와 같이 튜닝할 수 있습니다.

EXPLAIN SELECT * FROM salaries WHERE salary > 29 AND salary < 31;