MySQL 인덱스 사용 규칙 (1)

SQL 작성 시 인덱스를 사용할 수 있도록 쿼리 작성에 대해 설명한다. 

 

인덱스를 사용하기 위한 기본 규칙 

 

1. WHERE 절이나 ORDER BY 또는 GROUP BY 가 인덱스를 사용하려면 기본적으로 인덱스된 컬럼의 값 자체를 변환하지 않고 그대로 사용한다는 조건을 만족해야한다. 

 

ex)

ex) 인덱스를 사용하지 못한 예 
SELECT * FROM salaries WHERE salary * 10 > 150000;

--->

SELECT * FROM salaries WHERE salary  > 150000 / 10 ;

 

인덱스의 컬럼을 변형해서 비교하는 경우에는 인덱스를 이용할 수 없게 된다.

 

2.  WHERE 절에 사용되는 비교 조건에서 연산자 양쪽의 두 비교 대상 값은 데이터 타입이 일치해야한다. 

 

CREATE TABLE tb_test ( age VARCHAR(10), INDEX ix_age(age));
INSERT INTO tb_test VALUES ('1'),('2'),('3');

SELECT * FROM tb_test WHERE age=2;

비교되는 두 값의 타입이 다르기 때문에  인덱스 레인지 스캔을 사용하지 못하고, 인덱스 풀 스캔 사용하게 된다.

  

WHERE 절의 인덱스를 사용 

CREATE TABLE employees ( first_name VARCHAR(10), last_name VARCHAR(10));
ALTER TABLE employees ADD INDEX idx_name ( first_name );


--- AND 조건 
SELECT * FROM employees WHERE first_name ='kebin' AND last_name = 'poly';

  first_name 컬럼 인덱스가 있고 AND 연산자로 연결됐다면 first_name의 인덱스를 이용한다.

 

SELECT * FROM employees WHERE first_name ='kebin' OR last_name = 'poly';

  (풀 테이블 스캔) + (인덱스 레인지 스캔 ) 의 작업량보다는 (풀 테이블 스캔) 한번이 더 빠르기 때문에 
옵티마이저는 풀 테이블 스캔 선택

 

 -> (first_name, last_name ) 인덱스가 있다면

ALTER TABLE employees ADD INDEX idx_first_last ( first_name, last_name);

SELECT * FROM employees  WHERE first_name ='kebin' OR last_name = 'poly';

 

WHERE 절에서 각 조건이 AND로 연결되면 읽어와야 할 레코드의 건수를 줄이는 역할을 하지만 각 조건이 OR 로 연결되면 읽어서 비교해야 할 레코드가 더 늘어가기 때문에 WHERE 조건에 OR 연산자가 있다면 주의해야한다. 

 

Reference

Real MySQL