So lösen Sie das Problem, dass der MySQL-Löschvorgang tatsächlich ein gefälschter Löschvorgang ist

Der MySQL-Löschvorgang ist eigentlich ein gefälschter Löschvorgang.

In InnoDB löscht Ihr Löschvorgang die gelöschten Daten nicht tatsächlich als gelöscht, Sie verwenden also „delete“, um sie zu löschen Der von der Tabellendatei auf der Festplatte belegte Speicherplatz wird vorerst nicht kleiner. Wir können es anhand eines Beispiels überprüfen

Lassen Sie uns dem Beispiel im vorherigen Artikel folgen. Erstellen Sie zunächst eine gespeicherte Prozedur, fügen Sie 100.000 Daten ein und sehen Sie dann, wie viel Platz diese 100.000 Daten belegen.

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB;

#定义分割符号，mysql 默认分割符为分号;，这里定义为 //
#分隔符的作用主要是告诉mysql遇到下一个 // 符号即执行上面这一整段sql语句
delimiter //
 
#创建一个存储过程，并命名为 testData
create procedure testData() 
 
#下面这段就是表示循环往表里插入10w条数据
begin
  declare i int;
  set i=1;
  while(i<=100000)do
    insert into t values(i, i, i);
    set i=i+1;
  end while;
end //  #这里遇到//符号，即执行上面一整段sql语句
 
delimiter ; #恢复mysql分隔符为;
 
call testData(); #调用存储过程

#下面这两条命令可以查看表文件所占空间大小
mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') from tables where table_schema='test' AND table_name='t';
+-------------------------------------------------+
| concat(round(sum(DATA_LENGTH/1024/1024),2),'M') |
+-------------------------------------------------+
| 3.52M                                           |
+-------------------------------------------------+
1 row in set (0.04 sec)

Sie können sehen, dass 100.000 Daten 3,52 MB Speicherplatz in MySQL belegen. Führen wir also den Löschbefehl delete from t aus und werfen wir einen Blick darauf.

#先删除表所有数据，再重新查看表文件大小
mysql> delete from t;
Query OK, 100000 rows affected (0.46 sec)
 
mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') from tables where table_schema='test' AND table_name='t';
+-------------------------------------------------+
| concat(round(sum(DATA_LENGTH/1024/1024),2),'M') |
+-------------------------------------------------+
| 3.52M                                           |
+-------------------------------------------------+
1 row in set (0.00 sec)

Anhand der Ergebnisse können wir feststellen, dass sich der von der Tabelle belegte Speicherplatz nicht ändert. Der Löschvorgang löscht die Daten nicht tatsächlich und der Tabellenbereich auch nicht freigegeben.

Diese gelöschten Datensatzzeilen sind nur zum Löschen markiert und können beim nächsten Mal wiederverwendet werden. Ein Datensatz, der die Bedingungen erfüllt, kann direkt an dieser markierten Position eingefügt werden.

Wenn wir beispielsweise einen Datensatz mit der ID = 500 unter den Datensätzen mit der ID zwischen 300 und 600 löschen, wird dieser Datensatz als gelöscht markiert. Wenn beim nächsten Mal ein Datensatz mit der ID = 400 eingefügt werden soll, dann der Standort Zum Löschen markierte Dateien mit der ID=500 können wiederverwendet werden. Diese Situation wird als „Wiederverwendung von Zeilendatensätzen“ bezeichnet.

Eine andere Situation ist die Wiederverwendung von Datenseiten, was bedeutet, dass die gesamte Datenseite markiert und gelöscht wurde, sodass die gesamte Datenseite wiederverwendet werden kann. Im Gegensatz zur Wiederverwendung von Zeilendatensätzen gibt es bei der Wiederverwendung von Datenseiten fast keine Einschränkungen für die Daten einzufügen.

Nehmen wir das obige Einfügen als Beispiel: Wenn der einzufügende Datensatz die ID = 1000 hat, kann die Positions-ID = 500 nicht wiederverwendet werden. Wenn jedoch eine gesamte Datenseite wiederverwendet werden kann, spielt die ID keine Rolle Der Wert ist: Kann auf dieser Seite wiederverwendet werden. Diese zum Löschen markierten Datensätze sind tatsächlich ein Loch. Sie haben das Gefühl, dass sie einen Schacht besetzen, ohne einen Scheiß zu machen. Sie sind nicht nur eine Platzverschwendung, sondern beeinträchtigen auch die Abfrageeffizienz.

Denn Sie müssen wissen, dass MySQL Daten in Einheiten von Datenseiten auf der untersten Ebene speichert und liest. Jedes Mal, wenn Sie Daten von der Festplatte lesen, lesen Sie jedoch jedes Mal, wenn Sie auf eine Datenseite zugreifen zu einem Festplatten-IO-Vorgang. Festplatten-IO ist im Vergleich zur Speicherzugriffsgeschwindigkeit recht langsam.

Wenn Sie also darüber nachdenken: Wenn eine Tabelle viele Datenlücken aufweist, werden die Daten, für deren Speicherung ursprünglich nur eine Datenseite erforderlich war, von vielen Lücken belegt, und es müssen weitere Datenseiten hinzugefügt werden Wenn MySQL dieselben Daten abfragt, müssen dementsprechend die Festplatten-E/A-Vorgänge erhöht werden, was sich auf die Abfragegeschwindigkeit auswirkt.

Tatsächlich führen nicht nur Löschvorgänge zu Datenlücken, sondern auch Einfügungen und Aktualisierungen. Ich werde hier nicht auf Details eingehen, ich weiß es einfach.

Daher führt eine große Anzahl häufiger Hinzufügungen, Löschungen und Änderungen zu einer Datentabelle, die unweigerlich zu Datenlücken führt, Platz verschwendet und die Abfrageeffizienz beeinträchtigt Schnelle Abfragen werden immer langsamer.

In diesem Fall können wir normalerweise den folgenden Befehl verwenden, um das Datenlochproblem zu lösen.

optimize table t

Das Prinzip dieses Befehls besteht darin, die Tabelle neu zu erstellen, das heißt, eine temporäre Tabelle B zu erstellen, dann alle Daten in Tabelle A (die Tabelle mit Datenlücken) abzufragen, dann alle Daten erneut in die temporäre Tabelle B einzufügen und Zum Schluss ersetzen Sie einfach Tabelle A durch temporäre Tabelle B. Dies ist der Prozess der Neuerstellung der Tabelle.

Lass es uns noch einmal versuchen.

Sehen Sie sich den Effekt an

mysql> optimize table t;
+--------+----------+----------+-------------------------------------------------------------------+
| Table  | Op       | Msg_type | Msg_text                                                          |
+--------+----------+----------+-------------------------------------------------------------------+
| test.t | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.t | optimize | status   | OK                                                                |
+--------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.39 sec)
 
mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') from tables where table_schema='test' AND table_name='t';
+-------------------------------------------------+
| concat(round(sum(DATA_LENGTH/1024/1024),2),'M') |
+-------------------------------------------------+
| 0.02M                                           |
+-------------------------------------------------+
1 row in set (0.00 sec)

Sie können sehen, dass die Tabellendateigröße 0,02 MB beträgt, was darauf hinweist, dass der Tabellenbereich freigegeben wurde. Diese 0,02 MB sollten die Größe der Datei sein, die die Tabellenstruktur definiert.

Darüber hinaus kann der folgende Befehl auch zum Neuaufbau der Tabelle verwendet werden, wodurch der gleiche Effekt wie oben erzielt werden kann. Es wird empfohlen, den folgenden Befehl zu verwenden.

alter table t engine=InnoDB

Beachten Sie, dass der Inhalt dieses Artikels auf der InnoDB-Engine basiert und es bei anderen Engines einige Unterschiede geben kann.

Das obige ist der detaillierte Inhalt vonSo lösen Sie das Problem, dass der MySQL-Löschvorgang tatsächlich ein gefälschter Löschvorgang ist. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!