[📕 Real MySQL 8.0 (2)] 쿼리 작성 및 최적화 - 시스템 변수와 연산자

쿼리가 빠르게 수행되게 하려면 DB 서버에서 쿼리가 어떻게 요청을 처리할지 예측할 수 있어야 한다.

그러므로 SQL 작성 방법이나 규칙, 내부적인 처리방식(옵티마이저)에 대한 지식이 필요하다.

 

이 챕터에서는 

쿼리의 패턴별로 어떻게 처리되는지와 프로그램 코드를 상당히 줄일 수 있는 유용한 쿼리 패턴을 다룬다.

 

 

 

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

✳️ SQL 모드 

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

 

👉 SQL 작성과 결과에 영향을 미치는 값의 종류

• STRICT_ALL_TABLES & STRICT_TRANS_TABLES
  • INSERT나 UPDATE 문장으로 데이터를 변경하는 경우 컬럼의 타입과 저장되는 값의 타입이 다를 때 자동으로 타입 변경을 수행하는데, 타입이 적절히 변환되기 어렵거나 컬럼에 저장될 값이 없거나 컬럼의 최대 길이보다 큰 경우 INSERT나 UPDATE를 계속 수행할지, 아니면 에러를 발생시킬지 결정 
  • STRICT_TRANS_TABLES 옵션은 InnoDB 같은 트랜잭션을 지원하는 스토리지 엔진에 대해 Strict Mode 적용
  • STRICT_ALL_TABLES 옵션은 트랜잭션 지원 여부와 상관없이 모든 스토리지 엔진에 대해 Strict Mode 적용 

🔹 활성화 상태

CREATE TABLE example (
    id INT NOT NULL,
    name VARCHAR(50) NOT NULL,
    age TINYINT
);

-- 잘못된 데이터 삽입 시 오류 발생
INSERT INTO example (id, name, age) VALUES (1, 'Sam', 300);
-- 오류: '300'은 TINYINT 범위(0~255 또는 -128~127)를 초과

 

🔹 비활성화 상태

-- 잘못된 데이터 삽입 시 값 조정
INSERT INTO example (id, name, age) VALUES (1, 'Sam', 300);
-- 성공: '300'은 최대값인 '255'로 조정되어 삽입

 

 

• ANSI_QUOTES
  • MySQL 서버에서 문자열 값(리터럴)을 표현하기 위해 홑따옴표와 쌍따옴표를 동시에 사용 가능 (오라클에서는 홑따옴표만 문자열 값 표기)
  • 해당 값을 설정할 경우 홑따옴표만 문자열 값 표기로 사용 가능하고, 쌍따옴표는 컬럼명이나 테이블명과 같은 식별자를 표기하는 데만 사용 (오라클과 같은 방법 사용)

SELECT "column_name" FROM "table_name";

SELECT 'hello' AS greeting;

 

 

 

• ONLY_FULL_GROUP_BY
  • GROUP BY 절에 포함되지 않은 컬럼이더라도 집합 함수의 사용 없이 그대로 SELECT 절이나 HAVING 절에 사용 가능
  • GROUP BY를 사용할 때, 집계되지 않은 컬럼이 SELECT 절에 포함될 경우, 해당 모드가 활성화되어 있으면 쿼리는 오류 발생
    👉 GROUP BY 절이 사용된 문장의 SELECT 절에는 GROUP BY 절에 명시된 컬럼과 집계함수(COUNT 또는 SUM과 같은 그룹 함수)만 사용 가능 

SELECT department, COUNT(*) 
FROM employees 
GROUP BY department;

 

 

• PIPES_AS_CONCAT
  • "||"를 오라클과 같이 문자열 연결 연산자로 사용 

 

• PAD_CHAR_TO_FULL_LENGTH
  • CHAR 타입이라고 하더라도 VARCHAR와 같이 유효 문자열 뒤의 공백 문자는 제거되어 반환됨
  • 불필요한 공백 문자를 제거하는 방식을 원한다면 비활성화
  • CHAR 타입의 컬럼 값을 가져올 때 뒤쪽의 공백이 제거되지 않고 반환되어야 할 때 활성화

 

• NO_BACKSLASH_ESCAPES
  • 역슬래시를 문자의 이스케이프 용도로 사용하지 못함 👉 역슬래시 문자도 다른 문자와 동일하게 취급 

 

• IGNORE_SPACE
  • 함수명과 괄호 사이의 공백 무시 👉 공백이 있어도 MySQL이 이를 허용

 

• REAL_AS_FLOAT
  • MySQL에서는 REAL을 DOUBLE로 처리
  • 해당 옵션 설정하면 REAL 타입이 FLOAT 타입의 동의어로 바뀜
  • 저장 공간 효율성을 중시하면 FLOAT으로 처리
  • 높은 정밀도를 유지하려면 DOUBLE로 처리

 

• NO_ZERO_IN_DATE & NO_ZERO_DATE
  • 해당 옵션이 활성화되면 DATE 또는 DATETIME 타입의 컬럼에 "2020-00-00"EHsms "0000-00-00"과 같은 잘못된 날짜를 저장하는 것 불가능

 

• ANSI
  • 앞의 여러 옵션을 조합해서 MySQL 서버가 최대한 SQL 표준에 맞게 동작하게 함

 

• TRADITIONAL
  • 여러 엄격한 SQL 모드를 한꺼번에 활성화하여 데이터 무결성과 정확성 강화

 

 

✳️ 영문 대소문자 구분 

• 설치된 운영체제에 따라 테이블명의 대소문자 구분
• 운영체제와 관계없이 대소문자 구분의 영향을 받지 않게 하려면 lower_case_table_names 시스템 변수 설정
• 변수를 1로 설정하면 모두 소문자로만 저장되고, 대소문자 구분 X
• 설정의 기본값은 0으로, DB나 테이블명에 대해 대소문자 구분
• 윈도우와 macOS에서는 2로 설정 가능 👉 저장은 대소문자 구분하지만, MySQL의 쿼리에서는 대소문자 구분 X

💡 이러한 설정 자체를 떠나서 가능하면 초기 DB나 테이블을 생성할 때 대문자 또는 소문자만으로 통일해서 사용하는 편이 좋음 !

 

 

✳️ MySQL 예약어 

• 생성하는 DB나 테이블, 컬럼의 이름을 예약어와 같은 키워드로 생성하면 해당 컬럼이나 테이블을 SQL에서 사용하기 위해 항상 백틱이나 쌍따옴표로 감싸야 함 
• 예약어인지 확인하는 방법으로, 테이블을 생성할 때 항상 백틱으로 테이블이나 컬럼의 이름을 둘러싸지 않은 상태로 생성
  👉 그래야만 예약어인지 아닌지를 MySQL 서버가 에러로 알려줌

 

 

 

 

2. MySQL 연산자

✳️ 리터럴 표기법 문자열 

 

🔷 문자열 

SELECT * FROM departments WHERE dept_no='d''001';
SELECT * FROM departments WHERE dept_no='d"001';
SELECT * FROM departments WHERE dept_no="d'001";
SELECT * FROM departments WHERE dept_no="d""001";

• 1, 2번째 쿼리의 문자열 표기법은 SQL 표준
• 3, 4번째 쿼리의 문자열 표기법은 MySQL에서만 지원되는 방식
• d'001, d"001, d'001, d"001 로 처리됨

 

🔷 숫자

• 따옴표 없이 숫자값을 입력하면 됨
• 문자열 형태로 따옴표를 사용하더라도 비교 대상이 숫자 값이거나 숫자 타입의 컬럼이면 MySQL 서버가 문자열 값을 숫자 값으로 자동 변환
• MySQL은 숫자 타입과 문자열 타입 간의 비교에서 숫자 타입을 우선시하므로 문자열 값을 숫자 값으로 변환한 후 비교 수행 

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

• 첫 번째 쿼리는 주어진 상숫값을 숫자로 변환하는데, 이대는 상숫값 하나만 변환하므로 성능상 문제 X
• 두 번째 쿼리는 주어진 상숫값이 숫자 값인데, 비교되는 컬럼은 문자열 컬럼이므로 문자열 컬럼을 숫자로 변환해서 비교
• string_column 컬럼의 모든 문자열 값을 숫자로 변환해서 수행해야 하므로 string_column에 인덱스가 있더라도 이를 이용 X

💡 숫자 값은 숫자 타입의 컬럼에만 저장하자 !

 

 

🔷 날짜 

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

• 정해진 형태의 날짜 포맷으로 표기하면 MySQL 서버가 자동으로 DATE나 DATETIME 값으로 변환하기 때문에 STR_TO_DATE() 같은 함수 사용 X

 

🔷 불리언

• BOOL이나 BOOLEAN 타입은 TINYINT 타입에 대한 동의어
• MySQL에서 FALSE는 0이 되지만, TRUE는 1만을 의미
• 모든 숫자 값이 TRUE나 FALSE로 매핑되지 않기 때문에 불리언 타입을 꼭 사용하고 싶다면 ENUM 타입으로 관리

 

---

✳️ MySQL 연산자 

 

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

• <=> 연산자는 NULL 값에 대한 비교 수행 
  NULL을 하나의 값으로 인식하고 비교
• NULL = NULL 은 FALSE
• NULL <=> NULL 은 TRUE
• 둘 중 하나라도 NULL일 때 NULL <=> <value> 는 FALSE

SELECT NULL <=> NULL; -- 결과: TRUE
SELECT NULL <=> 1;    -- 결과: FALSE

SELECT 5 <=> 5;  -- 결과: TRUE
SELECT 5 <=> 10; -- 결과: FALSE

SELECT NULL <=> 'value';  -- 결과: FALSE
SELECT 'value' <=> 'value';  -- 결과: TRUE

 

 

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

• 통일해서 사용하도록 .

 

🔷 NOT 연산자 (!)

• TRUE 또는 FALSE 연산의 결과를 반대로 만드는 연산자

 

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

• 불리언 표현식의 결과를 결합하기 위해 사용 
• 오라클에서는 || 를 문자열 결합 연산자로 사용
• 오라클에서 운영되던 어플리케이션을 MySQL로 이관한다거나 문자열 결합 연산에 
  "||" 를 사용하고 싶다면 sql_mode 시스템 변숫값에 PIPES_AS_CONCAT 설정

 

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

• 몫의 정수 부분 👉 DIV
• 나눈 결과 몫이 아닌 나머지를 가져오는 연산자 👉 MOD

 

🔷 REGEXP 연산자 

• 문자열 값이 어떤 패턴을 만족하는지 확인하는 연산자
• REGEXP 연산자의 좌측에 비교 대상 문자열 값 또는 문자열 컬럼을, 
  우측에 검증하고자 하는 정규 표현식 사용   
• POSIX 정규 표현식에서 사용하는 패턴 키워드 그대로 사용
• REGEXP 연산자를 문자열 컬럼 비교에 사용할 때 REGEXP 조건의 비교는 인덱스 레인지 스캔 사용 X
  👉 WHERE 조건절에 REGEXP 연산자를 사용한 조건을 단독으로 사용하는 것은 성능상 좋지 X

SELECT * FROM users WHERE email REGEXP '^admin';

 

 

🔷 LIKE 연산자

• REGEXP 연산자는 인덱스를 전혀 사용하지 못함
• LIKE 연산자는 인덱스 이용해 처리 가능
• 정규 표현식을 검사하는 것이 아니라 어떤 상수 문자열이 있는지 없는지 정도 파악
• LIKE에서 사용할 수 있는 와일드카드 문자는 "%"와 "_" 
  • % 👉 0 또는 1개 이상의 모든 문자에 일치
  • _ 👉 정확히 1개의 문자에 일치
• REGEXP는 비교 대상 문자열의 일부에 대해서만 일치해도 TRUE 반환
• LIKE는 항상 비교 대상 문자열의 처음부터 끝까지 일치하는 경우에만 TRUE 반환
• 와일드카드 문자인(%, _)가 검색어의 뒤쪽에 있다면 인덱스 레인지 스캔으로 사용 가능하지만 와일드카드가 검색어의 앞쪽에 있다면 인덱스 레인지 스캔 사용 불가 

SELECT COUNT(*)
FROM employees
WHERE first_name LIKE '%rist';

👉 인덱스의 Left-most 특성으로 인해 레인지 스캔 사용 못하고 인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔 방식으로 쿼리 처리

 

 

 

🔷 BETWEEN 연산자

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

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

👉 'd003'보다 크거나 같고, 'd005'보다 작거나 같은 모든 인덱스의 범위를 검색해야만 하므로 emp_no=10001조건은 비교 범위를 줄일 수 없음

SELECT * FROM dept_emp
WHERE dept_no IN ('d003', 'd004', 'd005')
	AND emp_no=10001;

👉 동등비교를 여러 번 수행하므로 작업 범위를 줄여 최적으로 사용 가능

 

 

🔹 BETWEEN 연산자를 IN 연산로 변경

SELECT  *
FROM dept_emp USE INDEX(PRIMARY)
WHERE dept_no IN (
	SELECT dept_no
    FROM departments
    WHERE dept_no BETWEEN 'd003' AND 'd005')
AND emp_no=10001;

👉 세미 조인 최적화 이용해 더 빠른 쿼리로 변환

👉 세미 조인 최적화의 장점 

- 서브 쿼리에서 모든 값을 반환하지 않고, 첫 번째 일치하는 값만 확인
- 조건에 맞는 최소한의 데이터만 처리하여 더 빠른 실행 속도 제공
- 옵티마이저는 첫 번째로 일치하는 dept_no만 확인하고, dept_emp 테이블에서 해당 dept_no와 emp_no=10001에 일치하는 행만 조회

 

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';

👉 IN (subquery) 조인을 실행해도 MySQL 옵티마이저는 JOIN 쿼리로 재작성해서 쿼리 최적화함

 

 

 

🔷 IN 연산자

• 여러 개의 값에 대해 동등 비교 연산 수행
• 범위로 검색하는 것이 아니라 여러 번의 동등 비교로 실행하기 때문에 일반적으로 빠르게 처리됨
• 상수가 사용된 경우 - IN (?, ?, ?)
  • 동등 비교와 동일하게 작동하므로 매우 빠르게 쿼리가 처리됨 
• 서브쿼리가 사용된 경우 - IN (SELECT ... FROM ...)
  • 세미 조인의 최적화(고급 최적화)와 관련된 것은 추후에 정리
• NOT IN은 대부분 인덱스 풀 스캔으로 실행됨 👉 인덱스를 이용해 처리 범위 줄이는 조건으로 사용 X