SQL | 인덱스 살펴보기

2023-02-12 Comments

SQL에서 사용되는 인덱스에 대해 정리하는 글입니다.

데이터베이스와 SQL
SQL 미리 맛보기
SQL 기본 문법
SQL 고급 문법
테이블과 뷰
인덱스 ⬅
스토어드 프로시져
SQL과 파이썬 연결

인덱스 개념

인덱스는 원하는 위치를 빠르게 탐색할 수 있습니다. 책을 예로 들면 찾아보기와 같습니다.
하지만 문제점도 존재합니다. 예를 들어, SELECT는 SQL 책 전반에 존재하는데, 찾아보기에 모든 위치를 표시한다면 비효율 적일 수 있습니다.
따라서, 입력될 데이터의 형태와 구조를 파악하여 인덱스를 필요한 곳에 사용할 수 있어야합니다.

장점

SELECT 문으로 검색하는 속도가 빨라짐.
컴퓨팅 리소스가 줄어듦.

단점

추가적인 공간을 차지함. (테이블 크기의 약 10%정도)
처음 생성시 시간이 오래 걸림.
테이블의 데이터가 변경되면 성능이 나빠질 수 있음.

인덱스 종류

클러스터형 인덱스^{clustered index}
보조 인덱스^{secondary index}

인덱스 확인은 SHOW INDEX FROM [테이블 명]; 으로 가능합니다. 간단한 예를 보겠습니다.

CREATE TABLE table1 (
    col1 INT PRIMARY KEY,
    col2 INT UNIQUE,
    col3 INT
);
SHOW INDEX FROM table1;

Key_name: PRIMARY는 자동으로 생성된 인덱스 라는 의미이고, 클러스터형 인덱스입니다.
Column_name: col1 열에 인덱스가 생성되어 있습니다.
Non_unique: 고유하지 않음을 0 (False), 1 (True)로 나타냅니다.

PRIMARY KEY

PRIMARY KEY 가 지정되면 해당 열에 자동으로 인덱스가 생성되며, 클러스터형 인덱스 입니다.
자동으로 순서대로 정렬됩니다. (중간에 row가 추가되어도요)
PRIMARY KEY가 변경되면 인덱스를 다시 생성합니다. 데이터 양에 따라 소요시간이 길어질 수 있습니다.
기본적으로 UNIQUE입니다. (중복되지 않습니다)

UNIQUE

UNIQUE로 지정하면 값이 중복되지 않고 보조 인덱스로 지정됩니다.
별도 공간에 인덱스를 생성합니다.
데이터는 바뀌지 않습니다.

인덱스 내부 작동

클러스터형 인덱스가 빠르게 찾는 원리에 대해 알아봅시다.

예를 들어, 아래에 정렬된 데이터의 배열이 있습니다.

배열에서 7이라는 숫자를 찾으려면 어떻게 해야할까요?

우리는 그림으로 그려서 딱 보이지만 컴퓨터는 배열내 원소를 바로 찾지 못합니다. 각 데이터를 조회 해야합니다.

가장 쉬운 방법은 1부터 하나씩 하나씩 조회해 보는 것입니다.

1, 2, 3 , .. , 7 이런 식으로요. 7번의 조회를 해야합니다.

수행 횟수는 Big O 개념으로 시간 복잡도로 나타낼 수 있고, 여기서는 $ O(7) $ 입니다.

하지만 이런식으로 찾으면 어떨까요?

7을 찾았습니다. 7번 조회해야하는 일을 3번만에 수행하였습니다. -> $ O(3) $

이것이 가능한 이유는 배열이 정렬되어 있었고, 가운데 원소의 위치를 미리 알고 있었기 때문입니다.

인덱스로 지정된 컬럼이 자동으로 정렬되는 이유도 같습니다.

클러스터형 인덱스, 보조 인덱스는 모두 균형 트리^{Balanced Tree, B-Tree}로 만들어집니다.

균형트리는 위 그림의 배열을 트리 형태로 만든 것입니다.

SQL에서는 조금 형태가 다릅니다. 아래 그림을 보시죠.

가장 처음은 루트^Root, 중간^Internal, 리프^Leaf 노드로 나누어집니다.

트리의 노드는 MySQL에서는 페이지 (page) 라고 부릅니다.

페이지에는 여러개의 값이 들어 갈 수 있습니다. 하지만 원리는 바로 전에 보여드린 그림과 비슷합니다.

7을 찾는 과정은 아래와 같습니다.

루트 페이지의 1 -> 루트 페이지의 7 -> 중간 페이지의 7 -> 리프 페이지의 7

4번의 조회로 찾았고, 페이지는 3번 조회를 하였습니다. SQL에서 데이터의 수를 조회하는 것보다 페이지의 조회 횟수로 효율성을 판단합니다.

그리고, 값이 추가된다면 페이지가 새로 생성될 수도 있습니다. 15가 추가된다면, 중간 페이지 내 12의 하위 페이지에는 자리가 없으므로 중간 페이지에 15가 추가되고 리프 페이지가 새로 생성됩니다.

보조 인덱스 (Secondary Index)는 데이터를 변경하지 않고, 데이터의 위치를 알려주는 역할을 합니다.

하지만, 보조 인덱스의 인덱스 페이지 내에서는 데이터가 정렬되어 있습니다. 그리고 말단 노드에는 데이터의 위치가 페이지 번호+#위치 로 나타납니다. 책 부록의 찾아보기와 같습니다.

위치만 나타나 있으므로, 보조 인덱스를 활용하면 데이터에 두 번 접근하게됩니다. 하지만 그냥 찾을때 보다는 훨씬 빠릅니다.

추가로,

페이지는 하나당 16KB의 공간이 필요합니다.
Primary Key로 데이터를 조회하면 소요되는 비용은 $ O(log{N}) $입니다.
보조 인덱스로 데이터를 조회하면 소요되는 비용은 $ O(2*log{N}) $ 입니다.

인덱스 실제 사용

인덱스 생성

테이블을 생성할 때, 인덱스를 설정할 수 있지만 따로 설정할 수 있습니다.

기본 문법은 아래와 같습니다.

1 2	CREATE [UNIQUE] INDEX 인덱스_이름 ON 테이블_이름 (열_이름) [ASC \| DESC]

인덱스 제거

1	DROP INDEX 인덱스_이름 ON 테이블_이름

실습

member 테이블의 형태는 다음과 같습니다.

1	SELECT * FROM member;

member 테이블의 인덱스는 다음과 같이 구성되어 있습니다. 최초 생성때 mem_id에 PRIMARY KEY를 지정하여 PRIMARY로 지정되어 있습니다.

1	SHOW INDEX FROM member;

테이블의 STATUS는 다음처름 확인할 수 있습니다.

페이지는 하나당 16KB를 차지하므로 Data_length에 16384 가 지정되어 있습니다.
Index_length는 보조 인덱스의 크기입니다. 아직 보조인덱스가 없으므로 크기가 0입니다.

1	SHOW TABLE STATUS LIKE 'member';

addr 열을 보조인덱스로 지정해봅시다.여기서는 중복을 허용하는 단순 보조 인덱스를 추가하겠습니다.

1 2	CREATE INDEX idex_member_addr ON member (addr);

⭐️ 생성한 인덱스를 적용시키려면 ANALYZE TABLE을 이용해 테이블을 분석/처리 해주어야 합니다.

아래 결과에서 Index_length에 1페이지 크기가 늘어난 것을 확인할 수 있습니다. 보조 인덱스가 1개이므로 1페이지가 생성되었습니다.

1 2	ANALYZE TABLE member; SHOW TABLE STATUS LIKE 'member';

참고

MySQL에서는 Executation Plan 탭이 실행결과 오른쪽에 위치해 있습니다. 테이블 검색의 비용을 확인 할 수 있습니다.
SELECT로만 조회하면 인덱스를 사용하지 않습니다. WHERE같은 조건이 입력되어야 인덱스를 사용합니다.
WHERE 에 연산이 추가되면 인덱스를 사용하지 않습니다. 예, WHERE mem_number*2 >= 14

인덱스를 효과적으로 사용하는 방법

인덱스는 열 단위에 생성됩니다.
WHERE 절에서 사용되는 열에 인덱스를 만들어야합니다.
WHERE 절에 사용되더라도 자주 사용되어야 가치가 있습니다.
데이터의 중복이 많은 열은 인덱스를 만들어도 효과가 없습니다.
클러스터형 인덱스는 테이블당 하나만 생성할 수 있습니다.
사용하지 않는 인덱스는 제거해야 합니다.

오늘은 SQL의 핵심 개체인 인덱스에 대해 정리하였습니다.

다음에는 Stored Procedure에 대해 정리하고, 파이썬과 연동을 해보겠습니다.

읽어주셔서 감사합니다 👋