최적화? Indexing?

인덱스란?

• 인덱스(Index)란 데이터베이스 테이블에서 데이터 검색을 빠르게 하기 위한 구조이다.
• 테이블에 데이터가 많아지면 검색 속도가 느려지기 때문에, 인덱스를 사용하면 쿼리 성능을 극대화 할 수 있다.
• 인덱스가 없을 경우 쿼리가 Full Table Scan 수행
• 반면 인덱스가 있으면 특정 열에 대해 빠르게 검색 가능.

 

• 인덱스의 종류 (Types of Indexes)
  • B-Tree Index
    • B-Tree 구조로 데이터를 정렬하여 검색 속도를 높임
    • 대부분의 데이터베이스에서 가장 널리 사용되는 인덱스 형태
    • PRIMAL KEY와 UNIQUE KEY는 기본적으로  B-Tree 인덱스이다
      
      
  • Unique Index
    • 중복되지 않는 값만 저장할 수 있다.
    • 예) 이메일이나 주민등록번호처럼 유니크한 데이터에 적합하다.

CREATE UNIQUE INDEX idx_email_unique ON customers(email);

• Clustered Index
  • 데이터가 물리적으로 정렬된 상태로 저장된다.
  • 테이블 당 하나만 생성 가능 (주로 PRIMAL KEY로 사용)
  • 검색 시 매우 빠르지만, 테이블 정렬이 자주 변경되면 성능에 영향을 미칠 수 있다.
    
    
• Non-Clustered Index
  • 데이터 자체는 정렬되지 않지만, 인덱스만 따로 저장된다.
  • 한 테이블에 여러 개의 비클러스터형 인덱스를 만들 수 있다.
    
    
• Composite Index
  • 여러 개의 컬럼을 조합해 인덱스를 생성
  • WHERE 조건에 여러 열을 사용할 때 효과적이다.
  • 첫 번째 컬럼의 카디널리티가 매우 중요해서, 높은 카디널리티의 컬럼을 앞에 배치하는 것이 좋다.

CREATE INDEX idx_title_board_date ON card (title, board_id, due_date);

• Function-based Index
  • 특정 열에 함수를 적용한 결과를 인덱스로 사용한다.
  • 예) 이메일 대소문자 구분을 없애기 위해 LOWER(email)을 인덱스에 사용

CREATE INDEX idx_lower_email ON customers (LOWER(email));

---

 

카디널리티(Cardinality)와 인덱스 최적화

• 카디널리티(Cardinality)는 특정 컬럼에서 유니크한 값의 개수를 의미한다. 카디널리티가 인덱스 성능에 큰 영향을 미친다
  
  
• 카디널리티 유형
  • 높은 카디널리티
    • 유니크한 값이 많은 경우
    • 예) 사용자 ID, 이메일
    • 인덱스 효율이 높아 검색 속도가 크게 향상된다.
  • 중간 카디널리티
    • 예) 날짜 (due_date 등)
    • 검색 속도는 유리하지만, 인덱스만으로 성능이 크게 향상되진 않는다.
  • 낮은 카디널리티
    • 중간 값이 많은 경우
    • 예) 상태(status  = "ACTIVE", "INACTIVE")
    • 인덱스가 비효율적일 수 있으며, 테이블 스캔이 발생할 가능성이 크다.

---

• 카디널리티 확인 방법

SHOW INDEX FROM card;

 

• 결과 예시

+--------+------------+----------------------------+--------------+-------------+-------------+
| Table  | Non_unique | Key_name                   | Seq_in_index | Column_name | Cardinality |
+--------+------------+----------------------------+--------------+-------------+-------------+
| card   |          0 | PRIMARY                    |            1 | id          |   1000000   |
| card   |          1 | idx_card_title             |            1 | title       |      50000  |
| card   |          1 | idx_card_board_due_date    |            1 | board_id    |         10  |
| card   |          1 | idx_card_board_due_date    |            2 | due_date    |        100  |
+--------+------------+----------------------------+--------------+-------------+-------------+

 

• 복합 인덱스에서의 카디널리티 곱 계산
  • 복합 인덱스의 성능은 각 열의 카디널리티를 곱해서 계산할 수 있다.
  • 예) board_id + due_date 인덱스
    • board_id 의 카디널리티 : 10
    • due_date 의 카디널리티 : 100
    • 카디널리티 곱 = 10 * 100 = 1000
      -> 약 1000개의 유니크 조합을 제공할 수 있다
  • 복합 인덱스 설계 팁 :
    • 첫 번째 커럼의 카디널리티가 높을수록 인덱스 효율이 좋아진다.
    • 첫 번째 컬럼이 낮은  카디널리티면 테이블 스캔이 발생할 수 있습니다.

---

EXPLAIN과 EXPLAIN ANALYZE의 차이점

 

EXPLAIN

• 쿼리 실행 전에 실행 계획을 보여준다
• 인덱스 사용 여부를 미리 예측할 수 있다.

EXPLAIN 
SELECT * FROM card 
WHERE title IN ('카드 제목 1', '카드 제목 2');

 

EXPLAIN ANALYZE

• 쿼리를 실제로 실행하고 실제 실행 시간과 루프 횟수를 제공한다.
• 인덱스 사용 여부를 명확하게 파악할 수 있다.
• 쿼리가 오래 걸리는 경우에는 최적화가 필요하다.

EXPLAIN ANALYZE 
SELECT * FROM card 
WHERE title IN ('카드 제목 1', '카드 제목 2');

 

실행 계획 예시

+-------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Index range scan on card using idx_card_title (title in ('카드 제목 1', '카드 제목 2')) (cost=1.86 rows=2) (actual time=0.02..0.03 rows=2)    |
+-------------------------------------------------------------------------------------------------------------------------------------------------+

 

Index range scan : 인덱스를 사용해 범위 스캔을 수정한다.
cost : 쿼리 실행 비용 (낮을수록 좋음)
rows : 예상되는 결과 수
actual time : 실제 실행 시간
loops : 쿼리 반복 횟수