5.1 Index filter Predicates Intentionally Used

use-the-index-luke 저자 Markus Winand

---------------------------------------------

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/clustering/index-filter-predicates

ㄴ의도적으로 사용된 인덱스 filter predicate

 

인덱스 filter predicate는 연결된 인덱스의 잘못된 열 순서로 인해 잘못된 인덱스 사용을 나타내는 경우가 많습니다. 그럼에도 불구하고 인덱스 filter predicate는 범위 검색 성능을 향상 시키는 것이 아니라 연속적으로 액세스되는 데이터를 그룹화하는 데에도 좋은 이유로 사용될 수 있습니다.

 

여기서 access predicate로 사용할 수 없는  Where 절 prediacte는 이 기술의 좋은 후보입니다.

----------------------------------------------------------

SELECT first_name, last_name, subsidiary_id, phone_number

  FROM employees

 WHERE subsidiary_id = ?

   AND UPPER(last_name) LIKE '%INA%'

----------------------------------------------------------

선행 와일드카드가 있는 LIKE 식은 인덱스 트리를 사용할 수 없습니다. 즉, LAST_NAME Indexing는 LAST_NAME indexing 또는 UPPER(last_name) Indexing에 관계없이 검색된 index 범위를 좁히지 않습니다. 따라서 이 조건은 Indexing에 적합하지 않습니다.

 

 

그러나 SUBSIDIARY_ID의 조건은 인덱싱에 적합합니다. SUBSIDIARY_ID가 이미 기본키의 인덱스에서 선행 열이기 때문에 새 인덱스를 추가할 필요가 없습니다.

----------------------------------------------------------

---------------------------------------------------------------
|Id | Operation                   | Name        | Rows | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT            |             |   17 |  230 |
|*1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |   17 |  230 |
|*2 |   INDEX RANGE SCAN          | EMPLOYEEs_PK|  333 |    2 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(UPPER("LAST_NAME") LIKE '%INA%')
   2 - access("SUBSIDIARY_ID"=TO_NUMBER(:A))

----------------------------------------------------------

위의 실행 계획에서 비용 값은 INDEX RANGE SCAN에서 INDEX ROWID 작업에 의한 후속 TABLE ACCESS까지 100배 상승합니다.

즉, 테이블 액세스로 인해 작업이 가장 많이 발생합니다. 그것은 사실 흔한 패턴이고 그 자체로는 문제가 되지 않습니다. 그럼에도 불구하고 이 쿼리의 전체 실행 시간에 가장 중요한 기여를 합니다.

 

데이터베이스는 단일 읽기 작업으로 모든 행을 가져올 수 있으므로 액세스한 행이 단일 테이블 블록에 저장된 경우 테이블 액세스가 병목 현상이 될 필요는 없습니다. 반대로 동일한 행이 여러 블록에 분산되어 있는 경우 모든 행을 검색하기 위해 데이터베이스가 많은 블록을 가져와야 하므로 테이블 액세스가 심각한 성능 문제가 될 수 있습니다. 즉, 액세스한 행의 물리적 분포에 따라 성능이 달라집니다. 즉, 행의 클러스터링에 따라 성능이 달라집니다.

----------------------------------------------------------

Note

인덱스 순서와 테이블 순서 간의 상관 관계는 성능 벤치마크, 이른바 인덱스 클러스터링 요인입니다.

----------------------------------------------------------

실제로 테이블의 행을 인덱스 순서에 맞게 재정렬하여 쿼리 성능을 향상시킬 수 있습니다. 그러나 테이블 행을 한 시퀀스에만 저장할 수 있기 때문에 이 방법은 거의 적용되지 않습니다. 즉, 하나의 인덱스에 대해서만 테이블을 최적화할 수 있습니다.

테이블을 최적화할 단일 색인을 선택할 수 있더라도 대부분의 데이터베이스는 이 태스크에 대한 기본 도구만 제공하므로 여전히 어려운 테스크입니다. 소위 행 순서화는 결국 다소 비현실적인 접근법입니다.

----------------------------------------------------------

The Index Clustering Factor

인덱스 클러스터링 요인은 두 개의 후속 인덱스 항목이 동일한 테이블 블록을 참조할 확률을 간접적으로 측정한 것입니다.

Optimizer는 TABLE ACCESS BY INDEX ROWID 연상의 비용 값을 계산할 때 이 확률을 고려합니다.

----------------------------------------------------------

이것이 바로 인덱싱의 두 번째 힘인 클러스터링 데이터입니다. 인덱스에 많은 열을 추가하여 잘 정의된 순서대로 자동으로 저장할 수 있습니다. 따라서 인덱스는 데이터를 클러스터링하기 위한 강력하면서도 간단한 도구입니다.

 

위 쿼리에 이 개념을 적용하려면 검색된 인덱스 범위를 좁히지 않더라도 where 절의 모든 열을 포함하도록 인덱스를 확장해야 합니다:

----------------------------------------------------------

CREATE INDEX empsubupnam ON employees
       (subsidiary_id, UPPER(last_name))

----------------------------------------------------------

열 SUBSIDIARY_ID는 첫 번째 인덱스열이므로 access predicate로 사용할 수 있습니다.

식 UPPER(last_name)는 LIKE 필터를 인덱스 filter predicate로 포함합니다. 대문자 표시를 인덱실하면 실행 중에 CPU 주기가 몇번 절약 되지만 LAST_NAME의 직선 인덱스도 작동합니다. 이에 대한 자세한 내용은 다음 섹션에서 확인할 수 있습니다.

----------------------------------------------------------

--------------------------------------------------------------
|Id | Operation                   | Name       | Rows | Cost |
--------------------------------------------------------------
| 0 | SELECT STATEMENT            |            |   17 |   20 |
| 1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES  |   17 |   20 |
|*2 |   INDEX RANGE SCAN          | EMPSUBUPNAM|   17 |    3 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("SUBSIDIARY_ID"=TO_NUMBER(:A))
       filter(UPPER("LAST_NAME") LIKE '%INA%')

----------------------------------------------------------

 

새 실행 계획에는 이전과 동일한 작업이 표시됩니다. 그럼에도 불구하고 cost value는 상당히 떨어졌습니다.

Predicate 정보에서 우리는 INDEX RANGE SCAN 동안

LIKE 필터가 이미 적용되었음을 알 수 있습니다.

LIKE 필터를 충족하지 않은 행은 즉시 삭제됩니다.

테이블 액세스에 더 이상 필터 술어가 없습니다.

즉, where 절을 충족하지 않는 행은 로드하지 않습니다.

 

두 실행 계획의 차이는 "Rows" 열에서 명확하게 볼 수 있습니다. 옵티마이저의 추정에 따르면 쿼리는 최종적으로 17개의 레코드와 일치합니다. 그럼에도 불구하고 첫번째 실행 계획의 인덱스 스캔은 333개의 행을 제공합니다. 그런 다음 데이터베이스 LIKE 필터를 적용하여 결과를 17개 행으로 줄이는 테이블에서 이 333개의 행을 로드해야 합니다. 두 번째 실행 계획에서 인덱스 액세스는 처음부터 해당 행을 제공하지 않으므로 데이터베이스는 TABLE ACCESS BY INDEX ROWID 작업을 17번만 실행하면 됩니다.

 

또한 열을 추가하면 index가 커지기 떄문에 INDEX RANGE SCAN 작업의 비용 값이 2에서 3으로 증가했습니다. 성능 향상을 고려할 때, 이것은 허용 가능한 절충안 입니다.

 

 

----------------------------------------------------------

Warning

filter Predicate 의 유일한 목적으로 새 인덱스를 도입하지 마십시오. 대신 기존 인덱스를 확장하고 유지 관리 작업을 맞게 유지합니다.

일부 데이터베이스에서는 기본키의 일부가 아닌 기본 키에 대한 색인에 열을 추가할 수도 있습니다.

----------------------------------------------------------

다음 애니메이션(GIF)은 두 실행 계획의 차이를 보여줍니다.

 

 

Figure 5.1 Intentional Index Filter-Predicates

----------------------------------------------------------

https://use-the-index-luke.com/static/intentional-filter-predicate.en.N5J1kRR3.gif

----------------------------------------------------------

 

이 사소한 예는 where 절에서 모든 열을 index화 하는

일반적인 지혜를 확인하는 것 같습니다. 그러나 이 "지혜"는 access pridicate로 사용할 수 있는 조건을 결정하는 열 순서의 관련성을 무시하므로 성능에 큰 영향을 미칩니다. 따라서 열 순서에 대한 결정을 절대 운에 맡겨서는 안 됩니다.

 

인덱스 크기는 특히 텍스트 열을 추가할 때 열 수에 따라 증가합니다. 물론 로그 확장성이 영향을 상당히 제한하더라도 더 큰 인덱스의 경우 성능이 향상되지는 않습니다. where 절에 언급된 모든 열을 인덱스에 추가하지 않고 인덱스 filter predicate만 사용하여 이전 실행 단계에서 데이터 볼륨을 줄이십시오.

 

 

----------------------------------------------------------

Tip

용어집 : Index filter predicates

예제로 설명된 인덱스 액세스 및 필터 술어

우발적인 Index filter predicate 의 영향이 입증됨

"anywhere" LIKE 검색이 액세스 술어가 아닌 이유

Oracle, Postgre에서 인덱스 필터 술어 발견SQL 및 SQL Server 실행 계획.

----------------------------------------------------------