Real MySQL - Ch.11 쿼리 작성 및 최적화

Posted Dec 8, 2025

By Hyeon Jae Kim

28 min read

Real MySQL 8.0 : 11장 쿼리 작성 및 최적화

들어가며

MySQL에서 SQL을 작성할 때, 단순히 문법만 맞추면 되는 것이 아니다. MySQL 서버의 설정에 따라 대소문자 구분, 문자열 표기 방법, 날짜 형식 등이 달라지며, 이러한 시스템 변수들을 이해하지 못하면 예상치 못한 동작이나 오류를 마주하게 된다.

11.1 쿼리 작성과 연관된 시스템 변수

대소문자 구분, 문자열 표기 방법 등과 같은 SQL 작성 규칙은 MySQL 서버의 시스템 설정에 따라 달라진다.

11.1.1 SQL 모드

MySQL 서버의 sql_mode 시스템 변수는 SQL 문장 작성 규칙과 데이터 타입 변환 및 기본값 제어 등에 영향을 미친다. sql_mode는 여러 개의 값을 동시에 설정할 수 있으며, 구분자(,)로 연결해서 설정한다.

        
-- 현재 sql_mode 확인
SELECT @@sql_mode;

-- sql_mode 설정
SET sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';

주요 sql_mode 값들을 살펴보자.

STRICT_ALL_TABLES & STRICT_TRANS_TABLES 는 INSERT나 UPDATE 문장으로 데이터를 변경할 때 칼럼의 타입과 저장되는 값의 타입이 다를 경우 자동으로 타입 변경을 수행하는데, 이때 타입 변환이 적절히 수행될 수 없거나 칼럼에 저장될 값이 없거나 값의 길이가 칼럼의 최대 길이보다 큰 경우 에러를 발생시킬지 결정한다. 이를 엄격 모드(Strict Mode) 라고 한다.

STRICT_TRANS_TABLES : 트랜잭션을 지원하는 스토리지 엔진에만 엄격 모드 적용
STRICT_ALL_TABLES : 모든 스토리지 엔진에 엄격 모드 적용

ANSI_QUOTES 를 설정하면 홑따옴표(‘)만 문자열 값 표기로 사용할 수 있고, 쌍따옴표(“)는 칼럼명이나 테이블명과 같은 식별자를 표기하는 데만 사용할 수 있다.

ONLY_FULL_GROUP_BY 는 GROUP BY 절에 포함되지 않은 칼럼을 SELECT 절이나 HAVING 절에 사용하면 에러를 발생시킨다. MySQL 5.7부터 기본값으로 활성화되어 있다.

PIPE_AS_CONCAT 을 설정하면

를 문자열 연결 연산자(CONCAT)로 사용할 수 있다. 이 설정이 없으면

는 OR 연산자로 동작한다.

PAD_CHAR_TO_FULL_LENGTH 는 CHAR 타입의 칼럼값을 가져올 때 뒤쪽의 공백 문자를 제거하지 않고 그대로 가져온다.

NO_ZERO_DATE 는 ‘0000-00-00’과 같은 잘못된 날짜를 저장하지 못하게 한다.

NO_ZERO_IN_DATE 는 ‘2020-00-01’이나 ‘2020-01-00’처럼 월이나 일에 0이 포함된 잘못된 날짜를 저장하지 못하게 한다.

ANSI 모드는 MySQL 서버가 최대한 SQL 표준에 맞게 동작하도록 여러 sql_mode를 조합해둔 모드다.

11.1.2 영문 대소문자 구분

MySQL 서버는 설치된 운영체제에 따라 테이블명의 대소문자 구분이 달라진다. 이는 MySQL의 DB나 테이블이 디스크의 디렉터리나 파일로 매핑되기 때문이다.

윈도우는 대소문자를 구분하지 않지만 유닉스 계열에서는 대소문자를 구분한다. 이 문제를 해결하기 위해 lower_case_table_names 시스템 변수를 사용한다.

0 : DB와 테이블명에 대해 대소문자를 구분한다 (유닉스 기본값)
1 : 모두 소문자로만 저장되고 대소문자를 구분하지 않는다 (윈도우 기본값)
2 : 저장은 대소문자를 구분하지만, 쿼리에서는 대소문자를 구분하지 않는다 (macOS)

운영 환경과 개발 환경의 OS가 다를 수 있으므로, DB나 테이블을 생성할 때 일관되게 소문자로 통일 하는 것이 권장된다.

11.1.3 MySQL 예약어

MySQL에서 테이블이나 칼럼 이름을 예약어와 같은 키워드로 생성하면 SQL 문장에서 해당 칼럼이나 테이블을 사용할 때 항상 역따옴표(`)나 쌍따옴표로 감싸야 한다. 이는 유지보수를 어렵게 만들고 실수할 가능성이 높아지므로, 가능하면 예약어를 테이블이나 칼럼 이름으로 사용하지 않는 것이 좋다.

        
      
-- 예약어 'order'를 테이블명으로 사용하면 에러 발생
CREATE TABLE order (...);  -- 에러!

-- 역따옴표로 감싸면 사용 가능
CREATE TABLE `order` (...);

-- 이후 모든 쿼리에서도 역따옴표 필요
SELECT * FROM `order` WHERE ...;

테이블을 생성할 때는 역따옴표 없이 생성하고, 그때 에러가 발생하면 해당 단어가 예약어인지 확인하고 다른 이름을 선택하는 것이 바람직하다.

11.2 매뉴얼의 SQL 문법 표기를 읽는 방법

MySQL 공식 문서에서 SQL 문법은 특별한 표기법으로 작성되어 있다. 이 표기법을 이해하면 매뉴얼을 훨씬 효율적으로 활용할 수 있다.

        
      
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    { {VALUES | VALUE} (value_list) [, (value_list)] ... }
    [ON DUPLICATE KEY UPDATE assignment_list]

대괄호 [] 는 해당 키워드나 표현식이 선택 사항 임을 의미한다. 예를 들어 [INTO]는 INTO 키워드를 생략해도 된다는 뜻이다.

파이프 | 는 구분된 키워드 중에서 단 하나만 선택해서 사용할 수 있음을 의미한다. LOW_PRIORITY | DELAYED | HIGH_PRIORITY는 세 옵션 중 하나만 사용할 수 있다.

중괄호 {} 는 괄호 내의 아이템 중에서 반드시 하나를 선택 해야 하는 필수 사항임을 의미한다. {VALUES | VALUE}는 둘 중 하나를 반드시 사용해야 한다.

... 은 앞의 패턴이 반복될 수 있음을 나타낸다.

11.3 MySQL 연산자와 내장 함수

MySQL에서만 사용되는 연산자나 표기법이 있으며, ANSI 표준 형태가 아닌 것도 있다. 가독성을 높이고 다른 DBMS와의 호환성을 위해 ANSI 표준 형태의 연산자 를 사용하는 것이 권장된다.

11.3.1 리터럴 표기법 문자열

문자열

SQL 표준에서 문자열은 항상 홑따옴표(‘)를 사용 해서 표기한다. MySQL에서는 쌍따옴표(“)도 문자열 표기에 사용할 수 있지만, sql_mode에 ANSI_QUOTES가 설정되면 쌍따옴표는 식별자를 표기하는 데만 사용되므로 홑따옴표만 사용하는 습관 을 들이는 것이 좋다.

문자열 내에 홑따옴표를 포함해야 할 때는 홑따옴표를 두 번 연속해서 입력하면 된다.

        
      
SELECT 'It''s a book';  -- It's a book

숫자

숫자 값은 따옴표 없이 숫자만 입력하면 된다. 문자열 형태로 따옴표를 사용하더라도 비교 대상이 숫자이면 MySQL이 자동으로 숫자로 타입 변환한다.

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

문제는 두 번째 쿼리다. string_column이 문자열이고 비교 값이 숫자이면 MySQL은 문자열 칼럼을 숫자로 변환 해서 비교한다. 이 경우 string_column 칼럼의 모든 값을 숫자로 변환해야 하므로 인덱스를 사용하지 못한다. 따라서 칼럼의 타입에 맞는 상수값을 사용하는 것이 중요하다.

날짜

다른 DBMS에서는 날짜 타입을 비교하거나 INSERT할 때 문자열을 DATE 타입으로 변환하는 코드가 필요하지만, MySQL에서는 정해진 형식의 날짜 포맷 문자열을 사용하면 MySQL 서버가 자동으로 DATE나 DATETIME으로 변환 해준다.

        
SELECT * FROM dept_emp WHERE from_date = '2011-04-29';
SELECT * FROM dept_emp WHERE from_date = STR_TO_DATE('2011-04-29', '%Y-%m-%d');

첫 번째 쿼리처럼 간단하게 사용해도 MySQL이 자동으로 DATETIME 타입으로 변환한다.

불리언

BOOL이나 BOOLEAN이라는 타입이 있지만 사실 TINYINT 타입의 동의어다. TRUE 또는 FALSE 형태로 비교하거나 저장할 수 있지만, 실제로는 1 또는 0으로 저장된다.

        
CREATE TABLE tb_boolean (bool_value BOOLEAN);
INSERT INTO tb_boolean VALUES (TRUE), (FALSE);

SELECT * FROM tb_boolean WHERE bool_value = TRUE;   -- bool_value = 1과 동일
SELECT * FROM tb_boolean WHERE bool_value = FALSE;  -- bool_value = 0과 동일

불리언 타입을 꼭 사용해야 한다면 ENUM 타입 으로 관리하는 것이 명확하다.

11.3.2 MySQL 연산자

동등 비교 (=, <=>)

= 연산자는 동등 비교에 사용된다. MySQL에서는 동등 비교를 위해 <=> 연산자도 제공하는데, 이는 NULL-safe 비교 연산자 다.

        
SELECT 1 = 1, NULL = NULL, 1 = NULL;
-- 결과: 1, NULL, NULL

SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
-- 결과: 1, 1, 0

일반 = 연산자는 NULL과 비교하면 결과도 NULL이지만, <=> 연산자는 NULL끼리 비교해도 TRUE(1)를 반환한다.

부정 비교 (<>, !=)

<> 와 != 모두 “같지 않다” 비교를 수행한다. 둘 다 MySQL에서 사용 가능하지만, 가능하면 ANSI 표준인 <>를 사용 하는 것이 좋다.

NOT 연산자 (!)

TRUE 또는 FALSE의 연산 결과를 반대로 만드는 연산자다. “NOT”이라는 키워드와 “!” 연산자 모두 사용 가능하지만, 가독성을 위해 NOT 키워드를 사용하는 것이 권장된다.

AND(&&)와 OR(||) 연산자

일반적으로 DBMS에서는 불리언 표현식의 결합을 위해 AND와 OR을 사용한다. MySQL에서는 &&와

를 AND, OR로 사용할 수 있지만, **sql_mode에 PIPE_AS_CONCAT이 설정되면

는 문자열 연결 연산자로 동작** 한다. 따라서 오해를 피하기 위해 AND와 OR 키워드를 사용하는 것이 좋다.

AND와 OR이 같이 사용되면 AND가 OR보다 우선순위가 높다. 따라서 의도와 다른 결과가 나올 수 있으므로 괄호를 사용 해서 우선순위를 명확히 하는 것이 좋다.

        
-- AND가 먼저 처리됨
SELECT 1 OR 0 AND 0;  -- 결과: 1 (1 OR (0 AND 0) = 1 OR 0 = 1)

-- 의도를 명확히 하려면 괄호 사용
SELECT (1 OR 0) AND 0;  -- 결과: 0

나누기(/, DIV)와 나머지(%, MOD) 연산자

/ 는 나눈 결과를 실수로 반환하고, DIV 는 나눈 결과의 정수 부분만 반환한다.

        
SELECT 29 / 9;    -- 3.2222
SELECT 29 DIV 9;  -- 3

나머지 연산은 % 나 MOD 를 사용한다.

        
SELECT 29 MOD 9;  -- 2
SELECT 29 % 9;    -- 2

REGEXP 연산자

문자열 값이 어떤 패턴을 만족하는지 확인하는 연산자다. RLIKE도 동의어로 사용할 수 있다.

        
SELECT 'abc' REGEXP '^[x-z]';  -- 0 (FALSE)
SELECT 'xyz' REGEXP '^[x-z]';  -- 1 (TRUE)

REGEXP 연산자는 인덱스를 사용할 수 없으므로 가능하면 범위를 줄일 수 있는 다른 조건과 함께 사용하는 것이 좋다.

LIKE 연산자

LIKE에서 사용할 수 있는 와일드카드 문자는 % 와 _ 가 있다.

% : 0개 또는 1개 이상의 모든 문자에 일치
_ : 정확히 1개의 문자에 일치

        
      
SELECT 'abcdef' LIKE 'abc%';   -- 1
SELECT 'abcdef' LIKE '%def';   -- 1
SELECT 'abcdef' LIKE 'a_c%';   -- 1

LIKE 연산자는 와일드카드 문자의 위치에 따라 인덱스를 사용할 수 있는지 여부가 달라진다. 와일드카드가 검색어의 뒤쪽에만 있으면 인덱스 레인지 스캔 으로 처리할 수 있지만, 와일드카드가 앞쪽에 있으면 인덱스를 사용할 수 없다.

        
-- 인덱스 레인지 스캔 가능
SELECT * FROM employees WHERE first_name LIKE 'Christ%';

-- 인덱스 사용 불가 (풀 테이블 스캔)
SELECT * FROM employees WHERE first_name LIKE '%rist';

BETWEEN 연산자

BETWEEN은 크거나 같다 와 작거나 같다 의 두 조건을 하나로 합친 것이다. 즉, 시작값과 끝값이 모두 포함 된다.

        
      
SELECT * FROM dept_emp 
WHERE dept_no BETWEEN 'd003' AND 'd005';

-- 위 쿼리는 아래와 동일
SELECT * FROM dept_emp 
WHERE dept_no >= 'd003' AND dept_no <= 'd005';

IN 연산자

IN은 여러 개의 값에 대해 동등 비교를 수행하는 연산자다. 여러 개의 값이 비교되지만 범위 검색이 아니라 동등 비교 이므로 인덱스를 최적으로 사용할 수 있다.

        
SELECT * FROM dept_emp 
WHERE dept_no IN ('d001', 'd002', 'd003');

MySQL 8.0부터는 IN 절에 튜플을 사용해서 다중 칼럼 비교도 가능하다.

        
SELECT * FROM dept_emp 
WHERE (dept_no, emp_no) IN (('d001', 10017), ('d002', 10144));

11.3.3 MySQL 내장 함수

MySQL에서 기본으로 제공하는 내장 함수는 DBMS마다 다르므로 주의가 필요하다. 여기서는 자주 사용되고 중요한 함수들을 살펴본다.

NULL 값 비교 및 대체 (IFNULL, ISNULL)

IFNULL() 은 칼럼이나 표현식의 값이 NULL인지 비교하고, NULL이면 다른 값으로 대체한다.

        
SELECT IFNULL(NULL, 1);        -- 1
SELECT IFNULL('value', 1);     -- 'value'

ISNULL() 은 인자로 전달한 값이 NULL이면 TRUE(1), NULL이 아니면 FALSE(0)를 반환한다.

        
SELECT ISNULL(NULL);     -- 1
SELECT ISNULL(1);        -- 0

현재 시각 조회 (NOW, SYSDATE)

두 함수 모두 현재 시간을 반환하지만 작동 방식이 다르다.

NOW() 는 쿼리가 실행되는 시점에서 한 번만 값이 결정 되어 같은 쿼리 내에서는 항상 같은 값을 반환한다.

SYSDATE() 는 함수가 호출되는 시점 에 값이 결정되어 같은 쿼리 내에서도 다른 값을 반환할 수 있다.

        
SELECT NOW(), SLEEP(2), NOW();
-- 두 NOW()가 동일한 값

SELECT SYSDATE(), SLEEP(2), SYSDATE();
-- 두 SYSDATE()가 2초 차이

SYSDATE()는 두 가지 문제점이 있다.

복제(Replication)가 구축된 환경 에서 소스 서버와 레플리카 서버에서 쿼리 실행 시점이 달라 값이 다를 수 있다
SYSDATE()가 사용된 쿼리는 비확정적(Non-Deterministic) 함수 로 인식되어 인덱스를 효율적으로 사용하지 못한다

따라서 NOW() 함수를 사용하는 것이 권장 된다. 꼭 SYSDATE()를 사용해야 한다면 sysdate-is-now 시스템 변수를 활성화해서 SYSDATE()가 NOW()처럼 동작하게 할 수 있다.

날짜와 시간의 포맷 (DATE_FORMAT, STR_TO_DATE)

DATE_FORMAT() 은 날짜를 원하는 형식의 문자열로 변환한다.

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

STR_TO_DATE() 는 문자열을 날짜 타입으로 변환한다.

        
      
SELECT STR_TO_DATE('2024-01-15', '%Y-%m-%d');

주요 포맷 지시자는 다음과 같다.

지시자	설명
%Y	4자리 연도
%m	2자리 월
%d	2자리 일
%H	24시간 기준 시간
%i	분
%s	초

날짜와 시간의 연산 (DATE_ADD, DATE_SUB)

특정 날짜에서 기간을 더하거나 빼는 함수다.

        
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);      -- 1일 후
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);    -- 1개월 전

INTERVAL 뒤에는 SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR 등을 사용할 수 있다.

타임스탬프 연산 (UNIX_TIMESTAMP, FROM_UNIXTIME)

UNIX_TIMESTAMP() 는 ‘1970-01-01 00:00:00’부터 경과된 초를 반환하고, FROM_UNIXTIME() 은 그 반대로 변환한다.

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

문자열 처리 (RPAD, LPAD / RTRIM, LTRIM, TRIM)

RPAD(), LPAD() 는 문자열을 지정된 길이로 만들고 빈 공간을 특정 문자로 채운다.

        
SELECT LPAD('123', 5, '0');   -- '00123'
SELECT RPAD('123', 5, '0');   -- '12300'

RTRIM(), LTRIM(), TRIM() 은 문자열의 공백을 제거한다.

문자열 결합 (CONCAT, CONCAT_WS)

CONCAT() 은 여러 문자열을 하나로 연결한다.

        
      
SELECT CONCAT('Hello', ' ', 'World');  -- 'Hello World'

CONCAT_WS() 는 첫 번째 인자로 구분자를 지정한다.

        
      
SELECT CONCAT_WS(',', 'a', 'b', 'c');  -- 'a,b,c'

GROUP BY 문자열 집계 (GROUP_CONCAT)

GROUP BY와 함께 사용하며, 그룹 내의 값들을 하나의 문자열로 연결한다.

        
      
SELECT dept_no, GROUP_CONCAT(emp_no SEPARATOR ',')
FROM dept_emp
GROUP BY dept_no;

결과 문자열의 최대 길이는 group_concat_max_len 시스템 변수에 의해 제한된다(기본값 1024 바이트). 초과하면 결과가 잘린다.

값의 비교와 대체 (CASE WHEN … THEN … END)

프로그래밍 언어의 switch 구문과 같은 역할을 한다.

        
      
SELECT emp_no,
       CASE gender
           WHEN 'M' THEN '남성'
           WHEN 'F' THEN '여성'
           ELSE '미정'
       END AS gender_kor
FROM employees;

타입의 변환 (CAST, CONVERT)

SQL에서 명시적으로 타입을 변환할 때 사용한다.

        
      
SELECT CAST('1234' AS SIGNED INTEGER);
SELECT CONVERT('1234', SIGNED INTEGER);
SELECT CONVERT('ABC' USING utf8mb4);    -- 문자셋 변환

CAST() 와 CONVERT() 는 거의 동일하며, 인자 사용 규칙만 다르다.

이진값과 16진수 문자열 변환 (HEX, UNHEX)

HEX() 는 값을 16진수 문자열로, UNHEX() 는 16진수 문자열을 이진값으로 변환한다.

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

암호화 및 해시 함수 (MD5, SHA, SHA2)

MD5() 는 128비트(32자리 16진수) 해시를 생성하고, SHA() 는 160비트(40자리 16진수) 해시를 생성한다. 두 함수 모두 보안 취약점이 발견되어 SHA2() 사용이 권장된다.

SHA2() 는 224, 256, 384, 512비트 해시를 생성할 수 있다.

        
      
SELECT SHA2('password', 256);   -- SHA-256 (64자리 16진수)

처리 대기 (SLEEP)

쿼리 실행을 지정된 초만큼 대기시킨다. 디버깅이나 테스트 용도로 사용된다.

        
      
SELECT SLEEP(1);  -- 1초 대기

SELECT에서 SLEEP을 사용하면 각 레코드마다 대기 하므로 주의가 필요하다.

벤치마크 (BENCHMARK)

표현식을 반복 실행하여 성능을 측정한다.

        
      
SELECT BENCHMARK(1000000, MD5('test'));

BENCHMARK()는 순수 런타임 실행만 반복 하므로, 실제 쿼리 성능과는 다를 수 있다. 단순한 스칼라 함수 성능 측정에 적합하다.

IP 주소 변환 (INET_ATON, INET_NTOA)

IPv4 주소를 정수로 저장하면 저장 공간을 줄이고 검색 성능을 향상시킬 수 있다.

INET_ATON() 은 IP 주소를 정수로 변환하고(Address TO Number), INET_NTOA() 는 정수를 IP 주소로 변환한다(Number TO Address).

        
SELECT INET_ATON('192.168.0.1');   -- 3232235521
SELECT INET_NTOA(3232235521);     -- '192.168.0.1'

변환 원리는 a.b.c.d 주소를 a×256³ + b×256² + c×256 + d로 계산하는 것이다.

JSON 포맷 (JSON_PRETTY)

JSON 컬럼 값을 읽기 쉬운 형태로 포맷팅한다.

        
      
SELECT JSON_PRETTY(data) FROM json_table;

JSON 필드 추출 (JSON_EXTRACT)

JSON 데이터에서 특정 경로의 값을 추출한다.

        
SELECT JSON_EXTRACT('{"name": "홍길동", "age": 30}', '$.name');
-- "홍길동"

-- 축약 문법 (->)
SELECT json_column->'$.name' FROM users;

마치며

이번 글에서는 Real MySQL 8.0 2권 11장의 SELECT 절 이전까지의 내용을 정리했다. 핵심 원칙을 정리하면 다음과 같다.

sql_mode 는 SQL 문장의 작성 규칙을 결정하며, 운영 환경에서는 엄격 모드(STRICT)를 활성화하는 것이 권장된다
테이블명과 칼럼명은 소문자로 통일 하고, 예약어를 사용하지 않는 것 이 좋다
매뉴얼의 SQL 문법 표기에서 대괄호는 선택, 중괄호는 필수, 파이프는 택일 을 의미한다
문자열은 홑따옴표 로 표기하고, 칼럼 타입에 맞는 상수값을 사용 해야 인덱스를 효율적으로 사용할 수 있다
LIKE 연산자 에서 와일드카드가 검색어 앞에 있으면 인덱스를 사용할 수 없다
NOW()와 SYSDATE() 의 차이를 이해하고, 특별한 이유가 없다면 NOW()를 사용한다
해시 함수는 SHA2() 를 사용하고, IP 주소는 INET_ATON() 으로 정수 변환하여 저장하면 공간과 성능 모두 이점이 있다

References

Real MySQL 8.0 2권

데이터베이스

Database mysql

This post is licensed under CC BY 4.0 by the author.