WHERE 절의 필드 순서가 MySQL의 성능에 영향을 미칩니까?

WHERE 절의 필드 순서가 MySQL의 성능에 영향을 미칩니까?

두 .type ★★★★★★★★★★★★★★★★★」userid（ ( ( ( ( ( ( ( ( ( 。

type에(0 는 s를 있습니다(0으로 1로 하다).typeuserid에, 「」, 「」, 「」, 「」, 「」, 「」를 가지는 레코드의 은, 「」, 「」, 「」로 되어 있습니다.userid습니니다

다음 중 하나의 쿼리가 다른 쿼리보다 빠르게 실행됩니까?

select * from table where type=1 and userid=5
select * from table where userid=5 and type=1

또한 두 필드가 모두 색인화되지 않은 경우 동작이 변경됩니까?

SQL은 절차적 언어가 아닌 선언적 언어로 설계되었습니다.따라서 쿼리 옵티마이저는 적용 방법을 결정할 때 where 절의 순서를 고려하지 마십시오.

SQL 쿼리 옵티마이저에 대한 다음 설명은 생략하겠습니다.저는 1년 전에 이런 식으로 썼어요(너무 재미있었어요!).최신 쿼리 최적화에 대해 자세히 알아보려면 O'Reilly의 Dan Tow의 SQL Tuning을 참조하십시오.

단순한 SQL 쿼리 최적화 도구에서 SQL 문은 먼저 관계형 대수 연산의 트리로 컴파일됩니다.이러한 연산에서는 각각 1개 이상의 테이블을 입력으로 사용하고 다른 테이블을 출력으로 생성합니다.검색은 데이터베이스에서 테이블을 읽는 순차적 검색입니다.정렬은 정렬된 테이블을 생성합니다.Select를 선택하면 선택 조건에 따라 다른 테이블에서 행이 선택되는 테이블이 생성됩니다.프로젝트에서 다른 테이블의 특정 열만 있는 테이블을 생성합니다.Cross Product는 두 개의 테이블을 사용하여 각 행의 가능한 모든 쌍으로 구성된 출력 테이블을 생성합니다.

SQL SELECT 절은 관계형 대수 프로젝트로 컴파일되고 WHERE 절은 관계형 대수 Select로 변환됩니다.FROM 구는 하나 이상의 Join으로 변환되어 각각2개의 테이블을 받아들여1개의 테이블을 생성합니다.집합 합집합, 교차, 차이 및 멤버십과 관련된 다른 관계 대수 연산이 있지만, 단순하게 유지합시다.

이 트리는 정말 최적화가 필요합니다.예를 들어 다음과 같습니다.

select E.name, D.name 
from Employee E, Department D 
where E.id = 123456 and E.dept_id = D.dept_id

500개 부문에 5,000명의 종업원이 있는 경우 최적화되지 않은 트리를 실행하면 1명의 종업원과 1개의 부문(크로스 프로덕트)의 가능한 모든 조합이 맹목적으로 생성되어 필요한 조합만 선택할 수 있습니다.종업원 스캔에서는 5,000개의 레코드 테이블이 생성되고, 부서 스캔에서는 500개의 레코드 테이블이 생성되며, 두 테이블의 크로스 프로덕트에서는 250,000개의 레코드 테이블이 생성되며, E.id의 셀렉트에서는 250,000개의 레코드 테이블이 생성되어 원하는 레코드를 제외한 모든 레코드가 폐기됩니다.

[실제 쿼리 프로세서는 이러한 중간 테이블을 모두 메모리 내에 구현하지 않도록 합니다.]

따라서 쿼리 옵티마이저는 트리를 사용하여 다양한 최적화를 적용합니다.하나는 각 Select를 Select의 체인으로 분할하는 것입니다.이것을 「접속 정규 형식」이라고 부릅니다.그런 다음 개별 더 작은 선택 항목이 트리 내에서 이동되고 보다 효율적인 다른 관계 대수 연산을 형성하기 위해 다른 관계 대수 연산과 병합됩니다.

위의 예에서 옵티마이저는 먼저 Select on E.id = 123456을 값비싼 교차 제품 작업 아래로 밀어 넣습니다.즉, 크로스 프로덕트는 500개의 행(해당 직원과 1개 부서의 조합당 1개씩)만 생성합니다.그런 다음 최상위 수준 Select for E.dept_id = D.dept_id에서 499개의 불필요한 행을 필터링합니다.나쁘지 않은데요.

Employee's id 필드에 인덱스가 있는 경우 옵티마이저는 E.id = 123456의 Select와 Employee 스캔을 결합하여 빠른 인덱스 룩업을 구성할 수 있습니다.즉, 5,000 행이 아닌 하나의 Employee 행만 디스크에서 메모리로 읽힙니다.상황이 호전되고 있다.

마지막 주요 최적화는 Select on E.dept_id = D.dept_id를 Cross Product와 결합하는 것입니다.이것은 그것을 관계대수 Equijoin 연산으로 바꾼다.이것만으로는 별로 도움이 되지 않는다.그러나 Depart.dept_id에 인덱스가 있는 경우 Equijoin에 공급되는 하위 단계의 Sequential Scan of Department는 한 직원의 부서 레코드를 매우 빠르게 색인 조회할 수 있습니다.

최적화가 덜 되면 프로젝트 작업이 중단됩니다.쿼리의 최상위 레벨에 E.name 및 D.name만 필요하고 조건에 E.id, E.dept_id 및 D.dept_id가 필요한 경우 스캔 작업은 다른 모든 열과 함께 중간 테이블을 작성할 필요가 없으므로 쿼리 실행 시 공간을 절약할 수 있습니다.우리는 끔찍할 정도로 느린 쿼리를 두 개의 인덱스 룩업으로 바꿨고, 다른 것은 별로 없습니다.

첫 번째 질문으로 넘어가서 예를 들어 다음과 같습니다.

select E.name 
from Employee E 
where E.age > 21 and E.state = 'Delaware'

최적화되지 않은 관계 대수 트리가 실행되면 5,000명의 직원을 스캔하여 델라웨어에 있는 21세 이상의 126명의 직원을 생성합니다.쿼리 옵티마이저는 데이터베이스 내의 값도 대략적으로 파악하고 있습니다.E.state 열에 회사가 위치한 14개 주가 표시되고 E.age 분포에 대해 알 수 있습니다.따라서 먼저 두 필드 중 하나가 색인화되어 있는지 확인합니다.E.state의 경우 이 인덱스를 사용하여 쿼리 프로세서가 마지막으로 계산한 통계정보를 바탕으로 델라웨어에 있는 것으로 의심되는 소수의 종업원을 선별하는 것이 타당합니다.E.age만 해당될 경우 쿼리 프로세서는 전체 직원의 96%가 22세 이상이기 때문에 그럴 가치가 없다고 판단합니다.따라서 E.state가 인덱싱되면 쿼리 프로세서가 Select(선택)를 해제하고 E.state = 'Delaware(델라웨어)'를 Scan(스캔)과 병합하여 훨씬 효율적인 Index Scan(인덱스 스캔)으로 변환합니다.

이 예에서는 E.state 및 E.age에 인덱스가 없다고 합니다.결합된 선택 작업은 Employee의 순차적인 "Scan" 후에 수행됩니다.Select의 어떤 조건이 먼저 수행되는지 차이가 있습니까?아마 많이는 아닐 겁니다.쿼리 프로세서가 SQL 문에서 원래 순서를 그대로 두거나 좀 더 정교하게 예상 비용을 확인할 수 있습니다.통계에서 E.state = '델라웨어' 조건은 보다 선택성이 높아야 하므로 조건을 역전하고 먼저 이를 수행하므로 비교는 5,000이 아닌 126 E.age > 21에 불과하다는 것을 다시 알 수 있다.또는 문자열 동등성 비교가 정수 비교보다 훨씬 더 비싸다는 것을 깨닫고 순서를 그대로 둘 수도 있습니다.

어쨌든 이 모든 것은 매우 복잡하며 구문 조건의 순서가 달라질 가능성은 거의 없습니다.실제 성능 문제가 있고 데이터베이스 벤더가 조건 순서를 힌트로 사용하지 않는 한 걱정하지 않습니다.

대부분의 쿼리 옵티마이저는 조건이 표시되는 순서를 힌트로 사용합니다.만약 다른 모든 것이 같다면, 그들은 그 순서를 따를 것이다.

그러나 많은 것들이 이를 무시할 수 있습니다.

• 두 번째 필드에는 인덱스가 있고 첫 번째 필드에는 인덱스가 없습니다.
• 필드 2가 더 선택적이라는 것을 시사하는 통계가 있습니다.
• 두 번째 필드는 검색하기가 더 쉽습니다(varchar(max)대int)

따라서 (모든 SQL 최적화 질문에 해당됩니다) 성능 문제가 관찰되지 않는 한 (상상된) 성능이 아니라 명확성을 위해 최적화하는 것이 좋습니다.

당신의 작은 예에서는 그럴 수 없습니다.쿼리 옵티마이저는 올바른 작업을 수행해야 합니다.다음을 추가하면 확실히 확인할 수 있습니다.explain쿼리 맨 앞에 표시됩니다.MySQL은 결합 방법과 결합을 위해 검색해야 하는 행 수를 알려줍니다.예를 들어 다음과 같습니다.

explain select * from table where type=1 and userid=5

인덱스가 작성되지 않은 경우 동작이 변경될 수 있습니다.

언급URL : https://stackoverflow.com/questions/4035760/does-the-order-of-fields-in-a-where-clause-affect-performance-in-mysql