2.4-2 Indexing SQL LIKE Filters

use-the-index-luke

2.4-2 Indexing SQL LIKE Filters

kim-jiyoung 2023. 7. 14. 20:28

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/where-clause/searching-for-ranges/like-performance-tuning

ㄴ 인덱싱 LIKE 필터

SQL LIKE 연산자는 일부 검색어가 효율적인 인덱스 사용을 방해하기 때문에 예기치 않은 성능 동작을 자주 발생시킵니다. 즉, 매우 잘 인덱실할 수 있는 검색어가 있지만 그렇지 않은 검색어가 있습니다. 모든 차이를 만드는 것은 와일드 카드 문자의 위치입니다.

다음 예에서는 %검색어 중간에 와일드 카드를 사용합니다.

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

SELECT first_name, last_name, date_of_birth

FROM employees

WHERE UPPER(last_name) LIKE 'WIN%D'

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

---------------------------------------------------------------
|Id | Operation                   | Name        | Rows | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT            |             |    1 |    4 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |    1 |    4 |
|*2 |   INDEX RANGE SCAN          | EMP_UP_NAME |    1 |    2 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(UPPER("LAST_NAME") LIKE 'WIN%D')
filter(UPPER("LAST_NAME") LIKE 'WIN%D')

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

LIKE필터 는 트리 순회 중에 첫 번째 와일드카드 앞의 문자만 사용할 수 있습니다.

나머지 문자는 검색된 인덱스 범위를 좁히지 않는 filter Predicate일 뿐입니다.

따라서 단일 LIKE 식은 두 가지 조건자 유형을 포함할 수 있습니다.

(1)access Predicate 로 첫 번째 와일드카드(%) 앞부분

/* access Predicate 가 WIN% 으로 검색 */

(2)firter Predicate로 다른 문자

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

Warning

PostgreSQL 데이터베이스의 경우 표현식을 액세스 조건자로

사용하려면 연산자 클래스(예: varchar_pattern_ops)를 지정해야 할 수 있습니다.

자세한 내용은 PostgreSQL 설명서의 "연산자 클래스 및 연산자 패밀리 LIKE"를 참조하십시오.

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

첫 번째 와일드카드 앞의 접두사가 더 선택적일 수록 검색된 인덱스 범위가 작아집니다. 그러면 INDEX 조회가 더 빨라집니다. 그림 2.4는 세 가지 다른표현을 사용하여 LIKE 관계를 보여줍니다. 세 가지 모두 동일한 행을 선택하지만 스캔된 인덱스 범위와 성능이 매우 다릅니다.

그림 2.4 다양한 LIKE 검색

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

전체 LIKE 식과 일치합니다. 나머지 17개는 가져오지만 버립니다. 두 번째 식에는 검색된 인덱스 범위를 두 행으로 좁히는 더 긴 접두사가 있습니다. 이 표현식을 사용하면 데이터베이스는 결과와 관련이 없는 하나의 추가 행만 읽습니다. 마지막 식에는 filter predicate가 전혀 없습니다.. 데이터베이스는 전체 LIKE 식과 일치하는 항목만 읽습니다.

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

중요한

첫 번째 와일드카드 앞 부분만 액세스 조건자로 사용됩니다.

나머지 문자는 검색된 인덱스 범위를 좁히지 않습니다.

일치하지 않는 항목은 결과에서 제외됩니다.

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

그 반대의 경우도 가능합니다. 즉, 와일드카드(%)로 시작하는 LIKE 표현식 입니다.

이러한 LIKE 표현식은 액세스 조건자로 사용할 수 없습니다. 데이터베이스는 access Predicate를 제공하는 다른 조건이 없는 경우 전체 테이블을 스캔해야합니다.

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

Tip

LIKE 앞에 와일드카드 있는 식(예:'%TERM') 을 사용하지 마십시오.

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

와일드 카드 문자의 위치는 적어도 이론적으로는 인덱스 사용에 영향을 미칩니다. 실제로 옵티마이저는 바인드 매개변수를 통해 검색어가 제공될 때 일반 실행 계획을 생성합니다. 이 경우 최적화 프로그램은 대부분의 실행에 선행 와일드 카드가 있는지 여부를 추측해야합니다.

LIKE 대부분의 데이터베이스는 바인드 매개변수로 조건을 최적화할 때 선행 와일드카드가 없다고 가정 하지만, 이 가정은 LIKE표현식이 전체 텍스트 검색에 사용되는 경우 잘못된 것입니다. 안타깝게도 LIKE 조건에 전체 텍스트 검색으로 태그를 지정하는 직접적인 방법은 없습니다.

" 전체 텍스트 LIKE 식 레이블 지정" BOX는 작동하지 않는 시도를 보여줍니다.

바인드 매개변수 없이 검색어를 지정하는 것이 가장 확실한 해결책이지만

최적화 오버헤드가 증가하고 SQL Injection 취약점이 발생합니다.

효과적이고 안전하며 이식 가능한 솔루션은 의도적으로 LIKE 조건을 난독화하는 것입니다.

"Combining Columns"에서 이에 대해 자세히 설명합니다.

ㄴhttps://use-the-index-luke.com/sql/where-clause/obfuscation/concatenation

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

" 전체 텍스트 LIKE 식 레이블 지정"

전체 텍스트 검색에 연산자를 사용할 때 LIKE 검색어에서 와일드카드를 분리할 수 있습니다.

WHERE text_column LIKE '%' || ? || '%'

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

PostgreSQL 데이터베이스의 경우 PostgreSQL은 LIKE 표현식에 바인드 매개변수를 사용할 때 선행 와일드 카드가 있다고 가정하기 때문에 문제가 다릅니다.

이 경우 PostgreSQL은 인덱스를 사용하지 않습니다. 표현식에 대한 인덱스 access 권한을 얻는 유일한 방법은 LIKE 실제 검색어를 옵티마이저에 표시하는 것입니다. 바인드 매개변수를 사용하지 않고 검색어를 SQL문에 직접 넣으면 SQL Injection 공격에 대해 다른 예방 조치를 취해야 합니다!

데이터베이스가 주요 와일드 카드에 대한 실행 계획을 최적화하더라도 성능이 여전히 부족할 수 있습니다. 이 경우 where 절의 다른 부분을 사용하여 데이터에 효율적으로 액세스할 수 있습니다.

"Index Filter Predicates Used Intentionally" 도 참조하십시오.

ㄴhttps://use-the-index-luke.com/sql/clustering/index-filter-predicates

다른 액세스 경로가 없는 경우 다음 독점 전체 텍스트 인덱스 솔루션 중 하나를 사용할 수 있습니다.

Oracle 데이터베이스는 cotains 키워드를 제공합니다.

Oracle Text Application Developer's Guide

ㄴhttps://docs.oracle.com/cd/E11882_01/text.112/e24435/toc.htm

think

LIKE 검색 시작 부분(%TERM)에 와일드 카드가 하나만 있는 검색을 어떻게 인덱싱할 수 있습니까?