플랫 BNL 가입이 있는 느린 MariaDB SQL 쿼리 속도를 높이려면 어떻게 해야 합니까?

플랫 BNL 가입이 있는 느린 MariaDB SQL 쿼리 속도를 높이려면 어떻게 해야 합니까?

다음 시스템에서 실행 중인 SQL 쿼리가 느려서 문제가 발생했습니다.

• 운영 체제:데비안 11(불세아이)
• 데이터베이스:MariaDB 10.5.15(bullseye용 패키지 버전)

테이블 스키마 및 일부 샘플 데이터(MariaDB를 지원하지 않으므로 DB Fielen 없음):

DROP TABLE IF EXISTS item_prices;
DROP TABLE IF EXISTS prices;
DROP TABLE IF EXISTS item_orders;

CREATE TABLE item_orders
(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ordered_date DATE NOT NULL
) Engine=InnoDB;

CREATE TABLE prices
(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    selected_flag TINYINT UNSIGNED NOT NULL
) Engine=InnoDB;

CREATE TABLE item_prices
(
    item_order_id INT UNSIGNED NOT NULL,
    price_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (item_order_id, price_id),
    FOREIGN KEY (item_order_id) REFERENCES item_orders(id),
    FOREIGN KEY (price_id) REFERENCES prices(id)
) Engine=InnoDB;

INSERT INTO item_orders VALUES (1, '2022-01-01');
INSERT INTO item_orders VALUES (2, '2022-02-01');
INSERT INTO item_orders VALUES (3, '2022-03-01');

INSERT INTO prices VALUES (1, 0);
INSERT INTO prices VALUES (2, 0);
INSERT INTO prices VALUES (3, 1);

INSERT INTO prices VALUES (4, 0);
INSERT INTO prices VALUES (5, 0);

INSERT INTO prices VALUES (6, 1);

INSERT INTO item_prices VALUES (1, 1);
INSERT INTO item_prices VALUES (1, 2);
INSERT INTO item_prices VALUES (1, 3);

INSERT INTO item_prices VALUES (2, 4);
INSERT INTO item_prices VALUES (2, 5);

INSERT INTO item_prices VALUES (3, 6);

테이블 사용 현황의 개요는 다음과 같습니다.

1. 어느 한 달 동안 item_orders에는 수천 개의 행이 있습니다.
2. item_orders의 행은 item_orders.id = item_inters.item_orders_id의 0개 이상의 행에 링크됩니다.
3. item_timeouts의 행에는 가격(item_time.price_id = prices.id)에 정확히 하나의 연결된 행이 있습니다.
4. item_orders의 특정 행에 대해 selected_orders.id = item_selected.item_order_id AND item_selected.price_id = prices.id AND 가격에는 0 또는 1개의 행이 있습니다.selected_selected = 1).이것은 데이터베이스가 아닌 애플리케이션에 의해 강제됩니다(즉, 구속조건으로 정의되지 않음).

하나의 질문으로 얻을 수 있는 것은 다음과 같습니다.

1. item_orders의 행 수.
2. 관련된 selected_flag가 1인 item_order의 행 수.

현시점에서는 다음과 같은 질문이 있습니다.

SELECT
    COUNT(item_orders.id) AS item_order_count,
    SUM(CASE WHEN prices.id IS NOT NULL THEN 1 ELSE 0 END) AS item_order_selected_count
FROM
    item_orders
LEFT JOIN prices ON prices.id IN (
    SELECT price_id
    FROM item_prices
    WHERE
        item_prices.item_order_id = item_orders.id)
    AND prices.selected_flag = 1

이 쿼리는 올바른 데이터(item_order_count = 3, item_order_selected_count = 2)를 반환하지만 라이브 데이터 집합에서 실행되는 데는 오랜 시간(10초 이상)이 소요되며, 이는 사용자에게 너무 느립니다(사용률이 높은 보고서이며 하루 동안 반복적으로 새로 고침됨).LEFT JOIN과 관련 SUM을 삭제하면 쿼리 시간이 약 0.1초로 단축되므로 LEFT JOIN의 서브쿼리가 문제라고 생각합니다.또한 조인에 대한 EXPLINE 출력의 Extra 컬럼에는 다음과 같이 표시됩니다.

장소 사용; 가입 버퍼 사용(플랫, BNL 가입)

'평탄한 BNL 결합'을 검색하면 많은 정보가 노출되는데, 그 중 요약하면 'BNL 결합은 느리므로 가능하면 피하십시오'이다.

BNL 가입을 회피하고 동일한 정보를 반환하도록 이 쿼리를 다시 작성할 수 있습니까?

이미 고려한 사항:

1. 모든 ID 열은 인덱스화됩니다(item_orders.id, prices.id, item_disc.item_order_id, item_disc.price_id).

2. 쿼리를 두 개로 분할합니다. 하나는 item_order_count(JOIN 없음), 다른 하나는 item_order_selected_count(INNER JOIN, 일치하는 행만 필요하기 때문에).이 방법은 작동하지만 더 많은 데이터를 반환하기 위해 이 쿼리를 빌드하고 싶기 때문에 이상적이지 않습니다(이 질문의 최소값으로 되돌렸습니다).또한 쿼리 출력을 사용자가 볼 수 있는 내용과 최대한 가깝게 유지하려고 합니다. 그러면 디버깅이 쉬워지고 애플리케이션이 아닌 데이터베이스(그 워크로드에 맞게 최적화됨)가 작업을 수행할 수 있기 때문입니다.

3. MariaDB 설정 변경:BNL join에 대한 검색 결과 중 일부는 구성 옵션을 변경하는 것을 제안하지만, 애플리케이션에는 수백 개의 다른 쿼리가 있기 때문에 이를 실행하는 것이 조심스럽고 회귀를 일으키고 싶지 않습니다(예: 이 쿼리의 속도는 빨라지지만 실수로 다른 모든 쿼리의 속도가 느려집니다).

4. MariaDB 업그레이드:이는 Debian과 함께 패키지화된 버전과 다른 버전을 사용해야 하며, 애플리케이션의 다른 부분이 파손될 수 있으며, 시스템이 대규모 업그레이드를 거쳤습니다.

이것이 더 빠를지는 모르겠지만 시도해 볼 가치가 있습니다(인덱스된 외부 키의 테이블 조인은 빠르고 때로는 단순성이 중요합니다).

SELECT 
    (SELECT COUNT(*) FROM item_orders) AS item_order_count,
    (SELECT COUNT(*)
     FROM item_orders io
     JOIN item_prices ip
     ON io.id = ip.item_order_id
     JOIN prices p
     ON ip.price_id = p.id
     WHERE p.selected_flag = 1) AS item_order_selected_count;

언급URL : https://stackoverflow.com/questions/73193600/how-to-speed-up-a-slow-mariadb-sql-query-that-has-a-flat-bnl-join