MySQL

MySQL 은 데이터를 여러 표에 나눠 담아 두고 질의로 꺼내 쓰는 데이터베이스 서버입니다. 데이터를 실제로 넣고 빼는 일은 갈아 끼울 수 있는 부품이 맡습니다. 오라클이 개발·배포·지원합니다.

상세

MySQL 은 데이터베이스 관리 시스템(Database Management System, DBMS)입니다. 데이터베이스는 구조를 갖춘 데이터의 모음입니다. 컴퓨터에 담긴 데이터를 넣고 꺼내고 처리하려면 MySQL 서버 같은 데이터베이스 관리 시스템이 필요합니다. 매뉴얼은 자신을 오픈소스라고 밝히고, 오라클이 개발·배포·지원한다고 적습니다.

MySQL 의 데이터베이스는 관계형입니다. 데이터를 한 창고에 몰아넣지 않고 별개의 테이블들에 나눠 담습니다. 논리 모델은 데이터베이스·테이블·뷰·행·열 같은 객체로 짜입니다. 서로 다른 데이터 필드 사이의 관계를 규칙으로 정해 둡니다. 일대일, 일대다, 유일, 필수, 선택, 그리고 테이블 사이의 포인터입니다. 데이터베이스가 이 규칙을 강제합니다. 매뉴얼은 잘 설계된 데이터베이스라면 애플리케이션이 일관성 없는 데이터, 중복된 데이터, 부모를 잃은 데이터, 낡은 데이터, 빠진 데이터를 보는 일이 없다고 적습니다.

이름의 뒷부분인 SQL 은 구조화 질의 언어(Structured Query Language)를 뜻합니다. 매뉴얼은 이것이 데이터베이스에 접근하는 데 쓰이는, 가장 흔한 표준화된 언어라고 적습니다. SQL 은 SQL 표준이 정합니다. 매뉴얼은 그 표준이 1986년부터 이어져 왔고 판이 여럿 있다고 적습니다.

클라이언트와 서버

MySQL 데이터베이스 소프트웨어는 클라이언트/서버 시스템입니다. 여러 백엔드를 받치는 멀티스레드 SQL 서버 하나와, 여러 클라이언트 프로그램과 라이브러리, 관리 도구, 그리고 넓은 범위의 응용 프로그램 인터페이스(Application Programming Interface, API)로 이루어집니다. 서버를 임베디드 멀티스레드 라이브러리로도 제공합니다. 애플리케이션에 링크해 넣으면 더 작고 관리 손이 덜 가는 독립 제품이 됩니다.

스토리지 엔진은 테이블 종류마다 SQL 연산을 처리하는 MySQL 구성요소입니다. 기본 엔진은 InnoDB 이고, 매뉴얼은 오라클이 특수한 용도가 아니면 테이블에 InnoDB 를 쓰기를 권한다고 적습니다. MySQL 8.4 의 CREATE TABLE 은 기본으로 InnoDB 테이블을 만듭니다. 매뉴얼은 이 구조를 플러거블 스토리지 엔진 아키텍처라고 부릅니다. 돌아가고 있는 서버에 엔진을 싣고 내릴 수 있습니다.

층은 셋입니다. 맨 위가 클라이언트 프로그램과 라이브러리와 관리 도구, 가운데가 멀티스레드 SQL 서버, 맨 아래가 스토리지 엔진입니다. 갈아 끼우는 자리는 맨 아래 한 층뿐입니다. 위의 두 층은 그대로 두고 테이블마다 아래 층의 엔진만 바꿉니다.

flowchart TD
    C["클라이언트 프로그램 · 라이브러리 · 관리 도구"] --> S["멀티스레드 SQL 서버"]
    subgraph SE["스토리지 엔진 · 갈아 끼우는 층"]
        E1["InnoDB · 기본"]
        E2["MyISAM"]
        E3["그 밖의 엔진"]
    end
    S --> E1
    S --> E2
    S --> E3

포기한 것

엔진마다 다른 보장

엔진을 갈아 끼울 수 있게 한 대신, 무엇을 보장하느냐가 엔진마다 갈립니다. SHOW ENGINES 의 출력이 이것을 칸으로 보여줍니다. InnoDB 의 설명 칸은 트랜잭션과 행 단위 잠금과 외래키를 지원한다고 적혀 있고 Transactions 칸이 YES 입니다. 같은 출력에서 MyISAM·MRG_MYISAM· BLACKHOLE 의 Transactions 칸은 NO 입니다.

MyISAM 기능표가 더 자세합니다. 트랜잭션 없음, 외래키 지원 없음, 다중 버전 동시성 제어(Multiversion Concurrency Control, MVCC) 없음, 잠금 단위는 테이블입니다. B-tree 인덱스와 전문 검색 인덱스는 있고 저장 한도는 256TB 입니다. 같은 서버 안이라도 테이블을 어느 엔진에 얹었느냐가 그 테이블의 보장을 정합니다.

트랜잭션 안의 스키마 변경

데이터 정의어(Data Definition Language, DDL) 문은 현재 세션에서 활성인 트랜잭션을 암묵적으로 끝냅니다. 그 문을 실행하기 전에 COMMIT 을 한 것과 같습니다. 대부분은 실행한 뒤에도 암묵 커밋을 일으킵니다. 매뉴얼은 그 의도가 그런 문 하나하나를 자기만의 특별한 트랜잭션으로 다루는 것이라고 적습니다. 앞 트랜잭션과 뒤 트랜잭션 사이에 그 문 하나가 따로 떨어져 앉는 모양입니다.

flowchart TD
    subgraph T1["앞 트랜잭션"]
        A["보통 문"]
    end
    subgraph X1["DDL 문 하나가 자기만의 트랜잭션"]
        D["DDL 문"]
    end
    subgraph T2["뒤 트랜잭션"]
        F["다음 문"]
    end
    A -->|"암묵 커밋"| D
    D -->|"암묵 커밋 · 대부분"| F

매뉴얼은 원자적 DDL 이 트랜잭션 DDL 은 아니라고 못 박습니다. DDL 문은 다른 트랜잭션 안에서 수행될 수 없습니다. START TRANSACTION … COMMIT 같은 트랜잭션 제어문 안에서도 안 되고, 같은 트랜잭션 안에서 다른 문과 묶일 수도 없습니다. 목록에는 ALTER TABLE·CREATE TABLE· CREATE INDEX·DROP TABLE·RENAME TABLE·TRUNCATE TABLE·INSTALL PLUGIN 이 들어갑니다. 예외가 하나 적혀 있습니다. TEMPORARY 키워드를 쓴 CREATE TABLE 과 DROP TABLE 은 트랜잭션을 커밋하지 않습니다.

기본이 비동기인 복제

복제는 한 MySQL 서버의 데이터를 하나 이상의 다른 MySQL 서버로 복사합니다. 앞쪽을 소스, 뒤쪽을 레플리카라고 부릅니다. 복제는 기본이 비동기입니다. 그 대신 레플리카는 소스에서 갱신을 받으려고 영구히 연결돼 있을 필요가 없습니다. 설정에 따라 모든 데이터베이스, 고른 데이터베이스, 또는 데이터베이스 안의 고른 테이블까지 범위를 정합니다.

utf8mb3 과 보조 평면 문자

utf8mb3 문자셋은 기본 다국어 평면(Basic Multilingual Plane, BMP) 문자만 지원합니다. 보조 문자는 지원하지 않습니다. 멀티바이트 문자 하나에 최대 3바이트를 씁니다. 매뉴얼의 목차는 utf8 을 utf8mb3 의 사용 중단된 별명이라고 적습니다.

보조 문자를 담는 자리는 utf8mb4 로 따로 냈습니다. 이쪽은 BMP 문자와 보조 문자를 모두 지원하고 문자 하나에 최대 4바이트를 씁니다. BMP 문자에 대해서는 두 문자셋의 저장 특성이 같습니다. 같은 코드값, 같은 인코딩, 같은 길이입니다. 보조 문자는 utf8mb4 가 4바이트로 담고 utf8mb3 은 아예 담지 못합니다. utf8mb4 는 utf8mb3 의 상위집합입니다. 매뉴얼은 MySQL 의 권장 문자셋이 utf8mb4 이고 새 애플리케이션은 전부 이것을 쓰라고 적습니다. utf8mb3 은 사용 중단됐고, MySQL 8.0.x 와 8.4.x 장기 지원(Long-Term Support, LTS) 릴리스 계열의 수명 동안 지원이 유지됩니다. 이후의 주요 릴리스에서 제거될 것으로 예상하라고 적습니다.

예시

mysql 클라이언트 세션

mysql> SELECT VERSION(), CURRENT_DATE;
+-----------+--------------+
| VERSION() | CURRENT_DATE |
+-----------+--------------+
| 8.4.0-tr | 2024-01-25 |
+-----------+--------------+
1 row in set (0.00 sec)

mysql> 

매뉴얼이 처음 치라고 싣는 질의입니다. mysql> 프롬프트 뒤에 문을 치고 엔터를 누릅니다. 서버가 자기 버전 번호와 현재 날짜를 돌려주고, 클라이언트가 그것을 표로 그립니다. 마지막 줄의 1 row in set 이 돌려준 행 수입니다.

엔진을 정해 테이블 만들기

-- ENGINE=INNODB not needed unless you have set a different
-- default storage engine.
CREATE TABLE t1 (i INT) ENGINE = INNODB;
-- Simple table definitions can be switched from one to another.
CREATE TABLE t2 (i INT) ENGINE = CSV;
CREATE TABLE t3 (i INT) ENGINE = MEMORY;

테이블을 만들 때 CREATE TABLE 에 ENGINE 테이블 옵션을 붙여 어느 스토리지 엔진을 쓸지 지정합니다. 이 옵션을 빼면 기본 엔진이 쓰이고, MySQL 8.4 의 기본 엔진은 InnoDB 입니다. 서버의 기본 엔진은 --default-storage-engine 시작 옵션이나 my.cnf 설정 파일의 default-storage-engine 옵션으로 정합니다. 현재 세션만 바꾸려면 default_storage_engine 변수를 설정합니다. CREATE TEMPORARY TABLE 로 만드는 임시 테이블의 엔진은 default_tmp_storage_engine 으로 따로 정합니다. 이미 만든 테이블을 옮길 때는 ALTER TABLE t ENGINE = InnoDB; 처럼 새 엔진을 가리키는 ALTER TABLE 을 씁니다.

WordPress

WordPress 공식 요구사항 문서는 호스트가 갖추기를 권하는 목록에 데이터베이스 항목을 두고 MariaDB 10.11+ or MySQL 8.0+ 라고 적습니다. 이 물건을 안에 넣고 도는 시스템이 그 사실을 스스로 밝힌 문장입니다.

운영

굴릴 때 만지는 손잡이는 시스템 변수입니다. 기본값이 먼저 걸리는 자리입니다.

max_connections 의 실효 최댓값은 둘 중 작은 쪽입니다. open_files_limit 의 실효값에서 810 을 뺀 값과, max_connections 에 실제로 설정한 값입니다. transaction_isolation 은 전역·세션·다음 트랜잭션 세 가지 범위를 갖습니다. 매뉴얼은 이 세 범위 구현 때문에 표준과 다른 격리 수준 할당 의미가 생긴다고 적습니다.

표의 손잡이 가운데 셋은 데이터가 놓이는 자리에 붙습니다. innodb_buffer_pool_size 는 메모리 쪽 크기를 정하고, innodb_flush_log_at_trx_commit 과 sync_binlog 는 커밋 때 디스크로 무엇이 언제 넘어가는지를 정합니다. 버퍼 풀은 테이블과 인덱스 데이터를 담아 두는 메모리 영역이고, 로그와 바이너리 로그는 디스크에 남습니다.

flowchart TD
    subgraph MEM["메모리"]
        BP["버퍼 풀 · 크기는 innodb_buffer_pool_size"]
    end
    CM["커밋"]
    subgraph DSK["디스크"]
        DT["테이블과 인덱스 데이터"]
        LG["로그"]
        BL["바이너리 로그"]
    end
    DT -->|"캐시"| BP
    CM -->|"innodb_flush_log_at_trx_commit"| LG
    CM -->|"sync_binlog"| BL

innodb_buffer_pool_size

버퍼 풀은 InnoDB 가 테이블과 인덱스 데이터를 캐시하는 메모리 영역입니다. 기본값은 134217728 바이트, 곧 128MB 입니다. 최댓값은 프로세서 아키텍처에 따라 갈립니다. 32비트 시스템에서는 4294967295 이고 64비트 시스템에서는 18446744073709551615 입니다. 32비트 시스템에서는 프로세서 아키텍처와 운영체제가 이 최댓값보다 낮은 실제 상한을 강제할 수도 있습니다. 전역 변수이고 동적으로 바꿀 수 있습니다.

버퍼 풀이 크면 같은 테이블 데이터를 여러 번 읽을 때 디스크 입출력이 덜 필요합니다. 매뉴얼은 전용 데이터베이스 서버라면 버퍼 풀 크기를 그 기계 물리 메모리의 80% 로 둘 수도 있다고 적고, 아래 자리들을 염두에 두고 필요하면 크기를 되돌릴 준비를 하라고 덧붙입니다.

• 물리 메모리를 두고 경쟁하면 운영체제에서 페이징이 일어날 수 있습니다
• InnoDB 는 버퍼와 제어 구조에 메모리를 더 잡습니다. 그래서 총 할당량이 지정한 버퍼 풀 크기보다 약 10% 큽니다
• 버퍼 풀의 주소 공간은 연속이어야 합니다. 특정 주소에 적재되는 DLL(Dynamic-Link Library, 동적 링크 라이브러리)이 있는 윈도우 시스템에서는 이것이 문제가 될 수 있습니다
• 버퍼 풀 초기화에 걸리는 시간은 대체로 그 크기에 비례합니다. 버퍼 풀이 큰 인스턴스에서는 초기화 시간이 상당할 수 있습니다. 서버를 내릴 때 버퍼 풀 상태를 저장했다가 시작할 때 복원하면 이 구간을 줄일 수 있습니다

버퍼 풀 크기가 1GB 를 넘을 때 innodb_buffer_pool_instances 를 1보다 큰 값으로 두면 바쁜 서버에서 확장성이 나아질 수 있습니다. 크기를 늘리거나 줄이는 작업은 청크 단위로 이루어집니다. 청크 크기는 innodb_buffer_pool_chunk_size 가 정하고 기본값은 128MB 입니다.

innodb_flush_log_at_trx_commit

이 변수는 커밋 연산의 엄격한 ACID(Atomicity, Consistency, Isolation, Durability. 원자성· 일관성·격리성·지속성) 준수와, 커밋에 딸린 입출력을 재배열해 묶어서 처리할 때 얻는 성능 사이의 균형을 조절합니다. 유효값은 0·1·2 이고 기본값은 1 입니다. 매뉴얼은 기본값을 바꾸면 성능이 나아질 수 있지만 그때는 크래시에서 트랜잭션을 잃을 수 있다고 적습니다.

0 과 2 의 초당 플러시는 100% 보장되지 않습니다. DDL 변경이나 InnoDB 내부 활동이 이 설정과 무관하게 로그를 플러시해서 더 자주 일어날 수도 있고, 스케줄링 사정으로 덜 자주 일어날 수도 있습니다. 플러시 주기 자체는 innodb_flush_log_at_timeout 이 정합니다. 1초에 한 번 플러시되면 크래시에서 최대 1초치 트랜잭션을 잃을 수 있습니다. InnoDB 의 크래시 복구는 이 설정과 상관없이 동작합니다. 트랜잭션은 통째로 적용되거나 통째로 지워집니다.

복제 구성에서 내구성과 일관성을 지키려면 매뉴얼이 두 가지를 지시합니다. 바이너리 로깅을 켰으면 sync_binlog=1 로 두고, innodb_flush_log_at_trx_commit 은 언제나 1로 둡니다. sync_binlog 쪽은 0 이면 동기화를 서버가 하지 않고 운영체제에 맡깁니다. 이때는 전원 장애나 운영체제 크래시에서 커밋은 됐지만 바이너리 로그에는 동기화되지 않은 트랜잭션이 생길 수 있습니다. 1 이면 트랜잭션이 커밋되기 전에 바이너리 로그를 디스크로 동기화합니다. 매뉴얼은 이것이 가장 안전한 설정이지만 디스크 쓰기가 늘어 성능에 부정적 영향이 있을 수 있다고 적습니다.

sql_mode

MySQL 8.4 의 기본 SQL 모드에는 여섯 가지가 들어 있습니다. ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION 입니다. 서버 시작 때 정하려면 명령줄의 --sql-mode="modes" 옵션을 쓰거나, my.cnf 나 my.ini 같은 옵션 파일에 sql-mode="modes" 를 적습니다. 모드는 쉼표로 구분한 목록입니다. 명시적으로 비우려면 빈 문자열로 설정합니다.

볼 자리

SHOW ENGINES 는 이 서버가 어느 스토리지 엔진을 받치는지 보여줍니다. Support 칸의 값이 YES·NO·DEFAULT 로, 각각 쓸 수 있음, 쓸 수 없음, 쓸 수 있고 현재 기본 엔진으로 설정됨을 가리킵니다.

InnoDB 안쪽은 표준 모니터가 보여줍니다.

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2018-04-12 15:14:08 0x7f971c063700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 4 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 15 srv_active, 0 srv_shutdown, 1122 srv_idle
srv_master_thread log flush and writes: 0

출력 머리에 초당 평균이 최근 몇 초를 기준으로 계산됐는지가 적힙니다. 그 아래로 백그라운드 스레드 구간이 이어지고, srv_master_thread 의 루프 횟수가 활성·종료·유휴로 갈려 나옵니다.

관련 항목

MySQL을 이루는 구성 요소

스토리지 엔진 · InnoDB · MyISAM · 데이터 딕셔너리 · mysql 클라이언트 · API · 백엔드 · 임베디드

MySQL이 다루는 데이터 객체

데이터베이스 · 테이블 · 뷰 · 스키마 · 인덱스

MySQL이 따르는 질의 언어·표준

SQL · SQL 표준

MySQL이 얹히는 실행 환경

운영체제

MySQL이 비롯된 배경

개발 · 배포

트랜잭션이 지키는 성질

ACID · 격리 수준 · 다중 버전 동시성 제어 · MVCC · 일관된 비잠금 읽기 · 잠금 읽기 · autocommit

트랜잭션에서 자주 나는 문제

데드락 · 팬텀 행

MySQL이 거치는 트랜잭션 처리 단계

세션 · 트랜잭션 · 커밋 · DDL

MySQL이 메모리에 두는 캐시 구조

버퍼 풀 · 체인지 버퍼 · 적응형 해시 인덱스 · 로그 버퍼

MySQL이 디스크에 두는 저장 구조

클러스터드 인덱스 · 보조 인덱스 · B-tree · 전문 인덱스 · 테이블스페이스 · AUTO_INCREMENT

MySQL이 내구성을 지키려 쓰는 로그·버퍼

리두 로그 · 언두 로그 · 언두 테이블스페이스 · 더블라이트 버퍼

MySQL이 데이터를 복제·백업하는 수단

복제 · 바이너리 로그 · mysqldump · MySQL Shell 덤프 유틸리티

MySQL이 문자를 인코딩하는 체계

문자셋 · 콜레이션 · utf8mb4 · utf8mb3 · BMP

MySQL을 조절하는 시스템 변수

시스템 변수 · sql_mode · innodb_buffer_pool_size · innodb_flush_log_at_trx_commit · max_connections · transaction_isolation · sync_binlog

MySQL이 내구성과 저울질하는 성능 요인

성능 · 스케줄링

실제로 이 서버를 채택한 시스템

WordPress · MediaWiki