인덱스
선형/이진 탐색
•
선형/이진 탐색의 개념 필요
•
위와 같은 정렬되어 있지 않은 배열에서 2를 찾기 위해서는
◦
앞에서부터 하나씩 찾는 것 = 선형 탐색(Linear Search)
◦
오름차순으로 정렬되어 있다면? ⇒ 탐색 범위를 반씩 줄여나간다 ⇒ 이진 탐색(Binary Search)
◦
0 ~ 10
▪
0~10 사이의 중간 원소를 찾는다 = 5
▪
5 뒤에는 3이 존재할 수 없다.
▪
0~4
•
3이 있을 수 있는 부분에서 중간 원소를 찾는다
•
중간 원소 = 2
•
2 이하(0~2)에서는 3을 찾을 수 없기 때문에 2를 넘어선 범위를 찾게 된다
◦
3~4
▪
배열 요소가 짝수 개여서 중간 요소가 없을 때는 중간 지점에서 하나 앞 원소 사용
▪
3을 찾는다
•
선형 탐색
◦
O(n)
▪
1024개의 숫자가 정렬되어 있을 때, 최대 1024번만에 원하는 데이터를 찾을 수 있다.
•
이진 탐색
◦
O(log(n))
▪
1024개의 숫자가 정렬되어 있을 때, 최대 11번만에 원하는 데이터를 찾을 수 있다.
◦
데이터가 정렬되어 있어야한다.
인덱스
•
데이터베이스도 찾고 싶은 값이 들어있는 column을 기준으로 선형탐색을 사용해서 찾고 싶은 값과 일치하는 record를 탐색할 수 있다.
•
하지만 선형 탐색은 너무 비효율적이다.
•
위와 같이 index를 사용하지 않은 컬럼을 조회해야 하는 상황이라면 전체를 탐색하는 Full Scan을 수행해야 한다. Full Scan은 전체를 비교하여 탐색하기 때문에 처리 속도가 떨어진다.
•
인덱스는 데이터베이스 분야에 있어서 테이블에 대한 동작의 속도를 높여주는 자료 구조를 일컫는다.
•
인덱스는 테이블 내의 1개의 컬럼, 혹은 여러 개의 컬럼을 이용하여 생성될 수 있다. 고속의 검색 동작뿐만 아니라 레코드 접근과 관련 효율적인 순서 매김 동작에 대한 기초를 제공한다
•
인덱스는 순서대로 정렬되어 있기 때문에 이진 탐색이 가능하다.
◦
아래와 같이 하나 혹은 여러 칼럼에 대해서 인덱스 자료구조를 생성할 수 있다.
▪
아래의 인덱스 이메일 레코드들은 알파벳 순서대로 정렬되어 있다.
user 테이블의 이메일 칼럼에 대해서 인덱스를 저장해놓고 값의 위치를 저장해놓는다
원하는 값의 칼럼 : 원본 테이블에서의 원하는 값의 메모리 주소
위와 같이 데이터와 데이터의 위치를 포함한 자료구조를 생성하여 빠르게 조회할 수 있도록 돕고 있다.
Clustered vs Non-Clustered 인덱스
•
Clustered 인덱스
◦
데이터베이스에 저장돼있는 데이터 자체를 특정 순서대로 저장하는 인덱스
◦
로우들이 email의 알파벳 순으로 정렬됨
◦
장점
▪
조회 속도가 굉장히 빠르다
▪
데이터를 특정 순서대로 저장했기 때문에 Clustered 인덱스는 하나밖에 만들 수 없습니다.
•
책으로 비유를 하면,
•
책 가장 뒤에 따로 인덱스를 만들어놓는 게 아니라, 영한 사전 같이 애초부터 단어들을 알파벳 순서대로 저장하는 겁니다.
•
이렇게 하면 찾고 싶은 영어 단어는 빠르게 찾을 수 있겠지만, 이미 알파벳 순서대로 책을 만들어놨기 때문에, 사전 안에서 특정 한글 단어를 찾고 싶을 때는 선형 탐색을 사용해야 합니다.
▪
언어 사전과 비슷한 개념
•
non-clustered 인덱스
◦
테이블 자체는 그대로 놔두고 다른 곳에 순서를 저장
▪
예
•
각 이메일에 대해서 해당 row의 유저가 저장된 주소를 저장
•
원하는 이메일을 찾았을 때, 주소를 사용해서 바로 원하는 유저 row를 찾을 수 있다.
◦
인덱스를 모든 칼럼에 대해서 만들 수 있다.
▪
실제 테이블과 무관하게 순서를 저장하기 때문에 개수의 제한이 없음
◦
실제 테이블과 다른 곳에 저장돼있기 때문에 데이터를 찾는 게 clustered 인덱스보다 조금 느리다
◦
일반 책의 색인, 또는 인덱스와 비슷한 개념
인덱스의 중복
•
컬럼에 중복되는 값들이 있어도 인덱스는 충분히 잘 작동할 수 있습니다.
특정 브랜드인 제품 로우들을 찾고 싶을 때는 이진 탐색을 통해서 브랜드를 찾고 가장 위에 있는 주소부터
시작해서 하나씩 아래로 가면서 해당 로우들에 접근하면 되는 거죠.
•
예를 들어 브랜드가 나이코인 제품들을 찾고 싶으면 주소가 1080인 제품부터 1030, 1040에 순차적으로 접근할 수 있습니다.
•
만약 브랜드가 나이코면서, 다른 조건도 만족하는 로우를 찾고 싶다면 이 주소들 중에서 원하는 로우를 찾을 수 있습니다.
•
이 로우 주소들은 어떤 특정 순서로 정렬된 것이 아니기 때문에 브랜드가 나이코인 제품들 중에서 또 다른 조건도 만족하는 데이터를 찾고 싶을 때는 네모 쳐진 제품들을 일일이 확인해보는 선형 탐색을 사용해야 합니다.
Composite 인덱스
인덱스는 단순히 하나의 컬럼이 아니라, 여러 개의 컬럼에 대해서, 합쳐서도 만들 수 있습니다.
1.
각각 브랜드와 종류에 대해서 따로 인덱스가 있을 경우
a.
먼저 특정 브랜드, 나이코에 해당하는 로우들을 인덱스와 이진 탐색으로 찾고, 그 다음에 이 로우들에서 반팔티 제품들을 선형 탐색으로 찾거나,
b.
반팔티 제품인 로우들을 인덱스와 이진 탐색으로 찾고, 그 다음에 이 로우들에서 나이코인 제품을 선형 탐색으로 찾아야하는 거죠.
2.
여러 개에 대해서 인덱스를 만들 수 있습니다.
a.
먼저 원하는 브랜드를 이진 탐색으로 찾고, 원하는 종류도 그 안에서 이진 탐색으로 찾을 수 있기 때문에, 데이터 조회를 훨씬 더 빨리 할 수 있습니다.
3.
주의할 점
a.
개별 컬럼들에 인덱스를 추가하는 것과, 여러 컬럼들에 대한 인덱스를 추가하는 건 두 개의 다른 인덱스
i.
브랜드, 종류, 그리고 색 컬럼들에 대한 인덱스 세 개를 만들어놔도, 브랜드, 종류, 색 모두에 대한 인덱스와는 다르다
b.
여러 컬럼들에 대한 인덱스를 만들 때, 순서가 중요
i.
여러 컬럼에 대한 인덱스를 사용할 때는 항상 가장 왼쪽에 조건으로 가장 많이 사용하는 컬럼을 사용하고, 오른쪽으로 갈수록 조건으로 덜 사용하는 컬럼을 사용해야 합니다.
인덱스(index)의 장점과 단점
•
장점
◦
테이블을 조회하는 속도와 그에 따른 성능을 향상시킬 수 있다.
◦
전반적인 시스템의 부하를 줄일 수 있다.
◦
모든 칼럼과 모든 칼럼의 조합에 대해서 인덱스를 추가하지는 않는다.
•
단점
◦
인덱스를 관리하기 위해 DB의 약 10%에 해당하는 저장공간이 필요하다.
◦
인덱스를 관리하기 위해 추가 작업이 필요하다.
◦
인덱스를 잘못 사용할 경우 오히려 성능이 저하되는 역효과가 발생할 수 있다.
▪
인덱스를 너무 많이 사용할 경우, 용량이 커져 데이터 베이스가 느려짐
◦
인덱스 업데이트 문제
▪
인덱스는 조회는 빠르게 할 수 있지만, 삽입, 삭제, 업데이트는 오히려 더 느리게 만든다.
•
위의 연산들이 있는 테이블 칼럼은 인덱스를 지양한다.
인덱스(index)를 사용하면 좋은 경우
•
모든 Primary key에 대해서 인덱스 생성
•
모든 Foreign key에 대해서 인덱스 생성
•
특정 조회 쿼리가 너무 느려지거나, 느려질 게 확실한 경우 조회에 사용되는 칼럼들에 대해서 인덱스 생성
◦
INSERT, UPDATE, DELETE가 자주 발생하지 않는 컬럼
◦
JOIN이나 WHERE 또는 ORDER BY에 자주 사용되는 컬럼
•
규모가 작지 않은 테이블
•
데이터의 중복도가 낮은 컬럼
•
기타 등등
인덱스 코드
Clustered 인덱스 만들기
MySQL에서는 자동으로 각 테이블이 primary key (주로 id 컬럼)에 대한 clustered 인덱스가 만들어진다.
일반적으로 primary key를 사용한 조회를 많이 하기 때문.
primary key로 설정된 clustered 인덱스를 바꾸는 경우는 많지 않음.
하지만 만약 primary key가 아닌 다른 컬럼을 clustered 인덱스로 사용하고 싶을 때는:
1.
clustered 인덱스는 테이블당 하나씩 밖에 있을 수 없기 때문에, 먼저 기존 인덱스를 삭제한 후
2.
해당 코드를 사용해서 인덱스를 추가
CREATE CLUSTERED INDEX index_name ON table_name (column_name)
•
table_name 테이블에 column_name 컬럼에 대해서 index_name 이라는 이름의 clustered 인덱스를 만들어라"라는 내용.
Non-clustered 인덱스 만들기
Non-clustered 인덱스는 개수 상관없이 여러 개를 만들 수 있기 때문에 이미 있는 인덱스를 지울 필요가 없다. 그리고 clustered 인덱스를 만들 때와 거의 똑같은 코드를 사용해서 만들 수 있다.
그냥 키워드 CLUSTERED 만 빼주면 된다.
•
CREATE INDEX index_name ON table_name (column_name)
Composite 인덱스 만들기
Clustered/Non-clustered 인덱스 모두, 하나의 컬럼이 아니라, 여러 개의 컬럼에 대해서 composite 인덱스를 만들 수 있다.
composite 인덱스를 만들고 싶은 컬럼 이름들을 괄호 안에 모두 넣어주면 된다
•
CREATE INDEX index_name ON table_name (column_name_1, column_2, ...)
인덱스 확인하기
만약 테이블 안에 있는 모든 인덱스에 대한 정보를 확인하고 싶으면, 아래 코드를 사용해서 확인
•
SHOW INDEX FROM table_name;
보이는 것과 같이 인덱스의 이름, 컬럼들 다양한 내용을 확인.
인덱스 삭제하기
인덱스를 삭제하고 싶을 때는 아래의 코드를 사용.
•
DROP INDEX index_name ON table_name;
꼭 기억해야되는 점이 인덱스를 삭제하기 위해서는 항상 인덱스 이름을 알고 있어야 된다는 점
인덱스 이름을 까먹으셨다면 바로 위에 있는 SHOW INDEX 문을 사용해서 확인.
인덱스 사용하기
그렇다면 조회를 할 때 인덱스를 사용하고 싶을 때는 어떻게 할까?
인덱스가 있으나 없으나, 똑같이 SELECT 문을 사용해서 조회 가능.
DBMS가 알아서 인덱스를 사용할 수 있는 쿼리들에 대해서는 인덱스를 사용을 해주기 때문.