2.1-3 Slow Indexes, Part II
---------------------------------------------
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/slow-indexes-part-ii
ㄴ
느린 인덱스, Part II
이전 섹션에서는 열 순서를 변경하여 기존 인덱스에서 추가 이점을 얻는 방법을 설명했지만 예제에서는 두 개의 SQL문만 고려했습니다.
그러나 인덱스를 변경하면 인덱싱된 테이블의 모든 쿼리에 영향을 미칠 수 있습니다.
이 섹션에서는 데이터베이스가 인덱스를 선택하는 방법을 설명하고 기존 인덱스를 변경할 때 발생할 수 있는 부작용을 보여줍니다.
채택된 EMPLOYEES_PK 인덱스는 자회사로만 검색하는 모든 쿼리의 성능을 향상시킵니다.
그러나 추가 검색 기준이 있는지 여부에 관계없이 SUBSIDIARY_ID로 검색하는 모든 쿼리에 사용할 수 있습니다.
즉, where 절의 다른 부분과 함께 다른 인덱스를 사용했던 쿼리에 인덱스를 사용할 수 있게 됩니다. 이 경우 사용가능한 액세스 경로가 여러 개인 경우 최상의 경로를 선택하는 것이 옵티마이저의 작업입니다.
---------------------------------------------
The Query Optimizer
---------------------------------------------
인덱스를 변경하면 불쾌한 부작용도 발생할 수 있습니다.이 예에서 합병이후 매우 느려진 것은 내부 전화번호부 응용 프로그램입니다.
첫 번째 분석에서는 속도 저하의 원인으로 다음 쿼리를 식별했습니다.
SELECT first_name, last_name, subsidiary_id, phone_number
FROM scott.employees
WHERE last_name = '73'
AND subsidiary_id = 30;
실행계획은 다음과 같습니다.
예 2.1 기본 키 인덱스가 수정된 실행계획
---------------------------------------------
---------------------------------------------------------------
|Id |Operation | Name | Rows | Cost |
---------------------------------------------------------------
| 0 |SELECT STATEMENT | | 1 | 30 |
|*1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 30 |
|*2 | INDEX RANGE SCAN | EMPLOYEES_PK | 40 | 2 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("LAST_NAME"='WINAND')
2 - access("SUBSIDIARY_ID"=30)
---------------------------------------------
실행 계획은 인덱스를 사용하며 전체 비용 값은 30입니다. 지금까지는 좋습니다. 그러나 우리가 방금 변경한 인덱스를 사용한다는 것은 의심스럽습니다. 특히 이전 인덱스 정의를 염두에 두고 있을 때 인덱스 변경이 성능 문제를 일으켰다고 의심할 충분한 이유가 있습니다.
where 절의 일부가 아닌 EMPLOYEE_ID 열에서 시작 했습니다.
쿼리는 이전 인덱스를 사용할 수 없었습니다.
추가 분석을 위해 변경 전과 후의 실행 계획을 비교하면 좋을 것입니다. 원래 실행 계획을 얻으려면 이전 인덱스 정의를 다시 배포하면 되지만 대부분의 데이터베이스는 특정 쿼리에 대한 인덱스 사용을 방지하는 더 간단한 방법을 제공합니다.
다음 예제에서는 해당 용도로 Oracle 최적화 프로그램 힌트를 사용합니다.
SELECT /*+ NO_INDEX(EMPLOYEES EMPLOYEES_PK) */
first_name, last_name, subsidiary_id, phone_number
FROM scott.employees
WHERE last_name = 'WINAND'
AND subsidiary_id = 30;
인덱스 변경 전에 사용된 것으로 추정되는 실행 계획은 인덱스를 전혀 사용하지 않았습니다.
---------------------------------------------
----------------------------------------------------
| Id | Operation | Name | Rows | Cost |
----------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 477 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 477 |
----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("LAST_NAME"='WINAND' AND "SUBSIDIARY_ID"=30)
---------------------------------------------
TABLE ACCESS FULL 작업은 전체 테이블을 읽고 처리해야 하지만 이 경우 인덱스를 사용하는 것보다 빠를 것 같습니다.
쿼리가 하나의 행에만 일치하기 때문에 이는 특히 이례적입니다.
인덱스를 사용하여 단일 행을 찾는 것이 전체테이블 스캔보다 휠씬 빨라야 하지만 이 경우에는 그렇지 않습니다. 인덱스가 느린 것 같습니다.
이러한 경우 번거로운 실행 계획의 각 단계를 거치는 것이 가장좋습니다.
첫번째 단계는 EMPLOYEE_PK 의 INDEX RANGE SCAN 입니다.
이 인덱스는 Last_Name column을 다루지 않습니다. INDEX RANGE SCAN 은
SUBSIDIARY_ID만 걸러낼 수 있습니다.
Oracle database는 이를 "Predicate Information" 영역(실행 계획의 "2"번 항목)에 이를 표시합니다.
여기에서 각 작업에 적용되는 조건을 볼 수 있습니다.
---------------------------------------------
TIP
부록 A, "실행 계획" 에서는 다른 데이터베이스에 대한 "실행 계획"을 찾는 방법을 설명합니다.
---------------------------------------------
operation ID 2(예 2.1 참조)번에 있는 INDEX RANGE SCAN 작업은 SUBSIDIARY_ID=30 인 값만 걸러냅니다.
즉, 인텍스 트리를 순회하며 SUBSIDIARY_ID이 30인 첫번째 항목을 찾습니다.
그다음 리프노트 체인을 따라 해당 자회사에 대한 다른 모든 항목을 찾습니다.
INDEX RANGE SCAN의 결과는 SUBSIDIARY_ID 조건을 충족하는 ROWID 목록입니다. 자회사 규모에 따라 몇 가지만 있거나 수백 개가 있을 수 있습니다.
그 다음 작업은 TABLE ACCESS BY INDEX ROWID 입니다.
Fetch 이전 단계에서 ROWID를 사용하여 테이블에서 행을 가져옵니다.
LAST_NAME column을 사용할 수 있게 되면 데이터베이스는 WHERE 절의 나머지 부분을 평가할 수 있습니다. 즉, 데이터베이스는 Last_name 필터를 적용하기 전에 SUBSIDIARY_ID=30에 대한 모든 ROW를 가져와야합니다.
SQL 문의 응답시간은 결과 set 크기(치ㅗ종 ROW 크기)가 아니라 특정 자회사의 지원 수에 따라 다릅니다. 자회사에 회원이 몇 명인 경우 INDEX RANGE SCAN은 더 나은 성능을 제공합니다. 허나 자회사(SUBSIDIARY)가 거대한 규모를 가지고 있을경우 TABLE ACCESS FULL 작업이 더 빠를 수도 있습니다.
(2장 WHERE 문 /2- Concatenated Keys/Full Table Scan 참조).
인덱스 조회가 원래 회사의 각 직원에 대해 많은 ROWID를 반환하고 데이터베이스는 개별적으로 가져와야하기 때문에 쿼리는 느립니다.
최상의 실행 계획을 선택하는 것은 테이블의 데이터 분포에 따라 달라지므로 옵티마이저는 데이터베이스 내용에 대한 통계를 사용합니다. 이 예에서는 자회사에 대한 직원 분포를 포함하는 히스토그램이 사용됩니다. 이를 통해 옵티마이저는 인덱스 조회에서 반환된 ROW수를 추정할 수 있습니다. 결과는 비용 계산에 사용됩니다.
---------------------------------------------
통계(Statistics)
비용 기반 최적화 프로그램은 테이블, COLUMN 및 인덱스에 대한 통계를 사용합니다. 대부분의 통계는 COLUMN 수준에서 수집됩니다 : 별개의 값의 수, 가장 작은 값(데이터 범위), 널 발생수 및 열 히스토그램 (데이터 분포). 테이블의 가장 중요한 통계값은 크기 (행 및 블록)입니다.
가장 중요한 인덱스 통계는 트리 깊이,
리프 노드 수, 고유 키 수 및 클러스터링 요소입니다(5장 "데이터 클러스터링" 참조).
옵티마이저는 이 값을 사용하여 where절의
predicate의 selectivity를 추정합니다.
(술어의 선택성을 추정합니다.)
---------------------------------------------
사용 가능한 통계가 없는 경우(예:삭제된 경우) 최적화 프로그램은 기본값을 사용합니다. Oracle 데이터베이스의 기본 통계는 선택도가 중간인 작은 인덱스를 제안합니다.
INDEX RANGE SCAN에서 40개의 행을 반환할 것으로 추정됩니다.
실행 계획은 Rows 열에 이 추정을 표시합니다.(예제 2.1 참조). 이 자회사에서 일하는 직원이 1000명이므로 분명히 이것은 총제적으로 과소평가된 것입니다.
올바른 통계를 제공하면 옵티마니저가 더 잘 작동합니다. 다음 실행 계획은 새로운 추정치를 보여줍니다 . INDEX RANGE SCAN작업으로 1000 rows를 감지했습니다.
결과적으로 후속 테이블 액세스에 대해 더 높은 비용 값을 계산했습니다.
---------------------------------------------
---------------------------------------------------------------
|Id |Operation | Name | Rows | Cost |
---------------------------------------------------------------
| 0 |SELECT STATEMENT | | 1 | 680 |
|*1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 680 |
|*2 | INDEX RANGE SCAN | EMPLOYEES_PK | 1000 | 4 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("LAST_NAME"='WINAND')
2 - access("SUBSIDIARY_ID"=30)
---------------------------------------------
비용 값 680은 FULL TABLE SCAN(477)을 사용하는 실행 계획의 비용 값보다 횔씬 높습니다.
따라서 옵티마이저는 자동으로 FULL TABLE SCAN을 하게 됩니다.
느린 인덱스의 이 예는 적절한 인덱싱이 최상의 솔루션이라는 사실을 숨겨서는 안 됩니다. 물론 LAST_NAME에 대한 검색은 LAST_NAME에 대한 INDEX 에서 가장 잘 지원됩니다.
CREATE INDEX emp_name ON employees (last_name)
새 인덱스를 사용하여 옵티마이저는 비용 값 3을 계산합니다.
예시 2.2 전용 인덱스가 있는 실행계획
---------------------------------------------
--------------------------------------------------------------
| Id | Operation | Name | Rows | Cost |
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 3 |
|* 2 | INDEX RANGE SCAN | EMP_NAME | 1 | 1 |
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SUBSIDIARY_ID"=30)
2 - access("LAST_NAME"='WINAND')
---------------------------------------------
인덱스 액세스는 옵티마이저의 추정에 따라 한행만 전달합니다. 따라서 데이터베이스는 테이블에서 해당 행만 가져와야 합니다.
이것은 FULL TABLE SCAN 보다 빠릅니다.
적절하게 정의된 인덱스는 여전히 원래의 FULL TABLE SCAN 보다 낫습니다.
예제 2.1와 예제 2.2. 두 실행 계획은 거의 동일합니다.
데이터베이스는 동일한 작업을 수행하고 옵티마이저는 비슷한 cost를 계산했지만
두 번째 계획이 휠씬 더 잘 수행됩니다.
INDEX RANGE SCAN의 효율성은 특히 테이블 액세스가 뒤따를 때 광범위하게 달라질 수 있습니다. INDEX를 사용한다고 해서 SQL문이 가능한 최선의 방법으로 자동 실행되는 것은 아닙니다.