[DB] 인덱스

인덱스란

https://soobysu.tistory.com/115

[DB] Index 테이블 인덱스 설정 feat.JPA

인덱스 생성 고려사항 

• 인덱스는 WHERE 절에 자주 사용되는 속성이어야 한다.
•  인덱스는 JOIN에 자주 사용되는 속성이어야 한다. 
• 단일 테이블에 인덱스가 많으면 속도가 느려질 수 있다. 
• 속성이 가공되는 경우 사용하지 않는다. 속성의 선택도가 낮을 때 유리하다. 

즉, 검색 조건에 자주 사용되고 카디널리티가 높은 컬럼을 인덱스로 설정하면 좋다.

*카디널리티란 

특정 데이터 집합의 유니크(Unique)한 값의 개수이다. 

중복도가 ‘낮으면’ 카디널리티가 ‘높다’고 표현한다 - 주민등록번호 / 주소  

반대로 중복도가 ‘높으면’ 카디널리티가 ‘낮다’고 표현한다 - 이름 / 취미

---

커버링 인덱스란

데이터베이스 쿼리에서 필요한 모든 데이터가 인덱스에 포함되어 있어서, 인덱스만으로 쿼리 결과를 가져올 수 있는 경우를 말한다.

즉, 쿼리를 실행할 때 실제 테이블에서 데이터를 조회하지 않고, 인덱스만을 사용하여 결과를 얻을 수 있다.

그렇기 위해서는 필요한 데이터를 인덱스로 설정해 두는것이 조회성능에 큰 영향을 줄 수 있다.

그렇다면 모든 컬럼을 인덱스로 설정 하면 좋은거 아닌가요 ?

인덱스는 별도의 데이터 공간을 따로 만든다 - 데이터 사용량 증가 
만약 인덱스로 설정한 컬럼의 수정 / 삭제가 일어나면,
인덱스도 업데이트가 일어나기 때문에 추가적인 오버헤드가 발생되어, 꼭 사용할 컬럼을 인덱스화 해주는 것이 좋다.

 

그래서 커버링 인덱스 사용하는지 어떻게 알 수 있는데 ?

해당 쿼리에서 커버링 인덱스가 사용 되었는지 확인 하기 위해서는 쿼리 앞에 EXPLAIN 키워드를 사용해주면 된다.

EXPLAIN SELECT name FROM employees WHERE department = 'Sales';

 

id | select_type | table     | type  | possible_keys      | key                  | key_len | ref  | rows | Extra
---+-------------+-----------+-------+--------------------+----------------------+---------+------+------|--------------------------------
1  | SIMPLE      | employees | index | idx_department_name | idx_department_name | 103     | NULL | 2    | Using where; Using index

위와 같이 Extra 에  Using index 라는 값이 나오는데 이것은 커버링 인덱스를 사용 했다는 것이다.

이처럼 데이터 추출을 인덱스에서만 수행하는 것을 커버링 인덱스라고 한다.

Extra의 속성은 아래와 같이 있다

1. Using index:

• 의미: 쿼리가 인덱스만 사용하여 결과를 검색하는 경우입니다. 인덱스만으로 쿼리를 처리할 수 있는 경우, 실제 테이블 데이터에 접근하지 않습니다. 이는 커버링 인덱스가 사용되고 있음을 나타냅니다.

2. Using where:

• 의미: 쿼리에서 WHERE 절을 사용하여 결과를 필터링하는 경우입니다. 인덱스는 사용되지만, 조건에 맞는 데이터를 찾기 위해 추가적인 필터링이 필요합니다. 즉, 인덱스를 사용한 후 필터링을 위해 테이블의 실제 데이터를 읽어야 할 수 있습니다.

3. Using index condition:

• 의미: 인덱스의 조건만으로 필터링을 수행할 수 있는 경우입니다. 인덱스의 조건을 사용하여 필터링을 수행하며, 인덱스를 통해 조건을 충족하는 데이터를 찾습니다. Using index와 유사하지만, 보다 구체적인 조건을 통해 데이터를 검색합니다.

4. Using filesort:

• 의미: 정렬 작업이 인덱스만으로는 수행되지 않고, 별도의 정렬 작업이 필요할 때 표시됩니다. 데이터베이스는 데이터를 메모리나 디스크에 임시로 저장하고 정렬 후 결과를 반환합니다.

5. Using temporary:

• 의미: 쿼리를 실행하기 위해 임시 테이블을 사용하는 경우입니다. GROUP BY나 ORDER BY와 같은 연산을 수행하기 위해 임시 테이블이 필요할 때 발생합니다.

6. Using join buffer:

• 의미: 조인 작업을 수행하기 위해 조인 버퍼를 사용하는 경우입니다. 조인 버퍼는 메모리에서 조인을 수행하는데 사용됩니다.

7. Using index for group-by:

• 의미: GROUP BY 연산을 수행할 때 인덱스를 사용하여 결과를 그룹화하는 경우입니다. 인덱스를 이용해 그룹화를 보다 효율적으로 수행할 수 있습니다.

8. Using distinct:

• 의미: 쿼리에서 중복된 결과를 제거하기 위해 DISTINCT를 사용하는 경우입니다. 데이터베이스가 중복 제거 작업을 수행합니다.

9. Range checked for each record (index map: ...):

• 의미: 범위 검색이 수행되며, 각 레코드에 대해 인덱스가 확인되는 경우입니다. 인덱스 범위 검색을 통해 조건을 만족하는 데이터를 찾습니다.

Extra가 어떤 정보를 주는 지 알아 보려면 DB(MySQL)의 쿼리 실행 구조를 알아야 한다. 

MySQL(MariaDB)은 내부적으로 MySQL (MariaDB) 엔진과 스토리지 엔진 (InnoDB/XtraDB) 로 나눠져 있다.

스토리지 엔진이 넘겨 준 데이터 (인덱스를 사용해 걸러진 데이터) 중에서 MySQL (MariaDB) 엔진이 한번 더 걸러야되는 조건 

(필터링 혹은 체크 조건 - where, group by) 이 있다면 Extra(실행 계획)에 작업 정보가 나타난다.

즉 Extra는 쿼리 실행 과정에서 데이터베이스 엔진이 수행하는 추가 작업에 대한 정보를 제공하는 것이다.
이를 통해 데이터베이스가 필터링, 정렬, 그룹화 등의 작업을 어떻게 처리하는지 확인할 수 있다.

구현 코드

QueryDSL

QueryDSL 은 from 절에 서브쿼리를 지원하지 않는다.

커버링 인덱스를 사용해야 한다면 2개의 쿼리로 분리해서 진행할 수 밖에 없다.

- 커버링 인덱스를 활용해 조회 대상의 PK를 조회

- 해당 PK로 필요한 컬럼 항목들 조회

pubblic List<BookPaginationDto> paginationCoveringIndex(String name, int pageNo, int pageSize) {

    // 1. 커버링 인덱스로 대상 조회 (id만 포함하여 커버링 인덱스를 활용해 빠르게 조회)
    List<Long> ids = queryFactory
                        .select(book.id)
                        .from(book)
                        .where(book.name.like(name + "%"))
                        .orderBy(book.id.desc())
                        .limit(pageSize)
                        .offset(pageNo * pageSize)
                        .fetch();
    
    // 1-1. 대상이 없을 경우 추가 쿼리 실행 방지
    if (CollectionUtils.isEmpty(ids)) {
        return new ArrayList<>();
    }
    
    // 2. 
    return queryFactory
             .select(Projections.fields(BookPaginationDto.class,
                     book.id.as("bookId"),
                     book.name,
                     book.bookNo,
                     book.bookType))
             .from(book)
             .where(book.id.in(ids))
             .orderBy(book.id.desc()) // where in id 만 있어 결과 정렬이 보장되지 않는다.
             .fetch();
}

Jdbc Template

public List<BookPaginationDto> paginationCoveringIndexSql(String name, int pageNo, int pageSize) {
String query =
        "SELECT i.id, book_no, book_type, name " +
        "FROM book as i " +
        "JOIN (SELECT id " +
        "       FROM book " +
        "       WHERE name LIKE '?%' " +
        "       ORDER BY id DESC " +
        "       LIMIT ? " +
        "       OFFSET ?) as temp on temp.id = i.id";

return jdbcTemplate
        .query(query, new BeanPropertyRowMapper<>(BookPaginationDto.class),
                name,
                pageSize,
                pageNo * pageSize);
}

단점

커버링 인덱스 방식은 일반적인 페이징 방식에서는 거의 대부분 적용할 수 있는 효과적인 개선 방법인 것을 확인하였는데요.
No Offset 방식처럼 UX의 변경 등이 필요하지도 않다보니 단점이 없어보이지만 실제로는 몇개의 단점이 있습니다.

• 너무 많은 인덱스가 필요하다
  • 결국 쿼리의 모든 항목이 인덱스에 포함되어야하기 때문에 느린 쿼리가 발생할때마다 인덱스가 신규 생성될 수도 있다.
• 인덱스 크기가 너무 커진다
  • 인덱스도 결국 데이터이기 때문에 너무 많은 항목이 들어가면 성능 상 이슈가 발생할 수 밖에 없는데, where절에 필요한 컬럼외에도 order by, group by, having 등에 들어가는 컬럼들까지 인덱스에 들어가게 되면 인덱스 크기가 너무 비대해진다.
• 데이터 양이 많아지고, 페이지 번호가 뒤로 갈수록 NoOffset에 비해 느리다.
  • 시작 지점을 PK로 지정하고 조회하는 NoOffset 방식에 비해서 성능 차이가 있음 (NoOffset과 동일한 데이터 조회시 커버링 인덱스 방식은 272ms, No Offset은 83ms)
  • 테이블 사이즈가 계속 커지면 No Offset 방식에 비해서는 성능 차이가 발생

 

---

참고 

https://jojoldu.tistory.com/529

2. 페이징 성능 개선하기 - 커버링 인덱스 사용하기