2025-09-10

오늘 배운 것

8.6 함수 기반 인덱스

  • 칼럼의 값을 변형해서 만들어진 값에 대해 인덱스를 구축해야 할 때 함수 기반의 인덱스를 활용
  • 구현 방법
    • 가상 칼럼을 이용한 인덱스
    • 함수를 이용한 인덱스

8.6.1 가상 칼럼을 이용한 인덱스

ADD full_name VARCHAR(30) AS (CONCAT(first_name,' ',last_name)) VIRTUAL,
ADD INDEX ix_fullname(full_name);
  • 이처럼 가상 컬럼을 추가하고 해당 가상 칼럼에 인덱스를 생성할 수 있음
  • VIRTUAL이나 STORED 옵션 중 어떤 옵션으로 생성됐든 관계없이 해당 가상 칼럼에 인덱스 생성이 가능하다
  • 단점
    • 가상 칼럼은 새로운 칼럼을 추가하는 것과 같은 효과를 내기 때문에 실제 테이블의 구조가 변경됌

8.6.2 함수를 이용한 인덱스

CREATE TABLE use (
	user_id BIGINT,
	first_name VARCHAR(10),
	last_name VARCHAR(10),
	PRIMARY KEY (user_id),
	INDEX ix_fullname ((CONCAT(first_name,' ',last_name)))
);
  • 이처럼 테이블의 구조를 변경하지 않고, 함수를 직접 사용하는 인덱스를 생성할 수 있음
  • 활용할 때 반드시 조건절에 함수 기반 인덱스에 명시된 표현식이 그대로 사용되어야 함
    • 표현식이 다르면 옵티마이저가 다른 표현식으로 간주하여 인덱스 활용이 안된다.

8.7 멀티 밸류 인덱스

  • 모든 인덱스는 레코드와 인덱스 키 값이 1:1
  • 멀티 밸류 인덱스는 하나의 데이터 레코드가 여러 개의 키 값을 가질 수 있는 형태의 인덱스
  • 이는 JSON 데이터 타입을 지원하기 위해 시작됨
  • 활용하려면 일반적인 조건 방식을 사용하면 안됨
    • MEMBER_OF()
    • JSON_CONTAINS()
    • JSON_OVERLAPS()
    • 이 함수들을 이용해서 검색해야 옵티마이저가 멀티 밸류 인덱스를 활용한 실행 계획을 수립

8.8 클러스터링 인덱스

  • MySQL 서버에서 클러스터링은 테이블의 레코드를 비슷한것(PK를 기준으로)들끼리 묶어서 저장하는 형태로 구현
    • 왜? 주로 비슷한 값을 동시에 조회하는 경우가 많기 때문

8.8.1 클러스터링 인덱스

  • 프라이머리 키 값이 비슷한 레코드끼리 묶어서 저장하는 것
  • 특징
    • 프라이머리 키 값에 의해 레코드의 저장 위치가 변함
    • 프라이머리 키 값이 변경되면 레코드의 물리적 위치가 변함
    • 프라이머리 키 기반의 검색이 매우 빠름
    • 레코드의 저장이나 프라이머리 키의 변경이 상대적으로 느림
    • 리프 노드에 레코드의 모든 칼럼이 같이 저장
  • 프라이머리 키가 없는 경우 스토리지 엔진이 대체 칼럼을 선택하는 우선 순위
    1. 프라이머리키가 있으며 기본적으로 프라이머리키를 클러스터링 키로 선택
    2. NOT NULL 옵션의 유니크 인덱스 중에서 첫 번째 인덱스를 클러스터링 키로 선택
    3. 자동으로 유니크한 값을 가지도록 증가되는 칼럼을 내부적으로 추가한 후, 클러스터링 키로 선택
      1. 자동으로 추가된 프라이머리키는 사용자에게 노출이 되지 않으며, 쿼리 문장에 명시적으로 사용할 수 없음
      2. 따라서 가능한 프라이머리키를 명시적으로 생성하는 것이 좋음

8.8.2 세컨더리 인덱스에 미치는 영향

  • 세컨더리 인덱스에 리프 노드에는 레코드 주소가 아닌 프라잉머리 키 값이 저장되어 있음
    • 따라서 클러스터링 키 값이 변경될때마다 해당 테이블의 모든 인덱스에 저장된 주솟값을 변경하지 않아도되는 장점이 있음

8.8.3 클러스터링 인덱스의 장점과 단점

  • 장점
    • 프라이머리 키로 검색할 때 처리 성능이 매우 빠름
      • 특히 범위 검색의 경우 매우 빠름
    • 테이블의 모든 세컨더리 인덱스가 프라이머리 키를 가지고 있기 때문에 인덱스 만으로 처리될 수 있는 경우가 많음 → 커버링 인덱스
  • 단점
    • 테이블의 모든 세컨더리 인덱스가 클러스터링 키를 갖기 때문에 클러스터링 키 값의 크기가 클 경우 전체적으로 인덱스의 크기가 커짐
    • 세컨더리 인덱스를 통해 검색할 때 프라이머리 키로 다시 한번 검색해야 하므로 처리 성능이 느림
    • INSERT 할 때 프라이머리 키에 의해 레코드의 저장위치가 결정되기 때문에 처리 성능이 느림
    • 프라이머리 키를 변경할 때 레코드를 DELETE하고 INSERT하는 작업이 필요하기 때문에 처리 성능이 느림
  • OLTP 환경에서 쓰기와 읽기의 비율이 2:8 또는 1:9 정도이기 때문에 조금의 느린 쓰기를 감수하고 읽기를 빠르게 유지하는것이 매우 중요

8.8.4 클러스터링 테이블 사용 시 주의사항

8.8.4.1 클러스터링 인덱스 키의 크기

  • 클러스터링 테이블의 경우 모든 세컨더리 인덱스가 프라이머리 키 값을 포함
  • 따라서 프라이머리 키의 크기가 커지면 세컨더리 인덱스도 자동으로 크기가 커진다.

8.8.4.2 프라이머리 키는 AUTO-INCREMENT보다는 업무적인 칼럼으로 생성(가능한 경우)

  • 프라이머리 키로 검색하는 경우 클러스터링 되지 않은 테이블에 비해 레코드가 프라이머리 키 값에 의해 위치가 결정되므로 매우 빠르게 처리 될 수 있음
  • 따라서 설령 칼럼의 크기가 크더라도 업무적으로 해당 레코드를 대표할 수 있다면 그 칼럼을 프라이머리 키로 설정하는 것이 좋음

8.8.4.3 프라이머리 키는 반드시 명시할 것

  • 프라이머리키를 명시하지 않으면 InnoDB스토리지 엔진이 내부적으로 일련번호 칼럼을 추가
  • 자동으로 추가된 칼럼은 사용자에게 보이지 않기 때문에 사용자가 전혀 접근할 수가 없음
  • 따라서 정의하지 않든 AUTO_INCREMENT 칼럼을 생성하고 PK로 사용하든 결과는 똑같음
  • 그래서 차라리 활용하기 위해 pk를 명시하는 것이 좋음

8.8.4.4 AUTO-INCREMENT 칼럼을 인조 식별자로 사용할 경우

  • 여러 개의 칼럼이 복합으로 프라이머리 키가 만들어져서 길이가 길어져도 세컨더리 인덱스가 필요치 않다면 그래로 PK를 사용하는 것이 좋다.
  • 만약 세컨더리 인덱스가 필요하고 PK가 길다면 AUTO_INCREMENT 칼럼을 추가하고 이를 PK로 설정
  • 인조 식별자 : PK를 대체하기 위해 인위적으로 추가된 프라이머리 키
    • 로그 테이블 처럼 조회보다는 INSERT 위주의 테이블들은 인조 식별자를 PK로 설정하는 것이 성능 향상에 도움이 됨

8.9 유니크 인덱스

  • MySQL에서는 인덱스 없이 유니크 제약만 설정할 방법이 없음
  • 유니크 인덱스에서 NULL도 저장될 수 있는데, NULL은 특정 값이 아니므로 2개 이상 저장될 수 있음
  • 프라이머리 키는 기본적으로 NULL을 허용하지 않는 유니크 속성이 자동으로 부여

8.9.1 유니크 인덱스와 일반 세컨더리 인덱스의 비교

8.9.1.1 인덱스 읽기

  • 유니크하지 않은 세컨더리 인덱스에서 한 번 더 해야하는 작업은 디스크 읽기가 아니라 CPU에서 칼럼 값을 비교하는 작업이기 때문에 이는 성능상 영향이 거의 없다.
  • 일반 세컨더리 인덱스는 중복된 값이 허용되므로 읽어야 할 레코드가 많아서 느린 것이지, 인덱스 자체의 특성 때문에 느린 것이 아님
  • 읽어야 할 레코드 건수가 같다면 성능상의 차이는 미미

8.9.1.2 인덱스 쓰기

  • 유니크 인덱스의 키 값을 쓸 때는 중복된 값이 있는지 없는지 체크하는 과정이 한 단계 더 필요
    • 유니크하지 않은 세컨더리 인덱스의 쓰기보다 느림
  • 중복된 값을 체크할 때는 읽기 잠금을 사용, 쓰기를 할 때는 쓰기 잠금을 사용 → 데드락이 아주 빈번히 발생
  • 체인지 버퍼 : 인덱스 키의 저장을 버퍼링하기 위해 사용
  • 그러나 유니크 인덱스는 반드시 중복 체크를 해야 하므로 작업 자체를 버퍼링하지 못함

8.9.2 유니크 인덱스 사용 시 주의사항

  • 더 성능이 좋아질 것으로 생각하고 불필요하게 유니크 인덱스를 생성하지는 않는 것이 좋음
  • 유니크 인덱스도 일반 세컨더리 인덱스와 같은 역할을 동일하게 수행할 수 있으므로 중복으로 만들어 줄 필요는 없음
  • 유일성이 꼭 보장돼야 하는 칼럼에 대해서는 유니크 인덱스를 생성하되, 꼭필요하지 않다면 유니크 인덱스보다는 유니크하지 않은 세컨더리 인덱스를 생성하는 방법 고려하기

8.10 외래키

  • 외래키는 InnoDB 스토리지 엔진에서만 생성가능
  • 외래키 제약 설정되면 자동으로 연관되는 테이블의 칼럼에 인덱스까지 생성
  • 외래키가 제거되지 않은 상태에서는 자동으로 생성된 인덱스를 삭제할 수 없음
  • 특징
    • 테이블의 변경(쓰기 잠금)이 발생하는 경우에만 잠금 경합(잠금 대기)이 발생한다.
    • 외래키와 연관되지 않은 칼럼의 변경은 최대한 잠금 경합(잠금 대기)을 발생시키지 않는다.

8.10.1 자식 테이블의 변경이 대기하는 경우

  • 자식 테이블의 외래 키 칼럼의 변경(INSERT, UPDATE)는 부모 테이블의 확인이 필요
  • 이 상태에서 부모 테이블의 해당 레코드가 쓰기 잠금이 걸려 있으면 해당 쓰기 잠금이 해제될 때까지 기다리게 됨
  • 자식 테이블의 외래키가 아닌 컬럼의 변경은 외래키로 인한 잠금 확장이 발생하지 않는다.

8.10.2 부모 테이블의 변경 작업이 대기하는 경우

  • 부모 키를 참조하는 자식 테이블의 레코드를 변경하면 자식 테이블의 레코드에 대해 쓰기 잠금 획득
  • 이 상태에서 부모 테이블에서 해당 키를 삭제하는 경우 자식 테이블의 레코드에 대한 쓰기 잠금이 해제될 때까지 기다려야 함
    • 원인 : ON DELETE CASACADE로 인해 부모 레코드가 삭제되면 자식 레코드도 동시에 삭제되는 식으로 작동

  • 따라서 외래키를 물리적으로 생성할 때 잠금 경합까지 고려해 모델링을 진행하는 것이 좋음

  • 옵티마이저
    • 쿼리를 최적으로 실행하기 위해 각 테이블의 데이터가 어떤 분포로 저장돼 있는지 통계 정보를 참조하며, 그러한 기본 데이터를 비교해 최적의 실행 계획을 수립

9.1 개요

9.1.1 쿼리 실행 절차

  • 쿼리가 실행되는 과정
    1. 사용자로부터 요청된 SQL 문자를 잘게 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리(파스 트리)한다.
      1. SQL 파서 모듈로 진행 → SQL 파싱
      2. SQL 문장이 문법적으로 잘못됐다면 이 단계에서 걸러짐
      3. SQL 파스 트리가 만들어짐
    2. SQL의 파싱 정보(파스 트리)를 확인하면서 어떤 테이블부터 읽고 어떤 인덱스를 이용해 테이블을 읽을지 선택한다.
      1. 최적화 및 실행 계획 수립 → 옵티마이저가 처리
        1. 불필요한 조건 제거 및 복잡한 연산의 단순화
        2. 여러 테이블의 조인이 있는 경우 어떤 순서로 테이블을 읽을지 결정
        3. 각 테이블에 사용된 조건과 인덱스 통계 정보를 이용해 사용할 인덱스를 결정
        4. 가져온 레코드들을 임시 테이블에 넣고 다시 한번 가공해야 하는지 결정
    3. 두번째 단계에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져온다.
      1. 수립된 실행 계획대로 스토리지 엔진에 레코드를 읽어오도록 요청
      2. MySQL 엔진에서는 스토리지 엔진으로부터 받은 레코드를 조인하거나 정렬하는 작업을 수행
  • 첫번째, 두번째 단계 → MySQL 엔진
  • 세번째 단계 → MySQL 엔진 + 스토리지 엔진

9.1.2 옵티마이저 종류

  • 규칙 기반 최적화
    • 대상 테이블의 레코드 건수나 선택도 등을 고려하지 않고 옵티마이저에 내장된 우선순위에 따라 실행계획을 수립하는 방식
    • 같은 쿼리에 대해서는 거의 항상 같은 실행 방법을 만들어 냄
    • 사용자의 데이터는 분포도가 매우 다양하여 이제는 거의 사용되지 않음
      • 각 테이블이나 인덱스의 통계정보가 거의 없고 상대적으로 느린 CPU 연산 탓에 비용 계산 과정이 부담스럽다는 이유로 사용되었었음
  • 비용 기반 최적화
    • 쿼리를 처리하기 위한 여러 가지 가능한 방법을 만들고, 각 단위 작업의 비용(부하) 정보와 대상 테이블의 예측된 통계 정보를 이용해 실행 계획별 비용을 산출
    • 산출된 실행 방법별로 비용이 최소로 소요되는 처리방식을 선택해 최종적으로 쿼리를 실행

9.2 기본 데이터 처리

9.2.1 풀 테이블 스캔과 풀 인덱스 스캔

  • 풀 테이블 스캔 : 인덱스를 사용하지 않고 테이블의 데이터를 처음부터 끝가지 읽어서 요청된 작업을 처리하는 작업
    • 사용 조건
      • 테이블의 레코드 건수가 너무 작아서 인덱스를 통해 읽는 것보다 풀 테이블 스캔을 하는 편이 더 빠른 경우
        • 일반적으로 테이블이 페이지 1개로 구성된 경우
      • WHERE 절이나 ON 절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
      • 인덱스 레인지 스캔을 사용할 수 있는 쿼리라고 하더라도 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은 경우
        • 인덱스의 B-Tree를 샘플링해서 조사한 통계 정보 기준
  • 테이블의 전체 크기는 인덱스보다 훨씬 크기 때문에 테이블을 처음부터 끝까지 읽는 작업은 상당히 많은 디스크 읽기가 필요
  • 따라서 풀 테이블 스캔을 실행할 때 한꺼번에 여러 개의 블록이나 페이지를 읽어오는 기능을 내장하고 있음
  • 리드 어헤드 : 어떤 영역의 데이터가 앞으로 필요해지리라는 것을 예측해서 요청이 오기 전에 미리 디스크에서 읽어 InnoDB의 버퍼 풀에 가져다 두는 것
    • 처음 몇 개의 데이터 페이지는 포그라운드 스레드가 페이지 읽기를 실행
    • 특정 시점 부터는 읽기 작업을 백그라운드 스레드로 넘김
    • 백그라운드 스레드가 읽기를 넘겨 받는 시점부터 한 번에 4개 또는 8개씩의 페이지를 읽으면서 계속 그 수를 증가시킴
    • 한번에 최대 64개의 데이터 페이지까지 읽어서 버퍼 풀에 저장
    • 포그라운드 스레드는 미리 버퍼 풀에 준비된 데이터를 가져다 사용하기만 하면 되므로 쿼리가 상당히 빨리 처리됨
    • 리드 어헤드는 풀 테이블 스캔, 풀 인덱스 스캔에서 동일하게 사용
  • 단순 레코드의 건수만 필요하는 쿼리는 용량이 작은 인덱스를 선택하는 것이 디스크 읽기 횟수를 줄일 수 있어 풀 테이블 스캔보다는 풀 인덱스 스캔을 하게될 가능서잉 높음

9.2.2 병렬 처리

  • innodb_parallel_read_threads 라는 시스템 변수를 이용해 하나의 쿼리를 최대 몇 개의 스레드를 이용해서 처리할지를 변경할 수 있음
  • 아무런 WHERE 조건 없이 단순히 테이블의 전체 건수를 가져오는 쿼리만 병렬로 처리할 수 있음
  • 병렬 처리용 스레드 개수를 아무리 늘리더라도 서버에 장착된 CPU의 코어 개수를 넘어서는 경우에는 오히려 성능이 떨어질 수도 있으니 주의

9.2.3 ORDER BY 처리 (Using filesort)

  • 레코드 1~2건을 가져오는 쿼리를 제외하면 대부분의 SELECT 쿼리에서 정렬은 필수적으로 사용
  • 정렬 처리 방법
    • 인덱스 이용
      • 장점
        • INSERT, UPDATE, DELETE 쿼리가 실행될 때 이미 인덱스가 정렬돼 있어서 순서대로 읽기만 하면 되므로 매우 빠르다. → 근데 이거 오타 같음 저 실행될때 이전 내용 잘못된것 같음
      • 단점
        • INSERT, UPDATE, DELETE 작업 시 부가적인 인덱스 추가/삭제 작업이 필요하므로 느리다.
        • 인덱스 때문에 디스크 공간이 더 많이 필요하다.
        • 인덱스의 개수가 늘어날수록 InnoDB의 버퍼 풀을 위한 메모리가 많이 필요하다.
    • Filesort
      • 장점
        • 인덱스를 생성하지 않아도 되므로 인덱스를 이용할 때의 단점이 장점이 된다.
        • 정렬해야할 레코드가 많지 않으면 메모리에서 Filesort가 처리되므로 충분히 빠르다.
      • 단점
        • 정렬작업이 쿼리 실행 시 처리되므로 레코드 대상 건수가 많아질수록 쿼리의 응답속도가 느리다.
  • 인덱스를 이용하여 정렬이 불가능할 때
    • 정렬 기준이 너무 많아서 요건별로 모두 인덱스를 생성하는 것이 불가능한 경우
    • GROUP BY의 결과 또는 DISTINCT 같은 처리의 결과를 정렬해야 하는 경우
    • UNION의 결과와 같이 임시 테이블의 결과를 다시 정렬해야 하는 경우
    • 랜덤하게 결과 레코드를 가져와야 하는 경우

9.2.3.1 소트 버퍼

  • 소트 버퍼 : 정렬을 수행하기 위해 별도의 메모리 공간을 할당 받는 것
    • 정렬이 필요한 경우에만 할당
    • 버퍼의 크기는 정렬해야 할 레코드의 크기에 따라 가변적으로 증가
    • 최대 사용 가능한 소트 버퍼 공간 → sort_buffer_size 시스템 변수로 설정
    • 쿼리의 실행이 완료되면 즉시 시스템으로 반납
  • 정렬해야할 레코드 건수가 소트 버퍼의 크기보다 클 때
    • 메모리의 소트 버퍼에서 정렬을 수행하고, 그 결과를 임시로 디스크에 기록
    • 다음 레코드를 가져와서 다시 정렬해서 반복적으로 디스크에 임시 저장
    • 각 버퍼 크기만큼 정렬된 레코드를 다시 병합하면서 정렬을 수행 → 멀티 머지
      • 이 작업들 모두 디스크의 쓰기와 읽기를 유발
      • 레코드 건수가 많을수록 이 반복 작업의 횟수가 많아짐
      • 그렇다고 소트 버퍼의 크기를 크게 설정한다고 성능이 좋아지지 않음
  • 리눅스 계열의 운영체제에서는 너무 큰 sort_buffer_size를 사용하는 경우, 큰 메모리 공간 할당 때문에 성능이 훨씬 떨어질 수 있다.
    • 정렬을 위해 할당하는 소트 버퍼는 세션 메모리 영역
    • 즉 여러 클라이언트가 공유해서 사용할 수 있는 영역이 아님
    • 커넥션이 많으면 많을수록, 정렬 작업이 많으면 많을 수록 소트 버퍼로 소비되는 메모리 공간이 커짐
    • 이에 따라 운영체제는 메모리 부족 현상을 겪을 수 있고 여유 메모리 확보를 위해 MySQL 서버를 강제 종료 할 수 있음

9.2.3.2 정렬 알고리즘

  • 싱글 패스 : 레코드를 정렬할 때 레코드 전체를 소트버퍼에 담음
  • 투 패스 : 레코드를 정렬할 때 정렬 기준 칼럼만 소트 버퍼에 담음
  • MySQL 서버의 정렬 방식
    • <sort_key, rowid> : 정렬 키와 레코드의 로우아이디만 가져와서 정렬
    • <sort_key, additional_fields> : 정렬 키와 레코드 전체를 가져와서 정렬하는 방식으로, 레코드의 칼럼들은 고정 사이즈로 메모리 저장
    • <sort_key, packed_additional_fields> : 정렬 키와 레코드 전체를 가져와서 정렬하는 방식으로, 레코드의 칼럼들은 가변사이즈로 메모리 저장

9.2.3.2.1 싱글 패스 정렬 방식

  • 소트 버퍼에 정렬 기준 칼럼을 포함해 SELECT 대상이 되는 칼럼 전부를 담아서 정렬을 수행
  • 정렬이 완료되면 정렬 버퍼의 내용을 그대로 클라이언트로 넘겨줌

9.2.3.2.2 투 패스 정렬 방식

  • 정렬 대상 칼럼과 프라이머리 키 값만 소트 버퍼에 담아서 정렬을 수행
  • 정렬된 순서대로 다시 프라이머리 키로 테이블을 읽어서 SELECT할 컬럼을 가져오는 정렬 방식
  • 싱글 패스 VS 투패스
    • 싱글 패스
      • 장점
        • 한번만 읽고 그대로 리턴 가능
        • 정렬 대상 레코드의 크기나 건수가 작은 경우 빠른 성능
      • 단점 : 더 많은 소트 버퍼 공간 필요
    • 투 패스
      • 장점
        • 더 적은 소트 버퍼 공간 필요
        • 정렬 대상 레코드의 크기나 건수가 많은 경우 빠른 성능
      • 단점 : 두번 읽어야함
  • 최신 버전을 주로 싱글 패스 사용함
  • 싱글 패스 사용 불가한 경우
    • 레코드의 크기가 max_length_for_sort_data 시스템 변수에 설정된 값보다 클 때
    • BLOB이나 TEXT 타입의 컬럼이 SELECT 대상에 포함할 때

9.2.3.3 정렬 처리 방법

  • ORDER BY 사용시 정렬하는 3가지 처리 방법(아래쪽으로 갈 수록 처리 속도 떨어짐)
    • 인덱스를 사용한 정렬
    • 조인에서 드라이빙 테이블만 정렬
    • 조인에서 조인 결과를 임시 테이블로 저장 후 정렬
  • 옵티마이저의 실행 계획 수립 과정
    • 인덱스 사용 가능한지 확인 → 가능하다면 filesort 없이 인덱스로 정렬
    • 사용 불가 시, WHERE 조건에 일치하는 레코드를 검색해 정렬 버퍼에 저장하면서 정렬을 처리
    • 이때 정렬 대상 레코드를 최소화 하기 위해 2가지 방법 중 하나 선택
      • 조인의 드라이빙 테이블만 정렬한 다음 조인을 수행 → 효율적
      • 조인이 끝나고 일치하는 레코드를 모두 가져온 후 정렬을 수행

9.2.3.3.1 인덱스를 이용한 정렬

  • 사용 조건
    • ORDER BY에 명시된 칼럼이 제일 먼저 읽는 테이블에 속하고 , ORDER BY의 순서대로 생성된 인덱스가 있어야 함
    • WHERE절에 첫 번째로 읽는 테이블의 칼럼에 대한 조건이 있다면 그 조건과 ORDER BY는 같은 인덱스를 사용할 수 있어야 함
    • B-Tree 계열의 인덱스가 아닌 해시 인덱스나 전문 검색 인덱스 등에서는 인덱스를 이용한 정렬을 사용할 수 없음
      • R-Tree도 안됨
    • 여러 테이블이 조인되는 경우에는 네스티드-루프 방식의 조인에서만 이 방식 사용 가능
  • 주의점
    • 인덱스로 정렬이 처리될 때 ORDER BY를 명시된다고 해서 성능상의 손해가 없고 예외 상황에서도 버그로 연결되지 않으므로 굳이 없앨 필요는 없음

9.2.3.3.2 조인의 드라이빙 테이블만 정렬

  • 조인을 실행하기 전에 첫 번째 테이블의 레코드를 먼저 정렬한 다음 조인을 실행
  • 조건 : 조인에서 첫 번째로 읽히는 테이블의 칼럼만으로 ORDER BY 절을 작성해야 함

9.2.3.3.3 임시 테이블을 이용한 정렬

  • ORDER BY 절의 정렬 기준 칼럼이 드라이빙 테이블이 아니라 드리븐 테이블에 있는 칼럼일 때는 조인 후 정렬해야 함

9.2.3.3.4 정렬 처리 방법의 성능 비교

  • LIMIT는 테이블이나 처리 결과의 일부만 가져오기 때문에 MySQL 서버가 처리해야 할 작업량을 줄이는 역할을 함
  • 하지만 ORDER BY나 GROUP BY 같은 작업은 WHERE 조건을 만족하는 레코드 LIMIT 건수 만큼 가져와서는 처리할 수 없음
  • 따라서 WHERE 조건이 아무리 인덱스를 잘 활용하도록 튜닝해도 잘못된 ORDER BY나 GROUP BY 때문에 쿼리가 느려지는 경우가 자주 발생
  • 쿼리 처리 방법
    • 스트리밍 처리
    • 버퍼링 처리

9.2.3.3.4.1 스트리밍 방식

  • 서버 쪽에서 처리할 데이터가 얼마인지에 관계없이 조건에 일치하는 레코드가 검색될 때마다 바로바로 클라이언트로 전송해주는 방식
  • 쿼리가 얼마나 많은 레코드를 조회하느냐에 상관없이 빠른 응답 시간을 보장
    • OLTP환경에서 응답시간이 중요하므로 매우 효율적
  • 스트리밍 방식에서는 LIMIT 처럼 결과 건수를 제한하는 조건들은 쿼리의 전체 실행 시간을 상당히 줄여줌

9.2.3.3.4.2 버퍼링 방식

  • ORDER BY나 GROUP BY같은 처리는 쿼리의 결과가 스트리밍 되는 것을 불가능
    • WHERE 조건에 일치하는 모든 레코드를 가져온후, 정렬하거나 그루핑해서 차례대로 보내야 하기 때문
  • 모든 레코드를 검색하고 정렬 작업을 하는 동안 클라이언트는 아무것도 하지 않고 기다려야 하기 때문에 응답속도가 느려진다.
  • 따라서 LIMIT처럼 결과 건수를 제한하는 조건이 있어도 성능향상에 별로 도움이 되지 않음
  • ORDER BY 3가지 처리방법 방식 구분
    • 인덱스 → 스트리밍
    • 드라이빙 테이블 정렬 후 조인 → 버퍼링
    • 조인 후 정렬 → 버퍼링
  • 어떤 테이블이 먼저 드라이빙되어 조인되는지도 중요하지만 어떤 정렬 방식으로 처리되는지는 더 큰 성능 차이를 만듦
    • 가능한 인덱스로 유도하고 안되면 드라이빙 테이블로 유도

9.2.3.4 정렬 관련 상태 변수

  • Sort_merge_passes : 멀티 머지 처리 횟수
  • Sort_range : 인덱스 레인지 스캔을 통해 검색된 결과에 대한 정렬 작업 횟수다
  • Sort_scan : 테이블 스캔을 통해 검색된 결과에 대한 정렬 작업 횟수
  • Sort_rows : 지금까지 정렬한 전체 레코드 건수

results matching ""

    No results matching ""