아, 이거 정말 멋진 일이네요! Sakila 데이터베이스에는 FILM.RATING 열에 대한 CHECK 제약 조건이 있습니다 .
CREATE TABLE film (..RATINGvarchar(10) DEFAULT 'G',..
CONSTRAINT check_special_rating
CHECK (rating IN ('G','PG','PG-13','R','NC-17')),..);
진지하게, 데이터 무결성을 보장하려면 CHECK 제약 조건을 사용하십시오. 이를 추가하는 데 드는 비용은 매우 낮습니다. 예를 들어 PRIMARY , UNIQUE 또는 FOREIGN KEY 와 같은 다른 제한 사항보다 훨씬 적습니다 . 작동하는 데 인덱스가 필요하지 않기 때문에 사실상 "무료"로 얻을 수 있기 때문입니다. 하지만 최적화와 관련하여 흥미로운 뉘앙스가 있습니다! 다음 쿼리를 고려해보세요.
불가능한 술어
우리는 이미 불가능한 술어 , NOT NULL 제약 조건 (실제로는 특별한 종류의 CHECK 제약 조건 )을 접했지만 이것은 훨씬 더 멋집니다.
SELECT *
FROM film
WHERE rating ='N/A';
그러한 영화는 없으며, 있을 수도 없습니다. CHECK 제약 조건 이 삽입(또는 업데이트)을 방지하기 때문입니다. 다시 말하지만, 이는 아무것도 하지 말라는 명령으로 해석되어야 합니다. 이 요청은 어떻습니까?
CREATE INDEX idx_film_rating ON film (rating);SELECTcount(*)
FROM film
WHERE rating NOT IN ('G','PG','PG-13','R');
위의 색인 덕분에 색인을 빠르게 스캔하고 등급 = 'NC-17' 인 모든 영화를 세는 것만으로도 충분할 것입니다 . 왜냐하면 그것이 남은 유일한 등급이기 때문입니다. 따라서 쿼리는 다음과 같이 다시 작성되어야 합니다.
SELECTcount(*)
FROM film
WHERE rating ='NC-17';
열을 하나의 값과 비교하는 것이 4와 비교하는 것보다 빠르기 때문에 이는 인덱스에 관계없이 적용됩니다. 그렇다면 어떤 데이터베이스가 이를 수행할 수 있습니까?
DB2
불가능한 술어(등급 = 'N/A') 멋지네요!
ExplainPlan-----------------------------------
ID |Operation|Rows|Cost1| RETURN ||02| TBSCAN GENROW |0 of 0|0PredicateInformation2- RESID (1=0)
역술어(등급 = 'NC-17') 아니요...
ExplainPlan------------------------------------------------------------
ID |Operation|Rows|Cost1| RETURN ||342| GRPBY (COMPLETE)|1 of 210(.48%)|343| IXSCAN IDX_FILM_RATING |210 of 1000(21.00%)|34PredicateInformation3-SARGNOT(Q1.RATING IN ('G','PG','PG-13','R'))
ID=3 단계에서 인덱스를 사용하고 카디널리티가 정확하더라도 계획에 범위 조건자가 없고 "SARG" 조건자만 있으므로 전체 검색이 발생합니다. 자세한 내용은 Marcus Wynand의 리뷰를 참조하세요 . 술어를 수동으로 반전하고 다음을 얻음으로써 이를 입증할 수도 있습니다.
ExplainPlan------------------------------------------------------------
ID |Operation|Rows|Cost1| RETURN ||72| GRPBY (COMPLETE)|1 of 210(.48%)|73| IXSCAN IDX_FILM_RATING |210 of 1000(21.00%)|7PredicateInformation3- START (Q1.RATING ='NC-17')
STOP (Q1.RATING ='NC-17')
이제 원하는 범위 조건자가 생겼습니다.
MySQL
MySQL은 CHECK 제약 조건 구문을 지원 하지만 어떤 이유로 이를 시행하지 않습니다. 이 시도:
CREATE TABLE x (a INT CHECK (a !=0));
INSERT INTO x VALUES (0);
SELECT *FROM x;
그리고 당신은 얻을 것입니다 :
A-0
MySQL의 경우 0점(실제로 CHECK 제약 조건만 지원하면 안 되나요 ?)
신탁
불가능한 술어(등급 = 'N/A')
--------------------------------------------------------------|Id|Operation|Name|Starts|E-Rows|A-Rows|--------------------------------------------------------------|0| SELECT STATEMENT ||1||0||*1| FILTER ||1||0||*2| TABLE ACCESS FULL| FILM |0|89|0|--------------------------------------------------------------PredicateInformation(identified by operation id):---------------------------------------------------1-filter(NULL IS NOT NULL)2-filter("RATING"='N/A')
다시 말하지만, 매우 이상한 필터 NULL IS NOT NULL 은 FULL TABLE SCAN 을 차단하며 이는 계획에서 완전히 쉽게 제거될 수 있습니다. 하지만 적어도 작동합니다! 역술어(등급 = 'NC-17') 이런:
----------------------------------------------------------------------------|Id|Operation|Name|Starts|E-Rows|A-Rows|----------------------------------------------------------------------------|0| SELECT STATEMENT ||1||1||1| SORT AGGREGATE ||1|1|1||*2| INDEX FAST FULL SCAN| IDX_FILM_RATING |1|415|210|----------------------------------------------------------------------------PredicateInformation(identified by operation id):---------------------------------------------------2-filter((RATING'PG-13' AND RATING'R' AND RATING'PG' AND RATING'G'))
조건자는 반전될 수 없으며 카디널리티 평가는 매우 형편없습니다 . 또한 INDEX RANGE SCAN 대신 INDEX FAST FULL SCAN을 얻고 액세스 조건자 대신 필터 조건 자를 얻습니다 . 그러나 이것은 예를 들어 수동으로 조건자를 반전하여 얻어야 하는 것입니다.
------------------------------------------------------------------------|Id|Operation|Name|Starts|E-Rows|A-Rows|------------------------------------------------------------------------|0| SELECT STATEMENT ||1||1||1| SORT AGGREGATE ||1|1|1||*2| INDEX RANGE SCAN| IDX_FILM_RATING |1|210|210|------------------------------------------------------------------------PredicateInformation(identified by operation id):---------------------------------------------------2-access("RATING"='NC-17')
망할!
포스트그레SQL
Sakila 데이터베이스 의 PostgreSQL 버전 은 RATING 열에 대한 CHECK 제약 조건 대신 ENUM 유형을 사용합니다 . 대신 CHECK 제약 조건을 사용하여 테이블을 복제했습니다 . 불가능한 조건자(등급 = 'N/A')가 작동하지 않습니다.
QUERY PLAN
------------------------------------------------------SeqScan on film2 (cost=0.00..67.50 rows=1 width=385)Filter:((rating)::text='N/A'::text)
역방향 술어(등급 = 'NC-17')도 작동하지 않습니다.
QUERY PLAN
------------------------------------------------------------------Aggregate(cost=70.53..70.54 rows=1 width=8)->SeqScan on film2 (cost=0.00..70.00 rows=210 width=0)Filter:((rating)::text ALL ('{G,PG,PG-13,R}'::text[]))
쿼리가 더욱 복잡해지면 기본 키를 기반으로 테이블에서 반사 조인을 수행해야 할 수도 있습니다. 저를 믿으십시오. 이것은 복잡한 뷰를 구축하고 서로 연결할 때 매우 일반적인 관행이므로 데이터베이스가 이에 주의를 기울이도록 하는 것은 복잡한 SQL 코드를 최적화하는 데 중요한 부분입니다. 복잡한 예를 보여주지는 않겠습니다. 간단한 예만 들어도 충분합니다. 예를 들면 다음과 같습니다.
SELECT a1.first_name, a1.last_name
FROM actor a1
JOIN actor a2 ON a1.actor_id = a2.actor_id;
이는 JOIN 제거의 특별한 경우로 볼 수 있습니다 . 실제로 A2 에 대한 조인이 필요하지 않기 때문에 테이블 A1 만으로 필요한 모든 작업을 수행할 수 있습니다 . 다음으로 INNER JOIN 제거는 여기에 없는 FOREIGN KEY 가 있는 경우에만 제대로 작동합니다 . 그러나 ACTOR_ID 의 기본 키 덕분에 실제로 A1 = A2 임을 증명할 수 있습니다 . 어떤 의미에서 이것은 다시 전이적 폐쇄 입니다 . 더 나아가 테이블 A1 과 A2 의 열을 사용할 수도 있습니다 .
SELECT a1.first_name, a2.last_name
FROM actor a1
JOIN actor a2 ON a1.actor_id = a2.actor_id;
JOIN 제거 의 전형적인 경우에는 두 테이블이 모두 프로젝션되므로 더 이상 제거가 불가능합니다. 그러나 A1 = A2 임을 이미 입증했으므로 서로 바꿔 사용할 수 있으므로 쿼리가 다음과 같이 변환될 것으로 예상할 수 있습니다.
SELECT first_name, last_name
FROM actor;
어떤 DBMS가 이것을 할 수 있나요?
DB2
테이블 A1만 투영 예:
ExplainPlan------------------------------------------------
ID |Operation|Rows|Cost1| RETURN ||202| TBSCAN ACTOR |200 of 200(100.00%)|20
테이블 A1 및 A2 투영 ... 또한 예:
ExplainPlan------------------------------------------------
ID |Operation|Rows|Cost1| RETURN ||202| TBSCAN ACTOR |200 of 200(100.00%)|20
MySQL
테이블 A1만 투영 No.
ID TABLE REF EXTRA
-----------------------------------1 a1
1 a2 a1.actor_id Using index
테이블 A1 및 A2 투영 ... 또한 아니요
ID TABLE REF EXTRA
-----------------------------------1 a1
1 a2 a1.actor_id
완전 실망...
신탁
테이블 A1만 투영 예
--------------------------------------------|Id|Operation|Name|E-Rows|--------------------------------------------|0| SELECT STATEMENT ||||1| TABLE ACCESS FULL| ACTOR |200|--------------------------------------------
테이블 A1 및 A2 투영 예 다시
--------------------------------------------|Id|Operation|Name|E-Rows|--------------------------------------------|0| SELECT STATEMENT ||||1| TABLE ACCESS FULL| ACTOR |200|--------------------------------------------
포스트그레SQL
테이블 A1만 투영 아니요:
QUERY PLAN
--------------------------------------------------------------------HashJoin(cost=6.50..13.25 rows=200 width=13)HashCond:(a1.actor_id = a2.actor_id)->SeqScan on actor a1 (cost=0.00..4.00 rows=200 width=17)->Hash(cost=4.00..4.00 rows=200 width=4)->SeqScan on actor a2 (cost=0.00..4.00 rows=200 width=4)
테이블 A1 및 A2 투영 그리고 다시 아니오:
QUERY PLAN
---------------------------------------------------------------------HashJoin(cost=6.50..13.25 rows=200 width=13)HashCond:(a1.actor_id = a2.actor_id)->SeqScan on actor a1 (cost=0.00..4.00 rows=200 width=10)->Hash(cost=4.00..4.00 rows=200 width=11)->SeqScan on actor a2 (cost=0.00..4.00 rows=200 width=11)
SQL 서버
테이블 A1만 투영합니다 . 이상하게도 그렇지 않습니다! (그러나 저는 SQL Server 2014를 사용하고 있으므로 최신 버전에서는 이 문제가 해결되었을 수 있습니다. 확실히 업그레이드를 사용할 수 있습니다!)
|--HashMatch(InnerJoin, HASH:([a1].[actor_id])=([a2].[actor_id]))|--TableScan(OBJECT:([sakila].[dbo].[actor] AS [a1]))|--TableScan(OBJECT:([sakila].[dbo].[actor] AS [a2]))
요약
솔직히 말해서 이 최적화가 모든 데이터베이스에서 수행될 것으로 예상했지만 안타깝게도 매우 착각했습니다. JOIN 제거 와 함께 이는 가장 중요한 최적화 중 하나이므로 뷰 및 테이블 함수와 같은 재사용 가능한 부분에서 대규모 SQL 쿼리를 작성할 수 있습니다. 불행하게도 가장 일반적인 데이터베이스 5개 중 3개에서는 지원되지 않습니다.
GO TO FULL VERSION