MySQL 스터디-1

Real MySQL 정리 (아키텍처, 트랜잭션과 잠금, 인덱스, 쿼리 작성 및 최적화)

03 아키텍처

MySQL 전체 구조

• MySQL 엔진
  - 커넥션 핸들러
  - SQL 파서, 전처리기
  - 옵티마이저
• 스토리지 엔진
  - 실제 저장된 데이터를 읽어옴

MySQL 스레딩 구조

• 포그라운드 스레드(클라이언트 스레드)
  - 클라이언트가 요청하는 쿼리 문장을 처리
  - thread_cache_size: 일정 개수의 스레드만 스레드 캐시에 존재하게 하는 파라미터
  - 데이터를 버퍼나 캐시로부터 가져옴
• 백그라운드 스레드
  - Insert buffer 병합
  - Log Thread : 로그를 디스크에 기록
  - Write thread : InnoDB 버퍼풀 데이터를 디스크에 기록
  - 데이터를 버퍼로 읽어들이는 스레드
  - 데드락 모니터링
  - 총괄 메인 스레드

메모리 할당 및 사용 구조

• 글로벌 메모리
  - 하나의 메모리 공간만 할당
  - 모든 스레드에 의해 공유
  - 버퍼 풀, 쿼리 캐시, 테이블 캐시, 로그 버퍼
• 로컬 메모리
  - 세션 메모리 영역, 클라이언트 메모리 영역
  - 공유되지 않음
  - 커넥션 버퍼, read 버퍼, 랜덤 read 버퍼, 바이너리 로그 버퍼, result 버퍼, 조인 버퍼, sort 버퍼

복제 구조

• 복제는 마스터와 슬레이브간 로그 이동을 통해 이루어짐
• 마스터 서버에서 실행되는 모든 dml과 ddl이 바이너리로그에 기록되며, binlog dump 스레드가 이것을 읽어서 슬레이브로 보내주는 역할
• 슬레이브가 5개가 연결되어있다면 마스터에서 binlog dump 스레드는 5개
• 슬레이브는 마스터로부터 받아온 변경 내역을 relay 로그에 기록하고, 이걸 재실행함으로서 마스터와 동일한 상태를 유지
• slave_io_thread와 slave_sql_thread는 슬레이브에서만 돌아가는 스레드

쿼리 캐시

1. 요청된 쿼리가 쿼리 캐시에 존재하는가?
2. 사용자가 그 결과를 볼 수 있는 권한이 있는가?
3. 트랜잭션 내에서 실행된 경우 가시 범위 내에 있는 결과인가?
4. CURRENT_DATE(), SYSDATE(), RAND() 와 같이 호출 시점에 따라 결과가 달라지는 요소가 있는가?
5. 변수가 결과에 영향을 끼치지 않는가?
6. 캐시가 만들어지고 난 이후 이 데이터가 다른 사용자에 의해 변경되지 않았는가?

InnoDB 스토리지 엔진

• 프라이머리 키에 의한 클러스터링
  - 오라클의 IOT와 비슷
• Non-locking consistent read
• 자동 데드락 감지

MySQL 로그 파일

• Error Log: 시작, 종료, 쿼리 에러,innodb 조회 명령 등
• General Log: 시간단위로 실행됐던 쿼리 내용이 모두 기록됨, 일단 요청받으면 기록하기 떄문에 에러가 발생해도 기록됨
  - 테이블로 조회 가능, log_output 파라미터로 확인 가능
• Slow Query Log: 튜닝할때 사용. long_query_Time 파라미터에 정의된것보다 느리면 기록됨
• Binary, Relay Log: 복제할때 사용
  - 둘다 내용이나 포맷은 동일.

QnA

Q. statement-based replication 방식에서는 REPEATABLE READ 을 지원, row-based replication 방식에서는 READ COMMITTED 격리 수준을 지원하는 이유는?
A. state-based 방식은 sql 문장 단위로 복제하기 때문에 시간 경과에 따른 정합성을 보장해줌, 따라서 repeatable read는 보장됨. row-based 방식에서는 (아마도) commit 된 데이터 단위로 복제를 하기 때문에 read-committed 방식을 보장할 수 있음

04 트랜잭션과 잠금

MySQL 엔진의 잠금

• GLOBAL LOCK: 데이터베이스 의 모든 테이블을 잠금
• TABLE LOCK: 명시적으로 테이블 잠금. 명시적으로 안해도 DDL 때 묵시적 잠금이 발생함
• USER LOCK: 사용자가 지정한 문자열을 잠금하고 해제함
• NAME LOCK: 객체의 이름을 변경할 때 획득하는 잠금, 원본 이름과 변결될 이름 모두 한꺼번에 잠금 설정
  - 한꺼번에 두개의 이름을 바꾸는 경우가 많은데! 아래처럼 하면 쿼리 에러 없이 바꿀 수 있음
  - MYSQL> RENAME TABLE rank TO rank_backup, rank_new TO rank;

InnoDB 잠금 종류

• Record lock: 레코드 자체만 잠금. 사실 레코드 자체가 아니라 인덱스의 레코드를 잠금, 인덱스가 없는 테이블이라도 내부적인 클러스터 인덱스를 사용해서 잠근다
• Gap lock: 레코드와 인접한 레코드 사이의 간격만 잠금, 개념적으로만 존재하는 LOCK
• Next key Lock: 위의 갭락, 레코드락을 합쳐놓은 형태
  - 바이너리 로그에 기록되는 쿼리가 슬레이브에서 실행될 때 마스터와 같은 결과를 만들어내도록 보장
  - innodb_locks_unsafe_for_binlog 파라미터가 비활성화되면 변경할 레코드에는 이 방식으로 잠금이 걸림

Auto Increment Lock

• Auto Increment 칼럼 관리를 위한 lock
• 0이면 모든 insert 문장은 자동증가락을 사용
• 1이면 insert되는 레코드 건수를 예측할 수 있는 경우에는 뮤텍스 사용, insert select 처럼 몇개가 있는지 알 수 없는 경우에는 이 auto increment lock을 사용, 이럴 경우에 대량 insert 하는 경우에 다른 커넥션에서는 insert를 하지 못하고 대기함
• 2면 자동 증가락을 걸지 않고 항상 뮤텍스를 사용, 대량 insert 해도 다른 커넥션에서 insert 가능, 동시 처리 성능 높아짐, 복제 사용시 마스터와 슬레이브에서 값이 달라질 수 있음.

MySQL 엔진의 잠금

• innodb의 잠금은 레코드가 아니라 인덱스를 잠그는것이고, 예를들어 이런식의 디비가 있다고 하면
• 실제 바뀌는 데이터는 하나지만 잠그는건 first_name이 georgi인 많은 데이터를 잠그는 것.
• 이건 Next key lock 때문에 잠기는거고, 빈로그를 사용하지 않거나 레코드 기반의 바이너리 로그를 사용하면 해결됨

05 인덱스

B-Tree 인덱스

• mysql은 대부분의 rdbms처럼 b-tree 구조를 사용
• b-tree 인덱스의 리프노드에는 레코드 주소가 저장됨
  - myIsam: 네부적인 레코드의 아이디를 저장
  - InnoDB: 프라이머리 키 자체

인덱스 키 추가

• InnoDB 이전까지는 변경된 인덱스를 즉시 BTree에 반영했지만, InnoDB부터는 아니게 됐음
• InnoDB 버퍼풀에 인덱스 리프노드가 있다면 즉시 insert 수행, 아니라면 insert buffer에 키와 레코드 주소를 임시 저장
• db 서버자원에 여유가 생기면 insert buffer merge thread가 디스크에 조금씩 머지시킴
• innodb_change_buffering 파라미터로 키를 추가하는 작업과 삭제하는 작업 중 어떤것을 지연처리할지 설정해야 함

B-Tree 인덱스로 데이터 읽기

• Index Range Scan: 인덱스 순서대로 읽는것. 결과가 인덱스에 없으면 데이터 레코드 주소로 가서 데이터를 읽어 오는데, 한건에 랜덤I/O가 한번 일어나서, 비용이 많이 발생함. 인덱스를 통해 읽어야 할 데이터 레코드가 20~25%를 넘으면 테이블을 직접 접근하는게 효율적
• Index Full Scan: 은 쿼리가 인덱스에 명시된 칼럼만으로 조건을 처리할 수 있는 경우에 사용됩니다. (데이터 레코드에 가지 않을 때에만)
• Loose Index Scan: 오라클에서의 index skip scan과 비슷함

클러스터링 인덱스

• PK값에 따라 테이블의 레코드가 정렬되어 저장된 경우에만 클러스터링 인덱스, 클러스터링 테이블이라 한다.
• 키값에 대한 의존도가 높아서 pk를 잘 잡아야 한다
• PK가 없는 InnoDB 테이블은 어떻게 저장될까
  - not null 옵션의 unique 인덱스 중 첫번째 인덱스를 클러스터 키로 선택
  - 자동으로 유니크한 값을 가지도록 증가되는 칼럼을 내부적으로 추가한 후 클러스터키로 선택

클러스터링 인덱스 주의사항

• 보조인덱스가 PK를 가지고 있고, 그값으로 클러스터 테이블을 검색한다.
• PK가 크다면 모든 인덱스의 크기가 커진다.
• 빠르기 떄문에 auto-increment 말고 업무적인 column을 사용하길 권하지만 PK가 없는 경우는 auto increment를 사용해서라도 무조건 정의하는것이 좋다.

07 쿼리 작성 및 최적화

쿼리와 관련한 시스템 설정

• strict_all_tables: mysql에서는 저장하려는 값의 길이가 컬럼 길이보다 긴 경우에도 잘라버리고 저장하기 때문에 에러가 나지 않는데, 시스템 변수에 strict_all_tables 를 설정하면 정해진 컬럼 길이보다 큰 값을 저장할 때 에러가 발생하고 쿼리 실행이 중지됨
• strict_Trans_tabels: 컬럼 타입과 호환되지 않을 때 최대한 바꿔서 저장하려고 하는데, 이 값을 설정하면 데이터 타입의 변환을 강제하지 않고 에러를 발생시킴
• ansi_quotes: 홑따옴표만 문자열 값 표기로 사용하고, 쌍따옴표는 테이블명같은 identifier 를 표기할 때만 사용
• only_full_group_by: 오라클처럼, groupby 에 있는 컬럼만 having절에 사용할 수 있께 함
• pipe_As_concat: || 연산자가 or 의미지만 이걸 사용하면 오라클처럼 concat이 됨
• pad_char_to_full_length: mysql에서 char도 varchar처럼 공백을 짤라서 반환하지만, 이 설정을 하면 뒤쪽의 공백이 제거되지 않고 반환됨
• no_backslash_escapes: /가 escape 문자로 사용가능
• ignore_space: 추가하면 프로시저나 함수명 괄호 사이의 공백은 무시됨. 아니면 함수명, 프로시저 괄호 사이에 있는 공백까지도 함수의 이름으로 간주됨
• ansi: 위의 값을 조합해서 mysql 서버가 최대한 sql 표준에 맞게 동작하게 만들어줌

날짜 표기

oracle처럼 명시적으로 문자열을 date 타입으로 바꿀 필요 없음. 그냥 첫번째처럼 써도 비교해줌. 두번째는 문자열을 date 타입으로 강제변화하는거지만 위와 별 차이 없음.

내장함수

• IFNULL은 뒤에 나온값이 null일때 바꿔주는 용도로, isnull은 null인지 확인
• NOW는 하나의 SQL 안에서 같은 값을 호출하고, SYSDATE는 다르다. 그리고 SYSDATE를 사용하면 인덱스를 쓸 수 없고 안정적으로 복제할 수 없음
• sysdate-is-now 설정을 넣어서 쓰면 sysdate도 now와 동일하게 사용됨
• date_format은 datetime 타입을 문자열로, str_to_date는 데이트타임 타입으로, 하지만 명시적으로 쓰지 않아도 됨
• rpad, lpad는 지정된 문자를 덧붙혀서 길이를 만듬, trim은 문자를 제거

<script async class=”speakerdeck-embed” data-id=”36809f08c4bd4d3eb61442761e16fbf5" data-ratio=”1.77777777777778" src=”//speakerdeck.com/assets/embed.js”></script>