[Real MySQL 8.0] 7장 데이터 암호화, 8장 인덱스 (1) (5일차)

7장 데이터 암호화

데이터 파일에서만 제공하다 이후 리두, 언두 로그 등 모두 암호화 기능을 지원한다.

이는 보안 감사에서 필수적인 요소이다.

7.1 MySQL 서버의 데이터 암호화

데이터베이스 서버와 디스크 사이의 데이터를 읽고 쓰는 지점에서 암호화, 복호화를 수행한다. 즉, InnoDB 의 I/O 레이어에서만 실행된다.

 

이 방식을 TDE(Transparent Data Encryption) 이라 한다. 사용자와 MySQL 내부에서 아무런 차이가 없다.

 

TDE 에서 암호화 키는 키링 플러그인에 의해 관리된다. 서버는 마스터 키와 테이블스페이스 키를 가진다.

 

암호화된 테이블이 생성될 때마다 해당 테이블을 위한 임의의 테이블스페이스 키를 발급한다. 그리고 마스터 키를 이용해 테이블스페이스 키를 암호화해서 데이터 파일 헤더에 저장한다.

 

마스터키는 외부의 파일을 사용하기에 주기적으로 바꾸고, 이때마다 모든 테이블의 테이블스페이스 키를 복호화 후 다시 새로운 마스터 키로 암호화한다.

 

TDE 는 디스크로 읽어올 때 복호화하기에 버퍼 풀에 한 번 적재되면 암호화되지 않은 테이블과 동일한 성능을 보인다. 하지만 새로운 페이지를 불러올 때 복호화 시간만큼 지연 시간이 발생한다.

 

복제시에는 모든 노드들이 각자의 마스터 키를 가지고 있어야한다.

7.2 keyring_file 플러그인 설치

암호화의 키 관리는 플러그인 방식으로 제공된다. keyring_file 플러그인은 마스터 키를 디스크에 평문으로 관리한다.

 

TDE 플러그인은 MySQL 서버 시작 시 가장 빨리 초기화 되어야하기에 시스템 변수에 라이브러리를 명시해줘야한다.

7.3 테이블 암호화

일반적인 테이블 생성 구문에 마지막 ENCRYPTION = 'Y' 옵션만 추가하면 된다.

 

필드에 암호화 된 내용을 집어넣으면 인덱스를 처리할 때 암호화 된 값을 기준으로 정렬되기에 암호화 전의 값을 기준으로 정렬할 수 없다. 하지만 TDE 는 서버 내에서는 모두 복호화되어있기에 이런 제약이 없다.

 

테이블스페이스 이동 시에 원본과 목적지 마스터 키가 다르기에 신경써줘야한다. 이때도 명령어를 통해 임시 마스터키를 발급하고 이를 통해 암호화하기에 나중에 해당 임시 마스터키와 함께 이동시키면 복구할 수 있다.

7.4 언두 로그 및 리두 로그 암호화

서버 내부 메모리는 복호화되었지만 이 평문 데이터가 테이블 데이터 이외에 디스크 파일로 기록되면 여전히 보안의 문제가 있다. 즉, 리두, 언두, 바이너리 로그에는 복호화된 상태로 기록되기에 여기에서도 암호화가 필요하다.

 

MySQL 서버는 리두, 언두 로그를 평문으로 저장하다가 암호화가 활성화되면 그때부터 생성되는 리두, 언두 로그를 암호화한다.

리두 로그와 언두 로그 데이터 모두 각각의 테이블스페이스 키로 암호화되고, 해당 키는 마스터 키로 암호화된다.

7.5 바이너리 로그 암호화

바이너리 로그는 의도적으로 상당히 긴 시간동안 보관되어야한다.

MySQL 은 바이너리 로그와 릴레이 로그 파일 데이터의 암호화를 위해 2단계 암호화 키 관리 방식을 사용한다.

 

데이터들은 파일 키로 암호화해서 디스크에 저장하고 파일 키는 바이너리 로그 암호화 키로 암호화 후 로그 파일의 헤더에 저장한다. 이때 바이너리 로그 암호화 키 는 테이블 암호화의 마스터키 같은 역할을 한다.

8장 인덱스

8.1 디스크 읽기 방식

요즘 HDD 보다 SDD 가 많이 사용되고 있지만 여전히 저장 매체는 컴퓨터에서 가장 느린 부분이다.

 

디스크의 헤더를 움직이지 않고 많은 데이터를 읽는 순차 I/O 에서는 HDD 와 SDD 의 큰 차이는 없아. SDD 가 정말 빠른 것은 랜덤 I/O 가 훨씬 바르다는 것이다. 또한 데이터베이스 서버에서는 랜덤 I/O 가 대부분이다.

 

순차 I/O 는 3개의 페이지를 디스크에 기록하기 위해서 1번의 시스템 콜을 요청해 한 번에 저장한다. 즉, 디스크에 기록해야할 위치를 찾기 위해 헤드를 1번만 움직인다.

 

반면 랜덤 I/O 는 3개의 페이지를 디스크에 기록하기 위해 각 페이지의 위치를 찾기 위해 3번의 시스템 콜을 요청한다.

 

디스크의 성능은 헤더의 위치 이동에 따라 좌지우지되므로 랜덤 I/O 작업이 부하가 훨씬 크다. 그리고 데이터베이스의 작업은 대부분 작은 데이터를 빈번히 읽고 쓰기 작업이다.

8.2 인덱스란?

인덱스는 많은 데이터 파일에서 빠르게 위치를 찾기 위한 색인이다. DBMS 에서 모든 데이터를 검색하기에는 시간이 오래걸리기에 칼럼과 레코드가 저장된 주소를 Key-Value 로 삼아 인덱스를 만들어 두는 것이다. 또한 책의 색인처럼 미리 정렬되어있다는 것도 큰 특징이다.

 

하지만 매번 데이터가 저장될 때마다 다시 정렬해야하기에 저장하는 과정이 느리지만 아주 빠른 조회가 가능하다.

인덱스는 데이터를 관리하는 방식과 중복 값 허용 여부에 따라 여러 가지로 분류할 수 있다.

 

데이터 저장 방식 별로는 대표적으로 B-Tree 와 Hash 인덱스가 있다. 요즘에는 Fractal-Tree 인덱스나 로그 기반 Merge-Tree 인덱스도 존재한다.

• B-Tree 알고리즘은 가장 일반적인 인덱스 알고리즘이다. 칼럼의 값을 변형하지 않고 원래의 값을 이용해 인덱싱한다.
• Hash 인덱스 알고리즘은 칼럼의 값으로 해시값을 계산해 인덱싱한다. 하지만 값을 변형해서 인덱싱하므로 Prefix 일치 같이 값의 일부만 검색하거나 범위 검색 시 사용할 수 없다.

데이터 중복 허용 여부로 분류하면 유니크 인덱스와 Non-Unique 인덱스로 구분할 수 있다. 이는 쿼리를 실행하는 옵티마이저에게 상당히 중요한 문제이다. 동등 조건으로 검색한다면 오직 한 건만 조회하면 된다는 정보를 추가적으로 알릴 수 있기 때문이다.

B-Tree 인덱스

가장 일반적인 알고리즘이다. 여러 변형된 형태가 있는데 B+-Tree, B*-Tree 가 있다. 이때 B 는 Balanced 이다.

B-Tree 는 이름 그대로 트리 구조로 최상위에 하나의 루트 노드가 존재한다. 가장 하위에 있는 노드를 리프 노드라 하며, 중간 노드들을 브랜치 노드라 한다.

 

데이터베이스에서 인덱스와 실제 데이터가 저장된 데이터는 따로 관리되는데, 인덱스의 리프 노드들은 항상 실제 데이터 레코드를 찾아갈 수 있는 주솟값을 가지고 있다.

 

위처럼 인덱스의 키 값은 모두 정렬되어있지만 데이터 파일은 임의의 순어이다. 인덱스는 테이블 키 칼럼만 가지고 있으므로 나머지 칼럼을 읽기 위해서는 데이터 파일과 매핑되어야한다.

 

테이블에 레코드를 저장하거나 변경 시 인덱스 키 추가나 삭제 작업이 발생한다. 새로운 키 값이 B-Tree 에 저장될 때 테이블의 스토리지 엔진에 따라 저장 시기가 다르다. 저장될 대는 키를 사용해 적절한 위치를 검색해야한다. 위치가 결정되면 레코드의 키 값과 대상 레코드 주소 정보를 리프 노드에 저장한다. 만약 리프 노드가 꽉 차면 리프 노드가 분리되어야하는데, 이는 상위 브랜치 노드까지 처리 범위가 넓어진다. 이러한 이유로 B-Tree 는 상대저긍로 쓰기 작업에 비용이 많이 든다.

 

B-Tree 의 키 값이 삭제되는 경우, 키 값이 저장된 리프 노드를 찾아 삭제 마크만 하면 작업이 완료된다. 이렇게 마킹된 공간은 방치하거나 재활용할 수 있다.

 

인덱스의 키 값은 그 값에 따라 위치가 결정되기에 키 값이 변경되면 위치도 변경해줘야한다. 추가 삭제는 위의 절차와 동일하다.

이러한 비용에도 인덱스를 사용하는 이유는 빠른 검색이다. 인덱스 검색에는 B-Tree 의 루트 노드부터 리프 노드까지 이동하며 비교 작업을 수행하는데 이 과정이 트리 탐색이다. 인덱스를 통한 검색은 정렬되어있기에 100% 일치 혹은 Prefix 검색만 가능하다.

 

InnoDB 에서는 레코드 잠금이나 넥스트 키락이 검색을 수행한 인덱스를 잠근 후 테이블의 레코드를 잠그는 방식으로 구현되어있다. 따라서 적절하게 사용할 수 있는 인덱스가 없으면 불필요하고 많은 레코드를 잠근다. 만약 인덱스가 없다면 모든 레코드를 잠글 수도 있다. 따라서 인덱스의 설계가 중요하고 많은 부분에 영향을 미친다.

 

일반적으로 B-Tree 의 자식 노드의 개수는 가변적인 구조이다. 최대 개수 또한 인덱스의 페이지 크기와 키 값의 크기에 따라 결정된다.

 

인덱스의 깊이도 매우 중요하지만 제어할 방법이 없다. 결국 인덱스 키 값의 크기는 가능한한 작게 만드는 것이 좋다.

 

인덱스를 통해 레코드를 읽는 것은 바로 레코드를 읽는 것보다 비용이 많이 든다. 즉, 100만건의 레코드를 모두 읽어 50만건을 버릴 지, 인덱스를 통해 50만건만 가져올 지 판단해야한다. 일반적으로는 레코드를 읽을 때 4

5 배 많은 비용이 들기 때문에 인덱스를 통해 읽는 레코드 건수가 전체의 20

25% 를 넘지 않을 때만 인덱스를 활용하도록 필터링 하는 것이 효율적이다.

MySQL 이 인덱스를 이용하는 대표적인 세 가지 방법이다.

 

인덱스 레인지 스캔

가장 대표적인 방식이다. 알파벳 순서로 정렬된 위의 인덱스 노드 예시에서 만약 SELECT ... first_name BETWEEN 'Aamer' AND 'Gad 쿼리를 실행한다고 하자.

 

인덱스 레인지 스캔은 검색해야할 인덱스의 범위가 결정되었을 때 사용한다. 실제 예제에서 보이듯이 레코드의 시작지점을 빠르게 찾을 수 있다. 한 번 시작 지점을 찾으면 그떄부터 리프 노드의 레코드만 순서대로 읽으면 된다. 이렇게 차례대로 읽는 것을 스캔이라 표현한 것이다. 이후 종료 지점을 찾게되면 지금까지 읽은 레코드를 반환하고 쿼리를 끝낸다.

 

물론 실제 읽는 값들은 인덱스의 값이므로 실제 주솟값에서 데이터를 읽을 것이다. 이때 중요한 것은 어떤 방식으로 스캔하든 해당 인덱스를 구성하는 칼럼의 정순 혹은 역순으로 정렬된 상태로 레코드를 반환하는 것이다.

 

또한 인덱스의 리프 노드에서 검색 조건에 일치하는 건들은 데이터 파일에서 레코드를 읽어오는 과정이 필요하다. 이를 위해서 레코드 한 건 한 건 단위로 랜덤 I/O 가 발생한다. 따라서 N 번의 랜덤 I/O 가 한 번의 순차 I/O 보다 성능이 좋기 위해서 전체 중 20~25% 의 레코드를 읽어야 한다는 것이다!

1. 인덱스에서 조건에 만족하는 값이 저장된 위치를 찾는다. 이 과정이 인덱스 탐색이다.
2. 1번에서 탐색된 위치부터 필요한 만큼 인덱스를 차례대로 쭉 읽는다. 이 과정을 인덱스 스캔이라 한다.
3. 2번에서 읽어 들인 인덱스 키와 레코드 주소를 이용해 레코드가 저장된 페이지를 가져오고, 최종 레코드를 읽어온다.

MySQL 에서는 1번, 2번 단계가 얼마나 수행됐는지 제공하기 위한 상태값을 제공한다.

인덱스 풀 스캔

인덱스의 처음부터 끝까지 모두 읽는 방식을 인덱스 풀 스캔이라한다.

쿼리의 조건절에 사용된 칼럼이, 인덱스의 첫 번째 칼럼이 아닌 경우 사용된다.

 

키 값이 정렬되어있지만 suffix 를 검색하는 느낌이다. 정렬된 내용을 사용할 수 없어 전부를 스캔해야한다.

이 방식은 인덱스 레인지 스캔보다는 빠르지 않지만, 테이블 풀 스캔보다는 효율적이다.

인덱스의 전체 크기는 테이블보다는 작으므로 테이블 전체를 읽는 것 보다는 적은 디스크 I/O가 일어나기 때문이다.

루스 인덱스 스캔

말 그대로 느슨하게, 듬성듬성하게 인덱스를 읽는 것이다.
인덱스 레인지 스캔과 비슷하게 동작하지만 중간에 필요치 않은 인덱스 키값은 SKIP 한다. 일반적으로 GROUP BY 혹은 집계 함수 중 MIN() or MAX() 최적화에 사용된다.

인덱스 스킵 스캔

인덱스의 핵심은 값이 정렬돼 있다는 것이다. 따라서 인덱스를 구성하는 칼럼의 순서가 매우 중요하다.

만약 ADD INDEX ix_gender_birthdate (gender, bitrh_date); 쿼리를 실행한다면 이후 gender 만을 조건으로 가지는 쿼리에서는 해당 인덱스를 사용할 수 있지만 birthdate 만을 조건으로 가진다면 인덱스를 사용하지 못하고 인덱스 풀 스캔이 발생한다.

 

따라서 옵티마이저가 birthdate 만으로 인덱스 검색이 가능하게 하는 인덱스 스킵 스캔 최적화 기능이 도입되었다.

 

SELECT ... WHERE birth_date >= ...

 

하나의 쿼리를 마치 2개의 쿼리처럼 실행하는 것이다.

SELECT ... WHERE gender='M' AND birth_date >= ...
SELECT ... WHERE gender='F' AND birth_date >= ...

하지만 MySQL 8.0 에 새롭게 도입된 기능이라 아직 단점이 있다.

1. WHERE 조건절에 조건이 없는 인덱스의 선행 칼럼의 유니크한 개수가 적어야함
2. 쿼리가 인덱스에 존재하는 칼럼만으로 처리 가능해야함

만약 유니크한 개수가 성별이 아니라 생일처럼 많다면 1개의 쿼리가 2개 처럼이 아닌 365개처럼 작동될 수 있기 때문이다.

또한 조건에서 다른 칼럼이 추가로 필요하다면 어쩔 수 없이 인덱스 데이터만으로 판별이 불가능하다.

다중 칼럼 인덱스

지금까지 본 인덱스는 모두 1개의 칼럼만 포함된 인덱스이다. 하지만 실제는 다중 칼럼 인덱스가 더 많이 사용된다.

이 경우 두 번째 칼럼의 정렬은 첫 번쨰 칼럼에 의존된다. 만약 성별이 첫번째 칼럼이고 생일이 두 번째 칼럼이라면 우선적으로 성별에 의해 정렬되고 그 내부적으로 생일로 분류된다는 것이다.

인덱스의 정렬 및 스캔 방향

인덱스 생성 시 키 값은 항상 오름차순이나 내림차순이다.
하지만 오름차순 인덱스도 거꾸로 읽으면 내림차순으로 정렬된 인덱스로 사용될 수 있다. 이런 방향은 옵티마이저가 실시간으로 만들어내는 계획에 따라 결정된다.

 

일반적인 상용 DBMS 에서는 인덱스 생성 시점에 정렬 방향을 정할 수 있다.

 

SELECT ... ORDER BY first_name DESC LIMIT 1

 

이런 쿼리가 있을 때 인덱스를 오름차순으로 끝까지 읽어 하나를 가져오는 것보다 거꾸로 읽어 하나만 가져오는 게 훨씬 좋을 것이다.

 

인덱스의 정렬 방향은 생성 시 결정되지만 인덱스를 읽는 방향에 따라 다른 정렬 효과를 얻을 수 있다는 것이다.

 

그렇다면 두 개 모두 동일한 성능을 가지는가?

 

실제 테스트를 해보면 역순 정렬 쿼리가 정순 정렬 쿼리보다 28.9% 더 시간이 걸린다. 이러한 차이가 발생하는 것은 InnoDB 의 내부적 구조 때문이다.

1. 페이지 잠금이 인덱스 정순 스캔에 적합한 구조이다.
2. 페이지 내에서 인덱스 레코드가 단방향으로만 연결된 구조이다.

실제 LinkedList 같지만 단 방향으로만 연결되었다는 것이다. 그러니 웬만하면 정순을 사용하자.

B-Tree 인덱스의 가용성과 효율성

다중 칼럼 인덱스에서 각 칼럼의 순서와 그 조건이 동등인지, 범위 조건인지에 따라 활용 형태가 달라진다.

SELECT ... WHERE dept_no='d002' AND emp_no >= 10114

 

이런 쿼리라면 dept_no, emp_no 인덱스를 만들 때 어떤 순서로 만들어야하는가??

 

위처럼 dept_no 가 동등 조건이라면 dept_no 를 첫 번째 순서로 인덱스를 만들자. 그러면 dept_no='d002' 데이터는 한 곳에 몰려있으니 나머지 조건으로 바로 검색할 수 있다. 즉 꼭 필요한 비교작업만 진행할 수 있다.

 

공식 명칭은 아니지만 작업의 범위를 결정하는 조건을 작업 범위 결정 조건 이라 하며, 비교 작업의 범위를 줄이지 못하는 조건을 필터링 조건 이라 표현한다.