데이터베이스 index의 고찰

인덱스란 무엇이고 어떻게 생성되고 적용되는지 알아보자~🥹

평소에 SQL을 툴이나 DDL문으로 직접 실행할 때 인덱스를 정의하는 부분이 없었기 때문에

내가 인덱스를 쓰는지 안쓰는지 모를때가 있었다.😂

하지만 대부분 인덱스를 사용하고 있었다. 왜냐면 index란 기본적으로  PK컬럼 지정시 자동으로 생성된다.

PK가 없는 테이블은 없을것이다.😂

 

인덱스(Index) 란?

- 특정 열(또는 칼럼)의 값을 기반으로 데이터를 빠르게 찾을 수 있도록 도와주는 데이터 구조.

- 인덱스는 데이터베이스 성능을 향상시키기 위해 사용되며, 특히 대량의 데이터에서 특정 레코드를 빠르게 검색하고 정렬하는 데 도움이 된다고 한다.

출처

https://chartio.com/learn/databases/how-does-indexing-work/

How Does Indexing Work

 

CASE 1

CREATE TABLE INDEX_TEST (
  no NUMBER,
  name VARCHAR2(50),
  email VARCHAR2(50)
);

 

이 생성문에는 PK가 정의되어 있지 않기 때문에 index가없다.

 

 

CASE 2

CREATE TABLE INDEX_TEST(
    NO NUMBER, 
    NAME VARCHAR2(50 BYTE), 
    EMAIL VARCHAR2(50 BYTE),
    CONSTRAINT PK_INDEX_TEST PRIMARY KEY (NO)
)

이 생성문에는 PK컬럼을 지정하고 있다.

 

따로 나는 인덱스를 생성하지 않았지만 PK컬럼에 자동으로 부여됐다.

 

보통 게시판이나 회원정보 조회시 WHERE조건이 PK이기 때문에 (회원번호,게시판번호 등..) 우리의 SQL은 인덱스를 타고 있었다.

 

하지만 실무에서의 sql은 단순한 조건이 없다. 검색조건이 아주 많기 때문이다. 😂

 

일반적인 인덱스의 종류

1. 일반 인덱스 (INDEX):
   • 표준 인덱스로, 중복된 값 허용, NULL 값 허용.
   • 주로 WHERE 절에서 사용되는 열에 적합.
2. 유니크 인덱스 (UNIQUE INDEX):
   • 중복된 값을 허용하지 않음.
   • 주로 기본 키 또는 유니크 제약 조건을 위한 열에 사용.
3. 비트맵 인덱스 (BITMAP INDEX):
   • 대량의 중복 데이터가 있는 열에 유용.
   • 특히 카디널리티가 낮은 열에 효과적.
4. 함수 기반 인덱스 (FUNCTION-BASED INDEX):
   • 열의 변형된 값을 기반으로 인덱스를 생성.
   • 예를 들어, 열의 일부만 인덱싱하거나 특정 함수를 적용한 값을 인덱싱.
5. 클러스터 인덱스 (CLUSTER INDEX):
   • 테이블의 데이터를 물리적으로 클러스터링하여 인덱스를 생성.
   • 동일한 클러스터에 속하는 행들이 인접해 저장되어 성능 향상.

PK컬럼에 자동으로 생성된 인덱스는 유니크 인덱스다.

 

그럼 이제부터 TEST데이터 5,000건을 넣고 실행계획창을 한번 살펴보자~

 

※ TEST 데이터를 5,000건 넣는 PL/SQL

DECLARE
    v_cnt NUMBER := 0; --초기값 
BEGIN
    LOOP -- 반복문 선언
    EXIT WHEN v_cnt > 5000; -- 조건v_cnt가 501되면 loop종료, 이 조건은 인서트문 아래에 선언해도 상관없다.
    v_cnt := v_cnt+1; -- 증가치 1씩 증가,  이 조건은 인서트문 아래에 선언해도 상관없다.
    
    INSERT INTO INDEX_TEST(
        NO
        , NAME
        , EMAIL
    )VALUES(
        v_cnt
        , '이름'||to_char(v_cnt)
        , '이메일'||to_char(v_cnt)  
    );
    END LOOP;
END;

5,000건 삽입 

 

 

TEST 1. 검색조건에 PK컬럼을 EQUAL 조회하는 SQL을 실행

SELECT * FROM INDEX_TEST
WHERE NO = 354

OPTIONS > BY INDEX ROWID란 인덱스를 사용하여 특정 행을 직접 찾아가는 방법을 의미한다.

OPTIONS > UNIQUE SCAN이란 유니크한 값을 찾는데 최적화된 인덱스 액세스 방법

 

 

TEST 2. 검색조건에 일반컬럼을 EQUAL 조회하는 SQL을 실행

SELECT * FROM INDEX_TEST
WHERE NAME = '이름354'

OPTIONS > FULL 은 전체 테이블 스캔을 의미한다. 인덱스를 사용하지 않고 전체 테이블을 순차적으로 스캔하여 필요한 행을 찾아간다.

 

 

TEST 3. 검색조건에 PK컬럼을 LIKE 조회하는 SQL을 실행

SELECT * FROM INDEX_TEST
WHERE NO LIKE '%354%'

EQUAL이 아닌 LIKE는 인덱스로 지정한 컬럼도 FULL스캔을 탄다.

인덱스를 사용할 수 없어 추가로 붙은 필터 조건식을 보면 숫자형 컬럼을 TO_CHAR로 변환하는 함수가 추가됐다.

숫자형을 LIKE로 조회하는건 절대 하지 말아야한다 🤣🤣

 

 

TEST 4. 검색조건에 일반컬럼을 LIKE 조회하는 SQL을 실행

SELECT * FROM INDEX_TEST
WHERE NAME LIKE '%이름4000%'

추가로 붙은 필터 조건식을 보면 IS NOT NULL 조건이 붙었다.

 

 

NAME 컬럼에 일반 인덱스를 추가하여 실행해보자

CREATE INDEX IDX_NAME ON INDEX_TEST(NAME);

ㅌ

테이블 정보를 보면 인덱스가 신규로 추가됐다.

 

TEST 5. 검색조건에 일반인덱스컬럼을 EQUAL 조회하는 SQL을 실행

SELECT * FROM INDEX_TEST
WHERE NAME = '이름4000'

OPTIONS > BY INDEX ROWID란 인덱스를 사용하여 특정 행을 직접 찾아가는 방법을 의미한다.

OPTIONS > RANGE SCAN이란 해당 값과 연관된 범위를 인덱스에서 스캔하여 검색한다는 의미

 

==========

정리하자면..

1. WHERE절 Equality Condition 사용

- WHERE column_name = 'some_value'; 와 같은 경우

2. 조인 조건에 사용

- 조인 연산에서 조인 조건에 사용되는 열에 인덱스가 있다면, 조인 성능이 향상된다.

- ON table1.column_name = table2.column_name;과 같은 경우

3. ORDER BY 및 GROUP BY에서 사용

- 해당 열에 인덱스가 있으면 정렬 작업의 효율이 향상

- ORDER BY column_name; 또는 GROUP BY column_name;과 같은 경우

4. 부등호(<, >, <=, >=)를 사용하는 범위 조건

- WHERE column_name > 100 AND column_name < 200;과 같은 경우

5. 유니크 또는 프라이머리 키 열

- 유니크 또는 프라이머리 키 열에 대한 인덱스는 특히 레코드의 고유성을 유지하거나 레코드를 빠르게 찾을 때 유용

6. 조건을 포함한 부분 검색

- 특정 문자열 패턴이 포함된 레코드를 찾을 때 LIKE 'pattern%'와 같은 부분 검색에 대한 조건에서 인덱스를 사용하면 성능이 향상

7. 데이터의 카디널리티가 높은 열

8.집계 함수와 함께 사용

MIN, MAX, COUNT, SUM 등  집계 함수의 인자로 사용되는 열에 인덱스를 생성하면 집계 연산의 성능이 향상