Îndepărtarea unor cantități mari de date din tabele mysql
Dacă trebuie să ștergeți zeci și sute de mii de înregistrări dintr-un tabel, știi că este lent. În mod evident, astfel cum Mysql în acest caz, ar trebui să meargă pe fiecare intrare și scoateți-l de pe disc.
În .io folosim (așa-numitele) tabel fereastră. Acest lucru este în cazul în care stocați datele din tabelul de la doar ultima oră (sau o altă perioadă de timp). Deci, nu numai pentru a scrie o mulțime, dar multe sunt eliminarea. În cazul în care pierderea de date nu este fatală, este posibil să se utilizeze un tabel MEMORY. Cu toate acestea, în cele mai multe cazuri, este, desigur, nu va funcționa. Dupa ce a pierdut date de fiecare dată când nu doriți repornește server.
De ce este lent DELETE
În primul rând, trebuie să se înțeleagă că îndepărtarea de 10 mii de înregistrări -. Este de 10 mii de ștergeri de înregistrări .. Dar fiecare ștergere - un record câteva tranzacții de modificări (datele și indexurile) pe disc.
În plus, înainte de a șterge înregistrări, Mysql trebuie mai întâi să le selectați. Și în acest caz, aceleași reguli ca și în probele. Dacă indexurile sunt configurate prost, operațiunea de ștergere va deveni mai lent.
Instalarea indicelui
Pentru a testa utilizarea indicilor este suficientă pentru a înlocui SELECT COUNT (*) Ștergețidin.
# Aflați în cazul în care utilizează un index
# Înlocuiți SELECT va testa utilizarea de indici
Apoi, putem fi siguri că problema este în index:
# Este necesar să se stabilească un index pe o coloană ts pentru a grăbi îndepărtarea
Utilizarea partiții
Indici ajuta la eliminarea cantități relativ mici. Cu toate acestea, dacă aveți în mod constant pentru a elimina o mulțime (ca în cazul nostru), este necesar să se uite la partiționarea. Partiționarea vă permite să împartă masa în mai multe blocuri fizice
Acest lucru înseamnă că vom fi capabili să manipuleze partiții individuale. Și în loc de a scoate un număr mare de intrări, vom fi în măsură să le unifice într-o singură unitate (partiție) și scoateți-l într-o singură operație.
Pentru a testa acest lucru în practică, vom crea un tabel simplu al acestei structuri:
# Id-ul, poziția și data creării titlu
Umple-l cu datele de testare (zeci de mii de înregistrări) și să șteargă datele:
# Ștergeți unele date dintr-un tabel
Cererea este destul de lent, eliminând circa 25 mii de intrări .:
Să ne asigurăm că problema nu este în indexul (l-am creat, dar doar în cazul, verificați):
A se vedea că indexul este utilizat - totul este bine aici:
De asemenea, verificați variabila de sistem care indică numărul de înregistrări șterse din toate tabelele InnoDB:
# A se vedea numărul de rânduri șterse
Noi conducem un experiment în mediul de testare securizat, astfel încât alte la distanță nu se întâmplă.
Alegerea sistemului de partiționare
Pe măsură ce va decide eliminarea problemei, trebuie să avem un sistem în care vom putea elimina în mod convenabil (curat) întreaga partiție. Avem nevoie pentru a șterge datele într-o oră, așa că vom crea o partiție HASH pe baza orelor de câmp datetime.
# 24 partiții, deoarece 24 de ore pe zi
Doar încercați să priviți distribuția partiției noastre de date:
# Numărul Partiția va corespunde coloana ora datetime
După cum puteți vedea, datele din tabel sunt plasate doar două partiții. Ele corespund curent și ora precedentă. Ceea ce avem nevoie - este de a curăța partiția pentru ora pe care nu mai avem nevoie. În acest scop, acolo TRUNCATE operație.
# Această operațiune a fost executat pentru (0.01 sec)
Dacă vom verifica metru distanță înregistrările InnoDB, vom vedea acolo:
# Valoarea nu este schimbat
Acest lucru confirmă faptul că TRUNCATE lucrări fundamental nu ca DELETE. In loc de a șterge fiecare intrare, masa (sau partiția) este eliminată la nivelul structurii. Dacă este foarte dur, apoi scoate fișierul vechi de baze de date Mysql și creează una nouă. Și această operație este mult mai rapid de eliminare progresivă.
Dacă aveți nevoie pentru a elimina cantități mari de date de la Mysql, urmați cele două sfaturi:- Construiți indexează de prelevare de probe de viteză atunci când scoateți, înlocuirea DELETE FROM pe EXPLICAȚI SELECT COUNT (*) FROM.
- Utilizați partiționare și PARTITIE TRUNCATE pentru a elimina în mod eficient un număr mare de rânduri.
