Indexace tabulky pro rychlé vyhledávání – MySQL – Fórum – Programujte.com
 x   TIP: Přetáhni ikonu na hlavní panel pro připnutí webu

Indexace tabulky pro rychlé vyhledávání – MySQL – Fórum – Programujte.comIndexace tabulky pro rychlé vyhledávání – MySQL – Fórum – Programujte.com

 
Hledat
Moderní platforma pro vytvoření vašeho nového webu – Wix.com.
Nyní už můžete mít web zdarma.
Vybavení pro Laser Game
Spuštěn Filmový magazín
Laser Game Brno
Laser Game Ostrava

šnekr0
Newbie
15. 1. 2019   #1
-
0
-

Zdravím přátelé,

nikdy jsem toto neměl potřebu řešit, nicméně, vše je jednou poprve :-)

Mám script, který mi do MySql natáhne kompletně celou databázi ruianu (Seznam všech existujících adres/parcel v čr). Je to poměrně velké množství řádků, ale vyhledávání v takové DB trvá poměrně dlouho.

Potřebuju docílit co nejlepší odezvy na vyhledávání řádku kvůli "naštěpávači" adres.

Jak toto nejlépe vyřešit? Vyhledávání bude vždy na základě dvou sloupců (nejdřív ulice, pak město)..

Kolik by zabrala taková tabulka, která má cca 600MB v RAM aby byla stále k dispozici? Mám k dispozici VPS s 6G Ram a primárně tam pojede jen tato aplikace... Data v ní se měnit nebudou (max. 1 za měsíc)

Nahlásit jako SPAM
IP: 37.205.9.–
jerry
~ Anonymní uživatel
342 příspěvků
15. 1. 2019   #2
-
0
-

#1 šnekr
to neni tabulka to co hledáš je tzv. binární vyhledávací strom a obvykle se píše v C/C++, a pro WEB aplikaci pak asi v Javě nebo C#.  Pokud data - tabulka se všemi adresami v čr má 600 mega tak v paměti bude zabírat taky 600mega + 32 bajtů pro každý žádek tabulky abys moch sestavit vyhledávací strom. S ohledem na to, že RAM muže být dnes klidně 64GB DDR5 4200 tak to neni žádnej velkej problém. Akorát ten kod si budeš muset v něčem pasat ... ideální by bylo kdyby to bylo v native C x64 napsaný třeba ve MS VS 2015/17 a běželo to na serveru. C++ ti ubere na výkonnosti cca 6-8 procent.

Nahlásit jako SPAM
IP: 2a00:1028:83be:235a:50e3:44aa:8912:ad05...–
jerry
~ Anonymní uživatel
342 příspěvků
15. 1. 2019   #3
-
0
-

#2 jerry
https://codingsight.com/using-indexes-in-sql-server-memory-optimized-tables/

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-index-create-memory-server-configuration-option?view=sql-server-2017

https://www.quora.com/How-does-SQL-indexing-work-with-memory-Where-is-the-index-stored

https://www.mssqltips.com/sqlservertip/3136/getting-started-with-indexes-on-sql-server-memory-optimized-tables/

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2017/11/02/in-memory-oltp-indexes-part-1-recommendations/

Nahlásit jako SPAM
IP: 2a00:1028:83be:235a:50e3:44aa:8912:ad05...–
jerry
~ Anonymní uživatel
342 příspěvků
15. 1. 2019   #4
-
0
-

#3 jerry
a to nejduležitější:

https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/requirements-for-using-memory-optimized-tables?view=sql-server-2017

Nahlásit jako SPAM
IP: 2a00:1028:83be:235a:50e3:44aa:8912:ad05...–
šnekr0
Newbie
15. 1. 2019   #5
-
0
-

Ted uplně nechápu ten binární strom vyhledávání?

Takže myslíte, že by byla vhodná kombinace udělat klasickou mysql tabulku a při spuštěnní VPS vytvořit duplikát jako memory tabulku?

Napadlo mě použít na třídění nějakej mechanizmus v jave a jen to posílat pomocí metody post, ale furt ty data někde musej bejt uložený...

Myslíte, že to bude furt pomalý, když v udělám memory tabulku a budu v ní klasicky vyhledávat pomocí PDO a LIKE%%?

Nahlásit jako SPAM
IP: 185.193.86.–
Kit+14
Guru
15. 1. 2019   #6
-
0
-

#1 šnekr
To se nedělá v RAM, ale přímo v databázi. K tabulce se vytvoří index nad sloupci ulice a město. Databáze si s tím sama poradí a bude to rychlé.

Nahlásit jako SPAM
IP: 46.174.34.–
Komentáře označují místa, kde programátor udělal chybu nebo něco nedodělal.
šnekr
~ Anonymní uživatel
26 příspěvků
15. 1. 2019   #7
-
0
-

#6 Kit
Jaký druh indexu?

Nahlásit jako SPAM
IP: 185.193.86.–
jerry
~ Anonymní uživatel
342 příspěvků
16. 1. 2019   #8
-
0
-

#7 šnekr
no máš přeci 2 možnosti 1/ buď si to napíšeš sám a budeš udržovat paralelní tabulku ve formě binárního stromu v paměti ram nebo 2/ použiješ možnost indexace v SQL:

https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/requirements-for-using-memory-optimized-tables?view=sql-server-2017

co je na tom nepochopitelnýho ?

ty si asi neměl ve škole databáze že jo ?

Nahlásit jako SPAM
IP: 2a00:1028:83be:235a:1896:1f1f:6dda:d7a7...–
peter
~ Anonymní uživatel
3474 příspěvků
16. 1. 2019   #9
-
0
-

Mam tabulku, co to znamena?
Ja, kdyz mam tabulku, tam mam xml, xls, csv, txt, sql, html soubor.

Pokud dotaz polozis do fora MySQL, tak tak mas v MySQL vytvorenou tabulku.

Pokud pro vyhledavani pouzivas sql prikazy, ty obsahuji WHERE. Pro vsechny sloupce, kde pouzivas WHERE je treba nastavit INDEX. Bud pri vytvareni tabulky v sql prikazu CREATE a nebo dodatecne prikazem (nevim, z hlavy). Pokud to je delsi text, da se pouzit fulltextove vyhledavani.
Indexy se nastavuji pro pole typu integer a nebo 2-3 znaky pole typu char, varchar.
Pokud tam mas adresy, hadam, ze to mas inteligentne pres ciselniky, pomocne tabulky (id, text napr 1,Praha, 2, Ostrava, ...). Vyhledavani pak provadis pres tyto ciselniky a je to asi 1-20x rychlejsi nez jiny index. Pokud to nemas inteligentne, tak doporucuji si to tak udelat.

Pokud mas csv z excelu, muzes pouzit muj program.
https://mlich.zam.slu.cz/…as3-view.htm
Prochazet… nahrajes soubor a vyskoci ti filtry pres js. Je to samozrejme extremne pomale proti db. Netestovano na vic nez 1000 radku.

Nahlásit jako SPAM
IP: 2001:718:2601:258:4dbc:3838:5a25:f2e0...–
šnekr0
Newbie
16. 1. 2019   #10
-
0
-

Ano, jedná se o MySql Tabulku, když to dávám do této části fóra....

Spíš mě zajímalo jestli InnoDb, nebo MyISAM a pod. Ale to sem už pogooglil, že na selecty je nejvhodnější innoDB.

Nejspíš si vytvořim ještě jednu tabulku se seznamem obcí a před naštěpávačem člověk zadá město a až následně ulici...

Jiank tabulka má 2 915 744 řádků a 360MB

Nahlásit jako SPAM
IP: 37.205.9.–
peter
~ Anonymní uživatel
3474 příspěvků
16. 1. 2019   #11
-
0
-

Hele, nemohl bys dat priklad?
- treba 2-3 radku vsechny tabulky, ze kterych to skladas
- sql prikaz pro vyber
- sql prikaz pro vytvoreni tabulky (pripadne, pokud mas php myadmin, tak tam je moznost exportovat jen strukturu tabulky)

3.000.000 radku je dost, ale pro sql nic zvlastniho. Pokud mas udelane spravne indexy, where by si mel poradit  s rekneme 6 znaky, like asi take. Pokud to jsou takove 30 znakove a vetsi retezce, mozna bych uz uvazoval o fulltext indexu.

Pokud to neni tajne, tak dej link na cely export. Zipnuty, treba.

Pokud tam nemas ciselniky, no, to hodne stesti :) Minimalne bych udelal ciselnik obci. Jakoze sloupec s integerem a tabulku, ve ktere mas integer + nazev obce. Z ciselniku ziskas int cislo. S tim rychle vyfiltrujes treba 1000 radku a z toho uz dohledas zaznam.

Ja si myslim, ze s ciselniky bych to na localhostu dokazal rozjet na me sunce s hledanim do 500 ms, mozna pod 50-100. Ale nemam zkusenosti s vic nez 10.000 radky, zatim.

Nahlásit jako SPAM
IP: 2001:718:2601:258:4dbc:3838:5a25:f2e0...–
šnekr0
Newbie
16. 1. 2019   #12
-
0
-

Neni to tajný, ruian poskytuje tyto záznamy v csv veřejně a pomocí scriptu je importuju do DB:

function loadFile($fileName, $_db) {
    $folder = "csv";    
    $file = "$folder/$fileName";
    $handle = fopen($file, "r"); 
    try { 
        $query_ip = $_db->prepare('
            INSERT INTO adresni_mista (kod_adm,kod_obce,nazev_obce,c,nazev_momc,b,nazev_mop,kod_casti_obce,nazev_casti_obce,kod_ulice, nazev_ulice,typ_so,cislo_domovni,cislo_orientacni, znak_cisla_orientacniho, psc,souradnice_y,souradnice_x,plati_od) VALUES (
                ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?           
            )
        ');

        // unset the first line like this       
        fgets($handle);

        // created loop here
        while (($data = fgetcsv($handle, 1000, ";")) !== FALSE) {
            $query_ip->execute($data);
        }       

        fclose($handle);

    } catch(PDOException $e) {
        die($e);
    }
  
}
//nejdriv promazem db
$prep = $_db->prepare("TRUNCATE `ruian`.`adresni_mista`");
$prep->execute();


$files = scandir("csv");
foreach ($files as $file) {
    $pripona= substr($file, -4);
    if($pripona == ".csv") {
        loadFile($file, $_db);
    }
}

tabulka:

CREATE TABLE IF NOT EXISTS `adresni_mista` (
`id` int(11) NOT NULL,
  `kod_adm` int(11) NOT NULL,
  `kod_obce` int(11) NOT NULL,
  `nazev_obce` char(70) COLLATE utf8_czech_ci NOT NULL,
  `nazev_momc` text COLLATE utf8_czech_ci NOT NULL,
  `nazev_mop` text COLLATE utf8_czech_ci NOT NULL,
  `kod_casti_obce` int(11) NOT NULL,
  `nazev_casti_obce` text COLLATE utf8_czech_ci NOT NULL,
  `nazev_ulice` char(80) COLLATE utf8_czech_ci NOT NULL,
  `typ_so` text COLLATE utf8_czech_ci NOT NULL,
  `cislo_domovni` text COLLATE utf8_czech_ci NOT NULL,
  `cislo_orientacni` int(11) NOT NULL,
  `znak_cisla_orientacniho` text COLLATE utf8_czech_ci NOT NULL,
  `psc` int(11) NOT NULL,
  `souradnice_y` text COLLATE utf8_czech_ci NOT NULL,
  `souradnice_x` int(11) NOT NULL,
  `plati_od` datetime NOT NULL,
  `a` varchar(100) COLLATE utf8_czech_ci NOT NULL,
  `b` varchar(100) COLLATE utf8_czech_ci NOT NULL,
  `c` varchar(100) COLLATE utf8_czech_ci NOT NULL,
  `d` varchar(100) COLLATE utf8_czech_ci NOT NULL,
  `kod_ulice` int(11) NOT NULL
)


A vlastně momentálně co reálná adresa, to řádek.

Uvažuju nad rozdělením:

Tabulka Města ID(int), nazev(char 50). psc(int 5)
Tabulka adresy: ID(int), mesto(int), ulice (char 70), čp (int), čo(int), x(int),y(int)

A nejdřív bych podle naštěpávače chtěl po uživateli zadat město, těch by už němělo být 3mil, a v druhém formuláři bych teprve hledal adresy podle toho města... Jen ještě nevím podle jaké topologie v naštěpávači vyhledávat ulici, čp/čo zároveň....

V principu chci docílit toho, aby uživatel co nejjednodušeji vyplnil svojí adresu (jen existující podle ruianu)...

Samozřejmě bych ho mohl donutit napsat celou adresu a pak mu dát na výběr 5 nejlepšíc shod, ale to je pro uživatele dost nekomfortní

Nahlásit jako SPAM
IP: 93.99.138.–
Ovrscout
~ Anonymní uživatel
104 příspěvků
16. 1. 2019   #13
-
0
-

#7 šnekr Jaký druh indexu?

Pokud chceš hledat podle začátku textu tak B-Tree je co hledáš, jukni třeba do dokumentace ohledně indexů v mysql. Ještě to umí fulltext ale s tím moc zkušeností nemám.

Do více tabulek bych to zbytečně nerozděloval, nejdříve si pohraj s těmi indexy a vyzkoušej jak je to rychlé.
Vypiš si pomocí explain jestli tvé selecty používají indexy které jsi vytvořil.

Nahlásit jako SPAM
IP: 193.165.79.–
peter
~ Anonymní uživatel
3474 příspěvků
17. 1. 2019   #14
-
0
-

Vcera mne napadlo jeste par veci

- Trochu jsem googloval a db RÚIAN ma rozdeleni po mestech. Pokud to bude I s indexy pomale, tak bych kazde mesto/csv dal zvlast do tabulky a udelal  si pomocnou tabulku se seznamem mest.
- Mozna by slo dat tu db na ssd, ale pamet je asi rychlejsi

csv
- V tom create table nemas zadny index. google = mysql add index

ALTER TABLE `table` ADD INDEX `product_id` (`product_id`)

- Ten sql prikaz ty data uklada. Coz je super, ale neresi tvuj problem s vyhledavanim :)
- Id v create table neni ani index ani autoincrement, google = mysql set column to autoincrement

ALTER TABLE document MODIFY document_id INT AUTO_INCREMENT PRIMARY KEY
Nahlásit jako SPAM
IP: 2001:718:2601:258:4dbc:3838:5a25:f2e0...–
peter
~ Anonymní uživatel
3474 příspěvků
17. 1. 2019   #15
-
0
-

Indexy
Bohuzel, dokumentace, co posila Ovrscout odkaz k indexum je v anglictine. Ale googlem bys asi nasel i ceske navody.

Kdyz nastavis tabulce index, sql si vytvori sam pomocnou tabulku. Obvykle pismenko po pismenku. Coz je fajn, do 6 pismen nebo kratka slova snesitelne. Ale pro dlouhe texty se to moc nehodi.
Je mozne nastavit, jakym zpusobem ma index fungovat, treba jako btree. To muze nektere vyhledavani urychlit.


Druha vec, co fakt je treba udelat, jsou ty ciselniky. Tabulky, kde mas treba vsechny kraje, mesta, propojeni kraje-mesta a pod. To budou minitabulky o 1000 radcich. najdes v tom mesto, ziskas id_mesto a id_mesto budes mit v te hlavni tabulce. Takovy INT-2-4 znaky se hleda v obri tabulce daleko rychleji nez 15 znaku textu
To propojeni tabulek se da delat pres LEFT JOIN, napr.

Nahlásit jako SPAM
IP: 2001:718:2601:258:4dbc:3838:5a25:f2e0...–
šnekr0
Newbie
17. 1. 2019   #16
-
0
-

#15 peter
Blbě jsem zkopíroval export:

ID - primary - AI
nazev_obce a _ulice maji index....

Jinak diky, pokoukam jeste, proctu man (EN nevadi) a zkusim udelat ty pomocne tabulky se seznamem mest (Vypada to ze kazdy mesto ma kod_mesta, takze nebudu muset delat nove cisleniky... Novou pomocnou tabulku vlastne muzu vytvorit ze stavajici, jen si necham vypsat zaznamy ktere maji vice vysledku se stejnym nazvem a vypisu jen jeden... Dam pak echo, jak sem pokrocil a jak dlouho priblizne trva jeden dotaz... Kazdopadne co jsem procetl, tak nejidealnejsi je vzit tabulky a po startu VPS si natahnout data do druhe memory tabulky a cerpat z ni, na serveru, kde je 30VPS to bude vhodnejsi, nez cekat na brzdu co se tyce IO disku

Nahlásit jako SPAM
IP: 37.205.9.–
peter
~ Anonymní uživatel
3474 příspěvků
17. 1. 2019   #17
-
0
-

Distinct se da pouzit na unikatni radek

SELECT DISTINCT column1, column2, ...
FROM table_name; 
Nahlásit jako SPAM
IP: 2001:718:2601:258:4dbc:3838:5a25:f2e0...–
Zjistit počet nových příspěvků

Přidej příspěvek

Toto téma je starší jak čtvrt roku – přidej svůj příspěvek jen tehdy, máš-li k tématu opravdu co říct!

Ano, opravdu chci reagovat → zobrazí formulář pro přidání příspěvku

×Vložení zdrojáku

×Vložení obrázku

Vložit URL obrázku Vybrat obrázek na disku
Vlož URL adresu obrázku:
Klikni a vyber obrázek z počítače:

×Vložení videa

Aktuálně jsou podporována videa ze serverů YouTube, Vimeo a Dailymotion.
×
 
Podporujeme Gravatara.
Zadej URL adresu Avatara (40 x 40 px) nebo emailovou adresu pro použití Gravatara.
Email nikam neukládáme, po získání Gravatara je zahozen.
-
Pravidla pro psaní příspěvků, používej diakritiku. ENTER pro nový odstavec, SHIFT + ENTER pro nový řádek.
Sledovat nové příspěvky (pouze pro přihlášené)
Sleduj vlákno a v případě přidání nového příspěvku o tom budeš vědět mezi prvními.
Reaguješ na příspěvek:

Uživatelé prohlížející si toto vlákno

Uživatelé on-line: 0 registrovaných, 3 hosté

 

Hostujeme u Českého hostingu       ISSN 1801-1586       ⇡ Nahoru Webtea.cz logo © 20032019 Programujte.com
Zasadilo a pěstuje Webtea.cz, šéfredaktor Lukáš Churý