11. 쿼리 작성 및 최적화

Book/Real MySQL 8.0 下

11. 쿼리 작성 및 최적화

kinggora 2022. 9. 29. 16:30

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

DDL(Data Definition Language): 데이터베이스나 테이블의 구조를 변경하기 위한 문장

DML(Data Manipulation Language): 테이블의 데이터를 조작(읽고 쓰기)하기 위한 문장

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

11.1.1 SQL 모드 (sql_mode)

sql_mode: 복수의 설정 값 가능

주의: MySQL 서버의 sql_mode 시스템 변수에 설정된 값들은 SQL 문장 작성 규칙뿐만 아니라, 서버 내부적으로 자동 실행되는 데이터 타입 변환 및 기본값 제어 등과 관련된 옵션도 가지고 있다. 그래서 일단 MySQL 서버에 사용자 테이블을 생성하고 데이터를 저장하기 시작했다면 가능한 한 sql_mode 시스템 변수의 내용을 변경하지 않는 것이 좋다. 그리고 하나의 복제 그룹에 속한 모든 MySQL 서버들은 동일한 sql_mode 시스템 변수를 유지하는 것이 좋다.

MySQL 8.0 서버의 sql_mode 기본값

ONLY_FULL_GROUP_BY
STRICT_TRANS_TABLES
NO_ZERO_IN_DATE
NO_ZERO_DATE
ERROR_FOR_DIVISION_BY_ZERO
NO_ENGINE_SUBSTITUTION

STRICT_ALL_TABLES & STRICT_TRANS_TABLES

INSERT나 UPDATE 문장으로 데이터를 변경하는 경우, 칼럼의 타입과 저장되는 값의 타입이 다를 때 자동으로 타입 변경을 수행한다. 이때 타입이 적절히 변환되기 어렵거나, 칼럼에 저장될 값이 없거나, 값의 길이가 칼럼의 최대 길이보다 큰 경우 INSERT나 UPDATE 문장을 계속 실행할지, 아니면 에러를 발생시킬지 결정한다. 실행을 멈추고 에러를 발생시키는 설정이 Strict Mode 이다.

STRICT_TRANS_TABLES: InnoDB 같은 트랜잭션을 지원하는 스토리지 엔진에만 Strict Mode 적용
STRICT_ALL_TABLES: 트랜잭션 지원 여부와 무관하게 모든 스토리지 엔진에 Strict Mode 적용

ANSI_QUOTES

MySQL에서는 문자열 값(리터럴)을 표현하기 위해 홑따옴표와 쌍따옴표를 동시에 사용할 수 있다. 하지만 오라클에서는 홑따옴표를 문자열 값을 표기하는 데 사용하고, 쌍따옴표는 칼럼명이나 테이블명과 같은 식별자(Identifier)를 구분하는 용도로만 사용한다. 이같은 상황이 혼란을 유발하고 가독성 또한 떨어뜨릴 수 있기에 ANSI_QUOTES를 설정하면 홑따옴표만 문자열 값 표기로 사용할 수 있고, 쌍따옴표는 식별자를 표기하는 데만 사용할 수 있다.

ONLY_FULL_GROUP_BY

MySQL의 쿼리에서는 GROUP BY 절에 포함되지 않은 칼럼이라도 집합 함수의 사용 없이 그대로 SELECT 절이나 HAVING 절에 사용할 수 있다. 이러한 부분도 SQL 표준이나 다른 DBMS와는 다른 동작 방식인데, ONLY_FULL_GROUP_BY 를 설정하면 GROUP BY 절이 사용된 문장의 SELECT 절에는 GROUP BY 절에 명시된 칼럼과 집계 함수(COUNT 또는 SUM 과 같은 그룹 함수)만 사용할 수 있다. SELECT 절에 집계 함수가 사용되는 경우 GROUP BY 절에 명시되지 않은 칼럼도 집계 함수의 인자로 사용할 수 있다. 5.7 버전까지는 ONLY_FULL_GROUP_BY 규칙을 준수하지 않는 쿼리가 많이 사용되었기 때문에 5.7에서 8.0 업그레이드 시 해당 옵션을 비활성해야 할 수도 있다.

PIPE_AS_CONCAT

MySQL에서 "||"는 OR 연산자와 같은 의미로 사용되나, PIPE_AS_CONCAT 값을 설정하면 오라클과 같이 문자열 연결 연산자(CONCAT)로 사용할 수 있다.

PAD_CHAR_TO_FULL_LENGTH

MySQL 에서는 CHAR 타입이라고 해도 VARCHAR 와 같이 유효 문자열 뒤의 공백 문자는 제거되어 반환된다. 이는 주로 애플리케이션 개발자에게 민감한 부분인데, 불필요한 공백 문자를 제거하는 편리한 기능이기도 하다. PAD_CHAR_TO_FULL_LENGTH 를 추가하면 CHAR 타입의 칼럼값의 뒤쪽 공백이 제거되지 않고 반환된다.

NO_BACKSLASH_ESCAPES

MySQL에서도 일반적인 프로그래밍 언어처럼 역슬래시 문자를 이스케이프 문자로 사용할 수 있다. sql_mode 시스템 변수에 NO_BACKSLASH_ESCAPES 를 추가하면 역슬래시 문자를 백슬래시 용도로 사용하지 못한다. 즉, 다른 문자와 동일 취급된다.

IGNORE_SPACE

MySQL에서 스토어드 프로시저나 함수의 이름 뒤에 공백이 있으면 "스토어드 프로시저나 함수가 없습니다"라는 에러가 출력될 수 있다. 스토어드 프로시저나 함수명과 괄호 사이에 있는 공백까지도 스토어드 프로시저나 함수의 이름으로 간주한다. 이 방식이 기본 모드이므로 몇 번이고 함수가 있는지 확인하기도 한다. IGNORE_SPACE 옵션이 활성화되면 프로시저나 함수명과 괄호 사이의 공백은 무시한다. 이는 MySQL 서버의 내장 함수에만 적용되며, 내장 함수는 모두 예약어로 간주되어 테이블이나 칼럼의 이름으로 사용될 수 없다. 물론 역따옴표(`, backtick)를 이용하면 예약어를 테이블이나 칼럼의 이름으로 사용할 수 있다.

REAL_AS_FLOAT

MySQL 서버에서 부동 소수점 타입은 FLOAT과 DOUBLE 타입이 지원되는데, REAL 타입은 DOUBLE 타입의 동의어로 사용된다. 하지만 REAL_AS_FLOAT 모드가 활성화되면 REAL타입이 FLOAT 타입의 동의어로 바뀐다.

NO_ZERO_IN_DATE & NO_ZERO_DATE

이 두 옵션이 활성화되면 MySQL 서버는 DATE 또는 DATETIME 타입의 칼럼에는 "2020-00-00" 이나 "0000-00-00" 과 같은 실제 존재하지 않는 날짜를 저장하는 것이 불가능해진다.

ANSI

이 값은 여러 가지 옵션을 조합해서 최대한 SQL 표준에 맞게 동작하게 만들어준다.

REAL_AS_FLOAT
PIPE_AS_CONCAT
ANSI_QUOTES
IGNORE_SPACE
ONLY_FULL_GROUP_BY

TRADITIONAL

STRICT_ALL_TABLES, STRICT_TRANS_TABLES 와 비슷하지만 조금 더 엄격한 방법으로 SQL의 작동을 제어한다. TRADITIONAL 모드가 활성화되면 TRADITIONAL 모드가 아닐 때 경고로 처리되던 상황이 모두 에러로 바뀌고 SQL 문장은 실패한다.

STRICT_ALL_TABLES
STRICT_TRANS_TABLES
NO_ZERO_IN_DATE
NO_ZERO_DATE
ERROR_FOR_DIVISION_BY_ZERO
NO_ENGINE_SUBSTITUTION

11.1.2 영문 대소문자 구분

MySQL 서버는 설치된 운영체제에 따라 테이블명의 대소문자를 구분한다. 이는 MySQL의 DB나 테이블이 디스크의 디렉터리나 파일로 매핑되기 때문이다. 즉, 윈도우에 설치된 MySQL에서는 대소문자를 구분하지 않지만 유닉스 계열의 운영체제에서는 대소문자를 구분한다. DB나 테이블명의 대소문자 구분은 가끔 윈도우에서 운영되던 MySQL 데이터를 리눅스로 가져오거나 그 반대의 경우 문제가 되기도 한다.

lower_case_table_names (설정 파일의 시스템 변수)

0: 기본값. 대소문자 구분 O

1: 모두 소문자로만 저장됨. 대소문자 구분 X

2(윈도우, macOS): 저장은 대소문자 구분 O, 쿼리는 대소문자 구분 X

설정 자체를 떠나 가능하면 초기 DB나 테이블을 생성할 때, 대문자 또는 소문자로 통일해서 사용하자.

11.1.3 MySQL 예약어

DB나 테이블, 칼럼의 이름을 예약어와 같은 키워드로 생성하면, 해당 칼럼이나 테이블을 사용할 때 이름을 명시하기 위해 항상 역따옴표(`)나 쌍따옴표로 감싸야 한다. 이는 찾아내기 어려운 버그의 원인이 될 수 있다. 예약어별로 문제가 되지 않는 키워드들도 있다. 이런 예약어를 구분해서 기억하기란 쉽지 않다. 예약어인지 아닌지 알 수 있는 가장 좋은 방법은 직접 MySQL에서 테이블을 생성해보는 것이다. 역따옴표(`)나 쌍따옴표로 감싸지 않고 생성했을 때 테이블 생성이 실패하면 해당 예약어는 역따옴표로 감싸지 않고는 사용할 수 없음을 의미한다.

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

대문자 단어: 키워드. 키워드는 대소문자 구분 없이 사용 가능
이탤릭체: 사용자가 선택해서 작성하는 토큰. (ex. 식별자(테이블명, 칼럼명), 표현식...) 이 항목이 SQL 키워드나 식별자가 아니라면 매뉴얼에서 "value"나 "value_list" 를 통해 상세한 문법을 설명
대괄호([]): 해당 키워드나 표현식 자체가 선택 사항. 있든 없는 문법적 오류를 일으키지 않음
파이프(|): 앞과 뒤의 키워드나 표현식 중 하나만 선택해서 사용 가능 (or)
중괄호({}): 괄호 내의 아이템 중 반드시 하나를 사용해야 함
... : 앞에 명시된 키워드나 표현식의 조합이 반복될 수 있음

11.3 MySQL 연산자와 내장 함수

11.3.1 리터럴 표기법 문자열

11.3.1.1 문자열

SQL 표준: 홑따옴표(') 사용

MySQL: 홑따옴표('), 쌍따옴표(") 사용

문자로써 홑따옴표 표현

SQL 표준: 홑따옴표 두개 ('')

MySQL: 홑따옴표 두개 (''), 쌍따옴표와 혼합(" ' ")

문자로써 쌍따옴표 표현

SQL 표준: 쌍따옴표는 원래 문자

MySQL: 쌍따옴표 두개 (""), 홑따옴표와 혼합(' " ')

첫 번째, 두 번째: SQL 표준과 MySQL 모두 지원 (d'001, d"001)

세 번째, 네 번째: MySQL에서만 지원 (d'001, d"001)

SQL 예약어(키워드) - 식별자 충돌 방지

오라클, PostgreSQL: 쌍따옴표(")나 대괄호([]) 사용

MySQL: 역따옴표(`) 사용

*sql_mode = [ANSI_QUOTES] 설정시 쌍따옴표는 문자열 리터럴 표기에 사용 불가. 쌍따옴표는 테이블명이나 칼럼명 등 식별자를 표기하는 데만 사용 가능

11.3.1.2 숫자

상수로써 숫자 값: 따옴표 없이 숫자 값 입력

숫자를 문자열로 사용해도 비교 대상이 숫자 값이거나 숫자 타입의 칼럼이면 문자열을 숫자로 자동 변환

숫자와 문자열 비교

MySQL은 숫자 타입과 문자열 타입 간 비교에서 숫자 타입을 우선시한다.

따라서 문자열 값을 숫자 값으로 변환한 후 비교를 수행한다.

첫 번째 쿼리: '10001' 만 숫자로 변환 후, 숫자 칼럼(number_column)과 비교 수행 -> 성능 문제X

두 번째 쿼리: 문자열 칼럼(string_column)을 숫자로 변환 후, 숫자 값과 비교 수행 -> 성능 문제O

string_column에 인덱스가 있더라도 이용 못하지 못하고, 숫자로 변환할 수 없는 문자가 있을 경우 쿼리 자체가 실패

이러한 문제를 제거하려면 숫자 값은 숫자 타입의 칼럼에만 저장할 것

11.3.1.3 날짜

다른 DBMS 에서는 날짜 타입을 비교하거나 INSERT 하려면 문자열을 DATE 타입으로 변환하는 코드가 필요하지만

MySQL의 경우, 문자열을 정해진 형태의 날짜 포맷으로 표기하면 자동으로 DATE나 DATETIME 값으로 변환해준다.

날짜와 문자열 비교

첫 번째 쿼리: 문자열을 DATE 타입으로 자동 변환 후, 날짜 칼럼(from_date)과 비교 수행

두 번째 쿼리: SQL 함수를 사용하여 문자열을 DATE 타입으로 변환 후, 날짜 칼럼과 비교 수행

두 쿼리의 차이점은 없다. 날짜 칼럼(from_date)을 문자열로 변환해서 비교하지 않기 때문에 해당 칼럼으로 생성된 인덱스를 사용하는 데 문제가 없다.

11.3.1.4 불리언

BOOL, BOOLEAN: TINYINT의 동의어

MySQL에서는 TRUE 또는 FALSE 형태로 비교하거나 값을 저장할 수는 있지만, 이는 숫자 타입의 칼럼에도 모두 적용되는 비교 방법이다. MySQL은 불리언 값을 정수로 매핑하여 사용하기 때문에, 실제로 값을 조회하면 0 또는 1 값이 조회된다.

TRUE -> 1, FALSE -> 0

즉, 숫자간 비교와 다르지 않다. "참, 거짓" 의 의미가 아님.

IN ( 0, 1 ) 과 같다

모든 숫자 값이 TRUE 나 FALSE 라는 두 개의 불리언 값으로 매핑되지 않기 때문에 버그로 연결될 가능성이 높다. 불리언 타입을 꼭 사용하고 싶다면 ENUM 타입으로 관리하자.

11.3.2 MySQL 연산자

11.3.2.1 동등(Equal) 비교 (=, <=>)

MySQL은 동등 비교를 위해 "=" 뿐만 아니라 "<=>" 를 제공한다.

<=> (NULL-Safe 비교 연산자) : 동등 비교 + NULL 값 비교 수행

NULL 은 "IS NULL" 연산자 이외에는 비교할 방법이 없다.

그래서 한쪽이 NULL이면 비교 결과도 NULL 로 반환

NULL을 하나의 값으로 인식하고 비교한다. NULL은 NULL과 같다.

11.3.2.2 부정(Not-Equal) 비교 (<>, !=)

어느 쪽을 사용해도 특별히 문제가 되지 않지만 가독성을 위해 하나로 통일해서 사용하자.

11.3.2.3 NOT 연산자(!)

TRUE 또는 FALSE 연산의 결과를 반대로(부정) 만드는 연산자로 "NOT"과 "!" 을 사용한다.

대상으로 불리언, 숫자, 표현식이 올 수 있으나 부정의 결과를 예측하기 어려울 땐 사용하지 말자.

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

불리언 표현식 결과를 결합하는 데 사용

(표현식)AND(표현식)OR(표현식)

(표현식)&&(표현식)||(표현식)

*오라클에서는 "||" 를 불리언 표현식의 결합 연산자가 아니라 문자열을 결합하는 연산자(CONCAT)로 사용한다.

MySQL에서도 같은 목적으로 활용하고 싶다면 sql_mode=[PIPE_AS_CONCAT]

이 경우 불리언 표현식 결합에 "&&" 은 사용 가능하나, "||" 는 사용 불가

*SQL 가독성을 높이기 위해 다른 용도로 사용될 수 있는 "&&", "||" 대신 AND와 OR를 사용하자.

{"originWidth":335,"originHeight":187,"style":"alignLeft","caption":"PIPES_AS_CONCAT 과

주의. AND와 OR 연산자가 동시에 사용된 경우 우선순위

어느 연산자가 먼저 오든 AND 연산자를 먼저 처리한다.

TRUE OR FALSE AND FALSE

-> TRUE OR (FALSE AND FALSE)

-> TRUE OR TRUE

-> TRUE(1)

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

나누기 연산: /
나눈 몫(정수): DIV
나머지: &, MOD, MOD()

29 / 9 = 3.2222

29 DIV 9 = 3

MOD(29,9) = 2

29 MOD 9 = 2

29 % 9 = 2

11.3.2.6 REGEXP 연산자

문자열 값이 어떤 패턴을 만족하는지 확인하는 연산자

(비교 대상 문자열 또는 문자열 칼럼) REGEXP (검증하고자 하는 정규 표현식)

정규 표현식은 자바 또는 자바스크립트와 같은 언어에서 많이 사용된다. REGEXP 의 정규 표현식은 POSIX 표준으로 구현되어 있어서 POSIX 정규 표현식에서 사용되는 패턴 키워드를 그대로 사용할 수 있다.

=정규 표현식 예=

[0-9]* : 0~9 까지의 숫자로만 구성된 문자열

[a-z]* : a~z 까지의 소문자 알파벳으로만 구성된 문자열

[a-zA-Z]* : a~z, A~Z 까지 대소문자 알파벳으로만 구성된 문자열

[a-zA-Z0-9]* : 영문 대소문자와 숫자만으로 구성된 문자열

^Tear : Tear 문자열로 시작하는 문자열

Tear$ : Tear 문자열로 끝나는 문자열

^Tear$ : Tear 와 완전히 같은 문자열. 이 경우 T 로 시작하고 연속으로 ear 이 나타나고, 뒤에 아무 문자가 없어야 한다.

REGEXP 연산자를 문자열 칼럼 비교에 사용할 때 REGEXP 조건의 비교는 인덱스 레인지 스캔을 사용할 수 없다. 따라서 WHERE 조건절에 REGEXP 를 사용한 조건을 단독으로 사용하는 것은 성능상 좋지 않다. 가능하다면 데이터 조회 범위를 줄일 수 있는 조건과 함께 REGEXP 연산자를 사용하기를 권장한다.

11.3.2.7 LIKE 연산자

REGEXP 보다 단순한 문자열 패턴 비교 연산자이지만 DBMS에서는 LIKE 를 더 많이 사용한다.

REGEXP 는 인덱스를 사용하지 못하지만 LIKE 는 한정적으로 인덱스를 사용할 수 있다.

(비교 대상 문자열 또는 문자열 칼럼) LIKE (상수 문자열)

LIKE에서 사용할 수 있는 와일드카드 문자는 '%' , '_' 뿐이다. REGEXP 는 비교 대상 문자열의 일부에 대해서만 일치해도 TRUE 를 반환하나, LIKE 는 항상 비교 대상 문자열의 처음부터 끝까지 일치하는 경우에만 TRUE 를 반환한다.

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

_ : 정확히 1개의 문자에 일치

=와일드카드 문자 사용 예시=

a%: a로 시작. a 뒤로 0 또는 1개 이상의 문자

%a: a로 끝. a 앞으로 0 또는 1개 이상의 문자

%or%: or을 포함하는 문자열

_r%: 2번째 글자가 r. 뒤로 0 또는 1개 이상의 문자

a__%: a로 시작. 최소 3글자 이상의 문자열

a%o: a로 시작, o로 끝. 최소 2글자 이상의 문자열

*와일드카드 문자 (%, _) 자체를 비교하기: ESCAPE 조건 추가

인덱스 레인지 스캔

와일드카드 문자 ( % , _ ) 가 검색어의 뒤쪽에 있다면 인덱스 레인지 스캔으로 사용할 수 있지만, 검색어의 앞쪽에 있다면 인덱스 레인지 스캔을 사용할 수 없다.

'%rist' 인덱스 레인지 스캔 사용 불가 -> 인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔

11.3.2.8 BETWEEN 연산자

"크거나 같다" 와 "작거나 같다" 라는 두 개의 연산자를 하나로 합친 연산자

ex) BETWEEN 10 AND 20

BETWEEN 은 다른 비교 조건과 결합하여 하나의 인덱스를 사용할 때 주의할 점이 있다.

첫 번째 쿼리: dept_no 칼럼의 동등 비교 조건과 emp_no 칼럼의 동등 비교 조건은 모두 인덱스를 이용해 범위를 줄여주는 방법으로 사용할 수 있다.

두 번째 쿼리: BETWEEN 연산자는 'd003' 보다 크거나 같고 'd005' 보다 작거나 같은 모든 인덱스의 범위를 검색해야 한다. 결국 AND 로 묶인 emp_no=10001 조건은 비교 범위를 줄이는 역할을 하지 못한다.

*BETWEEN vs IN

BETWEEN: 크다(>)와 작다(<) 비교를 하나로 묶음 (범위 비교)

IN: 여러 개의 동등 비교(=)를 하나로 묶음

*USE INDEX(PRIMARY) 힌트가 없다면 MySQL 서버는 (emp_no, from_date) 컬럼 조합의 인덱스를 사용할 것이다.

하지만 BETWEEN 연산자와 IN 연산자의 비교 설명을 위한 예제는 PRIMARY 인덱스를 사용해야 한다.

즉, 이 예제는 dept_emp 테이블에 (emp_no, from_date) 조합의 인덱스가 없다는 가정에서 만들어진 예제이다.

둘 다 인덱스 레인지 스캔을 하지만, 실제로 읽어들이는 레코드 건수에 많은 차이가 있다.

첫 번째 쿼리: 부서 번호가 'd003' 인 레코드 부터 'd005'인 레코드의 전체 범위를 다 비교해야 한다.두 번째 쿼리: 부서 번호와 사원 번호가 ('d003', 10001), ('d004', 10001), ('d005', 10001) 조합인 레코드만 비교하면 된다. *예전 버전의 MySQL 에서는 BETWEEN 연산자를 IN 연산자로 변경하기 위해서는 우선 dept_no 컬럼의 값이 'd003' 와 'd005' 사이의 모든 부서 코드 값을 가져와 "dept_no IN ('d003', 'd004','d005')" 조건을 만들어야 했다.그러나 8.0 부터는 다음과 같이 "IN (subquery)" 형태로 작성하면 옵티마이저가 세미 조인 최적화를 이용해 더 빠른 쿼리로 변환하여 실행한다.

참고: dept_no 컬럼은 PRIMARY KEY 이기 때문에 항상 유니크한 결과를 반환한다. 따라서 다음과 같이 단순한 조인 쿼리로 변경해서 실행할 수도 있다.

SELECT * FROM departments d

INNER JOIN dept_emp de USE INDEX(PRIMARY) ON de.dept_no=d.dept_no AND de.emp_no=10001

WHERE d.dept_no BETWEEN 'd003' AND 'd005';

실제로 8.0 버전의 MySQL 옵티마이저는 "IN (subquery)" 형태로 작성해도 JOIN 쿼리로 재작성하여 쿼리를 최적화시킨다.

11.3.2.9 IN 연산자

여러 개의 값에 대해 동등 비교 연산을 수행하는 연산자. 일반적으로 빠르게 처리된다.

상수가 사용된 경우: IN (?, ?, ?)
서브쿼리가 사용된 경우: IN (SELECT .. FROM ..)

IN 연산자에 상수가 사용된 경우는 동등 비교와 동일하게 작동하기 때문에 매우 빠르게 쿼리가 처리된다.

8.0 이전 버전에는 이 자리에 튜플을 사용할 경우 항상 풀 테이블 스캔을 했었다. -> 성능 문제 발생

8.0 버전부터는 IN 절에 튜플을 사용해도 인덱스를 최적으로 사용할 수 있게 개선되었다.