MySQL intervju pitanja, 83 MySQL pitanja za intervju (55.000 reči, 331 crteža), mora videti svaki kandidat
Uvod
55.000 reči i 331 crtež, detaljno objašnjeno 83 čestih MySQL pitanja za intervju, naukandid ovladim ovim MySQL pitanjima, ovaj put ću razbiti intervjuesiti, mislim da je sigurno. Organizovao: Chenmo Wang Er, link za prenos, autor: Sanfen e, originalni link.
Svetla verzija je bolja za štampanje, što mnogi studenti vole, učinkovitije je učiti na štampanom materijalu.
- februara 2025. počeo sam sa drugim izdanjem.
- Za česta pitanja, označiću gde se pojavljuju u "Java vodič za intervju", koja kompanija, koja je originalna tema, i dodati 🌟, sadržaj je jasan; ako želite da uštedite vreme, možete prvo učiti ova pitanja.
- Razlikuje najbolje odgovore od objašnjenja principa, da biste znali "zašto" i "kako", a takođe efikasno odgovarati na intervjuu.
- Kombinujem projekte (Jishupai, pmhub) u organizaciju odgovora, da bi intervjuer osetio vašu iskrenost, a ne mehanično učenje.
- Popravio sam probleme iz prvog izdanja, uključujući povratne informacije članova, komentare na sajtu i GitHub repozitorijum problema, da bi ovaj vodič za intervju bio potpuniji.
- Dodao sam neke offer koje su članovi dobili, zahvale na Mianzha-napred, i priznanja za izmenu životopisa, kako bih motivisao sve i dao više samopouzdanja.
- Optimizovao sam izgled, dodao crteže, reorganizovao odgovore, da bi bili govorniji i bliži očekivanjima intervjuesita.
Online verzija se ažurira redovno.
Ako vam ovo pomaže, molim vas dajte rec, da bi vaši kolege i drugovi mogli da koriste ovaj materijal.
Stavio sam sve Ergove puteve napredovanja: Java napredni put, JVM napredni put, put naprednog konkurentnog programiranja, kao i sve verzije Mianzha-napred, pokrivajući Java osnove, Java kolekcije, Java konkurentnost, JVM, Spring, MyBatis, računarske mreže, operativne sisteme, MySQL, Redis, RocketMQ, distribuirane sisteme, mikroservise, dizajn oblike, Linux i drugih 16 velikih tema, ukupno preko 400.000 reči, 2000+ ručno crtanih crteža, zaista je puno sadržaja.
Prikažimo tamnu verziju PDF-a, jasni raspored, elegantni fontovi, pogodnije za noćno čitanje, uveče će biti prijatnije za čitanje.
MySQL osnove
0.🌟Šta je MySQL?
MySQL je open-source relaciona baza podataka, sada u vlasništvu Oracle korporacije. Najčešće korišćena baza podataka kod nas, lokalno sam instalirao najnoviju 8.3 verziju.
Kako izbrisati/kreirati tabelu?
Možete koristiti DROP TABLE za brisanje tabele, CREATE TABLE za kreiranje tabele.
Prilikom kreiranja tabele, možete postaviti primarni ključ pomoću PRIMARY KEY.
CREATE TABLE users (
id INT AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
email VARCHAR(100),
PRIMARY KEY (id)
);Napišite SQL izraz za rastući/opadajući poredak?
U SQL-u, možete koristiti ORDER BY klauzulu za sortiranje rezultata u rastućem ili opadajućem redosledu. Podrazumevano, rezultati su u rastućem redosledu, ako treba opadajući, možete koristiti DESC ključnu reč.
Na primer, u tabeli zaposlenih, želimo da sortiramo po plati opadajuće, možemo koristiti ORDER BY salary DESC:
SELECT id, name, salary
FROM employees
ORDER BY salary DESC;Ako treba sortirati po više polja, na primer po plati opadajuće, po imenu rastuće, možete koristiti ORDER BY salary DESC, name ASC:
SELECT id, name, salary
FROM employees
ORDER BY salary DESC, name ASC;Koji su razlozi za slabu performansu MySQL?
Moguće je da SQL upit koristi puno skeniranje tabele, ili je previše kompleksan, kao više tabela JOIN ili ugnježdeni podupiti.
Takođe moguće je da je jedna tabela prevelika.
Obično, dodavanje indeksa rešava većinu problema sa performansama. Za neke vruće podatke, možete dodati Redis keš, da smanjite pritisak na bazu podataka.
- Java vodič za intervju prikupljen iskustva kandidata ByteDance 1, prvo tehničko intervju pitanje: koju bazu podataka koristite u svakodnevnom radu
- Java vodič za intervju prikupljen iskustva kandidata Tencent Cloud Intelligence 16, prvo intervju pitanje: koje baze podataka ste koristili, koja vam je najpoznatija?
- Java vodič za intervju prikupljen iskustva kandidata 360 3, prvo tehničko intervju pitanje: koje baze podataka ste koristili
- Java vodič za intervju prikupljen iskustva kandidata China Merchants Bank 6, intervju pitanje China Merchants Bank Network Technology: da li poznajete MySQL, Redis?
- Java vodič za intervju prikupljen iskustva kandidata državnih preduzeća 9, intervju pitanje: koje baze podataka najviše koristite (pomenuo MySQL i Redis)
- Java vodič za intervju prikupljen iskustva kandidata vivo 10, prvo tehničko intervju pitanje: kako obrisati/kreirati tabelu i postaviti primarni ključ, dajte primer za rastući/opadajući poredak sa SQL-om
- Java vodič za intervju prikupljen iskustva kandidata Didi 3, razvoj pozadine za servis vožnje, prvo intervju pitanje: razlozi za sporije performanse MySQL-a
1.Kako povezati dve tabele?
Možete koristiti unutrašnje povezivanje inner join, spoljašnje povezivanje outer join, unakrsno povezivanje cross join za kombinovanje rezultata upita iz više tabela.
Šta je unutrašnje povezivanje?
Unutrašnje povezivanje vraća redove koji se podudaraju u dve tabele. Pretpostavimo da imamo dve tabele, korisničku tabelu i tabelu narudžbina, želimo da pronađemo korisnike sa narudžbinama, možemo koristiti users INNER JOIN orders, povezivanjem po ID korisnika.
SELECT users.name, orders.order_id
FROM users
INNER JOIN orders ON users.id = orders.user_id;Samo zapisi koji postoje user_id u obe tabele će se pojaviti u rezultatu.
Šta je spoljašnje povezivanje?
Za razliku od unutrašnjeg povezivanja, spoljašnje povezivanje ne samo vraća podudarajuće redove iz dve tabele, već i redove bez podudaranja, popunjenih sa null.
Spoljašnje povezivanje se deli na levo spoljašnje povezivanje left join i desno spoljašnje povezivanje right join.
left join čuva sve zapise iz leve tabele, ako u desnoj tabeli postoje podudarajući zapisi, vraća se podudarni zapis, inače se popunjava sa null, koristi se kada jedna tabela ima podatke koje druga tabela možda nema.
Pretpostavimo da treba pretražiti sve korisnike i njihove narudžbine, čak i ako korisnik nije naručio, možemo koristiti levo povezivanje:
SELECT users.id, users.name, orders.order_id
FROM users
LEFT JOIN orders ON users.id = orders.user_id;Pre upita:
| users | orders |
|---|---|
| id | name |
| 1 | Wang Er |
| 2 | Zhang San |
| 3 | Li Si |
Posle upita:
| id | name | order_id |
|---|---|---|
| 1 | Wang Er | 10 |
| 2 | Zhang San | 20 |
| 3 | Li Si | null |
Desno povezivanje je ogledalo levog povezivanja, right join čuva sve zapise iz desne tabele koji zadovoljavaju uslov, ako u levoj tabeli postoje podudarajući zapisi, vraća se podudarni zapis, inače se popunjava sa null.
Šta je unakrsno povezivanje?
Unakrsno povezivanje vraća Dekartov proizvod dve tabele, to jest kombinaciju svakog reda leve tabele sa svakim redom desne tabele, broj redova je proizvod broja redova dve tabele.
Pretpostavimo da imamo tabelu A i tabelu B, tabela A ima 2 reda, tabela B ima 3 reda, rezultat unakrsnog povezivanja je 2 ✖️ 3 = 6 redova.
SELECT A.id, B.id
FROM A
CROSS JOIN B;Dekartov proizvod je koncept iz matematike, na primer skup A={a,b}, skup B={0,1,2}, tada A✖️B={<a,0>,<a,1>,<a,2>,<b,0>,<b,1>,<b,2>,}.
- Java vodič za intervju prikupljeno intervju pitanje Yonyou: kako povezati dve tabele
2.Koja je razlika između unutrašnjeg, levog i desnog povezivanja?
MySQL povezivanje se uglavnom deli na unutrašnje i spoljašnje povezivanje, spoljašnje se dalje deli na levo i desno povezivanje.
Unutrašnje povezivanje može pronasti zajedničke zapise iz dve tabele, što odgovara preseku dva skupa podataka.
Levo i desno povezivanje mogu pronasti različite zapise iz dve tabele, što odgovara uniji dva skupa podataka. Razlika je u tome što levo povezivanje čuva sve zapise iz leve tabele, a desno povezivanje je suprotno.
Kao primer uzmimo tabelu iz Jishupai projekta.
Imamo tri tabele, tabelu članaka article, uglavnom čuva naslov članka title, tabelu detalja članka article_detail, uglavnom čuva sadržaj content, tabelu komentara comment, uglavnom čuva komentar content, tri tabele su povezane preko id članka.
Prvo pogledajmo unutrašnje povezivanje:
SELECT LEFT(a.title, 20) AS ArticleTitle, LEFT(c.content, 20) AS CommentContent
FROM article a
INNER JOIN comment c ON a.id = c.article_id
LIMIT 2;
Vraća naslov članka i sadržaj komentara za članke koji imaju najmanje jedan komentar (prvih 20 znakova), vraća samo prvih 2 zapisa koji zadovoljavaju uslov.
Pogledajmo sada levo povezivanje:
SELECT LEFT(a.title, 20) AS ArticleTitle, LEFT(c.content, 20) AS CommentContent
FROM article a
LEFT JOIN comment c ON a.id = c.article_id
LIMIT 2;
Vraća naslove svih članaka i komentare na člancima, čak i ako neki članci nemaju komentare (popunjava se sa NULL).
Konačno pogledajmo desno povezivanje:
SELECT LEFT(a.title, 20) AS ArticleTitle, LEFT(c.content, 20) AS CommentContent
FROM comment c
RIGHT JOIN article a ON a.id = c.article_id
LIMIT 2;
- Java vodič za intervju prikupljeno Tencent Java back-end internship prvo intervju pitanje: recite razliku između unutrašnjeg povezivanja, levog povezivanja i desnog povezivanja u MySQL-u.
memo: 27. februara 2025. izmenjeno do ovde. U suštini su svi obični pitanja iz Mianzha-napred, tako da ako možete savladati visoko frekventna pitanja iz Mianzha-napred, verovatnoća za OC na intervjuu je stvarno velika, iskrenih reči.
3. Recite o tri normalne forme baze podataka?
Prva normalna forma, osigurava da je svaka kolona u tabeli nedeljiva osnovna jedinica podataka, na primer adresa korisnika treba da se podeli na 4 polja: pokrajina, grad, opština, detaljna adresa.
Druga normalna forma, zahteva da je svaka kolona u tabeli direktno povezana sa primarnim ključem. Na primer, u tabeli narudžbina, naziv proizvoda, jedinica, cena proizvoda itd. treba da se prebace u tabelu proizvoda.
Zatim kreiramo novu tabelu povezivanja narudžbina i proizvoda, povezivanjem broja narudžbine i broja proizvoda.
Treća normalna forma, ne-primarne kolone treba da zavise samo od primarnog ključa. Na primer, pri dizajnu tabele informacija o narudžbini, možete podeliti ime kupca, kompaniju, kontakt itd. u tabelu informacija o kupcu, a u tabeli informacija o narudžbini koristiti broj kupca za povezivanje.
Na šta treba obratiti pažnju prilikom kreiranja tabele?
Prvo, treba razmotiti da li tabela zadovoljava tri normalne forme baze podataka, osigurati da se polja ne mogu dalje deliti, ukloniti zavisnosti od ne-primarnog ključa, osigurati da polja zavise samo od primarnog ključa itd.
Zatim, pri biranju tipa polja, treba birati odgovarajući tip podataka.
Za set karaktera, birajte utf8mb4, tako podržavate kineski, engleski i emotikone.
Kada je količina podataka velika, na primer desetine miliona redova, treba razmotriti deljenje tabele. Na primer, tabelu narudžbina možete horizontalno podeliti da smanjite pritisak na jednu tabelu.
- Java vodič za intervju prikupljeno iskustvo kandidata ByteDance 13 Java back-end drugo intervju pitanje: šta su tri glavne normalne forme, zašto postoje tri glavne normalne forme, u kojim situacijama nije potrebno pratiti tri glavne normalne forme, dajte primer situacije
- Java vodič za intervju prikupljeno iskustvo kandidata JD.com 5 Java back-end tehničko prvo intervju pitanje: na šta treba obratiti pažnju prilikom kreiranja tabele
4.Koja je razlika između varchar i char?
varchar je promenljive dužine tip karaktera, u principu može da sadrži najviše 65535 karaktera, ali s obzirom na set karaktera i to što MySQL treba 1-2 bajta za predstavljanje dužine stringa, stvarni maksimum je 65533.
latin1 set karaktera, i osobina kolone definisana kao NOT NULL.
char je fiksne dužine tip karaktera, kada definišete CHAR(10) polje, bez obzira na stvarnu dužinu, zauzimaće 10 karaktera prostora. Ako ubačene podatke manje od 10 karaktera, preostali deo se popunjava razmacima.
| Vrednost | CHAR(4) | Potreba za skladištenjem (bajtovi) | VARCHAR(4) | Potreba za skladištenjem (bajtovi) |
|---|---|---|---|---|
| '' | ' ' | 4 | '' | 1 |
| 'ab' | 'ab ' | 4 | 'ab' | 3 |
| 'abcd' | 'abcd' | 4 | 'abcd' | 5 |
| 'abcdefgh' | 'abcd' | 4 | 'abcd' | 5 |
5.Koja je razlika između blob i text?
blob se koristi za čuvanje binarnih podataka, kao što su slike, audio, video, datoteke itd.; ali u stvarnom razvoju, obično čuvamo ove datoteke na OSS ili fajl serveru, a u bazi čuvamo URL datoteke.
text se koristi za čuvanje tekstualnih podataka, kao što su članci, komentari, logovi itd.
memo: 28. februara 2025. izmenjeno do ovde. Danas je član prijavio da je dobio offer od Li Xiang automobila, čestitam!
6.Koja je razlika između DATETIME i TIMESTAMP?
DATETIME čuva kompletnu vrednost datuma i vremena, nezavisno od vremenske zone.
TIMESTAMP čuva Unix timestamp, sekunde od 1970-01-01 00:00:01 UTC, zavisi od vremenske zone.
Dodatno, podrazumevana vrednost DATETIME je null, zauzima 8 bajtova; podrazumevana vrednost TIMESTAMP je trenutno vreme — CURRENT_TIMESTAMP, zauzima 4 bajta, u stvarnom razvoju se češće koristi, jer se može automatski ažurirati.
7.Koja je razlika između in i exists?
Kada koristite IN, MySQL prvo izvršava podupit, zatim koristi rezultat podupita kao uslov spoljašnjeg upita. To znači da rezultat podupita treba da se učita u memoriju.
EXISTS će za svaki red spoljašnjeg upita izvršiti jednom podupit. Ako podupit vrati bilo koji red, uslov EXISTS je tačan. EXISTS se fokusira na to da li podupit vraća redove, a ne na specifične vrednosti.
-- Privremena tabela za IN može postati usko grlo za performanse
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);
-- EXISTS može koristiti povezani indeks
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o
WHERE o.user_id = u.id AND o.amount > 100);IN je pogodno za situacije gde je rezultat podupita mali. Ako podupit vraća velike količine podataka, performanse IN mogu opasti, jer mora učitati ceo rezultat u memoriju.
Dok je EXISTS pogodno za situacije gde rezultat podupita može biti vrlo velik. Pošto EXISTS samo treba da proveri da li podupit vraća redove, a ne da učitava ceo rezultat, u nekim situacijama ima bolje performanse, naročito kada podupit može koristiti indeks.
Da li razumete vrednost NULL?
IN: Ako rezultat podupita sadrži vrednost NULL, to može dovesti do neočekivanih rezultata. Na primer, WHERE column IN (subquery), ako subquery vraća NULL, tada column IN (subquery) nikada neće biti tačno, osim ako column sam nije NULL.
EXISTS: Postupak sa vrednošću NULL je direktniji. EXISTS samo proverava da li podupit vraća redove, ne brine o konkretnim vrednostima redova, tako da nije podložan uticaju NULL vrednosti.
memo: 1. marta 2025. izmenjeno do ovde.
8.Koji tip je najbolji za čuvanje novca?
Ako se radi o e-trgovini, transakcijama, računima itd. koje uključuju novac, preporučuje se DECIMAL tip, jer je DECIMAL tip precizan numerički tip, nema grešaka u izračunima floating-point.
Na primer, DECIMAL(19,4) može čuvati najviše 19 cifara, od kojih 4 su decimalne.
CREATE TABLE orders (
id INT AUTO_INCREMENT,
amount DECIMAL(19,4),
PRIMARY KEY (id)
);Ako se radi o banci i scenarijima koji uključuju plaćanje, preporučuje se BIGINT tip. Iznos novca se može pomnožiti fiksnim faktorom, na primer 100, predstavlja se u “jedinicama od 1/100”, a zatim skladišti kao BIGINT. Ovaj način izbegava probleme sa pokretnim zarezom i pruža dobro performanse. Ali pri prikazu treba podeliti odgovarajućim faktorom.
Zašto se ne preporučuje FLOAT ili DOUBLE?
Pošto su FLOAT i DOUBLE tipovi sa pokretnim zarezom, postoji problem tačnosti.
U mnogim programskim jezicima, rezultat 0.1 + 0.2 će biti vrednost slična 0.30000000000000004, a ne očekivana 0.3.
9.🌟Kako čuvati emoji?
Jer je emoji (😊) 4-bajtni UTF-8 karakter, a MySQL utf8 set karaktera podržava samo 3-bajtne UTF-8 karaktere, tako da u MySQL-u za čuvanje emoji treba koristiti utf8mb4 set karaktera.
ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;MySQL 8.0 već podrazumevano podržava utf8mb4 set karaktera, možete proveriti sa SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';.
- Java vodič za intervju prikupljeno iskustvo kandidata ByteDance 13 Java back-end drugo intervju pitanje: kako čuvati emoji u MySQL-u, kako kodirati
10.Koja je razlika između drop, delete i truncate?
DROP je fizičko brisanje, koristi se za brisanje cele tabele, uključujući strukturu tabele, ne može se vratiti.
DELETE podržava brisanje na nivou redova, može imati WHERE uslov, može se vratiti.
TRUNCATE se koristi za brisanje svih podataka iz tabele, ali čuva strukturu tabele, ne može se vratiti.
memo: 4. marta 2025. izmenjeno do ovde. Pričam vam dobru vest, jedan član je dobio offer od iFLYTEK, ova plata u Hefei je stvarno odlična.
11.Koja je razlika između UNION i UNION ALL?
UNION automatski uklanja duplikate iz spojenog rezultata. UNION ALL ne uklanja duplikate, spaja sve rezultate.
12.Koja je razlika između count(1), count(*) i count(ime_kolone)?
U InnoDB motoru, COUNT(1) i COUNT(*) nemaju razlike, obe broje sve redove, uključujući NULL.
Ako tabela ima indeks, COUNT(*) će direktno koristiti indeks za brojanje, a ne puno skeniranje tabele, a COUNT(1) će takođe biti optimizovan u COUNT(*) od strane MySQL.
COUNT(ime_kolone) broji samo redove gde kolona nije NULL.
-- Pretpostavimo users tabelu:
+----+-------+------------+
| id | name | email |
+----+-------+------------+
| 1 | Zhang San | zhang@xx.com |
| 2 | Li Si | NULL |
| 3 | Wang Er | wang@xx.com |
+----+-------+------------+
-- COUNT(*)
SELECT COUNT(*) FROM users;
-- Rezultat: 3 (broji sve redove)
-- COUNT(1)
SELECT COUNT(1) FROM users;
-- Rezultat: 3 (broji sve redove)
-- COUNT(email)
SELECT COUNT(email) FROM users;
-- Rezultat: 2 (NULL se ne broji)Ovaj objašnjenje, pretpostavimo da imamo takvu tabelu:
CREATE TABLE t1 (
id INT,
name VARCHAR(50),
value INT
);Ubaceni podaci su:
INSERT INTO t1 VALUES
(1, 'A', 10),
(2, 'B', NULL), -- NULL u koloni vrednosti
(3, 'C', 30),
(4, NULL, 40), -- NULL u koloni imena
(5, 'E', NULL); -- NULL u koloni vrednostiPošto kolona id nema indeks, select count(*) je puno skeniranje tabele.
Zatim dodamo indeks na kolonu id.
alter table t1 add primary key (id);
Pogledajmo ponovo select count(*), primetimo da koristi indeks (MySQL podrazumevano dodaje indeks na primarni ključ).
Dodatno, zvanični priručnik za MySQL 8.0 jasno navodi da InnoDB motor na isti način obrađuje SELECT COUNT(*) i SELECT COUNT(1), nema razlike u performansama.
memo: 5. marta 2025. izmenjeno do ovde. Podelim još jednu dobru vest za vas koji učite pitanja za intervju, jedan član dobio je razvoj aplikacija velikih modela u Migu, vrlo dobar smer, čestitam! Dodajem i vam sreću🍀buff, i vi se potrudite.
13. Da li razumete redosled izvršenja SQL upita?
Razumem. Prvo se izvršava FROM za određivanje glavne tabele, zatim JOIN za povezivanje, zatim WHERE za filtriranje, zatim GROUP BY za grupisanje, HAVING za filtriranje agregatnih rezultata, SELECT za biranje konačnih kolona, ORDER BY za sortiranje, konačno LIMIT za ograničavanje broja vraćenih redova.
WHERE se prvo izvršava da bi se smanjila količina podataka, HAVING može filtrirati samo agregatne podatke, ORDER BY mora biti posle SELECT za sortiranje konačnog rezultata, LIMIT se izvršava poslednji da bi se smanjio prenos podataka.
| Redosled izvršenja | SQL ključne reči | Funkcija |
|---|---|---|
| ① | FROM | Odredi glavnu tabelu, pripremi podatke |
| ② | ON | Uslov za povezivanje više tabela |
| ③ | JOIN | Izvrši INNER JOIN / LEFT JOIN itd. |
| ④ | WHERE | Filtriraj redove (povećava efikasnost) |
| ⑤ | GROUP BY | Grupiši podatke |
| ⑥ | HAVING | Filtriraj agregirane podatke |
| ⑦ | SELECT | Izaberi konačne kolone za vraćanje |
| ⑧ | DISTINCT | Ukloni duplikate |
| ⑨ | ORDER BY | Sortiraj konačni rezultat |
| ⑩ | LIMIT | Ograniči broj vraćenih redova |
Ovaj redosled izvršenja se razlikuje od redosleda pisanja SQL izraza, zbog čega se ponekad ne mogu koristiti aliasi definisani u SELECT klauzi u WHERE klauzi, jer se WHERE izvršava pre SELECT.
Zašto se LIMIT izvršava poslednji?
Pošto se LIMIT izvršava na konačnom skupu rezultata, ako se LIMIT izvrši pre WHERE, tada će se prvo vratiti svi redovi, zatim se primeniti LIMIT ograničenje, što će povećati trošak prenosa podataka.
Zašto se ORDER BY izvršava posle SELECT?
Pošto sortiranje zahteva baziranje na konačnim vraćenim kolonama, ako se ORDER BY izvrši pre SELECT, proračun agregatnih funkcija poput COUNT(*) će imati problema.
SELECT name, COUNT(*) AS order_count
FROM orders
GROUP BY name
ORDER BY order_count DESC;14. Predstavite uobičajene MySQL komande (dodatak)
- marta 2024. dodatak.
Uobičajene MySQL komande uključuju komande za rad sa bazama podataka, komande za rad sa tabelama, CRUD komande za podatke u redovima, komande za kreiranje i izmenu indeksa i ograničenja, komande za upravljanje korisnicima i dozvolama, komande za kontrolu transakcija itd.
Neka se kaže o komandama za rad sa bazom podataka?
CREATE DATABASE database_name; se koristi za kreiranje baze podataka; DROP DATABASE database_name; se koristi za brisanje baze podataka; SHOW DATABASES; se koristi za prikaz svih baza podataka; USE database_name; se koristi za prebacivanje na bazu podataka.
Neka se kaže o komandama za rad sa tabelama?
CREATE TABLE table_name (kolona1 tip1, kolona2 tip2,...); se koristi za kreiranje tabele; DROP TABLE table_name; se koristi za brisanje tabele; SHOW TABLES; se koristi za prikaz svih tabela; DESCRIBE table_name; se koristi za pregled strukture tabele; ALTER TABLE table_name ADD column_name datatype; se koristi za izmenu tabele.
Neka se kaže o CRUD komandama za podatke u redovima?
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...); se koristi za ubacivanje podataka; SELECT column_names FROM table_name WHERE condition; se koristi za pretragu podataka; UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition; se koristi za ažuriranje podataka; DELETE FROM table_name WHERE condition; se koristi za brisanje podataka.
Neka se kaže o komandama za kreiranje i izmenu indeksa i ograničenja?
CREATE INDEX index_name ON table_name (column_name); se koristi za kreiranje indeksa; ALTER TABLE table_name ADD PRIMARY KEY (column_name); se koristi za dodavanje primarnog ključa; ALTER TABLE table_name ADD CONSTRAINT fk_name FOREIGN KEY (column_name) REFERENCES parent_table (parent_column_name); se koristi za dodavanje stranog ključa.
Neka se kaže o komandama za upravljanje korisnicima i dozvolama?
CREATE USER 'username'@'host' IDENTIFIED BY 'password'; se koristi za kreiranje korisnika; GRANT ALL PRIVILEGES ON database_name.table_name TO 'username'@'host'; se koristi za dodeljivanje dozvola; REVOKE ALL PRIVILEGES ON database_name.table_name FROM 'username'@'host'; se koristi za opozivanje dozvola; DROP USER 'username'@'host'; se koristi za brisanje korisnika.
Neka se kaže o komandama za kontrolu transakcija?
START TRANSACTION; se koristi za početak transakcije; COMMIT; se koristi za potvrdu transakcije; ROLLBACK; se koristi za poništenje transakcije.
- Java vodič za intervju prikupljeno intervju pitanje Yonyou Financial prvo intervju: predstavite uobičajene MySQL komande
15. Da li razumete izvršne datoteke u MySQL bin direktorijumu (dodatak)
- marta 2024. dodatak
Preporučeno čitanje: Neke izvršne datoteke u MySQL bin direktorijumu
Razumem. MySQL bin direktorijum sadrži mnogo izvršnih datoteka, uglavnom se koristi za upravljanje MySQL serverom, bazama podataka, tabelama, podacima itd. Na primer:
- mysql: koristi se za povezivanje sa MySQL serverom
- mysqldump: koristi se za backup baze podataka, vrlo korisno za backup, migraciju ili oporavak podataka
- mysqladmin: koristi se za izvršavanje nekih administrativnih operacija, na primer kreiranje baze podataka, brisanje baze podataka, pregled stanja MySQL servera itd.
- mysqlcheck: koristi se za proveru, popravku, analizu i optimizaciju tabela baze podataka, vrlo korisno za održavanje baze podataka i optimizaciju performansi.
- mysqlimport: koristi se za uvoz podataka iz tekstualnih datoteka u tabele baze podataka, pogodno za uvoz velikih količina podataka.
- mysqlshow: koristi se za prikaz informacija o bazama podataka, tabelama, kolonama itd. na MySQL serveru.
- mysqlbinlog: koristi se za pregled sadržaja binarnih log datoteka MySQL-a, može se koristiti za oporavak podataka, pregled promena podataka itd.
16. Kako pretražiti 3-10. zapis u MySQL-u (dodatak)
- marta 2024. dodatak
Možete koristiti limit izraz, u kombinaciji sa offset-om i brojem redova.
SELECT * FROM table_name LIMIT 2, 8;limit izraz se koristi za ograničavanje broja rezultata upita, offset označava od kojeg započinje zapis, broj redova označava koliko zapisa će biti vraćeno.
- 2: offset, označava da se preskaču prva dva zapisa, počevši od trećeg zapisa.
- 8: broj redova, označava da se od offset-a vraća 8 zapisa.
Offset počinje od 0, tj. offset prvog zapisa je 0; ako želite da započnete od trećeg zapisa, offset bi trebao biti 2.
- Java vodič za intervju prikupljeno iskustvo kandidata Meituan 16 letnja praksa prvo intervju pitanje: kako pretražiti 3-10. zapis u MySQL-u?
17. Koje MySQL funkcije ste koristili (dodatak)
- aprila 2024. dodatak
Koristio sam mnogo, na primer funkcije za obradu stringova:
CONCAT(): Koristi se za povezivanje dva ili više stringova.LENGTH(): Koristi se za vraćanje dužine stringa.SUBSTRING(): Izvlači podstring iz stringa.REPLACE(): Zamenjuje deo stringa.TRIM(): Uklanja razmake ili druge navedene karaktere sa obe strane stringa.
Stvarni podaci:
-- Povezivanje stringova
SELECT CONCAT('Chenmo', ' ', 'Wang Er') AS concatenated_string;
-- Dobijanje dužine stringa
SELECT LENGTH('Chenmo Wang Er') AS string_length;
-- Izvlačenje podstringa
SELECT SUBSTRING('Chenmo Wang Er', 1, 5) AS substring;
-- Zamena sadržaja stringa
SELECT REPLACE('Chenmo Wang Er', 'Wang Er', 'MySQL') AS replaced_string;
-- Uklanjanje razmaka sa obe strane stringa
SELECT TRIM(' Chenmo Wang Er ') AS trimmed_string;Funkcije za obradu brojeva:
ABS(): Vraća apsolutnu vrednost broja.ROUND(): Zaokružuje na zadati broj decimala.MOD(): Vraća ostatak deljenja.
Stvarni podaci:
-- Vraćanje apsolutne vrednosti
SELECT ABS(-123) AS absolute_value;
-- Zaokruživanje
SELECT ROUND(123.4567, 2) AS rounded_value;
-- Ostatak pri deljenju
SELECT MOD(10, 3) AS modulus;Funkcije za obradu datuma i vremena:
NOW(): Vraća trenutni datum i vreme.CURDATE(): Vraća trenutni datum.
Stvarni podaci:
-- Vraćanje trenutnog datuma i vremena
SELECT NOW() AS current_date_time;
-- Vraćanje trenutnog datuma
SELECT CURDATE() AS current_date;Agregatne funkcije:
SUM(): Računa zbir numeričke kolone.AVG(): Računa prosek numeričke kolone.COUNT(): Računa broj redova u koloni.
Stvarni podaci:
-- Kreiranje tabele i ubacivanje podataka za agregatni upit
CREATE TABLE sales (
product_id INT,
sales_amount DECIMAL(10, 2)
);
INSERT INTO sales (product_id, sales_amount) VALUES (1, 100.00);
INSERT INTO sales (product_id, sales_amount) VALUES (1, 150.00);
INSERT INTO sales (product_id, sales_amount) VALUES (2, 200.00);
-- Izračunavanje sume
SELECT SUM(sales_amount) AS total_sales FROM sales;
-- Izračunavanje proseka
SELECT AVG(sales_amount) AS average_sales FROM sales;
-- Izračunavanje ukupnog broja redova
SELECT COUNT(*) AS total_entries FROM sales;Logičke funkcije:
IF(): Ako je uslov tačan, vraća jednu vrednost; inače vraća drugu vrednost.CASE: Vraća vrednost na osnovu niza uslova.
-- IF funkcija
SELECT IF(1 > 0, 'True', 'False') AS simple_if;
-- CASE izraz
SELECT CASE WHEN 1 > 0 THEN 'True' ELSE 'False' END AS case_expression;
- Java vodič za intervju prikupljeno iskustvo kandidata Huawei OD 1 prvo intervju pitanje: koje MySQL funkcije ste koristili?
- Java vodič za intervju prikupljeno iskustvo kandidata male kompanije TAL Education Test Development 3 test razvoj prvo intervju pitanje: koje MySQL funkcije poznajete, npr. order by count()
18. Recite nešto o implicitnoj konverziji tipova podataka u SQL-u (dodatak)
- aprila 2024. dodatak
Kada se sabiraju ceo broj i broj sa pokretnim zarezom, ceo broj će biti konvertovan u broj sa pokretnim zarezom.
SELECT 1 + 1.0; -- Rezultat je 2.0Kada se sabiraju string i ceo broj, string će biti konvertovan u ceo broj.
SELECT '1' + 1; -- Rezultat je 2Implicitna konverzija može dovesti do neočekivanih rezultata, najbolje je izbegavati kroz eksplicitnu konverziju.
SELECT CAST('1' AS SIGNED INTEGER) + 1; -- Rezultat je 2Stvarni rezultati provere:
- Java vodič za intervju prikupljeno iskustvo kandidata male kompanije 1 Java back-end intervju pitanje: recite nešto o implicitnoj konverziji tipova podataka u SQL-u?
memo: 6. marta 2025. izmenjeno do ovde.
19. Recite nešto o parsiranju sintaksnog stabla SQL-a (dodatak)
- septembra 2024. dodatak
Parsiranje SQL sintaksnog stabla je proces konvertovanja SQL upita u apstraktno sintaksno stablo — AST, to je prvi korak u obradi upita od strane motora baze podataka, i važno sredstvo za sprečavanje SQL injekcije.
Obično se deli na 3 faze.
Prva faza, leksička analiza: rastavljanje SQL izraza, prepoznavanje ključnih reči, imena tabela, imena kolona itd.
---ova partija vam pomaže da razumete početak, ne morate učiti za intervju---
Na primer:
SELECT id, name FROM users WHERE age > 18;Biće rastavljeno na:
[SELECT] [id] [,] [name] [FROM] [users] [WHERE] [age] [>] [18] [;]---ova partija vam pomaže da razumete kraj, ne morate učiti za intervju---
Druga faza, sintaksna analiza: proverava da li SQL zadovoljava sintaksna pravila i gradi apstraktno sintaksno stablo.
---ova partija vam pomaže da razumete početak, ne morate učiti za intervju---
Na primer, gornji izraz će biti izgrađen u sledeće sintaksno stablo:
SELECT
/ \
Columns FROM
/ \ |
id name users
|
WHERE
|
age > 18Ili se može prikazati ovako:
SELECT
├── COLUMNS: id, name
├── FROM: users
├── WHERE
│ ├── CONDITION: age > 18---ova partija vam pomaže da razumete kraj, ne morate učiti za intervju---
Treća faza, semantička analiza: proverava da li tabele i kolone postoje, vrši proveru dozvola itd.
---ova partija vam pomaže da razumete početak, ne morate učiti za intervju---
Na primer, izvršavanje:
SELECT id, name FROM users WHERE age > 'eighteen';Doći će do greške:
ERROR: Column 'age' is INT, but 'eighteen' is STRING.---ova partija vam pomaže da razumete kraj, ne morate učiti za intervju---
- Java vodič za intervju prikupljeno intervju pitanje ByteDance 21 TikTok Mall prvo intervju pitanje: parsiranje sintaksnog stabla SQL-a
memo: 7. marta 2025. izmenjeno do ovde. Još jedan offer, jedan član je dobio praksu u Jingwei Hengrun, i iskreno rekao da je imao mnogo intervjua, rekao sam da su sva pitanja koja su se pojavila više od 5 puta, ne treba ništa da se kaže, Mianzha-napred YYDS.
Arhitektura baze podataka
20.Osnovna arhitektura MySQL?
MySQL koristi slojevitu arhitekturu, uglavnom uključuje sloj povezivanja, sloj usluga i sloj motora za skladištenje.
①, sloj povezivanja uglavnom je odgovoran za upravljanje klijentskim povezivanjima, uključuje proveru identiteta korisnika, proveru dozvola, upravljanje povezivanjima itd. Može se koristiti pool povezivanja baze podataka za povećanje efikasnosti.
②, sloj usluga je jezgro MySQL, uglavnom je odgovoran za parsiranje upita, optimizaciju, izvršenje itd. U ovom sloju, SQL izraz se parsira, optimizuje, zatim se prosleđuje motoru za izvršenje i vraća rezultat. Ovaj sloj sadrži parser, optimizator, generator plana izvršenja, modul za logiranje itd.
③, sloj motora za skladištenje je odgovoran za stvarno skladištenje i preuzimanje podataka. MySQL podržava više motora za skladištenje, kao što su InnoDB, MyISAM, Memory itd.
U kom sloju se piše binlog?
binlog je u sloju usluga, odgovoran za evidentiranje izmena SQL izraza. Evidentira sve operacije koje menjaju bazu podataka, koristi se za oporavak podataka, glavno-replikaciju itd.
- Java vodič za intervju prikupljeno intervju pitanje ByteDance 21 TikTok Mall prvo intervju pitanje: na koliko slojeva se deli MySQL? u kom sloju se piše binlog
21.🌟Kako se izvršava jedan SELECT izraz?
Kada izvršavamo SELECT izraz, MySQL ne čita podatke direktno sa diska, već prolazi kroz 6 koraka za parsiranje, optimizaciju, izvršenje, a zatim vraća rezultat.
Prvi korak, klijent šalje SQL upit na MySQL server.
Drugi korak, konektor MySQL servera počinje da procesira ovaj zahtev, uspostavlja konekciju sa klijentom, dobija dozvole, upravlja konekcijom.
Treći korak, parser parsira SQL izraz, proverava da li ispunjava SQL pravila, osigurava da baza podataka, tabela i kolone postoje, i rešava imena i proverava dozvole.
Četvrti korak, optimizator određuje plan izvršenja SQL izraza, uključuje izbor indeksa, redosled povezivanja tabela itd.
Peti korak, izvršilac poziva API motora za skladištenje za čitanje i pisanje podataka.
Šesti korak, motor za skladištenje preuzima podatke i vraća rezultat klijentu. Klijent prima rezultat upita.
- Java vodič za intervju prikupljeno intervju pitanje Meituan 2 Java back-end tehničko prvo intervju pitanje: razumete li ceo proces izvršenja MySQL izraza?
- Java vodič za intervju prikupljeno intervju pitanje Meituan 18 Chengdu Daojia intervju pitanje: proces pretrage jednog podatka u MySQL-u
- Java vodič za intervju prikupljeno intervju pitanje ByteDance 19 Tomato Novel prvo intervju pitanje: proces izvršenja jedne SQL naredbe u MySQL-u
memo: 8. marta 2025. izmenjeno do ovde.
22. Kako se izvršava jedan UPDATE izraz?
Uopšteno, proces izvršenja UPDATE izraza uključuje čitanje stranice podataka, zaključavanje i otključavanje, potvrdu transakcije, logiranje i više koraka.
Uzmimo update test set a=1 where id=2 kao primer:
Pre početka transakcije, MySQL mora da evidentira undo log, koristi se za rollback transakcije.
| Operacija | id | Stara vrednost | Nova vrednost |
|---|---|---|---|
| update | 2 | N | 1 |
Osim evidentiranja undo log, motor za skladištenje će takođe upisati operaciju ažuriranja u redo log, označi stanje kao prepare, i osigurati da je redo log perzistentiran na disk. Ovaj korak može osigurati da čak i ako se sistem sruši, podaci mogu biti oporavljeni u konzistentno stanje putem redo log.
Nakon upisivanja redo log, MySQL će dobiti zaključavanje redova, izmeniti vrednost a na 1, označiti kao prljava stranica, u ovom trenutku podaci su i dalje u buffer pool memorije, neće odmah biti upisani na disk. Pozadinska nit će u odgovarajućem trenutku osvežiti prljave stranice na disk, kako bi poboljšala performanse.
Konačno potvrdi transakciju, zapisi u redo log se označavaju kao committed, zaključavanje redova se oslobađa.
Ako je MySQL uključio binlog, takođe će evidentirati operaciju ažuriranja u binlog, uglavnom se koristi za glavno-replikaciju.
Kao i oporavak podataka, može se kombinovati sa redo log za oporavak tačka po tačka. Upisivanje binlog se obično dešava prilikom potvrde transakcije, zajedno sa redo log čini “dvofaznu potvrdu”, osigurava konzistentnost oba dnevnika.
Obratite pažnju, upisivanje redo log ima dvofaznu potvrdu, prvo je upis u stanju prepare pre binlog upisa, drugo je upis u stanju commit posle binlog upisa.
memo: 9. marta 2025. izmenjeno do ovde.
23. Recite nešto o segmentima, zonama, stranicama i redovima MySQL-a (dodatak)
- aprila 2024. dodatak
Preporučeno čitanje: Razumite mehanizam redova podataka i prelivanja redova MySQL-a
MySQL čuva podatke u obliku tabela, a strukturu tabele čine segmenti, zone, stranice i redovi.
①, Segment: tabelu prostor čini više segmenata, uobičajeni segmenti uključuju segment podataka, segment indeksa, segment rollback itd.
Prilikom kreiranja indeksa kreira se dva segmenta, segment podataka i segment indeksa, segment podataka se koristi za čuvanje podataka u listovima; segment indeksa se koristi za čuvanje podataka u ne-listovima.
Segment rollback sadrži stare podatke korišćene za rollback podataka tokom izvršenja transakcije.
②, Zona: segment se sastoji od jedne ili više zona, zona je grupa kontinualnih stranica, obično sadrži 64 kontinualne stranice, to jest 1M podataka.
Korišćenje zona umesto pojedinačnih stranica za dodelu podataka može optimizovati operacije diska, smanjiti vreme traženja diska, naročito prilikom čitanja i pisanja velikih količina podataka.
③, Stranica: Stranica je osnovna jedinica za skladištenje podataka u InnoDB, standardna veličina je 16 KB, jedan čvor u stablu indeksa je jedna stranica.
To znači da baza podataka svaki put čita i piše u jedinicama od 16 KB, jednom čita najmanje 16KB podataka sa diska u memoriju, jednom piše najmanje 16KB podataka sa memorije na disk.
④, Red: InnoDB koristi način skladištenja po redovima, to znači da se podaci organiziraju i upravljaju po redovima, podaci redova mogu imati više formata, na primer COMPACT, REDUNDANT, DYNAMIC itd.
MySQL 8.0 podrazumevani format redova je DYNAMIC, razvijen iz COMPACT, to znači da ako ovi podaci prevaziđu ograničenje inline skladištenja na stranici, biće skladišteni u stranici prelivanja.
Možete videti format redova putem show table status like '%article%'.
Motori za skladištenje
24.🌟Koji motori za skladištenje MySQL postoje?
MySQL podržava više motora za skladištenje, česti su MyISAM, InnoDB, Memory itd.
---pomaže razumevanju start, ne morate učiti za intervju---
Napravim tabelu za poređenje:
| Funkcija | InnoDB | MyISAM | Memory |
|---|---|---|---|
| Podržava transakcije | Yes | No | No |
| Podržava punoteksni indeks | Yes | Yes | No |
| Podržava B+ stablo indeks | Yes | Yes | Yes |
| Podržava heš indeks | Yes | No | Yes |
| Podržava strani ključ | Yes | No | No |
---pomaže razumevanju end, ne morate učiti za intervju---
Pored toga, treba znati:
①, Pre MySQL 5.5, podrazumevani motor je bio MyISAM, posle 5.5 je InnoDB.
②, Heš indeks koji InnoDB podržava je adaptivan, ne može se ručno kontrolisati.
③, Od MySQL 5.6, InnoDB podržava punoteksni indeks.
④, Najmanji prostor tabele InnoDB je malo manji od 10M, najveći prostor tabele zavisi od veličine stranice.
Kako promeniti motor podataka MySQL?
Možete promeniti motor podataka MySQL putem alter table izraza.
ALTER TABLE your_table_name ENGINE=InnoDB;Međutim, to se ne preporučuje, treba unapred dizajnirati koji ćete motor za skladištenje koristiti.
- Java vodič za intervju prikupljeno iskustvo kandidata ByteDance 1 Java back-end tehničko prvo intervju pitanje: koje motore za skladištenje podržava MySQL?
- Java vodič za intervju prikupljeno intervju pitanje Yonyou: koja je razlika između InnoDB motora i heš motora
- Java vodič za intervju prikupljeno iskustvo kandidata državnih preduzeća 9 intervju pitanje: motori za skladištenje MySQL-a
- Java vodič za intervju prikupljeno iskustvo kandidata JD.com 4 cloud praksa intervju pitanje: koje motore podataka ima mysql, razlika (innodb, MyISAM, Memory)
- Java vodič za intervju prikupljeno iskustvo kandidata Alibaba sistema 19 Ele.me intervju pitanje: predstavljanje motora za skladištenje
memo: 10. marta 2025. izmenjeno do ovde.
25.Kako treba birati motor za skladištenje?
U većini slučajeva, podrazumevani InnoDB je dovoljan, InnoDB može obezbediti transakcije, zaključavanje na nivou redova, strane ključeve, B+ stablo indekse itd.
MyISAM je pogodan za scenarije gde se više čita nego piše.
MEMORY je pogodan za privremene tabele, kada količina podataka nije velika. Pošto se svi podaci čuvaju u memoriji, brzina je vrlo visoka.
- Java vodič za intervju prikupljeno iskustvo kandidata Kuaishou 2 prvo intervju pitanje: karakteristike MySQL InnoDB? Zašto koristiti B+ stablo? A ne B stablo, koja je razlika?
26.Koja je glavna razlika između InnoDB i MyISAM?
Najveća razlika između InnoDB i MyISAM je u podršci transakcija i mehanizmu zaključavanja. InnoDB podržava transakcije, zaključavanje na nivou redova, pogodan za većinu poslovnih sistema; MyISAM ne podržava transakcije, koristi zaključavanje tabela, brzo je za upite ali sporo za pisanje, pogodno za scenarije gde se više čita nego piše.
Pored toga, sa stanovišta strukture skladištenja, MyISAM koristi tri formata fajlova, .rm fajl čuva definiciju tabele; .MYD čuva podatke; .MYI čuva indekse; InnoDB koristi dva formata, .rm fajl čuva definiciju tabele; .ibd čuva podatke i indekse.
Sa stanovišta tipa indeksa, MyISAM je ne-klastirani indeks, indeks i podaci su odvojeni, indeks čuva pokazivače na fajlove podataka.
InnoDB je klaster indeks, indeks i podaci nisu odvojeni.
Na finijem nivou, MyISAM ne podržava strane ključeve, može biti bez primarnog ključa, broj redova tabele se čuva u atributima tabele, upit može direktno vratiti; InnoDB podržava strane ključeve, mora imati primarni ključ, broj redova zahteva skeniranje cele tabele, ako postoji indeks skenira indeks.
Da li razumete memorijsku strukturu InnoDB?
- aprila 2025. dodatak
Memorijska oblast InnoDB se uglavnom sastoji iz dva dela, buffer pool i log buffer. buffer pool se koristi za keširanje stranica podataka i stranica indeksa, poboljšava performanse čitanja i pisanja; log buffer se koristi za keširanje redo log-a, poboljšava performanse pisanja.
Da li razumete strukturu stranice podataka?
Stranica podataka InnoDB se sastoji od 7 delova, gde su veličine zaglavlja fajla, zaglavlja stranice i završnog dela fajla fiksne, redom 38, 56 i 8 bajtova, koriste se za označavanje nekih informacija o stranici. Zapisi redova, slobodan prostor i direktorijum stranice su dinamički, predstavljaju stvarni prostor za skladištenje zapisa redova.
Evo sumarnog tabele:
| Naziv | Kineski naziv | Veličina (jedinica: B) | Opis |
|---|---|---|---|
| File Header | Zaglavlje fajla | 38 | Neki opšti podaci o stranici |
| Page Header | Zaglavlje stranice | 56 | Neki specifični podaci za stranicu podataka |
| Infimum + Supermum | Najmanji i najveći zapis | 26 | Dva virtuelna zapisa redova |
| User Records | Stvarni zapisi korisnika | Neodređeno | Sadržaj stvarno skladištenih zapisa redova |
| Free Space | Slobodan prostor | Neodređeno | Dosad nekorišćen prostor na stranici |
| Page Directory | Direktorijum stranice | Neodređeno | Relativni položaj nekih zapisa na stranici |
| File Trailer | Završni deo fajla | 8 | Provera da li je stranica potpuna |
Stvarni zapisi se skladište u User Records u skladu sa navedenim formatom redova.
Svaki File Header stranice podataka ima broj prethodne i sledeće stranice, sve stranice podataka će formirati dvostruko vezanu listu.
U InnoDB, podrazumevana veličina stranice je 16KB. Možete je videti putem show variables like 'innodb_page_size';.
Preporučeno čitanje: Struktura stranice podataka MySQL-a
- Java vodič za intervju prikupljeno iskustvo kandidata ByteDance 1 Java back-end tehničko prvo intervju pitanje: koje su razlike između MyISAM i InnoDB?
- Java vodič za intervju prikupljeno iskustvo kandidata Meituan 9 prvo intervju pitanje: kakvi su podaci koje MySQL skladišti?
memo: 11. marta 2025. izmenjeno do ovde.
27. Da li razumete Buffer Pool InnoDB? (dodatak)
- novembra 2024. dodatak
Buffer Pool je memorijski bafer u motoru InnoDB, on učitava često korišćene stranice podataka i stranice indeksa u memoriju, prvo se upita Buffer Pool pri čitanju, ako pogodi nije potreban pristup disku.
Ako ne pogodi, čita sa diska i učitava u Buffer Pool, tada može doći do eliminacije stranice, nekoriste stranice se izbacuju iz Buffer Pool.
Pri operacijama pisanja se ne piše direktno na disk, već se prvo menja stranica u memoriji, tada se stranica označava kao prljava, pozadinska nit periodično osvežava prljave stranice na disk.
Buffer Pool može značajno smanjiti broj operacija čitanja i pisanja sa diska, čime poboljšava performanse čitanja i pisanja MySQL-a.
Koja je podrazumevana veličina Buffer Pool?
Na mojoj mašini je podrazumevana veličina Buffer Pool InnoDB 128MB.
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';Dodatno, na sistemima sa 1GB-4GB RAM, podrazumevana vrednost je 25% sistemskog RAM-a; na sistemima sa više od 4GB RAM, podrazumevana vrednost je 50% sistemskog RAM-a, ali ne više od 4GB.
Da li razumete optimizaciju LRU algoritma od strane InnoDB?
Razumem, InnoDB je poboljšao LRU algoritam, nedavno pristupljeni podaci se ne stavljaju direktno na početak LRU liste, već na poziciju midpoiont. Podrazumevano, midpoint se nalazi na 5/8 LRU liste.
Na primer, ako Buffer Pool ima 100 stranica, pozicija umetanja nove stranice je otprilike 80. stranica; kada se podaci stranice često pristupaju, tada se premestaju u young zonu, prednost ovog pristupa je da se vruće stranice dugo zadržavaju u memoriji i ne lako se izbacuju.
----ova partija vam pomaže da razumete početak, ne morate učiti za intervju----
Možete podesiti odnos old i young zona u Buffer Pool putem parametra innodb_old_blocks_pct; podesiti vreme zadržavanja stranice u young zoni putem parametra innodb_old_blocks_time.
Podrazumevano, old zona zauzima 37% u LRU listi; najmanji vremenski interval za ponovni pristup istoj stranici je 1000 milisekundi.
To znači, ako se neka stranica pristupa više puta u roku od 1 sekunde, brojaće se samo jednom, neće odmah postati vruća stranica, čime se sprečava zagađivanje keša usled kratkotrajnog masovnog pristupa.
----ova partija vam pomaže da razumete kraj, ne morate učiti za intervju----
- Java vodič za intervju prikupljeno iskustvo kandidata Meituan 15 Dianping back-end tehničko intervju pitanje: recite nešto o bufferpool
memo: 12. marta 2025. izmenjeno do ovde. Još jedna dobra vest za vas, danas jedan član javio da je dobio offer od JD.com i Meituan za socijalno regrutovanje, kasnije je dopunio da je prošao i Didi, mogu samo reći da je predobar.
Dnevnici
28.🌟Koji dnevnici postoje u MySQL?
Ima 6 glavnih kategorija, dnevnik grešaka za dijagnostiku problema, dnevnik sporih upita za analizu SQL performansi, general log za evidenciju svih SQL izraza, binlog za glavno-replikaciju i oporavak podataka, redo log za osiguranje trajnosti transakcija, undo log za rollback transakcija i MVCC.
----pomaže razumevanju start, ne morate učiti za intervju----
①, Dnevnik grešaka (Error Log): evidentira probleme prilikom pokretanja, rada ili zaustavljanja MySQL servera.
②, Dnevnik sporih upita (Slow Query Log): evidentira sve SQL izraze čije vreme izvršenja prelazi vrednost long_query_time. Ova vrednost je konfigurabilna, podrazumevano je dnevnik sporih upita isključen.
③, Opšti dnevnik upita (General Query Log): evidentira informacije o pokretanju i zaustavljanju MySQL servera, informacije o konekciji klijenata, i SQL izraze za ažuriranje, upit itd.
④, Binarni dnevnik (Binary Log): evidentira sve SQL izraze koji menjaju stanje baze podataka, i vreme izvršenja svakog izraza, kao INSERT, UPDATE, DELETE itd., ali ne uključuje SELECT i SHOW operacije.
⑤, Redo dnevnik (Redo Log): evidentira svaku operaciju pisanja za InnoDB tabele, nije na nivou SQL, već na fizičkom nivou, uglavnom za oporavak od pada.
⑥, Undo dnevnik (Undo Log, ili dnevnik transakcija): evidentira vrednost pre izmene podataka, koristi se za rollback transakcija.
----pomaže razumevanju end, ne morate učiti za intervju----
Molim vas da detaljno objasnite binlog?
Preporučeno čitanje: Razumite manje poznate tajne MySQL Binlog
binlog je vrsta binarnog dnevnika koji evidentira sve operacije izmene baze podataka na disku.
Ako se slučajno obrišu podaci, možete koristiti binlog za povratak u stanje pre slučajnog brisanja.
# Korak 1: Obnova kompletnog backup-a
mysql -u root -p < full_backup.sql
# Korak 2: Primena Binlog na navedeni vremenski trenutak
mysqlbinlog --start-datetime="2025-03-13 14:00:00" --stop-datetime="2025-03-13 15:00:00" binlog.000001 | mysql -u root -pAko želite da postavite glavno-replikaciju, možete omogućiti replika bazi da periodično čita binlog glavne baze.
MySQL nudi tri formata binlog-a: Statement, Row i Mixed, odgovaraju nivou SQL izraza, nivou redova i mešovitom nivou, podrazumevano je nivo redova.
S obzirom na sufiks, binlog datoteke se dele u dve kategorije: indeksne datoteke koje se završavaju sa .index i binarne dnevničke datoteke koje se završavaju sa .00000*.
binlog je podrazumevano isključen.
U produkcionoj okolini je obavezno uključiti, možete uključiti binlog konfigurisanjem parametra log_bin u my.cnf datoteci.
log_bin = mysql-bin #uključuje binlog
#mysql-bin.* maksimalni broj bajtova datoteke dnevnika (jedinica: bajt)
#podesite maksimalno 100MB
max_binlog_size=104857600
#podeseno da zadrži samo 7 dana BINLOG (jedinica: dani)
expire_logs_days = 7
#binlog dnevnik evidentira samo izmene navedene baze
#binlog-do-db=db_name
#binlog dnevnik ne evidentira izmene navedene baze
#binlog-ignore-db=db_name
#koliko puta se bafer piše pre nego što se jednom operiše na disk, podrazumevano 0
sync_binlog=0Koje parametre konfiguracije binlog poznajete?
log_bin = mysql-bin se koristi za uključivanje binlog, tako možete pronaći db-bin.000001, db-bin.000002 i druge dnevničke datoteke u direktorijumu podataka MySQL.
max_binlog_size=104857600 se koristi za podesavanje veličine svake binlog datoteke, ne preporučuje se da bude prevelika jer je mrežni prenos težak.
Kada binlog datoteka dostigne max_binlog_size, MySQL će zatvoriti trenutnu datoteku i kreirati novu binlog datoteku.
expire_logs_days = 7 se koristi za podesavanje automatskog isteka binlog datoteka na 7 dana. Istekle binlog datoteke će biti automatski obrisane. Sprečava da se dugo akumulirane binlog datoteke zauzimaju previše prostora za skladištenje, Jishupai projekat koristi jeftini server, tako da je ova konfiguracija važna.
binlog-do-db=db_name, navedi koje izme tabele baze podataka treba evidentirati.
binlog-ignore-db=db_name, navedi koje izme tabele baze podataka treba ignorisati.
sync_binlog=0, podesi koliko puta će se operacija pisanja binlog desiti pre nego što se okida jedna operacija sinhronizacije diska. Podrazumevana vrednost je 0, što znači da MySQL neće aktivno okidati operacije sinhronizacije, već će zavisiti od strategije keša diska operativnog sistema.
To znači da se prilikom izvršavanja operacije pisanja podaci prvo upisuju u keš, a kada se keš popuni, operativni sistem odjednom upisuje podatke na disk.
Ako se podesi na 1, znači da će se posle svake operacije pisanja binlog sinhronizovati sa diskom, iako osigurava pravovremeno upisivanje podataka na disk, smanjiće performanse.
Možete proveriti da li je binlog uključen putem show variables like '%log_bin%';.
Zašto su potrebni undolog i redolog pored binlog?
binlog pripada sloju Server, nezavisan je od motora za skladištenje, ne može direktno operisati fizičke stranice podataka. Dok su redo log i undo log osnova implementacije ACID od strane motora InnoDB.
binlog se fokusira na globalni zapis logičkih izmena; redo log se koristi za osiguranje perzistencije fizičkih izmena, osigurava da transakcija na kraju može uspešno da se upiše na disk; undo log je dnevnik logičkih inverznih operacija, evidentira stare vrednosti, olakšava povratak u stanje pre početka transakcije.
Drugi način odgovaranja.
binlog evidentira ceo SQL ili promene redova; redo log služi za oporavak “podnesenih ali ne upisanih na disk” podataka, undo log služi za opoziv nepodnesenih transakcija.
Uzmimo primer jedne transakcije ažuriranja:
# Pokretanje transakcije
BEGIN;
# Ažuriranje podataka
UPDATE users SET age = age + 1 WHERE id = 1;
# Potvrda transakcije
COMMIT;Na početku transakcije se generiše undo log, evidentira podatke pre izmene, na primer originalna vrednost je 18:
undo log: id=1, age=18Prilikom izmene podataka, podaci se upisuju u redo log.
Na primer, na stranici podataka page_id=123, korisnik sa id=1 je ažuriran na age=26:
redo log (prepare):
page_id=123, offset=0x40, before=18, after=26Kada se transakcija potvrdi, redo log se upisuje na disk, binlog se upisuje na disk.
Nakon završetka pisanja binlog, status redo log će se promeniti u commit:
redo log (commit):
page_id=123, offset=0x40, before=18, after=26Ako je binlog u Statement formatu, evidentiraće jednu SQL izjavu:
UPDATE users SET age = age + 1 WHERE id = 1;Ako je binlog u Row formatu, evidentiraće:
Tabela: users
before: id=1, age=18
after: id=1, age=26Nakon toga, pozadinska nit će asinhrono osvežiti izmene iz redo log na disk.
memo: 13. marta 2025. izmenjeno do ovde. Jedan član javio, prošao je drugi intervju ByteDance, traži letnju praksu neverovatno lako, odmah je pevao Mianzha osnovna pitanja.
Koji je radni mehanizam redo log?
Kada se transakcija pokrene, MySQL će dodeliti jedinstveni identifikator toj transakciji.
Tokom izvršavanja transakcije, pri svakoj izmeni podataka, MySQL će generisati jedan Redo Log, evidentira stanje podataka pre i posle izmene.
Ovi Redo Log prvo će biti upisani u Redo Log Buffer u memoriji.
Kada se transakcija potvrdi, MySQL će osvežiti zapise iz Redo Log Buffer na disk u Redo Log datoteku.
Samo kada se Redo Log uspešno upiše na disk, transakcija se smatra uspešno potvrđenom.
Kada se MySQL sruši i ponovo pokrene, prvo će proveriti Redo Log. Za potvrđene transakcije, MySQL će ponoviti zapise iz Redo Log.
Za nepotvrđene transakcije, MySQL će poništiti te izmene putem Undo Log, osigurava da se podaci vrate u konzistentno stanje pre pada.
Redo Log se koristi cirkularno, kada se datoteka popuni, prepisuje najstarije zapise.
Da bi se izbeglo prepisivanje nepersistiranih zapisa, MySQL će periodično izvršavati CheckPoint operaciju, osvežava stranice podataka iz memorije na disk i evidentira CheckPoint tačku.
Prilikom ponovnog pokretanja, MySQL će ponoviti samo Redo Log nakon CheckPoint, čime poboljšava efikasnost oporavka.
Da li je veličina redo log datoteke fiksna?
redo log datoteke su fiksne veličine, obično se konfigurišu kao grupa datoteka, koriste se kružni način pisanja, stari dnevnik će biti prepisan kada je potreban prostor.
Način imenovanja je ib_logfile0, ib_logfile1, ..., ib_logfilen. Podrazumevano 2 datoteke, svaka datoteka je veličine 48MB.
Možete videti veličinu redo log datoteke putem show variables like 'innodb_log_file_size';; videti broj redo log datoteka putem show variables like 'innodb_log_files_in_group';.
Recite nešto o WAL?
WAL——Write-Ahead Logging.
Pre-write dnevnik je ključni mehanizam InnoDB za implementaciju perzistencije transakcija, njegova ideja je: prvo napisati dnevnik, zatim upisati na disk.
To znači da se pre izmene stranice podataka prvo evidentira izmena u Redo Log.
Na ovaj način, čak i ako stranica podataka nije još uvek upisana na disk, prilikom pada sistema se podaci mogu oporaviti putem Redo Log.
----ova partija vam pomaže da razumete početak, ne morate učiti za intervju----
Objasnite zašto je potreban WAL:
- Podaci se na kraju moraju upisati na disk, ali je disk IO veoma spor;
- Ako se pri svakom ažuriranju odmah upiše stranica podataka na disk, performanse su loše;
- Ako se sistem sruši pre upisivanja na disk, transakcija će biti izgubljena.
Prednost WAL je ta što se pri ažuriranju ne piše direktno stranica podataka, već se prvo evidentira zapis izmene u redo log, a pozadinska nit polako upisuje prave stranice podataka na disk, dobija se više pogodnosti odjednom.
----ova partija vam pomaže da razumete kraj, ne morate učiti za intervju----
- Java vodič za intervju prikupljeno iskustvo kandidata Huawei 8 tehničko drugo intervju pitanje: koja je uloga bin log u MySQL-u?
- Java vodič za intervju prikupljeno iskustvo kandidata Meituan 2 Java back-end tehničko prvo intervju pitanje: recite nešto o tri glavna dnevnika MySQL-a?
- Java vodič za intervju prikupljeno iskstvo kandidata ByteDance 21 TikTok Mall prvo intervju pitanje: redolog undolog binlog, zašto su potrebni undolog i redolog pored binlog, radni mehanizam redolog, recite nešto o WAL
memo: 14. marta 2025. izmenjeno do ovde. Danas prilikom izmene životopisa, sreo sam jednog člana sa izuzetno bogatim iskustvom takmičenja, ako imate vreme tokom studija, možete i vi pokušati.
29. Koja je razlika između binlog i redo log?
binlog implementira sloj Server MySQL-a, nezavisan od motora za skladištenje; redo log implementira motor InnoDB.
binlog evidentira logičke dnevnik, uključuje originalne SQL izjave ili promene redova podataka, na primer “postavi age polje na +1 za red gde je id=2”.
redo log evidentira fizičke dnevnike, to jest konkretne izmene stranica podataka, na primer “izmeni podatke sa offset=0x40 na page_id=123 sa 18 na 26”.
binlog se piše dodavanjem, kada se datoteka popuni napravi se nova datoteka i nastavi se pisanje, ne prepisuje istorijske dnevnike, čuva se kompletn zapis operacija; redo log se piše cirkularno, prostor je fiksiran, kada se popuni prepisuje stare dnevnike, čuva samo dnevnike prljavih stranica koje nisu očišćene, perzistentirani podaci se brišu.
Dodatno, kako bi se osigurala konzistentnost oba dnevnika, InnoDB koristi strategiju dvofaznog potvrđivanja, redo log se kontinuirano piše tokom izvršenja transakcije i pre ulaska u stanje prepare pre potvrde transakcije; binlog se piše u završnoj fazi potvrde transakcije, nakon čega se redo log označava kao commit stanje.
Može se postići sinhronizacija podataka ili oporavak do navedenog vremenskog trenutka reproduciranjem binlog; redo log služi za osiguranje da čak i ako se sistem sruši nakon potvrde transakcije, podaci se i dalje mogu oporaviti reproduciranjem redo log.
- Java vodič za intervju prikupljeno intervju pitanje Meituan 2 tehničko intervju 2: redo log, bin log
30.🌟Zašto je potreban dvofazni commit?
Da bi se osigurala konzistentnost podataka u redo log i binlog, i sprečila inkonzistentnost glavno-replikacije i stanja transakcija.
Zašto 2PC može garantovati snažnu konzistentnost redo log i binlog?
Ako se MySQL sruši nakon predpisa redo log ali pre pisanja binlog. Nakon ponovnog pokretanja InnoDB će poništiti tu transakciju, jer redo log nije u stanju potvrde. I pošto u binlog nema upisanih podataka, replika takođe neće imati podatke te transakcije.
Ako se MySQL sruši nakon pisanja binlog ali pre potvrde redo log. Nakon ponovnog pokretanja InnoDB će potvrditi tu transakciju, jer je redo log u kompletnom prepare stanju. I pošto u binlog ima upisanih podataka, replika će takođe sinhronizovati podatke te transakcije.
Pseudokod je sledeći:
// Početak transakcije
begin;
// try
{
// Izvrši SQL
execute SQL;
// Upisi redo log i označi kao prepare
write redo log prepare xid;
// Upisi binlog
write binlog xid sql;
// Potvrdi redo log
commit redo log xid;
}
// catch
{
// Poništi redo log
innodb rollback redo log xid;
}
// Kraj transakcije
end;Da li razumete XID?
XID je jedinstveni identifikator u binlog koji se koristi za identifikaciju potvrde transakcije.
Prilikom potvrde transakcije, upisaće se XID_EVENT u binlog, što označava da je transakcija zaista završena.
Log_name | Pos | Event_type | Server_id | End_log_pos | Info
| mysql-bin.000003 | 2005 | Gtid | 1013307 | 2070 | SET @@SESSION.GTID_NEXT= 'f971d5f1-d450-11ec-9e7b-5254000a56df:11' |
| mysql-bin.000003 | 2070 | Query | 1013307 | 2142 | BEGIN |
| mysql-bin.000003 | 2142 | Table_map | 1013307 | 2187 | table_id: 109 (test.t1) |
| mysql-bin.000003 | 2187 | Write_rows | 1013307 | 2227 | table_id: 109 flags: STMT_END_F |
| mysql-bin.000003 | 2227 | Xid | 1013307 | 2258 | COMMIT /* xid=121 */Koristi se ne samo za procenu integriteta transakcija u glavno-replikaciji, već takođe ključnu ulogu u proveri konzistentnosti redo log i binlog prilikom oporavka od pada.
XID može pomoći MySQL-u da proceni koji redo log su već potvrđeni, a koji su nepotrđeni i zahtevaju poništavanje, ključni je deo mehanizma dvofaznog potvrđivanja.
memo: 16. marta 2025. izmenjeno do ovde.
31.🌟Da li razumete proces pisanja redo log?
InnoDB će prvo upisati Redo Log u Redo Log Buffer u memoriji, zatim sa određenom učestalošću očistiti u Redo Log File na disku.
Koje scenarije okidaču operaciju čišćenja redo log na disk?
Na primer, kada nema dovoljno prostora u Redo Log Buffer, prilikom potvrde transakcije, prilikom okidača Checkpoint, kada pozadinska nit periodično čisti na disk.
Međutim, čišćenje Redo Log Buffer na Redo Log File još uvek uključuje strategiju keša diska operativnog sistema, možda neće odmah očistiti na disk, već će sačekati određeno vreme pre čišćenja na disk.
Koliko poznajete parametar innodb_flush_log_at_trx_commit?
Parametar innodb_flush_log_at_trx_commit se koristi za kontrolu strategije čišćenja Redo Log prilikom potvrde transakcije, ukupno postoji tri vrste.
0 znači da se prilikom potvrde transakcije ne čisti na disk, već se pozadinskoj niti daje zadatak da se izvršava svake 1 sekunde. Ovaj način ima najbolje performanse, ali prilikom pada MySQL može se izgubiti transakcija u periodu od jedne sekunde.
1 znači da će se prilikom potvrde transakcije odmah očistiti na disk, osiguravajući da se podaci trajno sačuvaju na disku nakon potvrde transakcije. Ovaj način je najsigurniji, i podrazumevana vrednost InnoDB.
2 znači da se prilikom potvrde transakcije samo Redo Log Buffer upisuje u Page Cache, a operativni sistem odlučuje kada će očistiti na disk. Kada se operativni sistem sruši, mogu se izgubiti deo podataka.
Da li će redo log nepotvrđene transakcije biti očišćen na disk?
InnoDB ima pozadinsku nit, svake 1 sekunde će upisivati dnevnike iz Redo Log Buffer u keš fajlovskog sistema, zatim pozvati operaciju čišćenja na disk.
Dakle, Redo Log nepotvrđene transakcije takođe može biti očišćen na disk.
Dodatno, kada prostor koji zauzima Redo Log Buffer dostigne polovinu innodb_log_buffer_size, takođe će se okinuti operacija čišćenja na disk.
memo: 17. marta 2025. izmenjeno do ovde. Već je jedan član poslao dobaru vest, dobio je letnju praksu u Hengsheng Electronics.
Da li je Redo Log Buffer sekvencijalno ili nasumično pisanje?
MySQL nakon pokretanja će zatražiti od operativnog sistema blok kontinualne memorije kao Redo Log Buffer i podeliti ga na nekoliko kontinualnih Redo Log Block.
Kako bi se poboljšala efikasnost pisanja, Redo Log Buffer koristi sekvencijalno pisanje, prvo će pisati u prethodne Redo Log Block, kada se popuni onda će pisati u sledeći Block.
Istovremeno, InnoDB pruža globalnu promenljivu buf_free, za kontrolu pozicije u block na koju treba upisati sledeći redo log zapis.
Da li razumete buf_next_to_write?
buf_next_to_write pokazuje na početnu poziciju u Redo Log Buffer na koju sledeći put treba upisati na hard disk.
Dok buf_free pokazuje na početnu poziciju slobodne oblasti u Redo Log Buffer.
Da li razumete MTR?
Mini Transaction je atomična operaciona jedinica koju InnoDB interno koristi za operacije nad stranicama podataka.
mtr_t mtr;
mtr_start(&mtr);
// 1. Zaključavanje
// Zaključavanje index-a za pristup
mtr_s_lock(rw_lock_t, mtr);
mtr_x_lock(rw_lock_t, mtr);
// Zaključavanje page-a za čitanje i pisanje
mtr_memo_push(mtr, buf_block_t, MTR_MEMO_PAGE_S_FIX);
mtr_memo_push(mtr, buf_block_t, MTR_MEMO_PAGE_X_FIX);
// 2. Pristup ili izmena page-a
btr_cur_search_to_nth_level
btr_cur_optimistic_insert
// 3. Generisanje redo za izmene
mlog_open
mlog_write_initial_log_record_fast
mlog_close
// 4. Perzistentnost redo, otključavanje
mtr_commit(&mtr);Redo Log više transakcija će se naizmjenično upisivati u Redo Log Buffer u jedinicama MTR, ako transakcija 1 i transakcija 2 obe imaju po dva MTR, jednom kada se neki MTR završi, njemu generisani Redo Log zapisi će se sekvencijalno upisati u Redo Log Buffer.
To znači, jedan MTR će sadržati grupu Redo Log zapisa, najmanja izvršna jedinica za oporavak transakcije nakon pada MySQL.
Da li razumete strukturu Redo Log Block?
Redo Log Block se sastoji od zaglavlja dnevnika, tela dnevnika i repa dnevnika, ukupno zauzima 512 bajtova, gde zaglavlje dnevnika zauzima 12 bajtova, rep dnevnika zauzima 4 bajta, preostalih 496 bajtova se koristi za skladištenje tela dnevnika.
Zaglavlje dnevnika sadrži broj serije trenutnog Block, broj serije prvog dnevnika, tip i druge informacije.
| Polje | Funkcija |
|---|---|
| LOG_BLOCK_HDR_NO | Broj trenutnog Block, ako se Redo Log Buffer posmatra kao niz, onda LOG_BLOCK_HDR_NO odgovara indeksu Block u Buffer. |
| LOG_BLOCK_HDR_DATA_LEN | Broj korišćenih bajtova Block, početna vrednost je 12, to jest dužina zaglavlja dnevnika; ako se telo dnevnika popuni, vrednost raste na 512. |
| LOG_BLOCK_FIRST_REC_GROUP | Pomeraj početka prvog MTR u ovom Block |
| LOG_BLOCK_CHECKPOINT_NO | Checkpoint kada je Block poslednji put upisan |
Rep dnevnika uglavnom čuva LOG_BLOCK_CHECKSUM, to jest kontrolni sum Block, uglavnom se koristi za procenu da li je Block potpun.
Zašto je Redo Log Block dizajniran kao 512 bajta?
Pošto je fizička veličina sektora mehaničkog hard diska obično 512 bajtova, Redo Log Block je takođe dizajniran iste veličine, može se osigurati da je svako pisanje integer broj sektora, smanjujući troškove poravnanja.
Na primer, keš stranice operativnog sistema je podrazumevano 4KB, 8 Redo Log Block se može kombinovati u jednu jedinicu keša stranice, čime poboljšava efikasnost pisanja Redo Log Buffer.
memo: 18. marta 2025. izmenjeno do ovde.
Da li razumete LSN?
Log Sequence Number je 8-bajtni monoton rastući integer, koristi se za identifikaciju ukupnog broja bajtova koje transakcija upiše u redo log, postoji u redo log, zaglavlju stranice podataka i checkpoint.
----ova partija vam pomaže da razumete početak, ne morate učiti za intervju----
MySQL prilikom prvog pokretanja, početna vrednost LSN nije 0, već 8704; kada se MySQL ponovo pokrene, nastaviće da koristi LSN iz prethodnog zaustavljanja servisa.
Prilikom računanja inkrementa LSN, ne treba razmatrati samo veličinu log block body, već takođe deo bajtova u log block header i log block tail.
Na primer, na gornjoj slici, ukupan MTR transakcije 3 je 300 bajtova, onda će LSN upisani u Redo Log Buffer rasti na 8704 + 300 + 12 = 9016.
Ako je ukupan MTR transakcije 4 900 bajtova, onda će se LSN ponovo upisani u Redo Log Buffer povećati na 9016 + 900 + 122 + 42 = 9948.
2 log block headera od 12 bajtova + 2 log block taila od 4 bajta.
----ova partija vam pomaže da razumete kraj, ne morate učiti za intervju----
Ključne uloge su tri:
Prvo, redo log evidentira sve operacije izmene podataka u rastućem redosledu LSN. Inkrement LSN je jednak broju bajtova svakog upisa dnevnika.
Drugo, u zaglavlju svake stranice podataka InnoDB će biti evidentiran LSN kada je stranica poslednji put očišćena na disk. Ako je LSN stranice podataka manji od LSN redo log, to znači da stranica treba da se oporavi iz dnevnika; inače znači da je stranica ažurirana.
Treće, checkpoint kroz LSN evidentira poziciju stranica podataka koje su već očišćene na disk, smanjujući dnevnik koji treba da se obrađuje prilikom oporavka.
----ova partija vam pomaže da razumete početak, ne morate učiti za intervju----
| Scenario | Uloga LSN |
|---|---|
| 🔁 redo log zapis | Svaki redo log odgovara jedinstvenom LSN |
| 📄 Očišćenje stranice podataka | Svaka stranica podataka će evidentirati LSN trenutnog čišćenja (FIL_PAGE_LSN) |
| ⛳ Checkpoint | Predstavlja bezbednu tačku “prljave stranice su očišćene, može se osloboditi redo” |
| 💥 Oporavak od pada | Ponovno puštanje redo log od checkpoint LSN prilikom ponovnog pokretanja |
Može se pregledati trenutna LSN informacija putem show engine innodb status;.
- Log sequence number: trenutni maksimalni LSN sistema (ukupan količina generisanih dnevnika).
- Log flushed up to: redo log LSN upisan na disk.
- Pages flushed up to: LSN očišćen na stranice podataka.
- Last checkpoint at: LSN poslednjeg checkpoint, predstavlja stanje perzistentiranih podataka.
----ova partija vam pomaže da razumete kraj, ne morate učiti za intervju----
memo: 19. marta 2025. izmenjeno do ovde. Danas čitalac pita kako da plati papirnu verziju Mianzha-napred, kaže da je video kod članova ovu, stvarno mu zavidi. Iskreno, prvi put kad sam video ovaj omot, stvarno sam mislio da je impresivno (iako sam ga ja dizajnirao).😄
Šta znate o Checkpoint?
Checkpoint je mehanizam InnoDB za osiguravanje perzistencije transakcija i oslobađanje prostora redo log.
Njegova uloga je da u odgovarajućem trenutku očisti deo prljavih stranica na disk, na primer kada kapacitet buffer pool nije dovoljan. I evidentira trenutni LSN kao Checkpoint LSN, što znači da redo log file pre ove pozicije je siguran i može se prepisati.
Prilikom oporavka od pada MySQL, potrebno je samo ponoviti redo log nakon Checkpoint, čime se maksimalno smanjuje vreme oporavka.
Zapis u redo log datoteku je kružni, gde postoje dva veoma važna označena položaja: Checkpoint i write pos.
write pos je trenutna pozicija upisa u redo log, Checkpoint je pozicija koja može biti prebrisana.
Kada write opstigne Checkpoint, to znači da je redo log dnevnik pun. Tada treba privremeno zaustaviti upis i prisilitiFlush na disk, oslobađajući prostor za dnevnik koji se može prebrisati.
Šta znate o parametrima za podešavanje redo log-a?
Ako je to e-commerce sistem sa visokim konkurentnim upisima, možete maksimizovati propusnost upisa, podnosivši rizik gubitka podataka na nivou nekoliko sekundi.
innodb_flush_log_at_trx_commit = 2
sync_binlog = 1000
innodb_redo_log_capacity = 64G
innodb_io_capacity = 5000
innodb_lru_scan_depth = 512
innodb_log_buffer_size = 256MAko je to finansijski tranzicioni sistem, potrebno je garantovati nulti gubitak podataka, prihvatajući manju propusnost.
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
innodb_redo_log_capacity = 32G
innodb_io_capacity = 2000
innodb_lru_scan_depth = 1024Tabela ključnih parametara:
| Parametar | Kontrolni sadržaj | Uticajna tačka |
|---|---|---|
| innodb_log_file_size | Veličina svake redo log datoteke | Ukupni redo prostor, vreme oporavka |
| innodb_log_files_in_group | Broj redo log datoteka | U kombinaciji sa veličinom datoteke određuje ukupni kapacitet |
| innodb_log_buffer_size | Veličina redo log bafera | Da li se često flush-a na disk, performanse upisa |
| innodb_flush_log_at_trx_commit | Strategija flush redo log-a na disk | Sigurnost vs TPS |
| innodb_max_dirty_pages_pct | Prag odnos prljavih stranica | Kada se okida flush / Checkpoint |
| innodb_io_capacity | Brzina flush-a u pozadini | Ograničava pritisak checkpoint flush-a |
Sažetak:
- Za scenarije sa visokim zahtevima za konzistentnost podataka, kao što su finansijske transakcije, koristite
innodb_flush_log_at_trx_commit=1; za scenarije osetljive na propusnost upisa, kao što je prikupljanje logova, možete koristiti =2 ili =0, uzimajući u obzir parametar sync_binlog - Parametar sync_binlog kontroliše strategiju flush binlog-a na disk, može se postaviti na 0, 1, N; 0 znači oslanjanje na sistemski flush, 1 znači flush pri svakoj potvrdi transakcije (preporučeno u kombinaciji sa
innodb_flush_log_at_trx_commit=1), N=1000 znači flush nakon akumulacije 1000 transakcija - innodb_redo_log_capacity dinamički podešava ukapni kapacitet Redo Log-a, može se prilagoditi prema opterećenju poslovnog sistema, preporučuje se postaviti na vrhunac jednog časa upisa (npr. ako je upis 10MB po sekundi, postaviti na 36GB)
- innodb_io_capacity definiše gornju granicu I/O operacija po sekundi pozadinskih niti InnoDB, direktno utiče na brzinu osvežavanja prljavih stranica; za mehaničke diskove preporučuje se 200-500, za SSD 1000-2000, za NVMe SSD može se postaviti na 5000+
- innodb_lru_scan_depth kontroliše dubinu skeniranja LRU liste u svakoj instanci bafera, određuje broj prljavih stranica koje se mogu osvežiti po sekundi, podrazumevana vrednost 1024 je pogodna za većinu scenarija, za I/O intenzivna opterećenja se može smanjiti (npr. 512) kako bi se smanjila potrošnja CPU-a.
Šta je spor SQL?
Preporučeno čitanje: Malo ideja za optimizaciju sporog SQL
MySQL ima parametar long_query_time, u principu SQL čije vreme izvršenja prevazilazi ovu vrednost smatra se sporim SQL, evidentira se u dnevnik sporih upita.
----pomaže razumevanju start, ne morate učiti za intervju----
Možete videti trenutnu vrednost long_query_time parametra sa show variables like 'long_query_time';.
----pomaže razumevanju end, ne morate učiti za intervju----
Razumete li proces izvršenja SQL?
Razumem.
Proces izvršenja SQL može se podeliti u šest faza: upravljanje konekcijom, parsiranje sintakse, semantička analiza, optimizacija upita, raspoređivanje izvršioca, čitanje/pisanje motora. Sloj usluga je odgovoran za razumevanje i planiranje kako će se SQL izvršiti, sloj motora je odgovoran za stvarno čitanje i pisanje podataka.
----ova partija vam pomaže da razumete početak, ne morate učiti za intervju----
Da detaljno razložimo:
- Klijent šalje SQL izjavu MySQL serveru.
- Ako je keš upita uključen, prvo se proverava keš, ako keš sadrži odgovarajući rezultat, vraća se direktno. Međutim, MySQL 8.0 je uklonio keš upita. Ova funkcija se zamenjuje Redis-om i sličnim keš posrednicima.
- Analizator vrši sintaksičku analizu SQL izjave, procenjujući da li ima sintaksičkih grešaka.
- Kada se razume šta SQL izjava treba da uradi, MySQL će kroz optimizator generisati plan izvršenja.
- Izvršilac poziva interfejs pogona skladištenja, izvršavajući SQL izjavu.
U procesu izvršenja SQL, optimizator kroz proračun troškova procenjuje način najviše efikasnosti, osnovne dimenzije procene su:
- IO trošak: trošak učitavanja podataka sa diska u memoriju.
- CPU trošak: trošak CPU obrade podataka u memoriji.
Na osnovu ove dve dimenzije, može se zaključiti da faktori koji utiču na efikasnost izvršenja SQL uključuju:
①,IO trošak, što je veća količina podataka, veći je IO trošak. Zato treba šta je više moguće upitavati neophodna polja; šta je više moguće koristiti straničenje upita; šta je više moguće ubrzati upite kroz indekse.
②,CPU trošak, što je više moguće izbegavati složene uslove upita, ako je neophodno, razmotriti filtriranje rezultata podupita.
----ova partija vam pomaže da razumete kraj, ne morate učiti za intervju----
Kako optimizovati spor SQL?
Prvo, treba pronaći one sporije SQL upite, može se to učiniti uključivanjem dnevnika sporih upita, beležeći one SQL upite koji prevazilaze određeno vreme izvršenja.
Takođe možete koristiti komandu show processlist; da vidite SQL izjave koje se trenutno izvršavaju, pronalazeći one sa dužim vremenom izvršenja.
Ili dodati monitoring sporih SQL u poslovnu infrastrukturu, uobičajena rešenja uključuju bytecode instrumentaciju, proširenje bafera konekcije, proširenje ORM okvira itd.
Zatim, koristite EXPLAIN da vidite plan izvršenja sporog SQL, proverite da li se koristi indeks, u većini slučajeva uzrok sporog SQL-a je što se ne koristi indeks.
EXPLAIN SELECT * FROM your_table WHERE conditions;Konačno, na osnovu rezultata analize, izvršite optimizaciju dodavanjem indeksa, optimizacijom uslova upita, smanjenjem polja za povrat itd.
Kako uključiti dnevnik sporih SQL?
Uredite konfiguracionu datoteku MySQL my.cnf, postavite parametar slow_query_log na 1.
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2 # beleži upite čije vreme izvršenja premašuje 2 sekundeZatum ponovo pokrenite MySQL i to je to.
Takođe možete dinamički podesiti kroz set global komandu.
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 2;
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje prvog intervjua kandidata 16 iz Tencent Cloud Smart: scenarijsko pitanje: SQL upit je vrlo spor, kako istražiti
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje intervjua kandidata 5 iz Kuaishou: kako uključiti dnevnik sporih sql?
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje prvog tehničkog intervjua kandidata 3 iz Meituan: kako proceniti efikasnost sql, kako istražiti sql sa manjom efikasnošću
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje prvog pozadinskog tehničkog intervjua kandidata 1 iz Zuoyebang: kako u mysql-u locirati spore upite
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje prvog pozadinskog tehničkog intervjua kandidata 1 iz Beike: kako analizirati spore upite
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje prvog tehničkog intervjua kandidata 27 iz Tencent Cloud: kako optimizovati izjavu sporog upita?
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje prvog intervjua kandidata 13 iz Shopee: mysql spori upiti
33.🌟Koje metode znate za optimizaciju SQL?
Metodi za SQL optimizaciju su vrlo brojni, ali suštinski je u jednoj rečenici: što je manje moguće skenirati, što je brže moguće vratiti rezultate.
Najuobičajeniji pristup je dodati indekse, preurediti SQL tako da koristi indekse, na primer korišćenje pokretnih indeksa, obezbedjivanje da združeni indeksi poštuju princip najlevijeg prefiksa itd.
Kako iskoristiti pokretni indeks?
Suština pokretnog indeksa je "sva polja potrebna za upit su u istom indeksu", tako da MySQL ne mora da se vraća na tabelu, direktno vraća rezultate iz indeksa.
U stvarnoj upotrebi, prvo ću razmatrati stvaranje združenog indeksa za polja uključena u WHERE i SELECT, i kroz EXPLAIN posmatrati da li rezultat sadrži Using index, potvrđujući da je indeks pogoden.
----ova partija vam pomaže da razumete početak, ne morate učiti za intervju----
Na primer, sada treba iz test tabele upitati polje name gde je city Shanghai.
select name from test where city='Shangaj'Ako se doda indeks samo na polje city, tada će ovaj upit prvo kroz indeks pronaći redove gde je city Shanghai, a zatum se vratiti na tabelu da upita polje name.
Da bi se izbeglo vraćanje na tabelu, možete napraviti združeni indeks na polja city i name, tako da se rezultati upita mogu dobiti direktno iz indeksa.
alter table test add index index1(city,name);----ova partija vam pomaže da razumete kraj, ne morate učiti za intervju----
Kao pravilno koristiti združene indekse?
Najvažnije pravilo korišćenja združenih indeksa je poštovati princip najlevijeg prefiksa, tj. uslovi upita moraju početi od levog polja indeksa.
----ova partija vam pomaže da razumete početak, ne morate učiti za intervju----
Na primer, kreirali smo združeni indeks sa tri kolone.
CREATE INDEX idx_name_age_sex ON user(name, age, sex);Da vidimo koje uslove upita može koristiti ovaj indeks:
| Uslov upita | Da li može koristiti idx_name_age_sex? | Objašnjenje |
|---|---|---|
| WHERE name = 'itwanger' | ✅ Može | Poklapa prvu kolonu, pogodak indeksa |
| WHERE name = 'itwanger' AND age=20 | ✅ Može | Poklapa prve dve kolone, pogodak indeksa |
| WHERE age = 20 | ❌ Ne | Prva kolona nije korišćena, indeks nevažeći |
| WHERE name='itwanger' AND sex='ženski' | ✅ Delimično moguće (koristi samo prvu kolonu) | age je preskočen, kasnije kolone se ne mogu koristiti |
| WHERE name LIKE 'it%' | ✅ Može (poklapanje prefiksa) | name je poklapanje prefiksa, ne utiče na korišćenje |
| WHERE name LIKE '%wanger%' | ❌ Ne | Džoker je na početku, ne može koristiti indeks |
----ova partija vam pomaže da razumete kraj, ne morate učiti za intervju----
Kako izvršiti optimizaciju straničenja?
Suština optimizacije straničenja je izbegavati skeniranje cele tabele usled dubokog pomeraja, može se optimizovati na dva načina: odloženo povezivanje i dodavanje obeleživača.
Odloženo povezivanje je pogodno za situacije gde je potrebno dobiti podatke iz više tabela i gde glavna tabela ima više redova. Prvo pronalazi potrebne ID redove iz indeksne tabele, a zatum na osnovu tih ID povezuje ostale tabele za dobijanje detaljnih informacija.
SELECT e.id, e.name, d.details
FROM employees e
JOIN department d ON e.department_id = d.id
ORDER BY e.id
LIMIT 1000, 20;Nakon odloženog povezivanja, prvi korup upituje samo primarni ključ, brz je, drugi korup obradjuje samo 20 podataka, visoka efikasnost.
SELECT e.id, e.name, d.details
FROM (
SELECT id
FROM employees
ORDER BY id
LIMIT 1000, 20
) AS sub
JOIN employees e ON sub.id = e.id
JOIN department d ON e.department_id = d.id;Način dodavanja obeleživača se ostvarju pamćenjem vrednosti primarnog ključa poslednjeg reda vraćenog prethodnim upitom, a zatim u sledećem upitu počinje od te vrednosti, čime se preskače izračunavanje pomeraja, skenira se samo ciljni podatak, pogodno za listanje, tok informacija itd.
Pretpostavimo da treba vršiti straničenje korisničke tabele.
SELECT id, name
FROM users
ORDER BY id
LIMIT 1000, 20;Nakon optimizacije dodavanjem obeleživača, upit više ne koristi OFFSET, već počinje upit od ID poslednjeg korisnika sa prethodne stranice. Ovaj metod može efikasno izbeći nepotrebno skeniranje podataka, poboljšavajući efikasnost upita straničenja.
SELECT id, name
FROM users
WHERE id > last_max_id -- pretpostavljajući da je last_max_id ID poslednjeg reda prethodne stranice
ORDER BY id
LIMIT 20;Zašto straničenje postaje sporo?
Problem efikasnosti upita straničenja uglavnom potiče od postojanja OFFSET, OFFSET će primorati MySQL da skenira i preskoči offset + limit redova podataka, ovaj proces je vrlo vremenski intenzivan.
Na primer, ako treba upitati 100000-ti podatak, tada MySQL mora skenirati 100000 podataka, a zatum vratiti 10 podataka.
SELECT * FROM user ORDER BY id LIMIT 100000, 10;Što je više podataka, što je veći pomeraj, to je sporije!
Šta koristi JOIN umesto podupita?
Prvo, ON uslov JOIN-a može direktnije aktivirati indeks, dok podupit može usled ugnježdenja dovesti do nevažećeg indeksa.
Drugo, jedna operacija povezivanja JOIN-a zamenjuje višestruko izvršenje podupita, posebno kod velikih količina podataka razlika u performansama je očigledna.
----ova partija vam pomaže da razumete početak, ne morate učiti za intervju----
Na primer, imamo dve tabele orders i customers.
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
amount DECIMAL(10,2),
INDEX idx_customer_id (customer_id) -- polje customer_id ima indeks
);
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(100)
);Način pisanja podupita:
SELECT o.order_id, o.amount,
(SELECT c.name
FROM customers c
WHERE c.customer_id = o.customer_id) AS customer_name
FROM orders o;Način pisanja JOIN-a:
SELECT o.order_id, o.amount, c.name AS customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;| Poredna stavka | Podupit | JOIN |
|---|---|---|
| Korišćenje indeksa | Unutrašnji podupit WHERE c.customer_id = o.customer_id pri svakom izvršenju možda ne može direktno iskoristiti indeks customer_id tabele orders. | ON uslov JOIN o.customer_id = c.customer_id može direktno iskoristiti indeks idx_customer_id tabele orders, ubrzavajući proces povezivanja. |
| Plan izvršenja | Podupit će se ponavljati izvršavati (svaki red orders tabele će okinuti jedan podupit), dovodeći do skeniranja cele tabele. | Optimizator može izabrati brzo povezivanje dve tabele kroz indeks, smanjujući količinu skeniranja podataka. Na primer, prvo pronalazi customer_id kroz indeks orders, zatim se brzo poklapa sa primarnim ključem customers. |
| Performanse | Kada tabela orders ima veliku količinu podataka, podupit može usled ponavljanja dovesti do drastičnog pada performansi. | Jedna operacija povezivanja JOIN je obično efikasnija, posebno kod velikih količina podataka. |
Za podupit, proces izvršenja je takav:
- Svaki red spoljne tabele orders će okinuti jedan podupit.
- Ako tabela orders ima 1000 zapisa, podupit će se izvršiti 1000 puta.
- Svaki podupit treba zasebno upitati tabelu customers (čak i kada je customer_id isti).
Dok je proces izvršenja JOIN-a takav:
- Optimizer baze podataka će spojiti operaciju povezivanja dve tabele u jedno izvršenje.
- Kroz indeks (kao što su orders.customer_id i customers.customer_id) brzo povezuje podatke.
- Izvršava se samo jedna operacija povezivanja, a ne višestruki podupiti.
Da pogledamo plan izvršenja podupita:
EXPLAIN SELECT o.order_id,
(SELECT c.name FROM customers c WHERE c.customer_id = o.customer_id)
FROM orders o;
Tip podupita (DEPENDENT SUBQUERY) ukazuje da zavisi od svakog reda spoljnjeg upita, dovodeći do ponavljanja izvršenja.
Zatum uporedimo pogledajmo plan izvršenja JOIN-a:
EXPLAIN SELECT o.order_id,
(SELECT c.name FROM customers c WHERE c.customer_id = o.customer_id)
FROM orders o;
JOIN kroz tip eq_ref direktno koristi primarni ključ (customers.customer_id) za brzo povezivanje, smanjujući broj skeniranja.
----ova partija vam pomaže da razumete kraj, ne morate učiti za intervju----
Zašto JOIN operaciju treba da vodi mala tabela?
Prvo, ako JOIN polje velike tabele ima indeks, tada svaki red male tabele može kroz indeks brzo poklopiti veliku tabelu.
Vremenska kompleksnost je broj redova male tabele N pomnožen sa kompleksnošću pretrage indeksa velike tabele log(broj redova velike tabele M), ukupna kompleksnost je N*log(M).
Očigledno je da je vremenska kompleksnost kada mala tabla služi kao pogonska tabela M*log(N) manja nego kada velika tabela služi kao pogonska tabela.
Drugo, ako velika tabela nema indeks, treba učitati podatke male tabele u memoriju, zatum skenirati celu veliku tabelu za poklapanje.
Vremenska kompleksnost je broj segmenata male tabele K pomnožen brojem redova velike tabele M, gde je K = broj redova male tabele N / veličina memorije join_buffer_size.
Očigledno je da je vrednost K manja kada mala tabela služi kao pogonska tabela, dok kada velika tabela služi kao pogonska tabela treba višestruko segmentiranje.
-- mala tabela pogoni (efikasno)
SELECT * FROM small_table s
JOIN large_table l ON s.id = l.id; -- l.id ima indeks
-- velika tabela pogoni (neefikasno)
SELECT * FROM large_table l
JOIN small_table s ON l.id = s.id; -- s.id nema indeks- Kod korišćenja left join, leva tabela je pogonska tabela, desna tabela je vođena tabela.
- Kod korišćenja right join, suprotno.
- Kod korišćenja join, MySQL će izabrati tabelu sa manjom količinom podataka kao pogonsku tabelu, veliku tabelu kao vođenu tabelu.
----ova partija vam pomaže da razumete početak, ne morate učiti za intervju----
Da bih potvrdio ovu tačku, posebno sam kreirao dve tabele departments i employees.
Ubaci testne podatke:
-- ubaci testne podatke
INSERT INTO departments VALUES
(1, 'Odeljenje za istraživanje i razvoj'),
(2, 'Marketing odeljenje'),
(3, 'Odeljenje za ljudske resurse');
-- ubaci više podataka u tabelu zaposlenih
INSERT INTO employees VALUES
(1, 'Zhang San', 1),
(2, 'Li Si', 1),
(3, 'Wang Er', 2),
(4, 'Zhao Liu', 2),
(5, 'Qian Qi', 3),
(6, 'Sun Ba', NULL),
(7, 'Zhou Jiu', 1),
(8, 'Wu Shi', 2);Zatum koristite explain da vidite plan izvršenja:
Kada se koristi left join, prvi red je tabela employees, što ukazuje da je leva tabela pogonska; kada se koristi right join, prvi red je tabela departments, što ukazuje da je desna tabela pogonska; kada se koristi join, prvi red je tabela departments, što ukazuje da je MySQL podrazumevano izabrao malu tabelu kao pogonsku.
----ova partija vam pomaže da razumete kraj, ne morate učiti za intervju----
Ovde se malom tabelom smatra stvarna količina podataka koja učestvuje u JOIN-u, a ne ukupan broj redova tabele. Velika tabela nakon filtriranja WHERE uslovom takođe može postati logički mala tabela.
-- količina podataka koja stvarno učestvuje u JOIN-u određuje malu tabelu
SELECT * FROM large_table l
JOIN small_table s ON l.id = s.id
WHERE l.created_at > '2025-01-01'; -- l nakon filtriranja može postati mala tabelaTakođe možete forsirati kroz STRAIGHT_JOIN sugestisati MySQL-u da koristi određenu pogonsku tabelu.
explain select table_1.col1, table_2.col2, table_3.col2
from table_1
straight_join table_2 on table_1.col1=table_2.col1
straight_join table_3 on table_1.col1 = table_3.col1;
explain select straight_join table_1.col1, table_2.col2, table_3.col2
from table_1
join table_2 on table_1.col1=table_2.col1
join table_3 on table_1.col1 = table_3.col1;Zašto treba izbegavati korišćenje JOIN za povezivanje previše tabela?
Prvo, putanja izvršenja višestrukog JOIN-a rastće eksponencijalno sa brojem tabela, optimizer treba proceniti troškove svih puteva, što može dovesti do situacije da velika tabela vodi malu tabelu.
SELECT * FROM A
JOIN B ON A.id = B.a_id
JOIN C ON B.id = C.b_id
JOIN D ON C.id = D.c_id
JOIN E ON D.id = E.d_id; -- 5 tabela, optimizer treba proceniti 5! = 120 redosledaDrugo, višestruki JOIN treba da kešira međurezultate, može prekoračiti join_buffer_size, u ovom slučaju privremena tabela u memoriji će preći na privremenu tabelu na disku, performanse će takođe drastično pasti.
U"Alibaba Java razvojni priručnik"je propisano da ne treba koristiti join za povezivanje previše tabela, najviše ne preko 3 tabele.
Kako izvršiti optimizaciju sortiranja?
Prvo, kreirajte indeks na polja uključena u ORDER BY, izbegavajte filesort.
-- pre optimizacije (može okinuti filesort)
SELECT * FROM users ORDER BY age DESC;
-- nakon optimizacije (dodaj indeks)
ALTER TABLE users ADD INDEX idx_age (age);Ako je više polja, združeni indeks treba osigurati da su kolone ORDER BY najlevi prefiks indeksa.
-- združeni indeks treba da bude saglasan sa redosledom ORDER BY (age prvo, name drugo)
ALTER TABLE users ADD INDEX idx_age_name (age, name);
-- efikasno iskorišćenje indeksa upita
SELECT * FROM users ORDER BY age, name;
-- neefikasan slučaj (indeks nevažeći, jer je name u indeksu iza age)
SELECT * FROM users ORDER BY name, age;Drugo, možete prilagoditi parametre sortiranja, kao što je povećanje sort_buffer_size, max_length_for_sort_data itd., da se sortiranje obavi u memoriji.
----ova partija vam pomaže da razumete početak, ne morate učiti za intervju----
- sort_buffer_size: koristi se za kontrolu veličine bafera sortiranja, podrazumevana je 256KB. To znači, ako je količina podataka za sortiranje manja od 256KB, MySQL će direktno sortirati u memoriji; inače treba raditi filesort na disku.
- max_length_for_sort_data: maksimalna dužina jednog reda podataka, utiče na izbor algoritma sortiranja. Ako jedan red podataka premašuje ovu vrednost, MySQL će koristiti dvostruko sortiranje, inače jednostruko sortiranje.
- max_sort_length: ograničava dužinu prefiksa pri poređenju stringova pri sortiranju. Kada MySQL mora da sortira TEXT, BLOB polja, odseca prvih max_sort_length znakova za poređenje.
----ova partija vam pomaže da razumete kraj, ne morate učiti za intervju----
Treće, možete kroz WHERE i LIMIT ograničiti količinu podataka za sortiranje, smanjujući trošak sortiranja.
-- pre optimizacije
SELECT * FROM users ORDER BY age LIMIT 100;
-- nakon optimizacije (smanjenje prenosa podataka i troška sortiranja)
SELECT id, name, age FROM users ORDER BY age LIMIT 100;
-- optimizacija dubokog straničenja (izbegavajte OFFSET skeniranje cele tabele)
SELECT * FROM users ORDER BY age LIMIT 10000, 20; -- neefikasno
SELECT * FROM users WHERE age > last_age ORDER BY age LIMIT 20; -- efikasno (zapamti age vrednost poslednjeg reda prethodne stranice)Šta je filesort?
Preporučeno čitanje: Kako MySQL izvršava ORDER BY
Kada ne može koristiti indeks za generisanje sortiranog rezultata, MySQL treba sam da sortira, ako je količina podataka manja, obaviće se u memoriji; ako je količina podataka veća, treba pisati privremenu datoteku na disk pa sortirati, ovaj proces nazivamo datotečno sortiranje.
----ova partija vam pomaže da razumete početak, ne morate učiti za intervju----
Dobro, da potvrdimo situaciju filesort, kreirajte tabelu, ubacite podatke.
Izvršite explain da vidite plan izvršenja.
Može se videti, kada se koristi order by id tj. primarni ključ, nije okinut filesort; kada se koristi order by age, jer nema indeksa, okinuo je filesort.
----ova partija vam pomaže da razumete kraj, ne morate učiti za intervju----
Šta znate o sortiranju svih polja i sortiranju rowid?
Kada je polje sortiranja indeksno polje i zadovoljava princip najlevijeg prefiksa, MySQL može direktno iskoristiti uređenost indeksa za obavljanje sortiranja.
Kada ne može koristiti indeksno sortiranje, MySQL treba da obavi operaciju sortiranja u memoriji ili na disku, dele se na dva algoritma: sortiranje svih polja i sortiranje rowid.
Sortiranje svih polja odjednom učitava sva polja redova koji zadovoljavaju uslove, zatim sortira u sort baferu, nakon sortiranja direktno vraća rezultate, bez potrebe za vraćanjem na tabelu.
Uz primer SELECT * FROM user WHERE name = "Wang Er" ORDER BY age:
- Iz name indeksa pronađi prvi primarni ključ id koji zadovoljava
name='Zhang San'; - Na osnovu primarnog ključa id učitaj sva polja celog reda, smesti u sort buffer;
- Ponovi gore navedeni proces dok se ne obrade svi redovi koji zadovoljavaju uslove
- Sortiraj podatke u sort bufferu po age, vrati rezultat.
Prednost je što je potreban samo jedan disk IO, mana je velika zauzetost memorije, ako količina premašuje sort buffer, treba segmentirano čitanje i pomoću privremenih datoteka spajanje sortiranja, broj IO operacija će se povećati.
Takođe ne može obraditi polja tipa TEXT i BLOB.
Sortiranje rowid deli se u dve faze:
- Prva faza: prema uslovima upita učitaj polja sortiranja i ID primarnog ključa, smesti u sort buffer za sortiranje;
- Druga faza: na osnovu sortiranog ID primarnog ključa vrati se na tabelu za učitavanje ostalih potrebnih polja.
Takođe uz primer SELECT * FROM user WHERE name = "Wang Er" ORDER BY age:
- Iz name indeksa pronađi prvi primarni ključ id koji zadovoljava
name='Zhang San'; - Na osnovu primarnog ključa id učitaj polje sortiranja age, zajedno sa ID primarnog ključa smesti u sort buffer;
- Ponovi gore navedeni proces dok se ne obrade svi redovi koji zadovoljavaju uslove
- Sortiraj podatke u sort bufferu po age;
- Prođi kroz sortirani ID primarnog ključa, vrati se na tabelu za učitavanje ostalih potrebnih polja, vrati rezultat.
Prednost je manja zauzetost memorije, pogodno za scenarije sa više polja ili velikim količinama podataka, mana je što su potrebna dva diska IO.
MySQL će na osnovu sistemskih promenljivih max_length_for_sort_data i ukupne veličine polja upita odlučiti da li koristiti sortiranje svih polja ili sortiranje rowid.
Ako je ukupna dužina polja upita <= max_length_for_sort_data, MySQL će koristiti sortiranje svih polja; inače će koristiti sortiranje rowid.
Šta znate o parametru Sort_merge_passes?
Preporučeno čitanje: Duboko razumevanje MySQL Order By datotečnog sortiranja
Sort_merge_passes je statusna promenljiva, koristi se za statistiku broja spajanja sortiranja koja MySQL izvrši prilikom operacija sortiranja.
Kada MySQL treba da sortira ali se podaci za sortiranje ne mogu u potpunosti smestiti u memorijski bafer definisan sa sort_buffer_size, koristiće privremene datoteke za spoljašnje sortiranje, tada će se proizvesti Sort_merge_passes.
Ako se Sort_merge_passes kratko vreme brzo poveća, to ukazuje da su podaci za operaciju sortiranja veliki, treba podesiti sort_buffer_size ili optimizovati izjavu upita.
MySQL prilikom izvršenja operacija sortiranja proći će kroz dva procesa:
- Faza sortiranja u memoriji, MySQL prvo pokušava da sortira u sort buffer-u. Ako je količina podataka manja od veličine bafera sort_buffer_size, potpuno će se obaviti u memoriji brzo sortiranje.
- Faza spoljašnjeg sortiranja, ako količina podataka premašuje sort_buffer_size, MySQL će podeliti podatke u više blokova, svaki blok se sortira zasebno i upisuje u privremenu datoteku, zatim vrši spajanje sortiranja ovih sortiranih blokova. Svaka operacija spajanja će povećati brojač Sort_merge_passes.
Koliko razumete sve uslove nametanje?
Suština nametanja uslova je da se spoljni filteri uslovi, kao što su WHERE, JOIN itd., što je više moguće spuste na niži nivo plana upita, na primer pre podupita, operacija povezivanja, čime se smanjuje količina međurezultata.
Na primer, originalni upit je:
SELECT * FROM (
SELECT * FROM orders WHERE total > 100
) AS subquery
WHERE subquery.status = 'shipped';Može se nametnuti uslov do podupita:
SELECT * FROM (
SELECT * FROM orders WHERE total > 100 AND status = 'shipped'
) AS subquery;Time se može smanjiti količina podataka vraćenih upitom, izbegavajući spoljašnju filtraciju.
Još jedan primer, originalni upit u UNION-u je:
(SELECT * FROM t1)
UNION ALL
(SELECT * FROM t2)
ORDER BY col LIMIT 10;Može se nametnuti uslov do svakog podupita:
(SELECT * FROM t1 ORDER BY col LIMIT 10)
UNION ALL
(SELECT * FROM t2 ORDER BY col LIMIT 10);Svaki podupit vraća samo prvih 10 podataka, smanjujući količinu podataka privremene tabele.
Još jedan primer, originalni upit u JOIN povezivanju upita je:
SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE customers.country = 'china';Može se nametnuti uslov pri skeniranju tabele:
SELECT * FROM orders
JOIN (
SELECT * FROM customers WHERE country = 'china'
) AS filtered_customers
ON orders.customer_id = filtered_customers.id;Prvo filtriraj tabelu customers, smanji količinu podataka pri JOIN-u.
Zašto treba izbegavati korišćenje select *?
SELECT * će primorati MySQL da čita sve podatke polja u tabeli, uključujući one koje aplikacija možda ne zahteva, na primer polja velikih tipova kao što su TEXT, BLOB.
Učitavanje suvišnih podataka će zauzeti više prostora u kešu, time istiskujući druge važne podatke iz keš resursa, smanjujući ukupnu propusnost sistema.
Takođe će povećati trošak mrežnog prenosa, posebno kod velikih polja.
Najvažnije je, SELECT * može dovesti do nevažećeg pokretnog indeksa, upit koji je mogao koristiti indeks na kraju postaje skeniranje cele tabele.
-- korišćenje pokretnog indeksa (pretpostavimo da je indeks idx_country)
SELECT id, country FROM users WHERE country = 'china'; -- možda samo skenira indeks
-- korišćenje SELECT *
SELECT * FROM users WHERE country = 'china'; -- treba vratiti se na tabelu da učita sve koloneKoje još metode SQL optimizacije znate?
①,Izbegavajte korišćenje != ili <> operatora
!= ili <> operatori će dovesti do toga da MySQL ne može koristiti indeks, čime se izaziva skeniranje cele tabele.
Možete column<>'aaa' promeniti u column>'aaa' or column<'aaa'.
②,Koristite indeks prefiksa
Na primer, sufiks email adrese je obično fiksan @xxx.com, tada polja čiji je zadnji deo fiksne vrednosti kao što su ova vrlo pogodna za definisanje kao indeks prefiksa:
alter table test add index index2(email(6));Treba napomenuti, MySQL ne može iskoristiti indeks prefiksa za order by i group by operacije.
③,Izbegavajte korišćenje funkcija na kolonama
Direktno korišćenje funkcija na kolonama u WHERE klauzuli će dovesti do nevažećeg indeksa, jer MySQL treba da primeni funkciju na svaki red kolone pre poređenja.
select name from test where date_format(create_time,'%Y-%m-%d')='2021-01-01';Može se promeniti u:
select name from test where create_time>='2021-01-01 00:00:00' and create_time<'2021-01-02 00:00:00';Kroz opseg datuma za upit, a ne korišćenje funkcija na kolonama, može se iskoristiti indeks na create_time.
34.🌟Da li ste često koristili explain?
Često koristim, explain je alat koji MySQL pruža za pregled plana izvršenja SQL, može nam pomoći da analiziramo probleme performansi upita.
Ukupno ima oko 10 izlaznih parametara.
Na primer type=ALL,key=NULL ukazuje da SQL vrši skeniranje cele tabele, možete razmotriti dodavanje indeksa na WHERE polje za optimizaciju; Extra=Using filesort ukazuje da SQL vrši datotečno sortiranje, možete razmotriti dodavanje indeksa na ORDER BY polje.
Način korišćenja je takođe vrlo jednostavan, direktno dodajte ključnu reč explain ispred select.
explain select * from students where name='Wang Er';Napredniji način korišćenja može se kombinovati sa parametrom format=json, vraćajući rezultate explain u JSON formatu.
explain format=json select * from students where name='Wang Er';
Razumete li značenje uobičajenih polja u rezultatima explain?
U rezultatima EXPLAIN najviše obraćam pažnju na polja type, key, rows i Extra.
Kroz njih ću proceniti da li SQL koristi indeks, da li vrši skeniranje cele tabele, da li je procenjenjeni broj skeniranih redova prevelik, i da li je okinut filesort ili privremena tabela. Jednom kada se otkrije problem, na primer type=ALL ili Extra=Using filesort, razmotriću kreiranje indeksa, preuređivanje SQL ili kontrolu skupa rezultata upita za optimizaciju.
----ova partija vam pomaže da razumete početak, ne morate učiti za intervju----
Uz primer izlaza EXPLAIN SELECT * FROM orders WHERE user_id = 100:
| Polje | Vrednost | Značenje i smernice optimizacije |
|---|---|---|
| id | 1 | Broj redosleda izvršenja upita. |
| select_type | SIMPLE | Jednostavan upit (bez podupita ili UNION). U složenim scenarijima postoje PRIMARY, SUBQUERY, DERIVED itd. |
| table | orders | Naziv tabele koju trenutni korak obrađuje. |
| partitions | NULL | Uključene particije. |
| type | ref | Tip pristupa: ključni pokazatelj performansi, uobičajeni tipovi: - system/const: poklapanje jedinstvene vrednosti (najbolje performanse) - eq_ref: povezivanje primarnog ključa/jedinstvenog indeksa - ref: poklapanje ne-jedinstvenog indeksa - range: skeniranje opsega indeksa - index: skeniranje celog indeksa - ALL: skeniranje cele tabele (potrebna optimizacija) |
| possible_keys | idx_user_id | Mogući indeksi za korišćenje. Ako je prazno, nema odgovarajućeg indeksa. |
| key | idx_user_id | Stvarno izabrani indeks. Ako je NULL, nije korišćen indeks. |
| key_len | 4 | Broj bajtova korišćenih od strane indeksa, može se proceniti da li se koristi ceo indeks. Na primer, združeni indeks (a,b), ako je key_len=4 možda je korišćena samo kolona a. |
| ref | const | Kolona ili konstanta koja se poređuje sa indeksom (kao što je 100 u WHERE user_id=100). |
| rows | 50 | Procejenjeni broj skeniranih redova. Što je manja vrednost bolje, ako se razlikuje od stvarne, možda su statistički podaci zastareli (potrebno ANALYZE TABLE). |
| filtered | 100.00 | Procenat preostalih redova nakon filtriranja uslovom upita. Na primer rows=1000 i filtered=10%, konačno se vraća oko 100 redova. |
| Extra | Using where | Dodatne informacije: - Using index: pokretni indeks (nema potrebe za vraćanjem na tabelu) - Using temporary: korišćenje privremene tabele - Using filesort: datotečno sortiranje |
Ne-tabela verzija:
①,Kolona id: broj redosleda izvršenja upita. isti id: isti nivo izvršenja, redosled izvršenja od gore na dole po koloni table (kao višestruki JOIN); id raste: ugnježdeni podupit, što je veća vrednost, veći prioritet, ranije se izvršava.
EXPLAIN SELECT * FROM t1 JOIN (SELECT * FROM t2 WHERE id = 1) AS sub;id=2 za podupit t2, prvo se izvršava.
②,Kolona select_type: tip upita. Uobičajeni tipovi uključuju:
- SIMPLE: jednostavan upit, ne sadrži podupit ili UNION.
- PRIMARY: ako upit sadrži podupit, najspoljniji upit se označava kao PRIMARY. Potrebno je obratiti pažnju na performanse podupita ili izvedene tabele.
- SUBQUERY: podupit; treba izbegavati višeslojnu ugnježdenost, što je više moguće preurediti u JOIN.
- DERIVED: izvedena tabela (podupit u FROM klauzuli). Potrebno je smanjiti količinu podataka izvedene tabele, ili materializovati u privremenu tabelu.
③,Kolona table: koja tabela se upituje.
- derivedN: označava izvedenu tabelu (N odgovara id).
- unionNM,N: označava rezultat spajanja UNION (M, N su id učesnika UNION).
④,Kolona type: označava način na koji MySQL pronalazi tražene redove u tabeli.
- system, tabela ima samo jedan red (sistemska tabela ili izvedena tabela), nema potrebe za optimizacijom.
- const: pronađen jedan red kroz primarni ključ ili jedinstveni indeks (kao WHERE id = 1). Idealan slučaj.
- eq_ref: poklapanje JOIN primarnog ključa/jedinstvenog indeksa (kao
A JOIN B ON A.id = B.id). Obezbedite da JOIN polje ima indeks. - ref: poklapanje ne-jedinstvenog indeksa (kao
WHERE name = 'Wang Er', name ima običan indeks). - range: preuzima samo redove u datom opsegu, koristi indeks za pretragu. U
whereizjavi koristebettween...and,<,>,<=,ini drugi uslovi upitatypesurange. - index: skeniranje celog indeksa, ako nije potrebno vraćanje na tabelu, prihvatljivo; inače razmotrite pokretni indeks.
- ALL: skeniranje cele tabele, najmanja efikasnost.
⑤,Kolona possible_keys: mogući indeksi koji se mogu koristiti, ali se ne koriste nužno.
⑥,Kolona key: stvarno korišćeni indeks. Ako je NULL, indeks se ne koristi. Ako je PRIMARY, korišćen je indeks primarnog ključa.
⑦,Kolona key_len: broj bajtova korišćenih indeksom, odražava iskorišćenost kolona indeksa. Kod korišćenja združenog indeksa (a, b), key_len je zbir bajtova a i b (efikasno samo kad uslovi upita koriste a ili a+b).
-- struktura tabele: CREATE TABLE t (a INT, b VARCHAR(20), INDEX idx_a_b (a, b));
EXPLAIN SELECT * FROM t WHERE a = 1 AND b = 'test';key_len = 4 (INT) + 20*3 (utf8) + 2 = 66 bajtova.
⑧,Kolona ref: vrednost ili kolona koja se poređuje sa kolonom indeksa.
- const: konstanta. Na primer WHERE
column = 'value'. - func: funkcija. Na primer WHERE
column = func(column).
⑨,Kolona rows: broj redova koje treba skenirati koje procenjuje optimizer. Što je manja vrednost bolje, ako se dosta razlikuje od stvarne, možda su statistički podaci zastareli (potrebno ANALYZE TABLE). U kombinaciji sa poljem filtered može se izračunati konačan broj vraćenih redova (rows × filtered).
⑩,Kolona Extra: dodatne informacije.
- Using index: pokretni indeks, nema potrebe za vraćanjem na tabelu.
- Using where: nakon što pogon skladištenja vrati rezultate, Server sloj treba ponovo filtrirati (uslovi nisu potpuno spušteni).
- Using temporary : korišćenje privremene tabele (češće kod GROUP BY, DISTINCT).
- Using filesort: datotečno sortiranje (češće kod ORDER BY). Razmotrite dodavanje indeksa na polja ORDER BY.
- Select tables optimized away: optimizer je već optimizovao (kao što je COUNT(*) direktno kroz indeks).
- Using join buffer: korišćenje bafera povezivanja (Block Nested Loop ili Hash Join). Razmotrite povećanje join_buffer_size.
Primer:
----ova partija vam pomaže da razumete kraj, ne morate učiti za intervju----
Kakva je efikasnost izvršenja type, do kog nivoa je prikladna?
Redosled efikasnosti od visokog do niskog je system, const, eq_ref, ref, range, index i ALL.
U opštem slučaju, preporučuje se da vrednost type dostigne const, eq_ref ili ref, jer ovi tipovi ukazuju da upit koristi indeks, visoka efikasnost.
Ako je upit opsega, tip range je takođe prihvatljiv.
Tip ALL ukazuje na skeniranje cele tabele, najlošije performanse, često neprihvatljivo, potrebna optimizacija.
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje drugog tehničkog intervjua kandidata 8 iz Huawei: kako videti da li se koristi indeks, kako analizirati SQL
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje prvog pozadinskog tehničkog intervjua kandidata 1 iz Zuoyebang: nema posebne razlike između key-len i key, kada se koristi key-len, koja još polja gledate u explain, koje tipove ima extra
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje prvog pozadinskog tehničkog intervjua kandidata 1 iz Beike: nakon analyze explain, efikasnost izvršenja type, do kog nivoa je prikladna
Indeksi
35.🌟Zašto indeksi poboljšavaju performanse MySQL upita?
Indeksi su kao sadržaj knjige, omogućuju MySQL-u da brzo pronađe podatke, izbegava puno skeniranje tabele.
Uglavnom su B+ stablo strukture, efikasnost pretrage je O(log n), mnogo brže nego skeniranje od početka do kraja.
Pored bržeg čitanja, indeksi takođe ubrzavaju sortiranje, grupisanje, povezivanje itd.
U projektu je najčešći način kreirati indeks create index za polja koja se često koriste u uslovima upita, na primer:
create index idx_name on students(name);----ova partija vam pomaže da razumete početak, ne morate učiti za intervju----
Mi kroz wrap agent potvrdimo da li postoji efikasnost upita s indeksom i bez indeksa.
Prvo dajemo rezultat, vreme upita s indeksom je 0.007 sekundi, vreme upita bez indeksa je 0.036 sekundi.
Kreirajte bazu podataka i tabelu.
Ubacite 100 hiljada podataka.
Zatum redom izvršite explain da vidite plan izvršenja bez indeksa i s indeksom.
----ova partija vam pomaže da razumete kraj, ne morate učiti za intervju----
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje prvog tehničkog intervjua kandidata 23 iz Tencent QQ Background: MySQL indeks, zašto se koristi B+ stablo
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje drugog oddeljenja kandidata E iz Xiaomi Java backend tehnički intervju: zašto je potreban indeks
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje Java backend intervjua kandidata 5 iz male kompanije: pričaj o indeksima baze podataka, zatim zašto ubrzavaju brzinu upita
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje drugog tehničkog intervjua kandidata 1 iz Qunar: zašto mysql koristi indekse
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje intervjua kandidata 1 iz OPPO: razumevanje MySQL indeksa
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje prvog tehničkog intervjua kandidata 10 iz vivo: indeks, zašto je korišćenje indeksa brže
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje prvog tehničkog intervjua kandidata 27 iz Tencent Cloud Background: predstavi indeks? Šta je u osnovi?
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje drugog oddeljenja kandidata E iz Xiaomi Java backend tehnički intervju: zašto je potreban indeks
36.🌟Možete li jednostavno reći o klasifikaciji indeksa?
Ako gledamo klasifikaciju po funkciji, postoje indeksi primarnog ključa, jedinstveni indeksi, indeksi celog teksta; ako gledamo klasifikaciju po strukturi podataka, postoje B+ stablo indeksi, heš indeksi; ako gledamo klasifikaciju po sadržaju skladištenja, postoje klaster indeksi, neklaster indeksi.
Šta znate o indeksu primarnog ključa?
Indeks primarnog ključa se koristi za jedinstveno označavanje svakog zapisa u tabeli, vrednost kolone mora biti jedinstvena i ne null. Kod kreiranja primarnog ključa, MySQL će automatski generisati odgovarajući jedinstveni indeks.
Svaka tabela može imati samo jedan indeks primarnog ključa, obično je to polje samo-rastućeg id.
CREATE TABLE emp6 (emp_id INT PRIMARY KEY, name VARCHAR(50)); -- jednokolumni primarni ključ
CREATE TABLE CountryLanguage (
CountryCode CHAR(3),
Language VARCHAR(30),
PRIMARY KEY (CountryCode, Language) -- složeni primarni ključ
);---- Ovaj deo pomaže svima da razumiju start, za intervju nije potrebno učiti ---
Ako prilikom kreiranja tabele niste specificirali primarni ključ, InnoDB pogon skladištenja MySQL-a će prvo izabrati ne-null jedinstveni indeks kao primarni ključ; ako nema indeksa koji zadovoljava uslove, MySQL će automatski generisati skrivenu kolonu _rowid kao primarni ključ.
Možete videti informacije o indeksu putem show index from table_name:
Tablenaziv tabele kojoj trenutni indeks pripada.Non_uniqueda li je jedinstveni indeks, 0 znači jedinstveni indeks (kao primarni ključ), 1 znači ne-jedinstveni.Key_nameindeks primarnog ključa se podrazumevano zove PRIMARY; obični indeks je prilagođeno ime.Seq_in_indexredosled kolona u indeksu, u združenom indeksu ovo polje označava koja je kolona po redu (prva 1).Column_namenaziv polja sadržanog u trenutnom indeksu.CollationA znači rastuće (Ascend); D znači opadajuće.Cardinalitykardinalnost indeksa, tj. broj jedinstvenih vrednosti indeksa. Što je viši, bolja je razlikovanost (utiče na to da li će optimizer koristiti ovaj indeks).Sub_partdužina indeksa prefiksa.Packedda li je kompresovano skladištenje indeksa; obično se ne koristi, podrazumevana je NULL.Nullda li polje može biti NULL; polje primarnog ključa ne sme biti NULL.Index_typestruktura osnova indeksa, InnoDB podrazumevano je B+ stablo (BTREE).Commentkomentar indeksa.Visibleda li je vidljiv; MySQL 8.0+ može sakriti indeks.
---- Ovaj deo pomaže svima da razumiju end, za intervju nije potrebno učiti ---
Koja je razlika između jedinstvenog indeksa i indeksa primarnog ključa?
Indeks primarnog ključa = jedinstveni indeks + ne-null. Svaka tabela može imati samo jedan indeks primarnog ključa, ali može imati više jedinstvenih indeksa.
-- dodaj jedinstveni indeks na email kolonu
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
UNIQUE KEY uk_email (email) -- jedinstveni indeks
);
-- složeni jedinstveni indeks (garantuje da je kombinacija user_id i role jedinstvena)
CREATE TABLE user_roles (
user_id INT NOT NULL,
role VARCHAR(20) NOT NULL,
UNIQUE KEY uk_user_role (user_id, role)
);Indeks primarnog ključa ne dozvoljava ubacivanje NULL vrednosti, pokušaj ubacivanja NULL će prijaviti grešku; jedinstveni indeks dozvoljava ubacivanje više NULL vrednosti.
Koja je razlika između unique key i unique index?
Prilikom kreiranja jedinstvenog ključa, MySQL će automatski generisati jedinstveni indeks istog imena; obrnuto, prilikom kreiranja jedinstvenog indeksa takođe će implicitno dodati jedinstveno ograničenje.
Može se definisati kroz UNIQUE KEY uk_name ili CONSTRAINT uk_name UNIQUE.
CREATE TABLE users (
id INT PRIMARY KEY,
email VARCHAR(100),
-- eksplicitno imenovanje jedinstvenog ključa
CONSTRAINT uk_email UNIQUE (email)
);
CREATE TABLE users3 (
id INT PRIMARY KEY,
email VARCHAR(100),
UNIQUE KEY uk_email (email) -- jedinstveni indeks
);Može se kreirati jedinstveni indeks kroz CREATE UNIQUE INDEX.
CREATE TABLE users (
id INT PRIMARY KEY,
email VARCHAR(100)
);
-- ručno kreiraj jedinstveni indeks
CREATE UNIQUE INDEX uk_email ON users(email);Kroz SHOW CREATE TABLE table_name pregledajte strukturu tabele, rezultati su isti.
Koja je razlika između običnog indeksa i jedinstvenog indeksa?
Običan indeks služi samo za ubrzanje upita, ne ograničava jedinstvenost vrednosti polja; pogodan za polja sa visokom učestalošću upisa, polja opsega upita.
-- vremenska oznaka loga dozvoljava ponavljanje, nema potrebe za proverom jedinstvenosti
CREATE INDEX idx_log_time ON access_logs(access_time);
-- status narudžbine dozvoljava ponavljanje, ali često filtrira podatke po statusu
CREATE INDEX idx_order_status ON orders(status);Jedinstveni indeks forsira jedinstvenost vrednosti polja, prilikom ubacivanja ili ažuriranja će pokinuti proveru jedinstvenosti; pogodan za polja ograničenja poslovne jedinstvenosti, polja za sprečavanje dupliranja podataka.
-- email korisnika mora biti jedinstven
CREATE UNIQUE INDEX uk_email ON users(email);
-- osiguraj da isti korisnik može imati samo jednu neplaćenu narudžbinu za isti proizvod
CREATE UNIQUE INDEX uk_user_product ON orders(user_id, product_id) WHERE status = 'unpaid';Šta znate o indeksu celog teksta?
Indeks celog teksta je specijalan tip indeksa koji MySQL optimizuje pretragu tekstualnih podataka, pogodan za polja tipa CHAR, VARCHAR i TEXT.
MySQL 5.7 i novije verzije imaju ugrađen ngram parser, može obraditi kineske, japanske i korejske riječi itd.
Prilikom kreiranja tabele definiše se kroz FULLTEXT (title, body). Pretraga se vrši kroz MATCH(col1, col2) AGAINST('keyword'), podrazumevano se rezultati vraćaju u opadajućem redosledu, podržava pretragu u bulovom režimu.
+znači mora sadržavati;-znači isključiti;*znači džoker;
-- kreiraj indeks celog teksta prilikom kreiranja tabele (podržava kineski)
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200),
content TEXT,
FULLTEXT(title, content) WITH PARSER ngram
) ENGINE=InnoDB;
-- koristi bulov režim za upit
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);U osnovi koristi invertirani indeks da bi podelio tekstualni sadržaj polja na riječi, a zatim uspostavi invertiranu tabelu. Performanse su mnogo više od LIKE '%keyword%'.
---- Ovaj deo pomaže svima da razumiju start, za intervju nije potrebno učiti ---
Invertirani indeks kroz pomoćnu tabelu čuva mapiranje između riječi i pozicija same riječi u jednom ili više dokumenata, obično se ostvaruje kroz asocijativni niz.
Postoje dva oblika prikaza: inverted file index ({riječ, ID dokumenta gde se riječ nalazi}) i full inverted index ({riječ, (ID dokumenta gde se riječ nalazi, pozicija u konkretnom dokumentu)})
Na primer, imamo takav dokument:
DocumentId Text
1 Pease porridge hot, pease porridge cold
2 Pease porridge in the pot
3 Nine days old
4 Some like it hot, some like it cold
5 Some like it in the pot
6 Nine days oldUobljeni niz oblik čuvanja inverted file index je:
days → 3,6
old → 3,6
pease → 1,2
porridge → 1,2
...Full inverted index je detaljniji:
days → (3:5),(6:5)
old → (3:11),(6:11)
pease → (1:1),(1:7),(2:1)
porridge → (1:7),(2:7)
...Full inverted index ne samo čuva ID dokumenta, već i konkretnu poziciju riječi u dokumentu.
InnoDB koristi način full inverted index za realizaciju indeksa celog teksta.
Ako treba obraditi kineske riječi, obavezno pamtite da dodate WITH PARSER ngram, inače možda nećete moći dobiti podatke.
Međutim, za složene kineske scenarije, preporučuje se korišćenje profesionalnih pretraživača kao što je Elasticsearch, u projektu Tehničke sektor je korišćeno ovo rešenje.
---- Ovaj deo pomaže svima da razumiju end, za intervju nije potrebno učiti ---
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje razvojnog plana istraživanja i razvoja Neveroplan: pričaj o MySQL indeksima
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje prvog tehničkog intervjua kandidata 23 iz Tencent QQ Background: MySQL indeks, zašto se koristi B+ stablo
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje prvog tehničkog intervjua kandidata 10 iz Ctrip: pričaj o MySQL indeksima, kako optimizovati SQL?
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje prvog tehničkog intervjua kandidata 5 iz Alibaba mama: klasifikacija indeksa, najbolja praksa kreiranja indeksa
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje prvog tehničkog intervjua kandidata 3 iz 360: koje ste indekse MySQL koristili
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje intervjua: šta je indeks? Koji indeksi postoje
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje prvog tehničkog intervjua kandidata 1 iz Zuoyebang: šta čuvaju listovi običnog indeksa
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje prvog tehničkog intervjua kandidata 1 iz Zuoyebang: koje strukture podataka postoje u osnovi InnoDB
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje tehničkog intervjua kandidata 12 iz BYD: koji indeksi postoje, koja je razlika
37.🌟Na šta treba obratiti pažnju prilikom kreiranja indeksa?
Prvo, izaberite odgovarajuća polja
- Na primer, polja koja se često pojavljuju u WHERE, JOIN, ORDER BY, GROUP BY.
- Prvo izaberite polja visokog razlikovanja, na primer ID korisnika, broj telefona itd. sa više jedinstvenih vrednosti, a ne polja kao što su pol, status itd. sa vrlo niskim razlikovanjem, ako je zaista potrebno, možete razmotriti združeni indeks.
Drugo, treba kontrolisati broj indeksa, izbegavati preterano indeksiranje, svaki indeks zauzima prostor za skladištenje, ne preporučuje se da broj indeksa po tabeli premašuje 5.
Trebalo bi redovno kroz SHOW INDEX FROM table_name pregledavati korišćenje indeksa, brisati nepotrebne indekse. Na primer, ako već postoji združeni indeks (a, b), pojedinačni indeks (a) je suvišan.
Treće, prilikom združenog indeksa treba poštovati princip najlevijeg prefiksa, tj. u uslovima upita koristiti prvo polje združenog indeksa, da bi se potpuno iskoristio indeks.
Na primer, združeni indeks (A, B, C) može podržati upite A,A+B,A+B+C, ali ne može podržati pojedinačne upite B ili C.
Polja visokog razlikovanja stavljajte na levu stranu, polja jednakosnog upita su prioriteta u odnosu na polja opsega upita. Na primer WHERE A=1 AND B>10 AND C=2, prioritet (A, C, B).
Ako združeni indeks sadrži sva potrebna polja upita, takođe može izbeći vraćanje na tabelu, poboljšavajući efikasnost upita.
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje prvog intervjua Youzhong Financial: uloga indeksa, na šta treba obratiti pažnju prilikom dodavanja indeksa
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje prvog intervjua stažira kandidata 10 iz JD: da li je pogodno kreirati indeks za polja koja se često ažuriraju i upituju, zašto
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje prvog intervjua kandidata K iz Xiaomi proljeće: kako dizajnirati najbolji indeks
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje prvog tehničkog intervjua kandidata 1 iz JD: struktura MySQL indeksa, strategija kreiranja indeksa
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje prvog tehničkog intervjua kandidata 5 iz Alibaba mama: klasifikacija indeksa, najbolja praksa kreiranja indeksa
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje prvog intervjua backend razvoja kandidata 8 iz OPPO jesenje regrutovanje: na šta treba obratiti pažnju prilikom kreiranja indeksa
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje prvog tehničkog intervjua kandidata 5 iz JD: koji probleme treba razmatrati pri kreiranju indeksa
38.🌟U kojim slučajevima indeksi postaju nevažeći?
Skraćena verzija: na primer, ako se na koloni indeksa koristi funkcija, upit sa džokerom na početku, združeni indeks ne zadovoljava princip najlevijeg prefiksa, ili prilikom korišćenja OR deo polja nema indeks itd.
Prvo, korišćenje funkcija ili izraza na koloni indeksa će dovesti do nevažećeg indeksa.
-- indeks nevažeći
SELECT * FROM users WHERE YEAR(create_time) = 2023;
SELECT * FROM products WHERE price*2 > 100;
-- rešenje optimizacije (koristite upit opsega)
SELECT * FROM users WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';
SELECT * FROM products WHERE price > 50;Drugo, LIKE nejasan upit sa džokerom na početku će dovesti do nevažećeg indeksa.
-- indeks nevažeći
SELECT * FROM articles WHERE title LIKE '%baza podataka%';
-- može koristiti indeks (ali opseg je ograničen)
SELECT * FROM articles WHERE title LIKE 'baza podataka%';
-- rešenje: razmotrite indeks celog teksta ili pretraživač
SELECT * FROM articles WHERE MATCH(title) AGAINST('baza podataka');Treće, združeni indeks ne poštuje princip najlevijeg prefiksa, indeks će postati nevažeći.
-- pretpostavimo združeni indeks (a, b, c)
SELECT * FROM table WHERE b = 2 AND c = 3; -- indeks nevažeći
SELECT * FROM table WHERE a = 1 AND c = 3; -- koristi samo indeks kolone a
-- ispravna upotreba združenog indeksa
SELECT * FROM table WHERE a = 1 AND b = 2 AND c = 3;- Združeni indeks, ali WHERE ne zadovoljava princip najlevijeg prefiksa, indeks ne može biti efikasan. Na primer:
SELECT * FROM table WHERE column2 = 2, združeni indeks je(column1, column2).
---- Ovaj deo pomaže svima da razumiju start, za intervju nije potrebno učiti ---
Četvrto, korišćenje OR za povezivanje uslova ne-indeksiranih kolona će dovesti do nevažećeg indeksa.
-- pretpostavimo da name ima indeks ali age nema
SELECT * FROM users WHERE name = 'Zhang San' OR age = 25; -- skeniranje cele tabele
-- rešenje optimizacije 1: koristite UNION ALL
SELECT * FROM users WHERE name = 'Zhang San'
UNION ALL
SELECT * FROM users WHERE age = 25 AND name != 'Zhang San';
-- rešenje optimizacije 2: razmotrite dodavanje indeksa za agePeto, korišćenje != ili <> upita nejednakosti će dovesti do nevažećeg indeksa.
SELECT * FROM user WHERE status != 1; -- ako većina redova `status=1`, može se desiti skeniranje cele tabele
-- rešenje optimizacije: koristite upit opsega
SELECT * FROM user WHERE status < 1 OR status > 1;---- Ovaj deo pomaže svima da razumiju end, za intervju nije potrebno učiti ---
U kojim slučajevima nejasan upit ne koristi indeks?
Nejasan upit uglavnom koristi LIKE izjavu, u kombinaciji sa džokerima.
% (predstavlja bilo koji broj znakova) i _ (predstavlja jedan znak)
SELECT * FROM table WHERE column LIKE '%xxx%';Ovaj upit će vratiti sve zapise gde column kolona sadrži xxx.
Međutim, ako se džoker % nejasnog upita pojavi na početku pretrazivanog stringa, kao LIKE '%xxx', MySQL neće moći koristiti indeks, jer baza podataka mora skenirati celu tabelu da bi poklopila stringove bilo gde pozicije.
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje prvog tehničkog intervjua kandidata 1 iz ByteDance: da li where b =5 sigurno pogađa indeks? (scenariji nevažećeg indeksa)
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje prvog tehničkog intervjua kandidata 1 iz JD: situacije nevažećeg indeksa
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje Java backend intervjua kandidata 1 iz male kompanije: pisanje SQL izjava koje situacije će dovesti do nevažećeg indeksa?
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje tehničkog intervjua kandidata 12 iz BYD: scenariji nevažećeg indeksa
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje intervjua kandidata 1 iz OPPO: u kojim slučajevima indeks postaje nevažeći?
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje prvog tehničkog intervjua kandidata 5 iz JD: situacije nevažećeg indeksa
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje prvog intervjua kandidata 2 iz Li Auto: koje operacije će dovesti do nevažećeg indeksa?
39.Za koje scenarije indeksi nisu pogodni?
Prvo, kolone niskog razlikovanja mogu se kombinovati sa kolonama visokog razlikovanja u združeni indeks.
Drugo, kolone koje se često ažuriraju, indeksi će povećati trošak ažuriranja.
Treće, polja velikih objekata kao što su TEXT, BLOB mogu se zameniti indeksom prefiksa, indeksom celog teksta.
Četveto, kada je količina podataka u tabeli vrlo mala, ne premašuje 1000 redova, skeniranje cele tabele može biti brže od korišćenja indeksa.
---- Ovaj deo pomaže svima da razumiju start, za intervju nije potrebno učiti ---
Da bismo potvrdili četvrtu tačku, kreirali smo malu tabelu, zatim smo redom izvršili skeniranje cele tabele i indeks upit.
Zaključak je zaista takav, skeniranje cele tabele je brže.
Razlog je što je kada je količina podataka vrlo mala, trošak skeniranja cele tabele je vrlo nizak, jer su svi podaci verovatno već učitani u memoriju, korišćenje indeksa zahteva prvo pronalaženje indeksa, zatim kroz indeks pronalaženje stvarnih redova podataka, povećavajući dodatno vreme I/O adresiranja.
---- Ovaj deo pomaže svima da razumiju end, za intervju nije potrebno učiti ---
Da li treba kreirati indeks na polju pola?
Nije pogodno kreirati zaseban indeks na polju pola. Jer je razlikovanje polja pola vrlo nisko.
Ako se polje pola zaista često koristi za uslove upita, a obim podataka je takođe velik, polje pola može se koristiti kao deo združenog indeksa, u kombinaciji sa poljima visokog razlikovanja, efekat će biti mnogo bolji.
Što je razlikovanje?
Razlikovanje je mera proporcije jedinstvenih vrednosti polja u MySQL tabeli.
Razlikovanje = broj jedinstvenih vrednosti polja / ukupan broj zapisa polja; što je bliže 1, to je pogodnije kao indeks. Jer indeks može efikasnije suziti opseg upita.
Na primer, u tabeli ima 1000 zapisa, gde polje pola ima samo dve vrednosti (muški, ženski), tada je razlikovanje polja pola samo 0.002, nije pogodno za kreiranje indeksa.
Možete izračunati razlikovanje polja kroz odnos COUNT(DISTINCT column_name) i COUNT(*). Na primer:
SELECT
COUNT(DISTINCT gender) / COUNT(*) AS gender_selectivity
FROM
users;Koja polja su pogodna za dodavanje indeksa?
Odgovor u jednoj rečenici:
Uopšteno, primarni ključ, jedinstveni ključ i polja koja se često koriste kao uslovi upita najpogodnija su za dodavanje indeksa. Pored toga, razlikovanje polja mora biti visoko, tako da indeks može imati ulogu filtriranja; ako se polja često koristi za povezivanje tabele, sortiranje ili grupisanje, takođe se preporučuje dodavanje indeksa. Istovremeno, ako se više polja često pojavljuje zajedno u uslovima upita, takođe možete kreirati združeni indeks za poboljšanje performansi.
---- Ovaj deo pomaže svima da razumiju start, za intervju nije potrebno učiti ---
Visokofrekventna polja u uslovima upita, na primer polja koja se često koriste za jednakosni upit, upit opsega ili IN listu u WHERE klauzuli.
SELECT * FROM orders WHERE status = 'PAID' AND create_time > '2023-01-01';
-- ako se `status` i `create_time` često upituju zajedno, kreirajte združeni indeks `(status, create_time)`Polja povezivanja višestrukih tabela, na primer user.id i order.user_id.
SELECT * FROM user u JOIN order o ON u.id = o.user_id; -- `user_id` treba indeksPolja koja učestvuju u sortiranju ili grupisanju mogu direktno iskoristiti uređenost indeksa, izbegavajući datotečno sortiranje.
SELECT * FROM product ORDER BY price DESC; -- sortiranje jednog polja
SELECT category, COUNT(*) FROM product GROUP BY category; -- grupisana statistikaPolja koja treba iskoristiti pokretni indeks mogu izbeći operaciju vraćanja na tabelu.
-- kreirajte združeni indeks `(user_id, create_time)`
SELECT user_id, create_time FROM orders WHERE user_id = 100; -- pokretni indeks važi---- Ovaj deo pomaže svima da razumiju end, za intervju nije potrebno učiti ---
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje tehničkog intervjua kandidata 1 iz ByteDance: da li treba kreirati indeks na polju pola? Zašto? Što je razlikovanje? Komanda za pregled razlikovanja polja u MySQL-u?
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje prvog intervjua kandidata 2 iz Kuaishou: koja polja su pogodna za dodavanje indeksa? Koja nisu?
40.Što je više indekasa bolje?
Više indeksa nije bolje. Iako indeksi mogu ubrzati upite, takođe će dovesti do sporijeg upisa, zauzimanja više prostora za skladištenje, čak rizika da optimizer pogrešno izabere indeks.
---- Ovaj deo pomaže svima da razumiju start, za intervju nije potrebno učiti ---
Svaki put kada se podaci upisuju (INSERT/UPDATE/DELETE), MySQL treba sinhrono ažurirati sve relevantne indekse, što je više indeksa, viši trošak održavanja.
Ako određena tabela ima 10 indeksa, ubacivanje jednog reda podataka zahteva ažuriranje 10 B+ stablo struktura, dovodeći do povećanja kašnjenja upisa 5~10 puta.
Ako je količina podataka određene tabele 100GB, ako se kreiraju 5 indeksa, ukupno skladištenje može dostići 200GB+.
Kada je indeksa previše, optimizer treba proceniti više mogućih putanja izvršenja, može dovesti do teškoća u izboru, optimizer takođe može pogrešno izabrati indeks.
Još jedan primer, ako već postoji združeni indeks (A, B, C), zasebno kreiranje (A) ili (A, B) indeksa je suvišno.
Preporučuje se da broj indeksa po tabeli ne premašuje 5, zvanična preporuka MySQL je da ukupan broj polja indeksa po tabeli ≤ 30% ukupnog broja polja tabele.
---- Ovaj deo pomaže svima da razumiju end, za intervju nije potrebno učiti ---
Koje su misli o optimizaciji indeksa?
Odgovor u jednoj rečenici:
Prvo pronalazite usko grlo performansi kroz dnevnik sporih upita, zatum koristite EXPLAIN za analiziranje plana izvršenja, procenjujete da li se koristi indeks, da li se vraća na tabelu, da li se sortira. Zatum na osnovu karakteristika polja dizajnirajte odgovarajući indeks, kao što su izbor polja visokog razlikovanja, korišćenje združenog indeksa i pokretnog indeksa, izbegavajte načine pisanja koji nevaže indekse, konačno potvrdite efekt optimizacije kroz stvarno testiranje.
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje tehničkog intervjua kandidata 12 iz BYD: misli o optimizaciji indeksa
41.🌟Zašto InnoDB koristi B+ stablo kao indeks?
Sažetak u jednoj rečenici:
Jer je B+ stablo visoko balansirano višestruko stablo pretrage, može efikasno smanjiti broj disk IO operacija, i podržava uređeno prolazak i upit opsega.
Performanse upita su vrlo visoke, njegova struktura je takođe pogodna za MySQL da skladišti na disku po jedinici stranice.
Kao druge opcije, na primer heš tabele ne podržavaju upit opsega, binarno stablo je previše duboko, B stablo je nepogodno za skeniranje opsega, tako da je konačno izabrano B+ stablo.
Drugi način odgovora:
- U poređenju sa heš tabelom: B+ stablo podržava upit opsega i sortiranje
- U poređenju sa binarnim stablom i crveno-crnim stablom: B+ stablo je "deblje i kraće", manje slojeva, manje disk IO operacija
- U poređenju sa B stablom: B+ stablo ne-listovi čuvaju samo vrednosti ključa, listovi čuvaju podatke i povezani su kroz list, podržava upit opsega
Druga verzija odgovora:
B+ stablo je samobalansirano višestruko stablo pretrage, razlikuje se od crveno-crnog stabla i balansiranog binarnog stabla, svaki čvor B+ stabla može imati m potčvorova, dok crveno-crno stablo i balansirano binarno stablo imaju samo 2.
Takođe, razlikuje se od B stabla, B+ stablo ne-listovi čuvaju samo vrednosti ključa, ne čuvaju podatke, dok listovi čuvaju sve podatke i čine uređeni list.
Prednost ovog pristupa je što se na ne-listovima može čuvati više parova ključeva, jer ne čuvaju podatke, plus što listovi čine uređeni list, pri upitu opsega može direktno kroz pokazivače između listova redom pristupiti svim zapisima u celom opsegu upita, bez potrebe za višestrukim prolascima kroz stablo. Efikasnost upita je viša nego B stablo.
- Preporučeno čitanje: Konačno razumem B stablo
- Preporučeno čitanje: Članak u potpunosti objašnjava zašto MySQL koristi B+ stablo za indeks
---- Ovaj deo pomaže svima da razumiju start, za intervju nije potrebno učiti ---
Prvo pričajmo o B stablu.
B stablo je samobalansirano višestruko stablo pretrage, razlikuje se od crveno-crnog stabla i balansiranog binarnog stabla, B stablo svaki čvor može imati m potčvorova, dok crveno-crno stablo i balansirano binarno stablo imaju samo 2.
Drugo rečeno, crveno-crno stablo i balansirano binarno stablo su "visoki i tanki", dok je B stablo "nisko i debelo".
Zatum pričajmo o IO čitanju i pisanju memorije i diska.
Da bi se poboljšala efikasnost čitanja i pisanja, pri čitanju podataka sa diska u memoriju, odjednom će se pročitati najmanje jedan stranica podataka, ako nije puna stranica, pročitajte malo više.
Na primer, ako upit zahteva samo čitanje 2KB podataka, ali će MySQL zapravo pročitati 4KB podataka, da bi se napunila cela stranica. Stranica je najmanja logička jedinica interakcije između MySQL memorije i diska.
Na primer, ako treba pročitati 5KB podataka, zapravo će MySQL pročitati 8KB podataka, tačno dve stranice.
Jer što je više puta čitanja, manja je efikasnost. Kao kod našeg prenošenja cigla na gradilištu, prenos 10 cigli po jednom sigurno ima višu efikasnost od prenošenja 1 cigle po jednom, svaki put prenosim 10 (😁).
Za crveno-crno stablo i balansirano binarno stablo ova "visoka i tanka" vrsta, svaki put nosi manje cigli, jer nema dovoljno snage, mora više puta trčati natrag-napred.
Obično je visina B+ stabla 3-4 slojeva može podržati TB nivo podataka, a svaki upit treba samo 2-4 disk I/O operacija, daleko ispod O(log2N) kompleksnosti binarnog stabla ili crveno-crnog stabla
Što je više stablo, to znači da je potrebno više disk IO za pronalazak podataka, jer svaki sloj možda treba učitati novi čvor sa diska.
Čvorovi B stabla su obično poravnati sa veličinom stranice, tako da svaki put kada sa diska učitate jedan čvor, upravo je veličina jedne stranice.
Jedan čvor B stabla obično uključuje tri dela:
- Vrednost ključa: tj. primarni ključ u tabeli
- Pokazivač: čuva informacije o potčvorovima
- Podaci: podaci redova osim primarnog ključa
Kao što se kaže "nesreća i sreća stoje zajedno", jer se podaci čuvaju na svakom čvoru B stabla, dovodi do toga da se svaki čvor može čuvati manje vrednosti ključeva i pokazivača, jer je veličina svakog čvora fiksna, zar ne?
Zato dolazi B+ stablo, B+ stablo ne-listovi čuvaju samo vrednosti ključa, ne čuvaju podatke, dok listovi čuvaju sve podatke redova i čine uređeni list.
Prednost ovog pristupa je što ne-listovi mogu čuvati više parova ključeva jer ne čuvaju podatke, stablo postaje "deblje i kraće", time ima više snage, svaki put nosi više cigli (😂).
U poređenju sa B stablom, ne-listovi B+ stabla mogu sadržati više vrednosti ključeva, jedan 16KB čvor može čuvati oko 1200 vrednosti ključeva, drastično smanjujući visinu stabla.
Time, broj disk IO potrebnih za pronalazak podataka je manji, efikasnost upita je viša.
Uz to, listovi čine uređeni list, pri upitu opsega može direktno kroz pokazivače između listova redom pristupiti svim zapisima u celom opsegu upita, bez potrebe za višestrukim prolascima kroz stablo.
B stablo ne može to postići.
---- Ovaj deo pomaže svima da razumiju end, za intervju nije potrebno učiti ---
Da li su listovi B+ stabla jednostruki ili dvostruki povezani? Ako se pretražuje od veće vrednosti ka manjoj, kako se operiše?
Listovi B+ stabla su povezani kroz dvostruku listu, time omogućavajući lagan upit opsega i obrnuti prolazak.
- Kod izvršenja upita opsega, može početi od početne ili krajnje tačke opsega, prolazeći unapred ili unazad.
- Kod potrebe za obrnutim obradom podataka, dvostruka lista je vrlo korisna.
Ako treba u B+ stablu pretraživati od veće vrednosti ka manjoj, može se prvo locirati na najdesniji čvor, pronaći list koji sadrži maksimalnu vrednost. Ostvaruje se kroz način da se od korenog čvora kreće nadesno kroz stablo.
Nakon što se locira na najdesnji list, iskoristite dvostruku list između listovih čvorova za prolazak ulevo i to je to.
Zašto MongoDB indeksi koriste B stablo, dok MySQL koristi B+ stablo?
MongoDB obično čuva dokumente u JSON formatu, upiti su uglavnom jedinstveni ključni upiti (kao find({_id: 123})), karakteristika "čvorovi čuvaju i ključeve i podatke" B stabla omogućava upitu da se završi ranije na ne-listovima, čime se smanjuje broj I/O operacija.
MySQL upiti obično uključuju opseg (WHERE id > 100), sortiranje (ORDER BY), povezivanje (JOIN) i druge operacije. Listovi B+ stabla su strukture lista, prirodno podržavaju uređeni prolazak, bez potrebe za povratkom na koren ili inorder prolazak, efikasnost je daleko viša nego B stablo.
---- Ovaj deo pomaže svima da razumiju start, za intervju nije potrebno učiti ---
| Karakteristika | MongoDB (B stablo) | MySQL InnoDB (B+ stablo) |
|---|---|---|
| Model podataka | Baza podataka dokumenata | Relacijska baza podataka |
| Način skladištenja | Podela datoteka podataka i indeksa | Indeks klastera je vezan za skladištenje primarnog ključa |
| Modalitet upita | Fokus na upite jednog dokumenta | Fokus na upite opsega i složena povezivanja |
| Modalitet pristupa podacima | Prenosno pristupanje je uglavnom | Sekvencijalni pristup je češći |
| Sadržaj skladištenja indeksa | Ne-listovi čuvaju pokazivače na podatke | Samo listovi čuvaju podatke |
| Efikasnost upita opsega | Treba višestruko prolazak kroz stablo | Efikasno prolazak kroz list listova |
| Iskorišćenost memorije | Keširanje jedne putanje upita je efikasnije | Pogodnije za keširanje skenskog prenosa |
Preporučeno čitanje: Zašto MongoDB indeksi koriste B stablo, a MySQL B+ stablo?
---- Ovaj deo pomaže svima da razumiju end, za intervju nije potrebno učiti ---
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje komercijalnog intervjua ByteDance: pričaj o B+ stablu, zašto 3 sloja može sadržati 2000W, zašto 2000w podataka se brzo pretražuje
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje intervjua državnog preduzeća: pričaj o osnovnoj strukturi podataka MySQL, razlika između B stabla i B+ stabla
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje prvog intervjua stažista kandidata 22 iz Tencent: zašto MySQL bira B+ stablo
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje drugog oddeljenja kandidata E iz Xiaomi Java backend tehnički intervju: pričaj o mehanizmu osnova MySQL indeksa
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje prvog tehničkog intervjua kandidata 1 iz JD: struktura MySQL indeksa, strategija kreiranja indeksa
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje prvog intervjua kandidata 16 iz Tencent Cloud Smart: struktura MySQL indeksa, zašto koristi B+ stablo?
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje Java backend intervjua kandidata 5 iz male kompanije: pričaj o indeksima baze podataka, zatim zašto ubrzavaju brzinu upita, sam sam pričao o B+ stablu, zatim sam pitao razliku između B stabla i B+ stabla
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje prvog intervjua stažista kandidata 1 iz Baidu Wenxin Yiyan 25 Java backend: da li je stranica B+ stabla jednostruka ili dvostruka povezana lista? Ako se pretražuje od veće vrednosti ka manjoj, kako se operiše?
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje intervjua kandidata 1 iz Dewu: projektni indeks, MySQL indeks, zašto mongoDB koristi B stablo, poređenje ova dva
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje prvog intervjua backend razvoja kandidata 8 iz OPPO jesenje regrutovanje: koja je struktura podataka MySQL indeksa, zašto se biraju takve strukture podataka
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje intervjua kandidata 8 iz JD: indeks
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje prvog intervjua kandidata 9 iz Meituan: B+ stablo?
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje tehničkog intervjua kandidata 15 iz Meituan Dianping backend: struktura podataka indeksa
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje intervjua kandidata 9 iz Dewu: da li znate B+ stablo? Šta je u osnovi? Zašto se tako koristi?
- Java Vodič za intervju (plaćeno) sadrži originalno pitanje prvog intervjua backend razvoja kandidata 3 iz Didi: princip MySQL indeksa, koje su prednosti B+ stablo "deblje"
42.🌟Koliko podataka može skladištiti jedno B+ stablo?
Odgovor u jednoj rečenici:
Koliko podataka može skladištiti jedno B+ stablo, zavisi od faktora grananja i visine. U InnoDB, podrazumevana veličina stranice je 16KB, kada je primarni ključ bigint, 3-slojno B+ stablo obično može skladištiti oko 20 miliona podataka.
---- Ovaj deo pomaže svima da razumiju start, za intervju nije potrebno učiti ---
Prvo pogledajmo formulu za izračun:
Maksimalan broj zapisa = (faktor grananja)^(visina stabla-1) × kapacitet list čvoraZatum pogledajmo ključne parametre:
①,Veličina stranice, podrazumevana 16KB
②,Veličina primarnog ključa, pretpostavimo da je tip bigint, tada je njegova veličina 8 bajtova.
③,Veličina pokazivača stranice, u InnoDB izvornom kodu podešeno na 6 bajtova, 4 bajta broja stranice + 2 bajta pomeraja u stranici.
Zato ne-list čvor može skladištiti 16384/14(vrednost ključa+pokazivač)=1170 takvih jedinica.
Kada je visina sloja 2, koren čvor može da čuva 1170 pokazivača, pokazujući na 1170 listnih čvorova, tako da je ukupna količina podataka 1170×16 =18720 zapisa.
Kada je visina sloja 3, koren čvor pokazuje na 1170 nelistnih čvorova, svaki nelistni čvor dalje pokazuje na 1170 listnih čvorova, tako da je ukupna količina podataka 1170×1170×16≈21,902,400 zapisa (približno 21.9 miliona zapisa).
Preporučeno čitanje: Tihog mesto: koliko redova podataka može da smesti jedno InnoDB B+ stablo?
---- ovo deo vam pomaže da razumete end, na intervjuu nije neophodno zapamtiti ----
Sada imam tabelu sa 20 milion podataka, koliko slojeva visine ima moj B+ stablo?
Za 20 miliona zapisa, dovoljno je 3 sloja visine B+ stabla.
Preporučeno čitanje: Koliko slojeva obično ima B+ stablo u Innodb motoru? Koliko podataka može da smesti?
Koliko podataka može da smešti svaki listni čvor?
Ako je veličina podataka jednog reda 1KB, tada svaka stranica može da čuva oko 16 redova (16KB/1KB) podataka.
---- ovo deo vam pomaže da razumete start, na intervjuu nije neophodno zapamtiti ----
Pretpostavimo da imamo takvu strukturu tabele:
CREATE TABLE `user` (
`id` BIGINT PRIMARY KEY, -- 8 bajtova
`name` VARCHAR(255) NOT NULL, -- stvarna dužina 50 bajtova (UTF8MB4, svaki karakter najviše 4 bajta)
`age` TINYINT, -- 1 bajt
`email` VARCHAR(255) -- stvarna dužina 30 bajtova, može biti NULL
) ROW_FORMAT=COMPACT;Tada je veličina jednog reda podataka: 8 + 50 + 1 + 30 = 89 bajtova.
Trošak formata reda je: zaglavlje reda 5 bajtova + pokazivač 6 bajtova + trošak polja promenljive dužine 2 bajtova (name i email svaki po 1 bajt) + bitmapa NULL 1 bajt = 14 bajtova.
Dakle, stvarna veličina podataka svakog reda je: 89 + 14 = 103 bajta.
Podrazumevana veličina svake stranice je 16KB, tada svaka stranica može najviše da čuva 16384 / 103 ≈ 158 redova podataka.
---- ovo deo vam pomaže da razumete end, na intervjuu nije neophodno zapamtiti ----
- Java vodič za intervju (plaćeni) sadrži originalno pitanje ByteDance komercijalne prve runde: pričaj o B+ stablu, zašto 3 sloja mogu da prime 20 miliona zapisa, zašto je pretraga 20 miliona zapisa brza
- Java vodič za intervju (plaćeni) sadrži originalno pitanje Qi An Xin prvog tehničkog intervjua: innodb koristi stranice podataka za čuvanje podataka? Podrazumevana veličina stranice podataka je 16K, sada imam tabelu sa 20 miliona podataka, koliko slojeva visine ima moj b+ stablo?
- Java vodič za intervju (plaćeni) sadrži originalno pitanje Meituan 18 Chengdu do kuće intervju: koliko podataka može najviše da čuva jedna tabela (odgovorio sam 20 miliona, izračunato na osnovu tro-slojne visine b+ stabla)
- Java vodič za intervju (plaćeni) sadrži originalno pitanje Dewu 1. intervju: MySQL B+ stabla, što je veći stepen stabla bolje ili ne, koliko se obično postavlja
- Java vodič za intervju (plaćeni) sadrži originalno pitanje Tencent 29 Java backenda prva runda: koliko podataka može da smesti tro-slojno B+ stablo u InnoDB? Koliko zapisa može da smesti svaki listni čvor?
memo: 4. aprila 2025. izmenjeno do ovde, danasje neki prijatelj pitao, da li postoji engleska verzija "pobede nad testovima", on studira u inostranstvu, počelo je da se zalazi i u inostranstvu, zaista neverovatno.
43. Zašto indeksi koriste B+ stablo a ne obična binarna stabla?
Svaki čvor običnog binarnog stabla može imati najviše dva podređena čvora. Kada se podaci ubacuju sekvencijalno u rastućem redosledu, binarno stablo se degradira u ulanzanu listu, što dovodi do toga da visina stabla bude jednaka količini podataka.
U ovom slučaju, pretraga id=7 zahteva 7 I/O operacija, što je ekvivalentno punom skeniranju tabele. B+ stablo kao višestruko balansirano stablo može da kontroliše stotine miliona podataka u 3-4 sloja visine stabla, što drastično smanjuje broj disk I/O operacija.
Zašto se ne koriste balansirana binarna stabla?
Iako balansirana binarna stabla rešavaju problem degradacije običnih binarnih stabala, problem i dalje postoji što svaki čvor može imati najviše dva podređena čvora.
- Java vodič za intervju (plaćeni) sadrži originalno pitanje Baidu 1 Wenxin Yiyan 25 praksi Java backend intervju: zašto MySQL indeksi koriste B+ stablo a ne druge strukture podataka?
- Java vodič za intervju (plaćeni) sadrži originalno pitanje Tencent 27 cloud backend tehničke prve runde: zašto se ne koriste binarna stabla? Zašto se ne koriste AVL stabla?
44.🌟 Zašto se koriste B+ stabla a ne B stabla?
B+ stabla imaju 3 značajne prednosti u poređenju sa B stablima:
Prvo, svaki čvor B stabla čuva i ključeve i podatke i pokazivače, što dovodi do toga da svaki čvor čuva manji broj ključeva.
U jednoj 16KB InnoDB stranici, ako su podaci veći, nelistni čvorovi B stabla mogu da prime samo nekoliko desetina ključeva, dok nelistni čvorovi B+ stabla mogu da prime hiljade ključeva.
Drugo, opsežna pretraga B stabla zahteva povratak kroz slojeve poredak srednje sekvence; dok su listni čvorovi B+ stabla povezani redosledom dvostruke ulančane liste, opsežna pretraga zahteva samo pozicioniranje na početnu tačku i zatim sekvencijalno skeniranje liste, nema troška povratka.
Treće, podaci B stabla mogu biti smešteni u bilo kom čvoru, ako su ciljni podaci upravo u korenskom čvoru ili čvorovima višeg sloja, pretraga zahteva samo 1-2 I/O operacije; ali ako su podaci u čvorovima donjeg sloja, potrebnih je više I/O operacija, što dovodi do većih fluktuacija u vremenu pretrage.
Svi podaci B+ stabla su smešteni u listnim čvorovima, dužina puta pretrage je fiksna, vreme je stabilno O(logN), što je kritično za stabilnost MySQL u scenarijima visoke konkurentnosti.
Ako želite da saznate više o razlikama između B stabla i B+ stabla, preporučujem čitanje:
- GitHub: B stablo i B+ stablo detaljno
- SegmentFault: kada vas intervjuer pita o B stablu i B+ stablu, bacite mu ovaj članak
- Geek Time: zašto se koriste B+ stabla za indekse?
- Jedna snažna semena: sa 16 slika vam objašnjavam zašto MySQL koristi B+ stabla za indekse
Koja je vremenska kompleksnost B+ stabla?
O(logN).
Visina stabla h je:
Gde je N ukupna količina podataka, m je red. Svaki sloj zahteva jednu binarnu pretragu, kompleksnost je
Ukupna kompleksnost je:
Zašto se koriste B+ stabla a ne skip liste?
Skip lista je u suštini struktura ulančane liste, samo što su određeni čvorovi izvučeni u višem sloju kao indeksi.
Jedan podatak jedan čvor, ako treba smestiti 20 miliona zapisa podataka, i svaka pretraga treba da postiže efekat binarne pretrage, tada je visina skip liste oko 24 sloja (2 na 24 stepena).
U najgorem slučaju, ovih 24 sloja podataka su raspoređeni u različitim stranicama podataka, pronalaženje jednog podatka zahteva 24 disk I/O operacije.
Dok 20 miliona zapisa u B+ stablu zahteva samo 3 sloja.
Kako se radi opsežna pretraga B+ stabla?
Odgovor jednom rečenicom:
Prvo se kroz putanju indeksa pozicionira na prvi listni čvor koji zadovoljava uslove, a zatim se sledi ulančana lista između listnih čvorova udesnule/ulevo, dok se ne premaši opseg.
Detaljna verzija:
Opsežna pretraga B+ stabla indeksa se prvenstveno oslanja na dvostruku ulančanu listu između listnih čvorova.
Prvi korak, počev od korenog čvora B+ stabla, kroz indeksne ključeve se silazi po slojevima, pronalazi se prvi listni čvor koji zadovoljava uslove.
Drugi korak, koristeći dvostruku ulančanu listu između listnih čvorova, počev od početnog čvora, sekvencijalno se posećuje svaki čvor. Kada vrednost indeksa premaši opseg pretrage, ili se dođe do kraja liste, pretraga se prekida.
---- ovo deo vam pomaže da razumete start, na intervjuu nije neophodno zapamtiti ----
Na primer, tražimo 45 u sledećem B+ stablu.
Prvi korak, počev od korenog čvora, jer je veće od 25, krećemo od desnog podstabla. Jer je 45 veće od 35, upoređujemo sa indeksom desne strane, indeks desne strane je takođe 45, tako da nastavljamo pretragu u desnom podstablu.
Drugi korak, počev od listnog čvora 45, sekvencijalno posećujemo, nalazimo 45.
---- ovo deo vam pomaže da razumete end, na intervjuu nije neophodno zapamtiti ----
Znate li brzo sortiranje?
Brzo sortiranje koristi metod deli-i-vladaj da podeli sekvencu u 2 manje podsekvence, a zatim rekurzivno sortira obe podsekvence, predložio ga je Tony Hoare 1960. godine.
Njegova osnovna ideja je:
- Izaberite pivotnu vrednost.
- Podelite niz na dva dela, levi manji od pivota, desni veći ili jednak od pivota.
- Rekurzivno sortirajte levu i desnu stranu, na kraju sjedinite.
public static void quickSort(int[] arr, int low, int high) {
if (low < high) {
int pivotIndex = partition(arr, low, high);
quickSort(arr, low, pivotIndex - 1);
quickSort(arr, pivotIndex + 1, high);
}
}
private static int partition(int[] arr, int low, int high) {
int pivot = arr[high];
int i = low - 1;
for (int j = low; j < high; j++) {
if (arr[j] <= pivot) {
i++;
swap(arr, i, j);
}
}
swap(arr, i + 1, high);
return i + 1;
}
private static void swap(int[] arr, int i, int j) {
int temp = arr[i];
arr[i] = arr[j];
arr[j] = temp;
}Preporučeni link: Brzo sortiranje
- Java vodič za intervju (plaćeni) sadrži originalno pitanje Alipay 2 prolećna regrutacija tehnička prva runda: razlika između klaster i ne-klaster indeksa? Pored čuvanja podataka šta još imaju listni čvorovi B+ stabla?
- Java vodič za intervju (plaćeni) sadrži originalno pitanje Qi An Xin 1 Java tehnička prva runda: koja je razlika između b stabla i b+ stabla
- Java vodič za intervju (plaćeni) sadrži originalno pitanje Baidu 1 Wenxin Yiyan 25 praksi Java backend intervju: zašto MySQL indeksi koriste B+ stablo a ne druge strukture podataka?
- Java vodič za intervju (plaćeni) sadrži originalno pitanje ByteDance 8 Java backend praksi prva runda: koja je razlika između mysql b+ stabla i b stabla
- Java vodič za intervju (plaćeni) sadrži originalno pitanje Zuoyebang 1 Java backend prva runda: koje prednosti ima B+ stablo
- Java vodič za intervju (plaćeni) sadrži originalno pitanje kolega 1 Beike Chain backend tehnička prva runda: zašto se koristi b+ stablo a ne b stablo
- Java vodič za intervju (plaćeni) sadrži originalno pitanje Ali sistema 19 Ele.me intervju: zašto indeksi koriste B+ stablo a ne B stablo, detaljna analiza vremenske kompleksnosti, b+ stablo, brzo sortiranje...
45. Koja je razlika između B+ stabla indeksa i Haš indeksa?
Skraćeni odgovor:
B+ stablo indeks podržava opsežnu pretragu i sortiran skeniranje, podrazumevana struktura indeksa InnoDB.
Haš indeks podržava samo pretragu jednakosti, brz je ali funkcionalnost je slaba, čest u Memory motoru.
Malo detaljniji odgovor:
B+ stablo indeks je balansirano višestruko stablo pretrage, svi podaci su smešteni u listnim čvorovima, nelistni čvorovi čuvaju samo indeksne ključeve. Listni čvorovi su povezani pokazivačima u uređenu ulančanu listu, prirodno podržava sortiranje.
I podržava opsežnu pretragu i pretragu zamagljenih uzoraka, podrazumevana struktura indeksa InnoDB.
Haš indeks mapira ključeve u fiksnu dužinu haš vrednosti pomoću haš funkcije, kroz haš vrednost pozicionira lokaciju čuvanja podataka.
Potpuno neuređeno, podržava samo pretragu jednakosti, čest u Memory motoru.
---- ovo deo vam pomaže da razumete start, na intervjuu nije neophodno zapamtiti ----
Jer je B+ stablo podrazumevana vrsta indeksa u InnoDB, prilikom kreiranja B+ stabla nije neophodno specificirati tip indeksa.
CREATE TABLE example_btree (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255),
INDEX name_index (name)
) ENGINE=InnoDB;Može se kreirati haš indeks putem UNIQUE HASH:
CREATE TABLE example_hash (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255),
UNIQUE HASH (name)
) ENGINE=MEMORY;InnoDB ne nudi opciju direktne kreacije haš indeksa, jer B+ stablo indeks može vrlo dobro da podrži opsežnu pretragu i pretragu jednakosti, zadovoljavajući potrebe većine operacija baze podataka.
Međutim, InnoDB interno koristi tehnologiju pod nazivom "adaptivni haš indeks" (Adaptive Hash Index, AHI), kada se određene vrednosti indeksa često pristupa, InnoDB će automatski kreirati haš indeks na osnovu B+ stabla, kombinujući prednosti oba.
Može se videti status adaptivnog haš indeksa putem SHOW VARIABLES LIKE 'innodb_adaptive_hash_index';.
Ako se vraćena vrednost prikazuje kao ON, to znači da je adaptivni haš indeks uključen.
---- ovo deo vam pomaže da razumete end, na intervjuu nije neophodno zapamtiti ----
- Java vodič za intervju (plaćeni) sadrži originalno pitanje Zuoyebang 1 Java backend prva runda: zašto se ne koristi haš indeks
46.🌟Koja je razlika između klaster i ne-klaster indeksa?
Listovi klaster indeksa čuvaju kompletne redove podataka, podaci i indeks su zajedno. Primarni indeks InnoDB je klaster indeks, listovi čuvaju ne samo primarni ključ već i druge kolone, stoga je upit po primarnom ključu vrlo brz.
Svaka tabela može imati samo jedan klaster indeks, obično definisan primarnim ključem. Ako nije eksplicitno definisan primarni ključ, InnoDB će implicitno kreirati skriveni primarni indeks row_id.
Listovi ne-klaster indeksa čuvaju samo vrednost primarnog ključa, potrebno je vratiti se na klaster indeks po primarnom ključu da bi se pronašle druge kolone, jedinstveni indeks, obični indeks itd. su ne-klaster indeksi.
Svaka tabela može imati više ne-klaster indeksa, ako ne želite da se vraćate na tabelu, možete koristiti pokrivajući indeks da uključite polja koja treba upit.
---- ovo deo vam pomaže da razumete start, na intervjuu nije neophodno zapamtiti ----
Jedna tabela može imati samo jedan klaster indeks.
CREATE TABLE user (
id INT PRIMARY KEY,
name VARCHAR(100),
age INT
);Primarni ključ id je klaster indeks, listni čvorovi B+ stabla direktno čuvaju (id, name, age).
Jedna tabela može imati više ne-klaster indeksa.
CREATE INDEX idx_name ON user(name);
CREATE INDEX idx_age ON user(age);idx_name je ne-klaster indeks, listni čvorovi čuvaju name -> id, za pretragu kompletnog reda podataka treba povratak na tabelu.
idx_age je takođe ne-klaster indeks, listni čvorovi čuvaju age -> id, za pretragu kompletnog reda podataka takođe treba povratak na tabelu.
Ako želite saznati više o klaster i ne-klaster indeksima, preporučujem čitanje:
- Lei Ge: koja je razlika između klaster i ne-klaster indeksa?
- Kratak prikaz klaster i ne-klaster indeksa
- Klaster indeks, ne-klaster indeks, kombinovani indeks, jedinstveni indeks
- Song Ge: još o MySQL klaster indeksu
---- ovo deo vam pomaže da razumete end, na intervjuu nije neophodno zapamtiti ----
- Java vodič za intervju (plaćeni) sadrži originalno pitanje Xiaomi prolećna regrutacija K prva runda: mysql: razlika između klaster i ne-klaster indeksa
- Java vodič za intervju (plaćeni) sadrži originalno pitanje Alipay 2 prolećna regrutacija tehnička prva runda: razlika između klaster i ne-klaster indeksa? Pored čuvanja podataka šta još imaju listni čvorovi B+ stabla?
- Java vodič za intervju (plaćeni) sadrži originalno pitanje ByteDance 1 Java backend tehnička prva runda: šta je klaster indeks? Šta je ne-klaster indeks?
- Java vodič za intervju (plaćeni) sadrži originalno pitanje Tencent Cloud Smart 16 prva runda: razlika između klaster i ne-klaster indeksa?
- Java vodič za intervju (plaćeni) sadrži originalno pitanje Kuaishou 1 glavna stanica tehnički odjeljenje prva runda: koja je razlika između MySQL klaster i ne-klaster indeksa?
- Java vodič za intervju (plaćeni) sadrži originalno pitanje Zuoyebang 1 Java backend prva runda: šta je MySQL klaster indeks
- Java vodič za intervju (plaćeni) sadrži originalno pitanje Tencent 29 Java backend prva runda: kako se čuvaju MySQL indeksi? Jedan indeks jedan B+ stablo, ili više indeksa u jednom B+ stablu? Koje podatke čuvaju listni čvorovi?
memo: 5. aprila 2025. izmenjeno do ovde, danasje neki prijatelj koji je dobio letnju praksu u Meituan-u rekao,da je dva puta menjao CV uz Ergeovu pomoć, praktično svi koji ne zapadaju na prvi stepen obrazovanja imaju intervju, vrlo dobro.
47.🌟Razumete li povratak na tabelu?
Kada koristite ne-klaster indeks za upit, MySQL mora pronaći primarni ključ preko ne-klaster indeksa, a zatim se vratiti na klaster indeks da pronađe kompletan red, ovaj proces se zove povratak na tabelu.
Pretpostavimo da imamo tabelu korisnika users:
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
email VARCHAR(50),
INDEX (name)
);Izvršavamo upit:
SELECT * FROM users WHERE name = 'Wang Er';Proces upita je sledeći:
- Prvi korak, MySQL koristi ne-klaster indeks na koloni name da pronađe sve primarne ključeve gde je
name = 'Wang Er'. - Drugi korak, koristi primarni ključ da pronađe kompletan zapis u klaster indeksu.
Koja je cena povratka na tabelu?
Povratak na tabelu obično zahteva pristup dodatnim stranicama podataka, ako podaci nisu u memoriji, potrebno je čitati sa diska, povećava I/O troškove.
Može se izbeći povratak na tabelu kroz pokrivajući indeks ili kombinovani indeks.
-- originalna struktura tabele
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
INDEX idx_name (name)
);
-- potrebno je pretražiti name i age
SELECT name, age FROM users WHERE name = 'Zhang San';
-- ovo će vratiti se na tabelu, jer age nije u idx_name indeksu
-- rešenje optimizacije 1: kreiraj kombinovani indeks koji uključuje age
ALTER TABLE users ADD INDEX idx_name_age (name, age);
-- sada isti upit ne zahteva povratak na tabeluU kojim slučajevima se dešava povratak na tabelu?
Prvo, kada polja upita nisu u ne-klaster indeksu, mora se vratiti na indeks primarnog ključa da bi se dobili podaci.
Drugo, kada polja upita uključuju ne-indeksirane kolone (kao SELECT *), neizostno dolazi do povratka na tabelu.
Da li je više zapisa povratka na tabelu bolje?
Što je više zapisa povratka na tabelu, obično to znači slabije performanse, jer svaki zadatak zahteva još jednu pretragu kompletnih podataka kroz primarni ključ. Ovaj proces uključuje pristup memoriji ili disku IO, naročito kada stopa pogodaka keša nije visoka, povratak na tabelu će ozbiljno uticati na efikasnost upita.
Znate li MRR?
MRR je optimizaciona strategija koju je InnoDB uveo da bi rešio problem velikog broja nasumičnih IO operacija koje nastaju povratkom na tabelu.
On će prvo sortirati listu vrednosti primarnog ključa pronađenih putem ne-klaster indeksa, a zatim se redosledom vraća na indeks primarnog ključa u serijama, pretvarajući nasumične I/O u sekvencijalne I/O, kako bi smanjilo vreme traženja diska.
---- ovo deo vam pomaže da razumete start, na intervjuu nije neophodno zapamtiti ----
Može se videti da li je MRR uključen putem SHOW VARIABLES LIKE 'optimizer_switch';.
Gde mrr=on znači da je MRR uključen, mrr_cost_based=on znači da se odluka o korišćenju MRR donosi na osnovu troškova.
Takođe može se videti veličina bafera MRR putem show variables like 'read_rnd_buffer_size';, podrazumevano je 256KB.
Kreirajmo tabelu, ubacimo nekoliko podataka, a zatim izvršimo upit da demonstriramo efekat MRR.
CREATE DATABASE IF NOT EXISTS mrr_test;
USE mrr_test;
CREATE TABLE IF NOT EXISTS orders (id INT AUTO_INCREMENT PRIMARY KEY, user_id INT, order_date DATE, amount DECIMAL(10,2), status VARCHAR(20), INDEX idx_user_date(user_id, order_date));
DELIMITER //
CREATE PROCEDURE generate_test_data()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 100000 DO
INSERT INTO orders (user_id, order_date, amount, status)
VALUES (
FLOOR(1 + RAND() * 1000), -- Nasumični user_id između 1 i 1000
DATE_ADD('2023-01-01', INTERVAL FLOOR(RAND() * 365) DAY), -- Nasumični datum u 2023
ROUND(10 + RAND() * 990, 2), -- Nasumični iznos između 10 i 1000
ELT(1 + FLOOR(RAND() * 3), 'completed', 'pending', 'cancelled') -- Nasumični status
);
SET i = i + 1;
END WHILE;
END //
DELIMITER ;
CALL generate_test_data();
DROP PROCEDURE generate_test_data;"Vidi performanse podataka kada je MRR uključen i isključen:
-- osiguraj da je MRR uključen i podesi dovoljno veliki bafer
SET SESSION optimizer_switch='mrr=on,mrr_cost_based=off';
SET SESSION read_rnd_buffer_size = 16*1024*1024;
-- očisti keš i status
FLUSH STATUS;
FLUSH TABLES;
-- prisili korišćenje sekundarnog indeksa i upit sa povratkom na tabelu (biranjem neindeksiranih kolona)
SELECT 'Raw data access pattern with MRR ON' as test_case;
SELECT /*+ MRR(orders_mrr_test) */ id, shipping_address, customer_name
FROM orders_mrr_test FORCE INDEX(idx_user_date)
WHERE user_id IN (100,200,300,400,500,600,700,800,900,1000)
AND order_date BETWEEN '2023-03-01' AND '2023-04-01'
LIMIT 15;
-- prikaži status procesora
SHOW STATUS LIKE 'Handler_%';
SHOW STATUS LIKE '%mrr%';
-- poređenje: isključi MRR
SET SESSION optimizer_switch='mrr=off,mrr_cost_based=off';
FLUSH STATUS;
FLUSH TABLES;
SELECT 'Raw data access pattern with MRR OFF' as test_case;
SELECT id, shipping_address, customer_name
FROM orders_mrr_test FORCE INDEX(idx_user_date)
WHERE user_id IN (100,200,300,400,500,600,700,800,900,1000)
AND order_date BETWEEN '2023-03-01' AND '2023-04-01'
LIMIT 15;
-- prikaži status procesora
SHOW STATUS LIKE 'Handler_%';
SHOW STATUS LIKE '%mrr%';
-- prikaži detaljan plan izvršenja
EXPLAIN FORMAT=TREE
SELECT /*+ MRR(orders_mrr_test) */ id, shipping_address, customer_name
FROM orders_mrr_test FORCE INDEX(idx_user_date)
WHERE user_id IN (100,200,300,400,500,600,700,800,900,1000)
AND order_date BETWEEN '2023-03-01' AND '2023-04-01';"Može se videti poređenje rezultata kada je MRR uključen:
Wrap je takođe dao odgovarajuće objašnjenje rezultata:
Takođe može se potvrdi korišćenje MRR u explain.
---- ovo deo vam pomaže da razumete end, na intervjuu nije neophodno zapamtiti ----
- Java vodič za intervju (plaćeni) sadrži originalno pitanje ByteDance 1 Java backend tehnička prva runda: kako koristeći ne-klaster indeks pronaći podatke?
- Java vodič za intervju (plaćeni) sadrži originalno pitanje ByteDance 1 tehnička druga runda: da li je više zapisa povratka na tabelu bolje? (cena povratka na tabelu)
memo: 6. aprila 2025. izmenjeno do ovde, danaskad sam pomagao prijatelju da promeni CV, video sam da je prijatelj napisaoTehničku pi na CV, vrlo dobro, preporučujem svima.
48.🌞 Znate li kombinovani indeks? (dopuna)
Dodato 22. novembra 2024.
Kombinovani indeks stavlja više polja u jedan indeks, ali mora da poštuje "najlevi prefiks" princip, samo kada se kontinuirano koriste od prvog polja, indeks će biti efikasan.
Kombinovani indeks će kreirati B+ stablo po redosledu polja. Na primer indeks (age, name) će prvo sortirati po age, ako je age isti onda sortira po name, ako su oba ista onda sortira po primarnom ključu, osiguravajući da listni čvorovi nemaju duplikate stavke indeksa.
Kreiranje kombinovanog indeksa (A,B,C) ekvivalentno je istovremeno kreiranju tri indeksa (A), (A,B) i (A,B,C).
-- kreiraj kombinovani indeks
CREATE INDEX idx_order_user_product ON orders(user_id, product_id, create_time)
-- efikasan upit
SELECT * FROM orders
WHERE user_id=1001 AND product_id=2002
ORDER BY create_time DESCKoja je struktura skladištenja kombinovanog indeksa na donjem nivou?
Kombinovani indeks koristi B+ stablo strukturu za skladištenje na donjem nivou, ovo je isto kao i kod indeksa jedne kolone.
Razlika u poređenju sa indeksom jedne kolone je u tome što svaki čvor kombinovanog indeksa čuva vrednosti svih indeksnih kolona, a ne samo vrednost prve kolone. Na primer, za kombinovani indeks (a,b,c), svaki čvor sadrži vrednosti tri kolone a, b, c.
primer nelistnog čvora:
[(a=1, b=2, c=3) → podčvor 1, (a=5, b=3, c=1) → podčvor 2]
primer listnog čvora (InnoDB):
(a=1, b=2, c=3) → PK=100 | (a=1, b=2, c=4) → PK=101
(povezani pokazivačima formiraju dvostruku ulančanu listu)Koji sadržaj čuvaju listni čvorovi kombinovanog indeksa?
Kombinovani indeks pripada ne-klaster indeksu, listni čvorovi čuvaju vrednosti svih kolona kombinovanog indeksa i odgovarajuću vrednost primarnog ključa reda, a ne kompletne podatke reda. Prilikom upita ne-indeksiranih polja, potrebno je kroz vrednost primarnog ključa vratiti se na klaster indeks da bi se dobili kompletni podaci.
Na primer, listni čvorovi indeksa (a, b) će kompletno čuvati vrednosti (a, b) i sortirati po redosledu polja (npr. a prvo, ako je a isti onda po b). Ako je primarni ključ id, listni čvor će čuvati kombinaciju (a, b, id).
- Java vodič za intervju (plaćeni) sadrži originalno pitanje Baidu 4 intervju: struktura skladištenja kombinovanog indeksa na donjem nivou (i koja je razlika u odnosu na druge vrste indeksa?) koji sadržaj čuvaju listni čvorovi kombinovanog indeksa?
memo: 7. aprila 2025. dodato do ovde, danasje prijatelj dao povratnu informaciju rekao, nakon što se pridružio Ergeovoj planeti, napisao je projekat Tehničke pi na CV, dobio offer u Tencent Tianmeu, zaista snažno.
49.🌟Razumete li pokrivajući indeks?
Pokrivajući indeks znači: sva polja potrebna za upit su u indeksu, nije potreban povratak na tabelu, rezultat se može direktno vratiti sa indeksa.
empname i job su dva polja u kombinovanom indeksu, upit upravo traži ova dva polja, tada jedan upit može postići bez povratka na tabelu.
Možete kombinovati često upitana polja (kao što su WHERE uslovi i SELECT kolone) u kombinovani indeks da biste implementirali pokrivajući indeks.
Na primer:
CREATE INDEX idx_empname_job ON employee(empname, job);Tada upit može koristiti indeks:
SELECT empname, job FROM employee WHERE empname = 'Wang Er' AND job = 'programer';Običan indeks služi samo za ubrzavanje podudaranja uslova upita, dok pokrivajući indeks može direktno pružiti rezultat upita.
Ako imam tabelu (name, sex, age, id), select age, id, name from tblname where name='paicoding'; kako kreirati indeks
Budući da uslov upita ima polje name, minimalno treba dodati indeks za polje name.
CREATE INDEX idx_name ON tblname(name);U rezultat upita su takođe potrebna polja age, id, možete kreirati kombinovani indeks za ova tri polja, koristeći pokrivajući indeks, direktno dobijati podatke iz indeksa, smanjiti povratak na tabelu.
CREATE INDEX idx_name_age_id ON tblname (name, age, id);
- Java vodič za intervju (plaćeni) sadrži originalno pitanje Zuoyebang 1 Java backend prva runda: da li razumete pokrivajući indeks
- Java vodič za intervju (plaćeni) sadrži originalno pitanje Meituan 9 prva runda: pokrivajući indeks, povratak na tabelu?
- Java vodič za intervju (plaćeni) sadrži originalno pitanje ByteDance 13 Java backend druga runda: jedan tabela (name, sex, age, id), select age, id, name from tblname where name='paicoding'; kako kreirati indeks
50.🌞 Šta je princip najlevog prefiksa?
Princip najlevog prefiksa znači: kada MySQL koristi kombinovani indeks, mora početi podudaranje od najlevijeg polja, kako bi pogodio indeks.
Ako postoji kombinovani indeks (A, B, C), uslovi važenja su sledeći:
| uslov upita | da li se aktivira indeks? | objašnjenje |
|---|---|---|
| WHERE A = 1 | ✅ da | koristi prvu kolonu indeksa |
| WHERE A = 1 AND B = 2 | ✅ da | koristi prve dve kolone indeksa |
| WHERE A = 1 AND B = 2 AND C = 3 | ✅ da | koristi sve kolone indeksa |
| WHERE B = 2 | ❌ ne | preskače levu kolonu A, indeks ne važi |
| WHERE B = 2 AND C = 3 | ❌ ne | nema levu kolonu, indeks ne važi |
| WHERE A = 1 AND C = 3 | ⚠️ delimično važi | samo kolona A, kolonu C nije moguće iskoristiti za optimizaciju indeksa |
| WHERE A = 1 | ✅ da | koristi prvu kolonu indeksa |
| WHERE A = 1 AND B = 2 | ✅ da | koristi prve dve kolone indeksa |
| WHERE A = 1 AND B = 2 AND C = 3 | ✅ da | koristi sve kolone indeksa (najbolji slučaj) |
| WHERE B = 2 | ❌ ne | preskače levu kolonu A, indeks ne važi |
| WHERE B = 2 AND C = 3 | ❌ ne | nema levu kolonu, indeks ne važi |
| WHERE A = 1 AND C = 3 | ⚠️ delimično važi | samo kolonu A, kolonu C nije moguće iskoristiti za optimizaciju indeksa |
Ako se kolone sortiranja ili grupisanja nalaze u delu najlevog prefiksa, indeks može takođe ubrzati operacije.
SQL
-- indeks(a,b)
SELECT * FROM table WHERE a = 1 ORDER BY b; -- može koristiti sortiranje indeksaDa li se može koristiti indeks nakon opsežnog upita?
Opsežni upit se može primeniti samo na poslednju kolonu najlevog prefiksa. Kolone nakon opsežnog upita ne mogu koristiti indeks.
SQL
-- indeks(a,b,c)
SELECT * FROM table WHERE a = 1 AND b > 2 AND c = 3;
-- može koristiti samo a i b, c ne može koristiti indeksZašto ako ne počneš od najlevog, ne možeš podudariti?
Odgovor jednom rečenicom:
Jer je kombinovani indeks u B+ stablu kreiran sortiranjem po najlevijem polju prvo, ako se preskoči najlevo polje, MySQL ne može da odredi odakle treba početi opseg pretrage, naravno ne može koristiti indeks.
Na primer, ako imamo user tabelu, kreirali smo kombinovani indeks (name, age) za name i age.
ALTER TABLE user add INDEX comidx_name_phone (name,age);Kombinovani indeks u B+ stablu kreira stablo pretrage redosledom sleva nadesno, name je levo, age je desno.
Kada koristimo where name= 'Wang Er' and age = '20' za pretragu, B+ stablo će prvo uporediti name da odredi sledeći smer pretrage, nalevo ili nadesno.
Ako je name isti, onda upoređuje age.
Ali ako uslov upita nema name, ne zna se kako treba pretraživati, jer je name preduslov u B+ stablu, bez name, indeks ne može pomoći.
Kombinovani indeks (a, b), where a = 1 i where b = 1, da li je isti efekat?
Nije isti.
WHERE a = 1 može pogoditi kombinovani indeks, jer je a prvo polje kombinovanog indeksa, zadovoljava princip podudaranja najlevog prefiksa. Dok WHERE b = 1 ne može pogoditi kombinovani indeks, jer nema uslov podudaranja a, MySQL će skenirati celu tabelu.
---- ovo deo vam pomaže da razumete start, na intervjuu nije neophodno zapamtiti ----
Da potvrdimo, pretpostavimo da imamo ab tabelu, kreirali smo kombinovani indeks (a, b):
CREATE TABLE ab (
a INT,
b INT,
INDEX ab_index (a, b)
);Ubaci podatke:
INSERT INTO ab (a, b) VALUES (1, 2), (1, 3), (2, 1), (3, 3), (2, 2);Izvrši upit:
Kroz explain se može videti, WHERE a = 1 koristi kombinovani indeks, dok WHERE b = 1 treba da skenira celu tabelu, red po red proverava svaki red.
---- ovo deo vam pomaže da razumete end, na intervjuu nije neophodno zapamtiti ----
Ako postoji kombinovani indeks abc, kako sledeći sql koristi kombinovani indeks?
select * from t where a = 2 and b = 2;
select * from t where b = 2 and c = 2;
select * from t where a > 2 and b = 2;Prva SQL izjava sadrži uslove a = 2 i b = 2, tačno odgovaraju prve dve kolone kombinovanog indeksa.
Druga SQL izjava jer nije koristila a u najlevom prefiksu, aktiviraće skeniranje cele tabele.
Treća SQL izjava nakon opsežnog uslova a > 2, indeks će prestati da se podudara, uslov b = 2 treba dodatno filtrirati.
(A,B,C) kombinovani indeks select * from tbn where a=? and b in (?,?) and c>? hoće li ići po indeksu?
Dodato 15. marta 2024.
Ovaj upit će pogoditi kombinovani indeks, jer je a tačno podudaranje, b je IN višestruko podudaranje vrednosti, c je opseg uslov posle b, zadovoljava princip najlevog prefiksa.
Za
a=?: ovo je tačno podudaranje, i prvo polje kombinovanog indeksa, pa sigurno će pogoditi indeks.Za
b IN (?, ?): ekvivalentno sa b=? OR b=?, pripada podudaranju višestrukih vrednosti, i drugo polje kombinovanog indeksa, takođe će pogoditi indeks.Za
c>?: ovo je opseg uslov, pripada trećem polju kombinovanog indeksa, takođe će pogoditi indeks.
---- ovo deo vam pomaže da razumete start, na intervjuu nije neophodno zapamtiti ----
Da potvrdimo.
Prvi korak, kreiraj tabelu.
CREATE TABLE tbn (A INT, B INT, C INT, D TEXT);Drugi korak, kreiraj indeks.
CREATE INDEX idx_abc ON tbn (A, B, C);Treći korak, ubaci podatke.
INSERT INTO tbn VALUES (1, 2, 3, 'First');
INSERT INTO tbn VALUES (1, 2, 4, 'Second');
INSERT INTO tbn VALUES (1, 3, 5, 'Third');
INSERT INTO tbn VALUES (2, 2, 3, 'Fourth');
INSERT INTO tbn VALUES (2, 3, 4, 'Fifth');Četvrti korak, izvrši upit.
EXPLAIN SELECT * FROM tbn WHERE A=1 AND B IN (2, 3) AND C>3\G
Iz rezultata EXPLAIN možemo dobiti neke ključne informacije o tome kako MySQL izvršava upit:
- type: tip upita, ovde je
range, znači da je MySQL koristio opsežnu pretragu, jer uslov upita sadrži operator>. - possible_keys: mogući indeksi koje se mogu koristiti za izvršenje upita, ovde je
idx_abc, znači da MySQL smatra da ćeidx_abcindeks biti korišćen za optimizaciju upita. - key: stvarno korišćeni indeks za izvršenje upita, takođe je
idx_abc, ovo potvrđuje da je ovaj upit pogodio kombinovani indeks. - Extra: pruža dodatne informacije o izvršenju upita.
Using index conditionznači da je MySQL koristio pushdown indeksnog uslova (Index Condition Pushdown, ICP), ovo je metod optimizacije MySQL-a, dozvoljava filtriranje podataka na nivou indeksa.
---- ovo deo vam pomaže da razumete end, na intervjuu nije neophodno zapamtiti ----
Jedan scenario pitanje kombinovanog indeksa: (a,b,c) kombinovani indeks, da li (b,c) ide po indeksu?
Dodato 6. aprila 2024
Prema principu najlevog prefiksa, (b,c) upit neće ići po indeksu.
Jer u kombinovanom indeksu (a,b,c), a je najlevja kolona, pri kreiranju stabla indeksa prvo treba imati a, zatim će biti b i c. A u uslovima upita nema a, tako da MySQL ne može koristiti ovaj indeks.
EXPLAIN SELECT * FROM tbn WHERE B=1 AND C=1\G
Kreiraj kombinovani indeks(a,b,c), gde c = 5 da li će koristiti indeks? Zašto?
Dodato 8. aprila 2024
Neće. Samo treća kolona indeksa c se koristi kao uslov upita, prve dve kolone a i b se ne koriste. Ovo ne zadovoljava princip najlevog prefiksa.
EXPLAIN SELECT * FROM tbn WHERE C=5\G
U sql se koristi like, ako sledi podudaranje najlevog prefiksa, da li će upit sigurno koristiti indeks?
Dodato 4. novembra 2024
Ako je uzorak upita sufiksni džoker LIKE 'prefix%', i to polje ima indeks, optimizator će obično koristiti indeks. U suprotnom, čak i ako sledi podudaranje najlevog prefiksa, LIKE polje neće moći pogoditi indeks.
Na primer age = 18 and name LIKE '%xxx', MySQL će prvo koristiti kombinovani indeks age_name da pronađe sve redove gde je age zadovoljen, a zatim će skenirati celu tabelu za filtriranje polja name.
type: ref znači korišćenje indeksa za pronalaženje svih redova koji se podudaraju sa određenom vrednošću.
Ako je sufiksni džoker, na primer age = 18 and name LIKE 'xxx%', MySQL će direktno koristiti kombinovani indeks age_name da pronađe sve redove koji zadovoljavaju uslove.
type je range, znači da je MySQL koristio opsežno skeniranje indeksa, filtered je 100.00%, znači da među skeniranim redovima svi redovi zadovoljavaju WHERE uslov.
- Java vodič za intervju (plaćeni) sadrži originalno pitanje ByteDance komercijalne prve runde: (A,B,C) kombinovani indeks
select * from tbn where a=? and b in (?,?) and c>?hoće li ići po indeksu?- Java vodič za intervju (plaćeni) sadrži originalno pitanje JD 10 backend praksi prva runda: kombinovani indeks abc, a=1,c=1/b=1,c=1/a=1,c=1,b=1 ide ili ne ide po indeksu
- Java vodič za intervju (plaćeni) sadrži originalno pitanje Kuaishou 7 Java backend tehnička prva runda: jedan scenario pitanje kombinovanog indeksa: (a,b,c) kombinovani indeks, da li (b,c) ide po indeksu
- Java vodič za intervju (plaćeni) sadrži originalno pitanje ByteDance 1 Java backend tehnička prva runda: kreiraj kombinovani indeks(a,b,c), gde c = 5 da li će koristiti indeks? Zašto?
- Java vodič za intervju (plaćeni) sadrži originalno pitanje Meituan 15 Dianping backend tehnička intervju: u sql se koristi like, ako sledi podudaranje najlevog prefiksa, da li će upit sigurno koristiti indeks?
- Java vodič za intervju (plaćeni) sadrži originalno pitanje BYD 3 Java tehnička prva runda: pričaj o indeksima baze podataka, principu najlevog podudaranja i strukturi indeksa
- Java vodič za intervju (plaćeni) sadrži originalno pitanje Tencent Cloud Smart 16 prva runda: pričaj o principu najlevog prefiksa
- Java vodič za intervju (plaćeni) sadrži originalno pitanje China Merchants Bank 9 Java backend tehnička prva runda: princip dizajna MySQL kombinovanog indeksa
- Java vodič za intervju (plaćeni) sadrži originalno pitanje kolega 1 Beike Chain backend tehnička prva runda: kombinovani indeks (a, b), where a = 1 i where b = 1, da li je isti efekat
- Java vodič za intervju (plaćeni) sadrži originalno pitanje Tencent 27 cloud backend tehnička prva runda: (kombinovani indeks) kako ide indeks u sledećem?
- Java vodič za intervju (plaćeni) sadrži originalno pitanje Didi 3 car-hailing backend development prva runda: kombinovani indeks (a, b, c), gde b = 1, može li ići, gde a = 1, može li ići
51.🌞 Šta je pushdown indeksnog uslova?
Pushdown indeksnog uslova znači: MySQL šalje WHERE uslove "što je više moguće" na nivo skeniranja indeksa, u sloju motora skladištenja unapred filtrira zapise koji ne zadovoljavaju uslove.
Kada uslov upita sadrži indeksirane kolone ali se ne podudara potpuno, ICP će u sloju motora skladištenja filtrirati uslove ne-indeksiranih kolona, kako bi smanjio broj povratka na tabelu.
Tradicionalni proces upita je, motor skladištenja kroz kombinovani indeks pozicionira ID primarnog ključa koji zadovoljava uslov najlevog prefiksa; vraća se na tabelu čita komplet podatke reda i vraća Server sloju; Server sloj filtrira sve vraćene redove po WHERE uslovima.
Sa ICP, motor skladištenja direktno filtrira uslove koji se mogu spustiti na nivou indeksa, samo vrati podatke na tabelu za zapise koji zadovoljavaju uslove indeksa, a zatim vrati Server sloju za filtriranje preostalih uslova.
---- ovo deo vam pomaže da razumete start, na intervjuu nije neophodno zapamtiti ----
Na primer, ako imamo user tabelu, kreirali smo kombinovani indeks (name, age), izjava upita: select * from user where name like 'Zhang%' and age=10;, bez optimizacije pushdown indeksnog uslova:
MySQL će koristiti indeks name da pronađe sve primarne ključeve gde je name like 'Zhang%', na osnovu ovih primarnih ključeva, jedan po jedan vraća se na tabelu tražeći kompletne podatke reda, i u Server sloju filtrira redove gde nije age=10.
Nakon uključenja ICP, InnoDB će kroz kombinovani indeks direktno filtrirati ID primarnog ključa koji zadovoljava uslove (name like 'Zhang%' and age=10), a zatim se vratiti na tabelu tražeći kompletne podatke reda.
Drugo rečeno, pretpostavimo da name like 'Zhang%' pronađe 10000 redova, age=10 ima samo 10 redova, bez pushdown indeksnog uslova, MySQL će se 10000 puta vratiti na tabelu, čitati 10000 redova podataka, a zatim u Server sloju filtrirati 9990 redova.
A nakon pushdown indeksnog uslova, MySQL će se samo 10 puta vratiti na tabelu, čitati 10 redova podataka.
Da potvrdimo.
Iz rezultata možemo jasno videti efekat ICP. Kada je ICP uključen, Extra kolona prikazuje "Using index condition", pokazuje da su uslovi filtriranja spušteni na sloj motora skladištenja.
Kada je ICP isključen, Extra kolona prikazuje samo "Using where", pokazuje da se uslovi filtriranja izvršavaju na sloju servera.
-- uključi ICP
SET optimizer_switch='index_condition_pushdown=on';
-- očisti status
FLUSH STATUS;
SELECT 'Performance test with ICP ON' as test_case;
-- izvrši upit i analiziraj performanse
EXPLAIN ANALYZE
SELECT /*+ ICP_ON */ *
FROM orders_mrr_test
WHERE user_id BETWEEN 100 AND 200
AND order_date >= '2023-01-01'
AND order_date < '2023-02-01'
AND order_date NOT LIKE '2023-01-15%';
-- prikaži status procesora
SHOW STATUS LIKE 'Handler_read%';
-- isključi ICP
SET optimizer_switch='index_condition_pushdown=off';
-- očisti status
FLUSH STATUS;
SELECT 'Performance test with ICP OFF' as test_case;
-- izvrši isti upit
EXPLAIN ANALYZE
SELECT *
FROM orders_mrr_test
WHERE user_id BETWEEN 100 AND 200
AND order_date >= '2023-01-01'
AND order_date < '2023-02-01'
AND order_date NOT LIKE '2023-01-15%';
-- prikaži status procesora
SHOW STATUS LIKE 'Handler_read%';"Stvarna razlika u performansama je takođe velika. Kada je ICP uključen, stvarno skenirani broj redova: 1,649 redova, vreme izvršenja: oko 12.3 ms. Kada je isključen, stvarno skenirani broj redova: 19,959 redova, vreme izvršenja: oko 32.1 ms.
---- ovo deo vam pomaže da razumete start, na intervjuu nije neophodno zapamtiti ----
- Java vodič za intervju (plaćeni) sadrži originalno pitanje Meituan 9 prva runda: pushdown indeksnog uslova
52. Kako videti da li se koristi indeks? (dopuna)
Dodato 15. marta 2024.
Može se videti da li se koristi indeks putem EXPLAIN ključne reči.
EXPLAIN SELECT * FROM table WHERE column = 'value';Ako se koristi indeks, key vrednost u rezultatu će prikazati naziv indeksa.
Kombinovani indeks abc, a=1,c=1/b=1,c=1/a=1,c=1,b=1 ide ili ne ide po indeksu?
Dodato 19. marta 2024
ac može koristiti indeks, uslov a=1 zadovoljava princip najlevog prefiksa, aktivira prvu kolonu indeksa a; jer je preskočena srednja kolona b, c=1 ne može direktno iskoristiti uređenost indeksa za optimizaciju, ali može kroz pushdown indeksnog uslova filtrirati uslov c na sloju motora skladištenja, smanjiti broj povratka na tabelu.
bc ne može koristiti indeks, može samo skenirati celu tabelu, jer ne zadovoljava princip najlevog prefiksa; acb iako je redosled pomućen, MySQL optimizator će automatski reorganizovati u abc, tako da može pogoditi indeks.
---- ovo deo vam pomaže da razumete start, na intervjuu nije neophodno zapamtiti ----
Da potvrdimo kroz stvarni SQL.
Primer 1 (a=1,c=1):
EXPLAIN SELECT * FROM tbn WHERE A=1 AND C=1\G
key je idx_abc, pokazuje da a=1,c=1 će koristiti kombinovani indeks. Extra: Using index condition pokazuje da je ICP efikasan.
Primer 2 (b=1,c=1):
EXPLAIN SELECT * FROM tbn WHERE B=1 AND C=1\G
key je NULL, pokazuje da b=1,c=1 neće koristiti kombinovani indeks. Jer uslov upita ne sledi princip najlevog prefiksa.
Primer 3 (a=1,c=1,b=1):
EXPLAIN SELECT * FROM tbn WHERE A=1 AND C=1 AND B=1\GOptimizator će automatski prilagoditi redosled uslova u a=1 AND b=1 AND c=1.
key je idx_abc, pokazuje da a=1,c=1,b=1 će koristiti kombinovani indeks.
I rows=1, jer MySQL optimizator automatski reorganizuje uslove upita da bi zadovoljio princip najlevog prefiksa, direktno koristi kombinovani indeks da pronađe redove gde je a=1 AND b=1 AND c=1.
memo: 8. aprila 2025. izmenjeno do ovde, danas je prijatelj dao povratnu informaciju, dobio offer za letnju praksu u Tencent-u i Meituan-u, i već je OC, zaista čestitam, još jedan dan vredi podeliti rezultate, haha.
Brave
53.🌞 Koje vrste brave postoje u MySQL-u?
MySQL ima više tipova brave, može se podeliti po različitim dimenzijama, po granularnosti brave, podeljene na tabela-brana i red-brana.
Po mehanizmu zaključavanja podeljene na optimističku bravu i pesimističnu bravu. Po kompatibilnosti podeljene na deljenu bravu i ekskluzivnu bravu.
---- ovo deo vam pomaže da razumete start, na intervjuu nije neophodno zapamtiti ----
Tabela-brana: zaključava celu tabelu, mali trošak resursa, brzo zaključavanje, ali niska konkurentnost, neće doći do mrtve brave; pogodna za scenarije gde su dominantne pretrage, malo izmena (kao MyISAM motor).
Dalje deljeno na tabelsku deljenu čitačku bravu (S-brana): dozvoljava više transakcija istovremeno čitanje, ali blokira operacije pisanja; tabelsku ekskluzivnu pisaću bravu (X-brana): ekskluzivno zauzima tabelu, blokira čitanje i pisanje drugih transakcija.
Red-brana: zaključava jedan ili više redova, veliki trošak, sporo zaključavanje, može doći do mrtve brave, ali visoka konkurentnost (InnoDB podrazumevano podržava).
Dalje deljeno na bravu zapisa (Record Lock): zaključava konkretne zapise u indeksu; bravu razmaka (Gap Lock): zaključava "razmak" između zapisa indeksa, sprečava fantomsko čitanje; brana sledećeg ključa (Next-Key Lock): kombinuje bravu zapisa i bravu razmaka, zaključava interval levo-otvoreno-desno-zatvoreno (npr. (5, 10]).
Deljena brana (S-brana/čitačka brana), dozvoljava više transakcija istovremeno čitanje podataka, ali blokira pisanje. Sintaksa: SELECT ... LOCK IN SHARE MODE
Ekskluzivna brana (X-brana/pisaća brana), ekskluzivno zauzima podatke, blokira čitanje i pisanje drugih transakcija. Sintaksa: SELECT ... FOR UPDATE.
Optimistička brana pretpostavlja malo konflikata, detektuje konflikte kroz mehanizam broja verzije ili CAS (npr. UPDATE SET version=version+1 WHERE version=old_version).
Pesimistička brana pretpostavlja česte konflikte konkurentnosti, prvo zaključa pa operiše SELECT FOR UPDATE.
---- ovo deo vam pomaže da razumete end, na intervjuu nije neophodno zapamtiti
- Java vodič za intervju (plaćeni) sadrži originalno pitanje JD 4 cloud praksi intervju: koje vrste bravery ima mysql
- Java vodič za intervju (plaćeni) sadrži originalno pitanje Meituan 15 Dianping backend tehnički intervju: pitao je malo mysql bravery i MVCC
- Java vodič za intervju (plaćeni) sadrži originalno pitanje Ali sistema 19 Ele.me intervju: MySQL bravery
54. Znate li globalnu bravu? (dopuna)
Dodato 15. jula 2024.
Globalna brana zaključava ceo instancu baze podataka, kada se izvrši operacija globalnog zaključavanja, cela baza podataka će biti u stanju samo za čitanje, sve operacije pisanja će biti blokirane, dok se globalna brana ne oslobodi.
Prilikom pravljenja rezerve cele baze, ili migracije podataka, može se koristiti globalna brana da bi se garantovala konzistentnost podataka.
U MySQL-u, može se koristiti komanda FLUSH TABLES WITH READ LOCK da bi se dobila globalna brana.
Nakon izvršenja ove komande, sve tabele će biti zaključane u stanje samo za čitanje. Ne zaboravite nakon završetka rezervi ili migracije koristiti komandu UNLOCK TABLES da oslobodite globalnu bravu.
-- zaključaj celu bazu podataka
FLUSH TABLES WITH READ LOCK;
-- izvrši operaciju rezervi
-- na primer koristiti mysqldump za rezervu
! mysqldump -u username -p database_name > backup.sql
-- oslobodi globalno zaključavanje
UNLOCK TABLES;Znate li tabelsku bravu?
Znam.
Tabelska brana je česta u MyISAM motoru, InnoDB takođe može ručno dodati branu putem LOCK TABLES.
Pogodna za scenarije gde se mnogo čita, malo piše, puno skeniranje tabele ili promena strukture tabele.
Tabelska brana se može dalje podeliti na deljenu branu i ekskluzivnu branicu. Deljena brana dozvoljava više transakcija istovremeno čitanje tabele, ali ne dozvoljava pisanje.
LOCK TABLES table_name READ; -- eksplicitno dodaj čitačku branicu
SELECT * FROM table_name; -- druge sesije mogu čitati, ne mogu pisati
UNLOCK TABLES; -- oslobodi branicuEkskluzivna brana dozvoljava samo jednoj transakciji da piše, druge transakcije ne mogu čitati ni pisati.
LOCK TABLES table_name WRITE; -- eksplicitno dodaj pisaću branicu
INSERT/UPDATE/DELETE table_name; -- čitanje i pisanje drugih sesija je blokirano
UNLOCK TABLES;MyISAM automatski dodaje čitačku branicu pri izvršenju SELECT, a pisaću branicu pri izvršenju INSERT/UPDATE/DELETE.
Za InnoDB motor, UPDATE/DELETE bez indeksa može dovesti do degradacije bravery u tabelsku bravu.
UPDATE innodb_table SET name='new' WHERE name='old'; -- puno skeniranje tabele, degradira se u tabelsku bravuPri izvršenju ALTER TABLE automatski se dodaje tabelska brana, blokira sve operacije čitanja i pisanja.
- Java vodič za intervju (plaćeni) sadrži originalno pitanje Meituan 3 Java backend tehnička prva runda: globalna brana baze podataka, tabelska brana, brana nivoa reda, koji su scenariji upotrebe svake bravery
- Java vodič za intervju (plaćeni) sadrži originalno pitanje kolega 30 Tencent Mjuzik intervju: koliko vrsta tabelskih bravery ima mysql
55.🌞 Pričaj o MySQL red-brani?
Red-brana je brana najfinje granularnosti u InnoDB motoru skladištenja, ona zaključava jedan red u tabeli, dozvoljava drugim transakcijama da pristupaju drugim redovima u tabeli.
Na donjem nivou se realizuje zaključavanjem indeksa, to znači da samo kroz uslove indeksa pretražuju podatke, InnoDB može koristiti bravu nivoa reda, inače će degradirati u tabelsku bravu.
Red-brana se može dalje podeliti na tri forme: bravu zapisa, bravu razmaka i branu sledećeg ključa. Putem SELECT ... FOR UPDATE može se dodati ekskluzivna brana.
START TRANSACTION;
-- dodaj ekskluzivnu bravu, zaključaj određeni red
SELECT * FROM your_table WHERE id = 1 FOR UPDATE;
-- operiši nad ovim redom
UPDATE your_table SET column1 = 'new_value' WHERE id = 1;
COMMIT;Putem SELECT ...LOCK IN SHARE MODE može se dodati deljena brana.
START TRANSACTION;
-- dodaj deljenu bravu, zaključaj određeni red
SELECT * FROM your_table WHERE id = 1 LOCK IN SHARE MODE;
-- samo može čitati ovaj red, ne može se menjati
COMMIT;Na šta treba paziti pri select for update?
Prvo, mora se koristiti u transakciji, inače će se brana odmah osloboditi.
START TRANSACTION;
SELECT * FROM your_table WHERE id = 1 FOR UPDATE;
-- operiši nad ovim redom
COMMIT;Drugo, prilikom korišćenja mora se obratiti pažnja da li pogodi indeks, inače može zaključati celu tabelu.
-- name nema indeks, degradiraće se u tabelsku bravu
SELECT * FROM user WHERE name = 'Wang Er' FOR UPDATE;---- ovo deo vam pomaže da razumete start, na intervjuu nije neophodno zapamtiti ----
Pretpostavimo da imamo tabelu orders, sledećih podataka:
CREATE TABLE orders (
id INT PRIMARY KEY,
order_no VARCHAR(255),
amount DECIMAL(10,2),
status VARCHAR(50),
INDEX (order_no) -- order_no ima indeks
);Podaci u tabeli su sledeći:
| id | order_no | amount | status |
|---|---|---|---|
| 1 | 10001 | 50.00 | pending |
| 2 | 10002 | 75.00 | pending |
| 3 | 10003 | 100.00 | pending |
| 4 | 10004 | 150.00 | completed |
| 5 | 10005 | 200.00 | pending |
Ako izvršimo SELECT FOR UPDATE kroz indeks primarnog ključa, zaista će zaključati samo određeni red:
START TRANSACTION;
SELECT * FROM orders WHERE id = 1 FOR UPDATE;
-- operiši nad redom id=1
COMMIT;Jer je id primarni ključ, tako da će zaključati samo red id=1, neće uticati na operacije nad drugim redovima. Druge transakcije i dalje mogu izvršiti operacije ažuriranja nad id = 2, 3, 4, 5 itd. redovima, jer nisu zaključani.
Ako koristimo obični indeks order_no da izvršimo SELECT FOR UPDATE, takođe će zaključati samo određeni red:
START TRANSACTION;
SELECT * FROM orders WHERE order_no = '10001' FOR UPDATE;
-- operiši nad redom order_no=10001
COMMIT;Jer je order_no jedinstveni indeks, tako da će zaključati samo red order_no=10001, neće uticati na operacije nad drugim redovima.
Ali ako je WHERE uslov status='pending', a status nema indeks:
START TRANSACTION;
SELECT * FROM orders WHERE status = 'pending' FOR UPDATE;
-- operiši nad redovima status=pending
COMMIT;će degradirati u tabelsku bravu, jer u ovom slučaju MySQL treba da skenira celu tabelu i proveri status svakog reda.
---- ovo deo vam pomaže da razumete end, na intervjuu nije neophodno zapamtiti ----
memo: 9. aprila 2025. izmenjeno do ovde, danas jeprijatelj dao povratnu informaciju rekao, dobio offer za letnju praksu u Meituan-u, i posebno se zahvatio na "pobedu nad testovima", reputacija+1.
Pričaj o brani zapisa?
Brana zapisa je osnovni oblik red-brane, kada koristimo jedinstveni indeks ili indeks primarnog ključa za upit jednakosti, MySQL će automatski dodati ekskluzivnu bravu za taj zapis, zabranjujući drugim transakcijama čitanje ili izmenu zaključanog zapisa.
Na primer:
SELECT * FROM table WHERE id = 1 FOR UPDATE; -- dodaj X-branicu
UPDATE table SET name = 'Wang Er' WHERE id = 1; -- implicitno dodaj X-branicuZnate li branu razmaka? (dopuna)
Dodato 15. decembra 2024.
Brana razmaka se koristi da zaključa "razmak" između zapisa prilikom opsežnog upita, sprečava da druge transakcije ubace nove zapise u ovom opsegu. Efikasna samo u nivou izolacije ponovljivog čitanja i iznad, uglavnom koristi za sprečavanje fantomskog čitanja.
---- ovo deo vam pomaže da razumete start, na intervjuu nije neophodno zapamtiti ----
Na primer, transakcija A zaključa interval (1000,2000), sprečiće transakciju B da ubaci nove zapise u ovom intervalu:
-- transakcijaA
BEGIN;
SELECT * FROM orders WHERE amount BETWEEN 1000 AND 2000 FOR UPDATE;
-- transakcijaB pokušaj ubaciti će biti blokirana
INSERT INTO orders VALUES(null,1500,'pending'); -- blokiranoPretpostavimo da tabelu test_gaplock ima tri polja id, age, name, gde je id primarni ključ, na age postoji indeks, i ubačena su 4 zapisa.
CREATE TABLE `test_gaplock` (
`id` int(11) NOT NULL,
`age` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `age` (`age`)
) ENGINE=InnoDB;
insert into test_gaplock values(1,1,'Zhang San'),(6,6,'Wu Lao Er'),(8,8,'Zhao Si'),(12,12,'Xiong Da');Brana razmaka će zaključati:
(−∞, 1): razmak pre najmanjeg zapisa.(1, 6),(6, 8),(8, 12): razmak između zapisa.(12, +∞): razmak posle najvećeg zapisa.
Pretpostavimo da postoje dve transakcije, T1 izvršava sledeću izjavu:
START TRANSACTION;
SELECT * FROM test_gaplock WHERE age > 5 FOR UPDATE;T2 izvršava sledeću izjavu:
START TRANSACTION;
INSERT INTO test_gaplock VALUES (7, 7, 'Wang Wu');T1 će zaključati razmak (6, 8), sprečavajući druge transakcije da ubace nove zapise u ovom opsegu.
T2 pri ubacivanju (7, 7, 'Wang Wu') će biti blokirana, može se u drugoj sesiji izvršiti SHOW ENGINE INNODB STATUS da bi se videla informacija o brani razmaka.
Preporučeno čitanje: šest slučajeva za razumeti branu razmaka,mehanizam zaključavanja bravery razmaka u MySQL-u
---- ovo deo vam pomaže da razumete end, na intervjuu nije neophodno zapamtiti ----
Koje komande će dodati branu razmaka?
U nivou izolacije ponovljivog čitanja, izvršenje izjava zaključavanja kao FOR UPDATE / LOCK IN SHARE MODE, i uslov upita je opsežni upit, automatski će dodati branu razmaka.
-- SELECT ... FOR UPDATE + opsežni upit
SELECT * FROM user WHERE score > 100 FOR UPDATE;
-- SELECT ... LOCK IN SHARE MODE + opsežni upit
SELECT * FROM user WHERE id BETWEEN 10 AND 20 LOCK IN SHARE MODE;
-- UPDATE/DELETE + opsežni upit
DELETE FROM user WHERE score < 50;
- Java vodič za intervju (plaćeni) sadrži originalno pitanje Ali Cloud 22 intervju: koje mysql komande će dodati branu razmaka
56. Znate li branu sledećeg ključa?
Brana sledećeg ključa je kombinacija bravery zapisa i bravery razmaka, zaključava zapise indeksa i razmak između zapisa indeksa.
Razlika u odnosu na branu razmaka je u tome što je razmak bravery sledećeg ključa levi-otvoren-desni-zatvoren interval. Na primer (1,3] znači zaključavanje svih zapisa većih od 1 i manjih ili jednakih 3.
Kada InnoDB izvrši opsežni upit, koristiće branu sledećeg ključa da zaključa podatke redova koji zadovoljavaju uslove i razmak u ovom opsegu.
Na primer, sledeća izjava će zaključati sve zapise gde je id između 5 i 10, i razmak između ovih zapisa.
SELECT * FROM table WHERE id BETWEEN 5 AND 10 FOR UPDATE;Podrazumevana vrsta bravery redova u MySQL-u je brana sledećeg ključa. Kada se koristi upit jednakosti jedinstvenog indeksa i podudari sa jednim zapisa, brana sledećeg ključa će degradirati u bravu zapisa; ako se ne podudari ni sa jednim zapisom, degradiraće se u branu razmaka.
memo: 10. aprila 2025. izmenjeno do ovde, danasje prijatelj s fakultetskom pripremom dao povratnu informaciju, dobio sp offer u Didi-jem, zaista neponovljivo, previše se utrkuje.
57. Da li znaš šta je namerska brana?
Namerska brana je vrsta tabela-brane, pokazuje da transakcija namjerava da zaključa određene redove podatke u tabeli, ali neće direktno zaključati same redove podataka.
InnoDB je automatski upravlja, kada transakcija treba da doda brangu redova, prvo će dodati namersku brangu na tabeli. Tako prilikom dodavanja bravery tabele, može se brzo suditi da li postoji konflikt pregledavanjem namerske bravery na tabeli, bez potrebe za proverom red po red, čime se povećava efikasnost zaključavanja.
Kada se izvršava SELECT ... LOCK IN SHARE MODE, automatski se dodaje namerska deljena brana; kada se izvršava SELECT ... FOR UPDATE, automatski se dodaje namerska ekskluzivna brana.
Namerske bravery su međusobno kompatibilne, takođe neće biti u konfliktu sa bravery redova.
| kompatibilnost | namerska deljena brana | namerska ekskluzivna brana | deljena brana (tabelska) | ekskluzivna brana (tabelska) |
|---|---|---|---|---|
| namerska deljena brana | kompatibilna | kompatibilna | kompatibilna | konflikt |
| namerska ekskluzivna brana | kompatibilna | kompatibilna | konflikt | konflikt |
| S-brana | kompatibilna | konflikt | kompatibilna | konflikt |
| X-brana | konflikt | konflikt | konflikt | konflikt |
Koji je smisao namerske bravery?
Bez namerske bravery, kada transakcija A drži bravery određenih redova tabele, ako transakcija B želi dodati brangu tabele, InnoDB mora proveriti da li je svaki red u tabeli zaključan, ovaj metod punog skeniranja tabele je vrlo neefikasan.
Sa namerskom brangu, transakcija pre dodavanja bravery redova prvo doda odgovarajuću namersku brangu na tabeli; druge transakcije prilikom dodavanja bravery tabele samo treba proveriti namersku brangu na tabeli, nije neophodno proveravati red po red.
-- transakcijaA dobija ekskluzivnu brangu određenog reda
BEGIN;
SELECT * FROM users WHERE id = 6 FOR UPDATE; -- automatski dodaj IX-branu i X-branu reda
-- transakcijaB pokušava dodati brangu tabele
LOCK TABLES users READ; -- otkriva da na tabeli postoji IX-brana, u konfliktu je sa S-branom, direktno se blokira bez potrebe za punim skeniranjem tabelememo: 11. aprila 2025. izmenjeno do ovde, danasprijatelj koji je dobio offer u Didi-jem dao povratnu informaciju, MQ deo uglavnom gleda "pobedu nad testovima", prilično kompletno, eto, reputacija je došla.
58.🌞 Znate li MySQL optimističku i pesimističku bravu?
Pesimistička brana je konzervativna strategija "prvo zaključaj pa operiši", pretpostavlja da će pristup podacima spolja neminovno dovesti do konflikta, stoga tokom procesiranja podataka celokupno vreme je zaključano, osiguravajući da istovremeno samo jedan nit može pristupiti podacima.
Brana redova i tabela u MySQL-u su pesimističke bravery.
Optimistička brana pretpostavlja da konkurentne operacije neće uvek dovesti do konflikta, pripada maloj verovatnoći, stoga se ne zaključava prilikom čitanja podataka, već prikazuje ažuriranja tek proverava da li su podaci modifikovani od strane drugih transakcija.
Optimistička brana nije ugrađeni mehanizam bravery MySQL-a, već se realizuje kroz logiku programa, česti načini realizacije su mehanizam broja verzije i mehanizam vremenske oznake. Realizuje se dodavanjem polja version ili timestamp polja u tabelu.
---- ovo deo vam pomaže da razumete start, na intervjuu nije neophodno zapamtiti ----
Kada transakcija A već zaključa, transakcija B će neprekidno čekati da transakcija A oslobodi branicu; ako transakcija A dugo ne oslobodi branicu, transakcija B će prijaviti grešku Lock wait timeout exceeded; try restarting transaction.
Transakcija A i transakcija B istovremeno čitaju podatke istog primarnog ključa ID, broj verzije je 0; transakcija A koristi broj verzije (version=1) kao uslov za ažuriranje podataka, istovremeno broj verzije +1; transakcija B takođe koristi version=1 kao uslov ažuriranja, uočava da se broj verzije ne podudara, ažuriranje neuspešno.
---- ovo deo vam pomaže da razumete end, na intervjuu nije neophodno zapamtiti ----
Kako rešiti problem prekomernog prodaje zaliha kroz pesimističku i optimističku bravu?
Pesimistička brana kroz SELECT ... FOR UPDATE direktno zaključava zapise prilikom upita, osiguravajući da druge transakcije moraju čekati da se trenutna transakcija završi pre operacije nad redom podataka.
BEGIN;
-- dodaj ekskluzivnu brangu na zapis proizvoda id=1
SELECT stock FROM products WHERE id=1 FOR UPDATE;
-- generiši narudžbinu
INSERT INTO orders (user_id, product_id) VALUES (123, 1);
-- umanji zalihe
UPDATE products SET stock=stock-1 WHERE id=1;
COMMIT;Optimistička brana dodaje polje version u tabelu kao uslov sudije.
-- pretraži informacije proizvoda, dobij broj verzije
SELECT stock, version FROM products WHERE id=1;
-- prilikom ažuriranja zaliha proveri broj verzije
UPDATE products
SET stock=stock-1, version=version+1
WHERE id=1 AND version=stari broj verzije;---- ovo deo vam pomaže da razumete start, na intervjuu nije neophodno zapamtiti ----
Prekomerna prodaja zaliha je vrlo klasičan problem:
- Transakcija A pretražuje zalihe proizvoda, dobija vrednost zaliha 1
- Transakcija B takođe pretražuje zalihe istog proizvoda, takođe dobija vrednost zaliha 1
- Transakcija A na osnovu rezultata pretrage izvršava umanjenje zaliha, ažurira zalihe na 0
- Transakcija B takođe izvršava umanjenje zaliha, ažurira zalihe na -1
Ključne tačke pesimističke bravery:
- Mora se izvršiti u jednoj transakciji;
- Kroz
SELECT ... FOR UPDATEzaključaj red, osiguravajući da druge transakcije moraju čekati da se trenutna transakcija završi pre operacije nad redom podataka; - Ne zaboravite dodati indeks za uslove pretrage, izbegnite puno skeniranje tabele što dovodi do degradacije bravery u brangu tabele.
Ključne tačke optimističke bravery:
- Dodajte polje version u tabelu;
- Prilikom pretrage dobijte tekući broj verzije;
- Prilikom ažuriranja proverite da li se broj verzije promenio.
Kompletan kod primera Java programa:
@Service
public class ProductService {
@Autowired
private ProductMapper productMapper;
@Transactional
public boolean purchaseWithOptimisticLock(Long productId, int quantity) {
int retryCount = 0;
while(retryCount < 3) { // maksimalni broj pokušaja
Product product = productMapper.selectById(productId);
if(product.getStock() < quantity) {
return false; // zalihe nedovoljne
}
int updated = productMapper.reduceStockWithVersion(
productId, quantity, product.getVersion());
if(updated > 0) {
return true; // ažuriranje uspešno
}
retryCount++;
}
return false; // ažuriranje neuspešno
}
}Odgovarajući mapper:
@Update("UPDATE products SET stock=stock-#{quantity}, version=version+1 " +
"WHERE id=#{productId} AND version=#{version}")
int reduceStockWithVersion(@Param("productId") Long productId,
@Param("quantity") int quantity,
@Param("version") int version);Optimistička brana realizovana mehanizmom vremenske oznake:
UPDATE products SET stock=stock-1, update_time=NOW()
WHERE id=1 AND update_time=stara vremenska oznaka;Oba načina treba da garantuju atomičnost operacija, potrebno je staviti više SQL izjava u istu transakciju za izvršenje.
Preporučeno čitanje: Mu Xiao Nong: pesimistička i optimistička brana
---- ovo deo vam pomaže da razumete end, na intervjuu nije neophodno zapamtiti ----
- Java vodič za intervju (plaćeni) sadrži originalno pitanje zbirke malih kompanija 1 Java backend intervju: optimistička i pesimistička brana, uzrok i rešenje problema prekomernog prodaje zaliha?
- Java vodič za intervju (plaćeni) sadrži originalno pitanje JD 5 Java backend tehnička prva runda: optimistička i pesimistička brana
memo: 12. aprila 2025. izmenjeno do ovde, danasje prijatelj dao povratnu informaciju rekao, ušao je u HR intervju JD-a, ali dodao jedan intervju VP nivoa, zamenik direktora, čekamo njgave dobre vesti. Naravno, i dalje nije zaboravio da zahvali "pobedi nad testovima" za pomoć, haha.
59. Da li ste imali problema sa MySQL mrtvom bravom, kako ste rešili?
Imao sam. MySQL mrtva brana je uzrokovana time što više transakcija drži resurse i međusobno čekaju. Rešio sam kroz SHOW ENGINE INNODB STATUS pregledao informacije o mrtvoj brani, locirao da je uzrokovano nekonzistentnim redosledom zaključavanja, i konačno rešio problem prilagođavanjem redosleda zaključavanja.
Na primer, u projektuTehničke pi, dve transakcije posebno ažuriraju dve tabele, ali je redosled ažuriranja nekonzistentan.
-- kreiraj tabelu/ubaci podatke
CREATE TABLE account (
id INT AUTO_INCREMENT PRIMARY KEY,
balance INT NOT NULL
);
INSERT INTO account (balance) VALUES (100), (200);
-- transakcija 1
START TRANSACTION;
-- zaključaj red id=1
UPDATE account SET balance = balance - 10 WHERE id = 1;
-- čekaj zaključavanje reda id=2 (transakcija 2 je već zaključala)
UPDATE account SET balance = balance + 10 WHERE id = 2;
-- transakcija 2
START TRANSACTION;
-- zaključaj red id=2
UPDATE account SET balance = balance - 10 WHERE id = 2;
-- čekaj zaključavanje reda id=1 (transakcija 1 je već zaključala)
UPDATE account SET balance = balance + 10 WHERE id = 1;Pristup istim resursima, ali različitim redosledom, će dovesti do mrtve bravery.
Rešenje je vrlo jednostavno, prvo koristi SHOW ENGINE INNODB STATUS\G; potvrdi specifične informacije o mrtvoj brani, a zatim prilagodi redosled pristupa resursima.
- Java vodič za intervju (plaćeni) sadrži originalno pitanje Shopee 13 prva runda: da li ste imali mysql mrtvu branicu ili nebezbedne podatke
Transakcije
60.🌞 Pričaj o četiri glavne karakteristike MySQL transakcija?
Transakcija je jedinica izvršenja sastavljena od jedne ili više SQL izjava. Četiri karakteristike su atomičnost, konzistentnost, izolacija i trajnost. Atomičnost garantuje da će se sve operacije u transakciji ili izvršiti u potpunosti ili uopšte ne izvršiti; konzistentnost garantuje da će podaci preći iz jednog konzistentnog stanja pre početka transakcije u drugo konzistentno stanje posle završetka; izolacija garantuje da konkurentne transakcije ne mešaju jedna sa drugom; trajnost garantuje da podaci neće biti izgubljeni nakon potvrde transakcije.
Detaljno pričaj o atomičnosti?
Atomičnost znači da se sve operacije u transakciji moraju u potpunosti završiti ili uopšte ne završiti, ona je nedeljiva jedinica. Ako bilo koja operacija u transakciji ne uspe, cela transakcija će se vratiti u stanje pre početka transakcije, kao da se te operacije nikada nisu izvršile.
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
-- ako druga izjava ne uspe, prva će takođe biti vraćena
COMMIT;Skraćeni odgovor: atomičnost zahteva da se sve operacije transakcije moraju u potpunosti potvrditi uspešno ili u potpunosti vratiti neuspešno, ne može se izvršiti samo deo operacija u jednoj transakciji.
Detaljno pričaj o konzistentnosti?
Konzistentnost osigurava da transakcija prelazi iz jednog konzistentnog stanja u drugo konzistentno stanje.
Na primer u transakciji bankovnog transfera, bez obzira na šta se dogodi, ukupan iznos dva naloga treba da ostane nepromenjen pre i posle transfera. Ako se A nalog (100) prebaci 10 na B nalog (10), bez obzira na uspeh ili neuspeh, ukupan iznos A i B je i dalje 110.
-- pretpostavimo da je saldo A naloga 100, B naloga 10
-- stanje pre transfera
SELECT balance FROM accounts WHERE user_id = 'A'; -- 100
SELECT balance FROM accounts WHERE user_id = 'B'; -- 10
-- operacija transfera
START TRANSACTION;
UPDATE accounts SET balance = balance - 10 WHERE user_id = 'A';
UPDATE accounts SET balance = balance + 10 WHERE user_id = 'B';
COMMIT;
-- stanje posle transfera
SELECT balance FROM accounts WHERE user_id = 'A'; -- 90
SELECT balance FROM accounts WHERE user_id = 'B'; -- 20`
-- ukupan iznos je i dalje 110Skraćeni odgovor: konzistentnost osigurava da stanje podataka prelazi iz jednog konzistentnog stanja u drugo konzistentno stanje. Konzistentnost je povezana sa poslovnim pravilima, na primer bankovni transfer, bez obzira na uspeh ili neuspeh transakcije, ukupna suma obe strane treba da bude nepromenjena.
Detaljno pričaj o izolaciji?
Izolacija znači da se konkurentne transakcije međusobno izoliraju, izvršenje jedne transakcije neće biti ometeno drugim transakcijama. Transakcije ne mešaju jedna sa drugima.
Izolacija uglavnom služi za rešavanje problema prljavog čitanja, neponovljivog čitanja, fantomskog čitanja i drugih problema koji mogu nastati prilikom konkurentnog izvršenja transakcija.
---- ovaj deo pomaže svima da razumeju start, na intervjuu se ne mora napamet ----
Na primer, pod nivom izolacije čitanja nepodnetih podataka, pojaviće se fenomen prljavog čitanja: transakcija C čita modifikovane podatke transakcije B koje još nisu potvrđene. Ako transakcija B na kraju vrati unazad (rollback), podaci koje je pročitala transakcija C su nevažeći "prljavi podaci".
-- sesija A
-- kreiranje testne tabele za simulaciju konkurentnosti
DROP TABLE IF EXISTS accounts;
CREATE TABLE accounts (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50),
balance DECIMAL(10,2)
);
-- ubacivanje testnih podataka
INSERT INTO accounts (name, balance) VALUES
('Wang Er', 1000.00),
('Zhang San', 2000.00),
('Li Si', 3000.00);
-- u sesiji B, podesiti nivo izolacije na čitanje nepodnetih
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
-- u sesiji B ažurirati podatke ali ne potvrditi
UPDATE accounts SET balance = balance - 500 WHERE name='Wang Er';
-- sesija C je na nivou čitanja nepodnetih, čita podatke, dobija 500
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM accounts WHERE name='Wang Er';
-- nastaviti druge operacije, na osnovu 500
-- transakcija sesije B se vraća unazad, što dovodi do toga da sesija A čita prljave podatke
ROLLBACK;
Nadgradnjom nivoa izolacije na čitanje potvrđenih može se rešiti problem prljavog čitanja.
-- sesija B menja na čitanje potvrđenih
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- izvršiti prvi upit 1000
SELECT * FROM accounts WHERE name='Wang Er';
-- u sesiji C, podesiti nivo izolacije na čitanje potvrđenih
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- u sesiji C ažurirati podatke ali ne potvrditi
START TRANSACTION;
UPDATE accounts SET balance = balance + 200 WHERE name='Wang Er';
-- u sesiji B ponovo čitati podatke, rezultat je i dalje 1000
SELECT * FROM accounts WHERE name='Wang Er';
-- u sesiji C vratiti transakciju unazad
ROLLBACK;
-- u sesiji B ponovo čitati podatke, rezultat je i dalje 1000
SELECT * FROM accounts WHERE name='Wang Er';
Ali će se pojaviti problem neponovljivog čitanja: transakcija B prvi put čita određeni red podatka sa vrednošću X, između toga transakcija C modifikuje taj podatak na Y i potvrdi, transakcija B pri ponovnom čitanju otkriva da se vrednost promenila na Y, što dovodi do nepodudaranja rezultata dva čitanja.
-- sesija B menja na čitanje potvrđenih
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- izvršiti prvi upit 1000
START TRANSACTION;
SELECT * FROM accounts WHERE name='Wang Er';
-- u sesiji C, podesiti nivo izolacije na čitanje potvrđenih
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- u sesiji C ažurirati podatke i potvrditi
START TRANSACTION;
UPDATE accounts SET balance = balance + 200 WHERE name='Wang Er';
-- sesija C potvrđuje transakciju
COMMIT;
-- u sesiji B ponovo čitati podatke, rezultat je 1200
SELECT * FROM accounts WHERE name='Wang Er';
Može se rešiti problem neponovljivog čitanja nadogradnjom nivoa izolacije na ponovljivo čitanje.
-- sesija B menja na ponovljivo čitanje
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- započeti transakciju i izvršiti prvi upit 1000
START TRANSACTION;
SELECT * FROM accounts WHERE name='Wang Er';
-- u sesiji C, podesiti nivo izolacije na ponovljivo čitanje
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- u sesiji C ažurirati podatke i potvrditi
START TRANSACTION;
UPDATE accounts SET balance = balance + 200 WHERE name='Wang Er';
-- sesija C potvrđuje transakciju
COMMIT;
-- u sesiji B ponovo čitati podatke, rezultat je i dalje 1000
SELECT * FROM accounts WHERE name='Wang Er';
Ali pod nivoom ponovljivog čitanja i dalje može doći do problema fantomskog čitanja: transakcija B prvi put dobija 2 zapisa podataka, nakon što transakcija C doda 1 zapis podataka i potvrdi, transakcija B pri ponovnom upitu i dalje ima 2 zapisa, ali može ažurirati novo dodate podatke, pri ponovnom upitu otkriva da ima 3 zapisa.
-- sesija B menja na ponovljivo čitanje
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- izvršiti prvi upit, pronađeno 2 zapisa
START TRANSACTION;
SELECT * FROM accounts WHERE balance > 1000;
-- u sesiji C, podesiti nivo izolacije na ponovljivo čitanje
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- u sesiji C dodati podatke i potvrditi
START TRANSACTION;
INSERT INTO accounts (name, balance) VALUES ('Wang Wu', 4000);
-- sesija C potvrđuje transakciju
COMMIT;
-- u sesiji B ponovo čitati podatke, rezultat je i dalje 2 zapisa
SELECT * FROM accounts WHERE balance > 1000;
-- u sesiji B pokušati ažurirati Wang Wu-ov balans na 5000, uspeva
UPDATE accounts SET balance = 5000 WHERE name='Wang Wu';
-- u sesiji B ponovo čitati podatke, otkriva 3 zapisa
SELECT * FROM accounts WHERE balance > 1000;
Može se rešiti problem fantomskog čitanja nadogradnjom nivoa izolacije na serijalizaciju.
-- sesija B menja na serijalizaciju
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- izvršiti prvi upit, pronađeno 2 zapisa
START TRANSACTION;
SELECT * FROM accounts WHERE balance > 1000;
-- u sesiji C, podesiti nivo izolacije na serijalizaciju
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- u sesiji C dodati podatke, blokiraće se
START TRANSACTION;
INSERT INTO accounts (name, balance) VALUES ('Wang Wu', 4000);
-- samo kada sesija B potvrdi transakciju sesija C će nastaviti i potvrditi transakciju
COMMIT;
| Nivo izolacije | Da li dolazi do prljavog čitanja | Da li dolazi do neponovljivog čitanja | Da li dolazi do fantomskog čitanja |
|---|---|---|---|
| Read Uncommitted (Čitanje nepodnetih) | ✅ Moguće | ✅ Moguće | ✅ Moguće |
| Read Committed (Čitanje potvrđenih) | ❌ Ne dolazi | ✅ Moguće | ✅ Moguće |
| Repeatable Read (Ponovljivo čitanje) | ❌ Ne dolazi | ❌ Ne dolazi | ✅ Moguće (ali InnoDB je rešio) |
| Serializable (Serijalizacija) | ❌ Ne dolazi | ❌ Ne dolazi | ❌ Ne dolazi |
---- ovaj deo pomaže svima da razumeju end, na intervjuu se ne mora napamet ----
Kratak odgovor: više konkurentnih transakcija mora biti međusobno izolovano, odnosno izvršavanje jedne transakcije ne sme biti ometano drugim transakcijama.
Detaljno objašnjenje trajnosti?
Trajnost osigurava da jednom kada je transakcija potvrđena, njene izmene podataka su trajne, čak i ako dođe do rušenja sistema, podaci se mogu vratiti u stanje poslednje potvrde.
MySQL-ova trajnost se realizuje kroz redo log pogona InnoDB. U trenutku potvrde transakcije, InnoDB će prvo zapisati operacije izmene u redo log i upisati na disk. Nakon rušenja, InnoDB će vratiti podatke kroz redo log, čime osigurava da uspešno potvrđeni podaci transakcije neće biti izgubljeni.
Kratak odgovor: jednom kada je transakcija potvrđena, njene izmene se trajno čuvaju u MySQL-u. Čak i ako dođe do rušenja sistema, izmenjeni podaci neće biti izgubljeni.
61.ACID šta garantuje?
Jednom rečenicom sažeto:
ACID svojstva atomičnosti se uglavnom realizuju kroz Undo Log, trajnost kroz Redo Log, izolaciju kroz MVCC i mehanizam zaključavanja, a konzistentnost je zajednički garantovana kroz tri druge glavne karakteristike.
Detaljno kako se garantuje atomičnost?
Pre nego što transakcija modifikuje podatke, snima se snapshot u Undo Log, ako se bilo koji korak u transakciji ne uspešno izvrši, sistem će pročitati Undo Log i vratiti sve operacije unazad, povratiti u stanje pre početka transakcije, time garantujući da se transakcija ili uspešno izvrši u potpunosti ili potpuno ne uspe.
1)BEGIN;
2)UPDATE user SET balance = balance - 100 WHERE id = 1;
=> upis u Undo Log: zabilježeni originalni balans id=1 je 500
3)UPDATE user SET balance = balance + 100 WHERE id = 2;
=> upis u Undo Log: zabilježeni originalni balans id=2 je 300
4)COMMIT;
=> očistiti Undo Log, transakcija uspešna
❗ako ne uspe:
=> izvršiti ROLLBACK: vratiti podatke na osnovu Undo Log-a!Preporučeno čitanje: UNDO LOG InnoDB analiza
Detaljno kako se garantuje trajnost?
MySQL-ova trajnost se uglavnom zajednički garantuje kroz prethodno pisanje Redo Log, mehanizam dvostrukog pisanja, dvoetapno potvrđivanje i mehanizam upisivanja Checkpoint na disk.
Kada se transakcija potvrdi, MySQL će prvo zapisati operacije izmene transakcije u Redo Log i prisilno upisati na disk, a zatim upisati stranice podataka iz memorije na disk. Tako čak i ako dođe do rušenja sistema, nakon ponovnog pokretanja može se vratiti podatke ponovnim izvršavanjem Redo Log.
Pri upisivanju stranica podataka na disk, ako dođe do rušenja, može doći do nepotpunosti stranica podataka. Veličina InnoDB stranice podataka je 16KB, obično veća od 4KB veličine stranice operativnog sistema.
Da bi se rešio problem delimičnog upisa, MySQL koristi mehanizam dvostrukog pisanja, pri upisivanju prljavih stranica na disk, prvo se upisuju stranice podataka u dvostruki bafer, 2M neprekinutog prostora, a zatim se upisuju na stvarnu lokaciju na disku.
Pri povratku nakon rušenja, ako se uoči da stranica podataka nije potpuna, vraća se kopija iz dvostrukog bafera, osiguravajući potpunost stranice podataka.
U scenarijima gde je uključena glavno-podrepljena replikacija, MySQL kroz dvoetapno potvrđivanje garantuje konzistentnost Redo Log i Binlog: prva faza, upis Redo Log i označavanje kao prepare stanje; druga faza, upis Binlog i potvrđivanje Redo Log kao commit stanje.
Pri povratku nakon rušenja, ako se uoči da je Redo Log u prepare stanju ali Binlog je potpun, potvrdiće se transakcija; u suprotnom će se vratiti unazad, izbegavajući neusklađenost glavne i podrepljenih baza.
Pored toga, zbog ograničenog kapaciteta Redo Log, mehanizam Checkpoint će periodično upisivati prljave stranice iz memorije na disk, čime se smanjuje broj Redo Log zapisa koje treba obraditi pri povratu nakon rušenja.
Preporučeno čitanje: Detaljna analiza MySQL dvostrukog bafera, MySQL dvoetapno potvrđivanje transakcija
Detaljno kako se garantuje izolacija?
Izolacija se uglavnom realizuje kroz mehanizam zaključavanja i MVCC.
Na primer, kada jedna transakcija modifikuje određeni podatak, MySQL će kroz next-key lock sprečiti da druge transakcije istovremeno modifikuju, izbegavajući konflikte podataka.
Istovremeno, next-key lock može sprečiti pojavu fantomskog čitanja. Na primer, ako transakcija A upita id > 10 zapise, tada next-key lock neće samo zaključati red id=10, već će zaključati i "razmak" iza 10, sprečavajući druge transakcije da ubace podatke sa id=15.
Ako primarni ključ u tabeli ima id: 5, 10, 15, 20, 25, tada će InnoDB zaključati sledeće intervale i zapise:
| Zaključani objekat | Tip | Značenje zaključavanja |
|---|---|---|
(10, 15] | Next-key lock | Zaključava id=15 i prednji razmak, sprečava ubacivanje 11~14 |
(15, 20] | Next-key lock | Zaključava id=20 i prednji razmak |
(20, 25] | Next-key lock | Zaključava id=25 i prednji razmak |
(25, +∞) | Gap lock | Zaključava kraj, sprečava ubacivanje 30 itd. |
MVCC se uglavnom koristi za optimizaciju operacija čitanja, kroz čuvanje istorijskih verzija podataka, omogućavajući operacijama čitanja da čitaju snapshot bez zaključavanja, poboljšavajući performanse konkurentnog čitanja.
Različiti nivoi izolacije odgovaraju različitim strategijama implementacije, na primer pod nivoom izolacije ponovljivog čitanja, transakcija će prvi put pri upitu generisati Read View, kasnija sva čitanja koriste taj view, garantirajući konzistentnost rezultata višestrukog čitanja.
Kako se garantuje konzistentnost?
MySQL-ova konzistentnost nije zagarantovana odvojeno jednim mehanizmom, već je rezultat zajedničkog delovanja atomičnosti, izolacije i trajnosti.
Da li se transakcija automatski potvrđuje?
Da, MySQL podrazumevano uključuje režim automatskog potvrđivanja transakcija.
Svaka zasebna SQL naredba smatra se nezavisnom jedinicom procesiranja transakcije; nakon uspešnog izvršenja SQL naredbe automatski se izvršava COMMIT; pri neuspehu se automatski izvršava ROLLBACK.
Može se proveriti trenutno stanje automatskog potvrđivanja sesije kroz SELECT @@autocommit;.
Ako je potrebno izvršiti više SQL naredbi, mogu se staviti u jednu transakciju, koristeći START TRANSACTION za pokretanje transakcije, nakon izvršenja svih SQL naredbi ručno potvrditi.
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
COMMIT;62.🌟Koji su nivoi izolacije transakcija?
Nivo izolacije definiše stepen uticaja drugih transakcija na jednu transakciju, MySQL podržava četiri nivoa izolacije: čitanje nepodnetih, čitanje potvrđenih, ponovljivo čitanje i serijalizacija.
Čitanje nepodnetih dovodi do prljavog čitanja, čitanje potvrđenih dovodi do neponovljivog čitanja, ponovljivo čitanje je podrazumevani nivo izolacije InnoDB, može izbeći prljavo čitanje i neponovljivo čitanje, ali dovodi do fantomskog čitanja. Međutim, kroz MVCC i next-key lock može se sprečiti većina problema konkurentnosti.
Serijalizacija je najsigurnija, ali sa lošijim performansama, obično se ne preporučuje za korišćenje.
Detaljno objašnjenje čitanja nepodnetih?
Transakcija može čitati podatke modifikovane od strane drugih nepotvrđenih transakcija. To znači, ako se nepotvrđena transakcija vrati unazad, pročitani podaci postaju "prljavi podaci", obično se ne koristi.
Šta je čitanje potvrđenih?
Čitanje potvrđenih izbegava prljavo čitanje, ali može doći do neponovljivog čitanja, odnosno višestruko čitanje istih podataka u istoj transakciji daje različite rezultate, jer su izmene potvrđene od strane drugih transakcija vidljive trenutnoj transakciji.
To je podrazumevani nivo izolacije za Oracle, SQL Server i druge baze podataka.
Šta je ponovljivo čitanje?
Ponovljivo čitanje osigurava da su rezultati višestrukog čitanja istih podataka u istoj transakciji konzistentni, čak i ako su druge transakcije potvrdile izmene.
To je podrazumevani nivo izolacije u MySQL-u, izbegava "prljavo čitanje" i "neponovljivo čitanje", kroz MVCC i next-key lock takođe može do određene mere izbeći fantomsko čitanje.
-- Session A:
START TRANSACTION;
SELECT balance FROM accounts WHERE id=1; --vraca 500
-- Session B:
UPDATE accounts SET balance = balance +100 WHERE id=1;
COMMIT;
-- Session A ponovni upit:
SELECT balance FROM accounts WHERE id=1; --još uvek vraća 500 (ponovljivo čitanje)
-- Session A nakon ažuriranja upit:
UPDATE accounts SET balance = balance +50 WHERE id=1; --ažuriranje na osnovu najnovije vrednosti 550 na 600
SELECT balance FROM accounts WHERE id=1; --vraca 600Šta je serijalizacija?
Serijalizacija je najviši nivo izolacije, kroz prisilno serijsko izvršavanje transakcija rešava problem "fantomskog čitanja".
Ali dovodi do problema velike konkurencije zaključavanja, u realnim aplikacijama se retko koristi.
Ako transakcija A nije potvrđena, da li transakcija B pri upitu dobija staru ili novu vrednost?
Ako B je običan SELECT, odnosno snapshot čitanje, on čita staru vrednost, odnosno snapshot pre modifikacije transakcije A, i neće biti blokiran; ako B je trenutno čitanje, na primer SELECT … FOR UPDATE, biće blokiran dok se transakcija A ne potvrdi ili vrati unazad.
-- u sesiji A, ažurirati Wang Er-ov balans
START TRANSACTION;
UPDATE accounts SET balance = 8000 WHERE name = 'Wang Er';
-- trenutno nije COMMIT
-- u sesiji B upitati Wang Er-ov balans
SELECT * FROM accounts WHERE name = 'Wang Er';
-- sesija B će pročitati staru vrednost 1000
-- u sesiji C koristiti trenutno čitanje za upit Wang Er-ovog balansa
SELECT * FROM accounts WHERE name = 'Wang Er' FOR UPDATE;
-- sesija C će biti blokirana, dok se sesija A ne potvrdi ili vrati unazad
Kako promeniti nivo izolacije transakcije?
MySQL podržava izmenu nivoa izolacije transakcije kroz SET naredbu, uključujući globalni nivo, trenutnu sesiju, ali obično se ne preporučuje u proizvodnom okruženju siliti izmenu nivoa izolacije.
U testnom okruženju može se koristiti SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; za izmenu nivoa izolacije trenutne sesije.
Korišćenjem SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED; može se izmeniti globalni nivo izolacije, utiče na nove veze, ali ne menja postojeće sesije.
63.Kako se implementiraju nivoi izolacije transakcija?
Čitanje nepodnetih kroz deljene zaključavanja na nivou redova osigurava da dok jedna transakcija ažurira podatke reda ali nije potvrdila, druge transakcije ne mogu ažurirati taj red, ali ne sprečava prljoavo čitanje, što znači da transakcija 2 može pre potvrde transakcije 1 pročitati podatke modifikovane od strane transakcije 1.
Čitanje potvrđenih će pre ažuriranja podataka dodati ekskluzivno zaključavanje na nivou redova, ne dozvoljavajući drugim transakcijama da pišu ili čitaju nepotvrđene podatke, što znači da transakcija 2 ne može pre potvrde transakcije 1 pročitati podatke modifikovane od strane transakcije 1, time rešavajući problem prljavog čitanja.
Dodatno, čitanje potvrđenih će pre svakog čitanja podataka generisati novi ReadView, stoga dolazi do problema neponovljivog čitanja.
Ponovljivo čitanje generiše ReadView samo pri prvom čitanju, naredna čitanja koriste taj ReadView, time izbegavajući problem neponovljivog čitanja.
Dodatno, za operacije trenutnog čitanja, ponovljivo čitanje će kroz next-key lock zaključati trenutni red i prednji razmak, sprečavajući druge transakcije da ubace podatke u ovom opsegu, time izbegavajući problem fantomskog čitanja.
Pod nivoom serijalizacije, kada transakcija vrši operaciju čitanja, prvo će dodati deljeno zaključavanje na nivou tabele; pri operaciji pisanja, prvo će dodati ekskluzivno zaključavanje na nivou tabele.
Tek nakon završetka transakcije oslobađaju se zaključavanja, time se osigurava da se transakcije ne međusobno ometaju.
64.🌟Molim vas detaljno objasnite fantomsko čitanje?
Fantomsko čitanje znači da u istoj transakciji, višestruko izvršavanje istih opsežnih upita daje različite rezultate. Ovaj fenomen se obično dešava kada druge transakcije između dva upita ubace ili obrišu podatke koji zadovoljavaju uslove trenutnog upita.
---- ovaj deo pomaže svima da razumeju start, na intervjuu se ne mora napamet ----
Na primer, transakcija A posle prvog upita redova podataka u određenom opsegu uslova, transakcija B ubaci novi podatak koji zadovoljava opseg uslova, transakcija A pri ponovnom upitu otkriva da ima još jedan podatak.
Da bismo potvrdili, prvo kreiramo testnu tabelu, ubacimo testne podatke.
CREATE TABLE `user_info` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'primarni ključ id',
`name` VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'ime',
`gender` VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'pol',
`email` VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'email',
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='tabela korisničkih informacija';
-- ubaciti testne podatke
INSERT INTO `user_info` (`id`, `name`, `gender`, `email`) VALUES
(1, 'Curry', 'muški', 'curry@163.com'),
(2, 'Wade', 'muški', 'wade@163.com'),
(3, 'James', 'muški', 'james@163.com');
COMMIT;Zatim u transakciji A izvršavamo upit SELECT * FROM user_info WHERE id > 1;, u transakciji B ubacujemo podatke INSERT INTO user_info (name, gender, email) VALUES ('wanger', 'ženski', 'wanger@163.com');, zatim u transakciji A modifikujemo upravo ubačene podatke update user_info set gender='muški' where id = 4;, konačno u transakciji A ponovo izvršavamo upit SELECT * FROM user_info WHERE id > 1;.
---- ovaj deo pomaže svima da razumeju end, na intervjuu se ne mora napamet ----
Kako izbeći fantomsko čitanje?
MySQL pod nivoom izolacije ponovljivog čitanja, kroz MVCC i next-key lock može do određene mere izbeći fantomsko čitanje.
Na primer, pri upitu eksplicitno zaključati, koristeći next-key lock zaključati opseg upita, sprečavajući druge transakcije da ubace nove podatke.
START TRANSACTION;
SELECT * FROM user_info WHERE id > 1 FOR UPDATE; -- dodati next-key lock
COMMIT;Druge transakcije pri ubacivanju podataka će biti blokirane, dok se trenutna transakcija ne potvrdi ili vrati unazad.
---- ovaj deo pomaže svima da razumeju start, na intervjuu se ne mora napamet ----
Objasniću.
Ako se u izrazu upita sadrži eksplicitno zaključavanje (kao FOR UPDATE), InnoDB će koristiti trenutno čitanje, direktno čita najnovije podatke i dodaje zaključavanje.
Pri opsežnom upitu, InnoDB neće samo zaključati redove koji zadovoljavaju uslove, već će dodati gap lock na susedne indeksne razmake, time formirajući next-key lock.
Next-key lock može sprečiti druge transakcije da ubace nove podatke u razmak, time izbegavajući fantomsko čitanje.
---- ovaj deo pomaže svima da razumeju end, na intervjuu se ne mora napamet ----
Na primer, u transakciji koja izvršava upit, ne pokušavati ažurirati podatke ubačene/obrisane od strane drugih transakcija, koristiti snapshot čitanje za izbegavanje fantomskog čitanja.
---- ovaj deo pomaže svima da razumeju start, na intervjuu se ne mora napamet ----
Koristeći SELECT upit, ako nema eksplicitnog zaključavanja, InnoDB će koristiti MVCC da obezbedi konzistentan view.
Svaka transakcija pri pokretanju generiše Read View, koji se koristi za određivanje koji su podaci vidljivi trenutnoj transakciji.
Novi podaci koje su druge transakcije ubacile nakon pokretanja trenutne transakcije neće biti vidljivi trenutnoj transakciji, stoga neće doći do fantomskog čitanja.
---- ovaj deo pomaže svima da razumeju end, na intervjuu se ne mora napamet ----
Šta je trenutno čitanje?
Trenutno čitanje znači čitanje najnovije potvrđene verzije zapisa, i pri čitanju dodaje zaključavanje na zapis, osiguravajući da druge konkurentne transakcije ne mogu modifikovati trenutni zapis.
Na primer SELECT ... LOCK IN SHARE MODE, SELECT ... FOR UPDATE, kao i UPDATE, DELETE, pripadaju trenutnom čitanju.
Zašto UPDATE i DELETE takođe pripadaju trenutnom čitanju?
Zato što operacije ažuriranja, brisanja, u suštini nisu samo operacije pisanja, već se pre pisanja moraju pročitati podaci, a zatim modifikovati ili obrisati. Da bi se osiguralo da se modifikuje najnoviji podatak, i spreče konflikt konkurentnosti, InnoDB mora pročitati najnoviju verziju podataka i dodati zaključavanje, stoga UPDATE i DELETE takođe pripadaju trenutnom čitanju.
| SQL izraz | Da li je trenutno čitanje | Da li se zaključava |
|---|---|---|
SELECT * FROM user WHERE id=1 | ❌ Ne | ❌ Ne |
SELECT * FROM user WHERE id=1 FOR UPDATE | ✅ Da | ✅ Dodaje ekskluzivno zaključavanje |
SELECT * FROM user WHERE id=1 LOCK IN SHARE MODE | ✅ Da | ✅ Dodaje deljeno zaključavanje |
UPDATE user SET ... WHERE id=1 | ✅ Da | ✅ Dodaje ekskluzivno zaključavanje |
DELETE FROM user WHERE id=1 | ✅ Da | ✅ Dodaje ekskluzivno zaključavanje |
Šta je snapshot čitanje?
Snapshot čitanje je način neblokirajućeg čitanja koji InnoDB implementira kroz MVCC. Kada transakcija izvrši SELECT upit, InnoDB neće direktno čitati trenutne najnovije podatke, već će na osnovu Read View generisanog pri početku transakcije proceniti vidljivost svakog zapisa, time čitajući istorijsku verziju koja zadovoljava uslove.
| SQL | Da li je snapshot čitanje? | Objašnjenje |
|---|---|---|
SELECT * FROM t WHERE id=1 | ✅ Da | Snapshot čitanje |
SELECT * FROM t WHERE id=1 FOR UPDATE | ❌ Ne | Trenutno čitanje, čita najnoviju verziju i dodaje zaključavanje |
UPDATE / DELETE | ❌ Ne | Trenutno čitanje, mora čitati trenutnu verziju i dodati zaključavanje |
INSERT | ❌ Ne | Operacija pisanja, ne postoji istorijska verzija |
65.🌟Jeste li upoznati sa MVCC?
MVCC znači viševerziona konkurentna kontrola, pri svakoj modifikaciji podataka generisaće se nova verzija, umesto direktnog modifikovanja originalnih podataka. I svaka transakcija može videti samo verzije podataka koje su potvrđene pre njenog početka.
Na taj način, operacije čitanja neće blokirati operacije pisanja, operacije pisanja neće blokirati operacije čitanja, time izbegavajući gubitke performansi uzrokovane zaključavanjem.
Njena implementacija na najnižem nivou uglavnom zavisi od Undo Log i Read View.
Pri svakoj modifikaciji podataka, prvo se kopira zapis u Undo Log, i svaki zapis sadrži tri skrivene kolone, DB_TRX_ID se koristi za zabilježavanje ID transakcije koja je modifikovala ovaj red, DB_ROLL_PTR se koristi za pokazivanje na prethodnu verziju u Undo Log, DB_ROW_ID se koristi za jedinstvenu identifikaciju ovog reda podataka (generiše se samo kada nema primarni ključ).
Pri svakom čitanju podataka generisaće se ReadView, gde se bilže ID kolekcije trenutno aktivnih transakcija, minimalni ID transakcije, maksimalni ID transakcije i druge informacije, kroz poređenje sa DB_TRX_ID, procenjuje da li trenutna transakcija može videti ovu verziju podataka.
Molim vas detaljno objasnite šta je lanac verzija?
Lanac verzija znači više istorijskih verzija istog zapisa u InnoDB, kroz polje DB_ROLL_PTR ih povezuje kao lanac, koristi se za podršku snapshot čitanja MVCC.
Pretpostavimo da postoji hero tabela, u tabeli postoji takav zapis, name je Zhang San, city je Di Du, ID transakcije koja je ubacila ovaj zapis je 80.
Tada je vrednost DB_TRX_ID 80, vrednost DB_ROLL_PTR je pokazivač na ovaj insert undo log.
Zatim, ako postoje dve transakcije sa DB_TRX_ID 100, 200 izvrše update operacije na ovaj zapis, lanac verzija ovog zapisa će postati sledeći:
To znači, kada se ažurira jedan red podataka, InnoDB neće direktno prebrisati originalne podatke, već kreirati novu verziju podataka, i ažurirati DB_TRX_ID i DB_ROLL_PTR, tako da pokazuju na prethodnu verziju i odgovarajući undo log.
Na taj način, stari podaci neće biti izgubljeni, mogu se pronaći kroz lanac verzija.
Budući da će undo log zabilježiti svaki update, i novo ubačeni red podataka bilježi pokazivač prethodnog undo log, može se kroz pokazivač DB_ROLL_PTR pronaći prethodni zapis, time formirajući lanac verzija.
Molim vas detaljno objasnite šta je ReadView?
ReadView je "vidljivost view" koji InnoDB kreira za svaku transakciju, koristi se za određivanje koje verzije podataka su vidljive trenutnoj transakciji pri izvršavanju snapshot čitanja, a koje ne.
Kada transakcija počne da se izvršava, InnoDB će za tu transakciju kreirati ReadView, ovaj ReadView će zabilježiti 4 važne informacije:
- creator_trx_id: ID transakcije koja je kreirala ovaj ReadView.
- m_ids: lista ID svih aktivnih transakcija, aktivne transakcije su one koje su počele ali još nisu potvrđene.
- min_trx_id: minimalni ID svih aktivnih transakcija. To je minimalni ID transakcije u nizu m_ids.
- max_trx_id: maksimalna vrednost ID transakcije plus jedna. Drugim rečima, to je sledeći ID transakcije koji će biti generisan.
Kako ReadView ocenjuje da li je određena verzija zapisa vidljiva?
Oceniće se kroz tri koraka:
①,Ako je DB_TRX_ID određene verzije podataka manji od min_trx_id, tada je ta verzija podataka potvrđena pre generisanja ReadView, stoga je vidljiva trenutnoj transakciji.
②,Ako je DB_TRX_ID veći od max_trx_id, tada to znači da transakcija koja je kreirala ovu verziju podataka počela nakon generisanja ReadView, stoga nije vidljiva trenutnoj transakciji.
③,Ako je DB_TRX_ID između min_trx_id i max_trx_id, potrebno je proceniti da li je DB_TRX_ID u listi m_ids:
- Nije, znači da je transakcija koja je kreirala ovu verziju podataka potvrđena nakon generisanja ReadView, stoga je takođe vidljiva trenutnoj transakciji.
- Jeste, znači da je transakcija i dalje aktivna, ili je počela nakon što je trenutna transakcija generisala ReadView, stoga nije vidljiva.
Dajmo stvaran primer.
Transakcija čitanja otvorila je ReadView, ovaj ReadView zabilježio je listu ID trenutno aktivnih transakcija (444, 555, 665), kao i minimalni ID transakcije (444) i maksimalni ID transakcije (666). Takođe i sopstveni ID transakcije 520, odnosno creator_trx_id.
ID tranzakcije pisanja reda kojeg treba pročitati je x, odnosno DB_TRX_ID.
- Ako je x = 110, očigledno je potvrđen pre generisanja ReadView, stoga je ovaj red vidljiv.
- Ako je x = 667, očigledno je nepoznati svet, stoga ovaj red nije vidljiv operaciji čitanja.
- Ako je x = 519, iako je 519 veće od 444 manje od 666, ali 519 nije u listi aktivnih transakcija, stoga je ovaj red vidljiv. Jer je 519 potvrđen pre nego što je 520 generisao ReadView.
- Ako je x = 555, iako je 555 veće od 444 manje od 666, ali 555 je u listi aktivnih transakcija, stoga ovaj red nije vidljiv. Jer je 555 nepoznato da li je potvrđen.
Koja je razlika između ponovljivog čitanja i čitanja potvrđenih u ReadView?
Ponovljivo čitanje: pri prvom čitanju podataka generisaće se jedan ReadView, ovaj ReadView će se održavati do završetka transakcije, time se osigurava da se višestruko čitanje istog reda podataka u transakciji uvek čita iste podatke.
Čitanje potvrđenih: pre svakog čitanja podataka generisaće se ReadView, time se osigurava da se svaki put čitaju najnoviji podaci.
Preporučeno čitanje: Razumeti MVCC InnoDB
Ako dve AB transakcije konkurentno modifikuju jednu promenljivu, koju vrednost će A pročitati, kako analizirati.
Da li će transakcija A pri čitanju moći da pročita modifikacije transakcije B, zavisi od toga da li A vrši snapshot čitanje ili trenutno čitanje. Ako je snapshot čitanje, InnoDB će koristiti MVCC ReadView da proceni vidljivost verzije zapisa, ako transakcija B još nije potvrđena ili nije vidljiva u view-u transakcije A, tada će A pročitati staru vrednost; ako je trenutno čitanje, potrebno je zaključavanje, ako je B potvrđena može se direktno pročitati, inače će A biti blokiran dok se B ne završi.
Visoka dostupnost
66.Jeste li upoznati sa čitanje-pisanje razdvajanjem MySQL baze podataka?
Razdvajanje čitanja i pisanja znači da se "operacije pisanja" poklanjaju glavnoj bazi, "operacije čitanja" dele se više podrepljenih baza, time poboljšavajući performanse konkurentnosti sistema.
Sloj aplikacije kroz posrednika (kao MyCat, ShardingSphere) automatski rutira zahteve, šalje INSERT / UPDATE / DELETE i druge operacije pisanja glavnoj bazi, SELECT operacije upita šalje podrepljenim bazama.
// primer: u Java-i kroz različite izvore podataka
@Transactional
public void updateOrder(Order order) {
masterDataSource.update(order); // operacije pisanja idu na glavnu bazu
}
public Order getOrderById(Long id) {
return slaveDataSource.query(id); // operacije čitanja idu na podrepljenu bazu
}Glavna baza kroz binlog sinhronizuje izmene podataka ka podrepljenim bazama, time održavajući konzistentnost podataka.
Glavna baza dump_thread nit kroz TCP šalje binlog ka podrepljenoj bazi, podrepljena baza io_thread nit prima glavnu binlog, upisuje u relay log, podrepljena baza sql_thread nit čita relay log, i sekvencijalno izvršava SQL naredbe, ažurira podatke podrepljene baze.
67.Koje su metode implementacije razdvajanja čitanja i pisanja?
Implementiranje razdvajanja čitanja i pisanja ima tri načina: najjednostavniji je ručna kontrola glavno-podrepljenih izvora podataka na sloju aplikacije, pogodno za male projekte;
Srednji projekti kroz Spring + dodatak više izvora podataka, AOP napomene automatski rutiraju;
Veliki sistemi obično koriste posrednike, kao ShardingSphere, MyCat, podržavaju automatsko rutiranje, balansiranje opterećenja, prenos grešaka i druge funkcionalnosti.
Funkcionalnost razdvajanja čitanja i pisanja MyCat-a zavisi od arhitekture glavno-podrepljene replikacije MySQL:
- writeHost: predstavlja glavi čvor, odgovoran za obradu svih DML SQL naredbi, kao INSERT, UPDATE i DELETE.
- readHost: predstavlja podrepljeni čvor, odgovoran za obradu SQL naredbi upita (kao SELECT), kako bi se realizovalo razdvajanje čitanja i pisanja.
U normalnim okolnostima, MyCat će prvobitno konfigurisani writeHost koristiti kao podrazumevani čvor za pisanje. Sve DML SQL naredbe će biti poslate na ovaj podrazumevani čvor pisanja za izvršenje.
Nakon što čvor za pisanja završi pisanje podataka, kroz mehanizam glavno-podrepljene replikacije MySQL, sinhronizuje podatke ka svim podrepljenim čvorovima, osiguravajući konzistentnost podataka glavne i podrepljenih baza.
57.Jeste li upoznati sa principom glavno-podrepljene replikacije?
MySQL-ova glavno-podrepljena replikacija je mehanizam sinhronizacije podataka, koristi se za kopiranje podataka iz glavne baze podataka u jednu ili više podrepljenih baza.
Glavna baza pri potvrđivanju transakcije bilježi izmene podataka u obliku događaja u Binlog. Podrepljena baza kroz I/O nit čita događaje izmene iz Binlog glavne baze, i upisuje te događaje u lokalni fajl relay log, SQL nit će u realnom vremenu pratiti sadržaj relay log, sekvencijalno čitati i izvršavati te događaje, time garantujući da su podaci podrepljene baze konzistentni sa glavnom bazom.
69.Kako se rešava kašnjenje glavno-podrepljene sinhronizacije?
Kašnjenje glavno-podrepljene sinhronizacije postoji zato što podrepljena baza mora prvo primiti binlog, zatim izvršiti SQL da bi sinhronizovala podatke glavne baze, lako dolazi do kašnjenja pri visokoj konkurentnosti pisanja ili mrežnim oscilacijama, dovodeći do neusklađenosti čitanja i pisanja.
Prvo rešenje: upiti koji visoko zahtevaju konzistentnost (kao upit rezultata plaćanja) mogu direktno ići na glavnu bazu.
// primer pseudokoda
public Object query(String sql) {
if(isWriteQuery(sql) || needStrongConsistency(sql)) {
return masterDataSource.query(sql);
} else {
return slaveDataSource.query(sql);
}
}Drugo rešenje: za nekritične poslove moguće je dozvoliti kratkotrajnu neusklađenost podataka, može se korisniku naglasiti "podaci se sinhronizuju, molim vas osvežite", zatim kroz mehanizam asinhrone notifikacije zameniti real-time upite.
// primer pseudokoda
public Object query(String sql) {
if(isWriteQuery(sql)) {
return masterDataSource.query(sql);
} else {
// asinhrono obavestiti korisnika da su podaci ažurirani
notifyUser("podaci se sinhronizuju, molim vas osvežite");
return slaveDataSource.query(sql);
}
}Treće rešenje: usvojiti polusinhronu replikaciju, glavna baza pri potvrđivanju transakcije mora sačekati da bar jedna podrepljena baza potvrdi prijem binlog (ali ne zahteva da se izvršenje završi), tek se smatra uspešnom potvrdom.
Molim vas objasnite tok polusinhron replikacije?
Prvi korak, glavna baza instalira polusinhroni plugin:
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';Drugi korak, glavna baza uključuje polusinhronu replikaciju i podešava vreme isteka:
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_timeout = 10000;Primer konfiguracije glavne baze my.cnf:
[mysqld]
plugin-load = "rpl_semi_sync_master=semisync_master.so"
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_timeout = 10000
# MySQL 5.7+ se preporučuje korišćenje bezgubitnog režima
rpl_semi_sync_master_wait_point = AFTER_SYNCTreći korak, podrepljena baza instalira polusinhroni plugin:
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';Četvrti korak, podrepljena baza uključuje polusinhronu replikaciju:
SET GLOBAL rpl_semi_sync_slave_enabled = 1;Primer konfiguracije podrepljene baze my.cnf:
[mysqld]
plugin-load = "rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_slave_enabled = 170.🌟Kako obično delite bazu?
Strategije deljenja baze imaju dve, prva je vertikalno deljenje baze: prema poslovnim modulima različite tabele se dele u različite baze, na primer tabele korisnika, prijave, dozvola se stavljaju u bazu korisnika, tabele proizvoda, kategorija, zaliha se stavljaju u bazu proizvoda, kuponi, popusti, sekundarna prodaja se stavljaju u bazu aktivnosti.
Druga je horizontalno deljenje baze: prema određenoj strategiji deli podatke jedne tabele u više baza, na primer heš fragmentaciju i fragmentaciju opsega, vrši mod operaciju na id korisnika ili podelu opsega, rasipa podatke u različite baze.
Dajem odeljak korišćenjem ShardingSphere inline algoritma za definisanje pravila fragmentacije:
rules:
- !SHARDING
tables:
order:
actualDataNodes: db_${0..3}.order_${0..15}
databaseStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: db_hash_mod
tableStrategy:
standard:
shardingColumn: order_time
shardingAlgorithmName: table_interval_yearly
shardingAlgorithms:
db_hash_mod:
type: HASH_MOD
props:
sharding-count: 4
table_interval_yearly:
type: INTERVAL
props:
datetime-pattern: 'yyyy-MM-dd HH:mm:ss'
datetime-lower: '2024-01-01 00:00:00'
datetime-upper: '2025-01-01 00:00:00'
sharding-suffix-pattern: 'yyyy'
datetime-interval-amount: 1
datetime-interval-unit: 'Years'71.🌟A kako delite tabele?
Kada jedna tabela pređe 5 miliona zapisa, može se razmotriti horizontalno deljenje tabela. Na primer, možemo podeliti tabelu članaka u više tabela, kao article_0, article_9999, article_19999 itd.
U [Tehnicki Pai praktični projekat] smo osnovne informacije članka i detalje sadržaja vertikalno podelili, jer sadržaj članka zauzima relativno veliki prostor, kada je potrebno samo videti osnovne informacije članka, povlačenje detalja članka će zauzeti više mrežnog IO i memorije, dovodeći do sporijeg upita; dok osnovne informacije članka, kao naslov, autor, status itd. zauzimaju manji prostor, pogodne su za scenarije gde nije potrebno upitivati detalje članka.
72.Koje su strategije fragmentacije horizontalnog deljenja baza i tabela?
Uobičajene strategije fragmentacije imaju tri, fragmentacija opsega, Heš fragmentacija i rutiranje fragmentacije.
Fragmentacija opsega je horizontalno deljenje na osnovu vrednosti određenog polja. Pogodna je za scenarije gde fragmentacioni ključ ima kontinuitet.
Na primer, ako se kao fragmentacioni ključ koristi user_id:
- 1 ~ 10000 → db1.user_1
- 10001 ~ 20000 → db2.user_2
Heš fragmentacija znači da se kroz heširanje vrednosti fragmentacionog ključa, podaci ravnomerno distribuiraju u više baza i tabela, pogodna je za scenarije gde fragmentacioni ključ ima diskretnost.
Na primer, ako smo na početku planirali 4 tabele, jednostavno možemo implementirati deljenje tabela kroz mod operaciju:
public String getTableNameByHash(long userId) {
int tableIndex = (int) (userId % 4);
return "user_" + tableIndex;
}Rutiranje fragmentacije određuje u koju bazu i tabelu treba skladištiti podatke kroz konfiguraciju rutiranja, pogodno je za scenarije gde fragmentacioni ključ nije pravilan.
Na primer, možemo odrediti u koju tabelu se skladište narudžbina kroz tabelu order_router:
| order_id | table_id |
|---|---|
| xxxx | table_1 |
| yyyy | table_2 |
| zzzz | table_3 |
73.Kako se implementira širenje bez zaustavljanja?
Prva faza: istovremeno pisanje u stare i nove baze, osiguravajući real-time sinhronizaciju podataka; može se koristiti red poruka za asinhronu kompenzaciju, idempotentnost izbegava duplo pisanje. Operacije čitanja i dalje idu na staru bazu.
Referenca koda:
@Transactional
public void createOrder(Order order) {
oldDB.insert(order); // upis u staru bazu
newDB.insert(order); // upis u novi čvor proširenja
kafka.send("data_sync", order); // kanal asinhrone kompenzacije
}Druga faza, kroz Canal ili vlastitu skriptu sinhronizuje istorijske podatke stare baze ka novoj bazi. Ključni poslovi pri upitu istovremeno upituju stare i nove baze, vrše proveru podataka, osiguravajući konzistentnost.
public List<Order> getOrders(Long userId) {
List<Order> orders = newDB.getOrders(userId);
List<Order> oldOrders = oldDB.getOrders(userId);
if (!orders.equals(oldOrders)) {
// podaci nisu konzistentni, vršiti kompenzaciju
kafka.send("data_sync", oldOrders);
}
}Treća faza, nakon potvrde konzistentnosti podataka nove baze, postepeno prebacuje zahteve za čitanje na novu bazu, zatim gasi staru bazu.
74.Koji se posrednici obično koriste za deljenje baza i tabela?
Uobičajeni posrednici za deljenje baza i tabela su ShardingSphere i Mycat.
①,ShardingSphere je prvobitno open-source od Dangdang, kasnije dodeljen Apache, njegov podprojekat Sharding-JDBC uglavnom obezbeđuje dodatne servise na JDBC sloju Java. Ne zahteva dodatno deployment i zavisnosti, može se razumeti kao pojačana JDBC drajver, potpuno kompatibilan sa JDBC i različitim ORM framework-ima.
②,Mycat je deriviran iz Cobar proizvoda Alibaba Grupe, može se razumeti kao proxy baze podataka.
Preporučeno čitanje: Mycat predstavljanje
75.Koji problemi bi deljenje baza i tabela moglo doneti?
Prvo, transakcije između baza ne mogu zavisiti od ACID karakteristika jednog servera MySQL, potrebno je koristiti rešenja za distribuirane transakcije, kao AT mod, TCC mod Seata.
Drugo, nakon deljenja baza ne može se koristiti JOIN za povezani upit tabela. Može se izvršiti spajanje na sloju poslovanja, ili staviti podatke potrebne za povezani upit u ES.
// primer Java koda
User user = userService.getUserById(1);
List<Order> orders = orderService.getOrdersByUserId(1);Treće, samorasle ID u scenariju fragmentacije lako dolazi do konflikta, potrebno je koristiti rešenje globalne jedinstvenosti.
Nakon što se tabela baze iseče, ne može se više zavisiti od mehanizma generisanja primarnog ključa same baze podataka, stoga su potrebni neki sredstvi za garantovanje jedinstvenosti globalnog primarnog ključa. Na primer Snowflake algoritam, JD-hotkey JD.com.
Kako se generiše distribuirani primarni ključ ID u vašem projektu?
U [Tehnicki Pai] projektu, na osnovu Snowflake algoritma implementirali smo set prilagođenih rešenja generisanja ID, kroz promenu jedinice vremenskog pečata, dužine ID, proporcije dodele workId i dataCenterId, kašnjenje generisanja ID smanjeno je za 20%; zadovoljeno jedinstvenost ID u distribuiranom okruženju.
Kako se konkretno implementira Snowflake algoritam?
Snowflake algoritam je open-source distribuirani algoritam generisanja ID od Twitter, njegova glavna ideja: koristiti 64-bitni broj kao globalno jedinstveni ID.
- Prvi bit je znakovni bit, uvek je 0, predstavlja pozitivan broj.
- Sledeća 41 bita je vremenski pečat, bilježi trenutno vreme minus fiksni početni vremenski pečat, može se koristiti 69 godina.
- Zatim 10 bitova ID radne mašine.
- Konačno 12 bitova serijskog broja, po milisekundi može se generisati najviše 4096 ID.
Približna implementacija koda je sledeća:
public class SnowflakeIdGenerator {
private long datacenterId = 1L; // ID data centra
private long machineId = 1L; // ID mašine
private long sequence = 0L; // serijski broj
private long lastTimestamp = -1L;
public synchronized long nextId() {
long timestamp = System.currentTimeMillis();
if (timestamp == lastTimestamp) {
sequence = (sequence + 1) & 4095;
if (sequence == 0) {
while (timestamp == lastTimestamp) {
timestamp = System.currentTimeMillis();
}
}
} else {
sequence = 0;
}
lastTimestamp = timestamp;
return ((timestamp - 1609459200000L) << 22) | (datacenterId << 17) | (machineId << 12) | sequence;
}
}Održavanje
76.Kako obrisati podatke na nivou stotina hiljada ili više?
Pri obradi brisanja podataka na nivou stotina hiljada, DELETE naredbe velikog opsega često uzrokuju dugo zaključavanje tabela, širenje transakcionih logova i druge probleme.
Može se koristiti rešenje paketnog brisanja, deljenje operacija brisanja na više malih grupa za obradu.
public void batchDelete(String tableName, String condition, int batchSize) {
// 1. kreirati grupu niti
int threadCount = Runtime.getRuntime().availableProcessors();
ExecutorService executor = Executors.newFixedThreadPool(threadCount);
CountDownLatch latch = new CountDownLatch(threadCount);
// 2. dobiti ukupan broj zapisa
long totalCount = getTotalCount(tableName, condition);
// 3. izračunati količinu podataka koju svaka nit obrađuje
long perThreadCount = totalCount / threadCount;
// 4. dodeliti zadatke grupi niti
for (int i = 0; i < threadCount; i++) {
long startId = i * perThreadCount;
long endId = (i == threadCount - 1) ? totalCount : (startId + perThreadCount);
executor.execute(() -> {
try {
// pakovano brisanje podataka
for (long j = startId; j < endId; j += batchSize) {
String deleteSql = String.format(
"DELETE FROM %s WHERE %s LIMIT %d",
tableName, condition, batchSize
);
// izvršiti brisanje
jdbcTemplate.update(deleteSql);
}
} finally {
latch.countDown();
}
});
}
// 5. sačekati da se sve završe
latch.await();
executor.shutdown();
}Takođe može se koristiti način kreiranja nove tabele za zamenom originalne tabele, migraciju podataka koje treba zadržati u novu tabelu, zatim brisanje stare tabele.
Jednostavno rešenje:
-- 1. kreirati strukturu nove tabele (uključujući indekse)
CREATE TABLE new_table LIKE large_table;
-- 2. ubaciti podatke koje treba zadržati
INSERT INTO new_table
SELECT * FROM large_table WHERE condition;
-- 3. preimenovati tabelu
RENAME TABLE large_table TO old_table, new_table TO large_table;
-- 4. obrisati staru tabelu
DROP TABLE old_table;Dodati korake provere prostora tabele, paketnog uvoza podataka, provere konzistentnosti podataka itd.:
-- 1. pre izvršenja prvo proveriti da li je dovoljno prostora
SELECT table_schema,
table_name,
round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB"
FROM information_schema.TABLES
WHERE table_schema = DATABASE()
AND table_name = 'large_table';
-- 2. kreirati novu tabelu
CREATE TABLE new_table LIKE large_table;
-- 3. paketni uvoz podataka (izbegavati uvoz previše podataka odjednom)
SET @batch = 1;
SET @batch_size = 10000;
SET @total = (SELECT COUNT(*) FROM large_table WHERE condition);
REPEAT
INSERT INTO new_table
SELECT * FROM large_table
WHERE condition
LIMIT @batch_size;
SET @batch = @batch + 1;
UNTIL @batch * @batch_size > @total END REPEAT;
-- 4. proveriti konzistentnost podataka
SELECT COUNT(*) FROM new_table;
SELECT COUNT(*) FROM large_table WHERE condition;
-- 5. izvršiti zamenu tabela u periodu niskog poslovnog opterećenja
RENAME TABLE large_table TO old_table,
new_table TO large_table;
-- 6. nakon potvrde obrisati staru tabelu (preporučuje se ne obrisati odmah)
-- DROP TABLE old_table;77.Kako dodati polje u tabelu sa desetinama miliona zapisa?
U nižim verzijama MySQL-a, kada se dodaje polje u tabelu sa desetinama miliona zapisa podataka, direktno korišćenje naredbe ALTER TABLE dovodi do dugog zaključavanja tabela, pa čak i do rušenja baze podataka.
Može se koristiti Percona Toolkit pt-online-schema-change za dovršetak, kroz kreiranje privremene tabele, postepeno sinhronizovanje podataka i korišćenje triggera za hvatanje izmena.
pt-online-schema-change --alter "ADD COLUMN new_column datatype" D=database,t=your_table --executeZa MySQL 8.0+ verziju, može se direktno dovršiti kroz ALTER TABLE, jer je dodat algoritam INSTANT, dodavanje kolone neće zaključavati tabelu dugo.
ALTER TABLE your_table ADD COLUMN new_column datatype;Ako nije specificiran algoritam ALGORITHM=INSTANT, MySQL će prvo pokušati INSTANT algoritam; ako ne može dovršiti, preći će na INPLACE algoritam; ako i dalje ne može dovršiti, pokušaće COPY algoritam.
78.Ako MySQL CPUsko vrelo, kako rešiti?
Obično prvo kroz top naredbu potvrđujem da li je proces mysqld zauzeo.
Zatim kroz SHOW PROCESSLIST i dnevnik sporih upita lociram da li postoji SQL koji dugo traje, zatim u kombinaciji sa explain i performance_schema analiziram da li SQL pogodja indeks, da li postoje privremene tabele i sortiranje.
-- koristiti EXPLAIN za analizu plana izvršenja SQL
EXPLAIN SELECT * FROM large_table WHERE condition;
-- videti korišćenje indeksa tabele
SHOW INDEX FROM table_name;
-- videti InnoDB status
SHOW ENGINE INNODB STATUS;
-- videti statističke informacije tabele
ANALYZE TABLE table_name;Konačno, kroz SQL optimizaciju, dodavanje indeksa, paketne operacije i druge metode postepeno poboljšavam.
SQL pitanja
79.Tabela: id, name, age, sex, class, SQL izraz: sva imena starih 18 godina? Naći koliko ljudi starijih od 18 po svakoj razredu? Naći prve dve najstarije osobe u svakoj razredu? (dopuna)
Preporučujem svima da lokalno kreirate tabelu i praktikujete. Dodato 11. aprila 2024.
Prvi korak, kreiranje tabele:
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
age INT,
sex CHAR(1),
class VARCHAR(50)
);Drugi korak, ubacivanje podataka:
INSERT INTO students (name, age, sex, class) VALUES
('Chenmo Wang Er', 18, 'ženski', 'tri dva razred'),
('Chenmo Wang Yi', 20, 'muški', 'tri dva razred'),
('Chenmo Wang San', 19, 'muški', 'tri tri razred'),
('Chenmo Wang Si', 17, 'muški', 'tri tri razred'),
('Chenmo Wang Wu', 20, 'ženski', 'tri četiri razred'),
('Chenmo Wang Liu', 21, 'muški', 'tri četiri razred'),
('Chenmo Wang Qi', 18, 'ženski', 'tri četiri razred');Sva imena starih 18 godina?
SELECT name FROM students WHERE age = 18;Ova SQL naredba bira sve zapise iz tabele gde je age jednako 18, i vraća polje name tih zapisa.
Ako je moguće, može se dodati indeks na polje age.
ALTER TABLE students ADD INDEX age_index (age);Koliko ljudi starijih od 18 po svakoj razredu?
SELECT class, COUNT(*) AS number_of_students
FROM students
WHERE age > 18
GROUP BY class;Ova SQL naredba prvo filtrira zapise starije od 18 godina, zatim grupiše po class, i kroz count broji broj učenika svakog razreda.
Prve dve najstarije osobe u svakoj razredu?
Ovaj upit je malo komplikovaniji, potrebno je koristiti podupit i DISTINCT.
SELECT a.class, a.name, a.age
FROM students a
WHERE (
SELECT COUNT(DISTINCT b.age)
FROM students b
WHERE b.class = a.class AND b.age > a.age
) < 2
ORDER BY a.class, a.age DESC;Ova SQL naredba prvo bira polja class, name i age iz tabele students, zatim koristi podupit za računanje prvih dva učenika po starosti u svakom razredu.
80.Postoji zahtev za upit, MySQL ima dve tabele, jedna tabela ima 10M podataka, druga tabela samo nekoliko hiljada podataka, treba izvršiti povezani upit, kako optimisati
Prvi korak, kreirati indeks za polja povezivanja, osigurati da polja ON povezivanja imaju indekse.
ALTER TABLE big_table ADD INDEX idx_small_id(small_id);Drugi korak, mala tabela pokreće veliku tabelu, malu tabelu staviti levo od JOIN (pokretačka tabla), veliku tabelu desno.
SELECT ... FROM small_table s
JOIN big_table b ON s.id = b.small_id81.Kreirati novu strukturu tabele, kreirati indekse, uvoziti hiljade ili desetine miliona podataka kroz insert u tu tabelu, kreirati novu strukturu tabele, uvoziti hiljade ili desetine miliona podataka kroz insert u tu tabelu, zatim kreirati indekse, koja efikasnost je veća? Ili koja traje kraće?
Prvo rezime:
U scenariju uvoza velikih količina podataka, efikasnost uvoza podataka prvo pa kreiranja indeksa kasnije je značajno veća od efikasnosti kreiranja indeksa prvo pa uvoza podataka.
Hajde, praktično.
Prvo kreirati tabelu, zatim kreirati indekse, izvršiti insert naredbu, videti vreme izvršenja (1 milion podataka na mom mašini izvršenje traje relativno dugo, koristićemo 100 hiljada podataka za test).
CREATE TABLE test_table (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
created_at DATETIME NOT NULL
);
CREATE INDEX idx_name ON test_table(name);
DELIMITER //
CREATE PROCEDURE insert_data()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < 1000000 DO
INSERT INTO test_table(name, email, created_at)
VALUES (CONCAT('wanger',i), CONCAT('email', i, '@example.com'), NOW());
SET i = i + 1;
END WHILE;
END //
DELIMITER ;
CALL insert_data();Ukupno vreme 13.93+0.01+0.01+0.01=13.96 sekundi.
Zatim, ponovo kreiramo tabelu, izvršavamo insert operaciju, zatim kreiramo indekse.
CREATE TABLE test_table_no_index (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
created_at DATETIME NOT NULL
);
DELIMITER //
CREATE PROCEDURE insert_data_no_index()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < 1000000 DO
INSERT INTO test_table_no_index(name, email, created_at)
VALUES (CONCAT('wanger', i), CONCAT('email', i, '@example.com'), NOW());
SET i = i + 1;
END WHILE;
END //
DELIMITER ;
CALL insert_data_no_index();
CREATE INDEX idx_name_no_index ON test_table_no_index(name);Pogledajmo ukupno vreme, 0.01+0.00+13.08+0.18=13.27 sekundi.
Način ubacivanja podataka prvo pa kreiranja indeksa je malo brži od načina kreiranja indeksa prvo pa ubacivanja podataka.
A zatim je vremenska razlika vrlo mala, uglavnom zato što smo ubacili malo podataka. Objasniću razliku.
- Prvo ubaciti podatke zatim kreirati indekse: u odsustvu indeksa pri ubacivanju podataka, baza ne mora ažurirati indekse pri svakom ubacivanju.
- Prvo kreirati indekse zatim ubaciti podatke: baza mora održavati strukturu indeksa pri svakom ubacivanju novog zapisa, s rastom količine podataka, održavanje indeksa dovodi do dodatnih troškova performansi.
Da li je MySQL bolje prvo kreirati indekse ili prvo ubaciti podatke?
Ako je u pitanju malo ubacivanje, može se prvo kreirati indeksi; ali u scenariju uvoza velikih količina podataka, preporučuje se prvo ubaciti podatke zatim kreirati indekse.
Budući da su indeksi bazirani na B+ stablu, pri velikom ubacivanju ako se unapred kreiraju indeksi, često će se okidati podela stranica i prilagođavanje strukture indeksa, utičući na performanse.
Nakon završetka ubacivanja jedinstveno kreiranje strukture indeksa, MySQL će se generisati u redosledu serijama, brže je, manja potrošnja resursa.
82.Šta je duboko straničenje, select * from tbn limit 1000000000 koji problem ima, ako je tabela velika ili mala koji problem redom
Duboko straničenje znači uzeti relativno kasnije stranice podataka u MySQL-u, na primer strana 1000, strana 10000 itd. Posebno korišćenjem LIMIT offset,count načina, kada je offset posebno veliki, donosiće ozbiljne probleme performansi.
Za SELECT * FROM tbn LIMIT 1000000,10, ovakva izjava upita, MySQL će:
- čitati prvi zapis iz tabele, proceniti da li zadovoljava where uslov; ako zadovolji, brojač +1; inače dok se brojač ne akumulira do 1000000 počinje zaista uzimati podatke
- zatim nastaviti da dobija 10 zapisa, vratiti
Performanse će biti vrlo loše, jer treba skenirati od početka, ne može se koristiti optimizacija indeksa, i treba odbaciti velike količine nepotrebnih podataka, zauzimajući velike količine memorije i CPU resursa.
Može se optimisati kroz indeksiranje primarnog ključa:
SELECT * FROM tbn
WHERE id > (SELECT id FROM tbn ORDER BY id LIMIT 1000000, 1)
LIMIT 10Ili zapamtiti maksimalni ID poslednje stranicenje, zatim upitati:
SELECT * FROM tbn
WHERE id > last_page_max_id
LIMIT 1083.SQL problem: tabela rezultata učenika, polja su ime učenika, razred, rezultat, naći prvih 10 po svakom razredu
Prvi korak, kreiranje tabele:
CREATE TABLE student_scores (
student_name VARCHAR(100),
class VARCHAR(50),
score INT
);Drugi korak, ubacivanje podataka:
INSERT INTO student_scores (student_name, class, score) VALUES
('Chenmo Wang Er', 'tri dva razred', 88),
('Chenmo Wang San', 'tri dva razred', 92),
('Chenmo Wang Si', 'tri dva razred', 87),
('Chenmo Wang Wu', 'tri dva razred', 85),
('Chenmo Wang Liu', 'tri dva razred', 90),
('Chenmo Wang Qi', 'tri dva razred', 95),
('Chenmo Wang Ba', 'tri dva razred', 82),
('Chenmo Wang Jiu', 'tri dva razred', 78),
('Chenmo Wang Shi', 'tri dva razred', 91),
('Chenmo Wang Shi Yi', 'tri dva razred', 79),
('Chenmo Wang Shi Er', 'tri tri razred', 84),
('Chenmo Wang Shi San', 'tri tri razred', 81),
('Chenmo Wang Shi Si', 'tri tri razred', 90),
('Chenmo Wang Shi Wu', 'tri tri razred', 88),
('Chenmo Wang Shi Liu', 'tri tri razred', 87),
('Chenmo Wang Shi Qi', 'tri tri razred', 93),
('Chenmo Wang Shi Ba', 'tri tri razred', 89),
('Chenmo Wang Shi Jiu', 'tri tri razred', 85),
('Chenmo Wang Er Shi', 'tri tri razred', 92),
('Chenmo Wang Er Shi Yi', 'tri tri razred', 84);Treći korak, upit prvih 10 po svakom razredu. Ako je MySQL ispod verzije 8.0, ne podržava window funkciju, može se kroz održavanje trenutnog stanja razreda u upitu i rangiranje, implementirati grupisanje unutar razreda po sortiranju rezultata i označavanje rednih brojeva, zatim uzeti prvih 10.
SET @cur_class = NULL, @cur_rank = 0;
SELECT student_name, class, score
FROM (
SELECT
student_name,
class,
score,
@cur_rank := IF(@cur_class = class, @cur_rank + 1, 1) AS rank,
@cur_class := class
FROM student_scores
ORDER BY class, score DESC
) AS ranked
WHERE ranked.rank <= 10;| Korak | Objašnjenje |
|---|---|
| @cur_class promenljiva | bilježi trenutni razred koji se obrađuje |
| @cur_rank promenljiva | bilježi rang trenutnog razreda, podrazumevano 0 |
IF(@cur_class = class, @cur_rank + 1, 1) | ako se razred nije promenio, rang +1; ako se promenio novi razred, rang ponovo od 1 |
@cur_class := class | ažurirati promenljivu trenutnog razreda, održavati praćenje promena razreda |
ORDER BY class, score DESC | mora se prvo sortirati po razredu rastuće, po rezultatu opadajuće, da bi promenljive ispravno dodelile rang |
Spoljašnji WHERE rank <= 10 | uzima samo prvih 10 svakog razreda ✅ |
Ako je MySQL 8.0+ verzija, mogu se koristiti window funkcije za dovršetak:
SELECT student_name, class, score
FROM (
SELECT
student_name,
class,
score,
ROW_NUMBER() OVER (PARTITION BY class ORDER BY score DESC) AS rn
FROM student_scores
) AS tmp
WHERE rn <= 10;| Tehnologija korišćena u SQL | Objašnjenje |
|---|---|
ROW_NUMBER() OVER (PARTITION BY class ORDER BY score DESC) | daje svakom razredu nezavisni rang, od 1 |
| Podupit tmp | koristi se za privremeno generisanje skupa podataka sa rn (rang) |
Spoljašnji WHERE rn <= 10 | bira učenike prvih 10 svakog razreda |
ORDER BY score DESC | viši rezultat ispred, odgovara konvencionalnoj logici rangiranja |
