DaehunGwak commented 2 years ago

진도

쿼리 작성 및 최적화 (11.4.10 서브쿼리 ~ 11.6 UPDATE와 DELETE)

일시

2022.07.24.(일) 21:00

Edwin222 commented 2 years ago

서브쿼리

select에서 쓴 경우
- 셀렉트의 칼럼 중 하나에 서브쿼리를 쓴 경우로, 출력되는 값이 (row, col) 하나를 핀포인트로 집는 값이어야 함. 아니면 값이 없던가.(NULL 취급)
- 서브쿼리 처리 과정에서 임시 테이블을 만들거나, 비효율적으로 돈다거나 하지 않는다.
- 그냥 Select가 비효율적으로 안 돌게 주의하면 됨.
from에서 쓴 경우
- from에 있다고 꼭 임시 테이블을 만드는 건 아니다. 최적화 절차에서 가능한 한 외부쿼리로 병합해서 처리하려고 함.
- 서브쿼리에서 사용할 경우 확실히 병합하지 못하고 임시 테이블을 만드는 기능들이 있다.
- 집합 함수 / distinct / group by / having / union / 사용자 변수 / 서브쿼리를 select에서 쓴 경우 등
where에서 쓴 경우
- <=> 비교
- 서브쿼리를 먼저 실행하고 상수값으로 변환 후 조건 체크를 진행.
- in 비교
- 데이터를 조인하진 않지만 다른 테이블과 일치 여부는 검사한다는 의미에서 세미조인이라고도 한다.
- 예전엔 끔찍하게 비효율적이었다. 서브쿼리 테이블에 있는 모든 레코드를 외부 쿼리에서 꺼낸 레코드와 비교했기 떄문에.. (n x m)
- 그래서, 대체로 서브쿼리 내의 테이블과 외부 쿼리의 테이블간의 실제 조인으로 바꿔서 처리하는 것을 골자로 하는 여러 방식으로 최적화되어있음(from 9.3.1.9)
- not in 비교
- in 비교의 반대로, 안티 세미 조인이라고도 한다.
- 최적화 가능한 방법이 그다지 많지 않다. where에 단독으로 있으면 무조건 풀 테이블 스캔함.

CTE

이름을 갖는 임시 테이블. SQL 문장 내에서 with절을 통해 한 번 쓸 수 있고, 문장 종료와 함께 삭제된다.
- 한 SQL 문장에서 임시 테이블을 여러개 만드는 건 가능하다. 하지만 "한 번"만 된다.
한번 만들어두고 쿼리 내에서 반복적으로 재사용 가능하므로, 일반적으로 FROM 서브 쿼리보다 효율적이다.
CTE 테이블끼리 서로 참조하는 것도 가능하다.
임시 테이블 생성과 데이터 이용 부분을 분리 가능하므로 가독성이 높다.
재귀적 CTE
- 비 재귀적 쿼리와 재귀적 쿼리로 구분되며, 이 둘을 UNION으로 연결하는 형태로 작성된다.
- 비 재귀적 쿼리가 처음 실행되면서 임시 테이블을 만들고, 이후 쿼리의 결과가 없을 때 까지 재귀적 쿼리가 실행된다.
- 즉, 비재귀적 쿼리 파트에서 임시 테이블의 구조를 설정하고, 재귀 쿼리에서 테이블에 내용을 채우는 것이다.
- 그리고 임시테이블엔 최종적으로 쿼리 결과가 없어지기 직전의 테이블이 남는다.
- 무한 루프 방지턱으로 "최대 반복 횟수" 시스템 변수가 있다.

윈도우 함수

행과 행 사이의 관계에 대한 처리를 하는 함수. 결과값을 내기 위해 여러 행을 봐야되는 계통. 대표적으로 순위를 매기는 RANK()
- 왜 이름이 윈도우 함수인진 모르겠다. 행에서 다른 행을 보는 처리가 창문 너머 보는거 같아서인가?
(where/from/group by/having) => 윈도우 함수 => (select/order by/limit) 순으로 처리.
<함수 호출> over(파티션)
인덱스에 의한 최적화를 잘 활용하지 못하므로 너무 의존하진 않는게 좋다. 소량의 레코드에 대한 처리면 성능에 대해 고민할 필요는 없음

잠금을 활용하는 SELECT

FOR SHARE(읽기)나 FOR UPDATE(쓰기)를 써서 잠금걸고 SELECT할 수 있다.
- 쿼리한 정보를 어플리케이션에서 가공하고자 할 때 사용하면 됨.
NOWAIT이나 SKIP LOCKED를 써서 잠겨있는 레코드에 대한 접근을 영영(혹은 오랫동안) 기다리는 상황을 막을 수 있다.
- NOWAIT은 접근하는 레코드가 잠겨있으면 에러를 반환하고 즉시 종료한다.
- SKIP LOCKED는 접근하는 레코드가 잠겨있으면 무시하고, 잠겨있지 않은 레코드만 반환한다.
- SKIP LOCKED는 그 특성상 쿼리가 결정론적이지 않다.

INSERT

IGNORE 옵션으로, 레코드 도중 삽입에 문제가 생길 경우 해당 레코드를 경고만 남기고 스킵 후 나머지를 처리하도록 할 수 있다.
- 느슨하게 처리해도 되는 트랜잭션인 경우 좋음
- 면밀하게 확인하고 써야 한다. 트랜잭션의 고립성과 원자성을 스스로 깨는 꼴이 될 수도 있음.
ON DUPLICATE KEY UPDATE 옵션은 중복 키 삽입인 경우 삽입하는 값으로 UPDATE 처리를 하게 해준다.
INSERT의 성능은 쿼리문보다는 테이블 스키마에 의해 더 영향을 많이 받는다.
- 프라이머리 키로 정렬된 레코드를 집어넣는게 그렇지 않은 경우보다 훨씬 빠르다.(메모리 캐시 특성상)
- 특정 레코드에 대한 검색보다는 INSERT를 많이 처리하는 테이블이라면, 단조 증가/감소하는 키를 쓰는게 좋다.
- INSERT를 많이 쓰는 테이블이라면 세컨더리 인덱스 갯수를 최소화하는게 좋다

UPDATE와 DELETE

ORDER BY ~ LIMIT ~를 이용하면 부하를 감당할 수 있는 범위 내에서 조금씩 업데이트하는걸 쉽게 만들 수 있다.

minkukjo commented 2 years ago

서브쿼리

서브쿼리는 SELECT, FROM, WHERE 에섬나 사용될 수 있다.
사용되는 곳에 따라 영향도나 최적화 방식이 완전히 다르다.

SELECT

SELECT 절에 사용된 서브쿼리는 임시테이블을 만들거나 쿼리를 비효율적으로 만들지는 않으므로 서브쿼리가 적절히 인덱스를 탄다면 크게 주의할 부분은 없음.
다만 SELECT 절에서의 서브쿼리 응답값은 레코드가 1개여야만 한다.
1건 이상일 경우, 또는 두개의 컬럼을 가져오려는 경우는 에러가 발생한다.
SELECT 서브쿼리는 로우 서브쿼리를 사용할 수 없고 스칼라 서브쿼리만 사용가능하다.
로우 서브쿼리 : 반환 레코드와 컬럼 수가 2개 이상
스칼라 서브쿼리 : 반환 레코드와 컬럼수가 한 개인 경우

FROM

5.7 이전 버전 까지는 FROM의 서브쿼리는 항상 임시 테이블로 저장하고 읽는 방식을 사용했음
그러나 5.7 부터는 옵티마이저가 FROM의 서브쿼리를 외부 쿼리로 병합하는 최적화를 한다.
다만 FROM 절의 서브쿼리에서 아래의 기능을 사용하면 외부 쿼리로 병합되는 최적화가 안된다.
- 집합 함수 (SUM,MIN,MAX 등)
- DISTICNT
- GROUP BY OR HAVING
- LIMIT
- UNION OR UNION ALL
- SELECT 절에 서브쿼리가 있을 때
- 사용자 변수 사용

WHERE

WHERE는 크게 동등, IN, NOT IN에서 서브쿼리를 사용할 수 있다.

WHERE 동등 비교

동등 비교의 경우 서브쿼리에서 사용될 수 있는 인덱스로 처리한 후 그 결과를 이용해 외부 쿼리의 인덱스를 태워서 검색을 한다.
즉 동등 비교에서 서브쿼리로 인덱스를 탈 수 있게 설계하면 좋은 성능을 내는 쿼리를 만들 수 있다.

WHERE IN 비교

실제 조인은 아니지만 테이블의 레코드가 다른 테이블의 레코드를 이용한 표현식과 일치하는지 체크하는 형태를 세미 조인이라 부른다.
WHERE 절에 사용된 IN 형태의 조건을 조인의 한 방식인 세미 조인이라고 부른다.
IN 쿼리는 9.3.1.9절에도 나오지만, 알아서 세미조인 최적화를 통해 조인으로 쿼리를 변환시켜 질의를 수행한다.

WHERE NOT IN 비교

IN과 비슷한 형태지만 여기서는 안티 세미 조인이라 부른다.
RDBMS에서 Not Equal이 인덱스를 제대로 활용할 수 없듯이, 세미조인 또한 최적화 할 수 있는 방법이 많지 않다.
WHERE 절에 단독 안티 세미조인만 있으면 풀 테이블 스캔을 피할 수 없으니 주의하자.

CTE ( Common Table Expression )

이름을 가지는 임시 테이블로서, SQL 문장 내에서 한 번 이상 사용될 수 있으며 SQL 종료 즉시 CTE 임시 테이블은 제거된다.
CTE는 재귀적 반복 실행 여부를 기준으로 비 재귀적 / 재귀적 CTE로 나뉜다.

Non Recursive CTE

WITH 절을 사용해 CTE를 정의할 수 있다.
CTE의 장점은 아래와 같다.
- CTE 임시 테이블은 재사용이 가능하므로 FROM 절의 서브쿼리보다 효율적이다.
- CTE로 선언된 임시 테이블은 다른 CTE 쿼리에서도 참조할 수 있다.
- CTE는 임시 테이블의 생성과 사용 부분의 코드를 분리할 수 있으므로 가독성이 높다.

Resursive CTE

DB 업계에서 쓰이는 용어로 Win Back 프로젝트라는게 있다고 한다. 어원은 초기에 MySQL을 검토했으나 다른 DB (오라클, PostgreSQL)을 사용하다가 MySQL이 다시 보니 선녀같아서 (?) MySQL로 마이그레이션 하는 작업을 의미한다.
재귀적 CTE는 이전 재귀 쿼리의 실행 결과를 갖고 최종 산출물을 만들어낸다.
흥미롭게도 이 재귀적 쿼리의 반복 종료 조건은 꼭 명시하지 않아도 된다.
단지 재귀 쿼리의 결과가 0건일 때 까지만 반복된다.
물론 작성자의 쿼리 실수로 인해 무한 루프가 발생할 수도 있으므로, MySQL 서버에는 cte_max_recursion_depth 시스템 변수를 이용해 최대 반복 실행 횟수를 설정할 수 있으며 기본 값은 1000이다.
그럼 이 쿼리는 언제 써먹을 수 있을까? 트리 구조에서 써먹어볼 수 있다.
가령 어떤 직원의 상위 조직장을 찾는 쿼리를 작성할 때 RECURSIVE CTE로 해당 조직원의 상위 조직장들을 재귀적으로 찾은 뒤 그 조직장들의 정보를 보여주는 쿼리도 CTE 쿼리로 작성해볼 수 있다.

Window Function

Group By가 집계된 결과만 출력해준다면, 윈도우 함수는 기존 데이터에 추가 컬럼으로 집계 결과까지 같이 보여주는 기능이다.
where, from, group-by, having => window fuction => select, oder-by, limit
윈도우 함수의 최적화 능력은 아직 구리다.
권도님과 마찬가지로 이 기능의 이름이 왜 window fuction일까 궁금해서 검색해봤다.
완전 똑같은 질문이 있었다 ㅋㅋ
본 답글에 따르면 아래와 같은 이유로 윈도우 함수라 불리운다고 한다. (권도님 똑똑하신 듯...)

Window functions operate on a range of rows, defined by the OVER clause. It is like you are looking through the window, you see sky, star, moon. You are not seeing the whole sky, you see part of the sky.

윈도우 함수는 OVER절에 의해 정의된 레코드의 범위에서 동작한다. 
그것은 마치 당신이 창을 통해 하늘, 달, 별을 보는 것과 같다.
당신은 그저 하늘의 한 부분을 보는 것이며 하늘의 전체를 보고 있는 것은 아니다.

OVER 절에 정의된 파티션(윈도우)들을 통해 각각의 레코드가 집계 결과를 볼 수 있게 되는 기능 자체를 마치 창문을 통해 하늘과 달, 별을 보는 것과 같다고 하여 윈도우 펑션이라 이름지어진 것 같다. (갬성 무엇..??)

잠금을 사용하는 SELECT

InnoDB에서는 MVCC를 공부할 때도 나왔지만 조회할 때 레코드에 잠금을 걸지 않는다.
근데 필요에 따라서는 레코드를 읽으면서 강제로 잠금을 걸어야할 때도 있는데 이 때 FOR SHARE와 FOR UPDATE를 사용해볼 수 있다.
FOR SHARE는 읽기 잠금, FOR UPDATE는 쓰기 잠금을 건다.
또한 8버전에는 NOWAIT 옵션과 SKIP LOCKED라는 새로운 기능도 생겼다.
NOWAIT은 FOR UPDATE 끝에 NOWAIT 옵션을 쿼리문에 추가할 수 있는데, 이러면 해당 레코드가 다른 트랜잭션에 의해 잠긴 경우 즉시 에러를 반환하며 쿼리를 종료하는 기능이다.
SKIP LOCKED의 경우 SELECT 하려는 레코드가 다른 트랜잭션에 의해 잠겨진 상태면 에러를 반환하지 않고 잠긴 레코드는 무시하고, 잠기지 않은 레코드만 가져오는 기능이다.
NOWAIT은 실무에서 써볼만한데, SKIP LOCKED는 실무에서 쓸만한 상황이 떠오르진 않는다.
라고 생각했는데 글 마지막에 쿠폰 발급 예제를 통한 SKIP LOCKED를 보여주는데 이거 완전 히트라는 생각이 들었다.
만약 동시성 처리가 필요한 경우라면 이 기능을 사용해봐도 좋을 듯 하다!
NOWAIT과 SKIP LOCKED는 FOR UPDATE 구문에서만 사용할 수 있으며 당연히 UPDATE나 DELETE에선 사용할 수 없다.
그럼 왜 UPDATE에선 DELETE에선 이 기능을 막아뒀을까?
NOWAIT과 SKIP LOCKED가 DELETE나 UPDATE에서 실행되면 데이터베이스의 상태를 다른 결과로 만들어버리게 된다.
UPDATE나 DLETE는 수행됐는데, 어떤 레코드가 업데이트되거나 삭제됐는지를 알 수 없게 되어버린다.
즉 UPDATE나 DELETE 쿼리들의 멱등성 보장이 안된다.
또한 MySQL 복제에서는 더 큰 문제를 일으킬 수도 있다.
예를 들어, Master에서는 SKIP LOCKED를 사용했고 트랜잭션 경합 상태로 인해 3개만 업데이트가 됐는데, Slave에서는 5개가 업데이트 된다던지 하는 상황도 충분히 발생할 수 있다.

INSERT

INSERT는 주로 쿼리 자체에 성능 저하보다는 테이블 구조에 따라 성능이 좌우되는 질의다.
INSERT에서 사용할 수 있는 고급 옵션이 두 가지인데, 하나는 INSERT IGNORE이고 하나는 INSERT ... ON DUPLICATE KEY UPDATE이다.

INSERT IGNORE

INSERT 하려는 레코드의 PK나 Unique Index 컬럼의 값이 이미 테이블에 존재하는 레코드와 중복되는 경우, 무시하고 레코드를 처리할 수 있게 해준다.
INSERT하고자 하는 데이터가 정교하지 않아도 된다면 레코드의 중복체크를 수행하지 않고 동일한 데이터를 집어넣을 수 있다.
근데 이런게 되면 테이블 무결성이 깨질 수 있는 무서운 기능같아보여서 안쓰는게 좋을 듯 하다.

INSERT .. ON DUPLICATE KEY UPDATE

실무에서 종종 많이 쓰는 기능이다.
쉽게 말해 UPSERT, PK나 유니크 인덱스에 의해 중복이 발생하면 중복된 대상 레코드의 특정 컬럼 값을 업데이트하는 방식이다. (replace into와는 다르게 레코드를 지우지 않고 업데이트한다. replace는 절!대! 쓰면 안되는 쿼리이지만... 예전에 회사에 이런 쿼리가 있었다.)

LOAD DATA

RDBMS에서 데이터를 빠르게 적재하는 방법으로 LOAD DATA가 자주 소개된다고 한다. (처음 들어봄)
INSERT와 비교했을 때 매우 빠르지만, MySQL의 경우 단일 트랜잭션과 단일 스레드로 이 명령이 실행되서 데이터가 아주 많으면 꽤나 느리다고 한다.
이거 하는 것 보단, 그냥 INSERT를 PK 별로 짤라서 여러 번에 걸쳐서 INSERT 하는게 성능상 더 낫다고 한다. (뭐야...;;)

성능을 위한 테이블 구조

실무를 하다보면 대량의 데이터를 INSERT하는 일이 종종 있다.
INSERT의 성능을 높이고자 INSERT 구문을 튜닝해보려 하지만, 사실 INSERT 구문 자체의 튜닝 포인트는 별로 없다.
그래서 해볼 수 있는건 테이블 구조를 좀 예쁘게 가져가는 방법이 있다.
테이블 내의 세컨더리 인덱스가 많으면 많을 수록, 테이블이 커지면 커질 수록 INSERT 성능도 떨어지기 때문에, 세컨더리 인덱스의 크기를 작게 가져가는 것도 INSERT 성능을 높이는 포인트가 될 수 있다.
SELECT 보다 INSERT에 최적화된 테이블을 만드려면 단조 증가, 감소되는 값으로 PK를 선정하고, 세컨더리 인덱스를 최소화하는 방법이 있다.
자동증가 PK를 설정하면 MySQL 서버에서 가장 빠른 INSERT를 보장해줄 수 있다.

UPDATE와 DELETE

UPDATE와 DELETE는 주로 하나의 테이블에 대해 한 건 또는 여러 건의 레코드를 변경/삭제 하기 위해 사용된다.
하지만 MySQL 서버에서는 여러 테이블을 조인해 한 개 이상 테이블의 레코드를 변경하거나 삭제하는 기능도 존재한다.
JOIN UPDATE와 JOIN DELETE가 그것이다.

UPDATE ... ORDER BY ... LIMI n

조금씩 업데이트할 때 사용 ( DB 부하 최소화)

JOIN UPDATE

두 개 이상 테이블을 조인한 결과 레코드를 변경/삭제 하는 쿼리

update test a t1, user b u
   set t1.first_name=u.first_name
   where t1.user_id=u.user_id;

여러 레코드 UPDATE

8버전부터 Row Constructor라는 문법을 사용해 레코드별로 서로 다른 값을 업데이트할 수 있다는데... 딱히 쓸 것 같진 않다;

JOIN DELETE

DELETE e
FROM employee e, dept_emp de, departments d
WHERE e.~~~~~

DELETE와 FROM 절 사이에 삭제 대상이 될 타겟 테이블의 alias만 명시함으로써 저 테이블만 지울 수 있다.
물론 저기에 e,de,d 이런식으로 여러개도 가능.

DaehunGwak commented 2 years ago

11.4 SELECT

11.4.10 서브쿼리

SELECT 절 서브쿼리
- 임시테이블을 만들거나 비효율적으로 만드는 부분 없음
- 칼럼과 레코드가 하나인 결과를 반환해야 함 (스칼라 서브쿼리)
- 0건이면 null로 처리
- 해당 서브쿼리가 조인으로 처리 가능한 서브쿼리라면 조인 사용권장
- 동일한 쿼리인데 칼럼만다른 형태가 반복사용된다면 래터럴 조인 사용 (대신 임시테이블 사용)
FROM 절 서브쿼리
- 5.7부터 해당 서브쿼리는 외부쿼리와 병합하는 것으로 옵티마이징 함 (모든것이 그런건 아님)
  - 예외 사항: 집합 함수, DISTINCT, GROUP BY(HAVING), LIMIT, UNION*, SELECT 절 서브쿼리 사용, 사용자 변수 사용
WHERE 절 서브쿼리
- 동등 또는 크다 작다 비교
  - 서브쿼리 실행 후 외부 쿼리 실행
  - 상수가 아닌 튜플 비교라면 테이블 풀 스캔 일어나니 주의
- IN
  - 세미조인 select * from table1 t1 where t1.id in (select t2.id from table2 t2)
    - 최적화 전략: 테이블 풀-아웃, 퍼스트 매치, 루스 스캔, 구체화(Materialization), 중복제거(duplicated weed-out)
- NOT IN
  - 안티 세미 조인
    - NOT EXISTS, 구체화로 최적화
  - 최적화 방법도 그리 좋은 방법은 아니니 사용 지양

11.4.11 CTE(Common Table Expression)

이름을 가지는 임시 테이블
한 쿼리 내에서 여러번 사용될 수 있음
쿼리가 끝나면 삭제
재귀적 반복 실행여부를 기준으로 Non-recursive/Recursive CTE 로 나뉨
각 쿼리의 제일 앞, 서브쿼리 제일 앞, SELECT 절의 바로 앞에서 사용 가능
Non-recursive CTE
- WITH cte1 AS (SELECT * FROM departments) SELECT * FROM cte1;
- 다른 cte 테이블 쿼리에서 선행으로 만들어진 cte 테이블 사용 가능
- 장점
  - 재사용 가능하므로 FROM 절의 서브쿼리에 반복이 있다면 효율적으로 개선 가능
  - 다른 CTE 쿼리에 재사용 가능
  - CTE 생성과 사용 부분의 코드를 분리할 수 있으므로 가독성 높음

Recursive CTE (8.0 부터)

WITH RECURSIVE cte (no) AS (
  SELECT 1
    UNION ALL
    SELECT (no + 1) FROM cte WHERE no < 5
)
SELECT * FROM cte;

사용되는 경우가 있을까..? 기능만 알아두자

11.4.12 윈도우 함수 (Window Function) (8.0+)

조회하는 현재 레코드 기준 집합 연산, 레코드 건수는 GROUP BY 처럼 변하지 않음
처리 순서
1. 레코드 결정 (WHERE, FROM, GROUP BY, HAVING)
2. 윈도우 함수 처리
3. 이후 실행 (SELECT 절, ORDER BY, LIMIT)
따라서 윈도우 함수는 WHERE, FROM, GROUP BY 사용 불가능함을 알 수 있음
기본 사용법
- AGGREGATE_FUNC() OVER(<partition> <order> <frame>) AS window_func_column
```
// ex
SELECT e.*, RANK() OVER(ORDER BY e.hire_date) AS hire_date_rank
FROM employees e;
```
frame: 각 파티션 안에서 연산 대상 레코드 별 연산을 수행할 소그룹 (함수 대상 범위)
- frame: {ROWS | RANGE} {frame_start | frame_between}
- frame_between: BETWEEN frame_start AND frame_end
- 프레임 만드는 기준
  - ROWS : 레코드의 위치를 기준으로 프레임 생성
  - RANGE : ORDER BY 에 명시된 칼럼 기준, 값의 범위로 프레임 생성
- frame_start, frame_end
  - CURRENT ROW: 현재 레코드
  - UNBOUNDED PRECEDING: 파티션의 첫 번째 레코드
  - UNBOUNDED FOLLOWING: 파티션의 마지막 레코드
  - expr PRECEDING: 현재 레코드로부터 n번째 이전 레코드
  - expr FOLLOWING: 현재 레코드로부터 n번째 이후 레코드
윈도우 함수 성능
- 아직 최적화가 덜된 부분이 많아 존재한다의 의미만 알고 있으면 될 듯

11.4.13 잠금을 사용하는 SELECT

FOR SHARE, FOR UPDATE 를 이용해 트랜잭션 내 SELECT 할때 해당 레코드에 대해 잠금을 걸고 DML 쿼리를 날려 잠금 해제하는 식으로 갈 수 있음 (8.0+)
FOR SHARE: 읽은 레코드에 대해 읽기 잠금(shared lock), 다른 트랜잭션에서 읽기 가능/ 쓰기 불가
FOR UPDATE: 읽은 레코드에 대해 쓰기 잠금(exclusive lock), 다른 트랜잭션에서 읽기/쓰기 불가
SELECT * FROM employees WHERE emp_no=10001 {FOR SHARE | FOR UPDATE}
8.0 이전에는 LOCK IN SHARE MODE 문법으로 지원
InnoDB 는 논블록킹 리드가 가능하기 때문에 일반 SELECT 문은 바로 결과를 반환한다
8.0 부터 선택적 잠금 가능

SELECT *
FROM employees e
    INNER JOIN dept_emp de ON de.emp_no=e.emp_no
    INNER JOIN departments d ON d.dept_no=de.dept_no
WHERE e.emp_no=10001
FOR UPDATE OF e; // employees 테이블만 쓰기 잠금 획득

NOWAIT & SKIP LOCKED (8.0+)
- SELECT 마지막에 NOWAIT 옵션으로 해당 레코드가 락이 걸려있을 시 기다리지 않고 즉시 에러 반환
- SKIP LOCKED 옵션은 잠긴 레코드는 가져오지 않고 나머지 레코드만 가져옴
- UPDATE, DELETE 에는 사용 불가 → UPDATE, DELETE 는 대상 레코드에 대해 확정적인 결과만을 줘야하기 때문

11.5 INSERT

11.5.1 고급 옵션

INSERT IGNORE

프라이머리 키, 유니크 인덱스 값이 이미 테이블에 있다면 or 칼럼 값이 호환되지 않는다면 해당 경우를 무시하고 다음 레코드를 처리할 수 있게 함
이 옵션이 없으면 에러가 나지만, 옵션을 붙이면 경고로만 표시하고 다음 레코드 처리

INSERT … ON DUPLICATE KEY UPDATE

프라이머리 키, 유니크 인덱스 값이 이미 테이블에 있다면 업데이트를 수행
VALUES 함수는 8.0.20 이후에 없어질 예정이므로 다른 문법으로 권장

11.5.2 LOAD DATA 명령 주의 사항

스토리지 엔진이 데이터 파일을 직접 적재해 INSERT 비해 빠르긴 함
MySQL 서버의 LOAD DATA 단점 → 다른 온라인 트랜잭션에 영향을 미칠 수 있음
- 단일 스레드로 실행
- 단일 트랜잭션으로 실행
적재할 데이터 파일이 크다면 여러개로 나누는 것을 권장
테이블 간 복사 작업이라면 프라이머리 키 같은 것으로 INSERT 배치를 나눠 돌리는 것을 권장

11.5.3 성능을 위한 테이블 구조

테이블 복사 시 프라이머리키 정렬 후 진행
- 하지 않으면 B-TREE 페이지 랜덤 엑세스 및 재정렬에 많은 비용이 소모될 수 있음
세컨더리 인덱스가 많아지면 SELECT 성능은 높아지겠지만 INSERT 성능은 감소될 수 밖에 없음
프라이머리 키 선정 은 SELECT와 INSERT 성능이 대립되므로 테이블의 목적에 맞게 선정해야 함
- 로그 테이블 같은 INSERT 가 많이 일어나는 테이블은 프라이머리 키를 단조 증가, 감소 하는 패턴으로 가져가는 것이 좋음 (auto-increment 같은)
- 그 밖에 온라인 트랜잭션에 많이 접근하는 테이블 같이 읽기가 발생하는 패턴의 테이블은 SELECT 가 많이 접근 조건을 기준으로 프라이머리 키를 선정
INSERT가 상대적으로 많이 일어나는 테이블은 인덱스 개수를 최소화

11.6. UPDATE, DELETE

UPDATE … ORDER BY … LIMIT n

정렬하여 상위의 n 건만 업데이트
복제 소스 서버와 레플리카 서버간의 순서가 다를 수 있으니 주의

JOIN UPDATE

여러개의 테이블을 조인해 조인된 결과 레코드를 변경
읽기 용으로만 조인 된 테이블은 읽기 잠금, 업데이트 대상이 되는 테이블은 쓰기 잠금이 걸림

UPDATE tb_test1 t1, employees e
    SET t1.first_name=e.first_name
WHERE e.emp_no=t1.emp_no;

여러 레코드 UPDATE

8.0 부터 Row Constructor 문법을 이용해 레코드 별로 서로 다른 값을 업데이트할 수 있게 됨
사용하는 경우는… 모르겠다

UPDATE user_level ul
    INNER JOIN (VALUES ROW(1, 1), ROW(2, 4)) new_user_level (user_id, user_lv) # 임시 테이블 생성
        ON new_user_level.user_id=ul.user_id
SET ul.user_lv=ul.user_lv + new_user_level.user_lv;

JOIN DELETE

여러 개의 테이블의 레코드를 한꺼번에 삭제 가능

DELETE e, de
FROM employees e, dept_emp de, departments d
WHERE e.emp_no=de.emp_no AND de.dept_no=d.dept_no AND d.dept_no='d001';

DaehunGwak / study-real-mysql-2

2. 11_쿼리 작성 및 최적화 (11.4.10 서브쿼리 ~ 11.6 UPDATE와 DELETE) #6

진도

일시

서브쿼리

CTE

윈도우 함수

잠금을 활용하는 SELECT

INSERT

UPDATE와 DELETE

서브쿼리

SELECT

FROM

WHERE

WHERE 동등 비교

WHERE IN 비교

WHERE NOT IN 비교

CTE ( Common Table Expression )

Non Recursive CTE

Resursive CTE

Window Function

잠금을 사용하는 SELECT

INSERT

INSERT IGNORE

INSERT .. ON DUPLICATE KEY UPDATE

LOAD DATA

성능을 위한 테이블 구조

UPDATE와 DELETE

UPDATE ... ORDER BY ... LIMI n

JOIN UPDATE

여러 레코드 UPDATE

JOIN DELETE

11.4 SELECT

11.4.10 서브쿼리

11.4.11 CTE(Common Table Expression)

11.4.12 윈도우 함수 (Window Function) (8.0+)

11.4.13 잠금을 사용하는 SELECT

11.5 INSERT

11.5.1 고급 옵션

INSERT IGNORE

INSERT … ON DUPLICATE KEY UPDATE

11.5.2 LOAD DATA 명령 주의 사항

11.5.3 성능을 위한 테이블 구조

11.6. UPDATE, DELETE

UPDATE … ORDER BY … LIMIT n

JOIN UPDATE

여러 레코드 UPDATE

JOIN DELETE