Index(색인)

인덱스란, 데이터베이스에서 테이블의 열(Colunm)에 대한 검색 속도를 높이기 위해 사용되는 자료구조다.

쉽게 말해, 데이터베이스 내의 테이블에서 특정 열에 대한 검색을 빠르게 수행하기 위함이다.

인덱스를 사용하면 데이터 검색이 빨라지기 때문에, 데이터베이스 성능을 향상시킬 수 있다.

 

특징

• 인덱스는 해당 열에 대한 정렬된 목록을 만든다.
• 인덱스를 생성하면 검색 속도가 향상된다.
• 인덱스는 디스크 공간을 추가로 사용한다.
• 적절한 열에 대해서만 인덱스를 생성해야 한다.
• 인덱스를 지나치게 많이 생성하면 데이터 입력 및 삭제 성능이 저하될 수 있다. (역효과 발생 가능)
• 데이터베이스에서 새로운 데이터가 추가되거나 삭제될 때 인덱스 업데이트 작업이 수행된다.

 

칼럼에 인덱스를 거는 것에 대한 장단점

• 장점 : SELECT 속도 향상
  • 인덱스를 걸면 풀스캔이 아니라 좁은범위검색 으로 찾을 수 있다.
    • 인덱스가 유니크 인덱스라면 좁은범위검색 + 발견즉시검색종료 의 효과까지 발생한다.
• 단점 : INSERT, UPDATE, DELETE 속도 하락
  • 데이터를 추가, 수정, 삭제할 때 마다 그에 대응되는 색인(INDEX) 데이터도 추가, 수정, 삭제 되어야 하기 때문이다.

 

그렇다면 언제 색인을 걸어야 하는가?

• 시스템의 속도가 느려질 때
• SLOW QUERY(느린코드 검출)을 찾아 분석하여 인덱스를 적절하게 걸어주면 된다.
• 느린 쿼리 필터링 기술을 이용하면, 쉽게 쿼리 튜닝을 통한 속도 향상 가능

 

인덱스 종류

1. PRIMARY KEY : 주 키 인덱스
   • 유니크 + NOT NULL
2. UNIQUE KEY : 유니크 인덱스
   • 유니크
3. NORMAL KEY : 일반 인덱스
   • 유니크 없는 일반 인덱스
4. FULL TEXT : 풀 텍스트 인덱스
   • 본문 검색에 사용할 수 있는 인덱스
   • 한글을 지원하지 않는다.
     • 엘라스틱 서치가 필요한 이유

 

UNIQUE INDEX

: 중복이 없는 인덱스로, PRIMARY KEY와 유사하다.

고유한 값을 가지기 때문에 검색 시 일치한다면 추가 검색을 하지 않아 시간을 절약할 수 있다.

 

인덱스 조회

SHOW INDEX FROM 테이블명;

 

인덱스 추가

ALTER TABLE 테이블명 ADD (UNIQUE) INDEX 인덱스명;

or

CREATE TABLE `member` (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(id),
    regDate DATETIME NOT NULL,
    loginId CHAR(50) UNIQUE NOT NULL,    #유니크 인덱스 추가
    loginPw VARCHAR(100) NOT NULL,
    `name` CHAR(100) NOT NULL
);

• loginID 에 UNIQUE 설정을 통해 UNIQUE INDEX 추가

 

인덱스 삭제

ALTER TABLE 테이블명 DROP INDEX 인덱스명;

 

 

Index의 필요성

1. 더미 데이터 생성

CREATE TABLE `member` (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(id),
    regDate DATETIME NOT NULL,
    loginId CHAR(50) NOT NULL,
    loginPw VARCHAR(100) NOT NULL,
    `name` CHAR(100) NOT NULL
);

-- 해당 sql 구문에 from member 를 붙이게 되면 member의 수 만큼 결과가 나온다. 
select now(), UUID(), 'pw', 'aaa'
	from member;

-- 이렇게 insert를 하게 되면 **member의 수만큼 data가 insert** 된다. 
-- 1->2->4->8 이런식으로 데이터의 갯수가 증가한다. 더미 데이터 100만개를 만들어보자. 
insert into member(regDate, loginId, loginPw, `name`)
	select now(), UUID(), 'pw', 'aaa'
	from member;

→ index 의 필요성을 알려주기 위해서 더미 데이터를 만드는 코드이다.

• 하단의 insert SQL 문을 약 10~20번 정도 반복해서 100만 ~ 400만 개의 데이터를 생성한다.

 

2. select 쿼리 날리기

# 회원수 확인
select count(*) from member;

# 검색속도 확인
## 힌트 : SQL_NO_CACHE
-- 4백만개에서 데이터 찾는데 2초가 걸렸다... 2백만개에서는 1초 걸림  
SELECT SQL_NO_CACHE * FROM `member`
	WHERE loginId = 'user1';

• 기존의 SQL문제를 풀 때 튜플(row)이 5개 미만 이였다. 그래서 결과가 0초에 근접하게 걸렸다.
• 지금 100~400만개의 데이터 속에서 전체 데이터를 카운팅 하고 특정 user를 찾는 쿼리문을 날렸을 때 2초 정도가 걸린다.

→ 그래서 INDEX를 사용하는 것이다. 우리가 실생활에서 백과사전에서 단어를 찾을 때, 백과사전을 순차적으로 뒤지면서 원하는 단어의 뜻을 찾지 않을 것이다. ㄱ ㄴ ㄷ ㄹ 이렇게 Indexing이 되어있는 부분부터 찾을 것이다. DataBase에서도 Index가 존재한다.

 

3. Index 사용하기

# 유니크 인덱스를 loginID 칼럼에 걸기
ALTER TABLE `member` ADD UNIQUE INDEX (`loginId`); 
show index from member; -- member 테이블에 걸려있는 index 갯수 확인. 기본적으로는 1개 뿐. primary key에 index가 걸려있다.

# 검색속도 확인, loginId 가 'user1' 인 회원 검색
-- Wow... loginId에 index를 걸었더니 검색속도가 0에 수렴한다..  
SELECT SQL_NO_CACHE * FROM `member`
	WHERE loginId = 'user1';

• Index 를 등록하고 select 구문으로 조회를 해보자.
• 데이터가 5개미만 있었을 때와 비슷한 속도를 보여준다.