MariaDB | 인덱스 INDEX

1. INDEX 

• 데이터베이스 테이블의 검색 속도를 향상시키기 위한 자료구조
• 테이블의 모든 데이터를 검색하면(full scan) 시간이 오래 걸리기 때문에 데이터와 데이터의 위치(pointer)를 포함한 자료구조를 생성하여 빠르게 조회할 수 있도록 돕고 있다.
• 데이터베이스의 index는 책의 목차, 색인과 같다.
• 주로 B-Tree (Balanced Tree) 기반으로 구현됨.

출처: https://mangkyu.tistory.com/96

2. INDEX의 종류 

• Primary Index (기본 키 인덱스): PRIMARY KEY 설정된 컬럼에 자동으로 생성됨.
• Unique Index (유니크 인덱스): 중복을 허용하지 않는 인덱스.
• Secondary Index (보조 인덱스, 일반 인덱스): 보조적인 역할을 하는 일반적인 인덱스.
• Composite Index (복합 인덱스): 여러 개의 컬럼을 묶어서 만든 인덱스.
• Full-text Index (전문 검색 인덱스): 긴 텍스트를 검색할 때 사용 (FULLTEXT 인덱스).

 

3. INDEX의 장점 

• 장점 
  • 빠른 검색: WHERE 조건으로 데이터를 찾을 때, Full Table Scan을 피하고 원하는 데이터를 빠르게 검색 가능 
  • 빠른 정렬 및 그룹핑: ORDER BY, GROUP BY 같은 연산을 할 때도 인덱스를 활용하면 속도가 빨라진다. 
  • 조인(Join) 성능 향상: 테이블 간 조인을 할 때, 인덱스가 있는 컬럼을 기준으로 하면 탐색 속도가 훨씬 빨라짐.
  • 중복 방지 (Unique Index) : UNIQUE INDEX를 사용하면 중복된 값이 들어가는 것을 방지할 수 있음.
• INDEX를 쓰면 좋은 경우
  • 검색이 자주 일어나는 컬럼 (WHERE 조건에 자주 사용되는 컬럼)
  • JOIN에 자주 사용되는 컬럼
  • ORDER BY, GROUP BY 연산이 많은 경우
  • 데이터 양이 많은 테이블 (소량 데이터는 인덱스 효과가 크지 않음)

 

4. INDEX의 단점 

• 단점 
  • INSERT, UPDATE, DELETE 성능 저하: 인덱스를 생성하면 검색은 빨라지지만, 데이터를 추가/수정/삭제할 때 오버헤드가 발생, 자주 변경되는 컬럼에 인덱스를 걸면 비효율적.
  • 디스크 공간 추가 사용, 인덱스도 저장해야 하니까 추가적인 스토리지가 필요함.
  • 과도한 인덱스 사용은 비효율적, 너무 많은 인덱스를 만들면 오히려 DB 성능이 저하될 수 있음.
• INDEX가 불필요한 경우
  • 자주 변경(INSERT, UPDATE, DELETE)되는 컬럼: 성능 저하됨
  • 데이터 개수가 적은 테이블: 데이터가 몇백 개인 경우 Full Table Scan이 더 빠를 수도 있음.
  • 중복 값이 많은 컬럼: 인덱스는 고유한 값이 많을수록(선택도가 높을수록) 효과적, 성별(gender), 국가코드(country_code) 같은 중복이 많은 컬럼은 인덱스 효율이 낮음.
  • 자주 검색되지 않는 컬럼: 공간만 낭비됨 

 

 

5. INDEX 예제 

5-1. INDEX 생성, 삭제 

-- 일반 인덱스 생성 : users 테이블의 name이라는 컬럼에 idx_name이라는 인덱스 생성
CREATE INDEX idx_name ON users(name);

-- 복합 인덱스 생성 (first_name + last_name)
CREATE INDEX idx_fullname ON users(first_name, last_name);

-- 인덱스 삭제 
DROP INDEX idx_name ON users;

 

5-2. INDEX 활용

-- 1. 조회 
-- 테이블에 인덱스가 설정 된 컬럼을 활용해 조회를 진행
SELECT * FROM phone WHERE phone_name = 'iPhone14pro';

-- EXPLAIN 명령어로 쿼리 실행 계획 확인(인덱스 사용 여부 및 사용 컬럼 확인)
EXPLAIN SELECT * FROM phone WHERE phone_name = 'iPhone14pro';

-- 2. 인덱스 재구성
-- 인덱스 지우기 -> 추가 (MySQL)
ALTER TABLE phone DROP INDEX idx_name;
ALTER TABLE phone ADD INDEX idx_name(phone_name);

-- 3. 인덱스 최적화 명령어 (MySQL) 
-- 테이블을 정리하고 디스크 공간을 회수하는 명령어.
-- DELETE 후 빈 공간을 최적화할 때 유용함
OPTIMIZE TABLE phone;