안녕하세요! 이번엔 인덱스(index)에 대해서 간단히 알아보는 시간을 가지겠습니다. 인덱스는 데이터 검색 및 쿼리 성능을 향상시키는 도구로써 비유를 하자면 "책의 목차"라고도 생각할 수 있겠습니다. 인덱스는 테이블의 특정 컬럼에 대해서 데이터 위치를 나타내는 구조이며 이 위치를 활용해 데이터 검색을 더 빠르게 할 수 있습니다. 😉
인덱스의 종류 🔠
1. 단일 열 인덱스
가장 기본적인 인덱스 유형으로 하나의 열에 대한 인덱스를 생성합니다. 이것은 데이터베이스에서 가장 일반적으로 사용되며, 하나의 열을 빠르게 검색할 때 효과적입니다.
- EX) 유저 데이터베이스에서 유저 이름을 검색하는 경우
users 테이블 생성
CREATE TABLE users (
user_id INT PRIMARY KEY,
username VARCHAR(50)
);
유저 이름에 대한 단일 열 인덱스 생성
CREATE INDEX idx_username ON users(username);
유저 이름으로 검색하는 쿼리
SELECT * FROM users WHERE username = 'zihwan';
2. 복합 인덱스
두 개 이상의 열을 결합하여 인덱스를 생성하는 것으로, 여러 열을 사용한 복합 검색에 유용합니다. 복합 인덱스를 사용하면 데이터베이스 성능을 향상시킬 수 있습니다.
- EX) 오더 테이블에서 특정 날짜 범위 내의 주문 검색 및 주문 상태에 따라 필터링 하는 경우
orders 테이블 생성
CREATE TABLE orders (
order_id INT PRIMARY KEY,
order_date DATE,
order_status VARCHAR(20)
);
복합 인덱스 생성 (날짜와 주문 상태)
CREATE INDEX idx_order_date_status ON orders(order_date, order_status);
특정 날짜 범위 내의 주문 중 "주문완료" 상태의 주문을 검색하는 쿼리
SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31' AND order_status = '주문완료';
3. 고유 인덱스
고유한 값만을 포함하도록 설정된 인덱스로, 주로 기본 키 제약 조건을 만족시키기 위해 사용됩니다. 중복 값을 허용하지 않는 특성을 갖습니다.
- EX) 이메일 주소가 중복되지 않도록 사용자 데이터베이스에서 고유한 이메일 주소를 저장하는 경우
테이블 생성
CREATE TABLE users (
user_id INT PRIMARY KEY,
email VARCHAR(100) UNIQUE
);
-- 이메일 주소에 대한 고유 인덱스가 자동으로 생성됩니다.
새 사용자 추가 (중복 이메일은 허용되지 않음)
INSERT INTO users (email) VALUES ('zihwan@google.com');
4. 전체 텍스트 인덱스
텍스트 검색 및 필터링에 사용되며, 전체 텍스트 검색 엔진을 통해 효율적으로 동작합니다. 주로 텍스트 데이터베이스에서 활용됩니다.
- EX) 칵테일 레시피 데이터베이스에서, 칵테일의 한글 이름과 영어 이름을 검색하기 위해 전체 텍스트 검색 기능을 추가하는 경우
테이블 생성
CREATE TABLE cocktail (
cocktail_id INT AUTO_INCREMENT PRIMARY KEY,
name_korean VARCHAR(50),
name_english VARCHAR(50),
ingredients varchar(300),
method varchar(500),
garnish varchar(100),
FULLTEXT(name_korean, name_english)
);
CREATE 단계에서 생성하지 않고 추가적으로 FULLTEXT INDEX를 추가하는 법
CREATE TABLE cocktail (
cocktail_id INT AUTO_INCREMENT PRIMARY KEY,
name_korean VARCHAR(50),
name_english VARCHAR(50)
ingredients varchar(300),
method varchar(500),
garnish varchar(100),
);
-- "name_korean" 열에 전체 텍스트 인덱스 추가
ALTER TABLE cocktail ADD FULLTEXT(name_korean);
-- "name_english" 열에도 전체 텍스트 인덱스 추가
ALTER TABLE cocktail ADD FULLTEXT(name_english);
칵테일을 키워드로 검색하는 쿼리
-- 칵테일 이름(한글)로 검색
SELECT FROM cocktail WHERE MATCH(name_korean) AGAINST('블루 하와이' IN BOOLEAN MODE);
-- 칵테일 이름(영어)로 검색
SELECT FROM cocktail WHERE MATCH(name_english) AGAINST('Blue Hawaii' IN BOOLEAN MODE);
MATCH...AGAINST 구문은 완전일치하거나 부분일치하는 결과를 반환하며 텍스트의 관련성을 고려해서 결과를 반환합니다. 또한 논리 연산자를 사용해서 쿼리를 구성할 수 있으며 검색 결과를 스코어링 하는등의 특징을 가지고 있습니다.
인덱스의 성능 📈
인덱스는 데이터 검색 속도를 향상시키지만, 그 성능은 몇 가지 요소에 따라 다를 수 있습니다.
검색 속도 향상
인덱스를 사용하면 데이터베이스는 테이블을 훑지 않고 인덱스 트리를 사용하여 레코드를 빠르게 찾을 수 있습니다. 이로 인해 데이터 검색 속도가 향상됩니다.
쓰기 작업 성능 저하
인덱스를 생성하면 데이터 쓰기 작업(INSERT, UPDATE, DELETE)의 성능이 약간 저하됩니다. 인덱스를 수정해야 하기 때문입니다.
인덱스 선택
적절한 인덱스를 선택하는 것이 중요합니다. 과도한 인덱스 생성은 저장 공간을 낭비하고 쿼리 최적화에 오히려 악영향을 미칠 수 있습니다.
인덱스는 검색 속도를 향상 시키지만 데이터베이스의 저장 공간을 차지하기에 무분별하게 사용하는 것은 불필요한 저장 공간을 낭비할 수 있으며 데이터베이스 성능에 부정적인 영향을 미칠 수 있습니다. 또한 인덱스가 너무 많으면 쿼리를 최적화 할때 어려움을 겪을 수 있습니다. 그 외에도 여러 부작용이 있기에 자주 검색되는 컬럼이나 핵심 컬럼에만 인덱스를 생성하는 것이 중요합니다. 이상으로 인덱스 간단 소개를 마치도록 하겠습니다. 감사합니다!🙂
