안녕하세요, 혼자 공부하는 SQL을 보고 학습한 자료를 남깁니다.
인덱스의 개념
책의 찾아보기라고 보면 쉽다.
UNIQUE라는 단어를 찾아본다고 보자.
찾아보기에 없다면 책 전체를 다 넘겨야만 이 글자를 찾을 수가 있다.
엄청난게 오랜 시간이 걸리게 된다.
하지만 찾아보기에 있다면 'U'자를 찾아서 75페이지에서 금방 찾을 수가 있다.
찾아보기가 없을 때 찾는 것과 찾아보기가 있을 때 찾는 것은 차이가 크게 나면 수백배 차이가 난다.
인덱스의 필요성
인덱스는 반드시 필요한 것은 아니다.
하지만 현실적으로 실무에서는 INDEX가 없이는 사용하기가 굉장히 어렵다.
실제로 데이터가 엄청나게 많기 때문이다.
실무에서는 INDEX를 반드시 사용한다고 보면 된다.
실습에서는 데이터가 워낙 작기 때문에 INDEX가 없어도 괜찮은 것이다.
실제로는 INDEX가 있어야만 찾아보기가 가능하다.
찾는 기능은 바로 SELECT문이다.
SELECT를 빨리 하기 위해서는 찾아보기가 필요하다.
INDEX를 통해서 SELECT문을 하는 것이 현실적인 실무에서의 상황이다.
인덱스의 문제점
하지만 IDNEX의 이해 없이 무리하게 SELECT문을 날리면
시스템에 문제가 생기거나, 더 느려지는 경우가 있다.
그래서 정확히 이해를 해야한다.
만일 INDEX를 전부 만든다면
찾아보기가 더 커져서 찾아보기에서 찾기가 어려울 것이다.
특히 책과 찾아보기를 왔다 갔다를 너무 많이 반복하면 당연히 어려울 것이다.
그래서 INDEX를 만든다고 다 좋은 것은 아니다.
대량의 데이터만 INDEX를 만들면 된다.
인덱스의 장단점
장점) SELECT문의 결과가 정말 빠르다.
한 명의 처리가 빠르면 다른 사람의 처리도 빨라 진다.
그러면 전반적인 컴퓨터의 시스템이 빨라진다.
컴퓨터의 부담 또한 줄어든다.
단점) INDEX는 추가적인 공간을 차지한다.
10~20% 정도를 차지한다.
만들어진 테이블의 INDEX를 만드는데 시간이 오래걸릴 수도 있다.
그래서 가능하면 처음부터 INDEX를 만드는 것이 좋다.
인덱스의 종류
1. 클러스터형 인덱스
영어 사전, 국어 사전
2. 보조 인덱스
책의 맨 뒤의 찾아보기
자동으로 생성되는 인덱스
클러스터형 인덱스가 자동 생성될 수 있고, 보조 인덱스가 자동 생성될 수 있다.
PK 생성 시 PK열에 자동으로 클러스터형 인덱스가 생성이 된다.
영어사전과 같은 의미이다.
ABCD 순으로 되어있다.
SELECT를 하면 PK열로 정렬이 되어서 조회가 된다. (ABCD~ 순으로)
PK가 있는 테이블의 경우,
아무렇게나 입력을 해도
SELECT 시 PK 열로 ABCD~ 순으로 정렬이 되어서 조회가 되는 것을 볼 수 있다.
PK로 지정한 열에 자동으로 클러스터형 인덱스가 생성이 되었다.
SHOW INDEX FROM
table1 테이블을 생성하고, col1은 PK로 생성한다.
SHOW INDEX FROM 명령문을 통해 해당 table1 테이블의 인덱스를 조회하는 것을 확인할 수 있다.
Non_unique가 0이면 유일하다는 의미이다. PK는 중복값이 입력될 수 없기 때문이다.
Key_name에 PRIMARY로 입력되어 있다면 클러스터형 인덱스이다.
Column_name에 col1이라면 col1 컬럼에 클러스터형 인덱스가 생성되어 있다.
table2 테이블을 생성하고, 인덱스를 확인해보겠다.
이번에는 인덱스가 3개가 생긴 것을 볼 수 있다. 클러스터형 인덱스 1개(PK) 그리고 보조 인덱스 2개(UNIQUE)가 자동으로 생성되었다.
Key_name에 col2, col3가 입력된 이 두개의 컬럼은 보조 인덱스가 생성된 것이다.
UNIQUE는 테이블에 여러 개를 만들 수 있다. 즉 보조 인덱스는 여러 개 만들 수 있다.
그림(1)은 인덱스가 없이 데이터를 INSERT 시킨 것
정렬이 되어 있지 않다.
그리고 그림(2)는 인덱스가 있이 데이터를 INSERT 시킨 것이다.
정렬이 되어 있다.
PK로 자동 클러스터형 인덱스를 생성하거나, 클러스터형 인덱스를 생성해주어야 한다.
인덱스 없이 테이블을 생성한 경우
INSERT한 순서 그대로 SELECT 되는 것을 볼 수 있다.
인덱스를 생성한 테이블의 경우
ALTER TABLE member ADD CONSTRAINT PRIMARY KEY(mem_id);
명령어를 통해서 member 테이블의 mem_id를 PK로 선언할 수 있다.
mem_id열에 자동 클러스터형 인덱스가 생성된다.
SELECT 시 mem_id열로 알파벳 순으로 정렬이 된 것을 볼 수 있다. 영어사전 처럼 정렬이 된다.
클러스터형 인덱스는 테이블에 하나밖에 지정을 못 한다.
두개가 지정이 안 되는 것이 말이 안 되는 것은, 한 개의 컬럼으로 정렬이 되어야지 두개의 테이블을 동시에 정렬을 할 수는 없기 때문이다.
PK를 지우고 다시 이번에는 member 테이블의 mem_name열을 PK로 지정을 하였다.
그리고 SELECT시 mem_name열로 정렬되어 조회가 되는 것을 확인할 수 있다.
그리고 소녀시대를 INSERT 시
member 테이블에 순서가 맞추어서 INSERT가 되는 것을 볼 수 있다.
클러스터형 인덱스를 만들면 그 클러스터형 인덱스로 정렬이 된다.
내용적인 측면은 전혀 변함이 없다. 차례만 바귀는 것이다.
정렬되지 않는 보조 인덱스
보조 인덱스는 책의 내용은 그대로 유지가 되면서 찾아보기가 만들어 지는 것이다.
찾아보기는 가나다 순으로 되어 있다.
찾아보기를 통해서 페이지를 찾아보는 기능이다.
다시 인덱스를 제거하기 위하여
DROP TABLE IF EXTISTS member; 를 실행하여 member 테이블을 제거하고
CREATE TABLE 을 통해서 member 테이블을 인덱스가 없이 생성한다.
그리고 데이터를 아무렇게나 입력을 한다.
SELECT를 하면 데이터가 INSERT한 순서대로 입력된 것을 볼 수 있다.
이번에는 mem_id에 UNIQUE라는 CONSTRAINT를 추가하고
다시 member 테이블을 조회를 한 것을 볼 수 있다.
내용 및 차례는 아무런 변화도 없는 것을 볼 수 있다.
여러개의 보조 인덱스
보조 인덱스는 한 테이블에 여러개 만들 수 있다.
예를 들어 동식물에 대한 책이 있다고 가정을 해보자.
그러면 당연히 찾아보기에는 동물에 관한 찾아보기와, 식물에 관한 찾아보기
이렇게 두개의 찾아보기가 있어야 편리하다.
mem_name열을 보조 인덱스로 만들었다.
그리고 SELECT를 해보니 결과의 내용과 순서에는 변함이 없다.
똑같다.
이번에는 소녀시대라는 새로운 데이터를 추가해보겠다.
그리고 SELECT를 해보면
새로 추가된 데이터는 맨 뒤에 입력이 되어서 조회가 되는 것을 볼 수가 있다.
찾아보기만 나중에 추가가 되는 것이다.
내용은 그대로이다.
감사합니다.
https://www.youtube.com/watch?v=KZmW6VaY5BU&list=PLVsNizTWUw7GCfy5RH27cQL5MeKYnl8Pm&index=16
'SQLD' 카테고리의 다른 글
| [혼자 공부하는 SQL] 인덱스의 생성과 제거 문법 (CREATE INDEX, DROP INDEX) (1) | 2024.10.27 |
|---|---|
| [혼자 공부하는 SQL] 인덱스의 내부 작동 원리와 구조, 인덱스에서 데이터 검색하기 (0) | 2024.10.27 |
| [혼자 공부하는 SQL] 인덱스의 개념과 장단점, 클러스터형 인덱스와 보조 인덱스 (0) | 2024.10.27 |
| [혼자 공부하는 SQL] 가상의 테이블: 뷰(생성, 수정, 삭제) (0) | 2024.10.26 |
| [혼자 공부하는 SQL] SQL 테이블 제약조건 (기본키, 외래키, 고유키) (0) | 2024.10.26 |