쿼리에서 참조되는 컬럼이 인덱스에 모두 포함되는 경우가 아니라면 인덱스 스캔 이후 테이블 랜덤 액세스가 발생한다.
이는 잦은 블록 I/O를 발생시켜 성능 저하의 원인이 될 수 있다.
디스크 I/O 종류
디스크의 순차 I/O는 데이터를 연속적인 순서로 접근하는 방식이며, 디스크의 랜덤 I/O는 데이터를 임의의 순서로 접근하느 방식이다.
하드디스크에서 파일을 읽을 떄, 랜덤 I/O는 파일의 특정 부분만 읽거나 쓰기 위해 디스크 헤더를 움직이는 방식이고, 순차 I/O는 파일의 처음부터 끝까지 읽거나 쓰기 위해 디스크 헤더를 한 방향으로 움직이는 방식이다.
DB서버에서 순차 I/O와 랜덤 I/O가 발생하는 상황은 아래와 같다.
순차 I/O
1, 테이블의 모든 데이터를 조회하는 상황
2, 대량의 데이터를 정렬하거나 그룹화 하는 상황
3, 풀 테이블 스캔
랜덤 I/O
1, WHERE 조건이 포함된 쿼리를 실행해 데이터를 조회하는 상황
2, WHERE 조건이 포함된 쿼리를 실행해 데이터를 삭제하거나 수정하는 상황
3, 인덱스 레인지 스캔
순차 I/O는 디스크에서 연속적인 데이터를 읽거나 쓰기 떄문에, 대량의 데이터를 처리하는 데 좋은 성능을 보인다.
랜덤 I/O는 순차 I/O보다 원하는 데이터를 빠르게 찾을 수 있지만, 디스크의 헤드가 여러 위치를 탐색해야 하기 때문에, 대량의 데이터를 처리하는데 비교적 느린 작업이다.
따라서 디스크의 성능은 얼마나 헤드의 이동 없이 많은 데이터를 순차적으로 저장하는가에 달려있다.
즉, 랜덤 I/O를 줄이는 것이 성능 개선에 중요합니다.
랜덤 I/O 종류
1. 확인 랜덤 액세스
WHERE, HAVING 조건의 컬럼이 인덱스에 존재하지 않아 테이블을 액세스하는 랜덤 액세스이다.
확인 랜덤 액세스의 특징은 랜덤 액세스의 횟수보다 최종 결과가 동일하거나 적게 추출된다.
SELECT * FROM 사원 테이블 WHERE 이름 = "" AND 사업장코드 = ""
사원 테이블에 "이름" 컬럼에만 인덱스가 존재한다면, 위 SQL이 실행되면 이름 컬럼에 의해 인덱스를 액세스하고 처리 범위가 좁혀질 것이다.
그러나, "사업장코드"는 인덱스로 설정되어 있지 않기 때문에 결국 "이름" 조건을 만족하는 모든 데이터에 대해 테이블을 액세스 하여 "사업장코드" 컬럼의 값을 확인하여 조건을 부합하는 값을 찾게 된다.
이처럼 WHERE 조건의 컬럼이 인덱스에 존재하지 않아 테이블 랜덤 액세스를 발생시키는 것을 확인 랜덤 액세스라고 한다.
테이블을 액세스 한 후 버려지는 데이터가 존재하기 떄문에, 랜덤 액세스의 3가지 종류 중에서도 확인 랜덤 액세스의 제거는 성능에 있어 매우 중요하다.
2. 추출 랜덤 액세스
인덱스 액세스 후 SELECT 절의 컬럼을 결과로 추출하기 위해 추가로 테이블을 액세스한다. 추출 랜덤 액세스의 특징은 랜덤 액세스 횟수와 추출 데이터의 양이 동일하며, SELECT 절에서 발생한다는 것이다.
WHERE 조건에 사용되는 컬럼들이 모두 인덱스에 존재하지만, SELECT 절의 컬럼들에 인덱스가 포함되지 않는 컬럼이 있다면 인덱스 액세스 이후에 추가로 테이블에 액세스 해야된다. 이와 같은 현상이 추출 랜덤 액세스이다.
SELECT 절의 컬럼들은 추출되는 데이터를 감소시키거나 증가시키지 않기 때문에, 랜덤 액세스 횟수와 추출 데이터의 양이 동일하다. 따라서 추출 랜덤 액세스는 WHERE 조건의 조회 결과만큼 발생하게 된다.
3. 정렬 랜덤 액세스
ORDER BY, GROUP BY절 컬럼이 인덱스에 존재하지 않아 추가적으로 테이블 액세스한다. 정렬 랜덤 액세스의 특징은 랜덤액세스와 추출 데이터의 양이 동일하며, ORDER BY, GROUP BY 절에서 발생한다.
SELECT 이후에, ORDER BY 절이나 GROUP BY절에 사용되는 컬럼에 인덱스가 존재하지 않을 때에 발생한다.
정렬 랜덤 액세스의 양도 추출 랜덤 액세스와 마찬가지로 결과의 양과 동일하다.
그렇기 떄문에, 랜덤 액세스 중 추출되는 데이터를 감소시키는 확인 랜덤 액세스를 감소시키는 방안이 성능 측면에서 가장 중요하다.
랜덤 액세스 최소화
확인 랜덤 액세스
WHERE 조건에는 인덱스로 설정된 컬럼을 사용하는 것이 좋다. 만약, 인덱스로 설정되지 않은 컬럼을 사용해야 된다면 그 컬럼을 인덱스로 설정하는 것을 고려해야된다.
하지만, 인덱스의 추가 또는 삭제가 운영 중인 시스템에서는 매우 위험한 작업일 수 있다.
인덱스를 생성 시 인덱스를 생성하는 과정에서 CPU, 메모리 I/O 부하가 크다.
인덱스를 삭제 시 내부적으로 데이터 사전과 옵티마이저 통계가 바뀌고, 관련 객체에 락이 걸리 수 있기 떄문이다.
*데이터 사전(Data Dictionary AKA System Catalog)
대부분 읽기전용으로 제공되는 테이블 및 뷰들의 집합으로, 데이터베이스 전반에 대한 정보를 제공한다. 데이터베이스의 데이터를 제외한 모든 정보-데이터에 대한 데이터(메타데이터)가 들어있다. 데이터 사전의 내용을 변경하는 권한은 시스템이 가진다.
데이터 사전에 저장되는 내용
데이터베이스 사용자 정보, 권한과 롤 정보, 데이터베이스 스키마 객체(TABLE, VIEW 등)의 정보
데이터 사전에는 데이터베이스 운영에 중요한 데이터들이 저장되기 때문에, 데이터 사전에 문제가 발생할 시 데이터베이스 사용이 불가능해질 수 있다.
데이터 사전도 데이터를 저장하는 데이터베이스의 일종이기 때문에 시스템 데이터베이스라고도 ㅎ산다. 데이터 사전은 DBMS가 스스로 생성하고 유지하는 것으로, DBMS가 주로 접근하짐나 일반 사용자도 접근 가능하다. 단, 조회만 가능
