MySQL(InnoDB) un FOREIGN KEY

[eT`]

čau visiem,

man te izveidojās diskusija vai ir vērts lietot MySQL foreign keys.

Piem:

 

TABLE - galleries

ID(PRIMARY)

TITLE

 

TABLE - pictures

ID(PRIMARY)

PATH

GALLERIES_ID(FK) ON UPDATE CASCADE, ON DELETE CASCADE

 

Kolēģis teica, ka MySQL nevajagot lietot jo baigi spiež uz performanci.

 

Bet, kas ir `labāk`?

bez FK: delete from pictures where gallery=ID;delete from galleries where id=ID

ar FK: delete from galleries where id=ID

[rpr]

performance būs jebkurā gadījumā zaudētāja, ja lietotsi fk.

izvērtē pats, kas tev ir dārgāks - performance vai datu kvalitāte. ja nav liels projekt, to performanci nejutīsi, bet pie pārsimts tūkstošiem jau gan.

[F3llony]

Ne tikai vērts, bet pat vajadzīgs. Jo īpaši lieliem projektiem, kur datu integritāte ir vitāla un performance->whatever trade-off arī.

[draugz]

Es personīgi FK lietoju tikai izstrādes vidē. Bet bez visiem CASCADE update,delet ,etc. Galvenā doma ir lai piefiksētu kurā vietā programma mēģina izmantot ierakstus, kas ir nepareizi. Produkcijā vienmēr izmantoju vienkāršus indexus jo FK izmantošana var novest pie katastrofālām sekām. Īpaši CASCADE delete. Protams visi mēs te esam guru utt, taču nedod dievs hakeri iegūst iespēju izpildīt DELETE uz vienas tabulas un tam automātiski sekos visas tabulas....

 

 

Ne tikai vērts, bet pat vajadzīgs. Jo īpaši lieliem projektiem, kur datu integritāte ir vitāla un performance->whatever trade-off arī.

Ka jau minēju uzskatu, ka FK ir labs tikai izstradē, lai uzbūvētu pareizu sistēmu. Kad tas ir izdarits FK ir lieka slodze serverim. Lielākā daļa MySQL guru iesaka izvairīties no FK lietošanas produkcijā.

Manuprāt, īpaši lielie projekti, izmanto citas DB un ja nu tomēr viņi izmanto MySQL, tad noteikti datu svarīgumu dēļ tiek izmantotas ari tranzakcijas... tranzakcijas + FK ir nāviga kombinācija. Gadījās redzet vienu projektu uz MsSQL, kur bija izmantota šī pieeja tranzakcijas + FK... Deadlocki šādā sistemā bija normāla parādiba, bija gadījumi, kad lidzēja tikai restarts :)

[vbz]

Ir 4 varianti:

1) iztikt tikai ar transakciju;

-- example with transactioon (sql "pseidocode")
begin;
select ... into table1
delete from table2 ... where table2.field1<>table2.field1;
delete from ...;
delete from ...;
-- rollback;
-- raise exception messages;
update table1 ...;
commit;

 

2. izmantot FK indeksu.

Tavā piemērā action on update būs izņēmuma gadījumi, faktiski var iztikt bez CASCADE. Relācijas inner join, left/right join veido starp PK un FK:

from table1 join table2 on table1.PK=table2.FK

 

Līdz ar to table2 pārējos datu laukus var brīvi klients labot bez kaskādes. Relācijas ir starp PK<->FK

Izņēmuma gadījumi - kaut kādas iekšējas darbības, maipulācijas (datu migrācijas, importi, exporti, reindex, darbības ar sekvencēm). Kādi ir riski ka tiek "nobrucināti" PK indeksi? Pastāv, bet minimāli, pats db admins sačakarē ... :) Drošības pēc var likt tavā piemērā FK ON UPDATE CASCADE, maz būs pieprasījumi, ka klients PK labos.

 

action on delete

Stipri individuāli, sistēmanalītiķa darbs. Jāatbild uz jautājumu - vai entītija zaudē savu vērtību, pamatfunkcionalitāti bez FK entītijas, vai tā spēj eksistēt bez FK entītijas? Ja spēj - ON DELETE NO ACTION, ja entītija tiek sabojāta, nespēj vairāk eksistēt - ON DELETE RESTRICT un exception apstrāde kodā.

Jāčeko kodā, kā tiek veidoti vaicājumi, kā tiek atrādīti dati - ar striktu sasaisti join(inner join) vai left/right join!

 

ON DELETE CASCADE - ļoti uzmanīgi, ja nelieto transakciju un nav procesa gaitas apstrādes, rezultāts var būt bēdīgs.

 

Pēdējos gadus vairāk sanāk strādāt ar PostgreSql, protams kaut kāda performance tiek zaudēta, bet sistēma nepaliek gliemeža ātrumā. Nepieciešams monitoringot, analizēt, profiling , kuri sistēmā ir vājie mezgli. Tad jautājums kā saglabāt datu integritāti? Par MySql(InnoDB) nemācēšu pateik, ļoti sen ar to strādājis, mož MySql tiešām ar FK paliek baigā bremze!

 

3. FK + tansactions

4. trigerēt šito konkrētajai tabulai. Faktiski gadījums, kad nepieciešama speciāla apstrāde - unikāla, tad konkrētajai tabulai izveido insert, update, delete triggers

Edited October 6, 2012 by vbz

[vbz]

Performance jau ir kompleks pasākums (neiedziļinoties, ātrumā uzrakstīts):

DB level - memcached; queries cashe; noSQL DB (MongoDB, Redis), utt

 

PHP code - APC; XCACHE; Profiling(xhprof); utt.

 

Filesystem level - file size limitation; aggregate css/js; reduce css/js; utt

 

web servers level - mod_proxy; Ningx vs Apache vs ...; Varnish,Varnish proxy caching;

 

Kopumā - load balancing, tā arhitektūra un tā tālāk un tā joprojām. Nevar analizēt vienu posmu, nezinot sistēmas arhitektūru kopumā.

Edited October 6, 2012 by vbz

[F3llony]

Es personīgi FK lietoju tikai izstrādes vidē. Bet bez visiem CASCADE update,delet ,etc. Galvenā doma ir lai piefiksētu kurā vietā programma mēģina izmantot ierakstus, kas ir nepareizi. Produkcijā vienmēr izmantoju vienkāršus indexus jo FK izmantošana var novest pie katastrofālām sekām. Īpaši CASCADE delete. Protams visi mēs te esam guru utt, taču nedod dievs hakeri iegūst iespēju izpildīt DELETE uz vienas tabulas un tam automātiski sekos visas tabulas....

Interesanti kā tu iedomājies scenāriju, kad kāds iegūtu pieeju tikai vienai tabulai? :)

Ka jau minēju uzskatu, ka FK ir labs tikai izstradē, lai uzbūvētu pareizu sistēmu. Kad tas ir izdarits FK ir lieka slodze serverim. Lielākā daļa MySQL guru iesaka izvairīties no FK lietošanas produkcijā.

Manuprāt, īpaši lielie projekti, izmanto citas DB un ja nu tomēr viņi izmanto MySQL, tad noteikti datu svarīgumu dēļ tiek izmantotas ari tranzakcijas... tranzakcijas + FK ir nāviga kombinācija. Gadījās redzet vienu projektu uz MsSQL, kur bija izmantota šī pieeja tranzakcijas + FK... Deadlocki šādā sistemā bija normāla parādiba, bija gadījumi, kad lidzēja tikai restarts :)

Kas būtu tā liekā slodze? Atslēgu pārbaude? FK locki? Ja tā ir lieka noslodze, tad atvaino, tev ir problēmas arhitektūrā. FK kaskāde būs ātrāka kā atsevišķi vaicājumi. Bet tā, kā pamatā no datu bāzem neko nedzēš, bet gan atzīmē kā dzēstu, FK kaskādes vairāk izmantojamas datu atjaunošanai lai saglabātu datu bāzes datu integirtāti un aplikācijā nevajadzētu vienmēr sekot līdzi relācijām.

[marrtins]

Man šķiet, ka draugz reāli ir kautko pārpratis vai nav sapratis līdz galam.

[rpr]

Ne tikai vērts, bet pat vajadzīgs. Jo īpaši lieliem projektiem, kur datu integritāte ir vitāla un performance->whatever trade-off arī.

nesapratu šo teikumu. lieliem projektiem performance nav svarīga? draugz diezgan prātīgi spriež.

[marrtins]

Atkal sākas. Par micro optimizācijām tak sen jau arī šajā forumā ir izrunāts. Un ieguvums kveriju optimizētājam no hinta par datu struktūru arī nebūtu jāatstāj bez ievērības.