인덱스는 데이터베이스에서 검색 성능을 향상시키기 위해 사용하는 자료 구조입니다.
특정 컬럼을 인덱스로 지정하면 데이터베이스는 인덱스 테이블을 생성하여 인덱스 컬럼과 값, 테이블 아이디 (혹은 레코드 위치) 를 저장합니다.
인덱스의 종류
인덱스는 클러스터 인덱스, 보조 인덱스로 구분됩니다.
클러스터 인덱스
클러스터 인덱스는 테이블 당 하나만 생성되며 해당 인덱스를 기준으로 기본 정렬 됩니다.
Primary Key 를 등록하면 자동으로 클러스터 인덱스로 등록됩니다.
클러스터 인덱스는 보조 인덱스보다 성능이 좋습니다.
보조 인덱스
보조 인덱스는 테이블 내에서 여러 개를 생성할 수 있습니다.
UK, FK 를 생성하는 경우 자동으로 보조 인덱스로 등록 되며 이외에도 쿼리를 통해 보조 인덱스를 직접 생성할 수 있습니다.
CREATE INDEX <인덱스명>
ON <테이블명> ( 칼럼명1, 칼럼명2, ... );
인덱스 생성시 고려사항
인덱스는 조회 성능을 크게 향상시킬 수 있지만 사용 시 디스크 공간을 차지하고 I/O 비용을 증가시킬 수 있기 때문에 적절한 컬럼에 인덱스를 설정하는 것이 중요합니다.
또한 인덱스가 적용된 컬럼을 수정하는 경우 인덱스 재정렬이 발생하기 때문에 성능에 치명적인 영향을 끼칠 수 있습니다. 따라서 자주 수정되는 컬럼에는 인덱스를 걸지 않는것이 좋습니다.
인덱스를 추가하기 전 다음의 기준을 고려하면 인덱스 최적화를 이룰 수 있습니다.
카디널리티 수치
카디널리티 수치는 데이터의 고유한 정도를 나타내는 값 입니다. 상품 테이블에서 100개의 상품이 등록되어 있는경우 상품 코드는 유니크 값이기 때문에 카디널리티가 100이 되고 상품 카테고리는 그보다 적을 것입니다.
조회 쿼리는 일종의 필터링이기 떄문에 애초에 유니크한 값이 적은 컬럼의 경우 인덱스 추가 효율이 좋지 않을 수 밖에 없습니다.
SELECT
COUNT(DISTINCT ID),
COUNT(DISTINCT SERVICE_NAME),
COUNT(DISTINCT TIER_CODE),
COUNT(DISTINCT CHECK_DATE),
COUNT(DISTINCT COUNT)
FROM TBL_SERVICE_STATS_DAILY;선택도
선택도는 카디널리티 / 전체 카운트 의 값입니다. 0에서 1의 값이 주어지면 해당 값이 1 이 된다면 100% 유니크한 값이 됩니다. 따라서 해당 값이 클 수록 인덱스 효율이 좋습니다.
SELECT
ROUND(COUNT(DISTINCT ID) / COUNT(*), 4) AS id_selectivity,
ROUND(COUNT(DISTINCT SERVICE_NAME) / COUNT(*), 4) AS serviceName_selectivity,
ROUND(COUNT(DISTINCT TIER_CODE) / COUNT(*), 4) AS tierCode_selectivity,
ROUND(COUNT(DISTINCT CHECK_DATE) / COUNT(*), 4) AS checkDate_selectivity,
ROUND(COUNT(DISTINCT `COUNT`) / COUNT(*), 4) AS count_selectivity
FROM TBL_SERVICE_STATS_DAILY; 100개의 데이터가 등록된 테이블의 카디널리티와 선택도 수치는 다음과 같습니다.
| 컬럼 | 카디널리티 | 선택도 |
|---|---|---|
| product_id | 100 | 1 |
| category | 10 | 0.1 |
| gender | 2 | 0.02 |
일반적으로 선택도가 0.3 이하인 컬럼의 경우 단독 인덱스 추가를 하지 않는 것이 좋습니다.
인덱스를 걸었다고 해도 옵티마이저가 인덱스 조회를 하지 않을 수 있기 때문입니다.
따라서 수치만으로 본다면 product_id 에 인덱스를 거는것이 좋을 것입니다.
보조 인덱스의 종류
복합 인덱스
인덱스는 하나의 컬럼만 등록하는 단일 인덱스와 여러 컬럼을 조합하여 등록하는 복합 인덱스로 구분됩니다.
선택도가 낮은 컬럼이라도 복합 인덱스의 일부로 사용되면 쿼리 성능이 개선될 수 있습니다.
주의할 점은 복합 인덱스를 선언한 테이블을 조회하는 경우 인덱스를 등록한 컬럼 순서대로 조회하거나 첫 번째로 선언한 컬럼으로 조회해야 인덱스가 적용 된다는 것입니다.
create index idx1 on user ( name, age );
select * from user where name = 'od' and age = 30;
select * from user where name = 'od';
-- 인덱스 적용 안됨
select * from user where age = 30 and name = 'od';커버링 인덱스
커버링 인덱스 (Covering Index)는 쿼리가 인덱스만으로 모든 필요한 데이터를 조회할 수 있는 인덱스를 의미합니다.
이는 테이블을 직접 조회하지 않기 때문에 데이터베이스 성능을 크게 향상시킬 수 있습니다.
커버링 인덱스를 사용하기 위해서는 조회할 컬럼이 복합 인덱스로 등록되어야 하며 이 중 조회에 사용할 컬럼이 복합 인덱스의 첫 번째 컬럼이 되어야 합니다.
CREATE index idx1 ON orders ( status, created_at, order_id);
SELECT status, created_at, order_id
FROM orders
WHERE status = 'shipped';풀텍스트 인덱스
풀텍스트 인덱스는 문서 내의 단어들을 공백, 콤마, 하이픈 등의 기준으로 나누어 토큰화하고 각 단어를 인덱스로 등록하는 방식 입니다. 이는 강력한 검색 도구 이지만 검색시 단어의 순서를 보장하지 않기 때문에 온전한 문장 검색에 사용하기에는 한계가 있습니다.
Elasicsearch 와의 비교
엘라스틱서치는 문장 검색 및 다양한 필터링 기술을 지원합니다. 또한 비동기(요청을 받으면 즉시 처리하지 않고 백그라운드에서 처리) 로 작동하고 문서 수정이 일어나는 경우 기존 문서를 삭제 후 재등록 하기 때문에 인덱스 재정렬이 필요하지 않습니다. 이로 인해 데이터 수정이 빈번한 환경에서도 성능이 비교적 안정적일 수 있습니다.
만약 대량의 데이터를 사용하고 문장 검색이 필요하다면 엘라스틱서치가 더 유용합니다.
인덱스가 적용되지 않는 경우
인덱스를 추가 했음에도 불구하고 실행계획에 인덱스가 적용되지 않는 경우가 발생할 수 있습니다.
다음은 인덱스가 적용되지 않는 경우의 예 입니다.
잘못된 쿼리 작성으로 인한 미적용
-- 1. 복합 INDEX 순서대로 검색하지 않은 경우 (SHOP_CODE, USER_ID, SERVICE_KEY)
EXPLAIN SELECT * FROM USER_ID = '123' AND SHOP_CODE='15'
-- 2. 컬럼 타입을 잘못 쓴 경우
EXPLAIN SELECT * FROM USER_ID = 123
-- 3. NULL 조건을 사용하는 경우
EXPLAIN SELECT * FROM USER_ID IS NULL -- 사용 불가
EXPLAIN SELECT * FROM USER_ID > '' -- 사용 가능
-- 4. 부정형 사용의 경우
SELECT * FROM users WHERE ages != 31 -- 사용 불가
SELECT * FROm users WHERE ages < 31 AND ages > 31 -- 사용 가능
-- 5. LIKE 문을 사용할 때 전체 범위 설정
SELECT * FROM users WHERE first_name LIKE '%hong%' -- 사용 불가
SELECT * FROM users WHERE first_name LIKE 'hong%' -- 사용 가능인덱스 처리 범위가 넓은 경우
인덱스 처리 범위가 테이블 풀스캔 보다 넓은 경우 옵티마이저는 인덱스를 사용하지 않도록 처리합니다.
테이블 레코드가 매우 적은 경우나 인덱스 컬럼의 카디널리티 수치가 매우 낮을 경우가 이에 해당합니다.
