[혼공학습단 12기] 혼자 공부하는 SQL - 5주차
[혼공학습단] - [혼공학습단 12기] 혼자 공부하는 SQL - 1주차
[혼공학습단] - [혼공학습단 12기] 혼자 공부하는 SQL - 2주차
[혼공학습단] - [혼공학습단 12기] 혼자 공부하는 SQL - 3주차
[혼공학습단] - [혼공학습단 12기] 혼자 공부하는 SQL - 4주차
- 인덱스
인덱스: 데이터를 빠르게 찾을 수 있도록 도와주는 도구
클러스터형 인덱스: 기본키로 지정하면 자동생성되며 테이블에 1개만 생성가능
예) 영어 사전
보조 인덱스: 고유키로 지정하면 자동 생성되며 여러개를 생성가능(자동 정렬기능은 없다.)
예) 책의 뒤의 찾아보기
* 필요없는 인덱스를 만들면, DB가 차지하는 공간만 더 늘어나고 인덱스를 이용해 데이터를 찾는 것이 전체 테이블을 찾아보는 것보다 느려진다. (인덱스는 테이블크기의 10%정도의 공간이 추가로 필요) 그러나, 적절한 인덱스 생성 시 기존보다 빠른 응답속도를 얻을 수 있다. (예: SELECT 문 검색 속도 향상) SELECT가 아닌 데이터의 변경 작업이 자주 일어나면 선능 저하를 불러올 수 있다.
- 자동으로 생성되는 인덱스
인덱스는 테이블의 열 단위에 생성, 하나의 열에는 하나의 인덱스를 생성가능
예) 회원 아이디가 기본키일 때, 자동으로 회원 아이디 열에 클러스터형 인덱스가 생성된다.
테이블에 1개만 생성 가능하다.
* 예를 들면, show index from table1을 했을 때, Key_name에 primary라고 있는데, 기본키로 설정해서 '자동으로 생성된 인덱스'라는 의미로 클러스터형 인덱스를 의미한다.
클러스터형 인덱스는 생성시, 순서대로 자동으로 정렬되고, 보조 인덱스는 생성 시 자동으로 정렬되지 않는다.
alter table member
add constraint
unique (mem_id);
select * from member;* 보조 인덱스를 생성해도 데이터의 내용이나 순서는 변경되지 않는다.
- 확인 문제
1. 인덱스의 개념으로 옳은 것은:
2) 인덱스는 select 문을 빠르게 처리합니다.
2. 인덱스의 장점 및 단점으로 거리가 먼 것은:
3) 인덱스는 추가적인 공간이 필요하지 않다.
3. 인덱스 종류와 관련된 설명으로 가장 거리가 먼 것은:
4) 보조 인덱스는 not null을 설정하면 자동 생성된다.(보조 인덱스는 고유키를 설정하면 자동생성)
4. 인덱스와 관련된 설명 중 가장 거리가 먼 것은:
1) 중복된 값을 허용하는 인덱스를 고유 인덱스라고 부른다. (인덱스 값이 중복되는 것을 허용하는 인덱스는 단순 인덱스이다.)
(show index문을 통해 인덱스 정보를 확인할 수 있다.)
5. 클러스터형 인덱스에 관련된 설명 중 거리가 먼 것 2개:
2) unique로 지정하면 자동으로 생성된다. 4) 테이블당 1개 이상을 설정할 수 있다.
- 인덱스의 내부 작동
클러스터형 인덱스와 보조 인덱스는 내부적으로 균형 트리로 만들어진다. 균형 트리는 '자료구조'에 나오는 범용적으로 사용되는 데이터의 구조를 말한다. 균형트리 구조에서 데이터가 저장되는 공간을 노드라고 한다.
루트노드: 노드의 가장 상위노드
중간노드: 루트노드와 리프노드 사이의 노드
리프노드: 노드의 가장 마지막에 존재하는 노드
mysql에서는 노드를 페이지라고 부른다.
데이터를 검색하는 데 균형 트리가 더 효율적인데, 인덱스를 만들면 select의 속도를 향상 시킬 수 있다. insert 작업이 일어날 때 더 느리게 입력될 수 있는데, 이유는 페이지 분할이라는 작업이 발생하기 때문이다. 페이지 분할이 일어나면 MySQL이 느려진다.
기본 키 제약조건을 통해, 클러스터형 인덱스를 생성하면, 데이터 페이지가 정렬된다. 클러스터형 인덱스는 루트 페이지, (중간 페이지), 리프 페이지로 구성되어 있고, 인덱스페이지의 리프 페이지는 데이터 그 자체를 말한다.
고유 키 제약조건을 통해, 보조 인덱스를 생성하면, 보조 인덱스는 데이터 페이지를 건드리지 않고, 별도의 장소에 인덱스 페이지를 생성한다.데이터의 위치는 페이지 번호 +#위치로 기록된다.
* 인덱스에서 데이터를 검색할 때, 클러스터형 인덱스는 루트 페이지, 리프 페이지 2개의 페이지를 읽어서 목표한 이름을 알아내고, 보조 인덱스는 인덱스페이지의 루트 페이지, 리프 페이지, 그리고 데이터 페이지를 읽어서 목표한 이름을 알아낸다.
- 확인 문제
1. 트리와 관련된 용어로 거리가 먼것은:
4) 트리의 일부분을 잘라낸 것을 가지라고 한다.
2. 최상위 노드 - 루트 노드, 가운데 노드 - 중간 노드, 최하단 노드 - 리프 노드, 16kb 크기의 최소한의 저장단위 - 페이지
3. 인덱스를 구성하게 되면 데이터의 변경 작업(insert, update, delete) 시에 성능이 나빠지는 단점이 있는데, insert 작업이 일어날 때 페이지 분할이라는 작업이 발생되기 때문에 더 느리게 입력 될 수 있다.
4. 인덱스 구조에 대한 설명으로 거리가 먼 것을 2개 고르면:
2) 클러스터형 인덱스를 생성하면 데이터는 해당열의 내림차순으로 정렬된다. (오름차순으로 정렬된다.)
3) 보조 인덱스를 생성하면 데이터는 해당 열을 기준으로 오름차순 정렬된다. (정렬되지 않는다.)
- 인덱스의 실제 사용
인덱스를 생성하기 위해서는 create index 문을 사용하고 제거하기 위해서는 drop index 문을 사용한다.
- 인덱스 생성, 제거하는 기본 형식 작성
create [unique] index 인덱스_이름
on 테이블_이름 (열_이름) [asc | desc] --- 인덱스 생성
drop index 인덱스_이름 on 테이블_이름 --- 인덱스 제거- 인덱스 생성과 제거 문법
직접 인덱스를 생성할 때 create index 문을 사용
unique 사용 시, 중복이 안되는 고유 인덱스를 생성
예) 회원 이름이 unique로 지정되면, 향후 같은 이름의 회원은 입력 불가
휴대폰 번호, 이메일 등은 별 문제가 없다.
기본키, 고유키로 자동생성된 인덱스는 drop index로 제거하지 못하고 alter table문을 사용해야 한다.
* 클러스터형 인덱스부터 제거하면 내부적으로 데이터가 재구성되므로, 인덱스를 제거할 때는 보조 인덱스부터 제거하는 것이 좋다.
- 인덱스 생성과 제거 실습(p. 310 인덱스 생성 후 key_name이 primary로 출력)
use market_db;
select * from member;
show index from member;
show table status like 'member';
테이블에 생성된 인덱스의 크기를 확인할 수 있는 show table status 문을 사용하여, member 이라는 글자가 들어간 테이블의 정보를 확인한다. 이미 클러스터형 인덱스가 존재하므로 이 테이블에는 더 이상 클러스터형 인덱스를 생성할 수 없다.
주소에 중복을 허용하는 단순 보조 인덱스를 생성한다.
create index idx_member_addr
on member(addr);
show index from member;
analyze table 문을 통해 생성한 인덱스를 실제로 적용시키기 위해, 테이블을 분석/처리한다.
analyze table member; --- 지금까지 만든 인덱스를 모두 적용
show table status like 'member';
* index_length(보조 인덱스 크기)는 16kb(16384byte)이다.
만약, 인원수에 중복을 허용하지 않는 고유 보조 인덱스를 생성한다고 한다. 3개의 그룹의 인원수가 4이므로 이미 중복된 값이 존재한다. 따라서, 인원수 열에는 고유 보조 인덱스를 생성할 수 없다.
create unique index idx_member_mem_number
on member (mem_number);| 0 | 60 | 20:04:55 | create unique index idx_member_mem_number on member (mem_number) | Error Code: 1062. Duplicate entry '4' for key 'member.idx_member_mem_number' | 0.031 sec |
*만약 이름이 중복된다고 회원 가입이 안된다면, 실제로는 문제가 발생할 소지가 있다. 따라서, 고유 보조 인덱스를 지정할 때 현재 중복된 값이 없다고 무조건 설정하면 안된다. 업무상 절대로 중복되지 않는 열 (주민등록번호, 학번, 이메일 주소)에만 unique 옵션을 사용한다.
MySQL이 전체 테이블 검색이 인덱스 검색보다 낫겠다고 판단하면, 테이블을 차례대로 읽어 full table scan을 한다.
*외래키의 이름을 알아내는 방법
select table_name, constraint_name
from information_schema.referential_constraints
where constraint_schema = 'market_db';
- 인덱스를 효과적으로 사용하는 방법
1) 인덱스는 열 단위에 생성된다.
2) where 절에서 사용되는 열에 인덱스를 만들어야 한다.
- select 문을 사용할 때 where 절의 조건에 해당 열이 나와야 인덱스를 사용하는데,
아래에서는 market_db의 member을 사용한다.
select mem_id, mem_name, mem_number, addr
from member
where mem_name = '에이핑크';
member 테이블에 이 sql만 사용한다고 가정하면,
mem_id, mem_number, addr 열에는 인덱스를 생성해도 전혀 사용하지 않는다.
where 절에 있는 mem_name 열의 인덱스만 사용하는데, 이 열 외에 다른 열에 인덱스를 생성하는 것은 낭비이다.
3) where 절에 사용되더라도 자주 사용해야 가치가 있다.
mem_name 열에 인덱스를 생성해서 효율이 아주 좋아진다고 하더라도 select문은 1년에 1번정도만 사용되고 member 테이블에는 주로 insert 작업만 일어난다면, 인덱스로 인해서 성능이 나빠지는 결과가 발생한다.
4) 데이터의 중복이 높은 열은 인덱스를 만들어도 효과가 없다. 예) 성별, 연락처 국번, 주 사용 교통 수단
5) 클러스터형 인덱스는 테이블당 하나만 생성할 수 있으므로, 조회 시 가장 많이 사용되는 열에 지정하는 것이 효과적이다.
6) 사용하지 않는 인덱스는 제거해야 한다.
- 확인문제
1. 인덱스를 생성하는 문에 대한 설명으로 거리가 먼것은:
2) 인덱스의 이름은 고정되어 있다. (고정되어 있지 않다.)
2.
인덱스 생성: create index
인덱스 제거: drop index
테이블에 생성된 인덱스 이름과 열 확인: show index
인덱스의 할당된 크기를 확인: show table status
3. 인덱스를 생성하는 세부적인 내용과 거리가 먼 것은:
1) 클러스터형 인덱스와 보조 인덱스를 동시에 한 테이블에 생성할 수 없다.(클러스터형 인덱스는 1개, 보조 인덱스는 여러개 생성가능)
2) 이미 중복된 값이 있는 열에 create unique index 문을 사용하면 중복데이터는 제거된다. (중복된 값이 있으면 create unique index문은 오류 발생)
(중복 데이터가 많은 열에는 인덱스를 만들어도 큰 효과 없다.)
4. 실행 계획에 관련된 설명으로 거리가 먼 것은:
1) mysql 워크 벤치의 [execution plan] 창은 sql을 실행하기 전에 확인할 수 있다. (sql 실행 후 확인 가능)
3) 인덱스를 사용하면 index scan이라고 표시된다. (인덱스를 사용하면 single row, index range scan 등 다양한 형태로 표시됨)
( full table scan은 인덱스를 사용하지 않았다는 의미이고, 인덱스가 있어도 full table scan을 할 수 있다.. )
5. 인덱스읙 결론으로 거리가 먼 것은:
1) 인덱스는 행단위에 생성된다. (인덱스는 열 단위에 생성됨)
3) 데이터의 중복도가 높으면 인덱스가 효과적이다. (중복도가 높으면 인덱스의 효과가 없음)
(where 절에서 사용되는 열에 인덱스를 생성해야 한다.)