기사를 이해하는 데 필요한 지식 수준: 데이터베이스 및 SQL에 대한 일반적인 이해, DBMS에 대한 실무 경험.
아마도 효과적인 SQL 쿼리를 작성하는 방법을 배울 수 있는 가장 중요한 것은 인덱싱일 것입니다. 그러나 두 번째는 많은 SQL 클라이언트가 데이터베이스에서 많은 "불필요하지만 필요한 작업"을 수행해야 한다는 지식입니다 . 내 말을 따라 반복하세요.
불필요하지만 꼭 필요한 작업
'불필요하지만 의무적인 일'이란 무엇인가요? Captain Obvious는 다음과 같이 말합니다.
불필요한
클라이언트 애플리케이션에 다음 데이터가 필요합니다.
특이한 것은 없습니다. 우리는 영화 데이터베이스(예: Sakila 데이터베이스 )를 사용하여 작업하고 있으며 모든 영화의 제목과 등급을 사용자에게 표시하려고 합니다. 다음 쿼리는 필요한 결과를 제공할 수 있습니다.
SELECT title, rating
FROM film
그러나 우리 애플리케이션(또는 ORM)은 대신 다음 쿼리를 실행합니다.
SELECT *
FROM film
결과적으로 우리는 무엇을 얻습니까? 추측하다. 우리는 쓸모없는 정보를 많이 받습니다:
오른쪽에서는 복잡한 JSON이 로드되는 것을 볼 수 있습니다.
디스크에서
캐시하다
유선으로
클라이언트를 기억하며
결국에는 [불필요하게] 버려졌습니다.
예, 우리는 이 정보의 대부분을 버립니다. 이 정보를 추출하기 위해 취한 모든 조치는 전혀 쓸모가 없는 것으로 판명되었습니다. 사실인가요? 사실인가요?
필수적인
그리고 지금 - 최악의 부분. 이제 최적화 프로그램이 많은 작업을 수행할 수 있지만 이러한 작업은 데이터베이스에 필수입니다. 데이터베이스는 클라이언트 애플리케이션에 이 데이터의 95%가 필요하지 않다는 것을 알 수 있는 방법이 없습니다. 그리고 이것은 가장 간단한 예일 뿐입니다. 여러 테이블을 연결한다고 상상해 보세요... 그렇다면 데이터베이스가 빠르다고 할까요? 아마도 당신이 생각하지 못했던 몇 가지 사항에 대해 알려 드리겠습니다. 물론 개별 요청의 실행 시간은 실제로 어떤 영향도 미치지 않습니다. 좋아, 1.5배 느리게 실행되었지만 우리는 이겨낼 수 있을 거야, 그렇지? 편의상? 때때로 이것은 사실입니다. 그러나 편의를 위해 항상 성능을 희생한다면 이러한 작은 것들이 추가되기 시작할 것입니다. 더 이상 성능(개별 요청 실행 속도)에 대해 이야기하지 않고 처리량(시스템 응답 시간)에 대해 이야기하면 해결하기 쉽지 않은 심각한 문제가 시작될 것입니다. 이때 확장성이 상실됩니다. 실행 계획(이 경우 Oracle DBMS)을 살펴보겠습니다.
--------------------------------------------------|Id|Operation|Name|Rows|Bytes|--------------------------------------------------|0| SELECT STATEMENT ||1000|166K||1| TABLE ACCESS FULL| FILM |1000|166K|--------------------------------------------------
다음과 비교:
--------------------------------------------------|Id|Operation|Name|Rows|Bytes|--------------------------------------------------|0| SELECT STATEMENT ||1000|20000||1| TABLE ACCESS FULL| FILM |1000|20000|--------------------------------------------------
SELECT 제목 대신 SELECT * 쿼리를 실행하면 등급이 데이터베이스에서 8배 더 많은 메모리를 사용합니다. 예상치 못한 일이 아니죠? 우리는 이런 일이 일어날 것이라는 것을 알고 있었습니다. 그러나 우리는 이 모든 데이터가 필요하지 않은 많은 요청에 대해 여전히 이에 동의합니다. 우리는 데이터베이스에 대해 불필요하지만 필수적인 작업을 생성하며 , 이는 계속해서 쌓여갑니다. 우리는 필요한 것보다 8배 더 많은 메모리를 사용합니다(물론 승수는 변경됩니다). 한편, 다른 모든 단계(디스크 I/O, 네트워크를 통한 데이터 전송, 클라이언트의 메모리 소비)에서는 문제가 정확히 동일하지만 건너뛰고 대신 살펴보겠습니다.
인덱스 사용
오늘날 대부분의 데이터베이스는 이미 인덱스 포함 개념을 높이 평가하고 있습니다 . 포함 인덱스 자체는 특별한 유형의 인덱스가 아닙니다. 그러나 "우연히" 또는 그렇게 의도했기 때문에 특정 쿼리에 대한 "특수 인덱스"로 판명될 수 있습니다. 다음 쿼리를 고려해보세요.
SELECT *
FROM actor
WHERE last_name LIKE 'A%'
구현 측면에서 예상치 못한 것은 없습니다. 이것은 간단한 요청입니다. 인덱스별로 범위를 보고 테이블에 액세스하면 완료됩니다.
-------------------------------------------------------------------|Id|Operation|Name|Rows|-------------------------------------------------------------------|0| SELECT STATEMENT ||8||1| TABLE ACCESS BY INDEX ROWID| ACTOR |8||*2| INDEX RANGE SCAN | IDX_ACTOR_LAST_NAME |8|-------------------------------------------------------------------
좋은 계획이죠? 음, 이것이 정말로 필요하다면, 아니오:
분명히 우리는 메모리 등을 낭비하고 있습니다. 대안으로 이 쿼리를 고려해 보겠습니다.
SELECT first_name, last_name
FROM actor
WHERE last_name LIKE 'A%'
그의 계획은 이렇습니다.
----------------------------------------------------|Id|Operation|Name|Rows|----------------------------------------------------|0| SELECT STATEMENT ||8||*1| INDEX RANGE SCAN| IDX_ACTOR_NAMES |8|----------------------------------------------------
쿼리의 모든 요구 사항을 충족하는 인덱스, 즉 커버링 인덱스의 존재 덕분에 테이블에 대한 액세스를 완전히 없앨 수 있었습니다. 그것은 중요한가? 그리고 어떻게! 이 접근 방식을 사용하면 일부 쿼리의 속도를 몇 배로 높일 수 있습니다(또는 일부 변경 후 인덱스가 더 이상 적용되지 않는 경우 몇 배로 속도를 늦출 수 있습니다). 포함 인덱스를 항상 사용할 수는 없습니다. 인덱스 비용을 지불해야 하며 인덱스를 너무 많이 추가하면 안 됩니다. 하지만 이 경우에는 모든 것이 분명합니다. 성능을 평가해 보겠습니다.
SET SERVEROUTPUT ON
DECLARE
v_ts TIMESTAMP;
v_repeat CONSTANT NUMBER :=100000;
BEGIN
v_ts := SYSTIMESTAMP;
FOR i IN 1..v_repeat LOOP
FOR rec IN (-- Наихудший вариант requestа: перерасход памяти ПЛЮС доступ к таблице
SELECT *
FROM actor
WHERE last_name LIKE 'A%')LOOP
NULL;END LOOP;END LOOP;
dbms_output.put_line('Оператор 1 : '||(SYSTIMESTAMP - v_ts));
v_ts := SYSTIMESTAMP;
FOR i IN 1..v_repeat LOOP
FOR rec IN (-- Улучшенный request, но все равно с доступом к таблице
SELECT /*+INDEX(actor(last_name))*/
first_name, last_name
FROM actor
WHERE last_name LIKE 'A%')LOOP
NULL;END LOOP;END LOOP;
dbms_output.put_line('Оператор 2 : '||(SYSTIMESTAMP - v_ts));
v_ts := SYSTIMESTAMP;
FOR i IN 1..v_repeat LOOP
FOR rec IN (-- Оптимальный request: покрывающий индекс
SELECT /*+INDEX(actor(last_name, first_name))*/
first_name, last_name
FROM actor
WHERE last_name LIKE 'A%')LOOP
NULL;END LOOP;END LOOP;
dbms_output.put_line('Оператор 3 : '||(SYSTIMESTAMP - v_ts));
END;/
결과적으로 우리는 다음을 얻습니다:
연산자 1: +000000000 00:00:02.479000000
연산자 2: +000000000 00:00:02.261000000
연산자 3: +000000000 00:00:01.857000000
행위자 테이블에는 열이 4개만 있으므로 명령문 1과 2 사이의 성능 차이는 그다지 크지 않지만 여전히 상당합니다. 또한 저는 Oracle 최적화 프로그램 힌트를 사용하여 최적화 프로그램이 쿼리에 대해 하나 또는 다른 특정 인덱스를 선택하도록 했다는 점에 주목하겠습니다. 오퍼레이터 3은 우리 경주의 확실한 승자입니다. 성능이 훨씬 향상되었으며 매우 간단한 쿼리에 대해 이야기하고 있습니다. 다시 말하지만, SELECT *를 작성하면 데이터베이스에 대해 불필요하지만 최적화할 수 없는 필수 작업이 생성됩니다. 그녀는 자신이 선택한 LAST_NAME 인덱스보다 오버헤드가 약간 더 높기 때문에 해당 인덱스를 선택하지 않을 것이며, 무엇보다도 쓸모 없는 LAST_UPDATE 열을 검색하려면 여전히 테이블에 액세스해야 하기 때문입니다. 그러나 SELECT *를 더 깊이 분석할수록 상황은 더욱 악화됩니다. 에 대해 이야기하자 ...
CREATE VIEW v_customer AS
SELECT
c.first_name, c.last_name,
a.address, ci.city, co.country
FROM customer c
JOIN address a USING (address_id)
JOIN city ci USING (city_id)
JOIN country co USING (country_id)
이 뷰는 단순히 CUSTOMER 고객 테이블과 주소 일부에 대한 다양한 테이블 간의 모든 "...-대-일" 조인을 수행합니다. 고마워요, 정규화. 이 뷰에 대해 약간의 작업을 한 후에 우리는 그것에 익숙해졌고 그 밑에 있는 테이블을 잊어버렸다고 상상해 보십시오. 이제 다음 쿼리를 실행합니다.
SELECT *
FROM v_customer
결과적으로 우리는 매우 인상적인 계획을 얻었습니다.
----------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost|----------------------------------------------------------------|0| SELECT STATEMENT ||599|47920|14||*1| HASH JOIN ||599|47920|14||2| TABLE ACCESS FULL | COUNTRY |109|1526|2||*3| HASH JOIN ||599|39534|11||4| TABLE ACCESS FULL | CITY |600|10800|3||*5| HASH JOIN ||599|28752|8||6| TABLE ACCESS FULL| CUSTOMER |599|11381|4||7| TABLE ACCESS FULL| ADDRESS |603|17487|3|----------------------------------------------------------------
물론이죠. 데이터베이스는 우리가 지시한 대로 모든 조인과 전체 테이블 스캔을 수행합니다. 즉, 이 모든 데이터를 가져옵니다. 이제 다시 한 번 우리에게 정말로 필요한 것은 이것이었다고 상상해 보세요.
뭐, 진지하게 그렇죠? 이제 당신은 내가 말하는 것을 이해하기 시작했습니다. 하지만 과거의 실수로부터 무언가를 배웠다고 가정하고 보다 최적의 쿼리를 실행해 보세요.
실행 측면에서 더 나은 방향으로의 급격한 변화. 이제 옵티마이저가 조인이 쓸모없다는 것을 확인할 수 있기 때문에 조인이 제거되었으며 , 이를 확인할 수 있는 경우(그리고 *를 선택하여 해당 작업을 필수로 설정하지 않은 경우) 단순히 해당 작업을 모두 수행할 수 없습니다. 이 경우 왜 그럴까요? 기본 키 ADDRESS.ADDRESS_ID에 대한 외래 키 CUSTOMER.ADDRESS_ID는 후자 중 정확히 하나의 값을 보장합니다. 즉, JOIN 작업은 행 수를 늘리거나 줄이지 않는 "...-대-일" 조인이 됩니다. . 그리고 행을 전혀 선택하거나 요청하지 않으므로 해당 행을 로드할 필요가 전혀 없습니다. JOIN을 제거해도 쿼리 결과에는 전혀 영향을 미치지 않습니다. 데이터베이스는 항상 이 작업을 수행합니다. 거의 모든 데이터베이스에서 다음 쿼리를 실행할 수 있습니다.
--Oracle
SELECT CASE WHEN EXISTS (
SELECT 1/0FROM dual
) THEN 1 ELSE 0 END
FROM dual
-- Более адекватные диалекты SQL, например,PostgreSQLSELECT EXISTS (SELECT 1/0)
이 경우 다음 쿼리를 실행할 때처럼 산술 예외가 발생할 것으로 예상할 수 있습니다.
SELECT 1/0 FROM dual
일어난:
ORA-01476: 제수가 0과 같습니다
하지만 이런 일은 일어나지 않습니다. 최적화 프로그램(또는 파서)은 EXISTS 조건자(SELECT ..)의 선택 목록 요소가 쿼리 결과를 변경하지 않도록 보장할 수 있으므로 쿼리를 실행할 필요가 없습니다. 이와 같이!
그 동안에...
ORM의 가장 짜증나는 문제 중 하나는 SELECT * 쿼리를 작성하기가 너무 쉽다는 것입니다. 실제로 예를 들어 HQL/JPQL에서는 일반적으로 기본적으로 사용됩니다. 전체 엔터티를 검색할 것이기 때문에 SELECT 절을 모두 생략할 수 있습니다. 그렇죠? 예를 들어:
FROM v_customer
예를 들어, Hibernate 개발 전문가이자 옹호자인 Vlad Mihalcea는 체크아웃 후 변경 사항을 저장하고 싶지 않다고 확신할 때 거의 항상 [한정된] 쿼리를 사용할 것을 권장합니다. ORM은 객체 그래프의 지속성 문제 해결을 크게 촉진합니다. 참고: 지속성. 실제로 객체 그래프를 수정하고 변경 사항을 저장하는 작업은 불가분하게 연결되어 있습니다. 그런데 그렇게 할 것도 아니면 굳이 굳이 에센스를 추출할 이유가 있을까요? [세련된] 요청을 작성해 보는 것은 어떨까요? 명확히 하자면, 성능 관점에서 볼 때 특정 사용 사례에 맞게 특별히 맞춤화된 쿼리를 작성하는 것이 다른 어떤 옵션보다 확실히 더 좋습니다. 데이터 세트가 작고 중요하지 않기 때문에 신경 쓰지 않을 수도 있습니다. 엄청난. 그러나 결국 확장성이 필요할 때 엔터티 그래프를 강제적으로 탐색하는 대신 쿼리를 사용하도록 애플리케이션을 재설계하는 것은 상당히 어려울 것입니다. 그리고 그것 없이도 할 일이 생길 것입니다.
무언가가 있는지 알아보기 위해 행 개수 계산
최악의 리소스 낭비 중 하나는 데이터베이스에 무언가가 있는지 확인하기 위해 COUNT(*) 쿼리를 실행하는 것입니다. 예를 들어 특정 사용자에게 주문이 있는지 확인해야 합니다. 그리고 요청을 실행합니다.
SELECTcount(*)
FROM orders
WHERE user_id =:user_id
초등학교. COUNT = 0이면 주문이 없습니다. 그렇지 않으면 그렇습니다. 아마도 ORDERS.USER_ID 열에 인덱스가 있으므로 성능은 그다지 나쁘지 않을 것입니다. 그러나 위 쿼리의 성능이 다음 옵션과 비교될 것이라고 생각하십니까?
--Oracle
SELECT CASE WHEN EXISTS (
SELECT *
FROM orders
WHERE user_id =:user_id
) THEN 1 ELSE 0 END
FROM dual
-- Более адекватные диалекты SQL, например,PostgreSQLSELECT EXISTS (
SELECT *
FROM orders
WHERE user_id =:user_id
)
실제 존재 술어가 첫 번째 문자열을 찾으자마자 추가 문자열 검색을 중단한다는 사실을 알아내는 데는 로켓 과학자가 필요하지 않습니다 . 따라서 결과가 "주문 없음"으로 판명되면 속도는 비슷할 것입니다. 그러나 결과가 "예, 주문이 있습니다"인 경우 정확한 수량을 계산할 필요가 없는 경우 답변을 훨씬 빨리 받을 수 있습니다. 결국 우리는 정확한 숫자에는 관심이 없습니다. 그러나 우리는 데이터베이스에 이를 계산하라고 지시했으며( 불필요한 작업 ), 데이터베이스는 우리가 1보다 큰 모든 결과( 필요한 작업 ) 를 무시하고 있다는 사실을 알지 못합니다 . 물론 동일한 결과를 얻기 위해 JPA 지원 컬렉션에서 list.size()를 호출하면 훨씬 더 나쁠 것입니다. 나는 이미 이전에 내 블로그에 이에 대해 썼고, 두 옵션에 대한 비교 테스트를 실시했습니다...
결론
이 기사에는 분명한 내용이 나와 있습니다. 불필요하지만 꼭 필요한 작업을 데이터베이스에 강요하지 마세요 . 요구사항에 따라 일부 특정 작업을 수행할 필요가 없다는 것을 알고 있으므로 이는 불필요합니다 . 그러나 데이터베이스에 그렇게 하도록 지시합니다. 데이터베이스에서 이 작업이 불필요하다는 것을 확인할 수 있는 방법이 없기 때문에 필요합니다 . 이 정보는 클라이언트에서만 사용할 수 있으며 서버에서는 사용할 수 없습니다. 따라서 데이터베이스는 이를 실행해야 합니다. 이 기사에서는 SELECT *에 중점을 두었는데, 그 이유는 보기에 매우 편리한 개체이기 때문입니다. 그러나 이는 데이터베이스에만 적용되는 것은 아닙니다. 이는 클라이언트가 서버에게 불필요하지만 필요한 작업을 수행하도록 지시하는 모든 분산 알고리즘에 적용됩니다 . B에 대한 모든 호출을 단일 호출로 묶는 대신 서비스 B를 여러 번 호출하여 UI가 서비스 A의 결과를 반복하는 평균 AngularJS 애플리케이션에는 몇 개의 N+1 작업이 있습니까? 이것은 매우 흔한 일입니다. 해결책은 항상 동일합니다. 명령을 실행하는 엔터티에 더 많은 정보를 제공할수록 해당 엔터티는 (이론적으로) 해당 명령을 더 빠르게 실행합니다. 최적의 쿼리를 작성합니다. 언제나. 전체 시스템이 이에 대해 감사할 것입니다. 원본 기사
GO TO FULL VERSION