쿼리 작성 및 최적화

Created by 이항희 / atconsole.com (Team Blog)

목차

SQL 의 구성

SQL = DDL + DML

DDL

스키마, 테이블 구조 정의

DML

  • Query

    • SELECT

  • Statement

    • INSERT
    • DELETE
    • UPDATE
  • ...라고 부르는데 보통 구분없이 부른다.

쿼리와 연관된 시스템 설정

SQL_MODE 라는 키 값으로 저장된다.

시스템 설정값이다.
설정에 따라 SQL 작성법과 결과에 영향을 준다

SHOW global variables  where variable_name = 'sql_mode'

STRICT_ALL_TABLE

설정되면 데이터 입력 시 컬럼값의 길이보다 입력하는 값이 클 경우 오류 발생.

STRICT_TRANS_TABLE

설정되면 데이터 입력 시 타입과 호환되지 않을 때 오류.

NO_AUTO_CREATE_USER

설정되면 특정 유저에게 권한을 GRANT 시 공백문자가 아닌 패스워드가 반드시 필요하다.

NO_ENGINE_SUBSTITUTION

설정되면 테이블 생성 시 테이블 엔진 명시가 반드시 필요하다.

PIPE AS CONCAT

설정되면 오라클 스타일의 문자열 결합 (||) 을 사용할 수 있다.

기본값

  • STRICT_TRANS_TABLES
  • NO_AUTO_CREATE_USER
  • NO_ENGINE_SUBSTITUTION

테이블 명 대소문자 구분

lower_table_case_name

테이블 명에서 윈도우 MySQL은 대소문자를 구별하지 않으나 리눅스는 구별한다.

기본값은 0으로 구분하게 되어 있으나 1로 지정하면 모든 테이블은 소문자 저장된다.

MySQL 예약어

MySQL은 각 DBMS 자체에서 쓸 각종 키워드를 예약해두고 있다.

예약어를 토큰으로 사용하려 할 경우 역따옴표(`)로 감싸줘야 정상 동작을 기대할 수 있다.

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

MySQL 공식 매뉴얼의 경우 다음과 같은 구조로 되어 있다

  • 대문자는 키워드를 가르킨다. 키워드는 사용 시 대소문자 구별이 없다.
  • 이탤릭체는 사용자 입력 토큰을 가르킨다. 테이블명이나 컬럼 등
  • [] 는 옵셔널 구문을 가르킨다. 구문에 반드시 필요하지 않다.
  • | 는 앞과 뒤중 하나만을 선택해야 한다는 걸 나타낸다.
  • {} 는 안의 내용 중 반드시 하나를 사용해야 한다는 것을 나타낸다.
  • ... 은 앞의 키워드나 표현식이 반복될 수 있음을 나타낸다

연산자와 내장 함수

MySQL은 보통 ANSI 표준을 준수하지만 독자적인 사용이 되는 연산자나 표기법이 있다

특히 내장 함수는 DBMS마다 다들 다른 이름을 가지고 있는게 보통이다.

문자열

MySQL은 문자열 표기 시 쌍따옴표와 홑따옴표 두개를 사용할 수 있지만 ANSI 표준은 홑따옴표이다.
문자열 안에 홑따옴표나 쌍따옴표가 있을 경우 연속으로 두개를 입력하면 된다.

SELECT * FROM employee WHERE name = 'Joe';
SELECT * FROM employee WHERE name = "Joe";
SELECT * FROM employee WHERE name = 'De''Raven';
SELECT * FROM employee WHERE name = "De""Raven";

sql_mode 값에 ANSI_QUOTE 옵션을 지정하면 사용할 수 없게 된다

숫자

MySQL은 숫자나 문자 사용 시 상황에 맞게 자동 변환한다.

SELECT * FROM xxx WHERE number_col = '1';
SELECT * FROM xxx WHERE string_col = 1;

첫번째 쿼리는 비교할 상수 하나만 문자열 변환하면 되기에 문제가 없다.

두번째 쿼리는 모든 테이블의 컬럼을 숫자로 변환 뒤 비교하기 때문에 문제가 있다.

날짜

MySQL은 다른 DBMS와는 다르게 정해진 포맷으로 표기하면 서버에서 자동으로 변환하여 비교하기에 TO_DATE 함수등을 사용하지 않아도 된다. 

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

두 쿼리는 동작이 같다

불리언

TINYINT 타입에 대한 동의어. TRUE는 1. FALSE는 0.
만일 진정한 불리언 값을 사용하고 싶다면 ENUM 을 사용하는게 좋다.

연산자

<=>

보통은 = 연산자와 같지만 NULL 값 비교시 차이가 있다.

보통 NULL은 비교할 방법은 IS NULL 뿐이지만, <=> 는 NULL을 하나로 값으로 보고 비교한다.

<>, !=

둘의 의미는 같다. 다만 프로젝트 시 하나만 통일하여 사용하는게 좋다.

NOT, !

둘의 의미는 같다. 조건의 반전을 할때 쓰인다.

&&,AND / ||,OR

가급적 AND, OR를 사용하는게 가독성 면에서 좋다

REGEXP, RLIKE

피연산자의 값에서 패턴을 만족하는지 찾는 연산자이다. RLIKE 와 동일한 비교를 수행한다.

SELECT 'abs' REGEXP '^[a-z]';
SELECT 'abs' RLIKE '^[a-z]';

LIKE

와일드 카드로 '%', '_' 을 사용할 수있다. 와일드카드 이스케이프에는 ESCAPE 키워드를 사용한다.

  • % 는 개 혹은 1개 이상의 모든 문자열 일치
  • _ 는 0정확히 1개 문자에 일치
SELECT 'abs' LIKE 'a%' /* true */;
SELECT 'abs' LIKE 'a_' /* false */;
SELECT 'abs' LIKE 'ab_' /* true */;
SELECT 'ab%sxyz' LIKE 'ab/%s%' ESCAPE '/'/* true */;

BETWEEN

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

이러한 방식에서 잘못된 실행 계획이 탄생하는 경우가 있다.

SELECT * FROM dept_emp
WHERE dept_no BWTEEN 'd003' AND 'd005' AND emp_no = 10001; /* BAD */

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

그런데

BEWTEEN을 IN 으로 바꾸려고 SELECT 쿼리를 한번 더 실행해야 할 경우가 있는데 이럴 경우 실제 계획 테스트가 필요하다.
주의할 점은 IN (subquery) 형태는 대부분 나쁜 결과를 가져올 수 있다.

IN 연산자

여러 번의 동등 비교로 수행된다. 만일 IN 연산자 안에 서브쿼리가 있다면 서브쿼리가 먼저 실행되는것이 아니라 외부의 쿼리가 먼저 실행되고
그 다음 조건으로 서브 쿼리가 실행되기에 대부분 안좋은 결과를 가져온다.

NOT 과 함께 수행되는 IN 연산자는 동등이 아닌 부정형 비교가 되기에 인덱스 풀 스캔을 수행하고,처리 범위를 줄일 수가 없다.

내장 함수

ISNULL

NULL 여부를 검사하여 0, 1 을 반환한다.

IFNULL

NULL 이면 첫번째, 아니면 두번째 인자를 반환한다.

NOW, SYSDATE

둘다 현재의 시간을 반환한다.
하지만 NOW는 SQL 안에서 전부 값이 같지만 SYSDATE는 SQL 내에서도 호출 시점에 따라 값이 달라진다. 주의.

my.cnf 나 my.ini 파일에 sysdate-is-now 설정으로 둘의 동작을 같게 할 수 있다.

날짜 관련

  • 날짜 시간 포맷

    DATE_FORAMT, STR_TO_DATE

  • 날짜 연산

    DATE_ADD, DATE_SUB

SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);
SELECT DATE_ADD('2013-11-26', INTERVAL 2 DAY);

타임스탬프 연산

UNIX_TIMESTAMP, FROM_UNIXTIME

문자열 처리

RPAD, LPAD, LTRIM, RTRIM, CONCAT(_WS)

GROUP_CONCAT. GROUP BY 절과 같이 사용하면 그룹절을 하나의 결과로 만든다.

값의 비교와 대체

CASE...WHEN...THEN...END

구문 사이에 서브 쿼리도 사용할 수 있다.

타입 변환

CAST, CONVERT

이진값, 16진수

HEX, UNHEX

해시 함수

MD5, SHA

문자열 저장이라 원래 크기보다 더 큰 문자열 타입으로 저장되는데 원래의 크기로 저장하려면 BINARY 타입을 사용하면 된다.

SLEEP

지정된 시간만큼 쿼리를 대기한다.

/* 각 행마다 1초씩 대기. 즉 5초간 실행 */
SELECT SLEEP(1), firstName FROM employees LIMIT 5;

BENCHMARK

첫번째 인자의 숫자만큼 두번째 쿼리를 반복 실행한다. 반환값보다 수행 시간을 보기 위해 사용.
반드시 쿼리는 스칼라 값을 반환해야 한다.

SELECT benchmark(10, (SELECT firstName FROM employees LIMIT 1))

INET_ATON, INET_NTOA

IP Adresss를 Integer 형태의 컬럼에 넣을 수 있게 변환하거나 복구하는 기능.

PASSWORD, OLD_PASSWORD

문자열을 단반향 비대칭 비밀번호로 만든다. OLD_PASSWORD는 4.0 이하 버전에서 사용되던 PASSWORD 함수의 fallback 이다.

현재도 계속 알고리즘이 변하고 있으므로 실제 고객 정보 관리에는 해시 함수를 사용하는 편이 좋다.

VALUES

INSERT INTO 나 ON DUPLICAT KEY UPDATE SQL 구문에만 사용할 수 있다.

COUNT

레코드의 건수를 반환하는 함수. 인자로 *를 줄 경우 레코드 자체를 의미한다. COUNT(1)이나 COUNT(*) 이나 완전히 같다.

사용 시 쿼리에 불필요한 ORDER BY 나 LEFT JOIN 등의 쿼리는 빼자.

특정 컬럼을 지정할 경우 NULL 이 아닌 컬럼만을 계산한다.

SQL 주석

-- 한줄 주석

# MySQL 전용 한줄 주석

/* 여러 라인 주석 */

/*! SQL 컴파일러 지시자 주석. 적법하지 못한 문자열이 올 경우 Error */
SELECT /*! STRAIGHT JOIN */
FROM employees e, dept_no de WHERE de.emp_no = e.emp_no

SELECT

SELECT 구문의 절

  • SELECT 절
  • FROM 절
  • WHERE 절
  • GROUP BY 절
  • HAVING 절
  • ORDER BY 절
  • LIMIT 절

SELECT 각 절 실행 순서

대다수의 쿼리는 이 순서를 벗어나지 않는다. 다만 FROM 절의 서브쿼리 등의 DERIVED 실행 계획 등이 생기면 LIMIT 가 먼저 실행되는 등 순서가 달라진다.

  1. 드라이빙 및 드리븐 테이블의 WHERE 절 실행, JOIN
  2. GROUP BY 적용
  3. DISTINCT 적용
  4. HAVING 적용
  5. ORDER BY 적용
  6. LIMIT 적용

WHERE, GROUP BY, ORDER BY INDEX 사용

기본 규칙

  • 컬럼 가공을 하게 되면 정렬된 인덱스를 사용하지 못한다.
  • 가급적 같은 데이터 타입을 사용한다

WHERE 절의 인덱스 사용

AND 연산이 있을 경우 대부분 행 수가 줄어들며 인덱스 이용이 쉽다.

OR 연산이 있을 경우 옵티마이저는 풀 테이블 스캔을 사용한다.

GROUP BY 절의 인덱스 사용

  • 컬럼의 순서가 맞아야 한다.
  • 컬럼이 전부 인덱스에 있어야 한다.
/* col1, col2, col3 이 인덱스인 경우 */
... WHERE col1 = '1' GROUP BY  col1, col2, col3
... WHERE col1 = '1' GROUP BY  col2, col3

ORDER BY 절의 인덱스 사용

GROUP BY 절과 유사하지만 한가지 더, 정렬 순서가 모두 오름차순이거나 내림차순일 경우에만 해당된다.

/* col1, col2, col3 이 인덱스인 경우 */
... ORDER BY  col1, col2, col3 /* OK */
... ORDER BY  col1, col2 DESC, col3 /* NO */

WHERE, ORDER BY 절의 혼용 인덱스 사용

  • WHERE 절과 ORDER BY 절이 동시에 같은 인덱스를 사용. 제일 좋다
  • WHERE 절만 인덱스를 사용. 처리 레코드가 적을 때만 효율적
  • ORDER BY 절만 인덱스를 사용. 아주 많은 레코드 정렬시에만 사용

GROUP BY, ORDER BY 절의 혼용 인덱스 사용

전에 기술한 둘의 제약이 비슷함.
다만 MySQL의 GROUP BY는 ORDER BY의 기능까지 내부적으로 수행하므로 대부분 ORDER BY는 제거하는 쪽이 좋다

WHERE, GROUP BY, ORDER BY 절의 혼용 인덱스 사용

다음 사항을 순차 검토하고 수행.

  1. WHERE 절이 인덱스를 사용할 수 있는가
  2. GROUP BY 절이 인덱스를 사용할 수 있는가
  3. GROUP BY과 ORDER BY 절이 인덱스를 사용할 수 있는가

432-7-10

WHERE 비교 시 주의사항

NULL 비교

비교할 수 없는 값.

SELECT * FROM employees WHERE to_date IS NULL /* OK */
SELECT * FROM employees WHERE ISNULL(to_date) = 1 /* NO... Full Scan! */

문자열 숫자열 타입 일치

날자 비교

기본적으로 날자 형식으로 포매팅된 문자열은 자동적으로 변환 후 비교하며 인덱스도 잘 적용됨

다만 컬럼에 대해 함수로 조작하는 행위는 Full Scan.

DISTINCT

유니크한 값을 조회할 경우 사용

실수가 잦은 부분이 있는데 DISTINCT(컬럼명) 문법은 유니크 조회가 레코드 단위이기에 의미가 없는 구문.

SELECT DISTINCT firstName, lastName FROM employees;
SELECT DISTINCT(firstName), lastName FROM employees;

집합 함수와 함께 사용된 DISTINCT

레코드 단위가 아닌 컬럼 안의 유일한 값만 가져옴.

SELECT count(DISTINCT firstName) as f, count(lastName) as l FROM employees;
SELECT count(DISTINCT firstName) as f, lastName FROM employees;
SELECT count(DISTINCT firstName, lastName) FROM employees;

LIMIT

오라클의 ROWNUM, MSSQL의 TOP-n 과 비슷. 제일 마지막 실행.

  • 검색 조건에 일치하는 테이블을 전부 가져온다
  • 그룹 및 정렬
  • 결과에서 LIMIT 지정된 레코드 수만큼만 반환

쿼리 성능 향상 효과

대부분의 경우 쿼리 성능이 상승한다.

LIMIT 0 으로 할 경우, 옵티마이저는 쿼리에 대한 최적화만 하고 즉시 사용자 응답을 한다
커넥션 유효성 체크 시 유용.

JOIN

순서와 인덱스 #1

  1. 인덱스에서 조건을 만족하는 값의 위치를 찾는다. 이 과정을 인덱스 탐색이라고 한다.
  2. 탐색된 위치부터 필요한 만큼 인덱스를 읽는다. 이 과정을 인덱스 스캔이라고 한다.
  3. 읽어들인 인덱스 키와 레코드주소를 이용해 레코드가 저장된 페이지를 가져오고 최종 레코드를 읽어온다

순서와 인덱스 #2

드라이빙 테이블에서는 인덱스 탐색을 한번만 하고 조인 진행시에는 스캔만 실행하면 된다

드리븐 테이블은 드라이빙 레코드 수만큼 탐색, 스캔을 반복한다.

그래서 옵티마이저는 드리븐 테이블을 최적으로 읽을 수 있게 실행 계획을 정한다.

순서와 인덱스 #3

두 컬럼 모두 인덱스가 있다면 옵티마이저의 기준에 따라 드라이빙을 정한다.

한쪽에만 있는 경우 없는 쪽을 드라이빙 테이블로 정한다.

어느쪽을 선택해도 풀 스캔이 일어나기에 다른 기준으로 드라이빙을 정한다.
단 건수가 적은 테이블을 드리븐 테이블로 정하는 것이 효율적이다.
조인 버퍼가 실행되기에 성능이 저하되고 계획에 Using join buffer 가 표시된다.

JOIN 컬럼 타입

조인 조건에서도 인덱스와 동일하게 같은 데이터타입일때 최적의 성능을 낸다.

OUTER JOIN 유의사항

OUTER JOIN에서는 테이블의 컬럼에 대한 조건은 모두 ON 조건에 명시해야 한다.

그렇지 않으면 MySQL은 Inner JOIN 처리한다.

/* OUTER JOIN NO~ */
SELECT * FROM A a LEFT JOIN B b ON a.id = b.aid
WHERE b.extra = 100;

/* OUTER JOIN OK! */
SELECT * FROM A a LEFT JOIN B b ON a.id = b.aid ON b.extra = 100;

OUTER JOIN 과 COUNT(*)

보통 페이징 쿼리를 만들 때 SELECT 에서 쓰던 구문에 컬럼만 count(*) 를 넣는 경우가 많다

OUTER JOIN 의 특성상 제거해주면 빠른 성능을 낼 수 있다.

  1. 드라이빙 테이블과 드리븐 테이블 관계가 1:1, M:1 인 경우
  2. 드리븐 테이블에 조인조건 외의 별도 조건이 없을 경우

OUTER JOIN을 사용한 ANTI JOIN

한쪽 테이블에는 있지만 다른 한쪽 테이블에는 없는 레코드를 검색할 때 최적

(subquery) 구문은 최적화가 부실하여 성능이 많이 떨어진다.

/* NOT IN */
SELECT * FROM A a WHERE a.id NOT IN (SELECT b.aid FROM B b);

/* NOT EXISTS. 서브쿼리가 어떤 값이든 리턴하면 true. 아니면 false */
SELECT * FROM A a WHERE a.id NOT EXISTS (SELECT b.aid FROM B b WHERE a.id = b.aid);

/* ANTI JOIN. 제일 좋다. */
SELECT * FROM A a LEFT JOIN B b ON a.id = b.aid
WHERE b.aid IS NULL

FULL OUTER JOIN 구현

MySQL에서는 제공하지 않기에 UNION 으로 구현 가능하나 임시테이블로 인해 성능이 떨어진다

Mutex 테이블로 최적화가 가능하다. t_copy 라는 이름으로도 많이 알려져 있다

CREATE TABLE mutex ( no INT NOT NULL PRIMARY KEY(no) );
INSERT INTO mutex VALUES (0), (1);

SELECT IFNULL(a.id, b.aid) AS id, e.name, a.age
FROM mutex m
    LEFT JOIN A a ON m.no = 0
    LEFT JOIN B b ON m.no = 1 OR a.id = a.aid
    LEFT JOIN A a2 ON m.no = 0 AND a2.id = b.aid
WHERE b.aid IS NULL OR a2.id IS NULL;

Delayed JOIN #1

JOIN 시 GROUP BY 나 ORDER BY 를 사용할 때 인덱스를 사용하지 못할 때 사용

GROUP BY 나 ORDER BY 를 먼저 처리하고 JOIN. LIMIT 가 사용되면 더욱 큰 효과를 얻는다.

SELECT * FROM A a LEFT JOIN B b ON a.id = b.aid;
LIMIT 990, 10

쿼리의 결과를 1000개 조회하고 990개를 버린다.

Delayed JOIN #2

SELECT *
FROM (SELECT * FROM A a LIMIT 990, 10) a
    LEFT JOIN B b ON a.id = b.aid
LIMIT 990, 10;

쿼리의 결과를 990 번째 인덱스부터 10개 조회한다

GROUP BY

MySQL에서는 GROUP BY 에 사용되지 않은 컬럼을 별도의 집합함수 없이 사용할 수 있으나 예측 불가이기에 사용을 안하는게 좋다.

GROUP BY

MySQL에서는 GROUP BY 가 정렬까지 수행한다.

보통 정렬까지 수행할 필요가 없으므로 ORDER BY NULL 이라고 명시적으로 정렬을 중단시킬 수있다.

GROUP BY .. WITH ROLLUP

그룹핑 소계 및 총 합을 제공.

레코드 컬럼 변환

GROUP BY 와 CASE WHEN을 사용하여 테이블 결과를 가로에서 세로로 변경하거나 하나의 칼럼을 여러개로 나눌 수 있다.

ORDER BY

MySQL의 정렬 기준

  • 인덱스를 사용한 경우에는 인덱스 정렬 기준으로 가져온다
  • Primary 키 기준으로 가져온다.
  • 임시 테이블을 거쳤을 경우 예측할 수 없다.

사용법, 주의사항

여러개를 줄 수 있다.

숫자를 주게 되면 컬럼의 순서에 해당하는 컬럼이 정렬 기준이 된다

문자열 상수를 줄 경우 무시한다.

랜덤한 행을 꼽을 땐 ORDER BY RAND() 를 사용하나 행 수가 많아지면 성능이 저하된다

정렬이 어려운 경우 쿼리를 나눠 여러번 실행하는게 오히려 성능 향상에 좋을 수 있다.

MySQL DESC 인덱스는 명목상일 뿐 지원하지 않고 있다.

함수나 표현식을 사용한 정렬

SELECT * FROM A ORDER BY id;
SELECT * FROM A ORDER BY id + 10;

첫번째 예제는 정상 동작. 두번째 예제는 최적화를 실패하고 별도 정렬 작업을 수행하기에 성능이 저하된다.

조금이라도 컬럼값을 연산하게 되면 인덱스 정렬이 불가능해진다.

레코드 컬럼 변환

GROUP BY 와 CASE WHEN을 사용하여 테이블 결과를 가로에서 세로로 변경하거나 하나의 칼럼을 여러개로 나눌 수 있다.

Sub Query

단위 처리별로 쿼리를 독립시킬 수 있다. 하지만 MySQL은 서브쿼리 최적화를 제대로 못할 경우가 많다.

Sub Query 의 종류

상관 서브쿼리. 자신의 실행이 외부의 영향을 받는 경우

SELECT * FROM A a WHERE EXISTS
    (SELECT 1 FROM B b FROM a.id = b.aid);

독립 서브쿼리. 자신의 실행이 외부의 영향을 받지 않는 경우

SELECT * FROM A a WHERE a.id =
    (SELECT b.aid FROM B b WHERE name LIKE 'Joe%');

Sub Query의 제약

  • LIMIT 절과 LOAD DATA INFILE 절에 사용할 수 없다
  • 서브쿼리를 IN 연산자와 사용할 경우 효율적이지 않다.
  • IN 연산자 안의 서브쿼리는 ORDER BY와 LIMIT 를 동시에 사용할 수 없다
  • FROM 절의 서브쿼리는 상관 서브쿼리 형태로 사용할 수 없다.

SELECT 절에 IN 과 함께 사용된 서브쿼리

SELECT * FROM employees WHERE emp_no IN ( 10001, 10002, 10010 );
SELECT * FROM dept_emp WHERE dept_no IN
    ( SELECT d.dept_no FROM departments d WHERE d.dept_name = 'Finance' );

위의 쿼리는 동등 비교로 상당히 최적화되어 실행된다

두번째 쿼리는 실제 실행 시 상관 쿼리로 분석되어 A 테이블에 대해풀 테이블 스캔이 일어난다

/* 실제 수행되는 쿼리 */
SELECT * FROM dept_emp de WHERE EXISTS
    ( SELECT 1 FROM departments d WHERE d.dept_name = 'Finance' AND de.dept_no = d.dept_no );

개선

  • 아우터 테이블과 서브쿼리 테이블 관계가 1:1 이거나 1:M인경우 JOIN 처리
  • 아우터 테이블과 서브쿼리 테이블 관계가 M:1 이면 조인 후 조인 키로 그룹핑한다.

FROM 절에 사용된 서브쿼리

FROM 절의 서브쿼리는 제일 우선되는 튜닝사항. 항상 임시테이블을 사용하기에 성능이 저하된다.

집합 연산

JOIN 이 여러 테이블의 컬럼을 연결하는 거라면 집합 연산은 여러 테이블의 레코드를 연결하는 방법이다.

UNION, INTERSECT, MINUS 가 있다.

UNION( ALL)

중복 처리로 UNION ALL 혹은 UNION DISTINCT 처리할 수 있다. 기본값은 DISTINCT 이다.

ALL 은 두 집합에 대해 별도 처리 과정을 거치지 않고 바로 반환한다.

DISTINCT 는 두개의 집합에서 중복된 레코드를 제거한 뒤 합집합을 사용자에게 반환한다.

실제 이 둘의 차이는 유니크 인덱스 생성 유무에 있다. 쿼리 결과에 중복이 없을거라고 생각되면 UNION ALL로 성능을 높이는게 좋다.

INTERSECT

INNER JOIN 쿼리와 동일하지만 성능은 떨어진다. 가급적 INNER JOIN 을 쓰자

MINUS

앞에서 살펴 본 ANTI JOIN과 동일하다. ANTI JOIN 이 더 빠르므로 이쪽을 쓰자.

LOCK IN SHARE MODE 와 FOR UPDATE

InnoDB에서는 SELECT 시 레코드 락을 걸 경우 사용.

LOCK IN SHARE MODE

SELECT 된 레코드에 대해 읽기 잠금을 걸고 다른 세션의 변경을 막는다

SELECT * FROM dept_emp de WHERE id = 100 LOCK IN SHARE MODE;
SELECT * FROM dept_emp de WHERE id = 100 LOCK IN SHARE MODE /*! LOCK IN SHARE MODE */;

FOR UPDATE

SELECT 된 레코드에 대해 쓰기 잠금을 걸고 다른 세션의 변경 뿐 아니라 읽기도 막는다.

SELECT * FROM dept_emp de WHERE id = 100 FOR UPDATE;
SELECT * FROM dept_emp de WHERE id = 100 LOCK IN SHARE MODE /*! FOR UPDATE */;

레코드 해제

COMMIT 이나 ROLLBACK으로 해제된다.

데드락이나 보틀넥을 증가시키므로 사용에 주의를 요한다.