티스토리 뷰
1. 랜덤 I/O와 순차 I/O에 대해서 설명해주세요.
순차 I/O
데이터를 읽거나 쓰려고 하는 위치가 연속적으로 있어 디스크 헤더를 한번만 이동시켜 데이터를 읽거나 쓰는 방법
랜덤 I/O
데이터의 위치가 불연속적으로 위치해 있어 디스크 헤더를 여러번 이동시켜 데이터를 읽거나 쓰는 방법
디스크 헤더를 이동시켜야 하는 횟수가 많아질수록 성능이 느려진다. 이때문에 랜덤 I/O가 순차 I/O에 비해 성능이 꽤 느리다.
SSD
SSD는 HDD와 달리 디스크 원판을 사용하지 않고 플래시 메모리를 사용한다. 그래서 디스크 원판을 기계적으로 회전시킬 필요가 없으므로 아주 빨리 데이터를 읽고 쓸 수 있다.
디스크의 헤더를 움직이지 않고 한 번에 많은 데이터를 읽는 순차 I/O에서는 SSD가 하드 디스크 드라이브보다 조금 빠르거나 거의 비슷한 성능을 보이기도 한다. 하지만 기존 하드 디스크 드라이브보다 랜덤 I/O가 훨씬 빠르다. 데이터베이스 서버에서 순차 I/O 작업은 그다지 비중
이 크지 않고 랜덤 I/O를 통해 작은 데이터를 읽고 쓰는 작업이 대부분이므로 SSD 장점은 DBMS 용 스토리지에 최적이라고 볼 수 있다.
2. 인덱스에 대해서 설명해주세요.
인덱스란?
인덱스란 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조이다.
인덱스가 없을때 특정 값과 일치하는 레코드를 조회한다면 데이터베이스 테이블의 모든 데이터를 검색해서 원하는 결과를 가져오기 때문에 시간이 오래 걸린다. 그래서 컬럼의 값과 해당 레코드가 저장된 주소를 키와 값의 쌍으로 삼아 인덱스를 만들어 두어 더 빠르게 찾게하는 것이다. 그리고 최대한 빠르게 찾아갈 수 있게 컬럼의 값을 주어진 순서로 미리 정렬해서 보관한다.
인덱스의 오버헤드
인덱스를 항상 최신의 정렬된 상태로 유지해야 원하는 값을 빠르게 탐색할 수 있다. 그렇기 때문에 인덱스가 적용된 컬럼에 INSERT, DELETE, UPDATE가 수행된다면 최신의 정렬 상태로 유지하기 위해 추가적인 작업을 하게 되고, 그에 따른 오버헤드가 발생한다.
3. 인덱스의 동작 방식에 대해서 설명해주세요.
사용자 요청으로 들어온 SELECT 쿼리의 WHERE에 인덱스가 걸린 컬럼이 조건으로 들어있다면, 컬럼 값에 해당하는 레코드를 찾기위해 B Tree의 루트 노드부터 시작해서 브랜치 노드를 거쳐 해당하는 리프 노드를 찾는다. 그 다음 리프 노드에 있는 레코드의 실제 주솟값을 따라 데이터 파일에서 레코드를 조회 한다.
4. 어떤 기준으로 인덱스를 설정해야할까요?
조회 조건으로 많이 사용되고 카디널리티가 높은 컬럼을 기준으로 인덱스를 설정해야한다.
카디널리티가 높다는 것은 인덱스에 해당하는 컬럼 기준으로 테이블에서 유일한 레코드 개수가 많다는 의미이다. 카디널리티가 높은 열은 데이터 값이 고유한 경우가 많아, 검색 조건이 주어졌을 때 더 작은 데이터 범위를 탐색하게 된다. 반면 카디널리티가 낮은 열이라면 조건에 해당하는 데이터가 많아져 검색 범위가 넓어지고, 성능이 저하될 수 있다.
5. 테이블에 인덱스를 많이 설정하면 좋을까요?
인덱스를 많이 설정하면 좋지 않다. 인덱스 자체를 저장하는 공간이 필요하기도 하고, 인덱스는 빠른 검색을 위해 항상 정렬된 상태를 유지해야 하므로, 인덱스가 많아진다면 데이터 삽입, 수정, 삭제 성능이 저하될 우려가 있다.
6. 커버링 인덱스(Covering index)에 대해서 설명해주세요.
커버링 인덱스는 쿼리를 충족하는데 필요한 모든 데이터를 갖는 인덱스를 뜻한다. SELECT / WHERE / GROUP BY / ORDER BY 등에 활용되는 모든 컬럼이 인덱스의 구성 요소인 경우를 말한다.
커버링 인덱스를 잘 쓰면(특히, 대용량 데이터 처리 시), 조회 성능을 상당 부분 높일 수 있다.
필요한 데이터가 모두 인덱스에 있기 때문에 실제 데이터에 접근하는 과정을 생략할 수 있어 조회 성능을 높일 수 있다.
7. 다중 컬럼 인덱스(Multi-column index, 복합 인덱스)에 대해서 설명해주세요.
두 개 이상의 컬럼으로 구성된 인덱스를 다중 컬럼 인덱스(또는 복합 컬럼 인덱스)라고 하며, 또한 2개 이상의 컬럼이 연결됐다고 해서 Concatenated Index라고도 한다.
다중 컬럼 인덱스에서 중요한 것은 인덱스의 두 번째 컬럼은 첫 번째 컬럼에 의존해서 정렬된다는 것이다. 만약 컬럼이 4개인 인덱스를 생성한다면 세 번째 컬럼은 두 번째 컬럼에 의존해서 정렬되고 네 번째 컬럼은 다시 세 번째 컬럼에 의존해서 정렬된다.
8. B-Tree 인덱스와 B+Tree 인덱스에 대해 설명해주세요.
B-Tree는 데이터베이스에서 널리 사용되는 트리 자료구조의 일종으로, 이진 트리를 확장해 하나의 노드가 가질 수 있는 자식 노드의 최대 숫자가 2보다 큰 트리 구조이다. root node, iternal node, leaf node로 구성되며, 리프 노드가 모두 같은 레벨에 존재하는 balanced tree이다.
B+Tree는 B-Tree를 기반으로 한 자료구조로, 데이터베이스에서 효율적인 검색과 범위 쿼리를 처리하기 위해 설계되었다.
B-Tree는 모든 노드에 키와 데이터를 저장하지만 B+Tree는 데이터를 리프 노드에만 저장하고, 내부 노드에는 키 값만 저장 다. 이를 통해 내부 노드가 더 작아지고 더 많은 키를 담을 수 있다. 이로 인해 트리의 높이가 낮아져 검색 속도가 향상 된다.
또한 B+트리의 리프 노드는 링크드 리스트 처럼 서로 연결되어 있다. 이 특징은 범위 검색에 매우 유리하다. 예를 들어, 특정 범위의 데이터를 검색할 때 리프 노드를 따라가기만 하면 되므로 속도가 빠르다.
9. Hash 인덱스에 대해서 설명해주세요.
Hash 인덱스 알고리즘은 컬럼의 값으로 해시값을 계산해서 인덱싱하는 알고리즘으로, 매우 빠른 검색을 지원한다. 하지만 값을 변행해서 인덱싱하므로 전방(Prefix) 일치와 같이 일부만 검색하거나 범위를 검색할 때는 해시 인덱스를 사용할 수 없다.
10. 클러스터링 인덱스에 대해서 설명해주세요.
프라이머리 키 값이 비슷한 레코드끼리 묶어서 저장하는 것을 클러스터링 인덱스라고 표현한다. 프라이머리 키 값에 의해 레코드의 저장 위치가 결정된다. 또한 프라이머리 키 값이 변경된다면 그 레코드의 물리적인 저장 위치가 바뀌어야 한다.
프라이머리 키 값으로 클러스터링된 테이블은 프라이머리 키 값 자체에 대한 의존도가 상당히 크기 때문에 신중히 프라이머리 키를 결정해야 한다.
장점
- 프라이머리 키로 검색할 때 처리 성능이 매우 빠름
단점
- INSERT할 때 프라이머리 키에 의해 레코드의 저장 위치가 결정되기 때문에 처리 성능이 느림
- 프라이머리 키를 변경할 때 레코드를 DELETE 하고 INSERT하는 작업이 필요하기 때문에 처리 성능이 느림
11. 인덱스 스캔 방식에 대해서 설명해주세요.
인덱 레인지 스캔
인덱스 레인지 스캔은 검색 해야할 인덱스의 범위가 결정 됐을때 사용하는 방식이다. 루트 노드, 브랜치 노드를 거쳐 검색 시작점 리프 노드를 찾고, 리프 노드간의 링크를 통해 검색 종료점 리프 노드까지 스캔하면서 레코드를 랜덤 엑세스로 읽어 들인다.
인덱스 풀 스캔
인덱스를 사용하지만 인덱스 레인지 스캔과는 달리 인덱스의 처음부터 끝까지 모두 읽는 방식을 인덱스 풀 스캔이라고 한다. 먼저 인덱스 리프 노드의 제일 앞 또는 제일 뒤로 이동한 후, 인덱스의 리프 노드를 연결하는 링크드 리스트를 따라서 처음부터 끝까지 스캔하는 방식이다. 이방식은 인덱스 레인지 스캔보다는 빠르지 않지만 테이블 풀 스캔보다는 효율적이다. 일반적으로 인덱스의 크기는 테이블의 크기보다 작으므로 직접 테이블을 처음부터 끝까지 읽는 것보다는 인덱스만 읽는 것이 효율적이기 때문
루스 인덱스 스캔
말 그로 느슨하게 또는 듬성등성하게 인덱스를 읽는 것을 의미한다. 루스 인덱스 스캔은 인덱스 레인지 스캔과 비슷하게 작동하지만 중간에 필요치 않은 인덱스 키 값은 무시(SKIP) 하고 다음으로 넘어가는 형태로 처리한다.
일반적으로 GROUP BY 또는 집합 함수 가운데 MAX() 또는 MIN() 함수에 대해 최적화를 하는 경우에 사용된다.
인덱스 스킵 스캔
복합 인덱스를 사용할때 첫번째 순서로 둔 컬럼이 아닌 다음 순서를 조회조건으로 둔다면 인덱스를 활용하지 못한다. 하지만 MySQL 8.0버전부터는 옵티마이저가 복합 인덱스의 첫 번째 순서의 컬럼을 건너뛰어서 다음 순서의 컬럼만으로도 인덱스 검색이 가능하게 해주는 인덱스 스킵 스캔 최적화 기능을 도입했다.
gender, birth_date 순서로 인덱스를 만들었을때 birth_date가 2000-12-02보다 크거나 같다는 조건을 가지고 select 쿼리를 한다고 해보자
SELECT gender, birth_date
FROM employees
WHERE birth_date >= '2000-12-02'
위의 쿼리는 WHERE 조건절에 gender 컬럼에 대한 조건 없이 birth_date 컬럼의 비교 조건만 가지고 있기 때문에 쉽게 인덱스를 효율적으로 이용할 수 없다. 위 쿼리에서 인덱스 스킵 스캔을 off로 한다면 풀 인덱스 스캔을 이용해 쿼리가 수행된다.
인덱스 스킵 스캔 활성화 한다면 gender 컬럼에서 유니크한 값을 모두 조회해서 주어진 쿼리에 gender 컬럼의 조건을 추가해서 쿼리를 다시 실행하는 형태로 처리한다.
SELECT gender, birth_date FROM employees WHERE gender = 'M' AND birth_date >= 2000-12-02;
SELECT gender, birth_date FROM employees WHERE gender = 'F' AND birth_date >= 2000-12-02;
인덱스 스킵 스캔은 MySQL 8.0 버전에 새로 도입된 기능이어서 아직 다음과 같은 단점이 있다.
- WHERE 조건절에 조건이 없는 인덱스의 선행 컬럼의 유니크한 값의 개수가 작아야함
- 쿼리가 인덱스에 존재하는 컬럼만으로 처리 가능해야 함 (커버링 인덱스)
첫 번째 조건은 쿼리 실행 계획의 비용과 관련된 부분인데, 만약 유니크한 값의 개수가 매우 많다면 MySQL 옵티마이저는 인덱스에서 스킨해야 할 시작 지점을 검색하는 작업이 많이 필요해진다. 그래서 쿼리의 처리 성능이 오히려 더 느려질 수도 있다.
두 번째 제약 조건은 아래 예제 쿼리를 통해 살펴보자
SELECT *
FROM employees
WHERE birth_date >= '2000-12-02'
위의 쿼리는 WHERE 조건절은 동일하지만 SELECT 절에서 employees 테이블의 모든 컬럼을 조회하도록 변경했다. 이 쿼리는 인덱스에 포함된 gender 컬럼과 birth_date 컬럼 이외의 나머지 컬럼도 필요하기 대문에 인덱스 스킵 스캔을 사용하지 못하고 풀 테이블 스캔으로 실행 계획을 수립한 것을 확인할 수 있다.
12. 쿼리 실행 계획에 대해서 설명해주세요. 실행 계획을 확인해본적이 있나요?
쿼리 실행 계획이란 DBMS 내부에서 어떤 실행 절차대로 SQL문이 수행되는지를 나타낸다. MySQL에서는 EXPLAIN 문을 통해 실행 계획을 확인할 수 있다.
13. 힌트에 대해서 설명해주세요.
쿼리 힌트는 데이터베이스 옵티마이저에게 특정 실행 계획을 사용하도록 지시하는 역할을 한다. 힌트를 사용하여 옵티마이저가 쿼리를 처리하는 방식을 직접 제어할 수 있다. 힌트는 주로 성능 최적화나 예상치 못한 실행 계획을 수정하고자 할 때 사용된다.
14. 인덱스가 잘 동작하고 있는지 어떻게 확인할 수 있을까요?
쿼리에 대한 실행 계획을 보면서 인덱스가 어떻게 걸리는지 확인할 수 있다.
15. 인덱스 사용시 주의해야할 점에 대해서 알려주세요.
인덱스의 키 값이 너무 길어지게 하지 않는다. 인덱스 키 값이 길어지면 인덱스 정보들을 담고 있는 페이지 개수가 많아진다. 이는 디스크로부터 페이지를 읽어야 하는 횟수가 늘어나는 걸 뜻하고, 랜덤 엑세스 횟수가 증가하는 것을 뜻하기 때문에 조회 속도가 느려지는 결과를 가져올 수 있다.
16. GROUP BY 사용시 인덱스가 걸리는 조건에 대해 설명해주세요.
GROUP BY 절에 명시된 컬럼이 인덱스 컬럼의 순서와 위치가 같아야한다. 인덱스를 구성하는 컬럼 중에서 뒷쪽에 있는 컬럼은 GROUP BY 절에 명시되지 않아도 인덱스를 사용할 수 있지만 인덱스의 앞쪽에 있는 컬럼이 GROUP BY 절에 명시되지 않으면 인덱스를 사용할 수 없다.