Mysql

옵티마이저

사용자의 쿼리에 대해 최적의 실행방법(실행계획)을 결정하는 역할을 합니다.

옵티마이저에는 규칙기반과 비용기반이 있으며, 관계형 DB 에는 비용기반 옵티마이저를 제공합니다.

비용기반 옵티마이저

쿼리를 처리하는데 필요한 가장 적은 비용이 드는 실행계획을 선택하는 방식으로, 비용은 예상 소요시간 혹은 자원 사용량을 의미합니다. 이를 예측하기 위해 테이블, 인덱스, 칼럼 등의 객체 통계정보와 시스템 통계정보를 이용하며, 통계정보가 없는 경우에는 정확한 비용 예측이 불가능해져서 비효율적인 실행계획을 생성할 수 있습니다.

대안계획생성기에서 적용순서변경, 연산방법변경, 조인순서변경 등으로 다양한 대안 계획을 생성하여 최적의 방안을 찾으며, 이에 대한 비용을 예측하는 모듈도 존재합니다.

대안계획의 정확한 비용을 예측하기 위해서 연산의 중간 집합의 크기 및 결과 집합의 크기, 분포도 등의 예측이 정확해야 하므로, 보다 나은 예측을 위해 옵티마이저는 정확한 통계정보를 필요로 합니다. 또한 대안 계획을 구성하는 각 연산에 대한 비용 계산식이 정확해야 합니다.

규칙기반 옵티마이저는 인덱스를 사용할 수 있다면 항상 풀 테이블 스캔 보다는 인덱스를 사용하는 실행계획을 생성하지만, 비용기반 옵티마이저는 인덱스를 사용하는 비용이 풀 테이블 스캔 비용보다 크다고 판단되면 전체 테이블 스캔을 수행하는 방법으로 실행계획을 생성할 수도 있습니다.

비용기반 옵티마이저는 통계정보, DBMS 버전, DBMS 설정 정보 등의 차이로 인해 동일 SQL 문도 서로 다른 실행계획이 생성될 수 있고, 비용기반 옵티마이저의 다양한 한계들로 인해 실행계획의 예측 및 제어가 어렵다는 단점이 있습니다.

table full scan 인 경우

1. SQL 문에 조건이 존재하지 않는 경우

SQL 문에 조건이 존재하지 않는다는 것은 테이블의 모든 블록을 읽으면서 무조건 결과를 반환합니다.

2. SQL 문의 주어진 조건에 사용 가능한 인덱스가 존재하는 않는 경우

사용 가능한 인덱스가 존재하지 않는다면 테이블의 모든 데이터를 읽으면서 주어진 조건을 만족하는지 검사하고, 주어진 조건에 사용 가능한 인덱스는 존재하나 함수를 사용하여 인덱스 칼럼을 변형한 경우에 인덱스를 사용할 수 없습니다.

3. 옵티마이저의 취사 선택

조건을 만족하는 데이터가 많은 경우, 결과를 추출하기 위해서 테이블의 대부분의 블록을 액세스해야 한다고 옵티마이저가 판단하면 조건에 사용 가능한 인덱스가 존재해도 전체 테이블 스캔 방식으로 읽을 수 있습니다.

4. 그 밖의 경우 병렬처리 방식으로 처리하는 경우 또는 전체 테이블 스캔 방식의 힌트를 사용한 경우에 전체 테이블 스캔 방식으로 데이터를 읽을 수 있습니다.

index scan

리프 블럭은 인덱스를 구성하는 칼럼의 데이터와 해당 데이터를 가지고 있는 행의 위치를 가리키는 레코드 식별자(RID) 로 구성됩니다. 리프 블럭은 양방향 링크로써, 내림, 오름차순 검색을 쉽게할 수 있어, '=', '>', between 등 연산자로 검색하는 방법에 모두 적합합니다.

만약 37 ~ 50 을 찾고자한다면, 먼저 37에 해당하는 리프블럭을 찾고, 양방향링크를 통해 50보다 큰 값을 만날때까지 오른쪽으로 이동하면서 인덱스를 읽습니다. (만약 인덱스에 존재하지 않는 칼럼의 값이 필요하다면 읽은 레코드 식별자로 테이블을 엑세스합니다.)

SQL 문에서 필요로 하는 모든 칼럼이 인덱스 구성 칼럼에 포함된 경우 테이블에 대한 액세스는 발생하지 않습니다.

인덱스는 인덱스 구성 칼럼의 순서로 정렬되어 있습니다. 인덱스의 구성 칼럼이 A+B 라면 먼저 칼럼 A 로 정렬되고 칼럼 A 의 값이 동일할 경우에는 칼럼 B 로 정렬되며 칼럼 B 까지 모두 동일하면 레코드 식별자로 정렬됩니다. 인덱스가 구성 칼럼으로 정렬되어 있기 때문에 인덱스를 경유하여 데이터를 읽으면 그 결과 또한 정렬되어 반환됩니다.

인덱스 스캔은 인덱스에 존재하는 레코드 식별자를 이용해서 검색하는 데이터의 정확한 위치를 알고서 데이터를 읽으므로 인덱스 스캔 방식에서는 불필요하게 다른 블록을 더 읽을 필요가 없습니다. 따라서 한번의 I/O 요청에 한 블록씩 데이터를 읽는 반면, 풀 테이블 스캔은 데이터를 읽을 때 한번의 I/O 요청으로 여러 블록을 한꺼번에 읽습니다.

따라서 대용량 데이터 중에서 극히 일부의 데이터를 찾을 때, 인덱스 스캔 방식은 인덱스를 이용해 몇 번의 I/O 만으로 원하는 데이터를 쉽게 찾을 수 있지만 풀 테이블 스캔은 테이블의 모든 데이터를 읽으면서 원하는 데이터를 찾아야 하기 때문에 비효율적인 검색을 하게 됩니다.

반대로 테이블의 대부분의 데이터를 찾을 때는 한 블록씩 읽는 인덱스 스캔 방식 보다는 어차피 대부분의 데이터를 읽을 거라면 한번에 여러 블록씩 읽는 풀 테이블 스캔 방식이 유리할 수 있습니다.

기능

계정

계성생성

아래 명령어로 계정을 생성할 수 있습니다.

CREATE USER '사용자명'@'접속 호스트 IP' IDENTIFIED BY '비밀번호';

만약 특정 호스트(111.111.111.111)만 계정에 접근가능하게 하려면 'web'@'111.111.111.111' 와 같이 할 수 있고, 아무곳에서나 접근가능하게 하려면 'web'@'%' 를 합니다. 또한, 'web'@'192.68.%.%' 와 같이 특정 대역폭으로 IP 를 설정할 수도 있습니다.

참고로 사용자명이 동일해도 IP 가 다르면 다른 계정으로 취급됩니다. 따라서 권한도 각각 부여해야합니다.

권한부여

계정에 따라 수행할 수 있는 권한을 설정할 수 있습니다.

# 인스턴스 레벨단위
GRANT 권한명, 권한명 ON *.* TO '사용자명'@'접속 호스트 IP';
# 데이터베이스 레벨단위
GRANT 권한명, 권한명 ON 데이터베이스명.* TO '사용자명'@'접속 호스트 IP';
# 오브젝트 레벨단위
GRANT 권한명, 권한명 ON 데이터베이스명.오브젝트명.* TO '사용자명'@'접속 호스트 IP';

아래는 일반적인 예제입니다.

GRANT create, drop ON db1.* to 'web'@'111.111.111.111';
GRANT ALL PRIVILEGES ON *.* TO 'web'@'111.111.111.111';

권한회수

아래 명령어로 권한을 회수할 수 있습니다.

REVOKE 권한명, 권한명 ON *.* FROM '사용자명'@'접속 호스트 IP';

Replication

Replication 방식

Mysql 의 복제 방식은 기본적으로 동기, 비동기로 나뉩니다. 동기 방식은 Master 노드에 데이터 변경이 발생할 경우 Slave 노드까지 동시에 적용되는 것을 보장하는 방식이며, 비동기 방식은 Master 노드의 변경과 Slave 노드로의 적용이 동시에 이루어지지 않습니다. 따라서 동기 방식의 경우 Master 노드에 장애가 생겨도 Slave 노드를 통해 서비스를 이어갈 수 있지만, 비동기의 겨우 데이터 정합성에 문제가 발생할 수 있습니다.

Mysql 의 Replication 은 기본적으로 비동기로 동작합니다. Single point of failure 를 없애려는 cluster 와 달리 단순히 데이터만 복제하며, 모든 데이터가 동기화되는 cluster 와 달리 동기화가 비동기적으로 발생합니다.

cluster 와 비교하여 동기화도 보장하지 않고 쿼리를 분산할 수도 없지만, 값을 변경하기 위해서 클러스터 군을 이루는 다른 서버들도 값이 변경되었는지 확인해야하는 cluster 보다 빠르게 데이터를 변경할 수 있어 쿼리 성능이 빠르므로, 실시간 동기화가 필요없는 경우 cluster 대신 replication 을 사용합니다.

따라서, 쿼리의 중요도를 판단하여 매우 중용한 쿼리는 Master 노드로, 이외의 읽기 쿼리는 Slave 노드로 분기시켜야 합니다. (보통 Master 로 쓰기 & 읽기, Slave 로 읽기 작업을 수행합니다.)

동작방식

Master 노등에서 변경되는 데이터에 대한 이력을 Binary log 에 기록하면 Replication Master Thread 가 비동기적으로 이를 읽어서 slave 에 전송하고 Relay log 에 기록합니다.

Binary log, Relay log

데이터 복구를 목적으로 Create, Drop 등의 DDL 과 Insert, Update 등의 DML 이벤트를 저장합니다. (일반적인 show, select 문은 저장되지 않습니다.) 아카이브된 데이터가 있고 아카이브 된 다음에 들어온 이벤트를 기록한 Binary log 가 있으면 원하는 시점으로 데이터를 복구할 수 있습니다. 데이터베이스를 변경하는 모든 이벤트가 저장되어 있으므로 이를 Slave 에서 다시 실행하는 것만으로도 복사된 데이터베이스가 만들어집니다.

바이너리 로그를 활성화하면 로그를 제어하고 변경된 이벤트를 작성해야 하므로 성능이 약간 저하됩니다. 또한, replication 을 사용하면 Master 노드에서 로그를 읽어가므로 디스크 I/O 도 발생하게 됩니다.

my.cnf 에서 활성화할 수 있습니다.

[mysqld]
log-bin=/data dir/base_name

mysqlbinlog 프로그램으로 로그를 확인할 수 있습니다.

Relay log 는 Slave 에 저장되며 Binary log 와 내용, 포맷 모두 동일합니다.

Cluster

MySQL Cluster 는 기존의 MySQL Replication 의 Master & Slave 의 단점을 보완하고 보다 안전한 시스템 구현이 가능하게 해줍니다. 대체로 Cluster 로 묶여진 서버는 Multi Master 서버로 활용되어 어느 서버에서 데이터가 입력, 수정, 삭제 되더라도 다른 데이터 서버로의 갱신이 신속히 이루어집니다.

또한 Replication 에서와는 다르게 장애 발생시, 장애 포인트를 인지하여 스스로 복원하는 기능을 제공하고 있어 무정지 DB시스템을 원하는 고객에게 권장할만 합니다. 다량의 접속이 이루어져 DB 서버가 다운되어도 또 다른 서버로 서비스를 유지하기 위한 시스템으로 보면 됩니다.

트랜잭션

autocommit

여러 줄의 쿼리에서 rollback 혹은 commit 연산을 만나면 하나의 트랜잭션이 종료됩니다. MySQL 의 기본 스토리지 엔진은 InnoDB 로, 기본적으로 autocommit (줄 단위 커밋) 모드가 켜져있는 상태이므로 트랜잭션이 지원되지 않습니다. 트랜잭션을 지원받기 위해서는 autocommit 모드를 false 로 변경해야합니다.

show variables like '%commit%'; // commit 모드 확인하기 
set @@autocommit=0;               // autocommit모드 false로 변경
show variables like '%commit%';

위 명령을 통해 세션단에서 autocommit 모드를 조작할 수 있고, DB 의 기본세팅을 변경하기 위해서는 config 파일을 작성해야합니다.

[mysqld]
autocommit=0

DDL

DDL rollback

DDL 은 트랜잭션 안에서 실행되더라도 롤백되지 않습니다. 롤백 대상이 아니므로 항상 autocommit 됩니다. (https://dev.mysql.com/doc/refman/5.7/en/cannot-roll-back.html)

Todo. Online DDL

예전의 DDL 의 경우, 새롭게 정의된 스키마로 빈 테이블을 작성한 후에 데이터를 복사하고, 기존의 테이블과 교체하는 형식으로 처리되었습니다. 이 과정에서는 해당 리소스에 대한 read 는 가능하지만 write 는 할 수 없었습니다.

mysql 5.1 의 InnoDB plugin 과 5.5 에는 Fast index creation 이라는 기능이 추가되어, 인덱스를 추가할 때 새로운 정의의 테이블을 작성하고 데이터를 완전히 복사하는 대신 인덱스만을 작성할 수 있게 되었습니다. 즉, Secondary index 에 대한 CREATE INDEX 나 DROP INDEX 가 수행되도록 최적화되어 인덱스 작성에 걸리던 시간은 짧아졌지만, 마찬가지로 리소스에 대한 변경은 불가능했습니다.

5.6 에서는 온라인 DDL 이 추가되어, 온라인에서 테이블 정의의 변경이 실행 가능한 경우 데이터를 복사하는 중에서도 참조 뿐 아니라 변경도 가능해졌습니다. 단, ALTER TABLE 의 시작과 종료시점에는 테이블 정의의 정합성을 위해 exclusive 처리를 하여 row lock 이 필요한 트랜잭션과는 동시에 실행할 수 없습니다.

Lock

아래에서는 실제 lock 은 리소스(row, table 등)에 발생하고, 해당 리소스에 접근한 트랜잭션은 lock 을 취득했다고 표현합니다.

Shared lock (LS, Read lock)

읽기 잠금으로, 하나의 리소스에 대해 동시에 읽을 수는 있지만 변경은 불가능하게 하는 lock 입니다. 하나의 트랜잭션에서 리소스를 읽으려고 할 때, 다른 트랜잭션에서의 shared lock 은 허용되지만 exclusive lock 은 불가능합니다. 즉, 리소스에 대해 shared lock 끼리는 동시에 접근할 수 있습니다.

Exclusive lock (LX, Wirte lock)

쓰기 잠금으로, 하나의 트랜잭션에서 데이터를 변경할 때, 트랜잭션이 완료되기 전까지는 해당 테이블을 읽거나 쓰지못하게 합니다.

한 트랜잭션이 exclusive lock 을 취득하면 다른 트랜잭션은 shared lock 을 취득할 수 없습니다. 한 트랜잭션이 exclusive lock 을 취득하면 해당 테이블에 대해 다른 트랜잭션은 exclusive lock 을 취득할 수 없습니다.

Metadata lock (이슈)

사용중인 오브젝트(table, trigger, event 등) 접근과 데이터 일관성을 유지하기 위해 metadata lock 을 사용합니다. 트랜잭션이 유지되는 동안에 DDL 작업이 들어오면 락이 걸리고, DDL 작업이 대기하는 동안 신규로 유입된 DML 쿼리 역시 대기상태로 빠집니다. 이 때 대기중인 쿼리들은 "Waiting for table metadata lock" state 가 됩니다.

즉, autocommit 이 off 일 때는 어떠한 쿼리가 실행되든지간에 트랜잭션이 시작되고 commit 혹은 rollback 이 되기 전에는 사용된 모든 오브젝트에 metadata lock 이 설정되므로 DDL 을 실행할 수 없습니다. 따라서 항상 명시적으로 commit/rollback 을 작성하여 트랜잭션을 종료해주어야 합니다.

빈도가 높은 오브젝트일수록 운영 시간대에 변경하기 어렵지만 원한다면 metadata lock 을 잡는 모든 세션을 종료한 후에 적용할 수 있습니다.

lock_wait_timeout 파라미터를 별도로 설정하여 설정된 시간만 대기하고 DDL 작업을 취소할 수도 있습니다.

https://suhwan.dev/2019/06/09/transaction-isolation-level-and-lock/

sql_mode=only_full_group_by

Reference

• https://myinfrabox.tistory.com/38?category=804723
• https://sysdocu.tistory.com/970