[MySQL] 인덱스(INDEX)의 생성, 삭제, 조회, 수정

인덱스(INDEX)

인덱스는 데이터를 빠르게 찾을 수 있는 수단으로써, 테이블에 대한 조회 속도를 높여 주는 자료구조입니다.

인덱스는 다음 그림과 같이 테이블의 특정 레코드 위치를 알려 주는 용도로 사용하는데,

이러한 인덱스는 자동으로 생성되지 않습니다.

 

 

PK 컬럼은 PK를 생성할 때 자동으로 인덱스가 생성됩니다.

 

테이블에서 일련번호를 기본키(Primary Key)로 하는 경우, 일련번호에 대한 인덱스는 자동으로 생성되나, 생년월일이나 이름을 기준으로 하는 인덱스는 자동으로 생성되지 않습니다.

 

select * from table_great_men where 이름 = ‘이순신’;

 

조건문 where 절에서 '이름'을 비교하고 있습니다.

이 경우 해당 테이블의 '이름' 컬럼에 인덱스가 없는 경우, 테이블의 전체 내용을 검색(Table full scan)하게 됩니다.
반면, 인덱스가 생성되어 있다면 테이블의 일부분을 검색(Range scan)하여 데이터를 빠르게 찾을 수 있습니다.

조건절에 '='로 비교되는 컬럼을 대상으로 인덱스를 생성하면 검색 속도를 높일 수 있습니다.

하지만 자동으로 생성되지 않기 때문에 DB 사용자는 인덱스를 추가로 생성해야 합니다.

 

---

 

먼저, 인덱스를 생성하기 전에 테이블을 생성하고 데이터 값을 입력하겠습니다.

 

-- 'notice' 테이블 생성
create table notice (
  idx int auto_increment primary key,
  title varchar(100) not null,
  content text not null,
  author varchar(20) not null,
  wr_date date not null,
  view_count int default 0
);

-- 'notice' 테이블에 데이터값 입력
insert into notice(title, content, author, wr_date) values
('첫 번째 글', '글 등록하기 1111', '관리자', '2021-05-01'),
('두 번째 글', '글 등록하기 2222', '홍일동', '2021-05-10'),
('세 번째 글', '글 등록하기 3333', '홍이동', '2021-05-18'),
('네 번째 글', '글 등록하기 4444', '홍삼동', '2021-05-27');

 

---

 

1. 인덱스 생성

 

CREATE  [UNIQUE]  INDEX  인덱스명  ON  테이블명  ( 컬럼명1  [, 컬럼명2...]  [ ASC | DESC ] );

- [UNIQUE] : 인덱스 걸린 컬럼에 중복 값을 허용하지 않음 (CREATE 테이블에서 사용하는 UNIQUE 제약 조건과 동일한 의미)

 

-- 'notice' 테이블의 'author' 컬럼에 대해 중복값을 허용하는 인덱스 생성
CREATE INDEX idx_author on notice (author);

-- 'notice' 테이블의 'wr_date', 'view_count' 결합 컬럼에 대해 인덱스 생성
CREATE INDEX idx_wr_date_viewcount on notice (wr_date, view_count);

-- 'notice' 테이블의 'wr_date' 컬럼에 대해 중복값을 허용하지 않는 인덱스 생성
CREATE UNIQUE INDEX idx_wr_date on notice (wr_date);

 

 

▶ 테이블 생성시 인덱스 생성

 

[UNIQUE]  INDEX  인덱스명  ( 컬럼명1  [, 칼럼명2, ...] );

 

-- 'notice' 테이블 생성시 'author' 인덱스 생성
CREATE TABLE notice (
    idx int auto_increment,
    title varchar(100) not null,
    content text not null,
    author varchar(20) not null,
    wr_date date not null,
    view_count int default 0,
    PRIMARY KEY ('idx'),
    INDEX 'idx_author' ('author')
);

 

---

 

2. 인덱스 조회

 

SHOW INDEX FROM  테이블명;

 

-- 'notice' 테이블의 인덱스 조회
SHOW INDEX FROM notice;

 

---

 

3. 인덱스 삭제

 

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

 

-- 'notice' 테이블의 'idx_author' 인덱스 삭제
ALTER TABLE notice DROP INDEX idx_author;

-- 'notice' 테이블의 'idx_wr_date_viewcount' 인덱스 삭제
ALTER TABLE notice DROP INDEX idx_wr_date_viewcount;

-- 'notice' 테이블의 'idx_wr_date' 인덱스 삭제
ALTER TABLE notice DROP INDEX idx_wr_date;

 

---

 

3. 인덱스 변경

 

ALTER  [UNIQUE]  INDEX  인덱스명  ON  테이블명  ( 컬럼명1  [, 컬럼명2...] );

 

※ 한번 생성된 인덱스에 대해 변경이 필요한 경우는 드물고, 일부 제품은 인덱스에 대한 변경 SQL문이 없습니다.

이 경우 기존 인덱스를 삭제하고 신규 인덱스를 생성하는 방식으로 사용이 권고되고 있습니다.