Lūdzu palīdzību SQL valodā

[gintsp]

1kārt var protams abstrakti runāt par SQL teikumu un mēģināt izdomāt, vai tas ir labs vai nē. Taču no vienkāršas blenšanas tam virsū, ja neesi hiper mega super speciālists (un arī tad ne vienmēr), nesapratīsi, vai tas ir labāks par otru, ja vien tas nav acīmredzams.

1mais solis attiecīgajā virzienā ir pārbaudīt izpildes plānu kā tas rakstīts šeit.

Nākošais solis, protams, ir saprast ko tas kas tur rakstīts nozīmē un kas ir labāk.

Š eit var palīdzēt MySQL dokumentācija kā redzams šeit.

Vēl nākošais solis ir panākt tā uzlabošanos. Tas diemžēl ir pats sarežģītākais solis un pāris teikumos to pastāstīt būs grūti.

Tātad runājot par konkrēto keisu:

izveidoju šāds tabulas ar šādiem sākotnējiem datiem:

Code:

create table goods (
god_id int not null primary key,
name varchar(50) not null
);

create table prices (
prc_id int not null primary key,
prc_god_id int not null,
price float,
prc_time datetime
);

insert into goods values (1, ‘aaa’);
insert into goods values (2, ‘bbbb’);
insert into goods values (3, ‘ccc’);
insert into goods values (4, ‘ddd’);
insert into goods values (5, ‘eee’);
insert into goods values (6, ‘fff’);
insert into goods values (7, ‘ggg’);
insert into goods values (8, ‘hhh’);
insert into goods values (9, ‘iii’);
insert into goods values (10, ‘jjj’);

insert into prices values (1, 1, 10, now());
insert into prices values (2, 1, 20, now()-100);
insert into prices values (3, 1, 30, now()-200);
insert into prices values (4, 2, 30, now());
insert into prices values (5, 2, 20, now()-100);
insert into prices values (6, 2, 10, now()-200);
insert into prices values (7, 3, 10, now());
insert into prices values (8, 3, 20, now()-100);
insert into prices values (9, 4, 20, now());
insert into prices values (10, 4, 10, now()-100);
insert into prices values (11, 5, 10, now());
insert into prices values (12, 6, 10, now());
insert into prices values (13, 7, 10, now());
insert into prices values (14, 7, 20, now());
insert into prices values (15, 8, 20, now());
insert into prices values (16, 9, 20, now());
insert into prices values (17, 10, 20, now());

alter table prices add constraint prc_god_fk foreign key (prc_god_id)
references goods(god_id);

Lai datu daudzums nebūtu simbolisks, saģenerējam kādu kluci klāt.

Code:

insert into goods select god_id + 10, name from goods;
insert into goods select god_id + 20, name from goods;
insert into goods select god_id + 40, name from goods;
insert into goods select god_id + 80, name from goods;
insert into goods select god_id + 160, name from goods;
insert into goods select god_id + 320, name from goods;
insert into goods select god_id + 640, name from goods;
insert into goods select god_id + 1280, name from goods;
insert into goods select god_id + 2560, name from goods;
insert into goods select god_id + 5120, name from goods;
insert into goods select god_id + 10240, name from goods;
insert into goods select god_id + 20480, name from goods;
insert into goods select god_id + 40960, name from goods;

insert into prices
select prc_id + 20, prc_god_id + 10, price, prc_time from prices;
insert into prices
select prc_id + 40, prc_god_id + 20, price, prc_time from prices;
insert into prices
select prc_id + 80, prc_god_id + 40, price, prc_time from prices;
insert into prices
select prc_id + 160, prc_god_id + 80, price, prc_time from prices;
insert into prices
select prc_id + 320, prc_god_id + 160, price, prc_time from prices;
insert into prices
select prc_id + 640, prc_god_id + 320, price, prc_time from prices;
insert into prices
select prc_id + 1280, prc_god_id + 640, price, prc_time from prices;
insert into prices
select prc_id + 2560, prc_god_id + 1280, price, prc_time from prices;
insert into prices
select prc_id + 5120, prc_god_id + 2560, price, prc_time from prices;
insert into prices
select prc_id + 10240, prc_god_id + 5120, price, prc_time from prices;
insert into prices
select prc_id + 20480, prc_god_id + 10240, price, prc_time from prices;
insert into prices
select prc_id + 40960, prc_god_id + 20480, price, prc_time from prices;
insert into prices
select prc_id + 100000, prc_god_id + 40960, price, prc_time from prices;

Tātad skatamies, kā MySQLs taisās izpildīt šito:

Code:

mysql> explain
-> select q.prc_god_id, price, name
-> from goods
-> inner join prices
-> on (god_id = prc_god_id)
-> inner join (
-> select prc_god_id, max(prc_time) max_prc_time
-> from prices
-> group by prc_god_id) q
-> on (prices.prc_god_id = q.prc_god_id
-> and prices.prc_time = q.max_prc_time);
+—-+————-+————+——–+—————+————+———+————–+——–+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+————+——–+—————+————+———+————–+——–+————-+
| 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 81920 | |
| 1 | PRIMARY | goods | eq_ref | PRIMARY | PRIMARY | 4 | q.prc_god_id | 1 | |
| 1 | PRIMARY | prices | ref | prc_god_fk | prc_god_fk | 4 | q.prc_god_id | 8 | Using where |
| 2 | DERIVED | prices | index | NULL | prc_god_fk | 4 | NULL | 139664 | |
+—-+————-+————+——–+—————+————+———+————–+——–+————-+

Lai pārliecinātos, cik ilgi tas varētu iet (jāņem vērā, ka datu parādīšana aizņems ļoti daudz laika, tāpēc’atlasam vienkārši sum(price)), palaižam vaicājumu:

Code:

mysql> select sum(price) from (
-> select q.prc_god_id, price, name
-> from goods
-> inner join prices
-> on (god_id = prc_god_id)
-> inner join (
-> select prc_god_id, max(prc_time) max_prc_time
-> from prices
-> group by prc_god_id) q
-> on (prices.prc_god_id = q.prc_god_id
-> and prices.prc_time = q.max_prc_time)
-> )q1;
+————+
| sum(price) |
+————+
| 1474560 |
+————+
1 row in set (3.67 sec)

nav nemaz tik slikti priekš mana mazā lapša ar nīkulīgo noklusēto mysql konfigurāciju, ņemot vērā, ka vien’tabulā ir 80k ierakstu, otrā 140 K

varam pasākumu mazliet uzlabot pieeliekot indexu uz visām 3 iesaistītajām kolonām

Code:

mysql> create index prc_idx on prices (prc_god_id, prc_time, price);
Query OK, 139264 rows affected (7.45 sec)
Records: 139264 Duplicates: 0 Warnings: 0

tad izpildes plāns drusku pamainās

Code:

mysql> explain
-> select q.prc_god_id, price, name
-> from goods
-> inner join prices
-> on (god_id = prc_god_id)
-> inner join (
-> select prc_god_id, max(prc_time) max_prc_time
-> from prices
-> group by prc_god_id) q
-> on (prices.prc_god_id = q.prc_god_id
-> and prices.prc_time = q.max_prc_time);
+—-+————-+————+——–+—————+———+———+—————————–+——–+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+————+——–+—————+———+———+—————————–+——–+————-+
| 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 81920 | |
| 1 | PRIMARY | goods | eq_ref | PRIMARY | PRIMARY | 4 | q.prc_god_id | 1 | |
| 1 | PRIMARY | prices | ref | prc_idx | prc_idx | 13 | q.prc_god_id,q.max_prc_time | 1 | Using index |
| 2 | DERIVED | prices | index | NULL | prc_idx | 18 | NULL | 130928 | Using index |
+—-+————-+————+——–+—————+———+———+—————————–+——–+————-+

un laiks arī mazliet samazinās

Code:

mysql> select sum(price) from (
-> select q.prc_god_id, price, name
-> from goods
-> inner join prices
-> on (god_id = prc_god_id)
-> inner join (
-> select prc_god_id, max(prc_time) max_prc_time
-> from prices
-> group by prc_god_id) q
-> on (prices.prc_god_id = q.prc_god_id
-> and prices.prc_time = q.max_prc_time)
-> )q1;
+————+
| sum(price) |
+————+
| 1474560 |
+————+
1 row in set (3.00 sec)

BTW man tabulu tips bija InnoDB.

[ob1]

Paldies, gintsp par saturīgo analīzi. Tomēr atļaušos pakomentēt.

1) Selekta ātrumu VAR novērtēt “uz aci”, ja vien ir zināšanas un pieredze.

Izpildes plānu var noemulēt galvā – šie selekti nav pārāk sarežģīti.

2) Analizējot viena selekta gadījumu, skaidri redzams, ka apakšselekts izpildīsies katrai galvenā selekta ierakstam atsevišķi.

Pilnīgi skaidri redzams, ka bremze būs… un paldies Tev, ka tagad zinu cik liela tā bremze ir.

3) 3 sekundes pie tik maziem datubāzes izmēriem ir slikts rezultāts. Vajadzētu kādu 0.1 sekundi vai mazāk.

---

Rezumējums – ir atrastas 2 iespējas kā risināt šādu uzdevumu:

1) Pirmais variants ir ar vienu samērā vienkāršu selektu, bet bremzīgs.

Code:

SELECT Goods.id, Goods.name, Price.price;
FROM ;
data1!goods ;
LEFT OUTER JOIN data1!price ;
ON Goods.id = Price.id_good;
WHERE Price.when IN (select max(when) from price where id_good=goods.id);
ORDER BY Goods.id

2) Otrais variants ir ar diviem selektiem, bet ir nepieciešama pagaidu tabula (kas ne vienmēr ir iespējams).

Code:

SELECT Goods.id, MAX(Price.when) as when;
FROM goods ;
LEFT JOIN price ON Goods.id = Price.id_good;
GROUP BY Goods.id ;
INTO CURSOR tc_1

SELECT tc_1.id, goods.name, price.price ;
FROM tc_1 ;
LEFT JOIN goods ON tc_1.id = goods.id ;
LEFT JOIN price ON tc_1.id = price.id_good AND tc_1.when = price.when ;
INTO CURSOR tc_2

Vai kāds nevar atrast trešo variantu – viens selekts, bet ātrs?

[gintsp]

Pirmkārt jautājums ar ko īstenībā vajadzēja sākt – kas par DB. Jo es te kaut ko bezsakarā rakstu par MySQL, bet tas taču nav MySQL, vai ne?

Katrai DB ir savas konkrētas fīčas, kas vienā ir, bet otrā nav.

ob1 wrote:

1) Selekta ātrumu VAR novērtēt “uz aci”, ja vien ir zināšanas un pieredze.

Izpildes plānu var noemulēt galvā – šie selekti nav pārāk sarežģīti.

Jā varēt var vienu no N plāniem, kas šķiet acīmredzmākais. Taču nekad par to nevari būt drošs, ja vien nezini

1) datu sadalījumu

2) neesi db vaicājumu izpildes optimizatora izstrādātājs. Nezinu kā šai konkrētajā db, taču pastāv vismaz 2 soļi normālas db vaicājumu izpildes būvēšanas laikā – vaicājuma transformācija, kur izpildoties noteiktiem nosacījumiem vaicājums var tikt vienkārši pārveidots, otrs solis vaicājuma optimizācija, kuras laikā balstoties uz info par datiem, vidi (environment) tiek izvēlēts konkrētais variants. Līdz ar to iesaistītie faktori ir pārāk daudz, lai varētu kaut ko droši pateikt. Piemēram Oracle savienojumus var transformēt par apakšvaicājumiem, ja tas ir ekvivalents un otrādi, savukārt atkarībā no pieejamās atmiņas apjoma var mainīt savioenojumu fizisko veidu no nested loops uz hash joiniem un otrādi. OK MySQLā, piemēram, iespēju vienkārši tik daudz nav, bet bez pārbaude stas ir minējums. Minējumi nerullē, ja ir iespēja noskaidrot konkrēti kā lietas notiek.

ob1 wrote:

2) Analizējot viena selekta gadījumu, skaidri redzams, ka apakšselekts izpildīsies katrai galvenā selekta ierakstam atsevišķi.

Pilnīgi skaidri redzams, ka bremze būs… un paldies Tev, ka tagad zinu cik liela tā bremze ir.

Starp citu vai Tu paskatījies manis doto variantu? Tur nav apakšvaicājumu. Tur tie netiks pildīti katru reizi katrai virsselketā atlasītajai rindiņai.

Tevis dotajā variantā tiešām ir apakšvaicājumā un konkrēti uz tiem pašiem datiem man tas izskatās šadi:

Code:

mysql> explain
-> select prc_god_id, price, name
-> from goods
-> left outer join prices
-> on prc_god_id = god_id
-> where prc_time in (
-> select max(prc_time) from prices where prc_god_id = goods.god_id)
-> order by god_id;
+—-+——————–+——–+——+—————+———+———+——————-+——-+————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+——————–+——–+——+—————+———+———+——————-+——-+————————–+
| 1 | PRIMARY | goods | ALL | PRIMARY | NULL | NULL | NULL | 82370 | Using filesort |
| 1 | PRIMARY | prices | ref | prc_idx | prc_idx | 4 | test.goods.god_id | 1 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | prices | ref | prc_idx | prc_idx | 4 | test.goods.god_id | 1 | Using index |
+—-+——————–+——–+——+—————+———+———+——————-+——-+————————–+
mysql> select sum(price) from (
-> select prc_god_id, price, name
-> from goods
-> left outer join prices
-> on prc_god_id = god_id
-> where prc_time in (
-> select max(prc_time) from prices where prc_god_id = goods.god_id)
-> order by god_id) a;
+————+
| sum(price) |
+————+
| 1474560 |
+————+
1 row in set (7.20 sec)

Bet ja Tev ir cita DB, tad šādi runāt ir bezjēdzīgi.

ob1 wrote:

3) 3 sekundes pie tik maziem datubāzes izmēriem ir slikts rezultāts. Vajadzētu kādu 0.1 sekundi vai mazāk.

Gribēt nevienam, protams, nav aizliegts, bet uz mana konkrētā dzelža tas neiet cauri, jo tur vienkāršs sum aizņem 0.2 sek un sum no abu tabulu savienojuma pussekundi.

Otrkārt jautājums – ko īstenībā vajag?

Summārus datus? Vai vienkāršus detalizētus datus? Ja pēdējo, tad datu apjoms nevienā normālā aplikācijā nevar būt īpaši liels, jo ntos tūkstošus ierakstu neviens klients nenolasīs no db un neattēlos 0.1 sekundes laikā, tas ir bezcerīgi. Ntos tūkstošus datu neviens lietotājs ikdienā vienā brīdī nelieto. Ja tā ir atskaite, tad tai ir pavisam citas ātrdarbības prasības.

Tas tā pārdomām 🙂

[ob1]

-> gintsp

Vēlreiz Paldies. Bet:

1) Tur jau tā lieta, ka datubāze var būt jebkura. Lietoju FoxPro, kas māk pieslēgties pie gandrīz jebkuras bāzes.

2) Tev taisnība – nebiju iedziļinājies Tavā selektā, paskatījos, ka mainīgo vārdi ir mainīti un subselekts arī kaut kāds ir…

Vispār jau Tev taisnība par tiem lauku nosaukumiem – manējie izmanto atslēgas vārdus…

Ok, tad mums ir 3. variants:

Code:

select q.id_good, price, name ;
from goods ;
inner join price on (price.id_good = goods.id) ;
inner join ( ;
select id_good, max(when) max_when ;
from price ;
group by id_good) q ;
on (price.id_good = q.id_good ;
and price.when = q.max_when) ;

3) Ko vajag? Vajag principu kā ar tādām datu struktūrām strādāt. Zinu, ka projektā tas būs vajadzīgs.

Nu un tā kā ar datubāzi darbojos caur FoxPro, tad nevaru izmantot visādas izvirtības (piem. temp tabulas).

Visdrīzāk, ka šāda veida selekts būs kā apakšelekts kaut kam lielākam, tāpēc arī cepos par ātrdarbību.

4) Padzenāju uz ātrumu, tikai tabulā ‘price’ piemetu 32x vairāk ierakstu (citādi viss notika zibenīgi).

Variants 1 – 26 sekundes.

Variants 2 – 14 sekundes.

Variants 3 – 14 sekundes.

5) Testēšana parādīja, ka visi 3 varianti ir maigi izsakoties nekorekti.

Varianti 1 un 3 rezultātos neiekļauj preci, kurai nav atbilstoša ieraksta tabulā ‘price’.

Visi trīs varianti atgriež liekus ierakstus, ja tabulā ‘price’ ir vairāki ieraksti ar vienādu ‘id_good’ un ‘when’. (tavā piemērā ar id=7)

Zinu, jau zinu, tas uzdevumā nebija atrunāts… nu bet īsti korekti tas nav…

Tātad papildinu nosacījumus:

1) Ja preces nav ‘price’ tabulā, tad ir jāatgriež id, name, bet price laukam ir jābūt NULL.

2) Ja tabulā ‘price’ ir vairāki ieraksti ar vienādiem ‘id_good’ un ‘when’,

tad rezultējošā tabulā vienalga ir jābūt vienam ierakstam ar šo preci.

[usver]

ob1 wrote:

Code:

inner join price on (price.id_good = goods.id) ;
inner join
..
;

5) Varianti 1 un 3 rezultātos neiekļauj preci, kurai nav atbilstoša ieraksta tabulā ‘price’.

šozanah? kur ir ponts lietot INNER JOIN un sūdzēties, ka “atlasa tikai sakrītošos”? 0_o takš lieto LEFT JOIN, ja vajag!

[ob1]

usver, variantā nr. 2 es tā arī darīju… bet pārējos variantus neizdomāju es…

Bet bugs nr. 2 ir arī ar left join…

[gintsp]

ob1 wrote:

usver, variantā nr. 2 es tā arī darīju… bet pārējos variantus neizdomāju es…

Bet bugs nr. 2 ir arī ar left join…

Bugs bija specifikācijā nevis kodā 😉

Bet atbildot uz jautājumu – jāuzliek group by:

Code:

select q.prc_god_id, max(price), name
from goods
left join prices
on (god_id = prc_god_id)
left join (
select prc_god_id, max(prc_time) max_prc_time
from prices
group by prc_god_id) q
on (prices.prc_god_id = q.prc_god_id
and prices.prc_time = q.max_prc_time)
group by prc_god_id, name;

Tagad par iepriekšējo:

>Tur jau tā lieta, ka datubāze var būt jebkura. Lietoju FoxPro, kas māk pieslēgties pie gandrīz jebkuras bāzes.

Opā. Elastīgi, dinamiski un hvz vēl kādi risinājumi praktiski nekad neiet roku rokā ar vārdu ātrs. Vismaz ne datubāzēs. Gala rezultātā anyway katrai DB šos vaicājumus nāksies skaņot atsevišķi. Pie tam atceries, ka nav tāda universāla SQLa. Tas ir sapnis. Tas ir kā ar mašīnām. Tu nevari gribēt ātri aizvest 7 tonnas kartupeļu no Rīgas līdz Daugavpilij ar Ferrari, Opeli un Kamazu. Tās visas ir atšķirīgas un visām ir savi atšķirīgi knifi. Protams, ir kopējas lietas – tabulas, kolonas un indeksi 😉 un vēl šis tas, bet skaņošana un vēlme izspiest maksimumu no konkrētās db ar to galīgi neaprobežojas.

>Visdrīzāk, ka šāda veida selekts būs kā apakšelekts kaut kam lielākam, tāpēc arī cepos par ātrdarbību.

Tātad ja šāda apkopojoša info būs nepieciešama bieži un daudz, tad ir vienkārši vērts sākt domāt par atvasinātām struktūrām un laukiem. Š eit vienkārši pie preces var glabāt klāt atvasinātu lauku pēdējā cena, kuru attiecīgi aizpilda aplikācija vai trigeris/whatever un tad nebūs jāmokās to atlasot.

[Autors]

Ieraksti