Mysql - Mysql(InnoDB)를 사용할때 성능을 위해 알아야하는 것(Index의 중요성)

이번 포스팅에서는, 자신이 DBMS로 mysql 을 사용한다고 말한다면, 당연히 알아야하는 것에 대해 간단히 알아보려고합니다.

1. Mysql의 구조

먼저, Mysql을 사용할 때 기본으로 알아야하는 것에 알아보기 전에 이해를 돕기 위해, Mysql의 기본구조를 간단하게 살펴보도록 하겠습니다.

Mysql은 크게 Mysql 엔진과, 스토리지 엔진 부분으로 나뉩니다.

1.1 Mysql 엔진

Mysql 엔진은 파서, 전처리기, 옵티마이저, 쿼리실행기 등으로 구성되어 있으며, 사용자의 요청을 받아, 쿼리를 검사하고, 스토리지 엔진에게 데이터 읽기 또는 변경에 대한 실행 요청을 합니다.

1.2 스토리지 엔진

스토리지 엔진은 실질적으로, 데이터 파일로부터 데이터를 읽거나, 데이터를 쓰고 변경하는 작업을 진행합니다.

스토리지 엔진은 다시 InnoDB, MyISAM, Maria 등등으로 나뉩니다.

InnoDB 스토리지 엔진

최신 버전(mysql 8.x) 기준으로 기본 스토리지 엔진은 InnoDB 를 채택하고 있습니다. 이때, InnoDB의 특성을 이해해야, 개발시, 최적화를 조금 더 쉽게 할 수 있습니다.

InnoDB의 경우 다른 스토리지 엔진과달리, 트랜잭션 기능과 테이블의 레코드 단위의 잠금등 많은 장점을 가지고 있습니다.

2. Mysql(InnoDB 엔진)을 사용하면 꼭 알아야할 것

정확히 말해 스토리지 엔진으로 InnoDB 를 사용하는 경우 꼭 알아야 할 것에대해 알아보려고 합니다. 앞서, Mysql 8.x 는 스토리지 엔진으로 기본적으로 InnoDB 를 사용한다고 했습니다. 따라서, Mysql을 사용한다면 꼭 알아야하는 것이라고 말씀드려도 무방할것 같습니다.

2.1 잠금

우선 잠금에 대해알아야 합니다. 잠금이란, 데이터의 일관성을 보장하기 위해, 한 자원에대해 여러 유저가 접근하는 경우, 앞선 유저가 한 자원에 대해서 변경작업을 수행하는 경우, 다른 유저가 같은 자원에 접근하지 못하도록 하는 기능을 말합니다.

Mysql의 InnoDB를 제외한 다른 스토리지엔진은 거의 테이블단위의 잠금까지 만을 제공하지만, InnoDB의 경우 레코드 단위의 잠금도 제공합니다.

2.2 InnoDB의 레코드 단위 잠금(인덱스)의 중요성

InnoDB의 레코드 단위 잠금은 독특하게 레코드 자체를 잠그지 않고 익덱스를 잠급니다. 이는 매우 중요한 특성입니다. 아래의 예제를 통해 인덱스를 잠그는 특성이 왜 중요한지를 알아보도록 하겠습니다.

예제

Employee (테이블)

emp_no	first_name	last_name	hire_date
10001	Georgi	Facello	2019-01-02
10002	Georgi	Atchley	2019-03-20
10003	Georgi	Klassen	2020-01-03
…	…	…	…

Georgi 라는 first_name 을 가진 사원이 200명이 존재하고, 총 30만개의 레코드가 존재하는 Employee 테이블입니다.

Idx_firstname (인덱스)

first_name	emp_no
Georgi	10001
Georgi	10002
Georgi	10003
… + 197	…

Employee 테이블의 인덱스입니다. first_name만을 가지고 있는 것을 중점으로 보셔야합니다.

문제상황

mysql> UPDATE employees SET hire_date = NOW()
             WHERE first_name = 'Georgi' AND last_name='Klassen';

위와 같은 테이블과 인덱스를 가진 상태에서, first_name이 Georgin이며, last_name이 Klassen인 사용자의 입사날짜를 오늘로 변경하는 쿼리를 요청한다면, 몇개의 레코드가 잠기게 될까요 ?

정답부터 말씀드리자면 200개의 레코드가 모두 잠기게 됩니다. 왜그럴까요?

이유

idx_firstname 인덱스를 다시 보면 알수 있듯이. UPDATE 쿼리에서 idx_firstname 인덱스를 사용할수 있는 조건은 first_name='Geogi' 하나 뿐입니다. 즉, last_name 컬럼이 인덱스에 존재하지 않기 때문에, first_name이 Georgi인 레코드 200개가 모두 잠기게 되는 것입니다.

Employees 테이블에 인덱스가 없다면?

employees 테이블에 인덱스가 존재하지 않는다면, 상황은 더욱 심각해집니다. 이때는, 테이블을 풀 스캔하면서, UPDATE 작업을 하게 되는데, 이 과정에서 테이블에 있는 30만 건의 모든 레코드를 잠그게 됩니다. 이 때문에 Mysql(InnoDB)을 사용할때에는 인덱스 설계가 매우 중요합니다.

3. 결론

Mysql을 사용하며 스토리지 엔진으로 InnoDB 를 사용한다면, 잠금으로 인한 동시성 및 성능 저하를 줄이기위해, 인덱스 설계는 성능을 위해 거의 필수입니다.

다음 포스팅에서는 인덱스 설계 방법에 대해 알아보도록 하겠습니다.