[DB] MariaDB - Index 걸기

이전 글 [DB] 현실을 통해 쉽게 이해하는 Index

 

DB에 Index(인덱스) 거는 법을 알아보자. 기본적인 Index 생성 방법부터, Index 성능 최적화에 대해 알아보겠다. (MariaDB 기준)

 

기본 문법

Index 만들기

기본적으로 Index는 다음과 같은 방법으로 만들 수 있다.

CREATE INDEX [인덱스 이름] ON [테이블 이름]([칼럼 이름]);

 

예시)

CREATE INDEX IX_USER_USERNAME ON user(username);

 

Multi-column Index 만들기

CREATE INDEX [인덱스 이름] ON [테이블 이름]([칼럼 이름 1], [칼럼 이름 2], ...);
CREATE INDEX IX_USER_USERNAME_PHONE ON user(username, phone);

 

인라인으로 Index 만들기

CREATE TABLE t2 (a INT NOT NULL, b INT, INDEX (a,b));

 

여타 다른 제약조건들과 비슷하게 Index 또한 테이블 생성 쿼리에서 인라인으로 Index를 설정해줄 수 있다.

 

Index를 생성할 때 이름을 지정하지 않는 경우, 지정한 칼럼 이름으로 Index의 이름이 결정된다.

Multi-column Index의 경우에는 첫 번째로 지정한 칼럼명을 기준으로 Index의 이름이 결정된다.

 

자동 생성 이름이 중복된다면?

바로 위에서 생성한 t2 테이블에 a 칼럼 Index를 생성하면 이름이 어떻게 결정될까 궁금해서 해봤다.

결론부터 말하자면 'a_2'라는 이름으로 생성됐는데, 아래와 같은 명령을 실행해서 확인해봤다.

ALTER TABLE t2 ADD INDEX(a);

 

show index from t2;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| t2    |          1 | a        |            1 | a           | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| t2    |          1 | a        |            2 | b           | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| t2    |          1 | a_2      |            1 | a           | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | NO      |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+

 

참고로 (a, b)를 인덱스로 만들면 굳이 a에 인덱스를 걸 필요가 없다. (a, b)를 이용해서 a 인덱스로 활용할 수 있기 때문이다. (b, a)로 인덱스를 만드는 경우에는 또 다르지만 이 글에서 너무 많은걸 다루게 되는 것 같아 다른 글에서 작성하겠다.

 

Index 삭제하기

Index 삭제는 다음과 같은 방법으로 실행한다.

DROP INDEX [인덱스 이름] ON [테이블 이름];
DROP INDEX IX_USER_USERNAME ON user;

 

Null 다루기

MariaDB에서는 Index를 거는 칼럼에 Null이 있어도 된다.

Index를 이용해 IS NOT NULL과 IS NULL 조건문 검사를 빠르게 할 수 있다.

 

Index가 자동으로 생성되는 경우

MariaDB에서는 Primary Key, Unique Key, Foreign Key에 대하여 자동으로 Index를 생성해준다.

 

 

 

Index 성능 최적화

MariaDB 공식 안내

1. Index는 어플리케이션에서 DB를 사용하는 쿼리에 따라 잘 만들어야 한다.

마구잡이로 만들면 오히려 자원 낭비이고, 규모가 작은 테이블에도 굳이 만들 필요가 없다.

→ 이에 대한 내용은 다음에 조금 더 자세히 정리해보겠다.

 

2. Explain 명령어를 사용하면 쿼리 실행 계획을 확인할 수 있는데, 해당 명령의 결과를 확인해보고 Index를 활용하면 좋을지 말지 참고하기 좋다.

 

3. LIKE '%word%' 와 같은 구문이 포함된 쿼리는 기본 Index가 아닌 Fulltext Index를 만들어줘야 그 효과를 볼 수 있다.

ALTER TABLE 테이블명 ADD FULLTEXT INDEX 인덱스명 (칼럼명);

 

위와 같은 방법으로 Fulltext Index를 만들어줄 수 있다. 하지만 Index 생성 비용이 더 크고, 다양한 최적화 기법과 설정이 있으니 정말 Fulltext Index를 사용하겠다면 꼭 다양한 자료를 확인해보길 바란다.

 

4. 테이블 업데이트마다 Index 또한 업데이트하는 비용이 들어간다. (Index 이해하기 참고)

따라서, 대규모 데이터를 가진 테이블을 만들 때는, 데이터를 먼저 쌓고나서 Index를 생성하는 것이 좋다. 이러면 다량의 데이터를 INSERT 하는 중간중간 INDEX를 업데이트하는 오버헤드를 줄여줄 수 있다.

이를 바탕으로 생각해보니, CREATE TABLE 할 때 Index를 바로 걸어주는 것은 안좋을 수 있다는 생각이 들었다.

 

5. 아마도 비슷한 이유로 인해서 다량의 데이터 읽기/쓰기가 발생할 때, "지연 쓰기" 전략을 사용하는 것을 고려해보라고 하는 듯 하다. DB 엔진이 "batch" (배치) 쓰기 모드를 사용해 디스크 IO를 줄여줘 성능이 향상된다고 한다.

문서를 읽어보니 어감상 INSERT 쿼리 자체로 batch INSERT를 실행하는 것과 "지연 쓰기" 전략으로 batch write를 하는 것 둘이 다른 것 같다. 이 부분도 다음 시간에 알아보겠다.

 

 

참고 자료

MariaDB의 공식 문서를 참고했다.

https://mariadb.com/kb/en/getting-started-with-indexes/

Getting Started with Indexes

The terms 'KEY' and 'INDEX' are generally used interchangeably, and statements should work with either keyword.

위 문서에서 KEY와 INDEX는 서로 교차 사용되어도 말이 된다는 표현을 했다. PRIMARY KEY, UNIQUE KEY와 FOREIGN KEY는 INDEX가 기본적으로 설정되니 "오~ 그렇네" 싶었다.

 

 

RDB마다 다르다!

RDB에 따라 특성이 약간씩 다를 수 있다는 점은 꼭 명심해야겠다.

예시) MariaDB는 Foreign Key를 등록하면 해당 칼럼에 Index를 걸어주지만,
Postgresql에서는 Foreign Key를 등록해도 기본값으로 Index를 생성해주지 않는다.

 

 

실제로 테스트해본 내용

MariaDB에서는 아래와 같이 FK인 rno에 자동으로 인덱스를 걸어줬다.

SHOW INDEX FROM user;
+-------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table | Non_unique | Key_name                    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+-------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| user  |          0 | PRIMARY                     |            1 | uno         | A         |           5 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| user  |          0 | UK_USER_NICKNAME_PHONE      |            1 | nickname    | A         |           5 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| user  |          0 | UK_USER_NICKNAME_PHONE      |            2 | phone       | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| user  |          1 | FKg1u8y0mm0w6w5c11cyk0aqvtc |            1 | rno         | A         |           5 |     NULL | NULL   |      | BTREE      |         |               | NO      |
+-------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+

 

Postgresql에서 비슷한 구조의 테이블을 만들어 확인해보았다.

postgres=# \d+ users
                                                  Table "public.users"
  Column  |         Type          | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
----------+-----------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 uno      | integer               |           | not null |         | plain    |             |              | 
 nickname | character varying     |           |          |         | extended |             |              | 
 username | character varying(50) |           | not null |         | extended |             |              | 
 rno      | integer               |           |          |         | plain    |             |              | 
Indexes:
    "users_pkey" PRIMARY KEY, btree (uno)
Foreign-key constraints:
    "fk_users_role" FOREIGN KEY (rno) REFERENCES role(rno)
Access method: heap

 

PK인 uno에는 자동으로 Index가 걸렸지만, FK는 Index에 표시하지 않고 FK 제약조건이라고만 나온다.

 

인라인 생성 관련 참고 자료

테이블을 만들 때 인라인으로 Index를 생성하는 것 관련해서는 CREATE TABLE 혹은 ALTER TABLE 문서를 확인해보라.