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

[kihyuk-sung]

148p insert 전 까지

[kihyuk-sung]

• 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 로 잠금을 우회 가능하다.

[kyupid]

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 서버가 느려서 트랜잭션이 느려진다고들 한다" 뭔가 저자의 고충 같은 느낌이다 ㅋㅋ

[sanhee]

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: 잠긴 레코드는 무시하고 잠금이 걸리지 않은 레코드만 가져온다.

[jihye-woo]

Limit

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

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

Count

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

JOIN

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

OUTER JOIN (88p)

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

SELECT 절 서브쿼리

• only 스칼라 서브쿼리

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

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

[kyupid]

토요일 피드백받은거

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)T
  • 저번에 페이징 이야기하면서 뒤로갈수록 느려진다고 했고 다들 자연스럽게 수긍하는 분위기였는데
  • 이것이 페이지 뒤로 갈수록 느려지는 일반적인 원인?
  • 84p "물론 게시물을 가져오는 두번째 쿼리도 다음 페이지로 넘어가면 갈수록 성능이 더 느려질 가능성이 높다"
    • Q2) 84p에서 이전과 다음 버튼만 표시하는 방식을 검토해보라는데 무슨 의미?
    • 80~81p
    • limit 으로 안될거같으면 where절에서 limit 0, 10
    • where salary > ? and not (salary = ? and )
    • 리미티 0부터 10까지 찾은담에 10개 마지막껄 이용해서 조건을 두번쨰부터 재구성하는 것이다

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을 매칭하므로, 드라이븐 테이블이 된다
  • 라고 생각이 드는데 왜 반대일까?

• A3) 인덱스가 있는게 드리븐 인덱스가 없는게 드라이빙

  • 반대로해도 풀스캔을 해야한다
  • 인덱스가 있는애를 드라이빙으로 잡으면 조인조건에만 있는거지 ㅈhere절이 아니다 where조건이면 인덱스쓸수있는데, 조인조건이기떄문에 컬럼을 인덱스 풀스캔을 할수는 있지만, 전체조회하는 걸 피할 순 업따 !
  • 로우 하나에 대해서 드리븐 테입르 조건을 찾아야하는데 드리븐 테이블에 인덱스 없다면 무조건 다 찾아야한다
  • 로우가 1번 부터 10만까지 있다 치자
    • 값이 1번인걸 보고 조인을 해야하는데 드리븐에서 값이 1번인걸 찾아야한다, 그런데 인덱스가 없다? -> 그럼 다 찾아야지 인덱스가 없으니까
    • 반대로 해보자
    • 인덱스없는건 다찾아야한다 어쩄든!
    • 드라이빙 테이블ㅇ르 찾는데 이것도 1만부터 10만까지 있다치자
      • 1번인거에대해 드리븐 찾는데 이때는 인덱스 ㅣㅇㅆ으니까 그걸 찾으면 그걸 인덱스를 통해 가지고 올수있는것이다
      • 그러니까 인덱스가 없는게 드라이빙테이블이 되고, 그 드라이빙테이블에 대해서 하나씩 전체적으로 보면서 로우를 하나씩 찾는다
      • 그래서 nested-loop join 이다
      • 만약에 둘다 인덱스가 없다면 블럭단위로 nested join을 하는 것이다
  • for loop 두번 돈다고 한번 생각해봐라, 안에 있는 for문도 전체조회인데 그걸 줄이는게 뽀인뜨

90p

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

91p 지연된 조인

• Q4) 왜 salaries 테이블 조인 수행한 횟수가 3000 * 4 번인가? salaries 레코드 갯수만큼 찍혀야하는 거 아닌가?
  • sum(salary)를 하는데 대충 4개씩 조인 돤다

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

• 한줄로 꺼내고 싶을때 저렇게하지못하고 프로그래밍적으로 map 같은데 담아서 어떻게 해결했던거 같다, 단순히 데이터분류하는 작업이니까 SQL로 하는게 책임소재가 나누어져 좋을것같다
  • k: 실제로 사용하게 되는 자원은 거의 비슷하다고 본다 그걸 단지 db냐 앱에냐 -> 그걸 잘 따져보는게 좋다
  • bigint로 로우 150만건 정도 가지고오면 메모리에 올라가는 데이터가 한 1기가 이상된다,
  • 이걸 어플리케이션에서 한다? 이거 실행할때마다 메모리에 1기가가 올라가고 그러개때문에 이런걸 디비에서도 효율적으로 처리할 수 있는가? 이런걸 고민해보는게 좋을 것이다,
  • 잘 따져서 해야한다!
  • 여러가지 상황변수 고려 - 네트워크 / 메모리 등등

101p order by

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

---

100~120p 정도쯤

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

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

---

146p select for update 쿠폰 예시

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

147p 주의

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