인덱스
데이터베이스에서 인덱스와 실제 데이터가 저장된 데이터는 따로 관리된다. 인덱스의 리프 노드는 항상 실제 데이터
레코드를 찾아가기 위한 주소 값을 가지고 있다. 인덱스는 테이블의 키 값만 가지고 있으므로 나머지 칼럼을
읽으려면 데이터 파일에서 해당 레코드를 찾아야 한다. 오라클은 물리적 레코드 주소가 되지만 MySQL 테이블은
내부적인 레코드 아이디를 의미한다. InnoDB 테이블에서는 프라이머리 키에 의해 클러스터링되기 때문에
프라이머리 키 값 자체가 주소 역할을 한다.
B-Tree 알고리즘: 칼럼을 변형하지 않고, 원래의 값을 이용하여 인덱싱하는 알고리즘
Hash 알고리즘: 칼럼의 값으로 해시 값을 계산해서 인덱싱하는 알고리즘. 빠른 검색 지원.
Fractal-Tree 알고리즘: B-Tree의 단점 보완. 데이터 저장이나 삭제 시 처리 비용을 상당히 절약.
B-Tree 인덱스
새로운 키 값이 B-Tree에 저장될 때 테이블의 스토리지 엔진에 따라 새로운 키 값이 즉시 인덱스에 저장될 수도
있고 그렇지 않을 수도 있다. B-Tree에 저장될 때는 저장될 키 값을 이용해 B-Tree상의 적절한 위치를 검색해야
한다. 저장될 위치가 결정되면 레코드의 키 값과 대상 레코드의 주소 정보를 B-Tree의 리프 노드에 저장한다.
만약, 리프 노드가 꽉 차서 더는 저장할 수 없을 때는 리프 노드가 분리(Split)돼야 한다. 이는 상위 브랜치 노드까지
처리의 범위가 넓어진다. 이러한 작업 탓에 B-Tree는 상대적으로 쓰기 작업에 비용이 많이 든다.
MyISAM이나 Memory 스토리지 엔진을 사용하는 테이블에서는 INSERT 문장이 실행되면 즉시 새로운 키 값을
B-Tree 인덱스에 반영한다. 즉 B-Tree에 키를 추가하는 작업이 완료될 때까지 클라이언트는 결과를 받아보지 못한다.
MyISAM 스토리지 엔진에 “delay-key-write” 파라미터를 설정해 인덱스 키 추가 작업을 지연하여 처리할 수 있는데
이는 동시 작업 환경에서는 적합하지 않다. InnoDB 스토리지 엔진은 상황에 따라 적절하게 인덱스 키 추가 작업을
지연시켜 나중에 처리할지, 아니면 바로 처리할지 결정한다.
Innodb_insert_buffer(MySQL5.1), innodb_change_buffering(MySQL5.5)
인덱스의 키 값은 그 값에 따라 저장될 리프 노드의 위치가 결정되므로 B-Tree의 키 값이 변경되는 경우에는 단순희
인덱스상의 키 값만 변경하는 것은 불가능하다. B-Tree의 키 값 변경 작업은 먼저 키 값을 삭제한 후, 다시 새로운
키 값을 추가하는 형태로 처리.
인덱스의 키 값에 변형이 가해지 후 비교되는 경우에는 B-Tree의 검색 기능을 사용할 수 없다. 이미 변형된 값은
B-Tree 인덱스에 존재하지 않는 값이다.
InnoDB 스토리지 엔진에서는 검색을 수행한 인덱스를 레코드 잠금이나 넥스트 키 락을 한다. 적절히 사용할 수
있는 인덱스가 없으면 불필요하게 많은 레코드를 잠근다. InnoDB에서는 인덱스 설계가 중요하다.
InnoDB 스토리지 엔진은 디스크에 데이터를 저장하는 기본 단위를 페이지(Page) 또는 블록(Block)이라고 하며,
디스크의 읽기 및 쓰기 작업의 최소 작업 단위가 된다. 그리고, 버퍼 풀에서 데이터를 버퍼링하는 기본 단위이기도
하다. 인덱스도 결국 페이지 단위로 관리된다. InnoDB의 모든 페이지 크기는 16KB로 고정돼 있다.
인덱스 페이지 구성은 [키 값][자식 노드 주소]로 구성된다. 예)[16바이트 키 값][12바이트 주소]
16*1024 / (16+12) = 585개 – 한 페이지에 585개의 인덱스 저장
인덱스를 구성하는 키 값의 크기가 커지면 한 페이지에 들어가는 인덱스의 수가 감소하고, 디스크로부터 읽어야
하는 횟수가 증가한다. 즉, 그만큼 느려진다. 키 값은 작을수록 좋다.
인덱스의 키 값의 길이가 길어진다는 것은 전체적인 인덱스의 크기가 커진다는 의미이다.
인덱스를 캐시하고 있는 InnoDB의 버퍼 풀이나 MyISAM의 키 캐시 영역은 제한적이고, 메모리 버퍼를 사용하기
때문에 메모리 효율이 떨어진다.
인덱스 레인지 스캔: 인덱스 레인지 스캔은 검색해야 할 인덱스의 범위가 결정됐을 때 사용하는 방식.
만약, 3건의 레코드가 검색 조건에 일치했다고 가정하면 데이터 레코드를 읽기 위해 랜덤 I/O가 최대 3번
발생. 인덱스를 통해 데이터 레코드를 읽는 작업은 비용이 많이 드는 작업으로 분류.
인덱스를 통해 읽어야 할 데이터 레코드가 20~25%를 넘으면 인덱스를 통한 읽기보다 테이블의 데이터를
직접 읽는 것이 더 효율적이다.
인덱스 풀 스캔: 인덱스의 크기는 테이블의 크기보다 작으므로 직접 테이블을 읽는 것보다 인덱스만 읽는 것이
효율적이다. 쿼리가 인덱스에 명시된 칼럼만으로 조건을 처리할 수 있는 경우 주로 이 방식을 사용한다.
루스 인덱스 스캔: 오라클 DB의 ‘인덱스 스킵 스캔’과 비슷한 동작. 제한적 동작
다중 컬럼 인덱스
B-Tree 인덱스의 정렬 및 스캔 방향
인덱스를 어느 방향으로 읽을지는 쿼리에 따라 옵티마이저가 실시간으로 만들어내는 실행 계획에 따라 결정
B-Tree 인덱스의 가용성과 효율성
인덱스를 사용할 수 없는 경우:
- NOT-EQUAL로 비교된 경우. (<>, NOT IN, NOT BETWEEN, IS NOT NULL)
- LIKE ‘%??’ (앞 부분이 아닌 뒷부분 일치) 형태로 문자열 패턴이 비교된 경우
- 스토어드 함수나 다른 연산자로 인덱스 컬럼이 변형된 후 비교된 경우
- NOT-DETERMINISTIC 속성의 스토어드 함수가 비교 조건에 사용된 경우
- 데이터 타입이 서로 다른 비교 (인덱스 칼럼의 타입을 변환해야 비교가 가능한 경우)
- 문자열 데이터 타입의 콜레이션이 다른 경우
전문 검색 (Full Text) 인덱스
옵티마이저
옵티마이저는 비용 기반 최적화(Cost-based optimizer) 방법과 규칙 기반 최적화 방법(Rule-based optimizer)으로
크게 나뉘어진다.
규칙 기반 최적화는 기본적으로 대상 테이블의 레코드 건수나 선택도 등을 고려하지 않고 옵티마이저에 내장된
우선순위에 따라 실행 계획을 수립하는 방식. 이 방식에서는 통계 정보를 조사하지 않고 실행 계획이 수립되기
때문에 같은 쿼리에 대해서는 거의 항상 같은 실행 방법을 만든다. 잘 사용되지 않는다.
비용 기반 최적화는 쿼리를 처리하기 위한 여러 가지 가능한 방법을 만들고, 각 단위 작업의 비용 정보와 대상
테이블의 예측된 통계 정보를 이용해 각 실행 계획 별 비용을 산출한다. 이렇게 산출된 각 실행 방법 별로 최소
비용이 소요되는 처리 방식을 선택해 최종 쿼리를 실행한다.
비용 기반 최적화에서 가장 중요한 것은 통계 정보다.
MySQL에서 관리되는 통계 정보는 레코드 건수와 인덱스의 유니크한 값의 개수 정도.
mysql> EXPLAIN select * from table_name;결과>
id |
select_type |
table |
type |
key |
key_len |
ref |
rows |
extra |
표 형태로 된 1줄 이상의 결과가 표시된다. 표의 각 라인은 쿼리 문장에서 사용된 테이블(서브 쿼리의 임시 테이블
포함)의 개수만큼 출력된다. 출력된 실행 계획에서 위쪽에 출력된 결과일수록 쿼리의 바깥 부분이나 먼저 접근한
테이블이고, 아래쪽에 출력된 결과일수록 쿼리의 안쪽 부분 또는 나중에 접근한 테이블에 해당된다.
id: 단위 select 쿼리 별로 부여되는 식별자 값. 하나의 select 문장에 여러 개의 테이블을 조인하면 조인되는
테이블의 개수만큼 실행 계획의 레코드가 출력되지만 같은 id값이 부여된다.
select_type: 각 단위 select 쿼리가 어떤 타입의 쿼리인지 표시되는 칼럼.
SIMPLE/ PRIMARY/ UNION/ DEPENDENT UNION/ UNION RESULT/ SUBQUERY/ DEPENDENT SUBQUERY/
DERIVED/ UNCACHEABLE SUBQUERY/ UNCACHEABLE UNION/
table: 실행 계획은 단위 SELECT 쿼리 기준이 아니라 테이블 기준으로 표시. <derived> 또는 <union>과 같이
<>로 둘러싸인 테이블은 임시 테이블을 의미. <>안에 표시되는 숫자는 단위 select 쿼리의 id를 의미
<derived2>는 단위 select 쿼리의 아이디 2번인 실행 계획으로부터 만들어진 파생 테이블
type: 쿼리 실행 계획에서 type 이후의 칼럼은 MySQL 서버가 각 테이블의 레코드를 어떤 방식으로 읽었는지 의미.
인덱스를 사용해 레코드를 읽었는지 아니면 테이블을 처음부터 끝까지 읽는 풀 테이블 스캔으로 읽었는지 의미
system/ 레코드가 1건만 존재하는 테이블 또는 한 건도 존재하지 않는 테이블을 참조하는 형태
const/ PK 또는 Unique Key를 이용하는 WHERE 절을 사용, 반드시 1건만 반환하는 쿼리 처리 방식
eq_ref/ 여러 테이블이 조인되는 쿼리의 실행 계획에서만 표시. 조인에서 처음 읽은 테이블의 칼럼 값을 그 다음
읽어야 할 테이블의 PK나 Unique Key 칼럼의 검색 조건에 사용할 때 eq_ref라고 한다.
ref/ 인덱스의 종류와 관계없이 동등조건으로 검색할 때. 반환되는 레코드가 반드시 1건이라는 보장이 없으므로
const나 eq_ref보다는 빠르지 않다. 하지만 동등한 조건으로만 비교되므로 매우 빠른 레코드 조회 방법
fulltext/ MySQL 전문 검색(Full Text) 인덱스를 사용해 레코드를 읽는 방법.
ref_or_null/ ref 접근 방식과 같은데, NULL 비교가 추가된 형태.
unique_subquery/ WHERE 조건절에서 사용될 수 있는 IN 형태의 쿼리를 위한 접근 방식.
IN 형태의 조건에서 subquery의 반환 값엣는 중복이 없으므로 별도의 중복 제거 작업이 필요 없음.
index_subquery/ IN 형태의 조건에서 subquery의 반환 값에 중복된 값이 있을 수 있지만 인덱스를 이용해
중복을 제거할 수 있음
range/ 인덱스 레인지 스캔 형태의 접근 방법. 인덱스를 하나의 값이 아니라 범위로 검색하는 경우.
>, <, IS NULL, BETWEEN, IN, LIKE 등의 연산자를 이용해 인덱스를 검색.
index_merge/ 2개 이상의 인덱스를 이용해 각각의 검색 결과를 만들어낸 후 그 결과를 병합하는 처리 방식.
index/ 인덱스 풀 스캔.
ALL/ 풀 테이블 스캔.
key: 최종 선택된 실행 계획에서 사용하는 인덱스를 의미. Key 칼럼에 의도했던 인덱스가 표시되는지 확인.
Key_len: 쿼리를 처리하기 위해 다중 칼럼으로 구성된 인덱스에서 몇 개의 칼럼까지 사용했는지 표시. 인덱스의
각 레코드에서 몇 바이트까지 사용했는지 알려주는 값
ref: 접근 방법(type)이 ref 방식이면 참조 조건으로 어떤 값이 제공됐는지 표시.
rows: 비용을 산정하는 방법은 각 처리 방식이 얼마나 많은 레코드를 읽고 비교해야 하는지 예측.
대상 테이블에 얼마나 많은 레코드가 포함돼 있는지 또는 각 인덱스 값의 분포도가 어떤지 통계 정보를 기준으로
조사하여 예측. 쿼리를 처리하기 위해 얼마나 많은 레코드를 디스크로부터 읽고 체크해야 하는지를 의미.
extra:
쿼리에서 const 접근 방식이 필요한 부분은 실행 계획 수립 단계에서 옵티마이저가 직접 쿼리의 일부를 실행하고,
실행된 결과 값을 원본 쿼리의 상수로 대체한다.
풀 테이블 스캔
인덱스를 사용하지 않고 테이블의 데이터를 처음부터 끝까지 읽어서 요청된 작업을 처리하는 작업
풀 테이블 스캔 시, 한꺼번에 여러 개의 블록이나 페이지를 읽어오는 기능.
InnoDB: 리드 어헤드(Read Ahead)를 이용해서 InnoDB 버퍼 풀에 데이터를 저장.
Rss Feed