카테고리 없음

Real MySQL CH 11 쿼리 작성 및 최적화(2)

my-log 2023. 7. 14. 20:14

LIMIT

  • limit은 항상 쿼리의 맨 마지막에 수행된다.
  • limit절의 n,m값이 커지게 되면 쿼리 수행이 상당히 오래 걸리게 된다.
  • SELECT * from salaries ORDER BY salary LIMIT 2000000,10
  • 위 쿼리를 수행하게 되면 20000010번까지 읽은 후 마지막 10개만 반환하게 된다. 이럴경우 쿼리가 느려질 수 있다.
  • 보통 페이징 처리를 위해 LIMIT절을 사용하게 되는데, 이럴경우 단순히 페이지 번호로 조회하지 말고 WHERE절로 읽어야할 위치를 찾고 그 위치에서 10개만 읽는 식의 쿼리가 좋다.
  • SELECT * from salaries WHERE salary >= (이전 페이지 마지막 값) AND NOT (중복을 제거하기 위한 조건) ORDER BY salary LIMIT 0,10;

COUNT()

  • 레코드 건수를 가져오는 함수
  • WHERE 조건이 없는 COUNT(*) 쿼리는 직접 데이터를 읽어야(PK)조회 해야 수행이 가능하므로 수행시간이 오래 걸릴 수 있다. → warmup시 사용
  • 대략적인 건수도 괜찮다면 SHOW TABLE STATUS 사용

—> Q. cache warm-up이 필요한 경우 count(인덱스가 있는 컬럼) 대신 count(인덱스가 없는 컬럼)을 해야 하는 경우

JOIN

  • JOIN 순서와 인덱스
    • 옵티마이저는 항상 드라이빙 테이블이 드리븐 테이블을 최적으로 읽을 수 있게 실행계획을 수립한다.
    • 인덱스가 있는 테이블을 드리븐 테이블로 선택한다.
  • JOIN 컬럼의 데이터 타입
    • 조인 컬럼간에 비교에서 데이터 타입이 일치하지 않으면 인덱스를 효율적으로 사용할 수 없다.
      • 대표적으로 CHAR 과 INT 비교
      • 같은 CHAR이라도 문자 집합이나 콜레이션이 다른 경우
      • 같은 INT라도 부호의 존재 여부가 다른경우
  • OUTER JOIN 성능과 주의사항
    • MySQL 옵티마이저는 절대로 아우터로 조인되는 테이블을 드라이빙 테이블로 선택하지 못한다.
    • 이너 조인으로 사용해도 되는 쿼리를 아우터 조인으로 작성하면 옵티마이저가 조인 순서를 변경하면서 수행할 수 있는 최적화의 기회를 뺏어버리는 것.

→Q. 해시 조인의 장 단점은 ?

GROUP BY

  • WITH ROLLUP
    • 그루핑된 그룹별로 소계(합계)를 가져올 수 있는 기능.엑셀의 피벗 테이블과 거의 동일한 기능

ORDER BY

  • order by절이 사용되지 않은 경우 select의 결과가 정렬되는 순서
    • 인덱스를 사용한 select의 경우 인덱스에 정렬된 순서대로 select
    • 테이블 풀 스캔을 사용한 경우 innodb의 경우 pk 순서대로
    • 임시 테이블을 거쳐 처리되면 레코드 순서 파악 어려움

서브쿼리

  • select절에 사용된 서브쿼리
    • 서브쿼리가 적절히 인덱스를 사용할 수 있으면 크게 주의할 사항은 없음
    • 레코드가 1건만 존재해야 함
  • from절에 사용된 서브쿼리
    • mysql 5.7버전부터는 옵티마이저가 from절의 서브쿼리를 외부 쿼리로 병합하는 최적화를 수행하도록 개선됨
    • explain을 수행한 후 show warnings를 실행하면 mysql서버가 서브쿼리를 병합해서 재작성한 쿼리 내용 확인 가능

CTE

  • 이름을 가진 임시테이블, SQL문장 내에서 한번이상 사용될 수 있으며 SQL 문장 종료시 삭제되는 임시테이블
  • 기존 FROM절에 사용되던 서브쿼리 대비 장점
    • CTE는 재사용 가능ㅎ하므로 FROM절의 서브쿼리보다 효율적
    • 다른 CTE 테이블에서 참조 가능
  • 재귀적 CTE
    • 재귀적 CTE 쿼리는 비 재귀적 쿼리 파트와 재귀적 파트로 구분되며. 이 둘을 UNION(ALL)로 연결하는 형태로 반드시 쿼리를 작성해야 한다.

윈도우 함수

  • 조회하는 현재 레코드를 기준으로 연관된 레코드 집합 연산 수행. 집계함수는 주어진 그룹별로 하나의 레코드로 묶어서 출력하지만 윈도우 함수는 조건에 일치하는 레코드 건수는 변하지 않고 그대로 유지한다.
  • 집계함수는 OVER()절 없이 단독으로 사용될수도 있고 OVER()절을 가진 윈도우 함수로도 사용 가능
  • DENS_RANK() / RANK () / ROW_NUMBER()
    • RANK()함수는 동점인 레코드가 두건 이상인 경우 그 다음 레코드를 동점인 레코드 순위만큼 증가시킨 후 반환
    • DENS_RANK()는 동점인 레코드를 1건으로 가정, 연속된 순위 나타남

잠금을 사용하는 SELECT

  • FOR SHARE
    • select 쿼리로 읽은 레코드에 대해서 읽기 잠금을 검
  • FOR UPDATE
    • 쿼리가 읽은 레코드에 대해서 쓰기 잠금을 검
  • NOWAIT / SKIP LOCKED
    • NOWAIT : select 쿼리시 해당 레코드가 다른 트랜잭션에 의해서 잠겨진 상태라면 기다리지 않고 바로 쿼리를 종료 시킴
    • SKIP LOCKED : select 쿼리가 다른 트랜잭션에 의해 잠겨진 상태라면 에러를 반환하지 않고 잠긴 레코드는 무시하고 잠금이 걸리지 않은 레코드만 가져옴
    • SELECT FOR UPDATE에서만 사용 가능하며, UPDATE / DELETE에서는 사용 불가능

INSERT

  • INSERT IGNORE
    • 저장하는 레코드의 PK/UK 값이 이미 테이블에 존재하는 경우 또는 저장하는 레코드의 컬럼이 테이블 칼럼과 호환되지 않는 경우 warning 표시 후 트랜잭션을 롤백하지 않고 다음 작업 진행

'카테고리 없음'의 다른글

  • 현재글Real MySQL CH 11 쿼리 작성 및 최적화(2)

티스토리툴바