인덱스 개념과 장단점, 클러스터형 인덱스와 보조 인덱스

인덱스는 색인 말 그대로 원하는 내용을 찾는 것을 뜻한다.(책 뒤에 찾아보기 페이지가 필요하다.)

데이터가 많은 곳에서 SELECT문을 날리기 위해서는 INDEX가 필요함.

 

장점으로는

• SELECT 문으로 검색하는 속도가 빨라짐
• 컴퓨터의 부담이 줄어들고 전체 시스템의 성능이 향상됨

단점은

• 인덱스도 공간을 차지하므로 DB 안에 추가적인 공간이 필요
• 인덱스를 만드는 데 시간이 오래 걸림
• SELECT가 아닌 데이터의 변경 작업 INSERT UPDATE, DELETE가 자주 일어나면 성능이 나빠질 수도 있음

 

인덱스의 종류에는 크게 두가지 

• 클러스터형 인덱스
• 보조 인덱스

 

 

 

CREATE TABLE을 할 때 mem_id에 PRIMARY KEY를 지정해주는 순간 영어사전 처럼 순차적으로 진행된다.

(아이디 순으로 정렬이 된다.)

 

 

코드는 아래와 같이 해준다. 영어사전처럼 정렬이 된다.

PRIMARY KEY => CLUSTER INDEX 생성

CREATE TABLE member -- 회원 테이블
( mem_id  		CHAR(8) NOT NULL PRIMARY KEY, -- 사용자 아이디(PK)
  mem_name    	VARCHAR(10) NOT NULL, -- 이름
  mem_number    INT NOT NULL,  -- 인원수
  addr	  		CHAR(2) NOT NULL, -- 지역(경기,서울,경남 식으로 2글자만입력)
  phone1		CHAR(3), -- 연락처의 국번(02, 031, 055 등)
  phone2		CHAR(8), -- 연락처의 나머지 전화번호(하이픈제외)
  height    	SMALLINT,  -- 평균 키
  debut_date	DATE  -- 데뷔 일자
);



SELECT * FROM member;

 

 

non unique 0 유일하다 / key name PRIMARY => 클러스터형 인덱스다. / PRIMARY KEY INDEX가 column 1 col1에 있다.

USE market_db;
CREATE TABLE table1  (
    col1  INT  PRIMARY KEY,
    col2  INT,
    col3  INT
);
SHOW INDEX FROM table1;

 

row가 3개가 생겼다.

col2 col3는 보조인덱스가 되는 것이다.(UNIQUE로 지정)

PRIMARY KEY 클러스터 인덱스는 TABLE에 한개 생성

UNIQUE 보조 인덱스는 여러개 생성

CREATE TABLE table2  (
    col1  INT  PRIMARY KEY,
    col2  INT  UNIQUE,
    col3  INT  UNIQUE
);

 

이번에는 PRIMARY KEY(한개 열에만 지정가능)를 지정안하고 테이블 생성후에 ALTER를 이용해서 PRIMARY KEY를 지정해주면 아래와 같이 된다.

USE market_db;
DROP TABLE IF EXISTS buy, member;
CREATE TABLE member 
( mem_id      CHAR(8) , 
  mem_name    VARCHAR(10),
  mem_number  INT ,  
  addr        CHAR(2)  
 );

INSERT INTO member VALUES('TWC', '트와이스', 9, '서울');
INSERT INTO member VALUES('BLK', '블랙핑크', 4, '경남');
INSERT INTO member VALUES('WMN', '여자친구', 6, '경기');
INSERT INTO member VALUES('OMY', '오마이걸', 7, '서울');
SELECT * FROM member;

 

ALTER TABLE member
     ADD CONSTRAINT 
     PRIMARY KEY (mem_id);
SELECT * FROM member;

 

기본키를 한 번 제거해보고 mem_name에다가 PRIMARY KEY를 써주면 가나다 순으로 mem_name이 정렬된다.

ALTER TABLE member DROP PRIMARY KEY ; -- 기본 키 제거
ALTER TABLE member 
    ADD CONSTRAINT 
    PRIMARY KEY(mem_name);
SELECT * FROM member;

 

여기다 데이터를 INSERT 하게되면 위와 같이 mem_name순으로 정렬이 된다.

INSERT INTO member VALUES('GRL', '소녀시대', 8, '서울');
SELECT * FROM member;

 

테이블을 생성 후 ALTER를 이용해 mem_id에 UNIQUE를 넣어주어 테이블을 구성하면 아래와 같다.

USE market_db;
DROP TABLE IF EXISTS member;
CREATE TABLE member 
( mem_id      CHAR(8) , 
  mem_name    VARCHAR(10),
  mem_number  INT ,  
  addr        CHAR(2)  
 );

INSERT INTO member VALUES('TWC', '트와이스', 9, '서울');
INSERT INTO member VALUES('BLK', '블랙핑크', 4, '경남');
INSERT INTO member VALUES('WMN', '여자친구', 6, '경기');
INSERT INTO member VALUES('OMY', '오마이걸', 7, '서울');
SELECT * FROM member;

ALTER TABLE member
     ADD CONSTRAINT 
     UNIQUE (mem_id);
SELECT * FROM member;

 

mem_name을 UNIQUE로 변경하고 mem_name기준으로 INSERT해주면 맨 뒤에 추가가 된다.

ALTER TABLE member
     ADD CONSTRAINT 
     UNIQUE (mem_name);
SELECT * FROM member;

INSERT INTO member VALUES('GRL', '소녀시대', 8, '서울');
SELECT * FROM member;

 

 

 

 

 

 

 

 

<출처 한빛미디어:  인덱스의 개념과 장단점, 클러스터형 인덱스와 보조 인덱스> 

https://www.youtube.com/watch?v=KZmW6VaY5BU&list=PLVsNizTWUw7GCfy5RH27cQL5MeKYnl8Pm&index=16