대문자 함수 인덱스까지 만들었는데 로그인 조회는 여전히 고객 테이블을 다 읽는다

중급DDL이커머스16 / 700

이 문제는 교재 18화 졸업 과제와 같은 이커머스 데이터베이스를 씁니다. 『튜너의 1년』을 순서대로 읽고 있다면 졸업 과제를 먼저 풀고 오세요. 아래 「테이블과 인덱스」 칸에 과제의 답 일부(제약 조건, 인덱스, 통계의 실제 상태)가 나옵니다.

상황

신규 이커머스의 로그인과 비밀번호 찾기 API는 이메일로 고객을 찾는다. 가입 화면이 입력한 대소문자를 그대로 저장해 와서, 개발팀은 대소문자를 무시하려고 양쪽을 LOWER로 감쌌다. 하루 약 30만 번 호출된다. 지난주 개발자 한 명이 "함수 인덱스를 만들면 된다더라"라며 UPPER(email) 인덱스를 만들었는데 계획은 그대로다.

테이블과 인덱스

customers: 5,230행 · 51블록 · email VARCHAR2 · 유니크 제약 UQ_CUSTOMERS_EMAIL (email)
문제 설정: 대문자가 섞인 이메일 312건 · 대소문자만 다르고 같은 주소인 계정 2쌍(Kim.J@…와 kim.j@… 같은 경우)
인덱스 PK_CUSTOMERS (customer_id) · UQ_CUSTOMERS_EMAIL (email) · IX_CUS_EMAIL_UP (UPPER(email)): 지난주 생성 · 통계 최신

원본 SQL

sql

SELECT customer_id, name, grade
  FROM customers
 WHERE LOWER(email) = LOWER(:email)

실행계획 학습용 예시

xplan

------------------------------------------------------------------------------------------
| Id  | Operation         | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |      1 |        |      1 |00:00:00.01 |      51 |
|*  1 |  TABLE ACCESS FULL| CUSTOMERS |      1 |     52 |      1 |00:00:00.01 |      51 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(LOWER("EMAIL")=LOWER(:EMAIL))

문제

이메일에 유니크 인덱스와 UPPER(email) 함수 기반 인덱스가 다 있는데도 테이블 전체를 읽는 이유를 설명하시오.
대소문자를 무시하는 규칙은 지키면서 한 건만 찾아가도록 DDL과 SQL을 작성하시오. 이미 있는 데이터 때문에 생기는 문제와 그 처리도 설명하시오.

개선 SQL원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.

개선 사유무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자

모범답안

모범 답안 SQL

sql

-- ① 대소문자만 다른 계정 2쌍을 먼저 찾아 정리한다 (CS팀과 병합이나 휴면 처리를 정한 뒤)
SELECT LOWER(email), COUNT(*) FROM customers GROUP BY LOWER(email) HAVING COUNT(*) > 1;

-- ② 쓰이지 않는 대문자 인덱스를 지우고, 조건과 같은 식으로 유니크 함수 기반 인덱스를 만든다
DROP INDEX IX_CUS_EMAIL_UP;
CREATE UNIQUE INDEX UX_CUS_EMAIL_LOWER ON customers (LOWER(email));
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'CUSTOMERS', method_opt => 'FOR ALL HIDDEN COLUMNS SIZE 1');

-- ③ SQL은 그대로 (식이 인덱스와 글자까지 같다)
SELECT customer_id, name, grade
  FROM customers
 WHERE LOWER(email) = LOWER(:email);

개선 후 실행계획 학습용 예시

xplan

---------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |      1 |        |      1 |00:00:00.01 |       3 |
|   1 |  TABLE ACCESS BY INDEX ROWID| CUSTOMERS          |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  2 |   INDEX UNIQUE SCAN         | UX_CUS_EMAIL_LOWER |      1 |      1 |      1 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(LOWER("EMAIL")=LOWER(:EMAIL))

모범 답안 서술

1번 줄은 filter(LOWER("EMAIL")=LOWER(:EMAIL))이다. UQ_CUSTOMERS_EMAIL에는 email 원래 값이 정렬되어 있고 LOWER로 감싼 값은 없으므로 쓸 수 없다. 지난주 만든 IX_CUS_EMAIL_UP에는 UPPER(email) 값이 정렬되어 있다. 함수 기반 인덱스는 조건의 식이 인덱스를 만든 식과 같아야 쓰인다. 대소문자를 무시한다는 뜻은 같아도 UPPER와 LOWER는 다른 식이라 조건과 일치하지 않는다. 그래서 호출마다 5,230행을 다 읽는다(Buffers 51). 조건과 같은 식 LOWER(email)로 함수 기반 인덱스를 만들면 SQL을 고치지 않고 한 건만 찾아간다(51 → 3). 하루 30만 번이면 약 1,530만 → 90만 블록이다. 이왕이면 UNIQUE로 만든다. 그러면 대소문자만 다른 주소로 새 계정을 만드는 것까지 막아, 로그인 규칙(대소문자 무시)과 데이터 규칙이 일치한다. 다만 지금 데이터에 대소문자만 다른 계정이 2쌍 있어서 그대로는 ORA-01452로 만들어지지 않는다. 중복 2쌍을 먼저 찾아 CS팀과 병합이나 휴면 처리를 정한 뒤에 만든다. 쓰이지 않는 IX_CUS_EMAIL_UP은 지운다. 남겨 두면 가입하거나 이메일을 바꿀 때마다 고칠 인덱스만 하나 늘어난다. 다른 방법은 저장할 때부터 소문자로 통일하는 것이다. 기존 312건을 LOWER로 UPDATE하고 CHECK (email = LOWER(email)) 제약을 두고, 가입과 수정 프로그램이 소문자로 넣게 한다. 그러면 SQL을 email = LOWER(:email)로 바꿔 이미 있는 UQ_CUSTOMERS_EMAIL로 찾을 수 있고 인덱스를 더 만들지 않아도 된다. 대신 이메일을 쓰는 프로그램을 모두 고쳐야 하고, 이 경우에도 중복 2쌍은 UPDATE 단계에서 유니크 제약에 걸리므로 먼저 정리해야 한다.

해설

함정: SQL을 UPPER(email) = UPPER(:email)로 바꿔 기존 대문자 인덱스에 맞추는 것도 성능 면에서는 답이다. 하지만 대소문자만 다른 주소가 중복으로 남는 문제는 그대로다. 인덱스를 UNIQUE로 둘 수 있는지까지 보면 LOWER 쪽 유니크 인덱스가 규칙에 맞는다.
함수 기반 인덱스는 식이 글자 단위로 같아야 쓰인다. LOWER(TRIM(email))처럼 앞뒤 공백까지 지우는 조건이라면 그 식 그대로 인덱스를 만들어야 한다(교재 03화 §4.2의 'YYYYMMDD'와 'yyyymmdd').
51블록짜리 테이블이라 한 번은 0.01초 안쪽이지만, 하루 30만 번이면 버퍼 캐시에서 1,500만 블록을 읽는 CPU가 든다. 작은 테이블의 FULL도 호출 횟수를 곱해 본다(교재 07화).
함수 기반 인덱스를 만든 뒤에는 숨은 컬럼의 통계를 모은다. 통계가 없으면 E-Rows를 기본값으로 추정해 계획이 흔들릴 수 있다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황