Pomalý Select sum() nad tabulkou

dev1

Dobrý den,

ačkoliv na projektu používáme Nette a Kdyby\Doctrine, tak se tento dotaz se týká spíš samotné databáze.

Řešíme problém s rychlostí tohoto SQL dotazu:

SELECT SUM(amount) FROM orders WHERE product="abc"

Doba zpracování tohoto dotazu je neuvěřitelných 15 sekund. Navíc, když odebereme podmínku WHERE product=„abc“ a chceme tak spočítat všechny záznamy, tak je dotaz zpracován za 1 sekundu.

Tabulka má aktuálně 3 miliony záznamů.
Uložiště tabulky je InnoDB.
Nad sloupcem product je nastaven index a je to varchar(255).
Unikátních produktů je v tabulce celkem 10.

Dokážete prosím někdo poradit, proč je doba zpracování tohoto jednoduchého dotazu tak dlouhá?
Popřípadě, které parametry MySQL hrají roli při tomto výpočtu a pomohlo by je navýšit?

Díky všem!

Editoval dev1 (16. 1. 2019 12:44)

CZechBoY

Zkus to přes subselect

SELECT SUM(id) FROM (SELECT id FROM orders WHERE product = "abc") x

btw. nechceš spíš count?

ps. nějak nejede sql zvýrazňovač syntaxe :-/

Editoval CZechBoY (16. 1. 2019 12:37)

dev1

Tak bohužel nepomohlo. Čas zpracování je ± stejný.

A chci opravdu sum().

h4kuna

Tohle moc dobře nevyřešíš. Pokud máš 3 milión záznamů a jen 10 unikátností. Protože v takovém případě se index ani nepoužije, případně nemá absolutně možnost ukázat svou sílu.

Co si předpočítávat ty hodnoty pomocí triggerů? Incrementálně.

Editoval h4kuna (16. 1. 2019 13:30)

Mysteria

Nastav složenej index nad product a amount. Pak by se měl použít jenom index na výpočet a mělo by to být rychlý. Případně zkus opačný pořadí, nejsem si jistej, jestli na to pořadí má vliv.

dev1

A neexistují nějaké parametry samotné databáze, jejichž navýšení by vedlo k rychlejšímu výpočtu? Například innodb_buffer_pool_size, atp.? Popřípadě na které další věci je dobré se zaměřit?

Jinak zkoušeli jsme teď i ten složený index, ale bohužel to vůbec nepomohlo.

duke

Pokud tento dotaz potřebuješ spouštět často, rozhodně dává smysl si tu sumu počítat průběžně při ukládání objednávek, jak už radil @h4kuna. Buď přes ty triggery nebo v transakci ručně v modelové třídě, která ukládá ty objednávky.

Mysteria

dev1 napsal(a):
Jinak zkoušeli jsme teď i ten složený index, ale bohužel to vůbec nepomohlo.

V tom případě tam máte nějakej problém, nebo ten dotaz není tak jednoduchej jak píšeš, že nedokáže použít index.
Naschvál jsem to vyzkoušel u sebe: 6 milionů řádků s náhodným rozdělením do 10 produktů:

SELECT SQL_NO_CACHE SUM(amount) FROM orders WHERE product = 'A'; // 0.5s

EXPLAIN: 1	SIMPLE	orders	NULL	ALL	NULL	NULL	NULL	NULL	998979	Using where

Když přidám index jak jsem psal výše tak to mám cca 8× rychlejší:

ALTER TABLE orders ADD INDEX IK_product_amount (product, amount);

SELECT SQL_NO_CACHE SUM(amount) FROM orders WHERE product = 'A'; // 0.06s

EXPLAIN:  1	SIMPLE	orders	NULL	ref	IK_product_amount	IK_product_amount	767	const	1282466	Using where; Using index

Jedná se o nejlevnější VPSku s 1 CPU na 3GHz a 1GB RAM.

EDIT: Protože jsem se nudil a zajímalo mě, kolik z toho vymáčknu, tak na 15s jsem se dostal bez indexu při 35M záznamů (s indexem 2s).

Editoval Mysteria (16. 1. 2019 22:00)

Kcko

Mysteria napsal(a):

dev1 napsal(a):
Jinak zkoušeli jsme teď i ten složený index, ale bohužel to vůbec nepomohlo.

V tom případě tam máte nějakej problém, nebo ten dotaz není tak jednoduchej jak píšeš, že nedokáže použít index.
Naschvál jsem to vyzkoušel u sebe: 6 milionů řádků s náhodným rozdělením do 10 produktů:
SELECT SQL_NO_CACHE SUM(amount) FROM orders WHERE product = 'A'; // 0.5s

EXPLAIN: 1	SIMPLE	orders	NULL	ALL	NULL	NULL	NULL	NULL	998979	Using where
Když přidám index jak jsem psal výše tak to mám cca 8× rychlejší:
ALTER TABLE orders ADD INDEX IK_product_amount (product, amount);

SELECT SQL_NO_CACHE SUM(amount) FROM orders WHERE product = 'A'; // 0.06s

EXPLAIN:  1	SIMPLE	orders	NULL	ref	IK_product_amount	IK_product_amount	767	const	1282466	Using where; Using index
Jedná se o nejlevnější VPSku s 1 CPU na 3GHz a 1GB RAM.

EDIT: Protože jsem se nudil a zajímalo mě, kolik z toho vymáčknu, tak na 15s jsem se dostal bez indexu při 35M záznamů (s indexem 2s).

Tabulku sis naplnil procedurou?

Mysteria

Ano, takovouhle kráskou. :)

DELIMITER $$
DROP PROCEDURE IF EXISTS test$$
CREATE PROCEDURE test() BEGIN DECLARE COUNT INT DEFAULT 0; WHILE COUNT < 10000000 DO
INSERT INTO orders (amount, product)
VALUES (
          (SELECT FLOOR((RAND() * (9999 - 1000 + 1)) + 1000)),
          (SELECT *
           FROM
             (SELECT 'A'
              UNION ALL SELECT 'B'
              UNION ALL SELECT 'C'
              UNION ALL SELECT 'D'
              UNION ALL SELECT 'E'
              UNION ALL SELECT 'F'
              UNION ALL SELECT 'G'
              UNION ALL SELECT 'H'
              UNION ALL SELECT 'I'
              UNION ALL SELECT 'J') t
           ORDER BY RAND()
           LIMIT 1));
SET COUNT = COUNT + 1; END WHILE; END$$
DELIMITER ;

START TRANSACTION;
CALL test();
COMMIT;

Kcko

Mysteria napsal(a):

Ano, takovouhle kráskou. :)

DELIMITER $$
DROP PROCEDURE IF EXISTS test$$
CREATE PROCEDURE test() BEGIN DECLARE COUNT INT DEFAULT 0; WHILE COUNT < 10000000 DO
INSERT INTO orders (amount, product)
VALUES (
          (SELECT FLOOR((RAND() * (9999 - 1000 + 1)) + 1000)),
          (SELECT *
           FROM
             (SELECT 'A'
              UNION ALL SELECT 'B'
              UNION ALL SELECT 'C'
              UNION ALL SELECT 'D'
              UNION ALL SELECT 'E'
              UNION ALL SELECT 'F'
              UNION ALL SELECT 'G'
              UNION ALL SELECT 'H'
              UNION ALL SELECT 'I'
              UNION ALL SELECT 'J') t
           ORDER BY RAND()
           LIMIT 1));
SET COUNT = COUNT + 1; END WHILE; END$$
DELIMITER ;

START TRANSACTION;
CALL test();
COMMIT;

Hezké, hodím si do gistu. Vstupni parametr na počet by byl ještě lepší, než zadrátovaný do procedury :-)

Mysteria

Můžeš si ho tam přidat. :) Pro testovací účely mi bohatě stačilo i tohle. :)