Index 에 대해 — 1편 (Single Index vs Composite index)
안녕하세요.
저는 SpoonRadio 에서 DBA (DataBase Administrator) 로 업무를 수행하고 있는 Ryan (김광호) 입니다.
SpoonRadio 는 6개 국가에서 서비스 하고 있는 Global service인 만큼 다양한 방법과 노력으로 서비스를 안정적으로 운영하기 위해 노력 하고 있습니다.
이러한 노력과 도전으로 약간은 다르지만 하나의 서비스를 위한 시스템이 각 국가별로 포진하고 있습니다.
그래서 블로그를 쓰기 위한 주제를 찾는데 많은 어려움이 있었고, 고민 끝에 가장 기본적인걸 써보자.. 라는 마음으로 Index 라는 주제를 설정하게 되었습니다.
Index 란 ?
Index는 Database 라는 것을 접하기 시작할 때 부터 지겹도록 듣습니다.
하지만, Index는 Optimizer 별로 그리고 DBA별로 Index를 만드는 시각과 방법이 다양 합니다.
하지만 Index를 만드는 궁극적인 목표는 아래와 같습니다.
Insert / Update / Delete 의 cost 를 trade off 하면서 가능한 적은 양의 Disk IO 를 통해 원하는 데이터를 원활하게 가져올 수 있도록 설정 하는 자료 구조
위에서 작성한것 처럼 Index는 Select 를 원활하게 하기위해 Insert / Delete / Update 의 희생을 요구 하는 것이니 만큼 정확하고 적절하게 Index 를 생성 하는 것이 가장 중요 합니다.
이제 제가 경험한 몇몇 Index에 관련된 이야기를 시작해 보겠습니다.
먼저 Index 선택의 기준에 대해 저의 생각을 정리 해보겠습니다.
- Index column은 Selectivity (선택도) 와 Cardinality (중복된 수치) 가 높아야 한다.
- Index 의 ordering 은 데이터의 형식과 사용 방식에 따라 명확해야 한다.
- Column에 Function 을 사용하여 연산하여야 하는 Column 은 피해야 한다.
- Index column 의 size 는 작아야 한다.
이러한 선택 기준을 갖고 아래와 같은 Index 종류를 살펴 보겠습니다.
Single Index
위에서 설명한 것 처럼 Index 설정 기준을 기억 하시고 아래와 같은 Table 이 있다고 가정 하겠습니다.
Create Table Spooner
(
user_id int not null constraint pk_spooner_user_id primary key
, name varchar(64)
, gender smallint
, type smallint
, status smallint
, address1 varchar (128)
, address2 varchar (128)
, latest_login timestamp);create index idx_spooner_gender on Spooner (gender);
create index idx_spooner_latest_login on Spooner (latest_login);
여기 Spooner 라는 Table 이 있습니다. 그리고 그 안엔 3개의 Index가 존재 합니다. (물론, Primary key 포함입니다..)
여기서 선택도가 가장 높은 index는 무엇일까요?
user_id > latest_login > gender 순 입니다. 만약 남/여 비율이 같다면 gender 의 선택도는 50% 입니다. 전체 Table 의 50%에 해당되는 Rows 를 읽어와야 하기 때문이죠.
[ Cardinality 계산 ]
[ Selectivity 계산 ] (앞에 변수는 위에서 계산한 cardinality 입니다.)
어떤 데이터를 어떻게 활용하느냐 에 따라 다르겠지만, Size 가 작고 ordering 이 명확하고 selectivity 가 높은건 User_id 가 가장 좋겠지만 이미 PK 네요.
Composite index
그럼 위 테이블에서 가장 흔히 생각할 수 있는 Query 를 생각해 보자면 ..
select status, user_id from Spooner where user_id = 123456;update
Spooner
set
latest_login = current_timestamp
where
user_id = 123456;select
user_id
, status
, name
, gender
, concat(address1, ' ', address2) as address
from
Spooner
where
latest_login > '2020-08-01 00:00:00'
and status = 0
and address1 like '%서울%'order by
latest_login desc
limit 10;
위 같은 쿼리는 로그인할 때, 그리고 가장 최근 접속자 정보를 가져올 때 사용하는 Query 입니다. 이러한 Query 를 사용할 때 어떤 문제점이 발생할지 고민해 보면 아래와 같습니다.
- user_id 는 PK 이기 때문에 status 값을 가져올 때 Random access 가 발생하지 않습니다.
- 하지만, update query 에서 새로운 값이 index page 에 추가 되기 때문에 page split 이 일어날 가능성이 높습니다.
- latest_login 의 index를 사용할 가능성이 높은 2번째 Query 는 status, name, gender, address1/2 를 가져오려면 Random access 가 발생 하여야 합니다.
- latest_login 의 index 는 ordering 이 ascending 으로 처리되어 있어 limit 10을 가져오기 위해선 전체의 데이터를 가져와 ordering 해야 합니다.
첫번째 Query 는 User_id (Primary key)를 사용하기 때문에 크게 문제가 없을 수 있지만 두번째 Query 는 많은 문제점을 포함할 수 있습니다.
이러한 경우 아래와 같은 Composite index 를 만들 수 있습니다.
create index idx_spooner_status on spooner (status, latest_login desc)
하지만 이게 최선일까?!
앞서 말씀 드렸듯이 Index 선정 기준은 selectivity 즉, 선택도 입니다. 하지만 status 는 0~10 까지만 있다고 가정해도 10% , latest_login 보단 선택도가 낮을 수 밖에 없습니다.
그런데 왜 status 가 맨 앞에 오는 것인가? 라는 질문이 생길 수 있습니다. 그 답은 아래와 같습니다.
Composite index는 선행 조건이 중요하다!
위에 2개의 지표가 있습니다. 같은 조건인 쿼리가 cost 는 약 2000배 차이가 나는 것을 볼 수 있습니다. 즉 Composite index 의 경우 선행 조건이 non equal 이들어올 수 있는 것에 대해 조심 하여야 합니다.
Composite index의 ordering 은 선행 컬럼에 의존된다!
위 스크린샷에서 1000,999 row 를 확인해보면 status 가 0 -> 1 로 변경 되는 시점에 latest_login 의 값이 0에서 가장 작은 값에서 1에서 가장 큰 값으로 변경 되는 것을 볼 수 있습니다.
따라서 앞의 조건이 다중이라면 ordering 은 무의미해 집니다.
1편의 결론
1편에서는 Sigle Index 와 Compoliste index에 대해 알아봤습니다.
어떤 컬럼에 Index를 활용해야 하는지와 그 Index를 어떻게 활용 해야 하는지 그리고 Composite index (복합인덱스)에 대한 순서를 어떻게 할 것인지까지 공유드렸습니다.
Index에 대해 적절한 장소에 사용하지 않을 경우 그리고 너무 많은 Index를 만들어 Table의 data size 보다 index size 가 커지게 되면 Table scan 하는 비용 보가 더 많은 비용이 발생할 수 있으니 Index 는 항상 많은 Test 후에 적용 하시길 바랍니다.
2편에서는 Unique index 와 Unique costraint 그리고 Clustered index / NonClustered index 를 설명 드릴 예정입니다.
그리고 마지막 3편에서는 Index sort , seek 과정에서 발생하는 Random Access 에 대해 설명할 예정입니다.
