[MySQL] 8장(완) - MySQL 인덱스

1. 인덱스(Index)

MySQL에서 인덱스는 데이터 검색 속도를 높이기 위한 중요한 구조로, 테이블의 특정 열에 대한 데이터 구조로, 효율적인 데이터 조회를 가능하게 합니다. 아래 MySQL 인덱스의 주요 개념과 종류 장단점, 최적화 방법에 대해 소개해보겠습니다.

---

2. 인덱스의 개념

• 인덱스는 데이터 테이블에서 특정 열 값의 위치를 효율적으로 찾기 위한 데이터구조
• 일반적으로 B-Tree 또는 해시 테이블과 같은 자료 구조를 사용하여 인덱스를 구조
• 인덱스를 사용하면 데이터 검색, 정렬, 필터링 속도를 크게 향상

---

3. 인덱스의 종류

• 단일 인덱스 (Single Index)
  • 하나의 열에 대한 인덱스
  • ex. CREATE INDEX idx_name ON users(name);
• 복합 인덱스 (Composite Index)
  • 두 개 이상의 열에 대한 인덱스
  • ex. CREATE INDEX idx_name_age ON users(name,age);
• 유니크 인덱스 (Unique Index)
  • 중복된 값을 허용하지 않는 인덱스
  • ex. CREATE UNIQUE INDEX idx_email ON users(email)
• 풀텍스트 인덱스 (Full-text Index)
  • 텍스트 검색을 위한 인덱스
  • ex. CREATE FULLTEXT INDEX idx_description ON products(description);
• 비트맵 인덱스 (Bitmap Index)
  • 주로 낮은 카디널리티 열에 사용 (예: 성별, 상태 등)
  • MySQL에서 주로  InnoDB에서 지원되지 않지만, 다른 데이터베이스 시스템에서 사용됨

---

4. 인덱스의 장단점

• 장점
  • 검색 속도 향상: 인덱스를 사용하면 데이터 검색 시간이 크게 단축
  • 정렬 성능 향상: ORDER BY 절을 사용할 때 인덱스가 도움이 됨
• 단점 
  • 저장 공간 사용: 인덱스는 추가적인 저장 공간을 차지
  • 데이터 변경 성능 저하: 데이터 삽입, 수정, 삭제 시 인덱스 업데이트로 인해 성능 저하될 수 있음

---

5. 인덱스 최적화

• 적절한 열 선택: 자주 검색되거나 조건절에 사용되는 열에 인덱스를 설정
• 복합 인덱스 활용: 여러 열에 대한 검색이 빈번하게 이루어지는 경우 복합 인덱스를 사용하여 성능 향샹
• EXPLAN 명령어 사용: 쿼리 실행 계획을 확인하여 인덱스 사용 여부 분석

EXPLAIN SELECT * FROM users WHERE name = 'Alice';

---

6. 인덱스 관리

• 인덱스 삭제:필요 없는 인덱스는 삭제하여 성능을 최적화합니다.

DROP INDEX idx_name ON users;

• 인덱스 재구성: 데이터 수정이 빈번한 테이블에서는 인덱스를 재구성하여 성능 유지

---

7. 인덱스 예제

• 단일 인덱스 (Single Index)

단일 인덱스는 하나의 열에 대한 인덱스로, 주로 자주 검색되는 열에 사용됩니다.

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
)

-- 단일 인덱스 생성
CREATE INDEX ide_name ON users(name);

• 복합 인덱스 (Composite Index)

복합 인덱스는 두 개 이상의 열에 대한 인덱스로, 여러 열의 기준으로 검색할 때 성능을 향상 

-- 2. 복합 인덱스(Composite Index)
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    status VARCHAR(50)
)

-- 복합 인덱스 생성
CREATE INDEX idx_user_date ON orders(user_id, order_date);

• 유니크 인덱스 (Unique Index)

유니크 인덱스는 중복된 값을 허용하지 않는 인덱스로, 주로 고유한 값을 가져야 하는 열에 사용됩니다.

-- 3. 유니크 인덱스 (Unique Index)
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100), 
    sku VARCHAR(50) UNIQUE
)

-- 유니크 인덱스는 테이블 생성 시 이미 정의
-- 또는 다음과 같이 추가
CREATE UNIQUE INDEX idx_sku ON products(sku); --

• 풀텍스트 인덱스 (Full-Text Index)

풀텍스트 인덱스는 텍스트 검색을 위한 인덱스로, 주로 긴 텍스트를 검색할 때 유용합니다.

CREATE TABLE articles (
   article_id INT AUTO_INCREMENT PRIMARY KEY,
   title VARCHAR(200),
   content TEXT
);

-- 풀텍스트 인덱스 생성
CREATE FULLTEXT INDEX idx_content ON articles(content);

---

8. 인덱스 사용 예제

• 단일 인덱스 사용

--  단일 인덱스 사용 
SELECT * FROM users WHERE name = "Alice"

• 복합 인덱스 사용 예

SELECT * FROM orders WHERE user_id = 1 AND order_date BETWEEN '2025-03-09' AND '2026-03-09';

• 유니크 인덱스 사용 예

-- 유니크 인덱스 사용 예
INSERT INTO products (product_name, sku) VALUES ('Apple', 'Steve Jobs');
INSERT INTO products (product_name, sku) VALUES ('Microsoft', 'Bill Gates');

INSERT INTO products (product_name, sku) VALUES ('Microsoft', 'Steve Jobs'); -- 오류 발생

• 풀텍스트 인덱스 사용 예

-- 풀텍스트 인덱스 

INSERT INTO articles (article_id, title,content) VALUES (1,'MySQL', 'MySQL is a database management system.')

SELECT * FROM articles WHERE MATCH(content) AGAINST('database'  IN NATURAL LANGUAGE MODE);

---

 

GitHub - Koras02/mysql-bloging