주소 검색 기능을 개선하는 업무를 진행 중이다.

 

'성수이로 10길'이라는 단어로 검색을 했을 때 기존 주소 검색 시간은 15초 ~ 17초이다.

-- ORACLE
SELECT
    A.POST_NO,
    A.SIDO,
    A.SIGUGUN,
    ...
FROM
    주소_테이블 A
WHERE
    A.SEARCH_ZIP LIKE '%'|| :키워드 ||'%'

 

이런 형식으로 쿼리가 작성되어있고, SEARCH_ZIP 컬럼은 인덱스 설정이 되어있다.

인덱스가 설정되어있는데도 왜 이럴까.

 

1. 인덱스가 사용되지 않아서 Table Full Scan 되고 있는 걸까?

2. 인덱스가 사용되고 있는지 확인할 수 있는 방법은 없나?

 

먼저 인덱스가 사용되고 있는지 확인해야 하는데

Oracle에서는 실행할 쿼리 앞에 Explain Plan For라는 명령어로 쿼리의 실행 계획을 확인하여 인덱스 사용 여부를 알 수 있다.

Explain Plan
SQL을 실행할 때 DB가 어떤 경로를 선택하는지 실행 계획을 보여주는 도구이다.
SQL이 어떻게 실행될지, 어떤 인덱스가 사용되는지, 어떤 테이블이 먼저 스캔되는지 등을 알 수 있다.
EXPLAIN PLAN FOR
SELECT
    A.POST_NO,
    A.SIDO,
    A.SIGUGUN,
    ...
FROM
    주소_테이블 A
WHERE
    A.SEARCH_ZIP LIKE '%'|| :키워드 ||'%'

위 쿼리를 실행하면 Oracle이 해당 쿼리의 실행 계획을 메모리나 테이블에 저장한다.

이때 쿼리는 실제로 실행되지 않고, 계획만 생성된다.

 

실행 계획을 확인하려면 DBMS_XPLAN.DISPLAY를 사용하거나 계획 테이블에서 직접 데이터를 조회해야 한다.

SELECT * FROM table(DBMS_XPLAN.DISPLAY);
SELECT * FROM plan_table; -- 테이블 직접 조회

 

확인 결과, 인덱스는 사용되지 않고 Table Full Scan이 사용됐다.

약 600만 건의 데이터를 인덱스를 타지 않고, 하나씩 확인하고 있으니 속도가 느려질 수밖에.

 

 

왜 인덱스가 작동하지 않았을까

LIKE 쿼리에서 접미사 검색(LIKE '% 값')이나 중간 검색(LIKE '% 값%') 시 인덱스가 적용되지 않는다.

인덱스는 기본적으로 정렬된 데이터 구조인 B-Tree를 사용한다.

정렬된 구조이기 때문에 데이터의 앞부분을 기준으로 빠르게 값을 찾을 수 있다.

 

예를 들어, '서울특별시송파구'에 해당하는 주소값들을 조회한다면,

접두사 검색(LIKE ' 서울특별시송파구 %')을 했을 때 인덱스는 정렬된 순서에 따라 처음 ' 서울특별시송파구'로 시작하는 값을 찾아 해당 범위의 값을 읽어 들인다.

접두사 검색이라도 인덱스가 사용되지 않을 수 있다.
데이터베이스의 옵티마이저는 쿼리 실행 계획을 선택할 때 데이터 분포와 결과 집합의 크기를 고려한다.
만약 LIKE '서울%' 로 검색했을 때 너무 많은 행이 반환될 것으로 예상되면, 인덱스를 사용하지 않고 테이블 풀 스캔으로 전환하는 경우가 많다.

약 10만건 이상의 데이터를 조회했을 때 테이블 풀 스캔으로 전환되었다.

 

반면, 접미사 검색이나 중간 검색의 경우

데이터의 뒷부분과 데이터의 중간 어딘가에 값이 포함된 데이터를 찾는 것이기 때문에 인덱스가 제공하는 정렬된 구조로는 찾을 수 없다.

 

인덱스가 작동하지 않았던 이유

1. 인덱스는 앞부분부터 순차적으로 데이터를 탐색하는 구조를 가지고 있다.

2. 접미사 검색, 중간 검색은 데이터의 중간이나 끝을 기준으로 검색하기 때문에, 인덱스의 정렬된 구조를 이용할 수 없다.

 

이 두 가지 이유로 인해 인덱스가 작동하지 않고, 모든 데이터를 읽는 Table Full Scan이 사용되었다.

 

 

요구사항은 개발자 친화적이지 않다.

요구사항은 인풋 박스 하나에 사용자가 원하는 주소를 입력하고, 그에 해당하는 주소 값들이 조회되는 것이다.

 

어떤 사람은 서울을 먼저 입력할 수도 있고, 다른 사람은 성수이로, 석촌동... 이렇게 형식이 정해지지 않은 채로 입력될 수 있다.

그렇다면 중간 검색이 방법이 될 텐데, 위와 같은 이유로 중간 검색은 인덱스가 작동하지 않는다.

 

인덱스 힌트 사용

구조상 인덱스가 사용되지 않는다면 힌트(HINT)를 사용하여 인덱스 사용을 강제할 수 있다.

SELECT /*+ INDEX(A IDX_SEARCH_ZIP) */
    A.POST_NO,
    A.SIDO,
    A.SIGUGUN,
    ...
FROM
    주소_테이블 A
WHERE
    A.SEARCH_ZIP LIKE '%'|| :키워드 ||'%'

이런 방식으로 Oracle이 특정 인덱스를 사용하도록 유도할 수 있다.

 

역인덱스 (Reverse Index)

문자열을 역순으로 저장하고 인덱스를 만드는 방식이다.

예를 들어, 12345라는 값이 있을 때

  • 일반 인덱스에서는 12345 그대로 저장한다.
  • 역인덱스에서는 54321로 변환되어 저장된다.

이 방식은 중간 검색, 접미사 검색 같은 특정 패턴의 검색에서 성능을 향상할 수 있다.

CREATE INDEX REV_IDX_SEARCH_ZIP ON 주소_테이블(SEARCH_ZIP);

역인덱스 생성 후 평소와 동일하게 WHERE 절에서 인덱스 컬럼을 사용하여 조회하면 된다.

만약 역인덱스를 생성하려는 컬럼이 이미 일반 인덱스로 생성되어 있다면, 기존 인덱스를 삭제해야 역인덱스를 생성할 수 있다.

특정 검색 패턴에 역인덱스는 좋은 선택일 수 있으나, 모든 경우에 역인덱스를 사용하는 것은 오히려 역효과를 초래한다.

역인덱스는 기본 인덱스보다 더 많은 메모리를 사용하는 것도 이유다.

 

 

고민 끝에 인덱스 힌트를 사용하는 것으로 결정했다.

고객사에서는 DB 설정 변경을 별로 탑탁지 않아 하는 것 같다.

인덱스 힌트 사용했을 때 기존 조회 속도보다 2배나 빨라지는 효과를 봤지만

주소 검색에 7~8초라면...

저작자표시
tjdgus
tjdgus

티스토리툴바