[DB] 어바웃 인덱스

개요

• 인덱스는 MySQL과 같은 데이터베이스 시스템에서 데이터 검색 속도를 향상시키기 위한 데이터 구조
• 인덱스는 특정 열 또는 열들의 조합에 대해 생성되며, 데이터베이스 테이블의 데이터를 보다 효율적으로 검색할 수 있도록 돕는다.
• 인덱스는 책의 색인처럼 동작하여, 필요한 정보를 빠르게 찾을 수 있도록 도와준다.

 

인덱스의 동작 원리

1. B-Tree 구조
   • MySQL의 일반적인 인덱스는 B-Tree 구조를 사용한다.
   • B-Tree는 균형 이진 탐색 트리로 데이터가 정렬된 상태로 저장되어 빠른 검색이 가능하며 검색 시 트리의 루트부터 시작하여 자식 노드로 내려가며 키 값을 비교하여 원하는 데이터를 찾는다.
2. 해시 인덱스
   • 특정 상황에서는 해시 인덱스도 사용될 수 있다.
   • 해시 인덱스는 해시 함수를 이용해 인덱스를 구성하며, 특정 키 값에 대한 빠른 조회가 가능하지만 범위 검색에는 적합하지 않다.
3. Full-Text 인덱스
   • 텍스트 검색에 특화된 인덱스로, 텍스트 필드의 내용을 검색할 때 효율적이다.
4. Spatial 인덱스
   • 공간 데이터를 위한 인덱스로, 지리적 정보의 검색을 최적화한다.

인덱스의 장단점

장점

• 검색 속도 향상
  • 인덱스를 사용하면 데이터베이스는 테이블의 모든 행을 순차적으로 검색하는 대신, 인덱스를 사용하여 필요한 데이터를 빠르게 찾을 수 있다.
• 정렬 속도 향상
  • 정렬 작업 시 인덱스를 이용하면 더욱 빠르게 정렬할 수 있다.
• 유니크 제약 조건
  • 인덱스를 이용해 중복 데이터를 방지할 수 있다.

 

단점

• 추가 저장 공간 필요
  • 인덱스를 저장하기 위해 추가적인 디스크 공간이 필요
• 데이터 수정 시 오버헤드
  • 데이터의 삽입, 수정, 삭제 시에 인덱스도 갱신해야 하므로 성능 저하가 발생할 수 있음

 

인덱스의 생성 및 관리

인덱스 생성

CREATE INDEX index_name ON table_name(column_name);

 

유니크 인덱스 생성

CREATE UNIQUE INDEX index_name ON table_name(column_name);

 

인덱스 삭제

DROP INDEX index_name ON table_name;

 

인덱스 사용 예제

-- 테이블 생성
CREATE TABLE employees (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  department VARCHAR(100),
  salary DECIMAL(10, 2)
);

-- 인덱스 생성
CREATE INDEX idx_name ON employees(name);
CREATE INDEX idx_department_salary ON employees(department, salary);

• 위 예제에서는 name열과 department, salary 열 조합에 대해 인덱스를 생성하였음
• 이 경우, name으로 검색하거나 department, salary 조합으로 검색할 때 성능이 향상됨

 

인덱스를 적절히 사용하면, 데이터베이스 성능을 크게 향상시킬 수 있지만, 남발하면 오히려 성능 저하를 초래할 수 있으므로 신중히 사용해야 한다!

 

선택도(Selectivity) 또는 기수성(Cardinality)

• 모든 인덱스 키값 가운데 유니크한 값의 수
• 인덱스 키값 가운데 중복된 값이 많아지면 많아질수록 기수성은 낮아지고, 선택도 또한 떨어진다.
• 인덱스는 선택도가 높을수록 검색 대상이 줄어들기 때문에 그만큼 빠르게 처리된다.
• 인덱스에서 유니크한 값의 개수는 인덱스나 쿼리의 효율성에 큰 영향을미치게 된다.

 

INDEX 사용불가 Case

• not 연산자
• is not null 연산자
• 칼럼을 변형하여 비교

# salary 에는 index가 있지만, * 12 때문에 컬럼 변형됨
explain
select * from employees where salary*12 >= 120000;

# 해결책 - 컬럼 변형 X, 조건식을 변형!
explain
select * from employees where salary >= (120000/12);

• 칼럼 타입이 자동 변형되는 경우

create table my_internal(
	t_no varchar(10) primary key, # PK를 varchar로
	t_name varchar(20)
);	

insert into my_internal(t_no, t_name) values(1234, 'hong'); # 자동 형변환

explain select * from my_internal where t_no = 1234; # type: All, index 사용 X, 성능 이슈 야기

explain select * from my_internal where t_no = '1234'; # type: const

ALL
풀테이블 스캔
테이블을 처음부터 끝까지 전부 읽어서 불필요한 레코드를 제거하고 반환 (가장 비효율적)

const
테이블의 레코드의 건수에 관계없이 쿼리가 프라이머리 키나 유니크 키 칼럼을 이용하는 WHERE 조건절을 가지고 있으며, 반드시 1건을 반환하는 쿼리의 처리 방식

• 복합 칼럼에서 AND, OR에 따라

#index 사용 O
explain select * from salaries where emp_no = 10001 and from_date = '1986-06-26';

# index 사용 X
explain select * from salaries where emp_no = 10001 or from_date = '1986-06-26';