[SQL AntiPattern] 3부 쿼리 안티패턴

SQL 을 효과적으로 활용하기 위해 SQL AntiPattern 의 내용을 정리한다.
3부에서는 DML(Data Manipulation Language)에 대해 다룬다.

14장 모르는 것에 대한 두려움: 누락된 값 구분하기

NULL 을 포함하는 컬럼에 대해 쿼리를 작성해야 한다.
아래는 쿼리에서 NULL 값을 생산적으로 사용하는 방법이다.

행을 생성할 때 값을 알수 없는 곳에 NULL 사용
적용 가능한 값이 없는 경우 NULL 사용
유효하지 않은 값이 입력되는 경우 NULL 사용
외부 조인에서 매치되지 않는 행의 컬럼 값을 채우는 경우 NULL 사용

안티패턴: NULL 을 일반 값처럼 사용

SQL 에서 NULL 을 0, false, 빈문자열과 다른 특별한 값으로 취급한다.
NULL 값은 특별한 규칙을 따른다.

수식에서 NULL 사용
- NULL 에 숫자를 더해도 알지 못하는 값이기 때문에 결과는 NULL
  - ex) SELECT NULL + 1 -> NULL
- AND, OR, NOT 같은 불리언 수식 결과는 NULL
NULL 을 가질 수 있는 컬럼 검색
- 여집합(NOT)으로 검색해도 값이 NULL 인 행은 검색되지 않음
- 동등 조건으로 검색해도 값이 NULL 인 행은 검색되지 않음
  - ex) WHERE column = NULL, WHERE column <> NULL 조건은 모두 NULL 인 행을 반환하지 않음
쿼리 파라미터로 NULL 사용
- NULL 을 일반적인 값처럼 사용하기 어려워 파라미터로 사용 불가
  - ex) WHERE column = ?
문제 회피하기
- NULL 을 금지하기 위해 다른 값(ex. -1, ''…)으로 대체하면 문제가 될 수 있음
  - 해당되는 값이 어떤 컬럼에서는 중요한 값이 될 수 있음
  - FK 설정이 어려워짐 (없는 값에 대한 행추가가 필요)
  - 해당 값에 대한 의미를 기억하거나 문서화 필요
- 누락된 값은 NULL 로 표현하는 것이 좋음

사용이 합당한 경우

외부 데이터를 불러오거나(import) 내보내기(export) 하는 경우
누락된 값에 대해 특별한 구분이 있는 경우

해법: 유일한 값으로 NULL을 사용하라

스칼라 수식에서의 NULL
- 다음 모든 수식의 결과는 NULL
  - NULL + 1
  - NULL = 1
  - NULL <> 1
  - NULL || 'string'
  - NULL = NULL (모르는 값과 모르는 값은 같은지 알 수 없음)
  - NULL <> NULL (모르는 값과 모르는 값은 다른지 알 수 없음)
불리언 수식에서의 NULL
- NULL AND TRUE -> NULL
- NULL AND FALSE -> FALSE (어떤 값이든 FALSE와 AND 연산하면 FALSE)
- NULL OR TRUE -> TRUE (어떤 값이든 TRUE와 OR 연산하면 TRUE)
- NULL OR FALSE -> NULL
- NOT NULL -> NULL
NULL 검색하기
- NULL 을 검색하기 위해서는 다른 연산 필요
  - IS NULL, IS NOT NULL
  - IS DISTINCT FROM 을 사용하면 IS NULL 확인 조건이 포함됨
    - MySQL 에서는 IS DISTINCT FROM 대신 <=> 지원
칼럼을 NOT NULL 로 선언하기
- NULL 값이 정책 위반 또는 의미가 없는 경우 NOT NULL 선언 권장
동적 디폴트
- COALESCE 을 통해 특정 쿼리에서만 디폴트 값 설정

15장 애매한 그룹: 그룹당 최댓값을 가진 행 얻기

그룹의 최댓값(또는 최솟값, 평균 값)과 함께 해당되는 다른 속성도 포함되는 쿼리를 작성하고자 한다.
GROUP BY 를 활용하면 값을 쉽게 구할 수 있지만 다른 속성의 값은 해당되는 행에 대한 값이 아닐 수 있다.

안티패턴: 그룹되지 않은 컬럼 참조

단일 값 규칙(Single-Value Rule)
- SELECT 목록의 모든 컬럼은 그룹당 하나의 값을 가져야 함
- 하나의 값을 가지지 못한 경우 단일 값 규칙 위반이 됨
내 뜻대로 동작하는 쿼리
- 다른 컬럼에서도 최대값에 대한 행을 가져온다면 다음과 같은 경우 추론 불가능
  - 최댓값을 구하는 컬럼의 값이 같은 행이 여러 개인 경우
  - 집계함수가 여러 개인 경우 (ex. MAX(date_reported), MIN(date_reported))
  - 매칭되는 행이 존재하지 않는 경우 (ex. SUM(hours) AS total, bug_id)

사용이 합당한 경우

함수 종속(functional dependency) 인 경우
- ex) 외래키 그룹과 참조된 테이블의 속성을 가져오는 경우

해법: 컬럼을 모호하게 사용하지 않기

함수 종속인 컬럼만 쿼리하기
- 모호한 컬럼을 쿼리에서 제거
상호 연관된 서브쿼리 사용하기
- 더이상 큰(작은) 값이 없는 데이터을 서브 쿼리로 검색
- 성능상 최적의 방법은 아님
유도 테이블(derived table) 사용하기
- 유도 테이블 = 인라인 뷰: 테이블이 들어갈 자리에 서브쿼리 사용
- 서브쿼리 사용하는 방법보다 확장적응성(scalability)이 좋음
- 임시 테이블에 중간 결과를 저장하므로 성능상 최적의 방법은 아님
조인 사용하기
- 대응되는 행이 없을 수도 있는 행의 집합에 대해 외부 조인 사용
- 개념이 어렵고 유지보수가 어려움
- 확장적응성(scalability)이 좋음
다른 컬럼에 집계 함수 사용하기
- 집계 함수를 사용하여 단일 값 규칙을 따르도록 함
각 그룹에 대해 모든 값 연결하기
- GROUP_CONCAT 함수를 사용하여 모든 값을 연결하여 조회
- 대응되는 값은 보여주지 않음
- 표준 SQL 은 아님
- 단일 값 규칙이 위반되는 경우 사용될 수 있음

16장 임의의 선택: 샘플 행 가져오기

대량의 집합에서 샘플 데이터를 요청하는 일은 흔하다.
임의의 샘플 데이터만 리턴할 수 있는 SQL 쿼리를 작성하고자 한다.

안티패턴: 데이터를 임의로 정렬하기

임의의 행을 고르기 위해 RAND() 로 정렬하여 데이터를 임의로 선택
- 일반적인 정렬에 비해 인덱스를 사용할 수 없음
- 데이터가 많은 경우 매우 많은 비용이 발생

사용이 합당한 경우

데이터 집합이 적은 경우
데이터가 늘어날 일이 거의 없는 경우

해법: 테이블 전체 정렬 피하기

임의 정렬은 매우 많은 비용이 발생되므로 대안이 필요함

1과 MAX 사이에서 임의의 키 값 고르기
- PK 값이 1부터 연속적으로 존재한다고 가정하여 사이의 난수 생성
다음으로 큰 키 값 고르기
- 빈틈이 있는 경우를 위해 찾아내는 키 값의 이상인 행을 찾음
  - ex) WHERE id >= {random_number}
- 빈틈 바로 앞에 있는 키 값이 자주 선택될 수 있는 단점
- 동일한 빈도로 선태되는 것이 중요하지 않으면 유용
모든 키 값의 목록을 구한 다음, 임의로 하나 고르기
- 애플리케이션 코드에서 모든 키만을 조회하여 임의로 하나를 선택
- 또 다른 비용이 발생
  - 모든 키를 조회하기 때문에 메모리 자원을 넘어설 수 있음
  - 쿼리가 두 번 발생됨
오프셋을 이용해 임의로 고르기
- 행의 개수를 세고 임의의 수를 고르고 오프셋
- LIMIT 절에 의존
- 키 값이 연속적이지 않아도 됨
- 각 행이 선택될 확률을 같아야 하는 경우 유용
벤더 종속적인 방법
- Oracle: SAMPLE 절

17장 가난한 자의 검색 엔진: 전체 텍스트 검색

텍스트 검색의 기능이 필요한데 성능과 확장 적응성이 필요하다.
하지만 SQL 에서 부분문자열(substring)을 비교하는 것은 비효율적이거나 부정확하다.

안티패턴: 패턴 매칭 사용

LIKE 또는 REGEXP 연산자 사용
- 많은 테이블 스캔 비용 발생
- 일반적인 인덱스 사용 불가
- 단순한 패턴 매칭을 원하지 않는 경우가 있음
  - ex) LIKE '%one%' 는 money, prone 포함

사용이 합당한 경우

간단한 경우의 패턴인 경우
아주 가끔씩만 실행되는 경우

해법: 작업에 맞는 올바른 도구 찾기

전체 텍스트 검색(full-text search)
- MySQL: FULLTEXT 인덱스
  - CHAR, VARCHAR, TEXT 타입의 컬럼에 정의 가능
  - 전체 텍스트 인덱스의 컬럼 지정 필요
- Oracle
  - CONTEXT: 하나의 텍스트 컬럼에 대해 정의, CONTRAINS() 연산자 사용
  - CTXCAT: 짧은 텍스트 샘플과 다른 컬럼 함께 사용, CATSEARCH() 연산자 사용
  - CTXXPATH: XML 문서 검색, existsNode() 연산자 사용
  - CTXRULE: 문서 분석하여 분류하는 규칙 설계
- PostgreSQL
  - TSVECTOR 타입: 텍스트 검색을 위한 인덱스
직접 만들기
- 전치 인덱스(inverted index)를 만들어서 검색
  - 검색할 모든 단어의 목록을 저장하여 연관 시킴 (다대다 관계)
  - 키워드에 대한 검색을 쉽게 하기 위해 저장 프로시저 작성
    - LIKE 또는 REGEXP 연산자를 사용하여 검색하여 매칭 여부 결정
    - 검색된 적이 있다면 키워드를 포함한 데이터를 매핑 테이블에 저장
    - 검색된 적이 없다면 검색이 오래 걸릴 수 있음

18장 스파게티 쿼리: SQL 쿼리 줄이기

SQL 프로그래머들은 흔하게 복잡한 문제를 하나의 쿼리로 해결하려고 한다.
복잡한 문제를 단순화하는 방법을 찾아본다.

안티패턴: 복잡한 문제를 한 번에 풀기

모든 작업을 한 줄의 코드로 해치워야 한다는 접근 방법은 좋지 않다.

의도하지 않은 제품
- 흔하게 카테시안 곱(Cartesian product)이 발생
  - 쿼리에 사용된 두 테이블의 관계 제한 조건이 없는 경우 발생
  - 훨씬 많은 행이 반환되어 성능 저하
그래도 충분하지 않다면
- 작성, 수정, 디버깅이 어려움
- 개선하는 데 많은 비용과 시간이 발생
- 복잡한 SQL 쿼리는 엔진이 최적화하여 실행하기 어려움
  - 여러 개의 단순한 쿼리 사용이 좋음
사용이 합당한 경우
- 프레임워크나 비주얼 컴포넌트 라이버리를 사용하는 경우
- 모든 결과에 대해 정렬된 순서로 봐야하는 경우

해법: 분할해서 정복하기

동일한 결과 집합을 만드는 여러 쿼리 중에 단순한 쿼리를 선택해야 한다.
하지만 너무 많은 SQL 쿼리나 명령문 실행은 가장 효율적인 방법은 아닐 수 있으므로 균형을 잘 유지해야 함

한 번에 하나씩
- 쿼리를 여러 개로 분할
  - 원치 않은 카테시안 곱 방지
  - 간단한 쿼리 추가가 쉬워짐
  - SQL 엔진은 단순한 쿼리를 최적화
  - 쿼리 설명이 쉬워짐
UNION 연산
- 여러 쿼리의 겨과를 하나의 집합으로 묶을 수 있음
- 결과를 구분할 수 있는 컬럼 추가 필요
상사의 문제 해결하기
- 원하는 값들을 분리해서 쿼리를 작성
SQL 을 이용한 SQL 자동 생성
- 여러 쿼리로 나누면 데이터 값에 따라 변하는 비슷한 쿼리가 나올 수 있음
- 코드 생성을 통해 출력하는 코드를 작성하면 반복 잡업 제거 가능

19장 암묵적 컬럼: 타이핑 줄이기

와일드카드(*)는 암묵적으로 컬럼을 조회하하는데 사용한다.
쿼리를 간결하게 하는 데 도움이 되지만 위험이 따른다.

안티패턴: 지름길 좋아하면 길을 잃은다.

리팩터링 방해
- INSERT 에 엉뚱한 값이 할당될 수 있음
- SELECT 에서 컬럼 순서가 바뀌면 결과가 달라질 수 있음
  - 오류 추적이 어려움
숨겨진 비용
- 성능과 확장적응성(scalability) 저하
  - 보다 더 많은 데이터가 전달될 수 있음
요청한 것을 얻은 것이다
- 모든 컬럼을 요청하거나 칼럼의 목록을 명시적으로 나열해야 함

사용이 합당한 경우

테스트를 위한 데이터 확인 용도의 임시 쿼리

해법: 명시적으로 컬럼 이름 지정하기

항상 필요한 컬럼을 나열 해야 함

오류 검증
- 테이블 컬럼 위치가 바뀌어도 결과가 바뀌지 않음
- 컬럼이 추가되어도 결과에 나타나지 않음
- 컬럼이 삭제되면, 쿼리에서 에러 발생
그거 필요하지 않을 꺼야
- 자연히 불필요한 컬럼을 쓰지 않게 됨
- 대역폭을 보다 효율적으로 사용
어쨌든 와일드카드를 포기해야 돼
- 와일드카드를 사용하면 컬럼 하나씩 다루기 어려움
- 처음부터 와일드카드를 사용하지 않으면 쿼리 변경이 쉬워짐

출처

SQL AntiPatterns