11장. 쿼리 작성 및 최적화

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

11.1.1 SQL 모드

• STRICT_ALL_TABLES: 트랜잭션 지원 여부와 무관하게 모든 스토리지 엔진에 대해 엄격한 모드
• STRICT_TRANS_TABLES: InnoDB 같은 트랜잭션 지원 스토리지 엔진에만 엄격한 모드

엄격한 모드
MySQL 에서는 INSERT 나 UPDATE 문장으로 데이터를 변경하는 경우, 컬럼의 타입과 저장되는 값의 타입이 다를 때 자동으로 타입 변경을 수행한다. 이때 적절한 타입 변환이 어렵거나 컬럼에 저장될 값이 없거나, 컬럼의 최대 길이를 초과하는 경우 INSERT나 UPDATE 를 계속 실행할지 아니면 에러를 발생시킬지 결정한다.
엄격한 모드일 경우 에러를 발생시킨다.

 

• ANSI_QUOTES: MySQL 에서 홀따옴표로 문자열 표기, 쌍따옴표는 컬럼명/테이블명 과 같은 식별자 구분용으로 사용
• ONLY_FULL_GROUP_BY: GROUP BY 에 사용된 컬럼 or 집계함수만 SELECT 에 사용 가능
• PIPES_AS_CONCAT: || 를 문자열 연결 연산자로 사용
• PAD_CHAR_TO_FULL_LENGTH: CHAR 타입의 컬럼값 조회 시 뒤쪽 공백이 제거되지 않음
• NO_BACKSLASH_EXCAPES: 역슬래시를 문자의 이스케이프 용도로 사용 불가, 역슬래시도 다른 문자와 동일하게 취급
• IGNORE_SPACE: 프로시저나 함수명과 괄호 사이 공백 무시
• REAL_AS_FLOAT: REAL 타입이 FLOAT 타입의 동의어로 바뀜
• NO_ZERO_IN_DATE & NO_ZERO_DATE: DATE or DATETIME 타입의 컬럼에 실존하지 않는 날짜 저장 불가
• ANSI: REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, ONLY_FULL_GROUP_BY 조합
• TRADITIOANL: STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGIN_SUBSTITUTION 조합

 

11.1.2 영문 대소문자 구분

MySQL 서버는 OS 에 따라 테이블명의 대소문자 구분

-> DB 나 Table 이 디스크 내 디렉터리 or 파일로 매핑되기 때문

 

lower_case_table_names: 운영체제와 관계없이 대소문자 구분의 영향을 받지 않게 함 (기본값 0)

그러나 왠만하면 초기 DB 나 테이블 생성 시 대문자 또는 소문자만으로 통일해 사용하는 것이 좋다.

 

11.1.3 MySQL 예약어

예약어와 같은 키워드 사용 시 역따옴표(`) 혹은 쌍따옴표로 감싸야 한다.

예약어인지 아닌지 확인해보려면 MySQL 에서 테이블을 생성해보면 알 수 있다.

 

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

• 대괄호("[]"): 해당 키워드나 표현식 자체가 선택 사항
• 파이프("|"): 앞과 뒤 키워드나 표현식 중에 단 하나만 선택 사용
• 중괄호("{}"): 괄호 내 아이템 중 반드시 하나를 사용
• "...": 앞에 명시된 키워드나 표현식의 조합이 반복될 수 있음

 

11.3 MySQL 연산자와 내장 함수

11.3.1 리터럴 표기법 문자열

11.3.1.1 문자열

SQL 표준에서 문자열을 항상 홀따옴표 사용해 표시. 쌍따옴표와 혼합 가능

 

11.3.1.2 숫자

따옴표 없이 숫자 값 입력

문자열 형태로 따옴표를 사용하더라도 비교 대상이 숫자 값이거나 숫자 타입의 컬럼 값이면 문자열 값을 숫자 값으로 자동 변환해줌. 숫자 타입과 문자열 타입 간 비교에서는 숫자 타입을 우선시 하므로, 문자열 값을 숫자 값으로 변환해서 비교함

이때 문자열 값을 숫자로 변환해서 비교를 수행하기 때문에 인덱스를 이용하지 못함

 

11.3.1.3 날짜

정해진 형태의 날짜 포맷으로 표기하면 자동으로 DATE 나 DATETIME 값으로 변환해준다.

 

11.3.1.4 불리언

BOOL, BOOLEAN 타입은 TINYINT 타입에 대한 동의어

TRUE 나 FALSE 로 비교하더라도, 실제 값은 0 또는 1 값으로 조회된다.

TRUE, FALSE 값으로 꼭 저장되야 한다면, ENUM('TRUE', 'FALSE') 타입으로 설정하기

 

11.3.2 MySQL 연산자

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

<=>: = 연산자와 같으며, NULL 값에 대한 비교까지 수행(NULL-safe 비교 연산자)

 

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

<>: != 연산자와 같음.

 

11.3.2.3 NOT 연산자 (!)

부정의 결과값을 정확히 예측할 수 없는 경우에는 사용 자제하기

 

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

• AND = &&
• OR = ||

그러나 오라클에서는 || 이 문자열 결합 연산자로 사용됨

SQL 가독성 높이기 위해 다른 용도로 사용될 수 있는 && 나 || 연산자는 사용 자제

 

11.3.2.5 나누기와 나머지 연산자

"/": 나누기

"%": 나머지

 

11.3.2.6 REGEXP 연산자

문자열 값이 어떤 패턴을 만족하는지 확인하는 연산자. 인덱스 처리 불가능

SELECT 'abc' REGEXP '^[x-z]';

 

11.3.2.7 LIKE 연산자

와일드 카드를 통해 비교하는 연산자. 인덱스 처리 가능

 

% 와일드 카드가 검색어

• 뒤쪽에 있으면 인덱스 레인지 스캔 사용
• 앞쪽에 있으면 인덱스 풀 스캔 사용

 

11.3.2.8 BETWEEN 연산자

크거나 같다, 작거나 같다 를 합친 연산자

범위 내 모든 인덱스를 검색해야 해서 상당히 많은 레코드를 읽어야 할 수 있다.

조건에 BETWEEN 연산자의 컬럼 선택도가 떨어질 때는 IN 으로 변경하는 방법으로 쿼리 성능을 개선할 수도 있다.

 

11.3.2.9 IN 연산자

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

MySQL 8.0 이후 세미 조인 최적화가 많이 안정화 됨. (인덱스 레인지 스캔)

 

NOT IN 의 경우, 인덱스 풀 스캔으로 표시됨.

동등이 아닌 부정형 비교여서 인덱스 이용해 처리 범위를 줄이는 조건으로 사용 불가능하기 때문

가끔 PK 와 비교될 때 클러스터링 키이기 떄문에 인덱스 레인지 스캔으로 표시되기도 하는데, 실제론 IN 처럼 효율적으로 실행되지 않음

 

11.3.3 MySQL 내장 함수

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

• IFNULL(target, default): 컬럼/표현식 값이 NULL 인지 비교하고, NULL 이면 다른 값으로 대체
• ISNULL(target): 인자로 전달한 표현식이나 컬럼 값이 NULL 인지 비교하는 함수

 

11.3.3.2 현재 시각 조회(NOW, SYSDATE)

• NOW: 항상 같은 값 
• SYSDATE: 하나의 SQL 내에서도 호출 시점에 따라 결과값이 달라짐

왠만하면 NOW() 사용 권장

 

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

• DATE_FORMAT(): DATETIME 타입의 컬럼이나 값을 원하는 형태의 문자열로 변환해야 함(%Y, %m, %d, %H, %i, %s)
  • SQL 표준 형태로 입력된 문자열은 필요한 경우 자동으로 DATETIME 타입으로 변환됨

• STR_TO_DATE(): 문자열을 DATETIME 타입으로 변환

 

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

특정 날짜에서 연도나 월일 또는 시간 등을 더하거나 뺄때 사용

SELECT DATE_ADD(NOW(), INTERVAL -1 DAY) AS yesterday;

 

11.3.3.5 타임스탬프 연산

UNIX_TIMESTAMP(): '1970-01-01 00:00:00' 으로부터 경과된 초의 수 반환

FROM_UNIXTIME(): 인자로 전달한 타임스탬프 값을 DATETIME 타입으로 변환

 

11.3.3.6 문자열 처리

• RPAD(target, bytes, char), LPAD(target, bytes, char): 문자열의 좌측 또는 우측에 특정 문자를 덧붙여서 지정된 길이의 문자열로 만듦
• RTRIM(target), LTRIM( target ): 문자열 우측/좌측에 연속된 공백문자 제거 TRIM() 은 양쪽 모두

 

11.3.3.7 문자열 결합

• CONCAT(): 여러 문자열 연결해서 하나의 문자열로 변환
• CONCAT_WS(delimeter, target...): 구분자로 두 번째 파라미터부터 이후까지 모두 연결

 

11.3.3.8 GROUP BY 문자열 결합

• GROUP_CONCAT(): 값들을 먼저 정렬한 후 연결하거나 각 값의 구분자 설정도 가능하며, 여러 값 중에서 중복을 제거하고 연결하는 것도 가능

SELECT GROUP_CONCAT(DISINCT dept_no ORDER BY emp_no DESC)
FROM dept_emp
WHERE emp_no BETWEEN 100001 and 100003;

 

지정한 컬럼 값들을 연결하기 위해 제한적인 메모리 버퍼 공간을 사용. group_concat_mat_len 시스템 변수로 조정 가능

기본값: 1KB

 

MySQL 8.0 부터는 래터럴 조인이나 윈도우 함수와 같이 사용 가능

 

11.3.3.9 값의 비교와 대체

CASE WHEN...THEN...END: SWITCH 구문과 동일

SLELECT emp_no, first_name,
  CASE gender WHEN 'M' THEN 'Man'
              WHEN 'F' THEN 'Woman'
              ELSE 'Unknown' END AS gender
FROM employees
LIMIT 10;

 

서브쿼리를 조건이 일치할 때만 실행해야 할 경우 유용하게 사용 가능

 

11.3.3.10 타입의 변환

SQL 은 텍스트 기반으로 작동하기 때문에 모든 입력값은 문자열처럼 취급된다.

명시적으로 타입 변환이 필요할 때 CAST() 함수 사용

 

일반적으로 문자열과 숫자, 날짜의 변환은 명시적으로 해주지 않아도 자동으로 필요한 형태로 변환해줌

 

CONVERT(): CAST() 함수 처럼 타입을 변환하는 용도 + 문자열의 문자 집합(e.g. utf8mb4) 변환 용도

 

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

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

 

11.3.3.12 암호화 및 해시 함수

-

 

11.3.3.13 처리 대기

• SLEEP(): 개발이나 디버깅 용도로 잠깐 대기하거나 일부러 쿼리의 실행을 오랜 시간 유지하려고 할 때 사용
  • 대기할 시간을 초 단위로 받음
  • 레코드 건수 만큼 SLEEP() 을 호출

 

11.3.3.14 벤치마크

BENCHMARK(반복수행 횟수, 반복실행 표현식): 디버깅 or 간단한 함수 성능 테스트용

• 벤치마크 횟수에 상관없이 단 1번의 네트워크, 쿼리 파싱 및 최적화 비용 소요 -> 실제 성능을 알기는 힘들고, 단순 비교용으로 활용 가능

 

11.3.3.15 IP 주소 변환

-

 

11.3.3.16 JSON 포맷

• JSON 데이터의 기본 표시 방법은 단순 텍스트 포맷
• JSON_PRETTY(): JSON 컬럼의 값을 알기 쉬운 포맷으로 변환
• JSON_STORAGE_SIZE(): BSON 포맷으로 저장하여 실제 디스크에 저장된 크기 반환
• JSON_EXTRACT(JSON 데이터 컬럼 or 도큐먼트 자체, 필드의 JSON 경로): JSON 필드 추출
  • " JSON 데이터 컬럼 or 도큐먼트 자체 " -> " 필드의 JSON 경로 " 와 동일
• JSON_UNQUOTE(): 추출한 JSON 필드에서 쌍따옴표 제거
• JSON_EXTRACT + JSON_UNQUOTE()
  • " JSON 데이터 컬럼 or 도큐먼트 자체 " ->> " 필드의 JSON 경로 " 와 동일
• JSON_CONTAINS(JSON 데이터 컬럼 or 도큐먼트 자체, JSON 오브젝트(도큐먼트 or 필드값), JSON 경로): 도큐먼트 또는 지정된 JSON 경로에 JSON 필드를 가지고 있는지 확인
• JSON_OBJECT(key1, value1, key2, value2 ...): JSON 오브젝트 생성 
• JSON_OBJECTAGG(): 컬럼 값들을 모아 JSON 오브젝트 생성
• JSON_ARRAYAGG(): 컬럼 값들을 모아 배열 생성
• JSON_TABLE(): JSON 데이터 값들을 모아서 RDBMS 테이블을 만들어 반환
  • 내부 임시 테이블을 사용
  • 원본 테이블과 동일한 레코드 건수를 가짐