MySQL 인덱스와 Stockey

인덱스

DB에서 검색 속도를 높이기 위해 테이블의 특정 열에 대해 생성하는 자료 구조. (B+ Tree)

주로 자주 조회(SELECT)되는 열에 대해 설정됩니다.

그러나 인덱스를 지나치게 많이 생성하면 생성, 수정, 삭제 작업에서 성능 저하의 우려가 있습니다.

[인덱스 생성]

CREATE INDEX idx_username ON users (username);

특징

1. 인덱스 생성 시 DB의 크기의 약 10%의 추가 공간이 필요하다
2. 삽입(INSERT), 삭제(DELETE), 수정(UPDATE)같은 데이터 변경 쿼리가 잦은 경우 성능이 악화될 수 있다
3. 조회(SELECT) 성능 향상

멀티인덱스

두 개 이상의 열을 결합한 인덱스.

여러 열을 동시에 조회하는 쿼리에서 성능을 향상 시킬 수 있습니다.

SELECT * FROM employees WHERE department_id = 5 AND salary > 50000

위와 같은 쿼리에서 department_id 와 salary 열을 결합한 멀티인덱스를 사용하면 성능이 향상될 수 있습니다.

[멀티인덱스 생성]

CREATE INDEX idx_name ON users (first_name, last_name);

실행계획

데이터베이스가 쿼리를 실행하기 위해 선택한 경로와 방법을 설명하는 계획입니다.

SQL 쿼리가 실행될 때, 데이터베이스는 여러 가지 방법으로 쿼리를 실행할 수 있는데, 실행계획은 이러한 방법 중 가장 효율적인 것을 선택한 경로를 보여줍니다.

이를 통해 쿼리의 성능을 분석하고 최적화할 수 있습니다.

[실행계획 확인]

EXPLAIN SELECT * FROM users WHERE username = 'john_doe';

인덱스 설정 기준?

1. 조회가 자주 되는가?

쿼리에서 자주 사용하는 열에 대해서 인덱스를 설정하면 검색 성능을 개선할 수 있습니다.

WHERE, JOIN, ORDER BY, GROUP BY 조건에서 자주 사용되는 열에 인덱스를 추가하는 것이 유리합니다

2. 쓰기가 빈번한가?

INSERT, UPDATE, DELETE가 자주 일어나는 테이블에는 장점보다 단점이 클 수 있습니다.

인덱스를 추가하면 데이터가 변경될 때마다 인덱스도 함께 갱신해야하기 때문

3. 복합 쿼리

특정 쿼리가 여러 열을 동시에 조회할 때 멀티인덱스를 고려할 수 있습니다.

복합 인덱스가 유리한 경우가 많습니다.

Stockey와 인덱스

기존 Keyword 테이블에는 PRIMARY KEY를 제외한 인덱스가 하나도 존재하지 않았습니다.

SELECT * FROM Keyword WHERE keyword = '양극';

간단한 쿼리의 실행계획을 MySQL Workbench에서 확인한 결과 아래와 같은 이미지가 나옵니다.

빨간색으로 표시되는 항목은 느낌적으로 알 수 있듯 성능이 좋지 않은 작업을 나타냅니다.

Full Table Scan. 즉, Row를 하나씩 전부 확인하며 keyword가 ‘양극’인 데이터를 찾는 것입니다.

Query cost는 쿼리 실행에 드는 비용을 상대적인 값응로 표현한 수치입니다. 쿼리의 효율성을 평가할 수 있습니다.

ADD INDEX keyword_idx USING BTREE (keyword) VISIBLE;

하지만, keyword를 인덱스로 등록하면?

인덱스로 등록한 결과 Query cost는 대폭 줄어들고 성능이 우수해진 것을 확인할 수 있습니다.

EXPLAIN ANALYZE SELECT * FROM Keyword WHERE keyword = '양극';

추가로 해당 명령어를 통해 쿼리 실행에 실제로 소요된 시간과 자원 사용량을 확인해볼 수 있습니다.

[INDEX 적용 전]
'-> Filter: (keyword.keyword = \'양극\')
(cost=5460 rows=5403) (actual time=0.377..23.7 rows=8 loops=1)\n
-> Table scan on Keyword 
(cost=5460 rows=54033) (actual time=0.0747..18.3 rows=53727 loops=1)\n'

[INDEX 적용 후]
'-> Index lookup on Keyword using keyword_idx (keyword=\'양극\')
(cost=2.8 rows=8) (actual time=0.0676..0.0855 rows=8 loops=1)\n'

실제 시간(actual time)이 상당히 낮아진것을 확인할 수 있습니다.