SQL 인덱스

 

 

• 랜덤 I/O와 순차 I/O에 대해서 설명해주세요.
  병목 현상 : 병목 현상은 다른 분야의 병목 현상과 근본적으로 크게 다르지 않고, 학계에서는 시스템의 성능이 지연되고 프로세스 전체적인 효율성이 감소되는 것으로 정의하고 있다
  https://velog.io/@ddangle/%EC%88%9C%EC%B0%A8Sequential-IO%EC%99%80-%EB%9E%9C%EB%8D%A4Random-IO
  i/o는 input output 입출력을 의미
  랜덤i/o는 하드디스크 드라이브의 플래터(원판)를 돌려서 읽어야할 데이터가 저장된 위치로 디스크 헤어디를 이동 시간다음 데이터를 읽는것 순차 I/O도 동일한 과정을 거친다
  차이점 : 여러 개의 데이터 입력시 -> 순차 I/O는 디스크 헤드를 한번만 움직이고 
  랜덤 I/O는 디스크 헤더를 데이터 갯수만큼 움직임
  디스크 헤더를 움직이는 시간은 데이터를 읽고 쓰는데에 가장 시간이 오래 걸리는 작업으로 시간이 병목되는 부분
  따라서 랜덤 I/O 작업이 부하가 큼
  
  
• 인덱스에 대해서 설명해주세요.
  인덱스는 책의 색인과 같은 것으로 인덱스를 통해 칼럼의 키를 찾아 칼럼에 더 빨리 접근할 수 있는 기능을 제공
  테이블 전체를 읽는 것이 아니라 pk를 통해 바로 저장된 위치를 가져오므로 
  레코드의 검색이 빠릅니다. 
  보통 b-tree 알고리즘과 해시 알고리즘을 사용합니다.
  
  
• 인덱스의 동작 방식에 대해서 설명해주세요.
  데이터 파일의 블록이 10만개가 있다고 가정
  데이터에 select문 을 실행할때 server process가 구문 분석 과정을 마친 후 
  data buffer cahce에 조건에 부합하는 데이터가 있는지 확인
     index가 없다면:  10만개 전부 database buffer cache로 복사한 뒤 풀 스캔으로 찾게됨
  (이 부분에서 테이블의 모든 블록을 읽기 때문에 조건(WHERE와 같은)을 확인하려면 메모리에 전체 테이블을 올려야함 따라서 전체 테이블을 캐시에 일단 올리게되는 것)
     index가 있다면 : where절의 조건의 컬럼이 index의 키로 생성되어있는지 확인한 뒤, 인덱스에 먼저 가서 조건에 부합하는 정보가 어떤 ROWID를 가지고 있는지 확인 후 ROWID에 있는 블럭을 찾아가 해당 블럭만 buffer cache에 복사
  
  
• 어떤 기준으로 인덱스를 설정해야할까요? -카디널리티, 선택도, 조회 활용도, 수정빈도
  카디널리티 : 카디널리티가 높으면 인덱스 설정에 좋다 -> 인덱스를 통해 불필요한 데이터의 대부분을 걸러낼 수 있음
      카디널리티가 높음  = 한 칼럼이 갖고 있는 값의 중복도가 낮음 = 값들이 대부분 다른 값을 가짐
  선택도 : 선택도가 낮으면 인덱스 설정에 좋은 컬럼이다 (5 ~ 10%)
      선택도가 높다 = 한 컬림이 갖고잇는 값 하나로 여러 row가 찾아진다 
  조회 활용도 : 조회 활용도가 높으면 인덱스 설정에 좋은 칼럼이다.
      해당 칼럼이 실제 작업에서 얼마나 활용되는지 에 대한 값 ( WHERE의 대상 칼럼으로 많이 활용되는지)
  수정 빈도 : 수정빈도가 낮으면 인덱스 설정에 좋은 칼럼이다
      인덱스도 테이블 이기때문에, 인덱스로 지정된 칼럼의 값이 바뀌게 되면 인덱스 테이블도 새롭게 갱신되어야하기 때
  1) 열(Column)에 많은 NULL 값이 포함된 경우
   - NULL 값을 제외하고 검색해야하는 경우 인덱스를 사용하면 검색 속도를 향상시킬 수 있다.
  2)열(Column)에 광범위한 값이 포함된 경우
   - 인덱스 컬럼에 다양한 값이 있는 경우 인덱스를 사용하면 검색 속도를 향상시킬 수 있다.
  3) WHERE절 혹은 JOIN 조건에 자주 사용되는 경우
  4) 테이블이 크고 대부분의 쿼리가 테이블에서2~4%미만의 행을 검색할 것으로 예상되는경우
   -  Index range scan이 table full scan 보다 느려지는 조회 건수 지점을 인덱스 손익분기점 이라 하는데, 테이블 전체 데이터양의 10 ~ 15% 이상을 출력하게 되면 table full scan이 효율적일 수 있다.
  5) ORDER BY 절에 자주 사용되는 경우
   - 인덱스는 기본적으로 정렬되어있어서 order by를 수행할 필요가 없다.
  
  
• 테이블에 인덱스를 많이 설정하면 좋을까요?
  DML 작업이 커밋되면 변경 사항을 인덱스에도 반영해야하는데 인덱스가 많을 수록 관련 인덱스를 변경해야하므로 서버의 부담이 증가한다. 
  
  
• 커버링 인덱스(Covering index)에 대해서 설명해주세요.
  인덱스를 잘 활용한다면  인덱스 안에 포함된 데이터를 사용할 수 있어 굳이 실제 데이터까지 접근할 필요가 없습니다 
  따라서 쿼리를 충족시키는데 필요한 모든 데이터를 갖고 있는 인덱스를 커버링 인덱스라고 합니다 
  covered index , covering index
  
  커버링 인덱스가 적용되면 아래와 같이 EXPLAIN 결과 (실행 계획) 의 Extra 필드에 "Using index" 가 표기됩니다.
  Extra 항목에 Using index가 있어야만 인덱스를 사용한 것이 아닙니다.
  인덱스 사용 유무는 key 항목에 선택된 인덱스가 있냐 없냐의 차이입니다.
  Extra 항목에 Using index가 있는 경우는 쿼리 전체가 인덱스 컬럼값으로 다 채워진 경우에만 발생합니다.
  
  https://jojoldu.tistory.com/476
  
  
  
  
  
• 다중 컬럼 인덱스(Multi-column index, 복합 인덱스)에 대해서 설명해주세요.
  2개 이상의 칼럼을 포함하는 인덱스
  
  • Multi Column Index의 장점
    ① 질의(SQL) 컬럼이 모두 조합 인덱스에 있는 경우, 물리적인 데이터 블록을 읽을 필요가 없다.
    (인덱스 테이블만 읽으면 된다.) -> 커버링 인덱스 처럼
  • Multi Column Index를 고려해야 하는 경우
    ① WHERE에 사용될 때 AND 연산자에 의해 자주 같이 질의되는 컬럼들인 경우.
  
  
  
• B-Tree 인덱스와 B+Tree 인덱스에 대해 설명해주세요.
  b-tree는 데이터베이스와 파일 시스템에서 널리 사용되는 이진트리를 확장한 트리 구조 입니다
  b+tree는 MySql의 DB 엔진인 innoDB에서 사용하는 트리구조로 
  b-tree의 확장된 개념 입니다
  
  b-tree의 경우에는 internal, branch 노드에 key와 데이터를 담을 수 있는데
  b+tree의 경우에는 브랜치 노드에 key만 담고 data는 담지 않는다 리프 노드에만 key와 데이터를 저장하고 리프 노드끼리 linked list로 연결되어있다
  
  장점 : 리프노드를 제외하고 데이터를 담아두지 않기때문에 메모리를 더 확보함으로서 더 많은 key들을 수용할 수 있다. 하나의 노드에 더 많은 key들을 담을 수 있기때문에 트리의 높이가 낮아짐
  
  
  
• Hash 인덱스에 대해서 설명해주세요.
  해시(Hash)인덱스는 검색하고자하는 값을 주면 해시 함수를 거쳐 찾고자하는 키 값이 포함된 버켓을 찾아낸다 
  버킷은 인덱스의 키값과 레코드의 주소값 등의 정보를 두는 공간이다 
  b-tree보다 결과가 빠를 수 있다
  
  
  
• 클러스터링 인덱스에 대해서 설명해주세요.
  
  Clustered 인덱스는 물리적 정렬로 DB에 데이터를 입력 시 Clustered 인덱스를 기준으로 입력이 된다.
  따라서 한 테이블에 오직 하나만 존재 할 수 있으며 Table을열었을 때 Order By를 사용하지 않아도 데이터가 Clustered 인덱스에 따라 정렬이 되어 있는 것을 확인 할 수있다.
  클러스터링 인덱스로 정의된 테이블의 데이터는 클러스터링된 인덱스의 키 순서에 따라 저장됩니다. 따라서 인덱스의 검색 성능이 향상됩니다. - 입력 수정 삭제는 느림
  리프 페이지가 모두 차있는데 새로운 데이터를 추가하면 페이지 분할이 일어남
  인덱스가 B-tree 구조이기 때문에 페이지 크기가 유지되어야 하므로
  이때 페이지 전체를 다시 정렬해야하기 때문에 느려진다.
  https://velog.io/@sweet_sumin/%ED%81%B4%EB%9F%AC%EC%8A%A4%ED%84%B0%EB%93%9C-%EC%9D%B8%EB%8D%B1%EC%8A%A4-Clustered-Index-%EB%84%8C-%ED%81%B4%EB%9F%AC%EC%8A%A4%ED%84%B0%EB%93%9C-%EC%9D%B8%EB%8D%B1%EC%8A%A4-Non-Clustered-Index
  
  논 클러스터 인덱스 /
  레코드의 원본은 정렬되지 않고, 인덱스 페이지만 정렬된다. 즉, Non-Clustered Index는 데이터 페이지를 건드리지 않고, 별도의 장소에 인덱스 페이지를 생성
  한다.
  
  
  
• 인덱스 스캔 방식에 대해서 설명해주세요.
  인덱스 스캔은 인덱스를 사용해 레코드를 찾는 작업을 말합니다
  
  

  인덱스 종류	설명	장점	단점
  인덱스 전체 스캔  (Index Full Scan)	인덱스의 모든 데이터를 읽어오는 방법	인덱스 크기가 작은 경우 빠른 검색 가능	테이블의 크기가 큰 경우 전체 테이블 스캔보다 느릴 수 있음
  인덱스 범위 스캔 (Index Range Scan)	인덱스의 일부 데이터만 읽어오는 방법으로, 인덱스의 범위를 지정하여 빠른 검색 가능	인덱스의 크기와 테이블의 크기에 관계없이 빠른 검색 가능	검색 범위가 넓을 경우 느릴 수 있음
  인덱스 고유 스캔 (Index Unique Scan)	인덱스의 유일한 값을 검색하는 방법	인덱스의 크기와 테이블의 크기에 관계없이 빠른 검색 가능	인덱스 컬럼이 유일한 값을 가지고 있을 때만 사용 가능
  인덱스 루스 스캔 (Index Loose Scan)	인덱스의 일부 데이터만 읽어오는 방법으로, 인덱스의 범위를 지정하지 않고 검색을 수행	인덱스의 크기와 테이블의 크기에 관계없이 빠른 검색 가능	정확도가 떨어질 수 있음
  인덱스 병합 스캔 (Index Merge Scan)	여러 개의 인덱스를 병합하여 검색하는 방법	복잡한 검색 조건을 처리할 수 있음	인덱스의 크기가 작은 경우에만 유용하며, 병합에 시간이 걸릴 수 있음

  [ 더 알아보기 ]
  
  💡 데이터베이스에서 비용(Cost)의 의미
  
  - 시스템의 성능, 안정성, 보안, 유지보수 등을 유지하고 개선하기 위해 지출되는 비용을 의미합니다. 데이터베이스 비용은 하드웨어, 소프트웨어, 인력, 보안, 유지보수 비용의 요소로 포함되어 있습니다.
  
  
  
  
• 쿼리 실행 계획에 대해서 설명해주세요. 실행 계획을 확인해본적이 있나요?
  쿼리 실행계획이란 DBMS가 SQL쿼리를 처리하기 위해 사용하는 실행계획입니다. 이것은 DBMS에서 쿼리를 처리할때 데이터베이스에서 데이터를 검색하고 가져오는 방법을 결정하는데 사용됩니다. 
  sql 을 실행하는데 있어 사용되는 비용과 어떤 방식이 가장 적절한지 판단하고
  여러개의 플랜을 평가하여 어떤 순서와 방식으로 처리할 것인지 계획을 세우고 필요에 따라 변경하여 성능을개선하는데 사용됩니다. 
  이를 통해 최소한의 리소르를 사용하면서 최적의 성능을 제공할 수 있습니다. 
  
  
  Oracle에서는 EXPLAIN PLAN 명령어를 사용하여 SQL 쿼리의 실행 계획을 생성할 수 있다. 이를 통해 쿼리가 어떻게 실행되는지, 어떤 인덱스가 사용되는지 등을 확인할 수 있다.
  EXPLAIN PLAN 명령어는 다음과 같은 형태로 사용된다.
   

  EXPLAIN PLAN
  SET STATEMENT_ID = '계획명' INTO PLAN_TABLE
  FOR SQL_쿼리;

  
  
  
• 힌트에 대해서 설명해주세요.
  일종의 지시 구문으로서 오라클 옵티마이저에게 SQL문 실행을 위한 데이터를 스캐닝하는 경로, 조인하는 방법등을 알려주기 위해 SQL 사용자가 SQL 구문에 작성하는 것을 말합니다.
  오라클리 항상 최적의 실행경로를 만들어내기는 불가능 하기때문에 직접 최적의 실행 경로를 작성해 주는 것입니다.
  사용자가 특정 SQL 문장에서 어떤 인덱스가 선택도가 가장높은지 알고있는 경우 Optimizer에 의존한 실행 계획보다 훨씬 효율적인 실행계획을 구사할 수 있습니다.
  최적화 목표나, 엑세스 방식, 조인 순서와 방식 등을 지정할 수 있습니다.
  
  
  
• 인덱스가 잘 동작하고 있는지 어떻게 확인할 수 있을까요?
  explain analyze를 통해 실행계획 정보를 출력할 수 있습니다. 
  이를 통해 인덱스 스캔이 이루어지는 방식이나. 쿼리가 실행되는 시간 등을 알 수 있습니다.
  
  
  
• 인덱스 사용시 주의해야할 점에 대해서 알려주세요.
  삽입, 수정, 삭제 기능에 취약합니다
  위의 작업을 할 시에 인덱스 테이블과 원본 테이블 두가지의 테이블이 다시 정렬해야할 수 있으므로 
  데이터베이스의 속도가 떨어질 수 있습니다.
  
  데이터의 양이 적으면 적을 수록 효율을 내기 어렵습니다.
  인덱스는 테이블의 전체 데이터 중에서 10~15% 이하의 데이터를 처리하는 경우에만 효율적이고 그 이상의 데이터를 처리할 땐 인덱스를 사용하지 않는 것이 더 낫다.
  
  예를 들면 100개의 데이터가 있는 테이블과 100만 개의 데이터가 들어 있는 테이블이 있다고 하자. 100만 개의 데이터가 들어있는 테이블이라면 풀 스캔보다는 인덱스 스캔이 유리하겠지만, 100개의 데이터가 들어있는 테이블은 굳이 인덱스 스캔 없이 풀 스캔이 빠를 것이다.
  
  
  
• GROUP BY 사용시 인덱스가 걸리는 조건에 대해 설명해주세요.
  GROUP BY 절에 명시된 컬럼의 순서가 인덱스를 구성하는 컬럼의 순서와 같으면 GROUP BY 절은 이용할 수 있다.
  
  
  
• 이름, 국가, 성별이 있는 테이블에서 인덱스를 어떻게 걸어야할까요?
  칼럼은 카디널리티가 낮기때문에 대부분의 칼럼들이 성별이 중복되므로 인덱스를 생성하는 것이 무의미합니다
  이름의 경우 중복이 발생하므로 인덱스를 써서만들기에는 애매할 수 있습니다
  따라서 국가를 인덱스로만들어서 쓰는게 가장 적절할 수 있습니다
  
  

클러스터드 인덱스 (Clustered Index), 넌 클러스터드 인덱스 (Non Clustered Index)

1. 커버링 인덱스 (기본 지식 / WHERE / GROUP BY)

순차(Sequential) I/O와 랜덤(Random) I/O

https://velog.io/@jwpark06/%ED%9A%A8%EA%B3%BC%EC%A0%81%EC%9D%B8-DB-index-%EC%84%A4%EC%A0%95%ED%95%98%EA%B8%B0

효과적인 DB index 설정하기

 

https://adjh54.tistory.com/163

[DB/Postgres] 테이블 스캔 -1 : 전체, 인덱스(전체, 범위, 고유, 루스, 병합) 스캔