REAL MYSQL 정리

이번 블로그에서는 Mysql에 대해서 상세히 기록해보려고 한다.

내용 : Mysql 8.0을 기반으로한 REAL MYSQL을 참고하여 제작하였습니다.

1. Mysql Architecture ★★★★★

구성 : Mysql서버, 응용프로그램, Mysql 엔진, 스토리지 엔진, 운영체제, 하드웨어

Mysql 엔진 구성

- 클라이언트로부터의 접속 및 쿼리 요청을 처리하는 커넥션 핸들러

- Cache, Buffer, SQL Interface

- SQL파서 및 전처리기

- 옵티마이져

- 실행 엔진

- Mysql 엔진은 하나만 존재

스토리지 엔진

- SQL문장을 분석 및 최적화하여 실제 데이터를 읽고 쓰기 작업을 수행

- 여러개 존재하고 동시 동작 가능

- Mysql 5.5버전 이후부터는 MyISAM 스토리지 엔진보다 InnoDB가 주된 역할을 수행 중이다.

핸들러 API

- Mysql 엔진의 쿼리 실행기에서 데이터를 읽거나 쓸 때, 스토리지 엔진에게 요청하는데, 해당 요청을 핸들러 요청이라하고 요청을 위한 API를 핸들러 API라고 한다.

- Mysql 엔진 <-> 스토리지 엔진

쿼리 캐시

- 한번 사용한 쿼리 기록하여 동일한 쿼리 요청시 이를 활용

기본 실행과정

1. SQL 파서 : 쿼리 문장을 토큰(SELECT, FROM 등)으로 분해하고 트리 형태로 만드는 작업, 기본 문번 오류를 걸러낸다.

2. 전처리기 : 파서 트리를 기반으로 구조적 문제를 파악, 존재하지 않는 데이터나 권한상 사용하지 못하는 데이터 사용 걸러짐.

3. 옵티마이저 : 저렴한 비용으로 가장 빠르게 처리할지 역할, 쿼리 최적화하여 실행 계획 작성, 쿼리 재작성, 테이블 스캔 순서 결정, 인덱스 선택 등의 역할을 수행한다. (두뇌)

4. 실행 엔진 : 여러 데이터를 읽고 저장하는 것을 스토리지 엔진에게 요청 (손, 발)

5. 스토리지 엔진 : 데이터를 디스크에 읽고 쓰기

6. 핸들러 : Mysql 서버의 가장 밑단에서 Mysql 실행 엔진의 요청에 따라 데이터를 저장, 읽어오는 역할

옵티마이저의 실행 계획작성법

- 비용 기반 최적화 : Mysql의 다양한 통계정보 활용 실행 계획 수립, 대부분의 DB는 비용기반 사용

- 규칙 기반 최적화 : 동일한 쿼리에 대해 동일한 실행 계획 수립

2. 스레드 방식

Mysql은 기본적으로 스레드 방식을 사용한다.

그리고 이는 크게 두가지 포그라운드 스레드, 백그라운드 스레드로 구분된다.

포그라운드 스레드

- Mysql 서버에 접속한 유저만큼 존재하며 클라이언트 요청을 처리한다. 클라이언트와의 커넥션 종료시 스레드 캐시로 복귀한다.

백그라운드 스레드

- 예를들어, 스토리지 엔진 중 InnoDB같은 경우 여러가지 여러 작업이 백그라운드에서 처리된다. 로그를 디스크에 기록하는 스레드, InnoDB Buffer Pool의 데이터를 디스크에 기록하는 스레드, 데이터를 버퍼로 읽어들이는 스레드 등 여러가지 스레드가 백그라운드에서 동작한다.

3. 메모리 할당

글로벌 메모리 영역

- 모든 스레드가 공유하는 자원영역

- MyISAM의 키 캐시, InnoDB의 버퍼 풀, 쿼리 캐시, 로그 버퍼 등

로컬 메모리 영역

- 클라이언트 스레드가 정보를 독립적으로 갖는 공간

4. Replication

- 두대 이상의 Mysql 서버가 동일한 데이터를 담도록 실시간으로 동기화 하는 기술을 의미한다.

5. InnoDB 스토리지 엔진 ★★★★★

특징

Clustering Index : PK로 클러스터 처리하여 PK기준으로 정렬해 디스크에 저장
MVCC : 하나의 레코드에 대한 여러가지 버전 관리, Undo log, 잠금 필요없이 일관된 읽기 가능
Foreign Key: 외래키 지원
Deadlock Detect : 데드락을 자동 감지해서 종료 시킨다.
Transaction : MyISAM은 트랜잭션 지원 안함
Buffer Pool : 데이터 파일, 인덱스 캐싱, 쓰기 지연을 통한 버퍼링 역할 수행
장애로 부터 데이터를 보호 하기 위한 여러 매커니즘 사용
오라클과 상당히 유사

(+) Deadlock과 Deadlock Detect

- 상호 배타적 지원 : 두개이상의 스레드가 하나의 자원을 서로 사용하는 상황에서 하나의 자원은 하나의 스레드가 사용

- 점유와 대기, 비선점 : 하나의 스레드가 자원을 소유하고 다른 스레드가 이 자원을 이용하려고 하는 상황

- 환형 대기 : 두번째 상황에서 다른 또 다른 스레드가 해당 자원을 이용하려고 기다리는 상황

위와 같은 상황에서 해당 스레드의 트랜잭션 중에 Rollback이 용이한 트랜잭션 자동으로 감지해 강제 종료 시킨다.

버퍼 풀

- LRU(Least Recently Used) 알고리즘을 통해 관리되는 InnoDB의 캐시를 의미

- 데이터 파일이나 인덱스 정보를 메모리에 캐시해둔다. 또한 데이터 쓰기 작업을 지연시켜 한번에 일괄 처리하는 버퍼링 역할도 수행

- MyISAM의 키 캐시는 인덱스만 캐싱하고 버퍼링역할도 못함

Undo 로그

- Update문장이나 Delete와 같은 문장으로 데이터를 변경할 때, 변경하기 전의 데이터를 보관하는 곳

- 트랜잭션의 롤백 대비용, 트랜잭션의 격리 수준을 유지하면서 높은 동시성을 제공하는데 사용한다.

동작 예시

- Transcation의 격리 레벨을 Repeatable Read로 설정했음을 가정

1. Insert로 데이터A 삽입

- 디스크와 버퍼풀에 삽입 됨. 즉, 디스크에 저장후 버퍼풀에 캐싱한 상태

2. Update로 데이터A를 데이터B로 변경 후 Commit

- 버퍼풀의 데이터A 를 데이터B로 수정

- Undo Log에 기존에 데이터A를 Transaction Id와 함께 삽입

- 디스크에는 쓰기 지연 (쓰기 버퍼링)으로 아직 데이터 A, 쓰기 작업 일괄처리 시 B로 변경

3-1. Trasaction ID(유저의 ID)가 Undo의 Transaction ID보다 작은 데이터 조회 -> A 반환

3-2. Trasaction ID(유저의 ID)가 Undo의 Transaction ID보다 큰 데이터 조회 -> (버퍼풀) B 반환 -> 버퍼풀에 없다면 디스크에서 반환, 성능 저하

- Undo의 TransactionID를 기준으로하여 기존/변경 데이터 반환

이를 통해, A사용자가 레코드를 변경하고 커밋을 수행하지 않더라도 다른 사용자의 Select 작업을 방해하지 않는다.

또한, 격리 레벨 원리의 근간이 된다.

Insert Buffer

- Insert/Delete 될 때, 파일 작업(디스크) 뿐만 아니라 인덱스도 변경을 해주어야 한다.

- 인덱스 페이지가 버퍼풀에 있다면 바로 업데이트 진행, 그렇지 않다면 디스크로부터 읽어와 임시 저장공간에 저장

- 해당 임시 저장공간이 Insert Buffer

Redo 로그

- 변경된 내용을 순차적으로 디스크에 기록하는 로그 파일

6. Mysql 엔진의 락

글로벌 락 : FLUSH TABLES WITH READ LOCK으로 설정 가능하며, Select 제외, DDL, DML 쿼리를 글로벌 락이 풀릴때까지 대기시킨다. 잠금 범위가 가장 크다, 전체 테이블 잠금하고 SELECT만 가능한 상태
테이블 락 : 명시적 테이블 락, 묵시적 테이블 락 존재, 이 후, InnoDB 스토리지 엔진에서는 대부분에 DML 쿼리에 대해 테이블 락이 무시되고 DDL 쿼리에서는 테이블 락 적용
유저락
네임락 : 명시적이 아닌 자동 설정

명시적 테이블 락

- 개발자가 직접 테이블에 대한 락을 설정하고 관리하는 방법

- LOCK TABLES 테이블명 [READ/WRITE] 명령어로 설정가능

- READ 설정시 모든 유저가 읽기는 가능하지만 쓰기는 막힘, 만약 쓰고 있는 중에 해당 데이터 읽기 요청시 변경 처리 안된 데이터 추출

- WRITE 설정시 나머지 유저 읽기/쓰기 막힘

- UNLOCK TABLES 로 설정 해제

묵시적 테이블 락

- 데이터베이스가 자동으로 락을 설정하고 해제하는 방법

7. InnoDB 스토리지 엔진의 잠금 ★★★★★

- Mysql 엔진의 잠금과는 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 탑재하고 있다.

- MyISAM보다 훨씬 뛰어난 동시성 처리

InnoDB의 잠금 방식 : 비관적 잠금

- 비관적 잠금 사용 : 현재 트랜잭션에서 변경하고자하는 레코드에 대해 잠금을 설정하고 변경처리, Select는 가능하지만 다른 트랜잭션의 Update에 대해 막음

(+) 낙관적 잠금

- 각 트랜잭션이 동일한 레코드를 수정할 것은 희박한 가능성이라 판단하고 우선, 동시 변경가능하고 마지막에 잠김 충돌이 있었다면 Rollback 처리

InnoDB의 잠금 특징 및 종류

특징

- 레코드 기반으로 잠금 기능을 제공하고 레코드 락이 페이지락, 테이블 락으로 레벨업 되는 경우는 없다.

종류

- 레코드 락 : 레코드 자체만을 잠금

- 갭 락 : 레코드와 인접한 레코드의 간격을 잠금

- 넥스트키 락 : 레코드 락 + 갭 락

- 자동증가 락 : AUTO_INCREMENT 적용한 칼럼을 가진 테이블에 여러 레코드가 동시에 INSERT되는 경우, 테이블 잠금을 통해 이를 순차적으로 구현

InnoDB의 격리 수준 설정 : 레코드 기반 ★★★★★

READ_UNCOMMITED

→ A가 수정 중인 레코드를 B가 읽을 수 있다.
→ 쓰기 중인 데이터에 대하여 읽기 가능

READ_COMMITED

→ A가 수정 중인 레코드를 B는 읽을 수 없고, 커밋시 읽을 수 있다.
→ A가 이미 읽었고 B가 이 후 수정했다. 이 후, 다시 A가 읽는다면 B의 변경내용 반영후 반환
→ 쓰기 중인 데이터에 대하여 읽기 불가능, 재읽기 변경 내용 반영

REPEATABLE_READ (DEFAULT)

→ A가 수정 중인 레코드를 B는 읽을 수 없고, 커밋시 읽을 수 있다.
→ A가 이미 읽었고 B가 이 후 수정했다. 이 후, 다시 A가 읽는다면 B의 변경내용 반영 안하고 A가 읽었던 내용 반환
→ 쓰기 중인 데이터에 대하여 읽기 불가능, 재읽기 변경 내용 반영 x

SERIALIZABLE

→ A가 읽기, 수정 중인 데이터에 대하여 B는 접근 불가능
→ 읽기/쓰기 중인 데이터에 대하여 읽기 불가능, 쓰기 불가능

문제정리 ★★★★★

- Dirty Read : 수정 중인 내용을 읽는다, Read_Uncommitted에서 발생

- Non-Repeatable Read : 두번의 데이터 읽기시 데이터 불일치, Read_Committed에서 발생

- Phantom Read : 데이터를 읽은 후, 해당 데이터에 수정이 아닌, 삽입, 삭제 작업시 레코드가 달라짐, Repeatable_Read에서 발생

→ Serializable에서는 모든 문제 발생 안함

8. 디스크 읽기 방식

→ " 데이터 베이스의 성능 튜닝은 어떻게 디스크 I/O를 줄이냐의 문제이다. "

→ " 쿼리 튜닝은 디스크 접근, 랜덤 접근을 줄이는 것 "

저장 매체 종류

내장 디스크
DAS
NAS
SAN

9. 인덱스

- SELECT 성능 향상

- UPDATE, DELETE, INSERT 성능 저하

- 데이터를 저장할 때마다 인덱스를 정렬 및 갱신

종류

- 클러스터형 인덱스

- 유니크 인덱스 : Unique 칼럼으로 인덱스 생성, 보통은 Not Null과 함께 사용되고 인덱스 추가,수정시 겹치는 값을 확인해야해서 성능 저하, 보통 사용 안함

- 보조 인덱스, 세컨더리 인덱스

- 복합 인덱스

스토리지 엔진별 인덱스 알고리즘 정리

MyISAM : B-Tree, R-Tree, Fulltext-Index

InnoDB : B-Tree

Memory : B-Tree, Hash

TokuDB : Fratal-Tree

NDB : B-Tree, Hash

(+) Analyze와 Optimize의 필요성

- MyISAM, InnoDB는 인덱스에 대한 통계정보 관리

- 통계 정보를 기반으로 쿼리의 실행계획 수립

- 인덱스 통계정보가 계속 쌓이고 실제와는 다르게 수집되어 문제 발생 가능하다.

- ANALIZE 명령어로 통계정보를 다시 수집하는게 낫다.

10. B-TREE 인덱스 ★★★★★

- (루트 - 브랜치 - 리프) 노드로 구성

- 레코드의 키와 주소 형태로 구성

- 주소는 PK로 설정되어 있음

인덱스 키 추가 및 삭제

키 저장 : 오래걸린다.

- 인덱스 키(지정한 칼럼을 토대로 제작)를 이용해 적절한 위치 선택 후, 레코드의 키와 주소를 리프노드에 저장

- 쓰기 작업이 비용이 큰 이유 : 만약 리프노드가 꽉찬다면, 분리하고, 상위 노드까지 갱신

- 인덱스 쓰기 작업은 메모리가 아닌 디스크로부터 페이지를 읽고 쓰기를 해야하기에 더 오래걸린다.

키 삭제, 키 검색 : 오래걸리지 않는다.

- 해당 키 값이 저장된 리프노드를 찾아가 삭제

- 검색시 그냥 검색

키 변경 : 오래걸린다.

- B-Tree는 키를 기준으로 리프노드의 위치가 결정되므로 단순히 키 변경을 하는것은 불가능하다.

- 키 삭제 후, 다시 새로운 키 저장하는 방식 사용

- 따라서 시간이 오래걸린다.

B-Tree 사용에 영향을 미치는 요소 ★★★★★

인덱스 키 값의 크기가 작을수록 좋다.

- 페이지, 블록 : 디스크에 데이터를 저장하는 가장 기본 단위

- 인덱스 또한 페이지 단위로 루트 페이지, 브랜치 페이지, 리프 페이지 등으로 구성된다.

- 키값의 크기가 작을 수록 많은 데이터를 페이지에 넣을 수 있다. 그래야 페이지 수가 줄어들고 그만큼 속도가 빨라진다.

- 또한, 키 길이가 클수록 페이지에 들어가는 인덱스 양이 줄어들어 페이지가 늘어나 B-Tree의 깊이에도 영향을 준다.

카디널리티가 높을수록 즉, 중복값이 적을 수록 좋다.

- 카디널리티가 높을수록 (중복된 값이 적을수록) 필터링 대상이 많아지고 검색대상이 줄어들기에 그만큼 빠르게 처리된다.

- 카디널리티는 유니크한 값의 개수를 의미한다.

- 유니크한 값의 개수가 많을수록 검색대상이 필터링이 더 많이 되기에 더 빨리 처리된다.

읽어야하는 레코드의 수가 많을수록 좋지 않다.

- 인덱스를 이용해 데이터를 읽는 것은 바로 테이블의 레코드를 읽는 것보다 비용이 큰 작업이다.

- 옵티마이저에서는 인덱스를 사용하는 것이 그냥 읽는 것보다 4배 정도의 비용이 든다고 예측한다.

- 조회하려는 레코드의 수가 전체 레코드 수의 20% ~ 25%가 넘아가면 인덱스를 사용하지 않는 것이 효율적이다.

Mysql이 B-Tree 인덱스를 읽는 방법 ★★★★★

인덱스 레인지 스캔
- 가장 대표적인 접근 방식으로 가장 빠르다
- 검색해야할 인덱스의 범위가 결정되었을 때, 사용한다.
- 루프노드-브랜치노드-리프노드를 순차적으로 탐색하고 리프노드에서 시작해야할 위치를 찾으면 순방향 혹은 역방향으로 정해진 범위까지 리프노드의 레코드를 순서대로 읽는다.
- 이 후, 랜덤 I/O방식으로 데이터 하나씩 디스크에서 읽어온다.
- >, < 등이 WHERE 절에 적용되어있다면 사용가능

인덱스 풀 스캔
- 인덱스 레인지 스캔과 달리, 그냥 인덱스의 모든 데이터를 처음부터 끝까지 읽는다.
- 대표적으로 WHERE 절에 인덱스의 첫번째 칼럼이 아닌경우 사용된다.
- 풀스캔보다는 빠르지만 인덱스 레인지 스캔보단 느리다.

루스 인덱스 스캔
- 느슨하게 듬성듬성하게 인덱스를 읽는 것을 의미한다.
- 중간마다 필요하지 않은 인덱스 키 값은 무시하고 다음으로 넘어가는 형태
- 일반적으로, Group By 또는 Max(), Min()에 대해 최적화를 하는 경우에 사용한다.
- 예를 들어, GROUP BY(ID), WHERE 조건 처리시 페이지를 돌며, 조건절에 맞는 데이터를 찾고 다음으로 넘어간다.

인덱스 스킵 스캔
- 복합 인덱스가 (A, B, C)인 상황에서 조건절에 A가 없이 B,C만 있을 때, A를 건너 뛰고 B, C 칼럼만으로 인덱스 검색 가능
- WHERE 절에도 적용 가능하다.
- MySQL 8.0부터 도입되었다.

다중 칼럼 인덱스

- 두개 이상의 칼럼을 활용하여 만든 인덱스로 StoreNamdStoreEmail 처럼 합쳐서 키로 사용된다.

- 두번째 칼럼의 정렬은 첫번째 칼럼의 정렬에 의존한다. 즉, 순서에 신중을 가할 필요가 있다.

B-Tree 인덱스의 가용성과 효율성 : 복합 인덱스 잘 작성해서 인덱스 레인지 스캔 적용 ★★★★★

복합 인덱스 작성 방법

- 다중 칼럼 인덱스에서 칼럼의 순서, 칼럼의 사용되는 동등 비교, 크다 작다 등의 범위 조건에 따라 효율이 달라진다.

- 보통은 같음, 정렬, 범위 조건(>, <), 카디널리티 순으로 복합 인덱스 생성해야 효율이 좋다.

작용 범위 결정 조건 (= 인덱스 레인지 스캔 사용 조건)

- 예시 : where절에 =연산자를 사용하고 >= 연산자를 이용한다고 가정하자.

- 그대로 사용하면 같은것을 찾고 그 속에서 해당 범위를 찾으면 된다.

- 반대인 경우, =, >= 를 하고 = 연산자를 통해 한번 더 찾는다.

- 첫번째 예시와 같이 작업의 범위를 결정하는 조건을 작용 범위 결정 조건이라고 한다.

작용 범위 결정 조건(작업의 범위를 결정하는 조건)을 사용하지 못하는 경우

- NOT-EQUAL 사용한 경우 (NOT IN, NOT BETWEEN, IS NOT NULL 등)

- LIKE %AB 쓴 경우 (앞부분모르고 뒷부분 아는 경우만 해당)

- 스토어드 함수나 다른 연산자로 인덱스 칼럼이 변형된 후 비교된 경우

- 데이터 타입이 서로 다를 때 비교하는 경우

- IS NULL 은 작업 범위 결정 조건으로 사용 가능하다.

11. 클러스터링 인덱스

- 테이블의 PK에 적용

- 레코드의 실제 위치에도 적용된다. 즉, 물리적 위치에도 영향을 준다.

- 다른 인덱스를 생성하는 것보다 클러스터링 인덱스를 사용하는 것이 좀 더 빠르다.

- 클러스터링 인덱스를 사용하지 않는 다른 DB보다 레코드의 저장(정렬해야해서)이나 PK 변경이 느려진다.

- B-TREE(키 - 주소)와 달리 클러스터링 인덱스의 리프노드에는 실제 레코드가 저장된다.

장점

- PK로 검색시 매우 빠름

단점

- INSERT, UPDATE, DELETE 시 인덱스 수정을 해야해서 속도 저하

요약

→ 클러스터링 인덱스에서 다른 DB보다 SELECT는 빨라지지만 INSERT, UPDATE, DELETE 는 느려진다.

12. 외래키 잠금 상황을 고려하자 ★★★★★

자식 테이블의 변경이 지연되는 경우

- 자식테이블의 칼럼 중 FK를 변경하려고 하는 경우, 부모 테이블의 변경이 선행되어야 하는데, 부모 테이블이 쓰기 잠금 상태라면 해제될때까지 기다린다.

부모 테이블의 변경이 지연되는 경우

- 예를들어, 첫번째 커넥션에서 부모테이블을 통해 자식테이블을 변경하려고 한다면 쓰기가 완료될때까지 자식 테이블의 해당 레코드는 잠금상태가 된다. 이 후, 두번째 커넥션에서 부모 테이블을 삭제하려고 한다면 자식테이블의 잠금이 풀릴때까지 기다려야 한다. 이는 외래키의 특성상(ON DELETE CASCADE) 부모테이블이 삭제되면 자식테이블도 삭제되기 때문에 이를 먼저 삭제하기 위해 기다리는 것이다.

요약

→ "자식 테이블을 변경할 때, 부모 테이블이 잠금이라 기다려야하는 상황"

→ "부모테이블 삭제시 자식 테이블이 잠금이라 기다려야 하는 상황"

→ 두가지의 상황을 고려해서 설계하자

13. 옵티마이저의 실행 계획

- 옵티마이저의 실행계획을 파악하자.

쿼리 실행 절차

1. SQL 파서 : 쿼리문 쪼개서 Mysql 서버가 이해할 수 있게 분리

2. 전처리기 : 존재하지 않거나 권한 없는 데이터 걸러짐

3. 옵티마이저 : 파싱정보를 통해 어떤 데이터를 읽을 것이며, 어떤 인덱스를 이용해 테이블을 읽을지 설정

4. 3번의 결과를 스토리지 엔진으로부터 가져온다.

옵티마이저의 역할

불필요한 조건 제거, 복잡한 연산 단순화
테이블 조인의 경우, 어떤 순서로 테이블을 읽을지 결정
테이블 제약조건과 통계정보를 이용하여 사용할 인덱스 결정
스토리지 엔진으로부터 가져온 결과를 임시 테이블에 넣고 다시 가공해야하는지 결정 이 후, 가공해야한다면 Mysql엔진에서 스토리지 엔진으로부터 가져온 결과를 임시 테이블에서 조인하거나 정렬하는 작업을 수행

옵티마이저의 종류

RBO (규칙 기반 최적화) : 옵티마이저에 내장된 우선순위에 따라 실행계획 설정, 같은 쿼리 같은 결과 반환, 사용 안함
CBO (비용 기반 최적화) : 쿼리를 처리하기 위한 여러가지 방법 생성 후 통계자료, 비용 정보를 통해 최선의 방법으로 실행 계획설정, Mysql의 옵티마이저는 CBO를 채택하고 사용중이다.

통계 정보

- 대략의 레코드 건 수, 인덱스의 유니크한 값의 개수

- 레코드의 수가 적을수록 통계정보는 부정확해진다.

통계정보 예측 방법
- 레코드 건수 확인 : SHOW TABLE STATUS LIKE 'TABLEA'\G;
- 인덱스 확인 : SHOW INDEX FROM TABLEA;

통계정보 갱신 방법
- ANALIZE TABLE TABLEA;
- 갱신 중에는 해당 테이블 읽기/쓰기 불가능

14. 실행계획 분석

- explain : 기본적인 쿼리 실행 계획

- explain extended, explain partitions : 상세한 쿼리 실행 계획

- explain 표의 각 라인은 사용된 테이블 개수만큼 출력

- explain 표의 실행순서는 위에서 아래로 출력된다. (위가 가장먼저 접근한 테이블 또는 쿼리의 가장 바깥 부분)

코드 예시

EXPLAIN
SELECT * FROM member LEFT JOIN reservation ON member.user_id = reservation.user_id
WHERE user_address = "천안시";

결과

- Id 칼럼

- SELECT 쿼리별로 부여되는 식별자

- 조인된 결과를 SELET하더라도 하나의 SELECT 안에 내용들은 같은 식별자로 표시

- select_type 칼럼 ★★★★★

- 각 단위의 SELECT 쿼리가 어떤 타입인지 반환

- UNION, 서브 쿼리 사용여부에 따라 변경된다.

- UNION : 두개의 이상의 SELECT 문의 결과를 합쳐서 출력

SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;

- 서브 쿼리

SELECT order_id, 
       (SELECT SUM(price * quantity) FROM order_items WHERE order_id = orders.order_id) AS total_price
FROM orders;

코드 예시

EXPLAIN
SELECT user_id FROM member WHERE user_address = "천안시"
UNION
SELECT owner_id FROM store WHERE owner_id = "mokjaemin";

결과

- table 칼럼

- 사용되는 테이블 표시

- 임시 테이블 사용시 <>로 표시

- type 칼럼 ★★★★★

- 각 테이블을 어떤 방식으로 읽었는지 확인

- 속도 : 위에 있을수록 빠르다.

system : 레코드가 0, 1건인 경우
const : WHERE 절에 PK나 Unique 키를 활용하여 반드시 한건만 조회하는 경우, 동등 비교연산자 사용
eq_ref : 여러 테이블이 조인할 때, 처음 읽은 테이블의 칼럼 값이 다음 테이블의 PK, Unique 키의 검색 조건에 사용되는 경우, 동등 비교연산자 사용
ref : 인덱스의 종류와 상관없이 동등 조건 검색, 동등 비교연산자 사용
fulltext : 전문 검색 인덱스를 활용해 레코드를 읽는 방법, 해당 테이블에 전문 검색 인덱스가 준비되어야 함.
ref_or_null : ref + Null 비교 추가, 별로 사용 안함
unique_subquery : WHERE 절에 서브쿼리를 위한 IN 연산자 사용
index_subquery
range : 인덱스 레인지 스캔 형태의 접근 방법, <, >, IS NULL, BETWEEN, IN, LIKE 이용하여 인덱스 검색시 적용
index_merge : 2개 이상의 이용하여 각각의 결과를 만든 후, 그 결과를 병합하는 방법, 효율적이지 않음
index : 인덱스 풀 스캔 형태의 접근 방법
ALL : 풀테이블 스캔

- possible_keys

- 옵티마이저가 최적의 실행계획을 만들기 위해, 선정했던 접근 방식에서 사용되는 인덱스의 목록

- 사용을 고려했었던 인덱스 목록

- key

- 최종 선택된 인덱스

- key_len

- 다중 인덱스에서 몇개의 칼럼으로 만들어졌는지 확인, 바이트로 알려줌

- ref

- 접근 방식이 ref라면 사용한 값의 타입 반환

- 상수라면 const, 나머지 칼럼이라면 칼럼 이름 반환

- rows

- 실행전 조회를 위해 얼마나 많은 레코드를 디스크로부터 읽고 체크해야할 지 예측한 레코드 건 수

- 정확한건 아님

- Filtered

- 전체 테이블의 레코드에서 얼마나 많은 레코드가 제거되었는지 확인

- 100에 가까울수록 필터링이 거의 없음

해당 내용에서는 Member의 테이블을 모두 가져와 Store 테이블에서 50이 걸러짐을 의미

- Extra ★★★★★

Using Temporary

- Order By, Group By, 복잡한 조인 등에서 임시 테이블, 또는 sort buffer 생성

- 대용량 데이터가 아니라면 Memory에 임시 테이블 생성

- 대용량 데이터라면 디스크에 생성, 디스크에서 I/O 작업을 하기에 성능 저하

Using Filesort

- Order By 등 정렬을 메모리 또는 디스크에서 진행

- 대용량 데이터라면 디스크에서 정렬하는데 이는 성능 저하

Using index (커버링 인덱스)

- 데이터 파일을 전혀 읽지 않고 커버링 인덱스로 쿼리를 모두 처리

- 커버링 인덱스 : 인덱스만으로 모든 조회 내용을 가져올 수 있는 경우, ID는 인덱스에 포함되어 있지 않아도 가져올 수 있다.

- 그렇지 않다면, 다른 칼럼들을 조회하기 위해 디스크 I/O가 N개의 데이터에 대해 N번 발생한다.

Using Join Buffer

- 드리븐 테이블 검색을 위한 적절한 인덱스가 없다면 드라이빙 테이블로부터 읽은 레코드 건 수만큼 드리븐 테이블을 풀스캔해야 한다.

이러한 비효율을 막기 위해 메모리에 위치한 조인버퍼에 드라이빙 테이블의 결과를 저장하고 한번의 드리븐 테이블을 풀스캔하여 속도를 향상시킨다.

- 대용량 데이터라면 조인버퍼 사용을 못하기에 성능 저하

Using where

- InnoDB엔진에서 MySQL 엔진으로 데이터를 불러오는 경우 필터링을 거쳐 데이터를 반환하는 경우

- 예를들어, WHERE절에 2개의 칼럼이 있고 하나만 인덱스 처리가 되어있을때, 인덱스 조건에 맞는 레코드 전부를 MySQL엔진으로 보내고 MySQL 엔진에서 두번째 조건 처리 -> 비효율적

Using Index Condition

- Using Where을 개선한 결과로 많이 사용된다.

- InnoDB가 WHERE 절의 모든 칼럼을 처리 후, MySQL로 결과 레코드들을 보내고 필터링 없이 바로 클라이언트에게 보낸다.

-> 효율증가

Using Index for skip scan

- 인덱스 스킵 스캔 최적화시에 사용된다.

- 인덱스 스킵 스캔 : 첫번째 인덱스가 없어도 두번째 인덱스만으로 인덱스를 검색하게 해주는 기능

15. 처리방식 상세 설명 ★★★★★

- 아래에서 풀테이블 스캔을 제외한 나머지는 모두 Mysql 엔진에서 처리한다.

- Mysql 엔진에서 처리하는 작업은 쿼리의 성능을 저하시킨다.

0. 스트리밍 방식 vs 버퍼링 방식

스트리밍 처리 : 조건에 일치하는 레코드가 검색될때마다 바로 클라이언트에게 전달, LIMIT 처리 등에서 가능하다.

버퍼링 처리 : 모든 조건에 맞는 데이터를 불러와 정렬하거나 그룹핑하고 클라이언트에게 전달, Order By, Group By와 같이 그룹핑하고 정렬하는 방식은 스트리밍 처리가 안된다, LIMIT 처리해도 도움 안됨

1. Full Table Scan (type = all)

- 레코드 건수가 작아 인덱스 사용보다 풀테이블 스캔이 낫다고 판단한경우, 테이블의 인덱스 페이지가 1개인 경우

- WHERE, ON 절에 인덱스를 사용할만한 적절한 조건이 없는 경우

- 인덱스 레인지 스캔을 사용할 수 있어도 조건 일치 레코드 건수가 너무 많은 경우

동작 방식

- 리드 어헤드 방식 덕분에 포그라운드 스레드가 필요한 데이터를 미리 예측해 디스크에서 메모리(InnoDB 버퍼 풀)로 저장해두고 백그라운드 스레드가 이를 가져간다. 이를 통해, 좀 더 빠른 데이터 추출이 가능하다.

ORDER BY ★★★★★

2. ORDER BY의 정렬 방식 : Index sorting, Using filesort

- Order By는 정렬시 사용되고 정렬방법은 2가지가 있다.

인덱스를 이용 : 이미 인덱스는 정렬되어있기 때문에, 정렬이 따로 필요없이 순서대로 읽기만 하면 돼서 매우 빠르지만, 인덱스를 사용하면 변경/삭제 작업할 때 느려지고 메모리 버퍼풀에 공간이 더 필요하다.
Filesort : 인덱스를 사용안하기에 인덱스의 단점이 없고 적은 레코드라면 충분히 빠르지만, 정렬 대상 건수가 많아질수록(메모리 버퍼, 소트 버퍼보다 커진다면) 메모리가 아닌 디스크에서 정렬작업을 실행하여

정렬에 인덱스를 사용하도록 하는 것은 거의 불가능하다.

- 정렬 기준이 너무 많아 기준별 인덱스를 생성할 수 없는 경우

- Group By의 결과 또는 Distinct 같은 처리 결과를 정렬하는 경우

- 임시 테이블의 결과를 재정렬하는경우

- 랜덤하게 레코드를 가져오는 경우

3. ORDER BY의 메모리 공간 : Soft Buffer

- 정렬을 사용하기 위해 만든 임시 테이블은 메모리에 생성되는데, 이를 소트 버퍼라고한다.

- 메모리 공간이 부족하다면 가능한 만큼 정렬 후 디스크에 저장한다. 이를 반복하고 마지막으로 디스크에서 한번 더 정렬(Multi Merge)을 한다.

- 이러한 작업은 모두 디스크 I/O 요청을 자주하게 되고 레코드가 많을수록 반복되어 성능 저하를 불러일으킨다.

4. ORDER BY의 정렬 알고리즘 : 싱글패스 알고리즘

- 정렬을 위한 전체 레코드를 소트 버퍼에 담을지 vs 정렬 기준이 되는 칼럼만 소트 버퍼에 담을지 결정

싱글패스 알고리즘 : 다 담음, 정렬 완료시 그대로 클라이언트에게 반환, 주로 사용
투패스 알고리즘 : 정렬 기준이 되는 칼럼만 담고 정렬 후 정렬된 순서대로 테이블에서 레코드를 가져와 반환, BLOB, TEXT 사용시 또는 max_length_for_sort_data 변수보다 레코드의 크기가 클 때 사용하고 보통은 싱글패스 사용

5. ORDER BY 최적화 : 정렬 종류별 속도

인덱스 사용한 정렬 > 드라이빙 테이블(조인 결과가 없는 테이블)만 정렬 > 조인 결과를 임시 테이블로 저장한 후 정렬

인덱스 사용한 정렬 최대한 유도하고 드라이빙 테이블이라도 사용하게 하자

- 1. 인덱스를 사용한 정렬, 스트리밍 처리 (Using filesort X, Using Temporary X)

조건 : 해당 조건을 모두 만족해야 인덱스 기반 정렬을 적용가능하다.

1. Order By 절의 칼럼이 조회 첫번째 테이블, 조인이라면 드라이빙 테이블에 속한다.

2. Order By 조건이 여러개라면 순서대로 생성된 인덱스가 있어야 한다.

3. 드라이빙 클래스의 조건이 있다면 그 조건과 Order by는 같은 인덱스를 사용할 수 있어야 한다.

4. B-Tree만이 해당 방식을 사용 가능하다.

5. 여러 테이블이 조인되는 경우에는 Nested-loop 방식에서만 이 방법을 사용 가능하다.

1. 조인 없는 단일 테이블

1. 클러스터형을 Order by로 적용 : 스트리밍 정렬

EXPLAIN
SELECT *
FROM member
ORDER BY user_id;

2. 세컨더리 인덱스 : 조인 버퍼로 Using Filesort

EXPLAIN
SELECT *
FROM member
ORDER BY user_email;

3. 복합 인덱스 : 조인 버퍼로 Using Filesort

EXPLAIN
SELECT *
FROM member
ORDER BY user_address, user_number;

- 2. 조인해서 드라이빙 테이블만 Soft Buffer 에서 정렬 (Using FIlesort O, Using Temporary X)

드라이빙 테이블 : 두개의 테이블에서 첫번째 사용되는 테이블을 의미

- 원리 : 드라이빙 테이블을 정렬해 sort buffer 저장 후, 조인

- 조인에서는 드라이빙 테이블 먼저 조인하면 가능하다.

- LEFT JOIN 등 명시적 조인에서 안 됨.

드라이빙 테이블의 칼럼으로만 Order by 작성

EXPLAIN
SELECT *
FROM store s, member m
WHERE m.user_id = s.owner_id
ORDER BY s.owner_id;

드리븐 테이블의 칼럼으로만 Order by 작성

EXPLAIN
SELECT *
FROM store s, member m
WHERE m.user_id = s.owner_id
ORDER BY m.user_id;

결론 : 임시 테이블 없이 Join은 묵시적 조인에서만 가능하며 드라이빙의 인덱스 처리된 Order by 칼럼 작성시 가능하다.

- 3. 조인 결과를 임시 테이블로 저장한 후 정렬 (Using Filesort O, Using Temporary O)

- 조인 테이블을 생성 후, ORDER BY로 정렬하는 경우

- 조인 후, 정렬시 발생 -> 조인을 위한 임시테이블을 만들고 Filesort 방식으로 정렬

GROUP BY ★★★★★

6. Group By 최적화 : 임시 테이블 사용 줄이기

- 스트리밍 처리 불가능

- 특별한 경우 제외, 임시 테이블 생성

- 임시 테이블이나 버퍼에 존재하는 값을 필터링하는 역할

- Having 절에도 인덱스 적용 못함

내부 동작 : 데이터 검색 → 그룹 화 → Having절로 필터링 → 집계함수 처리

인덱스 이용하는 경우

- 인덱스를 차례대로 읽는 인덱스 스캔

- 인덱스를 건너 뛰면서 읽는 루스 인덱스 스캔

인덱스를 사용하지 못하는 경우

- 임시 테이블 활용

1. 인덱스 스캔을 이용한 Group By (Using Temprary X)

조건 : 드라이빙 테이블(첫번째 테이블)에 속한 칼럼만 이용해 Group By절을 구성할 때, 해당 칼럼이 인덱스 처리되어 있다

동작 과정 : 인덱스를 기반으로 그룹핑 작업을 진행하고 그 결과를 조인한다.

- 거의 집계함수를 사용하더라도 임시테이블 처리 없이 실행된다.

실험 결과

- 별도의 임시 테이블이 필요하지 않는다.

- Group By에 인덱스를 태우면 정렬 작업까지 처리해준다.

1. 조인 없는 단일 테이블

1. 인덱스가 없는 경우

EXPLAIN
SELECT *
FROM member
GROUP BY user_name;

2. PK, Secondary 인덱스

EXPLAIN
SELECT *
FROM member
GROUP BY user_id;

EXPLAIN
SELECT *
FROM member
GROUP BY user_email;

3. 복합 인덱스

- 복합 인덱스 순서 바뀌면 안됨

- 몇개 없어도 순서만 지키면 사용 가능 (칼럼1, 칼럼2)가 있을 때, 칼럼1로 Group by 설정시 묵시적 적렬 + 임시 테이블 없이 사용 가능

EXPLAIN
SELECT *
FROM member
GROUP BY user_address, user_number;

2. 조인 테이블

- 조인 테이블에서도 모든 종류의 인덱스에 적용가능하지만, 대용량 데이터의 경우 Using Temporary가 발생한다.

2. 루스(Loose) 인덱스 스캔을 이용한 Group By (Using Index for group-by)

의미 : 인덱스의 레코드를 건너뛰며, 필요한 부분만을 가져오는 것을 의미한다.

동작 : 인덱스 내부에 유니크한 값이 많을수록 잘 동작하지만 유니크한 값이 적은 경우 성능이 저하된다.

- 별도의 임시 테이블이 필요하지 않는다.

- 루스 인덱스 스캔은 Min, Max, Group By에서 보통 사용된다.

원리(user_id, user_email) : id가 1인것 중 user_email에 해당 하는 레코드 가져온다 -> id가 2인것 중 .. id 모두 확인

EXPLAIN
SELECT *
FROM member
WHERE created_at = "2023-10-18 08:58:47.229223"
GROUP BY user_email;

루스 인덱스를 사용할 수 없는 경우

- MIN, MAX 이외의 집계함수 사용시 사용불가능하다.

- SELECT 절의 칼럼이 GROUP BY 절의 칼럼과 일치하지 않는 경우

- GROUP BY에서 사용한 복합 인덱스를 적용한 칼럼들이 복합 인덱스 순서를 따르지 않는 경우

3. 임시 테이블을 사용하는 Group By (Using Temporary O)

- 기존 칼럼이 드라이빙 테이블에 있든, 드리븐 테이블에 있든 관계없이 칼럼에 인덱스를 적용하지 않은 경우에는 임시 테이블을 생성해 처리한다.

- Mysql 8.0부터는 묵시적 정렬을 수행하지 않으므로 Using filesort는 표시되지 않는다.

DISTINCT ★★★★★

7. Distinct 처리

- 집합함수와 사용하냐 안하냐에 따라 차이 발생

1. 집합 함수 없이 사용

아래의 두코드는 같은 기능이다.

SELECT DISTINCT user_name
FROM member;

SELECT user_name
FROM member
GROUP BY user_name;

- DISTINCT (user_name, user_eamil) 처리시 user_name + user_eamil 조합이 유니크한 결과를 찾는다.

2. 집합함수와 함께 사용 : 임시 테이블 사용을 줄이자

- 집합함수 내에서 DISTINCT 키워드가 사용된 경우

- 집함함수 내에서 사용된 DISTINCT는 인자로 전달된 칼럼이 유니크한 값으로 가져온다.

- 쿼리 계획에 Using Temporary가 표시되지 않는데, 이는 내부 임시테이블을 사용하기 때문에

EXPLAIN
SELECT COUNT(DISTINCT member.user_number)
FROM member, store
WHERE store.owner_id = member.user_id;

- COUNT(DISTINCT member.user_number) 에서 임시 테이블을 사용한다.

- member.user_number 을 저장하기위해 임시테이블을 생성한다.

- member.user_number 칼럼에 유니크 인덱스가 생성되므로 레코드 수가 많아지면 성능이 저하된다.

최적화 방법 : 인덱스 처리된 칼럼에 대해서 DISTINCT 처리를 한다.

- 인덱스 처리된 칼럼에 DISTINCT 처리시 인덱스 풀 스캔이나 인덱스 레인지 스캔하면서 임시 테이블 없이 최적화된 처리를 수행한다.

SELECT COUNT(DISTINCT member.user_id)

16. 내부 임시 테이블 활용

Mysql의 일반적인 임시 테이블

- 메모리에 생겼다가 테이블의 크기가 커지면 디스크로 옮겨진다.

- 특정 예외 케이스에서는 바로 디스크에 임시 테이블이 생성되기도 한다.

내부적인 임시 테이블

- 다른 세션이나 다른 쿼리에서는 볼 수 없으며, 사용하는 것이 불가능하다.

- 쿼리의 처리가 완료되면 바로 삭제된다.

메모리 임시 테이블과 디스크 임시 테이블

- 메모리의 임시 테이블은 8.0 이 후, TempTable 스토리지 엔진 사용, 예전에는 Memory

- 디스크의 임시 테이블은 8.0 이 후, InnoDB 스토리지 엔진 사용, 예전에는 MyISAM

임시 테이블이 필요한 경우 ★★★★★

1. ORDER BY와 GROUP BY에 명시된 칼럼이 다른 경우
2. ORDER BY나 GROUP BY에 명시된 칼럼이 드라이빙 테이블이 아닌 경우
3. DISTINCT, ORDER BY가 동시에 존재하는 쿼리 또는 DISTINCT가 인덱스 처리되지 못하는 쿼리
4. UNION이나 UNION DISTINCT가 사용된 쿼리 (select_type 칼럼이 UNION RESULT인 경우)
5. 쿼리의 실행계획에서 select_type 칼럼이 DERIVED인 쿼리 (FROM 절에서 서브쿼리 사용 등)

3~5는 Using Temporary 표시 없이, 내부 임시 테이블 생성

1~4는 유니크 인덱스를 사용하는 내부 임시 테이블 생성하며 처리속도가 상당히 느리다.

5는 유니크 인덱스가 없는 내부 임시 테이블을 생성하며 1~4번보다 처리속도가 상당히 빠르다.

EXPLAIN
SELECT COUNT(DISTINCT user_name)
FROM member;

임시 테이블 관련 상태 변수 ★★★★★

- 실행 계획의 Extra를 통해 임시 테이블을 사용했다는 것은 알 수 있지만, 임시 테이블이 메모리에서 처리됐는지, 디스크에서 처리됐는지는 알 수 없다. 몇 개의 임시 테이블이 사용되었는지도 알 수 없다.

하지만, 임시 테이블이 디스크에 생성되었는지, 메모리에 생성되었는지 다음과 같은 상태변수를 통해 확인 가능하다.

Created_tmp_disk_tables : 내부 임시 테이블의 총 개수

Created_tmp_files : 디스크에 생성된 테이블의 수

Created_tmp_tables : 메모리에 생성된 테이블의 수

FLUSH STATUS;

SELECT *
FROM member
GROUP BY user_number;

-- 결과 확인 -- 
SHOW SESSION STATUS LIKE 'Created_tmp%';

17. 쿼리 최적화 ★★★★★

1. 연산자 최적화

<=> : Null-Safe 연산자로 =와 동일한 역할을 수행한다. Null <=> Null 은 True
LIKE : 와일드카드 문자(%, _)가 뒤에 있다면 인덱스 레인지 스캔을 사용할 수 있지만 앞에 있다면 사용하지 못한다. (앞글자부터 정렬되어있기 때문이다.)
BETWEEN : 보통 숫자, 날짜, 문자열 범위 지정으로 사용
IN : IN ('A', 'B')처럼 여러개의 동등 비교 연산자(=)를 묶은 것이다. 또한, IN안에 서브 쿼리 작성시 서브 쿼리의 외부가 먼저 실행되고 서브 쿼리가 실행되기에 성능에 문제 발생한다.
NOT IN : 부정형은 인덱스를 사용할 수 없다.

2. COUNT 최적화

- COUNT()와 COUNT(1)은 성능 차이가 없다
- 결과에 영향을 미치지 않는 WHERE, JOIN은 사용하지 말아야한다.
- ORDER BY, LEFT JOIN은 COUNT와 함께 사용하기에 적합하지 않다.
- 인덱스를 제대로 사용하지 않은 쿼리의 COUNT사용은 부하를 일으킨다.
- 페이징 처리와 분리해야한다 : 게시판의 3페이지 내용을 페이징을 통해 먼저 불러오고 전체 게시글 수 계산해야한다. 같은 쿼리 X

3. SELECT의 작동 순서

순서 : 드라이빙 테이블과 드리븐 테이블에 대하여 WHERE 적용 및 조인 실행 → GROUP BY → DISTINCE → HAVING → ORDER BY → LIMIT
GROUP BY 없이 ORDER BY만 사용한 쿼리 : 드라이빙 테이블에서 읽어서 ORDER BY 적용 → 드리븐 테이블에서 WHERE 적용 및 조인 실행 → LIMIT 적용

4. WHERE절에서 INDEX를 사용하기 위한 규칙

인덱스된 칼럼의 값 자체를 변경해서는 안된다 → worket.salary*10
= 의 양쪽의 데이터는 타입이 같아야한다. → age = 2
복합 인덱스 사용에서 칼럼 종류를 충족한다면 WHERE 절에 순서와 상관없이 실행되지만 실행 순서는 복합 인덱스 순을 따른다.
OR 연산자 사용시 비교해야 할 레코드가 늘어나 복잡해진다. 따라서 AND를 주로 사용하자

5. GROUP BY에서 INDEX를 사용하기 위한 규칙

GROUP BY절의 명시된 칼럼이 복합인덱스를 사용한다면 칼럼의 순서와 위치가 같아야한다.
복합 인덱스가 (1, 2, 3) 일 때, (1만 사용), (1, 2만 사용)하는 경우는 인덱스 적용이 가능하다. 즉, 선행 칼럼이 GROUP BY에 있어야 한다.
모든 GROUP BY의 칼럼은 인덱스에 포함되어야 하며, 하나라도 인덱스에 없는 칼럼이 사용된다면 인덱스 적용이 안된다.
복합 인덱스가 (1, 2, 3) 일 때, WHERE 절에 1을 넣고 GROUP BY 절에 2, 3을 넣어도 GROUP BY 절에 1, 2, 3 넣는 것과 같은 결과를 가져온다.

6. ORDER BY에서 INDEX를 사용하기 위한 규칙

ORDER BY절의 명시된 칼럼이 복합인덱스를 사용한다면 칼럼의 순서와 위치가 같아야한다.
복합 인덱스가 (1, 2, 3) 일 때, (1만 사용), (1, 2만 사용)하는 경우는 인덱스 적용이 가능하다. 즉, 선행 칼럼이ORDER BY에 있어야 한다.
모든 ORDER BY의 칼럼은 인덱스에 포함되어야 하며, 하나라도 인덱스에 없는 칼럼이 사용된다면 인덱스 적용이 안된다.
복합 인덱스가 (1, 2, 3) 일 때, WHERE 절에 1을 동등조건으로(다른 조건은 안됨) 넣고 ORDER BY 절에 2, 3을 넣어도 ORDER BY 절에 1, 2, 3 넣는 것과 같은 결과를 가져온다.
복합인덱스의 각각의 오름차순/내림차순의 기준이 ORDER BY의 각각의 칼럼이 모두 오름차순/내림차순 기준과 같거나 반대여야 적용 가능하다. (인덱스 : ASC, ASC, ASC) -> (ORDER BY ASC, ASC, ASC or DESC, DESC, DESC)

7. WHERE + (ORDER BY or GROUP BY)에서 INDEX를 사용하기 위한 규칙

- index_merge(여러 인덱스 동시 사용) 제외 하나의 쿼리에는 하나의 인덱스만 사용가능하므로 다음 중 하나로 INDEX를 사용한다.

1. WHERE 절과 (ORDER BY or GROUP BY) 절이 동시에 같은 인덱스를 사용
- 두가지의 절의 칼럼이 하나의 인덱스에 속하는 경우에 사용가능하며 가장 빠르기에 이 방식으로 튜닝하거나 인덱스 생성
2. WHERE 절만 인덱스를 사용
- ORDER BY 절에서는 정렬을 Filesort로 처리
- WHERE 절에 일치하는 레코드가 적을때만 사용하자.
3. (ORDER BY or GROUP BY) 절만 인덱스를 사용
- ORDER BY/GROUP BY 절의 순서대로 인덱스를 읽으며, WHERE 절의 조건과 일치하는지 비교하는 형태
- 대량의 레코드를 읽어서 정렬해야할 때, 이런 형태로 튜닝

8. GROUP BY + ORDER BY에서 INDEX를 사용하기 위한 규칙

- 두 절이 모두 하나의 인덱스를 사용하려면 GROUP BY, ORDER BY 에 명시된 칼럼의 내용과 순서가 같아야 한다.

9. 정리

- 1. WHERE 절이 인덱스를 사용할 수 있는가?

- 2. GROUP BY 절이 인덱스를 사용할 수 있는가?

- 3. ORDER BY 절이 인덱스를 사용할 수 있는가?

10. WHERE 절에 비교 조건 사용 시 주의 사항

NULL 비교 : IS NULL을 ISNULL() 대신 사용하자 (ISNULL() 함수는 인덱스를 사용 못하기 때문에)
문자열이나 숫자 비교 : 타입 맞춰서 비교하자.
DATE or DATETIME과 문자열 비교 : 아래 코드 참조
DATE와 DATETIME을 비교 : DATETIME을 DATE로 만들어 비교해야 한다.
DATE/DATETIME과 TIMESTAMP 비교 : FROM_UNIXTIME()을 통해 DATETIME으로 변경하거나 UNIX_TIMESTAMP()를 통해 TIMESTAMP로 변경하거나하여 타입을 맞춰주고 비교하자.

DATE/DATETIME과 문자열의 비교

인덱스 사용 X
SELECT COUNT(*) FROM employees
WHERE DATE_FORMAT(hire_date,'%Y-%m-%d') > '2011-07-23' ;

인덱스 사용 X
SELECT COUNT(*) FROM employees
WHERE DATE_ADD(hire_date, INTERVAL 1 YEAR) > '2011-07-23' ;

인덱스 사용 O : 상수를 변경하는 형태를 사용하자
SELECT COUNT(*) FROM employees
WHERE hire_date > DATE_SUB('2011-07-23', INTERVAL 1 YEAR);

11. Short-Circuit Evaluation

WHERE절에서 (조건식1 && 조건식2)과 같은 조건식 구조를 가질때, 조건식1이 FALSE라면 조건식2는 실행되지 않는다.

- WHERE절에서 인덱스를 사용하지 못한다면 쿼리 성능이 더 빨라진다.

- 복잡한 연산 또는 다른 테이블의 레코드를 읽어야 하는 서브쿼리 조건 등은 WHERE절의 조건절에서 뒤쪽으로 배치해야 성능 향상된다.

12. LIMIT

- LIMIT는 가장 마지막에 실행된다.

LIMIT 0, 10 : 풀 테이블 스캔하여 10개만큼 레코드를 읽은 시점에서 종료된다.
GROUP BY + LIMIT : GROUP BY가 선행되기에 LIMIT는 성능에 영향을 주지 못한다.
DISTINCT + LIMIT 0, 10 : DISTINCT 처리를 하다 유니크한 값 10개를 찾는다면 종료되기에 성능향상된다.

주의 사항

- LIMIT 안에는 표현식이나 서브쿼리 사용을 못한다.

- 페이징 처리 주의

비효율적인 방식 - 10000000까지 읽고 10개 반환
SELECT * FROM salaries ORDER BY salary LIMIT 1000000,10;

인덱스로 개선 - WHERE절로 읽어야할 위치를 찾고 LIMIT 처리
SELECT * FROM salaries
WHERE salary >= 10000 AND NOT(salary=10001 AND emp_no <= 10000) 
ORDER BY salary LIMIT 0, 10;

- 또한, 중복 허용 인덱스를 고려하여 AND NOT() 부분을 통해 이를 대비하였다.

'Server Development > DataBase' 카테고리의 다른 글

DBCP (0)	2023.11.16