[Database] 인덱싱(Indexing)의 개념 및 특징, 실무 적용기

개념 및 특징

✅ 인덱싱이란?

데이터베이스의 테이블에 대한 검색 속도를 높이는 구조이다. 인덱싱은 책의 목차와 같아서, 특정 컬럼을 기준으로 원하는 데이터를 빠르게 검색할 수 있도록 도와준다.
참고로 인덱스는 B-트리(B-Tree) 또는 해시(Hash)와 같은 자료구조를 사용하여 구현된다.

데이터 조회시 인덱스가 없으면 전체 테이블을 스캔하여 일치하는 레코드를 찾아야 한다.

그러나 인덱스가 존재하면 인덱스를 통해 Id를 알고 Id를 통해 데이터 주소로 가서 데이터를 조회할 수 있기 때문에 불필요한 엑세스를 줄일 수 있다. 인덱스를 기준으로 조회할 때 성능을 개선시키는 것이므로 당연히 인덱스는 WHERE절에서 그 효과가 있다.

하지만 인덱스가 무조건 있다고 해서 좋은 것은 아니며, 추가적인 DB 공간이 필요하고 CREATE, UPDATE, INSERT, DELETE와 같은 데이터 변경 작업시 오히려 성능 저하를 불러일으킬 수 있기 때문에 꼭 필요한 컬럼에 인덱스를 생성해야한다.

데이터 변경 작업시 인덱스가 성능 저하를 야기하는 이유는 DBMS가 원하는 값을 빠르게 탐색하기 위해 인덱스를 항상 최신 정렬된 상태로 유지해야 한다는 점과 관련이 있다.

인덱스가 적용된 컬럼에 INSERT, UPDATE, DELETE 가 수행되면 각각 다음의 연산을 추가적으로 수행해야 하며 그에 따른 오버헤드가 발생하기 때문이다.

INSERT : 새로운 데이터에 대한 인덱스 추가
UPDATE : 기존의 인덱스를 '사용하지 않음' 처리하고 갱신된 데이터에 대한 인덱스 추가
DELETE : 삭제하는 것이 아니라 삭제하는 데이터의 인덱스를 '사용하지 않음' 처리

또한 인덱스는 어떤 컬럼으로 어떻게 구성하느냐에 따라 성능의 차이는 천차만별이다. 그렇다면 어떤 기준을 가지고 인덱스를 적용해야 할까?

✅ 인덱스 대상 컬럼을 선택하는 기준

카디널리티 (Cardinality) : 컬럼에 사용되는 값의 다양성 정도, 즉 중복 수치를 나타내는 지표.
선택도 (Selectivity) : 특정 컬럼에서 같은 값을 얼마나 잘 선택할 수 있는지에 대한 지표. 특정 필드값을 지정했을 때 선택되는 레코드 수를 테이블 전체의 레코드 수로 나눈 비율이다. 선택도는 다음과 같이 계산할 수 있다.
= 컬럼의 특정 값의 row 수 / 테이블의 총 row 수 * 100
= 컬럼의 값들의 평균 row 수 / 테이블의 총 row 수 * 100
활용도 : 활용도는 해당 컬럼이 실제 작업에서 얼마나 활용되는지에 대한 값. WHERE 절에서 얼마나 자주 활용되는지를 보면 된다.
중복도 : 중복되는 인덱스가 있는지에 대한 값. 서로 다른 작업자의 잘못된 요청으로 같은 컬럼에 대해 인덱스가 중복으로 생성된 경우가 이에 해당한다.

카디널리티가 높을수록(= 한 칼럼이 갖고 있는 값의 중복 정도가 낮을수록),

선택도가 낮을수록,

활용도가 높을수록,

중복도가 없을수록 인덱스 설정에 좋은 컬럼이다.

기본적으로 인덱스 컬럼을 효과적으로 설정할 때 고려해야 할 특징들은 위와 같다.

그러나 실제로는 데이터의 특성과 인덱스 타입 등의 요소를 추가적으로 고려해야 하기 때문에 이러한 요소들이 절대적인 판단 기준이 되지는 않는다.

또한 인덱스는 테이블마다 하나 혹은 여러 개의 컬럼에 대해 설정할 수 있다. 즉, 단일 인덱스를 여러 개 생성할 수도 있고, 여러 컬럼을 묶어 복합 인덱스에 대해 설정할 수 있다.

그리고 복합인덱스 구성시 같은 컬럼을 사용하더라도 순서를 어떻게 구성하느냐에 따라서 완전히 다른 결과를 가져온다.

이와 같이 여러 요인이 존재하므로 나는 인덱스 대상 컬럼을 바꿔가며, 단일 인덱스 혹은 중복 인덱스로 인덱스 구성을 변경해가며 직접 속도를 측정해보았다.

실무 적용기

회사에서 회원 사용 내역을 DB에 저장하고 있었는데, 그 양이 방대해서 리스트를 띄우는 시간이 8초나 걸렸었다.

결과적으로 복합 인덱싱 처리하여 2초까지 속도를 개선할 수 있었다.

문제의 쿼리는 다음과 같다.

<!-- 회원 사용 내역 리스트 -->
<select id="findTrackingMemberUsedUrlList" parameterType="TrackingMemberUsedVO" resultType="TrackingMemberUsedVO">
    SELECT  request_url,
            COUNT(*) AS requestCnt
    FROM    tracking_member_used tmu
            LEFT OUTER JOIN member m ON m.member_seq = tmu.member_seq
    WHERE   1=1
            <if test="(startDate != null and startDate != '') and (endDate != null and endDate != '')">
            AND DATE_FORMAT(tmu.reg_date, '%Y%m%d') BETWEEN REPLACE(#{startDate},'-','') AND REPLACE(#{endDate},'-','')
            </if>
            <if test="memberTypeCd != null and memberTypeCd != '' and memberTypeCd == 'RUSER'">
            AND m.member_type_cd = #{memberTypeCd}
            </if>
            <if test="memberTypeCd != null and memberTypeCd != '' and memberTypeCd == 'IUSER'">
            AND (
                m.member_type_cd = 'IOP'
                OR
                m.member_type_cd = 'IUSER'
                )
            </if>
            <if test="testAccountYn != null and testAccountYn != ''">
            AND m.test_account_yn = #{testAccountYn}
            </if>
            <if test="searchValue != null and searchValue != ''">
                <if test="searchKey == 'all'">
                AND (
                    REGEXP_REPLACE(m.company_nm, '[ \t\r\n]', '') LIKE REGEXP_REPLACE(CONCAT('%',#{searchValue},'%'), '[ \t\r\n]', '')
                    OR
                    REGEXP_REPLACE(m.member_id, '[ \t\r\n]', '') LIKE REGEXP_REPLACE(CONCAT('%',#{searchValue},'%'), '[ \t\r\n]', '')
                    OR
                    REGEXP_REPLACE(m.member_nm, '[ \t\r\n]', '') LIKE REGEXP_REPLACE(CONCAT('%',#{searchValue},'%'), '[ \t\r\n]', '')
                    OR
                    REGEXP_REPLACE(tmu.request_url, '[ \t\r\n]', '') LIKE REGEXP_REPLACE(CONCAT('%',#{searchValue},'%'), '[ \t\r\n]', '')
                )
                </if>
                <if test="searchKey == 'companyNm'">
                AND REGEXP_REPLACE(m.company_nm, '[ \t\r\n]', '') LIKE REGEXP_REPLACE(CONCAT('%',#{searchValue},'%'), '[ \t\r\n]', '')
                </if>
                <if test="searchKey == 'memberId'">
                AND REGEXP_REPLACE(m.member_id, '[ \t\r\n]', '') LIKE REGEXP_REPLACE(CONCAT('%',#{searchValue},'%'), '[ \t\r\n]', '')
                </if>
                <if test="searchKey == 'memberNm'">
                AND REGEXP_REPLACE(m.member_nm, '[ \t\r\n]', '') LIKE REGEXP_REPLACE(CONCAT('%',#{searchValue},'%'), '[ \t\r\n]', '')
                </if>
                <if test="searchKey == 'requestUrl'">
                AND REGEXP_REPLACE(tmu.request_url, '[ \t\r\n]', '') LIKE REGEXP_REPLACE(CONCAT('%',#{searchValue},'%'), '[ \t\r\n]', '')
                </if>
            </if>
    GROUP BY
            request_url
    ORDER BY
            request_url ASC
</select>

✅ 해당 쿼리에서 인덱스 대상 컬럼을 선택한 기준

인덱스는 데이터 검색을 빠르게 하기 위해 데이터를 정렬된 형태로 유지한다.

그렇기 때문에 WHERE 절에서 조건으로 사용하는 컬럼에 인덱스를 추가하면 해당 조건을 만족하는 데이터를 더 빠르게 찾을 수 있다.
또한 GROUP BY 절에서 사용되는 컬럼을 기준으로 인덱스가 있으면 그룹화 할 때 더 빠르게 그룹을 생성할 수 있다.
조인 시 기준이 되는 컬럼이 인덱스로 존재하면 검색 속도가 향상된다.

따라서 나는 WHERE, GROUP BY, ORDER BY 절에서 모두 사용되는 컬럼인 request_url을 인덱스로 추가하기로 했다.

또한 member_seq는 member 테이블과 조인하는 데 사용되므로, request_url과 함께 인덱스를 구성하면 조인 작업도 빠르게 처리될 것이라고 추측했다. 추측을 토대로 3가지 인덱스를 구성하여 테스트 해보았고 각각의 인덱싱 처리 후 결과는 다음과 같다.

단일 인덱싱 적용 후 ⇒ 3초

ALTER TABLE tracking_member_used ADD INDEX idx_requesturl (request_url);

member_seq, request_url 의 순서로 복합 인덱싱 적용 후 ⇒ 4초

ALTER TABLE tracking_member_used ADD INDEX idx_requestUrl_memberSeq (member_seq, request_url);

request_url, member_seq 의 순서로 복합 인덱싱 적용 후 ⇒ 2초

ALTER TABLE tracking_member_used ADD INDEX idx_requestUrl_memberSeq (request_url, member_seq);

🧩결론 : where조건절에서 request_url과 member_seq컬럼으로 탐색하고, request_url 컬럼으로 group by 하고 있으므로 복합 인덱싱으로 처리하는 게 가장 빠르다.

request_url, member_seq로 이루어진 복합 인덱스로 구성하여 GROUP BY, ORDER BY, 조인 작업을 모두 최적화했다.

✅ 인덱스의 컬럼 순서에 따라 결과가 달라진 이유

복합 인덱스 생성 시 같은 컬럼 구성임에도 컬럼 순서에 따라 결과도 달라졌는데, 아래의 인덱스 정보를 통해 그 이유를 알 수 있었다.

다음은 DBeaver에서 인덱스 정보를 보여주는 SHOW INDEX 명령어 결과를 캡처한 화면이다.

member_seq를 첫 번째, request_url을 두 번째로 구성한 경우

request_url을 첫 번째, member_seq를 두 번째로 구성한 경우

⇒ request_url이 첫 번째 순서로 배치된 것이 카디널리티가 4,969로 더 높으므로(=중복도가 낮으므로), 인덱스를 사용할 때 더 많은 고유 값을 가지게 되어 검색이 더 빨라진 것으로 보인다.

[각 요소에 대한 설명]

Table	인덱스가 적용된 테이블 이름
Non_unique	0이면 단일 인덱스, 1이면 중복 인덱스
Key_name	인덱스 이름
Seq_in_index	인덱스 내의 열 순서. 인덱스가 여러 열로 구성된 경우, 열의 순서
Column_name	인덱스가 적용된 열 이름.
Collation	인덱스에서 열 값이 정렬되는 방식. 'A'는 오름차순 정렬을 의미
Cardinality	인덱스가 나타내는 고유한 값의 추정 개수. 이 값은 인덱스의 통계 정보에서 나온 값이며, 인덱스가 얼마나 선택적인지(유니크한지)를 표현
Sub_part	부분 인덱스의 길이. NULL이면 전체 열이 인덱스에 포함된다는 의미
Packed	인덱스가 압축되었는지 여부. NULL이면 압축되지 않았음을 의미
Null	인덱스 열이 NULL 값을 가질 수 있는지 여부
Index_type	인덱스의 유형. 여기서는 모두 BTREE 타입

zisooya