본문 바로가기
SQL

[SQL] 인덱스

쌈뽕코딩 2024. 6. 5. 00:16

목차

  • 인덱스의 종류
  • 인덱스의 장단점
  • 클러스터형 인덱스
  • 보조 인덱스
  • 인덱스의 내부 작동
  • 인덱스의 구조
  • 인덱스의 활용
  • 인덱스를 효과적으로 사용하는 방법

 

인덱스

인덱스는 데이터를 빠르게 찾을 수 있는 도구이다.

 

인덱스의 종류

 

  • 클러스터형 인덱스
    • 기본 키를 생성하면 자동 생성
    • 테이블에 1개만 생성가능
    • 기본 키를 지정한 열을 기준으로 자동정렬
  • 보조 인덱스
    • 고유 키로 지정하면 자동 생성
    • 여러 개 만들수 있음
    • 자동 정렬 X

 

인덱스의 장단점

  • 장점
    • SELECT 문으로 검색하는 속도가 매우 빨라진다.
    • 그 결과 컴퓨터의 부담이 줄어들어 결국 전체 시스템의 성능이 향상된다.
  • 단점
    • 인덱스도 공간을 차지해서 데이터베이스 안에 추가적인 공간이 필요하다.
    • 보통 테이블의 10% 정도 된다.
    • 처음 인덱스를 만드는 데 시간이 오래 걸릴 수 있다.
    • SELECT 가 아닌 변경작업(INSERT, UPDATE, DELETE)이 자주 일어나면 성능이 나빠질 수 있다.

 

인덱스 확인

기본 키를 지정하면 자동적으로 클러스터형 인덱스가 만들어진다고 했다.

테이블을 생성하고 인덱스를 확인해보자.

CREATE TABLE table1 (
    col1 INT PRIMARY KEY,
    col2 INT,
    col3 INT
);

SHOW INDEX FROM table1;

 

  • KEY_name을 보면 PRIMARY 라고 써져있다.
  • 이것은 기본 키로 설정해서 '자동으로 생성된 인덱스' 라는 의미이다.
  • 이것이 클러스터형 인덱스이다.
  • Column_name이 col1 로 설정되어 있다는 것은 col1에 인덱스가 만들어져 있다는 의미이다.
  • Non_unique는 '고유하지 않다' 라는 뜻이다.
  • unique가 0이라면 False, 1이라면 True의 의미이다.

 

참고: 고유 인덱스(Unique Index)
고유 인덱스는 인덱스의 값이 중복되지 않는다는 의미이고,
단순 인덱스는 인덱스의 값이 중복되어도 된다는 뜻이다.
기본 키나 고유 키로 지정하면 값이 중복되지 않으므로 고유 인덱스가 생성된다.
그 외에 인덱스는 단순 인덱스로 지정한다.

 

 

기본 키에 더불어 고유 키도 인덱스가 자동으로 생성된다.

고유 키로 생성된 인덱스는 보조 인덱스이다.

CREATE TABLE table2 (
    col1 INT PRIMARY KEY,
    col2 INT UNIQUE,
    col3 INT UNIQUE 
);

SHOW INDEX FROM table2;

  • Key_name 에 열 이름이 들어가있다면 보조 인덱스라고 보면 된다.
  • 고유 키 역시 중복을 허용하지 않기 때문에 0으로 되어있다.
  • 보조 인덱스는 여러 개 만들 수 있다.

 

자동으로 정렬되는 클러스터형 인덱스

 

클러스터형 인덱스(Clustered Index)는 기본 키로 지정하면 자동 생성된다.

그리고 테이블에 1개만 생성된다.

어떤 열을 기본 키로 지정하면(클러스터 형 인덱스가 생성되면) 그 열을 기준으로 자동 정렬된다.

CREATE TABLE member (
    mem_id CHAR(8),
    mem_name VARCHAR(10),
    mem_number INT,
    addr CHAR(2)
);
INSERT INTO member VALUES('TWC', '트와이스', 9, '서울');
INSERT INTO member VALUES('BLK', '블랙핑크', 4, '경남');
INSERT INTO member VALUES('WMN', '여자친구', 6, '경기');
INSERT INTO member VALUES('OMY', '오마이걸', 7, '서울');

SELECT * FROM member;

 

이제 mem_id 에 기본 키 제약을 줘보고 다시 검색해보자!

ALTER TABLE member 
    ADD CONSTRAINT
	PRIMARY KEY(mem_id);
    
SELECT * FROM member;

 

  • 아이디 기준으로 정렬 순서가 바뀌었다.
  • 클러스터형 인덱스가 생성되어 mem_id을 기준으로 정렬한 것!

 

당연하지만 기본 키를 다른 열에 지정한다면 바뀌게 된 열을 기준으로 정렬을 한다.

ALTER TABLE member DROP PRIMARY KEY;

ALTER TABLE member 
    ADD CONSTRAINT 
        PRIMARY KEY(mem_name);
        
SELECT * FROM member;

  • mem_name 을 기준으로 다시 정렬되었다.

 

데이터를 추가해도 알아서 기준에 맞춰 재정렬 된다.

INSERT INTO member VALUES ('GRL', '소녀시대', 8, '서울');
SELECT * FROM member;

  • 대용량의 데이터에 기본 키를 지정하는 것은 엄청 오랜 시간이 걸릴 수 있다.
  • 또한, 회원 이름처럼 중복될 수 있는 열에 기본 키를 설정한 것은 논리적으로 위험하다.

 

 

정렬되지 않은 보조 인덱스

  • 고유 키로 지정하면 보조 인덱스(Secondary Index)가 생성된다.
  • 보조 인덱스는 테이블에 여러 개 설정할 수 있다.
  • 고유 키를 테이블에 여러 개 지정할 수 있는 것과 마찬가지이다.

mem_id 열에 고유 키를 지정해보자.

ALTER TABLE member 
    ADD CONSTRAINT
        UNIQUE(mem_id);
        
SELECT * FROM member;

  • 보조 인덱스를 생성해도 데이터의 내용이나 순서는 변경되지 않는다.

 

mem_name에 추가로 고유 키를 지정해보자.

ALTER TABLE member 
    ADD CONSTRAINT
        UNIQUE(mem_name);
        
SELECT * FROM member;

  • 결과를 보면 데이터의 내용은 역시 그대로 이다.
  • mem_id, mem_name 열에 모두 보조 인덱스가 생성된 상태

 

데이터를 추가해보면 어떨까?

INSERT INTO member VALUES ('GRL', '소녀시대', 8, '서울');
SELECT * FROM member;

  • 기존처럼 제일 뒤에 추가되는 것과 동일하다.

 

보조 인덱스는 여러 개 만들 수 있다.

하지만 보조 인덱스를 만들 때마다 데이터베이스 공간을 차지하게 되고,

전반적으로 시스템에 오히려 나쁜 영향을 미치게 된다.

그러므로 꼭 필요한 열에만 적절히 보조 인덱스를 생성하는 것이 좋다.

 

 

 

인덱스의 내부 작동

 

클러스터형 인덱스와 보조 인덱스는 모두 내부적으로 균형 트리로 만들어진다.

인덱스의 내부 작동 원리를 이해한다면, 인덱스를 사용해야 할 경우와 말아야 할 경우를 선택하는데 도움된다.

 

균형 트리의 개념

  • 균형 트리 구조에서 데이터가 저장되는 공간을 노드라고 한다.
  • MySQL 에서는 노드를 페이지라 읽는다.
  • 페이지는 최소한의 저장 단위로, 16Kbyte(16384byte)를 가진다.
  • 루트 노드는 가장 상위 노드를 말한다.
  • 리프 노드는 제일 마지막에 존재하는 노드를 말한다.
  • 루트 노드와 리프 노드의 중간에 끼인 노드들은 중간 노드라고 부른다.
  • 균형 트리는 데이터를 검색할 때 아주 뛰어난 성능을 발휘한다.

 

 

 

만약 아래 그림에서 MMM 이라는 데이터를 검색한다고 생각해보자.

  • 모두 리프 페이지만 있으므로 MMM을 찾는 방법은 처음부터 검색하는 방법밖에 없다.
  • AAA 부터 MMM 까지 8건의 데이터(페이지는 3개)를 검색해야 그 결과를 알 수 있다.

데이터를 처음부터 끝까지 검색하는 것을 전체 테이블 검색(Full Table Scan) 이라고 부른다.

 

 

 

이번에는 균형 트리에서 검색해보자.

  • 균형 트리는 무조건 루트 페이지부터 검색한다.
  • 모든 데이터는 정렬되어 있고 MMM은 AAA, FFF, LLL 3개를 모두 읽은 다음 나오므로
  • 세번 째 페이지로 직접 이동하면 된다. 세번 째 리프 페이지에서 LLL, MMM 2개를 읽어 MMM을 찾았다.
  • 루트 페이지에서, AAA, BBB, DDD 세번 째 리프 페이지에서 LLL, MMM 을 읽어 총 5건의 데이터를 검색해서 찾았다.

위에서 균형 트리가 아닌 구조에서는 3페이지를 읽었지만, 균형 트리에서는 2페이지만 읽어서 결과를 얻을 수 있었다.

데이터 건수는 별 의미가 없다. 몇 개의 페이지를 읽었냐에 따라 효율성을 판단할 수 있다.

 

 

균형 트리의 페이지 분할

인덱스를 구성하면 데이터 변경 작업(INSERT, UPDATE, DELETE) 시 성능이 나빠진다.

이유는 페이지 분할이라는 작업이 발생하기 때문이다.

페이지 분할이란 새로운 페이지를 준비해서 데이터를 나누는 작업을 말한다.

페이지 분할이 일어나면 MySQL이 느려지고, 너무 자주 일어난다면 성능에 큰 영향을 준다.

 

앞에 있는 그림에 데이터 III 데이터가 새로 INSERT 되었다고 가정해보자.

균형 트리는 다음과 같이 변경될 것이다.

 

두 번째 리프 페이지에는 정렬되어야 하기 때문에 JJJ을 한 칸 이동되고 III가 그 자리에 삽입됬다.

정렬되어야 하기 때문에 JJJ가 한 칸 이동했을 뿐 큰 변화는 일어나지 않았다.

즉, III를 입력하는 작업은 순식간에 일어난다는 것이다.

 

하지만 GGG를 입력해보자.

그런데 두 번째 리프 페이지에서는 더 이상 빈 공간이 없다. 

이럴때 페이지 분할이 발생한다.

위와 같이 데이터를 1개 밖에 추가하지 않았는데 많은 변화가 일어났다.

우선 새 페이지를 확보한 후 페이지 분할 작업이 1회 일어났고,

루트 페이지에서도 새로 등록된 페이지의 제일 위에 있는 데이터 III가 등록되었다.

 

이번에는 PPP와 QQQ 2개를 연속해서 입력해보겠다.

 

  1. PPP를 입력하면 네 번째 리프 페이지에 빈칸이 있으므로 제일 마지막에 추가된다. 별일이 일어나지 않는다.
  2. QQQ를 입력하면 네 번째 페이지에 빈 칸이 없으므로 페이지 분할 작업이 일어난다.
  3. 페이지 분할 후에 추가된 다섯 번째 리프 페이지를 루트페이지에 등록하려고 하니, 루트 페이지도 이미 꽉찼다.
  4. 그래서 루트 페이지도 다시 페이지 분할 작업을 해야 한다.
  5. 그리고 원래 루트 페이지가 있던 곳은 2개의 페이지가 되어서 더 이상 루트 페이지가 아니라 중간 페이지가 된다.
  6. 마지막으로 새 페이지를 준비해서 중간 노드를 가르키는 새로운 루트 페이지로 구성된다.

결국 QQQ 하나를 입력하기 위해서 3개의 새로운 페이지가 할당되고 2회의 페이지 분할이 되었다.

데이터 하나를 입력하기 위해 너무 많은 일이 일어난 것이다. 

이 예를 통해 인덱스를 구성하면 데이터 변경(특히 INSERT) 작업이 느려지는지 확인할 수 있다.

 

 

 

인덱스의 구조

 

인덱스 구조를 통해 인덱스를 생성하면 왜 데이터가 정렬되는지, 어떤 인덱스가 효율적인지 보자.

 

클러스터형 인덱스 구성하기

CREATE TABLE cluster (
    mem_id CHAR(8),
    mem_name VARCHAR(10)
);

INSERT INTO cluster VALUES ('TWC', '트와이스');
INSERT INTO cluster VALUES ('BLK', '블랙핑크');
INSERT INTO cluster VALUES ('WHN', '여자친구');
INSERT INTO cluster VALUES ('OMY', '오마이걸');
INSERT INTO cluster VALUES ('GRL', '소녀시대');
INSERT INTO cluster VALUES ('ITZ', '잇지');
INSERT INTO cluster VALUES ('RED', '레드벨벳');
INSERT INTO cluster VALUES ('APN', '에이핑크');
INSERT INTO cluster VALUES ('SPC', '우주소녀');
INSERT INTO cluster VALUES ('MMU', '마마무');

 

1페이지에 4개의 행이 입력된다고 가정해보자.

아직은 인덱스가 없는 상태로 각 페이지 위에 쓰인 숫자는 페이지 번호를 임의로 부여.

 

정렬된 순서를 보자. 위와 똑같다.

SELECT * FROM cluster;

 

이제 mem_id 에 클러스터형 인덱스를 구성해보자.

ALTER TABLE cluster
    ADD CONSTRAINT
        PRIMARY KEY(mem_id);

  • 결과를 보면 mem_id를 기준으로 오름차순 정렬되었다.
  • mem_id를 기본 키로 지정해 클러스터형 인덱스가 생겨서 그렇다.

 

 

 

  • 실제 데이터는 데이터 페이지가 정렬되고 균형 트리 형태의 인덱스가 형성된다.
  • 먼저 클러스터형 인덱스를 구성하기 위해 행 데이터가 지정한 열로 정렬한다.
  • 그리고 각 페이지의 인덱스로 저장된 열의 첫 번째 값을 가지고 루트 페이지를 만든다.
  • 클러스터형 인덱스는 루트 페이지, 리프 페이지(중간 페이지가 있다면 중간 페이지 포함) 으로 구성되어 있다.
  • 인덱스 페이지의 리프 페이지는 데이터 그 자체이다.

 

보조 인덱스 구성하기

이번에는 동일한 데이터로 보조 테이블을 만들어보자.

CREATE TABLE second (
    mem_id CHAR(8),
    mem_name VARCHAR(10)
);

INSERT INTO second VALUES ('TWC', '트와이스');
INSERT INTO second VALUES ('BLK', '블랙핑크');
INSERT INTO second VALUES ('WHN', '여자친구');
INSERT INTO second VALUES ('OMY', '오마이걸');
INSERT INTO second VALUES ('GRL', '소녀시대');
INSERT INTO second VALUES ('ITZ', '잇지');
INSERT INTO second VALUES ('RED', '레드벨벳');
INSERT INTO second VALUES ('APN', '에이핑크');
INSERT INTO second VALUES ('SPC', '우주소녀');
INSERT INTO second VALUES ('MMU', '마마무');
  • 마찬가지로 인덱스가 없으므로 순서대로 데이터가 입력된다.

 

 

mem_id 에 UNIQUE를 지정해 보조 인덱스를 생성하자.

ALTER TABLE second
    ADD CONSTRAINT
        UNIQUE(mem_id);

SELECT * FROM second;

 

  • 보조 인덱스가 생성되었는데도 입력한 것과 순서가 똑같다.

 

내부적으로는 이렇게 구성되어 있다.

 

  • 보조 인덱스는 데이터 페이지를 건드리지 않았다.
  • 그리고 별도의 장소에 인덱스 페이지를 생성했다.
  • 보조 인덱스를 생성해도 데이터 페이지는 변경되지 않는다.
  • 책의 뒷부분 등 별도의 공간에 찾아보기가 만들어진 것처럼 보조 인덱스가 별도에 공간에 만들어졌다.
  1. 인덱스 페이지의 리프 페이지에 인덱스로 구성한 열(mem_id)를 정렬한다.
  2. 그리고 실제 데이터가 있는 위치를 준비한다.
  3. 데이터의 위치는 페이지 번호 + #위치로 기록되어 있다.
  4. APN은 1001번 페이지의 네 번째(#4)에 데이터가 있다.

 

 

인덱스에서 데이터 검색하기 

 

먼저 클러스터형 인덱스에서 데이터를 검색(SELECT) 해보자.

회원의 이름이 SPC 인 회원을 검색한다면 몇 개 페이지를 읽어야 알 수 있을까?

 

  • 클러스터형 인덱스에서는 SPC 회원의 이름을 알아내기 위해 루트 페이지(100번) 와 리프 페이지(1001 번)를 읽었다.
  • 결국 총 2개의 페이지를 읽어서 SPC 의 이름을 알아낸 것!

이번엔 보조 인덱스에서 SPC 회원의 이름을 읽어보자.

 

  • 인덱스 페이지의 루트 페이지(10번), 리프 페이지(200번), 데이터 페이지(1002번) 를 읽었다.
  • 결국 총 3번의 페이지를 읽어서 SPC의 이름을 알아낸 것!

인덱스 검색을 통해 클러스터형 인덱스는 2페이지만 읽어서 원하는 결과를 검색했고,

보조 인덱스는 3페이지를 읽어서 원하는 결과를 검색했다.

두 인덱스 모두 빠르긴 하지만 클러스터형 인덱스가 조금 더 빠르다.

 

 

 

인덱스의 실제 사용

 

인덱스 생성

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

 

인덱스 제거

DROP INDEX 인덱스_이름 ON 테이블_이름
  • 보조 인덱스는 데이터의 중복 여부에 따라 단순 보조 인덱스와 고유 보조 인덱스로 나눈다.

 

인덱스 생성과 문법

테이블을 생성할 때 특정 열을 기본 키, 고유 키로 설정하면 인덱스가 자동 생성된다는 것은 확인했다.

그 외에 직접 인덱스를 생성하려면 CREATE INDEX 문을 사용해야 한다.

CREATE INDEX로 생성되는 인덱스는 보조 인덱스이다.

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

 

  • UNIQUE는 중복이 안 되는 고유 인덱스를 만드는 것인데, 생략하면 중복이 허용된다.
  • CREATE INDEX로 인덱스를 생성하려면 기존에 입력된 값들에 중복이 있으면 안된다.
  • 그리고 인덱스를 생성한 후에 입력되는 데이터와도 중복될 수 없으니 신중해야 한다.
  • ASC 또는 DESC는 인덱스를 오름차순 또는 내림차순으로 만들어준다.

 

인덱스 제거 문법

 

CREATE로 생성한 인덱스는 DROP INDEX로 제거한다.

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

 

주의할 점

  • 기본 키, 고유 키로 자동 생성된 인덱스는 DROP INDEX로 제거하지 못한다.
  • ALTER TABLE로 기본 키나 고유 키를 제거하면 자동으로 생성된 인덱스도 제거할 수 있다.
  • 하나의 테이블에 클러스터형 인덱스와 보조 인덱스가 모두 있는 경우, 인덱스를 제거할 때는 보조 인덱스 먼저 제거하는 것이 좋다.

 

예제

SHOW INDEX FROM member; -- 어떤 INDEX가 있는지 확인

클러스터형 인덱스 1개

 

SHOW TABLE STATUS LIKE 'member'; -- 인덱스의 크기 확인

  • Data_length는 클러스터형 인덱스(또는 데이터)의 크기를 Byte 단위로 표현한 것
  • MySQL의 1페이지 크기는 기본적으로 16KB이므로 클러스터형 인덱스는 16384 / (16*1024) = 1페이지가 할당
  • 실제로는 데이터의 내용이 많지 않아서 16KB까지 필요없지만, 최소 단위가 1페이지
  • Index_length는 보조 인덱스의 크기, 현재 member 테이블에는 보조 키가 없으므로 표기되지 않음

 

주소(addr)에 중복을 허용하는 단순 보조 인덱스를 생성, 인덱스의 이름은 idx_member_addr

CREATE INDEX idx_member_addr
    ON member(addr);

 

인덱스 확인하기

SHOW INDEX FROM member; -- 어떤 INDEX가 있는지 확인

  • Key_name 에서 지금 생성한 단순 보조 인덱스의 이름이 확인된다.
  • Column_name 에서 어느 열에 지정되었는지 확인된다.
  • Non_unique가 1로 설정되어 있으므로 고유 보조 인덱스가 아니라는 것, 중복된 데이터를 허용한다.

 

보조 인덱스가 생성되었으니 인덱스 크기를 확인해보자.

SHOW TABLE STATUS LIKE 'member'; -- 인덱스의 크기 확인

  • Index_length 부분이 보조 인덱스의 크기인데, 이상하게도 크기가 0으로 나왔다.

 

 

생성한 인덱스를 실제로 적용시키려면 ANALYZE TABLE 문으로 먼저 테이블을 분석/처리 해줘야한다.

ANALYZE TABLE member; -- 테이블을 분석/처리
SHOW TABLE STATUS LIKE 'member';

 

 

 

이번에는 인원 수(mem_number)에 중복을 허용하지 않는 고유 보조 인덱스를 생성해보자.

CREATE INDEX idx_member_mem_number
    ON member(mem_number);

오류 발생

  • 블랙핑크, 마마무, 레드벨벳의 인원수가 4이기에 이미 중복된 값이 있어 고유 보조 인덱스를 생성할 수 없다.

 

그렇다면 회원 이름(mem_name)에 고유 보조 인덱스를 생성해보자.

CREATE UNIQUE INDEX idx_member_mem_name
    ON member(mem_name);
    
SHOW INDEX FROM member; -- 어떤 INDEX가 있는지 확인

 

 

기본 키는 다르고 똑같은 회원 이름을 데이터 추가해보겠다.

INSERT INTO member VALUES ('MOO', '마마무', 2, '태국', '001', '12341234', 155, '2020.10.10');

  • 고유 인덱스를 생성하면 이후로는 중복된 값이 입력되지 않는다.
  • 그러므로 단순, 고유 보조 인덱스를 생성할 때 현재 중복된 값이 없다고 무조건 설정하면 안된다.
  • 업무상 절대로 중복되지 않는 열(전화번호, 주민등록번호, 이메일)에만 UNIQUE 옵션을 사용해서 인덱스를 생성하자.
  • 중복된 데이터가 많은 열에 인덱스를 생성하는 것은 의미도 없고 성능만 나빠진다. (여자/남자)

 

인덱스 활용

 

1. 먼저 지금까지 만든 인덱스가 어느 열에 있는지 확인해보자.

  • 현재 회원 아이디(mem_id), 회원 이름(mem_name), 주소(addr) 열에 인덱스가 생성되어있다.

 

2. 이번에는 전체를 테이블을 조회해보자.

SELECT * FROM member;
  • 그런데 이 SQL은 인덱스와 아무런 상관이 없다.
  • 인덱스를 사용하려면 인덱스가 생성된 열 이름이 SQL 문에 있어야 한다.
  • 인덱스를 사용했는지 여부는 결과 중 [Execution Plan] 창을 확인하면 된다.
  • 전체 테이블 검색(Full Table Scan) 을 한 것이 확인되었다.

 

 

3. 그렇다면 이번에는 인덱스가 있는 열을 조회해보자.

SELECT mem_id, mem_name, addr FROM member;

 

  • 엥? 열 이름이 SELECT 다음에 나와도 인덱스를 사용하지 않는다.

 

 

4. 이번에는 인덱스가 생성된 mem_name 값이 '에이핑크' 인 행을 조회해보자.

SELECT mem_id, mem_name, addr FROM member WHERE mem_name = '에이핑크';

  • Single Row(constant) 라고 되어 있다.
  • 이 용어는 인덱스를 사용해서 결과를 얻었다는 의미이다.
  • 즉, WHERE 절에 열 이름이 들어가있어야 인덱스를 사용한다.

 

5. 이번에는 숫자의 범위로 조회해보자.

먼저 숫자로 구성된 인원 수(mem_number)로 단순 보조 인덱스를 만들어보자.

CREATE INDEX idx_member_mem_number
    ON member(mem_number);

ANALYZE TABLE member; -- 인덱스 적용

SELECT mem_name, mem_number
    FROM member
    WHERE mem_number >= 7;

  • mem_number >= 7과 같이 숫자의 범위로 조회하는 것도 인덱스를 사용한다.

 

 

 

인덱스를 사용하지 않을 때

 

인덱스가 있고 WHERE 절에 열 이름이 나와도 인덱스를 사용하지 않는 경우가 있다.

  • 대부분의 행을 가져올 때
  • WHERE 문에서 열에 연산이 가해질 때

 

 

1. 대부분의 행을 가져올 때

 

인원 수가 1명 이상인 회원을 조회해보자. 회원은 1명 이상이므로 10건 모두 조회된다.

SELECT mem_name, mem_number
    FROM member
    WHERE mem_number >= 1;

 

전체 테이블을 검색했다.

 

앞에서 7명 이상일 때는 틀림없이 인덱스를 사용했는데 왜 전체 테이블을 검색했을까?

=> MySQL이 인덱스 검색보다는 전체 테이블 검색이 낫겠다고 판단했기 때문이다.

  • 이 경우에는 대부분의 행을 가져와야 하므로 인덱스를 왔다 갔다 하는 것보다는 차례대로 읽는 것이 효율적
  • 인덱스가 있어도 MySQL이 판단해서 사용하지 않을 수 있다.

 

 

2. WHERE 문에서 열에 연산이 가해질 때

SELECT mem_name, mem_number
    FROM member
    WHERE mem_number * 2 >= 14;

 

이런 경우에는 다음과 같이 수정해주면 인덱스를 사용할 수 있다.

SELECT mem_name, mem_number
    FROM member
    WHERE mem_number >= 14 / 2;

 

 

 

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

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

 

 

해당 포스팅의 글과 내용은 우재남 저자님의 혼자 공부하는 SQL 책을 재구성하여 만들었습니다.

좀 더 자세한 내용이 필요하시다면 '혼자 공부하는 SQL 책'을 구매해주세요

우재남 저자님 카페: https://cafe.naver.com/thisisMySQL

 

이것이MySQL이다 : 네이버 카페

한빛미디어 [이것이 MySQL이다] 카페입니다.

cafe.naver.com

 

'SQL' 카테고리의 다른 글

[SQL] 트리거  (0) 2024.06.08
[SQL] 스토어드 프로시저, 스토어드 함수, 커서  (0) 2024.06.07
[SQL] 뷰  (1) 2024.06.03
[SQL] 테이블 제약조건  (0) 2024.06.02
[SQL] 스토어드 프로시저  (1) 2024.06.02

'SQL' Related Articles

티스토리툴바