BackEnd

[MySql] 쿼리 속도 개선 (Virtual Column 및 인덱스 적용) Or 절 여러개 예시

mingg123 2023. 7. 28. 17:23

쿼리의 성능을 향상시킨 경험에 대해 적어보려고 한다. 

처음부터 내가 해당 방식을 이용한 것은 아니다.

아래의 시행 착오를 겪었었고, 최종적으로 선택한 방안에 대해 공유하려고 한다. 

  • 쿼리 모두 분해
  • SUBSTRING, CONVERT 등 없에고 자바 로직으로 해결 
  • 함수 인덱스 적용 
  • VirtualColumn 추가 및 인덱스 적용 

 

문제

 

사용하고있는 검색 쿼리가 매우 느려서 개선이 필요했다.

@Query("SELECT u FROM User u WHERE " +
            "u.completed = true AND " + 
            "(u.username = :searchTerm OR " +
            "SUBSTRING(u.phone, LENGTH(u.phone) - 7) = :searchTerm OR " +
            "LOWER(u.userId) = LOWER(:searchTerm) OR " +
            "LOWER(u.email) = LOWER(:searchTerm))"
    )

사실 OR절이 굉장히 많기도하고, SUBSTRING, CONVERT 처럼 컬럼에 직접적인 변화를 주는 함수들이 많아서 오래걸렸다. 

 

 

우선 인덱스 적용방법과 확인방법에 대해서 알아보자.

인덱스 적용 방법 

 

단일 인덱스 적용 / 제거

ALTER TABLE `user` ADD INDEX userphoneIndex (phone);
ALTER TABLE `user` DROP INDEX 인덱스아이디;

 

인덱스 확인 

SHOW INDEX FROM `user`;

 

인덱스 추가후, select Query 확인

select * from `user` u WHERE u.phone = '01000000000'

Index 붙일경우 소요시간 1731 -> 9 ms로 확 줄었음 

 

쿼리 수행 시간은 윈도우 > Show View > 쿼리관리자를 통해 확인 가능 

 

email 인덱스 추가

email도 마찬가지로 index를 붙이기전 1671 -> 68 ms 러 확 줄었음 

 

 

단일 인덱스 여러개 추가 

 

문제는 해당 쿼리를 사용할 경우  email, phone, username에 모두 index를 달았더니 결과가 미미했다. 

@Query("SELECT u FROM User u WHERE " +
            "u.completed = true AND " + 
            "(u.username = :searchTerm OR " +
            "SUBSTRING(u.phone, LENGTH(u.phone) - 7) = :searchTerm OR " +
            "CONVERT(u.id, CHAR(255)) = :searchTerm OR " +
            "LOWER(u.userId) = LOWER(:searchTerm) OR " +
            "LOWER(u.email) = LOWER(:searchTerm))"
)

인덱스를 적용전: 2198, 인덱스 적용후 2195

그래서 멀티컬럼 인덱스를 적용해보았다. 

 

 

멀티컬럼 인덱스 적용

ALTER TABLE `user` ADD INDEX usersearchIndex (phone, username, email);

결과 차이가 없었다. 

 

너무나 당연한 결과였다. SUBSTRING, CONVERT, LOWER를 이용할 경우 인덱스가 적용이 되지 않는다. 

 

 

explain 쿼리

 

정상적으로 인덱스가 적용될 경우 explain 했을때 아래처럼 key컬럼에 사용한 인덱스가 표시되어야 한다. 

이로인해 새롭게 택한방법은 함수형 인덱스 적용 혹은 가상컬럼을 만들고 거기에 함수형 인덱스를 추가하는 것이다. 

 

 

첫 번째 방안은 함수 인덱스를 적용한 방안이다. 

 

함수 인덱스 적용 예시 

왼쪽항에 있는 함수에 대한 인덱스를 적용을 하려했다. 

CREATE INDEX idx_phone_last ON user ((SUBSTRING(phone, LENGTH(phone) - 7)));
CREATE INDEX idx_user_lower_user_id ON user ((LOWER(user_id)));
CREATE INDEX idx_user_lower_email ON user ((LOWER(email)));

Expression으로 함수 인덱스가 올바르게 적용된 것을 확인했다. 

 

적용 이후 아래의 쿼리를 수행했다.

@Query("SELECT u FROM User u WHERE " +
            "u.completed = true AND " + 
            "(u.username = :searchTerm OR " +
            "SUBSTRING(u.phone, LENGTH(u.phone) - 7) = :searchTerm OR " +
            "LOWER(u.userId) = LOWER(:searchTerm) OR " +
            "LOWER(u.email) = LOWER(:searchTerm))"
    )

 

개선 결과

 

적용 전: 2046 ms (약 2초)

적용 후: 290ms  

 

매우 빨라졌다. 

 

 

 

두번째 방안은 Virtual Column을 추가하고 인덱스를 적용하는 방안이다. 

 

VirtualColumn 추가 및 인덱스 적용 

 

virtual column 생성 및 인덱스 적용 방법

 

where절 컬럼의 왼쪽항에서 함수를 사용하게 되면 인덱스를 타지 않는다. 

explain SELECT * from `user` u where u.completed = true
AND (
u.username = :searchTerm OR
SUBSTRING(u.phone, LENGTH(u.phone) -7) = :searchTerm 
)

이런 쿼리가 있고, SUBSTRING(u.phone, LENGTH(u.phone) -7 컬럼이 있다고 치자.

 

해당 쿼리에 인덱스를 적용하고 싶다면  user테이블에 virtual column을 하나 생성한다. 

ALTER TABLE `user`
ADD COLUMN phone_last VARCHAR(255) GENERATED ALWAYS AS (SUBSTRING(phone, LENGTH(phone) - 7)) STORED;

CREATE INDEX idx_phone_last ON `user` (phone_last);

 

 

explain 결과 

EXPLAIN SELECT * FROM `user` u
WHERE u.completed = true AND (
    u.username = :searchTerm OR
    u.phone_last = :searchTerm 
);

이후 실행해보면  만들어주었던 가상컬럼의 phone_last 컬럼에 대한 index가 탄것을 확인할 수 있다. 

 

자 하나 테스트 해보았으니 본격적으로 내가 필요한 쿼리를 바탕으로 적용해보도록 하겠다. 

 

 

사용 SQL

 @Query(value = "SELECT * FROM user u " +
      "WHERE u.completed = true AND " +
      "(u.username = :searchTerm OR " +
      "u.phone_last = :searchTerm OR " +
      "u.user_id_lower = :searchTerm OR " +
      "u.email_lower = :searchTerm)",
      nativeQuery = true)

 

 

virtual Column 추가 및 인덱스 생성

 

총 4개의 컬럼을 검색만을 위해 영구적으로 뚫는것은 오히려 메모리 비효율이라고 느꼈고, virtual column을 추가했다. 

 

 (Store 옵션은 영구저장, virtual가 가상 컬럼임) 

ALTER TABLE user
ADD COLUMN phone_last VARCHAR(255) GENERATED ALWAYS AS (SUBSTRING(phone, LENGTH(phone) - 7)) virtual;

ALTER TABLE user
ADD COLUMN user_id_lower VARCHAR(255) GENERATED ALWAYS AS (LOWER(user_id)) virtual;

ALTER TABLE user
ADD COLUMN email_lower VARCHAR(120) GENERATED ALWAYS AS (LOWER(email)) virtual;


CREATE INDEX idx_phone_last ON user (phone_last);

CREATE INDEX idx_user_id_lower ON user (user_id_lower);

CREATE INDEX idx_email_lower ON user (email_lower);

 

explain 쿼리 결과 

EXPLAIN SELECT * FROM `user` u
WHERE u.completed = true AND (
    u.username = :searchTerm OR
    u.phone_last = :searchTerm OR
    u.user_id_lower = :searchTerm OR
    u.email_lower = :searchTerm
);

만들어두었던 함수인덱스가 가상컬럼에 적용되어있음을 key, Extra를 통해 확인했다.

만약 인덱스가 걸리지 않을 경우에는 key에는 빈값이, Extra에 Using Where라고 나와있을 것이다. 

 

 

인덱스를 확인해보면 내가 만들어주었던 것들이 걸림을 확인할 수 있다. 

SHOW INDEX FROM `user`;

 

개선 결과

 

적용 전: 2046 ms (약 2초)

적용 후: 190ms  

 

 

인덱스가 매우 파워풀한 기능이구나 느꼈다.