[MySQL] 인덱스 적용안되고 풀스캔이 적용되는 패턴

들어가기 전

이번 포스팅에서는 인덱스를 설정을 하고 인덱스 스캔방식을 원했지만 풀스캔이 쿼리 패턴에 대해서 알아보겠습니다.

Mysql에서 지원해 주는 Explain이라는 키워드를 사용하여 스캔방식을 파악할 것입니다.

 

 

테이블 구조 및 데이터 삽입

 

테이블 생성

 

create table member(
	id bigInt primary key auto_increment,
    name varchar(255) not null,
    age int not null,
    login_count bigint default 0,
    created_at datetime not null default now()
);

 

인덱스 생성

 

CREATE INDEX idx_login_count ON member(login_count);

 

 

더미 데이터 생성

 

DELIMITER $$
DROP PROCEDURE IF EXISTS insertLoop$$
create procedure insertLoop() 
BEGIN
  DECLARE i INT default 1;
  WHILE i <= 500000 DO
	INSERT INTO member(name, age, login_count)
    values(concat("더미데이터", i), i, i + 2);
    set i = i + 1;
	END while;
END$$
DELIMITER $$

CALL insertLoop;
$$

 

 

풀스캔이 적용되는 패턴

• 인덱싱 된 컬럼이 가공되는 경우
• 인덱싱 되지 않은 컬럼을 조건절에 OR 연산을 함께 사용하는 경우
• LIKE문에서 와일드카드(%)가 맨 앞에 존재할 경우
• 정규화를 이용하여 비교할 경우
• 복합 인덱스로 설정 된 컬럼 중 선행 컬럼이 조건에서 사용되지 않을 경우

 

풀스캔이 적용되는 패턴을 하나씩 알아보겠습니다.

 

 

인덱싱 된  컬럼이 가공되는 경우

 

explain select * from member where login_count + 1000 = 10000;

 

 

타입을 보면 "ALL"인 것을 확인할 수 있습니다. WHERE 문에 생성한 인덱스의 값을 가공하여 사용하였더니 인덱스를 활용하지 않고 풀스캔하는 것을 확인할 수 있습니다. 현재 예시에서는 인덱싱 된 컬럼에 "+" 연산자를 활용하여 값을 가공한 예시만 되어있지만 데이터베이스에서 적용하는 함수를 활용해서 가공하는 방식도 풀스캔 방식으로 동작합니다.

 

 

인덱싱 되지 않은 컬럼을 조건절에 OR 연산을 함께 사용하는 경우

 

explain select * from member where login_count = 1002 OR age = 1000 ;

 

 

 

위에서 login_count를 인덱스로 설정하였지만 age는 인덱스로 설정을 안 했습니다. 해당 쿼리에서 인덱싱 된 컬럼(login_count)과 인덱싱 되지 않은 컬럼(age)을 OR 연산과 함께 사용하면 풀스캔으로 동작합니다.

 

 

Like문에서 와일드카드(%)가 맨 앞에 존재하는 경우

 

설명하기 앞서 추가적인 인덱스를 생성하고 설명하겠습니다.

 

인덱스 생성

 

create index idx_name ON member(name)

 

 

memeber 테이블에 있는 name 컬럼에 인덱스를 설정하였습니다.

 

explain select * from member where name like '%더미데이터10000%';

 

 

 

인덱스로 설정된 name 컬럼을 LIKE 문과 함께 사용하였더니 풀스캔으로 동작하는 것을 확인할 수 있습니다.

 

 

정규식을 이용하여 비교하는 경우

 

explain select * from member where name regexp '[가-힇]';

 

 

 

정규식을 이용하여 인덱스로 설정된 컬럼을 조건문에 넣었을대 인덱스 스캔방식이 아닌 풀스캔 방식으로 동작하는 것을 확인할 수 있습니다.

 

 

복합 인덱스로 설정 된 컬럼 중 선행 컬럼이 조건에서 사용되지 않을 경우

 

 

복합 인덱스 생성

 

create index idx_name_created_at ON member(name, created_at);

 

name과 created_at을 가지고 복합 인덱스를 생성하였습니다.

 

 

explain select * from member where  created_at = '2024-06-30 18:12:11';

 

 

 

name과 created_at으로 복합 인덱스를 생성을 하였지만 해당 쿼리에서는 created_at으로만 조회를 해서 인덱스 스캔방식이 아닌 풀스캔 방식으로 동작하는 것을 확인할 수 있습니다.