본문 바로가기
DB

DB 인덱스, 옵티마이저

by 워니 wony 2024. 3. 22.

인덱스

 

  • 시간 복잡도
    • 풀스캔 : O(n)
    • 인덱스가 걸린 경우(B-tree 기반 인덱스) : O(logN)
  • 인덱스 사용 이유
    • 조건을 만족하는 튜플들을 빠르게 조회하기 위해
    • 빠르게 정렬(order by)하거나 그룹핑(group by) 하기 위해
  • 인덱스 생성 쿼리
    • 전체 텍스트 인덱스
      • 텍스트 형식의 데이터를 대상으로 하는 인덱스
      • 주로 긴 텍스트 열에 대한 검색을 효율적으로 수행하기 위해 사용
      • 전체 텍스트 검색 기능은 단순 문자열 일치를 넘어 단어 간의 일치도 고려
      • 예) 블로그 게시말이나 제품 설명 같은 텍스트 데이터 검색 시 사용
    • 공간 인덱스
      • 지리 정보(Geometry 또는 Geography)를 포함하는 열에 대한 인덱스
      • 주로 지리 정보 시스템(GIS)에서 사용되며, 지도 데이터나 위치 기반 서비스에서 지리적 위치를 기반으로 검색 및 분석을 수행할 때 사용
      • 예를 들어, 지도 상의 지리적 범위를 나타내는 다각형이나 선, 점과 같은 요소를 효율적으로 검색할 수 있도록 도와줌
  • # 일반 인덱스 CREATE INDEX idx_name ON table_name(column_name); # 고유 인덱스 생성 CREATE UNIQUE INDEX idx_name ON table_name(column_name); # 복합 인덱스 생성 CREATE INDEX idx_name ON table_name(column1, column2, ...); # 전체 텍스트 인덱스 ALTER TABLE table_name ADD FULLTEXT(column_name); # 공간 인덱스 CREATE SPATIAL INDEX idx_name ON table_name(column_name);
  • 대부분 RDBMS는 Primary Key는 인덴스 자동 생성
  • 인덱스 조회
    • SHOW INDEX FROM table_name;
    SHOW INDEX FROM employees;

+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| employees  |          0 | PRIMARY    |            1 | emp_id      | A         |      299523 |     NULL | NULL   |      | BTREE      |         |               |
| employees  |          1 | idx_dept   |            1 | dept_id     | A         |           7 |     NULL | NULL   | YES  | BTREE      |         |               |
| employees  |          1 | idx_dept   |            2 | manager_id  | A         |          26 |     NULL | NULL   | YES  | BTREE      |         |               |
| employees  |          1 | idx_name   |            1 | last_name   | A         |       29952 |     NULL | NULL   |      | BTREE      |         |               |
| employees  |          1 | idx_name   |            2 | first_name  | A         |      149761 |     NULL | NULL   |      | BTREE      |         |               |
+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    • 첫 번째 열은 **Table**로 인덱스가 속한 테이블의 이름입니다.
    • Non_unique 열은 해당 인덱스가 고유한지 여부를 나타냄
      • 0이면 고유한 인덱스, 1이면 고유하지 않은 인덱스
    • Key_name 열은 인덱스의 이름
    • Seq_in_index 열은 인덱스 내의 열의 순서
    • Column_name 열은 인덱스를 생성한 열의 이름
    • Collation, Cardinality, Sub_part, Packed, Null, Index_type, Comment, Index_comment 열은 추가적인 인덱스 정보 제공

B-tree 기반 인덱스 동작 방식

  • 이진 탐색 트리
    • 자녀 노드는 최대 두 개까지
  • B tree
    • M : 각 노드의 최대 자녀 노드 수
      • 최대 M개의 자녀를 가질 수 있는 B tree를 M차 B tree라 함
    • M-1 : 각 노드의 최대 key 수
    • M/2 : 각 노드의 최소 자녀 노드 수
    • M/2 -1 : 각 노드의 최소 key 수
      • root node, leaf node 제외

인덱스 개요

  • DBMS에는 원하는 데이터를 빨리 찾을 수 있는 ‘인덱스’라는 자료구조가 있음
    • 데이블의 모든 데이터를 하나씩 확인하지 않고 찾고자 하는 데이터를 빨리 찾을 수 있도록 돕는 정보
  • DBMS의 인덱스
    • 인덱스는 키(key)와 행주소(row address)에 대한 정보를 담고 있음
      • 키 : 찾고자 하는 키워드
      • 행 주소 : 위치를 나타내는 정보
  • 인덱스와 기본 키
    • 기본 키는 테이블에서 매우 중요한 정보로 DBMS는 기본 키로 지정된 열에 인덱스를 자동으로 만듦
    • B-Tree 인덱스
      • 뒤집어진 나무 모양의 구조
      • 맨 위의 노드를 루트 노드, 맨 아래의 노드를 리프 노드, 이들 사이에 있는 노드를 브랜치 노드라 함
      • 찾고자 하는 인덱스 키가 가운데 노드보다 작으면 왼편만 검색하고, 크면 오른편만 검색함
        • 이런 방식으로 범위를 좁혀가면서 데이터를 찾기 때문에 인덱스를 찾는 시간이 매우 짧음
  • 인덱스 지정
    • 빠른 검색이 필요한 열이 있다면 인덱스를 직접 지정해야 함
      • 인덱스를 잘못 지정하면 오히려 성능이 나빠질 수도 있음
    CREATE INDEX employee_index ON 직원(이름);
    • 인덱스를 잘 지정하기 위해서는 값이 중복되지 않고 유일한 열을 선택해야 함
      • 이런 열을 인덱스로 지정하면 검색 속도가 빨라짐
      • 예) 사번, 이름(중복이 있을수 있으나 확률이 적음)
    • 인덱스 부적합
      • 성별 : 성별이 여자인 사람은 과반이 넘어서 전체 테이블 조회가 나을 수 있음
      • 주소 : 중복되지 않지만 긴 문자열을 인덱스 키로 지정 시 인덱스에 저장되는 데이터 크기도 덩달아 커져 성능에 부담이 될 수 있음
  • 인덱스는 막 만들어도 괜찮은가?
    • table에 write 할 때마다 index도 변경 발생
      • 오버헤드가 생김
    • 추가적인 저장 공간 차지
    • 위와 같은 이유로 불필요한 인덱스를 만들지 말자!
  • Full scan이 더 좋은 경우
    • table에 데이터가 조금 있는 경우(몇십, 몇백 건 정도)
    • 조회하려는 데이터가 테이블의 상당 부분을 차지할 때
      • 조회 데이터가 많은 경우는 풀스캔이 더 빠를 수 있음
      • 풀 스캔 여부는 옵티마이저가 판단
  • 참고
    • order by나 group by에도 index 사용 가능
    • FK에는 index 자동 생성되지 않을 수 있음(join 관련)
    • 이미 데이터가 몇 백만 건 이상 있는 테이블에 인덱스를 생성하는 경우 시간이 몇 분 이상 소요될 수 있고 DB 성능에 안 좋은 영향을 줄 수 있음

index 종류 (참고 유튜브)

  • Covering index
    • 조회하는 attribute(s)를 index가 모두 cover하는 경우를 말함
    • 조회 성능이 더 빠름
    • 종종 의도적으로 Covering index로 조회하도록 하기도 함
  • Hash index
    • hash table을 사용하여 index 구현
    • 시간복잡도 0(1)의 성능
    • rehasing에 대한 부담
      • Array에 저장한 데이터가 꽉 차면 늘려야 하는 것에 대한 부담
    • equality 비교만 가능, range 비교 불가
      • 범위 비교 불가
    • multicolumn index의 경우 전체 attributes에 대한 조회만 가능

쿼리 옵티마이저의 실행 계획

  • 풀 테이블 스캔 full table scan
    • 테이블 전체를 찾는 스캔 방식
  • 인덱스 스캔 index scan
    • 인덱스를 통해 빠른 검색을 하는 스캔 방식
  • 쿼리 실행 계획
    • DBMS는 쿼리를 최적으로 실행할 계획을 짬
    • 쿼리문을 실행하면 DBMS에 있는 쿼리 옵티마이저가 이 쿼리문에 맞게 최적의 실행 계획을 세움
      • 쿼리 옵티마이저 : 쿼리 실행 최적화를 돕는 DBMS 기능
  • 옵티마이저 동작
    • 쿼리문을 실행하면 옵티마이저는 자신이 처리할 수 있는 형태로 쿼리문을 변환함
    • 테이블에 대한 통계 정보를 활용해서 쿼리문 실행을 위해 얼마나 많은 작업을 해야 하는지를 비용 측면에서 계산함
      • 통계정보 : 테이블의 행의 개수, 인텍스 크기, 열 값의 분포도 등
    • 그리고 그 계산 결과를 기반으로 최적의 실행 계획을 세움
    • 예를 들어 MariaDB에서는 EXPLAIN 키워드를 사용하여 실행 계획을 확인함
      • EXPLAIN 키워드 옆에 쿼리문을 작성하면 쿼리문을 실행하기 위해 옵티마이저가 어떤 실행 계획을 세웠는지 확인할 수 있음
      • 해당 쿼리는 풀 테이블 스캔
      • 테이블의 모든 정보를 가져와야 하기 때문에 인덱스를 거칠 필요가 없음
      • EXPLAIN 실행 계획 확인 시 위와 같이 나옴
        • key가 NULL
        • 인덱스를 활용하지 않고 풀 테이블 스캔을 통해 데이터를 가져온다는 의미
      • key : PRIMARY
      • 인덱스를 활용해서 스캔하는 인덱스 스캔 방식
      • 학번 컬럼이 기본 키로 지정되어 있어서 인덱스 스캔을 함
      • 기본키가 지정되어 있으면 인덱스가 만들어지기 때문에 옵티마이저는 쿼리를 실행할 때 쿼리문의 WHERE 절에 주목함
        • WHERE 절에 기본 키로 정의된 열이 있다면 풀 테이블 스캔과 인덱스 스캔을 따져본 후 통계 정보를 이용해 최소 비용이 드는 방향을 실행 계획을 세움
      • 학과로 검색 시 key → NULL
      • 학과 열에 대한 인덱스가 없기 때문에 데이블의 모든 데이터를 확인하는 풀 테이블 스캔 방법을 선택
  • 프로그램에서 자주 사용하는 쿼리문을 풀 테이블 스캔으로 하면 DBMS에 부담을 줄 수 있음
    • 그러므로 실행 계획을 확인해보고, 필요하다면 인덱스를 지정하는 것이 좋음
      • 학과 인덱스를 추가하고 검색하면 아래와 같이 실행 계획이 변경됨
  • 옵티마이저는 주어진 시간 내에 여러 개의 실행 계획을 세우고 비용이 가장 낮은 방법을 선택
    • 인덱스 스캔 비용이 풀 테이블 스캔 비용보다 크다고 판단되면, 옵티마이저는 인덱스를 선택하지 않을 수도 있음
  • DBMS에는 힌트라는 기능이 있음
    • 옵티마이저의 실행 계획을 변경할 수 있도록 알려주는 기능
    • FORCE INDEX 키워드 사용 시 풀 테이블 스캔으로 세운 실행 계획을 인덱스 스캔으로 변경 가능
    • EXPLAIN SELECT * FROM 학생 FORCE INDEX(학과_idx) WHERE 학과 = '컴퓨터공학과' OR 학과 = '수학과' OR 학과 = '물리' OR 학과 = '유아교육과';
    • 옵티마이저는 실행 계획을 세울때 힌트를 우선 고려함
      • 옵티마이저가 세운 계획이 있더라도 이를 무시하고 힌트로 지정한 방식으로 실행 계획을 세움
    • 힌트는 실행 계획을 직접 변경할 수 있는 장점이 있지만, 전문가들은 계획적이지 않은 힌트 사용을 권장하지 않음
      • 잘못된 힌트 사용은 오히려 옵티마이저의 올바른 판단을 방해하여 높은 비용의 실행 계획이 선택 될 수 있음
      • 일반적으로 옵티마이저가 다양한 통계 정보를 활용해 알아서 실행 계획을 잘 세우기 때문에 옵티마이저의 판단을 따르는 것이 바람직
      • 힌트는 옵티마이저가 세운 실행 계획보다 더 좋은 실행 계획을 만들 수 있는 경우에만 제한적으로 사용하는 것이 좋음
    • USE INDEX
      • MYSQL에서 해당 인덱스를 써달라고 권장하는 명령어
      • SELECT * FROM player USE INDEX (인덱스명) WHERE number = 7;
    • FORCE INDEX
      • 해당 인덱스를 쓰라고 강제 하는 느낌
      • 단, 옵티마이저가 해당 인덱스로 원하는 데이터를 가져올 수 없다고 판단하면 사용하지 않고 풀스캔
      • SELECT * FROM player FORCE INDEX (인덱스명) WHERE number = 7;

인덱스

  • 시간 복잡도
    • 풀스캔 : O(n)
    • 인덱스가 걸린 경우(B-tree 기반 인덱스) : O(logN)
  • 인덱스 사용 이유
    • 조건을 만족하는 튜플들을 빠르게 조회하기 위해
    • 빠르게 정렬(order by)하거나 그룹핑(group by) 하기 위해
  • 인덱스 생성 쿼리
    • 전체 텍스트 인덱스
      • 텍스트 형식의 데이터를 대상으로 하는 인덱스
      • 주로 긴 텍스트 열에 대한 검색을 효율적으로 수행하기 위해 사용
      • 전체 텍스트 검색 기능은 단순 문자열 일치를 넘어 단어 간의 일치도 고려
      • 예) 블로그 게시말이나 제품 설명 같은 텍스트 데이터 검색 시 사용
    • 공간 인덱스
      • 지리 정보(Geometry 또는 Geography)를 포함하는 열에 대한 인덱스
      • 주로 지리 정보 시스템(GIS)에서 사용되며, 지도 데이터나 위치 기반 서비스에서 지리적 위치를 기반으로 검색 및 분석을 수행할 때 사용
      • 예를 들어, 지도 상의 지리적 범위를 나타내는 다각형이나 선, 점과 같은 요소를 효율적으로 검색할 수 있도록 도와줌
  • # 일반 인덱스 CREATE INDEX idx_name ON table_name(column_name); # 고유 인덱스 생성 CREATE UNIQUE INDEX idx_name ON table_name(column_name); # 복합 인덱스 생성 CREATE INDEX idx_name ON table_name(column1, column2, ...); # 전체 텍스트 인덱스 ALTER TABLE table_name ADD FULLTEXT(column_name); # 공간 인덱스 CREATE SPATIAL INDEX idx_name ON table_name(column_name);
  • 대부분 RDBMS는 Primary Key는 인덴스 자동 생성
  • 인덱스 조회
    • SHOW INDEX FROM table_name;
    SHOW INDEX FROM employees;

+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| employees  |          0 | PRIMARY    |            1 | emp_id      | A         |      299523 |     NULL | NULL   |      | BTREE      |         |               |
| employees  |          1 | idx_dept   |            1 | dept_id     | A         |           7 |     NULL | NULL   | YES  | BTREE      |         |               |
| employees  |          1 | idx_dept   |            2 | manager_id  | A         |          26 |     NULL | NULL   | YES  | BTREE      |         |               |
| employees  |          1 | idx_name   |            1 | last_name   | A         |       29952 |     NULL | NULL   |      | BTREE      |         |               |
| employees  |          1 | idx_name   |            2 | first_name  | A         |      149761 |     NULL | NULL   |      | BTREE      |         |               |
+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    • 첫 번째 열은 **Table**로 인덱스가 속한 테이블의 이름입니다.
    • Non_unique 열은 해당 인덱스가 고유한지 여부를 나타냄
      • 0이면 고유한 인덱스, 1이면 고유하지 않은 인덱스
    • Key_name 열은 인덱스의 이름
    • Seq_in_index 열은 인덱스 내의 열의 순서
    • Column_name 열은 인덱스를 생성한 열의 이름
    • Collation, Cardinality, Sub_part, Packed, Null, Index_type, Comment, Index_comment 열은 추가적인 인덱스 정보 제공

B-tree 기반 인덱스 동작 방식

  • 이진 탐색 트리
    • 자녀 노드는 최대 두 개까지
  • B tree
    • M : 각 노드의 최대 자녀 노드 수
      • 최대 M개의 자녀를 가질 수 있는 B tree를 M차 B tree라 함
    • M-1 : 각 노드의 최대 key 수
    • M/2 : 각 노드의 최소 자녀 노드 수
    • M/2 -1 : 각 노드의 최소 key 수
      • root node, leaf node 제외

인덱스 개요

  • DBMS에는 원하는 데이터를 빨리 찾을 수 있는 ‘인덱스’라는 자료구조가 있음
    • 데이블의 모든 데이터를 하나씩 확인하지 않고 찾고자 하는 데이터를 빨리 찾을 수 있도록 돕는 정보
  • DBMS의 인덱스
    • 인덱스는 키(key)와 행주소(row address)에 대한 정보를 담고 있음
      • 키 : 찾고자 하는 키워드
      • 행 주소 : 위치를 나타내는 정보
  • 인덱스와 기본 키
    • 기본 키는 테이블에서 매우 중요한 정보로 DBMS는 기본 키로 지정된 열에 인덱스를 자동으로 만듦
    • B-Tree 인덱스
      • 뒤집어진 나무 모양의 구조
      • 맨 위의 노드를 루트 노드, 맨 아래의 노드를 리프 노드, 이들 사이에 있는 노드를 브랜치 노드라 함
      • 찾고자 하는 인덱스 키가 가운데 노드보다 작으면 왼편만 검색하고, 크면 오른편만 검색함
        • 이런 방식으로 범위를 좁혀가면서 데이터를 찾기 때문에 인덱스를 찾는 시간이 매우 짧음
  • 인덱스 지정
    • 빠른 검색이 필요한 열이 있다면 인덱스를 직접 지정해야 함
      • 인덱스를 잘못 지정하면 오히려 성능이 나빠질 수도 있음
    CREATE INDEX employee_index ON 직원(이름);
    • 인덱스를 잘 지정하기 위해서는 값이 중복되지 않고 유일한 열을 선택해야 함
      • 이런 열을 인덱스로 지정하면 검색 속도가 빨라짐
      • 예) 사번, 이름(중복이 있을수 있으나 확률이 적음)
    • 인덱스 부적합
      • 성별 : 성별이 여자인 사람은 과반이 넘어서 전체 테이블 조회가 나을 수 있음
      • 주소 : 중복되지 않지만 긴 문자열을 인덱스 키로 지정 시 인덱스에 저장되는 데이터 크기도 덩달아 커져 성능에 부담이 될 수 있음
  • 인덱스는 막 만들어도 괜찮은가?
    • table에 write 할 때마다 index도 변경 발생
      • 오버헤드가 생김
    • 추가적인 저장 공간 차지
    • 위와 같은 이유로 불필요한 인덱스를 만들지 말자!
  • Full scan이 더 좋은 경우
    • table에 데이터가 조금 있는 경우(몇십, 몇백 건 정도)
    • 조회하려는 데이터가 테이블의 상당 부분을 차지할 때
      • 조회 데이터가 많은 경우는 풀스캔이 더 빠를 수 있음
      • 풀 스캔 여부는 옵티마이저가 판단
  • 참고
    • order by나 group by에도 index 사용 가능
    • FK에는 index 자동 생성되지 않을 수 있음(join 관련)
    • 이미 데이터가 몇 백만 건 이상 있는 테이블에 인덱스를 생성하는 경우 시간이 몇 분 이상 소요될 수 있고 DB 성능에 안 좋은 영향을 줄 수 있음

index 종류 (참고 유튜브)

  • Covering index
    • 조회하는 attribute(s)를 index가 모두 cover하는 경우를 말함
    • 조회 성능이 더 빠름
    • 종종 의도적으로 Covering index로 조회하도록 하기도 함
  • Hash index
    • hash table을 사용하여 index 구현
    • 시간복잡도 0(1)의 성능
    • rehasing에 대한 부담
      • Array에 저장한 데이터가 꽉 차면 늘려야 하는 것에 대한 부담
    • equality 비교만 가능, range 비교 불가
      • 범위 비교 불가
    • multicolumn index의 경우 전체 attributes에 대한 조회만 가능

쿼리 옵티마이저의 실행 계획

  • 풀 테이블 스캔 full table scan
    • 테이블 전체를 찾는 스캔 방식
  • 인덱스 스캔 index scan
    • 인덱스를 통해 빠른 검색을 하는 스캔 방식
  • 쿼리 실행 계획
    • DBMS는 쿼리를 최적으로 실행할 계획을 짬
    • 쿼리문을 실행하면 DBMS에 있는 쿼리 옵티마이저가 이 쿼리문에 맞게 최적의 실행 계획을 세움
      • 쿼리 옵티마이저 : 쿼리 실행 최적화를 돕는 DBMS 기능
  • 옵티마이저 동작
    • 쿼리문을 실행하면 옵티마이저는 자신이 처리할 수 있는 형태로 쿼리문을 변환함
    • 테이블에 대한 통계 정보를 활용해서 쿼리문 실행을 위해 얼마나 많은 작업을 해야 하는지를 비용 측면에서 계산함
      • 통계정보 : 테이블의 행의 개수, 인텍스 크기, 열 값의 분포도 등
    • 그리고 그 계산 결과를 기반으로 최적의 실행 계획을 세움
    • 예를 들어 MariaDB에서는 EXPLAIN 키워드를 사용하여 실행 계획을 확인함
      • EXPLAIN 키워드 옆에 쿼리문을 작성하면 쿼리문을 실행하기 위해 옵티마이저가 어떤 실행 계획을 세웠는지 확인할 수 있음
      • 해당 쿼리는 풀 테이블 스캔
      • 테이블의 모든 정보를 가져와야 하기 때문에 인덱스를 거칠 필요가 없음
      • EXPLAIN 실행 계획 확인 시 위와 같이 나옴
        • key가 NULL
        • 인덱스를 활용하지 않고 풀 테이블 스캔을 통해 데이터를 가져온다는 의미
      • key : PRIMARY
      • 인덱스를 활용해서 스캔하는 인덱스 스캔 방식
      • 학번 컬럼이 기본 키로 지정되어 있어서 인덱스 스캔을 함
      • 기본키가 지정되어 있으면 인덱스가 만들어지기 때문에 옵티마이저는 쿼리를 실행할 때 쿼리문의 WHERE 절에 주목함
        • WHERE 절에 기본 키로 정의된 열이 있다면 풀 테이블 스캔과 인덱스 스캔을 따져본 후 통계 정보를 이용해 최소 비용이 드는 방향을 실행 계획을 세움
      • 학과로 검색 시 key → NULL
      • 학과 열에 대한 인덱스가 없기 때문에 데이블의 모든 데이터를 확인하는 풀 테이블 스캔 방법을 선택
  • 프로그램에서 자주 사용하는 쿼리문을 풀 테이블 스캔으로 하면 DBMS에 부담을 줄 수 있음
    • 그러므로 실행 계획을 확인해보고, 필요하다면 인덱스를 지정하는 것이 좋음
      • 학과 인덱스를 추가하고 검색하면 아래와 같이 실행 계획이 변경됨
  • 옵티마이저는 주어진 시간 내에 여러 개의 실행 계획을 세우고 비용이 가장 낮은 방법을 선택
    • 인덱스 스캔 비용이 풀 테이블 스캔 비용보다 크다고 판단되면, 옵티마이저는 인덱스를 선택하지 않을 수도 있음
  • DBMS에는 힌트라는 기능이 있음
    • 옵티마이저의 실행 계획을 변경할 수 있도록 알려주는 기능
    • FORCE INDEX 키워드 사용 시 풀 테이블 스캔으로 세운 실행 계획을 인덱스 스캔으로 변경 가능
    • EXPLAIN SELECT * FROM 학생 FORCE INDEX(학과_idx) WHERE 학과 = '컴퓨터공학과' OR 학과 = '수학과' OR 학과 = '물리' OR 학과 = '유아교육과';
    • 옵티마이저는 실행 계획을 세울때 힌트를 우선 고려함
      • 옵티마이저가 세운 계획이 있더라도 이를 무시하고 힌트로 지정한 방식으로 실행 계획을 세움
    • 힌트는 실행 계획을 직접 변경할 수 있는 장점이 있지만, 전문가들은 계획적이지 않은 힌트 사용을 권장하지 않음
      • 잘못된 힌트 사용은 오히려 옵티마이저의 올바른 판단을 방해하여 높은 비용의 실행 계획이 선택 될 수 있음
      • 일반적으로 옵티마이저가 다양한 통계 정보를 활용해 알아서 실행 계획을 잘 세우기 때문에 옵티마이저의 판단을 따르는 것이 바람직
      • 힌트는 옵티마이저가 세운 실행 계획보다 더 좋은 실행 계획을 만들 수 있는 경우에만 제한적으로 사용하는 것이 좋음
    • USE INDEX
      • MYSQL에서 해당 인덱스를 써달라고 권장하는 명령어
      • SELECT * FROM player USE INDEX (인덱스명) WHERE number = 7;
    • FORCE INDEX
      • 해당 인덱스를 쓰라고 강제 하는 느낌
      • 단, 옵티마이저가 해당 인덱스로 원하는 데이터를 가져올 수 없다고 판단하면 사용하지 않고 풀스캔
      • SELECT * FROM player FORCE INDEX (인덱스명) WHERE number = 7;

인덱스

  • 시간 복잡도
    • 풀스캔 : O(n)
    • 인덱스가 걸린 경우(B-tree 기반 인덱스) : O(logN)
  • 인덱스 사용 이유
    • 조건을 만족하는 튜플들을 빠르게 조회하기 위해
    • 빠르게 정렬(order by)하거나 그룹핑(group by) 하기 위해
  • 인덱스 생성 쿼리
    • 전체 텍스트 인덱스
      • 텍스트 형식의 데이터를 대상으로 하는 인덱스
      • 주로 긴 텍스트 열에 대한 검색을 효율적으로 수행하기 위해 사용
      • 전체 텍스트 검색 기능은 단순 문자열 일치를 넘어 단어 간의 일치도 고려
      • 예) 블로그 게시말이나 제품 설명 같은 텍스트 데이터 검색 시 사용
    • 공간 인덱스
      • 지리 정보(Geometry 또는 Geography)를 포함하는 열에 대한 인덱스
      • 주로 지리 정보 시스템(GIS)에서 사용되며, 지도 데이터나 위치 기반 서비스에서 지리적 위치를 기반으로 검색 및 분석을 수행할 때 사용
      • 예를 들어, 지도 상의 지리적 범위를 나타내는 다각형이나 선, 점과 같은 요소를 효율적으로 검색할 수 있도록 도와줌
  • # 일반 인덱스 CREATE INDEX idx_name ON table_name(column_name); # 고유 인덱스 생성 CREATE UNIQUE INDEX idx_name ON table_name(column_name); # 복합 인덱스 생성 CREATE INDEX idx_name ON table_name(column1, column2, ...); # 전체 텍스트 인덱스 ALTER TABLE table_name ADD FULLTEXT(column_name); # 공간 인덱스 CREATE SPATIAL INDEX idx_name ON table_name(column_name);
  • 대부분 RDBMS는 Primary Key는 인덴스 자동 생성
  • 인덱스 조회
    • SHOW INDEX FROM table_name;
    SHOW INDEX FROM employees;

+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| employees  |          0 | PRIMARY    |            1 | emp_id      | A         |      299523 |     NULL | NULL   |      | BTREE      |         |               |
| employees  |          1 | idx_dept   |            1 | dept_id     | A         |           7 |     NULL | NULL   | YES  | BTREE      |         |               |
| employees  |          1 | idx_dept   |            2 | manager_id  | A         |          26 |     NULL | NULL   | YES  | BTREE      |         |               |
| employees  |          1 | idx_name   |            1 | last_name   | A         |       29952 |     NULL | NULL   |      | BTREE      |         |               |
| employees  |          1 | idx_name   |            2 | first_name  | A         |      149761 |     NULL | NULL   |      | BTREE      |         |               |
+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    • 첫 번째 열은 **Table**로 인덱스가 속한 테이블의 이름입니다.
    • Non_unique 열은 해당 인덱스가 고유한지 여부를 나타냄
      • 0이면 고유한 인덱스, 1이면 고유하지 않은 인덱스
    • Key_name 열은 인덱스의 이름
    • Seq_in_index 열은 인덱스 내의 열의 순서
    • Column_name 열은 인덱스를 생성한 열의 이름
    • Collation, Cardinality, Sub_part, Packed, Null, Index_type, Comment, Index_comment 열은 추가적인 인덱스 정보 제공

B-tree 기반 인덱스 동작 방식

  • 이진 탐색 트리
    • 자녀 노드는 최대 두 개까지
  • B tree
    • M : 각 노드의 최대 자녀 노드 수
      • 최대 M개의 자녀를 가질 수 있는 B tree를 M차 B tree라 함
    • M-1 : 각 노드의 최대 key 수
    • M/2 : 각 노드의 최소 자녀 노드 수
    • M/2 -1 : 각 노드의 최소 key 수
      • root node, leaf node 제외

인덱스 개요

  • DBMS에는 원하는 데이터를 빨리 찾을 수 있는 ‘인덱스’라는 자료구조가 있음
    • 데이블의 모든 데이터를 하나씩 확인하지 않고 찾고자 하는 데이터를 빨리 찾을 수 있도록 돕는 정보
  • DBMS의 인덱스
    • 인덱스는 키(key)와 행주소(row address)에 대한 정보를 담고 있음
      • 키 : 찾고자 하는 키워드
      • 행 주소 : 위치를 나타내는 정보
  • 인덱스와 기본 키
    • 기본 키는 테이블에서 매우 중요한 정보로 DBMS는 기본 키로 지정된 열에 인덱스를 자동으로 만듦
    • B-Tree 인덱스
      • 뒤집어진 나무 모양의 구조
      • 맨 위의 노드를 루트 노드, 맨 아래의 노드를 리프 노드, 이들 사이에 있는 노드를 브랜치 노드라 함
      • 찾고자 하는 인덱스 키가 가운데 노드보다 작으면 왼편만 검색하고, 크면 오른편만 검색함
        • 이런 방식으로 범위를 좁혀가면서 데이터를 찾기 때문에 인덱스를 찾는 시간이 매우 짧음
  • 인덱스 지정
    • 빠른 검색이 필요한 열이 있다면 인덱스를 직접 지정해야 함
      • 인덱스를 잘못 지정하면 오히려 성능이 나빠질 수도 있음
    CREATE INDEX employee_index ON 직원(이름);
    • 인덱스를 잘 지정하기 위해서는 값이 중복되지 않고 유일한 열을 선택해야 함
      • 이런 열을 인덱스로 지정하면 검색 속도가 빨라짐
      • 예) 사번, 이름(중복이 있을수 있으나 확률이 적음)
    • 인덱스 부적합
      • 성별 : 성별이 여자인 사람은 과반이 넘어서 전체 테이블 조회가 나을 수 있음
      • 주소 : 중복되지 않지만 긴 문자열을 인덱스 키로 지정 시 인덱스에 저장되는 데이터 크기도 덩달아 커져 성능에 부담이 될 수 있음
  • 인덱스는 막 만들어도 괜찮은가?
    • table에 write 할 때마다 index도 변경 발생
      • 오버헤드가 생김
    • 추가적인 저장 공간 차지
    • 위와 같은 이유로 불필요한 인덱스를 만들지 말자!
  • Full scan이 더 좋은 경우
    • table에 데이터가 조금 있는 경우(몇십, 몇백 건 정도)
    • 조회하려는 데이터가 테이블의 상당 부분을 차지할 때
      • 조회 데이터가 많은 경우는 풀스캔이 더 빠를 수 있음
      • 풀 스캔 여부는 옵티마이저가 판단
  • 참고
    • order by나 group by에도 index 사용 가능
    • FK에는 index 자동 생성되지 않을 수 있음(join 관련)
    • 이미 데이터가 몇 백만 건 이상 있는 테이블에 인덱스를 생성하는 경우 시간이 몇 분 이상 소요될 수 있고 DB 성능에 안 좋은 영향을 줄 수 있음

index 종류 (참고 유튜브)

  • Covering index
    • 조회하는 attribute(s)를 index가 모두 cover하는 경우를 말함
    • 조회 성능이 더 빠름
    • 종종 의도적으로 Covering index로 조회하도록 하기도 함
  • Hash index
    • hash table을 사용하여 index 구현
    • 시간복잡도 0(1)의 성능
    • rehasing에 대한 부담
      • Array에 저장한 데이터가 꽉 차면 늘려야 하는 것에 대한 부담
    • equality 비교만 가능, range 비교 불가
      • 범위 비교 불가
    • multicolumn index의 경우 전체 attributes에 대한 조회만 가능

쿼리 옵티마이저의 실행 계획

  • 풀 테이블 스캔 full table scan
    • 테이블 전체를 찾는 스캔 방식
  • 인덱스 스캔 index scan
    • 인덱스를 통해 빠른 검색을 하는 스캔 방식
  • 쿼리 실행 계획
    • DBMS는 쿼리를 최적으로 실행할 계획을 짬
    • 쿼리문을 실행하면 DBMS에 있는 쿼리 옵티마이저가 이 쿼리문에 맞게 최적의 실행 계획을 세움
      • 쿼리 옵티마이저 : 쿼리 실행 최적화를 돕는 DBMS 기능
  • 옵티마이저 동작
    • 쿼리문을 실행하면 옵티마이저는 자신이 처리할 수 있는 형태로 쿼리문을 변환함
    • 테이블에 대한 통계 정보를 활용해서 쿼리문 실행을 위해 얼마나 많은 작업을 해야 하는지를 비용 측면에서 계산함
      • 통계정보 : 테이블의 행의 개수, 인텍스 크기, 열 값의 분포도 등
    • 그리고 그 계산 결과를 기반으로 최적의 실행 계획을 세움
    • 예를 들어 MariaDB에서는 EXPLAIN 키워드를 사용하여 실행 계획을 확인함
      • EXPLAIN 키워드 옆에 쿼리문을 작성하면 쿼리문을 실행하기 위해 옵티마이저가 어떤 실행 계획을 세웠는지 확인할 수 있음
      • 해당 쿼리는 풀 테이블 스캔
      • 테이블의 모든 정보를 가져와야 하기 때문에 인덱스를 거칠 필요가 없음
      • EXPLAIN 실행 계획 확인 시 위와 같이 나옴
        • key가 NULL
        • 인덱스를 활용하지 않고 풀 테이블 스캔을 통해 데이터를 가져온다는 의미
      • key : PRIMARY
      • 인덱스를 활용해서 스캔하는 인덱스 스캔 방식
      • 학번 컬럼이 기본 키로 지정되어 있어서 인덱스 스캔을 함
      • 기본키가 지정되어 있으면 인덱스가 만들어지기 때문에 옵티마이저는 쿼리를 실행할 때 쿼리문의 WHERE 절에 주목함
        • WHERE 절에 기본 키로 정의된 열이 있다면 풀 테이블 스캔과 인덱스 스캔을 따져본 후 통계 정보를 이용해 최소 비용이 드는 방향을 실행 계획을 세움
      • 학과로 검색 시 key → NULL
      • 학과 열에 대한 인덱스가 없기 때문에 데이블의 모든 데이터를 확인하는 풀 테이블 스캔 방법을 선택
  • 프로그램에서 자주 사용하는 쿼리문을 풀 테이블 스캔으로 하면 DBMS에 부담을 줄 수 있음
    • 그러므로 실행 계획을 확인해보고, 필요하다면 인덱스를 지정하는 것이 좋음
      • 학과 인덱스를 추가하고 검색하면 아래와 같이 실행 계획이 변경됨
  • 옵티마이저는 주어진 시간 내에 여러 개의 실행 계획을 세우고 비용이 가장 낮은 방법을 선택
    • 인덱스 스캔 비용이 풀 테이블 스캔 비용보다 크다고 판단되면, 옵티마이저는 인덱스를 선택하지 않을 수도 있음
  • DBMS에는 힌트라는 기능이 있음
    • 옵티마이저의 실행 계획을 변경할 수 있도록 알려주는 기능
    • FORCE INDEX 키워드 사용 시 풀 테이블 스캔으로 세운 실행 계획을 인덱스 스캔으로 변경 가능
    • EXPLAIN SELECT * FROM 학생 FORCE INDEX(학과_idx) WHERE 학과 = '컴퓨터공학과' OR 학과 = '수학과' OR 학과 = '물리' OR 학과 = '유아교육과';
    • 옵티마이저는 실행 계획을 세울때 힌트를 우선 고려함
      • 옵티마이저가 세운 계획이 있더라도 이를 무시하고 힌트로 지정한 방식으로 실행 계획을 세움
    • 힌트는 실행 계획을 직접 변경할 수 있는 장점이 있지만, 전문가들은 계획적이지 않은 힌트 사용을 권장하지 않음
      • 잘못된 힌트 사용은 오히려 옵티마이저의 올바른 판단을 방해하여 높은 비용의 실행 계획이 선택 될 수 있음
      • 일반적으로 옵티마이저가 다양한 통계 정보를 활용해 알아서 실행 계획을 잘 세우기 때문에 옵티마이저의 판단을 따르는 것이 바람직
      • 힌트는 옵티마이저가 세운 실행 계획보다 더 좋은 실행 계획을 만들 수 있는 경우에만 제한적으로 사용하는 것이 좋음
    • USE INDEX
      • MYSQL에서 해당 인덱스를 써달라고 권장하는 명령어
      • SELECT * FROM player USE INDEX (인덱스명) WHERE number = 7;
    • FORCE INDEX
      • 해당 인덱스를 쓰라고 강제 하는 느낌
      • 단, 옵티마이저가 해당 인덱스로 원하는 데이터를 가져올 수 없다고 판단하면 사용하지 않고 풀스캔
      • SELECT * FROM player FORCE INDEX (인덱스명) WHERE number = 7;

인덱스

  • 시간 복잡도
    • 풀스캔 : O(n)
    • 인덱스가 걸린 경우(B-tree 기반 인덱스) : O(logN)
  • 인덱스 사용 이유
    • 조건을 만족하는 튜플들을 빠르게 조회하기 위해
    • 빠르게 정렬(order by)하거나 그룹핑(group by) 하기 위해
  • 인덱스 생성 쿼리
    • 전체 텍스트 인덱스
      • 텍스트 형식의 데이터를 대상으로 하는 인덱스
      • 주로 긴 텍스트 열에 대한 검색을 효율적으로 수행하기 위해 사용
      • 전체 텍스트 검색 기능은 단순 문자열 일치를 넘어 단어 간의 일치도 고려
      • 예) 블로그 게시말이나 제품 설명 같은 텍스트 데이터 검색 시 사용
    • 공간 인덱스
      • 지리 정보(Geometry 또는 Geography)를 포함하는 열에 대한 인덱스
      • 주로 지리 정보 시스템(GIS)에서 사용되며, 지도 데이터나 위치 기반 서비스에서 지리적 위치를 기반으로 검색 및 분석을 수행할 때 사용
      • 예를 들어, 지도 상의 지리적 범위를 나타내는 다각형이나 선, 점과 같은 요소를 효율적으로 검색할 수 있도록 도와줌
  • # 일반 인덱스 CREATE INDEX idx_name ON table_name(column_name); # 고유 인덱스 생성 CREATE UNIQUE INDEX idx_name ON table_name(column_name); # 복합 인덱스 생성 CREATE INDEX idx_name ON table_name(column1, column2, ...); # 전체 텍스트 인덱스 ALTER TABLE table_name ADD FULLTEXT(column_name); # 공간 인덱스 CREATE SPATIAL INDEX idx_name ON table_name(column_name);
  • 대부분 RDBMS는 Primary Key는 인덴스 자동 생성
  • 인덱스 조회
    • SHOW INDEX FROM table_name;
    SHOW INDEX FROM employees;

+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| employees  |          0 | PRIMARY    |            1 | emp_id      | A         |      299523 |     NULL | NULL   |      | BTREE      |         |               |
| employees  |          1 | idx_dept   |            1 | dept_id     | A         |           7 |     NULL | NULL   | YES  | BTREE      |         |               |
| employees  |          1 | idx_dept   |            2 | manager_id  | A         |          26 |     NULL | NULL   | YES  | BTREE      |         |               |
| employees  |          1 | idx_name   |            1 | last_name   | A         |       29952 |     NULL | NULL   |      | BTREE      |         |               |
| employees  |          1 | idx_name   |            2 | first_name  | A         |      149761 |     NULL | NULL   |      | BTREE      |         |               |
+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    • 첫 번째 열은 **Table**로 인덱스가 속한 테이블의 이름입니다.
    • Non_unique 열은 해당 인덱스가 고유한지 여부를 나타냄
      • 0이면 고유한 인덱스, 1이면 고유하지 않은 인덱스
    • Key_name 열은 인덱스의 이름
    • Seq_in_index 열은 인덱스 내의 열의 순서
    • Column_name 열은 인덱스를 생성한 열의 이름
    • Collation, Cardinality, Sub_part, Packed, Null, Index_type, Comment, Index_comment 열은 추가적인 인덱스 정보 제공

B-tree 기반 인덱스 동작 방식

  • 이진 탐색 트리
    • 자녀 노드는 최대 두 개까지
  • B tree
    • M : 각 노드의 최대 자녀 노드 수
      • 최대 M개의 자녀를 가질 수 있는 B tree를 M차 B tree라 함
    • M-1 : 각 노드의 최대 key 수
    • M/2 : 각 노드의 최소 자녀 노드 수
    • M/2 -1 : 각 노드의 최소 key 수
      • root node, leaf node 제외

인덱스 개요

  • DBMS에는 원하는 데이터를 빨리 찾을 수 있는 ‘인덱스’라는 자료구조가 있음
    • 데이블의 모든 데이터를 하나씩 확인하지 않고 찾고자 하는 데이터를 빨리 찾을 수 있도록 돕는 정보
  • DBMS의 인덱스
    • 인덱스는 키(key)와 행주소(row address)에 대한 정보를 담고 있음
      • 키 : 찾고자 하는 키워드
      • 행 주소 : 위치를 나타내는 정보
  • 인덱스와 기본 키
    • 기본 키는 테이블에서 매우 중요한 정보로 DBMS는 기본 키로 지정된 열에 인덱스를 자동으로 만듦
    • B-Tree 인덱스
      • 뒤집어진 나무 모양의 구조
      • 맨 위의 노드를 루트 노드, 맨 아래의 노드를 리프 노드, 이들 사이에 있는 노드를 브랜치 노드라 함
      • 찾고자 하는 인덱스 키가 가운데 노드보다 작으면 왼편만 검색하고, 크면 오른편만 검색함
        • 이런 방식으로 범위를 좁혀가면서 데이터를 찾기 때문에 인덱스를 찾는 시간이 매우 짧음
  • 인덱스 지정
    • 빠른 검색이 필요한 열이 있다면 인덱스를 직접 지정해야 함
      • 인덱스를 잘못 지정하면 오히려 성능이 나빠질 수도 있음
    CREATE INDEX employee_index ON 직원(이름);
    • 인덱스를 잘 지정하기 위해서는 값이 중복되지 않고 유일한 열을 선택해야 함
      • 이런 열을 인덱스로 지정하면 검색 속도가 빨라짐
      • 예) 사번, 이름(중복이 있을수 있으나 확률이 적음)
    • 인덱스 부적합
      • 성별 : 성별이 여자인 사람은 과반이 넘어서 전체 테이블 조회가 나을 수 있음
      • 주소 : 중복되지 않지만 긴 문자열을 인덱스 키로 지정 시 인덱스에 저장되는 데이터 크기도 덩달아 커져 성능에 부담이 될 수 있음
  • 인덱스는 막 만들어도 괜찮은가?
    • table에 write 할 때마다 index도 변경 발생
      • 오버헤드가 생김
    • 추가적인 저장 공간 차지
    • 위와 같은 이유로 불필요한 인덱스를 만들지 말자!
  • Full scan이 더 좋은 경우
    • table에 데이터가 조금 있는 경우(몇십, 몇백 건 정도)
    • 조회하려는 데이터가 테이블의 상당 부분을 차지할 때
      • 조회 데이터가 많은 경우는 풀스캔이 더 빠를 수 있음
      • 풀 스캔 여부는 옵티마이저가 판단
  • 참고
    • order by나 group by에도 index 사용 가능
    • FK에는 index 자동 생성되지 않을 수 있음(join 관련)
    • 이미 데이터가 몇 백만 건 이상 있는 테이블에 인덱스를 생성하는 경우 시간이 몇 분 이상 소요될 수 있고 DB 성능에 안 좋은 영향을 줄 수 있음

index 종류 (참고 유튜브)

  • Covering index
    • 조회하는 attribute(s)를 index가 모두 cover하는 경우를 말함
    • 조회 성능이 더 빠름
    • 종종 의도적으로 Covering index로 조회하도록 하기도 함
  • Hash index
    • hash table을 사용하여 index 구현
    • 시간복잡도 0(1)의 성능
    • rehasing에 대한 부담
      • Array에 저장한 데이터가 꽉 차면 늘려야 하는 것에 대한 부담
    • equality 비교만 가능, range 비교 불가
      • 범위 비교 불가
    • multicolumn index의 경우 전체 attributes에 대한 조회만 가능

쿼리 옵티마이저의 실행 계획

  • 풀 테이블 스캔 full table scan
    • 테이블 전체를 찾는 스캔 방식
  • 인덱스 스캔 index scan
    • 인덱스를 통해 빠른 검색을 하는 스캔 방식
  • 쿼리 실행 계획
    • DBMS는 쿼리를 최적으로 실행할 계획을 짬
    • 쿼리문을 실행하면 DBMS에 있는 쿼리 옵티마이저가 이 쿼리문에 맞게 최적의 실행 계획을 세움
      • 쿼리 옵티마이저 : 쿼리 실행 최적화를 돕는 DBMS 기능
  • 옵티마이저 동작
    • 쿼리문을 실행하면 옵티마이저는 자신이 처리할 수 있는 형태로 쿼리문을 변환함
    • 테이블에 대한 통계 정보를 활용해서 쿼리문 실행을 위해 얼마나 많은 작업을 해야 하는지를 비용 측면에서 계산함
      • 통계정보 : 테이블의 행의 개수, 인텍스 크기, 열 값의 분포도 등
    • 그리고 그 계산 결과를 기반으로 최적의 실행 계획을 세움
    • 예를 들어 MariaDB에서는 EXPLAIN 키워드를 사용하여 실행 계획을 확인함
      • EXPLAIN 키워드 옆에 쿼리문을 작성하면 쿼리문을 실행하기 위해 옵티마이저가 어떤 실행 계획을 세웠는지 확인할 수 있음
      • 해당 쿼리는 풀 테이블 스캔
      • 테이블의 모든 정보를 가져와야 하기 때문에 인덱스를 거칠 필요가 없음
      • EXPLAIN 실행 계획 확인 시 위와 같이 나옴
        • key가 NULL
        • 인덱스를 활용하지 않고 풀 테이블 스캔을 통해 데이터를 가져온다는 의미
      • key : PRIMARY
      • 인덱스를 활용해서 스캔하는 인덱스 스캔 방식
      • 학번 컬럼이 기본 키로 지정되어 있어서 인덱스 스캔을 함
      • 기본키가 지정되어 있으면 인덱스가 만들어지기 때문에 옵티마이저는 쿼리를 실행할 때 쿼리문의 WHERE 절에 주목함
        • WHERE 절에 기본 키로 정의된 열이 있다면 풀 테이블 스캔과 인덱스 스캔을 따져본 후 통계 정보를 이용해 최소 비용이 드는 방향을 실행 계획을 세움
      • 학과로 검색 시 key → NULL
      • 학과 열에 대한 인덱스가 없기 때문에 데이블의 모든 데이터를 확인하는 풀 테이블 스캔 방법을 선택
  • 프로그램에서 자주 사용하는 쿼리문을 풀 테이블 스캔으로 하면 DBMS에 부담을 줄 수 있음
    • 그러므로 실행 계획을 확인해보고, 필요하다면 인덱스를 지정하는 것이 좋음
      • 학과 인덱스를 추가하고 검색하면 아래와 같이 실행 계획이 변경됨
  • 옵티마이저는 주어진 시간 내에 여러 개의 실행 계획을 세우고 비용이 가장 낮은 방법을 선택
    • 인덱스 스캔 비용이 풀 테이블 스캔 비용보다 크다고 판단되면, 옵티마이저는 인덱스를 선택하지 않을 수도 있음
  • DBMS에는 힌트라는 기능이 있음
    • 옵티마이저의 실행 계획을 변경할 수 있도록 알려주는 기능
    • FORCE INDEX 키워드 사용 시 풀 테이블 스캔으로 세운 실행 계획을 인덱스 스캔으로 변경 가능
    • EXPLAIN SELECT * FROM 학생 FORCE INDEX(학과_idx) WHERE 학과 = '컴퓨터공학과' OR 학과 = '수학과' OR 학과 = '물리' OR 학과 = '유아교육과';
    • 옵티마이저는 실행 계획을 세울때 힌트를 우선 고려함
      • 옵티마이저가 세운 계획이 있더라도 이를 무시하고 힌트로 지정한 방식으로 실행 계획을 세움
    • 힌트는 실행 계획을 직접 변경할 수 있는 장점이 있지만, 전문가들은 계획적이지 않은 힌트 사용을 권장하지 않음
      • 잘못된 힌트 사용은 오히려 옵티마이저의 올바른 판단을 방해하여 높은 비용의 실행 계획이 선택 될 수 있음
      • 일반적으로 옵티마이저가 다양한 통계 정보를 활용해 알아서 실행 계획을 잘 세우기 때문에 옵티마이저의 판단을 따르는 것이 바람직
      • 힌트는 옵티마이저가 세운 실행 계획보다 더 좋은 실행 계획을 만들 수 있는 경우에만 제한적으로 사용하는 것이 좋음
    • USE INDEX
      • MYSQL에서 해당 인덱스를 써달라고 권장하는 명령어
      • SELECT * FROM player USE INDEX (인덱스명) WHERE number = 7;
    • FORCE INDEX
      • 해당 인덱스를 쓰라고 강제 하는 느낌
      • 단, 옵티마이저가 해당 인덱스로 원하는 데이터를 가져올 수 없다고 판단하면 사용하지 않고 풀스캔
      • SELECT * FROM player FORCE INDEX (인덱스명) WHERE number = 7;

인덱스

  • 시간 복잡도
    • 풀스캔 : O(n)
    • 인덱스가 걸린 경우(B-tree 기반 인덱스) : O(logN)
  • 인덱스 사용 이유
    • 조건을 만족하는 튜플들을 빠르게 조회하기 위해
    • 빠르게 정렬(order by)하거나 그룹핑(group by) 하기 위해
  • 인덱스 생성 쿼리
    • 전체 텍스트 인덱스
      • 텍스트 형식의 데이터를 대상으로 하는 인덱스
      • 주로 긴 텍스트 열에 대한 검색을 효율적으로 수행하기 위해 사용
      • 전체 텍스트 검색 기능은 단순 문자열 일치를 넘어 단어 간의 일치도 고려
      • 예) 블로그 게시말이나 제품 설명 같은 텍스트 데이터 검색 시 사용
    • 공간 인덱스
      • 지리 정보(Geometry 또는 Geography)를 포함하는 열에 대한 인덱스
      • 주로 지리 정보 시스템(GIS)에서 사용되며, 지도 데이터나 위치 기반 서비스에서 지리적 위치를 기반으로 검색 및 분석을 수행할 때 사용
      • 예를 들어, 지도 상의 지리적 범위를 나타내는 다각형이나 선, 점과 같은 요소를 효율적으로 검색할 수 있도록 도와줌
  • # 일반 인덱스 CREATE INDEX idx_name ON table_name(column_name); # 고유 인덱스 생성 CREATE UNIQUE INDEX idx_name ON table_name(column_name); # 복합 인덱스 생성 CREATE INDEX idx_name ON table_name(column1, column2, ...); # 전체 텍스트 인덱스 ALTER TABLE table_name ADD FULLTEXT(column_name); # 공간 인덱스 CREATE SPATIAL INDEX idx_name ON table_name(column_name);
  • 대부분 RDBMS는 Primary Key는 인덴스 자동 생성
  • 인덱스 조회
    • SHOW INDEX FROM table_name;
    SHOW INDEX FROM employees;

+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| employees  |          0 | PRIMARY    |            1 | emp_id      | A         |      299523 |     NULL | NULL   |      | BTREE      |         |               |
| employees  |          1 | idx_dept   |            1 | dept_id     | A         |           7 |     NULL | NULL   | YES  | BTREE      |         |               |
| employees  |          1 | idx_dept   |            2 | manager_id  | A         |          26 |     NULL | NULL   | YES  | BTREE      |         |               |
| employees  |          1 | idx_name   |            1 | last_name   | A         |       29952 |     NULL | NULL   |      | BTREE      |         |               |
| employees  |          1 | idx_name   |            2 | first_name  | A         |      149761 |     NULL | NULL   |      | BTREE      |         |               |
+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    • 첫 번째 열은 **Table**로 인덱스가 속한 테이블의 이름입니다.
    • Non_unique 열은 해당 인덱스가 고유한지 여부를 나타냄
      • 0이면 고유한 인덱스, 1이면 고유하지 않은 인덱스
    • Key_name 열은 인덱스의 이름
    • Seq_in_index 열은 인덱스 내의 열의 순서
    • Column_name 열은 인덱스를 생성한 열의 이름
    • Collation, Cardinality, Sub_part, Packed, Null, Index_type, Comment, Index_comment 열은 추가적인 인덱스 정보 제공

B-tree 기반 인덱스 동작 방식

  • 이진 탐색 트리
    • 자녀 노드는 최대 두 개까지
  • B tree
    • M : 각 노드의 최대 자녀 노드 수
      • 최대 M개의 자녀를 가질 수 있는 B tree를 M차 B tree라 함
    • M-1 : 각 노드의 최대 key 수
    • M/2 : 각 노드의 최소 자녀 노드 수
    • M/2 -1 : 각 노드의 최소 key 수
      • root node, leaf node 제외

인덱스 개요

  • DBMS에는 원하는 데이터를 빨리 찾을 수 있는 ‘인덱스’라는 자료구조가 있음
    • 데이블의 모든 데이터를 하나씩 확인하지 않고 찾고자 하는 데이터를 빨리 찾을 수 있도록 돕는 정보
  • DBMS의 인덱스
    • 인덱스는 키(key)와 행주소(row address)에 대한 정보를 담고 있음
      • 키 : 찾고자 하는 키워드
      • 행 주소 : 위치를 나타내는 정보
  • 인덱스와 기본 키
    • 기본 키는 테이블에서 매우 중요한 정보로 DBMS는 기본 키로 지정된 열에 인덱스를 자동으로 만듦
    • B-Tree 인덱스
      • 뒤집어진 나무 모양의 구조
      • 맨 위의 노드를 루트 노드, 맨 아래의 노드를 리프 노드, 이들 사이에 있는 노드를 브랜치 노드라 함
      • 찾고자 하는 인덱스 키가 가운데 노드보다 작으면 왼편만 검색하고, 크면 오른편만 검색함
        • 이런 방식으로 범위를 좁혀가면서 데이터를 찾기 때문에 인덱스를 찾는 시간이 매우 짧음
  • 인덱스 지정
    • 빠른 검색이 필요한 열이 있다면 인덱스를 직접 지정해야 함
      • 인덱스를 잘못 지정하면 오히려 성능이 나빠질 수도 있음
    CREATE INDEX employee_index ON 직원(이름);
    • 인덱스를 잘 지정하기 위해서는 값이 중복되지 않고 유일한 열을 선택해야 함
      • 이런 열을 인덱스로 지정하면 검색 속도가 빨라짐
      • 예) 사번, 이름(중복이 있을수 있으나 확률이 적음)
    • 인덱스 부적합
      • 성별 : 성별이 여자인 사람은 과반이 넘어서 전체 테이블 조회가 나을 수 있음
      • 주소 : 중복되지 않지만 긴 문자열을 인덱스 키로 지정 시 인덱스에 저장되는 데이터 크기도 덩달아 커져 성능에 부담이 될 수 있음
  • 인덱스는 막 만들어도 괜찮은가?
    • table에 write 할 때마다 index도 변경 발생
      • 오버헤드가 생김
    • 추가적인 저장 공간 차지
    • 위와 같은 이유로 불필요한 인덱스를 만들지 말자!
  • Full scan이 더 좋은 경우
    • table에 데이터가 조금 있는 경우(몇십, 몇백 건 정도)
    • 조회하려는 데이터가 테이블의 상당 부분을 차지할 때
      • 조회 데이터가 많은 경우는 풀스캔이 더 빠를 수 있음
      • 풀 스캔 여부는 옵티마이저가 판단
  • 참고
    • order by나 group by에도 index 사용 가능
    • FK에는 index 자동 생성되지 않을 수 있음(join 관련)
    • 이미 데이터가 몇 백만 건 이상 있는 테이블에 인덱스를 생성하는 경우 시간이 몇 분 이상 소요될 수 있고 DB 성능에 안 좋은 영향을 줄 수 있음

index 종류 (참고 유튜브)

  • Covering index
    • 조회하는 attribute(s)를 index가 모두 cover하는 경우를 말함
    • 조회 성능이 더 빠름
    • 종종 의도적으로 Covering index로 조회하도록 하기도 함
  • Hash index
    • hash table을 사용하여 index 구현
    • 시간복잡도 0(1)의 성능
    • rehasing에 대한 부담
      • Array에 저장한 데이터가 꽉 차면 늘려야 하는 것에 대한 부담
    • equality 비교만 가능, range 비교 불가
      • 범위 비교 불가
    • multicolumn index의 경우 전체 attributes에 대한 조회만 가능

쿼리 옵티마이저의 실행 계획

  • 풀 테이블 스캔 full table scan
    • 테이블 전체를 찾는 스캔 방식
  • 인덱스 스캔 index scan
    • 인덱스를 통해 빠른 검색을 하는 스캔 방식
  • 쿼리 실행 계획
    • DBMS는 쿼리를 최적으로 실행할 계획을 짬
    • 쿼리문을 실행하면 DBMS에 있는 쿼리 옵티마이저가 이 쿼리문에 맞게 최적의 실행 계획을 세움
      • 쿼리 옵티마이저 : 쿼리 실행 최적화를 돕는 DBMS 기능
  • 옵티마이저 동작
    • 쿼리문을 실행하면 옵티마이저는 자신이 처리할 수 있는 형태로 쿼리문을 변환함
    • 테이블에 대한 통계 정보를 활용해서 쿼리문 실행을 위해 얼마나 많은 작업을 해야 하는지를 비용 측면에서 계산함
      • 통계정보 : 테이블의 행의 개수, 인텍스 크기, 열 값의 분포도 등
    • 그리고 그 계산 결과를 기반으로 최적의 실행 계획을 세움
    • 예를 들어 MariaDB에서는 EXPLAIN 키워드를 사용하여 실행 계획을 확인함
      • EXPLAIN 키워드 옆에 쿼리문을 작성하면 쿼리문을 실행하기 위해 옵티마이저가 어떤 실행 계획을 세웠는지 확인할 수 있음
      • 해당 쿼리는 풀 테이블 스캔
      • 테이블의 모든 정보를 가져와야 하기 때문에 인덱스를 거칠 필요가 없음
      • EXPLAIN 실행 계획 확인 시 위와 같이 나옴
        • key가 NULL
        • 인덱스를 활용하지 않고 풀 테이블 스캔을 통해 데이터를 가져온다는 의미
      • key : PRIMARY
      • 인덱스를 활용해서 스캔하는 인덱스 스캔 방식
      • 학번 컬럼이 기본 키로 지정되어 있어서 인덱스 스캔을 함
      • 기본키가 지정되어 있으면 인덱스가 만들어지기 때문에 옵티마이저는 쿼리를 실행할 때 쿼리문의 WHERE 절에 주목함
        • WHERE 절에 기본 키로 정의된 열이 있다면 풀 테이블 스캔과 인덱스 스캔을 따져본 후 통계 정보를 이용해 최소 비용이 드는 방향을 실행 계획을 세움
      • 학과로 검색 시 key → NULL
      • 학과 열에 대한 인덱스가 없기 때문에 데이블의 모든 데이터를 확인하는 풀 테이블 스캔 방법을 선택
  • 프로그램에서 자주 사용하는 쿼리문을 풀 테이블 스캔으로 하면 DBMS에 부담을 줄 수 있음
    • 그러므로 실행 계획을 확인해보고, 필요하다면 인덱스를 지정하는 것이 좋음
      • 학과 인덱스를 추가하고 검색하면 아래와 같이 실행 계획이 변경됨
  • 옵티마이저는 주어진 시간 내에 여러 개의 실행 계획을 세우고 비용이 가장 낮은 방법을 선택
    • 인덱스 스캔 비용이 풀 테이블 스캔 비용보다 크다고 판단되면, 옵티마이저는 인덱스를 선택하지 않을 수도 있음
  • DBMS에는 힌트라는 기능이 있음
    • 옵티마이저의 실행 계획을 변경할 수 있도록 알려주는 기능
    • FORCE INDEX 키워드 사용 시 풀 테이블 스캔으로 세운 실행 계획을 인덱스 스캔으로 변경 가능
    • EXPLAIN SELECT * FROM 학생 FORCE INDEX(학과_idx) WHERE 학과 = '컴퓨터공학과' OR 학과 = '수학과' OR 학과 = '물리' OR 학과 = '유아교육과';
    • 옵티마이저는 실행 계획을 세울때 힌트를 우선 고려함
      • 옵티마이저가 세운 계획이 있더라도 이를 무시하고 힌트로 지정한 방식으로 실행 계획을 세움
    • 힌트는 실행 계획을 직접 변경할 수 있는 장점이 있지만, 전문가들은 계획적이지 않은 힌트 사용을 권장하지 않음
      • 잘못된 힌트 사용은 오히려 옵티마이저의 올바른 판단을 방해하여 높은 비용의 실행 계획이 선택 될 수 있음
      • 일반적으로 옵티마이저가 다양한 통계 정보를 활용해 알아서 실행 계획을 잘 세우기 때문에 옵티마이저의 판단을 따르는 것이 바람직
      • 힌트는 옵티마이저가 세운 실행 계획보다 더 좋은 실행 계획을 만들 수 있는 경우에만 제한적으로 사용하는 것이 좋음
    • USE INDEX
      • MYSQL에서 해당 인덱스를 써달라고 권장하는 명령어
      • SELECT * FROM player USE INDEX (인덱스명) WHERE number = 7;
    • FORCE INDEX
      • 해당 인덱스를 쓰라고 강제 하는 느낌
      • 단, 옵티마이저가 해당 인덱스로 원하는 데이터를 가져올 수 없다고 판단하면 사용하지 않고 풀스캔
      • SELECT * FROM player FORCE INDEX (인덱스명) WHERE number = 7;

인덱스

  • 시간 복잡도
    • 풀스캔 : O(n)
    • 인덱스가 걸린 경우(B-tree 기반 인덱스) : O(logN)
  • 인덱스 사용 이유
    • 조건을 만족하는 튜플들을 빠르게 조회하기 위해
    • 빠르게 정렬(order by)하거나 그룹핑(group by) 하기 위해
  • 인덱스 생성 쿼리
    • 전체 텍스트 인덱스
      • 텍스트 형식의 데이터를 대상으로 하는 인덱스
      • 주로 긴 텍스트 열에 대한 검색을 효율적으로 수행하기 위해 사용
      • 전체 텍스트 검색 기능은 단순 문자열 일치를 넘어 단어 간의 일치도 고려
      • 예) 블로그 게시말이나 제품 설명 같은 텍스트 데이터 검색 시 사용
    • 공간 인덱스
      • 지리 정보(Geometry 또는 Geography)를 포함하는 열에 대한 인덱스
      • 주로 지리 정보 시스템(GIS)에서 사용되며, 지도 데이터나 위치 기반 서비스에서 지리적 위치를 기반으로 검색 및 분석을 수행할 때 사용
      • 예를 들어, 지도 상의 지리적 범위를 나타내는 다각형이나 선, 점과 같은 요소를 효율적으로 검색할 수 있도록 도와줌
  • # 일반 인덱스 CREATE INDEX idx_name ON table_name(column_name); # 고유 인덱스 생성 CREATE UNIQUE INDEX idx_name ON table_name(column_name); # 복합 인덱스 생성 CREATE INDEX idx_name ON table_name(column1, column2, ...); # 전체 텍스트 인덱스 ALTER TABLE table_name ADD FULLTEXT(column_name); # 공간 인덱스 CREATE SPATIAL INDEX idx_name ON table_name(column_name);
  • 대부분 RDBMS는 Primary Key는 인덴스 자동 생성
  • 인덱스 조회
    • SHOW INDEX FROM table_name;
    SHOW INDEX FROM employees;

+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| employees  |          0 | PRIMARY    |            1 | emp_id      | A         |      299523 |     NULL | NULL   |      | BTREE      |         |               |
| employees  |          1 | idx_dept   |            1 | dept_id     | A         |           7 |     NULL | NULL   | YES  | BTREE      |         |               |
| employees  |          1 | idx_dept   |            2 | manager_id  | A         |          26 |     NULL | NULL   | YES  | BTREE      |         |               |
| employees  |          1 | idx_name   |            1 | last_name   | A         |       29952 |     NULL | NULL   |      | BTREE      |         |               |
| employees  |          1 | idx_name   |            2 | first_name  | A         |      149761 |     NULL | NULL   |      | BTREE      |         |               |
+------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    • 첫 번째 열은 **Table**로 인덱스가 속한 테이블의 이름입니다.
    • Non_unique 열은 해당 인덱스가 고유한지 여부를 나타냄
      • 0이면 고유한 인덱스, 1이면 고유하지 않은 인덱스
    • Key_name 열은 인덱스의 이름
    • Seq_in_index 열은 인덱스 내의 열의 순서
    • Column_name 열은 인덱스를 생성한 열의 이름
    • Collation, Cardinality, Sub_part, Packed, Null, Index_type, Comment, Index_comment 열은 추가적인 인덱스 정보 제공

B-tree 기반 인덱스 동작 방식

  • 이진 탐색 트리
    • 자녀 노드는 최대 두 개까지
  • B tree
    • M : 각 노드의 최대 자녀 노드 수
      • 최대 M개의 자녀를 가질 수 있는 B tree를 M차 B tree라 함
    • M-1 : 각 노드의 최대 key 수
    • M/2 : 각 노드의 최소 자녀 노드 수
    • M/2 -1 : 각 노드의 최소 key 수
      • root node, leaf node 제외

인덱스 개요

  • DBMS에는 원하는 데이터를 빨리 찾을 수 있는 ‘인덱스’라는 자료구조가 있음
    • 데이블의 모든 데이터를 하나씩 확인하지 않고 찾고자 하는 데이터를 빨리 찾을 수 있도록 돕는 정보
  • DBMS의 인덱스
    • 인덱스는 키(key)와 행주소(row address)에 대한 정보를 담고 있음
      • 키 : 찾고자 하는 키워드
      • 행 주소 : 위치를 나타내는 정보
  • 인덱스와 기본 키
    • 기본 키는 테이블에서 매우 중요한 정보로 DBMS는 기본 키로 지정된 열에 인덱스를 자동으로 만듦
    • B-Tree 인덱스
      • 뒤집어진 나무 모양의 구조
      • 맨 위의 노드를 루트 노드, 맨 아래의 노드를 리프 노드, 이들 사이에 있는 노드를 브랜치 노드라 함
      • 찾고자 하는 인덱스 키가 가운데 노드보다 작으면 왼편만 검색하고, 크면 오른편만 검색함
        • 이런 방식으로 범위를 좁혀가면서 데이터를 찾기 때문에 인덱스를 찾는 시간이 매우 짧음
  • 인덱스 지정
    • 빠른 검색이 필요한 열이 있다면 인덱스를 직접 지정해야 함
      • 인덱스를 잘못 지정하면 오히려 성능이 나빠질 수도 있음
    CREATE INDEX employee_index ON 직원(이름);
    • 인덱스를 잘 지정하기 위해서는 값이 중복되지 않고 유일한 열을 선택해야 함
      • 이런 열을 인덱스로 지정하면 검색 속도가 빨라짐
      • 예) 사번, 이름(중복이 있을수 있으나 확률이 적음)
    • 인덱스 부적합
      • 성별 : 성별이 여자인 사람은 과반이 넘어서 전체 테이블 조회가 나을 수 있음
      • 주소 : 중복되지 않지만 긴 문자열을 인덱스 키로 지정 시 인덱스에 저장되는 데이터 크기도 덩달아 커져 성능에 부담이 될 수 있음
  • 인덱스는 막 만들어도 괜찮은가?
    • table에 write 할 때마다 index도 변경 발생
      • 오버헤드가 생김
    • 추가적인 저장 공간 차지
    • 위와 같은 이유로 불필요한 인덱스를 만들지 말자!
  • Full scan이 더 좋은 경우
    • table에 데이터가 조금 있는 경우(몇십, 몇백 건 정도)
    • 조회하려는 데이터가 테이블의 상당 부분을 차지할 때
      • 조회 데이터가 많은 경우는 풀스캔이 더 빠를 수 있음
      • 풀 스캔 여부는 옵티마이저가 판단
  • 참고
    • order by나 group by에도 index 사용 가능
    • FK에는 index 자동 생성되지 않을 수 있음(join 관련)
    • 이미 데이터가 몇 백만 건 이상 있는 테이블에 인덱스를 생성하는 경우 시간이 몇 분 이상 소요될 수 있고 DB 성능에 안 좋은 영향을 줄 수 있음

index 종류 (참고 유튜브)

  • Covering index
    • 조회하는 attribute(s)를 index가 모두 cover하는 경우를 말함
    • 조회 성능이 더 빠름
    • 종종 의도적으로 Covering index로 조회하도록 하기도 함
  • Hash index
    • hash table을 사용하여 index 구현
    • 시간복잡도 0(1)의 성능
    • rehasing에 대한 부담
      • Array에 저장한 데이터가 꽉 차면 늘려야 하는 것에 대한 부담
    • equality 비교만 가능, range 비교 불가
      • 범위 비교 불가
    • multicolumn index의 경우 전체 attributes에 대한 조회만 가능

쿼리 옵티마이저의 실행 계획

  • 풀 테이블 스캔 full table scan
    • 테이블 전체를 찾는 스캔 방식
  • 인덱스 스캔 index scan
    • 인덱스를 통해 빠른 검색을 하는 스캔 방식
  • 쿼리 실행 계획
    • DBMS는 쿼리를 최적으로 실행할 계획을 짬
    • 쿼리문을 실행하면 DBMS에 있는 쿼리 옵티마이저가 이 쿼리문에 맞게 최적의 실행 계획을 세움
      • 쿼리 옵티마이저 : 쿼리 실행 최적화를 돕는 DBMS 기능
  • 옵티마이저 동작
    • 쿼리문을 실행하면 옵티마이저는 자신이 처리할 수 있는 형태로 쿼리문을 변환함
    • 테이블에 대한 통계 정보를 활용해서 쿼리문 실행을 위해 얼마나 많은 작업을 해야 하는지를 비용 측면에서 계산함
      • 통계정보 : 테이블의 행의 개수, 인텍스 크기, 열 값의 분포도 등
    • 그리고 그 계산 결과를 기반으로 최적의 실행 계획을 세움
    • 예를 들어 MariaDB에서는 EXPLAIN 키워드를 사용하여 실행 계획을 확인함
      • EXPLAIN 키워드 옆에 쿼리문을 작성하면 쿼리문을 실행하기 위해 옵티마이저가 어떤 실행 계획을 세웠는지 확인할 수 있음
      • 해당 쿼리는 풀 테이블 스캔
      • 테이블의 모든 정보를 가져와야 하기 때문에 인덱스를 거칠 필요가 없음
      • EXPLAIN 실행 계획 확인 시 위와 같이 나옴
        • key가 NULL
        • 인덱스를 활용하지 않고 풀 테이블 스캔을 통해 데이터를 가져온다는 의미
      • key : PRIMARY
      • 인덱스를 활용해서 스캔하는 인덱스 스캔 방식
      • 학번 컬럼이 기본 키로 지정되어 있어서 인덱스 스캔을 함
      • 기본키가 지정되어 있으면 인덱스가 만들어지기 때문에 옵티마이저는 쿼리를 실행할 때 쿼리문의 WHERE 절에 주목함
        • WHERE 절에 기본 키로 정의된 열이 있다면 풀 테이블 스캔과 인덱스 스캔을 따져본 후 통계 정보를 이용해 최소 비용이 드는 방향을 실행 계획을 세움
      • 학과로 검색 시 key → NULL
      • 학과 열에 대한 인덱스가 없기 때문에 데이블의 모든 데이터를 확인하는 풀 테이블 스캔 방법을 선택
  • 프로그램에서 자주 사용하는 쿼리문을 풀 테이블 스캔으로 하면 DBMS에 부담을 줄 수 있음
    • 그러므로 실행 계획을 확인해보고, 필요하다면 인덱스를 지정하는 것이 좋음
      • 학과 인덱스를 추가하고 검색하면 아래와 같이 실행 계획이 변경됨
  • 옵티마이저는 주어진 시간 내에 여러 개의 실행 계획을 세우고 비용이 가장 낮은 방법을 선택
    • 인덱스 스캔 비용이 풀 테이블 스캔 비용보다 크다고 판단되면, 옵티마이저는 인덱스를 선택하지 않을 수도 있음
  • DBMS에는 힌트라는 기능이 있음
    • 옵티마이저의 실행 계획을 변경할 수 있도록 알려주는 기능
    • FORCE INDEX 키워드 사용 시 풀 테이블 스캔으로 세운 실행 계획을 인덱스 스캔으로 변경 가능
    • EXPLAIN SELECT * FROM 학생 FORCE INDEX(학과_idx) WHERE 학과 = '컴퓨터공학과' OR 학과 = '수학과' OR 학과 = '물리' OR 학과 = '유아교육과';
    • 옵티마이저는 실행 계획을 세울때 힌트를 우선 고려함
      • 옵티마이저가 세운 계획이 있더라도 이를 무시하고 힌트로 지정한 방식으로 실행 계획을 세움
    • 힌트는 실행 계획을 직접 변경할 수 있는 장점이 있지만, 전문가들은 계획적이지 않은 힌트 사용을 권장하지 않음
      • 잘못된 힌트 사용은 오히려 옵티마이저의 올바른 판단을 방해하여 높은 비용의 실행 계획이 선택 될 수 있음
      • 일반적으로 옵티마이저가 다양한 통계 정보를 활용해 알아서 실행 계획을 잘 세우기 때문에 옵티마이저의 판단을 따르는 것이 바람직
      • 힌트는 옵티마이저가 세운 실행 계획보다 더 좋은 실행 계획을 만들 수 있는 경우에만 제한적으로 사용하는 것이 좋음
    • USE INDEX
      • MYSQL에서 해당 인덱스를 써달라고 권장하는 명령어
      • SELECT * FROM player USE INDEX (인덱스명) WHERE number = 7;
    • FORCE INDEX
      • 해당 인덱스를 쓰라고 강제 하는 느낌
      • 단, 옵티마이저가 해당 인덱스로 원하는 데이터를 가져올 수 없다고 판단하면 사용하지 않고 풀스캔
      • SELECT * FROM player FORCE INDEX (인덱스명) WHERE number = 7;
반응형
저작자표시 비영리 변경금지

댓글

티스토리툴바