11장. 쿼리 작성 및 최적화

[im-gnar]

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 필드를 가지고 있는지 확인하는 함수

[im-gnar]

https://puzzle-cowbell-508.notion.site/11-1-d8f7a9f351d641cbac55e08da998881d

[im-gnar]

https://puzzle-cowbell-508.notion.site/11-4-select-752cfa71ea6f4303ab49b44cc2840aa0