INDEX 부여와 그 이유
▤ 목차
DB에서 가장 중요한 것은 탐색 속도이다.
예시로는 책뒤에 단어별 색인과 비슷하다.
특정 인덱스를 생성하면 해당 컬럼에 데이터들을 정렬하여 별도의 메모리 공간에 데이터의 물리적 주소와 저장된다.
pk값(+ unique값)은 자동으로 인덱스가 부여된다.
내가 자주 검색하는 칼럼(column)이 있다면 해당 칼럼에 인덱스를 부여해서 빠르게 검색할 수 있다.
쿼리문에 인덱스가 생성된 컬럼을 WHERE 조건으로 걸어주면 옵티마이저가 판단하여 생성된 인덱스를 사용할 수 있다.
📑 index
인덱스는 의미 그대로 '색인'이다.
내가 원하는 column에 인덱스 표를 부여하는 것이다.
보통 하나의 테이블에는 수십 개의 column이 존재한다.
database는 첫번째 column부터 차례대로 검색하는데, 내가 원하는 데이터가 데이터 하단에 존재한다면
찾을때마다 많은 시간이 걸릴 것이다.
즉, 자주 찾는 데이터가 있다면 index를 부여하는게 성능, 비용적으로 효율적이다.
[장점]
- WHERE절의 효율적인 사용 : 테이블을 만들고 데이터가 쌓이면 내부적으로 정해진 순서없이 데이터가 저장이 된다.
- ORDER BY절의 미사용 : ORDER BY는 부하가 많이 걸리는 작업이다. 디스트 I/O에서도 추가적으로 작업이 이뤄지기 때문이다.
- MIN(),MAX()의 효율적인 처리 : 데이터가 정렬되어 있기에 풀 스캔 없이 빠른 작업이 가능하다.
- 전반적인 시스템의 부하를 줄일 수 있다.
- 키 값을 기초로 하여 테이블에서 검색과정렬 속도를 향상시킨다.
index 사용을 자제해야하는 경우
- 입력, 수정, 삭제 시 index도 재생성해야 한다.
때문에 create, delete, update가 빈번한 테이블은 index를 부여하지 않는게 좋다.
- 자주 사용하지 않는 경우, index가 의미없다.
- 인덱스 생성은 최후의 수단이다.
인덱스를 관리하기 위해서는 데이터베이스는 약 10%에 해당하는 저장공간이 추가로 필요하다.
인덱스를 사용하게 되면 쿼리문 하나의 속도는 빨라지지만, 쌓이면 전체적으로 베이터 베이스의 부하를 일으킨다.
>> SQL문을 효율적으로 짜는게 더 좋다.
✏️ primary Key
기본키(줄여서 pk)는 테이블 내에서 각 행을 구별시키는 가장 기본적인 값이다.
특징
- Null을 가질 수 없다.
- Unique 키워드를 내포하고 있다. (즉, 다른 행과 차이를 주는 키워드이다.)
PK로 지정할 수 있는 column을 candidate key라고 부른다.
✏️ B - tree / B + tree
MySQL은 pk를 설정하면 해당 값을 index로 잡아 데이터를 B+tree 구조로 저장한다.
B-tree 는 ROOT - BRACH - LEAF 형태를 갖는다.
이때 Leaf 노드가 같은 레벨에 위치하도록 balance를 맞추는데, 이때문에 B-tree라고 부른다.
각각의 노드는 N개의 자식을 갖을 수 있고, N이 홀수냐 짝수냐에 따라 다른 알고리즘이 적용된다.
B-tree 에서 데이터를 찾는다면, Root노트부터 시작하여 검색하려는 값과 key point값을 비교하며 branch-Leaf로 차례로 찾아 내려간다.
즉, 원하는 값을 찾을때까지 모든 자료를 순회한다.
B+tree 는 각각의 노드들은 index값만 가진 상태에서 최하단 leaf에 실제데이터가 존재하며 linked list로 연결되어 있다.
각 상위 노트에 존재하기 때문에 하나의 저장공간에 더 많은 key값을 저장할 수 있어 효율적이고 탐색에도 유리하다.
하지만 언제나 최하단 leaf까지 접근해야하는 단점이 있다.
👏 요약
이진 탐색을 사용한다. 데이터를 반으로 나누며 탐색해나가는 방식이다.
하나씩 비교하는 찾는 방법보다 빠르게 값을 찾을 수 있다.
💻 코드로 보기
인덱스 부여하기
가장 먼저 데이터베이스를 만들어 사용할 수 있도록 준비하자.
create table town; -- 테이블 생성
create table town(
no int primary key,
irum char(10),
juso varchar(20)
);
-- 테이블에 row(데이터) 추가
insert into town values(1,'짱구','빨간지붕');
insert into town values(2,'철수','빌라');
insert into town values(3,'훈이','초록지붕');
이제 이름 column인덱스 부여해보자.
-- 형식) create index 인덱스부여명 on 테이블명(칼럼명);
create index ind_irum on town(irum);
-- index 확인 쿼리
show index from town;
town 테이블에 irum값에 대해 인덱스를 부여했다. 위의 인덱스표를 보면 알 수 있다.
pk는 자동으로 인덱스에 들어간다.
인덱스를 부여했다는 것은 이진 탐색이 가능해 irum으로 빠르게 검색이 가능하는 점이다.
이렇게 alter를 이용해 인덱스를 부여할 수도 있다.
alter table town add index ind_juso(juso);
인덱스 탐색하기
특정 이름을 찾는다면, 내부적 진행은 다음과 같다.
0. '훈이'를 찾는다.
1. 이름 인덱스표에서 '훈이'를 찾는다.
2. 해당 레코드 번호를 찾는다.
3. 원본 테이블에서 데이터를 가져온다.
explain select * from town where irum='훈이';
인덱스 삭제하기
부여한 인덱스를 삭제할 수도 있다.
drop index ind_juso on town;
복합 인덱스 설정하기
복합 인덱스란 두 개 이상의 컬럼을 포함하는 인덱스를 의미한다.
쿼리가 여러 컬럼을 동시에 필터링하거나 정렬할 때 유용하게 사용된다.
복합 검색을 많이 하는 경우이다.
create index idx_irum_juso on town(irum,juso);위의 쿼리처럼 2개의 값을 묶어서 인덱스를 만들 수 있다.
그럼 결과는 어떻게 될까?
SELECT * FROM town WHERE irum='철수' AND juso = '빌라';이런 식으로 복합 검색을 많이 하는 경우 보다 빠른 속도로 검색할 수 있다.
🔶옵티마이저 (OPTIMAZER)
사용자가 질의한 SQL문에 대해 최적의 실행 방법을결정하는 역할을 수행한다.
즉, SQL실행시 실행계획을 수립하고 실행한다.
옵티마이저는 데이터베이스 관리 시스템(DBMS)의 소프트웨어이다.
옵티마이저가 선택한 실행 방법의 적절성 여부는 질의의 수행 속도에 가장 큰 영향을 미친다.
결과는 동일해도, 어떻게 실행하느냐에 따라서 DB의 성능이 달라진다.
PLAN_TABLE에 실행 계획을 저장한다.(=> 개발자가 조회하여 확인 가능하다. DESC PLAN_TABLE;)
🔶 HINT
힌트는 옵티마이저의 실행 계획을 원하는대로 바꿀 수 있게 해준다.
JOIN이나 INDEX의 잘못된 실행 계획을 개발자가 직접 바꿀 수 있도록 도와주는 방법
힌트의 문법이 올바르더라도 힌트가 반드시 실행되는 것은 아니다. 옵티마이저에 의해 선택되지 않을 수도/ 될 수도 있다.
[ HINT 종류 ]
1. 옵티마이저 힌트
명령문의 한 테이블에 대한 최적화를 활성화하고 다른 테이블에 대한 최적화를 비활성화할 수 있다.
명령문 내의 옵티마이저 힌트는 optimizer_switch 보다 우선 적용된다.
범위 ) 전역 / 쿼리블록 / 테이블 / 인덱스
2. 인덱스 힌트
Mysql에서 제공하는 인덱스 힌트를 쓰며 강제적으로 할당한 INDEX를 이용하여 쿼리가 실행이 된다.
두개의 힌트는 함께 사용할 수도, 별도로 사용할 수도 있다.
😊정리
직접 데이터를 넣는 지금은 속도를 체감할 수 없지만 거대한 데이터를 사용한다면 index의 속도를 알 수 있을 것이다.
index는 자주 수정하는 곳에는 지정하지말자.
무엇보다 자주 찾는 데이터 colume에 설정해두자.
index는 자주 사용하지 않는 데이터에는 지정하는 것은 의미가 없다