SQL 첫걸음 10장

[somefood]

성능 생각하기

성능

• 시스템 세계에서 성능은 2가지의 지표(메트릭스)에 의해 측정됨
  • 처리 시간(응답 시간): 어떤 특정 처리의 시작부터 종료까지 걸린 시간
  • 처리율: 특정 처리(트랜잭션)를 단위 시간에 몇 건 처리가 가능하지를 나타내는 단위 EX) 초당 50건의 트랜잭션 -> 50 TPS(Transaction Per Second). 처리율에선 초당/시간당 같은 단위 시간이 필요함
• 처리율이 성능에서 중요한 이유는 시스템의 자원 용량(Resource Capacity)를 결정하는 요인이기 때문. 처리율이 높은 시스템일수록 CPU나 메모리 같은 하드웨어 자원이 많이 필요하다.
• 동시 실행 처리 수가 늘어나는데 한 가지 자원이라도 한계에 이르는 시점이 오면 성능이 떨어진다. 이때 최초의 한계에 이른 자원을 버틀넥 포인트(Bottleneck Point), 병목이라 부른다.
• Peak(정점): 동시에 실행되는 처리가 가장 많아지는 순간. 피크 타임을 대비하지 않으면 서비스가 극단적인 지연이 일어날 수 있음
• Breaking Point(한계점): 처리율과 응답 시간이 극단적으로 나빠지기 시작하는 처리량
• Sizing(사이징), Capacity Planning(캐퍼시티 플랜): 한계점을 대비하기 위해 정점을 상정한 자원 확보
• 주기형과 돌발형에 맞게 자원을 확보해야하는데 이는 사실 쉽지 않음. 그에 맞는 대비책으론 클라우드 서비스(AWS, Azure)를 사용해서 스케일 업(Scale Up, 고성능 장비 도입)이나 스케일 아웃(Scale Out, 장비 추가 도입)을 통해 유연하게 대비를 한다.

성능을 결정하는 요인

옵티마이저가 참조하는 통계 정보

• 통계정보(Statistics)는 옵티마이저, 실행계획과 더불어 성능에 큰 영향을 주는 개념으로 옵티마이저가 실행계획을 세울 때 참조하는 정보이다. 통계정보는 아래의 데이터를 구성하고 있다.
  • 테이블의(대강의) 행수, 열수
  • 각 열의 길이와 데이터형
  • 테이블 크기
  • 열에 대한 기본키나 NOT NULL 제약 정보
  • 열 값의 분산과 편향
• 통계정보는 어림잡아 알려주는 값이기 때문에 정확하진 않을 수 있다. 통계정보를 정확히 하기 위해 테이블 풀 스캔을 하면 배보다 배꼽이 더 커질 수 있으니 주의하자

실행계획은 어떻게 세워지는가

• 실행 계획을 보고 싶으면 SQL 앞에 EXPLAIN을 붙이면 볼 수 있다.

EXPLAIN SELECT * FROM city;

+----+-------------+--------+------------+-------+---------------+-----------+---------+------+--------+----------+-------------+
| id | select_type | table  | type  | possible_keys | key       | key_len | ref  | rows   | Extra       |
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | City   | ALL   | NULL          | NULL      | NULL    | NULL | 500866 | Using index |
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+--------+----------+-------------+

• type: 테이블 액세스 방법을 나타내며 풀 스캔(ALL)과 레인지 스캔(range) 2가지가 있음. 테이블 스캔은 테이블에 포함된 레코드를 처음부터 끝까지 전부 읽어들이는 방식으로 테이블 풀 스캔이라고도 함. 레인지 스캔은 테이블의 일부 레코드만 액세스 하는 방법
• possible_keys, key: 인덱스를 사용할 수 있으면 possible_keys에 인덱스 목록들이 나오고, key에서 적용된 인덱스가 무엇인지 표시 됨

-- SHOW INDEX FROM 테이블 적용 시

+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| titles |          0 | PRIMARY   |            1 | emp_no      | A         |      251150 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| titles |          0 | PRIMARY   |            2 | from_date   | A         |      334215 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| titles |          0 | PRIMARY   |            3 | title       | A         |      500866 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| titles |          1 | ix_todate |            1 | to_date     | A         |        4813 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

• 인덱스 생성 시 명령어는 CREATE INDEX [인덱스명] ON [테이블명]([열명])
• SQL에서 내부적으로 정렬을 발생하게 하는 처리는 다음과 같이 있음. 해당 키에 인덱스가 있으면 정렬을 건너뛰어 효율화 작업을 이룰 수 있음 (EXTRA - Using temporary; Using file sort <- 정렬을 위해 임시 영역에 파일 작성)
  • GROUP BY
  • 집약 함수(COUNT/SUM/AVG 등)
  • 집합 연산(UNION/INTERSECT/EXPECT)

인덱스의 구조

• 인덱스는 데이터베이스 성능 튜닝할 때 1순위적으로 고려하는 사항이며 비앵 대비 성능이 높은 이유는 아래와 같음
  • SQL문을 변경하지 않아도 성능을 개선할 수 있음
  • 테이블의 데이터에 영향을 주지 않음
  • 일정한 (때로는 극적인) 효과를 기대할 수 있음
• 인덱스는 보통 B-TREE 구조로 이루어져 있음
  • B-TREE는 노드로 구성되어있고, 루트 노드, 브랜치 노드, 리프 노드로 트리 형태로 구성되어있음
  • B-TREE는 무조건 정렬이 되어있어야 하며 이를 통해 고속의 탐색이 가능한 것이다.
  • B-TREE는 Balanced있게 LEVEL을 조정해주기 때문에 성능 불균형이 작게 일어나도록 되어있다.

인덱스 사용 주의사항

• B-Tree 인덱스는 좋지만 무작정 만들면 오히려 성능 악화로 이어질 수 있다. 크게 두 가지로 나뻐지는 경우가 있음
• 인덱스 갱신의 오버헤드로 갱신 처리의 성능이 떨어짐
  • 인덱스는 테이블에 새로운 데이터가 추가되거나 갱신, 제거가 실행되면 자동으로 인덱스 자신도 갱신하게 됨
  • 이로 인해 인덱스 갱신이라는 부수적인 오버헤드가 발생
  • 통상 1행 정도 갱신, 제거로 인한 인덱스 갱신은 얼마 안 걸리지만 누적되면 많이 걸리게 된다.
  • SELECT문을 고속화할 수 있지만 INSERT, UPDATE 갱신 SQL의 속도가 느려질 수 있으니 테이블 상황에 맞게 작성 필요
• 의도한 것과 다른 인덱스 사용
  • 한 개의 테이블에 복수 인덱스가 작성된 경우 발생하는 문제
  • 인덱스 후보가 많아지면 옵티마이저도 헤매게 되니 너무 많은 인덱스를 만들지 않도록 유의

인덱스 만들 때 기준

• 인덱스는 조회와 갱신, 제거 간의 트레이드오프가 항상 발생하기 때문에 균형있게 작성해야 한다.
• 크기가 큰 테이블만 만든다.
  • 크기가 작은 테이블은 애당초 인덱스나 풀 스캔의 차이가 무의미함
• 기본키 제약이나 유일성 제약이 부여된 열에는 불필요함
  • 기본키 제약이나 유일성 제약이 걸린 열은 자동으로 인덱스가 작성되어 있음
• Cardinality가 높은 열에 만든다
  • 중복도가 낮은 데이터가 Cardinality가 높다 보면 된다.
  • 자동차 운전 면허는 유일하기 때문에 중복도가 낮아 카디널리티가 높은 반면, 성별은 남자와 여자 두 개로만 이루어졌기에 카디널리티가 낮고 이럴땐 오버헤드만 증가하기에 그냥 테이블 풀 스캔이 나을 수 있다.

[rawfishthelgh]

성능을 생각하자

• 성능은 처리시간과 응답시간이라는 지표에 의해 측정된다
• 처리율은 특정 처리(트랜잭션)를 단위 시간에 몇 건 처리가 가능한지를 나타낸다
• 처리율은 시스템의 자원 용량을 결정하는 요인이다. 처리율이 높은 시스템일수록 cpu나 메모리 같은 하드웨어 자원이 매우 필요하다는 것을 의미한다. 동시에 실행하는 사용자 수가 많아지면 필요한 물리 자원도 증가하기 때문이다.
• 동시 실행 처리 수가 증가할 수록 사용할 자원이 증가하는데 자원을 계속 증가시키다가도 한계가 오면 성능이 나빠진다. 응답 시간이 늘어나고, 처리율이 떨어짐.
• 이 때 한계에 이른 자원을 버틀넥 포인트, 병목이라 한다.
• 즉 시스템은 동시에 처리가 가장 많아지는 순간을 설정해서 자원을 준비해 두지 않으면, 정점일 때 극단적인 지연을 일으키게 된다는 의미다.
• 처리율과 응답 시간이 극단적으로 나빠지기 시작하는 처리량을 한계점이라고 부른다
• 정점을 상정한 자원을 확보해 두는 것을 “사이징”이나 “캐퍼시티 플랜” 이라고 한다.

주기형과 돌발형

• 업무 시스템은 성수기 실적을 조사해 성장률을 추가하면 사이징이 쉽지만, 온라인 상거래 사이트 등은 최대치 예측이 어렵다. 이렇게 변수가 많고 갑자기 액세스 집중이 일어나는 것이 돌발형 액세스다.
• 클라우드는 돌발형 액세스 집중에 대응하는 하나의 수단이다. 가상화를 기반으로 자원량을 유연하게 변동시킬 수 있는 기술이다. 스케일 업(고성능 장비 도입)과 스케일 아웃(장비 추가 도입)이 쉽게 이루어진다.
• 정점일 때 자원 증가하고 정점이 아닐 때 자원을 줄이는 동적인 자원 관리가 가능하다

데이터베이스와 병목

데이터베이스가 병목되는 이유는 다음과 같다

• 취급하는 데이터양이 가장 많다 : 데이터 총량이 계속 증가하는 추세
• 자원 증가를 통한 해결이 어렵다 : 데이터베이스는 동적 자원 증감이 어렵다. 데이터베이스의 병목 지점은 cpu나 메모리가 아닌 저장소다. 즉 대부분 하드디스크다. 저장소는 반드시 active-standby 혹은 active-active 전략을 취하므로 스케일 아웃이 어렵다(서버 계속 도입해도 저장소가 병목 됨). 저장소를 포함해 스케일 아웃이 가능한 것은 Shared Nothing(db 서버와 저장소의 세트를 늘려 병목 방지) 뿐이고, 이를 적용할 수 있는 요건은 한정되어 있다

이런 제한 때문에 데이터베이스에서는 튜닝 기술이 발달했다. 튜닝은 애플리케이션 효율화고 같은 자원으로 성능을 향상하는 것이다.

성능 결정 요인, 데이터베이스 결과 통지 과정을 보자

sql을 받아 사용자에게 결과를 통지하는 과정을 정리하자

1. 데이터베이스가 sql을 받고 구문 오류가 없는지를 보는 파스 작업을 한다. 오류나면 신택스 오류 메세지를 날림
2. 파스를 통과하면 sql문에 필요한 데이터에 어떤 경로로 접근할지 계획을 세운다. 이를 실행계획 또는 액세스 플랜이라 하며, 실행계획을 결정하는 내부 프로그램을 옵티마이저라 한다. 옵티마이저는 최적화라는 동사에서 파생됨.
   1. 사람이 아닌 옵티마이저에 실행 계획을 위임하는 이유는 데이터베이스가 더 효율적인 실행계획을 만들 수 있다고 판단하기 때문이다.
   2. 옵티마이저는 테이블의 열,행수, 열의 길이와 데이터형, 테이블 크기, 기본키나 not null 제약정보, 열 값의 분산과 편향 등의 통계정보를 고려하여 실행계획을 세운다
3. 옵티마이저는 작성한 실행계획을 평가한다
4. 실행계획을 통해 데이터에 액세스한다

통계정보 수집이 오래 걸리는 경우 오히려 성능이 떨어지게 된다. 통계정보는 이미 집약된 작은 크기에 데이터기에 다소 부정확함은 눈감아줘도 충분한 이익인 속도를 손에 넣을 수 있다.

실행계획 과정

1. 실행계획을 표시
   1. explain 명령어를 사용해 실행계획을 취득한다
   2. explain select * from city; 와 같은 문장을 실행할 수 있다
   3. tables 열에 데이터의 취득 대상 테이블이 나타나고, rows 열에 해당 select 문이 몇개의 레코드 행수에 액세스했는지 나타난다
2. 풀 스캔과 레인지 스캔
   1. explain의 type 열에는 테이블에 대한 액세스 방법이나타난다
   2. 풀 스캔(ALL)과 레인지 스캔(Range)로 구분된다
   3. 풀 스캔은 테이블의 포함된 레코드 전부를 읽고, 레인지 스캔은 테이블의 일부 레코드에만 액세스한다. 레인지 스캔은 책의 색인에서 찾는 단어가 있는 페이지만을 선택해 읽는 것과 같다
   4. sql 문에 where로 검색 조건에 따른 범위 제한을 하는 경우 레인지 스캔이 선택 된다
3. 인덱스의 중요성
   1. explain에는 possible_keys와 key 열이 존재한다. 풀 스캔에서는 null이 들어가지만, 레인지 스캔에서는 primary 키워드로 표시된다
   2. 레인지 스캔을 실행하려면 인덱스가 꼭 필요하다. 적절한 인덱스가 없다면 데이터베이스는 어쩔 수 없이 풀 스캔을 해야 한다.
   3. primary는 인덱스를 사용함을 의미한다. 정확히 말하면 “기본키(primary key)의 인덱스”를 사용함을 의미한다.
   4. 어떤 데이터베이스에서도 기본키를 구성하는 열에는 반드시 인덱스가 작성되어 있다
   5. 인덱스는 show index from 테이블명 을 검색한 결과에서 key_name 열에 이름이 나타난다.
4. 인덱스는 sql에서 만든다
   1. 선택된 행수가 전체의 극히 일부라 해도 인덱스가 없으면 풀 스캔을 선택할 수밖에 없다
   2. 인덱스는 아래와 같은 sql문으로 만들 수 있다
   3. create index 인덱스명 on 테이블명 (열명) ;
   4. 예를 들어 city 테이블명의 district 열에 “ind_districe”라는 인덱스를 건다면
   5. create index ind_districe on city (district) ; 와 같이 작성할 수 있다
   6. 사용자는 인덱스를 만들기만 할 뿐, 인덱스를 사용하는 지시는 dbms가 이를 풀 스캔보다 낫다고 인식 후 자동으로 이를 실행계획에 넣는다. 이것이 옵티마이저의 일이다.
5. 인덱스의 구조
   1. 응답 시간이 늦은 sql이 발견되면, 일단 인덱스로 해결할 수 없는지를 검사하는 것이 튜닝의 제 1선택이다
   2. 인덱스는 sql문을 변경하지 않아도 성능 개선이 가능하며, 테이블의 데이터에 영향을 주지 않고, 일정한 효과가 기대되기 때문이다. 즉 비용 대비 성능이 높다
   3. 인덱스의 구조는 트리 형태의 b-tree 구조를 가진다
   4. 트리 구조는 어떤 특정 데이터를 찾는 것을 매우 효율적이고 단시간 내에 실행할 수 있다.
   5. b-tree는 반드시 데이터를 정렬된 상태로 유지한다. 데이터가 순서를 유지하는 것은 b-tree의 핵심이다
   6. 트리의 가장 위는 루트 노드(뿌리), 가장 아래를 리프 노드(잎)라 한다. 중간 노드는 브랜치 노드(가지)라 한다.
   7. b-tree 구조의 장점은 어떤 값에 대해서도 같은 시간에 결과를 얻을 수 있는 균일성이다.
   8. 값의 대소를 비교해 분기에 들어가는 이진 탐색 방법을 사용해 데이터를 찾는다. 이는 정렬을 마친 데이터 구조를 탐색할 때 효율적이다. 가장자리에서부터 찾아들어가는 선형 탐색보다 훨씬 빠르다.
   9. b-tree는 값에 따른 성능 불균형이 작은 균형 트리이다. 균형 트리는 루트부터 리프까지의 거리가 일정한 구조이다. 하지만 데이터 갱신이 반복될 때 균형이 깨지고 성능이 약화되므로, 갱신 빈도가 잦은 테이블은 인덱스 재구성을 해서 균형을 찾아야 한다.
   10. b-tree는 데이터양이 증가할 수록 효과가 오른다. 풀 스캔이 테이블 크기에 비례해 실행시간이 늘어나는데, 인덱스를 사용할 때는 시간 저하가 거의 평평한 증가 곡선을 그린다
6. 정렬을 건너뛰는 인덱스
   1. 데이터베이스는 sql을 실행하면서 백그라운드로 정렬을 수행하는 경우도 있지만, 정렬이 끝난 인덱스를 사용하면서 이를 건너뛰는 경우가 있다.
   2. 예를 들어 group by 절을 사용하는 경우 sql에서 내부적으로 이미 정렬이 발생한다. 이 때 발생한 정렬을 인덱스에서 재사용하며 정렬을 건너뛸 수 있다
   3. sql에서 내부적으로 정렬을 발생하게 하는 처리는 다음과 같은 것들이다
      1. group by
      2. 집약 함수(count, sum, avg)
      3. 집합 연산(union, intersect, except)
7. 인덱스 작성 시 주의점
   1. 인덱스는 좋다고 마구 사용하다가 역효과가 날 수 있다.
   2. 인덱스 갱신의 오버헤드로 갱신 처리의 성능이 떨어질 수 있다
      1. 테이블에 데이터가 추가되거나 기존 데이터가 갱신 및 제거되면 자동으로 인덱스가 갱신된다. 몇천만 행이나 몇억 행 갱신을 수행하면 인덱스 갱신 시간이 걸릴 수 밖에 없다. 인덱스를 통해 select를 고속화하는 것은 insert나 update 같은 갱신 처리를 늦추는 트레이드 오프이다.
   3. 의도한 것과 다른 인덱스가 사용된다
      1. 한 개의 테이블에 복수의 인덱스를 작성한 경우 문제가 될 수 있다.
      2. 옵티마이저의 실행계획 선택은 고도화되어 있지만, 사용할 수 있는 인덱스가 많으면 옵티마이저도 헤매면서 최적의 예측을 하지 못할 수 있다.
   4. 인덱스도 만드는 만큼 저장소 용량을 소비한다. 그러나 일반 테이블에 비교하면 인덱스 크기는 수분에서 수십분의 1 정도이다. 그러나 시간이 좀 길어지는 경우도 있다
8. 인덱스를 만들 때 기준
   1. 크기가 큰 테이블만 만든다.
      1. 크기가 작으면 풀스캔이랑 차이 없음
   2. 기본키 제약이나 유일성 제약이 부여된 열에는 불필요하다.
      1. 이미 자동으로 인덱스가 작성되어 있기 때문이다. 자동으로 걸리는 이유는 값의 중복 체크를 위해서는 데이터 정렬이 필요한데, 인덱스를 작성해 정렬하는 것이 편리하기 때문이다.
   3. 카니널리티, 즉 값의 분산도가 높은 열에 만든다
      1. 특정 열에 많은 종류를 가지는 것을 카디널리티가 높다고 표현할 수 있다.
      2. 중복되지 않는 운전면허증 번호나 이메일 등이 해당한다
      3. 시도 행정구역, 성별같이 분산이 적은 데이터는 카디널리티가 낮다
      4. 카디널리티가 낮은 열은 인덱스 트리를 따라가는 조작이 증가할수록 오버헤드가 증가해 인덱스를 작성한 혜택을 받지 못하기 때문이다
9. 성능의 안티 패턴

성능은 옵티마이저가 얼마나 정밀도가 높은 실행계획은 세우냐에 달려있다. 이를 위해서는 정밀도가 높은 통계정보가 필요하고 이것이 떨어지는 것이 결국 낮은 성능을 가져온다. 아래는 통계정보의 정밀도를 낮추는 안티 패턴이다.

1. 결과 정보의 갱신이 off로 되어 있다
   1. 통계정보의 갱신이 off로 설정되면 테이블의 데이터가 변경되어도 갱신되지 않고 낡은 정보가 남는다
2. 정기 갱신을 설정하고 데이터양이 급격히 변화했다
   1. 급격히 테이블의 데이터양이 변경되고 다음 갱신 시점까지 낡은 통계정보가 사용되는 경우가 있다.
   2. 0건의 테이블에 1억개가 추가되어있는데, 옵티마이저가 해당 테이블의 데이터를 0건으로 취급할 수 있다
   3. 데이터베이스는 데이터양이 일정하게 변화하면 자동으로 통계정보를 수집하는 등으로 이를 피한다. 하지만 이런 설정으로 인해 통계정보의 수집에 시간이 걸리는 오버헤드가 발생할 수 있어 균형을 잘 맞춰야 한다.

[seokhwan-an]

10장 성능을 생각하자

성능이란

성능이란 무엇인가?

성능은 기본적으로 빠르기를 중심으로 한 개념이다.

성능을 측정하는 2가지 지표

• 성능을 측정하는 2가지 지표로는 응답시간과 처리율이 있다.
• 응답시간: 특정 처리의 시작부터 종료까지 걸린 시간을 나타낸다.
• 처리율: 속도와 같은 개념으로 특정처리를 단위 시간에 몇 건 처리가 가능한지를 나타내는 지표이다.
• 처리율이 성능에서 중요한 이유는 시스템의 자원 용량을 결정하는 요인이기 때문이다. → 처리율이 높은 시스템일수록 하드웨어 자원을 매우 필요로 한다는 것을 의미한다.

데이터베이스와 병목의 관계

데이터베이스는 왜 병목이 되는가

• 취급하는 데이터양이 가장 많다.
  • 과거에는 100GB만 되어도 대규모 였지만 현재에는 1TB도 일반적으로 다루어진다.
  • 데이터가 폭발적으로 늘어남에 따라 데이터를 보존하는 저장소 자원에서 병목이 일어나는 경우가 많다
• 지원 증가를 통한 해결이 어렵다
  • 데이터베이스 병목 지점은 CPU나 메모리가 아닌 저장소이다.
  • Shared Nothing 방식으로 스케일 아웃 해 나가는 과정이 쉽지 않다.
• 결국 데이터베이스에서는 튜닝기술이 발전했다.
  • 한정된 비용으로 성능을 높이기 위해서는 SQL이 얼마나 빠르게 동작할 수 있을까에 초점을 맞추고 발전해 왔다.

성능을 결정하는 요인

데이터베이스가 SQL문을 처리하는 과정

1. 구문 오류가 없는지를 보는 파스

요청온 SQL문의 문법을 파악하는 것으로 잘못된 SQL문으로 요청이 오면 에러를 반환한다.

1. 실행계획과 옵티마이저

요청온 SQL문에 오류가 없다면 데이터베이스에서는 어떤 방식으로 데이터에 접근할지에 대한 실행계획을 세우게 되는데 이를 실행계획이라고 하며 실행계획을 결정하는 내부 프로그램을 옵티마이저라고 한다.

사람이 아닌 옵티마이저를 통해 실행계획을 만들게 하는 이유는 사람이 직접 선택하는 것보다 컴퓨터가 선택하는 것이 정확하면서도 속도가 빠르기 때문이다. SQL문이 복잡해지면 이를 처리를 방식인 실행계획이 많아지는데 이를 직접 일일이 비교해 나가는 것은 쉽지 않다.

옵티마이저는 통계정보를 참고하여 실행계획을 생성한다. 통계정보로는 테이블의 행수 열수, 각 열의 길이와 데이터형, 테이블의 크기, 열에 대한 기본키나T NULL제약의 정보, 열 값의 분산과 편향 등이 있다. 직접 테이블 자체를 참조하는 것이 아닌 통계정보에 참조를 하는 이유는 직접 테이블을 참조를 하는 것은 비효율적인 방법이기 때문이다.

실행계획은 어떻게 세워지는가

실행계획을 표시한다.

우리가 실행하려는 SQL문 앞에 EXPLAIN을 붙이면 실행계획에 대한 정보를 확인할 수 있다.

풀 스캔과 레인지 스캔

table: 데이터가 취득되는 테이블을 의미한다.

rows: 현재 데이터 행의 개수를 의미한다.

type: 데이터를 취득하기 위해 테이블을 엑세스 하는 방식을 나타내며 풀 스캔(ALL)과 레인지 스캔(range) 2가지가 있다.

💡풀 스캔과 레인지 스캔
풀 스캔은 테이블에 포함된 모든 레코드를 처음부터 끝까지 읽어 들이는 방법
레인지 스캔은 테이블의 일부 레코드만 엑세스하는 방법이다. → 레인지 스캔은 where 절이 인덱스가 설정된 열을 범위로 정해지는 경우에 부분만 스캔하여 데이터를 엑세스한다.

인덱스의 중요성

possible Keys와 key의 경우 풀 스캔일 때에는 NULL 값을 가지지만 레인지 스캔을 하는 경우에는 PRIMARY라는 값을 가지는데 이는 인덱스를 이용한다는 것을 의미한다.

기본적으로 모든 DBMS에서는 인덱스를 이용한다. PRIMARY는 기본키로 인덱스를 설정한다는 것을 의미한다.

인덱스는 SQL에서 만든다

결국 추출해야하는 데이터가 적은 범위라도 인덱스가 없는 상황에서는 풀스캔을 하기 때문에 성능에 영향을 많이 미친다.

이와 같은 상황을 방지하기 위해서는 특정 열에도 인덱스를 추가할 수 있다.

CREATE INDEX [인덱스명] ON [테이블명]([열명]);

// 여기에 새로운 index를 추가한다.
CREATE INDEX ind_district ON City(district);

위와 같이 district 열을 인덱스를 추가한 이후에 그 열을 통해 제약조건을 걸게 되면 다음과 같은 실행계획을 볼 수 있다.

type 에 ALL이 아닌 ref가 나타나는 것을 알 수 있습니다.

이를 통해 알 수 있는 것은 우리가 직접 인덱스를 적용하는 것이 아닌 내부의 옵티마이저를 통해 실행계획이 보다 효율적인 방식으로 수행되게 설정이 된다는 것이다.

앞선 인덱스와 다르게 type에 range가 아닌 ref가 나타는데 이는 인덱스의 종류에 따라 혹은 where에 제약조건으로 등호를 사용했는지 아니면 범위를 사용했는지에 따라 다르게 나타난다. 그럼에도 두 방식 모두 레인지 스캔을 하는 것을 의미한다

인덱스가 인기가 있는 이유

인덱스를 성능 개선을 하는데 가장 먼저 고려되는 옵션이다. 인덱스를 가장 먼저 고려하는 이유는 다음과 같다.

1. SQL 문을 수정하지 않아도 성능을 개선할 수 있다.
2. 테이블의 데이터에 영향을 주지 않는다.
3. 일정한 효과를 기대할 수 있다.

즉, 인덱스는 비용 대비 성능이 높은 방법이다.

인덱스의 구조와 트리 구조의 우위성

인덱스는 B-tree 구조를 가지고 있으며 트리 구조가 효과를 나타내기 위해서는 항상 정렬된 상태를 유지해야 한다는 것입니다.

B-tree가 성능면에서 우수한 이유는 어떤 값에 대해서도 같은 시간에 결과를 얻을 수 있다 는 것이다.

예를 들어 다음과 같이 정렬된 데이터가 {개, 고양이, 곰, 말, 사슴, 여우, 코끼리} 존재하면 B-tree는 다음과 같이 정의 될 수 있다.

루트 노드에서 리프 노드로 접근할 때 탐색 과정(이진탐색)이 2번이면 모든 데이터에 접근할 수 있다.

이 부분이 효율적인 이유는 위의 데이터를 하나의 배열로 관리를 한다면 “코끼리”를 조회할 때에는 7번의 탐색과정이 필요하고 반면 “개”를 조회를 하는 경우에는 1번의 탐색과정이 필요하다. 이와 같이 배열은 탐색과정의 횟수가 불규칙하기 때문에 데이터가 많아지는 경우 성능의 편차가 발생하게 됩니다.

이와 같은 이유로 인덱스는 데이터가 많아지면 많아질수록 보다 좋은 성능을 낼 수 있다.

또다른 인덱스의 장점은 불필요한 정렬과정을 하지 않을 수 있다는 것이다.

인덱스를 사용할 때 주의할 점

• 인덱스 갱신의 오버헤드로 갱신 처리의 성능이 떨어진다.

인덱스를 이용한다는 것은 데이터가 추가 될 때마다 인덱스 자기 자신도 갱신이 필요하다는 것이다 (인덱스는 B-tree 구조를 가지고 있는데 데이터가 추가 될 때마다 이를 갱신해야한다.)

• 의도한 것과 다른 인덱스가 사용된다.

한 개의 테이블에 복수 개의 인덱스를 작성한 경우로 이런 경우에 옵티마이저가 실행계획을 정할 때 의도하지 않은 인덱스를 이용하는 경우가 있다. 이런 경우 오히려 성능이 떨이진다.

인덱스를 만들 때 기준

• 크기가 큰 테이블에서 인덱스를 고려해본다.
• Cardinality가 높은 열에 인덱스를 만든다.

Cardinality는 값의 분산도를 의미하고 특정 열에 대해 많은 종류의 값을 가지고 있다면 Cardinality가 높다고 한다. Cardinality가 작은 열에 대해서 인덱스를 추가하면 다음과 같은 이유로 성능 향상을 기대할 수 없다.

Cardinality가 작은 열에 인덱스를 추가하는 경우

• 검색 성능: 카디널리티가 작은 열은 많은 중복된 값들이 존재하므로, 인덱스를 사용하여 특정 값을 찾더라도 인덱스 트리를 따라가야 할 범위가 크게 줄어들지 않습니다. 따라서 검색 속도의 큰 향상이 기대되지 않을 수 있습니다.
• 정렬 성능: 카디널리티가 작은 열에는 정렬된 데이터가 많이 존재하지 않기 때문에 인덱스를 사용한 정렬 성능 향상의 이점이 크지 않을 수 있습니다.

[somefood]

https://velog.io/@sihyung92/query-tunning-1-execution-plan