Jins' Dev Inside

22 년 기준 Amazon Linux2 는 CentOS 기반으로 되어있으며, 기본적으로 Yum Repository 에는 MySQL 서버의 패키지 경로가 존재하지 않는다.

따라서 먼저 Amazon Linux2 서버 위에 Yum Repository 를 추가해준다.

sudo yum install https://dev.mysql.com/get/mysql80-community-release-el7-5.noarch.rpm

Yum Repository 가 등록되었다면 다음 명령어를 통해 MySQL Community Server 를 구축해준다.

sudo amazon-linux-extras install epel -y
sudo yum -y install mysql-community-server

다음으로는 EC2 위에서 MySQL 서비스를 실행시켜준다.

sudo systemctl enable --now mysqld

서비스의 실행 상태는 다음 명령어로 확인할 수 있다.

systemctl status mysqld

MySQL 서버는 초기에 Root 계정만 존재하며, 임시 비밀번호가 발급된 상황이다. 

초기에 해야할 일은 임시 비밀번호를 대체하고, 실제 데이터베이스에 접근할 사용자 계정을 만드는 일이다.

sudo grep 'temporary password' /var/log/mysqld.log

명령어를 치게 되면 다음과 같이 임시 비밀번호가 튀어나온다.

임시 비밀 번호를 이용해서 MySQL 서버에 접속한다.

mysql -uroot -p

위와 같이 명령어를 입력하면 localhost 에 루트 계정으로 접속을 시도하게 된다. 비밀번호 창이 나오면 임시 비밀번호를 입력해준다.

초기에 MySQL 에 들어가면 가장 먼저 해야할 일은 루트 비밀번호를 변경하는 것이다. 이를 하지 않고는 사실상 아무 작업도 하지 못한다.

ALTER user 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '변경할 비밀번호';

다음 명령어를 입력해서 권한을 반영해준다.

FLUSH PRIVILEGES;

- 슈퍼 유저 만들기

Root 계정으로 MySQL 에 접근하는 건 안전하지 못하며, 필요한 권한만 가진 데이터베이스 사용자 계정을 만드는 것이 좋다.

다음 명령어를 통해 사용자 계정을 만든다.

create user '<계정 이름>'@'%' identified by '<비밀번호>';

원래는 가져야하는 권한만 정의해서 사용자 계정을 만들어야하지만, 이번 포스팅에서는 슈퍼 유저를 만들어보도록 한다.

다음 명령어를 사용하면 모든 데이터베이스에 대한 모든 권한을 사용자가 어디에서 접근하던지 부여할 수 있다.

GRANT ALL PRIVILEGES ON *.* to '<계정 이름>'@'%';

마찬가지로 설정 이후에는 다음 명령어로 권한을 반영해주도록 한다.

FLUSH PRIVILEGES;

참고 : 

https://techviewleo.com/how-to-install-mysql-8-on-amazon-linux-2/

MySQL 의 빈로그 혹은 바이너리 로그는 MySQL 서버 인스턴스의 데이터 변경사항들에 대한 정보를 포함하는 로그 파일의 세트이다.

여기에는 에러코드, 바이너리 로그 자체에 대한 메타데이터 등 다양한 데이터가 같이 포함되게 된다.

기본적으로 Transaction Commit 시에 기록되어지며, 데이터 변경 순서를 보장한다는 특징이 있다.

주로 복제(Replication) 및 복구(Recovery)를 목적으로 binary log 가 사용되어지며, 복제 시에는 Secondary Node 가 Primary Node 로부터 binlog 데이터를 전달받아서 로깅하게 된다. (그리고 전달받아 로깅하는 이 로그를 릴레이 로그 라고 한다)

MySQL 에서 제공하는 바이너리 로그에는 3가지 종류가 있다.

(1) Statement-based logging

Insert, Update, Delete 에 대한 SQL 문들이 포함된다. Statement base 로 복제를 수행 시 Statement-Based Replication (SBR) 이라고 한다.

이 방식에서는 로그 파일에 적은 데이터가 기록되며, 로그 파일에 필요한 저장 공간이 줄어드는 장점이 있다.

백업에 대한 복구는 Replay 처럼 수행되며 빠르게 복원이 수행될 수 있다.

다만 로그 기반으로 복원 시 제약이 조금 있는데, 예를들어 RAND(), LOAD_FILE, UUID() 등과 같이 Deterministic 하지 않은 동작에 대해서는 정확히 복제가 안된다고 보면 된다. (당연히 RAND() 를 복원해서 나온 두 번의 결과가 같을리는 만무하다!)

(2) Row-based logging

이 방식은 각 행에 대한 변화를 기록한다. Row-based logging 을 이용해서 Primary → Secondary 로 복제를 수행할 수 있고, 이를 Row-Based Replication(RBR)이라 한다

RBL 의 경우 각 행의 변경 사항을 이진 로그에 기록하므로 로그 파일의 크기가 매우 빠르게 증가할 수 있으며 지연이 발생할 수도 있다.

또한, 임시 테이블의 경우 RBL 기반으로 복제되지 않으므로 임시 테이블 관련 구문은 Statement base 로 기록되어야 한다.

(3) 혼합형

MySQL 5.1 이상부터 Row-based logging 과 함께 혼합형태가 지원되어진다. 기본적으로는 Statement based logging 을 사용하지만, 스토리지 엔진 및 특정 명령문에 따라 로그가 자동으로 Row based logging 으로 기록되어진다.

물론 사용하는 개발자 입장에서는 큰 차이가 느껴지지는 않는다.

mysqlbinlog 유틸리티를 사용하면 바이너리 로그에 대한 내용을 쉽게 조회해볼 수 있다.

또 한가지.. 바이너리 로그와 헷갈리는 개념이 MySQL 의 Transaction log (트랜잭션 로그) 라는 개념이다.

binlog 는 데이터베이스에 기록 및 업데이트한 이력(History)을 로깅하는 개념이고, 이를 기반으로 상태 복원에 대해 핵심적으로 사용될 수 있는 Incremental Backup 을 지원한다.

반면 transaction log(redo log) 는 트랜잭션에 대한 처리, 롤백, Crash Recovery, Point In Time Recovery 등을 위한 용도로 사용되어지게 된다. 

MySQL 에서 트랜잭션은 InnoDB 스토리지 엔진만 사용하므로, MyISAM과 같은 스토리지 엔진에서는 binlog 만 사용하게 된다.

Point In Time Recovery 가 트랜잭션 로그를 통해서 지원되므로, 어떤 데이터베이스 솔루션 등을 쓴다라고 할 때에는 Transaction Log 에 대한 지원을 살펴볼 필요가 있겠다.

RDB 를 포스팅할 때 가장 먼저 정리해야 하는 부분인데 다소 순서가 밀렸다... 

이번 포스팅에서는 Database 를 다루는 데 있어서 가장 기본적인 Table 의 Key 에 대해 정리한다.

Database 에서 Key 의 의미는 테이블에서 각 데이터를 분류하는 기준의 역할을 한다.

MySQL 에서는 테이블의 데이터 들을 구분하기 위한 키의 종류로 다음과 같은 종류들을 사용한다.

(1) Key(Index)

 가장 일반적인 Key 는 DB 의 Index 와 동의어이다. Database 는 데이터의 검색을 위해 Index 를 색인으로 사용하므로 중요한 역할을 한다.

 중복을 허용하며 NULL 등의 허용도 가능하지만 NULL 이 허용될 경우... 색인에 있어 비약적인 성능 저하를 가져오므로 일반적으로 Nullable 한 데이터의 경우 Indexing 하지 않는다.

 단순히 Key, 즉 Index 로만 지정할 경우에는 별도에 제약조건(Constraint)을 가지지 않기 때문에 테이블 내에 데이터에 대해 엄격한 정합성이 요구될 경우에는 적합하지 않다.

(2) Primary Key

 일반적인 Key 는 Index 를 지칭하지만, 일반적으로 DB 설계를 할 때 Key 라고 하면 보통 PK 를 의미한다.

 NULL 을 허용하지 않고, Unique 성질을 지니므로 NOT NULL & UNIQUE 옵션이 포함되며 테이블 당 단 하나의 정의만 가질 수 있다.

 즉, PK 로 단 하나의 칼럼이 지정되어 있다면 해당 칼럼의 데이터는 Table 내에서 유일성이 보장되며 

 여러 개가 PK 로 지정되어 있다면 해당 Key 들의 조합에 대해 유일성이 보장된다.

 따라서 PK 는 같은 PK 를 갖는 행을 테이블 내에서 고유하게 만든다.

 기본적으로 Index 성질 역시 보장되기 때문에 검색 시 색인의 Key 가 되며, Constraint 를 갖기 때문에 다른 테이블과 JOIN 을 할 때 기준 값으로 사용된다.

 RDB 의 특징적인 데이터 정합성의 보장과 Key 값의 성질을 갖기 때문에 일반적인 설계에서도 가장 선호되는 Key 타입이다.

(3) Unique Key

 Unique Key 는 Uniqueness 를 지닌 Index를 말하며, Unique Index 라 부르기도 한다.

 PK 와 마찬가지로 중복성이 허용되지 않지만 NULL 에 대한 허용이 가능하다.

 테이블 당 여러개를 가질 수 있다.

(4) Foreign Key

 Foreign Key 란 JOIN 등으로 다른 DB 와의 Relation 을 맺는 경우, 다른 테이블의 PK를 참조하는 Column 을 FK 라고 한다.

 여기서 Foreign Key Relation 을 맺는 다는 의미는 논리적 뿐 아니라 물리적으로 다른 테이블과의 연결까지 맺는 경우를 말하며, 이 때 FK 는 제약조건(Constraint)으로의 역할을 한다.

 Foreign Key Restrict 옵션을 줄 수 있고 다음과 같은 옵션들이 있다.

  - RESTRICT : FK 관계를 맺고 있는 데이터 ROW 의 변경(UPDATE) 또는 삭제(DELETE) 를 막는다.

  - CASCADE : FK 관계를 맺을 때 가장 흔하게 접할 수 있는 옵션으로, FK 와 관계를 맺은 상대 PK 를 직접 연결해서 DELETE 또는 UPDATE 시, 상대 Key 값도 삭제 또는 갱신시킨다.

  이 때에는 Trigger 가 발생하지 않으니 주의하자.

  - SET NULL : 논리적 관계상 부모의 테이블, 즉 참조되는 테이블의 값이 변경 또는 삭제될 때 자식 테이블의 값을 NULL 로 만든다. UPDATE 쿼리로 인해 SET NULL 이 허용된 경우에만 동작한다.

  - NO ACTION : RESTRICT 옵션과 동작이 같지만, 체크를 뒤로 미룬다.

  - SET DEFAULT : 변경 또는 삭제 시에 값을 DEFAULT 값으로 세팅한다.

주로 PK 가 설계 시 많이 이용되지만, 정합성이 중요하지 않은 경우 Index 만 이용해서 테이블을 설계하기도 한다.

UNIQUE Index 는 주로 복잡한 테이블에서 부분적인 정합성을 살리기 위해 많이 이용된다.

그리고 FK 의 물리적 연결은 서버의 안정성을 위해 지양하는 편이 많다.

상황에 알맞게 적합한 Key 를 잡아 설계하는 것이 최선의 튜닝 기법이라고 생각된다.

 Char형의 뒤에는 캐릭터 형의 길이를 명시하기 위해 (숫자) 와 같은 형식으로 값이 붙게 되는데 Int형 뒤에도 붙일 수 있다. 

하지만 Int형의 괄호는 숫자 개수의 제약을 의미하는 것이 아니라 Zerofill을 위한 것이다. 

예를 들어 Int(5)는 5자리 내 숫자는 모두 0으로 채워준다는 뜻이다. 단 ORACLE에서는 실제 자리 수를 표현하는데 사용된다.

Data Replication은 데이터를 물리적으로 다른 서버 공간에 복제하는 일이다. 

이를 잘 이용하면 부하 분산 및 고가용성, 버전 테스트 등 멋지게 사용할 수 있는 방안이 많아진다. 

일반적으로 하나의 Master와 여러 개의 Slave로 이루어진 구조에서 Read Only인 Slave 노드들에 Write 가능한 Master의 데이터가 업데이트되면 동기화시키는 방향으로 복제가 이루어진다.

Database Replication 은 기본적으로 비동기(Asynchronous)로 이루어진다.

Replication 은 대게 Writable 한 Master Node 에서 Readonly인 Slave로 이루어진다.

Replication 으로 인한 이점들에는 다음과 같은 것들이 있다.

- Scale out solution : Replication 을 통해 Read 요청에 대한 분산을 여러 Slave 로 나눔으로써 병목현상을 해결할 수 있다. 

- Data security : 데이터가 Slave로 복제됨에 따라서 마스터 서비스에 대한 영향 없이 데이터에 대한 백업이 가능하다.

- Analytics : 서비스에 영향을 미치지 않고 데이터 분석이 용이하다.

- Long-distance data distribution : Data를 여러 지리적 Location 혹은 Local에 Copy 를 만들 수 있다. 이는 자연적 재해나 지리적 장애에 대한 대응책이 될 수 있다.

MySQL 의 Replication은 로그 기반으로 비동기적으로 데이터를 복제한다. 

마스터에서는 데이터가 변경되면 Binary Log라는 곳에 이력을 기록하는데, 실행된 SQL을 그대로 기록하거나(Statement-Based), 변경된 행을 Base64로 인코딩하여 기록하거나(Row-Based), 적절히 혼합한형태(Mixed Type)로 동기화를 수행한다. 

(1) Master에서 데이터 변경이 일어나면 자신의 데이터베이스에 반영한다.

(2) Master에서 변경된 이력을 Binary Log에 기록한 뒤 관련 이벤트를 날린다.

(3) Slave IO_THREAD에서 Master 이벤트를 감지하고 Master Binary Log의 Relay Log에 기록한다.

(4) Slave SQL_THREAD는 Relay Log를 읽고 자신의 DB에 기록한다.

마스터에서는 여러 세션에서 데이터 변경처리가 가능하지만 Slave에서는 오직 SQL Thread에서만 데이터 변경처리가 이루어질 수 있기 때문에 Master의 데이터 변경 트래픽이 과도할 경우 동기화 시간 차이가 크게 날 수도 있다.

동일한 Database 소스를 여러 군데에 분산시키는 점은 개발자가 API 를 구현할 때에도 신경써주어야 하는 부분이다.

WAS 레벨에서 어떤 DB를 바라볼지에 대한 문제는 보통 Web Framework 레벨에서 지원해주는 경우가 많다. 가장 간단한 방법으로는 서로 다른 커넥션 풀을 만들어 쿼리를 날릴때마다 직접 분기해주는 방법이지만 이는 비효율적이며 비생산적이다.

먼저 Spring을 사용하면 @Transactional(readOnly=true) 라는 어노테이션에서 readOnly 옵션을 이용해서 트랜잭션 설정 하나로 서로 다른 데이터 소스로 연결시킬 수 있다. 

또한 Spring의 AbstractRoutingDataSource 클래스는 여러 개의 DataSource를 하나로 묶고 자동으로 분기처리해주는 Spring 기본클래스이다. 이를 이용해서 @Transactional 과 잘 연계하면 쉽게 분기처리가 가능하다.

참고로 Spring의 @Transactional 처리는 TransactionManager 선별 -> DataSource에서 Connection 획득 -> Transaction 동기화 순으로 일어나기 때문에 커넥션을 동기화 이후에 얻는 Lazy 한 방식을 사용해야 함을 명심한다.

본 포스팅에서 언급한 내용은 Master-Slave Replication 이지만, 경우에 따라 Master-Master Replication 을 구조로 구성하기도 한다.

Master-Master replication 구조를 고려할 때에는 Master 간 Conflict 및 데이터베이스에서의 ACID 성질의 유지가 힘들기 때문에 신경쓸 부분이 조금 더 많다. 

그렇기 때문에 Master-Master Replication 의 경우 Active Directory / LDAP 과 같은 Directory Service Server 나 Data 의 Conflict 우려가 적고 Accessibility 가 중요한 서비스의 경우 고려된다.

(참조 : https://stackoverflow.com/questions/3736969/master-master-vs-master-slave-database-architecture)

클라우드 환경이 도입되면서 Multi A-Z 에서의 Replication 전략도 눈여겨 봐야하는데, 보통 하나의 Master 를 한 Availability Zone 에 두고 Read-only Slave 들을 같은 Zone 및 다른 AZ 에 배치시켜두는 형태를 취한다. AWS 에서는 이를 Read-Replica 라는 서비스로 제공한다.

Database 는 서비스 운영 시 대게 큰 부하(Load)를 감당하게 되는 부분이다. 

특히 RDB를 통해 운영할 경우 데이터 정합성의 오류는 RDB를 기반으로 만들어진 서비스에 악영향을 미칠 가능성이 높아진다.

Transaction Isolation Level 이란, RDB가 Data Processing 에 있어서 ACID 원칙을 지키기 위해 정의된 다수의 Transaction 을 처리하기 위한 규약이다.

가령 MySQL에서 전체 데이터를 Scan 하는 쿼리 실행 시 서비스에는 큰 부하가 발생한다. 설계 시에 이런 가능성은 최대한 제거하는 것이 맞지만 피하지 못했을 경우를 생각해보자.

만약 Transaction Isolation Level 의 미설정으로 InnoDB의 Transaction Isolation Level 이 Repeatable-Read(Default) 상태에서 Select가 들어간 참조 쿼리 실행 시 데이터 변경 작업이 대기 상태에 빠지게 된다. 

특히나 참조할 데이터가 많을 경우에는 시간 지연으로 인해 wait timeout이 초과되거나, Deadlock 현상에 빠질 수도 있다.

그 외에도 Master 데이터에 대한 Write Transaction 이 진행 중인 상태에서 유저의 데이터 접근이 일어나거나, DB내의 공통 자원에 대한 접근이 발생할 때 MySQL 은 트랜잭션의 관리를 위한 몇가지 방안을 제시한다.

MySQL 의 Transaction Isolation Level의 종류

- Read uncommitted : 다른 트랜잭션이 Commit 전상태를 볼 수 있으며 Binary Log가 자동으로 Row Based로 기록.

다른 트랜잭션이 진행중인 작업 상태를 Read 를 통해 읽어올 수 있다. 이렇게 커밋되지않은 신뢰할 수 없는 데이터를 로드하는 것을 Dirty Read 라 한다. 

따라서 한 트랜잭션이 진행 중일 때에 여러번의 Select 쿼리가 서로 다른 결과(Phantom-read)를 발생시킨다. 

(Non-repeatable Read)

- Read committed : Commit 된 내용을 읽을 수 있으며 트랜잭션이 다르더라도 다른 트랜잭션이 Commit시 Read 가능. 자동으로 Row based로 기록. 

Commit 하지않은 내용은 읽을 수 없기 때문에 Dirty Read 는 발생하지 않는다. 대신 한 트랜잭션 내에서 Select 쿼리문의 결과가 다를 수 있으므로 Non-repeatable Read 가 발생한다.

- Repeatable Read : 현재 버전의 Snapshot을 만들고 데이터를 조회한다. 일관성을 보장하고 데이터를 읽기 위해서는 트랜잭션을 재시작 해야 한다. 

Read committed 와 마찬가지로 Commit 되지않은 다른 트랜잭션의 결과값이 보이지 않는다. (Non-Dirty Read) 

대신 한 트랜잭션 내에서는 다른 트랜잭션의 작업 여부에 상관없이 Select 쿼리문의 결과가 같은 데이터를 조회한다. (Non-repeatable read 가 발생하지 않는다.)

- Serializable : 가장 높은 Isolation level로 트랜잭션 완료 이전까지 참조하는 모든 데이터에 Shared Lock이 걸린다. 변경 수정 및 입력이 불가능하다.

가장 엄격한 레벨로, 유일하게 위에 언급된 3가지 문제(Dirty Read, Non-repeatable Read, Phantom Read)를 커버가능하다.

하지만 성능에 있어 가장 부하가 크게 작용한다.

가령 위의 Select 참조 쿼리 문제에 대한 설정 해결법으로 기본 Isolation level을 Repeatable Read -> Read committed 로 바꾸고 InnoDB lock wait timeout 을 늘려주는 방안이 있다.

이유는 Repeatable Read 에서 Lock이 발생하는 이유는 해당 옵션이 현재 Select 버전을 보장하기 위해 Snapshot을 이용하고 이 때문에 Lock이 발생하기 때문이다.

Transaction Isolation Level 은 실무자 입장에서 빈번하게 다룰 내용은 아니지만, DB를 이해하고 있다면, 아키텍처를 이해하고 있는 위치에 있다면 반드시 알아두어야 할 내용이다.

DB가 복잡해지다보면, 모든 스키마를 외우고 있을 수는 없다. 

그래서 테이블들의 스키마를 조회하게되는 경우가 많아지는데, 그 중에서도 실무에서 자주 사용하는 테이블에 적용되어있는 Key 특성을 조회하는 쿼리들을 정리해보았다.

(1) SHOW INDEXES IN "table이름"

 : 해당 DB 내에서 사용해야하며, 테이블에 정의되어있는 Key 가 걸려있는 Column과, Cardinality 등까지 자세한 정보를 제공해준다.

(2) SELECT * FROM Information_schema.table_constraints

 : DB를 관리할 수 있다면 Information Schema 를 조회하는 방법은 알아두는게 좋다. 굳이 포스팅에서 다루는 Key 조회 이외에도 Information Schema 는 많은 정보를 담고 있다.

다음과 같이 테이블에 대한 정보를 얻어낼 수 있다.

SELECT * FROM Information_schema.table_constraints WHERE table_schema="db이름”;

SELECT * FROM Information_schema.table_constraints WHERE table_schema="db이름” AND table_name="table이름";

(3) SHOW CREATE TABLE "table이름";

 : 이 쿼리가 사실 만능이라고 여겨진다. (강조) 세부적인 정보를 도출하기는 힘들지만, 가장 쉽게 자주 필요한 정보들을 조회할 수 있다.

이 쿼리는 테이블 생성 쿼리를 그대로 보여준다.

개인적으로 가장 많이 사용하는 쿼리는 3번의 SHOW CREATE TABLE 구문이지만, 가장 강력한 건 Information_schema 정보를 다룰줄 아는것이다.

다양한 MySQL 의 시스템 테이블들과의 JOIN 연산을 통해 스키마 정보를 디테일하게 추출해낼 수 있으며, 이는 좀 더 자세하게 다룰 예정이다.

실무에서도 자주 사용하는 MySQL 쿼리문들을 정리해보았다. 많이 쓰는 기본 쿼리문 사용법들이므로, 필수적으로 알아두어야 한다.

응용만 한다면 어느정도 복잡한 쿼리문도 손쉽게 만들어낼 수 있다.

- 접속 방법

 : mysql -u root -p (dbname)

- 비밀번호 변경

 : mysqladmin -u root password 새로운 비밀번호

- 테이블의 생성

 : create table 테이블(col int);

- 구조 보기

 : desc 테이블 / explain 테이블

- 이름 변경

 : rename table A to B

- 삭제

 : drop table 테이블

- 레코드 삽입

 : Insert into table values(v1, v2) / Insert into table(col1, col2) values(v1, v2);

- 조회

 : select * from table A

> AS : 칼럼의 이름을 달리 명명해서 출력. (ex) Col1 as 'name'

> Desc : 내림차순, Asc : 오름차순 (ORDER BY)

> LIMIT 10 : 0~10 까지 레코드 수 제한. / LIMIT 100, 10 : 100~110까지 레코드 범위

- 수정

 : Update 테이블 set col1 = 칼럼1 where 조건

- 삭제

 : Delete from 테이블 where 조건

- 칼럼 추가

 : Alter table 테이블명 add col3 varchar(255) not null.

- 칼럼 삭제

 : Alter table 테이블명 drop col3

- 칼럼 수정

 : Alter table 테이블명 modify col3 char(50) not null.

- In : 원하는 필드값만을 선택 추출하는데 사용되는 그룹 조건문

- 조인

(1)   Inner join

 : Select * from tableA inner join tableB on tableA.col1 = tableB.col1

 => tableA의 col1과 tableB의 col1이 일치하는 데이터만을 출력. ON 절의 조건이 일치하는 조인테이블의 결과만을 출력한다.

(2)   Outer join

 : Select * from tableA left outer join tableB on tableA.col1 = tableB.col1

 => tableA.col1이 존재하나 tableB.col1이 존재하지 않으면 tableB.col1 = NULL인 상태로 출력. 

 조인하는 테이블의 ON 절 조건 중 한쪽의 모든 데이터를 가져옴(LEFT JOIN , RIGHT JOIN) 양쪽(FULL JOIN)

- 내장함수 Benchmark

 : Select Benchmark(반복횟수, 실행쿼리)

(ex) Select Benchmark(100, (“select * from table”)); => 해당 쿼리를 100번 반복한 벤치마크 결과를 출력.

- DISTINCT

 : 주로 UNIQUE한 COLUMN이나 TUPLE을 조회할 때 사용되는 키워드. 칼럼을 DISTINCT 를 이용하여 조회한다면 중복을 제거한 값들을 바로 얻을 수 있다. 단 이 때, 여러 개의 칼럼을 지정한다면 칼럼의 조합이 중복되는 것을 제외한다. DISTINCT는 함수처럼 WHERE이 아닌 HAVING 조건식에도 사용이 가능하다. 

(ex) Select DISTINCT email from table;

(ex) SELECT class FROM courses GROUP BY(class) HAVING count(distinct student) >= 5;

- GROUP BY

 : 데이터를 그루핑해서 결과를 가져오는 경우 사용. 내부적으로 중복값을 배제한채 정렬된 결과를 가져온다. 주로 HAVING과 같이 사용되며 그룹으로 묶어서 자체 정렬한다. 좀 더 정확히는 그룹의 대표값을 정렬해서 가져온다. 그렇기 때문에 모든 컬럼에 대해 단순 SELECT 하는 쿼리문에는 쓰기 적절치 않으며 테이블 내에서 데이터를 가공할 때 사용하기가 좋다. 예를 들어 accountType에 따라 해당하는 accountName의 row수를 그루핑 하고 싶다면 다음 쿼리를 사용해보자. 

Select accountType, COUNT(accountName) from accounts group by(accountType);

- HAVING

 : HAVING은 GROUP BY 와 같이 쓰이는 구문으로 GROUP BY의 조건문이라 할 수 있다. 위의 쿼리에서 COUNT가 1개 이상인 내용만 쿼리를 하는데 다음처럼 사용 가능하다. 

SELECT accountType, COUNT(accountName) FROM accounts GROUP BY(accountType) HAVING COUNT(accountName)>1; 

HAVING의 시점은 GROUPING이 끝난 이후이고 WHERE 절과 다르게 HAVING 절은 통계함수를 포함할 수 있다.

HAVING은 () 를 안 싸는 것이 좋다. 버전에 따라 오작동 위험이 있는듯하다 ;;

- SubQuery 사용법

 : 복잡한 쿼리문을 만들 때 많이 사용하게 되는 구문이 서브쿼리문이다. 서브쿼리의 사용은 Nested Loop 를 돌기 때문에 사용에 주의하자.

(ex) SELECT accountInfo from accounts where accountName in (select accountName from accountNames);

위의 쿼리는 accountNames 테이블에 있는 이름에 대해서만 accountInfo를 조회하는 쿼리(Validation)

기본적인 쿼리문들을 정리해보았다. 시간이 나면 Tricky 하게 사용되는 쿼리문들도 정리해볼 생각이다.

MySQL은 조회(SELECT) 에 있어서 최고의 강점을 갖는 종류의 DB이지만, 경우에 따라 막대한 양의 Insert 를 수행해야할 때가 있다.

특히 API 에서 조회하는 DB에 배치 서버가 수시로 데이터를 갱신해주는 경우나, 막대한 양의 메시지 데이터를 처리하게 될 경우가 있는데, DB가 커질 수록, 단순 쿼리로만 작업하다가는 퍼포먼스에 있어 엄청난 디버프를 받게 된다.

대량의 데이터를 삽입하는 것을 Bulk Inserting 이라 하며, 이를 위한 다음과 같은 튜닝 기술들 정도는 숙지해두도록 하자.

(1) 여러 개의 Insert 구문 수행시 Values 리스트를 다중으로 사용하는 것이 성능을 향상시킬 수 있다. 

정말 효과 제대로 본 방법인데, 다수의 동일한 Insert query에 대해서 다음과 같이 최적화가 가능하다.

Insert into T values(a, b, c)

Insert into T values(d, e, f)

위와 같이 쿼리를 날린다고 할때, 이를 다음과 같이 바꾼다.

Insert into T values(a, b, c), (d, e, f) ...

이렇게 되면 성능이 기하급수적으로 향상되며, 이는 JDBC 혹은 클라이언트 등의 연결 설정에서 RewriteBatchedStatements=true 와 같은 속성을 주어 자동으로 최적화시킬 수 있다.

(물론 설정의 경우 실수의 우려도 있고, 개발자가 한눈에 파악하기 쉽지 않기 때문에 협업 시에는 코드 레벨에서 처리하는게 더 좋다.)

또한 이 쿼리 튜닝을 사용할 때에는 비어있지 않은 Table에 Insert 시 my.cnf 파일 내에 있는 bulk_insert_buffer_size를 변경하여 속도 개선이 가능하다. 

(수정 방법은 /etc/mysql/my.cnf 파일을 열어서 [mysqld] 항목 아래에 bulk_insert_buffer_size=256M 과 같이 설정해주면 된다.)

(2) 여러 클라이언트에서 Insert 시 Insert Delayed 를 통해 속도 개선이 가능하다. 이 구문을 이용하면 수행 응답이 큐에 적재되고 테이블이 사용되지 않을 때 삽입한다. 

실시간으로 서비스 중인 경우에 빛을 발하는 성능 향상 법이다.

(3) 파일 스트림으로부터 대량의 데이터를 삽입 시 Load Data Local Infile 구문을 이용해서 필드 구문자로 정리된 File을 MySQL DB로 Redirection 시킬 수 있다. 

각각에 대한 Insert 구문 수행보다 빠른 퍼포먼스를 보인다.

 (ex) Delimeter가 "|" 이고 라인 개행 단위로 레코드가 기록되어 있을 때,

LOAD DATA LOCAL INFILE '경로' [REPLACE | IGNORE] INTO TABLE 테이블명 FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';

단, 이 경우 5.5 이상 버전에서는 LOCAL을 꼭 붙여야 에러가 나지 않으며, 이를 이용하기 위해 설정 옵션에 [mysql] 아래에 local-infile을, [mysqld] 아래에 local-infile을 추가해주어야 한다.

(4) Index가 많이 사용된 테이블에 대량으로 Insert 시 Index를 비활성화한 후 수행하는 것이 좋다. 

매 Row 마다 계산 후 삽입하는 것보다 전체 키를 비활성화하고 데이터 입력이 종료되고 다시 키를 활성화시키는 것이 빠른 수행을 할 수 있다.

ALTER TABLE 테이블 DISABLE KEYS;

Insert 구문 수행

ALTER TABLE 테이블 ENABLE KEYS;

여기서 Key 란 INDEX 뿐 아니라 FOREIGN KEY 참조도 포함한다. 실제로 Bulk Inserting 이 중요하다면, 체크를 꺼두는 것이 성능 향상에 큰 도움이 된다.

물론 이 경우 단점은, API 로직상 에러가 발생하여 문제가 될 수 있는 데이터가 삽입될 시, 실시간으로 정합성 체크가 불가능하다. 데이터를 신뢰할 경우에만 수행해야 한다.

(5) 트랜잭션을 지원하는 테이블의 경우, Start Transaction과 Commit을, 지원하지 않는 테이블의 경우 테이블 잠금을 실행하면 성능이 향상된다. 

(이유는 버퍼 플러시가 매번 수행되지 않고 작업이 끝난 후 수행되기 때문이다.)

(6) Buffer Size 의 조절을 통해 성능 향상이 가능하다.

테이블에 Insert 시 Index가 정렬되어 들어온다는 보장이 없기 때문에 이는 B트리 구조화시 추가적인 I/O를 수반한다. 

디스크에 데이터를 읽고 쓰고 하는 부가적인 동작이 레코드가 많아질수록, Buffer 크기를 넘어설수록 많이 수반되기 때문이다. 

이를 막기 위해서 InnoDB의 경우 Buffer Size를 크게 잡는다면 이 만큼의 메모리를 추가로 캐싱용 버퍼풀을 위해 사용하는 대신 디스크 I/O의 비용을 높은 비율로 줄일 수 있다. 

잔여 메모리의 50~80% 만큼 조절하는 게 정석이며 그 이상으로 조절 시 오히려 하드디스크를 가상 메모리로 쓰기 위한 스와핑 작업이 발생하기 때문에 성능이 저하된다고 알려져있다. 

(실제로 이부분은 범위 내라면, 조절하는 만큼 성능이 향상된다. 바꾸기 위해서는 my.cnf 설정 내에서 [mysql] 하위 항목으로 innodb_buffer_pool_size=1024M 과 같이 입력하면 된다.)

버퍼 풀 메모리가 충분히 큰 양으로 할당되어 있다면 innodb는 in-memory 데이터베이스처럼 동작한다. 

Access를 위한 select 데이터 뿐 아니라, Insert 및 Update 작업에도 도움이 되는 캐싱을 하기 때문에 적절하 조정하여 사용하는 것이 핵심이다.

버퍼 풀 메모리는 내부적으로 LRU 알고리즘을 사용하는 리스트의 형태로 자세한 내용은 http://dev.mysql.com 레퍼런스를 참조한다.

 MySQL의 TIMESTAMP/DATETIME 자료형과 CURRENT_TIMESTAMP 모듈을 이용하면 손쉽게 레코드의 삽입 시간과 수정 시간을 파일 시스템처럼 관리할 수 있다.

가령 게시판을 만든다고 해보자. 게시판에 유저들이 글을 쓰는데, 등록시간과 수정시간을 기록하는 건 굉장히 중요한 서비스이다.

이 때, 당연히 사용자에게 등록시간과 수정시간을 하나하나 입력받을 수는 없는 노릇이다.

혹은 DB 에 쿼리를 날릴 때 웹에서 직접 현재 시간을 기록하게 해주어도 괜찮지만, DB 단에서 처리할 수 있는 다음과 같은 방법도 있다.

 CURRENT_TIMESTAMP는 데이터 접근 시의 당시 현재 시간을 나타내는 모듈로, 위처럼 테이블을 생성하면 기본으로 레코드는 생성시 현재 시간을 갖게 된다.(미입력시의 초기값) 

그리고 ON UPDATE 키워드에 의해 갱신 시마다 Updated 칼럼의 Value는 해당 시점의 현재 시간으로 Update 된다.

(1) Insert into blog_text(name, contents) values(‘tester’, ‘안녕하세요. Korean Developers’);

1 | "tester" | "안녕하세요. Korean Developers" | 2018-08-27 09:25:58 | 2018-08-27 09:25:58

(2) Update blog_text set `contents` = "안녕하세요." where text_id = 1;

1 | "jinsp" | "안녕하세요." | 2018-08-27 09:25:58 | 2018-08-27 09:27:51

위와 같이 DB레벨에서 좀 더 쉽고 명확하게 등록시간 / 수정시간을 관리하는 것이 가능하다.

응용하면 다양한 방법으로 Tricky 하게 사용할 수도 있다.

물론 실무에서는 배치 작업이나 자동화된 로그성 데이터들이 아닌 관리해야하는 데이터의 경우 API 단에서 처리하는 경우가 더 많다.