데이터베이스 기술개념 - [북스터디] 데이터 베이스를 지탱하는 기술

1.데이터 베이스가 없으면 무엇이 곤란한가?

 

2. 인덱스로 고속 액세스 실현하기

인덱스의 개념

인덱스 : 리스트의 인덱스는 각 요소가 저장된 순서를 나타내는 숫자라면, 데이터베이스의 인덱스는 찾고자하는 레코드의 위치를 빠르게 탐색하기 위한 별도의 자료구조(B tree, Hash Table)이다. 데이터베이스는 Update문을 실행시킬때, record와 함께 index도 업데이트 하여 1가지 이상의 업데이트 행위를 함.

 

인덱스의 기본 원리 : 인덱스의 기본적인 원리는 대규모 데이터 집합에서 특정 데이터를 빠르게 찾기 위해 정렬된 상태를 유지하는 것입니다. 인덱스는 데이터베이스의 전체 데이터를 스캔하는 대신, 인덱스가 가리키는 특정 경로를 통해 데이터를 찾아 접근 시간을 단축합니다.

 

B-트리 인덱스

• BST의 이진트리에서 m개의 자식트리로 확장한 구조. SelfBalancing의 특징을 갖는다.
• 주요 파라미터
  • M : 각 노드의 최대 자녀노드수
  • M-1 : 각 노드의 최대 key개수
  • [M/2] : 각 노드의 최소 자녀 노드의 수
  • [M/2]-1 : 각 노드의 최소 key 수
• 자료구조: 각 노드는 M-1개의 키의 개수를 갖고, 이 키의 개수로 구간을 [0,1],[1,2]...[M-2],[M-2],[M-1] M개로 나누어 각 구간에 해당하는 숫자를 자녀노드로 보낸다. 그리고 자녀의 최소와 최대개수를 조절함으로 인해서 Sef Balancing한 특징을 유지한다.
  • 삽입 : Node에 M-1개까지 '정렬하여' 채운 후 Node의 개수가 M-1개를 넘으면 midean값을 기준으로 상위와 하위로 나누어 Node를 분리한다. 그리고 midean값은 부모 노드로 승격된다. 이러한 과정을 반복하다보면 자식노드의 레벨을 모두 동일하게 유지할 수 있다.
• 특징: 범위 검색에 효율적이며, 삽입, 삭제, 검색 작업을 로그 시간 내에 처리할 수 있습니다.

 

 

해시 인덱스

• 구조: 해시 테이블을 기반으로 구축되며, 각 키는 해시 함수를 통해 해시 값으로 변환됩니다.
• 특징: 주로 등가 검색에 사용됩니다. 해시 인덱스는 범위 검색에는 적합하지 않지만, 특정 값에 대한 접근은 매우 빠를 수 있습니다.

기타인덱스 : 풀 텍스트 인덱스, 공간 인덱스

 

 

 

인덱스의 선택과 사용

쿼리 유형: 인덱스는 특정 유형의 쿼리에 대해 최적화되어 있습니다. 예를 들어, 범위 검색이 많은 경우 B-트리 인덱스가 유리하고, 등가 검색이 주를 이룰 때는 해시 인덱스가 효율적입니다.

데이터 특성: 데이터의 크기, 분포, 갱신 빈도 등을 고려하여 적절한 인덱스를 선택합니다. 대용량 데이터에는 B-트리 같은 효율적인 검색 기능이 필요할 수 있습니다. 

성능과 오버헤드: 인덱스는 검색 성능을 향상시키지만, 데이터의 삽입, 삭제, 갱신 작업 시에는 추가적인 비용이 발생합니다. 따라서 성능 이점과 유지 비용을 균형 있게 고려해야 합니다.

cardinality가 높아서 레코드별로 구분이 가능한 칼럼을 인덱스로 사용하는게 효과적인다 (성별 x, 주민등록번호 O)

 

예를들어 배당금을 조회할때 companyId와 date칼럼을 index를 설정하지않고 조회한다면, 선형탐색을 시행하기때문에 매우 느릴 수 있음. 반면 index를 설정했을 때는 logn*2정도를 기대할 수 있음.

 

 

복수의 유니크 키 (Multiple Unique Keys)

• 정의: 복수의 유니크 키는 테이블 내 여러 개의 독립적인 유니크 키를 의미합니다. 각 키는 테이블의 다른 컬럼 또는 컬럼 그룹에 대해 유니크 제약 조건을 가집니다.

복합 유니크 키 (Composite Unique Key)

• 정의: 복합 유니크 키는 두 개 이상의 컬럼을 결합하여 그 조합이 유니크하도록 하는 키입니다. 이 키는 단일 컬럼의 값이 아니라 컬럼 조합의 고유성을 보장합니다.
• 인덱스 구성 : Each key within the B-tree node is a composite of both values (A, B).The primary sorting criterion is the first element of the tuple, "A". Within the nodes of the tree, when multiple keys have the same "A" value, these keys are then sorted by the second element, "B".

 

 

데이터 특성에 따른 인덱스 선택

1. 데이터 분포
   • 낮은 카디널리티: 데이터의 카디널리티(고유값의 수)가 낮은 경우, 해시 인덱스가 효과적일 수 있습니다. 해시 인덱스는 등가 검색에 최적화되어 있으며, 카디널리티가 낮은 경우 빠르게 결과를 찾을 수 있습니다.
   • 높은 카디널리티: 데이터의 카디널리티가 높은 경우 B-트리 인덱스가 유리합니다. B-트리는 키 값의 범위를 기반으로 하여 효율적으로 데이터를 검색할 수 있으며, 정렬된 데이터 접근에 용이합니다.
2. 쿼리 유형
   • 범위 검색: 범위 검색이 빈번하게 발생하는 경우 B-트리 인덱스가 적합합니다. 이 인덱스는 연속적인 값의 처리와 범위 기반 검색에 효과적입니다.
   • 정확한 일치 검색: 단일 값에 대한 일치 검색이 주를 이룰 때는 해시 인덱스가 더 빠를 수 있습니다.
3. 업데이트 빈도
   • 빈번한 업데이트: 데이터가 자주 변경되는 테이블의 경우, 인덱스 유지 관리 비용을 고려해야 합니다. 인덱스가 많거나 복잡할수록 데이터 수정, 삭제, 추가 시 오버헤드가 증가합니다.

데이터베이스 개발

인덱스 결정 및 설계

• 적절한 인덱스 선택: RDBMS는 자동으로 인덱스를 생성하거나 추천하지 않습니다. 개발자는 애플리케이션의 쿼리 패턴을 분석하고, 이에 기반해 어떤 컬럼에 인덱스를 생성할지 결정해야 합니다. 이 과정에서 데이터의 카디널리티, 쿼리의 종류(범위 검색, 등가 검색 등)를 고려해야 합니다.
• 인덱스 유형 결정: RDBMS는 여러 종류의 인덱스를 지원할 수 있지만, 어떤 유형의 인덱스(예: B-트리, 해시, 풀 텍스트, 공간 등)가 특정 상황에 가장 적합할지 결정하는 것은 개발자의 몫입니다.

. 성능 최적화

• 쿼리 성능 테스트: 개발자는 인덱스를 적용한 후에 실제 쿼리 성능 테스트를 수행하여 인덱스의 효과를 평가해야 합니다. 이는 개발 초기 단계에서 중요하며, 적절한 인덱스가 성능을 크게 향상시킬 수 있습니다.
• 성능 모니터링: 프로덕션 환경에서의 성능 모니터링을 통해 인덱스의 유용성을 지속적으로 검토하고 필요에 따라 조정합니다.

3. 인덱스 관리

• 인덱스 유지 관리: 인덱스의 조각화 및 비효율성을 관리하고, 정기적으로 인덱스를 재구성하거나 재구축하는 것은 데이터베이스의 성능을 유지하기 위해 필수적입니다.
• 비용-편익 분석: 인덱스는 성능을 향상시키지만, 유지 관리 비용과 추가 스토리지 비용이 발생합니다. 개발자는 이러한 비용과 이익을 균형 있게 고려하여 인덱스 전략을 수립해야 합니다.

4. 기술적 이해와 업데이트

• 데이터베이스 기능 이해: 사용하는 RDBMS의 인덱스 관련 기능과 최적화 기술을 이해하는 것이 중요합니다. 데이터베이스 버전 업데이트나 새로운 기능이 추가될 때 이를 적절히 활용할 수 있어야 합니다.
• 교육과 학습: 데이터베이스 기술은 끊임없이 발전하고 있습니다. 최신 기술 트렌드를 따라가고 새로운 인덱싱 방법을 학습하는 것도 개발자의 중요한 책임 중 하나입니다.

 

MariaDB를 사용하면서 인덱스 유형을 명시적으로 지정하지 않았다면, MariaDB는 기본적으로 가장 일반적인 인덱스 유형인 B-트리 인덱스를 사용합니다. 이는 대부분의 일반적인 쿼리 유형에 잘 작동하며, 범위 검색과 정렬, 정확한 일치 검색에 모두 효과적입니다. MariaDB에서 인덱스를 자동으로 관리하는 방식에 대해 좀 더 자세히 설명하겠습니다.

 

명시적 인덱스 생성

사용자가 특정 쿼리의 성능을 최적화하기 위해 추가 인덱스를 생성할 필요가 있는 경우, SQL 문을 사용하여 인덱스를 직접 만들 수 있습니다. MariaDB에서는 주로 B-트리 인덱스가 사용되지만, 특별한 요구 사항에 따라 다른 유형의 인덱스를 생성할 수도 있습니다.

 

 

최적화 전

초기 설정에서 signup_date 컬럼에 인덱스가 없다면, 데이터베이스는 이 쿼리를 수행하기 위해 customers 테이블의 모든 행을 스캔해야 합니다. 테이블에 많은 데이터가 있을 경우, 이는 매우 비효율적이며 쿼리 응답 시간이 길어집니다.

인덱스 추가

signup_date 컬럼에 인덱스를 추가하면, 데이터베이스는 이 날짜 범위에 맞는 데이터를 훨씬 더 빠르게 찾을 수 있습니다. 인덱스 생성 쿼리는 다음과 같습니다:

성능 검증

인덱스의 효과를 실제로 확인하기 위해, 인덱스 추가 전후의 쿼리 실행 계획을 EXPLAIN 문을 사용해 비교할 수 있습니다. 예를 들어:

 

 

데이터베이스 성능 최적화에서 적절한 인덱스 설정은 개발자와 데이터베이스 관리자의 주요 업무 중 하나입니다. 인덱스는 쿼리의 성능을 크게 향상시킬 수 있으며, 전체 데이터베이스 시스템의 효율성을 높이는 데 핵심적인 역할을 합니다. 여기에는 몇 가지 중요한 포인트가 포함됩니다:

인덱스의 중요성

1. 성능 향상: 인덱스는 데이터 검색 시간을 크게 단축시켜, 특히 대용량 데이터를 처리할 때 데이터베이스의 응답 시간을 개선합니다.
2. 자원 효율성 증대: 올바르게 인덱스를 설정하면, 데이터베이스가 필요 이상의 자원을 소모하는 것을 방지하여 전체 시스템의 부하를 줄일 수 있습니다.

인덱스 설정의 고려사항

• 쿼리 최적화: 인덱스는 자주 실행되는 쿼리의 컬럼에 대해 설정하여, 쿼리 성능을 집중적으로 향상시킬 수 있습니다.
• 데이터 변경 빈도: 데이터가 자주 변경되는 컬럼에 인덱스를 과도하게 설정하면, 인덱스 유지 비용으로 인해 성능 저하가 발생할 수 있습니다.
• 인덱스 관리: 인덱스는 지속적인 관리가 필요하며, 데이터의 변화와 쿼리 패턴의 변화에 따라 인덱스를 추가하거나 수정해야 할 수 있습니다.

개발자의 역할

• 인덱스 계획: 새로운 데이터베이스를 설계하거나 기존 시스템을 개선할 때, 인덱스 전략을 세우는 것이 필요합니다.
• 성능 모니터링 및 조정: 인덱스의 효과를 모니터링하고, 필요에 따라 인덱스를 조정하여 시스템의 성능을 지속적으로 유지 관리합니다.
• 기술 지식의 활용: 다양한 인덱스 유형과 RDBMS의 특징을 이해하고 이를 바탕으로 최적의 인덱스 전략을 수립합니다.

결론적으로, 적절한 인덱스 설정은 데이터베이스 성능 최적화의 핵심적인 부분이며, 개발자와 데이터베이스 관리자는 이를 통해 데이터베이스의 전반적인 성능과 사용자 경험을 크게 향상시킬 수 있습니다. 인덱스는 단순히 데이터베이스 구성 전략의 일부가 아니라, 전체 데이터베이스 성능 관리 전략에서 중요한 역할을 합니다.

 

SQL문 작동원리

• Select Where절이 A And/Or B로 검색하였을때 
  • A와 B가 별도의 인덱스인 경우 : A의 인덱스에서 A에 해당하는 레코드를 추출, B의 인덱스에서 B에 해당하는 레코드를 추출하여 two pointer을 사용하여 서로 1:1 비교하여 추가, 제외하여 산출
  • A와 B가 복합 인덱스인 경우 : A와 B는 하나의 인덱스 자료구조에 A순서로 정렬, 그 후 B순서로 정렬된 구조이기 때문에 logn
  • A와 B 모두가 인덱스가 아닌경우 : 선형탐색으로 A레코드와 B레코드 모두 탐색하고 two pointer을 사용하여 합병 혹은 제외 시행.

 

3. 테이블설계와 릴레이션

 

• "어플리케이션을 만들때 중요한 것은 바로 어떤 데이터 항목이 필요한가를 제대로 파악하는 것이다. 어떤 항목이 필요한지를 적절히 결정하는 데에는 데이터를 어떻게 사용하느냐라는 업무 요구사항이 명확하게 되어 있어야한다."

 

정규화의 필요성 : Anomalies, Locks and Concurrency, Disk Seek Time:

 

 

 

데이터베이스와 램에서의 시간복잡도 중요성 차이

 

• In RAM:
  • The difference between O(1)O(1)O(1) and O(log⁡n)O(\log n)O(logn) might be between a few hundred nanoseconds to a couple of microseconds, which is very small but can become noticeable in high-throughput, latency-sensitive applications (e.g., high-frequency trading systems).
• In Database:
  • The difference can be more substantial, especially when disk accesses are involved. The difference might be between a few milliseconds to several seconds, significantly impacting performance in large-scale applications, especially when frequent disk seeks are required.

 

데이터 엑세스 방식

sequential data 접근에 유리한 데이터베이스의 경우 각 레코드는 인접하게 저장되어 있다. 그러므로, 한 record row 내에서 다른 레코드 접근자체는 크게 부담되는 행위가 아니다는 것.

 

Insertion 

• an auto-increment field : there is no need to check for the existence of a value before insertion
• Unique key : 고유의 인덱스를 생성하여 유지보관함. look up in index for existence and insert data
• Foreign key : 고유의 인덱스를 생성하여 유지보관함. 

 

In row-based storage systems, which are typical for traditional relational databases like MySQL, PostgreSQL, and SQL Server, each row's data is stored sequentially on the disk. Here's how it generally works:

• Sequential Storage: When a database uses row-based storage, all data pertaining to a single record (row) is stored together. This means that all the columns of a single row are stored in contiguous memory locations. If you query a record, the system retrieves the entire row from storage, which includes all the column data for that row.
• Implications: This model is advantageous for transactional systems where operations frequently involve the entire row, such as inserting a new record or retrieving a complete record. It's efficient for writing operations and is well-suited for OLTP (Online Transaction Processing) systems where access patterns typically involve rows.
• Access Time: Accessing a single record generally involves reading a continuous block of data from the disk, which includes all the columns for that row, even if only a few columns are needed. This can be inefficient if the application frequently accesses only a few columns.

 

Column-based Storage

In column-based storage systems, which are used by databases like Apache Cassandra, SAP HANA, and Google BigQuery, data is stored column-wise across all rows:

• Columnar Storage: Data from each column is stored together. That is, the first column for all rows is stored together, followed by the second column for all rows, and so on. This layout is particularly efficient for analytical querying and OLAP (Online Analytical Processing) systems.
• Implications: This storage model is beneficial when queries frequently need to compute aggregations across many rows but only for a limited number of columns. It dramatically improves performance for read-heavy operations that don't need all the data from a record.
• Access Time: Accessing data from a specific column across many rows can be very fast, as it avoids unnecessary I/O for columns that are not queried. However, reconstructing a single full record might require accessing multiple disparate locations on the disk if multiple columns are needed.