| Autor | Zpráva | ||
|---|---|---|---|
| nethor Profil |
#1 · Zasláno: 25. 1. 2018, 20:52:35
Zdravím,
mám v db velkou tabulku , cca 3 mil. řádků. CREATE TABLE `cenove_udaje` ( `Id` int(11) NOT NULL AUTO_INCREMENT, `Cislo` text COLLATE utf8_czech_ci, `DatumZplatneni` datetime DEFAULT NULL, `Listina` text COLLATE utf8_czech_ci, `TypNemovitosti` text COLLATE utf8_czech_ci, `Nemovitost` text COLLATE utf8_czech_ci, `Katastr` text COLLATE utf8_czech_ci, `IdNemovitost` bigint(11) DEFAULT NULL, PRIMARY KEY (`Id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci; Jakékoliv vyhledávání je v ní hrozně pomalé, např. dotaz select * from cenove_udaje where 1 order by TypNemovitosti asc limit 1
zabere cca 20s. Jak to jde zrychlit? Zkoušel jsem na sloupec `TypNemovitosti ` nasadit ordinální index i fulltext, ale vůbec to nepomohlo. Na tabulku potřebuju klást i o dost komplikovanější dotazy. |
||
| Kajman Profil |
#2 · Zasláno: 25. 1. 2018, 21:45:43
Jste si jistý, že TypNemovitosti má být typu 'text' - tedy řetězec dlouhý až 65535 znaků? Při vytváření indexu jste použil jak dlouhý začátek tohoto sloupce k zahrnutí do indexu? Jaký byl pak explain? Změnilo se něco, pokud se vyhodí nesmyslné where?
|
||
| nethor Profil |
#3 · Zasláno: 25. 1. 2018, 22:36:14
Kajman:
V SQL nejsem až tak kovaný. TypNemovitosti typ 'text' asi být nemusí, to je pravda. podle dotazu SELECT MAX(LENGTH(col)) FROM my_table má max 3 znaky.
Jaký typ je tedy nejvhodnější? TINYTEXT? Při vytváření indexu jsem původně zadal 16 znaků. Dotau 'Jaký byl pak explain?' nerozumím. Zkusil jsem teď : 1) Změnit TypNemovitosti na TINYTEXT 2) Vytvořit index o délce 3 znaků. Původní dotaz select * from cenove_udaje where 1 order by TypNemovitosti asc limit 1 stále kolem 20s.
Nesmyslný dotaz select * from cenove_udaje where 0 order by TypNemovitosti asc limit 1 0.031 s. Tedy ihned.
|
||
| pcmanik Profil |
nethor:
„Dotau 'Jaký byl pak explain?' nerozumím.“ Máš použiť explain a výpis z neho hodiť sem. Jednoducho doplň slovo EXPLAIN pred SELECT teda EXPLAIN SELECT * FROM ... „1) Změnit TypNemovitosti na TINYTEXT“ Vhodnejší bude varchar a ak má typ vždy 3 znaky tak rovno char(3). A väčšinou sa typy zvyknú ukladať ako číslo a až v aplikácií prevádzať na textovú hodnotu. |
||
| Kajman Profil |
#5 · Zasláno: 25. 1. 2018, 23:09:17
Je potřeba, abyste vypsal explain, tam bude vidět, zda index je k dispozici, zda ho použil a kolik řádků musela databáze projít. Tedy výsledek dotazu (s odstraněným nesmyslným where 1)
explain select * from cenove_udaje order by TypNemovitosti asc limit 1 Problémem může být velká četnost abecedně nejmenšího typu a špatného rozhodnutí plánovače (ignorace limit 1). Třeba by stačilo použít novější mysql nebo mariadb. Možná bych také zkusil typ sloupce převést. A taky bych zkusil udělat index nad kombinací dvou sloupců (TypNemovitosti, id) a pak do řazení přidat to id. select * from cenove_udaje order by TypNemovitosti asc, id asc limit 1 |
||
| nethor Profil |
#6 · Zasláno: 27. 1. 2018, 09:15:37
Díky za rady, o explain jsem neměl ani tušení a volba správného typu mi taky trochu uniká.
Přetypoval jsem TypNemovitosti na varchar(3) a přidal index na TypNemovitosti (3). Už to běhá podstattně rychleji. Dotaz select * from cenove_udaje order by TypNemovitosti asc, id asc limit 1 se zpracuje za 0.001 s.
Ještě nevím, jaký typ a index použít pro Katastr, tam je délka od 10 znaků po cca 1000 znaků. Dotaz na tento sloupec bude asi vždy like '%%' (v kombinaci s dalšími podmínkami).
Typ jsem zkusil text a index fulltext. zpracování takového dotazu trvá cca 3s. explain select * from cenove_udaje where `Katastr` LIKE '%757853%' LIMIT 300 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE cenove_udaje ALL NULL NULL NULL NULL 2880803 Using where |
||
| Kajman Profil |
#7 · Zasláno: 27. 1. 2018, 09:32:52
Na to je dobré používat číselníky (mrkněte na normální formy).
Uděláte si tabulku katastrů katastr_id (cislo) katastr_nazev (retezec) Vyplníte ji a v tabulce cenových údajů se pak budete mít to číslo katastr_id místo názvu katastru. Při vyhledávání díky katastr_nazev like '%757853%' (které nelze zrychlit indexem) se projde jen tabulka katastrů (řekněme 13000 řádků) a podle id, která vyhoví se již rychle dokází najít záznamy z cenových udajů, pokud nad tím číslem bude index. |
||
| nethor Profil |
#8 · Zasláno: 28. 1. 2018, 19:16:43
Kajman:
Děkuji za zdroj, přečetl jsem a rozumím. V projektu už tabulku katastrů mám, trochu se ale obávám přidělit jim vlastní id z důvodu aktualizace. Když např. nějaké katastry přibydou (jako nedávno vojenské újezdy) musel bych je dočíslovat dodatečně. Nevím, .. aby to nedělalo paseku. Bezpečnější mi připadá nechat jako primární klíč číslo katastru. Nevím ale do jaké míry vadí, že jsou to šestimístná čísla (oproti 13000 úspora pouze jeden řád). U tab. cenove_udaje je problém, o kterém jsem se nezmínil; sloupec Katastr může obsahovat i více katastrů, např. Libichov 682799, Luštěnice 689106, Voděrady u Luštěnic 689114, Němčice u Luštěnic 702943 nabíze se A) Skrouhnout záznam pouze na čísla katastrů: 682799, 689106,689114,702943 a názvy katastrů dotáhnout z tabulky 'katastry' To nevím, jsetli pomůže, protože vlivem seperátoru musí sloupec asi zůstat typu text. B) Vytvořit pomocnou tabulku katastry_cenovych_udaju se sloupci IdCenovyUdaj a CisloKatastru
kde budou uloženy pouze čísla katastrů pro jednotliví cenové údaje. (pro jeden IdCenovyUdaj třeba i více CisloKatastru) .
Tabulka bude mít ale odhadem kolem 6 mil. řádků a budou komplikovnější i dotazy na vyhledávání. |
||
| Kajman Profil |
#9 · Zasláno: 28. 1. 2018, 19:54:03
b
|
||
| nethor Profil |
#10 · Zasláno: 28. 1. 2018, 20:49:43
:-) Děkuji.
|
||
| Keeehi Profil |
#11 · Zasláno: 28. 1. 2018, 22:20:44
nethor:
„Když např. nějaké katastry přibydou (jako nedávno vojenské újezdy) musel bych je dočíslovat dodatečně. Nevím, .. aby to nedělalo paseku.“ Paseku to nedělá. Naopak je docela nebezpečné používat jako primární klíč něco, nad čím nemáš kontrolu. Já vím, že to asi nehrozí, ale když se prostě stát rozhodně, že katastry se nově budou označovat nějak jinak, budeš mít problém. Toto ti nehrozí u umělého klíče, který si vytvoříš ty sám. Navíc je to velmi jednoduché. Stačí jeden unsigned int sloupec s nastaveným auto_increment a nové záznamy budou samy dostávat jedinečná ID, úplně nezávislá na ostatních sloupcích. „Nevím ale do jaké míry vadí, že jsou to šestimístná čísla (oproti 13000 úspora pouze jeden řád).“ To by bylo teoreticky jedno. |
||
| Kajman Profil |
#12 · Zasláno: 28. 1. 2018, 23:07:56
V tohle případě bych se možná číslo katastru nebál použít jako primární klíč.
|
||
|
Časová prodleva: 6 let
|
|||
0