[DB] LIKE 와 INDEX

LIKE 와 INDEX

• 테이블에 row 가 많은 경우 우리는 인덱스를 걸어주어 조회 속도를 높인다.
• LIKE 같은 경우 조건에 따라 인덱스를 타는 경우도 있고 안타는 경우도 있다.
• 어떤 경우에 인덱스를 타고 안타는지 확인해보자.

 

조건에 따른 인덱스 타는 여부

인덱스를 타는 경우

• WHERE name LIKE '안'
  - 문자가 일치 하는 데이터 검색
• WHERE name LIKE '안%'
  - 문자의 시작과 일치하는 데이터 검색

 

인덱스를 타지 않는 경우

• WHERE name LIKE '%안%'
  - 문자가 포함되는 데이터 검색
• WHERE name LIKE '%안'
  - 해당 문자로 끝나는 데이터 검색

 

WHERE name LIKE %안% 왜 인덱스를 안탈까?

요인은 INDEX 의 자료구조가 B*TREE 이다.

• 인덱스를 걸어주면 아래처럼 자료구조에 저장이 되는데 시작 값으로 데이터를 조회 한다.
  그렇기 때문에 포함되는 단어 혹은 끝나는 단어같은경우 기준을 잡을수 없기 때문에 풀스캔으로 검색을 하게 된다.

 

 

 

Explain 을 사용하여 MySQL 실행계획 보기

• 우리는 인덱스 관련한 데이터를 볼것 이기 때문에 type 에 대해 알아야 한다.

Type
system	테이블에 단 하나의 행만 존재(시스템 테이블). const join 의 특수한 경우이다.
const	SELECT 에서 조건을 만족하는 row가 하나일때
eq_ref	여러테이블이 조합되어 사용되는 경우 조인을 하게 되는데 인덱스의 모든 부분이 조인 되어 사용되어지고 인덱스가 PK 또는 UNIQUE NOT NULL 인덱스일 때 사용된다. 그리고 = 연산자를 사용하여 비교되는 인덱스 열에 사용 할 수 있다.
ref	조인 키의 가장 왼쪽 접두사만 사용하거나  키가 PK나 UNIQUE index 아닌경우를 말한다.
ref_or_null	ref 와 같지만 null 이 추가되어 검색되는 경우를 말한다.
index_merge	인덱스 머지 최적화가 사용되었음을 말한다. 즉 하나의 테이블에 2개 이상의 인덱스를 사용하는것이다.
range	지정된 범위 안에서 인덱스를 사용하여 검색하는 경우를 말한다.
index	인덱스 트리가 스캔되어 지는걸 제외 하고는 ALL 과 동일하다.
ALL	전 테이블과의 조인을 위해 풀스캔이 된다

 

 

실습 해보기

테이블 생성  및 인덱스 생성

CREATE TABLE `user` (
  `name` varchar(100) NOT NULL,
  `age` int NOT NULL,
  `id` bigint NOT NULL AUTO_INCREMENT PRIMARY KEY
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='고객 테이블';

CREATE INDEX idx_user_name ON user(name);

INSERT INTO user(name, age) VALUES 
('안성국', 11),
('강동원', 11),
('나영석', 12),
('마동석', 13),
('강경식', 17),
('강기영', 22),
('강남', 11),
('김나영', 25),
('김다솔', 33),
('차두리', 22),
('최원영', 22),
('차두식', 23),
('채원형', 34);

 

반응형

 

쿼리 테스트

1. WHERE name LIKE '강동원'

EXPLAIN SELECT 
    *
FROM 
    user
WHERE 
    name LIKE '강동원'

 

결과

type: range

 

2. WHERE name LIKE '강동원%'

EXPLAIN SELECT 
    *
FROM 
    user
WHERE 
    name LIKE '강동원%'

결과

• type: range

 

3. WHERE name LIKE '%강동원%'

EXPLAIN SELECT 
    *
FROM 
    user
WHERE 
    name LIKE '%강동원%'

 

결과

type: ALL(풀스캔)

 

4. WHERE name LIKE '%강동원'

EXPLAIN SELECT 
    *
FROM 
    user
WHERE 
    name LIKE '%강동원'

 

결과

type: ALL(풀스캔)

 

 

요약

• 앞서 이론과 동일하게 인덱스를 타는경우와 풀스캔이 되는경우를 실제 예제로 살펴 봤다.
• 다른 블로그를 보지말고 도큐먼트를 보는것이 정확한 것 같다.

 

참고:
https://dev.mysql.com/doc/refman/8.0/en/explain-output.html