인덱스 가 왜 필요할까?
데이터 양이 수십만개, 수백만개로 많아진 상태에서
데이터를 조회하면 서비스의 속도가 느려진다
인덱스가 없는 테이블에서 특정 데이터를 찾는 과정은
100만 페이지짜리의 거대한 책에서, 특정 단어 하나를 찾기위해
책의 첫페이지부터 마지막 페이지까지 한장씩 넘겨보는것과 같다
이러한 과정을 풀 테이블 스캔 (Full Table Scan) 이라고 한다
조인의 실무팁
WHERE절에 자주 사용하는 검색 조건 컬럼에는 인덱스를 생성해서 풀테이블 스캔을 방지- 대용량 데이터에 대한 풀스캔이 불가피한 통계/배치 작업이 있다면, 서비스 이용자가 적은 시간에 실행
✅ 인덱스
인덱스는 특정 컬럼 을 정렬해서 저장하고, 이를 통해
원본데이터를 빠르게 접근할 수 있는 특별한 목차이다
정렬이 되어있어야함
원본 데이터의 위치에는 실제 데이터 행의 위치(주소값, 포인터, PK값) 을 저장한다
이 값을 통해서 원본 데이터에 빠르게 접근할 수 있다
대부분의 관계형 데이터베이스는 인덱스를 구현하기 위해
이진트리를 개선한 B- 트리 또는 B+ 트리를 사용한다 이는 밸런스 트리 의 한 종류이다
✅ 인덱스 생성하기
CREATE INDEX 인덱스이름 ON 테이블이름 (컬럼1, 컬럼2, ...);
인덱스 이름은 대부분 idx_테이블명_컬럼명 과 같은 규칙으로 지은다
ex) : create index idx_items_item_name on items(item_name);
위와 같이 인덱스를 생성하고
💡 인덱스 조회 :
show index from 테이블명
show index from items;로 items 테이블과 관련된 인덱스를 모두 조회하면 아래와 같은 결과가 나온다
NON_unique : 1이면 중복을 허용하는 인덱스이며, 0이면 허용하지 않는 인덱스
Cardinality : 인덱스에 저장된 유니크 값의 개수에 대한 추정치이며, 높을수록 중복도가 낮고 성능이 좋다고 판단
ex) 만약 데이터가 남자, 여자 로만 되어있으면 Cardinality 가 2이며, 효율성이 없음
위 결과와 같이 데이터베이스는 PK FK 유니크키 를 설정하면 자동으로 인덱스를 만들어줌
💡 이유는 무엇일까? PK를 대상으로 말하면
만약 데이터가 수십만개의 데이터가 있는상태에서 하나의 데이터를 INSERT 한다고 가정하자
그러면 INSERT를 할 때 PK가 중복이 되는지 비교를 해야하기 때문이다
그래서 자주사용하는 PK나 FK 그리고 중복을 확인해야하는 유니크키는 자동으로 인덱스가 생성된다
✅ 인덱스 삭제
DROP INDEX 인덱스이름 ON 테이블이름;
ex) : DROP INDEX idx_items_item_name ON items;
데이터베이스에는 쿼리를 어떤 방식으로 최적화해서 실행할지 계획하는 기능이 있는데
옵티마이저 라고 부른다
인덱스를 만들어도, 항상 모든 SELECT 문을 실행하면 그 인덱스를 사용하는것은 아니다
데이터의 분포나, 쿼리의 형태에 따라 옵티마이저 가 판단해서 인덱스를 사용할지, 풀스캔을 할지 결정한다
또 여러개의 인덱스가 있다면, 어떤 인덱스를 사용할지도 판단한다
실제로 쿼리에 인덱스가 잘 사용되고 있는지 확인하려면 EXPLAIN 를 쿼리 앞에 붙이면 된다
ex) : EXPLAIN SELECT * FROM items WHERE item_name = '게이밍 노트북';
EXPLAIN SELECT * FROM items WHERE item_name = '게이밍 노트북'; 를 실행하면
아래와 같이 실행되는것을 확인할 수 있다
하지만 이결과는 데이터베이스가 통계 데이터를 기반으로 예측한 정보이므로 정확하지는 않음
type : 데이터베이스가 테이블을 어떻게 접근하는지 나타낸다
ALL: 풀테이블 스캔을 의미ref: 조건이나 JOIN 에서 인덱스를 사용했다는 의미range: 범위검색 (BETWEEN, >, <, >=) 에서 인덱스를 사용했다는 의미- 위 결과는 인덱스를 사용하지 않았으므로, 풀테이블 스캔을 사용한다고 예측
key : 쿼리를 실행할때 사용한 인덱스 이름이 나옴
하지만 값이 NULL 이라는 의미는 어떤 인덱스도 사용하지 않을것이라고 예측
rows : 옵티마이저가 쿼리를 처리하기 위해 탐색할 것으로 예측하는 행의 수
이미 type에서 풀스캔을 한다고 예측했으므로, 모든 데이터의 행의 수인 25개로 예측
fitered : 필터링 후 최종적으로 조회될 예측되는 행의 비율
모든 행의개수가 25개일 경우 10%이면 2.5개 정도의 행이 나올것이라고 예측한다
하지만 실제로는 1개가 나오는데 이 부분은 예측이기 때문에 정확하지 않다
Extra : 데이터를 가져온 후 추후의 작업을 의미
위 결과에서 Using where 은, 현재 인덱스가 없어서 모든 데이터를 가져온 후
조건에 맞는지 WHERE 절의 조건을 사용할것이라고 예측
데이터베이스에서 인덱스는 3가지 상황에서 사용한다
- 동등 비교 (=)
- 범위검색 (BETWEEN, >, <, >=, LIKE ) 등
- ORDER BY를 통한 정렬 작업
✅ 동등 비교
인덱스를 만든 후 EXPLAIN 로 동등 조건을 비교하면
type 이 ref 로 바뀐것을 확인할 수 있다
CREATE INDEX idx_items_item_name ON items (item_name); // 상품이름 대상의 인덱스 생성
EXPLAIN SELECT * FROM items WHERE item_name = '게이밍 노트북'; // 쿼리 실행 계획을 확인위 쿼리실행 계획을 조회하면 아래와 같은 사진이 나온다
우선
type은ref로 바뀌었으며,key는 NULL이 아닌 생성한 인덱스 명으로 바뀌었고
possible_keys는 현재 쿼리에서 사용한 모든 인덱스의 후보가 나오지만 현재 하나이므로
그것만 나오는 상태이다.rows는 인덱스를 사용한 결과 1개로 줄어든것을 볼 수 있다
filterd는 100%로 인덱스로 찾는 1개의 행의 100%이므로 1개가 조회될것을 예측한다
EXTRA는 인덱스를 사용해서 이제 데이터를 가져온 후 추후의 작업이 필요없으므로 NULL로 예측
✅ 범위 검색
인덱스를 만든 후 EXPLAIN 로 범위검색을 비교하면
type 이 range 로 바뀔 것이다
CREATE INDEX idx_items_price ON items (price); // 가격 컬럼 대상의 인덱스 생성
EXPLAIN SELECT * FROM items WHERE price BETWEEN 50000 AND 100000; // 쿼리 실행 계획을 확인위 쿼리실행 계획을 조회하면 아래와 같은 사진이 나온다
우선
type은range로 바뀌었으며,key는 NULL이 아닌 생성한 인덱스 명으로 바뀌었고
possible_keys는 현재 쿼리에서 사용한 모든 인덱스의 후보가 나오지만 현재 하나이므로
그것만 나오는 상태이다.rows는 인덱스를 사용한 결과 5개로 줄어든것을 볼 수 있다
filterd는 100%로 인덱스로 찾는 5개의 행의 100%이므로 5개가 조회될것을 예측한다
EXTRA는 인덱스를 사용해서 대략적인 범위만 찾고, 이후WHERE조건을 사용했다는 뜻
즉 인덱스로 조건을 미리 걸러낸 후WHERE을 사용했다는 것이다
💡 범위검색의 작동순서는 어떻게 되는걸까?
현재 조건이 5만이상 10만이하의 조건이므로 우선 가격이 5만이상인 조건을 찾는다
그리고 차례대로 5만원이상의 데이터를 찾다가, 10만원 초과의 데이터가 발견되면 탐색을 종료한다 가격이 정렬되어 있기 때문에 가능하다
✅ LIKE 범위 검색
우선 LIKE 의 경우 와일드카드(%) 가 앞에붙은 경우는 사용할 수 없다
'%게이밍' (X)
'게이밍%' (O)
'%게이밍%' (X)
LIKE도 범위이므로 type 이 range 로 바뀔것이다
// 위에서 이미 인덱스 생성해서 생략
EXPLAIN SELECT * FROM items WHERE item_name LIKE '게이밍%'; // 쿼리 실행 계획을 확인위 쿼리실행 계획을 조회하면 아래와 같은 사진이 나온다
여기에 대한 설명은 위에서 설명한 범위검색과 동일하다
💡 LIKE 범위 검색 또한 다음과 같이 동작한다
현재 글자가 국어사전 순으로 정렬되어 있으므로,
게이밍으로 시작하는 조건을 찾는다.
이렇게 찾다가게이밍으로 시작하지 않는 고급 가죽 지갑이 나오자마자 탐색을 종료한다
💡 그러면 와일드카드가 앞에붙은 경우의 LIKE는 어떻게 동작할까?
SELECT * FROM items WHERE item_name LIKE '%게이밍%';
풀테이블 스캔이 되는것을 확인할 수 있다. 그 이유는 국어사전에서 중간에
게이밍이라는 단어를 찾는것 자체가 쉽지 않기 때문이다. 시작글자를 알 수 없기 때문에 정렬된 인덱스가 도움이되지 않는다.
그러면 어떻게 해결해야할까?
실무에서는 데이터가 많아질수록 LIKE '%검색어%' 방식은 사용하기 어렵다
이러한 문제를 해결하려면 전문 검색 Full-Text Search 를 사용해야 한다
이 방식은 텍스트를 단어(토큰) 단위로 쪼개서 인덱싱하는 방식이다
이러한 비슷한 방식으로 ElasticSearch 가 있다
✅ 정렬
데이터베이스에서 ORDER BY 는 생각보다 많은 처리가 필요한 작업이다
-> 테이블을 다 조회한 후 정렬작업까지 하기 때문에
-> 인덱스를 사용하지 않고 ORDER BY 를 할 때, filesort 라는 정렬 작업을 한다
하지만 인덱스 를 잘 활용하면 이 filesort 작업을 생략할 수 있다
이미 정렬된 인덱스를 순서대로 읽기만 하면 되기 때문이다
WHERE 절의 조건과 ORDER BY 절의 정렬 기준이 같을 때
인덱스 하나로 검색과 정렬을 모두 해결할 수 있다
CREATE INDEX idx_items_price ON items (price); // 가격을 대상으로 인덱스 생성
EXPLAIN SELECT * FROM items WHERE price BETWEEN 50000 AND 100000
ORDER BY price; // 조건문에 대한 컬럼과 정렬 대상 기준컬럼이 같음 (price)위처럼 인덱스 를 사용해서 실행 계획을 분석해보면, filesort 작업이 없어졌을 것이다
그 이유는 위에서 만든 인덱스에서 이미 price(가격) 순서로 정렬을 했기 때문이다
그러면 이번엔 오름차순이 아닌 내림차순(DESC) 정렬을 사용하면 filesort 작업이 발생할까?
데이터베이스 옵티마이저는 인덱스를 거꾸로 읽는 역방향 스캔 을 할 수 있어서
내림차순 또한 filesort 없이 효율적인 처리가 가능하다
-- price 컬럼에 내림차순 인덱스 생성
CREATE INDEX idx_items_price_desc ON items (price DESC);✅ 옵티마이저의 선택
옵티마이저 는 인덱스를 사용하는 것이 비효율적이라고 판단하면, 인덱스가 존재하더라도
과감히 포기하고 풀 테이블 스캔 방법을 선택한다
그 이유는 인덱스를 사용하면 검색 대상의 양은 줄어들지만, 여러위치에 흩어진 데이터에 접근해야 하기 때문이다
일반적으로 전체 데이터의 20~25% 이상을 조회해야하는 쿼리는 인덱스를 통해
테이블의 각 행에 개별적으로 접근하는 것 보다, 테이블 전체를 순차적으로 스캔하는것이 더 효율적이라고 알려져 있다.
그러면 왜 흩어진 데이터를 접근할 때 시간이 오래걸릴까?
EX) 책에서 5, 25, 400 페이지를 접근할 때 5페이지를 갔다가, 다시 25페이지를 갔다가, 다시 400페이지를 가야한다. 그러면 데이터를 읽을 때 마다 물리적으로 해당 위치까지 이동해야하기 때문이다
이렇게 데이터의 위치를 찾는데 걸리는 시간(탐색 시간)이 추가되기 때문이다
위에서 데이터 파일에 접근하는데 시간이 걸린다는 점에서 문제점이 있었다
쿼리에서 필요한 결과값(컬럼) 을 포함하고 있는 인덱스를 만들면 해결이 될 것이다
✅ 커버링 인덱스 : 말 그대로 인덱스 하나로, 쿼리의 요구사항 전체를 덮는다는 의미
특정 인덱스가 SELECT , WHERE , ORDER BY , GROUP BY 절에
사용되는 모든 컬럼을 가지고 있다면, 원본 테이블에 전혀 접근하지 않고
오직 인덱스만을 읽어서 쿼리를 처리한다
예를들어 현재 items 테이블에 price 컬럼에 인덱스가 있다고 가정하자
이상태에서
EXPLAIN SELECT item_id, price, item_name FROM items WHERE price BETWEEN 50000 AND 100000;
쿼리를 실행하면 아래와 같이 나온다
여기서 Using index condition 은 WHERE 조건절을 필터링하는 데 인덱스를 효율적으로 사용했지만,
최종 데이터를 가져오기 위해서는 추가 작업이 필요하다는 뜻이다
즉 item_name 컬럼이 인덱스에 포함되어 있지 않기 때문에
item_name 컬럼을 가져오기 위해서 원본 데이터를 접근해야한다
그러면 인덱스에 다 포함되어 있는 컬럼값만 조회하면 어떤 결과가 나올까?
EXPLAIN SELECT item_id, price FROM items WHERE price BETWEEN 50000 AND 100000;
다음과 같이 Using index 는 쿼리에 필요한 모든 데이터를 오직 인덱스에서만 읽어서
처리했음을 의미한다. 옵티마이저가 인덱스만 스캔하여 price, item_id 를 얻었고
items 테이블에는 접근할 필요가 없다
결론적으로 이 실행계획은 커버링 인덱스 를 활용하여 테이블 접근을 피했고, 인덱스 내에서
WHERE 절의 조건으로 필터링을 수행한 훌륭한 쿼리이다
(WHERE 절의 조건 = price = 인덱스에 있는 컬럼)
그러면 item_name 을 포함한 쿼리는 어떻게 해야할까?
다음과 같이 복합 인덱스를 만들면된다
CREATE INDEX idx_items_price_name ON items (price, item_name);
💡 컬럼이 여러 개인 복합 인덱스에서 컬럼의 순서는 매우 중요하다. WHERE 절에서 동등 비교나 범위 검색에 사용되는 컬럼을 가장 앞에 두어야 인덱스를 효율적으로 사용할 수 있다
커버링 인덱스의 장단점
장점
- 압도적인 SELECT 성능 향상: 테이블 접근을 위한 랜덤 I/O를 제거하여 조회 성능을 극적으로 개선한다.
- 특히 COUNT 쿼리 최적화: SELECT COUNT(*) 와 같은 쿼리에서 테이블 전체가 아닌, 크기가 훨씬 작은
인덱스만 스캔하여 결과를 빠르게 반환할 수 있다.
단점
- 저장 공간 증가: 인덱스는 원본 데이터와 별도의 저장 공간을 차지한다. 인덱스에 포함되는 컬럼이 많아질수록 인덱스의 크기도 커진다.
- 쓰기 성능 저하: INSERT , UPDATE , DELETE 작업 시, 테이블 데이터뿐만 아니라 인덱스도 함께 수정해야 한다. 인덱스가 많고 복잡할수록 쓰기 작업에 대한 부하가 커진다.
커버링 인덱스 는 조회(읽기)가 빈번하고, 쓰기 작업인 적은 테이블에 유리하며
조회하는 컬럼의 개수가 적을 때 유리하다
