SELECT 작성과 처리 순서 2

SELECT 작성과 처리 순서에서 절이 처리되는 순서와 비교 조건의 함정을 봤다. 이 문서는 자주 쓰지만 결과를 흔들기 쉬운 DISTINCT·COUNT·LIMIT의 동작과, 같은 쿼리가 서버 설정에 따라 거부되거나 값이 잘려 들어가게 만드는 SQL 모드(SQL mode), 그리고 로컬과 운영 서버에서 결과가 달라지는 이름의 대소문자·예약어 규칙을 다룬다.

DISTINCT·COUNT·LIMIT

• DISTINCT는 SELECT 목록 전체에 걸린다. SELECT DISTINCT a, b는 (a, b) 조합이 같은 행을 없앤다. DISTINCT(a), b처럼 괄호를 쳐도 DISTINCT는 함수가 아니므로 a에만 걸리지 않고, DISTINCT a, b와 똑같이 (a, b) 조합의 중복을 없앤다.
• COUNT(*)와 COUNT(col)은 세는 대상이 다르다. COUNT(*)는 행 수를, COUNT(col)은 그 칼럼이 NULL이 아닌 행 수를 센다. COUNT(DISTINCT col)은 NULL이 아닌 서로 다른 값의 개수다. InnoDB에서 COUNT(*)와 COUNT(1)은 똑같이 처리되어 성능 차이가 없다. InnoDB는 테이블 행 수를 따로 저장하지 않으므로, 조건 없는 COUNT(*)도 읽을 양이 가장 적은, 즉 가장 작은 세컨더리 인덱스(secondary index)를 끝까지 읽어서 센다. 세컨더리 인덱스가 없으면 클러스터링 인덱스(clustered index), 즉 테이블 전체를 읽는다.
• ORDER BY 없는 LIMIT은 어느 행이 나올지 정해져 있지 않다. ORDER BY가 있어도 정렬 칼럼 값이 같은 행끼리는 순서가 정해지지 않아, 실행 계획(execution plan)에 따라 결과를 20건씩처럼 나눠 보여 주는 페이징(pagination) 결과가 흔들릴 수 있다. ORDER BY created_at, id처럼 PK(Primary Key, 기본 키)를 뒤에 붙여 순서를 하나로 고정한다.
• 큰 OFFSET은 비쌀 수 있다. LIMIT 100000, 20은 20행만 돌려주지만, 서버는 건너뛸 앞의 10만 행도 차례로 찾아 낸 뒤 버려야 할 수 있다. 한참 뒤쪽 결과를 볼 때는 "마지막으로 본 키보다 큰 것"을 조건으로 거는 키셋 페이징(keyset pagination, WHERE id > ? ORDER BY id LIMIT 20)이 싸다. 인덱스에서 id가 ?보다 큰 첫 위치로 바로 찾아가 20행만 읽기 때문이다.

SQL 모드와 이름 규칙

SQL 모드는 서버가 문법과 값을 얼마나 엄격하게 검사할지 정하는 설정(sql_mode)이다. MySQL 8.4 기본값에는 ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION이 들어 있다. NO_ZERO_IN_DATE·NO_ZERO_DATE·ERROR_FOR_DIVISION_BY_ZERO는 엄격 모드(strict mode)와 함께 쓰면 INSERT·UPDATE에서 월·일이 0인 날짜, '0000-00-00', 0으로 나누기를 오류로 막는다. NO_ENGINE_SUBSTITUTION은 없는 스토리지 엔진(storage engine)을 지정하면 다른 엔진으로 바꾸지 않고 오류를 낸다. 아래 두 모드는 따로 본다.

• ONLY_FULL_GROUP_BY는 GROUP BY에 없는 칼럼을 집계 없이 SELECT·HAVING·ORDER BY에 쓰면 쿼리를 거부한다. 이 모드를 끄면 쿼리는 돌지만, 서버가 그룹마다 아무 행의 값이나 골라 결과가 매번 달라질 수 있다. GROUP BY 칼럼이 PK면 PK 값이 정해질 때 그 테이블의 다른 칼럼 값도 하나로 정해지므로, 서버가 이 함수 종속(functional dependency)을 알아보고 허용한다. 값이 그룹 안에서 같다는 것을 내가 알 때는 모드를 끄지 말고 ANY_VALUE(col)로 감싼다. ANY_VALUE()는 "그룹 안의 아무 값이나 괜찮다"고 알려 이 칼럼에서만 검사를 풀어 주므로, 다른 칼럼의 실수는 계속 잡힌다.
• 엄격 모드인 STRICT_TRANS_TABLES가 켜져 있으면, 칼럼에 맞지 않는 값이나 빠진 값을 넣는 문장은 트랜잭션(transaction)을 지원하는 InnoDB 테이블에서 오류로 중단되고 그 문장이 롤백(rollback)된다. 꺼져 있으면 서버가 값을 가장 가까운 유효값으로 고쳐 넣고 경고만 남겨, 데이터가 조용히 잘린다. INSERT IGNORE처럼 IGNORE를 붙이면 엄격 모드여도 오류가 경고로 낮아진다(INSERT·UPDATE·DELETE 고급).

대소문자는 대상마다 다르다. 칼럼 이름과 칼럼 별칭은 어느 OS에서든 대소문자를 구분하지 않는다. 테이블 이름은 lower_case_table_names가 정하는데 기본값이 리눅스 0(입력한 대로 저장하고 구분), 윈도우 1(소문자로 저장하고 구분 안 함), macOS 2(입력한 대로 저장하되 비교할 때 구분 안 함)라서, 로컬에서 되던 쿼리가 리눅스 서버에서 "테이블 없음"으로 실패할 수 있다. 이 값은 서버를 처음 초기화할 때만 정할 수 있다. 데이터 딕셔너리(data dictionary)가 이름을 비교하는 규칙이 초기화 때 이 값으로 정해지기 때문이다. 그래서 테이블 이름은 처음부터 소문자로 통일한다. 문자열 값의 비교는 콜레이션(collation)이 정한다. 8.4 기본 콜레이션 utf8mb4_0900_ai_ci는 이름의 ai(악센트 무시)·ci(대소문자 무시)대로 대소문자를 구분하지 않으므로 'abc' = 'ABC'가 참이다.

예약어(reserved word)는 SQL 문법에 쓰이는 단어라 테이블·칼럼 이름 같은 식별자(identifier)로 쓰려면 백틱(backtick, `)으로 감싸야 한다. 8.0에서 윈도우 함수(window function)가 들어오면서 RANK, ROWS, GROUPS, WINDOW, LAG, LEAD 같은 단어가 예약어가 됐다. 그래서 예전에 rank라는 칼럼을 만든 테이블은 업그레이드 뒤 쿼리가 문법 오류를 낼 수 있다. t.rank처럼 점 뒤에 오는 이름은 예약어여도 감싸지 않아도 된다.

면접 포인트

• "COUNT(*)와 COUNT(col) 차이?" → 행 수 / NULL이 아닌 행 수. COUNT(*)와 COUNT(1)은 InnoDB에서 같다.
• "페이징 결과가 겹치거나 빠져요" → 정렬 값이 같은 행끼리 순서가 정해지지 않았다. ORDER BY 뒤에 PK를 붙인다. 한참 뒤쪽 결과는 큰 OFFSET 대신 마지막 키 조건.
• ONLY_FULL_GROUP_BY를 끄는 것은 해결이 아니다. 결과가 비결정적이 된다. ANY_VALUE()나 GROUP BY 보강으로 푼다.
• "로컬에서 되던 쿼리가 서버에서 테이블 없음" → lower_case_table_names 기본값이 OS마다 다르다. 테이블 이름은 소문자로 통일한다.