(DB)인덱스(index)가 뭐야? - 13

1. 인덱스란?

인덱스는 데이터베이스 테이블의 특정 열에 대한 정렬된 데이터 구조로, 검색 성능을 향상시키키 위해 사용됩니다.

데이블의 전체 데이터를 스캔하지 않고, 인덱스를 통해 빠르게 원하는 데이터를 찾을 수 있습니다.

 

책의 색인(index)과 유사

-책에서 '인터페이스'관련 내용을 찾을 때, 전체 페이지를 읽는 대신 색인을 보고 관련 페이지를 바로 찾음.

-인덱스는 테이블의 특정 열 값을 정렬된 형태로 저장해 빠른 조회를 가능하게 함.

 

주요 특징

-저장 위치: 데이터베이스 내 별도의 공간에 저장 

자동 생성 : PRMARY KEY: 자동으로 고유 인덱스 생성.

                 UNIQUE : 자동으로 고유 인덱스 생성.

2. 인덱스의 장단점

장점

- 빠른검색 : SELECT, WHERE, JOIN,ORDER BY쿼리 성능 향상.

- 효율적 데이터 처리 : 대량 데이터에서 조건에 맞는 행을 빠르게 필터링.

- 정렬 최적화 : 인덱스 열을 기준으로 이미 정렬된 상태로 유지.

 

단점

-추가 저장 공간: 인덱스는 별도의 디스크 공간을 차지.

-쓰기 성능 저하 : INSERT, UPDATE, DELETE시 인덱스도 갱신해야 하므로 속도 저하.

관리 비용: 인덱스 생성 및 유지에 시간과 리소스 소요.

 

언제 사용해야 좋을까?

- 자주 검색되는 열(예 : WHERE, JOIN 조건에 자주 사용).

-데이터 크기가 크고, 조회 빈도가 높은 테이블.

-고유한 값이 많은 열(예: 이메일, 학번)

 

언제 피해야 될까?

- 데이터가 자주 변경되는 테이블(쓰기 빈도 높음)

-고유 값이 적은 열 (예 : 성별, 상태 코드).

-테이블 크기가 작아 전체 스캔이 빠른 경우.

3. student 테이블

create table student (
id int primary key,
name varchar(50) not null,
english_score int not null,
index idx_major (major)
);

insert into student(id, name, grade, major, english_score)values
(1, '홍길동', 1, '컴퓨터공학과', 85),
(2, '김영희', 2, '경영학과', 90),
(3, '이철수', 3, '컴퓨터공학과', 78),
(4, '박민지', 1, '디자인학과', 92),
(5, '최재영', 4, '전자공학과', 88),
(6, '강영식', 2, '컴퓨터공학과', 95);

4. 인덱스 생성 및 관리

1. 테이블 생성 시 인덱스 추가

create table student(
id int primary key, -- 자동 인덱스 생성
name varchar(50) not null,
grade int not null,
major varchar(50) not null,
english_score int not null,
index idx_major(major)  --  major 열에 인덱스 생성
);

 

2. 기존 테이블에 인덱스 추가

alter table student
add index idx_grade (grade);

 - grade 열에 인덱스 생성.

 

3. 복합 인덱스 생성

여러 열을 조합한 인덱스.

create index idx_grade_major ON  student (grade, major);

 -grade와 major 조합으로 검색 시 유용.

 

4.인덱스 확인

SHOW INDEX FROM student;

 

5. 인덱스 삭제

DROP INDEX idx_grade ON student;

idx_grade 인덱스를 삭제.

5. 인덱스 실습

1.인덱스 없이 조회 

SELECT * FROM student
WHERE major = '컴퓨터공학과';

-인덱스 없으면 전체 테이블 스캔

 

2.인덱스 추가 후 조회

CREATE INDEX idx_major ON student(major);

SELECT * FROM student
WHERE major = '컴퓨터공학과';

 

-idx_major 인덱스를 사용해 빠르게 조회.

실습 코드

-- 시나리오 실습 
-- 인덱스 삭제하기 ( major ) 

drop index idx_major on student;

select * from student 
where major = '컴퓨터공학과';

alter table student 
add index idx_major (major);

-- 도전과제 (테이블 설계) 복습

 

-- drop database school;
-- create database school; 
use school;

-- 인덱스에 대해 알아 보자. 
-- 1. 기본키, 고유키 사용시 자동으로 인덱스가 생성 됨 
-- 2. 필요하다면 직접 인덱스를 설정할 수 있다. 

-- 2.1 테이블 생성시에 인덱스를 설정하는 방법 
create table student(
	id int primary key, 
    name varchar(50) not null, 
    grade int not null, 
    major varchar(50) not null, 
    english_score int not null, 
    index idx_major (major)
);

-- 2.2 기존 테이블에 인덱스를 추가하는 방법 
alter table student 
add index idx_grade (grade);

-- 2.3 복합 인덱스 생성 : 여러 열을 조합한 인덱스 
create index idx_grade_major on student (grade, major);

-- 2.4 인덱스 확인 
show index from student;

-- 2.5 인덱스 삭제 
drop index idx_grade on student;


INSERT INTO student (id, name, grade, major, english_score) VALUES
(1, '홍길동', 1, '컴퓨터공학과', 85),
(2, '김영희', 2, '경영학과', 90),
(3, '이철수', 3, '컴퓨터공학과', 78),
(4, '박민지', 1, '디자인학과', 92),
(5, '최재영', 4, '전자공학과', 88),
(6, '강영식', 2, '컴퓨터공학과', 95);

select * from student;

 

인덱스 남용 주의 :

-너무 많은 인덱스는 쓰기 성능 저하와 저장 공간 낭비.

-테이블당 5 ~10개 이하로 유지 권장.