파트 7. 인덱스

1. 인덱스


흔히 알고있는 책에서의 인덱스와 같은 의미이다. 특정 내용을 찾을 때 빠르게 찾기 위한 도구라고 생각하면 된다.


장점

  • 검색 속도가 매우 빨라질 수 있다.(항상 그런 것은 아니다.)
  • 그 결과 해당 쿼리의 부하가 줄어들어서, 결국 시스템 전체의 성능이 향상된다.

단점

  • 인덱스가 데이터베이스 공간을 차지해서 추가적인 공간이 필요해지는데, 대략 데이터베이스 크기의 10%의 정도의 추가 공간이 필요하다.
  • 처음 인덱스를 생성하는데 시간이 많이 소요될 수 있다.
  • 데이터의 변경 작업이 자주 일어날 경우에는 오히려 성능이 많이 나빠질 수 있다.


인덱스의 종류

인덱스는 클러스터형 인덱스와 보조 인덱스로 나뉜다. 클러스터형 인덱스는 영어사전 같이 책의 내용 자체가 순서대로 정렬되어 있어서 인덱스 자체가 책의 내용과 같은 것을 말한다. 보조 인덱스는 책 뒤에 찾아보기가 있는 일반 책과 같다.

  • 클러스터형 인덱스 : 테이블당 한 개만 생성 가능, 행 데이터를 인덱스로 지정한 열에 맞춰서 자동 정렬한다.
  • 보조 인덱스 : 테이블당 여러 개를 생성 가능

인덱스의 갯수는 3~4개 정도가 적당하다.
너무 많은 인덱스는 새로운 Row를 등록할때마다 인덱스를 추가해야하고, 수정/삭제시마다 인덱스 수정이 필요하여 성능상 이슈가 있다. 또한, 인덱스 역시 공간을 차지하기에 많은 인덱스들을 그만큼 많은 공간을 차지한다.

인덱스 키값의 크기

그림13
InnoDB (MySQL)은 디스크에 데이터를 저장하는 가장 기본 단위를 페이지라고 하며, 인덱스 역시 페이지 단위로 관리된다.
페이지는 16KB 로 크기가 고정되어 있다.
만약 본인이 설정한 인덱스 키의 크기가 16 Byte 라고 하고, 자식노드(Branch, Leaf)의 주소가 담긴 크기가 12 Byte 정도로 잡으면, 161024 / (16+12) = 585로 인해 하나의 페이지에는 585개가 저장될 수 있다.
여기서 인덱스 키가 32 Byte로 커지면 어떻게 될까?
16
1024 / (32+12) = 372로 되어 372개만 한 페이지에 저장할 수 있게 된다.
조회 결과로 500개의 row를 읽을때 16byte일때는 1개의 페이지에서 다 조회가 되지만, 32byte일때는 2개의 페이지를 읽어야 하므로 이는 성능 저하가 발행하게 된다.
즉, 인덱스의 키는 길면 길수록 성능상 이슈가 있다.


인덱스 컬럼 기준

1개의 컬럼만 인덱스를 걸어야 한다면, 해당 컬럼은 카디널리티(Cardinality)가 가장 높은 것을 잡아야 한다.
카디널리티(Cardinality)란 해당 컬럼의 중복된 수치의 정도를 나타내는데 중복도가 낮으면 카디널리티가 높다고 표현한다. 예를 들어 성별, 학년 등은 카디널리티가 낮다고 얘기한다. 반대로 주민등록번호, 계좌번호 등은 카디널리티가 높다고 얘기한다.
쉽게 생각하면, 중복의 반대말이라고 생각하면 된다.

자동으로 생성되는 인덱스

  • primary key로 지정한 열은 클러스터형 인덱스가 생성된다.
  • unique not null로 지정한 열은 primary key가 없다면 클러스터형 인덱스가 생성된다. primary key가 있다면 보조 인덱스가 생성된다.
  • unique로 지정한 열은 보조 인덱스가 생성된다.
  • primary key로 지정한 열로 데이터가 오름차순으로 정렬된다.
use sqldb;
CREATE TABLE tbl1 (
    a INT PRIMARY KEY,
    b INT,
    c INT
);

show index from tbl1;

그림1

key_name이 primary라고 나오는데 이걸 클러스터형 인덱스가 primary키라고 해석하면 된다. column_name을 보면 a열에 인덱스가 만들어진 것을 확인할 수 있다. non_unique가 0이면 중복이 허용되지 않는다는 뜻이다.


use sqldb;
CREATE TABLE tbl1 (
    a INT PRIMARY KEY,
    b INT unique,
    c INT unique
);

show index from tbl1;

그림2

a에 primary key가 지정되면서 클러스터형 인덱스가 만들어졌고, unique에는 보조 인덱스가 만들어졌다.


CREATE TABLE tbl1 (
    a INT unique not null,
    b INT unique,
    c INT unique
);

show index from tbl1;

그림3

primary key가 없고 unique지만 not null로 지정할 경우 해당 unique에는 보조 인덱스가 아니라 클러스터형 인덱스가 생성된다. 위 그림을 보면 a의 경우 null칸이 비어있는 것을 확인할 수 있는데 key_name에 primary key가 없는 경우에만 이것은 클러스터형 인덱스를 의미한다.

CREATE TABLE tbl1 (
    a INT unique not null,
    b INT unique,
    c INT unique,
    d int primary key
);

show index from tbl1;

그림4

a의 null칸이 비어있더라도 primary key가 존재하므로 a에는 보조 인덱스가 생성된다.


create database if not EXISTS testdb;
use testdb;
drop table if EXISTS usertbl;
CREATE TABLE usertbl 
( userID  CHAR(8) NOT NULL PRIMARY KEY, 
  name    VARCHAR(10) NOT NULL, 
  birthYear   INT NOT NULL,  
  addr	  CHAR(2) NOT NULL
);

insert into usertbl values('lsg','이승기',1987,'서울');
insert into usertbl values('kbs','김범수',1979,'경남');
insert into usertbl values('kkh','김경호',1971,'전남');
insert into usertbl values('jyp','조용필',1950,'경기');
insert into usertbl values('ssk','성시경',1979,'서울');
select * from usertbl;

그림5

클러스터형 인덱스의 경우, 행데이터를 지정한 열에 따라 자동 정렬한다고 했다. userid를 클러스터형 인덱스로 설정하고 정보를 아무렇게나 삽입하면 클러스터형 인덱스인 userid에 따라 정렬되어 출력된다.

여러 컬럼으로 인덱스 시 조건 누락

꼭 인덱스의 컬럼을 모두 사용해야만 인덱스가 사용되는 것은 아니다.

인덱스: group_no, from_date, is_bonus 위와 같은 순서대로 인덱스가 걸려있을 때, where문에 group_no를 넣은 쿼리와 where문에 from_date를 넣은 쿼리를 실행시켜보면 group_no를 넣은 쿼리는 인덱스를 타고, from_Date를 넣은 쿼리는 인덱스를 타지 않는 것을 확인할 수 있다.
즉, 첫번째 인덱스 조건은 조회조건에 포함되어야만 인덱스를 탈 수 있다.


2. 인덱스의 내부 작동


B-Tree (균형 트리)

mysql의 인덱스는 기본적으로 b-tree구조를 가진다.

그림6

만약 루트노드가 없이 mmm을 찾는다면 aaa,fff,lll 3페이지를 읽어야 하지만 루트노드가 있다면 루트노트 페이지를 읽고 바로 lll페이지로 갈 수 있으므로 2페이지만에 찾을 수 있다.


페이지 분할

앞서 설명했듯이 데이터를 검색하는 작업에서는 b-tree가 효율적이다. 즉, select의 속도가 급격하게 향상될 수 있다는 뜻이다. 하지만 인덱스를 구성하면 데이터의 변경 작업(insert, update, delete) 시에 성능이 나빠지는 단점이 있다고 했다. 특히, insert 작업이 일어날 때 성능이 급격하게 느려질 수 있는데 그 이유는 페이지 분할이라는 작업이 발생되기 때문이다. 그림7

III를 삽입할 경우에는 JJJ를 뒤로 밀고 삽입하면 별문제가 없다. 하지면 여기서 GGG를 삽입한다고 하면 문제가 생긴다.

그림8

자리가 없기 때문에 새로운 페이지를 만들어서 적절하게 데이터를 분할해야하고 루트노드에 새로 추가해줘야한다. 만약 루트 노드에 자리가 없다면 루트 노드도 분할하는 작업이 필요하다. 이런 작업은 mysql에서 시간이 많이 소요되기 때문에 성능이 떨어지게 되는 것이다.
여기서 주의해야할 것은 update, delete 행위가 느린 것이지, update, delete를 하기 위해 해당 데이터를 조회하는 것은 인덱스가 있으면 빠르게 조회할 수 있다.
따라서 인덱스가 없는 컬럼을 조건으로 update, delete를 하게 되면 굉장히 느려 많은 양의 데이터를 삭제해야 하는 상황에선 인덱스로 지정된 컬럼을 기준으로 진행하는 것이 좋다.


3. 클러스터형 인덱스와 보조 인덱스의 구조


클러스터형 인덱스

그림9

인덱스가 없는 테이블의 경우 데이터를 입력한 순서대로 위와 같이 저장될 것이다.

그림10

primary key를 설정해서 클러스터형 인덱스를 만들어준 테이블의 경우 해당 열에 의해 행데이터가 정렬되고 각 페이지의 맨 위에 내용을 가지고 루트 페이지를 만들게 된다.

보조 인덱스

그림11

보조 인덱스의 경우는 정렬되지 않으니 데이터 페이지가 입력된 순서대로 그대로 유지된다. 하지만 내부적으로는 각각의 인덱스를 뽑아서 새로운 인덱스 페이지를 만들어 정렬한다. 따라서 검색에서는 보조 인덱스가 한 단계를 더 거쳐야하므로 클러스터형 인덱스를 이용한 검색보다는 조금 느리다.


클러스터형 인덱스 특징

  • 클러스터형 인덱스의 생성 시에는 데이터 페이지 전체가 다시 정렬된다. 그러므로 이미 대용량의 데이터가 입력된 상태라면 업무시간에 클러스터형 인덱스를 생성하는 것은 심각한 시스템 부하를 줄 수 있다.
  • 클러스터형 인덱스는 인덱스 자체의 리프 페이지가 곧 데이터이다. 그러므로 인덱스 자체에 데이터가 포함되어 있다고 볼 수 있다.
  • 클러스터형 인덱스는 보조 인덱스보다 검색 속도는 더 빠르지만, 데이터 입력/수정/삭제는 더 느리다.
    • 검색에서는 보조 인덱스가 한 단계를 더 거쳐야하기 때문
    • insert/delete/update에서는 클러스터형 인덱스는 직접 찾아가야하지만, 보조 인덱스는 그냥 뒤에 칸에 넣기만 하면 되기 때문
  • 클러스터 인덱스는 성능이 좋지만 테이블 한 개만 생성할 수 있다. 그러므로 어느 열에 클러스터형 인덱스를 생성하는지에 따라 시스템의 성능이 달라질 수 있다.


보조 인덱스 특징

  • 보조 인덱스의 생성 시에는 데이터 페이지는 그냥 둔 상태에서 별도의 페이지에 인덱스를 구성한다.
  • 보조 인덱스는 인덱스 자체의 리프 페이지는 데이터가 아니라 데이터가 위치하는 주소값(RID)이다. 클러스터형보다 검색 속도는 더 느리지만, 데이터 입력/수정/삭제는 더 빠르다.
  • 보조 인덱스는 여러 개 생성할 수 있다.


4. 클러스터형 인덱스와 보조 인덱스가 혼합되어 있을 경우


CREATE DATABASE IF NOT EXISTS testdb;
USE testdb;
DROP TABLE IF EXISTS mixedtbl;
CREATE TABLE mixedtbl
( userID  CHAR(8) NOT NULL ,
  name    VARCHAR(10) NOT NULL,
  addr     char(2)
);
INSERT INTO mixedtbl VALUES('LSG', '이승기', '서울');
INSERT INTO mixedtbl VALUES('KBS', '김범수', '경남');
INSERT INTO mixedtbl VALUES('KKH', '김경호', '전남');
INSERT INTO mixedtbl VALUES('JYP', '조용필',  '경기');
INSERT INTO mixedtbl VALUES('SSK', '성시경', '서울');
INSERT INTO mixedtbl VALUES('LJB', '임재범',  '서울');
INSERT INTO mixedtbl VALUES('YJS', '윤종신',  '경남');
INSERT INTO mixedtbl VALUES('EJW', '은지원', '경북');
INSERT INTO mixedtbl VALUES('JKW', '조관우', '경기');
INSERT INTO mixedtbl VALUES('BBK', '바비킴',  '서울');

-- primary key -> 클러스터형 인덱스
ALTER TABLE mixedtbl
	ADD CONSTRAINT PK_mixedtbl_userID
		PRIMARY KEY (userID);

-- unique -> 보조 인덱스
ALTER TABLE mixedtbl
	ADD CONSTRAINT UK_mixedtbl_name
		UNIQUE (name) ;

SHOW INDEX FROM mixedtbl;

SELECT addr FROM mixedtbl WHERE name = '임재범';

그림12

위 코드처럼 구성해서 혼합되어 있는 경우 위 그림처럼 구성된다. 보조 인덱스만 단일로 있었던 경우에는 보조 인덱스 페이지에서는 보조 인덱스로 설정된 열 옆칸에는 주소값이 저장되었었다. 하지만 혼합된 경우에는 주소값이 아니라 primary key 즉, 클러스터형 인덱스가 들어가게 된다. 예를 들어, 임재범의 addr를 검색하게 되면 name이 보조 인덱스이기 때문에 먼저 보조 인덱스 페이지의 루트 페이지 -> 리프 페이지로 이동해서 클러스터형 인덱스값을 얻는다. 그리고 다시 클러스터형 인덱스 페이지 -> 루트페이지 -> 리프 페이지 로 이동하여 최종적으로 검색을 마치게 된다. 생각해보면 검색부분에서 단일 구성보다 페이지를 많이 읽게 되어 손해라고 느낄 수 있다. 하지만 삽입,검색,삭제의 경우 보조 인덱스에 클러스터형 인덱스가 아니라 주소값을 저장했을 경우, 만약 페이지가 삭제되거나 다시 만들어지게 된다면 모든 주소값들을 수정해야하는 상황이 만들어지기 때문에 검색에 약간의 손해가 있더라도 이렇게 구성되게 된 것이다.


cf) 인덱스를 검색하기 위한 일차 조건
where절에 해당 인덱스를 생성한 열의 이름이 나와야 한다. 단순하게 select 열이름 을 사용한다고 해서 인덱스를 검색하는 것이 아니다.

5. 인덱스 생성/삭제


생성

create [unique] index 인덱스이름 on 테이블이름(열이름)

-- 테이블을 만들 때 primary, unique로 만들어주는게 아니고
-- 위처럼 이후에 만들어주는 경우 만들고 반드시 적용시켜줘야한다.
ANALYZE TABLE usertbl; 

create index로 생성되는 인덱스는 보조 인덱스가 생성되고 unique 옵션을 사용하면 인덱스는 동일한 데이터 값이 입력될 수 없으며, unique가 생략될 경우 default값으로 중복이 허용되는 인덱스이다.


삭제

drop index 인덱스이름 on 테이블이름;

기본 키로 설정된 클러스터형 인덱스의 이름은 항상 primary로 되어 있으므로 인덱스이름에 primary로 써주면 된다. 혼합되어 있는 경우 모두 삭제할 경우에는 보조 인덱스부터 삭제하고 클러스터형 인덱스를 삭제한다. 클러스터형부터 삭제하게 되면 보조 인덱스가 다시 주소값으로 재구성되기 때문이다.


6. 실습


USE sqldb;
SELECT * FROM usertbl;

USE sqldb;
SHOW INDEX FROM usertbl;

-- index_length가 0이면 보조 인덱스가 없는 것이고 생기면 늘어난다
SHOW TABLE STATUS LIKE 'usertbl';

-- 중복이 허용되는 보조 인덱스 생성
CREATE INDEX idx_usertbl_addr 
   ON usertbl (addr);
   
SHOW INDEX FROM usertbl;

-- 조회해도 아직 index_length 가 0
-- 아직 적용이 안되었기 때문
SHOW TABLE STATUS LIKE 'usertbl';

-- 보조 인덱스 적용하고 다시 조회
ANALYZE TABLE usertbl;
SHOW TABLE STATUS LIKE 'usertbl';

-- 오류 발생 -> 출생년도가 같은 사람이 존재해서 unique 옵션 사용 불가능
CREATE UNIQUE INDEX idx_usertbl_birtyYear
	ON usertbl (birthYear);

-- 이름 열에 고유 보조 인덱스 생성
CREATE UNIQUE INDEX idx_usertbl_name
	ON usertbl (name);

SHOW INDEX FROM usertbl;

-- 오류 발생
-- 이미 김범수라는 정보가 저장되어 있어서 고유 보조 인덱스에 부합하지 않음
INSERT INTO usertbl VALUES('GPS', '김범수', 1983, '미국', NULL  , NULL  , 162, NULL);

-- 두개의 열을 하나의 인덱스로 만들기
CREATE INDEX idx_usertbl_name_birthYear
	ON usertbl (name,birthYear);
    
-- 인덱스를 사용해서 검색 where
SELECT * FROM usertbl WHERE name = '윤종신' and birthYear = '1969';

-- 인덱스 삭제 방법 1
DROP INDEX idx_usertbl_addr ON usertbl;
DROP INDEX idx_usertbl_name_birthYear ON usertbl;
DROP INDEX idx_usertbl_mobile1 ON usertbl;

-- 인덱스 삭제 방법 2
ALTER TABLE usertbl DROP INDEX idx_usertbl_addr;
ALTER TABLE usertbl DROP INDEX idx_usertbl_name_birthYear;
ALTER TABLE usertbl DROP INDEX idx_usertbl_mobile1;

-- buytbl과 foreign key로 연결되어 있는 경우 삭제 불가능
ALTER TABLE usertbl DROP PRIMARY KEY;

-- constraint_name에 foreign key 이름이 나온다.
SELECT table_name, constraint_name
    FROM information_schema.referential_constraints
    WHERE constraint_schema = 'sqldb';

-- foreign key 삭제 후 primary key 삭제
ALTER TABLE buyTbl DROP FOREIGN KEY buytbl_ibfk_1;
ALTER TABLE usertbl DROP PRIMARY KEY;


7. 인덱스를 생성해야 하는 경우와 그렇지 않은 경우


  • 인덱스는 열 단위에 생성된다.
  • where절에서 사용되는 열에 인덱스를 만들어야 한다.
  • where절에 사용되더라도 자주 사용해야 가치가 있다.
  • 데이터의 중복도가 높은 열은 인덱스를 만들어도 별 효과가 없다.
  • 외래 키를 지정한 열에는 자동으로 외래 키 인덱스가 생성된다.
  • join에 자주 사용되는 열에는 인덱스를 생성해 주는 것이 좋다.
  • insert/update/delete가 얼마나 자주 일어나는지를 고려해야 한다.
  • 클러스터형 인덱스는 테이블당 하나만 생성할 수 있다.
  • 클러스터형 인덱스가 테이블에 아예 없는 것이 좋은 경우도 있다.
  • 사용하지 않는 인덱스는 제거하자.


8. 인덱스 조회시 주의 사항


  • between, like, <, > 등 범위 조건은 해당 컬럼은 인덱스를 타지만, 그 뒤 인덱스 컬럼들은 인덱스가 사용되지 않는다.
    • 즉, group_no, from_date, is_bonus으로 인덱스가 잡혀있는데 조회 쿼리를 where group_no=XX and is_bonus=YY and from_date > ZZ등으로 잡으면 2번째 인덱스가 > 을 사용했으므로 3번째 인덱스인 is_bonus는 인덱스가 사용되지 않는다.
  • in은 인자값으로 상수가 포함되면 문제 없지만, 서브쿼리를 넣게되면 성능상 이슈가 발생한다.
    • in의 인자로 서브쿼리가 들어가면 서브쿼리의 외부가 먼저 실행 되고, in 은 체크조건으로 실행되기 때문이다.
  • AND연산자는 각 조건들이 읽어와야할 ROW수를 줄이는 역할을 하지만, or 연산자는 비교해야할 ROW가 더 늘어나기 때문에 풀 테이블 스캔이 발생할 확률이 높다.
    • WHERE 에서 OR을 사용할때는 주의가 필요하다.
  • 인덱스로 사용된 컬럼값 그대로 사용해야만 인덱스가 사용된다.
    • where salary * 10 > 150000;는 인덱스를 못타지만, where salary > 150000 / 10; 은 인덱스를 사용한다.
    • 컬럼이 문자열인데 숫자로 조회하면 타입이 달라 인덱스가 사용되지 않는다. 정확한 타입을 사용해야만 한다.



‘이것이 MySQL이다’ [https://jojoldu.tistory.com/243]



[커버링인덱스]
[커버링인덱스 2]
[페이징 성능 개선하기 - No Offset 사용하기]
[페이징 성능 개선 - 커버링 인덱스 사용하기]


© 2021. By Backtony