Ch6. 인덱스
6.1 인덱스 개념을 파악하자
- 인덱스 : SELECT를 사용해서 데이터를 빠르게 찾을 수 있도록 도와주는 도구. 하나의 열에 하나의 인덱스 생성.
| 인덱스 종류 | 설명 |
|---|---|
| 클러스터형인덱스 | PK를 통해 자동생성되고 자동정렬됨. 테이블당 1개. 사전느낌. |
| 보조인덱스 | 고유키(UNIQUE)로 지정하면 자동생성되고 정렬안됨. 여러개 가능. 책 뒤의 찾아보기느낌. |
💥 주의: 필요없는 인덱스를 만들면 데이터베이스가 차지하는 공간은 늘어나고, 전체 테이블을 찾아보는 것보다 인덱스를 사용해 데이터를 찾는 것이 더 느림.
- 장점 : 빠른 응답 속도. / 컴퓨터 부담도 줄어서 시스템 성능 향상.
- 단점 : 인덱스도 공간을 차지하므로 데이터베이스 안에 추가적인 공간 필요. / 데이터변경(UPDATE, INSERT, DELETE)을 자주하면 오히려 성능이 나빠짐.
* 자동으로 생성되는 인덱스
(1) 기본키(PK) -> 클러스터형 인덱스 생성
SHOW INDEX FROM 테이블이름;
으로 인덱스 정보 확인
💡Key_name이 PRIMARY라고 되어 있으면 클러스터형 인덱스라고 생각할 것.
- 기본키로 지정한 열을 기준으로 데이터들이 자동정렬됨. ex) 알파벳순, 가나다순
💥 주의: 이미 대용량의 데이터가 있는 상태에서 기본키를 지정하면 정렬하는데 오랜 시간이 소요됨.
💥 주의: 중복이 있을 수 있는 열(ex. 회원이름)으로 기본키를 변경하는 것도 위험!!!
(2) 고유키(UNIQUE) -> 보조 인덱스 생성
- Non Unique칸에
0 : False. 중복허용 x. 즉, 고유 인덱스
1 : True. 중복허용 o. 즉, 단순 인덱스 x.
💡Key_name이 열이름으로 되어 있으면 보조 인덱스라고 생각할 것.
- 여러개 설정가능
- 보조인덱스를 생성해도 데이터의 순서는 변경되지 않음. 데이터가 추가되면 제일 뒤에 추가됨.
💥 주의: 데이터베이스의 공간이 차지되므로 필요한 열만 보조인덱스를 생성할 것!
6.2 인덱스의 내부 작동
▧ 인덱스의 내부 작동 원리
- 균형트리의 개념
- 루트노드로부터 출발해서 중간노드, 리프노드에 데이터가 저장.
- 노드(페이지) : 균형트리구조에서 데이터가 저장되는 공간.
장점 :
데이터를 처음부터 끝까지 검색하는 전체 테이블 검색이 아닌, 루트페이지부터 리프페이지로 최소한의 페이지만 확인하여 원하는 데이터를 빠르게 찾음.
- 균형트리의 페이지 분할
- 페이지분할 : 새로운 페이지를 준비해서 데이터를 나누는 작업으로, 성능이 느려짐. 데이터를 INSERT하다보면 페이지에 빈 공간이 없어지는데 그 때 페이지 분할이 일어남. 리프페이지가 분할되면 루트페이지에도 해당사항이 반영됨.
→ 루트페이지가 2장이상이 되면, 중간페이지가 만들어지고 데이터 하나를 입력하는데 매우 오래 걸림.
▧ 인덱스의 구조
(1) 클러스터형 인덱스 구성하기
- CREATE TABLE 테이블이름 으로 먼저 테이블 생성
- SELECT * FROM 테이블이름; 으로 데이터 순서확인 -> 입력순서
- 클러스터형 인덱스 구성
ALTER TABLE 테이블이름
ADD CONSTRAINT
PRIMARY KEY(해당열);
- 데이터 순서 확인
SELECT * FROM 테이블이름;
-> mem_id가 영문으로 되어있으니까 알파벳순으로 정렬됨.
(2) 보조 인덱스 구성하기
- CREATE TABLE 테이블이름 으로 먼저 테이블 생성
- 고유키(UNIQUE)를 설정한 후 SELECT * FROM 테이블이름; 으로 데이터 순서 확인
ALTER TABLE 테이블이름
ADD CONSTRAINT
UNIQUE(mem_id);
SELECT * FROM 테이블이름;
-> 고유키 설정 전과 설정 후의 데이터 순서가 동일!
▧ 인덱스에서 데이터 검색하기
(1) 클러스터형 인덱스에서 검색
# SPC인 회원의 이름 검색하기
1️⃣ 루트 페이지 탐색
2️⃣ 해당하는 리프 페이지로 이동
3️⃣ 회원 이름 데이터 찾음!
💡클러스터형 인덱스는 리프 페이지 = 데이터 페이지
즉, 데이터 정렬된 순서대로 리프 페이지에도 정렬되어 있음.
(2) 보조 인덱스에서 검색
# SPC인 회원의 이름 검색하기
1️⃣ 루트 페이지 탐색
2️⃣ 해당하는 리프 페이지로 이동
3️⃣ 해당하는 위치의 데이터 페이지로 가서 데이터 찾음.
💡보조 인덱스는 리프 페이지 ≠ 데이터 페이지
보통, 클러스터형 검색이 더 빠름..
6.3 인덱스의 실제 사용
▧ 인덱스의 생성과 제거 문법(PK,UNIQUE 외에 직접!)
(1) 인덱스의 생성 문법
CREATE [UNIQUE] INDEX 인덱스이름
ON 테이블이름 (열이름) [ASC|DESC]
- CREATE INDEX로 생성되는 인덱스는 보조 인덱스
- 입력된 값들에 중복이 없을때 CREATE UNIQUE
ex) 회원이름, 나이 같은건 불가. 휴대폰 번호, 이메일은 가능
- 기본은 오름차순(ASC)
(2) 인덱스의 제거 문법
DROP INDEX 인덱스이름 ON 테이블이름
💥주의: PK, UNIQUE로 자동 생성된 인덱스는 DROP INDEX로 제거 불가!-> ALTER INDEX 이용
- 보조 인덱스와 클러스터형 인덱스 모두 있을 때에는 보조 인덱스부터 제거 -> 클러스터형 인덱스부터 제거하면 내부적으로 데이터가 재구성되므로.
▧ 인덱스의 생성과 제거 실습
(1) 인덱스 생성 실습
① 클러스터형 인덱스 생성
- 홈페이지에서 market_db.sql을 다운받아 열어서 member테이블의 인덱스를 알아보기
SHOW INDEX FROM member;
-> mem_id라는 열 1개만 클러스터형 인덱스(PRIMARY)로 설정됨.
- 인덱스의 크기 확인
SHOW INDEX STATUS LIKE 'member';
-> 한 페이지의 크기는 기본적으로 16KB이고, 1KB = 1024byte.
여기서는 16*1024 = 16384 이므로 한 페이지가 할당되어 있는것.
② 주소에 중복을 허용하는 단순 보조 인덱스 생성
참고)
중복을 허용하는 건 단순 보조 인덱스 -> Non unique가 1(true)
중복을 허용하지 않는건 고유 보조 인덱스 -> Non unique가 0(false)
CREATE INDEX 새롭게 지정할 인덱스 이름
ON 테이블이름(원래 테이블에서 인덱스로 지정할 열 이름);
SHOW INDEX FROM 테이블이름;
💥ANALYZE TABLE : 생성한 인덱스를 실제로 적용시킬 때 이용
ANALYZE TABLE 테이블이름;
SHOW TABLE STATUS LIKE '테이블이름';
-> Index_length부분이 보조 인덱스의 크기를 나타내어주는데 알맞은 결과가 나옴.
③ 회원 이름에 고유 보조 인덱스 생성
CREATE UNIQUE INDEX 새롭게 지정할 인덱스 이름
ON 테이블이름(원래 테이블에서 인덱스로 지정할 열 이름);
-> Non_unique값이 0이므로 중복을 허용하지 않는다는 의미
💥주의: 사용자가 입력했는데 중복이 있을 수 있다면, 이 열을 고유 보조 인덱스로 지정해서는 안됨!!!! 애초에 절대로 중복되지 않는 열(주민등록번호, 학번, 이메일 등)만 UNIQUE옵션 사용하여 인덱스 생성
(2) 인덱스의 활용 실습
- 지금까지 만든 인덱스가 어느 열에 있는지 확인
ANALYZE TABLE 테이블이름;
SHOW INDEX FROM 테이블이름;
- 인덱스를 사용하고 있는지 확인하려면 [Executon Plan]창을 열어 확인. Full Table Scan이라고 뜨면 인덱스 사용 안한 것!
나머지는 모두 인덱스를 사용했다는 의미
그런데 만약, 인덱스열을 통해 데이터를 검색했다면
-> 인덱스를 이용해서 결과를 얻었다는 의미 👍
- 인원수로 단순 보조 인덱스를 만들어 인덱스 사용 여부 확인
CREATE INDEX idx_member_mem_number
ON member(mem_number);
ANALYZE TABLE member; -- 인덱스 적용
SELECT mem_name, mem_number
FROM member
WHERE mem_number >= 7; -- 인원수 7이상인 멤버 조회
이후에 Execution Plan창 조회하면
-> 인덱스 사용했다는 결과!
(3) 인덱스를 사용하지 않을 때
- 전체 테이블 검색이 인덱스 검색보다 낫겠다고 SQL이 판단할 경우
ex) 인원수가 1명 이상인 회원 조회하고 인덱스 사용여부 확인해보니
- WHERE문에서 열에 연산을 했을 경우
ex) WHERE mem_number * 2 >= 14로 조회
(4) 인덱스 제거 실습
💥 주의 : 보조인덱스부터 제거 후 외래 키 제거 후 기본 키 제거!
1️⃣ 보조인덱스부터 제거
2️⃣ 외래 키 확인 후 외래 키부터 제거
-> information_schema 데이터베이스의 referential_constraints 테이블을 조회하면 전체 외래키의 이름을 알 수 있음.
ALTER TABLE buy
DROP FOREIGN KEY buy_ibfk_1;
3️⃣ 기본 키 제거
ALTER TABLE member
DROP PRIMARY KEY;
💡인덱스는 찾아보기를 제거한 것일뿐!!! 데이터의 내용이 바뀌거나 삭제되는거 아님!!!!!
🤩인덱스를 효과적으로 사용하려면?🤩
- WHERE절이 자주 사용되는 열에 인덱스를 만들 것!!
- 사용하지 않는 인덱스는 제거
- 데이터의 중복이 높은 열은 인덱스 만들지 말 것!!
