---------------------------------------------
Markus Winand는 SQL에 대한 통찰력을 제공하고 다양한 시스템이 SQL을 지원하는 방법을 modern-sql.com 에서 보여줍니다. 이전에 그는 use-the-index-luke.com 을 만들었는데, 지금도 활발하게 유지되고 있습니다. Markus는 winand.at 를 통해 강사, 연사 및 컨설턴트로 고용될 수 있습니다.
---------------------------------------------
You can upload a Korean translation of use-the-index-luke.com on your blog
Thank you from the bottom of my heart to author Makus Winand for allowing me.
These are translations that I use for studying by using a papago (google translate)
The translations may not be correct or there may be a typo.
I'd appreciate it if you could point it out in the comments.
---------------------------------------------
---------------------------------------------
use-the-index-luke.com 의 한글번역본을 블로그에 업로드 해도 된다고
허락해주신 Makus Winand 님께 진심으로 감사합니다.
이 번역본들은 제가 공부용도로 번역기(papago, google transrate)를 돌려서
번역한 내용들이라 맞지 않거나, 오타가 있을수 있습니다.
댓글로 지적해주시면 감사하겠습니다.
---------------------------------------------
https://use-the-index-luke.com/sql/where-clause/the-equals-operator/concatenated-keys
ㄴ
Concatenate Indexes (결합 인덱스)
데이터베이스가 기본키에대한 인덱스를 자동으로 만들더라도 키가 여러열로 구성된 경우
수동으로 개선할 수 있는 여지가 있습니다.
이 경우 데이터베이스는 모든 기본키 column에 인덱스를 작성합니다. concatenated index(연결 인덱스)라고도 부릅니다.
(multi column, comptite(다중 열, 복합) or combined index(결합 인덱스))
연결된 인덱스의 colmun 순서는 사용성에 큰영향을 미치므로 신중하게 선택해야 합니다.
시연을 위해 회사 합병이 있다고 가정해 보겠습니다.
다른 회사 직원들이 추가되어 EMPLOYEES 테이블에 직원이 10배로 늘어납니다.
한가지 문제가 생겼다면, EMPLOYEE_ID 가 두 회사 전부에서 유니크 하지 않다는 것입니다.
기본 키를 추가 식별자(예 : 보조 ID)로 확장해야 합니다.
따라서 새 기본 키에는 이전과 같은 EMPLOYEE_ID 와 고유성을 재정립 하기 위한 SUBSIDIARY_ID라는 두개의 열이 있어야 합니다.
따라서 새 기본키의 인덱스는 다음과 같이 정의됩니다.
--------------------------------------------
CREATE UNIQUE INDEX employees_pk ON
scott.employees (employee_id, subsidiary_id)
---------------------------------------------
특정 직원에 대한 쿼리는 전체 기본 키를 고려해야합니다. 즉, subsidiary_id column 도 고려해야합니다.
---------------------------------------------
SELECT first_name, last_name
FROM employees
WHERE employee_id = 123
AND subsidiary_id = 30
---------------------------------------------
쿼리가 전체 기본 키를 사용할 때마다 데이터베이스는 인덱스에 있는 column수에 관계 없이 INDEX UNIQUE SCAN을 사용할 수 있습니다.
그러나 예를 들어 자회사의 모든 직원을 검색할 때 키 column 중 하나만을 사용하면 어떻게 됩니까?
---------------------------------------------
SELECT first_name, last_name
FROM employees
WHERE subsidiary_id = 20
---------------------------------------------
실행 계획에 따르면 데이터베이스가 인덱스를 사용하지 않습니다. 대신 TABLE ACCESS FULL을 수행합니다. 그 결과
데이터베이스는 전체 테이블을 읽고 where 절에 대해 모든 행을 평가합니다.
실행 시간은 테이블 크기에 따라 증가합니다.
테이블이 10배 증가하면 TABLE ACCESS FULL이 10배더 오래 걸립니다.
이 작업의 위험은 소규모 개발환경에서는 충분히 빠른 경우가 많지만 프로덕션에서 심각한 성능 문제를 야기한다는 것입니다.
---------------------------------------------
Full Table Scan
database는 concatenated index의 단일 column을 임의로 사용할 수 없으므로 인덱스를 사용하지 않습니다. 인덱스 구조를 자세히 살펴보면 이를 알 수 있습니다.
---------------------------------------------
연결된 인덱스는 인덱싱된 데이터를 정렬된 목록에 유지하는 다른 인덱스와 마찬가지로 B-Tree 인덱스에 불과합니다.
데이터베이스는 인덱스 항목을 정렬하기 위해 인덱스 정의의 위치에 따라 각 열을 고려합니다. 첫 번째 열은 첫 번째 열 등에서 두 항목의 값이 동일한 경우에만 순서를 결정합니다.
---------------------------------------------
중요
연결된 인덱스는 여러 열에 걸쳐 있는 하나의 인덱스입니다.
---------------------------------------------
따라서 두 열 인덱스의 순서는 전화 디렉토리의 순서와 같습니다.
먼저 이름별로 정렬된 다음 이름별로 정렬됩니다. 즉, 구대의 열 인덱스는 두 번째 열에서만 검색을 지원하지 않습니다.
즉, 이름므로 전화번호부를 검색하는 것과 같습니다.
그림 2.1 Concatenated Index(연결 인덱스)
그림 2.1의 인덱스 예시본을 보면
subsidiary_id 가 20인 항목이 서로 붙어있지 않다는 사실을 보여줍니다.(빨간 글자)
리프 노드에 존재하지만 트리에 ID=20인 항목이 없는 것도 분명합니다.
따라서 트리는 이 쿼리에서 쓸모가 없습니다.
---------------------------------------------
Tip
인덱스를 시각화 하면 인덱스를 지원하는 쿼리를 이해하는 데 도움이 됩니다. 데이터베이스를 쿼리하여 인덱스 순서로 항목을 검색할 수 있습니다.
SELECT <INDEX COLUMN LIST>
FROM <TABLE>
ORDER BY <INDEX COLUMN LIST>
FETCH FIRST 100 ROWS ONLY
인덱스 정의와 테이블 이름을 쿼리에 넣으면 인덱스에서 샘플을 가져옵니다. 요청된 행이 중앙 위치에 모여있는지 스스로에게 질문하십시오.
그렇지 않으면 인덱스 트리가 해당 위치를 찾는데 도움을 줄 수 없습니다.
---------------------------------------------
물론 SUBSIDIARY_ID 쿼리 속도를 향상시키기 위해 다른 인덱스를 추가할 수 있습니다.
그러나 더 나은 해결책이 있습니다.
적어도 EMPLOYEE_ID 혼자 검색하는 것이 의미가 없다고 가정한다면 말입니다.
첫 번째 인덱스 열은 항상 검색에 사용할 수 있다는 사실을 활용할 수 있습니다.
다시 말하지만 이것은 전화번호부와 같습니다. 성으로 검색하기 위해 이름을 알 필요가 없습니다.
요령은 SUBDIARY_ID 가 첫번째 위치에 있도록 인덱스 열 순서를 반대로 하는 것 입니다.
CREATE UNIQUE INDEX EMPLOYEES_PK
ON EMPLOYEES (SUBSIDIARY_ID, EMPLOYEE_ID)
두 열은 함께 여전히 고유하므로 전체 기본키가 있는 쿼리는 계속 INDEX UNIQUE SCAN을 사용할 수 있지만 인덱스 항목의 순서는 완전히 다릅니다. SUBSIDIARY_ID가 기본정렬의 기준이 되었습니다. 이는 자회사에 대한 모든 항목이 인덱스에 연속적으로 있으므로 데이터베이스가 B-tree를 사용하여 위치를 찾을 수 있음을 의미합니다.
---------------------------------------------
중요
연결된 인덱스를 정의할 때 가장 중요한 고려 사항은 가능한 자주 사용할 수 있도록 열 순서를 선택하는 방법입니다.
---------------------------------------------
실행 계획은 데이터베이스가 "역방향" 인덱스를 사용하는지 확인합니다.
단독 SUBSIDIARY_ID 은 더 이상 고유하지 않으므로 데이터베이스는 일치하는 모든 항목을 찾기 위해 리프노드를 따라야 합니다.
따라서 INDEX RANGE SCAN 작업을 사용합니다.
---------------------------------------------
Plan hash value: 858421941
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1017 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 1 | 1017 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMPLOYEES_PK | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SUBSIDIARY_ID"=20)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
---------------------------------------------
일반적으로 데이터베이스는 선행(가장 왼쪽)열로 검색할 때 연결된 인덱스를 사용할 수 있습니다. 3개의 컬럼트로 구성된 인덱스는 첫 번째 컬럼을 검색할 때, 처음 두 컬럼을 함께 검색할 때, 모든 컬럼을 사용하여 검색할 때 사용할 수 있습니다.
two-인덱스 솔루션도 매우 우수한 select 성능을 제공하지만 단일 인덱스 솔루션이 더 좋습니다. 저장 공간을 절약할 뿐만 아니라 두 번째 인덱스에 대한 유지 관리 오버헤드도 절약합니다. 테이블의 인덱스가 적을수록 insert,delete,update 성능이 향상됩니다.
최적의 인덱스를 정의하려면 인덱스 작동 방식뿐만 아니라 응용 프로그램이 데이터를 쿼리하는 방식도 알아야 합니다.
즉, where 절에 나타나는 열 조합을 알아야 합니다.
따라서 어플리케이션의 액세스 경로에 대한 개요가 없기 때문에 외부 컨설턴트가 최적의 인덱스를 정의하는 것은 매우 어렵습니다.
컨설턴트는 일반적으로 하나의 쿼리만 고려할 수 있습니다. 인덱스가 다른 쿼리에 가져올 수 있는 추가 이점을 활용하지 않습니다.
데이터베이스 관리자는 데이터베이스 스키마를 알고 있지만 액세스 경로에 대한 깊은 통찰력이 없기 때문에 비슷한 위치에 있습니다.
기술 데이터베이스 지식이 비즈니스 도메인의 기능적 지식과 만나는 유일한 곳은 개발 부서입니다. 개발자는 데이터에 대한 감각이 있고 액세스 경로를 알고 있습니다.
많은 노력을 기울이지 않고도 전체 어플리케이션에 대해 최상의 이점을 얻을 수 있도록 적절하게 인덱스를 생성할 수 있습니다.
'use-the-index-luke' 카테고리의 다른 글
| 2.2 Functions (0) | 2023.06.26 |
|---|---|
| 2.1-3 Slow Indexes, Part II (0) | 2023.06.26 |
| 2.1-1 Primary keys (0) | 2023.06.19 |
| 2.1 The Equals Operator (0) | 2023.06.15 |
| 2장 WHERE 문 (0) | 2023.06.15 |
