Real mysql 8.0 ch7 8

7 데이터 암호화

응용 프로그램의 암호화는 주로 중요 정보를 가진 칼럼 단위로 암호화를 수행하며, 데이터베이스 수준에서는 테이블 단위로 암호화를 적용한다.

데이터베이스 서버와 디스크 사이의 데이터 읽고 쓰기 지점에서 암호화 또는 복호화를 수행한다. MySQL 서버의 I/O 레이어에서만 데이터의 암호화 및 복호화 과정이 실행된다. 데이터 암호화 기능이 활성화되어 있어도 사용자 입장에서는 아무런 차이가 없다(TDE, Transparent Data Encryption).

데이터 암호화는 마스터 키와 테이블스페이스 키라는 두 종류를 가지고 있다. 마스터 키는 외부의 파일에서 가져오고, 암호화된 테이블이 생성될 때마다 해당 테이블을 위한 임의의 테이블스페이스 키를 발급한다. 마스터 키를 이용해서 테이블스페이스 키를 발급한다. 테이블스페이스 키는 외부에 노출되지 않기 때문에 변경하지 않아도 보안상 취약점이 되지 않지만, 마스터 키는 외부 파일을 이용하기 때문에 보안상 취약하여 주기적으로 변경해야 한다.

암호화한다고 해서 InnoDB 버퍼 풀의 효율이 달라지거나 메모리 사용 효율이 떨어지는 현상은 발생하지 않는다. 데이터 페이지가 암호화 키보다 훨씬 크기 때문에 암호화 결과가 평문의 결과와 동일한 크기의 암호문을 반환한다.

같은 테이블에 대해 암호화와 압축이 동시에 적용되면 압축을 먼저 실행하고 암호화를 적용한다.

소스 서버와 레플리카 서버는 서로 각자의 마스터 키와 테이블스페이스 키를 관리하기 때문에 복제 멤버들의 데이터 파일은 암호화되기 전의 값이 동일하더라도 실제 암호화된 데이터가 저장된 데이터 파일의 내용은 완전히 달라진다.

응용 프로그램의 암호화는 이미 암호화된 값을 테이블에 넣기 때문에 정렬해서 가져오는 쿼리를 사용할 수 없다. 하지만 MySQL 서버의 암호화 기능은 인덱스 관련 작업을 모두 처리한 후 최종 디스크에 데이터 페이지를 저장할 때만 암호화하기 때문에 제약이 없다. 단, MySQL 서버의 암호화는 서버에 로그인이 가능하면 평문의 내용을 확인할 수 있다.

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

일반적으로 리두 로그나 언두 로그는 길지 않은 시간 동안의 데이터만 가지기 때문에 크게 보안에 민감하지 않을 수 있지만 바이너리 로그는 의도적으로 상당히 긴 시간 동안 보관하는 서비스도 있고 증분 백업을 위해 바이너리 로그를 보관하기도 해서 바이너리 로그 파일의 암호화는 상황에 따라 중요도가 높아질 수도 있다.
트랜잭션의 내용을 추적하거나 백업 복구를 위해 암호화된 바이너리 로그를 평문으로 복호화할 일이 자주 발생한다.


8 인덱스

데이터베이스의 성능 튜닝은 어떻게 디스크 I/O를 줄이느냐가 관건일 때가 상당히 많다. SSD는 기존 하드 디스크 드라이브보다 랩덤 I/O가 훨씬 빠르다는 것이다. 데이터베이스 서버에서 순차 I/O 작업은 그다지 비중이 크지 않고 랜덤 I/O를 통해 작은 데이터를 읽고 쓰는 작업이 대부분이므로 SSD의 장점은 DBMS용 스토리지에 최적이라고 볼 수 있다.

단순하게 말하자면, 랜덤 I/O는 디스크에 기록해야 할 위치를 찾기 위해 디스크의 헤더를 데이터 개수만큼 움직이지만 순차 I/O는 1번 움직인다. 순차 I/O가 훨씬 빠르다. 즉, 디스크 성능은 디스크 헤더의 위치 이동 없이 얼마나 많은 데이터를 한 번에 기록하느냐에 의해 결정된다. 그래서 여러 번 쓰기 또는 읽기를 요청하는 랜덤 I/O 작업이 작업 부하가 훨씬 크다. 데이터베이스 대부분의 작업은 이러한 작은 데이터를 빈번히 읽고 쓰기 때문에 MySQL 서버에는 그룹 커밋이나 바이너리 로그 버퍼 또는 InnoDB 로그 버퍼 등의 기능이 내장돼 있다.

일반적으로 쿼리를 튜닝하는 것은 랜덤 I/O 자체를 줄여 쿼리를 처리하는 데 꼭 필요한 데이터만 읽도록 쿼리를 개선하는 것을 의미한다.
인덱스 레인지 스캔은 데이터를 읽기 위해 주로 랜덤 I/O를 사용하며, 풀 테이블 스캔은 순차 I/O를 사용한다. 큰 테이블의 레코드 대부분을 읽는 작업에서는 인덱스를 사용하지 않고 풀 테이블 스캔을 사용하도록 유도할 때도 있다. (데이터 웨어하우스나 통계 작업에서 자주 사용된다.)

DBMS의 인덱스는 칼럼의 값을 주어진 순서로 미리 정렬해서 보관한다. 데이터 파일은 저장된 순서대로 별도의 정렬 없이 그대로 저장해둔다.

인덱스는 데이터의 저장 성능을 희생하고 그 대신 데이터의 읽기 속도를 높이는 기능이다. SELECT 쿼리 문장의 WHERE 조건절에 사용되는 칼럼이라고 해서 전부 인덱스로 생성하면 데이터 저장 성능이 떨어지고 인덱스의 크기가 비대해져 오히려 역효과만 불러올 수 있다.

  • B-Tree 인덱스는 칼럼의 값을 변형하지 않고 원래의 값을 이용해 인덱싱하는 알고리즘이다.
  • Hash 인덱스 알고리즘은 칼럼의 값으로 해시값을 계산해서 인덱싱하는 알고리즘으로, 매우 빠른 검색을 지원한다. 하지만 값을 변형해서 인덱싱하므로 값의 일부 검색이나 범위 검색할 때는 해시 인덱스를 사용할 수 없다. 주로 메모리 기반의 데이터베이스에서 많이 사용한다.

유니크 인덱스에 동등 조건으로 검색한다는 것은 항상 1건의 레코드만 찾으면 더 찾지 않아도 된다는 것을 옵티마이저에게 알려주는 효과를 낸다.

B-Tree

최상위에 하나의 루트 노드가 존재하고 그 하위에 자식 노드가 붙어 있는 형태다. 트리 구조의 가장 하위에 있는 노드를 리프 노드라 하고, 트리 구조에서 루트 노드도 아니고 리프 노드도 아닌 중간의 노드를 브랜치 노드라고 한다.

레코드가 삭제되어 빈 공간이 생기면 그다음의 INSERT는 가능한 한 삭제된 공간을 재활용하도록 DBMS가 설계되기 때문에 하상 INSERT된 순서로 저장되는 것은 아니다.

레코드 주소는 MyISAM 테이블의 생성 옵션에 따라 레코드가 테이블에 INSERT된 순번이거나 데이터 파일 내의 위치다. MyISAM 테이블은 세컨더리 인덱스가 물리적인 주소를 가지는 반면 InnoDB 테이블은 프라이머리 키를 주소처럼 사용하기 때문에 논리적인 주소를 가진다고 볼 수 있다.

InnoDB 스토리지 엔진에서는 모든 세컨더리 인덱스 검색에서 데이터 레코드를 읽기 위해서는 반드시 프라이머리 키를 저장하고 있는 B-Tree를 다시 한번 검색해야 한다.

B-Tree에 저장될 때는 저장될 키 값을 이용해 B-Tree상의 적절한 위치를 검색해야 한다. 저장될 위치가 결정되면 레코드의 키 값과 대상 레코드의 주소 정보를 B-Tree의 리프 노드에 저장한다. 리프 노드가 꽉 차서 더는 저장할 수 없을 때는 리프 노드가 분리돼야 하는데, 이는 상위 브랜치 노드까지 처리의 범위가 넓어진다.

인덱스 추가로 인해 INSERT나 UPDATE 비용의 대부분이 메모리와 CPU에서 처리하는 것이 아니라 디스크로부터 인덱스 페이지를 읽고 쓰기를 해야 해서 걸리는 시간이다.

InnoDB 스토리지 엔진은 필요하다면 인덱스 키 추가 작업을 지연시켜 나중에 처리할 수 있지만 프라이머리 키나 유니크 인덱스의 경우 중복 체크가 필요하기 때문에 즉시 B-Tree에 추가하거나 삭제한다.

인덱스 삭제는 해당 키 값이 저장된 리프 노드를 찾아서 삭제 마크만 하면 작업이 완료된다. 삭제 마킹된 인덱스 키 공간은 계속 그대로 방치하거나 재활용할 수 있다. 키 값 변경 작업은 먼저 키 값을 삭제한 후, 다시 새로운 키 값을 추가하는 형태로 처리된다.

B-Tree 인덱스를 이용한 검색은 100% 일치 또는 값의 앞부분만 일치하는 경우에 사용할 수 있다. 함수나 연산을 수행한 결과로 정렬한다거나 검색하는 작업은 B-Tree의 장점을 이용할 수 없으므로 주의해야 한다.

인덱스에서 유니크한 값의 개수는 인덱스나 쿼리의 효율성에 큰 영향을 미친다. 인덱스를 통해 테이블의 레코드를 읽는 것은 인덱스를 거치지 않고 바로 테이블의 레코드를 읽는 것보다 높은 비용이 드는 작업이다. 인덱스를 통해 레코드 1건을 읽는 것이 테이블에서 직접 레코드 1건을 읽는 것보다 4~5배 정도 비용이 더 많이 드는 작업인 것으로 예측한다. 이 예측을 통해 인덱스를 통해 읽어야 할 레코드 건 수가 총 레코드의 20~25%를 넘어서면 인덱스를 사용하는 것보다 모두 직접 읽어서 필요한 레코드만 가려내는(필터링) 방식으로 처리하는 것이 효율적이다.

인덱스 레인지 스캔

인덱스 레인지 스캔은 검색해야 할 인덱스의 범위가 결정됐을 때 사용하는 방식이다. 루트 노드에서부터 비교를 시작해 브랜치 노드를 거치고 최종적으로 리프 노드까지 찾아 들어가야만 비로소 필요한 레코드의 시작 지점을 찾을 수 있다. 시작해야 할 위치를 찾으면 그때부터는 리프 노드의 레코드만 순서대로 읽으면 된다. 스캔하다가 리프 노드의 끝까지 읽으면 리프 노드 간의 링크를 이용해 다음 리프 노드를 찾아서 다시 스캔한다. 최종적으로 스캔을 멈춰야 할 위치에 다다르면 지금까지 읽은 레코드를 사용자에게 반환하고 쿼리를 끝낸다. 인덱스 리프 노드에서 검색 조건에 일치하는 건들은 데이터 파일에서 레코드를 읽어오는데, 레코드 한 건 한 건 단위로 랜덤 I/O가 한 번씩 일어난다.

인덱스 풀 스캔

인덱스의 처음부터 끝까지 모두 읽는 방식이다. 쿼리의 조건절에 사용된 칼럼이 인덱스의 첫 번째 칼럼이 아닌 경우 인덱스 풀 스캔 방식이 사용된다. 인덱스 리프 노드의 제일 앞 또는 제일 뒤로 이동한 후, 인덱스의 리프 노드를 연결하는 링크드 리스트를 따라서 처음부터 끝까지 스캔하는 방식을 인덱스 풀 스캔이라고 한다. 테이블 풀 스캔보다는 효율적이다. 일반적으로 인덱스를 생성하는 목적은 아니다.

루스 인덱스 스캔 ( 인덱스 스킵 스캔 )

말 그대로 느슨하게 또는 듬성듬성하게 인덱스를 읽는 것을 의미한다. 인덱스 레인지 스캔과 비슷하게 작동하지만 중간에 필요치 않은 인덱스 키 값은 무시(SKIP)하고 다음으로 넘어가는 형태로 처리한다. 일반적으로 GROUP BY 또는 집합 함수 가운데 MAX(), MIN() 함수에 대해 최적화를 하는 경우에 사용된다. 인덱스 스킵 스캔은 인덱스의 선행 칼럼이 가진 유니크한 값의 개수가 소량일 때만 적용 가능한 최적화라는 것을 기억하자.

다중 칼럼(Multi-column) 인덱스

인덱스의 두 번째 칼럼은 첫 번째 칼럼에 의존해서 정렬돼 있다. 즉, 두 번째 칼럼의 정렬은 첫 번째 칼럼이 똑같은 레코드에서만 의미가 있다는 것이다. 앞의 칼럼에 의존하여 정렬된다. 다중 칼럼 인덱스에서는 인덱스 내에서 각 칼럼의 위치(순서)가 상당히 중요하다.

인덱스의 정렬 및 스캔 방향

인덱스를 어느 방향으로 읽을지는 쿼리에 따라 옵티마이저가 실시간으로 만들어내는 실행 계획에 따라 결정된다. 인덱스 생성 시점에 오름차순 또는 내림차순으로 정렬이 결정되지만 쿼리가 그 인덱스를 사용하는 시점에 인덱스를 읽는 방향에 따라 오름차순 또는 내림차순 정렬 효과를 얻을 수 있다.

InnoDB에서 인덱스 역순 스캔이 인덱스 정순 스캔에 비해 느릴 수밖에 없는 두 가지 이유가 있다.

  • 페이지 잠금이 인덱스 정순 스캔에 적합한 구조
  • 페이지 내에서 인덱스 레코드가 단방향으로만 연결된 구조 많은 쿼리가 인덱스의 한쪽 방향만 집중적으로 읽어서 인덱스의 특정 페이지 잠금이 병목이 될 것으로 예상된다면 쿼리에서 자주 사용되는 정렬 순서대로 인덱스를 생성하는 것이 잠금 병목 현상을 완화하는 데 도움이 될 것이다.

인덱스의 가용성과 효율성

작업 범위를 결정하는 조건은 많으면 많을수록 쿼리의 처리 성믕을 높이지만 체크 조건은 많다고 해서 쿼리의 성능을 높이지는 못한다. 오히려 쿼리 실행을 더 느리게 만들 때가 많다.

B-Tree 인덱스의 특징은 왼쪽 값에 기준해서 오른쪽 값이 정렬돼 있다는 것이다. 이런 정렬 특성은 빠른 검색의 전제 조건이다. 하나의 컬럼으로 검색해도 값의 왼쪽 부분이 없으면 인덱스 레인지 스캔 방식의 검색이 불가능하다. 다중 칼럼 인덱스에서도 마찬가지이다.

인덱스의 특성상 다음 조건

  • NOT-EQUAL로 비교된 경우
  • LIKE %?? 형태로 문자열 패턴이 비교된 경우
  • 스토어드 함수나 다른 연산자로 인덱스 칼럼이 변형된 후 비교된 경우
  • NOT-DETERMINISTIC 속성의 스토어드 함수가 비교 조건에 사용된 경우
  • 데이터 타입이 서로 다른 비교
  • 문자열 데이터 타입의 콜테이션이 다른 경우
INDEX ix_test ( column_1, column_2, column_3, ..., column_n )
  • 작업 범위 결정 조건으로 인덱스를 사용하지 못하는 경우
    • column_1 칼럼에 대한 조건이 없는 경우
    • column_1 칼럼의 비교 조건이 위의 인덱스 사용 불가 조건 중 하나인 경우
  • 작업 범위 결정 조건으로 인덱스를 사용하는 경우 (i는 2보다 크고 n보다 작은 임의의 값을 의미)
    • column_1 ~ column-(i-1) 칼럼까지 동등 비교 형태로 비교
    • column_i 칼럼에 대해 다음 연산자 중 하나로 비교
      • 동등 비교
      • 대소 비교
      • LIKE로 좌측 일치 패턴 위의 두 가지 조건을 모두 만족하는 쿼리는 column_1부터 column_i까지는 작업 범위 결정 조건으로 사용되고, column_(i+1)부터 column_n까지의 조건은 체크 조건으로 사용된다.

R-Tree 인덱스

공간 인덱스는 R-Tree 인덱스 알고리즘을 이용해 2차원의 데이터를 인덱싱하고 검색하는 목적의 인덱스다.

MySQL의 공간 확장

  • 공간 데이터를 저장할 수 있는 데이터 타입
  • 공간 데이터의 검색을 위한 공간 인덱스(R-Tree 알고리즘)
  • 공간 데이터의 연산 함수(거리 또는 포함 관계의 처리)

공간 정보의 검색을 위한 R-Tree 알고리즘을 이해하려면 MBR이라는 개념을 알고 있어야 한다. Minimum Bounding Rectangle의 약자로 해당 도형을 감싸는 최소 크기의 사각형을 의미한다.

  • 최하위 레벨의 MBR은 각 도형 데이터의 MBR을 의미한다.
  • 차상위 레벨의 MBR은 중간 크기의 MBR이다. R-Tree의 브랜치 노드.
  • 최상위 MBR은 R-Tree의 루트 노드에 저장되는 정보이다.

일반적으로는 GPS 기준의 위도, 경도 좌표 저장에 주로 사용된다. 뿐만 아니라 CAD/CAM 소프트웨어 또는 회로 디자인 등과 같이 좌표 시스템에 기반을 둔 정보에 대해서는 모두 적용할 수 있다.

인덱스 알고리즘

전문 검색 인덱스는 문서의 키워드를 인덱싱하는 기법에 따라 크게 단어의 어근 분석과 n-gram 분석 알고리즘으로 구분할 수 있다. 불용어 처리는 검색에서 별 가치가 없는 단어를 모두 필터링해서 제거하는 작업을 의마한다. 어근 분석은 검색어로 선정된 단어의 뿌리인 원형을 찾는 작업이다.

함수 기반 인덱스

칼럼의 값을 변형해서 만들어진 값에 대해 인덱스를 구축해야 할 때도 있는데, 이러한 경우 함수 기반의 인덱스를 활용하면 된다.

  • 가상 칼럼을 이용한 인덱스
  • 함수를 이용한 인덱스

내부적인 구조 및 유지관리 방법은 B-Tree 인덱스와 동일하다.

results matching ""

    No results matching ""