인덱스 바이너리

마지막 업데이트: 2022년 4월 16일 | 0개 댓글
  • 네이버 블로그 공유하기
  • 네이버 밴드에 공유하기
  • 페이스북 공유하기
  • 트위터 공유하기
  • 카카오스토리 공유하기
'BINARY as attribute of a type' is deprecated and will be removed in a future release.
Please use a CHARACTER SET clause with _bin collation instead

[Real MySQL] B-Tree 인덱스

인덱스는 데이터베이스 쿼리의 성능을 언급하면서 빼놓을 수 없는 부분입니다. MySQL에서 사용 가능한 인덱스의 종류 및 특성에서 각 특성의 차이는 상당히 중요하며, 물리 수준의 모델링을 할 때도 중요한 요소가 될 것입니다. 다른 RDBMS에서 제공하는 모든 기능을 제공하지는 않지만, MySQL에서는 인덱싱이나 검색 방식에 따라 다른 스토리지 엔진을 선택해야 할 수도 있기 때문에 여전히 인덱스에 대한 기본 지식은 중요하며, 쿼리 튜닝의 기본 이 될 것입니다. 또한 인덱스에만 의존적인 용어는 아니지만, 자주 언급되는 "랜덤(Random) I/O"와 "순차(Sequential) I/O"와 같은 디스크 읽기 방식도 알아두는 것이 좋습니다.

컴퓨터의 CPU나 메모리와 같은 전기적 특성을 띤 장치의 성능은 짧은 시간 동안 매우 빠른 속도로 발전했지만 디스크와 같은 기계식 장치의 성능은 상당히 제한적으로 발전했습니다. 데이터베이스나 쿼리 튜닝에 어느정도 지식을 갖춘 사용자가 많이 절감하고 있듯이, 데이터베이스의 성능 튜닝은 어떻게 디스크 I/O를 줄이느냐가 관건인 것들이 상당히 많습니다.

디스크의 읽기 방식을 살펴보기 전에 간단히 데이터를 저장할 수 있는 매체(Media)에 대해 살펴보면, 일바적으로 서버에 사용되는 저장 매체는 크게 3가지로 나뉩니다.

내장 디스크(Internal Disk)

DAS(Direct Attached Storage)

NAS(Network Attached Storage)

SAN(Storage Area Network)

내장 디스크는 개인용 PC의 본체 내에 장착된 디스크와 같은 매체입니다. 물론 서버용으로 사용되는 디스크는 개인 PC에 장착되는 것보다는 빠르고 안정적인 것들입니다. 그리고 개인 PC와는 달리 데이터베이스 서버용으로 사용되는 장비는 일반적으로 4~6개 정도의 내장 디스크를 장착합니다. 하지만 컴퓨터의 본체 내부 공간은 제한적이어서 장착할 수 있는 디스크의 개수가 적고 용량도 부족할 때가 많습니다.

내장 디스크의 용량 문제를 해결하기 위해 주로 사용하는 것이 DAS인데, DAS는 컴퓨터의 본체와는 달리 디스크만 있는 것이 특징 입니다. DAS 장치는 독자적으로 사용할 수 없으며, 컴퓨터 본체에 연결해서만 사용할 수 있습니다. DAS나 내장 디스크는 모두 SATA나 SAS와 같은 케이블로 연결되기 때문에 실제 사용자에게는 거의 같은 방식으로 사용되며, 성능 또한 내장 디스크와 거의 비슷합니다. 최근의 DAS는 디스크를 최대 200개까지 장착할 수 있는 것들도 있기 때문에 대용량의 디스크가 필요한 경우에는 DAS가 적합합니다. 하지만 DAS는 반드시 하나의 컴퓨터 본체에 연결해서 사용할 수 있기 때문에 디스크의 정보를 여러 컴퓨터가 동시에 공유하는 것이 불가능 합니다.

내장 디스크와 DAS의 문제점을 동시에 해결하기 위해 주로 NAS와 SAN을 사용합니다. DAS와 NAS의 가장 큰 차이는 여러 컴퓨터에서 동시에 사용할 수 있는지와 컴퓨터 본체와 연결되는 방식입니다. 위에서도 살펴봤지만 DAS는 내장 디스크와 같이 컴퓨터 본체와 SATA나 SAS 또는 SCSI 케이블로 연결되지만, NAS는 TCP/IP를 통해 연결됩니다. NAS는 동시에 여러 컴퓨터에서 공유해서 사용할 수 있는 저장매체이지만 SATA나 SAS 방식의 직접 연결보다는 속도가 매우 느립니다.

SAN은 DAS로는 구축할 수 없는 아주 대용량의 스토리지 공간을 제공하는 장치입니다. SAN은 여러 컴퓨터에서 동시에 사용할 수 있을뿐더러 컴퓨터 본체와 광케이블로 연결되기 때문에 상당히 빠르고 안정적인 데이터 처리(읽고 쓰기)를 보장해줍니다. 하지만 그만큼 고가의 구축 비용이 들기 때문에 각 기업에서는 중요 데이터를 보관할 경우에만 일반적으로 사용합니다.

NAS는 TCP/IP로 데이터가 전송되기 때문에 빈번한 데이터 읽고 쓰기가 필요한 데이터베이스 서버용으로는 거의 사용되지 않습니다. 내장 디스크 → DAS → SAN 순으로, 뒤로 갈수록 고사양 고성능이며, 구축 비용도 올라갑니다. 각 장치가 얼마나 많은 디스크 드라이브를 장착할 수 있는지, 그리고 어떤 방식으로 컴퓨터 본체에 연결되는지에 따른 구분일 뿐, 여기에 언급된 모든 저장 매체는 내부적으로 1개 이상의 디스크 드라이브를 장착하고 있다는 점은 같습니다. 대부분의 저장 매체는 디스크 드라이브의 플래터(Platter, 디스크 드라이브 내부의 데이터 저장용 원판)를 회전시켜서 데이터를 읽고 쓰는 기계적인 방식을 사용합니다.

디스크 드라이브와 솔리드 스테이트 드라이브

컴퓨터에서 CPU나 메모리와 같은 주요 장치는 대부분 전자식 장치지만 디스크 드라이브는 기계식 장치입니다. 그래서 데이터베이스 서버에서는 항상 디스크 장치가 병목 지점이 됩니다. 이러한 기계식 디스크 드라이브를 대체하기 위해 전자식 저장 매체인 SSD(Solid State Drive)가 많이 출시되고 있씁니다. SSD도 기존 디스크 드라이브와 같은 인터페이스(SATA나 SAS)를 지원하므로 내장 디스크나 DAS 또는 SAN에 그대로 사용 가능합니다.

SSD는 기존의 디스크 드라이브에서 데이터 저장용 플래터를 제거하고 대신 플래시 메모리를 장착하고 있습니다. 그래서 디스크 원판을 기계적으로 회전시킬 필요가 없으므로 아주 빨리 데이터를 읽고 쓸 수 있습니다. 플래시 메모리는 전원이 공급되지 않아도 데이터가 삭제되지 않습니다. 그리고 컴퓨터 메모리보다는 느리지만 기계식 디스크 드라이브보다는 훨씬 빠릅니다.

디스크의 헤더를 움직이지 않고 한번에 많은 데이터를 읽는 순차 I/O에서는 SSD가 디스크 드라이브보다 조금 빠르거나 거의 비슷한 성능 을 보이기도 합니다. 하지만 SSD의 장점은 기존의 디스크 드라이브보다 랜덤 I/O가 훨씬 빠 르다는 것 입니다. 데이터베이스 서버에 순차 I/O 작업은 그다지 비중이 크지 않고 랜덤 I/O를 통해 작은 데이터를 읽고 쓰는 작업이 대부분이므로 SSD의 장점은 DBMS용 스토리지에 최적이라고 볼 수 있씁니다.

가령 벤치마크 결과를 살펴보면 SSD는 초당 436개의 트랜잭션을 처리했지만 디스크 드라이브는 초당 60개의 트랜잭션밖에 처리하지 못했씁니다. 이 벤치마크 결과는 저자가 간단히 준비한 데이터로 테스트한 내용이라서 실제 애플리케이션에서는 어느 정도의 성능 차이를 보일지 예측하기가 어렵니다. 하지만 일반적인 웹 서비스 환경의 데이터베이스에서는 SSD가 디스크 드라이브보다는 훨씬 빠릅니다. 물론 인덱스 바이너리 애플리케이션을 직접 벤치마킹해볼 수 있다면 더 나은 선택을 할 수 있을 것입니다.

랜덤 I/O와 순차 I/O

랜덤 I/O라는 표현은 디스크 드라이브의 플래터(원판)를 돌려서 읽어야 할 데이터가 저장된 위치로 디스크 헤더를 이동시킨 다음 데이터를 읽는 것을 의미하는데, 사실 순차 I/O 또한 이 작업은 같습니다. 그렇다면 랜덤 I/O와 순차 I/O는 어떤 차이가 있을까요?

Sequential 액세스 방식 (인덱스 바이너리 [그림 Ⅲ-1-13]에서 ⑤번)

Random 액세스 방식 ([그림 Ⅲ-1-13]에서 ①, ②, ③, ④, ⑥번)

순차 I/O는 연속된 3개의 페이지를 접근하게 되는 방식이라 디스크에 기록하기 위해 한번 시스템 콜을 요청하지만 랜덤 I/O는 3개의 페이지를 디스크에 기록하기 위해 3번의 시스템 콜을 하게 되는 방식이 됩니다. 즉, 디스크에 기록해야 할 위치를 찾기 위해 순차 I/O는 디스크의 헤드를 1번 움직였고, 랜덤 I/O는 디스크 헤드를 3번 움직인 것입니다. 디스크에 데이터를 쓰고 읽는 데 걸리는 시간은 디스크 헤더를 움직여서 읽고 쓸 위치로 옮기는 단계에서 결정됩니다. 결국 여기서 제시한 예에서는 순차 I/O가 랜덤 I/O보다 거의 3배 정도 빠르다고 볼 수 있습니다. 즉, 디스크의 성능은 디스크 헤더의 위치 이동 없이 얼마나 많은 데이터를 한 번에 기록하느냐에 의해 결정된다고 볼 수 있습니다.

그래서 여러번 쓰기 또는 읽기를 요청하는 랜덤 I/O 작업이 훨씬 작업의 부하가 커지게 됩니다. 데이터베이스 대부분의 작업은 이러한 작은 데이터를 빈번히 읽고 쓰기 때문에 MySQL 서버에는 그룹 커밋이나 바이너리 로그 버퍼 또는 InnoDB 로그 버퍼 등의 기능이 내장되어 있습니다.

랜덤 I/O나 순차 I/O 모두 파일에 쓰기를 실행하면, 반드시 동기화(fsync 또는 flush 작업)가 필요합니다. 그런데 순차 I/O인 경우에도 이런 파일 동기화 작업이 빈번히 발생한다면 랜덤 I/O와 같이 비효율적인 형태로 처리될 때가 많습니다. 기업용으로 사용하는 데이터베이스 서버에는 캐시 메모리가 장착된 RAID 컨트롤러가 일반적으로 사용되는데, RAID 컨트롤러의 캐시 메모리는 아주 빈번한 파일 동기화 작업이 호출되는 순차 I/O를 효율적으로 처리될 수 있게 변환하는 역할 을 하게 됩니다.

사실 쿼리를 튜닝해서 랜덤 I/O를 순차 I/O로 바꿔서 실행할 방법은 그다지 많지 않습니다. 일반적으로 쿼리를 튜닝하는 것은 랜덤 I/O 자체를 줄여주는 것이 목적 이라고 할 수 있습니다. 여기서 랜덤 I/O를 줄인다는 것은 쿼리를 처리하는 데 꼭 필요한 데이터만 읽도록 쿼리를 개선하는 것 을 의미합니다.

인덱스 레인지 스캔은 데이터를 읽기 위해 주로 랜덤 I/O를 사용하며, 풀 테이블 스캔은 순차 I/O를 사용합니다. 그래서 큰 테이블의 레코드 대부분을 읽는 작업에서는 인덱스를 사용하지 않고 풀 테이블 스캔을 사용하도록 유도할 때도 있습니다. 이는 순차 I/O가 랜덤 I/O보다 훨씬 빨리 많은 레코드를 읽어올 수 있기 때문입니다. OLTP(On-Line Transaction Processing) 데이터갱신 위주 성격의 웹서비스보다는 데이터 웨어하우스나 통계 작업에서 자주 사용됩니다.

1) OLTP: On-Line Transaction Processing (데이터 갱신위주)

네트워크 상의 여러 이용자가 실시간으로 데이터베이스의 데이터를 갱신하거나 조회하는 등의 단위 작업을 처리하는 방식을 말합니다.

2) OLAP: On-Line Analytic Processing (데이터 조회위주 )

정보위주의 처리 분석을 의미합니다. 의사결정에 활용할 수 있는 정보를 얻을 수 있게 해주는 기술 입니다.

많은 사람들이 인덱스를 언급할 때는 항상 책의 제일 끝에 있는 찾아보기(또는 "색인")로 설명하곤 합니다. 책의 마지막에 있는 "찾아보기"가 인덱스에 비유된다면 책의 내용은 데이터 파일에 해당한다고 볼 수 있습니다. 책의 찾아보기를 통해 알아낼 수 있는 페이지 번호는 데이터 파일에 저장된 레코드의 주소에 비유될 것입니다. DBMS도 데이터베이스 테이블의 모든 데이터를 검색해서 원하는 결과를 가져오려면 시간이 오래 걸립니다. 그래서 컬럼(또는 컬럼들)의 값과 해당 레코드가 저장된 주소를 키와 값의 쌍(key-Value pair)로 인덱스를 만들어 두는 것입니다. 그리고 책의 "착아보기"와 DBMS의 인덱스의 공통점 가운데 중요한 것이 바로 정렬입니다. 책의 찾아보기도 내용이 많아지면 우리가 원하는 검색어를 찾아내는 데 시간이 걸릴 것입니다. 그래서 최대한 빠르게 찾아갈 수 있게 "ㄱ", "ㄴ", "ㄷ", . 와 같은 순서대로 정렬돼 있는데, DBMS의 인덱스도 마찬가지로 컬럼의 값을 주어진 순서로 미리 정렬해서 보관 합니다.

프로그래밍 언어의 자료구조와 인덱스와 데이터 파일을 비교해 가면서 살펴보면 다음과 같습니다. 프로그래밍 언어별로 각 자료구조의 이름이 조금씩 다르긴 하지만 SortedList와 ArrayList라는 자료구조는 익숙할 정도로 많이 들어본 적이 있을 것입니다. SortedList는 DBMS의 인덱스와 같은 자료구조이며, ArrayList는 데이터 파일과 같은 자료구조를 이용합니다. SortedList는 저장되는 값을 항상 정렬된 상태로 유지하는 자료구조이며, ArrayList는 값을 저장되는 순서대로 그대로 유지하는 자료구조 입니다. DBMS의 인덱스도 SortedList와 마찬가지로 저장되는 컬럼의 값을 이용해 항상 정렬된 상태로 유지합니다. 데이터 파일은 ArrayList와 같이 저장된 순서대로 별도의 정렬없이 그대로 저장해둡니다.

SortedList 자료구조는 데이터가 저장될 때마다 항상 값을 정렬해야 하므로 저장하는 과정이 복잡하고 느리지만, 이미 정렬돼 있어서 아주 빨리 원하는 값을 찾아올 수 있습니다. DBMS의 인덱스도 인덱스가 많은 테이블은 당연히 INSERT나 UPDATE 그리고 DELETE 문장의 처리가 느려집니다. 하지만 이미 정렬된 "찾아보기"용 표(인덱스)를 가지고 있기 때문에 SELECT 문장은 매우 빠르게 처리할 수 있습니다.

결론적으로 DBMS에서 인덱스는 데이터의 저장(INSERT, UPDATE, DELETE) 성능을 희생하고 그 대신 데이터의 읽기 속도를 높이는 기능 입니다. 여기서도 알 수 있듯이 테이블의 인덱스를 하나 더 추가할지 말지는 데이터의 저장 속도를 어디까지 희생할 수 있는지, 읽기 속도를 얼마나 더 빠르게 만들어야 하는지의 여부에 따라 결정 돼야 합니다. SELECT 쿼리 문장의 WHERE 조건절에 사용되는 컬럼이라고 전부 인덱스로 생성하면 데이터 저장 성능이 떨어지고 인덱스의 크기가 비대해져서 오히려 역효과 만 불러올 수 있습니다.

인덱스를 역할별로 구분한다면 프라이머리 키(Primary Key)와 보조 키(Secondary Key)로 구분해 볼 수 있습니다. 데이터 저장 방식(알고리즘)별로 구분하는 것은 상당히 많은 분류가 가능하겠지만 대표적으로 B-Tree 인덱스와 Hash 인덱스로 구분할 수 있습니다. 그리고 Fractal-Tree 인덱스와 같은 알고리즘도 존재합니다.

B-Tree 알고리즘은 가장 일반적으로 사용되는 인덱스 알고리즘 으로서, 상당히 오래전에 도입된 알고리즘이며 그만큼 성숙해진 상태입니다. B-Tree 인덱스는 칼럼의 값을 변형하지 않고, 원래의 값을 이용해 인덱싱하는 알고리즘 입니다.

Hash 인덱스 알고리즘은 컬럼의 값으로 해시 값을 계산해서 인덱싱하는 알고리즘 으로, 매우 빠른 검색을 지원합니다. 하지만 값을 변형해서 인덱싱하므로, 전방(Prefix) 일치와 같이 값의 일부만 검색하고자 할 때는 해시 인덱스를 사용할 수 없습니다. Hash 인덱스는 주로 메모리 기반의 데이터베이스에서 많이 사용합니다.

Fractal-Tree 알고리즘은 B-Tree의 단점을 보완하기 위해 고안된 알고리즘 입니다. 값을 변형하지 않고 인덱싱하며 범용적인 목적으로 사용할 수 있다는 측면에서 B-Tree와 거의 비슷하지만 데이터가 저장되거나 삭제될 때 처리 비용을 상당히 줄일 수 있게 설계된 것이 특징입니다. 아직 B-Tree 알고리즘만큼 안정적이고 성숙되진 않았지만 아마도 조만간 B-Tree 인덱스의 상당 부분을 대체할 수 있지 않을까 생각합니다.

데이터의 중복 허용 여부로 분류하면 유니크 인덱스(Unique)와 유니크하지 않은 인덱스(Non-Unique )로 구분할 수 있습니다. 인덱스가 유니크한지 아닌지는 단순하게 같은 값이 1개만 존재하는지 1개 이상 존재할 수 있는지를 의미하지만 실제 DBMS의 쿼리를 실행해야 하는 옵티마이저에게는 상당히 중요한 문제가 됩니다.

B-Tree는 데이터베이스의 인덱싱 알고리즘 가운데 가장 일반적으로 사용되고, 또한 가장 먼저 도입된 알고리즘입니다. 하지만 아직도 가장 범용적인 목적으로 사용되는 인덱스 알고리즘입니다. B-Tree에는 여러 가지 변형된 형태의 알고리즘이 있는데, 일반적으로 DBMS에서는 주로 B+-Tree 또는 B*-Tree가 사용됩니다. 인터넷상에서 쉽게 구할 수 있는 B-Tree의 구조를 설명한 그림 때문인지 많은 사람들이 B-Tree의 "B"가 바이너리(이진) 트리라고 잘못 생각하고 있습니다. 하지만 B-Tree의 "B"는 "Binary(이진)"의 약자가 아니라 "Balanced"를 의미 합니다.

B-Tree는 컬럼의 원래 값을 변형시키지 않고 (물론 값의 앞부분만 잘라서 관리하기는 하지만) 인덱스 구조체 내에서는 항상 정렬된 상태로 유지하고 있습니다. 전문 검색과 같은 특수한 요건이 아닌 경우, 대부분 인덱스는 거의 B-Tree를 사용할 정도로 일반적인 용도에 적합한 알고리즘입니다.

B-Tree 인덱스를 제대로 사용하려면 B-Tree의 기본적인 구조는 알고 있어야 합니다. B-Tree는 트리 구조의 최상위에 하나의 "루트 노드"가 존재하고 그 하위에 자식 노드가 붙어 있는 형태입니다. 트리 구조의 가장 하위에 있는 노드를 "리프 노드"라 하고, 트리 구조에서 루트 노드도 아니고 리프 노드도 아닌 중간 노드를 "브랜치 노드"라고 합니다. 데이터베이스에서 인덱스와 실제 데이터가 저장된 데이터는 따로 관리되는데, 인덱스의 리프 노드는 항상 실제 데이터 레코드를 찾아가기 위한 주소 값을 가지고 있습니다.

인덱스의 키값은 모두 정렬돼 있지만 데이터 파일의 레코드는 정렬돼 있지 않고 임의의 순서대로 저장돼 있습니다. 많은 사람이 데이터 파일의 레코드는 INSERT된 순서대로 저장되는 것으로 생각하지만 그렇지 않습니다. 만약 테이블의 레코드를 전혀 삭제나 변경없이 INSERT만 수행한다면 맞을 수도 있습니다. 하지만 레코드가 삭제되어 빈 공간이 생기면 그다음의 INSERT는 가능한 삭제된 공간을 재활용하도록 DBMS가 설계되기 때문에 항상 INSERT된 순서로 저장되는 것은 아닙니다.

대부분 RDBMS의 데이터 파일에서 레코드는 특정 기준으로 정렬되지 않고 임의의 순서대로 저장됩니다. 하지만 InnoDB 테이블에서 레코드는 클러스터되어 디스크에 저장되므로 기본적으로 프라이머리 키 순서대로 정렬되어 저장됩니다. 이는 오라클 IOT(Index organized table)나 MS-SQL의 클러스터 테이블과 같은 구조를 말합니다. 다른 DBMS에서는 클러스터링 기능이 선택 사항이지만, InnoDB에서는 사용자가 별도의 명령이나 옵션을 선택하지 않아도 디폴트로 클러스터링 테이블이 생성됩니다. 클러스터링이란 비슷한 값들은 최대한 모아서 저장하는 방식 을 의미합니다.

인덱스는 테이블의 키 컬럼만 가지고 있으므로 나머지 컬럼을 읽으려면 데이터 파일에서 해당 레코드를 찾아야 합니다. 이를 위해 인덱스의 리프 노드는 데이터 파일에 저장된 레코드의 주소를 가지게 됩니다. "레코드 주소"는 DBMS 종류나 MySQL의 스토리지 엔진에 따라 의미가 달라집니다. 오라클은 물리적인 레코드 주소가 되지만 MyISAM 테이블에서는 내부적인 레코드의 아이디(번호)를 의미합니다. 그리고 InnoDB 테이블에서는 프라이머리 키에 의해 클러스터링되기 때문에 프라이머리 키값 자체가 주소 역할을 합니다. 실제 MySQL 테이블의 인덱스는 항상 인덱스 컬럼 값과 주소 값(MyISAM의 레코드 아이디 값 또는 InnoDB의 프라이머리 키값)의 조합이 인덱스 레코드로 구성됩니다.

B-Tree 인덱스 키 추가 및 삭제

테이블의 레코드를 저장하거나 변경하는 경우, 인덱스 키 추가나 삭제 작업이 발생합니다. 인덱스 키 추가나 삭제가 어떻게 처리되는지 알아두면 쿼리의 성능을 쉽게 예측할 수 있을 것입니다. 또한, 인덱스를 사용하면서 주의해야 할 사항도 함께 살펴보겠습니다.

새로운 키값이 B-Tree에 저장될 때 테이블의 스토리지 엔진에 따라 새로운 키값이 즉시 인덱스에 저장될 수도 있고 그렇지 않을 수도 있습니다. B-Tree에 저장될 때는 저장될 키값을 이용해 B-Tree상의 적절한 위치를 검색해야 합니다. 저장될 위치가 결정되면 레코드의 키값과 대상 레코드의 주소 정보를 B-Tree의 리프 노드에 저장합니다. 만약 리프 노드 가 꽉 차서 더는 저장할 수 없을 때는 리프 노드가 분리(Split)돼야 하는데, 이는 상위 브랜치 노드까지 처리의 범위가 넓어집니다. 이러한 작업 탓에 B-Tree는 상대적으로 쓰기 작업(새로운 키를 추가하는 작업)에 비용이 많이 드는 것으로 알려졌습니다.

인덱스 추가로 인해 INSERT나 UPDATE 문장이 어떤 영향을 받을지 궁금해하는 사람이 많습니다. 하지만 이 질문에 명확하게 답변하려면 테이블의 컬럼 수, 컬럼의 크기, 인덱스 컬럼의 특성 등을 확인해야합니다. 대략적으로 계산하는 방법은 테이블에 레코드를 추가하는 작업 비용을 1이라고 가정하면 해당 테이블의 인덱스에 키를 추가하는 작업 비용을 1~1.5 정도로 예측하는 것이 일반적 입니다. 일반적으로 테이블에 인덱스가 3개(테이블의 모든 인덱스가 B-Tree라는 가정하에)가 있다면 이때 테이블에 인덱스가 하나도 없는 경우 작업 비용이 1이고, 3개인 경우에는 5.5 정도의 비용(1.5*3 + 1) 정도로 예측해 볼 수 있씁니다. 중요한 것은 이 비용의 대부분이 메모리와 CPU에서 처리하는 시간이 아니라 디스크로부터 인덱스 페이지를 읽고 쓰기를 해야하기 때문에 시간이 오래 걸린다는 점입니다.

MyISAM이나 Memory 스토리지 엔진을 사용하는 테이블에서는 INSERT 문장이 실행되면 즉시 새로운 키값을 B-Tree 인덱스에 반영합니다. 즉 B-Tree에 키를 추가하는 작업이 완료될 때까지 클라이언트는 쿼리의 결과를 받지 못하고 기다리게 됩니다. MyISAM 스토리지 엔진은 delay-key-write" 파라미터를 설정해 인덱스 키 추가 작업을 미뤄서(지연) 처리할 수 있는데, 이는 동시 작업 환경에서는 적합하지 않습니다. InnoDB 스토리지 엔진은 이 작업을 조금 더 지능적으로 처리하는데, 상황에 따라 적절하게 인덱스 키 추가 작업을 지연시켜 나중에 처리할지, 아니면 바로 처리할지 결정 합니다.

(2) InnoDB의 버퍼 풀에 새로운 키값이 추가해야 할 페이지(B-Tree의 리프 노드)가 존재한다면 즉시 키 추가 작업 처리

(3) 버퍼 풀에 B-Tree의 리프 노드가 없다면 인서트 버퍼에 추가할 키값과 레코드의 주소를 임시로 기록해두고 작업 완료 (사용자의 쿼리는 실행 완료됨)

(4) 백그라운드 작업으로 인덱스 페이지를 읽을 때마다 인서트 버퍼에 머지해야 할 인덱스 키 값이 있는지 확인한 후, 있다면 병합함(B-Tree에 인덱스 키와 주소를 저장 )

(5) 데이터베이스 서버 자원의 여유가 생기면 MySQL 서버의 인서트 버퍼 머지 스레드가 조금씩 인서트 버퍼에 임시 저장된 인덱스 키와 주소 값을 머지(B-Tree에 인덱스 키와 주소를 저장) 시킴

InnoDB 스토리지 엔진의 인서트 버퍼는 MySQL 5.1 이하에서는 INSERT로 인한 인덱스 키 추가 작업만 버퍼링 및 지연 처리를 할 수 있었습니다. 하지만 MySQL 5.5 이상의 버전에서는 INSERT뿐 아니라 DELETE 등에 의한 인덱스 키의 추가 및 삭제 작업까지 버퍼링해서 지연 처리할 수 있게 기능이 확장 됐습니다. 그래서 MySQL 5.1 이하 버전에서는 이 기능을 인서트 버퍼링(Insert Buffering)이라고 했지만 MySQL 5.5 이상 버전부터는 체인지 버퍼링(Change Buffering)이라는 이름으로 바뀌었습니다. MySQL 5.5 이상 버전부터는 관련 설정 파라미터로 "innodb_change_buffering"이 새롭게 도입됐습니다. 인서트 버퍼에 의해 인덱스 키 추가 작업이 지연되어 처리된다 하더라도, 이는 사용자에게 아무런 악영향 없이 투명하게 처리되므로 개발자나 DBA는 이를 전혀 신경쓰지 않아도 됩니다. MySQL 5.1 이하 버전 에서는 자동으로 적용되는 기능이었지만 MySQL 5.5 이상 버전 부터는 "innodb_change_buffering" 설정값을 이용해 키 추가 작업과 키 삭제 작업 중 어느 것을 지연 처리할지 설정 해야 합니다.

B-Tree의 키값이 삭제되는 경우는 상당히 간단합니다. 해당 키값이 저장된 B-Tree의 리프 노드를 찾아서 그냥 삭제 마크만 하면 작업이 완료됩니다. 이렇게 삭제 마킹된 인덱스 키 공간은 계속 그대로 방치하거나 또는 재활용할 수 있습니다. 인덱스 키 삭제로 인한 마킹 작업 또한 디스크 쓰기가 필요하므로 이작업 역시 디스크 I/O가 필요한 작업 입니다. MySQL 5.5 이상의 버전의 InnoDB 스토리지 엔진에서는 이 작업 또한 버퍼링되어 지연처리가 될 수도 있습니다. 처리가 지연된 인덱스 키 삭제 또한 사용자에게는 특별한 악영향 없이 MySQL 서버가 내부적으로 처리하므로 특별히 걱정할 것은 없습니다. MyISAM이나 Memory 스토리지 엔진의 테이블에서는 인서트 버퍼와 같은 기능이 없으므로 인덱스 키 삭제가 완료된 후 쿼리 실행이 완료 됩니다.

인덱스의 인덱스 바이너리 키값은 그 값에 따라 저장될 리프 노드의 위치가 결정되므로 B-Tree의 키값이 변경되는 경우에는 단순히 인덱상의 키값만 변경하는 것은 불가능합니다. B-Tree의 키값 변경 작업은 먼저 키값을 삭제한 후, 다시 새로운 키값을 추가하는 형태로 처리됩니다. 키 값의 변경 때문에 발생하는 B-Tree 인덱스 키값의 삭제와 추가 작업은 위에서 설명한 절차대로 처리 됩니다.

INSERT, UPDATE, DELETE 작업을 할 때 인덱스 관리에 따르는 추가 비용을 감당하면서 인덱스를 구축하는 이유는 바로 빠른 검색을 위해서 입니다. 인덱스를 검색하는 작업은 B-Tree의 루트 노드부터 시작해 브랜치 노드를 거쳐 최종 리프 노드까지 이동하면서 비교 작업을 수행하는데, 이 과정을 "트리 탐색(Tree traversal )"이라고 합니다. 인덱스 트리 탐색은 SELECT에서만 사용하는 것이 아니라 UPDATE나 DELETE를 처리하기 위해 항상 해당 레코드를 먼저 검색해야 할 경우에도 인덱스가 있으면 빠른 검색이 가능합니다. B-Tree 인덱스를 이용한 검색은 100% 일치 또는 값의 앞부분(Left-most part )만 일치하는 경우에 사용할 수 있습니다. 부등호("<> ") 비교나 값의 뒷부분이 일치하는 경우에는 B-Tree 인덱스를 이용한 검색이 불가능합니다. 또한 인덱스를 이용한 검색에서 중요한 사실은 인덱스의 키값에 변형이 가해진 후 비교되는 경우에는 절대 B-Tree의 빠른 검색 기능을 사용할 수 없다는 것입니다. 이미 변형된 값은 B-Tree 인덱스에 존재하는 값이 아닙니다. 따라서 함수나 연산을 수행한 결과로 정렬한다거나 검색하는 작업은 B-Tree의 장점을 이용할 수 없으므로 주의해야 합니다.

InnoDB 스토리지 엔진에서 인덱스는 더 특별한 의미가 있습니다. InnoDB 테이블에서 지원하는 레코드 잠금이나 넥스트 키 락(갭 락)이 검색을 수행한 인덱스를 잠근 후 테이블의 레코드를 잠그는 방식으로 구현 돼 있습니다. 따라서 UPDATE나 DELETE 문장이 실행될 때 테이블에 적절히 사용할 수 있는 인덱스가 없으면 불필요하게 많은 레코드를 잠급니다. 심지어 테이블의 모든 레코드를 잠글 수도 있습니다. InnoDB 스토리지 엔진에서는 그만큼 인덱스의 설계가 중요하고 많은 부분에 영향을 미친다는 것입니다.

B-Tree 인덱스 사용에 영향을 미치는 요소

B-Tree 인덱스는 인덱스를 구성하는 컬럼의 크기와 레코드의 건수, 그리고 유니크한 인덱스 키값의 개수 등에 의해 검색이나 변경 작업의 성능이 영향 을 받습니다.

인덱스 키 값의 크기

InnoDB 스토리지 엔진은 디스크에 데이터를 저장하는 가장 기본 단위를 페이지(Page) 또는 블록(Block)이라고 하며, 디스크의 모든 읽기 및 쓰기 작업의 최소 작업 단위 가 됩니다. 또한 페이지는 InnoDB 스토리지 엔진의 버퍼 풀에서 데이터를 버퍼링하는 기본 단위 이기도 합니다. 인덱스도 결국은 페이지 단위로 관리되며, 위의 B-Tree 그림에서 루트와 브랜치, 그리고 리프(L eaf) 노드를 구분한 기준이 바로 페이지 단위 입니다.

이진(Binary) 트리는 각 노드가 자식 노드를 2개만 가지는데, 만약 DBMS의 B-Tree가 이진 트리라면 인덱스 검색이 상당히 비효율적일 것입니다. 그래서 B-Tree의 "B"가 이진(Binary) 트리의 약자는 아니라고 강조했던 것입니다. 일반적으로 DBMS의 B-Tree는 자식 노드의 개수가 가변적인 구조입니다. 그러면 MySQL의 B-Tree는 자식 노드를 몇 개까지 가질지가 궁금할 것입니다. 그것은 바로 인덱스 페이지 크기와 키 값의 크기에 따라 결정됩니다. InnoDB의 모든 페이지 크기는 16KB로 고정돼 있습니다(이를 변경하려면 소스 컴파일이 필요함). 만약 인덱스의 키가 16바이트라고 가정하면 다음 그림과 같이 인덱스 페이지가 구성될 것입니다. 자식 노드 주소라는 것은 여러 가지 복합적인 정보가 담긴 영역이며, 페이지의 종류별로 대략 6바이트에서 12바이트까지 다양한 크기의 값을 가질 수 있습니다.

MySQL - binary 타입을 사용한 varchar, char 대소문자 구분 - _bin collation

안녕하세요.
이번 포스팅에서는 binary 타입의 collation 을 사용하여 문자형 컬럼 타입인 varchar 와 char 의 대소문자 구분과 관련된 내용을 확인 해보도록 하겠습니다.

MySQL Collation

Collation 은 Character 간의 정렬을 의미하며 Collation 은 크게 2가지로 나뉘게 됩니다.

• binary collation
문자를 encoding 된 바이너리 스트림 값으로 문자를 비교 하게 인덱스 바이너리 됩니다
a 와 A 는 코드가 다르기 때문에 다른문자로 인식 되며, 즉 대소문자를 구별할 수 있습니다.

• case-insensitive collation
_ci 가 붙은 문자열 입니다(ex: utf8mb4_unicode_ci)
1. 대문자 소문자를 같은 문자로 다루게 됩니다.
2. 이 후에 encoding 으로 비교 합니다.

_ci 에는 몇가지 종류가 있으며 여기서는 크게 2가지에 대해서 언급 드리도록 하겠습니다.
(CI: case-insensitive:대소문자 구별하지 않는)

크게 general_ci 과 unicode_ci 2가지로 구분할 수 있으며 보통 둘중 하나를 사용 합니다. 각 언어셋별 default collation 정보는 show character set 에서 확인 할 수 있습니다

default collation 은 general 이 대부분이며 collation 별로 정렬의 기능의 차이와 속도의 차이가 발생하게 됩니다.

• general_ci
속도를 높이는데 중점이 되어 빠르며 일반적인 경우 사용되게 됩니다 general_ci 의 경우 ÀÁÅåāă 인덱스 바이너리 등 과 같이 accents 문자가 없어서 대해서 해당 단어의 대문자인 A로 치환/비교 되게 됩니다

• unicode_ci
유니코드 규칙을 기반으로 하여 정확한 정렬을 목표로 수행 됩니다

해당 포스팅은 Characterset 과 관련된 이전 포스팅에서 이어지는 글 입니다.

포스팅 환경
- MySQL 8.0.23
- Characterset : utf8mb4
- collation_server : 기본(default)

binary collation

문자형 컬럼인 char 와 varchar 에서는 위에서 설명한 내용과 같은 binary 형 collation 을 사용할 수 있습니다.
* 포스팅에서는 테스트의 간편을 위해서 varchar 만 사용하였으나 동일하게 하게 사용할 수 있습니다.

관련 해서 어떠한 차이점이나 어떤 형태로 동작 하는지 아래에서 살펴보도록 하겠으며, 먼저 테스트 테이블 과 데이터를 입력 하도록 하겠습니다.

[ 참고 ] BINARY 및 VARBINARY 와 같은 이진 문자열 타입 데이터 유형은 CHAR BINARY 및 VARCHAR BINARY 데이터 유형과 다르며, 포스팅에서는 CHAR BINARY 및 VARCHAR BINARY 데이터 유형에 대해서 설명 하고 있습니다.

테스트 테이블 생성


테이블 생성

테이블은 위와같이 생성할 수 있으며, binary 절은 아직까지는 사용가능한 구문이나 8.0기준으로 deprecated 되었으며 향후 버전에서는 remove 될 예정 입니다.

그래서 binary 절을 사용하여 생성시 아래와 같은 warning 메세지를 확인 할 수 있습니다.

'BINARY as attribute of a type' is deprecated and will be removed in a future release.
Please use a CHARACTER SET clause with _bin collation instead


[ 참고 ] 현재 포스팅에서 사용중인 테스트 논리 database 는 별도의 collation 을 지정하지 않고 기본값으로 생성한 상태 입니다.


데이터 입력

먼저 co1 컬럼으로 정렬을 확인 해보도록 하겠습니다.

col1 은 보통의 varchar 컬럼입니다. 위와 결과와 같이 대소문가 섞여 있지만, ascending 이나 descending 모두 같은 정렬을 보여주고 있습니다.

이번에는 col2 로 정렬 하도록 하겠습니다.

col2 는 binary 형 varchar 로 collation 은 utf8mb4_bin 입니다. 위의 결과를 보듯이 대소문자 구분이 되어 정렬이 되는 것을 확인 할 수 있습니다.

컬럼을 binary collation 으로 생성하지 않아도 쿼리에서 지정하여 사용할 수 있습니다.

binary collation 으로 지정하지 않은 컬럼에 대해서는 위와 같이 사용할 수 있습니다.

테스트를 위해서 인덱스를 생성하고 SQL Plan 을 확인 해보도록 하겠습니다.

혹시 index 를 사용하지 못하는지를 확인 해보기 위해서 인덱스를 생성 후 plan 을 확인하였으며 order by 에서는 index 가 사용할 수 있음을 확인 할 수 있습니다.

이번에는 조회를 해보도록 하겠습니다.

먼저 col1 컬럼에 조회조건을 사용해서 조회해보도록 하겠습니다.

일반 varchar 인 col1 에서는 대소문자를 구분하지 않기 때문에 대소문자가 맞지 않는 데이터도 모두 같이 출력되는 것을 확인 할 수 있습니다.

이번에는 col2 에서 조회조건을 사용해서 조회해보도록 하겠습니다.

binary collation 인 col2는 대소문자 구별을 하여 정확히 일치하는 데이터만 출력하는 것을 확인 할 수 있습니다.

이전 단계에서 order by 시 binary 절을 사용한 것처럼 조회시에도 binary 절을 사용할 수 있습니다.

col1 컬럼에서 binary 를 사용해서 조회해보겠습니다.


SQL Plan도 살펴보도록 하겠습니다.

* 포스팅 중간에 생성한 인덱스를 동일한 같이 생성해야지 위와 같은 결과가 나옴

위의 플랜에서 확인 하였을 때 인덱스를 사용하는 것을 확인 할 수 있습니다.

다만 튜닝 방법론에서 보통 많이 나오는 얘기인 우변을 가공하고 좌변을 가공하지 말아라 입니다.

방금 위에서 binary 절은 우변인 조건 대입부분에서 사용하였으며, 이번에는 좌변의 의미인 컬럼에 사용하고 플랜을 확인 해보도록 하겠습니다.

원하는데로 대소문자를 구분하여 조회조건에 입력된 데이터만 추출은 되었습니다.
다만 플랜에서는 이전과는 다르게 Index가 사용하지 못하고 있는 것을 확인 할 수 있습니다.

MySQL 8.0 버전의 Function Based Index 를 사용하는 것이 아닌 일반적인 형태의 인덱스의 경우 컬럼인 좌변을 함수로 가공하면 인덱스이 안될 수 있습니다.

[ 참고 ] 컬럼 타입 변경
위의 테이블 구조에서 col1 을 varchar(10) binary 로 변경시 조건에 따라서 Onlie DDL 타입이 달라지게 됩니다.

algorithm=instant 는 불가하고 algorithm=inplace,lock=none 는 해당 컬럼에 인덱스가 없다면 가능합니다.
인덱스가 있다면 algorithm=inplace,lock=none 도 불가하며 copy 방식으로 가능 합니다.

관련 내용은 이전 포스팅을 참조하시면 됩니다.

Unique

이번에는 Unique 인덱스 관련된 내용을 확인 해보도록 하겠습니다.

테스트를 위해서 이전에 생성한 인덱스를 삭제 하도록 하겠습니다.


그 다음에 Unique 제약조건을 위해서 Unique 인덱스를 생성하도록 하겠습니다.

col2 컬럼에 해당하는 Unique 인덱스는 생성이 되었으며 col1 컬럼의 인덱스는 생성 에러가 발생하였습니다.

컬럼에 Unique 를 생성 하는 과정에서 에러가 발생되었으며, 이유로는 데이터가 중복이 있어서 입니다.
대소문자를 구별해서 보면 중복 데이터가 없지만, col1 은 대소문자 구별이 되지 않기 때문에 중복 데이터로 인식되고 있으며 그로 인해 인덱스가 생성이 되지 않고 있습니다.

테이블의 데이터를 삭제 하고 인덱스를 생성 후 데이터를 입력해보도록 하겠습니다.

위의 내용처럼 대소문자가 다르지만 구분하지 않기 때문에 데이터 중복이 발생되고 있습니다.

공백 처리

MySQL 데이터 정렬에는 PAD SPACE 또는 NO PAD 값과 같은 pad 속성이 있습니다.

대부분의 MySQL 데이터 정렬에는 PAD SPACE의 패드 속성 입니다.


이진 문자열( BINARY, VARBINARY및 BLOB) 아닌 문자열( CHAR, VARCHAR 및 TEXT 값)의 경우 문자열 데이터 정렬 패드 속성은 문자열 끝에 있는 후행 공백 비교에서 처리를 결정합니다.

- PAD SPACE 데이터 정렬 과 비교시 후행 공백은 비교 시 중요하지 않습니다. 문자열은 후행 공백에 관계없이 비교됩니다.
- NO PAD 데이터 정렬은 다른 문자와 마찬가지로 비교에서 후행 공백을 중요한 것으로 취급합니다.

utf8mb4 바이너리 collation 에서의 PAD_ATTRIBUTE 을 확인할 수 있으며 그 중 하나는 PAD SPACE이고 다른 하나는 NO PAD입니다.

INFORMATION_SCHEMA COLLATIONS 테이블을 사용하여 데이터 정렬에 대한 패드 속성을 결정하는 방법을 확인할 수 있으며, 몇가지를 확인 해보도록 하겠습니다.

위와 같이 NO PAD 와 PAD SPACE 의 정보값을 확인 할 수 있으며 차이점은 아래와 같이 확인 할 수 있습니다.

위에서 계속 사용하던 테이블을 이용해서 공백을 넣고 조회를 해보도록 하겠습니다.

위의 결과 처럼 col1 은 뒤의 공백을 추가하여 조회하였을때 공백을 인식하여 다른 값임으로 매칭되는 결과가 없어서 Empty set 이 되었지만, binary 형 varchar(utf8mb4_bin) 는 공백을 넣었음에도 where 절 조건 매칭이 되어 조회결과나 나오는 것을 확인할 수 있습니다.

PAD SPACE 와 NO PAD 차이점은 아래와 같습니다.
- PAD SPACE 데이터 정렬 과 비교시 후행 공백은 비교 시 중요하지 않습니다. 문자열은 후행 공백에 관계없이 비교됩니다.
- NO PAD 데이터 정렬은 다른 문자와 마찬가지로 비교에서 후행 공백을 중요한 것으로 취급합니다.

추가로 set names 을 통해서 몇가지를 더 확인 해보도록 하겠습니다.

위와 같이 collation 별로 PAD Attribute 가 다르며 그에 따라서 공백의 처리가 다르게 됩니다.

처음에 테이블에서 조회하였을때 col1 이 공백까지 확인해서 조회결과가 없는 것으로 확인 하였습니다.
그럼 데이터베이스와 테이블, 컬럼의 collation 정보를 조금 더 자세하게 살펴보겠습니다.

* 위의 표는 가로 길이에 따라서 일부 내용이 편집되어 있습니다.

글 처음에 기재된 내용과 같이 MySQL 8.0 의 기본 character set 과 collation 환경 입니다.

데이터베이스의 기본 collation은 utf8mb4_0900_ai_ci 이며, 그에 따라서 테이블도 utf8mb4_0900_ai_ci 이며, binary 타입 varchar가 아닌 col1 컬럼의 경우 collation이 utf8mb4_0900_ai_ci 입니다.

그래서 col1 의 collation 을 utf8mb4_0900_ai_ci 에서 utf8mb4_general_ci 로 변경하거나 생성시 부터 utf8mb4_general_ci 으로 생성하면 위의 공백을 포함한 조회결과는 달라집니다.

위의 algorithm 은 상황에 따라서 사용 가능 여부가 달라집니다.
- 컬럼에 인덱스 있음 : 불가 / 컬럼에 인덱스 없음 : 가능

관련된 Online DDL 은 이전 포스팅을 참조하시면 됩니다.


테이블 정보를 확인 후 다시 조회를 해보겠습니다.

이전과 다르게 이번에는 공백이 포함이 있지만 조건절에 매칭이 되어 결과가 나온 것을 확인 할 수 있습니다. 다만 utf8mb4_bin 와 달리 대소문자는 구별하지 않기 때문에 여러 로우가 출력된 것을 확인 할 수 있습니다.

이진 문자열(BINARY, VARBINARY 및 BLOB 값)의 경우 후행 공백을 포함하여 모든 바이트가 비교합니다.

그럼 이번 포스팅은 여기서 마무리 하도록 하겠습니다.

Reference


관련된 다른 글

안녕하세요 이번 포스팅은 MySQL 의 Multi Source Replication(MSR) 환경에서 Source 의 DB(스키마명)명이 같을 경우 DB 명을 변경하여 사용하는 REPLICATE_REWRITE_DB 에 대해서 확인 해보려고 합니다.

Principal DBA(MySQL, AWS Aurora, Oracle)

Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
[email protected] / [email protected]

인덱스 바이너리

정수형 배열 nums 가 주어졌을 때, 각 인덱스 별로 자기보다 오른쪽에 있는 수 중 자기보다 작은 수들의 개수를 구하는 문제

예를 들어, 배열 nums 가 [5, 2, 6, 1] 과 같이 주어졌을 때 정답은 [2, 1, 1, 0] 이다. 가장 오른쪽에 있는 '1' 은 자기보다 오른쪽에 있는 값이 없기 때문에 무조건 0이 되고, '6' 과 '2' 는 오른쪽에 '1' 이, '5'는 오른쪽에 '2', '1' 이 있어 각각 1 과 2 를 정답으로 갖는다.

이 문제를 풀기 위한 방법으로 두 가지 방법을 사용했다. 첫 번째 방법은 Merge Sort 를 활용한 방법이었고 두 번째는 Binary Index Tree 의 일종인 Fenwick Tree 를 활용한 방법이다.

우선, Merge Sort 를 활용한 방법은 아이디어가 전혀 떠오르지 않아 아래 Reference 를 참고했다. Merge Sort 는 기본적으로 Divide & Conquer 방식으로 정렬을 수행하는데, 이 때 매 단계 별로 시작 위치 ~ 끝 위치 중 가운데 위치를 선정하고, 시작 위치 ~ 가운데 위치 / 가운데 위치 + 1 ~ 끝 위치까지 정렬을 먼저 한 후 시작 위치 ~ 끝 위치의 정렬을 수행한다. 정렬을 할 때, 시작 위치 ~ 가운데 위치 / 가운데 위치 + 1 ~ 끝 위치는 각각 정렬이 된 상태이므로 (아래 단계에서 이미 정렬을 하고 난 상태) 두 배열을 (위치 별로 나눈 것을 두 배열이라 표현) 합치는 과정에서 두 배열 중 작은 값을 먼저 배치하는 방식으로 정렬을 한다. 이를 간단히 코드로 표현하면 다음과 같다.

이 문제를 해결하기 위해, Merge Sort 에 추가적으로 도입한 아이디어는 두 배열을 하나로 합치는 과정에서 뒤 배열의 값들이 앞 배열보다 먼저 배치될 경우 앞 배열의 값들은 원래 위치에서 자기보다 오른쪽에 먼저 배치된 값들이 존재한다는 것이다. 즉, 위의 merge 소스 코드에서 앞 배열과 뒤 배열의 값들을 서로 비교해 합치는 과정에서 뒤 배열의 값이 더 크다면 그러한 개수만큼 저장을 하고 앞 배열의 값을 배치할 때 해당 값이 원래 위치했던 인덱스의 결과를 증가시키면 되는 것이다.

이러한 해결 방법으로 작성한 코드는 인덱스 바이너리 아래와 같다.

위 방식으로 문제를 해결할 수는 있었지만, 시간복잡도는 O(NlogN) 으로 (N 은 주어진 nums 배열의 크기, 최대 10만) 수행시간이 생각보다 길어 다른 방식을 고민하게 되었다. 이 문제에서 nums 배열의 크기는 최대 10만이고, 배열에 저장되는 값들은 -1만 ~ 1만 의 값을 가진다. 즉 O(NlogN) 보다는, O(NlogM) 이 된다면 (이 때 M 은 배열에 저장되는 값의 범위, 이 문제에서는 최대 2만) 수행시간을 조금 더 단축할 수 있을 것으로 생각했다.

이 때, logN 을 logM 으로 바꿀 수 있는 방법으로 부분합을 생각했고, 부분합을 업데이트하는 횟수가 많아 세그먼트 트리보다는 펜윅 트리를 생각했다. 펜윅 트리에 대한 자세한 설명은 아래 Reference 를 참고하면 되고, 간단하게 얘기하면 값들의 업데이트가 많을 때 부분합을 구하고 업데이트하는 것의 시간복잡도가 모두 O(logN) 인 자료구조이다.

펜윅 트리는 기본적으로 인덱스를 사용해 인덱스 ? ~ ? 까지의 합을 저장하는 방식으로 사용하는데, 이 문제에서는 인덱스 대신 nums 배열의 값들을 사용했고 값이 -1만 ~ 1만의 범위를 가지기 때문에, Offset 으로 10001 을 더해서 사용했다. 트리에 저장되는 값들은 해당 값이 등장한 횟수이다.

펜윅 트리를 사용해 작성한 코드는 아래와 같다. 수행시간도 생각한 대로 Merge Sort 를 사용한 방식보다 빨랐고, 펜윅 트리의 Update 나 Sum 을 구하는 코드가 모두 간단하기 때문에 코드도 간단하게 작성할 수 있었다.

위 코드를 로직에만 집중하도록 간단하게 모듈화를 조금 더 하자면 다음과 같이 작성할 수 있을 것 같다.

Count of smaller elements on right side of each element in an Array using Merge sort - GeeksforGeeks

A Computer Science portal for geeks. It contains well written, well thought and well explained computer science and programming articles, quizzes and practice/competitive programming/company interview Questions.

펜윅 트리 (바이너리 인덱스 트리)

블로그: 세그먼트 트리 (Segment Tree) 에서 풀어본 문제를 Fenwick Tree를 이용해서 풀어보겠습니다. Fenwick Tree는 Binary Indexed Tree라고도 하며, 줄여서 BIT라고 합니다. Fenwick Tree를 구현하려면, 어떤 수 X

인덱스 바이너리

아래 문제는 코딜리티에서 제공하는 Binary Gap의 문제입니다🧑🏻‍💻

문제 제시

A binary gap within a positive integer N is any maximal sequence of consecutive zeros that is surrounded by ones at both ends in the binary representation of N.

For example, number 9 has binary representation 1001 and contains a binary gap of length 2. The number 529 has binary representation 1000010001 and contains two binary 인덱스 바이너리 gaps: one of length 4 and one of length 3. The number 20 has binary representation 10100 and contains one binary gap of length 1. The number 15 has binary representation 1111 and has no binary gaps. The number 32 has binary representation 100000 and has no binary gaps.

Write a function:

that, given a positive integer N, returns the length of its longest binary gap. The function should return 0 if N doesn't contain a binary gap.

For example, given N = 1041 the function should return 5, because N has binary representation 10000010001 and so its longest binary gap is of length 5. Given N = 32 the function should return 0, because N has binary representation '100000' and thus no binary gaps.

Write an efficient algorithm for the following assumptions:

인덱스 바이너리

B-tree는 인덱스를 이루고 있는 자료구조의 일종이다.

B-tree에서 'B'는 정확히 어떤 의미라고 밝혀진 바는 없다. 아마 'Balanced'를 의미하는 'B'가 아닐까라는 추측만 있다.

MySQL의 DB engine인 InnoDB는 B+tree로 이뤄져있는데, B-tree의 확장된 개념이다.

먼저 B -tree를 살펴보자.

트리 구조의 우위성

트리 구조는 꼭 데이터베이스에 한정하지 않더라도 시스템 세계에서는 데이터를 유지하기 위해 자주 사용하는 구조이다. '탐색' 시, 단시간 내에 실행할 수 있기 때문이다.

B-tree의 핵심은 데이터가 정렬된 상태로 유지되어 있다는 것이다.

그림에 표시된 사각형으로 표시된 한 개 한 개의 데이터를 '노드(Node)'라고 한다.

가장 상단의 노드를 '루트 노드(Root Node)', 중간 노드들을 '브랜치 노드(Branch Node)', 가장 아래 노드들을 '리프 노드(Leaf Node)'라고 한다.

출처: http://www.btechsmartclass.com/data_structures/b-trees.html

B-tree는 Binary search tree와 유사하지만, 한 노드 당 자식 노드가 2개 이상 가능하다.

key 값을 이용해 찾고자 하는 데이터를 트리 구조를 이용해 찾는 것이다.

왜 B-tree는 빠른가

B-tree의 장점 한 가지는 '어떤 값에 대해서도 같은 시간에 결과를 얻을 수 있다'인데, 이를 '균일성'이라고 한다.

위의 예시에서 리프노드에 있는 '15'나 '28'을 찾는 시간은 동일할 것이다.(트리 높이가 다른 경우, 약간의 차이는 있겠지만 O(logN)이라는 시간 복잡도를 구할 수 있다.)

만약 선형탐색일 경우 어떨까?

리프노드에 있는 값들만 따져보면,

[1, 3, 7, 15, 21 . 85, 89, 97]

'15', '28'을 찾기 위해서는 배열을 하나씩 체크하는 수 밖에 없고 시간은 더욱 소요된다. (시간복잡도 : O(n))

B-tree는 균형트리

'균형 트리'란 루트로부터 리프까지의 거리가 일정한 트리 구조를 뜻하는 것으로, 트리 중에서 특히 성능이 안정화 되어있다.

그러나, B-tree 처음 생성 당시는 균형 트리이지만 테이블 갱신(INSERT/UPDATE/DELETE)의 반복을 통해 서서히 균형이 깨지고, 성능도 악화된다.

어느 정도 자동으로 균형을 회복하는 기능이 있지만, 갱신 빈도가 인덱스 바이너리 높은 테이블에 작성되는 인덱스 같은 경우 인덱스 재구성을 해서 트리의 균형을 되찾는 작업이 필요하다.

데이터양에 비례해 효과 상승

풀 스캔이 테이블의 크기에 비례하는 형태로 실행 시간이 늘어가는데에 비해 인덱스를 사용한 경우 실행 시간의 저하는 보통 원만한 곡선을 그리게 된다.

B+tree란?

출처: 위키피디아

B+tree는 B-tree의 확장개념으로, B-tree의 경우, internal 또는 branch 노드에 key와 data를 담을 수 있다. 하지만, B+tree의 경우 브랜치 노드에 key만 담아두고, data는 담지 않는다. 오직 리프 노드에만 key와 data를 저장하고, 리프 노드끼리 Linked list로 연결되어 있다.

B+tree의 장점

1. 리프 노드를 제외하고 데이터를 담아두지 않기 때문에 메모리를 더 확보함으로써 더 많은 key들을 수용할 수 있다. 하나의 노드에 더 많은 key들을 담을 수 있기에 트리의 높이는 더 낮아진다.(cache hit를 높일 수 있음)

2. 풀 인덱스 바이너리 스캔 시, B+tree는 리프 노드에 데이터가 모두 있기 때문에 한 번의 선형탐색만 하면 되기 때문에 B-tree에 비해 빠르다. B-tree의 경우에는 모든 노드를 확인해야 한다.

InnoDB에서 사용된 B+tree

출처: https://blog.jcole.us/2013/01/10/btree-index-structures-in-innodb/

InnoDB에서 B+tree는 단순하게 설명한 B+tree보다 더 복잡하게 구현된 것 같다.

같은 레벨의 노드들끼리는 Linked List가 아닌 Double Linked List를 사용했고, 자식 노드로는 Single Linked List로 연결되어있다.


0 개 댓글

답장을 남겨주세요