60 izabranih MySQL intervju pitanja👍

Detaljno objašnjeno 60 najčešćih MySQL intervju pitanja sa slikom i tekstom — ovog puta apsolutno dominirate pred intervjuerom, mislim da je sigurno (ručni dog). Pripremio: Chenmo Wang Er, pogledajte link za preuzimanje, unutar se nalazi detaljan konceptualni map; autor: herongwei, pogledajte link na original.

Osnove

1. Koja je razlika između relacionih i nerelacionih baza podataka?

Prednosti relacionih baza podataka

• Lako ih je razumeti, jer koriste relacioni model za organizaciju podataka.
• Mogu održati konzistentnost podataka.
• Trošak ažuriranja podataka je relativno mali.
• Podržavaju složene upite (upite sa WHERE klauzulom).

Prednosti nerelacionih baza podataka (NOSQL)

• Ne moraju da prolaze kroz SQL slovo za parsiranje, pa su efikasnost čitanja i pisanja visoka.
• Zasnovane na parovima ključ-vrednost, imaju veoma visoke performanse čitanja i pisanja i lako se skaliraju.
• Mogu da podrže skladištenje više tipova podataka, poput slika, dokumenata itd.
• Proširenje (mogu se podeliti na baze u memoriji i baze zasnovane na dokumentima, na primer Redis, MongoDB, HBase i sl.; pogodni scenariji: sistemi sa velikom količinom podataka i visokom dostupnošću, log sistemi, sistemi za čuvanje geografskih lokacija).

2. Detaljno objašnjenje koraka izvršavanja jedne MySQL naredbe

Slovo Server izvršava SQL u sledećem redosledu:

• Zahtev klijenta -> konektor (autentifikacija korisnika i dodeljivanje dozvola)
• Keš upita (ako postoji u kešu, vraća se odmah; inače se nastavlja dalje)
• Analizator (leksička i sintaksna analiza SQL naredbe)
• Optimizator (optimizuje SQL i bira optimalan plan izvršavanja)
• Izvršilac (prilikom izvršavanja prvo proverava da li korisnik ima dozvolu za izvršavanje, pa tek onda koristi interfejs koji pruža engine) -> odlazi u slovo engine-a po podatke i vraća ih (ako je keš upita uključen, rezultat upita se kešira).

Indeksi

3. Zašto MySQL koristi indekse?

Osnovni razlog

• Indeksi su se pojavili kako bi povećali efikasnost upita nad podacima, baš kao sadržaj knjige.
• Za tabelu u bazi podataka, indeks je zapravo njen „sadržaj".

Dodatno

• Kreiranje jedinstvenog indeksa može da garantuje jedinstvenost svakog reda u tabeli baze podataka.
• Pomaže slovu engine-a da izbegne sortiranje i privremene tabele.
• Pretvara nasumični IO u sekvencijalni IO i ubrzava spajanje tabela.

4. Tri uobičajene osnovne strukture podataka indeksa i njihove prednosti i mane

Tri uobičajene osnovne strukture podataka za indekse su: heš tabela, sortiran niz i stablo pretrage.

• Heš tabela je pogodna za scenarije upita po jednakosti, na primer memcached i neki drugi NoSQL engine-i, ali nije pogodna za opsegne upite.
• Indeks u obliku sortiranog niza pogodan je samo za statičke storage engine-e; dobro radi za upite po jednakosti i opsegne upite, ali je trošak ažuriranja podataka visok.
• N-arno stablo se široko koristi u engine-ima baza podataka zbog prednosti u performansama čitanja i pisanja i prilagođenosti obrascima pristupa disku.
• Dodatno (na primer, za indeks nad celobrojnim poljem u InnoDB-u, ovaj N je otprilike 1200. Kada je visina stabla 4, može da uskladišti 1200 na treći broj vrednosti, što je već 1,7 milijardi. S obzirom da je koreni blok podataka stabla uvek u memoriji, za indeks nad celobrojnim poljem tabele od milijardu redova, pronalaženje jedne vrednosti zahteva najviše 3 pristupa disku. U stvari, i drugi nivo stabla velikom verovatnoćom je u memoriji, pa je prosečan broj pristupa disku još manji.)

5. Uobičajeni tipovi indeksa i kako oni funkcionišu?

Prema sadržaju listnih čvorova, tipovi indeksa se dele na indeks primarnog ključa i indeks koji nije primarni ključ.

• Listni čvorovi indeksa primarnog ključa čuvaju ceo red podataka; u InnoDB-u se naziva i klasterisani indeks.
• Listni čvorovi indeksa koji nije primarni ključ čuvaju vrednost primarnog ključa; u InnoDB-u se naziva i sekundarni indeks.

6. Koja je razlika između MyISAM i InnoDB u implementaciji B-stablo indeksa?

• InnoDB storage engine: listni čvorovi B+ stabla indeksa čuvaju same podatke, pa je datoteka sa podacima istovremeno i datoteka indeksa.
• MyISAM storage engine: listni čvorovi B+ stabla indeksa čuvaju fizičku adresu podataka, data polje listnog čvora sadrži adresu zapisa podataka, a datoteka indeksa i datoteka podataka su razdvojene.

7. Zašto InnoDB koristi B+ stablo indeksa?

Dva ključna faktora:

• Scenariji i funkcije koje InnoDB treba da izvršava zahtevaju jaku perfomansu na specifičnim upitima.
• CPU-u treba mnogo vremena da učita podatke sa diska u memoriju.

Zašto B+ stablo:

• Heš indeks može da pruži složenost upita O(1), ali ne može dobro da podrži opsegne upite i sortiranje, što na kraju dovodi do skeniranja cele tabele.

• B stablo može da čuva podatke u ne-listnim čvorovima, ali to može dovesti do više nasumičnog IO-a prilikom upita nad uzastopnim podacima.

• Kod B+ stabla, svi listni čvorovi mogu da se međusobno povežu pokazivačima, čime se smanjuje nasumični IO uzrokovan sekvencijalnim obilaskom.

• Običan ili jedinstveni indeks?

  Pošto jedinstveni indeks ne može da iskoristi optimizaciju change buffer-a, ako biznis to dozvoljava, iz ugla performansi preporučuje se da prvo razmotrite ne-jedinstveni indeks.

8. Šta je pokrivajući indeks i indeks pushdown?

Pokrivajući indeks:

• U nekom upitu, indeks k je već „pokrio" naše potrebe upita — to se naziva pokrivajući indeks.

• Pokrivajući indeks može da smanji broj pretraga stabla i značajno poboljša performanse upita, pa je korišćenje pokrivajućeg indeksa čest sredstvo za optimizaciju performansi.

Indeks pushdown:

• Optimizacija index condition pushdown uvedena u MySQL 5.6 omogućava da se tokom obilaska indeksa prvo izvrši provera nad poljima koja su sadržana u indeksu, čime se direktno filtriraju zapisi koji ne zadovoljavaju uslove i smanjuje broj povrata ka tabeli.

9. Koje operacije dovode do toga da indeks prestaje da se koristi?

• Korišćenje levog ili levog i desnog fuzzy match-ovanja na indeksu, odnosno like %xx ili like %xx%, može dovesti do toga da indeks ne bude iskorišćen. Razlog je što rezultat upita može biti višestruk i nije poznato od koje vrednosti indeksa početi poređenje, pa se mora skenirati cela tabela.
• Primena funkcije nad indeksom / izračunavanje izraza nad indeksom, jer indeks čuva originalnu vrednost polja indeksa, a ne vrednost nakon primene funkcije, pa logično ne može da iskoristi indeks.
• Implicitna konverzija nad indeksom jednaka je korišćenju nove funkcije.
• OR naredba u WHERE klauzuli: čim jedan uslovni stupanj nije indeksni, izvršiće se skeniranje cele tabele.

10. Dodavanje indeksa na string

• Direktno kreirajte puni indeks, što može zauzimati prilično prostora.
• Kreirajte prefiks indeks, što štedi prostor, ali povećava broj skeniranja u upitu i ne može da iskoristi pokrivajući indeks.
• Čuvanje u obrnutom redosledu, pa kreiranje prefiks indeksa, kako bi se zaobišao problem nedovoljne razlučivosti prefiksa samog stringa.
• Kreiranje heš polja indeksa: stabilne performanse upita, ali sa dodatnim troškovima skladištenja i računanja; kao i treća opcija, ne podržava skeniranje opsega.

Logovi

11. Šta je change buffer u MySQL-u?

• Kada je potrebno ažurirati stranicu podataka, ako se stranica podataka nalazi u memoriji, ažurira se odmah; ako ta stranica podataka još uvek nije u memoriji, bez uticaja na konzistentnost podataka, InnoDB će keširati ove operacije ažuriranja u change buffer-u.
• Tako nije potrebno učitavati ovu stranicu podataka sa diska; kada sledeći upit bude morao da pristupi toj stranici podataka, stranica se učita u memoriju, a zatim se izvrše operacije iz change buffer-a koje se odnose na nju. Na taj način se garantuje ispravnost logike podataka.
• Imajte na umu da ažuriranje jedinstvenog indeksa ne može da koristi change buffer; zapravo, samo obični indeksi mogu da ga koriste.
• Scenariji primene:
  • Za biznise sa mnogo pisanja i malo čitanja, verovatnoća da stranica bude odmah pristupljena nakon pisanja je relativno mala, pa tada change buffer daje najbolje rezultate. Ovaj model biznisa je čest kod sistema za račune i sisteme za logovanje.
  • Obrnuto, pretpostavimo da je model ažuriranja takav da se odmah nakon upisa radi upit — čak i ako su uslovi ispunjeni, ažuriranje će biti zabeleženo u change buffer, ali pošto se toj stranici podataka odmah pristupa, odmah će se pokrenuti merge proces. Tako se broj nasumičnih IO pristupa ne smanjuje, već se povećava trošak održavanja change buffer-a.

12. Kako MySQL procenjuje broj skeniranih redova?

• Pre nego što stvarno počne da izvršava naredbu, MySQL ne može tačno da zna koliko zapisa zadovoljava taj uslov.
• Može samo da proceni broj zapisa na osnovu statističkih informacija. Te statističke informacije su „razlučivost" indeksa.

13. Koja je razlika između redo log i binlog u MySQL-u?

14. Zašto je potreban redo log?

• Redo log se prvenstveno koristi kao sredstvo za oporavak podataka nakon nenormalnog restarta MySQL-a i obezbeđuje konzistentnost podataka.
• Zapravo, služi da podrži WAL mehanizam MySQL-a. Pošto MySQL radi operaciju ažuriranja i želi brzo da odgovori, koristi tehniku asinhronog upisivanja na disk: upiše u memoriju pa odmah vrati rezultat. Ali to stvara rizik gubitka podataka iz memorije nakon pada, dok redo log ima sposobnost crash-safe.

15. Zašto redo log ima crash-safe sposobnost koju binlog ne može da zameni?

Prva tačka: redo log omogućava InnoDB-u da prosudi koji su podaci već ispisani na disk, a koji još nisu.

• Velika razlika između redo log i binlog je u tome što se jedan piše ciklično, a drugi dodavanjem. Drugim rečima, redo log beleži samo logove koji nisu ispisani na disk; podaci koji su već ispisani na disk se uklanjaju iz ovog log fajla konačne veličine. Binlog je log koji se dopunjuje dodavanjem i čuva sve logove.

• Kada baza podataka padne i želimo da vratimo u memoriju podatke koji nisu ispisani na disk, ali su upisani u redo log i binlog, binlog to ne može da uradi. Iako binlog ima sve logove, nema oznake na osnovu koje bi InnoDB mogao da prosudi koji su podaci već ispisani, a koji nisu.

• Ali redo log je drugačiji: podaci koji se ispišu na disk uklanjaju se iz redo log-a, jer se piše ciklično! Nakon restarta baze, dovoljno je vratiti sve podatke iz redo log-a u memoriju.

Druga tačka: ako upis u redo log ne uspe, to znači da operacija nije uspela i transakcija ne može biti potvrđena.

• Nakon svake operacije ažuriranja, redo log se obavezno upisuje; ako upis ne uspe, to znači da je operacija neuspešna i transakcija ne može biti potvrđena.
• Unutrašnja struktura redo log-a je zasnovana na stranicama i beleži promenu vrednosti polja te stranice; nakon pada je dovoljno pročitati redo log i ponovo ga primeniti (replay) da bi se podaci oporavili.
• Zato redo log ima crash-safe sposobnost, dok je binlog nema.

16. Kada baza podataka padne, kako vratiti podatke koji nisu ispisani na disk u memoriju?

Prema dvofaznom slaganju redo log-a i binlog-a, podaci koji nisu trajno sačuvani se dele u nekoliko slučajeva:

• Upis u change buffer, redo log je uradio fsync ali nije commit-ovan, binlog nije uradio fsync na disk — ovaj deo podataka je izgubljen.

• Upis u change buffer, redo log uradio fsync ali nije commit-ovan, binlog je već uradio fsync na disk — prvo se iz binlog-a oporavi redo log, a zatim se iz redo log-a oporavi change buffer.

• Upis u change buffer, redo log i binlog su oba uradila fsync — oporavak se vrši direktno iz redo log-a.

17. Kako se upisuje redo log?

Redo log se sastoji iz dva dela: log bafer u memoriji (redo log buffer) i log fajl na disku (redo log file).

MySQL pri svakoj DML naredbi prvo upisuje zapis u redo log buffer (korisnički prostor), zatim ga čuva u baferu OS-buffer-u u kernel prostoru, a kasnije, u nekom trenutku, odjednom upisuje više zapisa operacija u redo log file (ispis na disk). Ova tehnika prvo upisivanja loga, pa tek onda diska, je WAL.

Može se primetiti da se redo log buffer upisuje u redo log file posredstvom OS buffer-a. Zapravo, to se može konfigurisati parametrom innodb_flush_log_at_trx_commit, čije vrednosti znače sledeće:

• 0: naziva se odloženo pisanje — pri potvrdi transakcije, log iz redo log buffer-a se ne upisuje u OS buffer, već se jednom u sekundi upisuje u OS buffer i poziva upis u redo log file.
• 1: naziva se trenutno pisanje, trenutno ispiranje — pri svakoj potvrdi transakcije, log iz redo log buffer-a se upisuje u OS buffer i čuva u redo log file-u.
• 2: naziva se trenutno pisanje, odloženo ispiranje — pri svakoj potvrdi transakcije upisuje se u OS buffer, a zatim se jednom u sekundi log upisuje u redo log file.

18. Koji je tok izvršavanja redo log-a?

Pogledajmo tok izvršavanja Redo log-a, pretpostavimo da se izvršava sledeći SQL:

update T set a =1 where id =666

1. MySQL klijent šalje naredbu zahteva update T set a =1 where id =666 u MySQL Server slovo.
2. Kada MySQL Server slovo primi SQL zahtev, on ga analizira, optimizuje i izvršava, te šalje generisani plan izvršavanja SQL-a u InnoDB storage engine na izvršavanje.
3. InnoDB storage engine beleži u memoriju operaciju a se menja na 1.
4. Nakon upisa u memoriju, izmeniće se zapis redo log-a, odnosno dodaće se jedan red čiji je sadržaj na kojoj stranici podataka treba uraditi koju izmenu.
5. Zatim se status transakcije postavlja na prepare, što znači da je transakcija spremna za potvrdu.
6. Kada MySQL Server slovo završi obradu transakcije, status transakcije se postavlja na commit, odnosno transakcija se potvrđuje.
7. Po prijemu zahteva za potvrdu transakcije, redo log će upisati na disk operaciju koja je upravo upisana u memoriju, čime se završava čitav proces beleženja loga.

19. Šta je binlog, koja mu je uloga i da li može da garantuje crash-safe?

• Binlog je arhivski log i pripada MySQL Server slovu. Omogućava dve uloge: master-slave replikaciju i oporavak podataka.
• Kada je potrebno oporaviti podatke, može se izdvojiti binlog iz određenog vremenskog opsega i ponovo ga primeniti (replay) radi oporavka.
• Ali binlog ne može da bude crash safe, jer pre pada binlog možda nije bio u potpunosti upisan pre nego što se MySQL sruši. Zato je potrebno kombinovati ga sa redo log da bi se postigao crash safe.

20. Šta je dvofazno slaganje (two-phase commit)?

MySQL deli upis redo log-a u dva koraka: prepare i commit, sa upisom binlog-a između njih — to je „dvofazno slaganje".

Dvofazno slaganje služi da ta dva stanja ostanu logički usklađena. Redo log se koristi za oporavak neažuriranih fizičkih podataka pri padu domaćina, dok binlog služi za rezervne kopije operacija. Ta dva loga su u osnovi dva nezavisna entiteta; da bi se održala njihova usklađenost, mora se koristiti rešenje distribuiranih transakcija.

Zašto je potrebno dvofazno slaganje?

• Bez dvofaznog slaganja mogla bi da se dogodi sledeća situacija:
• Ako se prvo upiše redo log, nakon pada, pri oporavku iz binlog rezervne kopije nedostaje jedno ažuriranje, pa podaci nisu usklađeni sa trenutnim stanjem.
• Ako se prvo upiše binlog, nakon pada, pošto redo log nije upisan, transakcija je nevažeća, pa pri naknadnom oporavku iz binlog rezervne kopije podaci nisu usklađeni.
• Dvofazno slaganje služi da garantuje sigurnu i usklađenu konzistentnost podataka u redo log-u i binlog-u. Tek kada su ta dva log fajla logički visoko usklađena, mogu se koristiti bez brige.

Pri oporavku podataka, ako je status redo log-a commit, to znači da je i binlog uspeo, pa se podaci direktno oporavljaju; ako je redo log u prepare stanju, potrebno je proveriti da li je odgovarajuća binlog transakcija uspela, pa na osnovu toga odlučiti da li se radi rollback ili se nastavlja izvršavanje.

21. Otkud MySQL zna da li je binlog potpun?

Binlog jedne transakcije ima potpuni format:

• binlog u statement formatu završava se sa COMMIT;
• binlog u row formatu završava se sa XID event-om.

22. Šta je WAL tehnika i koje su joj prednosti?

WAL je skraćenica od Write-Ahead Logging; ključna tačka je da se log prvo upisuje u memoriju, a zatim na disk. Nakon što MySQL izvrši operaciju ažuriranja, pre nego što stvarno upiše podatke na disk, prvo se beleži log.

Prednost je što ne mora pri svakoj operaciji u realnom vremenu da upisuje podatke na disk; čak i nakon pada moguće ih je oporaviti preko redo log-a, pa se može postići brz odgovor na SQL naredbe.

23. Tri formata binlog loga

Binlog log ima tri formata:

• Statement: replikacija zasnovana na SQL naredbama (statement-based replication, SBR).
• Row: replikacija zasnovana na redovima (row-based replication, RBR).
• Mixed: replikacija u mešovitom režimu (mixed-based replication, MBR).

Statement format

Svaka SQL naredba koja menja podatke biće zabeležena u binlog.

• Prednost: ne mora se beležiti promena svakog reda, čime se smanjuje količina binlog logova, štedi IO i poboljšavaju performanse.
• Mana: pošto se beleži samo izvršena naredba, da bi te naredbe mogle ispravno da se izvrše na replikama, moraju se zabeležiti i neke relevantne informacije o izvršavanju svake naredbe, kako bi sve naredbe na replici dobile isti rezultat kao na masteru.

Row format

Ne beleži se kontekstualna informacija SQL naredbe, već samo koji je red izmenjen.

• Prednost: binlog ne mora da beleži kontekstualne informacije izvršene SQL naredbe, već samo u šta je taj red promenjen. Zato će log na nivou reda vrlo jasno zabeležiti detalje izmene svakog reda podataka. Neće se pojaviti problem da pozivi i okidači stored procedura, funkcija ili trigger-a u određenim situacijama ne mogu ispravno da se replikuju.
• Mana: može stvoriti veliku količinu log sadržaja.

Mixed format

Zapravo je kombinacija Statement i Row formata. Za uobičajene izmene naredbi binlog se čuva u statement formatu; za operacije koje statement ne može da izvrši u master-slave replikaciji (poput nekih funkcija) binlog se čuva u row formatu. MySQL na osnovu svake konkretno izvršene SQL naredbe odlučuje u kom obliku će zabeležiti log.

24. Format redo log-a

Redo log buffer (u memoriji) sastoji se od četiri fajla međusobno povezana početak-kraj, i to su: ib_logfile_1, ib_logfile_2, ib_logfile_3, ib_logfile_4.

• write pos je trenutna pozicija zapisa; pomiče se tokom pisanja, a kada stigne do kraja fajla broj 3, vraća se na početak fajla broj 0.
• checkpoint je trenutna pozicija za brisanje; takođe se pomiče unapred i radi ciklično; pre brisanja zapisa potrebno je ažurirati zapise u fajlove podataka.
• Između write pos i checkpoint je deo „table za pisanje" koji je još uvek prazan i može se koristiti za beleženje novih operacija.
• Ako write pos stigne do checkpoint-a, to znači da je „tabla za pisanje" puna; tada se ne mogu izvršavati nove operacije ažuriranja — mora se stati i prvo obrisati nekoliko zapisa, kako bi se checkpoint pomerio.
• Zahvaljujući redo log-u, nakon pada i restarta baze podataka, putem redo log-a mogu se oporaviti podaci koji nisu upisani na disk (podaci nakon check point-a), čime se garantuje da zapisi potvrđenih transakcija ne budu izgubljeni — ta sposobnost se naziva crash-safe.

25. SQL naredba koja bi inače trebalo da se izvrši vrlo brzo, izvršava se znatno sporije od očekivanog — koji je razlog i kako se rešava?

Razlog: od većeg ka manjem, može se podeliti u četiri situacije:

• Sam MySQL je blokiran, na primer: nedovoljno sistemskih ili mrežnih resursa.
• SQL naredba je blokirana, na primer: table lock, row lock i sl., zbog čega storage engine ne izvršava odgovarajuću SQL naredbu.
• Zaista postoji nepravilno korišćenje indeksa — nije iskorišćen indeks.
• Posledica karakteristika podataka u tabeli — indeks je iskorišćen, ali je broj povrata ka tabeli ogroman.

Rešenje:

• Razmotrite korišćenje force index da nametnete određeni indeks.
• Razmotrite izmenu naredbe tako da navedete MySQL da koristi indeks koji očekujemo. Na primer, promenite order by b limit 1 u order by b,a limit 1; logika značenja je ista.
• Treći pristup je da, u određenim scenarijima, napravite pogodniji indeks koji optimizator može da izabere, ili obrišete pogrešno korišćen indeks.
• Ako ste sigurni da indeks uopšte nije potreban, razmotrite njegovo brisanje.

26. Struktura stranice podataka u InnoDB-u

Jedna stranica podataka grubo je podeljena na sedam delova:

• File Header: opšte informacije o stranici, zauzima fiksno 38 bajtova.
• Page Header: specifične informacije stranice podataka, zauzima fiksno 56 bajtova.
• Infimum+Supremum: dva virtuelna pseudo-zapisa koji redom predstavljaju najmanji i najveći zapis na stranici, zauzimaju fiksno 26 bajtova.
• User Records: ovde se stvarno čuvaju podaci koje unosimo, veličina nije fiksna.
• Free Space: deo stranice koji još uvek nije iskorišćen, veličina nije fiksna.
• Page Directory: relativne pozicije nekih zapisa na stranici, odnosno pomaci adresa u stranici za zapise koje odgovaraju pojedinim slotovima.
• File Trailer: služi za proveru da li je stranica kompletna, fiksne veličine 8 bajtova.

Podaci

27. Kako MySQL obezbeđuje da podaci ne budu izgubljeni?

• Sve dok redo log i binlog garantuju trajno upisivanje na disk, može se osigurati da MySQL nakon nenormalnog restarta obnovi podatke.
• Pri oporavku podataka, ako je status redo log-a commit, to znači da je i binlog uspeo, pa se podaci direktno obnavljaju; ako je redo log u prepare stanju, potrebno je proveriti da li je odgovarajuća binlog transakcija uspela, pa na osnovu toga odlučiti da li se radi rollback ili nastavlja izvršavanje.

28. Šta raditi kada se podaci slučajno obrišu?

Najvažniji zadatak DBA-e je da garantuje integritet podataka, pa pre svega treba dobro obaviti prevenciju. Prevencija obuhvata otprilike sledeće tačke:

• Kontrola i dodeljivanje dozvola (dozvole za bazu podataka i server).
• Uvođenje operativnih standarda.
• Redovna obuka programera.
• Postavljanje delayed replike.
• Dobra SQL revizija: sve naredbe koje menjaju produkcione podatke (DML i DDL) moraju proći kroz pregled.
• Dobre rezervne kopije. Rezervne kopije se dele na dva pristupa: (1) Ako je količina podataka velika, koristi se fizička rezervna kopija pomoću xtrabackup. Redovno se radi puna rezervna kopija baze, a može i inkrementalna. (2) Ako je količina podataka manja, koristi se mysqldump ili mysqldumper, pa se zatim koristi binlog za oporavak ili master-slave arhitektura za oporavak podataka. Redovno pravljenje rezervnih kopija binlog fajlova je takođe neophodno.
• Ako dođe do brisanja podataka, oporavak se može vršiti iz sledećih pravaca:
• DML naredba greškom je dovela do nepotpunih ili izgubljenih podataka. Može se koristiti flashback, odnosno myflash (alat firme Meituan), što je takođe dobar alat; suština je slična — prvo se parsira binlog event, a zatim se radi inverzija: delete se pretvara u insert, insert u delete, a kod update-a se preklapaju pre i posle slike. Zato se mora postaviti binlog_format=row i binlog_row_image=full; vodite računa da prilikom oporavka prvo vratite podatke na privremenu instancu, pa tek onda na master bazu.
• DDL naredba greškom (truncate i drop): pošto DDL naredbe u binlog-u beleže samo naredbu, bez obzira na to da li je binlog_format row ili statement, i ne beleže image, oporavak je znatno komplikovaniji. Podaci se mogu oporaviti samo pomoću pune rezervne kopije i primenom binlog-a. Kada je količina podataka velika, vreme oporavka je posebno dugo.
• Brisanje sa rm: koristite rezervne kopije koje se čuvaju na drugom mestu (kros-server sobe), ili još bolje u drugom gradu.

29. Razlika između drop, truncate i delete

• DELETE naredba pri brisanju u svakom koraku briše po jedan red iz tabele i istovremeno beleži tu operaciju brisanja tog reda kao transakciju u logu, čime se omogućava rollback.
• TRUNCATE TABLE odjednom briše sve podatke iz tabele i ne upisuje pojedinačne operacije brisanja u log; obrisani redovi ne mogu biti povraćeni. Tokom brisanja se ne aktiviraju trigger-i za brisanje vezani za tu tabelu. Brzina izvršavanja je visoka.
• Drop naredda oslobađa sav prostor koji tabela zauzima.
• Što se brzine tiče, generalno važi: drop > truncate > delete.
• Ako želite da obrišete deo podataka, koristite delete, obratite pažnju da dodate WHERE klauzulu i da rollback segment bude dovoljno veliki.
• Ako želite da obrišete celu tabelu, naravno, koristite drop; ako želite da zadržite tabelu, ali obrišete sve podatke i to nije vezano za transakciju, koristite truncate.
• Ako je operacija vezana za transakciju ili želite da okinete trigger, koristite delete; ako reorganizujete fragmente unutar tabele, možete koristiti truncate uz reuse storage, a zatim ponovo uvesti/umetnuti podatke.

30. U MySQL-u postoje dve kill naredbe

• Jedna je kill query + id niti — označava prekid naredbe koja se trenutno izvršava u toj niti.
• Druga je kill connection + id niti — ovde connection može da se izostavi, a označava prekid konekcije te niti.

Razlozi zbog kojih kill ne deluje:

• Kill naredba je blokirana i još nije stigla na mesto.
• Kill naredba je stigla na mesto, ali nije odmah aktivirana.
• Kill naredba je aktivirana, ali i njeno završavanje zahteva vreme.

31. Kako razumeti MySQL-ovo slanje tokom čitanja (read-while-send)?

• Ako klijent sporo prihvata, MySQL server ne može da pošalje rezultate, pa će vreme izvršavanja te transakcije biti dugo.
• Server ne mora da čuva kompletan rezultat skupa podataka; i tok uzimanja podataka i tok slanja podataka se odvijaju preko jednog next_buffer-a.
• Stranice podataka u memoriji opslužuju se u Buffer_Pool-u.
• InnoDB upravlja Buffer_Pool-om poboljšanim LRU algoritmom implementiranim povezanom listom; u implementaciji se čitava LRU lista deli na young i old region u odnosu 5:3.

32. Zašto veliki upiti nad MySQL tabelama ne raspršuju memoriju?

• Pošto MySQL radi „čitanje uz slanje", za rezultate upita sa velikom količinom podataka se ne čuva kompletan skup rezultata na serveru, tako da, ako klijent ne čita rezultate na vreme, zaustaviće proces MySQL upita, ali neće raspršiti memoriju.
• Unutar InnoDB engine-a, zahvaljujući strategiji izbacivanja, InnoDB upravlja Buffer_Pool-om poboljšanim LRU algoritmom povezanom listom; u implementaciji se čitava LRU lista deli na young i old region u odnosu 5:3. Tako se i puno skeniranje hladnih podataka može držati pod kontrolom.

33. Upotreba i karakteristike privremenih tabela u MySQL-u

• Vidljive su samo trenutnoj sesiji.
• Mogu imati isto ime kao obične tabele.
• INSERT/DELETE/UPDATE/SELECT se odvijaju nad privremenom tabelom.
• show tables ne prikazuje obične tabele.
• U praksi se privremene tabele obično koriste za obradu složenijih računskih logika.
• Pošto su privremene tabele vidljive samo svojoj niti, nije potrebno razmišljati o problemu ponavljanja imena kada više niti izvršavaju istu obradu; kada nit završi, privremena tabela se automatski briše.

34. Pregled MySQL storage engine-a (InnoDB, MyISAM, MEMORY)

• InnoDB je preferirani engine za transakcione baze, podržava ACID transakcione tabele, podržava zaključavanje na nivou reda i strane ključeve. Od MySQL 5.5.5, InnoDB je podrazumevani storage engine.
• MyISAM je storage engine zasnovan na ISAM-u i proširuje ga. Jedan je od najčešće korišćenih storage engine-a u okruženjima kao što su Web, skladištenje podataka i druge aplikacije. MyISAM ima visoku brzinu umetanja i upita, ali ne podržava transakcije. U verzijama pre MySQL 5.5.5, MyISAM je bio podrazumevani storage engine.
• MEMORY storage engine čuva podatke iz tabele u memoriji i obezbeđuje brz pristup za upite i referenciranje podataka iz drugih tabela.

35. Svi kažu da je InnoDB dobar — da li onda uopšte koristiti MEMORY engine?

• Tabele u memoriji su tabele kreirane pomoću memory engine-a.
• Zašto ne preporučujem korišćenje tabela u memoriji u produkciji? Razlozi se uglavnom svode na dve strane: problem granularnosti zaključavanja i problem trajnosti podataka.
• Pošto restart gubi podatke, restart jedne replike zaustaviće nit za master-slave sinhronizaciju; ako master i ta replika čine dual-M arhitekturu, čak može dovesti i do brisanja podataka iz tabela u memoriji na masteru.

36. Ako se u bazi podataka desi greška u operaciji, kako izvršiti oporavak podataka?

Ako u bazi podataka određenog trenutka dođe do pogrešne operacije, mogu se pronaći binlog zapisi najbližeg trenutka pre greške, ponovo ih primeniti u privremenu bazu, zatim izabrati čvor sa podacima koji su obrisani greškom i vratiti ih u produkcione baze.

Master-slave replikacija

37. Kako MySQL obezbeđuje master-slave sinhronizaciju?

Uspostavljanje odnosa master-slave:

• Prilikom prvog uspostavljanja master-slave odnosa, to određuje replika; na primer, kod master-slave odnosa zasnovanog na poziciji, replika kaže „želim da počnem sinhronizaciju od pozicije P u binlog fajlu A", a master onda od te navedene pozicije šalje podatke unapred.
• Nakon što je master-slave odnos uspostavljen, master odlučuje da li će slati podatke replikama, pa će i nove logove slati replikama.

Tok prebacivanja mastera i replike u MySQL-u:

• Klijent i za čitanje i za pisanje direktno pristupa čvoru A, dok je čvor B replika; dovoljno je sve ažuriranje sa A sinhronizovati i izvršiti lokalno da bi se garantovalo da su podaci isti.
• Kada je potrebno prebacivanje, samo se zameni čvor — čvor A postaje replika čvora B.

Kompletan proces sinhronizacije jedne transakcije:

• Replika B i master A uspostavljaju dugu konekciju; unutar mastera A postoji posebna nit koja održava tu dugu konekciju.

• Na repliki B se komandom change master postavlja IP, port, korisničko ime i lozinka mastera A, kao i od koje pozicije (uključujući ime fajla i offset loga) počinje traženje binlog-a.

• Na repliki B se izvršava start-slave, čime replika pokreće dve niti: io_thread i sql_thread, koje su redom zadužene za uspostavljanje veze i čitanje i parsiranje tranzitnog loga radi izvršavanja.

• Replika čita binlog fajlove koje master pošalje i po prijemu ih upisuje lokalno kao tranzitni log.

• Kasnije, uvođenjem rešenja za višenitnu replikaciju, sql_thread se razvio u više niti.

38. Šta je master-slave kašnjenje?

Pojava razlike u vremenu kada master i replika izvršavaju istu transakciju. Glavni razlozi su:

• U nekim uslovima postavljanja, mašina na kojoj se nalazi replika ima slabije performanse od mastera.
• Opterećenje replike je veliko.
• Velike transakcije: ako naredba na masteru traje 10 minuta, ta transakcija može dovesti do kašnjenja replike od 10 minuta.

39. Zašto je potrebna strategija višenitne replikacije?

• Pošto je sposobnost jednonitne replikacije u svakom pogledu niža od višenitne, kod mastera sa velikim pritiskom ažuriranja, replika možda nikada ne uspe da stigne mastera; posledica je da vrednost seconds_behind_master na repliki postaje sve veća.
• U praksi se preporučuje strategija sa prioritetom na pouzdanost, čime se smanjuje master-slave kašnjenje i poboljšava dostupnost sistema; nastojte da smanjite velike transakcije i da ih razdvojite na manje.

40. Koje paralelne strategije postoje u MySQL-u?

• Strategija raspodele po tabelama: ako dve transakcije ažuriraju različite tabele, mogu se izvršavati paralelno. Pošto su podaci uskladišteni u tabelama, raspodela po tabelama može da garantuje da dva workera neće ažurirati isti red. Mana: pri susretu sa hot tabelom — kada sve transakcije ažuriranja obuhvataju jednu određenu tabelu — sve transakcije se dodeljuju istom workeru i pretvaraju se u jednonitnu replikaciju.
• Strategija raspodele po redovima: ako dve transakcije ne ažuriraju iste redove, mogu se paralelno izvršavati na repliki. Očigledno, ovaj režim zahteva da binlog format bude row. Mana: u poređenju sa strategijom paralelne raspodele po tabelama, strategija paralelne raspodele po redovima pri odlučivanju o raspodeli niti zahteva više računskih resursa.

41. Koja je razlika između jednog mastera sa jednom replikom i jednog mastera sa više replika u MySQL-u?

U arhitekturi jednog mastera sa jednom replikom u dual-M modelu, prebacivanje mastera zahteva samo preusmeravanje klijentskog saobraćaja na repliku; dok u arhitekturi jednog mastera sa više replika, prebacivanje pored toga zahteva i da se replike ponovo vežu na novi master.

42. Šta raditi kada master padne?

• Prebacivanje master-slave zasnovano na poziciji: postoji problem pronalaženja pozicije sinhronizacije.
• U verziji MySQL 5.6 uveden je GTID, što je rešilo ovu poteškoću. Dakle, šta GTID zapravo znači i kako rešava problem pronalaženja pozicije sinhronizacije?
• GTID: globalni ID transakcije, generiše se kada se transakcija potvrdi i predstavlja njen jedinstveni identifikator; sastoji se iz dva dela, u formatu: GTID=server_uuid:gno.
• Svaka MySQL instanca održava skup GTID-ova koji odgovaraju „svim transakcijama koje je ta instanca izvršila".
• U GTID-zasnovanom master-slave odnosu, sistem smatra da čim se uspostavi taj odnos, mora se garantovati da su logovi koje master šalje repliki potpuni. Prema tome, ako log koji instanca B zahteva više ne postoji, A' će odbiti da pošalje log ka B.

43. Koja rešenja postoje za problem zastarelog čitanja kod MySQL read-write razdvajanja?

• Rešenje prisilnog korišćenja mastera.
• Rešenje sa sleep.
• Rešenje provere da li postoji master-slave kašnjenje.
• Rešenje uz podršku semi-sync.
• Rešenje čekanja na master poziciju.
• GTID rešenje.
• U produkciji prvo treba klasifikovati klijentske zahteve — razlikovati one koji mogu prihvatiti zastarelo čitanje od onih koji to nikako ne mogu; zatim, za naredbe koje ne podnose zastarelo čitanje, koristiti rešenje čekanja na GTID ili na poziciju.

44. Koja je razlika između istovremenih konekcija i istovremenih upita u MySQL-u?

• U rezultatima show processlist viđeno je nekoliko hiljada konekcija — to su istovremene konekcije. „Trenutno izvršavane" naredbe su istovremeni upiti.
• Veliki broj istovremenih konekcija utiče na memoriju; previsok broj istovremenih upita šteti CPU-u. Mašina ima konačan broj CPU jezgara, pa ako sve niti uđu u jednom trenutku, trošak promene konteksta postaje previsok.
• Zato je potrebno podesiti parametar innodb_thread_concurrency koji ograničava broj niti; kada broj niti dostigne taj parametar, InnoDB smatra da su niti potrošene i blokira druge naredbe da uđu u engine na izvršavanje.

Performanse

45. Kako u kratkom vremenu poboljšati performanse MySQL-a

• Prvi način: prvo uklonite one niti koje zauzimaju konekciju, a ne rade. Ili razmotrite prekidanje konekcija koje su predugo bile neaktivne unutar transakcije. kill connection + id.
• Drugi način: smanjite troškove samog procesa konektovanja. Problem performansi sporih upita u MySQL-u može imati tri moguća uzroka: indeksi nisu dobro dizajnirani; SQL naredba nije dobro napisana; MySQL je izabrao pogrešan indeks (force index).

46. Zašto MySQL auto-inkrementalni primarni ključ ID nije uvek uzastopan?

• Sukob jedinstvenog ključa.

• Rollback transakcije.

• Grupno zauzimanje vrednosti auto-inkrementalnog primarnog ključa.

• Dublji razlog: MySQL ne proverava da li auto-inkrementalni primarni ključ već postoji, čime se smanjuje vremenski opseg i granularnost zaključavanja; na taj način se održavaju više performanse i garantuje se da auto-inkrementalni primarni ključ ne može da se vrati unazad — zato on i nije uvek uzastopan.

• Kako auto-inkrementalni primarni ključ postiže jedinstvenost? Vrednost auto-inkrementa se povećava za 1 i kontroliše se auto-inkrementalnim lock-om radi upravljanja istovremenošću.

47. Zašto InnoDB koristi auto-inkrementalni ID kao primarni ključ?

• Model umetanja auto-inkrementalnog primarnog ključa odgovara uzastopnom umetanju: svaka operacija je dodavanje na kraj, ne zahteva pomeranje zapisa i ne izaziva cepanje listnih čvorova.

• Pri svakom umetanju novog zapisa, on se dodaje na sledeću poziciju trenutnog čvora indeksa; kada se stranica napuni, automatski se otvara nova stranica.

• Kada se za primarni ključ koristi polje sa poslovnom logikom, teško je garantovati uređeno umetanje, pošto je vrednost primarnog ključa pri svakom umetanju približno nasumična.

• Prema tome, svaki novi zapis mora da se umetne na neku srednju poziciju postojeće stranice indeksa; česte operacije pomeranja i deljenja stranica stvaraju mnogo fragmenata, čime se dobija ne baš kompaktna struktura indeksa, a trošak upisa podataka je viši.

48. Kako najbrže kopirati tabelu?

• Da biste izbegli read lock nad izvornom tabelom, sigurnije je prvo upisati podatke u eksternu tekstualnu datoteku, a zatim ih upisati u ciljnu tabelu.
• Jedan način je da se komandom mysqldump podaci izvezu u skup INSERT naredbi.
• Drugi način je da se rezultati direktno izvezu u .csv fajl. MySQL nudi sintaksu za izvoz rezultata upita u lokalni direktorijum servera: select * from db1.t where a>900 into outfile '/server_tmp/t.csv'; pošto dobijete .csv fajl, možete sledećom load data naredbom uvesti podatke u ciljnu tabelu db2.t: load data infile '/server_tmp/t.csv' into table db2.t;.
• Fizičko kopiranje: u verziji MySQL 5.6 uvedena je metoda transportable tablespace, koja omogućava fizičko kopiranje tabele putem izvoza i uvoza tablespace-a.

49. Naredbe grant i flush privileges

• Naredba grant istovremeno menja tabelu podataka i memoriju; pri proveri dozvola koriste se memorijski podaci, pa se pri standardnoj upotrebi ne mora dodavati flush privileges naredba.
• Naredba flush privileges sama po sebi rekonstruiše memorijske podatke o dozvolama na osnovu podataka iz tabele, pa se koristi kada podaci o dozvolama možda nisu usklađeni.

50. Da li koristiti particionisane tabele?

• Particionisanje nije što sitnije to bolje. Zapravo, tabela ili particija sa deset miliona redova, ukoliko nema posebno velike indekse, na današnjoj hardverskoj snazi već se smatra malom tabelom.
• Particije ne treba unapred previše rezervisati — dovoljno je kreirati ih pre upotrebe. Na primer, ako je particionisanje mesečno, na kraju svake godine kreirajte 12 novih particija za narednu godinu. Za istorijske particije bez podataka, blagovremeno ih ispušite (drop).

51. Upotreba JOIN

• Tabela na levoj strani left join ne mora biti driving tabela.
• Ako je potrebno značenje left join-a, polja driven tabele ne mogu se staviti u WHERE uslov radi provere jednakosti ili nejednakosti — moraju se pisati u ON.
• Standardna group by naredba zahteva agregatnu funkciju u SELECT delu, na primer select a,count(*) from t group by a order by null;.

52. Koje auto-inkrementalne ID-ove ima MySQL i koji su im scenariji?

• Kada auto-inkrementalni ID tabele dostigne gornju granicu, vrednost pri sledećem zauzimanju se ne menja, pa će pri uzastopnom umetanju podataka doći do greške sukoba primarnog ključa.

• Kada row_id dostigne gornju granicu, vraća se na 0 i ponovo raste; ako se pojavi isti row_id, podaci upisani kasnije će prepisati prethodne.

• Xid samo ne mora da se ponavlja unutar istog binlog fajla; teorijski može doći do duplikata, ali je verovatnoća zanemarljivo mala.

• Inkrementalna vrednost InnoDB max_trx_id se čuva pri svakom restartu MySQL-a.

• Xid održava server slovo. InnoDB interno koristi Xid kako bi povezao InnoDB transakciju sa serverom. Ali InnoDB-ov vlastiti trx_id se vodi posebno.

• thread_id je najčešće korišćen i najbolje obrađen auto-inkrementalni id logikom; koristi insert_unique algoritam.

53. Kako se Xid generiše unutar MySQL-a?

MySQL interno održava globalnu promenljivu global_query_id; pri svakom izvršavanju naredbe (uključujući SELECT naredbe), njena vrednost se dodeljuje Query_id-u, a zatim se ta promenljiva povećava za 1. Ako je trenutna naredba prva naredba koju ta transakcija izvršava, MySQL će takođe dodeliti Query_id Xid-u te transakcije.

Pošto je global_query_id čisto memorijska promenljiva, nakon restarta se resetuje na nulu. Prema tome, unutar iste instance baze podataka, Xid različitih transakcija takođe može biti isti. Ali pošto MySQL nakon restarta generiše novi binlog fajl, to garantuje da će unutar istog binlog fajla Xid sigurno biti jedinstven.

Zaključavanje (Lock)

54. Obratite se o lock-ovima u MySQL-u

• MySQL koristi mnogo lock-ova i u server slovu i u storage engine slovu.
• U MySQL server slovu treba pomenuti dve vrste lock-ova: prva je MDL (metadata lock), odnosno lock metapodataka; druga je Table Lock, odnosno lock na nivou tabele.
• MDL se još naziva i lock metapodataka — šta su zapravo metapodaci? Sve što opisuje bazu podataka jesu metapodaci, na primer struktura tabele, struktura baze itd. A zašto je potreban MDL?
• Glavni cilj je rešavanje dva problema: izolaciju transakcija i replikaciju podataka.
• InnoDB ima pet lock-ova na nivou tabele: IS (intention shared), IX (intention exclusive), S (shared, čitanje), X (exclusive, pisanje), AUTO-INC.
• Pri izvršavanju SELECT/INSERT/DELETE/UPDATE naredbi nad tabelom neće se postavljati lock-ovi na nivou tabele.
• IS i IX služe da bi se proverilo da li u tabeli postoje zaključani zapisi.
• Garancija auto-inkrementalnog primarnog ključa dolazi od AUTO-INC lock-a, koji je na nivou naredbe: dodavanjem atributa AUTO_INCREMENT na neku kolonu tabele, kasnije prilikom umetanja zapisa nije potrebno navoditi vrednost te kolone — sistem će joj automatski dodeliti monotono rastuću vrednost.
• InnoDB ima 4 lock-a na nivou reda:
• Record Lock: lock zapisa.
• Gap Lock: lock sa jazom, koji rešava problem fantomskog čitanja; stvari koje nisu postojale u prethodnom upitu pojave se u sledećem — zapravo je to situacija u kojoj između dva upita u transakciji A, transakcija B izvrši umetanje koje transakcija A oseti.
• Next-Key Lock: kada želite da zaključate određeni zapis i sprečite druge transakcije da u jaz ispred tog zapisa umetnu novi zapis.
• Insert Intention Lock: intention lock za umetanje; ako više transakcija umeće u isti jaz reda i ne umeću na istu poziciju unutar jaza, ne moraju da čekaju.
• Izbor između lock-a na nivou reda i lock-a na nivou tabele:
  • Pri punom skeniranju tabele koristi se lock na nivou reda.

55. Šta je fantomsko čitanje?

Znači da unutar iste transakcije, pri dva uzastopna upita nad istim opsegom podataka, drugi put se vide podaci koje prvi put nije bilo.

Scenariji u kojima se javlja fantomsko čitanje:

• Nivo izolacije transakcije je repeatable read, i radi se about current read.
• Fantomsko čitanje se odnosi na novoumetnute redove.

Problemi koje fantomsko čitanje donosi:

• Narušavanje semantike lock-a na nivou reda.
• Narušavanje konzistentnosti podataka.

Rešenje:

• Postavljanje gap lock-a koji zaključava jaz između redova i blokira operacije novog umetanja.
• Problem koji iz toga proizilazi: smanjenje nivoa istovremenosti i mogućnost mrtve petlje (deadlock).

Ostala „zašto" pitanja

56. Zašto MySQL ponekad „zatitra"?

• Prljave stranice će pozadinska nit automatski flush-ovati, a flush se aktivira i pri izbacivanju stranica podataka; pošto proces flush-ovanja prljavih stranica zauzima resurse, može učiniti da vreme odziva vaših naredbi za ažuriranje i upitanje bude nešto duže.

57. Zašto, nakon brisanja tabele, veličina fajla tabele ostaje nepromenjena?

• Nakon brisanja stavki podataka, neka stranica page A u InnoDB-u biće označena kao ponovo iskoristljiva.
• Kada delete naredba obriše sve podatke iz cele tabele, rezultat je da će sve stranice podataka biti označene kao ponovo iskoristljive. Ali na disku se fajl neće smanjiti.
• Tabele koje su prošle kroz mnogo dodavanja, brisanja i izmena mogu sadržati rupe. Te rupe takođe zauzimaju prostor, pa ako ih se reši, može se postići cilj skupljanja tablespace-a tabele.
• Obnova tabele (rebuild) može postići taj cilj. Možete koristiti komandu alter table A engine=InnoDB da rebuild-ujete tabelu.

58. Implementacija count(*) i poređenje različitih count-ova

• Za count(primarni ključ id), InnoDB engine će proći kroz celu tabelu, izvući vrednost id iz svakog reda i vratiti je server slovu. Kada server slovo dobije id, proceni da ne može biti NULL i sabira po redovima.
• Za count(1), InnoDB engine prolazi kroz celu tabelu, ali ne uzima vrednosti. Server slovo stavlja broj „1" za svaki vraćeni red, procenjuje da ne može biti NULL i sabira po redovima. Ako uporedimo samo ove dve upotrebe, možete zaključiti da se count(1) izvršava brže od count(primarni ključ id), jer vraćanje id-a iz engine-a uključuje parsiranje reda podataka i kopiranje vrednosti polja.
• Za count(polje): ako je to „polje" definisano kao NOT NULL, iz svakog zapisa se redom čita to polje, proverava se da ne može biti NULL i sabira po redovima; ako to „polje" dozvoljava NULL, tada se pri izvršavanju procenjuje da bi moglo biti NULL, pa se vrednost uzima i još jednom proverava — sabira se samo ako nije NULL. To je dakle prvi princip: koje god polje server slovo traži, InnoDB vraća to polje.
• Ali count(*) je izuzetak — ne uzima sva polja, već je posebno optimizovan i ne uzima vrednost. count(*) sigurno nije NULL, pa se sabira po redovima.
• Dakle, zaključak je: po efikasnosti, count(polje) < count(primarni ključ id) < count(1) ≈ count(*), pa se preporučuje korišćenje count(*) kad god je moguće.

59. Interna logika ORDER BY sortiranja

• MySQL će za svaku nit dodeliti memoriju (sort-buffer) za sortiranje; veličina te memorije je sort_buffer_size.

• Ako količina podataka za sortiranje nije veća od sort_buffer_size, sortiranje se završava u memoriji.

• Interno sortiranje se deli na dve vrste:

• Sortiranje po celim poljima: na stablu indeksa se pronađu ID-jevi primarnih ključeva koji zadovoljavaju uslov, zatim se na osnovu ID-a primarnog ključa uzmu podaci, stave u sort_buffer, pa se izvrši brzo sortiranje (quicksort).

• Sortiranje po rowid: kontrolom dužine podataka redova koji se sortiraju, omogućava se da sort_buffer primi što više podataka.

• Ako je količina podataka velika i ne može stati u memoriju, koristiće se privremeni fajl na disku za pomoć pri sortiranju — to je tzv. eksterno sortiranje.

• Kod eksternog sortiranja, MySQL podatke posle sortiranja deli u više zasebnih privremenih fajlova, obično radi merge sort na fajlovima na disku, a zatim te fajlove sjedinjuje u jedan veliki fajl.

60. Kako efikasno koristiti MySQL za prikaz nasumičnih poruka

• Nasumično izvucite Y1, Y2, Y3, a zatim izračunajte Ymax i Ymin.

• Nakon dobijanja skupa id-eva, izračunajte tri id-a koja odgovaraju Y1, Y2, Y3, i konačno uradite select * from t where id in (id1, id2, id3). Broj skeniranih redova bi trebalo da bude C+Ymax+3.

  mysql> select count(*) into @C from t;
  set @Y1 = floor(@C * rand());
  set @Y2 = floor(@C * rand());
  set @Y3 = floor(@C * rand());
  Ymax = max(Y1,Y2,Y3)
  Ymin = min(Y1,Y2,Y3)
  select id from t limit Ymin,(Ymax - Ymin)

Detaljno objašnjeno 60 najčešćih MySQL intervju pitanja sa slikom i tekstom — ovog puta apsolutno dominirate pred intervjuerom, mislim da je sigurno (ručni dog). Pripremio: Chenmo Wang Er, pogledajte link za preuzimanje, unutar se nalazi detaljan konceptualni map; autor: herongwei, pogledajte link na original.