week5 - 인덱스의 개념과 종류 및 작동 원리와 구조를 이해

chapter 6에서는 인덱스의 개념과 종류 및 작동 원리와 구조를 이해하고 실제로 인덱스를 만들고 사용 하는 방법을 알아본다.

인덱스 (Index)

1. 빅데이터에서 정보를 추출할 경우 많은 시간이 소요되는데 이런 문제점을 해결해주는 것이 인덱스 이다.(실무에서 필수적)
2. SELECT 를 사용해서 테이블을 조회할 때 결과를 빠르게 추출하도록 도와주는 기능. 
3. 쉽게는 책 뒤의 '색인', '찾아보기', '인덱스'로 이해 할 수 있다. 
4. 인덱스는 테이블의 열(column)단위에 생성되며 하나의 열에는 하나의 인덱스를 생성 할 수 있다. 
5. SHOW INDEX 문을 사용하면 인덱스 정보가 확인된다.
   
   1. 실행결과에서 Key_name을 확인한다.
      1. 클러스터형일경우 - PRIMARY 
      2. 보조형일 경우 - col2, col3 등으로 표시된다.
   2. 실행결과에서 Non_unique를 확인한다.
      1. 0 인 경우 False = UNIQUE값으로 고유인덱스임을 보여주고 중복값을 허용하지 않는다.
      2. 1인 경우 True = Non_unique로 중복값을 허용하고 보조인덱스라고 볼 수 있다. 

SHOW INDEX FROM table1;

 

인덱스 종류 

1. 클러스터형 인덱스(Clustered Index)
   1. 기본키로 지정하면 자동 생성되며 테이블에 1개만 만들 수 있다.  기본 키로 지정한 열을 기준으로 자동 정렬 됨
   2. 영어사전과 같은 개념 - 책의 내용이 이미 알파벳 순서대로 정려, 그래서 별도의 찾아보기가 없다. 책 자체가 찾아보기.
   3.  자동으로 생성되는 인덱스 
      1. PRIMARY KEY, 기본키로 지정
   4. 자동으로 정렬되는 클러스터형 인덱스
      1. 어떤 열을 기본키로 지정 하면 그 열을 기준으로 알파벳 혹은 별로 자동 정렬이 된다.
      2. 영어사전을 예로 든다면 단어의 알파벳순으로 자동 정렬이 된다
2. 보조 인덱스  (Secondary Index)
   1. 고유 키로 지정하면 자동 생성 되며 여러개를 만들 수 있지만 자동 정렬은 되지 않는다. 
   2. 책뒤의 색인과 같은 개념 - 새기에서 단어를 찾은 후 옆에 있는 페이지로 가는 형식
   3. 정렬되지 않는 보조 인덱스
      1. 보조키를 색인으로 비유한 것과 같이 책(테이블)을 만들고 색인(인덱스)를 만든다고 책의 순서나 내용이 바뀌지 않는 것과 같은 이유.
   4. 보조인덱스는 여러개를 만들 수 있지만 만들때마다 데이터 베이스의 공간을 차지하게 되고, 전반적으로 시스템에 오히려 나쁜 영향을 미친다. -- 꼭 필요한 열에만 적절히 보조 인덱스를 생성하는것이 좋다. 

인덱스의 장점과 단점

장점

• 기존 보다 아주 빠른 응답 속도를 얻을 수 있다. 결과적으로 전체 시스템의 성능이 향상되는 효과. 
• SELECT문으로 검색하는 속도가 매우 빨라진다.
• 그 결과 컴퓨터의 부담이 줄어들어서 결국 전체 시스템의 성능이 향상된다.

 

단점

• 인덱스도 공간을 차지해서 데이터베이스 안에 추가적인 공간이 필요 (보통, 테이블 크기의 10%정도의 추가 공간)
• 처음 인덱스를 만드는데 시간이 오래걸린다. (새로운 책의 색인을 만드는 과정)
• SELECT가 아닌 데이터의 변결 작업(INSERT, UPDATE, DELETE) 이 자주 일어나면 오히려 성능이 나빠질 수 있다. 

인덱스를 효과적으로 사용하기

1. 인덱스는 열단위에 생성된다.
   • 하나의 열에 2개 이사의 인덱스를 만들수도 있고 2개 이상의 열을 묶어서 하나의 인덱스로 만들수 있지만. 이런경우는 매우 드물기 때문에 하나의 열에 하나의 인덱스를 만드는 것이 가장 일반적
2. WHERE 절에서 사용 되는 열에 인덱스를 만들어야 한다.
   • SELECT 문을 사용할때 WHERE 절의 조건에 해당 열이 나와야 인덱스를 사용.
3. WHERE 절에 사용되더라도 자주 사용해야 가치가 있다
   • 인덱스를 생성해서 효율이 아주 좋아진다고 하더라도, 이 SELECT문이 자주 사용되지 않고 이 테이블에서는 주로 INSERT작업만 일어난다면 --> 이 인덱스때문에 INSERT의 성능을 나쁘게 한다. 
4. 데이터의 중복이 높은 열은 인덱스를 만들어도 별로 효과가 없다.
5. 클러스터형 인덱스는 테이블당 하나만 생성 할 수 있다.
   • 따라서 PRIARY KEY를 지정할때는 가장 조회가 잦은 열에 지정하는 것이 효과적
6. 사용하지 않는 인덱스는 제거한다.
   • 공간 확보 뿐만 아니라 데이터 입력 시 발생되는 부하도 많이 줄일 수 있다.

 

기본 미션

🐯 Chapter 06 p. 310 - 인덱스 생성하고 key_name이 PRIMARY로 출력된 결과화면 캡쳐하기

추가 미션

🐱 인덱스 생성, 제거하는 기본 형식 작성하기

인덱스 생성 기본 형식

-- 인덱스 생성
CREATE UNIQUE INDEX idx_member_mem_name ON member(mem_name);
SHOW INDEX FROM member;

CREATE INDEX idx_member_mem_nember ON member(mem_number);
ANALYZE TABLE member; -- 인덱스 적용

-- 전체 테이블 검색과 인덱스 검색의 차이로 순서가 다르고 결과 데이터는 동일하게 나오는 예시
-- 전체 테이블을 이용
SELECT mem_name, mem_number FROM member 
WHERE mem_number*2 >= 14;
-- 인덱스 테이블을 이용
SELECT mem_name, mem_number FROM member 
WHERE mem_number >= 14/2;

인덱스 제거 기본 형식

-- 인덱스 제거 실습
SHOW INDEX FROM member;
-- 클러스터형 인덱스와 보조형 인덱스가 섞여있을대는 ㅂ조 인덱스를 먼저 제거하는것이 좋다. 보조인덱스 제거에는 순서 없다.
-- 보조 인덱스 삭제하기
DROP INDEX idx_member_mem_name ON member;
SHOW INDEX FROM member;
-- 클러스터형 인덱스 제거하기
ALTER TABLE member
DROP PRIMARY KEY;
SHOW INDEX FROM member;

-- PRIMARY KEY가 삭제가 되지 않을때는, 기본 키와 외래키 관계가 있을때 이다. 그래서 기본키를 제거하기 전에 외래 키 관계를 제거해야한다.
-- 테이블에는 여러개의 외래키가 있으므로 확인 하기 위해서는 아래와 같이 테이블을 조회하면된다
SELECT table_name, constraint_name
FROM information_schema.referential_constraints
WHERE constraint_scema = 'market_db';

-- 외래 키 이름을 파악하면 외래키를 먼저 제거하고 기본키를 제거한다.
ALTER TABLE buy 
DROP FOREIGN_KEY buy_idfk_1;
ALTER TABLE member
DROP PRIMARY_KEY;