LIKE / BETWEEN
LIKE / BETWEEN 패턴을 사용하고자 할 때는 아래 네 가지 경우에 속하는지 반드시 점검하자.
(Between은 1, 2번만 점검)
1. 인덱스 선두 컬럼
2. NULL 허용 컬럼
3. 숫자형 컬럼
4. 가변 길이 컬럼
인덱스 선두 컬럼에 대한 옵션 조건을 LIKE / BETWEEN 연산자로 처리하지 말자
인덱스를 [고객ID + 거래일자]로 구성한 상황에서 고객ID에 대한 옵션 조건을 아래와 같이 LIKE로 처리했다고 생각해보자
SELECT *
FROM 거래
WHERE 고객 ID LIKE :cust_id || '%'
AND 거래일자 BETWEEN :dt1 AND :dt2사용자가 고객ID 값을 입력하면, 둘 다 범위검색 조건이어서 인덱스 스캔 과정에 약간 비효율이 있더라도 고객ID가 변별력이 매우 좋기 때문에 비교적 빠르게 조회된다.
그런데 만약 사용자가 고객ID 값을 입력하지 않으면, 인덱스에서 모든 거래 데이터를 스캔하면서 거래일자 조건을 필터링하는 불상사가 생긴다.
옵션 조건 처리에 위와 같이 LIKE/BETWEEN을 사용했다면, 인덱스를 [거래일자 + 고객ID] 순으로 구성해야 된다. 이때는 고객ID 값을 입력할 때 생기는 비효율을 감수해야 한다. 특정 고객의 거래를 조회하고 싶은데도 거래일자 범위에 속한 모든 거래 데이터를 스캔하면서 고객ID 조건을 필터링하기 때문이다.
NULL 허용 컬럼에 대한 옵션 조건을 LIKE/BETWEEN 연산자로 처리하지 말자
이것은 성능을 떠나 결과 집합에 오류가 생기기 때문이다. 위 SQL에서 :cust_id 변수에 NULL을 입력하면 조건절은 아래와 같은 형태가 된다.
SELECT *
FROM 거래
WHERE 고객ID LIKE '%'
AND 거래일자 BETWEEN :dt1 AND :dt2거래일자 조건에 해당하는 모든 고객의 거래를 선택하는 상황인데, 고객ID가 NULL허용컬럼이고 실제로 NULL이면 그 데이터는 결과집합에서 누락된다.
BETWEEN도 마찬가지이다.
숫자형이면서 인덱스 조건으로도 사용 가능한 컬럼에 대한 옵션 조건 처리는 LIKE 방식을 사용해선 안된다.
인덱스를 [거래일자 + 고객ID] 순으로 구성한 상황에서 SQL을 아래와 같이 작성하면 :cust_id에 값을 입력했을 때 두 컬럼 모두 인덱스 액세스 조건으로 사용된다.
SELECT *
FROM 거래
WHERE 거래일자 = :trd_dt
AND 고객ID LIKE :cust_id || '%'만약 고객ID가 숫자형 컬럼이면, 아래와 같이 자동 형변환이 일어나므로 고객ID가 필터 조건으로 사용된다. 특정 고객의 하루 치 거래를 조회하고 싶은데 하루 치 거래를 모두 스캔하면서 고객ID 조건을 필터링한다는 뜻이다.
SELECT *
FROM 거래
WHERE 거래일자 = :trd_dt
AND CONVERT(VARCHAR, 고객ID) LIKE :cust_id || '%'[고객ID + 거래일자] 순으로 구선한 인덱스는 아예 사용할수 없다.
LIKE를 옵션 조건에 사용할 때는 컬럼 값 길이가 고정적이어야 된다.
고객명 컬럼에는 '김광', '김광제' 등 길이가 다른 값이 입력 될 수도 있다. 그런데 고객명에 대한 옵션 조건을 아래와 같이 LIKE 패턴으로 처리하면, '김광'고객을 찾기 위해 :cust_nm 변수에 '김광'을 입력했을 때 '김광제' 고객도 같이 조회된다.
따라서 컬럼 값 길이가 가변적일 때는 변수 값 길이가 같은 레코드만 조회되도록 아래와 같이 조건절을 추가해야 된다.
WHERE 고객명 LIKE :cust_nm || '%'
AND LEGNTH(고객명) = LENGTH(ISNULL(:cust_nm, 고객명))UNION ALL 활용
아래와 같이 UNION ALL을 활용하는 방법도 있다.
:cust_id 변수에 값을 입력했는지에 따라 위아래 SQL 중 어느 하나만 실행되게 하는 방식이다.
SLEECT *
FROM 거래
WHERE :cust_id IS NULL
AND 거래일자 BETWEEN :dt1 AND :dt2
UNION ALL
SELECT *
FROM 거래
WHERE :cust_id IS NOT NULL
AND 고객ID = :cust_id
AND 거래일자 BETWEEN :dt1 AND :dt2:cust_id 변수에 값을 입력하지 않으면 위쪽 브랙치에서거래일자가 선두인 인덱스를 사용하고, 변수에 값을 입력하면 아래쪽 브랜치에서 [고객ID + 거래일자] 인덱스를 사용한다.
유일한 단점은 쿼리가 길어진다는 것이다.
