이 최적화는 비용 모델을 전혀 기반으로 하지 않는다고 말할 수 없기 때문에 여기서는 전적으로 적절하지 않습니다. 그러나 최적화 프로그램이 조건자를 파생 테이블에 푸시하면 안되는 이유를 단 한 가지도 생각할 수 없기 때문에 비용이 들지 않는 나머지 최적화와 함께 여기에 나열하겠습니다. 요청을 고려하십시오.
SELECT *
FROM (
SELECT *FROM actor
) a
WHERE a.actor_id =1;
이 쿼리의 파생 테이블은 의미가 없으며 쿼리 중첩 수준 수를 줄여 제거해야 합니다. 하지만 지금은 그것을 무시하자. 데이터베이스가 위 쿼리 대신 다음 쿼리를 실행할 것으로 예상할 수 있습니다.
SELECT *
FROM (
SELECT *
FROM actor
WHERE actor_id =1) a;
그런 다음 다시 외부 요청을 제거할 수도 있습니다. UNION을 사용하면 더 복잡한 예를 얻을 수 있습니다 .
SELECT *
FROM (SELECT first_name, last_name,'actor' type
FROM actor
UNION ALL
SELECT first_name, last_name,'customer' type
FROM customer
) people
WHERE people.last_name ='DAVIS';
이 쿼리의 결과:
FIRST_NAME LAST_NAME TYPE
----------------------------
JENNIFER DAVIS actor
SUSAN DAVIS actor
SUSAN DAVIS actor
JENNIFER DAVIS customer
이제 데이터베이스 최적화 프로그램이 대신 다음과 같은 쿼리를 실행하면 좋을 것입니다.
SELECT *
FROM (SELECT first_name, last_name,'actor' type
FROM actor
WHERE last_name ='DAVIS'
UNION ALL
SELECT first_name, last_name,'customer' type
FROM customer
WHERE last_name ='DAVIS') people;
즉, 조건자를 파생 테이블에 푸시하고 거기에서 두 개의 UNION ALL 하위 쿼리로 푸시합니다 . 결국 ACTOR.LAST_NAME 열과 CUSTOMER.LAST_NAME 열 모두에 대한 인덱스가 있기 때문입니다 . 다시 말하지만, 이 변환은 아마도 대부분의 데이터베이스의 비용 추정을 기반으로 하지만 어떤 알고리즘을 사용하든 가능한 한 빨리 처리되는 튜플 수를 줄이는 것이 거의 항상 더 낫기 때문에 여전히 생각할 필요가 없다고 생각합니다. 그러한 변화가 나쁜 생각으로 판명된 경우를 알고 계시다면 귀하의 의견을 듣고 싶습니다! 나는 매우 관심을 가질 것입니다. 그렇다면 어떤 데이터베이스가 이를 수행할 수 있습니까? (그리고 그것은 매우 간단하고 중요하므로 대답은 다음과 같습니다: 모든 것)
DB2
단순 파생 테이블 예
ExplainPlan--------------------------------------------------
ID |Operation|Rows|Cost1| RETURN ||62| FETCH ACTOR |1 of 1(100.00%)|63| IXSCAN PK_ACTOR |1 of 200(.50%)|0PredicateInformation3- START (Q1.ACTOR_ID =1)
STOP (Q1.ACTOR_ID =1)
UNION을 사용한 파생 테이블 또한 그렇습니다:
ExplainPlan-----------------------------------------------------------------
ID |Operation|Rows|Cost1| RETURN ||202| UNION |2 of 1|203| FETCH CUSTOMER |1 of 1(100.00%)|134| IXSCAN IDX_CUSTOMER_LAST_NAME |1 of 599(.17%)|65| FETCH ACTOR |1 of 1(100.00%)|66| IXSCAN IDX_ACTOR_LAST_NAME |1 of 200(.50%)|0PredicateInformation4- START (Q1.LAST_NAME ='DAVIS')
STOP (Q1.LAST_NAME ='DAVIS')6- START (Q3.LAST_NAME ='DAVIS')
STOP (Q3.LAST_NAME ='DAVIS')
또한 두 경우 모두 파생 테이블(뷰)은 실제로 필요하지 않아 계획에서 제외되었습니다.
MySQL
단순 파생 테이블 예
ID TABLE TYPE KEY REF EXTRA
---------------------------------------1 actor const PRIMARY const
상수 값에 의한 일반적인 기본 키 액세스가 사용됩니다. UNION을 사용하여 파생된 테이블입니다 . 죄송합니다.
ID SELECT_TYPE TABLE TYPE KEY REF ROWS EXTRA
------------------------------------------------------------------1 PRIMARY ref const102 DERIVED actor ALL 2003 UNION customer ALL 599
계획의 수동 변환 결과:
ID SELECT_TYPE TABLE TYPE KEY REF ROWS EXTRA
--------------------------------------------------------------------------1 PRIMARY ALL 52 DERIVED actor ref idx_actor_last_name const33 UNION customer ref idx_last_name const1
이는 MySQL에서 복잡한 중첩 쿼리를 사용할 때 심각한 문제입니다!
신탁
단순 파생 테이블 예, 작동합니다.
---------------------------------------------------------------------------|Id|Operation|Name|Starts|E-Rows|A-Rows|---------------------------------------------------------------------------|0| SELECT STATEMENT ||1||1||1| TABLE ACCESS BY INDEX ROWID| ACTOR |1|1|1||*2| INDEX UNIQUE SCAN | PK_ACTOR |1|1|1|---------------------------------------------------------------------------PredicateInformation(identified by operation id):---------------------------------------------------2-access("ACTOR"."ACTOR_ID"=1)
그리고 중첩 수준의 수가 감소했습니다. UNION을 사용한 파생 테이블 도 작동합니다.
---------------------------------------------------------------------------------|Id|Operation|Name|E-Rows|---------------------------------------------------------------------------------|0| SELECT STATEMENT ||||1| VIEW ||4||2| UNION-ALL ||||3| TABLE ACCESS BY INDEX ROWID BATCHED| ACTOR |3||*4| INDEX RANGE SCAN | IDX_ACTOR_LAST_NAME |3||5| TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMER |1||*6| INDEX RANGE SCAN | IDX_CUSTOMER_LAST_NAME |1|---------------------------------------------------------------------------------PredicateInformation(identified by operation id):---------------------------------------------------4-access("LAST_NAME"='DAVIS')6-access("LAST_NAME"='DAVIS')
그러나 중첩 수준 수는 줄이지 않습니다. Id=1 "View"는 파생 테이블이 여전히 존재함을 보여줍니다. 이 경우 큰 문제는 아니며 약간의 추가 비용이 발생할 수 있습니다.
포스트그레SQL
단순 파생 테이블 예, 작동합니다.
QUERY PLAN
----------------------------------------------------SeqScan on actor (cost=0.00..4.50 rows=1 width=25)Filter:(actor_id =1)
그러나 PostgreSQL은 때때로 기본 키를 사용하여 단일 행을 조회하지 않고 대신 전체 테이블을 검색한다는 점에 유의하십시오. 이 경우 200행 × 행당 25바이트("너비")가 한 블록에 들어맞는데, 이렇게 작은 테이블에 액세스하기 위해 불필요한 I/O 작업을 생성하는 것 외에 인덱스 읽기를 귀찮게 하는 이유는 무엇일까요? UNION을 사용한 파생 테이블 예, 다음과 같이 작동합니다.
QUERY PLAN
-----------------------------------------------------------------------------------Append(cost=0.00..12.83 rows=4 width=45)->SeqScan on actor (cost=0.00..4.50 rows=3 width=45)Filter:((last_name)::text='DAVIS'::text)->IndexScan using idx_last_name on customer (cost=0.28..8.29 rows=1 width=45)IndexCond:((last_name)::text='DAVIS'::text)
이번에도 ACTOR.LAST_NAME 컬럼 의 인덱스를 사용하지 않고, CUSTOMER 테이블이 훨씬 크기 때문에 CUSTOMER.LAST_NAME 컬럼의 인덱스를 사용한다 .
내 희망은 이루어지지 않았습니다. MySQL 8.0.2는 아직 이 간단한 최적화를 완전히 지원하지 않습니다. 그러나 다른 사람들은 모두 지지합니다.
데이터 베이스
간단한 파생 테이블 푸시
UNION을 사용하여 파생 테이블 푸시
DB2 LUW 10.5
예
예
MySQL 8.0.2
예
아니요
오라클 12.2.0.1
예
예
포스트그레SQL 9.6
예
예
SQL 서버 2014
예
예
결론
여기에 제시된 목록은 완전하지 않습니다. 비용 최적화 프로그램이 포함되기 전에도 데이터베이스 구현이 어렵지 않은(또는 어렵지 않아야 하는) 다른 간단한 SQL 변환이 많이 있습니다. 이는 [데이터베이스에 대한] 불필요하고 추가 작업을 제거합니다 ( 이미 작성한 불필요하고 필수 작업 과 반대 ). 이는 다음을 수행하는 데 중요한 도구입니다.
어리석은 [개발자] 실수는 성능에 영향을 미치지 않았습니다. 오류는 피할 수 없으며, 프로젝트가 성장하고 SQL 쿼리가 더욱 복잡해짐에 따라 이러한 오류가 누적될 수 있습니다.
상위 SQL 쿼리에 포함되거나, 변환되거나, 부분적으로 삭제되거나 다시 작성될 수 있는 뷰 및 테이블 함수와 같은 복잡한 블록을 재사용하는 기능을 제공합니다.
이러한 기능은 포인트 2에 매우 중요합니다. 이들 없이는 재사용 가능한 SQL 구성 요소 라이브러리를 기반으로 정상적인 성능으로 4000행 SQL 쿼리를 생성하는 것이 매우 어려울 것입니다. PostgreSQL 및 MySQL 사용자는 실망스러울 정도로 인기 있는 두 가지 오픈 소스 데이터베이스는 상용 경쟁사인 DB2, Oracle 및 SQL Server에 비해 아직 갈 길이 멀습니다. DB2는 Oracle 및 SQL Server와 함께 거의 2위를 차지하며 최고의 성능을 발휘했습니다. 목.조금 뒤에.
GO TO FULL VERSION