Warum MySQL das Löschen von Daten nicht empfiehlt

Inhaltsverzeichnis

Vorwort

InnoDB-Speicherarchitektur

Innodb-Tabellenbereich

Inndo Speicherverteilung

Erstellen Sie eine leere Tabelle, um Speicherplatzänderungen anzuzeigen
Räumliche Änderungen nach dem Einfügen von Daten
Speicherplatzänderungen nach dem Löschen von Daten

Fragmentierung in Innodb

Die Erzeugung von Fragmenten
Recycling von Bauschutt

Die Auswirkungen von delete auf SQL

SQL-Ausführungsstatus vor dem Löschen
SQL-Ausführung nach dem Löschen

löschenOptimierungsvorschläge

Kontrollieren Sie die Berechtigungen für Geschäftskonten
löschen zum Markieren einer Löschung

Datenarchivierungsmethode

Gängige Datenarchivierungsmethoden
Optimierte Archivierungsmethode

Zusammenfassen

Vorwort

Ich war für mehrere Systeme mit wachsendem Geschäftsvolumen verantwortlich und die in MySQL gespeicherte Datenmenge nahm dramatisch zu. Ich dachte damals, dass die Geschäftsseite unethisch handelte und einen Überraschungsangriff startete, meine Reaktionsunfähigkeit ausnutzte und die Größe vieler Tabellen schnell auf Milliardenniveau erhöhte. Ich war unvorsichtig und wich nicht aus, was dazu führte, dass das SQL für die mit ihnen verknüpften Tabellen sehr langsam wurde und sich auch die Reaktionszeit der Anwendungsschnittstelle verlängerte, was sich auf die Benutzererfahrung auswirkte.

Danach suchte ich die Geschäftspartei auf, kritisierte sie und forderte sie auf, sich an die Ethik der Gesellschaft zu halten. Sie entschuldigten sich schnell bei mir und die Angelegenheit wurde fallengelassen. Als ich ging, sagte ich ihnen, sie sollten das beim nächsten Mal nicht wieder tun und über ihre Fehler nachdenken.

Egal wie sehr Sie fluchen, das Problem muss immer noch gelöst werden. Als ich die Gründe analysierte, stellte ich fest, dass das Datenvolumen einiger Tabellen sehr schnell wuchs und das entsprechende SQL viele ungültige Daten scannte, was das SQL verlangsamte. Nach der Bestätigung waren diese großen Tabellen alle Daten vom Typ Fluss, Datensatz und Protokoll, die nur 1 bis 3 Monate aufbewahrt werden mussten. Zu diesem Zeitpunkt war es notwendig, die Tabellendaten zu bereinigen, um eine Verschlankung zu erreichen. Normalerweise würden die Leute daran denken, zum Bereinigen die Methode Einfügen + Löschen zu verwenden.

In diesem Artikel erkläre ich, warum das Löschen von Daten im Hinblick auf die Speicherplatzverteilung von InnoDB nicht zu empfehlen ist, mache Optimierungsvorschläge und erkläre, welche Auswirkungen das Löschen auf die Leistung hat.

InnoDB-Speicherarchitektur

Wie aus diesem Bild ersichtlich ist, besteht die InnoDB-Speicherstruktur hauptsächlich aus zwei Teilen: der logischen Speicherstruktur und der physischen Speicherstruktur.

Logisch besteht es aus Tablespace -> Segment oder Inode -> Extent -> Datenseite. Die logische Verwaltungseinheit von Innodb ist das Segment, und die kleinste Einheit der Speicherplatzzuweisung ist der Extent. Jedem Segment werden 32 Seiten aus dem Tablespace FREE_PAGE zugewiesen. Wenn diese 32 Seiten nicht ausreichen, werden sie nach den folgenden Prinzipien erweitert: Wenn der aktuelle Extent kleiner als 1 ist, wird er um 1 Extent erweitert; wenn der Tablespace kleiner als 32 MB ist, wird jeweils ein Extent erweitert; wenn der Tablespace größer als 32 MB ist, werden jeweils 4 Extents erweitert.

Physisch besteht es hauptsächlich aus Systembenutzerdatendateien und Protokolldateien. Die Datendateien speichern hauptsächlich MySQL-Wörterbuchdaten und Benutzerdaten. Die Protokolldateien zeichnen die Änderungen der Datenseiten auf und werden zur Wiederherstellung verwendet, wenn MySQL abstürzt.

Innodb-Tabellenbereich

Der InnoDB-Speicher umfasst drei Arten von Tablespaces: System-Tablespace, Benutzer-Tablespace und Undo-Tablespace.

**Systemtabellenbereich: **Speichert hauptsächlich Daten des internen MySQL-Datenwörterbuchs, z. B. Daten unter information_schema.

**Benutzertabellenbereich: **Wenn innodb_file_per_table=1 aktiviert ist, wird die Datentabelle vom Systemtabellenbereich getrennt und in einer Datendatei mit dem Namen table_name.ibd gespeichert, und die Strukturinformationen werden in der Datei table_name.frm gespeichert.

**Undo-Tablespace:** Speichert Undo-Informationen. Beispielsweise verwenden sowohl Snapshot Consistency Read als auch Flashback Undo-Informationen.

Ab MySQL 8.0 dürfen Benutzer Tablespaces definieren. Die spezifische Syntax lautet wie folgt:

CREATE TABLESPACE Tabellenbereichsname
  ADD DATAFILE 'Dateiname' #Datendateiname USE LOGFILE GROUP logfile_group #benutzerdefinierte Protokolldateigruppe, normalerweise 2 Protokolldateien pro Gruppe.
  [EXTENT_SIZE [=] extent_size] #Extent-Größe[INITIAL_SIZE [=] initial_size] #Initialisierungsgröße[AUTOEXTEND_SIZE [=] autoextend_size] #Größe automatisch erweitern[MAX_SIZE [=] max_size] #Maximale Größe einer einzelnen Datei, das Maximum beträgt 32 G.
  [NODEGROUP [=] nodegroup_id] #Knotengruppe [WAIT]
  [KOMMENTAR [=] Kommentartext]
  ENGINE[=] Enginename

Der Vorteil dabei ist, dass Sie die heißen und kalten Daten trennen und sie jeweils auf HDD und SSD speichern können, wodurch Sie nicht nur einen effizienten Datenzugriff erreichen, sondern auch Kosten sparen. Sie können beispielsweise zwei 500-G-Festplatten hinzufügen, eine Datenträgergruppe vg erstellen, den logischen Datenträger lv aufteilen, ein Datenverzeichnis erstellen und den entsprechenden lv mounten. Angenommen, die beiden aufgeteilten Verzeichnisse sind /hot_data und /cold_data.

Auf diese Weise können Kerngeschäftstabellen wie Benutzertabellen und Auftragstabellen auf Hochleistungs-SSD-Festplatten gespeichert werden, und einige Protokolle und Flusstabellen können auf normalen Festplatten gespeichert werden. Die wichtigsten Betriebsschritte sind wie folgt:

#Hot Data-Tablespace erstellen, Tablespace tbs_data_hot erstellen, Datendatei „/hot_data/tbs_data_hot01.dbf“ hinzufügen, max. Größe 20 G;
#Erstellen Sie die Kerngeschäftstabelle und speichern Sie sie im Hot Data-Tablespace. Erstellen Sie eine Tabelle „Booking“ (ID Bigint, nicht null, Primärschlüssel, Auto-Inkrement, ……) Tablespace tbs_data_hot;
#Cold-Data-Tablespace erstellen, Tablespace tbs_data_cold erstellen, Datendatei „/hot_data/tbs_data_cold01.dbf“ hinzufügen, max. Größe 20 G;
#Protokoll-, Journal- und Sicherungstabellen erstellen und im Cold-Data-Tablespace speichern. create table payment_log(id bigint not null primary key auto_increment, …… ) tablespace tbs_data_cold;
#Sie können die Tabelle in einen anderen Tabellenbereich verschieben. alter table payment_log tablespace tbs_data_hot;

Inndo Speicherverteilung

Erstellen Sie eine leere Tabelle, um Speicherplatzänderungen anzuzeigen

mysql> Tabelle erstellen Benutzer (ID Bigint nicht null Primärschlüssel Auto_Increment, 
  -> Name varchar(20) nicht null Standard '' Kommentar 'Name', 
  -> Alter tinyint nicht null Standard 0 Kommentar 'Alter', 
  -> Geschlecht char(1) nicht null Standard 'M' Kommentar 'Geschlecht',
  -> Telefon varchar(16) nicht null Standard '' Kommentar 'Telefonnummer',
  -> create_time datetime NICHT NULL STANDARD CURRENT_TIMESTAMP KOMMENTAR 'Erstellungszeit',
  -> update_time datetime NICHT NULL STANDARD CURRENT_TIMESTAMP BEI UPDATE CURRENT_TIMESTAMP KOMMENTAR 'Änderungszeit'
  ->) Engine = InnoDB DEFAULT CHARSET=utf8mb4 COMMENT 'Benutzerinformationstabelle';
Abfrage OK, 0 Zeilen betroffen (0,26 Sek.)

# ls -lh user1.ibd 
-rw-r----- 1 mysql mysql 96K 6. Nov. 12:48 user.ibd

Wenn der Parameter innodb_file_per_table auf 1 gesetzt ist, wird beim Anlegen einer Tabelle automatisch ein Segment angelegt und ein Extent zugeordnet, der 32 Datenseiten zur Datenspeicherung enthält. Die Standardgröße der so erstellten leeren Tabelle beträgt 96 KB. Nachdem der Extent aufgebraucht ist, werden 64 Verbindungsseiten angefordert. Auf diese Weise kann für einige kleine Tabellen oder Undo-Segmente zu Beginn weniger Speicherplatz angefordert werden, um Festplattenkapazitäts-Overhead zu sparen.

# python2 py_innodb_page_info.py -v /data2/mysql/test/user.ibd
Seitenoffset 00000000, Seitentyp <File Space Header>
Seitenoffset 00000001, Seitentyp <Puffer-Bitmap einfügen>
Seitenoffset 00000002, Seitentyp <Dateisegment-Inode>
Seitenoffset 00000003, Seitentyp <B-Baumknoten>, Seitenebene <0000>
Seitenoffset 00000000, Seitentyp <Frisch zugewiesene Seite>
Seitenoffset 00000000, Seitentyp <Frisch zugewiesene Seite>
Gesamtzahl der Seiten: 6: #Gesamtzahl der zugewiesenen Seiten. Frisch zugewiesene Seiten: 2 #Verfügbare Datenseite. Bitmap des Einfügepuffers: 1 #Einfügepufferseite. Dateibereichsheader: 1 #Dateibereichsheader. B-Baumknoten: 1 #Datenseite. Dateisegment-Inode: 1 #Dateiseiten-Inode, wenn er sich auf ibdata1.ibd befindet, gibt es mehrere Inodes.

Räumliche Änderungen nach dem Einfügen von Daten

mysql> TRENNUNGSZEICHEN $$
mysql> PROZEDUR ERSTELLEN insert_user_data(num INTEGER) 
  -> BEGIN
  -> DECLARE v_i int unsigned DEFAULT 0;
  -> setze Autocommit = 0;
  -> WHILE v_i < Zahl DO
  -> in Benutzerwerte („Name“, „Alter“, „Geschlecht“, „Telefon“) einfügen (CONCAT(„lyn“, „v_i“), mod(v_i“, „120“), „M“, CONCAT(„152“, „ROUND(RAND(1)*100000000“)));
  : -> SETZE v_i = v_i+1;
  -> ENDE WÄHREND;
  -> festschreiben;
  -> ENDE $$
Abfrage OK, 0 Zeilen betroffen (0,01 Sek.)
mysql> TRENNUNGSZEICHEN ;

#100.000 Daten einfügenmysql> call insert_user_data(100000);
Abfrage OK, 0 Zeilen betroffen (6,69 Sek.)

# ls -lh Benutzer.ibd
-rw-r----- 1 mysql mysql 14M 6. Nov. 10:58 /data2/mysql/test/user.ibd

# python2 py_innodb_page_info.py -v /data2/mysql/test/user.ibd
Seitenoffset 00000000, Seitentyp <File Space Header>
Seitenoffset 00000001, Seitentyp <Puffer-Bitmap einfügen>
Seitenoffset 00000002, Seitentyp <Dateisegment-Inode>
Seitenversatz 00000003, Seitentyp <B-Baumknoten>, Seitenebene <0001> #Ein Nicht-Blattknoten wird hinzugefügt und die Höhe des Baums ändert sich von 1 auf 2.
........................................................
Seitenoffset 00000000, Seitentyp <Frisch zugewiesene Seite>
Gesamtseitenzahl: 896:
Frisch zugewiesene Seite: 493
Puffer-Bitmap einfügen: 1
Dateibereichsheader: 1
B-Baum-Knoten: 400
Dateisegment-Inode: 1

Speicherplatzänderungen nach dem Löschen von Daten

mysql> wähle min(id),max(id),count(*) vom Benutzer aus;
+---------+---------+----------+
| min(id) | max(id) | Anzahl(*) |
+---------+---------+----------+
| 1 | 100000 | 100000 |
+---------+---------+----------+
1 Zeile im Satz (0,05 Sek.)
#50.000 Datenelemente löschen. Theoretisch sollte sich der Speicherplatz von 14 MB auf etwa 7 MB erhöhen.
mysql> löschen vom Benutzerlimit 50000;
Abfrage OK, 50000 Zeilen betroffen (0,25 Sek.)

 
#Die Datendateigröße beträgt immer noch 14 MB und ist nicht geschrumpft.
# ls -lh /data2/mysql/test/user1.ibd 
-rw-r----- 1 mysql mysql 14M 6. Nov. 13:22 /data2/mysql/test/user.ibd

#Datenseiten werden nicht wiederverwendet.
# python2 py_innodb_page_info.py -v /data2/mysql/test/user.ibd
Seitenoffset 00000000, Seitentyp <File Space Header>
Seitenoffset 00000001, Seitentyp <Puffer-Bitmap einfügen>
Seitenoffset 00000002, Seitentyp <Dateisegment-Inode>
Seitenoffset 00000003, Seitentyp <B-Baumknoten>, Seitenebene <0001>
........................................................
Seitenoffset 00000000, Seitentyp <Frisch zugewiesene Seite>
Gesamtseitenzahl: 896:
Frisch zugewiesene Seite: 493
Puffer-Bitmap einfügen: 1
Dateibereichsheader: 1
B-Baum-Knoten: 400
Dateisegment-Inode: 1
#In MySQL ist es zum Löschen markiert.

mysql> verwende Informationsschema;

Datenbank geändert
mysql> WÄHLEN SIE A.SPACE ALS TBL_SPACEID, A.TABLE_ID, A.NAME ALS TABLE_NAME, FILE_FORMAT, ROW_FORMAT, SPACE_TYPE, B.INDEX_ID, B.NAME ALS INDEX_NAME, PAGE_NO, B.TYPE ALS INDEX_TYPE AUS INNODB_SYS_TABLES A LEFT JOIN INNODB_SYS_INDEXES B ON A.TABLE_ID = B.TABLE_ID, wobei A.NAME = "test/user1" ist;
+-------------+----------+------------+-------------+-------------+--------------+-------------+------------+------------+---------+------------+---------+
| TBL_SPACEID | TABELLEN-ID | TABELLENNAME | DATEIFORMAT | ZEILENFORMAT | SPACE_TYPE | INDEX_ID | INDEX_NAME | SEITENNUMMER | INDEX_TYPE |
+-------------+----------+------------+-------------+-------------+--------------+-------------+------------+------------+---------+------------+---------+
| 1283 | 1207 | Test/Benutzer | Barracuda | Dynamisch | Einzeln | 2236 | PRIMÄR | 3 | 3 |
+-------------+----------+------------+-------------+-------------+--------------+-------------+------------+------------+---------+------------+---------+
1 Zeile im Satz (0,01 Sek.)

PAGE_NO = 3 gibt an, dass die Stammseite des B-Baums Seite 3 ist, und INDEX_TYPE = 3 ist ein gruppierter Index. INDEX_TYPE-Werte sind wie folgt:
0 = nicht eindeutiger sekundärer Index; 
1 = automatisch generierter gruppierter Index (GEN_CLUST_INDEX); 
2 = eindeutiger nicht gruppierter Index; 
3 = gruppierter Index; 
32 = Volltextindex;
#Verkleinern Sie den Raum und beobachten Sie später

MySQL löscht den Speicherplatz intern nicht wirklich, sondern markiert ihn zum Löschen, d. h., es ändert delflag:N in delflag:Y. Nach dem Commit wird er in die Löschliste gelöscht. Wenn beim nächsten Mal ein größerer Datensatz eingefügt wird, wird der Speicherplatz nach dem Löschen nicht wiederverwendet. Wenn der eingefügte Datensatz kleiner oder gleich dem gelöschten Datensatz ist, wird der Speicherplatz wiederverwendet. Dieser Inhalt kann mit dem Innblock-Tool von Zhishutang analysiert werden.

Fragmentierung in Innodb

Die Erzeugung von Fragmenten

Wir wissen, dass im Dateisystem gespeicherte Daten nicht immer 100 % des ihnen zugewiesenen physischen Speicherplatzes nutzen können. Das Löschen von Daten hinterlässt einige „Löcher“ auf der Seite, oder zufälliges Schreiben (nichtlinearer Anstieg des Clusterindex) führt zu Seitenaufteilungen. Seitenaufteilungen führen dazu, dass der Seitennutzungsplatz weniger als 50 % beträgt. Darüber hinaus führt das Hinzufügen, Löschen und Ändern der Tabelle zu zufälligen Hinzufügungen, Löschungen und Änderungen der entsprechenden sekundären Indexwerte, was ebenfalls einige „Löcher“ auf den Datenseiten in der Indexstruktur verursacht. Obwohl diese Löcher möglicherweise wiederverwendet werden, führen sie letztendlich dazu, dass physischer Speicherplatz ungenutzt bleibt, was zu Fragmentierung führt.

Gleichzeitig lässt Innodb, selbst wenn der Füllfaktor auf 100 % eingestellt ist, proaktiv 1/16 des Speicherplatzes in gruppierten Indexseiten für zukünftiges Indexwachstum frei, um einen durch Aktualisierungen verursachten Zeilenüberlauf zu verhindern.

mysql> Tabellenschema auswählen,
  -> Tabellenname,ENGINE,
  -> runden(DATENLÄNGE/1024/1024+INDEXLÄNGE/1024/1024) total_mb,TABELLENZEILEN,
  -> runden(DATENLÄNGE/1024/1024) Daten_MB, runden(INDEX_LÄNGE/1024/1024) Index_MB, runden(FREIE DATEN/1024/1024) freie_MB, runden(FREIE DATEN/DATENLÄNGE*100,2) freies_Verhältnis
  -> aus information_schema.TABLES, wobei TABLE_SCHEMA = 'test'
  -> und TABLE_NAME = 'Benutzer';
+--------------+------------+--------+----------+------------+----------+----------+----------+---------+------------+
| Tabellenschema | Tabellenname | ENGINE | Gesamtmenge_MB | TABELLENREIHEN | Daten_MB | Index_MB | Freimenge_MB | Freimenge_Verhältnis |
+--------------+------------+--------+----------+------------+----------+----------+----------+---------+------------+
| Test | Benutzer | InnoDB | 4 | 50000 | 4 | 0 | 6 | 149,42 |
+--------------+------------+--------+----------+------------+----------+----------+----------+---------+------------+
1 Zeile im Satz (0,00 Sek.)

Data_free ist die Anzahl der zugewiesenen Bytes, die nicht verwendet werden, und bedeutet nicht, dass der Speicherplatz vollständig fragmentiert ist.

Recycling von Bauschutt

Für InnoDB-Tabellen können Sie den folgenden Befehl verwenden, um Fragmente wiederzuverwenden und Speicherplatz freizugeben. Dies ist ein zufälliger Lese-E/A-Vorgang, der zeitaufwändig ist und normale DML-Vorgänge für die Tabelle blockiert. Außerdem wird mehr Speicherplatz benötigt. Bei RDS kann dies dazu führen, dass der Speicherplatz sofort voll ist, die Instanz sofort gesperrt wird und die Anwendung keine DML-Vorgänge ausführen kann. Daher ist die Ausführung in einer Onlineumgebung verboten.

#InnoDB-Fragmentwiederherstellung ausführenmysql> alter table user engine=InnoDB;
Abfrage OK, 0 Zeilen betroffen (9,00 Sek.)
Datensätze: 0 Duplikate: 0 Warnungen: 0

##Nach der Ausführung wird die Datendateigröße von 14 MB auf 10 M reduziert.
# ls -lh /data2/mysql/test/user1.ibd 
-rw-r----- 1 mysql mysql 10M 6. Nov. 16:18 /data2/mysql/test/user.ibd

mysql> wähle table_schema, table_name, ENGINE, round(DATA_LENGTH/1024/1024+ INDEX_LENGTH/1024/1024) total_mb, TABLE_ROWS, round(DATA_LENGTH/1024/1024) data_mb, round(INDEX_LENGTH/1024/1024) index_mb, round(DATA_FREE/1024/1024) free_mb, round(DATA_FREE/DATA_LENGTH*100,2) free_ratio aus information_schema.TABLES, wobei TABLE_SCHEMA= „Test“ und TABLE_NAME= „Benutzer“;
+--------------+------------+--------+----------+------------+----------+----------+----------+---------+------------+
| Tabellenschema | Tabellenname | ENGINE | Gesamtmenge_MB | TABELLENREIHEN | Daten_MB | Index_MB | Freimenge_MB | Freimenge_Verhältnis |
+--------------+------------+--------+----------+------------+----------+----------+----------+---------+------------+
| Test | Benutzer | InnoDB | 5 | 50000 | 5 | 0 | 2 | 44,29 |
+--------------+------------+--------+----------+------------+----------+----------+----------+---------+------------+
1 Zeile im Satz (0,00 Sek.)

Die Auswirkungen von delete auf SQL

SQL-Ausführungsstatus vor dem Löschen

#1 Million Daten einfügenmysql> call insert_user_data(1000000);
Abfrage OK, 0 Zeilen betroffen (35,99 Sek.)

#Zugehörige Indizes hinzufügenmysql> alter table user add index idx_name(name), add index idx_phone(phone);
Abfrage OK, 0 Zeilen betroffen (6,00 Sek.)
Datensätze: 0 Duplikate: 0 Warnungen: 0

#Indexstatistiken für die Tabellemysql> Index vom Benutzer anzeigen;
+-------+------------+-----------+--------------+---------------+---------------+---------------+-------------+----------+---------+---------+---------+---------+---------+---------+---------+-----------+
| Tabelle | Nicht_eindeutig | Schlüsselname | Sequenz_im_Index | Spaltenname | Sortierung | Kardinalität | Unterteil | Gepackt | Null | Indextyp | Kommentar | Indexkommentar |
+-------+------------+-----------+--------------+---------------+---------------+---------------+-------------+----------+---------+---------+---------+---------+---------+---------+---------+-----------+
| Benutzer | 0 | PRIMARY | 1 | ID | A | 996757 | NULL | NULL | | BTREE | | |
| Benutzer | 1 | idx_name | 1 | Name | A | 996757 | NULL | NULL | | BTREE | | |
| Benutzer | 1 | idx_phone | 1 | Telefon | A | 2 | NULL | NULL | | BTREE | | |
+-------+------------+-----------+--------------+---------------+---------------+---------------+-------------+----------+---------+---------+---------+---------+---------+---------+---------+-----------+
3 Zeilen im Satz (0,00 Sek.)

#Statusvariable zurücksetzen countmysql> flush status;
Abfrage OK, 0 Zeilen betroffen (0,00 Sek.)

#SQL-Anweisung ausführenmysql> wähle ID, Alter, Telefonnummer vom Benutzer aus, bei dem der Name beispielsweise ‚lyn12%‘ lautet;
+--------+-----+-------------+
| ID | Alter | Telefonnummer |
+--------+-----+-------------+
| 124 | 3 | 15240540354 |
| 1231 | 30 | 15240540354 |
| 12301 | 60 | 15240540354 |
.............................
| 129998 | 37 | 15240540354 |
| 129999 | 38 | 15240540354 |
| 130000 | 39 | 15240540354 |
+--------+-----+-------------+
11111 Zeilen im Satz (0,03 Sek.)

mysql> erläutern Sie „select id, age,phone“ von Benutzer mit Namen wie „lyn12%“;
+----+-----------+----------+-----------+---------------+----------+----------+---------+------+---------+----------+-------------+---------------------------+
| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+-----------+----------+-----------+---------------+----------+----------+---------+------+---------+----------+-------------+---------------------------+
| 1 | SIMPLE | Benutzer | Bereich | idx_name | idx_name | 82 | NULL | 22226 | Indexbedingung wird verwendet |
+----+-----------+----------+-----------+---------------+----------+----------+---------+------+---------+----------+-------------+---------------------------+
1 Zeile im Satz (0,00 Sek.)

#Zeigen Sie die relevanten Statusvariablen anmysql> select * from information_schema.session_status where variable_name in('Last_query_cost','Handler_read_next','Innodb_pages_read','Innodb_data_reads','Innodb_pages_read');
+---------------------------------+----------------+
| VARIABLENNAME | VARIABLENWERT |
+---------------------------------+----------------+
| HANDLER_READ_NEXT | 11111 | #Anzahl der zum Lesen angeforderten Zeilen | INNODB_DATA_READS | 7868409 | #Gesamtzahl der physischen Datenlesevorgänge | INNODB_PAGES_READ | 7855239 | #Gesamtzahl der logischen Lesevorgänge | LAST_QUERY_COST | 10.499000 | #Kosten der SQL-Anweisungen, hauptsächlich einschließlich IO_COST und CPU_COST.
+---------------------------------+----------------+
4 Zeilen im Satz (0,00 Sek.)

SQL-Ausführung nach dem Löschen

#500.000 Daten löschenmysql> aus Benutzerlimit 500000 löschen;
Abfrage OK, 500000 Zeilen betroffen (3,70 Sek.)

#Tabellenstatistiken analysieren mysql> Tabelle analysieren Benutzer;
+--------------+---------+----------+----------+
| Tabelle | Op | Nachrichtentyp | Nachrichtentext |
+--------------+---------+----------+----------+
| test.user | analysieren | Status | OK |
+--------------+---------+----------+----------+
1 Zeile im Satz (0,01 Sek.)

#Statusvariable zurücksetzen countmysql> flush status;
Abfrage OK, 0 Zeilen betroffen (0,01 Sek.)

mysql> wähle ID, Alter, Telefonnummer vom Benutzer mit Namen wie „lyn12%“;
Leerer Satz (0,05 Sek.)

mysql> erläutern Sie „select id, age,phone“ von Benutzer mit Namen wie „lyn12%“;
+----+-----------+----------+-----------+---------------+----------+----------+---------+------+---------+----------+-------------+---------------------------+
| ID | Auswahltyp | Tabelle | Typ | mögliche Schlüssel | Schlüssel | Schlüssellänge | Ref. | Zeilen | Extra |
+----+-----------+----------+-----------+---------------+----------+----------+---------+------+---------+----------+-------------+---------------------------+
| 1 | SIMPLE | Benutzer | Bereich | idx_name | idx_name | 82 | NULL | 22226 | Indexbedingung wird verwendet |
+----+-----------+----------+-----------+---------------+----------+----------+---------+------+---------+----------+-------------+---------------------------+
1 Zeile im Satz (0,00 Sek.)

mysql> wähle * aus information_schema.session_status, wobei Variablenname in ('Letzte_Abfragekosten', 'Handler_read_next', 'Innodb_pages_read', 'Innodb_data_reads', 'Innodb_pages_read');
+---------------------------------+----------------+
| VARIABLENNAME | VARIABLENWERT |
+---------------------------------+----------------+
| HANDLER_READ_NEXT | 0 |
| INNODB_DATA_READS | 7868409 |
| INNODB_PAGES_READ | 7855239 |
| LAST_QUERY_COST | 10,499000 |
+---------------------------------+----------------+
4 Zeilen im Satz (0,00 Sek.)

Statistische Analyse der Ergebnisse

arbeiten	KOSTEN	Physische Lesezeiten	Logische Lesezeiten	Scan-Zeilen	Gibt die Anzahl der Zeilen zurück	Ausführungszeit
Initialisierungsstecker 100W	10,499000	7868409	7855239	22226	11111	30 ms
100W, 50W nach dem Zufallsprinzip löschen	10,499000	7868409	7855239	22226	0	50 ms

Dies zeigt auch, dass es bei normalen großen Tabellen unrealistisch ist, die Tabelle durch Löschen von Daten zu verschlanken. Löschen Sie Daten daher niemals mit „delete“, sondern verwenden Sie die elegante Markierungslöschung.

löschenOptimierungsvorschläge

Kontrollieren Sie die Berechtigungen für Geschäftskonten

Bei einem großen System ist es notwendig, Subsysteme entsprechend den Geschäftsmerkmalen aufzuteilen. Jedes Subsystem kann als Dienst betrachtet werden. Beispielsweise verfügt die Meituan-App über viele Dienste. Zu den Kerndiensten gehören Benutzerdienste, Suchdienste, Produktdienste, Standortdienste, Preisdienste usw. Jeder Dienst entspricht einer Datenbank. Für die Datenbank wird ein separates Konto erstellt. Es werden nur DML-Berechtigungen vergeben und keine Löschberechtigungen. Datenbankübergreifender Zugriff ist untersagt.

#Erstellen Sie eine Benutzerdatenbank und autorisieren Sie die Erstellung der Datenbank mt_user charset utf8mb4;
Gewähren Sie „w_user“@„%“, identifiziert durch „t$W*g@gaHTGi123456“, USAGE, SELECT, INSERT, UPDATE ON mt_user.*;
Berechtigungen leeren;

löschen zum Markieren einer Löschung

Es gibt vier gemeinsame Felder in der MySQL-Datenbankmodellierungsspezifikation, die grundsätzlich für jede Tabelle erforderlich sind. Gleichzeitig bietet das Erstellen eines Indexes für die Spalte create_time zwei Vorteile:

Einige Abfragegeschäftsszenarien haben einen Standardzeitraum, z. B. 7 Tage oder einen Monat, die nach Erstellungszeit gefiltert werden, und das Indexscannen ist schneller.
Einige Kerngeschäftstabellen müssen im T + 1-Verfahren aus dem Data Warehouse extrahiert werden. Beispielsweise müssen die Daten des Vortages jede Nacht um 00:30 Uhr extrahiert werden und alle werden nach create_time gefiltert.

`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Primärschlüssel-ID',
`is_deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Ob logisch gelöscht: 0: nicht gelöscht, 1: gelöscht',
`create_time` Zeitstempel NICHT NULL STANDARD CURRENT_TIMESTAMP KOMMENTAR 'Erstellungszeit',
`update_time` Zeitstempel NICHT NULL STANDARD CURRENT_TIMESTAMP BEI UPDATE CURRENT_TIMESTAMP KOMMENTAR 'Änderungszeit'

#Mit dem Löschmarker kann der Löschvorgang der Geschäftsschnittstelle in ein Update umgewandelt werden
Benutzersatz aktualisieren: is_deleted = 1, wobei user_id = 1213;

#Bei der Abfrage müssen Sie den Filter „is_deleted“ verwenden, um ID, Alter und Telefonnummer des Benutzers auszuwählen, wobei „is_deleted = 0“ und ein Name wie „lyn12%“ ist.

Datenarchivierungsmethode

Gängige Datenarchivierungsmethoden

#1. Erstellen Sie eine Archivtabelle, normalerweise durch Hinzufügen von _bak nach dem ursprünglichen Tabellennamen.
TABELLE `ota_order_bak` erstellen (
 `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT 'Primärschlüssel',
 `order_id` varchar(255) DEFAULT NULL COMMENT 'Bestell-ID',
 `ota_id` varchar(255) STANDARD NULL KOMMENTAR 'ota',
 `check_in_date` varchar(255) DEFAULT NULL COMMENT 'Check-in-Datum',
 `check_out_date` varchar(255) DEFAULT NULL COMMENT 'Check-out-Datum',
 `hotel_id` varchar(255) DEFAULT NULL COMMENT 'Hotel-ID',
 `Gastname` varchar(255) DEFAULT NULL COMMENT 'Kunde',
 `purcharse_time` Zeitstempel NULL DEFAULT NULL COMMENT 'Kaufzeit',
 `create_time` Datum/Uhrzeit DEFAULT NULL,
 `update_time` Datum/Uhrzeit DEFAULT NULL,
 `create_user` varchar(255) DEFAULT NULL,
 `update_user` varchar(255) DEFAULT NULL,
 `status` int(4) DEFAULT '1' COMMENT 'Status: 1 normal, 0 gelöscht',
 `hotel_name` varchar(255) DEFAULT NULL,
 `Preis` Dezimalzahl (10,0) DEFAULT NULL,
 `Bemerkung` Langtext,
 Primärschlüssel (`id`),
 SCHLÜSSEL `IDX_order_id` (`order_id`) MIT BTREE,
 SCHLÜSSEL `hotel_name` (`hotel_name`) USING BTREE,
 SCHLÜSSEL `ota_id` (`ota_id`) MIT BTREE,
 SCHLÜSSEL `IDX_purcharse_time` (`purcharse_time`) USING BTREE,
 SCHLÜSSEL `IDX_create_time` (`create_time`) MIT BTREE
) ENGINE=InnoDB STANDARD-CHARSET=utf8
PARTITION NACH BEREICH (bis_Tage(Erstellungszeit)) ( 
PARTITION p201808 WERTE KLEINER ALS (to_days('2018-09-01')), 
PARTITION p201809 WERTE KLEINER ALS (to_days('2018-10-01')), 
PARTITION p201810 WERTE KLEINER ALS (to_days('2018-11-01')), 
PARTITION p201811 WERTE KLEINER ALS (to_days('2018-12-01')), 
PARTITION p201812 WERTE KLEINER ALS (to_days('2019-01-01')), 
PARTITION p201901 WERTE KLEINER ALS (to_days('2019-02-01')), 
PARTITION p201902 WERTE KLEINER ALS (to_days('2019-03-01')), 
PARTITION p201903 WERTE KLEINER ALS (to_days('2019-04-01')), 
PARTITION p201904 WERTE KLEINER ALS (to_days('2019-05-01')), 
PARTITION p201905 WERTE KLEINER ALS (to_days('2019-06-01')), 
PARTITION p201906 WERTE KLEINER ALS (to_days('2019-07-01')), 
PARTITION p201907 WERTE KLEINER ALS (to_days('2019-08-01')), 
PARTITION p201908 WERTE KLEINER ALS (to_days('2019-09-01')), 
PARTITION p201909 WERTE KLEINER ALS (to_days('2019-10-01')), 
PARTITION p201910 WERTE KLEINER ALS (to_days('2019-11-01')), 
PARTITION p201911 WERTE KLEINER ALS (to_days('2019-12-01')), 
PARTITION p201912 WERTE KLEINER ALS (to_days('2020-01-01')));

#2. Ungültige Daten in die Originaltabelle einfügen (Datenaufbewahrungsdauer muss mit dem Entwickler bestätigt werden)
Erstellen Sie die Tabelle tbl_p201808 als „Select * from ota_order“, wobei „create_time“ zwischen „2018-08-01 00:00:00“ und „2018-08-31 23:59:59“ liegt.

#3. Tauschen Sie die Partition mit der Archivtabellenpartition „alter table ota_order_bak“ aus. Tauschen Sie die Partition p201808 mit der Tabelle tbl_p201808 aus.

#4. Löschen Sie die standardisierten Daten in der Originaltabelle, löschen Sie aus ota_order, wobei create_time zwischen „2018-08-01 00:00:00“ und „2018-08-31 23:59:59“ liegt, Limit 3000;

Optimierte Archivierungsmethode

#1. Erstellen Sie eine Zwischentabelle CREATE TABLE `ota_order_2020` (........) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION NACH BEREICH (bis_Tage(Erstellungszeit)) ( 
PARTITION p201808 WERTE KLEINER ALS (to_days('2018-09-01')), 
PARTITION p201809 WERTE KLEINER ALS (to_days('2018-10-01')), 
PARTITION p201810 WERTE KLEINER ALS (to_days('2018-11-01')), 
PARTITION p201811 WERTE KLEINER ALS (to_days('2018-12-01')), 
PARTITION p201812 WERTE KLEINER ALS (to_days('2019-01-01')), 
PARTITION p201901 WERTE KLEINER ALS (to_days('2019-02-01')), 
PARTITION p201902 WERTE KLEINER ALS (to_days('2019-03-01')), 
PARTITION p201903 WERTE KLEINER ALS (to_days('2019-04-01')), 
PARTITION p201904 WERTE KLEINER ALS (to_days('2019-05-01')), 
PARTITION p201905 WERTE KLEINER ALS (to_days('2019-06-01')), 
PARTITION p201906 WERTE KLEINER ALS (to_days('2019-07-01')), 
PARTITION p201907 WERTE KLEINER ALS (to_days('2019-08-01')), 
PARTITION p201908 WERTE KLEINER ALS (to_days('2019-09-01')), 
PARTITION p201909 WERTE KLEINER ALS (to_days('2019-10-01')), 
PARTITION p201910 WERTE KLEINER ALS (to_days('2019-11-01')), 
PARTITION p201911 WERTE KLEINER ALS (to_days('2019-12-01')), 
PARTITION p201912 WERTE KLEINER ALS (to_days('2020-01-01')));

#2. Fügen Sie gültige Daten in die Originaltabelle ein. Wenn das Datenvolumen etwa 1 Million beträgt, können Sie es außerhalb der Spitzenzeiten direkt einfügen. Wenn es relativ groß ist, wird empfohlen, dataX zu verwenden, mit dem sich Häufigkeit und Größe steuern lassen. Ich habe dataX zuvor mit Go gekapselt, um automatisch JSON-Dateien zu generieren und sie mit benutzerdefinierten Größen auszuführen.
einfügen in ota_order_2020, auswählen * aus ota_order, wobei create_time zwischen „2020-08-01 00:00:00“ und „2020-08-31 23:59:59“ liegt;

#3. Tabelle umbenennen, Tabelle „ota_order“ ändern, umbenennen in „ota_order_bak“; 
Tabelle ota_order_2020 ändern und in ota_order umbenennen;
#4. Differenzdaten in ota_order einfügen. Wählen Sie * aus ota_order_bak a, wo es nicht existiert (wählen Sie 1 aus ota_order b, wo a.id = b.id);
#5. Transformieren Sie ota_order_bak in eine partitionierte Tabelle. Wenn die Tabelle groß ist, ist es nicht empfehlenswert, sie direkt zu transformieren. Sie können zuerst eine partitionierte Tabelle erstellen und sie dann über dataX importieren.

#6. Nachfolgende Archivierungsmethode #Erstellen Sie eine allgemeine Zwischentabelle create table ota_order_mid like ota_order;
#Tauschen Sie die ungültige Datenpartition der Originaltabelle gegen die normale Tabelle aus. Alter Table ota_order. Tauschen Sie die Partition p201808 gegen die Tabelle ota_order_mid aus. 
##Tauschen Sie die Daten der gemeinsamen Tabelle mit der entsprechenden Partition der Archivtabelle aus. Alter Table ota_order_bak. Exchange Partition p201808 with Table ota_order_mid;

Auf diese Weise werden sowohl die Originaltabelle als auch die Archivtabelle nach Monaten partitioniert. Sie müssen nur eine Zwischentabelle erstellen und während der Nebengeschäftszeit zwei Partitionswechsel durchführen. Dadurch können nicht nur ungültige Daten gelöscht, sondern auch Speicherplatz wiederverwendet werden. Es gibt keine Speicherplatzfragmentierung und der Index der Tabelle und der SQL-Ausführungsplan werden dadurch nicht beeinträchtigt.

Zusammenfassen

Anhand der Verteilung des InnoDB-Speicherplatzes und der Auswirkungen des Löschens auf die Leistung können wir erkennen, dass durch das physische Löschen weder Speicherplatz freigegeben noch eine große Fragmentierung erzeugt werden kann, was zu häufigen Indexaufteilungen führt und die Stabilität von SQL-Ausführungsplänen beeinträchtigt.

Gleichzeitig wird bei der Wiederherstellung der Fragmente eine große Menge an CPU- und Festplattenspeicher verbraucht, was die normalen DML-Vorgänge in der Tabelle beeinträchtigt.

Auf der Ebene des Geschäftscodes sollte eine logische Markierung vorgenommen werden, um eine physische Löschung zu vermeiden. Um die Anforderungen an die Datenarchivierung zu erfüllen, kann die MySQL-Partitionstabellenfunktion verwendet werden, die alle DDL-Operationen umfasst und keine Fragmentierung erzeugt.

Eine weitere, bessere Lösung ist die Verwendung von Clickhouse. Mit Clickhouse können Datentabellen mit Lebenszyklus gespeichert werden und die TTL-Funktion kann zum automatischen Bereinigen ungültiger Daten verwendet werden.

Damit ist dieser Artikel darüber, warum MySQL das Löschen von Daten nicht empfiehlt, abgeschlossen. Weitere Informationen dazu, warum MySQL das Löschen von Daten nicht empfiehlt, finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den folgenden verwandten Artikeln. Ich hoffe, Sie werden 123WORDPRESS.COM auch in Zukunft unterstützen!

Das könnte Sie auch interessieren:

Zwei Möglichkeiten, MySQL daran zu hindern, Daten mit dem Löschbefehl zu löschen
Vergleich der MySQL Delete- und Truncate-Anweisungen
So geben Sie Speicherplatz frei, nachdem Sie Daten in MySQL gelöscht haben

<<: Verwenden von Zabbix zum Überwachen des Ogg-Prozesses (Linux-Plattform)

>>: Natives JS zum Implementieren der Seitenleiste zum Teilen