[mySQL] 인덱스 개념과 복합 인덱스 (1)

인덱스란 지정한 컬럼들을 기준으로 메모리 영역에 일종의 목차를 생성하는 것이다.
insert, update, delete의 성능이 좀 느려질 수 있지만 select 성능을 향상 시킬 수있다.
인덱스는 B-tree라는 구조를 사용하는데 이와 관련해서는 이 블로그를 참고 바란다.!(그림으로 잘 설명되어있음)
 

---

인덱스는 한마디로 쿼리 결과값을 빠르게 하고 싶어서 생성한 것이다.
그럼 인덱스는 어떤 칼럼으로 생성해야하는데??
 

인덱스 칼럼 생성

 
일반적으로는 카디널리티가 높은 것을 잡아야한다고 한다.
카디널리티란 해당 컬럼 데이터의 중복된 수치를 말하는데 예를 들어 학번 과 성별의 카디널리티를 비교하면 학번이 당연히 높다.
카디널리티 높은 것로 잡는 이유는 필터링이다. 최대한 많은 부분을 걸러내기 위해서이다.
 
그럼 칼럼은 하나만 지정가능해??
아니다. 여기서 복합 인덱스라는 개념이 나온다.
 
복합인덱스란 테이블의 여러 칼럼을 조합해서 구성되는 인덱스를 말한다.
여기서 중요한 것은 명시된 칼럼의 순으로 정렬된다는 점이다.
 
그럼 카디널리티가 높은 순에서 낮은 순으로 구성하는게 더 성능이 좋나??
맞다. 앞서 이야기 했듯이 카디널리티가 높은 것이 필터링을 많이 해주기 때문..!
 
그런데 무조건 카디널리티만을 기준으로 설계해야할까??
그건 아니다.  자주 사용하는 쿼리, 조인에도 인덱스가 사용되는지(사용되면 검색향상됨), 인덱스 선행칼럼이 범위기반의 쿼리로 많이 이용되는지(카디널리티가 높아도 범위가 넓으면 인덱스로 적절하지 않을수 있음) 등 을 고려해야한다.
 
음 그럼 인덱스를 많이 생성하면 되지 않아? 매필드마다?? 
안된다..! 인덱스관련 추가 저장공간이 필요하기도 하고 데이터 추가,삭제,수정될때마다 index를 모두 수정해줘야한다. 따라서 검색 성능 향상과 인덱스 추가시 발생되는 사이드이펙트??를 고려해봐야한다.
 
인덱스 순서에 대해 예를 들어보자
 

(1)
create INDEX idx_order_date_customer_id on orders(order_date, customer_id);

EXPLAIN  select * from orders force index (idx_order_date_customer_id)
where customer_id = 3905
AND order_date > '2023-11-04'
ORDER BY order_date desc
LIMIT 10;

(2)
create INDEX idx_customer_id_order_date on orders(customer_id,order_date);

EXPLAIN  select * from orders
where customer_id = 3905
AND order_date > '2023-11-04'
ORDER BY order_date desc
LIMIT 10;

 
(1) 의 경우 order_date부터 인덱스를 생성했다.

type과 key를 보면 인덱스를 사용하였다. 다만 rows와 filterd를 보면 약 30만건의 데이터를 옵티마이저가 읽고 0.1%만 유효할것이라고 한다. 매우 비효율적이다..
실행시간은 50ms정도이다.
 
(2)의 경우 customer_id부터 인덱스를 생성했다.

type과 key를 보면 인덱스를 사용하였다. 여기서도 fiilterd를 보면 100%이다..!
 
 

복합인덱스에 관해 

 
앞서 복합인덱스라는 것을 설명했다. 근데 복합인덱스를 사용한 칼럼들을 항상 사용할까?? 뿐만 아니라 복합 인덱스에는 있지만 해당 칼럼을 사용하지 않는다면 인덱스를 탈것인가??
 

create INDEX idx_customer_id_order_date on orders(customer_id,order_date);

(1) customer_id만 조회
EXPLAIN  select * from orders
where customer_id = 3905
LIMIT 10;

(2)order_date만 조회
EXPLAIN  select * from orders
where order_date > '2023-11-04'
LIMIT 10;

 
이 예제를 통해 봐보자
 
(1)

인덱스를 탄다
 
(2)

인덱슬르 타지 않는다.
 
즉 조회 쿼리 사용시 인덱스를 타고 싶다면 최소한 첫번째 인덱스 조건은 조회조건에 포함되어야한다.
만약 없으면 인덱스를 타지 않는다..!
 
 

또 다른 인덱스를 타지 않는 경우

 
인덱스 공부하다가 테스트해보면 안타는 다른 경우가 발생했었다. 이에 대해 알아보니..
 
1.  데이터가 너무 없는경우 인덱스를 타지 않는다. 또한 검색쿼리 자체가 인덱스를 타지 않는게 유리하다고 옵티마이저가 판단하면인덱스를 타지 않는다.
 
2. 함수나 연산자를 사용하는경우 인덱스를 효과적으로 사용되지 않을 수 있다.
예를 들어 "where upper(name) = 'hello'" 와 같이 함수를 이용하면 인덱스를 안탈 수 도 있다.
 
3. Like + 와일드 카드
Like '%hello' 의 경우 인덱스를 활용할 수 없다. 즉 와일드 카드가 앞에 있는 경우 인덱스를 타지 않는다.
 
4. OR 사용
where 절에 or을 사용하면 비교해야하는 row가 늘어나는 것이라서 풀 테이블 스캔이 발생할 확률이 높다
(and연산자는 조건들로 row수를 줄이는 역할을 함)
 
5. null 값 비교
 
6. 칼럼 자료형이 다른 검색을 하는 경우 
자료형이 다르면 인덱스의 키와 비교할 값의 자료형이 일치하지 않아 인덱스를 사용할 수 없다.
 
7. IN 연산자 에서 IN안에 목록 개수가 많을 경우
많은 항목을 비교해야하기 때문에 인덱스 효과가 감소할 수 있다. 
 

---

다음 블로그는 커버링 인덱스에 대해 얘기해볼것이다.
 
https://www.inflearn.com/course/mysql-%EC%84%B1%EB%8A%A5-%EC%B5%9C%EC%A0%81%ED%99%94/dashboard
https://jojoldu.tistory.com/243