Ch11. 쿼리 작성 및 최적화 - 2

Question

Ch11. 쿼리 작성 및 최적화 - 2

Opened this issue 2 years ago · 5 comments

kihyuk-sung commented 2 years ago

148p insert 전 까지

Answer 1 · 2022-06-24T14:36:36.000Z

LIMIT
78p 3번째 쿼리는 카디널리티가 10보다 작으면 모든 row 를 읽어야 한다.
4번째 쿼리는 일부만 정렬한다는 것에 동의하지 않는다. 정확하게는 WHERE 절에 해당하는 row 를 조회한 후, 정렬을 수행한 다음에 LIMIT 을 실행할 것이다.
페이징
WHERE salary >= 38864 AND NOT (salary=38864 AND emp_no <= 274049)
위 조건에서 NOT 을 풀어쓰면 다음과 같다.
WHERE salary >= 38864 AND (salary !=38864 OR emp_no > 274049)
이거 index 사용하는지 확인 해야함.
OUTER JOIN
기본적으로 WHERE 조건에 OUTER JOIN이 되는 테이블에 대한 조건이 들어가면 INNER JOIN 으로 최적화 한다. (조건이 참이 되려면 null 인 경우를 제외 -> JOIN 이 되는 row 만 확인 -> INNER JOIN)
ANTI JOIN 인 경우는 조건절로 추가 가능
지연된 조인
조인을 바로 한다면, 10만건 쿼리에 대해서도 JOIN 을 하게 될 수 있음 -> 주요 조건으로 먼저 LIMIT 으로 제한한 다음에 JOIN 을 LIMIT 한 만큼만 수행한다.
WITH ROLLUP
group by 에 대한 전체 통계 기능
ORDER BY
https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html
https://dev.mysql.com/doc/refman/8.0/en/order-by-optimization.html
여러 방향 정렬 (8.0 부터 가능)
CTE
- 재귀적
- 비재귀적
  CTE 임시 테이블은 재사용 가능하여 효율적일 수 있다.
WINDOW 함수
GROUP BY 와 비교했을 때 집계 결과만을 얻을수 있는것과 row 별로 집계 결과를 얻을 수 있는 것의 차이
이때 OVER 내에 범위를 지정할 수 있다 (ex. 해당 row 에서 1년 전까지의 집계 등)
잠금을 사용하는 SELECT
FOR SHARE (읽기 잠금) / FOR UPDATE (쓰기 잠금)
기본적으로 잠금이 없는 SELECT 는 InnoDB 에서는 아무 제약없이 바로 조회 가능하다.
NO WAIT 와 SKIP LOCKED 로 잠금을 우회 가능하다.

Answer 2 · 2022-06-24T23:17:33.000Z

72p short circuit evaluation

WHERE 절에 AND 와 OR로 연결된 조건들에서 인덱스를 활용하여 효율적으로 데이터의 범위를 줄일 수 있는 것부터 먼저 실행하도록 판단
예를 들어 ix_B가 있고 SELECT * FROM table WHERE A AND B 가 있을 떄 B를 먼저 찾아보도록 판단

78p limit

limit은 쿼리에서 필요한 레코드 건수만 준비되면 즉시 쿼리 종료
LIMIT 20000, 10 이면 2만10건의 레코드를 읽는다
- Q1)
- 저번에 페이징 이야기하면서 뒤로갈수록 느려진다고 했고 다들 자연스럽게 수긍하는 분위기였는데
- 이것이 페이지 뒤로 갈수록 느려지는 일반적인 원인?
- 84p "물론 게시물을 가져오는 두번째 쿼리도 다음 페이지로 넘어가면 갈수록 성능이 더 느려질 가능성이 높다"
  - Q2) 84p에서 이전과 다음 버튼만 표시하는 방식을 검토해보라는데 무슨 의미?

83p count()

count()의 *은 전체 컬럼의 레코드를 읽는게 아니라 그냥 레코드 자체를 의미
count()의 컬럼이나 표현식은 그 컬럼이나 표현식이 NULL이 아닌 레코드 건수만 반환

85p join 순서와 인덱스

Q3) 어느쪽에 인덱스가 있냐에 따라서 드라이빙,드리븐 테이블이 바뀌고 풀스캔을 피한다는 말이 이해가 안감
- 예를 들어 SELECT * FROM employees e, dept_emp de WHERE e.emp_no=de.emp_no
- e.emp_no에 인덱스가 있으므로 빠르게 결과를 먼저 가져온다, 즉 드라이빙 테이블
- 그 빠르게 들고온 결과에 맞춰서 de.emp_no을 매칭하므로, 드라이븐 테이블이 된다
- 라고 생각이 드는데 왜 반대일까?

90p

데이터 모델링할때 테이블 간의 관계를 넣는다는 것은 외래키가 있다는 것을 무조건적으로 암시한다고 생각했는데 그게 아니었다

91p 지연된 조인

Q4) 왜 salaries 테이블 조인 수행한 횟수가 3000 * 4 번인가? salaries 레코드 갯수만큼 찍혀야하는 거 아닌가?

99p 레코드를 컬럼으로 변환

한줄로 꺼내고 싶을때 저렇게하지못하고 프로그래밍적으로 map 같은데 담아서 어떻게 해결했던거 같다, 단순히 데이터분류하는 작업이니까 SQL로 하는게 책임소재가 나누어져 좋을것같다

101p order by

order by가 없는게 자동 asc 라고 착각했다. innodb경우엔 항상 pk로 클러스터링돼 있기 때문에 기본적으로 pk 순서대로 레코드를 가져온다

100~120p 정도쯤

CTE, 윈도우 함수는 넘 어려워서 패스했습니다

Common Table Expression가 모지: VIEW같은 임시테이블. 대신 CTE는 쿼리 안에서 딱 한번 생성됨. 복잡한 쿼리에서 가독성향상. 여러번 참조 하기 위함. 재귀 쿼리( Q5) 무슨 뜻? )

윈도우 함수가 뭐지: 행과 행의 연산을 목적으로 하는 함수

146p select for update 쿠폰 예시

진짜 그냥 단순히 lock 걸린걸 패스만 하는것으로 엄청난 성능향상을 한거처럼 보이겠다
Q6) 레디스를 활용한다면 어떻게 할 수 있다는 것일까?

147p 주의

"자신의 어플리케이션이 겪는 문제의 원인을 제대로 분석하지 못하고, 애플리케이션 코드는 그대로 놔두고 MySQL 서버가 느려서 트랜잭션이 느려진다고들 한다" 뭔가 저자의 고충 같은 느낌이다 ㅋㅋ

Answer 3 · 2022-06-25T00:57:30.000Z

LIMIT - 77p

필요한 레코드 건수만 준비되면 쿼리 즉시 종료
- GROUB BY 절이 있을 경우, 처리가 완료 되고 나서야 LIMIT 처리를 수행
LIMIT의 인자로 표현식이나 별도의 서브쿼리를 사용할 수 없다.
LIMIT 조건의 페이징이 처음 몇 개 페이지 조회로 끝나지 않을 가능성이 높다면
- WHERE 조건절로 읽어야 할 위치를 찾고 그 위치에서 읽는 형태의 쿼리를 사용하는 것이 좋다.
- ex) ... LIMIT 200000, 10

COUNT - 82p

COUNT(*)의 *는 레코드 자체를 의미
주의. InnoDB 스토리지 엔진을 사용하는 테이블에서는 COUNT(*) 쿼리라고 해도 직접 데이터나 인덱스를 읽어야만 레코드 건수를 가져올 수 있음.
COUNT(*) 쿼리에서는 ORDER BY 필요 없음. 제거하는 게 성능에 좋다.

JOIN - 90p

외래키는 조인과 아무런 연관이 없다.
- 외래키를 생성하는 주목적은 데이터의 무결성 보장

지연된 조인

조인이 실행되기 이전에 GROUP BY나 ORDER BY를 처리하는 방식을 의미
LIMIT이 함께 사용된 쿼리에서 더 큰 효과를 얻을 수 있음
모든 조인을 지연할 수 있는 건 아니고 조건이 있음. (93p)

ORDER BY - 102p

ORDER BY 절은 정렬할 대상 칼럼명, 표현식, _칼럼의 순번_으로 명시 할 수 있다.
문자열 상수가 올 경우 옵티마이저가 무시

FROM 절에 사용된 서브쿼리, 뷰 - 109p

서브 쿼리의 결과를 항상 임시 테이블로 저장하고 다시 임시 테이블을 익는 방식으로 처리
MySQL 5.7부터는 서브쿼리, 뷰를 외부 쿼리로 병합하는 최적화 수행

CTE(Common Table Expression) - 114p

SQL 문장 내에서 이름을 가지는 임시 테이블

Recursive CTE

CTE를 이용한 재귀쿼리
8.0 부터 지원

윈도우 함수 - 137p

MySQL 서버의 윈도우 함수는 8.0 버전에 처음 도입, 인덱스를 이용한 최적화 부족
윈도우 함수는 조회하는 현재 레코드를 기준으로 연관된 레코드 집합의 연산을 수행한다.
GROUP BY와 달리 결과 집합을 그대로 유지

잠금을 사용한 SELECT - 139p

레코드를 읽으면서 강제로 잠금을 걸어두는 방식
- FOR UPDATE, FOR SHARE(= LOCK IN SHARE, 8.0 이전버전)
주의할 사항
- InnoDB를 사용하는 테이블은 잠금 없는 읽기가 지원되기 때문에 잠금 상태더라도 쿼리를 아무런 대기 없이 실행할 수 있다.
선택적 잠금, NOWAIT & SKIP LOCKED
- 8.0부터 가능함
SKIP LOCKED: 잠긴 레코드는 무시하고 잠금이 걸리지 않은 레코드만 가져온다.

Answer 4 · 2022-06-25T01:23:32.000Z

Limit

n, m 수치 매우 커질 때 쿼리 수행 시간 높

Where 절로 읽어야할 위치 찾고 그 위치에서 10개만 읽는 형태로 쿼리 작성

Count

count() 함수에 컬럼명이나 표현식이 인자로 사용되면 결과가 NULL이 아닌 레코드 건수만 반환한다
게시물 페이지 번호를 보여주는 방식보다는 이전, 다음 버튼만 표시하는 방식 검토

JOIN

보통은 인덱스 있는 테이블이 드리븐으로 선택
인덱스 없는 테이블이 드리븐이 될 경우, 드라이빙(앞) 테이블의 레코드 건수만큼 드리븐 테이블 풀스캔해야하기 때문
Q?
- 드리븐 최적으로 읽을 수 있게 실행 계획 수립
- 두 컬럼 모두 인덱스가 없는 경우, 레코드 건수가 적은 테이블을 드라이빙 테이블로 선택하는 것이 효율적

OUTER JOIN (88p)

아우터 조인 -> 절대 드라이빙 테이블로 선택하지 못하기 때문에 실행 계획 이점 누릴 수 없다

SELECT 절 서브쿼리

only 스칼라 서브쿼리

레코드를 칼럼으로 변환해서 조회

리대쉬 사용할 때, 많이 활용
어플리케이션에서 사용하려면, FE/BE 리소스를 들여서 만들어야 함
BUT, MD나 PO 등등 데이터 분석을 위해서 데이터 뽑아내는 요청 많이 해주심 그때 필요
정기적으로 스냅샷 찍은 운영디비와 별개의 DB를 만들어 사용

Answer 5 · 2022-06-25T02:11:50.000Z

토요일 피드백받은거