#혼공학습단 #혼공 #혼자공부하는SQL #혼공S
본 글은 2023년 7월 3일 ~ 2023년 8월 20일까지 진행하는 혼공학습단에 참여하며 정리한 글입니다.
이번 장에서는 테이블을 만들고 그 테이블에 제약조건을 거는 방법, 그리고 뷰를 이용한 SQL 활용에 대해 알아본다.
06-1 인덱스 개념을 파악하자
•
인덱스는 데이터를 빠르게 찾을 수 있또록 도와주는 도구
•
인덱스 종류에는 (1) 클러스터형 인덱스와 (2) 보조 인덱스가 있다.
◦
클러스터형 인덱스(Clustered Index) : 기본키로 지정하면 자동 생성되며, 테이블에 1개만 만들 수 있다.
▪
기본 키 (Primary Key)
▪
테이블당 1개
▪
지정한 열로 정렬됨
◦
보조 인덱스(Secondary Index) : 고유 키로 지정하면 자동 생성되며 여러 개를 만들 수도 있지만 자동 정렬되지는 않는다.
▪
고유 키 (Unique)
▪
테이블당 여러개 가능
▪
정렬되지 않음
인덱스의 개념
•
인덱스는 대용량일 수록 매우 중요하다.
•
검색을 빠르게 할 수 있도록 하는 도구이다.
•
대용량의 테이블일 수록 인덱스 사용 여부 및 제대로 사용하는 것이 매우 중요
인덱스의 문제점
•
인덱스를 제대로 이해하지 못하고 남용하는 것은 문제점을 발생
•
필요 없는 인덱ㄱ스를 만들면 데이터베이스가 차지느하는 공간만 더 늘어나고, 전체 테이블을 찾는 것보다 느려질 수 있다.
인덱스의 장점과 단점
•
장점
◦
빠른 응답 속도
•
단점
◦
SELECT , 즉 데이터를 찾는게 자주일어나느 것이 아니라, 변경 작업(INSERT, UPDATE, DELETE)가 많이 일어나면 성능이 나빠질 수 있음
인덱스 확인하기
SHOW INDEX FROM table1;
SQL
복사
인덱스 생성
•
col2, col3 은 고유키로 지정하여 보조 인덱스로 활용
CREATE TABLE table2 (
col1. INT. PRIMARY KEY,
col2. INT. UNIQUE,
col3. INT. UNIQUE
)
SQL
복사
06-2 인덱스의 내부 작동
•
클러스터형 인덱스와 보조 인덱스는 모두 내부적으로 균형 트리, 즉 Balanced Tree, B-Tree 자료 구조로 만들어짐
•
노드 : 균형 트리 자료 구조에서 데이터를 저장되는 공간을 가리킴
◦
MYSQL에서는 이 노드를 페이지라고 지칭
페이지
•
최소한의 저장 단위
•
16 Kbyte(16384 byte)
•
1건의 데이터를 저장하더라도 1개의 페이지가 필요
•
균형 트리는 무조건 루트 페이지부터 검색
◦
루트 페이지 → 리프 페이지로 검색하는 순서
페이지 분할
•
데이터를 변경하게 되면 페이지 분할이 일어남
•
여기서 데이터 변경은 INSERT, UPDATE, DELETE ㄷmd.
•
페이지 분할시 데이터가 다시 각각의 페이지를 생성하고, 균형 트리 정렬 알고리즘에 맞게 다시 정렬
•
그렇게 때문에 인덱스가 지정된 테이블에 데이터 변경은 그 속도를 느리게 할 수 있음
인덱스 검색
•
클러스터형 인덱스 / 보조 인덱스를 활용해 데이터 검색
•
전체 테이블을 검색할 때보다 훨씬 효율적임
06-3 인덱스의 실제 사용
•
실무에서는 인덱스 생성구문으로 아래 코드 블럭에서 가장 윗 구문을 제일 많이 사용한다.
인덱스 생성 구문
CREATE [UNIQUE] INDEX 인덱스_이름
ON 테이블_이름 (열_이름) [ASC|DESC]
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_mem_name
[index_type]
ON tbl_mem_name (key_part, ...)
[index_option]
[algorithm_option | lock_option] ...
key_part: {col_mem_name [(length)] | (expr)} [ASC | DESC]
index_option:
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_mem_name
| COMMENT 'string'
| {VISIBLE | INVISIBLE}
index_type:
USING {BTREE | HASH}
algorithm_option:
ALGORITHM [=] {DEFAULT | INPLACE | COPY}
lock_option:
LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}
SQL
복사
인덱스 제거 구문
DROP INDEX 인덱스_이름 ON 테이블_이름
SQL
복사
인덱스를 효과적으로 사용하는 방법
1.
인덱스는 열 단위에 생성한다.
•
하나의 열에 2개 이상의 인덱스를 만들 수는 있지만 이런 경우는 매우 드뭄.
•
하나의 열에 하나의 인덱스를 생성하는 것이 가장 일반적.
2.
WHERE 절에서 사용되는 열에 인덱스를 만들어야 한다.
•
SELECT 문을 사용할 때 WHERE 절의 조건에 해당 열이 나와는 경우 인덱스를 사용한다.
3.
WEHRE 절에 사용되더라도 자주 사용해야 가치가 있다.
4.
데이터의 중복이 높은 열은 인덱스를 만들어도 별 효과가 없다.
5.
클러스터형 인덱스는 테이블당 하나만 생성할 수 있다.
6.
사용하지 않는 인덱스는 제거한다
•
공간을 확보하기 목적 뿐만 아니라 데이터 삽입시 발생되는 부하도 많이 줄일 수 있다.
[혼공학습단 10기 미션]
기본미션
p. 310의 인덱스 생성하고 key_name이 PRIMARY로 출력된 결과 화면 캡처하기
선택미션
인덱스 생성, 제거하는 기본 형식 작성하기
-- 인덱스 생성
CREATE [UNIQUE] INDEX 인덱스_이름
ON 테이블_이름 (열_이름) [ASC|DESC];
-- 인덱스 제거
DROP INDEX 인덱스_이름 ON 테이블_이름
SQL
복사