와일드카드(%)만 값으로 사용하는 SQL LIKE 성능

와일드카드(%)만 값으로 사용하는 SQL LIKE 성능

LIKE 키워드와 와일드카드를 값으로 사용하는 쿼리의 성능은 where 절이 전혀 없는 것과 비교하여 어떤지 궁금합니다.

"WHERE a LIKE '%'와 같은 where 절을 생각해 보십시오.이 값은 'a' 열의 가능한 모든 값과 일치합니다.where 절이 전혀 없는 것과 비교하면 어떻습니까?

이 질문을 하는 이유는 사용자가 검색할 값을 지정할 수 있는 필드가 있는 응용 프로그램이 있기 때문입니다.경우에 따라 사용자는 가능한 모든 결과를 원할 수 있습니다.현재 다음과 같은 단일 쿼리를 사용하고 있습니다.

SELECT * FROM TableName WHERE a LIKE ? AND b LIKE ?

'%' 및 '%' 값은 a 및 b에 대해 가능한 모든 값과 일치하도록 제공할 수 있습니다.응용프로그램에서 하나의 명명된 쿼리를 사용할 수 있기 때문에 편리합니다.이를 위한 성능 고려사항이 무엇인지 궁금합니다.쿼리 옵티마이저는 LIKE '%'를 단순히 모두 일치하도록 줄입니까?명명된 쿼리(준비된 문)를 사용하고 있기 때문에 답변에도 영향을 미칠 수 있다는 것을 알고 있습니다.저는 그 대답이 데이터베이스에 특정한 것일 가능성이 높다는 것을 깨달았습니다.구체적으로 Oracle, MS SQL Server 및 Derby에서 이 기능이 어떻게 작동하는지 설명합니다.

이에 대한 대안적인 접근 방식은 와일드카드를 입력하는 사용자를 기준으로 3개의 개별 쿼리를 사용하는 것입니다.

A은(는) 와일드카드 쿼리입니다.

SELECT * FROM TableName WHERE b LIKE ?

B는 와일드카드 쿼리입니다.

SELECT * FROM TableName WHERE a LIKE ?

A 및 B는 와일드카드입니다.

SELECT * FROM TableName

와일드카드 없음:

SELECT * FROM TableName WHERE a LIKE ? AND b LIKE ?

단일 쿼리를 사용하는 것이 가장 간단하고 쉽게 유지 관리할 수 있습니다.성능이 여전히 좋을 경우 하나의 쿼리만 사용하는 것이 좋습니다.

SQL Server는 일반적으로 다음과 같이 표시됩니다.

WHERE City LIKE 'A%'

그리고 그것을 취급합니다.

WHERE City >= 'A' AND City < 'B'

...적절한 경우 인덱스 검색을 사용합니다.저는 '일반적으로'라고 말합니다. 특정한 경우에는 이 단순화를 수행하지 못하는 것을 보았기 때문입니다.

다른 사용자가 다음을 수행하려는 경우:

WHERE City LIKE '%ville'

...그러면 색인을 찾는 것은 근본적으로 불가능할 것입니다.

하지만 다음과 같은 간단한 것이 있습니다.

WHERE City LIKE '%'

다음과 동등한 것으로 간주됩니다.

WHERE City IS NOT NULL

DBMS에서 제공하는 쿼리 분석(예: MySQL, MS SQL 또는 다른 방법 중 하나 사용)을 사용할 수 있습니다.EXPLAIN PLAN FOR쿼리가 실행되는 방식을 확인합니다.

저는 이것에 대한 교과서적인 답이 있기를 바랐는데, 데이터베이스 유형에 따라 크게 다를 것으로 보입니다.대부분의 응답은 테스트를 실행해야 한다는 것을 나타냈기 때문에 정확히 그렇게 했습니다.

제 애플리케이션은 주로 Derby, MS SQL 및 Oracle 데이터베이스를 대상으로 합니다.더비는 임베디드 방식으로 운영할 수 있고 설치가 쉽기 때문에 먼저 성능을 테스트했습니다.결과는 놀라웠습니다.저는 꽤 큰 테이블에 대해 최악의 시나리오를 테스트했습니다.저는 테스트를 1000번 실행하고 결과를 평균했습니다.

쿼리 1:

SELECT * FROM TableName

쿼리 2(a=dircuit" 및 b=dircuit 값 포함):

SELECT * FROM TableName WHERE a LIKE ? AND b LIKE ?

쿼리 1 평균 시간: 178ms

쿼리 2 평균 시간: 181ms

따라서 더비 경기의 성과는 두 쿼리 간에 거의 같습니다.

는 DBMS의 가치를 입니다.LIKE '%'쿼리를 실행하기도 전에 절을 입력합니다.DB2/z가 실행 계획에서 이러한 작업을 수행하는 것을 본 적이 있다고 확신합니다.

준비된 문은 실행 엔진에 도달하기 전에 실제 SQL로 변환되어야 하므로 차이가 없습니다.

그러나 모든 최적화 질문과 마찬가지로 측정은 추측하지 마십시오!DBA는 실제 데이터(시간에 따라 변경됨)를 기반으로 DBMS를 지속적으로 조정하기 때문에 존재합니다.최소한 적절한 정적 데이터를 사용하여 모든 변형에 대해 시간을 두고 실행 계획을 파악해야 차이가 있는지 확인해야 합니다.

저는 다음과 같은 질문을 알고 있습니다.

select c from t where ((1 = 1) or (c = ?))

는 실행 전에 전체 where 절을 제거하도록 최적화되어 있습니다(어쨌든 DB2 및 사용자가 묻기 전에). where 절의 효과를 제거하면서 매개 변수 자리 표시자를 유지해야 하는 경우(BIRT를 Javascript와 함께 사용하여 와일드카드 쿼리를 수정함).

Derby는 실제 사용된 쿼리 계획을 검토하는 도구도 제공하므로 Derby를 사용하여 실험을 실행하고 Derby가 선택한 쿼리 계획을 살펴볼 수 있습니다.더비는 -Dderby.language로 운영할 수 있습니다.logQueryPlan=true를 입력하면 Derby가 derby.log에 쿼리 계획을 기록하거나, 다음과 같이 RUNTIMESTATISTICS 기능을 사용할 수 있습니다. http://db.apache.org/derby/docs/10.5/tuning/ctundepth853133.html

더비가 사전에 A LIKE '%'를 제거할지는 확신할 수 없지만, 그 조항의 존재로 인해 실행 속도가 크게 둔화되지는 않을 것이라고 생각합니다.

ALIKE '%' 절이 있거나 없는 환경에서 실제 쿼리 계획 출력을 확인해 보는 것이 매우 흥미롭습니다.

Oracle 10gR2는 이 상황에 대해 특별한 최적화를 수행하는 것으로 보이지 않지만 LIKE '%'가 null을 제외한다는 것을 인식합니다.

create table like_test (col1)
as select cast(dbms_random.string('U',10) as varchar2(10))
from dual
connect by level <= 1000
/
insert into like_test values (null)
/
commit
/

exec dbms_stats.gather_table_stats(user,'like_test')

explain plan for
select count(*)
from   like_test
/
select plan_table_output from table(dbms_xplan.display)
/
explain plan for
select count(*)
from   like_test
where  col1 like '%'
/
select plan_table_output from table(dbms_xplan.display)
/
explain plan for
select count(*)
from   like_test
where  col1 is not null
/
select plan_table_output from table(dbms_xplan.display)
/

주는 것...

Plan hash value: 3733279756

------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |           |     1 |            |          |
|   2 |   TABLE ACCESS FULL| LIKE_TEST |  1001 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------

그리고...

Plan hash value: 3733279756

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |    10 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |           |     1 |    10 |            |          |
|*  2 |   TABLE ACCESS FULL| LIKE_TEST |  1000 | 10000 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("COL1" LIKE '%')

그리고...

Plan hash value: 3733279756

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |    10 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |           |     1 |    10 |            |          |
|*  2 |   TABLE ACCESS FULL| LIKE_TEST |  1000 | 10000 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("COL1" IS NOT NULL)

TABLE ACCESS FULL 라인의 카디널리티(행)를 기록합니다.

LIKE 서술어의 구조와 테스트할 필드에 따라 전체 테이블 검색이 필요할 수 있습니다.의미론적으로 '%'는 전체 테이블 검색을 의미할 수 있지만 SQL Server는 쿼리에 대해 내부적으로 모든 종류의 최적화를 수행합니다.질문은 다음과 같습니다.SQL Server는 '%'로 구성된 LIKE 서술어를 최적화하고 WHERE 절에서 제외합니까?

제가 논의에서 누락되었다고 생각하는 한 가지 측면은 OP가 준비된 성명서를 사용하기를 원한다는 사실입니다.문을 준비할 때 데이터베이스/최적화 도구는 다른 사람들이 언급한 단순화를 해결할 수 없으므로 최적화를 수행할 수 없습니다.a like '%'실제 값은 준비 시점에 알 수 없기 때문입니다.

따라서:

• 준비된 문을 사용할 때는 4개의 다른 문(0, a만, b만, 둘 다)을 사용하고 필요할 때는 적절한 문을 사용합니다.
• 하나의 진술만 고수할 때 준비된 진술을 사용하지 않을 때 더 나은 성과를 얻을 수 있는지 확인합니다(비록 '빈' 조건을 포함하지 않는 것이 꽤 쉽겠지만).

열에 null이 아닌 빈 값이 있으면 어떻게 합니까?쿼리가 일치할 수 있습니다.

실제 응용프로그램에 대한 쿼리인 경우 대부분의 최신 SQL 데이터베이스의 무료 텍스트 인덱싱 기능을 사용해 보십시오.성능 문제는 중요하지 않게 됩니다.

if (A B) search a both (A) search both (A) search both (A) search both (B) search both 그들이 아무것도 지정하지 않았다고 사용자에게 말합니다.

유지 관리하기에는 사소한 것이며 LIKE 연산자에 대해 가정하는 대신 훨씬 쉽게 이해할 수 있습니다."A에 대한 검색에서 x를 찾았습니다." 또는 "AB에 대한 검색에서 찾았습니다."라는 결과를 표시할 때 UI에서 이 작업을 수행할 수 있습니다.."

당신이 설명하는 매개변수와 함께 준비된 문을 사용하는 것의 가치를 확신할 수 없습니다.그 이유는 쿼리 최적화 프로그램을 속여서 실행 계획을 준비하도록 할 수 있으며, 실행 계획은 매개 변수 중 '%'에 따라 완전히 틀릴 수 있습니다.

예를 들어, A열의 인덱스를 사용하여 실행 계획으로 문을 작성했지만 A열의 매개 변수가 '%'인 경우 성능이 저하될 수 있습니다.

"예: '%'를 유일한 술어로 사용하는 where 절은 nowhere 절과 정확히 동일하게 동작합니다.

언급URL : https://stackoverflow.com/questions/1604707/sql-like-performance-with-only-the-wildcard-as-a-value