[Real MySQL 8.0] 11장. 쿼리 작성 및 최적화(1)

 

 

Contents

• 11.3 MySQL 연산자와 내장 함수
  • 11.3.1 리터럴 표기법
  • 11.3.2 MySQL 연산자
  • 11.3.3 MySQL 내장 함수
• 11.4 SELECT 최적화 포인트
  • 지연된 조인(Delayed Join) 실무 예시
  • 서브쿼리 위치별 특성과 전환 전략
  • 잠금을 사용하는 SELECT (NOWAIT, SKIP LOCKED)
• 참고1) 실무 쿼리 튜닝 사례 및 베스트 프랙티스
• 참고2) 실행 효율성(Execution Efficiency) 고급 기법

 

11.3 MySQL 연산자와 내장 함수 #11-3

MySQL은 ANSI 표준 SQL과 호환되는 부분이 많지만, sql_mode 설정 및 MySQL 고유 문법에 따라 동작이 달라질 수 있다. 이 절에서는 리터럴 표기법, 주요 연산자, 실무에서 빈도가 높은 내장 함수의 핵심을 정리했다.

11.3.1 리터럴 표기법 #11-3-1

문자열

• SQL 표준에서 문자열은 홑따옴표(')로 표기했다.
• MySQL에서는 쌍따옴표(")도 문자열에 사용할 수 있지만, ANSI_QUOTES 모드에서는 쌍따옴표가 식별자용으로 동작하므로 홑따옴표 사용이 안전했다.
• 식별자(테이블/칼럼명)가 예약어와 충돌할 경우 역따옴표(`)로 감싸는 방식이 일반적이었다.

SELECT * FROM departments WHERE dept_name = 'Sales';
SELECT * FROM departments WHERE dept_name = "Sales";

주의

운영 환경에서 sql_mode가 다를 수 있으므로(특히 ANSI_QUOTES), 문자열 표기는 홑따옴표로 통일하는 편이 이식성과 안정성이 높았다.

숫자

숫자 타입과 문자열 타입을 혼용한 비교는 실행은 가능하지만, 내부 형변환으로 인해 인덱스를 사용하지 못하거나(풀 스캔), 변환 불가 값 때문에 실패할 수 있었다.

SELECT * FROM tab_test WHERE number_column = '10001';
SELECT * FROM tab_test WHERE string_column = 10001;

요점

첫 번째 형태는 “상수”가 숫자로 변환되는 방향이어서 상대적으로 안전했으나, 두 번째 형태는 “컬럼”이 숫자로 변환되는 방향이어서 인덱스 사용이 깨질 수 있었다.

날짜

MySQL은 정해진 포맷의 문자열을 DATE/DATETIME으로 자동 변환할 수 있어, 명시적 변환 함수가 필수는 아니었다. 다만 칼럼을 함수로 감싸는 형태는 인덱스 사용에 불리했다.

SELECT * FROM orders WHERE order_date = '2024-01-15';
SELECT * FROM orders WHERE order_date = '20240115';

불리언

• BOOLEAN은 내부적으로 TINYINT 동의어로 처리되었다.
• TRUE는 1, FALSE는 0으로 저장되었다.
• 0/1 외의 값도 저장될 수 있으므로(스키마/검증 정책에 따라) 도메인 제약을 별도로 두는 편이 안전했다.

CREATE TABLE tb_boolean (bool_value BOOLEAN);
INSERT INTO tb_boolean VALUES (TRUE), (FALSE), (2);
SELECT * FROM tb_boolean WHERE bool_value = TRUE;

11.3.2 MySQL 연산자 #11-3-2

동등 비교 (=, <=>)

• =는 일반 비교로, NULL 비교 결과가 NULL이 될 수 있었다.
• <=>는 NULL-safe 비교로, NULL끼리 비교 시 TRUE를 반환했다.

SELECT 1 = 1, NULL = NULL, 1 = NULL;
SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;

AND / OR 우선순위

AND가 OR보다 우선순위가 높으므로, 혼합 조건에서는 괄호로 의도를 명시하는 것이 안전했다.

SELECT * FROM employees
WHERE dept_id = 1 OR dept_id = 2 AND salary > 5000;

SELECT * FROM employees
WHERE (dept_id = 1 OR dept_id = 2) AND salary > 5000;

REGEXP

• 정규 표현식 매칭이 가능했지만, 인덱스 활용이 제한되어 풀 스캔이 발생하기 쉬웠다.
• 실무에서는 가능한 한 후보군을 다른 조건으로 먼저 줄인 뒤 REGEXP를 적용하는 방식이 유리했다.

SELECT * FROM employees WHERE name REGEXP '^Kim';
SELECT * FROM employees WHERE name RLIKE 'son$';

LIKE

• abc% 형태는 인덱스 레인지 스캔 가능성이 높았다.
• %abc 형태는 인덱스 사용이 어려워 풀 스캔이 발생하기 쉬웠다.

SELECT * FROM users WHERE name LIKE 'Kim%';
SELECT * FROM users WHERE name LIKE '%Kim';

BETWEEN / IN

• BETWEEN은 양 끝을 포함하는 범위 비교였고, 레인지 스캔 최적화가 가능했다.
• IN은 다중 동등 비교로 최적화될 수 있었고, MySQL 8.0부터 튜플 IN도 활용 가능했다.

SELECT * FROM employees WHERE salary BETWEEN 3000 AND 5000;
SELECT * FROM employees WHERE dept_id IN (1, 2, 3);
SELECT * FROM dept_emp WHERE (dept_no, emp_no) IN (('d001', 10017), ('d002', 10144));

NOT IN 주의

NOT IN (subquery)는 NULL 포함 여부에 따라 결과가 달라질 수 있었고, 실행 계획 측면에서도 안티 조인 변환 여부를 확인하는 습관이 필요했다.

11.3.3 MySQL 내장 함수 #11-3-3

NULL 처리 (IFNULL, ISNULL, NULLIF, COALESCE)

SELECT IFNULL(NULL, 'default');
SELECT ISNULL(NULL);
SELECT NULLIF(1, 1);
SELECT COALESCE(NULL, NULL, 'value');

현재 시각 (NOW, SYSDATE)

함수	특징
NOW()	하나의 SQL 문 내에서 항상 같은 값을 반환했다.
SYSDATE()	호출 시점에 따라 값이 달라질 수 있었고, 비확정적 특성으로 최적화에 불리할 수 있었다.

SELECT NOW(), SLEEP(2), NOW();
SELECT SYSDATE(), SLEEP(2), SYSDATE();

날짜 포맷/연산

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');
SELECT STR_TO_DATE('2024-01-15', '%Y-%m-%d');

SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);

타임스탬프 변환

SELECT UNIX_TIMESTAMP('2024-01-15 12:00:00');
SELECT FROM_UNIXTIME(1705312800);

문자열 처리 / 결합

SELECT RPAD('Hello', 10, '*');
SELECT LPAD('Hello', 10, '*');
SELECT TRIM('  Hello  ');

SELECT CONCAT('Hello', ' ', 'World');
SELECT CONCAT_WS('-', '2024', '01', '15');

GROUP_CONCAT

SELECT dept_id, GROUP_CONCAT(emp_name ORDER BY emp_name SEPARATOR ', ')
FROM employees
GROUP BY dept_id;

SELECT dept_id, GROUP_CONCAT(DISTINCT emp_name ORDER BY emp_name)
FROM employees
GROUP BY dept_id;

GROUP_CONCAT 사용 시 제약

group_concat_max_len 제한(기본값 1024 등)에 의해 결과가 잘릴 수 있었으므로, 대규모 결과 결합에서는 길이 정책과 전송(Driver/JDBC) 동작을 함께 점검하는 편이 안전했다.

CASE / CAST / HEX / 해시

SELECT name,
       CASE
         WHEN salary >= 5000 THEN 'High'
         WHEN salary >= 3000 THEN 'Medium'
         ELSE 'Low'
       END AS salary_level
FROM employees;

SELECT CAST('123' AS SIGNED);
SELECT CONVERT('123', SIGNED);

SELECT HEX('MySQL');
SELECT UNHEX('4D7953514C');

SELECT SHA2('password', 256);

보안 관련 정리

비밀번호 저장에는 MD5, SHA1 같은 단순 해시 사용을 지양했다. 비밀번호 해싱은 bcrypt/argon2 같은 느린 KDF를 애플리케이션 계층에서 적용하는 것이 권장되는 방향이었다.

11.4 SELECT 최적화 포인트 #11-4

온라인 트랜잭션 처리 환경에서는 INSERT/UPDATE는 레코드 단위로 수행되는 경우가 많아 상대적으로 병목이 덜했지만, SELECT는 조인·정렬·그룹화가 결합되며 비용이 폭발하기 쉬웠다.

핵심 원칙

인덱스 칼럼을 변형 없이 사용해야 인덱스가 유지되었다.

실전 관찰

칼럼 타입 불일치·함수 감싸기·선행 와일드카드가 인덱스를 무력화했다.

튜닝 전략

먼저 줄이고(필터/limit), 나중에 조인하는 패턴이 효과적이었다.

지연된 조인(Delayed Join) 실무 예시 #11-4-delayed-join

조인 결과가 커진 뒤 GROUP BY/ORDER BY/LIMIT을 수행하면 정렬·그룹화 비용이 증가했다. 따라서 조인 전에 드라이빙 테이블 측에서 먼저 집계/정렬/제한을 수행한 후, 최소 결과만 조인하는 지연된 조인 패턴이 유효했다.

일반 조인(비효율 가능)

SELECT e.*
FROM salaries s, employees e
WHERE e.emp_no = s.emp_no
  AND s.emp_no BETWEEN 10001 AND 15000
GROUP BY s.emp_no
ORDER BY SUM(s.salary) DESC
LIMIT 10;

지연된 조인 적용(레코드 수를 먼저 축소)

SELECT e.*
FROM (
    SELECT s.emp_no
    FROM salaries s
    WHERE s.emp_no BETWEEN 10001 AND 15000
    GROUP BY s.emp_no
    ORDER BY SUM(s.salary) DESC
    LIMIT 10
) x, employees e
WHERE e.emp_no = x.emp_no;

해석

두 쿼리는 동일한 결과를 반환할 수 있으나, 두 번째 쿼리는 조인 전에 결과 후보를 10건으로 축소했으므로 조인 후 비용이 크게 감소하는 구조였다. 실행 계획에서는 Materialize 및 Limit의 적용 지점이 조인 이전으로 이동하는지 확인하는 방식이 실무에서 유효했다.

서브쿼리 위치별 특성과 전환 전략 #11-4-subquery

SELECT 절 스칼라 서브쿼리

• SELECT 절에는 1행 1열 결과(스칼라)만 안정적으로 사용했다.
• 반복 호출되는 스칼라 서브쿼리는 조인으로 전환하거나, MySQL 8.0+에서는 LATERAL로 전환하는 패턴이 효과적이었다.

-- 동일 서브쿼리가 반복되는 경우(비효율 가능)
SELECT e.emp_no, e.first_name,
  (SELECT s.salary FROM salaries s
   WHERE s.emp_no=e.emp_no
   ORDER BY s.from_date DESC LIMIT 1) AS salary,
  (SELECT s.from_date FROM salaries s
   WHERE s.emp_no=e.emp_no
   ORDER BY s.from_date DESC LIMIT 1) AS salary_from_date
FROM employees e
WHERE e.emp_no=499999;

-- LATERAL 전환(한 번만 읽도록 정리)
SELECT e.emp_no, e.first_name,
       s2.salary, s2.from_date
FROM employees e
INNER JOIN LATERAL (
  SELECT * FROM salaries s
  WHERE s.emp_no=e.emp_no
  ORDER BY s.from_date DESC
  LIMIT 1
) s2 ON s2.emp_no=e.emp_no
WHERE e.emp_no=499999;

FROM 절 서브쿼리(파생 테이블)

MySQL 5.7+에서는 상황에 따라 파생 테이블이 외부 쿼리로 병합(merge)될 수 있었으나, 집계/limit/distinct 등 조건이 있으면 물리화(materialize)가 발생할 수 있었다. 따라서 EXPLAIN으로 병합 여부를 확인하는 습관이 필요했다.

잠금을 사용하는 SELECT (NOWAIT, SKIP LOCKED) #11-4-locking

선착순·좌석예매·작업큐 같은 동시성 시나리오에서는 일반 SELECT만으로는 레이스 컨디션이 발생했다. InnoDB에서는 FOR UPDATE 및 FOR SHARE를 통해 명시적 잠금 읽기를 수행했고, MySQL 8.0+에서는 NOWAIT, SKIP LOCKED 옵션이 실무적으로 유용했다.

옵션	동작	적합한 상황
NOWAIT	잠금 충돌 시 즉시 실패(에러)했다.	좌석 예매처럼 “실패를 즉시 알려야 하는” 상황에 적합했다.
SKIP LOCKED	잠긴 레코드는 건너뛰고 다음 후보를 선택했다.	선착순 쿠폰처럼 “아무거나 하나”가 필요한 큐 처리에 적합했다.

쿠폰 발급(큐) 구현 예시 — SKIP LOCKED

START TRANSACTION;

-- 잠기지 않은 미발급 쿠폰 1개를 가져와서 잠금
SELECT *
FROM coupon
WHERE user_id = 0
ORDER BY coupon_id ASC
LIMIT 1
FOR UPDATE SKIP LOCKED;

-- 해당 쿠폰에 사용자 할당
UPDATE coupon
SET user_id = ?, issued_at = NOW()
WHERE coupon_id = ?;

COMMIT;

SKIP LOCKED 한계

• 미리 쿠폰(재고)을 생성해두는 설계가 전제되었다.
• 동일 사용자의 중복 요청 방지(예: UNIQUE 제약, 멱등키)가 별도로 필요했다.
• 잠긴 레코드를 건너뛰므로 입력이 같아도 결과가 달라질 수 있는 비확정적 성격을 가졌다.

참고1. 실무 쿼리 튜닝 사례 및 베스트 프랙티스

참고1.1 “느린 쿼리”를 빠르게 분해하는 절차

1. 병목 구간을 분리하기 위해 쿼리를 WHERE, JOIN, GROUP BY, ORDER BY, LIMIT 단위로 나눠 관찰했다.
2. EXPLAIN과 실제 실행 통계를 함께 보고, “예상(옵티마이저)”과 “실제(실행)”의 괴리를 확인했다.
3. 인덱스가 무너지는 패턴(형변환, 함수 감싸기, 선행 와일드카드)을 우선 제거했다.
4. 조인 비용이 큰 경우 결과를 먼저 줄이는 방식(지연 조인, No-offset)을 적용했다.

실무 체크리스트

• WHERE 조건이 인덱스 선행 칼럼과 얼마나 정렬되어 있는지 점검했다.
• JOIN 컬럼의 타입/콜레이션/부호(SIGNED/UNSIGNED)가 일치하는지 점검했다.
• ORDER BY가 인덱스로 해결되는지, Using filesort가 발생하는지 점검했다.
• GROUP BY가 인덱스 순서를 그대로 따르는지 점검했다.

참고1.2 페이징 튜닝: Offset 방식에서 No-offset 방식으로 전환

LIMIT offset, size는 offset이 커질수록 읽고 버리는 비용이 증가했다. 따라서 마지막으로 본 키를 기반으로 다음 페이지를 가져오는 No-offset 방식이 권장되는 패턴이었다.

-- 비효율 가능: offset이 커질수록 비용이 증가했다.
SELECT * FROM salaries
ORDER BY salary
LIMIT 2000000, 10;

-- No-offset: 마지막 키를 기준으로 다음 페이지를 읽는 방식이었다.
SELECT * FROM salaries
WHERE salary >= 154888
  AND NOT (salary = 154888 AND emp_no <= 109334)
ORDER BY salary
LIMIT 10;

참고1.3 지연된 조인(Delayed Join) 적용 기준

• 조인 전에 GROUP BY / ORDER BY / LIMIT로 후보군을 충분히 줄일 수 있을 때 효과적이었다.
• 조인 결과가 폭증하는 1:N 구조에서 특히 효율이 개선되었다.
• 결과 일관성(동일 결과 보장)을 위해 정렬 기준과 tie-break(보조 정렬 키)를 명확히 두는 편이 안전했다.

참고1.4 Race Condition과 DB 락 기반 해결(비관적 락)

“카운트 후 삽입” 패턴은 동시 요청에서 쉽게 레이스 컨디션을 유발했다. 이 경우, 이벤트 상태를 대표하는 행을 FOR UPDATE로 잠그고 단일 임계구역에서 검증과 발급을 처리하는 방식이 안전했다.

START TRANSACTION;

-- 이벤트 상태(예: 발급 수량)를 대표하는 행을 잠근다.
SELECT * FROM coupon_event WHERE id = 1 FOR UPDATE;

-- 발급 가능 여부 확인 후 발급 처리(INSERT/UPDATE) 수행

COMMIT;

트레이드오프

비관적 락은 정합성을 높였지만, 동시 요청이 많으면 대기가 길어져 타임아웃이 증가할 수 있었다. 따라서 큐 성격의 문제에서는 SKIP LOCKED 기반 분산 처리로 전환하는 전략이 유효했다.

참고2. 실행 효율성(Execution Efficiency) 고급 기법

참고2.1 Prepared Statement: 파싱/최적화 비용과 실행 안정성

Prepared Statement는 SQL 파싱 및 일부 최적화 비용을 줄이고, 바인딩을 통해 SQL Injection 위험을 낮추는 방식으로 사용되었다. 특히 동일한 형태의 SQL이 반복되는 API(로그인, 목록 조회 등)에서 효과가 컸다.

PREPARE stmt FROM 'SELECT * FROM employees WHERE emp_no = ?';
SET @id = 10001;
EXECUTE stmt USING @id;
DEALLOCATE PREPARE stmt;

실무 포인트

• DB 레벨 PREPARE는 세션 범위로 관리되므로, 보통은 애플리케이션 드라이버(JDBC 등)의 PreparedStatement를 표준으로 사용했다.
• 바인딩 사용은 “문자열 결합으로 SQL 만들기”를 제거하여 계획 안정성과 보안성을 개선했다.

참고2.2 쿼리 캐시(Query Cache) 대신 무엇을 봐야 했는가

MySQL 8.0에서는 전통적인 Query Cache 기능이 제거되었고, 동일 SQL을 캐싱해주는 “서버 내장 쿼리 캐시”에 기대는 방식은 일반적이지 않았다. 대신 실무에서는 다음 접근이 더 유효했다.

• 애플리케이션 캐시/Redis로 결과 캐싱을 수행했다(일관성 정책을 함께 설계했다).
• 인덱스·실행계획·조인 순서·정렬 제거 등 “근본 비용”을 줄이는 방향으로 튜닝했다.

참고2.3 실행 계획의 “재사용”과 계획 흔들림(Plan Instability) 대응

동일 SQL이라도 통계 변화, 데이터 분포 변화, 파라미터 값 변화에 따라 계획이 달라질 수 있었다. 따라서 쿼리 튜닝에서는 “한 번 빠르게 만든 계획”을 신뢰하기보다, 대표 파라미터 케이스들로 반복 검증하는 방식이 필요했다.

대표 점검 항목

• 조건의 선택도(카디널리티)가 극단적으로 달라지는 파라미터가 있는지 점검했다.
• 조인 순서가 바뀌며 드라이븐 테이블 인덱스 탐색 비용이 폭증하는지 점검했다.
• 정렬/그룹화가 임시 테이블 및 filesort를 유발하는지 점검했다.

참고2.4 커넥션/트랜잭션 비용 최소화

• 트랜잭션 범위를 최소화하여 락 보유 시간을 줄이는 것이 중요했다.
• 선착순·예매·워크큐 시나리오에서는 NOWAIT/SKIP LOCKED 같은 옵션을 통해 대기 전략을 설계했다.
• 대규모 트래픽에서는 “즉시 응답(인메모리)”과 “영속 저장(비동기)”를 분리하는 아키텍처가 고려되었다.

대규모 이벤트에서의 일반적 방향(개념)

요청(동기)  ──>  인메모리 처리(예: Redis 원자 연산)  ──>  즉시 응답
                                 │
                                 └──>  비동기 적재(예: 메시지 큐/스트림)  ──>  MySQL 영속 저장

정합성 합의

비동기 적재 구조는 “응답 시점”과 “영속 저장 시점” 사이에 지연이 존재할 수 있었다. 따라서 기획/운영 문구, 재처리 전략(멱등성), 장애 시 보상 처리가 함께 설계되어야 했다.