Chapter11. 쿼리 작성 및 최적화

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

MySQL 서버의 시스템 설정에 따라 SQL 작성 규칙이 달라질 수 있다.

1-1. SQL 모드

테이블 및 인덱스 통계 정보

sql_mode 시스템 설정에 여러 개의 값이 동시에 설정

STRICT_ALL_TABLES & STRICT_TRANS_TABLES: insert, update 시 칼럼 타입과 저장되는 값의 타입이 다를 때 타입 변경
ANSI_QUOTES: 문자열 값에 홑따옴표만 사용
ONLY_FULL_GROUP_BY: GROUP BY 절이 사용된 문장의 SELECT 절에는 GROUP BY 절에 명시된 칼럼과 집계 함수만 사용
PIPE_AS_CONCAT: || 을 문자열 연결 연산자로 사용
PAD_CHAR_TO_FULL_LENGTH: CHAR 타입에서 유효 문자열 뒤의 공백 문자 제거
NO_BACKSLASH_ESCAPES: 역슬래시 문자를 이스케이프 문자로 사용
IGNORE_SPACE: 스토어드 프로시저나 함수명과 괄호 사이의 공백을 무시
REAL_AS_FLOAT: REAL 타입이 기본적으로 DOUBLE의 동의어로 사용되는데 FLOAT로 바꿀지
NO_ZERO_IN_DATE & NO_ZERO_DATE: DATE, DATETIME 타입 칼럼에 잘못된 날짜 저장
ANSI: MySQL 서버가 최대한 SQL 표준에 맞게 동작하도록
TRADITIONAL: 더 엄격하게 SQL 작동 제어 (STRICT_ALL_TABLES & STRICT_TRANS_TABLES)

영문 대소문자 구분

MYSQL 서버는 설치된 운영체제에 따라 테이블명의 대소문자를 구분한다 (윈도우는 구분 X, 유닉스는 구분) 운영체제와 관계없이 대소문자 구분 영향을 받지 않게 하려면 lower_case_table_names 시스템 변수 설정

MYSQL 예약어

데이터베이스나 테이블 칼럼의 이름을 예약어와 같은 키워드로 생성하면 해당 칼럼이나 테이블을 SQL에서 사용할 때 역따옴표나 쌍따옴표로 감싸야한다 예약어를 모두 기억하기 어려우므로 직접 MYSQL 테이블을 생성해서 예약어인지 알아볼 수 있다(에러로 알려줌)

1-2. 매뉴얼의 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)] ... }
    [AS row_alias[(col_alias [, col_alias] ...)]]
    [ON DUPLICATE KEY UPDATE assignment_list]

키워드: 대문자로 표기 (대소문자 구분 X)
토큰: 이탤릭체. 테이블명/칼럼명/표현식
[] 해당 키워드나 표현식이 선택사항일 때
| 앞과 뒤의 키워드나 표현식 중에서 하나만 선택해서 사용
{} 괄호 내 아이템 중 반드시 하나를 사용
...은 앞에 명시된 키워드나 표현식 조합이 반복될 수 있음을 의미

1-3. MySQL 연산자와 내장 함수

리터럴 표기법 문자열

문자열

홑따옴표와 쌍따옴표를 사용해 문자열 표기
문자열에 홑따옴표가 포함되어 있을 때는 홑따옴표 두 번 연속해서 입력하거나 쌍따옴표와 홑따옴표 혼합
식별자가 키워드와 충돌할 때는 역따옴표로 감싸서 사용

숫자

따옴표 없이 숫자 값을 입력
문자열 형태로 따옴표를 사용하더라도 비교 대상이 숫자 값이거나 숫자 타입의 칼럼이면 자동 변환. 숫자 타입을 문자열 타입보다 우선시하므로 문자열 값을 숫자 값으로 변환
```
SELECT * from test WHERE number_column = '10001';
SELECT * from test WHERE string_column = 10001;
```
첫번째에서는 상수값만 숫자로 변환하지만 두번째에서는 string_column의 모든 문자열을 숫자로 변환해서 비교하므로 인덱스가 있더라도 이용하지 못한다 => 숫자 값은 숫자 타입의 칼럼에만 저장하자

날짜

정해진 형태의 날짜 포맷을 자동으로 DATE나 DATETIME 값으로 변환

불리언

BOOL이나 BOOLEAN은 TINYINT 타입의 동의어
TRUE, FALSE 로 비교하지만 실제 값은 0,1 (TRUE는 1만을 의미한다)
모든 숫자값이 아닌 0,1만 매필되기 때문에 불리언 타입은 ENUM 타입으로 관리하는 것이 좋다

MySQL 연산자

동등 비교

= 과 <=>
<=> 은 Null 값도 하나의 값으로 생각하고 비교. (원래는 null 값에 대한 비교 결과는 null)

부정 비교

<> 과 !=

Not 연산자

NOT 과 !

AND, OR 연산자

AND 과 &&
OR 와 ||
||은 문자열 결합에도 사용될 수 있다
AND와 OR 연산자가 동시에 사용되면 AND 연산자가 우선순위 가짐

DIV, MOD 연산자

DIV 과 /
MOD 와 %

REGEXP 연산자

문자열 값이 패턴을 만족하는지 확인하는 연산자 (RLIKE)
REGEXP 연산자를 문자열 칼럼 비교에 사용할 때 인덱스 레인지 스캔을 사용할 수 없다. 가능하면 데이터 조회 범위를 줄일 수 있는 조건과 함께 사용해야 성능상 좋다

LIKE 연산자

%와 _ 와일드 카드를 사용해 문자열을 비교할 수 있다 (처음부터 끝까지 일치해야한다)
인덱스를 이용해 처리할 수 있다 (와일드 카드 문자가 검색어의 뒤 쪽에 있을 때만 인덱스 레인지 스캔. 아니라면 인덱스 풀 스캔)

BETWEEN 연산자

BETWEEN 범위의 모든 인덱스를 검색해야한다
비슷한 연산자인 IN이 있는데 처리 방법이 다르다. IN의 처리 방법은 동등 연산자와 비슷하다. BETWEEN은 선형으로 인덱스를 검색하는데, IN은 동등 비교를 여러번 수행하는 방식으로 검색한다.

IN 연산자

여러 개의 값에 대해 동등 비교 연산을 수행
상수를 사용하거나 서브쿼리를 사용할 수 있음
NOT IN은 인덱스 풀 스캔 사용

MySQL 내장 함수

Null 값 비교 및 대체

IFNULL() - NULL인지 비교하고, 맞으면 다른 값으로 대체
ISNULL() - NULL인지 비교

현재 시각 조회

NOW() - 하나의 SQL에서 항상 같은 값
SYSDATE() - 호출되는 시점에 따라 결과값 달라져서 레플리카 서버에서 안정적으로 복제되지 못하고, 인덱스를 효율적으로 사용하지 못한다는 문제가 있다 => NOW()를 사용하는 것이 좋다

날짜와 시간의 포맷

DATE_FORMAT() - DATETIME 타입의 값을 원하는 형태의 문자열로 변환할 때
STR_TO_DATE() - 문자열을 DATETIME 타입으로 변환

날짜와 시간의 연산

특정 날짜에서 시간을 더하거나 뺄 때 DATE_ADD(), DATE_SUB() 사용

타임스탬프 연산

UNIX_TIMESTAMP(): 1970/01/01 부터 경과된 초의 수를 반환
FROM_UNIXTIME(): 인자로 전달한 타임스탬프 값을 DATETIME으로 변환
TIMESTAMP는 4바이트 숫자 타입으로 저장

문자열 처리

RPAD(), LPAD(): 문자열 좌측 혹은 우측에 문자를 덧붙여 지정된 길이로 만듦
RTRIM(), LTRIM(), TRIM(): 공백문자 제거

문자열 결합

CONCAT(): 여러 개의 문자열을 하나의 문자열로 반환
CONCAT_WS(): 문자열 연결할 떄 구분자 넣어줌

GROUP BY 문자열 결합

GROUP_CONCAT(): GROUP BY 가 없는 SQL에서는 문자열 결합한 하나의 결과값만 만든다
지정된 칼럼의 값들을 연결하기 위해 제한적인 메모리 버퍼 공간을 사용한다

값의 비교와 대체

CASE WHEN은 SWITCH 구문 같은 역할. 동등 연산자로 비교하거나, 표현식으로 비교
CASE WHEN 절이 일치하는 경우에만 THEN 이하의 표현식이 실행된다

타입의 변환

SQL에 입력된 모든 입력값은 문자열처럼 취급된다. 명시적 타입 변환이 필요하면 CAST() 이용
CAST(): DATE, TIME, DATETIME, BINARY, CHAR, DECIMAL, SIGNED INTEGER, UNSIGNED INTEGER 변환 가능
문자열, 숫자, 날짜는 자동으로 변환하는 경우가 많다
CONVERT(): CAST()와 같이 타입을 변환하는 용도와 문자열의 문자 집합을 변환하는 용도로 사

이진값과 16진수 문자열 변환

HEX(): 이진값을 16진수 문자열로 변환
UNHEX(): 16진수 문자열을 이진값으로 변환

암호화 및 해시 함수

MD5(), SHA(), SHA2(): 비대칭형 암호화. 비밀번호같이 암호화 필요한 정보를 인코딩할 때 사용.
긴 데이터의 크기를 줄여서 인덱싱(해시) 하는 용도로도 사용
8.0 부터는 함수 기반의 인덱스를 생성하면 별도 칼럼을 추가하지 않아도 된다

SLEEP

SLEEP(): 개발이나 디버깅 용도로 잠깐 대기하거나, 쿼리 실행을 오래 유지하고자 할 때 사용
레코드의 건수만큼 호출

벤치마크

BENCHMARK(): 디버깅이나 간단한 함수 성능 테스트용으로 유용. 반복해서 수행할 횟수, 반복해서 실행할 표현식 입력받음
표현식을 SQL 클라이언트와 같은 도구로 여러번 실행하면 매번 쿼리 파싱이나 최적화, 테이블 잠금 비용 등이 소요되지만 벤치마크로 실행하면 단 1번의 네트워크, 쿼리 파싱 및 최적화 비용 소요
두 개의 동일 기능을 상대적으로 비교 분석하는 용도로 사용

IP 주소 변환

INET_ATON(), INET_NTOA(): IPv4 주소를 문자열이 아닌 부호 없는 정수 타입에 저장할 수 있게 제공
INET6_ATON(), INET6_NTOA(): IPv6 주소를 변환

JSON 포맷

JSON_PRETTY()로 JSON 칼럼의 값을 읽기 쉽게 변환

JSON 필드 크기

JSON_STORAGE_SIZE(): BSON(binary JSON) 포맷일 때 저장 공간의 크기 계산

JSON 필드 추출

JSON_EXTRACT(): JSON에서 특정 필드의 값을 가져옴
JSON_UNQUOTE(): 따옴표 없이 값만 가져옴
->, ->> 연산자를 대신 사용 가능

JSON 오브젝트 포함 여부 확인

JSON_CONTAINS(): JSON 도큐먼트 또는 지정된 JSON 경로에 JSON 필드를 가지고 있는지 확인하는 함수

Effective-Java-Camp / real-my-sql

11장. 쿼리 작성 및 최적화 #7