mysql tabulka s miliony řádků - select...

Vývojová prostředí, aplikace, skripty, http://www... síťové programy, internet, sdílení...
Václav Sedlář
Žlutá karta Bazar
Žlutá karta Bazar
Registrován: 01. dub 2006
Bydliště: Liberec
Kontaktovat uživatele:

mysql tabulka s miliony řádků - select...

Příspěvek od Václav Sedlář »

Ahoj, potřebuji vyřešit jeden problém - potřebuji v mysql tabulce, která má několik desítek (vlastně už 2 stovky)milionů řádků, zjisti od každého záznamu v tom sloupci počet duplicit...

Např.:

Kód: Vybrat vše

sloupec v tabulce obsahuje:
xxx
aaa
vvv
xxx
xxx
vvv
aaa
A já potřebuji dostat toto:

Kód: Vybrat vše

aaa=2
vvv=2
xxx=3
nebo alespoň toto:

Kód: Vybrat vše

nejvíce se vyskytuje xxx (3x)
A teď přemýšlím, jak dotaz na dbs optimalizovat co nejlépe, aby to DBS moc nevytížilo...
Nejraději bych udělal normální select a vše řešil až v PHP, ale zvládne vůbec PHP pojmout tolik dat?
Máte nějaké návrhy?

díky

EDIT: jde o tabulku kde jsou uloženy ip návštěvníků webu a potřebuji zjistit, zda tam není nějaká ip podezřele často (a za sebou)...prostě zda se nejedná o útok apod...
Václav Sedlář
Žlutá karta Bazar
Žlutá karta Bazar
Registrován: 01. dub 2006
Bydliště: Liberec
Kontaktovat uživatele:

Re: mysql tabulka s miliony řádků - select...

Příspěvek od Václav Sedlář »

ještě mě napadlo brát posledních (třeba) 200 000 záznamů a ty porovnat, ale i tak si nejsem jistý, zda dbs moc nevytíží ten dotaz na 200 000 záznamů...
raym0n
Mírně pokročilý
Mírně pokročilý
Uživatelský avatar
Registrován: 16. led 2007
Bydliště: Brno
Kontaktovat uživatele:

Re: mysql tabulka s miliony řádků - select...

Příspěvek od raym0n »

cau

Kód: Vybrat vše

select
  x.youripname,
  x.name_count
from
  (select
    u.youripname,
    count(*) as name_count,
    rank() over (order by count(*) desc) as rank
  from
    youriptable u
  group by
    u.youripname) x
where
  x.rank = 1
co rikas? ;)
timemachine in Cooler Master ATCS 840: Intel Core i5-2500K, ASUS Maximus V GENE - Intel Z77, Crucial 8GB KIT DDR3 1600MHz CL10 Ballistix Sport Series, GIGABYTE GTX 660 Ti OC 2GB 1032/6008[MHz], Kingston HyperX 3K SATA III - 120GB, Corsair HX750 - 750W.
Václav Sedlář
Žlutá karta Bazar
Žlutá karta Bazar
Registrován: 01. dub 2006
Bydliště: Liberec
Kontaktovat uživatele:

Re: mysql tabulka s miliony řádků - select...

Příspěvek od Václav Sedlář »

hmm, díky, ale ještě by se mi hodil komentář k tomu kodu...

Já to zatím vyřešil takto:

Kód: Vybrat vše

$result=mysql_query("SELECT ip FROM tabulka WHERE id > (SELECT MAX(id) - 200000 FROM tabulka) ORDER BY id DESC");
A pak už jsem si načtená data v klidu v php prošel...Jelikož script poběží na lokálním serveru, ale data z dbs si beru z hostingu, tak mi stačí, aby se načtení z dbs provedlo rychle...pak už mi je jedno, že php poběží na mém serveru delší dobu...
raym0n
Mírně pokročilý
Mírně pokročilý
Uživatelský avatar
Registrován: 16. led 2007
Bydliště: Brno
Kontaktovat uživatele:

Re: mysql tabulka s miliony řádků - select...

Příspěvek od raym0n »

pouzijes analytickou fci rank, ktera ti priradi 'ocislovani' na zaklade poradi 'count(*) desc', pak ve chvili, kdyz maji dva zaznamy stejny pocet, dostanou stejny rank, takze by se ti tam mohlo objevit neco neprirozene 2x, coz odstranis, kdyz bys pouzil fci 'dense_rank'

//btw nasel jsi nekoho podezreleho? nejakeho psot huntera napr? ;)
timemachine in Cooler Master ATCS 840: Intel Core i5-2500K, ASUS Maximus V GENE - Intel Z77, Crucial 8GB KIT DDR3 1600MHz CL10 Ballistix Sport Series, GIGABYTE GTX 660 Ti OC 2GB 1032/6008[MHz], Kingston HyperX 3K SATA III - 120GB, Corsair HX750 - 750W.
Václav Sedlář
Žlutá karta Bazar
Žlutá karta Bazar
Registrován: 01. dub 2006
Bydliště: Liberec
Kontaktovat uživatele:

Re: mysql tabulka s miliony řádků - select...

Příspěvek od Václav Sedlář »

raym0n píše:...
//btw nasel jsi nekoho podezreleho? nejakeho psot huntera napr? ;)
Zatím jsem to na ostré verzi nezkoušel...ještě si vše projdu a zkonzultuju s vedením (limity a tak...) :-D
1Pupik1989
Začátečník
Začátečník
Registrován: 20. říj 2011
Bydliště: Dvůr Králové nad Labem

Re: mysql tabulka s miliony řádků - select...

Příspěvek od 1Pupik1989 »

Kód: Vybrat vše

SELECT COUNT(id) as cnt, ipaddress FROM `table` GROUP BY ipaddress HAVING cnt > 1
nebo

Kód: Vybrat vše

SELECT DISTINCT ipaddress, count(name) as cnt FROM `table` GROUP BY ipadress
CPU: AMD Phenom II x4 955BE @ 4GHz FAN: Arctic Cooling Freezer Xtreme rev.2
MB: MSI 760GM-E51
RAM: Kingston 2x4Gb RAM DDR3 1333 @ 1466MHz
GPU: Gigabyte Radeon HD 6850 OC 985/1260MHz
HDD: WD Caviar Green WD10EARX 1TB SATAIII/600, ZDROJ: Fortron FSP550-APN (550W)
Václav Sedlář
Žlutá karta Bazar
Žlutá karta Bazar
Registrován: 01. dub 2006
Bydliště: Liberec
Kontaktovat uživatele:

Re: mysql tabulka s miliony řádků - select...

Příspěvek od Václav Sedlář »

hmm, podívám se na to...

Zatím to je takto:

fci předám kolik posledních záznamů se má testovat a jaký je limit kolikrát se tam ip může objevit...

Pak načtení z dbs:

Kód: Vybrat vše

$result=mysql_query("SELECT ip FROM tabulka WHERE id > (SELECT MAX(id) - pocet_vzorku FROM tabulka)");
Pak nahážu načtené ip do pole a udělám sort(pole). Nědělám sort už v dotazu na dbs, abych ji nezpomaloval...Díky tomu získám z dbs i 50 000 ip do 1 sekundy (víc jsem zatím nezkoušel)

Pak se spustí tento kod (x je seřazené pole s ip adresama z dbs, $aktual je aktuální testovaná ip):

Kód: Vybrat vše

...
foreach ($x as $value) {//prochazeni pole s ip adresami
            if ($aktual != $value and $value != "") {//pokud ip adresa jeste nebyla zkontrolovana
                $aktual = $value; //aktualni ip pro testovani
                for ($j = $index; $j < $pocet_radku; $j++) {//prochazeni pole ip adres od aktualni ip
                    if ($aktual == $x[$j]) {//ip adresa nalezena v poli
                        $pocet++; //zvysim pocet vyskytu ip adresy
                    } else {//pokud už je dalsi ip jina nez predchozi, ukoncime cyklus
                        break;
                    }
                }
                if ($pocet > $limit) {//pokud pocet vetsi nez hodnota, dam do vysledku
                    if ($seznam_nadlimit != "") {//jen pro strednik-aby byl za kazdou ip, ale ne za posledni
                        $seznam_nadlimit = $seznam_nadlimit . "; ";
                    }
                    $seznam_nadlimit = $seznam_nadlimit . $aktual; //pridam ip do vysledku
                }
            }
            $pocet = 0; //reset poctu
            $index++;
        }
        if ($seznam_nadlimit == "") {//pokud zadna ip nenalezena -> vysledek=OK
            $seznam_nadlimit = "ok";
        }
        return $seznam_nadlimit; //vratim vysledek
Tento kod jsem zkoušel na selectu 50 000 ip a výsledek zobrazí do 2.5 sekund+-
1Pupik1989
Začátečník
Začátečník
Registrován: 20. říj 2011
Bydliště: Dvůr Králové nad Labem

Re: mysql tabulka s miliony řádků - select...

Příspěvek od 1Pupik1989 »

Test na 50000 řadcích.

Zobrazeny záznamy 0 - 5 ( 6 celkem, Dotaz trval 0.0638 sekund)

8249 aaa
8266 bbb
8455 ccc
8205 ddd
8120 eee
8705 fff

Klidně můžu zkusit i random IP.

//edit:

Zobrazeny záznamy 0 - 5928 ( 5 929 celkem, Dotaz trval 0.1747 sekund) [ipaddress: 0.1.227.186 - 99.92.203.110]
Naposledy upravil(a) 1Pupik1989 dne čtv 18. dub 2013, 14:58, celkem upraveno 1 x.
CPU: AMD Phenom II x4 955BE @ 4GHz FAN: Arctic Cooling Freezer Xtreme rev.2
MB: MSI 760GM-E51
RAM: Kingston 2x4Gb RAM DDR3 1333 @ 1466MHz
GPU: Gigabyte Radeon HD 6850 OC 985/1260MHz
HDD: WD Caviar Green WD10EARX 1TB SATAIII/600, ZDROJ: Fortron FSP550-APN (550W)
Exp
Začátečník
Začátečník
Registrován: 24. črc 2004
Bydliště: Tábor
Kontaktovat uživatele:

Re: mysql tabulka s miliony řádků - select...

Příspěvek od Exp »

DB je samozřejmě násobně rychlejší než PHP, řešit to přes PHP je hrubá neoptimalizace.
Václav Sedlář
Žlutá karta Bazar
Žlutá karta Bazar
Registrován: 01. dub 2006
Bydliště: Liberec
Kontaktovat uživatele:

Re: mysql tabulka s miliony řádků - select...

Příspěvek od Václav Sedlář »

1Pupik1989:
Líbí se mi tento tvůj kod:

Kód: Vybrat vše

SELECT COUNT(id) as cnt, ipaddress FROM `table` GROUP BY ipaddress HAVING cnt > 1
jenže on prochází celou tabulku (a ta tabulka, která se pak u mě bude procházet má 200 milionu záznamů + mám info (nenavrhoval jsem ji já), že tato tabulka je optimalizovaná na zápis, ne čtení (nevím jak...))...

Takže ještě dopasovat do toho, aby procházel třeba jen 200 000 posledních ip...Nebo myslíš, že to bude jedno, že to prochází celou tabulku?

Vyzkoušel bych to sám, jenže si dbs nemůžu stáhnout k sobě na localhost a musím to zkoušet na ostré dbs, která je na hostingu...
webwalker
Začátečník
Začátečník
Uživatelský avatar
Registrován: 03. úno 2010
Bydliště: Buranov vedle Prahy

Re: mysql tabulka s miliony řádků - select...

Příspěvek od webwalker »

Pupíkovo řešení je imho nejlepší a Exp má pravdu, všehno bych to nechal na DB (včetně Order) :)
Chtěl bych se stát profesionálním pískačem. Už teď jsem v tom sice hvězda, ale chtěl bych se ještě zdokonalit a začít se tím živit.
GPUreport.cz
Václav Sedlář
Žlutá karta Bazar
Žlutá karta Bazar
Registrován: 01. dub 2006
Bydliště: Liberec
Kontaktovat uživatele:

Re: mysql tabulka s miliony řádků - select...

Příspěvek od Václav Sedlář »

Tak jsem si na testy vytvořil na svém localhostu svojí tabulku v databázi, jenže jak ji teď naplnit tolika miliony řádky (stačí čísla rand(min, max))? Máte nějaký nápad, jak to co nejrychleji zapsat do dbs?
1Pupik1989
Začátečník
Začátečník
Registrován: 20. říj 2011
Bydliště: Dvůr Králové nad Labem

Re: mysql tabulka s miliony řádků - select...

Příspěvek od 1Pupik1989 »

Ono moje řešení je vlastně to co dělá phpmyadmin. Stačí akorát najet na strukturu tabulky a kliknout na "Procházet odlišné hodnoty". Používat to pořád a nikdy jsem neměl nějaký problém.

Naplníš jí třeba i pomocí php, omezení je ale bohužel defaultně na 5000 záznamů, dá se to ale nastavit (leč nevím kde).

U té tabulky s IP adresami asi bude stačit vyhledat IP od nějaké doby. Není potřeba vědět co se tam dělo přes rok. Já většinou mažu záznamy starší jak měsíc, protože ty mě jsou k ničemu. Předtím ale vytáhnu počet přihlášení a zapíšu si je zvlášť do tabulky. Nicméně tedy nemám jen IP adresy, ale hash IP+USER_AGENT.
CPU: AMD Phenom II x4 955BE @ 4GHz FAN: Arctic Cooling Freezer Xtreme rev.2
MB: MSI 760GM-E51
RAM: Kingston 2x4Gb RAM DDR3 1333 @ 1466MHz
GPU: Gigabyte Radeon HD 6850 OC 985/1260MHz
HDD: WD Caviar Green WD10EARX 1TB SATAIII/600, ZDROJ: Fortron FSP550-APN (550W)
Václav Sedlář
Žlutá karta Bazar
Žlutá karta Bazar
Registrován: 01. dub 2006
Bydliště: Liberec
Kontaktovat uživatele:

Re: mysql tabulka s miliony řádků - select...

Příspěvek od Václav Sedlář »

1Pupik1989 píše:...
Naplníš jí třeba i pomocí php, omezení je ale bohužel defaultně na 5000 záznamů, dá se to ale nastavit (leč nevím kde).
No já to zkoušel - udělal jsem si jednoduchý skript, kde je cyklus for (od 0 do 50000000) a který každým průchodem zapíše jednu hodnotu...tot sice funguje, ale jen do nastaveného ma. času php scriptu...Tedy když jsem nastavil čas na 10 minut, tak to těch deset minut zapisovalo, ale stihlo se zapsat asi jen 18xxx záznamů...
1Pupik1989 píše:U té tabulky s IP adresami asi bude stačit vyhledat IP od nějaké doby. Není potřeba vědět co se tam dělo přes rok. Já většinou mažu záznamy starší jak měsíc, protože ty mě jsou k ničemu. Předtím ale vytáhnu počet přihlášení a zapíšu si je zvlášť do tabulky. Nicméně tedy nemám jen IP adresy, ale hash IP+USER_AGENT.
No zadání je takové:
funkce, které předám počet posledních x návštěv a limit, kolikrát se v tom počtu může daná ip vyskytovat...

A tak jsem právě bral selectem záznamy, které mají id větší než max(id) - počet...(id je PK a autoincrement)...

Proto jsme zkoušel dát do tvého kodu tu stejnou podmínku, ale buď ji dávám do špatného místa, nebo nevím, ale stejně to prolezlo celou tabulku...Stačilo by mi tedy nějak doplnit do tvého:

Kód: Vybrat vše

SELECT COUNT(id) as cnt, ip FROM `tabulka` GROUP BY ip HAVING cnt > 1
tuto podmínku:

Kód: Vybrat vše

WHERE id > (SELECT MAX(id) - pocet_vzorku
1Pupik1989
Začátečník
Začátečník
Registrován: 20. říj 2011
Bydliště: Dvůr Králové nad Labem

Re: mysql tabulka s miliony řádků - select...

Příspěvek od 1Pupik1989 »

Zkus:

Kód: Vybrat vše

SELECT COUNT(id) as cnt, ipaddress 
FROM `table` 
GROUP BY ipaddress 
HAVING cnt > 1 
ORDER BY id DESC
LIMIT 0, 50000
Zobrazeny záznamy 0 - 5928 ( 5 929 celkem, Dotaz trval 0.1555 sekund) [id: 46417 - 1]
CPU: AMD Phenom II x4 955BE @ 4GHz FAN: Arctic Cooling Freezer Xtreme rev.2
MB: MSI 760GM-E51
RAM: Kingston 2x4Gb RAM DDR3 1333 @ 1466MHz
GPU: Gigabyte Radeon HD 6850 OC 985/1260MHz
HDD: WD Caviar Green WD10EARX 1TB SATAIII/600, ZDROJ: Fortron FSP550-APN (550W)
Václav Sedlář
Žlutá karta Bazar
Žlutá karta Bazar
Registrován: 01. dub 2006
Bydliště: Liberec
Kontaktovat uživatele:

Re: mysql tabulka s miliony řádků - select...

Příspěvek od Václav Sedlář »

Takže pokud chápu dobře, tak cnt je ve výsledku kolkrát se tam daná ip nacházi (ve výběru 0-50000)...

Takže to asi nefunguje, protože když jsem si na svojí testovací tabulku dal limit 0-300, tak stejně tam byl jeden záznam 1300krát a pod...

ALE: Toto funguje (jen nevím, jak moc je to náročné):

Kód: Vybrat vše

SELECT ip, COUNT( ip ) AS pocet_vyskytu FROM tabulka WHERE id > ( SELECT max( id ) - pocet_od_konce FROM tabulka ) GROUP BY ip ORDER BY pocet_vyskytu DESC
Edit: Tak jsem to hodil do php a před dotaz do dbs jsem dal $start = microtime(true); a hned za dotaz jsem dal $stop = microtime(true); a vypis $stop-$start mi dal výsledek 1.7153949737549, přičemž hodnota "pocet_od_konce" byla 50 000...

Edit2: Když odeberu řazení (order by), tak se vysledek zlepší na 1.2085599899292

Edit3: Tak jsem to zkusil dát "pocet_od_konce" na 100 000 a když nechám order by, tak:
dotaz na dbs: 2.7618110179901
zpracovani php po dotazu: 0.0084078311920166
celkový čas: 2.7702429294586

Když smažu order by a necham na php zjištění která ip přesahuje limit, tak:
dotaz na dbs: 2.484915971756
zpracovani php po dotazu: 0.033513069152832
celkový čas: 2.5184440612793

Ale je pravda, že dbs je na hostingu a já se k ní připojuji z mého localhostu...+právě naše stránky prohlíží několik stovek lidí...Takže teď přemýšlím, zda nechat order by a nezatěžovat php, nebo naopak...
1Pupik1989
Začátečník
Začátečník
Registrován: 20. říj 2011
Bydliště: Dvůr Králové nad Labem

Re: mysql tabulka s miliony řádků - select...

Příspěvek od 1Pupik1989 »

A jo, já jsem blb. LIMIT v tom mém případě vlastně nemůže být s GROUP BY.

Zkuste:

Kód: Vybrat vše

SELECT COUNT(iptable.ipaddress) as cnt, iptable.ipaddress 
FROM (
  SELECT ipaddress
  FROM `test`
  ORDER BY id DESC
  LIMIT 3000
) as iptable
GROUP BY iptable.ipaddress
HAVING cnt > 1
Ten už by měl jít, respektive jde.

Zobrazeny záznamy 0 - 29 ( 929 celkem, Dotaz trval 0.0156 sekund)
Celkový počet 3000.

Microtime: 0.032025098800659

Vlastně vezmete tabulku, vytáhnete z ní určitý počet záznamů (tady mám 3000). Jelikož nepotřebujete celou. No a pak je to celé stejné jako předtím. "iptable" je vlastně "table" oříznutá pouze na 3000 záznamů. Takže se vybírá jen z těch 3000 nikoliv z 50000.

//edit: Dal jsem LIMIT na 50000 a výsledky:

Zobrazeny záznamy 0 - 29 ( 5 929 celkem, Dotaz trval 0.2965 sekund)
CPU: AMD Phenom II x4 955BE @ 4GHz FAN: Arctic Cooling Freezer Xtreme rev.2
MB: MSI 760GM-E51
RAM: Kingston 2x4Gb RAM DDR3 1333 @ 1466MHz
GPU: Gigabyte Radeon HD 6850 OC 985/1260MHz
HDD: WD Caviar Green WD10EARX 1TB SATAIII/600, ZDROJ: Fortron FSP550-APN (550W)
Václav Sedlář
Žlutá karta Bazar
Žlutá karta Bazar
Registrován: 01. dub 2006
Bydliště: Liberec
Kontaktovat uživatele:

Re: mysql tabulka s miliony řádků - select...

Příspěvek od Václav Sedlář »

Díky...toto funguje, ale má to nejhorší čas...Když jsem dal za limit 50 000 a having cnt > 10 000 tak čas dotazu byl 3.3762540817261

Edit: zkusil jsem ještě jednou a dotaz trval 2.05...Což je ale pořád víc než před tím (před tím bylo vzorků 70 000 a trvalo to pod 2 sekundy
webwalker
Začátečník
Začátečník
Uživatelský avatar
Registrován: 03. úno 2010
Bydliště: Buranov vedle Prahy

Re: mysql tabulka s miliony řádků - select...

Příspěvek od webwalker »

2 x Select nebude imho nikdy kdovíjak rychlé, nicméně jde o to, jak často bude takovýto dotaz realizován. Pokud to nebude nějaký běžný/častý dotaz na DB, vůbec bych časy neřešil a snažil bych se z toho dostat maximum komfortu pro aplikaci (tedy včetně požadovaného sortu).
Chtěl bych se stát profesionálním pískačem. Už teď jsem v tom sice hvězda, ale chtěl bych se ještě zdokonalit a začít se tím živit.
GPUreport.cz
Odpovědět

Zpět na „Programování a web“