혼공스 5주 차 (oﾟvﾟ)ノ

방학을 너무 즐겁게 보내고 왔더니... 모든 것을 다 잃어버린 기분입니다..

그래서 다시 한번 복습을 쭉 해볼 예정입니다. ♪(´▽｀)

---

인덱스의 개념을 파악하자

인덱스는 데이터를 빠르게 찾을 수 있도록 도와주는 도구이다.

인덱스에는 클러스터형 인덱스와 보조 인덱스가 있다.

클러스터형 인덱스는 기본 키로 지정하면 자동 생성되며 테이블에 1개만 만들 수 있다.

기본 키로 지정한 열을 기준으로 정렬된다.

보조 인덱스는 고유 키로 지정하면 자동 생성되며 여러 개를 만들 수도 있지만 자동 정렬되지는 않는다.

인덱스의 개념

인덱스의 장점과 단점

적절한 인덱스를 생성하고 인덱스를 사용하는 SQL을 만든다면 기존보다 아주 빠른 응답 속도를 얻을 수 있다.

또한 전체 시스템의 성능이 향상되는 효과를 얻게 된다!

 

하지만 이러한 인덱스에도 단점이 있는데 바로

1. 인덱스도 공간을 차지해서 데이터베이스 안에 추가적인 공간이 필요하다는 것

2. 처음에 인덱스를 만드는 데 시간이 오래 걸린다는 것

3. SELECT가 아닌 데이터의 변경 작업(INSERT, UPDATE, DELETE)이 자주 일어나면 오히려 성능이 나빠질 수도 있다.

인덱스의 종류

MySQL에서 사용되는 인덱스는 클러스터형 인덱스와 보조 인덱스로 나뉘는데

클러스터형 인덱스는 사전과 같고, 보조 인덱스는 책의 뒤에 찾아보기가 있는 일반적인 책과 같다.

 

자동으로 생성되는 인덱스

인덱스는 테이블의 열 단위에 생성되며, 하나의 열에는 하나의 인덱스를 생성할 수 있다.

member 정의 시 회원 아이디를 기본 키로 정의했다.

이렇게 기본 키로 지정하면 자동으로 mem_id 열에 클러스터형 인덱스가 생성된다.

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

SHOW INDEX FROM table1;

 

실행한 후 Key_name 부분을 보면 PRIMARY라고 쓰여 있다.

이것은 기본 키로 설정해서 '자동으로 생성된 인덱스'라는 의미이다.

이것이 바로 클러스터형 인덱스이다.

Column_name이 col1로 설정되어 있다는 것은 col1 열에 인덱스가 만들어져 있다는 말이다.

마지막으로 Non_Unique는 '고유하지 않다'라는 뜻이다. 즉, 중복이 허용되냐는 뜻이다.

Non_Unique가 0이라는 것은 False, 반대로 1은 True의 의미이다.

기본 키와 더불어 고유 키도 인덱스가 자동으로 생성된다.

고유 키로 생성되는 인덱스는 보조 인덱스이다.

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

SHOW INDEX FROM table2_new;

 

Key_name에 열 이름이 써 있는 것은 보조 인덱스라고 보면 된다.

고유 키 역시 중복값은 허용하지 않기 때문에 Non_unique가 0으로 되어 있다.

또 고유 키를 여러 개 지정할 수 있듯이 보조 인덱스도 여러 개 만들 수 있다.

 

자동으로 정렬되는 클러스터형 인덱스

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

 

mem_id 열을 기본 키로 지정했으므로 mem_id 열에 클러스터형 인덱스가 생성되어 mem_id 열을 기준으로 정렬되었다.

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

 

추가로 데이터를 입력하면 알아서 기준에 맞춰 정렬된다.

 

정렬되지 않는 보조 인덱스

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

 

mem_id 열을 고유 키로 설정하고 내용을 확인해도 데이터 순서에는 변화가 없다.

즉, 보조 인덱스를 생성해도 데이터의 순서는 변경되지 않고 별도로 인덱스를 만드는 것이다.

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

 

새로운 내용이 추가되면 본문의 제일 뒤에 추가되는 것과 동일하다.

보조 인덱스는 여러 개를 만들 수 있다.

하지만 보조 인덱스를 만들 때마다 데이터베이스의 공간을 차지하게 되고,

전반적으로 시스템에 오히려 나쁜 영향을 미치게 된다.

그러므로 꼭 필요한 열에만 적절히 보조 인덱스를 생성하는 것이 좋다.

---

인덱스의 내부 작동

클러스터형 인덱스와 보조 인덱스는 모두 내부적으로 균형 트리로 만들어진다.

균형 트리는 '자료 구조'에 나오는 범용적으로 사용되는 데이터의 구조이다.

나무를 거꾸로 표현한 자료 구조로, 트리에서 제일 상단의 뿌리를 루트, 줄기를 중간, 끝에 달린 잎을 리프라고 부른다.

인덱스의 내부 작동 원리

균형 트리의 개념

균형 트리 구조에서 데이터가 저장되는 공간을 노드라고 한다.

루트 노드는 노드의 가장 상위 노드를 말한다.

모든 출발은 루트 노드에서 시작한다.

리프 노드는 제일 마지막에 존재하는 노드를 말한다.

루트 노드와 리프 노드의 중간에 끼인 노드들은 중간 노드라 부른다.

 

노드라는 용어는 개념적인 설명에서 주로 나오는 용어이며, MySQL에서는 페이지라고 부른다.

페이지는 최소한의 저장단위로 16Kbyte 크기를 가진다.

예를 들어 데이터를 1건만 입력해도 1개 페이지가 필요하다.

데이터를 처음부터 끝까지 검색하는 것을 전체 테이블 검색(Full Table Scan)이라고 부른다.

 

균형 트리는 무조건 루트 페이지부터 검색한다.

 

균형 트리의 분할

인덱스를 구성하면 데이터 변경 작업 시 성능이 나빠진다.

특히 INSERT 작업이 일어날 때 더 느리게 입력될 수도 있다.

이유는 페이지 분할이라는 작업이 발생하기 때문이다.

페이지 분할이란 새로운 페이지를 준비해서 데이터를 나누는 작업을 말한다.

페이지 분할이 일어나면 MySQL이 느려지고, 너무 자주 일어나면 성능에 큰 영향을 준다.

인덱스의 구조

클러스터형 인덱스 구성하기

USE market_db;
DROP TABLE IF EXISTS cluster;
CREATE TABLE cluster
( mem_id	CHAR(8),
  mem_name	VARCHAR(10)
);
INSERT INTO cluster VALUES('TWC', '트와이스');
INSERT INTO cluster VALUES('BLK', '블랙핑크');
INSERT INTO cluster VALUES('WMN', '여자친구');
INSERT INTO cluster VALUES('OMY', '오마이걸');
INSERT INTO cluster VALUES('GRL', '소녀시대');
INSERT INTO cluster VALUES('ITZ', '잇지');
INSERT INTO cluster VALUES('RED', '레드벨벳');
INSERT INTO cluster VALUES('APN', '에이핑크');
INSERT INTO cluster VALUES('SPC', '우주소녀');
INSERT INTO cluster VALUES('MMU', '마마무');
SELECT * FROM cluster;

 

이러한 테이블에 클러스터형 인덱스를 구성 해보도록 하겠다. \^o^/

ALTER TABLE cluster
	ADD CONSTRAINT
	PRIMARY KEY (mem_id);

 

mem_id를 Primary Key로 지정하면 클러스터형 인덱스로 구성된다.

데이터를 확인해 보니 mem_id를 기준으로 오른차순 정렬되었다.

mem_id 열을 Primary Key로 지정했으니 클러스터형 인덱스가 생성되어서 그렇다.

실제 데이터는 다음과 같이 데이터 페이지가 정렬되고 균형 트리 형태의 인덱스가 형성된다.

 

클러스터형 인덱스는 루트 페이지와 리프 페이지로 구성되어 있다.

또한 인덱스 페이지의 리프 페이지는 데이터 그 자체이다.

 

보조 인덱스 구성하기

USE market_db;
DROP TABLE IF EXISTS second;
CREATE TABLE second
( mem_id	CHAR(8),
  mem_name	VARCHAR(10)
);

 

보조 인덱스가 생성되었는데도 입력한 것과 순서가 동일하다.

보조 인덱스는 데이터 페이지를 건들지 않는다. 그리고 별도의 장소에 인덱스 페이지를 생성했다.

데이터의 위치는 페이지 번호 +#위치로 기록되어 있다.

보조 인덱스를 구성하면 인덱스가 별도의 공간에 만들어 진다.

일반 책의 찾아보기가 책 뒤에 별도로 만들어지는 것과 같은 개념이다.

 

인덱스에서 데이터 검색하기

인덱스 검색을 통해 클러스터형 인덱스는 2페이지만 읽어서 원하는 결과를 검색했고,

보조 인덱스는 3페이지를 읽어서 원하는 결과를 검색했다.

두 인덱스 모두 검색이 빠르기는 하지만 클러스터형 인덱스가 조금 더 빠르다.

---

인덱스의 실제 사용

인덱스를 생성하기 위해서는 CREATE INDEX 문을 사용하고, 제거하기 위해서는 DROP INDEX 문을 사용한다.

CREATE [UNIQUE] INDEX 인덱스_이름
	ON 테이블_이름 (열_이름) [ASC | DESC]

인덱스 생성하는 기본 형식 작성하기

DROP INDEX 인덱스_이름 ON 테이블_이름

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

인덱스 생성과 제거 문법

인덱스 생성 문법

CREATE INDEX로 생성되는 인덱스는 보조 인덱스이다.

UNIQUE는 중복이 안 되는 고유 인덱스로 만드는 것인데, 생략하면 중복이 허용된다.

CREATE UNIQUE로 인덱스를 생성하려면 기존에 입력된 값들에 중복이 있으면 안 된다.

그리고 인덱스를 생성한 후에 입력되는 데이터와도 중복될 수 없으니 신중해야 한다. (*^_^*)

 

인덱스 제거 문법

DROP INDEX로 생성한 인덱스를 제거한다.

주의할 점은 기본 키, 고유 키로 자동 생성된 인덱스는 DROP INDEX로 제거하지 못한다는 것이다.

ALTER TABLE 문으로 기본 키나 고유 키를 제거하면 자동으로 생성된 인덱스도 제거할 수 있다.

 

만약 하나의 테이블에 클러스터형 인덱스와 보조 인덱스가 모두 있다면 인덱스를 제거할 때는

보조 인덱스부터 제거하는 것이 더 좋다. 클러스터 인덱스부터 제거하면 내부적으로 데이터가 재구성되기 때문이다.

인덱스 생성과 제거 실습

인덱스 생성 실습

 

P. 310 인덱스 생성하고 key_name이 PRIMARY로 출력된 결과 화면 캡쳐하기

SHOW TABLE STATUS LIKE 'member';

 

이 코드는 생성된 인덱스의 크기를 확인할 수 있다.

주소에는 중복을 허용하는 단순 보조 인덱스를 생성할 것이다.

여기서는 Non_unique가 1로 설정되어 있으므로 고유 보조 인덱스가 아니라는 것이다.

즉, 중복된 데이터를 허용한다.

 

이번에는 보조 인덱스가 추가되었으므로 전체 인덱스의 크기를 다시 확인해 보겠다.

그런데 이상하게도 크기가 0이 나온다.

바로 앞에서 보조 인덱스가 생성된 것을 확인했는데, 생성한 인덱스를 적용시키려면

ANALYZE TABLE 문으로 먼저 테이블을 분석/처리해줘야 한다.

 

인덱스 활용 실습

ANALYZE TABLE member;
SHOW INDEX FROM member;

 

이 코드를 통해 지금까지 만든 인덱스가 어느 열에 있는지 확인했다. ( •̀ ω •́ )✧

 

열 이름이 SELECT 다음에 나와도 인덱스를 사용하지 않았다.

 

SELECT mem_id, mem_name, addr
	FROM member
	WHERE mem_name = '에이핑크';

 

인덱스가 생성된 mem_name 값이 '에이핑크'인 행을 조회하면 결과는 나온다.

그리고 인덱스를 사용해서 결과를 얻었다.

WHERE 절에 열 이름이 들어 있어야 인덱스를 사용한 것이 된다.

 

CREATE INDEX idx_member_mem_number
	ON member (mem_number);
ANALYZE TABLE member;

SELECT mem_name, mem_number
	FROM member
	WHERE mem_number >= 7;

 

이번에는 숫자의 범위로 조회한 코드이다.

이러한 숫자의 범위로 조회하는 것도 인덱스를 사용한다.

 

인덱스를 사용하지 않을 때

인덱스가 있고 WHERE 절에 열 이름이 나와도 인덱스를 사용하지 않는 경우가 있다.

인덱스가 있어도 MySQL이 판단했을 때 효율적이지 않다면 사용하지 않을 수도 있다는 것을 기억해야 한다. o(*￣︶￣*)o

 

인덱스 제거 실습

클러스터형 인덱스와 보조 인덱스가 섞여 있을 때는 보조 인덱스를 먼저 제거하는 것이 좋다.

DROP INDEX idx_member_mem_name ON member;
DROP INDEX idx_member_addr ON member;
DROP INDEX idx_member_mem_number ON member;

 

이다음 기본 키 지정으로 자동 생성된 클러스터형 인덱스를 제거하면 된다.

Primary Key에 설정된 인덱스는 DROP INDEX 문으로 제거되지 않고 ALTER TABLE 문으로만 제거할 수 있다.

하지만 이 전에 더 먼저 외래 키 관계를 제거해야 한다.

테이블에는 여러 개의 외래 키가 있을 수도 있기 때문에 information_schema 데이터베이스의 referential_constraints 테이블을 조회하면 외래 키 이름을 알 수 있다.

SELECT table_name, constraint_name
	FROM information_schema.referential_constraints
	WHERE constraint_schema = 'market_db';
    
ALTER TABLE buy
	DROP FOREIGN KEY buy_ibfk_1;
ALTER TABLE member
	DROP PRIMARY KEY;

 

이렇게 외래 키를 찾아낸 후 외래 키를 먼저 제거하고 기본 키를 제거하면 된다!~!

인덱스를 제거한다고 데이터의 내용이 바뀌는 것은 아니다는 것도 꼭 기억해야 한다. ( *︾▽︾)

 

인덱스를 효과적으로 사용하는 방법

1. 인덱스는 열 단위에 생성된다.

2. WHERE 절에서 사용되는 열에 인덱스를 만들어야 한다.

3. WHERE 절에 사용되더라도 자주 사용해야 가치가 있다.

4. 데이터의 중복이 높은 열은 인덱스를 만들어도 별 효과가 없다.

5. 클러스터형 인덱스는 테이블당 하나만 생성할 수 있다.

6. 사용하지 않는 인덱스는 제거한다.

---

복습도 해야 하구~ 6차시두 공부해야 하구~ 너무 힘든 날이었습니닷....

6주 차는 더 힘내서 해보겠습니당! (●ˇ∀ˇ●)