[JSCode] Database Week3

랜덤 I/O와 순차 I/O에 대해서 설명해주세요.

랜덤 I/O(Random I/O, direct accss)와 순차 I/O(Sequential I/O) 모두 하드 디스크 드라이브의 플래터를 돌려서 읽어야 할 데이터가 저장된 위치로 디스크 헤더를 이동시킨 다음 데이터를 읽습니다. 하지만 여러개의 데이터를 읽을 때 순차 IO 는 디스크 헤드를 한번만 움직이지만, 랜덤 IO 는 디스크 헤더를 데이터 갯수만큼 움직여야 합니다. 디스크 헤더를 움직이게 되면 시간이 걸리기에 여러 번 쓰기 또는 읽기를 요청하는 랜덤 I/O 작업이 작업 부하가 훨씬 더 큽니다. 하지만 SSD에서도 랜덤 I/O는 순차 I/O보다 처리율(Throughput)이 떨어집니다. 인덱스 레인지 스캔(Index Range scan)은 데이터를 읽기 위해 주로 랜덤 I/O를 이용하고 풀 테이블 스캔(Full Table scan)은 순차 I/O를 사용합니다.

클러스터링 인덱스를 활용한 스캔은 "순차 I/O"

클러스터링 인덱스(PK)를 이용해 레인지 스캔을 하게 되면, "순차 I/O" 방식으로 동작합니다. 왜? 클러스터링 인덱스는 메모리 상에 저장된 데이터 페이지의 논리적인 위치와 물리적으로 저장된 위치가 동일한 순서로 정렬됩니다.

그래서, 여러 개의 데이터를 조회하거나 수정할 때, 바로 옆의 데이터를 조회하면 되기 때문입니다. 그래서 랜덤 I/O 방식대로 동작할 이유가 없고, 순차 I/O 방식으로 동작하는 것이 훨씬 효율적입니다.

하지만, 클러스터링 인덱스를 통해서 조회하더라도, 순서대로 조회하는 것이 아니라 띄엄띄엄 조회하게 된다면 랜덤 I/O 방식으로 동작합니다.

물론, 클러스터링 인덱스 또한 하나의 데이터에 대해 조회하거나 업데이트하는 것은 랜덤 I/O 방식이라고 봐도 무방합니다.

단, 랜덤 I/O와 순차 I/O를 구분짓는 차이점은 여러 개의 데이터를 어떻게 조회하는 것이므로 하나의 데이터를 조회하는 방법에 대해선 논의하지 않겠습니다.

세컨더리 인덱스를 활용한 스캔은 "랜덤 I/O"

세컨더리 인덱스는 클러스터링 인덱스와는 달리, 데이터 페이지의 위치와 물리적인 위치가 동일한 순서가 아닙니다. 세컨더리 인덱스는 인덱스 구조를 봐도, 물리적인 저장위치를 저장하는 것이 아니라, 클러스터링 인덱스의 위치를 저장하고 있습니다.

즉, 세컨더리 인덱스를 이용해 조회하는 명령은 여러 곳에 분산된 데이터를 조회하게 되므로, 랜덤 I/O 방식으로 동작합니다. 또한 수정하는 작업 또한 인덱스를 활용하므로, 여러 데이터를 동시에 수정하는 작업도 랜덤 I/O 방식으로 동작하게 됩니다.

인덱스에 대해서 설명해주세요.

인덱스는 데이터베이스 테이블에 대한 검색 성능의 속도를 높여주는 자료구조입니다. 특정 컬럼에 인덱스를 생성하면, 해당 컬럼의 데이터들을 정렬하여 별도의 메모리 공간에 데이터의 물리적 주소와 함께 저장된다. 또한, 인덱스 생성 시 오름차순으로 정렬하기 때문에 데이터를 읽는 것만으로도 정렬된 상태가 유지됩니다.

📎 장점: 인덱스를 사용하는 이유

👉 데이터가 정렬되어 있기 때문에 테이블에서 검색과 정렬 속도를 향상시킨다.

• 조건 검색 Where절의 효율성: 보통 Where절의 사용할 때 특정 조건에 맞는 데이터를 찾기 위해 데이터를 처음부터 끝까지 다 비교해야 하는데, 인덱스를 통해 데이터가 정렬되어 있으면 빠르게 찾아낼 수 있다.
• 정렬 Order by 정의 효율성: 인덱스를 사용하면 Order by에 의한 Sort과정을 피할 수 있다. 본래 Order by는 굉장히 부하가 많이 걸리는 작업이기 때문에 인덱스를 통해 이미 정렬되어 있으면 부하가 걸리지 않을 수 있다.
• MIN, MAX의 효율적인 처리가 가능: 이것 또한 인덱스를 통해 데이터가 정렬되어 있기 때문에 처음부터 끝까지 뒤져서 찾는 것이 아닌 인덱스로 정렬된 데이터에서 MIN, MAX를 효율적으로 추출할 수 있다.

👉 인덱스를 사용하면 테이블 행의 고유성을 강화시킬 수 있다.

👉 시스템의 전반적인 부하를 줄일 수 있다.

📎 단점: 인덱스 사용시 주의할 점

👉 인덱스의 가장 큰 문제점은 정렬된 상태를 계속 유지시켜야 한다는 점이다.

인덱스가 적용된 컬럼에 정렬을 변경시키는 INSERT, UPDATE, DELETE 명령어가 수행된다면 계속 정렬을 해주어해서 그에 따른 부하가 발생한다. 이런 부하를 최소화하기 위해 인덱스는 데이터 삭제라는 개념에서 인덱스를 사용하지 않는다 라는 작업으로 이를 대신한다.

• INSERT: 새로운 데이터에 대한 인덱스를 추가
• DELETE: 삭제하는 데이터의 인덱스를 사용하지 않는다는 작업 진행
• UPDATE: 기존의 인덱스를 사용하지 않음 처리하고, 갱신된 데이터에 대해 인덱스 추가

👉 무조건 인덱스 스캔이 좋은 것은 아니다

검색을 위주로 하는 테이블에 인덱스를 생성하는 것이 좋지만 무조건 인덱스가 검색에 좋은 것은 아니다. 예를 들어, 1개의 데이터가 있는 테이블과 100만개의 데이터가 들어 있는 테이블이 있다고 하자. 100만 개의 데이터가 들어있는 테이블이라면 풀 스캔보다는 인덱스 스캔이 유리하겠지만, 1개의 데이터가 들어있는 테이블은 익덱스 스캔보다 풀 스캔이 더 빠르다.

 

👉 속도 향상을 위해 인덱스를 많이 만드는 것이 좋지 않다.

인덱스를 관리하기 위해서는 데이터베이스의 약 10%에 해당하는 저장공간이 추가로 필요하다. 때문에 너무 많이 인덱스를 생성하면 하나의 쿼리문을 빠르게 만들 수 있지만 대신에 전체적인 데이터베이스의 성능 부하를 추래한다. 때문에 무조건적인 인덱스 생성보다 SQL문을 효율적으로 짜고, 인덱스 생성은 마지막 수단으로 사용해야한다.

인덱스의 동작 방식에 대해 설명해주세요

제가 사용한 MySQL을 기준으로 설명드리겠습니다. 먼저 Clustered Index는 개발자가 설정한 Index가 아닌 MySQL이 설정한 Index입니다. 해당 테이블에 Auto increments값으로 PK를 설정하거나, unique 컬럼, 또는 MySQL에서 내부적으로 Hidden Clustered Index Key (row ID)를 만들어 Clustered Index로 사용합니다. Non Clustered Index는 개발자가 설정한 Index를 말하는데 B-트리에 저장된 인덱스의 root node로 가서 branch node의 경로를 안내해주고 leaf node로의 경로를 알려줍니다. 그렇게 leaf node까지 도착하게 되면 leaf node는 index 의 값과 디스크의 주소값을 가지고 있어 해당 값을 참조하게 됩니다.

insert는 b-tree 넣는 과정
delete는 leaf node 찾아서 지우고 그 부모 데이터는 자식 데이터 중 가장 작은 값으로 변경
update는 delete → insert 과정

그렇다면 어떤 컬럼에 Index를 설정하면 좋을까

MySQL에서 자동 설정하는 Clustered Index의 조건을 다시한번 생각해보자. 테이블에서 모든 row에 중복이 적으면 적을수록 좋다. 카디널리티가 높은 컬럼이 좋다. 즉 데이터의 중복이 적으면 적을수록 좋다. discint foo 했을 때 랑 count(*) 가 비슷한 컬럼일수록 Index 효율이 높다.

Index와 무관하게 Full Scan으로 동작하는 경우

• 컬럼의 가공
• ≠ (부정형)
• like 앞 %
• count(*)
• 멀티 컬럼에서 두번째 컬럼을 조건으로 사용하는 경우
• 멀티 컬럼 Index 순서를 바꾸는 경우

어떤 기준으로 인덱스를 설정해야할까요?

카디널리티가 높고, 선택도가 낮으며, 활용도가 높고 수정 빈도가 낮은 컬럼을 Index로 사용하는게 좋습니다.

인덱스는 DML에 취약

INSERT, UPDATE, DELETE를 통해 데이터가 추가되거나 값이 바뀐다면 인덱스 테이블 내에 있는 값들을 다시 정렬을 해야 한다. 그리고 위에 사진처럼 인덱스 테이블, 원본 테이블 이렇게 두 군데의 데이터 수정 작업을 해줘야 한다는 단점도 발생

향상된 쿼리 성능

카디널리티가 높다는 것은 인덱싱된 열이 전체 행 수에 비해 많은 수의 고유 값을 포함한다는 것을 의미합니다. 이러한 고유성은 인덱스를 더 선택적으로 만들어 데이터베이스 엔진이 검색을 효율적으로 좁힐 수 있습니다. 선택성이 높은 인덱스를 가진 쿼리는 특정 행을 빠르게 찾을 수 있으므로 쿼리 실행 속도가 빨라집니다.

디스크 I/O 및 CPU 사용량 감소:

높은 카디널리티 인덱스를 가진 데이터베이스 엔진은 쿼리를 실행할 때 더 작은 데이터 서브셋을 스캔해야 합니다. 이는 필요한 정보를 검색하는 데 필요한 디스크 I/O 및 CPU 처리의 양을 줄입니다. 디스크 읽기 및 계산이 적을수록 전체 시스템 성능이 향상됩니다.

테이블에 인덱스를 많이 설정하면 좋을까요?

MySQL은 B-Tree를 통해 인덱스를 관리합니다. B-Tree는 인덱스 값을 기준으로 정렬된 상태를 계속 유지하기에 인덱스를 많이 설정하는 것은 좋은 방법이 아닙니다. select는 index에 의해 더 빨라지겠지만, update, delete, insert의 동작이 더 느려지게 될것이다. 새로운 데이터가 생겼을 때 index 1개면 위 과정이 1번이겠지만 index가 20개라면 20번 실행되어야 한다.

커버링 인덱스(Covering index)에 대해서 설명해주세요.

커버링 인덱스란, 쿼리를 충족시키는데 필요한 모든 데이터를 갖고 있는 인덱스를 말합니다.

 

인덱스는 조회 성능을 향상. 커버링 인덱스는 여기서 "2. 실제 데이터 접근" 의 행위 없이 인덱스에 있는 컬럼값들로만 쿼리를 완성하는 것을 이야기 합니다.

• 쿼리를 충족시킨다 : SELECT, WHERE, ORDER BY, LIMIT, GROUP BY등에서 사용되는 모든 컬럼이 인덱스 컬럼 안에 다 포함되는 경우.

장점

• DB Optimizer가 멋대로 PK를 활용한 조회 작동을 Index를 활용하도록 조회.
• 대용량 데이터 조회 시 성능 향상

다중 컬럼 인덱스(Multi-column index, 복합 인덱스)에 대해서 설명해주세요.

다중 컬럼 인덱스는 두개 이상의 필드를 조합하여 생성한 Index입니다. 첫번째 조건과 두번째 조건을 함께 index하여 검색 성능을 향상시키지만 단일 컬럼 인덱스에 비해 insert, update, delete 과정이 비효율적으로 일어나기에 업데이트가 안되는 값을 선정해야합니다.

다중 컬럼 인덱스의 정렬

• 다중 컬럼 인덱스에서 인덱스를 구성하는 컬럼은 첫번째 컬럼부터 차례대로 정렬.
• N번째 인덱스 컬럼은 N-1번째 인덱스에 의해 정렬

다중 컬럼 인덱스의 스캔

1. 인덱스 컬럼의 순서에 따른 성능 차이
   1. 다중 컬럼 인덱스 내 컬럼의 순서는 성능에 큰 영향을 미치므로 어떤 방식의 조회가 자주 일어나는지, 필터링이 어떻게 되는지 고민해야함.
2. 다중 컬럼 인덱스의 사용 여부
   1. 선행하는 컬럼를 먼저 조회해야 함.

B-Tree 인덱스와 B+Tree 인덱스에 대해 설명해주세요.

B-Tree 데이터가 정렬된 상태로 유지되어 있는 BST와 비슷한 구조이지만 자식노드가 2개 이상이 가능한 자료구조입니다. 이는 Key 값을 이용하여 찾고자하는 데이터를 트리 구조를 이용해서 찾을 수 있습니다. B+Tree는 branch node에는 key만 담아두고 data는 담지 않고, data는 오로지 leaf node에만 저장하고 leaf 노드끼리는 linkedlist로 연결되어 있는 자료구조입니다. 이는 메모리를 더욱 확보할 수 있기때문에 많은 key를 수용할 수 있습니다. 

Hash 인덱스에 대해서 설명해주세요.

Hash 인덱스는 인덱스를 해시 함수를 통해 키 값을 저장하고, 조회하는 방식을 의미합니다. 해시 인덱스는 동등 비교 검색에는 최적화되어 있지만 쿼리 조건문에 많이 사용하는 범위 검색이나 정렬에는 좋은 성능을 보이지 못합니다.

클러스터링 인덱스에 대해서 설명해주세요.

DBMS에서 테이블을 선언할 때 PK가 있을 때, 만약 PK가 없으면 unique column, 둘다 없을 경우 row id를 기반으로 선언하는 인덱스를 클러스터링 인덱스라고 합니다.

	대상	제한
Clustered Key	1) PK  2) PK가 없을땐 유니크키  3) 1~2 둘다 없을 경우 6byte의 Hidden Key를 생성 (rowid)	테이블당 1개만 존재 가능
Non Clustered Key	일반적인 인덱스	여러개 생성 가능

인덱스 스캔 방식에 대해서 설명해주세요.

인덱스 스캔 방식에는 Index Range Scan, Full Scan, Unique Scan, Skip Scan등이 있습니다.

MySQL의 인덱스의 일반적인 엑세스 방식인 Index Range Scan은 수직적으로 탐색하고 필요한 범위만큼 수평적 탐색하는 스캔 방식입니다.

Index Full Scan은 수직적 탐색없이 leaf block부터 수평적 탐색하는 방식입니다. 최적의 인덱스가 없을 때 차선으로 선택되고,

Unique Scan은 수직적 탐색으로만 데이터를 찾는 스캔 방식입니다. =조건으로 탐색하는 경우 동작하고, 범위 조건으로 검색하게 되면 Index Range Scan으로 처리됩니다.

index Skip Scan은 오라클 9i부터 제공하고 스캔방식은 조건절에 빠진 선행 인덱스 칼럼의 Distinct Value 갯수가 적고 후행 컬럼의 Distinct Value 갯수가 많을 때 유리합니다.

쿼리 실행 계획에 대해서 설명해주세요. 실행 계획을 확인해본적이 있나요?

쿼리 실행 계획(Query Plan)은 데이터베이스 관리 시스템(DBMS)이 SQL 쿼리를 처리하기 위해 사용하는 실행 계획입니다. DBMS에서 쿼리를 처리할 때 데이터베이스에서 데이터를 검색하고 가져오는 방법을 결정하는데 사용되고 쿼리 실행에 필요한 단계를 보여주며, 각 단계에서 DBMS가 사용하는 액세스 경로를 보여주고, 쿼리 실행에 필요한 리소스 및 비용 정보를 제공합니다.

특징

• 쿼리 실행에 필요한 단계를 보여준다.
• 각 단계에서 DBMS가 사용하는 액세스 경로를 보여준다.
• 쿼리 실행에 필요한 리소스 및 비용 정보를 제공한다.

장점

• 성능 문제를 식별하는 데 도움이 된다.
• 실행 계획을 변경하여 쿼리 성능을 개선할 수 있다.
• 쿼리 최적화를 위한 정보를 제공한다.

힌트에 대해서 설명해주세요.

힌트란 SQL 튜닝의 핵심 부분 옵티마이저의 실행계획을 조작하는 지시 구문입니다. 오라클 옵티마이저(Optimizer)에게 SQL문 실행을 위한 데이터를 스캐닝하는 경로, 조인하는 방법 등을 알려주기 위해 SQL사용자가 SQL 구문에 직접 작성하는 것을 뜻한다. 오라클이 항상 최적의 실행 경로를 만들어 내기는 불가능하기 때문에  직접 최적의 실행 경로를 작성해 주는 것입니다. 사용자가 특정 SQL 문장에서 어떤 인덱스가 선택도가 높은지 알고 있는 경우 Optimizer에 의존한 실행 계획보다 훨씬 효율적인 실행 계획을 구사할 수 있습니다.

인덱스가 잘 동작하고 있는지 어떻게 확인할 수 있을까요?

실행 계획을 통해서 실행 시간을 확인할 수 있습니다.

인덱스 사용시 주의해야할 점에 대해서 알려주세요.

너무 많은 칼럼을 인덱스로 선언할 시 MySQL기준 Insert, Update, Delete에 성능이 떨어지고 메모리를 많이 차지하게 됩니다. 또 카디널리티가 높은 컬럼을 기준으로 선언해야합니다.

GROUP BY 사용시 인덱스가 걸리는 조건에 대해 설명해주세요.

인덱스 칼럼의 순서와 같아야하고, 뒤에 있는 컬럼이 GROUP BY 절에 명시되지 않아도 사용이 가능합니다. 하지만 인덱스가 선행되는 컬럼이 먼저 선언되어 있지 않으면 인덱스를 사용할 수 없습니다.

이름, 국가, 성별이 있는 테이블에서 인덱스를 어떻게 걸어야할까요?

인덱스는 카디널리티가 높은 컬럼을 선택하는 것이 더욱 효율적입니다. 따라서 이름을 선행칼럼으로 잡고, 다음이 국가, 그 다음이 성별로 인덱스를 설정하는 것이 효과적입니다.