Autor Zpráva
Kcko
Profil
#1 · Zasláno: 26. 1. 2022, 11:34:20
Odpovědět Citovat
Ahoj,
mám poměrně velkou tabulku (zatím 1 mio záznamů, ale bude to růst).

Potřebuji z toho udělat pivotní tabulku, tj součet hodnot pro uživatele per akce.
Tohle je první verze, ale je to pomalé (~2s).

Šlo by to udělat elegantněji? Mám k dispozici i MySQL 8.

Ještě ale potřebuji k součtům za akci doplnit i čas posledn akce.

Věděl by někdo? 

SELECT user_id, (
  SUM(IF (action_type = 'login', 1, 0)) 
) AS 'login', (
  SUM(IF (action_type = 'publish', 1, 0)) 
) AS 'publish', (
  SUM(IF (action_type = 'download', 1, 0)) 
) AS 'download', (
  SUM(IF (action_type = 'upload', 1, 0)) 
) AS 'upload', (
  SUM(IF (action_type = 'comment', 1, 0)) 
) AS 'comment',(
  SUM(IF (action_type = 'share', 1, 0)) 
) AS 'share'

FROM ss_stats
GROUP BY user_id
HAVING user_id IS NOT NULL


CREATE TABLE `ss_stats` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `action_type` ENUM('login','view','download','share','publish','upload','comment') NOT NULL COLLATE 'utf8mb4_general_ci',
    `created` DATETIME NOT NULL,
    `ss_domain_id` INT(10) UNSIGNED NOT NULL,
    `user_id` INT(10) UNSIGNED NULL DEFAULT NULL,
    `ss_document_id` INT(10) UNSIGNED NULL DEFAULT NULL,
    `ss_share_id` INT(10) UNSIGNED NULL DEFAULT NULL,
    `ss_comment_id` INT(10) UNSIGNED NULL DEFAULT NULL,
    `file_id` INT(10) UNSIGNED NULL DEFAULT NULL,
    `hash` VARCHAR(32) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
    PRIMARY KEY (`id`) USING BTREE,
    INDEX `ss_stats_ss_domain_id_domain` (`ss_domain_id`) USING BTREE,
    INDEX `ss_stats_user_id_user` (`user_id`) USING BTREE,
    INDEX `ss_stats_ss_document_id_document` (`ss_document_id`) USING BTREE,
    INDEX `ss_stats_ss_share_id_ss_share` (`ss_share_id`) USING BTREE,
    INDEX `ss_start_ss_comment_id_comment` (`ss_comment_id`) USING BTREE,
    INDEX `ss_stats_file_id_file` (`file_id`) USING BTREE,
    INDEX `ss_stats_action_type_key` (`action_type`) USING BTREE,
    INDEX `ss_stats_action_type_user_id_key` (`action_type`, `user_id`) USING BTREE,
    INDEX `ss_stats_action_type_document_id_key` (`action_type`, `ss_document_id`) USING BTREE,
    INDEX `ss_stats_action_type_file_id_key` (`action_type`, `file_id`) USING BTREE,
    CONSTRAINT `ss_start_ss_comment_id_comment` FOREIGN KEY (`ss_comment_id`) REFERENCES `ss_comment` (`id`) ON UPDATE NO ACTION ON DELETE SET NULL,
    CONSTRAINT `ss_stats_ss_document_id_document` FOREIGN KEY (`ss_document_id`) REFERENCES `ss_document` (`id`) ON UPDATE NO ACTION ON DELETE SET NULL,
    CONSTRAINT `ss_stats_ss_domain_id_domain` FOREIGN KEY (`ss_domain_id`) REFERENCES `domain` (`id`) ON UPDATE NO ACTION ON DELETE RESTRICT,
    CONSTRAINT `ss_stats_ss_share_id_ss_share` FOREIGN KEY (`ss_share_id`) REFERENCES `ss_share` (`id`) ON UPDATE NO ACTION ON DELETE SET NULL,
    CONSTRAINT `ss_stats_file_id_file` FOREIGN KEY (`file_id`) REFERENCES `file` (`id`) ON UPDATE NO ACTION ON DELETE SET NULL,
    CONSTRAINT `ss_stats_user_id_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON UPDATE NO ACTION ON DELETE RESTRICT
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1008961
;
Keeehi
Profil
#2 · Zasláno: 26. 1. 2022, 11:58:06
Odpovědět Citovat
čas poslední akce je jednoduchý, něco ve stylu 
  MAX(IF (action_type = 'login', created, 0)) 
) AS 'last_login',

Jinak agregeace se takto dělají, nemáš tam nic obskurního. Jestli se to dá zrychlit nevím, na to odborník nejsem. Já pracuji s řádově mnoha násobně většími tabulkami v cloudu kde se na to nasadí masivní paralelizace. Optimalizaci na jednom stroji jsem moc nedělal. Pokud je něco při selectu pomalé, většinou tam chyb indexy. Ale ty je tam máš. Co říká EXPLAIN?
Kcko
Profil
#3 · Zasláno: 26. 1. 2022, 12:06:46 · Upravil/a: Kcko o 43 minut později
Odpovědět Citovat
Keeehi:
Ahoj,
potřebuji to zatím zrychlit na 1 stroji, tohle nemůžu vypustit ven, pak se to asi rozloží, ale to není zatím otázka mě:

EXPLAIN: 


ss_stats
---
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | 
| ---: | --- | --- | --- | --- | --- | --- | --- | --- | ---: | ---: | --- | 
| 1 | SIMPLE | ss_stats | \N | index | ss_stats_user_id_user,ss_stats_action_type_user_id_key | ss_stats_user_id_user | 5 | \N | 978004 | 100,00 | \N |



Jdu změnit strukturu, myslím, že to MySQL líp neumí, pivotní tabulky dělají všichni stejně +/-
Kajman
Profil
#4 · Zasláno: 26. 1. 2022, 13:20:12
Odpovědět Citovat
Je toto rychlejší? 
SELECT user_id,
       ( Sum(IF (action_type = 'login', pocet, 0)) )    AS 'login',
       ( Sum(IF (action_type = 'publish', pocet, 0)) )  AS 'publish',
       ( Sum(IF (action_type = 'download', pocet, 0)) ) AS 'download',
       ( Sum(IF (action_type = 'upload', pocet, 0)) )   AS 'upload',
       ( Sum(IF (action_type = 'comment', pocet, 0)) )  AS 'comment',
       ( Sum(IF (action_type = 'share', pocet, 0)) )    AS 'share'
FROM   (SELECT user_id,
               action_type,
               Count(*) pocet
        FROM   ss_stats
        GROUP  BY action_type,
                  user_id) pocty
WHERE  user_id IS NOT NULL
GROUP  BY user_id
Kcko
Profil
#5 · Zasláno: 26. 1. 2022, 13:29:04
Odpovědět Citovat
Kajman:
Ahoj,
je ale dotaz trvá cca 1s, což je pořád hodně.

Díky za ochotu.

Stejně si myslím, že bude lepší, jelikož mám pevný počet akcí, to přetavit do tabulky

... | login | publish | download ...
Kajman
Profil
#6 · Zasláno: 26. 1. 2022, 13:36:57
Odpovědět Citovat
A data si tam budeš měnit pomocí triggerů nad tou tabulkou ss_stats?
Kcko
Profil
#7 · Zasláno: 26. 1. 2022, 13:44:14
Odpovědět Citovat
Kajman:
Ty tam lezou klasicky přes Inserty z různých částí aplikace.
Kajman
Profil
#8 · Zasláno: 26. 1. 2022, 13:48:36
Odpovědět Citovat
Takže tam budeš mít stále milion řádků ale více sloupců. To rychlosti nepomůže. Pokud je ta statistika často potřeba, udělěj si druhou tabulku, kde si ty výpočty budeš držet. Pomocí triggerů můžeš zajistit, aby byly aktuální.

Jiné databáze umí materializované view - což je skoro to samé. Data předpočítána a uložena. Ale ty view se musí také refreshovat, aby tam byl aktuální stav.
Kcko
Profil
#9 · Zasláno: 26. 1. 2022, 13:57:03
Odpovědět Citovat
Kajman:
Uff. To nejde, tahle tabulka slouží pro ruzné výpisy statistiky (per user, per document, vstupují do toho pohledy za časová období atd ...)
S triggery popřemýšlím, díky (ale pořád je problém, že ta statistika bude mít X ruzných podob).
Kajman
Profil
#10 · Zasláno: 26. 1. 2022, 14:13:37
Odpovědět Citovat
Tu současnou tabulku přeci měnit nebudeš (takže jiné statistiky to nijak neovlivní), jen si uděláš druhou s předpočítanými daty, pokud potřebuješ rychlé statistiky pro všechny uživatele.
Kcko
Profil
#11 · Zasláno: 26. 1. 2022, 14:26:17
Odpovědět Citovat
Kajman:
Ano, ale já budu grupovat někdy podle dokumentu, jindy podle comment_id a může to být omezeno ještě třeba za rok a konkrétní měsíc atd.
Keeehi
Profil
#12 · Zasláno: 26. 1. 2022, 14:53:09
Odpovědět Citovat
Kcko:
Ano, ale statistika pro jeden dokument by měla být rychlejší protože řádků pro jeden dokument bude řádově méně. Takže nefiltrovanou statistiku můžeš brát z přepočítané tabulky a statistiku pro jednotlivé dokumenty pouštět live nad touto tabulkou.
Kcko
Profil
#13 · Zasláno: 26. 1. 2022, 15:21:56
Odpovědět Citovat
Keeehi:
Jasně, ale jeví se za tím hodně práce ;), myslel jsem, že to bude se správným indexem a lepším dotazem rychlejší (mnohem ...)
ttttt
Profil *
#14 · Zasláno: 26. 1. 2022, 17:15:21
Odpovědět Citovat
Není rychlejší to joinovat? 
SELECT
  user_id,
  logins.count AS 'login', 
  publishes.count AS 'publish', 
  downloads.count AS 'download', 
    uploads.count AS 'upload', 
  "comments".count AS 'comment', 
  shares.count AS 'shares'
FROM 
  users 
  LEFT JOIN (SELECT user_id, COUNT(*) FROM ss_stats WHERE action_type = 'login' GROUP BY user_id) logins USING(user_id)
  LEFT JOIN (SELECT user_id, COUNT(*) FROM ss_stats WHERE action_type = 'publish' GROUP BY user_id) publishes USING(user_id)
  LEFT JOIN (SELECT user_id, COUNT(*) FROM ss_stats WHERE action_type = 'download' GROUP BY user_id) downloads USING(user_id)
  LEFT JOIN (SELECT user_id, COUNT(*) FROM ss_stats WHERE action_type = 'upload' GROUP BY user_id) uploads USING(user_id)
  LEFT JOIN (SELECT user_id, COUNT(*) FROM ss_stats WHERE action_type = 'comment' GROUP BY user_id) "comments" USING(user_id)
  LEFT JOIN (SELECT user_id, COUNT(*) FROM ss_stats WHERE action_type = 'share' GROUP BY user_id) shares USING(user_id)
Kcko
Profil
#15 · Zasláno: 27. 1. 2022, 11:10:17 · Upravil/a: Kcko 27. 1. 2022, 12:32:37
Odpovědět Citovat
ttttt:
Pecka! Nad 1mio to trvá cca 0,2s :-)

Klobouček! :-)

a samozřejmě díky všem ostatním za snahu a další možné postupy.
Časová prodleva: 2 roky

Vaše odpověď

Mohlo by se hodit


Prosím používejte diakritiku a interpunkci.

Ochrana proti spamu. Napište prosím číslo dvě-sta čtyřicet-sedm:

0