코딩꿈나무 코딩꿈나무

1) 개요 ● 실행 순서 MySQL에서 쿼리가 실행되는 과정은 다음과 같은 3단계이다. 1. SQL문장을 잘게 쪼개서 MySQL서버가 이해할 수 있는 수준으로 분리(파스 트리)한다. 2. 파싱정보 확인하면서 어떤 테이블로부터 읽고 어떤 인덱스를 이용해 테이블을 읽을지 선택한다. 3. 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져온다. 1의 단계를 SQL 파싱이라고 하는데, 이 때 'SQL파서' 모듈로 처리한다. 문법적으로 잘못된 SQL문인 경우 해당 단계에서 걸러진다. 해당 단계에서 SQL 파스 트리가 만들어지며 SQL문을 해당 SQL 파스트리를 실행하는 것이다. 2의 단계는 최적화 및 실행 계획 수립이며, '옵티마이저' 에서 처리를 한다. 완료되면 실행계획이 만..

MySQL 8.0 버전까지 업그레이드가 되면서 MyISAM 스토리지엔진에서만 제공하던 전문검색, 위치기반 검색 기능 모두 InnoDB스토리지 엔진에서 사용할 수 있게 개선되었다. MySQL서버의 옵티마이저가 발전하고 성능이 개선되었어도 여전히 관리자의 역할은 매우 중요하기 때문에 인덱스에 관한 지식은 개발자나 관리자에게 모두 중요하며, 쿼리 튜닝의 기본이 된다. 1) 디스크 읽기 방식 데이터베이스의 성능 튜닝은 어떻게 디스크 I/O를 줄이느냐가 큰 관건이다. 컴퓨터에서 CPU나 메모리 같은 주요 장치는 전자식 장치이지만 HDD의 경우 기계식 장치이다. 이런 HDD를 대체하기 위해 전자식 저장 매체인 SSD가 많이 출시 되고 있다. SSD는 기존 하드 디스크 드라이브에서 데이터 저장용 플래터(원판)을 제거..

3) 테이블 암호화 키링 플러그인은 마스터 키를 생성하고 관리하는 부분까지만 담당하기 때문에 어떤 키링 플러그인을 사용하든 암호화된 테이블을 생성하고 활용하는 방법 모두 동일하다. 테이블 생성은 다음과 같이 할 수 있다. CREATE TABLE tab_encrypted ( id INT, data VARCHAR(100), PRIMARY KEY(id) ) ENCRYPTION='Y'; 기존 테이블 생성 구문과 동일하며 마지막에 ENCRYPTION ='Y' 옵션만 추가로 넣으면 된다. 응용프로그램에서 직접 암호화해서 MySQL 서버에 저장하는 경우가 있는데, MySQL은 이 컬럼이 암호화된것인지 여부를 판단하지 못한다. 이에 따라서 해당 컬럼을 인덱스를 생성하더라도 인덱스 기능을 100% 활용 할 수 없다. 응..

트랜잭션의 격리 수준 이란 여러 트랜잭션이 동시에 처리 될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지를 결정하는 것이다. 격리 수준을 알기 전에 용어 부터 정리해보자 1. DIRTY READ 한 트랜잭션이 다른 트랜잭션에 의해 수정되었지만 커밋되지 않은 데이터를 읽어오는 상황 만약 커밋되지 않고 롤백되어버린다면 데이터가 일관되지 않은 상태여서 잘못된 결과가 도출될 수 있다. 2. NON-REPEATABLE READ 동일한 트랜잭션 내에서 처음 조회한 데이터가 다른 트랜잭션에 의해 수정되고 커밋되고나서 다시 조회한 경우 수정된 데이터를 읽어오는 상황 3. PHANTOM READ 트랜잭션이 특정조건을 만족하는 행 집합 을 읽은 후 에 동일한 트랜잭션 내에서 ..

1) InnoDB 스토리지 엔진의 잠금 InnoDB스토리지 엔진은 레코드 기반의 잠금을 제공한다. 잠금 정보가 상당히 작은 공간으로 관리되기 때문에 레코드락이 페이지 락 혹은 테이블락으로 레벨업(락 에스컬레이션) 는 존재하지 않는다. InnoDB엔진에서는 레코드와 레코드 사이의 간격을 잠그는 갭(GAP) 락이라는 것이 존재한다. 1. 레코드락 레코드 자체만을 잠그는것을 레코드 락이라고 하며, InnoDB엔진은 레코드 자체가 아니라 인덱스 레코드를 잠근다. 만약 인덱스가 존재하지 않더라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정한다. 보조 인덱스를 이용한 변경 작업은 넥스트 키 락 혹은 갭 락을 사용하지만 프라이머리 키 혹은 유니크 인덱스에 의한 변경 작업에서는 갭에 대해서는 잠그지 않..

● 잠금 잠금은 동시성을 제어하기 위한 기능이다. 하나의 회원 정보 레코드를 여러 커넥션에서 동시에 변경하려고 하는것을 방지한다. 이것을 해주지 않으면 해당 레코드 값을 예측해줄 수 없다. 1) 트랜잭션 트랜잭션은 작업의 완전성을 보장해주는 것, 논리적인 작업 셋을 완벽히 처리하거나 처리하지 못할 경우 원상태로 복구해서 작업의 일부만 적용되는 현상(Partial update)이 발생하지 않게 해준다. ● 격리 수준 하나의 트랜잭션 내에서 혹은 여러 트랜잭션 간의 작업 내용을 어떻게 공유하고 차단할 것인지 결정하는 레벨 1. MySQL에서의 트랜잭션 트랜잭션은 하나의 논리적인 작업셋에 하나의 쿼리가 있든지, 두 개 이상의 쿼리가 있든 관계없이 해당 논리적인 작업 셋 자체가 100% 적용 혹은 아무것도 적용..

③ MyISAM 스토리지 엔진 아키텍처 1) 키 캐시 InnoDB의 버퍼풀과 비슷한 역할을 하지만, 인덱스 대상으로만 작동한다. 그리고 쓰기 작업에 대해서만 부분적 버퍼링 역할을 한다. 키캐시 히트율은 100 - (Key_reads / Key_read_requests * 100) 로 알 수 있다. Key_reads 는 인덱스를 디스크에서 읽어들이는 횟수를 저장하는 상태 변수, Key_read_requests는 키캐시로부터 인덱스를 읽은 횟수를 저장하는 상태 변수이다. (SHOW GLOBAL STATUS로 알수 있다.) 키 캐시 히트율은 99% 이상을 권장하며 미만이면 키 캐시 공간을 늘려야한다. 키 캐시는 영역설정을 해주어야 하는데, 어떤 스키마의 어떤 테이블의 인덱스를 캐시할지 설정을 해주어야한다. 2..

1) 프라이머리 키에 의한 클러스터링 InnoDB엔진의 모든 테이블은 프라이머리 키값 순서대로 디스크에 저장되며, 모든 세컨더리의 인덱스는 레코드의 주소 대신 프라이머리 키의 값을 논리적 주소로 사용한다. 프라이머리 키가 클러스터링 인덱스이기 때문에 해당 키를 이용한 레인지스캔은 상당히 빠르다. 또한 쿼리 실행계획에서 프라이머리키가 다른 보조 인덱스보다 선택될 확률이 높다. MyISAM엔진에서는 클러스터링 키를 지원하지 않아서 프라이머리 키와 세컨더리 인덱스와의 구조적 차이가 없다. 단지 유니크키를 가진 세컨더리 인덱스일 뿐이다. 2) 외래키 지원 MyISAM이나 MEMORY 엔진은 외래 키를 지원하지 않는다. InnoDB에서 외래 키는 부모 테이블과 자식테이블 모두 해당 컬럼에 인덱스 생성이 필요하고 ..