Search

Real MySQL 8.0

1. 소개

역사는 1979년 스웨덴의 TcX라는 회사의 터미널 인터페이스 라이브러리인 UNIREG로부터 시작된다.
버전 1.0은 TcX 사내에서만 사용되다가, 1996년 일반인에게 공개됐다.
그리고 2000년 TcX에서 MySQL AB라는 회사로 독립함과 동시에 FPL 라이센스 정책으로 바뀌고, 2006년 최종적으로 현재와 같은 두 가지 라이센스 정책을 취하게 된다.
이후에 썬마이크로시스템즈에 인수되고, 다시 오라클에 인수됐다.
MySQL의 라이센스 정책은 엔터프라이즈 에디션과 커뮤니티 에디션으로 두 가지이며, 두 에디션의 소스코드는 동일하고 오픈소스로 공개된다.
MySQL 5.5 이전버전까지는 얼마나 자주 패치 버전이 릴리즈되느냐의 차이였고, 5.5 버전부터는 엔터프라이즈 에디션의 소스코드는 공개되지 않도록 바뀌었다.
오라클로 인수된 이후부터 MySQL 서버의 소스코드 레벨부터 리팩토링이 시작됐고, 5.5 버전부터 5.7 버전까지는 안정성과 성능 개선에 집중했으며, 8.0 버전부터는 상용 DBMS가 가지고 있는 기능들이 장착되기 시작했다.
“어떤 DBMS를 사용해야 할지 모르겠습니다. 어떤 DBMS가 좋은가요?” 라는 질문에 항상 “자기가 가장 잘 활용할 수 있는 DBMS가 가장 좋은 DBMS입니다.” 라고 답변한다.
답변을 듣고도 아직 고민된다면 안정성, 성능과 기능, 커뮤니티나 인지도 순서로 고려하라.

2. 설치와 설정

MySQL 서버의 버전을 선택할 때는 다른 제약 사항이 없다면 가능한 한 최신 버전을 설치하는 것이 좋다.
기존 버전에서 새로운 메이저 버전으로 업그레이드하는 경우라면 최소 패치 버전이 15~20번 릴리즈된 버전을 선택하는 것이 안정적인 서비스에 도움이 될 것이다.
엔터프라이즈 에디션과 커뮤니티 에디션의 핵심 기능은 거의 차이가 없으며, 부가적인 기능과 서비스들은 엔터프라이즈 에디션에서만 지원된다.

기본 설정

우선 MySQL 서버가 설치되면 /etc/my.cnf 설정 파일이 준비되는데, 이 설정 파일에는 MySQL 서버를 실행하는데 꼭 필요한 3~4개의 아주 기본적인 설정만 기록돼 있다.
만약 서비스용으로 MySQL 서버를 설치한다면 서버 설정 내용을 충분히 숙지한 후 설정 파일을 준비하자.
linux > mysqld --defaults-file=/etc/my.cnf --initialize-insecure
Kotlin
복사
•
--initialize-insecure
필요한 초기 데이터 파일과 로그 파일들을 생성하고 마지막으로 비밀번호가 없는 관리자 계정인 root 유저를 생성한다.
•
--initialize
생성된 관리자 계정의 비밀번호를 /var/log/mysqld.log 에러 로그 파일로 기록한다.

클린 셧다운

실제 트랜잭션이 커밋돼도 데이터 파일에 변경된 내용이 기록되지 않고 로그 파일에만 기록돼 있을 수 있다.
심지어 MySQL 서버가 종료되고 다시 시작된 이후에도 계속 이 상태로 남아있을 수도 있다.
사용량이 많은 MySQL 서버가 종료될 때 모든 커밋된 내용을 데이터 파일에 기록하고 종료하게 할 수도 있다.
mysql > SET GLOBAL innodb_fast_shutdown=0;
mysql > SHUTDOWN;
MySQL 서버가 시작되거나 종료될 때는 MySQL 서버(innodb)의 버퍼 풀 내용을 백업하고 복구하는 과정이 내부적으로 실행된다.
실제 버퍼 풀의 내용을 백업하는 것이 아니라, 버퍼 풀에 적재돼 있던 데이터 파일의 데이터 페이지에 대한 메타 정보를 백업하기 때문에 용량이 크지 않으며, 백업 자체는 매우 빠르게 완료된다.
하지만 MySQL 서버가 새로 시작될 때는 디스크에서 데이터 파일들을 모두 읽어서 적재해야 하므로 상당한 시간이 걸릴 수도 있다.

서버 연결 테스트

linux > mysql -uroot -p --host=localhost --socket=/tmp/mysql.sock // (1) linux > mysql -uroot -p --host=127.0.0.1 --port=3306 // (2) linux > mysql -uroot -p // (3)
Kotlin
복사
•
(1)
MySQL 소켓 파일을 이용해 접속하는 방법이다.
•
(2)
host와 port를 명시하여 TCP/IP를 통해 접속하는 방법이다.
원격 호스트에 있는 MySQL 서버에 접속할 때는 반드시 두 번째 방법을 사용해야 한다.
host를 localhost로 명시하는 것과 127.0.0.1로 명시하는 것은 다르며, localhost는 IPC 기법을 통해 통신하고 127.0.0.1은 루프백 IP이기는 하지만 TCP/IP를 통해 통신한다.
•
(3)
기본값으로 호스트는 localhost를 사용하며, my.cnf 설정 파일에서 소켓 파일의 위치를 읽어서 사용한다.

서버 업그레이드

•
인플레이스 업그레이드
MySQL 서버의 데이터 파일을 그대로 두고 업그레이드
여러 가지 제약 사항이 있지만 업그레이드 시간을 크게 단축할 수 있다.
•
논리적 업그레이드
mysqldump 도구 등을 이용해 데이터를 덤프한 후, 새로 업그레이드 된 버전의 MySQL 서버에 데이터를 적재
버전 간 제약 사항이 거의 없지만 업그레이드 시간이 매우 많이 소요될 수 있다.
•
동일 메이저 버전에서 마이너 버전간 업그레이드
대부분 데이터 파일의 변경 없이 진행되며, 많은 경우 여러 버전을 건너뛰어서 업그레이드하는 것도 허용된다.
•
메이저 버전 간 업그레이드
반드시 직전 버전에서만 업그레이드가 허용된다.
5.1 버전을 8.0 버전으로 업그레이드해야 한다면 5.1 → 5.5 → 5.6 → 5.7 → 8.0 으로 진행해야 한다.
두 단계 이상을 한 번에 업그레이드해야 한다면 논리적 업그레이드가 더 나은 방법이다.

서버 설정

•
설정 파일
일반적으로 MySQL 서버는 단 하나의 설정 파일을 사용한다.
리눅스를 포함한 유닉스 계열에서는 my.cnf라는 이름을 사용하고, 윈도우 계열에서는 my.ini라는 이름을 사용한다.
이 설정 파일의 경로는 고정돼 있지 않고, 지정된 여러 개의 디렉터리를 순차적으로 탐색하면서 처음 발견된 파일을 사용한다.
shell > mysql --help ... Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf ...
Plain Text
복사
mysql --help 명령어를 사용하면 MySQL 서버나 클라이언트 프로그램이 어디에 있는 설정 파일들을 참조하는지 확인할 수 있다.
•
설정 파일 구성
MySQL 설정 파일은 하나의 my.cnf나 my.ini 파일에 여러 개의 설정 그룹을 담을 수 있으며, 대체로 실행 프로그램 이름을 그룹명으로 사용한다.
설정 파일의 각 그룹은 같은 파일을 공유하지만 서로 무관하게 적용된다.
[mysqld_safe] malloc-lib = /opt/lib/libtcmalloc_minimal.so [mysqld] socket = /usr/local/mysql/tmp/mysql.sock port = 3306 [mysql] default-character-set = utf8mb4 socket = /usr/local/mysql/tmp/mysql.sock port = 3304 [mysqldump] default-character-set = utf8mb4 socket = /usr/local/mysql/tmp/mysql.sock port = 3305
Plain Text
복사

시스템 변수

MySQL 서버는 기동하면서 설정 파일의 내용을 읽어 메모리나 작동 방식을 초기화하고, 접속된 사용자를 제어하기 위해 이러한 값을 시스템 변수로 저장해 둔다.
mysql > SHOW GLOBAL VARIABLES;
Plain Text
복사
시스템 변수 값이 어떻게 MySQL 서버와 클라이언트에 영향을 미치는지 판단하려면 각 변수가 글로벌 변수인지 세션 변수인지 구분할 수 있어야 한다.
•
시스템 변수의 속성
◦
Cmd-Line
MySQL 서버의 명령행 인자로 설정될 수 있는지 여부를 나타낸다.
◦
Option File
my.cnf 설정 파일로 제어할 수 있는지 여부를 나타낸다.
◦
System Var
시스템 변수인지 아닌지를 나타낸다.
대부분의 시스템 변수는 언더스코어 _를 구분자로 사용하며, 명령행 옵션으로만 사용 가능한 설정은 대쉬 -를 구분자로 사용한다.
◦
Var Scope
시스템 변수의 적용 범위를 나타낸다.
서버 전체인 Global, 서버와 클라이언트 간의 커넥션인 Session, 세션과 글로벌 모두인 Both
◦
Dynamic
시스템 변수가 동적인지 정적인지 구분한다.
•
글로벌 변수와 세션 변수
글로벌 변수는 하나의 MySQL 서버 인스턴스에서 전체적으로 영향을 미치는 시스템 변수를 의미한다.
세션 변수는 MySQL 클라이언트가 MySQL 서버에 접속할 때 기본으로 부여하는 옵션의 기본값을 제어하는데 사용된다.
여기서 기본값은 글로벌 변수이며, 커넥션별로 설정값을 서로 다르게 지정할 수 있고, 한 번 연결된 커넥션의 세션 변수는 서버에서 강제로 변경할 수 없다.
순수하게 범위가 세션인 변수는 MySQL 서버의 설정 파일에 초기값을 명시할 수 없으며, 커넥션이 만들어지는 순간부터 해당 커넥션에서만 유효한 설정 변수를 의미한다.
•
정적 변수와 동적 변수
MySQL 서버가 가동 중인 상태에서 변경 가능한지에 따라 동적 변수와 정적 변수로 구분된다.
디스크에 저장된 설정 파일의 내용은 변경하더라도 MySQL 서버가 재시작하기 전에는 적용되지 않는다.
하지만 SHOW 명령으로 MySQL 서버에 적용된 변수값을 확인하거나 SET 명령을 이용해 값을 바꿀 수 있다.
SET 명령을 통해 변경되는 시스템 변수값은 설정 파일에 반영되는 것은 아니기 때문에 재시작하면 다시 설정 파일의 내용으로 초기화된다.
•
SET PERSIST와 SET PERSIST_ONLY
SET PERSIST 명령으로 시스템 변수를 변경하면 MySQL 서버는 변경된 값을 즉시 적용함과 동시에 별도의 설정 파일 mysqld-auto.cnf에 변경 내용을 추가로 기록해 두어 재시작 시 참조해서 적용한다.
만약 현재 실행 중인 MySQL 서버에는 변경 내용을 적용하지 않고 다음 재시작을 위해 mysqld-auto.cnf 파일에만 변경 내용을 기록해두고자 한다면 SET PERSIST_ONLY 명령어를 사용하면 된다.
SET PERSIST나 SET PERSIST_ONLY 명령으로 추가된 시스템 변수의 내용을 삭제하는 경우에 mysqld-auto.cnf 파일의 내용을 직접 변경하다 오류를 만드는 경우 MySQL 서버가 시작되지 못할 수도 있다.
이 경우 RESET PERSIST 명령어를 사용하는 것이 안전하다.

3. 사용자 및 권한

사용자 식별

MySQL의 사용자는 다른 DBMS와는 다르게 사용자의 계정뿐 아니라 사용자의 접속 지점(클라이언트가 실행된 호스트명이나 도메인 또는 IP 주소)도 계정의 일부가 된다.
따라서 MySQL에서 계정을 언급할 때는 항상 아이디와 호스트를 함께 명시해야 한다.
아이디와 IP 주소를 감싸는 역따옴표(`)는 식별자를 감싸는 따옴표 역할을 하며, 종종 홑따옴표(')로 바뀌어서 사용되기도 한다.
'svc_id'@'127.0.0.1'
Plain Text
복사
이 계정은 MySQL 서버가 기동 중인 로컬 호스트에서 svc_id라는 아이디로 접속할 때만 사용될 수 있는 계정이다.
모든 외부 컴퓨터에서 접속이 가능한 사용자 계정을 생성하고 싶다면 사용자 계정의 호스트 부분을 % 문자로 대체하면 된다.
만약 접속하려는 계정의 호스트 조건이 여러 개가 있다면 MySQL은 더 좁은 범위의 계정을 먼저 선택한다.

시스템 계정과 일반 계정

MySQL 8.0부터 계정은 SYSTEM_USER 권한을 가지고 있느냐에 따라 시스템 계정과 일반 계정으로 구분된다.
시스템 계정은 일반 계정을 관리할 수 있지만 일반 계정은 시스템 계정을 관리할 수 없다.
또한 다음과 같이 데이터베이스 서버 관리와 관련된 중요 작업은 시스템 계정으로만 수행할 수 있다.

계정 생성

MySQL 5.7 버전까지는 GRANT 명령어로 권한의 부여와 동시에 계정 생성이 가능했다.
하지만 MySQL 8.0 버전부터는 계정의 생성은 CREATE USER 명령으로, 권한 부여는 GRANT 명령으로 구분해서 실행하도록 바뀌었다.
mysql > CREATE USER 'user'@'%' IDENTIFIED WITH 'mysql_native_password' BY 'password' REQUIRE NONE PASSWORD EXPIRE INTERVAL 30 DAY ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
Plain Text
복사
•
IDENTIFIED WITH
사용자의 인증 방식과 비밀번호를 설정하며, IDENTIFIED WITH 뒤에는 반드시 인증 방식(인증 플러그인 이름)을 명시해야 한다.
MySQL 5.7 버전까지는 Native Authentication이 기본 인증 방식으로 사용됐지만, MySQL 8.0 버전부터는 Caching SHA-2 Authentication이 기본 인증으로 바뀌었다.
Caching SHA-2 Pluggable Authentication은 SCRAM(Salted Challenge Response Authentication Mechanism) 인증 방식을 사용하며, 평문 비밀번호를 5000번 이상 암호화 해시 함수를 실행해야 로그인 요청을 보낼 수 있다.
•
REQUIRE
MySQL 서버에 접속할 때 암호화된 SSL/TLS 채널을 사용할지 여부를 설정한다.
REQUIRE 옵션을 SSL로 설정하지 않았다고 하더라도 Caching SHA-2 Authentication 인증 방식을 사용하면 암호화된 채널로 접속할 수 있다.
•
PASSWORD EXPIRE
비밀번호의 유효 기간을 설정하는 옵션이며, 별도로 명시하지 않으면 default_password_lifetime 시스템 변수의 기본값으로 설정된다.
•
PASSWORD HISTORY
한 번 사용했던 비밀번호를 재사용하지 못하게 설정하는 옵션이며, 별도로 명시하지 않으면 password_history 시스템 변수에 저장된 기간으로 설정된다.
mysql DB의 password_history 테이블을 사용하여 이전에 사용했던 비밀번호를 기억한다.
•
PASSWORD REUSE INTERVAL
한 번 사용했던 비밀번호의 재사용 금지 기간을 설정하는 옵션이며, 별도로 명시하지 않으면 password_reuse_interval 시스템 변수에 저장된 기간으로 설정된다.
•
PASSWORD REQUIRE
비밀번호가 만료되어 새로운 비밀번호로 변경할 때 현재 비밀번호(변경하기 전 만료된 비밀번호)를 필요로 할지 말지를 결정하는 옵션이며, 별도로 명시되지 않으면 password_require_current 시스템 변수의 값으로 설정된다.
•
ACCOUNT LOCK / UNLOCK
계정 생성 시 또는 ALTER USER 명령을 사용해 계정 정보를 변경할 때 계정을 사용하지 못하게 잠글지 여부를 결정한다.

비밀번호 관리

•
고수준 비밀번호
비밀번호를 쉽게 유추할 수 있는 단어들이 사용되지 않게 글자의 조합을 강제하거나 금칙어를 설정하는 기능도 있다.
MySQL 5.7 버전까지는 validate_password가 플러그인 형태로 제공됐지만, MySQL 8.0 버전부터는 컴포넌트 형태로 제공된다.
mysql > INSTALL COMPONENT 'file://component_validate_password'; mysql > SELECT * FROM mysql.component; +--------------+--------------------+------------------------------------+ | component_id | component_group_id | component_urn | +--------------+--------------------+------------------------------------+ | 1 | 1 | file://component_validate_password | +--------------+--------------------+------------------------------------+ mysql> SHOW GLOBAL VARIABLES LIKE 'validate_password%'; +-------------------------------------------------+--------+ | Variable_name | Value | +-------------------------------------------------+--------+ | validate_password.changed_characters_percentage | 0 | | validate_password.check_user_name | ON | | validate_password.dictionary_file | | | validate_password.length | 8 | | validate_password.mixed_case_count | 1 | | validate_password.number_count | 1 | | validate_password.policy | MEDIUM | | validate_password.special_char_count | 1 | +-------------------------------------------------+--------+
Plain Text
복사
비밀번호 정책은 다음 3가지 중에서 선택할 수 있으며, 기본값은 MEDIUM으로 자동 설정된다.
◦
LOW : 비밀번호의 길이만 검증
◦
MEDIUM : 비밀번호의 길이를 검증하며, 숫자와 대소문자, 그리고 특수문자의 배합을 검증
◦
STRONG : MEDIUM 레벨의 검증을 모두 수행하며, 금칙어가 포함됐는지 여부까지 검증
length, mixed_case_count, number_count, special_char_count 시스템 변수를 통해 설정된 글자 수 이상을 포함하고 있는지 검증할 수 있다.
금칙어 파일은 한 줄에 하나씩 기록해서 텍스트 파일로 작성하여 dictionary_file 시스템 변수로 설정한다.
•
이중 비밀번호
데이터베이스 계정의 비밀번호는 보안을 위해 주기적으로 변경해야 하지만 지금까지는 서비스를 모두 멈추지 않고서는 비밀번호를 변경하는 것은 불가능했다.
이 문제를 해결하기 위해 이중 비밀번호 기능을 추가했으며, 기존 비밀번호 변경 구문에 RETAIN CURRENT PASSWORD 옵션만 추가하면 된다.
최근에 설정된 비밀번호는 프라이머리 비밀번호이고 이전 비밀번호는 세컨더리 비밀번호가 된다.
-- // 프라이머리 비밀번호 = 'old_password', 세컨더리 비밀번호 = '' mysql > ALTER USER 'root'@'localhost' IDENTIFIED BY 'old_password'; -- // 프라이머리 비밀번호 = 'new_password', 세컨더리 비밀번호 = 'old_password' mysql > ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password' RETAIN CURRENT PASSWORD; -- // 프라이머리 비밀번호 = 'new_password', 세컨더리 비밀번호 = '' mysql > ALTER USER 'root'@'localhost' DISCARD OLD PASSWORD;
Plain Text
복사
MySQL 서버에 접속하는 모든 응용 프로그램의 재시작이 완료되면 DISCARD OLD PASSWORD 명령으로 세컨더리 비밀번호를 삭제할 수 있다.

권한

MySQL 5.7 버전까지 권한은 서버의 소스코드에 고정적으로 명시돼 있는 정적 권한을 의미한다.
•
글로벌 권한 : 데이터베이스나 테이블 이외의 객체에 적용되는 권한
•
객체 권한 : 데이터베이스나 테이블을 제어하는데 필요한 권한
•
ALL PRIVILEGES : 글로벌로 사용되면 데이터베이스의 모든 권한, 객체로 사용되면 해당 객체의 모든 권한
MySQL 8.0 버전부터는 서버가 시작되면서 동적으로 생성하는 동적 권한이 추가됐다.
사용자에게 권한을 부여할 때는 GRANT 명령을 사용한다.
각 권한의 특성(범위)에 따라 GRANT 명령의 ON 절에 명시되는 오브젝트(DB나 테이블)의 내용이 바뀌어야 한다.
mysql > GRANT privilege_list ON db.table TO 'user'@'host';
Plain Text
복사
글로벌 권한은 특정 DB나 테이블에 부여될 수 없기 때문에 ON 절에 항상 모든 DB의 모든 오브젝트를 의미하는 *.* 를 사용한다.
-- // 모든 DB의 모든 오브젝트에 대해 권한을 부여 mysql > GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'user'@'localhost'; -- // 특정 DB의 모든 오브젝트에 대해 권한을 부여 mysql > GRANT SELECT, INSERT, UPDATE, DELETE ON employees.* TO 'user'@'localhost'; -- // 특정 DB의 특정 오브젝트에 대해 권한을 부여 mysql > GRANT SELECT, INSERT, UPDATE, DELETE ON employees.department TO 'user'@'localhost'; -- // 특정 DB의 특정 오브젝트의 특정 컬럼에 대해 권한을 부여 mysql > GRANT SELECT, INSERT, UPDATE(dept_name), DELETE ON employees.department TO 'user'@'localhost';
Plain Text
복사
여러 가지 레벨이나 범위로 권한을 설정하는 것이 가능하지만 테이블이나 컬럼 단위의 권한은 잘 사용하지 않는다.
컬럼 단위의 권한이 하나라도 생성되면 나머지 모든 테이블의 모든 컬럼에 대해서도 권한 체크를 하기 때문에 전체적인 성능에 영향을 미칠 수 있다.
컬럼 단위의 접근 권한이 필요하다면 별도의 VIEW를 만들어 사용하는 방법도 생각해볼 수 있다.

역할

MySQL 8.0 버전부터 권한을 묶어서 역할을 사용할 수 있게 됐다.
mysql > CREATE ROLE role_employees_read, role_employees_write; mysql > GRANT SELECT ON employees.* TO role_employees_read; mysql > GRANT INSERT, UPDATE, DELETE ON employees.* TO role_employees_write;
Plain Text
복사
CREATE ROLE 명령은 빈 껍데기만 있는 역할을 정의한 것이며, GRANT 명령으로 각 역할에 대해 실질적인 권한을 부여하면 된다.
mysql > CREATE USER reader@'127.0.0.1' IDENTIFIED BY 'qwerty'; mysql > CREATE USER writer@'127.0.0.1' IDENTIFIED BY 'qwerty'; mysql > GRANT role_employees_read TO reader@'127.0.0.1'; mysql > GRANT role_employees_write TO writer@'127.0.0.1'; linux > -h 127.0.0.1 -u reader -p mysql > SET ROLE 'role_employees_read'; linux > -h 127.0.0.1 -u writer -p mysql > SET ROLE 'role_employees_write';
Plain Text
복사
기본적으로 역할은 그 자체로 사용될 수 없고 계정에 권한과 역할을 부여해야 한다.
MySQL 서버는 로그인할 때 역할이 자동으로 활성화되지 않게 설정되어 있으며, activate_all_roles_on_login 시스템 변수를 통해 설정할 수 있다.
일반적으로 CREATE USER 명령으로 계정을 생성할 때는 계정 이름과 호스트 부분을 함께 명시한다.
하지만 CREATE ROLE 명령으로 역할을 생성할 때는 호스트 부분을 별도로 명시하지 않으며, 모든 호스트(%)가 자동으로 추가된다.
mysql DB의 user 테이블을 조회하면 계정과 역할이 모두 조회되는 이유는 내외부적으로 동일한 객체이기 때문이며, 데이터베이스 관리의 직무를 분리할 수 있게 해서 보안을 강화하는 용도로 사용될 수 있게 하기 위해서다.
CREATE USER 명령에 대해서는 권한이 없지만 CREAT ROLE 명령만 실행 가능한 사용자는 역할을 생성할 수 있다.
이렇게 생성된 역할은 계정과 동일한 객체를 생성하지만 실제 이 역할은 account_locked 컬럼의 값이 Y로 설정돼 있어서 로그인 용도로 사용할 수 없게 된다.

4-1. MySQL 엔진 아키텍처

MySQL의 전체 구조

MySQL 서버는 사람의 머리 역할을 담당하는 MySQL 엔진과 손발 역할을 담당하는 스토리지 엔진으로 구분할 수 있다.
스토리지 엔진은 핸들러 API를 만족하면 누구든지 구현해서 MySQL 서버에 추가해서 사용할 수 있다.
•
MySQL 엔진
클라이언트로부터의 접속 및 쿼리 요청을 처리하는 커넥션 핸들러와 SQL 파서 및 전처리기, 쿼리의 최적화된 실행을 위한 옵티마이저가 중심을 이룬다.
또한 MySQL은 표준 SQL(ANSI SQL) 문법을 지원하기 때문에 표준 문법에 따라 작성된 쿼리는 타 DBMS와 호환되어 실행될 수 있다.
•
스토리지 엔진
디스크 스토리지로부터 데이터를 읽어오는 부분은 스토리지 엔진이 전담한다.
MySQL 서버에서 MySQL 엔진은 하나지만 스토리지 엔진은 여러 개를 동시에 사용할 수 있다.
mysql > CREATE TABLE test(column1 INT, column2 INT) ENGINE=INNODB;
Plain Text
복사
테이블이 사용할 스토리지 엔진을 지정하면 모든 읽기 작업이나 변경 작업을 정의된 스토리지 엔진이 처리한다.
•
핸들러 API
MySQL 엔진의 쿼리 실행기에서 데이터를 쓰거나 읽어야 할 때는 스토리지 엔진에 쓰기 또는 읽기를 요청한다.
이러한 요청을 핸들러 요청이라 하고, 여기서 사용되는 API를 핸들러 API라고 한다.
mysql > SHOW GLOBAL STATUS LIKE 'Handler%';
Plain Text
복사
이 핸들러 API를 통해 얼마나 많은 데이터 작업이 있었는지를 확인할 수 있다.

MySQL 엔진의 스레딩 구조

MySQL 서버는 프로세스 기반이 아니라 스레드 기반으로 작동하며, 포그라운드 스레드와 백그라운드 스레드로 구분할 수 있다.
mysql > SELECT thread_id, name, type, processlist_user, processlist_host FROM performance_schema.threads ORDER BY type, thread_id;
Plain Text
복사
MySQL 서버에서 실행 중인 스레드의 목록은 performance_schema 데이터베이스의 threads 테이블을 통해 확인할 수 있다.
백그라운드 스레드 스레드의 개수는 MySQL 서버의 설정 내용에 따라 가변적일 수 있으며, 동일한 이름의 스레드는 동일 작업을 병렬로 처리하는 경우다.
•
포그라운드 스레드 (클라이언트 스레드)
포그라운드 스레드는 MySQL 서버에 접속된 클라이언트의 수만큼 존재하며, 각 클라이언트 사용자가 요청하는 쿼리 문장을 처리한다.
데이터를 가져오는 작업은 데이터 버퍼나 캐시로부터 가져오며, 버퍼나 캐시에 없는 경우에는 직접 디스크의 데이터나 인덱스 파일로부터 데이터를 읽어와서 작업을 처리한다.
InnoDB 테이블은 데이터 버퍼나 캐시까지만 포그라운드 스레드가 처리하고, 나머지 버퍼로부터 디스크까지 기록하는 작업은 백그라운드 스레드가 처리한다.
MyISAM 테이블은 디스크 쓰기 작업까지 포그라운드 스레드가 처리한다.
작업을 마친 스레드는 스레드 캐시로 되돌아가며, 스레드 캐시에 최대 개수 이상의 대기 중인 스레드가 있다면 종료시킨다.
•
백그라운드 스레드
다음과 같은 작업을 백그라운드 스레드로 처리한다.
◦
인서트 버퍼를 병합하는 스레드
◦
로그를 디스크로 기록하는 스레드
◦
InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드
◦
데이터를 버퍼로 읽어 오는 스레드
◦
잠금이나 데드락을 모니터링하는 스레드
MySQL 5.5버전부터 innodb_write_io_threads, innodb_read_io_threads 시스템 변수로 스레드의 개수를 설정할 수 있다.
데이터를 읽는 작업은 주로 클라이언트 스레드에서 처리되기 때문에 읽기 스레드는 많이 설정할 필요가 없지만, 쓰기 스레드는 아주 많은 작업을 백그라운드로 처리하기 때문에 충분히 설정하는 것이 좋다.
MyISAM은 포그라운드 스레드가 쓰기 작업까지 함께 처리하므로 백그라운드 스레드를 사용하지 않지만, InnoDB는 백그라운드 스레드를 통해 쓰기 작업을 버퍼링해서 일괄 처리하는 기능이 탑재되어 있다.

메모리 할당 및 사용 구조

MySQL에서 사용되는 메모리 공간은 크게 글로벌 메모리 영역과 로컬 메모리 영역으로 구분할 수 있다.
MySQL 서버 내에 존재하는 많은 스레드가 공유해서 사용하는 공간인지 여부에 따라 구분된다.
•
글로벌 메모리 영역
MySQL 서버가 시작되면서 운영체제로부터 메모리 공간이 할당되며, 모든 스레드에 의해 공유된다.
◦
테이블 캐시
◦
InnoDB 버퍼 풀
◦
InnoDB 어댑티브 해시 인덱스
◦
InnoDB 리두 로그 버퍼
•
로컬 메모리 영역
MySQL 서버 상에 존재하는 클라이언트 스레드가 쿼리를 처리하는데 사용하는 메모리 영역이다.
각 클라이언트 스레드별로 독립적으로 할당되며 절대 공유되어 사용되지 않는다는 특징이 있다.
각 쿼리의 용도별로 필요할 때만 공간이 할당되고 필요하지 않은 경우에는 할당되지 않을 수 있다. (정렬 버퍼, 조인 버퍼)
그리고 커넥션이 열려 있는 동안 계속 할당된 상태로 남아 있는 공간(커넥션 버퍼, 결과 버퍼)도 있고, 쿼리를 실행하는 순간에만 할당했다가 다시 해제하는 공간(정렬 버퍼, 조인 버퍼)도 있다.
◦
정렬 버퍼
◦
조인 버퍼
◦
바이너리 로그 캐시
◦
네트워크 버퍼

플러그인 스토리지 엔진 모델

MySQL은 플러그인 모델을 사용하며, 스토리지 엔진은 플러그인으로 구현되어 제공된다.
거의 대부분의 작업은 MySQL 엔진에서 처리되고, 데이터 읽기/쓰기 작업만 스토리지 엔진에 의해 처리된다.
MySQL 엔진은 스토리지 엔진을 조정하기 위해 핸들러 API를 사용하게 되며, 스토리지 엔진은 이러한 API를 구현한다.
만약 MySQL 서버에 포함되지 않은 스토리지 엔진을 사용하려면, 플러그인 형태로 빌드된 스토리지 엔진 라이브러리를 다운로드해서 끼워 넣고 MySQL 서버를 다시 빌드하면 사용할 수 있다.

컴포넌트

MySQL 8.0버전부터는 기존의 플러그인 아키텍처를 대체하기 위해 컴포넌트 아키텍처가 지원된다.
컴포넌트는 다음과 같은 플러그인의 단점들을 보완한다.
•
플러그인은 오직 MySQL 서버와 인터페이스할 수 있고, 플러그인끼리는 통신할 수 없음
•
플러그인은 MySQL 서버의 변수나 함수를 직접 호출하기 때문에 안전하지 않음 (캡슐화 안 됨)
•
플러그인은 상호 의존 관계를 설정할 수 없어서 초기화가 어려움
이로 인해 MySQL 5.7버전까지 플러그인 형태로 제공됐던 비밀번호 검증 기능이 컴포넌트로 개선됐다.

쿼리 실행 구조

•
쿼리 파서
사용자 요청으로 들어온 쿼리 문장을 토큰(MySQL이 인식할 수 있는 최소 단위의 어휘나 기호)으로 분리해 트리 형태의 구조로 만들어 내는 작업을 한다.
쿼리 문장의 기본 문법 오류는 이 과정에서 발견되고 사용자에게 오류 메시지를 전달하게 된다.
•
전처리기
파서 과정에서 만들어진 파서 트리를 기반으로 쿼리 문장에 구조적인 문제점이 있는지 확인한다.
각 토큰을 테이블 이름이나 컬럼 이름, 또는 내장 함수와 같은 개체를 매핑해 해당 객체의 존재 여부와 객체의 접근 권한 등을 확인하는 과정을 이 단계에서 수행한다.
실제 존재하지 않거나 권한상 사용할 수 없는 개체의 토큰은 이 단계에서 걸러진다.
•
옵티마이저
사용자의 요청으로 들어온 쿼리 문장을 어떻게 저렴한 비용으로 가장 빠르게 처리할지를 결정하는 역할을 담당한다.
•
실행 엔진
옵티마이저에 의해 만들어진 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결하는 역할을 수행한다.
옵티마이저가 GROUP BY를 처리하기 위해 임시 테이블을 사용하기로 결정한 경우 다음과 같이 실행된다.
1.
실행 엔진이 핸들러에게 임시 테이블을 만들라고 요청
2.
다시 실행 엔진은 WHERE 절에 일치하는 레코드를 읽어오라고 핸들러에게 요청
3.
읽어온 레코드들을 1번에서 준비한 임시 테이블로 저장하라고 다시 핸들러에게 요청
4.
데이터가 준비된 임시 테이블에서 필요한 방식으로 데이터를 읽어 오라고 핸들러에게 다시 요청
5.
최종적으로 실행 엔진은 결과를 사용자나 다른 모듈로 넘김
•
핸들러 (스토리지 엔진)
핸들러는 결국 스토리지 엔진을 의미하며, MySQL 서버의 가장 밑단에서 MySQL 실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어 오는 역할을 담당한다.

쿼리 캐시

쿼리 캐시는 SQL의 실행 결과를 메모리에 캐시하고, 동일 SQL 쿼리가 실행되면 테이블을 읽지 않고 즉시 결과를 반환하기 때문에 매우 빠른 성능을 보였다.
하지만 쿼리 캐시는 테이블의 데이터가 변경되면 캐시에 저장된 결과 중에서 변경된 테이블과 관련된 것들은 모두 삭제해야 했고, 이는 심각한 동시 처리 성능 저하를 유발한다.
결국 MySQL 8.0버전부터 쿼리 캐시는 MySQL 서버의 기능에서 완전히 제거되었다.

스레드 풀

MySQL의 엔터프라이즈 에디션은 스레드 풀 기능을 제공하지만, 커뮤니티 에디션은 스레드 풀 기능을 지원하지 않는다.
커뮤니티 에디션에서 스레드 풀 기능을 사용하고자 한다면 Percona Server에서 스레드 풀 플러그인 라이브러리를 설치해서 사용하면 된다.
스레드 풀은 내부적으로 사용자의 요청을 처리하는 스레드 개수를 줄여서 동시 처리되는 요청이 많다 하더라도 MySQL 서버의 CPU가 제한된 개수의 스레드 처리에만 집중할 수 있게 해서 서버의 자원 소모를 줄이는 것이 목적이다.
동시에 실행 중인 스레드들을 CPU가 최대한 잘 처리해낼 수 있는 수준으로 줄여서 빨리 처리하게 하는 기능이기 때문에 스케줄링 과정에서 CPU 시간을 제대로 확보하지 못하는 경우에는 쿼리 처리가 더 느려지는 사례도 발생할 수 있다는 점에 주의하자.
물론 제한된 수의 스레드만으로 CPU가 처리하도록 적절히 유도한다면 CPU의 프로세서 친화도도 높이고 운영체제 입장에서는 불필요한 컨텍스트 스위치를 줄여서 오버헤드를 낮출 수 있다.
스레드 그룹의 모든 스레드가 일을 처리하고 있다면 스레드 풀은 해당 스레드 그룹에 새로운 작업 스레드를 추가할지, 아니면 기존 작업 스레드가 처리를 완료할 때까지 기다릴지 여부를 판단해야 한다.
스레드 풀의 타이머 스레드는 주기적으로 스레드 그룹의 상태를 체크해서 thread_pool_stall_limit 시스템 변수에 정의된 시간만큼 작업 스레드가 처리 중인 작업을 끝내지 못하면 새로운 스레드를 생성해서 스레드 그룹에 추가한다.
즉, 스레드 풀은 thread_pool_stall_limit 시간 동안 기다려야 새로운 요청을 처리할 수 있다.
Percona Server의 스레드 풀 플러그인은 선순위 큐와 후순위 큐를 이용해 특정 트랜잭션이나 쿼리를 우선적으로 처리할 수 있는 기능도 제공한다.
이렇게 먼저 시작된 트랜잭션 내에 속한 SQL을 빨리 처리해주면 해당 트랜잭션이 가지고 있던 잠금이 빨리 해제되고 잠금 경합을 낮춰서 전체적인 처리 성능을 향상시킬 수 있다.

트랜잭션 지원 메타데이터

테이블의 구조 정보와 스토어드 프로그램 등의 정보를 데이터 딕셔너리 또는 메타데이터라고 한다.
MySQL 5.7버전까지는 테이블의 구조를 FRM 파일에 저장하고 일부 스토어드 프로그램 또한 파일(TRN, TRG, PAR 등) 기반으로 관리했다.
하지만 이러한 파일 기반의 메타데이터는 생성 및 변경 작업이 트랜잭션을 지원하지 않아서 테이블의 생성 또는 변경 도중 MySQL 서버가 비정상적으로 종료되면 일관되지 않은 상태로 남는 문제가 있었다.
MySQL 8.0버전부터는 데이터 딕셔너리와 시스템 테이블이 모두 트랜잭션 기반의 InnoDB 스토리지 엔진의 mysql 데이터베이스에 저장되도록 개선되면서 스키마 변경이 완전한 성공 또는 완전한 실패로 정리된다.
InnoDB 스토리지 엔진 이외의 스토리지 엔진을 사용하는 테이블들을 위해 SDI(Serialized Dictionary Information) 파일을 사용하며, 기존의 FRM 파일과 동일한 역할을 한다.

4-2. InnoDB 스토리지 엔진 아키텍처

InnoDB 스토리지 엔진은 MySQL에서 사용할 수 있는 스토리지 엔진 중 거의 유일하게 레코드 기반의 잠금을 제공하며, 그 때문에 높은 동시성 처리가 가능하고 안정적이며 성능이 뛰어나다.

프라이머리 키에 의한 클러스터링

InnoDB의 모든 테이블은 기본적으로 프라이머리 키를 기준으로 클러스터링되어 저장된다.
즉, 프라이머리 키 값의 순서대로 디스크에 저장된다는 뜻이며, 모든 세컨더리 인덱스는 레코드의 주소 대신 프라이머리 키의 값을 논리적인 주소로 사용한다.
프라이머리 키가 클러스터링 인덱스이기 때문에 프라이머리 키를 이용한 레인지 스캔은 상당히 빨리 처리될 수 있다.

외래 키 지원

외래 키에 대한 지원은 InnoDB 스토리지 엔진 레벨에서 지원하는 기능으로 MyISAM이나 MEMORY 테이블에서는 사용할 수 없다.
InnoDB에서 외래 키는 부모 테이블과 자식 테이블 모두 해당 컬럼에 인덱스 생성이 필요하고, 변경 시에는 반드시 부모 테이블이나 자식 테이블에 데이터가 있는지 체크하는 작업이 필요하므로 잠금이 여러 테이블로 전파되고, 그로 인해 데드락이 발생할 때가 많으므로 개발할 때도 외래 키의 존재에 주의하는 것이 좋다.
외래 키로 인해 수동으로 데이터를 적재하거나 스키마 변경 등의 관리 작업이 실패할 수 있다.
이런 경우에는 FOREIGN_KEY_CHECKS 시스템 변수를 OFF로 설정하면 외래 키 관계에 대한 체크 작업(ON DELETE CASCADE, ON UPDATE CASCADE)을 일시적으로 멈출 수 있다.
만약 외래 키 체크를 일시적으로 중지한 상태에서 외래 키 관계를 가진 부모 테이블의 레코드를 삭제했다면 반드시 자식 테이블의 레코드도 삭제해서 일관성을 맞춰준 후 다시 외래 키 체크 기능을 활성화해야 한다.

MVCC(Multi Version Concurrency Control)

일반적으로 레코드 레벨의 트랜잭션을 지원하는 DBMS가 제공하는 기능이며, MVCC의 가장 큰 목적은 잠금을 사용하지 않는 일관된 읽기를 제공하는 데 있다.
InnoDB는 언두 로그를 이용해 이 기능을 구현하며, 여기서 멀티 버전이라 함은 하나의 레코드에 대해 여러 개의 버전이 동시에 관리된다는 의미다.
mysql > UPDATE member SET m_area='경기' WHERE m_id=12;
Plain Text
복사
UPDATE 쿼리가 실행되면 커밋 실행 여부와 관계없이 InnoDB의 버퍼 풀은 새로운 값인 ‘경기’로 업데이트된다.
그리고 디스크의 데이터 파일에는 체크포인트나 InnoDB의 Write 스레드에 의해 새로운 값으로 업데이트돼 있을 수도 있고 아닐 수도 있다.
아직 COMMIT이나 ROLLBACK이 되지 않은 상태에서 다른 사용자가 해당 레코드를 조회하면 어디에 있는 데이터를 조회할까?
이 질문의 답은 트랜잭션의 격리 수준에 따라 다르다.
READ_UNCOMMITED인 경우에는 InnoDB 버퍼 풀이 현재 가지고 있는 변경된 데이터를 읽어서 반환한다.
READ_COMMITED나 그 이상의 격리 수준인 경우에는 아직 커밋되지 않았기 때문에 변경되기 이전의 내용을 보관하고 있는 언두 영역의 데이터를 반환한다.
이러한 과정을 MVCC라고 표현하며, 하나의 레코드에 대해 2개의 버전이 유지되고 필요에 따라 어느 데이터가 보여지는지 여러 가지 상황에 따라 달라지는 구조다.
트랜잭션이 길어지면 언두에서 관리하는 예전 데이터가 삭제되지 못하고 오랫동안 관리돼야 하기 때문에, 예전 버전의 데이터는 무한히 많아질 수 있으며 자연히 언두 영역이 저장되는 시스템 테이블스페이스의 공간이 많이 늘어나는 상황이 발생할 수도 있다.
이 상태에서 COMMIT 명령을 실행하면 지금의 상태를 영구적인 데이터로 만들며 언두 영역의 내용은 필요로 하는 트랜잭션이 더는 없을 때 삭제하고, ROLLBACK을 실행하면 언두 영역에 있는 백업된 데이터를 버퍼 풀로 다시 복구하고 언두 영역의 내용을 즉시 삭제해 버린다.

잠금 없는 일관된 읽기(Non-Locking Consistent Read)

InnoDB 스토리지 엔진은 MVCC 기술을 통해 잠금을 걸지 않고 읽기 작업을 수행할 수 있다.
잠금을 걸지 않기 때문에 InnoDB에서 읽기 작업은 다른 트랜잭션이 가지고 있는 잠금을 기다리지 않고, 읽기 작업이 가능하다.
격리 수준이 SERIALIZABLE이 아닌 READ_UNCOMMITED나 READ_COMMITED, REPEATABLE_READ 수준인 경우 INSERT와 연결되지 않은 순수한 SELECT 작업은 언두 로그를 이용하여 다른 트랜잭션의 변경 작업과 관계없이 항상 잠금을 대기하지 않고 바로 실행된다.
오랜 시간 동안 활성 상태인 트랜잭션으로 인해 MySQL 서버가 느려지거나 문제가 발생할 때가 가끔 있는데, 이러한 일관된 읽기를 위해 언두 로그를 삭제하지 못하고 계속 유지해야 하기 때문에 발생하는 문제다.
따라서 트랜잭션이 시작됐다면 가능한 한 빨리 롤백이나 커밋을 통해 트랜잭션을 완료하는 것이 좋다.

자동 데드락 감지

InnoDB 스토리지 엔진은 내부적으로 잠금이 교착 상태에 빠지지 않았는지 체크하기 위해 잠금 대기 목록을 그래프(Wait-for List) 형태로 관리한다.
또한 InnoDB 스토리지 엔진은 데드락 감지 스레드를 가지고 있어서 데드락 감지 스레드가 주기적으로 잠금 대기 그래프를 검사해 교착 상태에 빠진 트랜잭션들을 찾아서 그중 하나를 강제 종료한다.
이때 어느 트랜잭션을 먼저 강제 종료할 것인지를 판단하는 기준은 트랜잭션의 언두 로그 양이며, 언두 로그 레코드를 더 적게 가진 트랜잭션이 일반적으로 롤백의 대상이 된다.
하지만 InnoDB 스토리지 엔진은 상위 레이어인 MySQL 엔진에서 관리되는 테이블 잠금(LOCK TABLES 명령)은 볼 수가 없어서 데드락 감지가 불확실할 수도 있기 때문에, innodb_table_locks 시스템 변수를 활성화하여 InnoDB 스토리지 엔진 내부의 레코드 잠금뿐만 아니라 테이블 레벨의 잠금까지 감지할 수 있게 해야 한다.
일반적인 서비스에서는 데드락 감지 스레드가 트랜잭션의 잠금 목록을 검사해서 데드락을 찾아내는 작업은 크게 부담되지 않는다.
하지만 동시 처리 스레드가 매우 많아지거나 각 트랜잭션이 가진 잠금의 개수가 많아지면 데드락 감지 스레드가 느려진다.
이런 문제점을 해결하기 위해 innodb_deadlock_detect 시스템 변수를 OFF하여 데드락 감지 스레드를 작동하지 않게 할 수 있다.
데드락 감지 스레드가 작동하지 않으면 데드락이 발생해도 누군가 중재하지 않기 때문에 무한정 대기하게 되며, 이를 방지하기 위해 innodb_lock_wait_timeout 시스템 변수를 통해 데드락 상황에서 일정 시간이 지나면 실패하고 에러를 반환하게 할 수 있다.

자동화된 장애 복구

MySQL 서버가 시작될 때 완료되지 못한 트랜잭션이나 디스크에 일부만 기록된 데이터 페이지 등에 대한 일련의 복구 작업은 InnoDB 스토리지 엔진에 의해 자동으로 진행된다.
하지만 MySQL 서버와 무관하게 디스크나 서버 하드웨어 이슈로 InnoDB 스토리지 엔진이 자동으로 복구를 못 하고 MySQL 서버가 종료되는 경우도 발생할 수 있다.
이때는 MySQL 서버의 설정 파일에 innodb_force_recovery 시스템 변수를 설정하고 MySQL 서버를 시작해서 선별적으로 손상 여부 검사 과정을 진행할 수 있게 해야 한다.
innodb_force_recovery 옵션에 설정 가능한 값은 1부터 6까지인데, 각 숫자 값으로 복구되는 장애 상황과 해결 방법은 다음과 같다.
innodb_force_recovery가 0이 아닌 복구 모드에서는 SELECT 이외의 INTER, UPDATE, DELETE 같은 쿼리는 수행할 수 없다.
•
1 (SRV_FORCE_IGNORE_CORRUPT)
InnoDB의 테이블스페이스의 데이터나 인덱스 페이지에서 손상된 부분이 발견돼도 무시하고 MySQL 서버를 재시작한다.
에러 로그 파일에 ‘Database page corruption on disk or a failed’ 메시지가 출력될 때는 대부분 이 경우에 해당한다.
•
2 (SRV_FORCE_NO_BACKGROUND)
InnoDB는 트랜잭션의 롤백을 위해 언두 데이터를 관리하는데, 트랜잭션이 커밋되어 불필요한 언두 데이터는 InnoDB의 메인 스레드에 의해 주기적으로 삭제(Undo perge)된다.
메인 스레드가 언두 데이터를 삭제하는 과정에서 장애가 발생한다면, 이 복구 모드를 사용하여 메인 스레드를 시작하지 않고 MySQL 서버를 시작한다.
•
3 (SRV_FORCE_NO_TRX_UNDO)
InnoDB에서 트랜잭션이 실행되면 롤백에 대비해 변경 전의 데이터를 언두 영역에 기록한다.
일반적으로 MySQL 서버는 다시 시작하면서 언두 영역의 데이터를 먼저 데이터 파일에 적용하고 그 다음 리두 로그의 내용을 다시 덮어써서 장애 시점의 데이터 상태를 만들어낸다.
그리고 정상적인 MySQL 서버의 시작에서는 최종적으로 커밋되지 않은 트랜잭션은 롤백을 수행한다.
이 복구 모드는 커밋되지 않고 종료된 트랜잭션은 계속 그 상태로 남아 있게 MySQL 서버를 시작하는 모드다.
•
4 (SRV_FORCE_NO_IBUF_MERGE)
InnoDB는 INSERT, UPDATE, DELETE 등의 데이터 변경으로 인한 인덱스 변경 작업을 상황에 따라 즉시 처리할 수도 있고 인서트 버퍼에 저장해두고 나중에 처리할 수도 있다.
이렇게 인서트 버퍼에 기록된 내용은 언제 데이터 파일에 병합될지 알 수 없다.
MySQL을 종료해도 병합되지 않을 수 있는데, 만약 MySQL이 재시작되면서 인서트 버퍼의 손상을 감지하면 InnoDB는 에러를 발생시키고 MySQL 서버는 시작하지 못한다.
이 복구 모드는 InnoDB 스토리지 엔진이 인서트 버퍼의 내용을 무시하고 강제로 MySQL 서버가 시작되게 한다.
•
5 (SRV_FORCE_NO_UNDO_LOG_SCAN)
InnoDB 스토리지 엔진의 언두 로그를 사용할 수 없다면 MySQL 서버를 시작할 수 없다.
이 복구 모드로 시작하면 InnoDB 엔진이 언두 로그를 모두 무시하고 MySQL을 시작할 수 있다.
하지만 이 모드로 복구되면 MySQL 서버가 종료되던 시점에 커밋되지 않았던 작업도 모두 커밋된 것처럼 처리되므로 실제로는 잘못된 데이터가 데이터베이스에 남는 것이라고 볼 수 있다.
•
6 (SRV_FORCE_NO_LOG_REDO)
InnoDB 스토리지 엔진의 리두 로그가 손상되면 MySQL 서버가 시작되지 못한다.
이 복구 모드로 시작하면 InnoDB 엔진이 리두 로그를 모두 무시하고 MySQL을 시작할 수 있다.
하지만 커밋됐다 하더라도 리두 로그에만 기록되고 데이터 파일에 기록되지 않은 데이터는 모두 무시된다.
일단 MySQL 서버가 기동되고 InnoDB 테이블이 인식된다면, mysqldump 프로그램이나 SELECT INTO OUTFILE 명령을 이용해 덤프해서 데이터베이스를 다시 구축하는 것이 좋다.
마지막 풀 백업 시점부터 장애 시점까지의 바이너리 로그가 있다면 InnoDB의 복구를 이용하는 것보다 풀 백업과 바이너리 로그로 복구하는 편이 데이터 손실이 더 적을 수 있다.

InnoDB 버퍼 풀

InnoDB 스토리지 엔진에서 가장 핵심적인 부분으로, 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간이다.
쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할도 같이 한다.
일반적인 어플리케이션에서는 INSERT, UPDATE, DELETE처럼 데이터를 변경하는 쿼리는 데이터 파일의 이곳저곳에 위치한 레코드를 변경하기 때문에 랜덤한 디스크 작업을 발생시킨다.
하지만 버퍼 풀이 이러한 변경된 데이터를 모아서 처리하면 랜덤한 디스크 작업의 횟수를 줄일 수 있다.
•
버퍼 풀의 크기 설정
InnoDB의 버퍼 풀은 단순하게 설정해서 되는 값이 아니며, 운영체제와 각 클라이언트 스레드가 사용할 메모리도 충분히 고려해서 설정해야 한다.
레코드 버퍼는 각 클라이언트 세션에서 테이블의 레코드를 읽고 쓸 때 버퍼로 사용하는 공간을 말하는데, 커넥션이 많고 사용하는 테이블도 많다면 레코드 버퍼 용도로 사용되는 메모리 공간이 꽤 많이 필요해질 수도 있다.
다행히 MySQL 5.7버전부터는 InnoDB 버퍼 풀의 크기를 동적으로 조절할 수 있게 개선됐다.
운영체제의 전체 메모리 공간이 8GB 미만이라면 50% 정도만 InnoDB 버퍼 풀로 설정하고 나머지 메모리 공간은 MySQL 서버와 운영체제, 그리고 다른 프로그램이 사용할 수 있는 공간으로 확보해주는 것이 좋다.
전체 메모리 공간이 그 이상이라면 InnoDB 버퍼 풀의 크기를 전체 메모리의 50%에서 시작해서 조금씩 올려가면서 최적점을 찾는다.
InnoDB 버퍼 풀은 innodb_buffer_pool_size 시스템 변수로 크기를 설정할 수 있으며, 동적으로 버퍼 풀의 크기를 확장할 수 있다.
버퍼 풀을 더 크게 변경하는 작업은 시스템 영향도가 크지 않지만, 버퍼 풀의 크기를 줄이는 작업은 서비스 영향도가 매우 크므로 가능하면 버퍼 풀의 크기를 줄이는 작업은 하지 않도록 주의하자.
InnoDB 버퍼 풀은 내부적으로 128MB 청크 단위로 쪼개어 관리되는데, 크기를 줄이거나 늘릴 때는 128MB 단위로 처리된다.
InnoDB 버퍼 풀은 전통적으로 버퍼 풀 전체를 관리하는 잠금(세마포어)으로 인해 내부 잠금 경합을 많이 유발해왔는데, 이런 경합을 줄이기 위해 버퍼 풀을 여러 개로 쪼개어 관리할 수 있게 개선됐다.
innodb_buffer_pool_instances 시스템 변수를 이용해 버퍼 풀을 여러 개의 버퍼 풀 인스턴스로 분리해서 관리할 수 있다.
기본적으로 버퍼 풀 인스턴스의 개수는 8개로 초기화되지만, 메모리 크기가 1GB 미만이라면 1개만 생성되고, 40GB 이하 수준이라면 기본값인 8개를 유지하고, 메모리가 크다면 버퍼 풀 인스턴스당 5GB 정도가 되게 인스턴스 개수를 설정하는 것이 좋다.
•
버퍼 풀의 구조
InnoDB 스토리지 엔진은 버퍼 풀이라는 거대한 메모리 공간을 페이지 크기(innodb_page_size)의 조각으로 쪼개어 InnoDB 스토리지 엔진이 데이터를 필요로 할 때 해당 데이터 페이지를 읽어서 각 조각에 저장한다.
이때 버퍼 풀의 페이지 크기 조각을 관리하기 위해 LRU List, Flush List, Free List 3개의 자료 구조를 관리한다.
◦
Free List
InnoDB 버퍼 풀에서 실제 사용자 데이터로 채워지지 않은 비어 있는 페이지들의 목록이며, 사용자의 쿼리가 새롭게 디스크의 데이터 페이지를 읽어와야 하는 경우 사용된다.
◦
Flush List
디스크에 반영되지 않은 변경된 데이터를 가진 데이터 페이지(dirty page)의 변경 시점 기준의 페이지 목록을 관리한다.
데이터가 변경되면 InnoDB는 변경 내용을 리두 로그에 기록하고 버퍼 풀의 데이터 페이지에도 변경 내용을 반영하지만, 둘은 독립적이기 때문에 서로에 의해 디스크로 기록됐다는 것을 항상 보장되지는 않는다.
InnoDB는 이러한 리두 로그에 체크포인트를 만들어 디스크의 리두 로그와 데이터 페이지의 상태를 동기화하며, 디스크에 반영된 지점을 마킹한다.
체크 포인트는 MySQL 서버가 시작될 때 InnoDB 스토리지 엔진이 리두 로그의 어느 부분부터 복구를 실행해야 할지 판단하는 기준점이 된다.
◦
LRU List
LRU List를 관리하는 목적은 디스크로부터 한 번 읽어온 페이지를 최대한 오랫동안 InnoDB 버퍼 풀의 메모리에 유지해서 디스크 읽기를 최소화하는 것이다.
LRU 영역과 MRU 영역이 결합된 형태로, Old 서브리스트는 LRU(Least Recently Used) 영역, New 서브리스트는 MRU(Most Recently Used) 영역이다.
InnoDB 스토리지 엔진에서 데이터를 찾는 과정은 다음과 같다.
1.
필요한 레코드가 저장된 데이터 페이지가 버퍼 풀에 있는지 검사
a.
InnoDB 어댑티브 해시 인덱스를 이용해 페이지 검색
b.
해당 테이블의 인덱스(B-Tree)를 이용해 버퍼 풀에서 페이지를 검색
c.
버퍼 풀에 이미 데이터 페이지가 있었다면 해당 페이지 포인터의 MRU 방향으로 승급
2.
디스크에서 필요한 데이터 페이지를 버퍼 풀에 적재하고, 적재된 페이지에 대한 포인터를 LRU 헤더 부분에 추가
3.
버퍼 풀의 LRU 헤더 부분에 적재된 데이터 페이지가 실제로 읽히면 MRU 헤더 부분으로 이동 (Read Ahead의 경우엔 MRU로 이동되지 않음)
4.
버퍼 풀에 상주하는 데이터 페이지는 사용자 쿼리로 인해 자주 사용된다면 MRU 영역에서 계속 살아남게 되고, 오랫동안 사용되지 않는다면 LRU 영역의 끝으로 밀려나 버퍼 풀에서 제거된다.
5.
필요한 데이터가 자주 접근됐다면 해당 페이지의 인덱스 키를 어댑티브 해시 인덱스에 추가
•
버퍼 풀과 리두 로그
InnoDB의 버퍼 풀은 서버의 메모리가 허용하는 만큼 크게 설정해서 디스크의 모든 데이터 파일이 버퍼 풀에 적재될수록 쿼리의 성능이 빨라진다.
하지만 버퍼 풀의 메모리 공간만 단순히 늘리는 것은 데이터 캐시 기능만 향상시키는 것이다.
InnoDB 버퍼 풀의 쓰기 버퍼링 기능까지 향상시키려면 리두 로그와의 관계를 이해해야 한다.
InnoDB 스토리지 엔진에서 리두 로그는 1개 이상의 고정 크기 파일을 연결해서 순환 고리처럼 사용한다.
그래서 전체 리두 로그 파일에서 재사용 가능한 공간과 당장 재사용 불가능한 공간을 구분해서 관리해야 하는데, 재사용 불가능한 공간을 활성 리두 로그라고 한다.
리두 로그 파일은 계속 순환되어 재사용되며, 매번 기록될 때마다 LSN(Log Sequence Number) 값을 갖는다.
InnoDB 버퍼 풀의 더티 페이지는 특정 리두 로그 엔트리와 관계를 가지고, 체크포인트가 발생하면 체크포인트 LSN보다 작은 리두 로그 엔트리와 해당 로그 엔트리와 관련된 더티 페이지는 모두 디스크로 동기화된다.
버퍼 풀의 크기가 100GB라고 해서 리두 로그의 공간이 100GB가 돼야 한다는 것은 아니다.
일반적으로 리두 로그는 변경분만 가지고 버퍼 풀은 데이터 페이지를 통째로 가지기 때문에 데이터 변경이 발생해도 리두 로그는 훨씬 작은 공간만 있으면 된다.
•
버퍼 풀 플러시
InnoDB 스토리지 엔진은 버퍼 풀에서 아직 디스크로 기록되지 않은 더티 페이지들을 성능상의 악영향 없이 디스크에 동기화하기 위해 다음과 같은 2가지 플러시 기능을 백그라운드로 실행한다.
◦
Flush List 플러시
InnoDB 스토리지 엔진은 리두 로그 공간의 재활용을 위해 주기적으로 Flush List 플러시 함수를 호출해서 오래전에 변경된 데이터 페이지를 순서대로 디스크에 동기화하는 작업을 수행하여 오래된 리두 로그 엔트리가 사용하는 공간을 비운다.
이때 언제부터 얼마나 많은 더티 페이지를 한 번에 디스크로 기록하느냐에 따라 사용자의 쿼리 처리가 악영향을 받지 않으면서 부드럽게 처리된다.
이러한 작업을 처리하는 스레드를 클리너 스레드라고 하는데, innodb_page_cleaners 시스템 변수는 클리너 스레드의 개수를 조정할 수 있게 해준다.
일반적으로 클리너 스레드의 갯수는 버퍼 풀 인스턴스의 갯수와 동일한 값으로 설정한다.
기본적으로 InnoDB 스토리지 엔진은 전체 버퍼 풀이 가진 페이지의 90%까지 더티 페이지를 가질 수 있는데, innodb_max_dirty_pages_pct 시스템 변수를 통해 가질 수 있는 더티 페이지의 비율을 조정할 수 있다.
또한 innodb_max_dirty_pages_pct_lwm 시스템 변수를 통해 급작스럽게 플러시가 폭증하지 않도록 일정 수준 이상의 더티 페이지가 발생했을 때 조금씩 더티 페이지를 디스크로 기록하게 할 수 있다.
이외에도 innodb_io_capacity와 innodb_io_capacity_max 시스템 변수를 통해 각 데이터베이스 서버가 어느 정도의 디스크 읽고 쓰기가 가능한지를 설정할 수 있다.
InnoDB 스토리지 엔진은 어댑티브 플러시 기능이 기본적으로 활성화되어 있으며, 리두 로그의 증가 속도를 분석해서 적절한 수준의 더티 페이지가 버퍼 풀에 유지될 수 있도록 디스크 쓰기를 실행한다.
이로 인해 일일히 서버의 트래픽을 봐가면서 시스템 변수들을 조정할 필요가 없으며, innodb_adaptive_flushing_lwm 시스템 변수만을 통해 전체 리두 공간에서 활성 리두 공간이 일정 비율 이상이면 어댑티브 플러시가 작동하게 할 수 있다.
마지막으로 innodb_flush_neighbors 시스템 변수는 더티 페이지를 디스크에 기록할 때 디스크에서 근접한 페이지 중에 더티 페이지가 있다면 함께 묶어서 디스크로 기록하게 해주는 기능을 활성화할지 여부를 결정한다.
하지만 요즘은 대부분 SDD(Solid State Drive)를 사용하기 때문에 기본값인 비활성 모드로 유지하는 것이 좋다.
◦
LRU List 플러시
버퍼 풀에 새로운 페이지들을 읽어올 공간을 만들기 위해 LRU List에서 사용 빈도가 낮은 데이터 페이지들을 제거하는 작업을 처리한다.
LRU List의 끝부분부터 innodb_lru_scan_depth 시스템 변수에 설정된 개수만큼 페이지를 스캔한다.
이때 더티 페이지는 디스크에 동기화하게 하고, 클린 페이지는 즉시 Free List로 옮긴다.
실질적으로 LRU List 스캔은 innodb_buffer_pool_instances * innodb_lru_scan_depth 수만큼 수행된다.
•
버퍼 풀 상태 백업 및 복구
디스크의 데이터가 버퍼 풀에 적재돼 있는 상태를 워밍업이라고 표현하며, 버퍼 풀이 잘 워밍업된 상태에서는 그렇지 않은 경우보다 몇십 배의 쿼리 처리 속도를 보이는 것이 일반적이다.
MySQL 5.6버전부터는 버퍼 풀 덤프 및 적재 기능이 도입됐다.
-- // MySQL 서버 셧다운 전 버퍼 풀의 상태 백업 mysql > SET GLOBAL innodb_buffer_pool_dump_now=ON; -- // MySQL 서버 재시작 후 백업된 버퍼 풀의 상태 복구 mysql > SET GLOBAL innodb_buffer_pool_load_now=ON; -- // 버퍼 풀 복구 중지 mysql > SET GLOBAL innodb_buffer_pool_load_abort=ON;
Plain Text
복사
버퍼 풀의 백업은 데이터 디렉터리에 ib_buffer_pool 이라는 이름의 파일로 생성되는데, 버퍼 풀의 LRU List에서 적재된 데이터 페이지의 메타 정보만 가져와서 저장하기 때문에 백업이 매우 빨리 완료된다.
-- // MySQL 서버 셧다운 시 자동으로 버퍼 풀 상태 백업 mysql > SET GLOBAL innodb_buffer_pool_dump_at_shutdown=ON; -- // MySQL 서버 재시작 후 자동으로 백업된 버퍼 풀의 상태 복구 mysql > SET GLOBAL innodb_buffer_pool_load_at_startup=ON;
Plain Text
복사
서버를 재시작할 때마다 수동으로 설정한다면 쉽게 잊어버릴 수 있기 때문에, 자동화하면 편리하다.
•
버퍼 풀의 적재 내용 확인
MySQL 5.6버전부터 MySQL 서버의 information_schema 데이터베이스의 innodb_buffer_page 테이블을 이용해 InnoDB 버퍼 풀의 메모리에 어떤 테이블의 페이지들이 적재돼 있는지 확인할 수 있다.
하지만 해당 테이블 조회는 상당히 큰 부하를 일으켜서 실제 서비스용으로 사용되는 MySQL 서버에서는 버퍼 풀의 상태를 확인하는 것이 거의 불가능했다.
MySQL 8.0버전에서는 information_schema 데이터베이스에 innodb_cached_indexes 테이블을 추가하여, 테이블의 인덱스별로 데이터 페이지가 얼마나 InnoDB 버퍼 풀에 적재돼 있는지 확인할 수 있게 했다.
이를 응용하면 특정 테이블의 페이지가 대략 어느정도 비율로 버퍼 풀에 적재돼 있는지 확인할 수 있다.
mysql > SELECT (SELECT SUM(ici.n_cached_pages) n_cached_pages FROM information_schema.innodb_tables it INNER JOIN information_schema.innodb_indexes ii ON ii.table_id = it.table_id INNER JOIN information_schema.innodb_cached_indexes ici ON ici.index_id = ii.index_id WHERE it.name=CONCAT(t.table_schema, '/', t.table_name) ) as total_cached_pages, ((t.data_length + t.index_length - t.data_free) / @@innodb_page_size) as total_pages FROM information_schema.tables t WHERE t.table_schema='employees' AND t.table_name='employees';
Plain Text
복사

Double Write Buffer

InnoDB 스토리지 엔진의 리두 로그는 리두 로그 공간의 낭비를 막기 위해 페이지의 변경된 내용만 기록하고, 이로 인해 플러시 시 페이지가 일부만 기록되는 Partial-page 또는 Torn-page 현상이 발생한다.
InnoDB 스토리지 엔진은 변경 내용을 디스크에 기록하기 전에 더티 페이지를 묶어 한 번의 디스크 쓰기로 시스템 테이블스페이스의 DoubleWrite 버퍼에 기록한다.
이후 변경 내용을 디스크에 기록하는 도중 운영체제가 비정상적으로 종료됐다면, InnoDB 스토리지 엔진이 재시작될 때 DoubleWrite 버퍼의 내용과 데이터 파일의 페이지들을 모두 비교해서 다른 내용을 담고 있는 페이지가 있으면 DoubleWrite 버퍼의 내용을 데이터 파일의 페이지로 복사한다.
DoubleWrite 기능은 innodb_doublewrite 시스템 변수로 제어할 수 있으며, 데이터의 무결성이 매우 중요한 서비스라면 활성화를 고려하는 것이 좋다.

언두 로그

InnoDB 스토리지 엔진은 트랜잭션과 격리 수준을 보장하기 위해 DML(INSERT, UPDATE, DELETE)로 변경되기 이전 버전의 데이터를 언두 로그로 백업한다.
•
언두 로그 레코드 모니터링
대용량의 데이터를 처리하는 트랜잭션뿐만 아니라 트랜잭션이 오랜 시간 동안 실행될 때도 언두 로그의 양은 급격히 증가할 수 있다.
트랜잭션이 완료됐다고 해서 해당 트랜잭션이 생성한 언두 로그를 즉시 삭제할 수 있는 것은 아니다.
일반적인 응용 프로그램에서 트랜잭션 관리가 잘못된 경우 이런 현상이 발생할 수 있지만 사용자의 실수로 인해 더 자주 문제가 되곤 한다.
누적된 언두 로그로 인해 디스크의 사용량이 증가하는 것은 그다지 큰 문제가 아닐 수도 있지만, 변경된 레코드를 조회하는 쿼리가 실행되면 언두 로그의 이력을 필요한 만큼 스캔해야만 필요한 레코드를 찾을 수 있기 때문에 쿼리의 성능이 전반적으로 떨어질 수 있다.
따라서 활성 상태의 트랜잭션이 장시간 유지되는 것은 성능상 좋지 않으며, 언두 로그 레코드가 얼마나 되는지 항상 모니터링하는 것이 좋다.
mysql> SELECT count FROM information_schema.innodb_metrics WHERE SUBSYSTEM='transaction' AND NAME='trx_rseg_history_len'; +-------+ | count | +-------+ | 0 | +-------+
Plain Text
복사
•
언두 테이블스페이스 관리
언두 로그가 저장되는 공간을 언두 테이블스페이스라고 한다.
MySQL 5.6버전 이전에는 언두 로그가 모두 시스템 테이블스페이스(ibdata.ibd)에 저장됐다.
하지만 시스템 테이블스페이스의 언두 로그는 MySQL 서버가 초기화될 때 생성되기 때문에 확장의 한계가 있었다.
그래서 MySQL 8.0버전부터는 언두 로그는 항상 시스템 테이블스페이스가 아닌 별도 로그 파일에 기록된다.
하나의 언두 테이블스페이스는 1개 이상 128개 이하의 롤백 세그먼트를 가지며, 롤백 세그먼트는 1개 이상의 언두 슬롯을 가진다.
하나의 롤백 세그먼트는 InnoDB의 페이지 크기를 16바이트로 나눈 값의 개수만큼의 언두 슬롯을 가진다.
하나의 트랜잭션이 필요로 하는 언두 슬롯의 개수는 트랜잭션이 실행하는 INSERT, UPDATE, DELETE 문장의 특성에 따라 최대 4개까지 언두 슬롯을 사용하게 된다.
이로 인해 최대 동시 처리 가능한 트랜잭션의 개수를 예측할 수 있다.
최대 동시 트랜잭션 수 = (InnoDB 페이지 크기) / 16 * (롤백 세그먼트 개수) * (언두 테이블스페이스 개수)
Plain Text
복사
언두 테이블스페이스 공간을 필요한 만큼만 남기고 불필요하거나 과도하게 할당된 공간을 운영체제로 반납하는 것을 Undo tablespaces truncate라고 하며, 자동 모드와 수동 모드 두 가지 방법이 있다.
◦
자동 모드
InnoDB 스토리지 엔진의 퍼지 스레드가 주기적으로 깨어나서 언두 로그 공간에서 불필요해진 언두 로그를 삭제하는 작업(Undo Purge)을 실행한다.
innodb_undo_log_truncate 시스템 변수로 활성화하며, innodb_purge_rseg_truncate_frequency 시스템 변수로 실행 빈도를 조정할 수 있다.
◦
수동 모드
ALTER UNDO TABLESPACE … TRUNCATE 명령어를 통해 언두 테이블스페이스 공간을 반납한다.
-- // 언두 테이블스페이스 비활성화 mysql > ALTER UNDO TABLESPACE tablespace_name SET INACTIVE; -- // 퍼지 스레드에 의해 언두 ㅔㅌ이블스페이스 공간이 반납되면 다시 활성화 mysql > ALTER UNDO TABLESPACE tablespace_name SET ACTIVE;
Plain Text
복사

체인지 버퍼

레코드가 INSERT되거나 UPDATE될 때는 데이터 파일을 변경하는 작업뿐 아니라 해당 테이블에 포함된 인덱스를 업데이트하는 작업도 필요하다.
그런데 인덱스를 업데이트하는 작업은 랜덤하게 디스크를 읽는 작업이 필요하므로 테이블에 인덱스가 많다면 이 작업은 상당히 많은 자원을 소모하게 된다.
그래서 InnoDB는 변경해야 할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행하지만 그렇지 않고 디스크로부터 읽어와서 업데이트해야 한다면 이를 즉시 실행하지 않고 임시 공간에 저장해 두고 바로 사용자에게 결과를 반환하는 형태로 성능을 향상시키게 되는데, 이때 사용하는 임시 메모리 공간을 체인지 버퍼라고 한다.
사용자에게 결과를 전달하기 전에 반드시 중복 여부를 체크해야 하는 유니크 인덱스는 체인지 버퍼를 사용할 수 없다.
체인지 버퍼에 임시로 저장된 인덱스 레코드 조각은 이후 백그라운드 스레드에 의해 병합되는데, 이 스레드를 체인지 버퍼 머지 스레드라고 한다.
MySQL 5.5버전 이전엔 INSERT 작업에 대해서만 사용할 수 있었지만, MySQL 8.0버전부터 innodb_change_buffering 시스템 변수를 통해 작업의 종류별로 체인지 버퍼를 활성화할 수 있다.
체인지 버퍼는 기본적으로 InnoDB 버퍼 풀로 설정된 메모리 공간의 25%까지 사용할 수 있으며, innodb_change_buffer_max_size 시스템 변수를 통해 조정할 수 있다.

리두 로그 및 리두 버퍼

리두 로그는 트랜잭션의 4가지 요소인 ACID 중에서 D에 해당하는 영속성과 관련되어 있다.
여러 가지 문제점으로 인해 MySQL 서버가 비정상적으로 종료됐을 때 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해주는 안전장치다.
비정상적으로 종료됐을 때 일관되지 않을 수 있는 데이터는 2가지 종류가 있다.
1.
커밋됐지만 데이터 파일에 기록되지 않은 데이터
2.
롤백됐지만 데이터 파일에 이미 기록된 데이터
1번의 경우 리두 로그에 저장된 데이터를 데이터 파일에 다시 복사하기만 하면 된다.
2번의 경우 언두 로그의 내용을 가져와 데이터 파일에 복사하면 되지만 커밋됐는지, 롤백됐는지, 아니면 트랜잭션의 실행 중간 상태였는지를 확인하기 위해 리두 로그가 필요하다.
데이터베이스 서버에서 리두 로그는 트랜잭션이 커밋되면 즉시 디스크로 기록되도록 시스템 변수를 설정하는 것을 권장한다.
하지만 이처럼 트랜잭션이 커밋될 때마다 리두 로그를 디스크에 기록하는 작업은 많은 부하를 유발하기 때문에, 어느 주기로 디스크에 동기화할지를 결정하는 innodb_flush_log_at_trx_commit 시스템 변수를 사용하여 조정한다.
•
0 : 1초에 한 번씩 리두 로그를 디스크로 기록하고 동기화를 실행한다.
•
1 : 매번 트랜잭션이 커밋될 때마다 디스크로 기록되고 동기화까지 수행된다.
•
2 : 매번 트랜잭션이 커밋될 때마다 디스크로 기록은 되지만 실질적인 동기화는 1초에 한 번씩 실행된다.
InnoDB 스토리지 엔진의 리두 로그 파일들의 전체 크기는 InnoDB 스토리지 엔진이 가지고 있는 버퍼 풀의 효율성을 결정하기 때문에 신중히 결정해야 한다.
리두 로그 파일의 크기는 innodb_log_file_size 시스템 변수로 결정하고, innodb_log_files_in_group 시스템 변수는 리두 로그 파일의 개수를 결정해서, 결국 두 시스템 변수의 곱으로 전체 리두 로그 파일의 크기를 결정할 수 있다.
로그 버퍼의 크기는 기본값인 16MB 수준에서 설정하는 것이 적합하지만, BLOB이나 TEXT와 같이 큰 데이터를 자주 변경하는 경우에는 더 크게 설정하는 것이 좋다.
MySQL 8.0버전은 InnoDB 스토리지 엔진의 리두 로그를 아카이빙할 수 있는 기능이 추가됐다.
linux > mkdir /var/log/mysql_redo_archive linux > cd /var/log/mysql_redo_archive linux > mkdir 20200722 linux > chmod 700 20200722 mysql > SET GLOBAL innodb_redo_log_archive_dirs='backup:/var/log/mysql_redo_archive'; mysql > DO innodb_repo_log_archive_start('backup', '20220722'); mysql > DO innodb_repo_log_archive_stop();
Plain Text
복사
innodb_redo_log_archive_dirs 시스템 변수를 통해 저장될 디렉터리를 지정하고, 이 디렉터리는 운영체제의 MySQL 서버를 실행하는 유저만 접근이 가능해야 한다.
디렉터리가 준비되면 리두 로그 아카이빙을 시작하도록 UDF(User Defined Function)을 실행한다.
UDF를 실행한 세션이 계속 연결이 끊어지지 않고 유지돼야 리두 로그 아카이빙이 계속 실행되고, 만약 비정상적으로 종료되면 리두 로그 아카이빙을 멈추고 아카이빙 파일도 자동으로 삭제된다.
InnoDB 스토리지 엔진의 리두 로그는 MySQL 서버가 비정상적으로 종료됐을 때 데이터 파일에 기록되지 못한 트랜잭션을 복구하기 위해 항상 활성화돼있다.
mysql > ALTER INSTANCE DISABLE INNODB REDO_LOG; mysql > ALTER INSTANCE ENABLE INNODB REDO_LOG;
Plain Text
복사
MySQL 8.0버전 이전까지는 수동으로 리두 로그를 비활성화할 수 있는 방법이 없었지만, MySQL 8.0버전부터는 수동으로 비활성화할 수 있게 되어 데이터를 복구하거나 대용량 데이터를 한 번에 적재하는 경우 비활성화를 통해 적재 시간을 단축시킬 수 있다.
만약 다시 활성화 시키는걸 잊어버린다면 MySQL 서버가 정상적으로 시작되지 못할 수 있기 때문에, 이 경우엔 innodb_force_recovery 시스템 변수를 6으로 설정한 후 다시 시작해야 한다.

어댑티브 해시 인덱스

어댑티브 해시 인덱스는 일반적인 인덱스의 B-Tree 검색 시간을 줄여주기 위해 도입된 기능이며, innodb_adaptive_hash_index 시스템 변수를 통해 활성화할 수 있다.
자주 읽히는 데이터 페이지의 키 값을 이용해 해시 인덱스를 만들고, 필요할 때마다 어댑티브 해시 인덱스를 검색해서 레코드가 저장된 데이터 페이지를 즉시 찾아갈 수 있게 한다.
해시 인덱스는 ‘인덱스 키 값’과 해당 인덱스 키 값이 저장된 ‘데이터 페이지 주소’의 쌍으로 관리된다.
이때 인덱스 키 값은 B-Tree 인덱스의 고유번호와 B-Tree 인덱스의 실제 키 값의 조합으로 생성되며, 그 이유는 모든 B-Tree 인덱스에 대한 어댑티브 해시 인덱스가 하나의 해시 인덱스에 저장되기 때문이다.
어댑티브 해시 인덱스를 사용하면 B-Tree에서 루트 노드부터 리프 노드까지 찾아가는 비용이 없어지고, 이로 인해 발생하는 내부 잠금(세마포어)의 횟수도 줄어든다.
또한 그만큼 CPU는 적은 일을 하지만 쿼리의 성능은 빨라지며, 컴퓨터는 더 많은 쿼리를 동시에 처리할 수 있게 된다.
하지만 어댑티브 해시 인덱스는 데이터 페이지를 버퍼 풀 내에서 접근하는 것을 빠르게 만드는 기능이기 때문에 데이터 페이지를 디스크에서 읽어오는 경우가 빈번한 경우에는 아무런 도움이 되지 않는다.
또한 테이블을 삭제하거나 변경하는 경우 해당 데이터 페이지의 내용을 어댑티브 해시 인덱스에서 제거해야 하기 때문에, 상당히 많은 CPU 자원을 사용하고 처리 성능이 느려진다.
mysql > SHOW ENGINE INNODB STATUS\G ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 4425293, node heap has 11 buffer(s) Hash table size 4425293, node heap has 53 buffer(s) Hash table size 4425293, node heap has 7 buffer(s) Hash table size 4425293, node heap has 2 buffer(s) Hash table size 4425293, node heap has 21 buffer(s) Hash table size 4425293, node heap has 34 buffer(s) Hash table size 4425293, node heap has 2 buffer(s) Hash table size 4425293, node heap has 56 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s
Plain Text
복사
우리 서비스 패턴에 어댑티브 해시 인덱스가 도움이 되는지 확인할 수 있다.
searches는 쿼리가 처리되기 위해 내부적으로 키 값의 검색이 몇 번 실행됐느냐를 의미한다.
어댑티브 해시 인덱스의 효율은 검색 횟수가 아니라 두 값의 비율과 어댑티브 해시 인덱스가 사용 중인 메모리 공간, 그리고 서버의 CPU 사용량을 종합해서 판단해야 한다.

4-3. MyISAM 스토리지 엔진 아키텍처

키 캐시

InnoDB의 버퍼 풀과 비슷한 역할을 하는 것이 MyISAM의 키 캐시(키 버퍼)이다.
하지만 MyISAM 키 캐시는 인덱스만을 대상으로 작동하며, 인덱스의 디스크 쓰기 작업에 대해서만 부분적으로 버퍼링 역할을 한다.
키 캐시 히트율 = 100 - (Key_reads / Key_read_requests * 100)
Plain Text
복사
SHOW GLOBAL STATUS LIKE ‘Key%’; 명령을 통해 키 캐시와 관련된 상태 변수를 확인할 수 있다.
이를 통해 키 캐시가 얼마나 효율적으로 작동하는지 확인할 수 있다.
Key_reads는 인덱스를 디스크에서 읽어 들인 횟수를 저장하는 상태 변수이며, Key_read_requests는 키 캐시로부터 인덱스를 읽은 횟수를 저장하는 상태 변수다.
매뉴얼에서는 일반적으로 키 캐시 히트율을 99% 이상으로 유지하라고 권장한다.
99% 미만이라면 키 캐시를 조금 더 크게 설정하는 것이 좋은데, 32비트 운영체제에서는 하나에 키 캐시에 4GB 이상의 메모리 공간을 설정할 수 없고, 64비트 운영체제에서는 OS_PER_PROCESS_LIMIT 값에 설정된 크기만큼의 메모리를 할당할 수 있다.
key_buffer_size = 4GB kbuf_board.key_buffer_size = 2GB kbuf_comment.key_buffer_size = 2GB mysql > CACHE INDEX db1.board, db2.board IN kbuf_board; mysql > CACHE INDEX db1.comment, db2.comment IN kbuf_comment;
Plain Text
복사
기본 키 캐시 공간은 key_buffer_size 시스템 변수를 통해 설정할 수 있으며, 제한 값 이상의 키 캐시를 할당하고 싶다면 별도의 명명된 키 캐시 공간을 설정해야 한다.
이렇게 만들어진 별도의 명명된 키 캐시 공간은 어떤 인덱스를 캐시할지 MyISAM 스토리지 엔진에 알려줘야 한다.

운영체제의 캐시 및 버퍼

MyISAM 스토리지 엔진은 디스크로부터의 I/O를 해결해 줄 만한 어떠한 캐시나 버퍼링 기능도 갖고 있지 않다.
물론 대부분의 운영체제는 디스크로부터 읽고 쓰는 파일에 대한 캐시나 버퍼링 메커니즘을 탑재하고 있기 때문에 매번 디스크의 파일을 읽지는 않는다.
하지만 운영체제의 캐시 공간은 남는 메모리를 사용하는 것이 기본 원칙이기 때문에, 다른 어플리케이션에서 메모리를 모두 사용해 버린다면 운영체제가 캐시 용도로 사용할 수 있는 메모리 공간이 없어진다.
MyISAM이 주로 사용되는 MySQL에서 일반적으로 키 캐시는 최대 물리 메모리의 40% 이상을 넘기지 않게 설정하고, 나머지 메모리 공간은 운영체제가 자체적인 파일 시스템을 위한 캐시 공간을 마련할 수 있게 해주는 것이 좋다.

데이터 파일과 프라이머리 키(인덱스) 구조

InnoDB 스토리지 엔진을 사용하는 테이블은 프라이머리 키에 의해서 클러스터링되어 저장되는 반면, MyISAM 테이블은 프라이머리 키에 의한 클러스터링 없이 데이터 파일이 힙 공간처럼 활용된다.
즉, MyISAM 테이블에 레코드는 프라이머리 키 값과 무관하게 INSERT되는 순서대로 데이터 파일에 저장된다.
그리고 MyISAM 테이블에 저장되는 레코드는 모두 ROWID라는 물리적인 주솟값을 가지는데, 프라이머리 키와 세컨더리 인덱스는 모두 데이터 파일에 저장된 레코드의 ROWID 값을 포인터로 가진다.
MyISAM 테이블에서 ROWID는 가변 길이와 고정 길이의 두 가지 방법으로 저장될 수 있다.
•
고정 길이 ROWID
MyISAM 테이블을 생성할 때 MAX_ROWS 옵션을 명시하면 MySQL 서버는 최대로 가질 수 있는 레코드가 한정된 테이블을 생성한다.
이렇게 생성된 테이블은 ROWID 값으로 4바이트 정수를 사용하고, 레코드가 INSERT된 순번이 ROWID로 사용된다.
•
가변 길이 ROWID
MyISAM 테이블을 생성할 때 MAX_ROWS 옵션을 설정하지 않으면 MyISAM 테이블의 ROWID는 최대 myisam_data_pointer_size 시스템 변수에 설정된 바이트 수만큼 공간을 사용할 수 있다.
기본값은 7이므로 2바이트부터 7바이트까지 가변적인 ROWID를 갖게 되는데, 그 중 첫번째 바이트는 ROWID의 길이를 저장하는 용도로 사용되고, 나머지 공간은 실제 ROWID를 저장하는데 사용된다.
이렇게 생성된 테이블은 레코드의 위치(offset)가 ROWID로 사용된다.

4-4. MySQL 로그 파일

에러 로그 파일

MySQL이 실행되는 도중에 발생하는 에러나 경고 메시지가 출력되는 로그 파일이다.
에러 로그 파일의 위치는 my.cnf에서 log_error라는 이름의 파라미터로 정의된 경로에 생성된다.
별도로 정의되지 않은 경우에는 datadir 파라미터로 설정된 디렉터리에 .err 라는 파일로 생성된다.
•
MySQL이 시작하는 과정과 관련된 정보성 및 에러 메시지
MySQL의 설정 파일을 변경하거나 데이터베이스가 비정상적으로 종료된 이후 다시 시작하는 경우에는 반드시 MySQL 에러 로그 파일을 통해 설정된 변수의 이름이나 값이 명확하게 설정되고 의도한 대로 적용됐는지 확인해야 한다.
MySQL 서버가 정상적으로 기동(’mysqld: ready for connections’)했고, 새로 변경하거나 추가한 파라미터에 대한 특별한 에러나 경고성 메시지가 없다면 정상적으로 적용된 것으로 판단한다.
그렇지 않고 특정 변수가 무시(ignore)되거나 인식하지 못해서 에러 메시지가 출력된다면 MySQL에 정상적으로 적용되지 못했음을 의미한다.
•
마지막으로 종료할 때 비정상적으로 종료된 경우 나타나는 InnoDB 트랜잭션 복구 메시지
InnoDB의 경우 MySQL 서버가 비정상적 또는 강제적으로 종료됐다면 다시 시작되면서 완료되지 못한 트랜잭션을 정리하고 디스크에 기록되지 못한 데이터가 있다면 다시 기록하는 재처리 작업을 하게 되며, 이 과정에서 간단한 메시지가 출력된다.
•
쿼리 처리 도중에 발생하는 문제에 대한 에러 메시지
쿼리의 실행 도중 발생한 에러나 복제에서 문제가 될 만한 쿼리에 대한 경고 메시지가 에러 로그에 기록된다.
쿼리 도중 발생하는 문제점은 사전 예방이 어려우며, 주기적으로 에러 로그 파일을 검토해야 한다.
•
비정상적으로 종료된 커넥션 메시지
클라이언트 어플리케이션에서 정상적으로 접속 종료를 하지 못하고 프로그램이 종료된 경우 MySQL 서버의 에러 로그 파일에 ‘Aborted connection’ 내용이 기록된다.
또한 max_connect_errors 시스템 변수값이 너무 낮게 설정된 경우 클라이언트 프로그램이 MySQL 서버에 접속하지 못하고 ‘Host OOO is blocked’ 라는 에러가 발생할 수도 있다.
이런 메시지가 많이 기록된다면 어플리케이션의 커넥션 종료 로직과 max_connect_errors 변수값을 검토해야 한다.
•
InnoDB의 모니터링 또는 상태 조회 명령의 결과 메시지
InnoDB의 테이블 모니터링이나 락 모니터링, 또는 InnoDB의 엔진 상태를 조회하는 명령은 상대적으로 큰 메시지를 에러 로그 파일에 기록한다.
따라서 모니터링을 활성화 상태로 오래 유지하는 경우에는 에러 로그 파일이 매우 커져서 파일 시스템 공간을 다 사용해 버릴지도 모르기 때문에 사용한 이후엔 다시 비활성화해야 한다.
•
MySQL의 종료 메시지
가끔 MySQL이 아무도 모르게 종료되거나, 재시작되는 경우가 있다.
만약 누군가가 MySQL 서버를 종료시켰다면 에러 로그 파일에서 ‘Received SHUTDOWN from user …’ 라는 메시지를 확인할 수 있다.
그렇지 않고 아무런 종료 관련 메시지가 없거나 스택트레이스와 같은 내용이 출력되는 경우에는 Segmentation fault로 비정상 종료된 것으로 판단할 수 있다.

제너럴 로그 파일

쿼리 로그를 활성화하면 시간 단위로 실행했던 쿼리의 내용을 확인할 수 있다.
슬로우 쿼리 로그와는 조금 다르게 제너럴 쿼리 로그는 쿼리 요청을 받으면 바로 기록하기 때문에 에러가 발생해도 일단 로그 파일에 기록된다.
쿼리 로그 파일의 경로는 general_log_file 파라미터에 설정돼 있으며, log_output 파라미터를 통해 파일이 아닌 테이블에 저장하도록 할 수도 있다.

슬로우 쿼리 로그

MySQL 서버의 쿼리 튜닝은 크게 서비스가 적용되기 전에 전체적으로 튜닝하는 경우와 서비스 운영 중에 전체적인 성능 저하를 검사하거나 정기적인 점검을 위한 튜닝으로 나눌 수 있다.
이런 경우에 서비스에서 사용되는 쿼리 중에 어떤 쿼리가 문제인지 판단하는데 슬로우 쿼리 로그가 도움이 된다.
슬로우 쿼리 로그 파일에는 long_query_time 시스템 변수에 설정한 시간 이상의 시간이 소요된 정상적인 쿼리가 모두 기록된다.
log_output 파라미터를 통해 테이블로 기록할 수 있으며, 이 테이블은 CSV 스토리지 엔진을 사용하기 때문에 CSV 파일로 저장하는 것과 동일하게 동작한다.
# Time: 2020-07-19T15:44:22. 178484+09:00 # User@Host: root[root] @ localhost [] Id : 14 # Query_time: 1.180245 Lock_time: 0.002658 Rows_sent: 1 Rows_examined: 2844047 use employees; SET timestamp=1595141060; select emp_no, max(salary) from salaries;
Plain Text
복사
•
Time
쿼리가 시작된 시간이 아닌 종료된 시점을 의미한다.
쿼리가 언제 시작됐는지 확인하려면 Time에서 Query_time을 빼야 한다.
•
User@Host
쿼리를 실행한 사용자의 계정이다.
•
Query_time
쿼리가 실행되는데 걸린 전체 시간을 의미한다.
•
Lock_time
MySQL 엔진의 테이블 잠금과 InnoDB 스토리지 엔진의 레코드 잠금의 2가지 잠금이 존재한다.
Lock_time은 이 중 테이블 잠금에 대한 대기 시간만 표현한다.
잠금 체크의 시간까지 모두 포함되기 때문에 0이 아니라고해서 무조건 잠금 대기가 있었다고 판단하기 어렵다.
•
Rows_examined, Row_sent
Row_examined는 쿼리가 처리되기 위해 몇 건의 레코드에 접근했는지를 의미한다.
Row_sent는 실제 몇 건의 처리 결과를 클라이언트로 보냈는지를 의미한다.
Row_examined의 레코드 건수는 높지만 Row_sent의 레코드 건수가 적다면, 조금 더 적은 레코드만 접근하도록 튜닝해 볼 수 있다.
MyISAM이나 MEMORY 스토리지 엔진에서는 테이블 단위의 잠금을 사용하고 MVCC와 같은 메커니즘이 없기 때문에 SELECT 쿼리라고 하더라도 Lock_time이 1초 이상 소요될 가능성이 있다.
또한 InnoDB 스토리지 엔진도 MySQL 엔진의 잠금으로 인해 Lock_time이 상대적으로 큰 값이 발생할 수 있다.
따라서 Lock_time 값은 튜닝이나 쿼리 분석에 별로 도움이 되지 않는다.

쿼리 통계

linux > pt-query-digest --type='genlog' general.log > parsed_general.log linux > pt-query-digest --type='slowlog' mysql-slow.log > parsed_mysql-slog.log
Plain Text
복사
직접 쿼리를 하나씩 검토하는건 어렵기 때문에, pt-query-digest 스크립트를 사용하면 쿼리 로그 파일을 분석해서 볼 수 있다.
분석 결과의 최상단에 모든 쿼리를 대상으로 Exec_time, Lock_time, Rows_sent, Rows_examine, Query_size 에 대한 평균 및 최대/최소 값을 표시한다.
또한 각 쿼리별로 응답시간과 실행횟수를 보여주며 스크립트 실행 시 --order-by 옵션을 통해 정렬 순서를 변경할 수 있다.

5. 트랜잭션과 잠금

트랜잭션

트랜잭션은 작업의 완전성을 보장해 주는 것이다.
즉, 논리적인 작업 셋을 모두 완벽하게 처리하거나, 처리하지 못할 경우에는 원 상태로 복구해서 작업의 일부만 적용되는 현상(Partial update)이 발생하지 않게 만들어주는 기능이다.
잠금과 트랜잭션은 서로 비슷한 개념 같지만 사실 잠금은 동시성을 제어하기 위한 기능이고 트랜잭션은 데이터의 정합성을 보장하기 위한 기능이다.
잠금은 여러 커넥션에서 동시에 동일한 자원(레코드나 테이블)을 요청할 경우 순서대로 한 시점에는 하나의 커넥션만 변경할 수 있게 해주는 역할을 한다.
격리 수준이라는 것은 하나의 트랜잭션 내에서 또는 여러 트랜잭션 간의 작업 내용을 어떻게 공유하고 차단할 것인지를 결정하는 레벨을 의미한다.
•
MySQL에서의 트랜잭션
트랜잭션은 하나의 논리적인 작업 셋에 하나의 쿼리가 있든 두 개 이상의 쿼리가 있든 관계없이 논리적인 작업 셋 자체가 100% 적용되거나(COMMIT을 실행했을 때) 아무것도 적용되지 않아야(ROLLBACK 또는 트랜잭션을 ROLLBACK 시키는 오류가 발생했을 때) 함을 보장해 주는 것이다.
mysql > CREATE TABLE tab_myisam (fdpk INT NOT NULL, PRIMARY KEY (fdpk)) ENGINE=MyISAM; mysql > INSERT INTO tab_myisam (fdpk) VALUES (3); mysql > CREATE TABLE tab_innodb (fdpk INT NOT NULL, PRIMARY KEY (fdpk)) ENGINE=INNODB; mysql > INSERT INTO tab_innodb (fdpk) VALUES (3); mysql > SET autocommit=ON; mysql > INSERT INTO tab_myisam (fdpk) VALUES (1), (2), (3); ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY' mysql > INSERT INTO tab_innodb (fdpk) VALUES (1), (2), (3); ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY' mysql> SELECT * FROM tab_myisam; +------+ | fdpk | +------+ | 1| | 2| | 3| +------+ mysql> SELECT * FROM tab_innodb; +------+ | fdpk | +------+ | 3| +------+
Plain Text
복사
테스트용 테이블에 미리 각각 레코드를 1건씩 저장한 후 AUTO-COMMIT 모드에서 레코드 한 건을 추가로 저장하는 쿼리를 각 테이블에 실행해보았다.
두 INSERT 문장 모두 프라이머리 키 중복 오류로 쿼리가 실패했지만, MyISAM 테이블에는 오류가 발생했음에도 ‘1’과 ‘2’는 INSERT된 상태로 남아 있다.
MyISAM 테이블에서 발생하는 이러한 현상을 부분 업데이트(Partial update)라고 표현하며, 이러한 부분 업데이트 현상은 테이블 데이터의 정합성을 맞추는데 상당히 어려운 문제를 만들어 낸다.
•
주의사항
사용자가 게시판에 게시물을 작성한 후 저장 버튼을 클릭했을 때 서버에서 처리하는 내용은 다음과 같다.
1.
처리 시작
→ 데이터베이스 커넥션 생성
→ 트랜잭션 시작
2.
사용자의 로그인 여부 확인
3.
사용자의 글쓰기 내용의 오류 여부 확인
4.
첨부로 업로드된 파일 확인 및 저장
5.
사용자의 입력 내용을 DBMS에 저장
6.
첨부 파일 정보를 DBMS에 저장
7.
저장된 내용 또는 기타 정보를 DBMS에서 조회
8.
게시물 등록에 대한 알림 메일 발송
9.
알림 메일 발송 이력을 DBMS에 저장
← 트랜잭션 종료 (COMMIT)
← 데이터베이스 커넥션 반납
10.
처리 완료
실제로 DBMS에 데이터를 저장하는 작업(트랜잭션)은 5번부터 시작된다.
하지만 많은 개발자가 데이터베이스의 커넥션을 생성(또는 커넥션 풀에서 가져오는)하는 코드를 1번과 2번 사이에 구현하며, 9번과 10번 사이에서 트랜잭션을 COMMIT하고 커넥션을 종료(또는 커넥션 풀로 반납)한다.
또한 8번처럼 I/O가 발생하는 작업은 통신할 수 없는 상황이 발생한다면 웹 서버뿐 아니라 DBMS 서버까지 위험해지는 상황이 발생할 수 있으므로 트랜잭션에서 제거하는 것이 좋다.
이 처리 절차에는 DBMS의 작업이 크게 4개가 있고, 모든 작업을 하나의 트랜잭션으로 묶어야 할 필요가 없다.
사용자가 입력한 정보를 저장하는 5번, 6번은 반드시 하나의 트랜잭션으로 묶어야 하며, 7번은 단순 조회 작업이므로 트랜잭션이 필요 없다.
이런 문제들을 보완해서 처리 절차를 다시 설계하면 다음과 같다.
1.
처리 시작
2.
사용자의 로그인 여부 확인
3.
사용자의 글쓰기 내용의 오류 발생 여부 확인
4.
첨부로 업로드된 파일 확인 및 저장
→ 데이터베이스 커넥션 생성(또는 커넥션 풀에서 가져오기)
→ 트랜잭션 시작
5.
사용자의 입력 내용을 DBMS에 저장
6.
첨부 파일 정보를 DBMS에 저장
← 트랜잭션 종료(COMMIT)
7.
저장된 내용 또는 기타 정보를 DBMS에서 조회
8.
게시물 등록에 대한 알림 메일 발송
→ 트랜잭션 시작
9.
알림 메일 발송 이력을 DBMS에 저장
← 트랜잭션 종료(COMMIT)
← 데이터베이스 커넥션 종료(또는 커넥션 풀에 반납)
10.
처리 완료
이 처리 절차가 최적의 트랜잭션 설계가 아닐 수 있지만, 프로그램의 코드가 데이터베이스 커넥션을 가지고 있는 범위와 트랜잭션이 활성화돼 있는 프로그램의 범위를 최소화해야 한다.
또한 네트워크 작업이 있는 경우에는 반드시 트랜잭션에서 배제해야 한다.

MySQL 엔진의 잠금

MySQL의 잠금은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눌 수 있다.
MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 미치지만, 스토리지 엔진 레벨의 잠금은 스토리지 엔진 간 상호 영향을 미치지 않는다.
•
글로벌 락
글로벌 락은 FLUSH TABLES WITH READ LOCK 명령으로 획득할 수 있으며, MySQL에서 제공하는 잠금 가운데 가장 범위가 크다.
MySQL 서버에 존재하는 모든 테이블을 닫고 잠금을 걸으며, 잠금을 걸기 전에 먼저 테이블을 플러시해야 하기 때문에 테이블에 실행 중인 모든 종류의 쿼리가 완료될 때까지 대기한다.
MySQL 8.0버전부터는 InnoDB 스토리지 엔진의 트랜잭션을 이용해 모든 데이터 변경 작업을 멈출 필요가 없는 조금 더 가벼운 글로벌 락을 위한 LOCK INSTANCE FOR BACKUP 명령의 백업 락이 도입됐다.
백업 락은 일반적인 테이블의 데이터 변경은 허용되지만, 다음과 같은 작업은 허용되지 않는다.
◦
데이터베이스 및 테이블 등 모든 객체 생성, 변경, 삭제
◦
REPAIR TABLE과 OPTIMIZE TABLE 명령
◦
사용자 관리 및 비밀번호 변경
주로 레플리카 서버에서 백업을 위한 목적으로 사용되며, DDL 명령이 실행되면 복제를 일시 중지하여 스키마 변경으로 인한 실패를 방지한다.
•
테이블 락
테이블 락은 개별 테이블 단위로 설정되는 잠금이며, LOCK TABLES table_name [READ | WRITE] 명령으로 명시적 또는 묵시적으로 특정 테이블의 락을 획득할 수 있다.
명시적으로 획득한 잠금은 UNLOCK TABLES 명령으로 잠금을 반납(해제)해야 한다.
MyISAM이나 MEMORY 테이블은 테이블에 데이터를 변경하는 쿼리를 실행하면 묵시적인 테이블 락을 실행하지만, InnoDB 테이블은 레코드 기반의 잠금을 제공하기 때문에 단순 데이터 변경으론 실행되지 않는다.
즉, 대부분의 데이터 변경(DML) 쿼리에선 테이블 락이 무시되고, 스키마를 변경하는 쿼리(DDL)의 경우에 영향을 미친다.
•
네임드 락
네임드 락은 GET_LOCK(), IS_FREE_LOCK(), RELEASE_LOCK() 함수를 이용해 임의의 문자열에 대해 잠금을 획득하고, 잠금 획득 여부를 확인하고, 잠금을 해제할 수 있다.
이 잠금의 특징은 대상이 테이블이나 레코드 또는 AUTO_INCREMENT와 같은 데이터베이스 객체가 아니라 단순히 사용자가 지정한 문자열에 대해 잠금을 획득하고 반납한다는 것이다.
MySQL 8.0버전부터는 네임드 락을 중첩해서 사용할 수 있으며, RELEASE_ALL_LOCKS() 함수를 통해 현재 세션에서 획득한 네임드 락을 한 번에 해제하는 기능도 추가됐다.
•
메타데이터 락
메타데이터 락은 데이터베이스 객체의 이름이나 구조를 변경하는 경우에 획득하는 잠금이다.
명시적으로 획득하거나 해제할 수 있는 것이 아니고, RENAME TABLE 명령과 같이 데이터베이스 객체의 이름이나 구조를 변경하는 경우 자동으로 획득하고 해제한다.

InnoDB 스토리지 엔진의 잠금

InnoDB 스토리지 엔진은 MySQL에서 제공하는 잠금과는 별개로 스토리지 엔진 내부에서 레코드 기반의 잠금 방식을 탑재하고 있다.
하지만 이원화된 잠금 처리 탓에 InnoDB 스토리지 엔진에서 사용되는 잠금에 대한 정보는 MySQL 명령을 이용해 접근하기가 상당히 까다롭다.
최근 버전에서는 information_schema 데이터베이스에 존재하는 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS 라는 테이블을 조인해서 조회하면 현재 어떤 트랜잭션이 어떤 잠금을 대기하고 있고 해당 잠금을 어느 트랜잭션이 가지고 있는지 확인할 수 있고, 종료시킬 수도 있다.
InnoDB 스토리지 엔진의 잠금 정보는 상당히 작은 공간으로 관리되기 때문에 레코드 락이 페이지 락으로, 또는 테이블 락으로 레벨업되는 경우(락 에스컬레이션)는 없다.
일반 상용 DBMS와는 다르게 레코드 락뿐 아니라 레코드와 레코드 사이의 간격을 잠그는 갭 락이라는 것이 존재한다.
•
레코드 락
레코드 락은 레코드를 잠그는 것으로 레코드 자체가 아니라 인덱스의 레코드를 잠근다.
인덱스가 없는 테이블이더라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정한다.
보조 인덱스를 이용한 변경 작업은 넥스트 키 락 또는 갭 락을 사용하지만, 프라이머리 키 또는 유니크 인덱스에 의한 변경 작업에서는 레코드 자체에 대한 락만 사용한다.
•
갭 락
갭 락은 레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것을 의미한다.
갭 락의 역할은 레코드와 레코드 사이의 간격에 새로운 레코드가 생성되는 것을 제어하는 것이다.
•
넥스트 키 락
레코드 락과 갭 락을 합쳐 놓은 형태의 잠금을 넥스트 키 락이라고 한다.
인덱스를 기준으로 해당 키와 다음 키까지의 갭에 락을 걸며, 레플리카 서버와 소스 서버의 결과가 동일함을 보장하여 PHANTOM_READ 현상을 방지하기 위해 사용된다.
MySQL의 기본적인 격리 수준인 REPEATABLE READ에선 자동으로 갭 락이 사용되며, READ COMMITTED 으로 격리 수준을 내린다면 갭 락이 사용되지 않는다.
•
자동 증가 락
자동 증가 락은 AUTO_INCREMENT 컬럼이 사용된 테이블에 동시에 여러 레코드가 INSERT 되는 경우, 저장되는 각 레코드가 중복되지 않고 저장된 순서대로 증가하는 일련번호 값을 가지게 하기 위해 사용하는 테이블 수준의 잠금이다.
INSERT와 REPLACE 쿼리와 같이 새로운 레코드를 저장하는 쿼리에서만 걸리며, 트랜잭션과 관계없이 AUTO_INCREMENT 값을 가져오는 순간만 락이 걸렸다가 즉시 해제된다.
자동 증가 락은 3가지의 작동 방식이 존재하고 innodb_autoinc_lock_mode 시스템 변수를 통해 변경할 수 있다.
◦
innodb_autoinc_lock_mode=0
모든 INSERT 문장은 자동 증가 락을 사용한다.
◦
innodb_autoinc_lock_mode=1
MySQL 서버가 INSERT되는 레코드의 건수를 정확히 예측할 수 있을 때, 자동 증가 락을 사용하지 않고 훨씬 가볍고 빠른 래치(뮤텍스)를 이용해 처리한다.
INSERT … SELECT와 같이 레코드의 건수를 예측할 수 없을 땐 자동 증가 락을 사용한다.
◦
innodb_autoinc_lock_mode=2
절대 자동 증가 락을 사용하지 않고 경량화된 래치(뮤텍스)만을 사용한다.
하지만 이 설정은 연속된 자동 증가 값을 보장하지 않고, 유니크한 값이 생성된다는 것만 보장한다.
INSERT … SELECT 와 같은 대량 INSERT를 실행하는 경우엔 다른 커넥션에서 INSERT를 수행할 수 있으므로 동시 처리 성능이 높아진다.
•
인덱스와 잠금
-- // KEY index_firstname(first_name) mysql > SELECT COUNT(*) FROM employees WHERE first_name='Georgi'; +-------+ | 253| +-------+ mysql > SELECT COUNT(*) FROM employees WHERE first_name='Georgi' AND last_name='Klassen'; +-------+ | 1| +-------+ mysql > UPDATE employees SET hire_date=NOW() WHERE first_name='Georgi' AND last_name='Klassen';
Plain Text
복사
UPDATE 쿼리의 조건에서 인덱스를 사용할 수 있는 컬럼은 first_name뿐이다.
레코드 락은 인덱스를 기준으로 잠금을 걸기 때문에 1건의 레코드가 잠금이 되는게 아닌 253건의 레코드가 잠금이 걸린다.
테이블에 인덱스가 하나도 없다면, 테이블을 풀 스캔하면서 UPDATE 작업을 하기 때문에 테이블에 있는 모든 레코드가 잠기게 된다.
이것이 MySQL의 InnoDB에서 인덱스 설계가 중요한 이유이다.
•
레코드 수준의 잠금 확인 및 해제
MySQL 5.1버전부터는 information_schema 데이터베이스에 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS 테이블을 통해 잠금을 확인할 수 있었다.
하지만 MySQL 8.0버전부터는 performance_schema 데이터베이스의 data_locks, data_lock_waits 테이블로 대체되고 있다.
만약 특정 스레드가 잠금을 가진 상태에서 상당히 오랜 시간 멈춰 있다면 KILL 명령을 통해 해당 번호의 스레드를 강제 종료하면 잠금 경합이 끝난다.

MySQL의 격리 수준

트랜잭션의 격리 수준이란 여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지를 결정하는 것이다.
READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE의 4가지 격리 수준은 순서대로 일관성이 높아지고 동시성이 떨어지며, 각각의 격리 수준은 동시성과 일관성이 반비례한다.
격리 수준 레벨에 따라 DIRTY READ, NON-REPEATABLE READ, PHANTOM READ의 3가지 부정합의 문제점이 발생할 수 있다.
•
READ UNCOMMITTED
각 트랜잭션에서의 변경 내용이 COMMIT 이나 ROLLBACK 여부에 상관없이 다른 트랜잭션에서 보인다.
어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있는 현상을 더티 리드(Dirty Read)라고 하고, 데이터가 나타났다가 사라졌다 하는 현상을 초래한다.
또한 표준 트랜잭션의 격리 수준으로 인정되지 않을 정도로 정합성에 문제가 많은 격리 수준이므로 READ COMMITTED 이상의 격리 수준을 사용하는 것이 좋다.
•
READ COMMITTED
어떤 트랜잭션에서 데이터를 변경했더라도 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있다.
이 레벨에서는 위에서 언급한 더티 리드(Dirty Read) 같은 현상이 발생하지 않는다.
어떤 트랜잭션에서 변경한 내용이 커밋되기 전까지는 해당 테이블의 데이터가 아닌 언두 영역에 백업된 레코드를 조회하기 때문이다.
하지만 이러한 언두 영역을 통한 동시성 보장으로 인해 항상 같은 결과를 가져올 수 없는 NON-REPEATABLE READ라는 부정합의 문제가 있다.
•
REPEATABLE READ
MySQL의 InnoDB 스토리지 엔진에서 기본으로 사용되는 격리 수준이며, 바이너리 로그를 가진 MySQL 서버에서는 최소 REPEATABLE READ 격리 수준 이상을 사용해야 한다.
READ COMMITTED와 동일하게 언두 영역에 백업된 이전 데이터를 이용해 COMMIT되기 전의 데이터를 보여준다.
하지만 REPEATABLE READ와의 차이점은 언두 영역에 백업된 레코드의 여러 버전 가운데 몇 번째 이전 버전까지 찾아 들어가야 하느냐에 있다.
모든 InnoDB의 트랜잭션은 고유한(순차적으로 증가하는) 트랜잭션 번호를 가지며, 언두 영역에 백업된 모든 레코드는 변경을 발생시킨 트랜잭션의 번호가 포함돼 있다.
REPEATABLE READ 격리 수준에서는 특정 트랜잭션 번호의 구간 내에서 백업된 모든 언두 데이터가 보존되며, SELECT 쿼리는 자신의 트랜잭션 번호보다 작은 트랜잭션 번호에서 변경된 것만 조회하게 된다.
하지만 REPEATABLE READ에서도 INSERT에 의해 PHANTOM READ 부정합이 발생할 수 있다.
REPEATABLE READ 격리 수준에서는 FOR UPDATE를 통해 자동으로 InnoDB 스토리지 엔진의 갭 락을 사용할 수 있지만, 언두 영역의 레코드에는 잠금을 걸 수 없기 때문에 이러한 부정합이 발생할 수 있다.
•
SERIALIZABLE
가장 단순한 격리 수준이면서 동시에 가장 엄격한 격리 수준이다.
InnoDB 테이블에서 기본적으로 순수한 SELECT 작업(INSERT … SELECT …, CREATE TABLE … AS SELECT …가 아닌)은 아무런 레코드 잠금도 설정하지 않고 실행된다.
하지만 트랜잭션의 격리 수준이 SERIALIZABLE로 설정되면 읽기 작업도 공유 잠금(읽기 잠금)을 획득해야만 하며, 동시에 다른 트랜잭션은 해당 레코드를 변경하지 못하게 된다.

6. 데이터 압축

MySQL 서버에서 디스크에 저장된 데이터 파일의 크기는 일반적으로 쿼리의 처리 성능과도 직결되지만 백업 및 복구 시간과도 밀접하게 연결된다.
디스크의 데이터 파일이 크면 클수록 쿼리를 처리하기 위해서 더 많은 데이터 페이지를 InnoDB 버퍼 풀로 읽어야 할 수도 있고, 새로운 페이지가 버퍼 풀로 적재되기 때문에 그만큼 더티 페이지가 더 자주 디스크로 기록돼야 한다.
많은 DBMS가 이런 문제를 해결하기 위해 데이터 압축 기능을 제공한다.

페이지 압축

MySQL 서버가 디스크에 저장하는 시점에 데이터 페이지가 압축되어 저장되고, 반대로 MySQL 서버가 디스크에서 데이터 페이지를 읽어올 때 압축이 해제된다.
즉, 버퍼 풀에 데이터 페이지가 한 번 적재되면 InnoDB 스토리지 엔진은 압축이 해제된 상태로만 데이터 페이지를 관리하기 때문에, MySQL 서버의 내부 코드에서는 압축 여부와 관계없이 투명(Transparent)하게 작동한다.
-- // 테이블 생성 시 페이지 압축 적용 mysql > CREATE TABLE t1 (c1 INT) COMPRESSION="zlib"; -- // 테이블 변경 시 페이지 압축 적용 mysql > ALTER TABLE t1 COMPRESSION="zlib"; mysql > OPTIMIZE TABLE t1;
Plain Text
복사
여기서 문제는 16KB인 하나의 데이터 페이지를 압축한 결과가 용량이 얼마나 될지 예측이 불가능하다는 것이다.
그래서 페이지 압축 기능은 펀치 홀이라는 기능을 사용한다.
1.
16KB 페이지를 압축 (압축 결과를 7KB라고 가정)
2.
MySQL 서버는 디스크에 압축된 결과 7KB를 기록
3.
디스크에 데이터를 기록한 후, 16KB에서 압축 결과인 7KB를 뺀 9KB의 빈 데이터를 갖는 펀치 홀을 생성
4.
파일 시스템은 7KB만 남기고 나머지 디스크의 9KB 공간은 다시 운영체제로 반납
그런데 MySQL 서버의 페이지 압축이 가진 문제는 펀치 홀 기능은 운영체제뿐만 아니라 하드웨어 자체에서도 해당 기능을 지원해야 사용 가능하다는 점이다.
또한 파일 관련 유틸리티들에는 펀치 홀이 적용되지 않아서 실제 데이터 파일의 크기가 1GB라고 하더라도 펀치 홀이 다시 채워진 10GB를 읽게 된다.
이러한 이유로 실제 페이지 압축은 많이 사용되지 않는다.

테이블 압축

테이블 압축은 운영체제나 하드웨어에 대한 제약 없이 사용할 수 있지만 다음과 같은 단점도 존재한다.
•
버퍼 풀 공간 활용률이 낮음
•
쿼리 처리 성능이 낮음
•
빈번한 데이터 변경 시 압축률이 떨어짐
mysql > SET GLOBAL innodb_file_per_table=ON; mysql > CREATE TABLE compressed_table (c1 INT PRIMARY KEY) ROW_FORMAT=COMPRESSED -- // 생략해도 기본으로 설정됨 KEY_BLOCK_SIZE=8;
Plain Text
복사
테이블 압축을 사용하기 위해선 압축하려는 테이블이 별도의 테이블 스페이스를 사용해야 한다.
이를 위해서는 innodb_file_per_table 시스템 변수가 ON으로 설정된 상태에서 ROW_FORMAT=COMPRESSED 옵션을 명시한채로 테이블이 생성되어야 한다.
추가로 KEY_BLOCK_SIZE 옵션을 통해 압축된 페이지의 목표 크기를 2n으로 설정할 수 있고, InnoDB 스토리지 엔진의 페이지 크기(innodb_page_size)가 16KB라면 4KB 또는 8KB만 설정할 수 있다.
KEY_BLOCK_SIZE 옵션은 압축된 페이지가 저장될 페이지의 크기를 지정한다.
만약 압축 결과가 KEY_BLOCK_SIZE보다 크다면 페이지를 스플릿하여 처리하게 된다.
•
압축된 결과가 8KB 이하이면 그대로 디스크에 저장 (압축 완료)
•
압축된 결과가 8KB 초과면 원본 페이지를 스플릿해서 재압축 시도 (압축 재시도)
하지만 KEY_BLOCK_SIZE가 잘못 설정되면 MySQL 서버의 처리 성능이 급격히 떨어질 수 있다.
그렇기 때문에 테이블 압축에서 KEY_BLOCK_SIZE를 결정하는 것이 중요하며, 샘플 데이터를 저장해보고 판단하는 것이 좋다.
mysql > USE employees; -- // 테스트 대상 테이블과 동일한 테이블을 생성 mysql > CREATE TABLE employees_comp4k (...) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4; -- // 인덱스별로 압축 실행 횟수와 성공 횟수를 기록 mysql> SET GLOBAL innodb_cmp_per_index_enabled=ON; -- // 압축 테스트 mysql > INSERT INTO employees_comp4k SELECT * FROM employees; -- // 인데긋별 압축 횟수, 성공 횟수, 압축 실패율 조회 mysql > SELECT table_name, index_name, compress_ops, compress_ops_ok, (compress_ops - compress_ops_ok) / compress_ops * 100 as compression_failure_pct FROM information_schema.INNODB_CMP_PER_INDEX;
Plain Text
복사
일반적으로 압축 실패율은 3~5% 미만으로 유지할 수 있게 KEY_BLOCK_SIZE를 선택하는 것이 좋다.
만약 4KB 압축과 8KB 압축의 결과가 거의 차이나지 않는다면, 압축 실패율은 낮으면서 압축 효율은 상대적으로 높은 8KB를 선택하는 것이 훨씬 효율적일 것이다.
또한 압축 실패율이 높다고 하더라도, 전체적으로 데이터 파일의 크기가 큰 폭으로 줄어든다면 압축을 사용하는 것이 좋다.
압축 알고리즘은 예상외로 많은 CPU 자원을 소모하기 때문에 데이터가 빈번하게 저장되거나 변경된다면 압축을 고민해보는 것이 좋다.
압축 실패율을 낮춰야 한다면, 다음과 같은 튜닝 포인트를 사용해볼 수 있다.
•
innodb_cmp_per_index_enabled
테이블 압축이 사용된 테이블의 모든 인덱스별로 압축 성공 및 압축 실행 횟수를 수집하도록 설정한다.
비활성화된다면 테이블 단위의 압축 성공 및 압축 실행 횟수만 수집된다.
테이블 단위로 수집된 정보는 information_schema.INNODB_CMP 테이블에 기록되며, 인덱스 단위로 수집된 정보는 information_schema.INNODB_CMP_INDEX 테이블에 기록된다.
•
innodb_compression_level
InnoDB가 지원하는 압축 알고리즘인 zlib의 0부터 9까지의 압축률을 설정할 수 있다.
값이 작을 수록 압축 속도는 빨라지지만 저장 공간은 커질 수 있고, 값이 커질 수록 속도는 느려질 수 있지만 압축률은 높아진다.
압축 속도가 빨라진다는 것은 CPU 자원을 그만큼 적게 사용한다는 의미다.
•
innodb_compression_failure_threshold_pct, innodb_compression_pad_pct_max
테이블 단위로 압축 실패율이 innodb_compression_failure_threshold_pct의 설정 값보다 커지면 압축을 실행하기 전 원본 데이터 페이지의 끝에 일정 크기의 빈 공간을 추가한다.
이 빈 공간의 크기는 innodb_compression_pad_pct_max을 통해 전체 데이터 페이지 크기 대비 최대 공간을 지정할 수 있다.
이렇게 추가된 빈 공간은 압축률을 높여서 압축 결과가 KEY_BLOCK_SIZE보다 작아지게 만드는 효과를 내며, 압축 실패율이 높아질수록 계속 증가된 크기를 가진다.
•
innodb_log_compressed_pages
일시적으로 압축 알고리즘(zlib)의 버전 차이가 발생하더라도 정상적으로 동작하도록 압축된 페이지를 그대로 리두 영역에 기록한다.
하지만 데이터 페이지를 통째로 리두 영역에 저장하는 것은 리두 영역의 증가량에 영향을 미치기 때문에, innodb_log_compressed_pages 시스템 변수를 OFF한 후 모니터링하는 것이 좋다.
InnoDB 스토리지 엔진은 압축된 테이블의 데이터 페이지를 버퍼 풀에 적재하면 압축된 상태와 압축이 해제된 상태 2개 버전을 관리한다.
그래서 데이터 페이지를 관리하기 위한 LRU 리스트 또한 압축된 데이터 페이지를 관리하는 리스트와 압축이 해제된 데이터 페이지를 관리하는 리스트가 존재한다.
결국 버퍼 풀의 공간을 이중으로 사용함으로써 메모리를 낭비하고, 압축된 페이지에서 데이터를 읽거나 변경하기 위해서 압축을 해제하며 CPU를 많이 소모한다.
이러한 단점을 보완하기 위해 MySQL 서버로 유입되는 요청 패턴에 따라 적절히(Adaptive) 다음과 같은 처리를 수행한다.
•
InnoDB 버퍼 풀의 공간이 필요한 경우
LRU 리스트(압축된 데이터 페이지)에서는 유지하고, Unzip_LRU 리스트(압축 해제된 데이터 페이지)에서는 제거해서 버퍼 풀의 공간을 확보한다.
•
압축된 데이터 페이지가 자주 사용되는 경우
Unzip_LRU 리스트에 압축 해제된 페이지를 계속 유지하면서 압축 및 압축 해제 작업을 최소화한다.
•
압축된 데이터 페이지가 자주 사용되지 않는 경우
InnoDB 스토리지 엔진에 의해 LRU 리스트에서 제거되면 Unzip_LRU 리스트에서도 함께 제거된다.

7. 데이터 암호화

MySQL 5.7버전부터 지원된 데이터 암호화 기능은 데이터 파일(테이블스페이스)에 대해서만 암호화 기능이 제공됐다.
그러다 MySQL 8.0버전으로 업그레이드되면서 데이터 파일뿐만 아니라 리두 로그나 언두 로그, 복제를 위한 바이너리 로그 등도 모두 암호화 기능을 지원하기 시작했다.
중요한 정보를 저장하는 서비스에서는 응용 프로그램에서 암호화한 데이터를 데이터베이스 서버에서 다시 암호화하는 이중 암호화 방법을 선택하기도 한다.
응용 프로그램의 암호화는 주로 중요 정보를 가진 컬럼 단위로 암호화를 수행하며, 데이터베이스 수준에서는 테이블 단위로 암호화를 적용한다.

MySQL 서버의 데이터 암호화

MySQL 서버의 암호화 기능은 데이터베이스 서버와 디스크 사이의 데이터 읽고 쓰기 지점에서 암호화 또는 복호화를 수행한다.
즉, MySQL 서버(InnoDB 스토리지 엔진)의 I/O 레이어에서만 데이터의 암호화 및 복호화 과정이 실행된다.
이러한 암호화 방식을 가리켜 TDE(Transparent Data Encryption)라고 한다.
•
2단계 키 관리
MySQL 서버의 TDE에서 암호화 키는 키링 플러그인에 의해 관리된다.
MySQL 커뮤니티 에디션에서는 keyring_file 플러그인만 사용 가능하고, 그 외 플러그인은 모두 MySQL 엔터프라이즈 에디션에서만 사용 가능하다.
키링 플러그인은 2단계 키 관리 방식을 사용하는데, 마스터 키, 테이블스페이스 키라는 두 가지 종류의 키를 사용한다.
MySQL 서버는 외부 키 관리 솔루션 또는 디스크의 파일(keyring_file)에서 마스터 키를 가져오고, 암호화된 테이블이 생성될 때마다 해당 테이블을 위한 임의의 테이블스페이스 키를 발급한다.
그리고 MySQL 서버는 마스터 키를 이용해 테이블스페이스 키를 암호화해서 각 테이블의 데이터 파일 헤더에 저장한다.
mysql > ALTER INSTANCE ROTATE INNODB MASTER KEY;
Plain Text
복사
테이블스페이스 키는 기존 테이블이 삭제되지 않는 이상 절대 변경되지 않고, 외부로 노출되지 않는다.
하지만 마스터 키는 외부의 파일을 이용하기 때문에 노출될 가능성이 있기 때문에, 주기적으로 변경해야 한다.
마스터 키를 변경하게 되면 MySQL 서버는 기존의 마스터 키로 모든 테이블의 테이블스페이스 키를 복호화하고 새로운 마스터 키로 다시 암호화한다.
•
암호화와 성능
MySQL 서버의 TDE에서 지원되는 암호화 알고리즘은 AES 256비트이며, 테이블스페이스 키는 AES-256 ECB 알고리즘을 이용해 암호화되고 실제 데이터 파일은 AES-256 CBC 알고리즘을 이용해 암호화된다.
AES 암호화 알고리즘은 암호화하고자 하는 평문의 길이가 짧은 경우 암호화 키의 크기에 따라 암호화된 결과의 용량이 더 커질 수도 있다.
하지만 이미 데이터 페이지는 암호화 키보다 훨씬 크기 때문에 암호화를 하더라도 동일한 크기를 가지며, 이로 인한 버퍼 풀의 효율이나 메모리 사용 효율이 떨어지는 현상은 발생하지 않는다.
또한 TDE 방식이기 때문에 디스크로부터 한 번 읽은 데이터 페이지는 복호화되어 버퍼 풀에 저장되며 테이블의 변경은 백그라운드 스레드에 의해 수행되기 때문에, 암/복호화로 인해 쿼리 처리가 지연되지 않는다.
하지만 버퍼 풀에 존재하지 않는 데이터 페이지를 읽을 경우엔 복호화 지연이 발생한다.
만약 같은 테이블에 대해 암호화와 압축이 동시에 적용되면 MySQL 서버는 압축을 먼저 실행하고 암호화를 적용한다.
일반적으로 암호화된 결과물은 랜덤한 바이트의 배열을 갖고, 이는 압축률을 떨어뜨리기 때문이다.
또한 암호화된 테이블의 데이터 페이지는 복호화된 상태로 버퍼 풀에 저장되지만, 압축된 데이터 페이지는 압축 또는 압축 해제의 모든 상태로 버퍼 풀에 존재할 수 있기 때문에, 암호화가 먼저 적용된다면 압축 여부에 따라 매번 암복호화 작업을 수행해야 한다.
mysql > SELECT (SUM(SUM_TIMER_READ) / SUM(COUNT_READ)) / 1000000000 as avg_read_latency_ms, (SUM(SUM_TIMER_WRITE) / SUM(COUNT_WRITE)) / 1000000000 as avg_write_latency_ms FROM performance_schema.file_summary_by_instance WHERE file_name LIKE '%DB_NAME/TABLE_NAME%';
Plain Text
복사
위 쿼리를 통해 암호화된 테이블의 읽고 쓰기 성능을 직접 확인할 수 있다.
암호화된 테이블의 경우 읽기는 3~5배 정도 느리며, 쓰기의 경우 5~6배 정도 느린 것을 확인할 수 있다.
밀리초 단위의 차이이므로 수치가 낮은 편이라 크게 체감되진 않는다.
•
암호화와 복제
레플리카 서버는 소스 서버의 모든 사용자 데이터를 동기화하기 때문에 실제 데이터 파일도 동일할 것이라 생각할 수 있다.
하지만 TDE를 이용한 암호화 사용 시 마스터 키와 테이블스페이스 키는 그렇지 않다.
레플리카 서버가 되는 MySQL 서버 또한 기본적으로 각자 마스터 키를 갖기 때문에, 레플리카 서버와 소스 서버는 서로 다른 마스터 키를 갖는다.
그렇기 때문에 레플리카 서버와 소스 서버의 데이터 파일은 암호화 되기 전의 값이 동일하더라도 실제 암호화된 데이터가 저장된 데이터 파일의 내용은 완전히 달라진다.
또한 마스터 키 로테이션을 실행하더라도 레플리카 서버와 소스 서버가 각각 서로 다른 마스터 키를 새로 발급받는다.
이 경우 백업을 위해 TDE의 키링 파일도 함께 백업해야 한다.
•
keyrind_file 플러그인
MySQL 서버의 다른 플러그인과는 달리, TDE 플러그인의 경우 MySQL 서버가 시작되는 단계에서도 가장 빨리 초기화돼야 한다.
ealry-plugin-load=keyring_file.so keyring_file_data=/very/secure/directory/tde_master.key
Plain Text
복사
이와 같이 my.cnf에서 시스템 변수를 설정하면 keyring_file 플러그인을 가장 먼저 초기화할 수 있다.

테이블 암호화

키링 플러그인은 마스터 키를 생성하고 관리하는 부분까지만 담당하기 때문에 어떤 플러그인을 사용하든 관계없이 암호화된 테이블을 생성하고 활용하는 방법은 모두 동일하다.
mysql > CREATE TABLE tab_encrypted (...) ENCRYPTION='Y';
Plain Text
복사
일반적인 테이블 생성 구문과 동일하며, 마지막에 ENCRYPTION=’Y’ 옵션만 추가로 넣으면 된다.
이제부터 이 테이블의 데이터가 디스크에 기록될 때는 데이터가 자동으로 암호화되어 저장되고, 읽어올 때 복호화된다.
모든 테이블에 암호화를 적용하고자 한다면, default_table_encryption 시스템 변수를 ON으로 설정하면 된다.
•
응용 프로그램 암호화와의 비교
응용 프로그램에서 직접 암호화해서 MySQL 서버에 저장하게 되면, MySQL 서버는 저장되는 컬럼의 값이 이미 암호화된 것인지를 인지하지 못한다.
그래서 암호화된 컬럼은 인덱스를 생성하더라도 인덱스의 기능을 100% 활용할 수 없다.
예를 들어 출생 연도에 대한 컬럼을 응용 프로그램에서 암호화했다면, 출생 연도 범위의 사용자를 검색한다거나 출생 연도를 기준으로 정렬해서 상위 10개만 가져오는 등의 쿼리는 사용할 수가 없다.
하지만 MySQL 서버의 암호화 기능(TDE)을 사용한다면 이 같은 제약은 없다.
•
테이블스페이스 이동
테이블을 다른 서버로 복사해야 하는 경우 또는 특정 테이블의 데이터 파일만 백업했다가 복구하는 경우라면, 테이블스페이스 이동(Export & Import) 기능이 레코드를 덤프했다가 복구하는 방식보다 훨씬 효율적이고 빠르다.
하지만 TDE가 적용되어 암호화된 테이블의 경우 원본 MySQL 서버와 타겟 MySQL 서버의 마스터 키가 다르기 때문에 신경써야 할 부분이 있다.
mysql > FLUSH TABLE table FOR EXPORT;
Plain Text
복사
암호화되지 않은 테이블의 경우
1.
아직 반영되지 않은 변경 사항을 모두 디스크로 반영하고 테이블에 잠금을 건다.
2.
해당 테이블의 구조를 table.cfg 파일로 기록하고, table.ibd 파일과 table.cfg 파일을 타겟 서버로 복사한다.
3.
복사가 완료되면 UNLOCK TABLES를 통해 잠금을 해제한다.
암호화된 테이블의 경우
1.
임시로 사용할 마스터 키를 발급해서 table.cfp라는 파일로 기록한다.
2.
암호화된 테이블의 테이블스페이스 키를 기존 마스터 키로 복호화한 후, 임시로 발급한 마스터 키를 사용해 다시 암호화해서 데이터 파일의 헤더 부분에 저장한다.
3.
아직 반영되지 않은 변경 사항을 모두 디스크로 반영하고 테이블에 잠금을 건다.
4.
해당 테이블의 구조를 table.cfg 파일로 기록하고, table.ibd 파일과 table.cfg 파일과 table.cfp 파일을 타겟 서버로 복사한다.
5.
복사가 완료되면 UNLOCK TABLES를 통해 잠금을 해제한다.
*.cfg 파일은 단순히 테이블의 구조만 가지고 있기 때문에 파일이 없어져도 경고만 발생하고 테이블스페이스를 복구할 수 있지만, *.cfp 파일이 없어지면 복구가 불가능해진다.

언두 로그 및 리두 로그 암호화

MySQL 8.0.16버전부터 innodb_undo_log_encrypt, innodb_redo_log_encrypt 시스템 변수를 이용해 언두 로그와 리두 로그를 암호화된 상태로 저장할 수 있게 됐다.
실행 중인 MySQL 서버에서 언두 로그나 리두 로그에 대한 암호화를 활성화하더라도 모든 리두 로그나 언두 로그의 데이터를 해당 시점에 한 번에 암호화해서 다시 저장할 수 없다.
그래서 MySQL 서버는 암호화가 활성화되면 그때부터 생성되는 리두 로그나 언두 로그만 암호화해서 저장한다.
반대로 리두 로그와 언두 로그가 암호화되어 저장된 상태에서 암호화를 비활성화하면, 새로 생성되는 로그는 평문으로 저장되지만 기존 암호화된 로그는 여전히 암호화된 상태로 남아있는다.
테이블 암호화와 동일하게 리두 로그와 언두 로그 각각에 테이블스페이스 키가 마스터 키로 암호화되어 로그 파일의 헤더에 저장된다.

바이너리 로그 암호화

테이블 암호화가 적용돼도 바이너리 로그와 릴레이 로그 파일은 평문을 저장한다.
바이너리 로그는 상당히 긴 시간 동안 보관하기도 하고, 백업을 위해 보관하기도 하기 때문에 암호화가 중요할 수도 있다.
바이너리 로그와 릴레이 로그 파일 암호화 기능은 디스크에 저장된 로그 파일에 대한 암호화만 담당한다.
MySQL 서버의 메모리 내부 또는 네트워크 구간에서의 암호화가 필요하다면 SSL을 사용하도록 설정한다.
바이너리 로그와 릴레이 로그 파일의 데이터는 파일 키로 암호화해서 디스크로 저장하고, 파일 키는 바이너리 로그 암호화 키로 암호화해서 각 바이너리 로그와 릴레이 로그 파일의 헤더에 저장된다.
즉, 바이너리 로그 암호화 키는 마스터 키와 동일한 역할을 하며, 파일 키는 테이블스페이스 키와 동일한 역할을 한다.
mysql > ALTER INSTANCE ROTATE BINLOG MASTER KEY;
Plain Text
복사
바이너리 로그 암호화 키는 이 명령어를 통해 변경(로테이션)할 수 있고, 다음과 같은 과정을 거친다.
1.
증가된 시퀀스 번호와 함께 새로운 바이너리 로그 암호화 키 발급 후 키링 파일(keyring_file)에 저장
2.
바이너리 로그 파일과 릴레이 로그 파일 스위치(새로운 로그 파일로 로테이션)
3.
새로 생성되는 바이너리 로그와 릴레이 로그 파일의 암호화를 위해 파일 키를 생성하고, 파일 키는 새로운 바이너리 로그 파일 키(마스터 키)로 암호화해서 각 로그 파일의 헤더에 저장
4.
기존 바이너리 로그와 릴레이 로그 파일의 파일 키를 읽어서 새로운 바이너리 로그 파일 키로 암호화해서 다시 저장 (암호화되지 않은 로그 파일은 무시)
5.
모든 바이너리 로그와 릴레이 로그 파일이 새로운 바이너리 로그 암호화 키로 다시 암호화됐다면 기존 바이너리 로그 암호화 키를 키링 파일에서 제거
linux > mysqlbinlog --read-from-remote-server -uroot -p -vvv mysql-bin.000011
Plain Text
복사
트랜잭션의 내용을 추적하거나 백업 복구를 위해 암호화된 바이너리 로그를 평문으로 복호화할 일이 종종 발생한다.
하지만 한 번 바이너리 로그 파일이 암호화되면 바이너리 로그 암호화 키가 없으면 복호화할 수 없다.
mysqlbinlog 도구는 바이너리 로그를 열어볼 수 있게 해주는 도구이지만, 암호화된 바이너리 로그는 열어볼 수 없다는 에러 메시지를 출력한다.
유일한 방법으론 MySQL 서버가 바이너리 로그 파일을 가지고 있다는 가정하에 mysqlbinlog 도구에 --read-from-remote-server 파라미터를 통해 MySQL 서버에 접속해서 바이너리 로그를 가져오는 방법 밖에 없다.

8-1. 인덱스

디스크 읽기 방식

•
HDD(Hard Disk Drvie)와 SDD(Solid State Drive)
컴퓨터에서 CPU나 메모리 같은 주요 장치는 대부분 전자식 장치이며 짧은 시간 동안 매우 빠른 속도로 발전했다.
하지만 하드 디스크 드라이브는 기계식 장치로 상당히 제한적으로 발전했다.
그래서 데이터베이스 서버에서는 항상 디스크 장치가 병목이 된다.
이러한 기계식 하드 디스크 드라이브를 대체하기 위해 전자식 저장 매체인 SSD(Solid State Drive)가 많이 출시되고 있다.
SSD도 기존 하드 디스크 드라이브와 같은 인터페이스를 지원하므로 내장 디스크나 DAS(Direct Attached Stroage) 또는 SAN(Storage Area Network)에 그대로 사용할 수 있다.
SSD는 기존 하드 디스크 드라이브에서 데이터 저장용 플래터(원판)를 제거하고 그 대신 플래시 메모리를 장착하고 있다.
그래서 디스크 원판을 기계적으로 회전시킬 필요가 없으므로 아주 빨리 데이터를 읽고 쓸 수 있다.
플래시 메모리는 전원이 공급되지 않아도 데이터가 삭제되지 않는다.
그리고 컴퓨터의 메모리(D-RAM)보다는 느리지만 기계식 하드 디스크 드라이브보다는 훨씬 빠르다.
디스크 헤더를 움직이지 않고 한 번에 많은 데이터를 읽는 순차 I/O에서는 SSD가 HDD보다 조금 빠르거나 거의 비슷한 성능을 보이기도 한다.
하지만 SSD의 장점은 HDD보다 랜덤 I/O가 훨씬 빠르다는 것이다.
•
랜덤 I/O와 순차 I/O
3개의 페이지(3 * 16KB)를 디스크에 기록한다고 해보자.
순차 I/O는 1번 시스템 콜을 요청했지만, 랜덤 I/O는 3번 시스템 콜을 요청한다.
즉, 디스크에 기록해야 할 위치를 찾기 위해 순차 I/O는 디스크의 헤드를 1번 움직였고, 랜덤 I/O는 디스크 헤드를 3번 움직였다.
디스크의 성능은 디스크 헤더의 위치 이동 없이 얼마나 많은 데이터를 한 번에 기록하느냐에 의해 결정된다.
그러면 디스크 원판을 가지지 않는 SSD는 랜덤 I/O와 순차 I/O의 차이가 없을 것으로 예측하지만, 실제로는 랜덤I/O는 여전히 순차I/O보다 전체 스루풋이 떨어진다.
데이터베이스 대부분의 작업은 이러한 작은 데이터를 빈번히 읽고 쓰기 때문에 MySQL 서버에는 그룹 커밋이나 바이너리 로그 버퍼 또는 InnoDB 로그 버퍼 등의 기능이 내장돼 있다.
일반적으로 쿼리를 튜닝하는 것은 랜덤I/O 자체를 줄여주는 것이 목적이고, 큰 테이블의 레코드 대부분을 읽는 작업에서는 풀 테이블 스캔을 통해 순차I/O를 유도하기도 한다.

인덱스란?

책의 맨 끝에 있는 “찾아보기”에 비유해서 설명할 수 있다.
“찾아보기”가 인덱스라면 책의 내용은 데이터 파일에 해당한다.
“찾아보기”를 통해 알아낼 수 있는 페이지 번호는 데이터 파일에 저장된 레코드의 주소에 비유할 수 있다.
테이블의 모든 데이터를 검색해서 원하는 결과를 가져오려면 시간이 오래 걸리기 때문에 컬럼의 값과 해당 레코드가 저장된 주소로 키-값 쌍의 인덱스를 만들어 둔다.
또한 데이터가 많아져도 원하는 결과를 최대한 빠르게 찾아갈 수 있도록 인덱스를 주어진 순서로 미리 정렬해서 보관한다.
이로 인해 데이터가 저장될 때마다 항상 값을 정렬해야 하므로 저장하는 과정이 복잡하고 느리지만, 이미 정렬돼 있어서 아주 빨리 원하는 값을 찾을 수 있다.
결론적으로 DBMS에서 인덱스는 데이터 저장(INSERT, UPDATE, DELETE) 성능을 희생하고 그 대신 데이터의 읽기(SELECT) 속도를 높이는 기능이다.
인덱스는 역할에 따라 프라이머리 키, 세컨더리 키로 나눌 수 있다.
•
프라이머리 키
레코드를 대표하는 컬럼의 값으로 만들어진 인덱스를 의미한다.
프라이머리 키는 테이블에서 해당 레코드를 식별할 수 있는 기준값이 되기 때문에 식별자라고도 부른다.
NULL 값을 허용하지 않으며 중복을 허용하지 않는 것이 특징이다.
•
세컨더리 키
프라이머리 키를 제외한 나머지 모든 인덱스는 세컨더리 키로 분류한다.
유니크 인덱스는 프라이머리 키와 성격이 비슷하기에 프라이머리 키를 대체할 수 있는 대체 키라고도 하는데, 별도로 분류하기도 하고 세컨더리 키로 분류하기도 한다.
인덱스는 데이터 저장 방식(알고리즘)에 따라 B-Tree 인덱스, Hash 인덱스로 나눌 수 있다.
•
B-Tree 인덱스
B-Tree 인덱스는 컬럼의 값을 변형하지 않고 원래의 값을 이용해 인덱싱하는 알고리즘이다.
위치 기반 검색을 지원하기 위한 R-Tree와 InnoDB 스토리지 엔진에서 사용하는 B+Tree는 모두 B-Tree의 응용 알고리즘이다.
•
Hash 인덱스
Hash 인덱스는 컬럼의 값으로 해시값을 계산해서 인덱싱하는 알고리즘이다.
매우 빠른 검색을 지원하지만, 값의 일부만 검색하거나 범위를 검색할 때는 해시 인덱스를 사용할 수 없다.
인덱스는 중복 허용 여부에 따라 Unique 인덱스, Non-Unique 인덱스로 나눌 수 있다.
인덱스가 유니크한지 아닌지는 단순히 같은 값이 1개만 존재하는지 1개 이상 존재할 수 있는지를 의미한다.
하지만 옵티마이저에게는 상당히 중요한 문제로 유니크 인덱스에 대해 동등 조건(=)으로 검색한다는 것은 항상 1건의 레코드만 찾으면 된다는 것을 알려주는 효과를 낸다.
이뿐만 아니라 유니크 인덱스로 인한 MySQL의 처리 방식의 변화나 차이점이 상당히 많다.

8-2. B-Tree 인덱스

구조 및 특성

B-Tree는 트리 구조의 최상위에 하나의 루트 노드가 존재하고, 그 하위에 자식 노드가 붙어 있는 형태다.
트리 구조의 가장 하위에 있는 노드를 리프 노드라 하고, 트리 구조에서 루트 노드도 아니고 리프 노드도 아닌 중간의 노드를 브랜치 노드라고 한다.
데이터베이스에서 인덱스와 실제 데이터가 저장된 데이터는 따로 관리되는데, 인덱스의 리프 노드는 항상 실제 데이터 레코드를 찾아가기 위한 주솟값을 가지고 있다.
인덱스의 키 값은 모두 정렬돼 있지만, 데이터 파일의 레코드는 정렬돼 있지 않고 임의의 순서로 저장돼 있다.
만약 테이블의 레코드를 전혀 삭제하거나 변경하지 않고 INSERT만 수행한다면 데이터 파일의 레코드는 INSERT 순서대로 정렬되어 저장되겠지만, 가능한 한 삭제된 공간을 재활용하도록 DBMS가 설계되었다.
인덱스는 테이블의 키 컬럼만 가지고 있으므로 나머지 컬럼을 읽으려면 데이터 파일에서 해당 레코드를 찾아야 한다.
InnoDB 테이블은 프라이머리 키를 주소처럼 사용하기 때문에 논리적인 주소를 가진다고 볼 수 있다.
그래서 세컨더리 키 인덱스를 통해 레코드를 읽을 때는 데이터 파일을 바로 찾아가지 못한다.
세컨더리 키 인덱스의 B-Tree를 한 번 검색한 후, 프라이머리 키 인덱스(클러스터링 인덱스)의 B-Tree를 검색하여 리프 페이지에 저장돼 있는 레코드를 읽는다.
InnoDB 스토리지 엔진은 디스크에 데이터를 저장하는 가장 기본 단위를 페이지 또는 블록이라고 하며, 디스크의 모든 읽기 및 쓰기 작업과 버퍼 풀에서 데이터를 버퍼링하는 작업의 단위가 된다.
인덱스도 결국은 페이지 단위로 관리되며, 루트와 브랜치 그리고 리프 노드를 구분한 기준이 바로 페이지 단위다.
이진 트리는 각 노드가 자식 노드를 2개만 가지는데, DBMS의 B-Tree가 이진 트리라면 인덱스 검색이 비효율적일 것이기 때문에, DBMS의 B-Tree는 자식 노드의 개수가 가변적인 구조다.
인덱스 키가 16바이트라고 가정하고 각 키가 12바이트의 자식 노드 주소 영역을 가진다고 하면, 16KB 페이지에 585개의 키를 가지는 B-Tree가 만들어진다.
InnoDB의 페이지 크기는 innodb_page_size 시스템 변수를 통해 4~64KB 의 값을 설정할 수 있고, 자식 노드 주소 영역은 6~12바이트까지 다양한 크기의 값을 가질 수 있다.
인덱스 키의 크기가 늘어나면 한 페이지에 저장할 수 있는 키의 갯수가 줄어들고, 이로 인해 디스크로부터 읽어야 하는 페이지 수가 늘어나게 되면 그만큼 느려진다.
또한 인덱스 키 크기가 커질수록 메모리에 캐시해 둘 수 있는 레코드 수는 줄어들고, 메모리의 효율이 떨어지는 결과를 가져온다.

B-Tree 인덱스 키 추가, 삭제, 변경, 검색

•
인덱스 키 추가
새로운 키 값이 B-Tree에 저장될 때 테이블의 스토리지 엔진에 따라 새로운 키 값이 즉시 인덱스에 저장될 수도 있고 그렇지 않을 수도 있다.
B-Tree에 저장될 때는 저장될 키 값을 이용해 적절한 위치를 검색한다.
리프 노드가 꽉 차서 더는 저장할 수 없을 때는 리프 노드가 분리돼야 하는데, 상위 브랜치 노드까지 처리의 범위가 넓어지기 때문에 상대적으로 쓰기 작업에 비용이 많이 든다.
InnoDB 스토리지 엔진은 체인지 버퍼를 활용해 지연시켜 나중에 처리할 수 있지만, 프라이머리 키나 유니크 인덱스의 경우 중복 체크가 필요하기 때문에 즉시 B-Tree에 추가하거나 삭제한다.
•
인덱스 키 삭제
해당 키 값이 저장된 B-Tree의 리프 노드를 찾아서 그냥 삭제 마크만 하면 작업이 완료되기에 상당히 간단하다.
이렇게 삭제 마킹된 인덱스 키 공간은 계속 방치하거나 재활용할 수 있다.
마킹 작업은 디스크 쓰기가 필요하고, MySQL 5.5버전 이상의 InnoDB 스토리지 엔진은 이러한 쓰기를 체인지 버퍼를 활용해 지연 처리하여 MySQL 서버가 내부적으로 처리한다.
•
인덱스 키 변경
인덱스의 키 값은 그 값에 따라 저장될 리프 노드의 위치가 결정되므로, 단순히 키 값만 변경하는 것은 불가능하다.
B-Tree의 키 값 변경 작업은 먼저 키 값을 삭제한 후, 다시 새로운 키 값을 추가하는 형태로 처리된다.
InnoDB 스토리지 엔진을 사용하는 테이블은 이 작업 모두 체인지 버퍼를 활용해 지연 처리될 수 있다.
•
인덱스 키 검색
B-Tree 인덱스를 이용한 검색은 100% 일치, 값의 앞부분만 일치, 부등호 비교 조건에서 활용할 수 있지만, 인덱스를 구성하는 키 값의 뒷부분만 검색하는 용도로는 인덱스를 사용할 수 없다.
또한 함수나 연산을 수행함으로써 인덱스의 키 값에 변형이 가해진 후 비교되는 경우에는 절대 B-Tree의 빠른 검색 기능을 사용할 수 없다.
InnoDB 스토리지 엔진의 레코드 잠금이나 넥스트 키락(갭락)은 검색을 수행한 결과로 찾아진 인덱스를 잠근 후 테이블의 레코드를 잠그는 방식으로 구현돼 있다.
따라서 UPDATE, DELETE 쿼리가 실행될 때 테이블에 적절히 사용할 수 있는 인덱스가 없으면 불필요하게 많은 레코드를 잠근다.

B-Tree 인덱스 사용에 영향을 미치는 요소

•
B-Tree 깊이
B-Tree 인덱스의 깊이는 MySQL에서 값을 검색할 때 몇 번이나 랜덤하게 디스크를 읽어야 하는지와 직결되기 때문에 상당히 중요하지만 직접 제어할 방법은 없다.
결론적으로 인덱스 키 값의 크기가 커지면 커질수록 하나의 인덱스 페이지가 담을 수 있는 인덱스 키 값의 개수가 적어지고, 그 때문에 같은 레코드 건수라 하더라도 B-Tree의 깊이가 깊어져서 디스크 읽기가 더 많이 필요하게 된다.
즉, 인덱스 키 값의 크기는 가능하면 작게 만드는 것이 좋다.
•
선택도(기수성)
인덱스에서 선택도 또는 기수성은 거의 같은 의미로 사용된다.
모든 인덱스 키 값 가운데 유니크한 값의 수를 의미하고, 인덱스 키 값 가운데 중복된 값이 많아지면 많아질수록 기수성은 낮아지고 동시에 선택도 또한 떨어진다.
인덱스는 선택도가 높을수록 검색 대상이 줄어들기 때문에 그만큼 빠르게 처리되고, 선택도가 낮다면 그만큼 불필요한 데이터를 읽어오게 된다.
•
읽어야 하는 레코드의 건수
전체 테이블을 모두 읽어서 필요 없는 데이터를 버리는 것이 효율적일지, 인덱스를 통해 필요한 데이터만 읽어 오는 것이 효율적일지 판단해야 한다.
일반적인 옵티마이저는 인덱스를 통해 레코드 1건을 읽는 것이 테이블에서 직접 레코드 1건을 읽는 것보다 4~5배정도 비용이 더 많이 드는 작업인 것으로 예측한다.
즉, 인덱스를 통해 읽어야 할 레코드의 건수(옵티마이저가 예상하는 건수)가 전체 테이블 레코드의 20~25%를 넘어서면 인덱스를 이용하지 않고 테이블을 모두 직접 읽어서 필요한 레코드만 가려내는 방식으로 처리하는 것이 효율적이다.
만약 강제로 인덱스를 사용하도록 힌트를 추가해도 성능상 얻을 수 있는 이점이 없으며, 옵티마이저가 기본적으로 힌트를 무시하고 테이블을 직접 읽는 방식으로 처리한다.

B-Tree 인덱스를 통한 데이터 읽기

어떤 경우에 인덱스를 사용하게 유도할지 또는 사용하지 못하게 할지 판단하려면 어떻게 인덱스를 이용해서 실제 레코드를 읽는지 알아야 한다.
•
인덱스 레인지 스캔
mysql > SELECT * FROM employees WHERE first_name BETWEEN 'Ebbe' AND 'Gad';
Plain Text
복사
인덱스 레인지 스캔은 검색해야 할 인덱스의 범위가 결정됐을 때 사용하는 방식이다.
루트 노드에서부터 비교를 시작해 브랜치 노드를 거치고 최종적으로 리프 노드까지 찾아 들어가야만 비로소 필요한 레코드의 시작 지점을 찾을 수 있다.
일단 시작해야 할 위치를 찾으면 그때부터는 리프 노드의 레코드만 순서대로 읽으면 되며, 이를 스캔이라고 표현한다.
만약 스캔하다가 리프 노드의 끝까지 읽으면 리프 노드 간의 링크를 이용해 다음 리프 노드를 찾아서 다시 스캔하고, 최종적으로 스캔을 멈춰야 할 위치에 다다르면 지금까지 읽은 레코드를 사용자에게 반환한다.
중요한 것은 어떤 방식으로 스캔하든 관계없이, 해당 인덱스를 구성하는 컬럼의 정순 또는 역순으로 정렬된 상태로 레코드를 가져온다는 것이다.
이는 별도의 정렬 과정이 수반되는 것이 아니라 인덱스 자체의 정렬 특성 때문에 자동으로 그렇게 된다.
또한 리프 노드에서 검색 조건에 일치하는 건들은 데이터 파일에서 레코드를 읽어오는데, 레코드 한 건 한 건 단위로 랜덤 I/O가 한 번씩 일어난다.
이로 인해 인덱스를 통해 데이터 레코드를 읽는 작업은 비용이 많이 드는 작업으로 분류되고, 인덱스를 통해 읽어야 할 데이터 레코드가 20~25%를 넘으면 인덱스를 통한 읽기보다 테이블의 데이터를 직접 읽는 것이 더 효율적인 처리 방식이 된다.
정리하자면 인덱스 레인지 스캔은 크게 3단계를 거친다.
1.
인덱스에서 조건을 만족하는 값이 지정된 위치를 찾는다. (인덱스 탐색)
2.
1번에서 탐색된 위치부터 필요한 만큼 인덱스를 차례대로 쭉 읽는다. (인덱스 스캔)
3.
2번에서 읽어 들인 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고, 최종 레코드를 읽어 온다. (커버링 인덱스)
쿼리가 필요로 하는 데이터에 따라 커버링 인덱스 과정은 필요하지 않을 수도 있으며, 이 경우엔 랜덤 읽기가 상당히 줄어들고 성능은 그만큼 빨라진다.
•
인덱스 풀 스캔
인덱스 레인지 스캔과는 달리 인덱스의 처음부터 끝까지 모두 읽는 방식을 인덱스 풀 스캔이라고 한다.
인덱스 리프 노드의 제일 앞 또는 제일 뒤로 이동한 후, 인덱스의 리프 노드를 연결하는 링크드 리스트를 따라서 처음부터 끝까지 스캔하는 방식이다.
대표적으로 쿼리의 조건절에 사용된 컬럼이 첫 번째 순서로 만들어진 컬럼이 아닌 경우 인덱스 풀 스캔 방식이 사용된다.
일반적으로 인덱스의 크기는 테이블의 크기보다 작으므로 직접 테이블을 처음부터 끝까지 읽는 것보다는 인덱스만 읽는 것이 효율적이다.
쿼리가 인덱스에 명시된 컬럼만으로 조건을 처리할 수 있는 경우 사용되며, 데이터 레코드까지 읽어야 한다면 절대 사용되지 않는다.
•
루즈 인덱스 스캔
루즈 인덱스 스캔은 말 그대로 느슨하게 또는 듬성듬성하게 인덱스를 읽는 것을 의미하며, 인덱스 레인지 스캔과 인덱스 풀 스캔은 루즈 인덱스 스캔과는 상반된 의미에서 타이트 인덱스 스캔으로 분류된다.
인덱스 레인지 스캔과 비슷하게 작동하지만 중간에 필요치 않은 인덱스 키 값은 무시하고 다음으로 넘어가는 형태로 처리한다.
주로 GROUP BY 또는 집합 함수 가운데 MAX() 또는 MIN() 함수에 대해 최적화를 하는 경우에 사용된다.
•
인덱스 스킵 스캔
-- // gender, birth_date에 대한 인덱스 생성 mysql > ALTER TABLE employees ADD INDEX ix_gender_birthdate(gender, birth_date); -- // 인덱스 사용 불가능 mysql > SELECT * FROM employees WHERE birth_date >= '1965-02-01'; -- // 인덱스 사용 가능 mysql > SELECT * FROM employees WHERE gender='M' AND birth_date >= '1965-02-01';
Plain Text
복사
위의 두 쿼리 중 gender 컬럼과 birth_date 컬럼의 조건을 모두 가진 두번째 쿼리는 인덱스를 효율적으로 사용할 수 있지만 gender 컬럼에 대한 비교 조건이 없는 첫번째 쿼리는 인덱스를 사용할 수 없다.
이런 경우엔 birth_date 컬럼부터 시작하는 인덱스를 새로 생성해야 했지만, MySQL 8.0버전부터 옵티마이저가 gender 컬럼을 건너뛰어서 birth_date 컬럼만으로도 인덱스 검색이 가능하게 해주는 인덱스 스킵 스캔 최적화 기능이 도입됐다.
하지만 새로이 도입된 기능이기에 아직 다음과 같은 단점이 있다.
◦
WHERE 조건절에 조건이 없는 인덱스의 선행 컬럼의 유니크한 값의 개수가 적어야함
◦
쿼리가 인덱스에 존재하는 컬럼만으로 처리 가능해야 함(커버링 인덱스)
유니크한 값의 개수가 많다는 것은 레인지 스캔 시작 지점을 검색하는 작업이 많아진다는 것을 의미하기 때문에 쿼리 처리 성능이 매우 떨어진다.
그래서 인덱스 스킵 스캔은 인덱스의 선행 컬럼이 가진 유니크한 값의 개수가 소량일 때만 적용 가능한 최적화라는 것을 기억하자.

다중 컬럼 인덱스

실제 서비스용 데이터베이스에서는 2개 이상의 컬럼을 포함하는 인덱스가 더 많이 사용된다.
인덱스의 두 번째 컬럼은 첫 번째 컬럼에 의존해서 정렬되며, 두 번째 컬럼의 정렬은 첫 번째 컬럼이 똑같은 레코드에서만 의미가 있다.
만약 컬럼이 4개인 인덱스를 생성한다면 세 번째 컬럼은 두 번째 컬럼에 의존해서 정렬되고, 네 번째 컬럼은 다시 세 번째 컬럼에 의존해서 정렬된다.
다중 컬럼 인덱스에서 인덱스 내에서 각 컬럼의 위치(순서)가 상당히 중요하며, 신중하게 결정해야 한다.

B-Tree 인덱스의 정렬 및 스캔 방향

•
인덱스의 정렬
인덱스를 생성할 때 설정한 정렬 규칙에 따라서 인덱스의 키 값은 항상 오름차순이거나 내림차순으로 정렬되어 저장된다.
MySQL 5.7버전까지는 컬럼 단위로 정렬 순서를 혼합해서 인덱스를 생성할 수 없었기 때문에, 숫자 컬럼의 경우 -1을 곱한 값을 저장하는 우회 방법을 사용했었다.
mysql > CREATE INDEX ix_teamname_userscore ON employees (team_name ASC, user_score DESC);
Plain Text
복사
하지만 MySQL 8.0버전부터는 다음과 같은 형태의 정렬 순서를 혼합한 인덱스도 생성할 수 있게 됐다.
•
인덱스의 스캔 방향
인덱스는 항상 오름차순으로만 정렬돼 있지만 인덱스를 최솟값부터 읽으면 오름차순으로 값을 가져올 수 있고, 최댓값부터 거꾸로 읽으면 내림차순으로 값을 가져올 수 있다는 것을 옵티마이저는 이미 알고 있다.
즉, 인덱스 생성 시점에 오름차순 또는 내림차순으로 정렬이 결정되지만 쿼리가 그 인덱스를 사용하는 시점에 인덱스를 읽는 방향에 따라 오름차순 또는 내림차순 정렬 효과를 얻을 수 있다.
•
내림차순 인덱스
MySQL 서버의 InnoDB 스토리지 엔진에서 정순 스캔과 역순 스캔은 페이지(블록) 간의 양방향 연결 고리를 통해 전진하느냐 후진하느냐의 차이만 있지만, 실제 내부적으로는 역순 스캔이 정순 스캔에 비해 느릴 수 밖에 없는 2가지 이유가 있다.
◦
페이지 잠금이 인덱스 정순 스캔에 적합한 구조
◦
페이지 내에서 인덱스 레코드가 단방향으로만 연결된 구조
mysql > SELECT * FROM tab WHERE userid=? ORDER BY score DESC LIMIT 10; 오름차순 인덱스 : INDEX(userid ASC, score ASC) 내림차순 인덱스 : INDEx(userid DESC, score DESC)
Plain Text
복사
위 쿼리가 많은 레코드를 조회하면서 빈번하게 실행된다면 오름차순 인덱스보다는 내림차순 인덱스가 더 효율적이라고 볼 수 있다.
또한 많은 쿼리가 인덱스의 앞쪽만 또는 뒤쪽만 집중적으로 읽어서 인덱스의 특정 페이지 잠금이 병목이 될 것으로 예상된다면 쿼리에서 자주 사용되는 정렬 순서대로 인덱스를 생성하는 것이 잠금 병목 현상을 완화하는데 도움이 될 것이다.

B-Tree 인덱스의 가용성과 효율성

쿼리의 WHERE 조건이나, GROUP BY 또는 ORDER BY 절이 어떤 경우에 인덱스를 사용할 수 있고 어떤 방식으로 사용할 수 있는지 식별할 수 있어야한다.
그래야만 쿼리의 조건을 최적화하거나 역으로 쿼리에 맞게 인덱스를 최적으로 생성할 수 있다.
•
비교 조건의 종류와 효율성
다중 컬럼 인덱스에서 각 컬럼의 순서와 그 컬럼에 사용된 조건이 동등 비교(=)인지 아니면 범위(>, <) 조건인지에 따라 각 인덱스 컬럼의 활용 형태와 효율이 달라진다.
mysql > SELECT * FROM dept_emp WHERE dept_no='d002' AND emp_no >= 10114; 케이스 A : INDEX(dept_no, emp_no) 케이스 B : INDEX(emp_no, dept_no)
Plain Text
복사
케이스 A는 dept_no=’d002’ AND emp_no>=10144 인 레코드만 찾고, 그 이후에는 dept_no가 ‘d002’가 아닐 때까지 인덱스를 그냥 쭉 읽기만 하면 된다.
하지만 케이스 B는 우선 emp_no>=10144 AND dept_no=’d002’ 인 레코드를 찾고, 그 이후 모든 레코드에 대해 dept_no가 ‘d002’인지 비교하는 과정을 거쳐야 한다.
케이스 A에서 두 조건(dept_no=’d002’와 emp_no>=10144)과 같이 작업의 범위를 결정하는 조건을 ‘작업 범위 결정 조건’이라고 하고, 케이스 B에서 dept_no=’d002’ 조건과 같이 단순히 거름종이 역할만 하는 조건을 ‘필터링 조건’이라고 표현한다.
결국, 케이스 A에서 dept_no 컬럼과 emp_no 컬럼은 모두 ‘작업 범위 결정 조건’에 해당하지만, 케이스 B에서 emp_no 컬럼만 ‘작업 범위 결정 조건’이고 dept_no 컬럼은 ‘필터링 조건’이다.
작업 범위를 결정하는 조건은 많으면 많을수록 쿼리 처리 성능을 높이지만, 체크 조건은 많아지면 쿼리 처리 성능을 낮춘다.
•
인덱스의 가용성
B-Tree 인덱스의 특징은 왼쪽 값에 기준해서 오른쪽 값이 정렬되며, 하나의 컬럼 내에서뿐만 아니라 다중 컬럼 인덱스의 컬럼에 대해서도 함께 적용된다.
즉, 하나의 컬럼으로 검색했을 때 값의 왼쪽 부분이 없으면 인덱스 레인지 스캔 방식의 검색이 불가능하며, 다중 컬럼 인덱스에서도 왼쪽 컬럼의 값을 모르면 인덱스 레인지 스캔을 사용할 수 없다.
mysql > SELECT * FROM employees WHERE first_name LIKE '%mer';
Plain Text
복사
이 쿼리의 경우 first_name 컬럼에 저장된 값의 왼쪽부터 한 글자씩 비교해 가면서 일치하는 레코드를 찾아야 하지만, 조건절에 주어진 상숫값(%mer)에는 왼쪽 부분이 고정되지 않았다.
따라서 왼쪽 기준 정렬 기반의 인덱스인 B-tree에서는 인덱스의 효과를 얻을 수 없다.
•
가용성과 효율성 판단
B-Tree 인덱스의 특성상 다음의 경우 인덱스를 사용할 수 없다.
여기서 사용할 수 없다는 것은 작업 범위 결정 조건으로 사용할 수 없다는 것을 의미하며, 경우에 따라서는 필터링 조건으로 인덱스를 사용할 수는 있다.
◦
NOT-EQUAL
▪
.. WHERE column <> ‘N’
▪
.. WHERE column NOT IN (10, 11, 12)
▪
.. WHERE column IS NOT NULL
◦
LIKE ‘%??’
▪
.. WHERE column LIKE ‘%승환’
▪
.. WHERE column LIKE ‘_승환’
▪
.. WHERE column LIKE ‘%승%’
◦
스토어드 함수나 다른 연산자로 인덱스 컬럼이 변형된 후 비교된 경우
▪
.. WHERE SUBSTRING(column, 1, 1) = ‘X’
▪
.. WHERE DAYOFMONTH(column) = 1
◦
NOT-DETERMINISTIC 속성의 스토어드 함수가 비교 조건에 사용된 경우
▪
.. WHERE column = deterministic_function()
◦
데이터 타입이 서로 다른 비교 (타입을 변환해야 비교가 가능한 경우)
▪
.. WHERE char_column = 10
◦
문자열 데이터 타입의 콜레이션이 다른 경우
▪
.. WHERE utf8_bin_char_column = euckr_bin_char_column
일반적인 DBMS에서는 NULL 값이 인덱스에 저장되지 않지만 MySQL에서는 NULL 값도 인덱스에 저장된다.
따라서 IS NULL을 통한 WHERE 조건도 작업 범위 결정 조건으로 인덱스를 사용할 수 있다.
다중 컬럼 인덱스에서는 특정 조건에서 인덱스가 사용될 수 있고, 사용될 수 없다.
INDEX ix_test (column_1, column_2, column_3, ..., column_n)
Plain Text
복사
◦
작업 범위 결정 조건으로 인덱스를 사용하지 못하는 경우
▪
column_1 컬럼에 대한 조건이 없는 경우
▪
column_1 컬럼의 비교 조건이 위의 인덱스 사용 불가 조건 중 하나인 경우
◦
작업 범위 결정 조건으로 인덱스를 사용하는 경우
▪
column_1 ~ column_(i - 1) 컬럼까지 동등 비교 형태(= 또는 IN)로 비교
▪
column_i 컬럼에 대해 동등 비교(= 또는 IN), 범위 비교(> 또는 <), 패턴 일치(LIKE) 중 하나로 비교
위 두 가지 조건을 모두 만족하는 쿼리는 왼쪽 컬럼부터 작업 범위 결정 조건으로 사용되고, 그 이후 컬럼은 필터링 조건으로 사용된다.

8-3. 그 외 인덱스

클러스터링 인덱스

프라이머리 키 값이 비슷한 레코드끼리 묶어서 저장하는 것을 클러스터링 인덱스라고 표현한다.
클러스터링 인덱스는 프라이머리 키 값에 의해 레코드의 저장 위치가 결정되므로 프라이머리 키 값 자체에 의한 의존도가 크다.
따라서 클러스터링 인덱스로 저장되는 테이블은 프라이머리 키 기반의 검색이 매우 빠르며, 대신 레코드의 저장이나 프라이머리 키의 변경이 상대적으로 느리다.
프라이머리 키가 없는 InnoDB 테이블은 다음 우선순위대로 프라이머리 키를 대체할 컬럼을 선택한다.
1.
프라이머리 키가 있으면 기본적으로 프라이머리 키를 클러스터링 키로 선택
2.
NOT NULL 옵션의 UNIQUE INDEX 중에서 첫번째 인덱스를 클러스터링 키로 선택
3.
내부적으로 순차적으로 증가하는 값을 갖는 컬럼을 추가한 후 클러스터링 키로 선택
클러스터링 인덱스 구조는 일반 B-Tree와 비슷하다.
하지만 세컨더리 키를 위한 B-Tree의 리프 노드와는 달리 클러스터링 인덱스의 리프 노드에는 레코드의 모든 컬럼이 같이 저장돼 있음을 알 수 있다.
세컨더리 키 B-Tree 인덱스는 해당 조건에 맞는 프라이머리 키를 찾는 작업이며, 결국 클러스터링 인덱스를 통해 데이터 페이지의 레코드를 찾게 된다.
만약 InnoDB 테이블에서 세컨더리 인덱스가 실제 레코드가 저장된 주소를 가지고 있다면, 클러스터링 키 값이 변경될 때마다 데이터 레코드의 주소가 변경되고 그때마다 해당 테이블의 모든 인덱스에 저장된 주솟값을 변경해야 한다.
이런 오버헤드를 제거하기 위해 모든 세컨더리 인덱스는 해당 레코드가 저장된 주소가 아니라 프라이머리 키 값을 저장하도록 구현돼 있다.
또한 세컨더리 인덱스가 프라이머리 키만 가짐으로써 인덱스만으로 처리할 수 있는 경우가 많다. (커버링 인덱스)
클러스터링 테이블을 사용할 때 주의사항이 몇 가지 존재한다.
•
클러스터링 인덱스 키의 크기
클러스터링 테이블의 경우 모든 세컨더리 인덱스가 프라이머리 키 값을 포함하기 때문에, 프라이머리 키의 크기가 커지면 세컨더리 인덱스도 자동으로 크기가 커진다.
또한 인덱스가 커질수록 같은 성능을 내기 위해 그만큼의 메모리가 더 필요해지므로 프라이머리 키는 신중하게 선택해야 한다.
•
프라이머리 키는 AUTO-INCREMENT보다는 되도록 업무적인 컬럼으로 생성
프라이머리 키로 검색하는 경우(특히 범위로 많은 레코드를 검색하는 경우) 매우 빠르게 처리될 수 있다.
그렇기 때문에 컬럼의 크기가 크더라도 업무적으로 해당 레코드를 대표하고 자주 사용된다면, 그 컬럼을 프라이머리 키로 설정하는 것이 좋다.
•
프라이머리 키는 반드시 명시할 것
프라이머리 키를 없더라도 InnoDB 스토리지 엔진이 내부적으로 일련번호 컬럼을 추가한다.
그렇기 때문에 AUTO_INCREMENT 컬럼을 생성하고 프라이머리 키로 설정하는 것과 똑같다.
뿐만 아니라 정상적인 복제 성능을 보장하기 위해서라도 프라이머리 키의 생성은 꼭 필요하다.
•
AUTO-INCREMENT 컬럼을 인조 식별자로 사용할 경우
프라이머리 키를 대체하기 위해 인위적으로 추가된 프라이머리 키를 인조 식별자라고 한다.
로그 테이블과 같이 조회보다는 INSERT 위주의 테이블들은 AUTO_INCREMENT를 이용한 인조 식별자를 프라이머리 키로 설정하는 것이 성능 향상에 도움이 된다.

유니크 인덱스

유니크 인덱스와 유니크하지 않은 일반 세컨더리 인덱스는 사실 인덱스의 구조상 아무런 차이점이 없다.
하지만 MySQL에서는 인덱스 없이 유니크 제약만 설정할 방법이 없기 때문에, NOT NULL 속성과 함께 유니크 제약을 설정한다.
유니크 인덱스와 일반 세컨더리 인덱스의 읽기와 쓰기 성능을 비교해보면 다음과 같다.
•
인덱스 읽기
유니크하지 않은 세컨더리 인덱스는 중복된 값이 허용되므로 컬럼 값을 비교하기 위해 읽어야 하는 값이 많다.
따라서 유니크하지 않은 세컨더리 인덱스라고 해서 디스크 읽기 작업을 더 하지 않으므로 성능상 큰 차이가 없다.
인덱스의 성격이 유니크한지 아닌지에 따라 1개의 레코드를 읽느냐 2개 이상의 레코드를 읽느냐의 차이만 존재한다.
•
인덱스 쓰기
유니크 인덱스의 경우 키 값을 쓸 때는 중복된 값이 있는지 없는지 체크하는 과정이 한 단계 더 필요하다.
중복된 값을 체크할 때 읽기 잠금을 사용하고, 쓰기를 할 때는 쓰기 잠금을 사용하기 때문에, 이 과정에서 데드락이 빈번하게 발생한다.
또한 인덱스의 저장이나 변경 작업을 빠르게 처리하기 위해 체인지 버퍼를 사용하지만, 유니크 인덱스는 중복 체크를 해야 하므로 작업 자체를 버퍼링하지 못한다.
이 때문에 유니크 인덱스는 일반 세컨더리 인덱스보다 변경 작업이 느리게 작동한다.
꼭 필요한 경우라면 유니크 인덱스를 생성하는 것은 당연하다.
하지만 더 성능이 좋아질 것으로 생각하고 불필요하게 유니크 인덱스를 생성하는 것은 좋지 않다.
유일성이 꼭 보장돼야 하는 컬럼에 대해서는 유니크 인덱스를 생성하되, 꼭 필요하지 않다면 유니크 인덱스보다는 유니크하지 않은 세컨더리 인덱스를 생성하는 방법도 고려해보자.

함수 기반 인덱스

일반적인 인덱스는 컬럼의 값 일부(컬럼의 값 앞부분) 또는 전체에 대해서만 인덱스 생성이 허용된다.
컬럼의 값을 변경해서 만들어진 값에 대해 인덱스를 구축해야 한다면 함수 기반의 인덱스를 사용하면 된다.
MySQL 서버는 8.0버전부터 함수 기반 인덱스를 지원하기 시작했으며, 함수 기반 인덱스를 구현하는 방법은 2가지가 있다.
함수 기반 인덱스는 인덱싱할 값을 계산하는 과정의 차이만 있을 뿐, 실제 인덱스의 내부적인 구조 및 유지관리 방법은 B-Tree 인덱스와 동일하다.
•
가상 컬럼을 이용한 인덱스
mysql > CREATE TABLE user ( user_id BIGINT, first_name VARCHAR(10), last_name VARCHAR(10), PRIMARY KEY (user_id) ); -- // 가상 컬럼을 생성하고 가상 컬럼에 인덱스 생성 mysql > ALTER TABLE user ADD full_name VARCHAR(30) AS (CONCAT(first_name, ' ', last_name)) VIRTUAL, ADD INDEX ix_fullname (full_name);
Plain Text
복사
컬럼의 값을 변경해서 만들어진 값을 가상 컬럼으로 추가하고 그 가상 컬럼에 인덱스를 생성할 수 있다.
하지만 가상 컬럼은 테이블에 새로운 컬럼을 추가하는 것과 같은 효과를 내기 때문에 실제 테이블의 구조가 변경된다는 단점이 있다.
•
함수를 이용한 인덱스
-- // 함수를 사용하는 인덱스를 생성 mysql > CREATE TABLE user ( user_id BIGINT, first_name VARCHAR(10), last_name VARCHAR(10), PRIMARY KEY (user_id), INDEX ix_fullname ((CONCAT(first_name, ' ', last_name))) );
Plain Text
복사
테이블 구조를 변경하지 않고 함수를 직접 사용하는 인덱스를 생성할 수 있다.
하지만 함수 기반 인덱스를 사용하려면 반드시 조건절에 함수 기반 인덱스에 명시된 표현식이 그대로 사용돼야 한다.
혹시라도 함수 생성 시 명시된 표현식과 쿼리의 WHERE 조건절에 사용된 표현식이 다르다면, MySQL 옵티마이저는 다른 표현식으로 간주해서 함수 기반 인덱스를 사용하지 못한다.

R-Tree 인덱스

공간 인덱스는 R-Tree 인덱스 알고리즘을 이용해 2차원의 데이터를 인덱싱하고 검색하는 목적의 인덱스다.
GIS와 GPS에 기반을 둔 서비스의 경우 MySQL의 공간 확장을 이용하면 간단하게 기능을 구현할 수 있다.
•
구조 및 특성
MySQL은 공간 정보의 저장 및 검색을 위해 여러 가지 기하학적 도형(POINT, LINE, POLYGON, GEOMETRY) 정보를 관리할 수 있는 데이터 타입을 제공한다.
GEOMETRY 타입은 나머지 3개 타입의 슈퍼 타입으로 POINT, LINE, POLYGON 객체를 모두 저장할 수 있다.
R-Tree 인덱스는 MBR이라는 사각형들의 포함 관계를 B-Tree 형태로 구현한 인덱스이다.
MBR(Minimum Bounding Rectangle)이란 해당 도형을 감싸는 최소 크기의 사각형을 의미한다.
최상위 MBR은 루트 노드, 차상위 그룹 MBR은 브랜치 노드, 최하위 MBR은 리프 노드로 R-Tree 인덱스를 표현할 수 있다.
•
R-Tree 인덱스의 용도
위도, 경도 좌표 저장뿐 아니라 CAD/CAM 소프트웨어 또는 회로 디자인 등과 같이 좌표 시스템에 기반을 둔 정보에 대해서는 모두 사용할 수 있다.
각 도형의 포함 관계를 이용해 만들어진 인덱스이기 때문에, ST_Contains() 또는 ST_Within() 등과 같이 포함 관계를 비교하는 함수로 검색을 수행하는 경우에만 인덱스를 사용할 수 있다.
mysql > SELECT * FROM tb_location WHERE ST_Contains(사각 상자, px); mysql > SELECT * FROM tb_location WHERE ST_Within(px, 사각 상자); mysql > SELECT * FROM tb_location WHERE ST_Contains(사각 상자, px) AND ST_Distance_Sphere(p, px) <= 5 * 1000
Plain Text
복사
기준점 P로부터 반경 거리 5km 이내의 점들을 검색하려면 위와 같은 쿼리를 사용할 수 있다.
만약 사각형이 아닌 원의 범위를 검색하려면 ST_Distance_Sphere() 함수를 이용해 다시 한번 필터링해야 한다.

전문 검색 인덱스

B-Tree 인덱스는 실제 컬럼의 값이 1MB이더라도 1000바이트(MyISAM) 또는 3072바이트(InnoDB)까지만 잘라서 인덱스 키로 사용한다.
또한 B-Tree 인덱스의 특성에서도 알아봤듯이 전체 일치 또는 좌측 일부 일치와 같은 검색만 가능하다.
문서의 내용 전체를 인덱스화해서 특정 키워드가 포함된 문서를 검색하는 전문(Full Text) 검색에는 B-Tree 인덱스를 사용할 수 없다.
문서 전체에 대한 분석과 검색을 위한 이러한 인덱싱 알고리즘을 전문 검색(Full Text Search) 인덱스라고 한다.
전문 검색에서는 문서 본문의 내용에서 사용자가 검색하게 될 키워드를 분석해 내고, 빠른 검색용으로 사용할 수 있게 이러한 키워드로 인덱스를 구축한다.
전문 검색 인덱스는 불용어(Stop Word) 처리와 어근 분석의 두 가지 중요한 과정을 거쳐서 색인 작업이 수행된다.
불용어 처리는 검색에서 별 가치가 없는 단어를 모두 필터링해서 제거하는 작업을 의미한다.
불용어의 개수는 많지 않기 때문에 알고리즘을 구현한 코드에 모두 상수로 정의해서 사용하는 경우가 많고, 유연성을 위해 불용어 자체를 데이터베이스화해서 사용자가 추가하거나 삭제할 수 있게 구현하는 경우도 있다.
MySQL 서버 자체에도 불용어가 소스코드에 정의돼 있지만, my.cnf 파일에서 ft_stopword_file 시스템 변수에 빈 문자열을 설정하여 사용자가 이를 무시하고 별도로 정의할 수도 있다.
또한 InnoDB 스토리지 엔진의 전문 검색 인덱스에 대해서만 불용어 처리를 무시하려면 innodb_ft_enable_stopword 시스템 변수를 OFF로 설정하면 된다.
만약 사용자가 직접 정의한 불용어를 사용하려면 불용어 목록의 txt 파일을 ft_stopword_file 설정에 등록하거나, 불용어 목록의 테이블을 innodb_ft_server_stopword_table 시스템 변수에 설정하면 된다.
어근 분석은 문서의 키워드를 인덱싱하는 기법에 따라 크게 단어의 어근 분석과 n-gram 분석 알고리즘으로 구분할 수 있다.
•
어근 분석 알고리즘
어근 분석은 검색어로 선정된 단어의 뿌리인 원형을 찾는 작업이다.
MySQL 서버에서는 오픈소스 형태소 분석 라이브러리인 MeCab을 플러그인 형태로 사용할 수 있게 지원한다.
하지만 단어 사전이 필요하며, 문장을 해제해서 각 단어의 품사를 식별할 수 있는 문장의 구조 인식이 필요하다.
한글이나 일본어의 경우 영어와 같이 단어의 변형 자체는 거의 없기 때문에 어근 분석보다는 문장의 형태소를 분석해서 명사와 조사를 구분하는 기능이 더 중요한 편이다.
서구권 언어를 위한 형태소 분석기는 MongoDB에서 사용되는 Snowball이라는 오픈소스가 있다.
•
n-gram 분석 알고리즘
형태소 분석이 문장을 이해하는 알고리즘이라면, n-gram은 단순히 키워드를 검색해내기 위한 인덱싱 알고리즘이라고 할 수 있다.
n-gram에서 n은 인덱싱할 키워드의 최소 글자 수를 의미하는데, 일반적으로는 2글자 단위로 키워드를 쪼개서 인덱싱한다.
To be or not to be. That is the question
Plain Text
복사
각 단어는 다음과 같이 띄어쓰기(공백)와 마침표(.)를 기준으로 10개의 단어로 구분되고, 각 글자가 2글자씩 중첩해서 토큰으로 분리된다.
구분된 각 토큰은 B-Tree 인덱스에 저장되며, 이때 중복된 토큰은 하나의 인덱스 엔트리로 병합되어 저장된다.
MySQL 서버는 이렇게 생성된 토큰들에 대해 불용어를 걸러내는 작업을 수행하는데, 이때 불용어와 동일하거나 불용어를 포함하는 경우 걸러서 버린다.
mysql > CREATE TABLE table ( doc_id INT, doc_body TEXT, PRIMARY KEY (doc_id), FULLTEXT KEY fx_docbody (doc_body) WITH PARSER ngram ) ENGINE=InnoDB; -- // 풀 테이블 스캔 mysql > SELECT * FROM table WHERE doc_body LIKE '%애플%'; -- // 전문 검색 인덱스 사용 mysql > SELECT * FROM table WHERE MATCH(doc_body) AGAINST('애플' IN BOOLEAN MODE);
Plain Text
복사
전문 검색 인덱스를 사용하려면 반드시 다음 두 가지 조건을 갖춰야 한다.
•
쿼리 문장이 전문 검색을 위한 문법(MATCH … AGAINST …)을 사용
•
테이블이 전문 검색 대상 컬럼에 대해서 전문 인덱스 보유

멀티 밸류 인덱스

전문 검색 인덱스를 제외한 모든 인덱스는 레코드 1건이 1개의 인덱스 키 값을 가진다.
즉, 인덱스 키와 데이터 레코드는 1:1 관계를 가진다.
하지만 멀티 밸류 인덱스는 하나의 데이터 레코드가 여러 개의 키 값을 가질 수 있는 형태의 인덱스다.
이러한 인덱스는 정규화에 위배되는 형태지만, JSON 데이터 타입을 지원하기 시작하면서 필드에 저장된 원소들에 대한 인덱스 필요성이 생겨났다.
mysql > CREATE TABLE user ( user_id BIGINT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(10), last_name VARCHAR(10), credit_info JSON, INDEX mx_creditscores ((CAST(credit_info->'$.credit_scores' AS UNSIGNED ARRAY))) ); mysql > INSERT INTO user VALUES (1, 'Matt', 'Lee', '{"credit_scores":[360, 353, 351]}');
Plain Text
복사
멀티 밸류 인덱스를 활용하기 위해서는 반드시 다음 함수들을 이용해서 검색해야 옵티마이저가 인덱스를 사용할 수 있다.
•
MEMBER OF()
•
JSON_CONTAINS()
•
JSON_OVERLAPS()

8-4. 외래키

MySQL에서 외래키는 InnoDB 스토리지 엔진에서만 생성할 수 있으며, 외래키 제약이 설정되면 자동으로 연관되는 테이블의 컬럼에 인덱스까지 생성된다.
InnoDB의 외래키 관리에는 두 가지 특징이 있다.
1.
테이블의 변경(쓰기 잠금)이 발생하는 경우에만 잠금 경합(잠금 대기)이 발생한다.
2.
외래키와 연관되지 않은 컬럼의 변경은 최대한 잠금 경합(잠금 대기)을 발생시키지 않는다.
mysql > CREATE TABLE tb_parent ( id INT NOT NULL, fd VARCHAR(100) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB; mysql > CREATE TABLE tb_child ( id INT NOT NULL, pid INT DEFAULT NULL, fd VARCHAR(100) DEFAULT NULL, PRIMARY KEY (id), KEY ix_parentid (pid), CONSTRAINT child_ibfk_1 FOREIGN KEY (pid) REFERENCES tb_parent (id) ON DELETE CASCADE ) ENGINE=InnoDB; mysql > INSERT INTO tb_parent VALUES (1, 'parent-1'), (2, 'parent-2'); mysql > INSERT INTO tb_child VALUES (100, 1, 'child-100');
Plain Text
복사
•
자식 테이블의 변경이 대기하는 경우
1.
1번 커넥션에서 먼저 트랜잭션을 시작하고 부모 테이블에서 id가 2인 레코드에 UPDATE를 실행하며, 쓰기 잠금을 획득한다.
2.
2번 커넥션에서 자식 테이블의 외래키 컬럼인 pid를 2로 변경하는 쿼리를 실행한다.
3.
2번 커넥션의 작업은 1번 커넥션의 작업이 완료될 때까지 대기한다.
자식 테이블의 외래 키 컬럼의 변경(INSERT, UPDATE)은 부모 테이블의 확인이 필요하기 때문에, 쓰기 잠금이 걸려 있으면 해제될 때까지 대기하게 된다.
만약 자식 테이블의 외래 키가 아닌 컬럼을 변경할 경우 외래키로 인한 잠금 확장이 발생하지 않는다.
•
부모 테이블의 변경 작업이 대기하는 경우
1.
1번 커넥션에서 자식 테이블의 부모 키 1을 참조하는 레코드에 UPDATE를 실행하며, 쓰기 잠금을 획득한다.
2.
2번 커넥션에서 부모 테이블의 부모 키 1인 레코드를 삭제하는 쿼리를 실행한다.
3.
2번 커넥션의 작업은 1번 커넥션의 작업이 완료될 때까지 대기한다.
자식 테이블이 생성될 때 정의된 외래키의 특성(ON DELETE CASCADE) 때문에 부모 레코드가 삭제되면 자식 레코드도 동시에 삭제되기 때문에 대기해야 한다.
데이터베이스에서 외래 키를 물리적으로 생성하려면 잠금 경합까지 고려해 모델링을 진행하는 것이 좋다.
외래키로 인한 체크 작업이 아니라 체크를 위해 연관 테이블에 읽기 잠금을 걸어야 하기 때문에 전체적으로 쿼리의 동시 처리에 영향을 미친다.

9. 옵티마이저와 힌트