Comment résoudre le problème selon lequel l'opération de suppression de MySQL est en fait une fausse suppression

L'opération de suppression de MySQL est en fait une fausse suppression

Dans InnoDB, votre opération de suppression ne supprime pas réellement les données. Mysql marque simplement les données supprimées comme supprimées, vous utilisez donc delete pour les supprimer. l'espace occupé par le fichier de table sur le disque ne diminuera pas. Nous appelons cela fausse suppression pour l'instant.

Nous pouvons le vérifier avec un exemple

Suivons l'exemple de l'article précédent. Créez d'abord une procédure stockée, insérez 100 000 données, puis voyez combien d'espace ces 100 000 données occupent.

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)

Vous pouvez voir que 100 000 éléments de données occupent 3,52 Mo d'espace dans MySQL, alors exécutons la commande delete delete from t et jetons un œil.

#先删除表所有数据，再重新查看表文件大小
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)

D'après les résultats, nous pouvons constater qu'une fois les données de la table effacées, l'espace occupé par la table ne change pas. Cela vérifie la conclusion ci-dessus. L'opération de suppression ne supprime pas réellement les données, et l'espace de la table ne l'est pas. libéré.

Ces lignes d'enregistrement supprimées sont uniquement marquées pour suppression et peuvent être réutilisées la prochaine fois, un enregistrement qui remplit les conditions peut être directement inséré dans cette position marquée.

Par exemple, si nous supprimons un enregistrement avec l'identifiant = 500 parmi les enregistrements dont l'identifiant est compris entre 300 et 600, cet enregistrement sera marqué comme supprimé. S'il y a un enregistrement avec l'identifiant = 400 à insérer la prochaine fois, alors l'emplacement. marqué pour suppression avec l'identifiant = 500 peut être réutilisé. Cette situation est appelée réutilisation des enregistrements de ligne.

Une autre situation est la réutilisation de la page de données, ce qui signifie que la page de données entière a été marquée et supprimée, de sorte que la page de données entière peut être réutilisée. Différent de la réutilisation des enregistrements de ligne, de la réutilisation de la page de données. Il n'y a presque aucune restriction sur les données. à insérer.

En prenant l'insertion ci-dessus comme exemple, si l'enregistrement à insérer est id=1000, alors la position id=500 ne peut pas être réutilisée, mais s'il existe une page de données entière qui peut être réutilisée, alors quel que soit l'identifiant la valeur est, peut être réutilisé sur cette page.

Ces enregistrements qui ont été marqués pour suppression sont en fait un trou. Ils ont l'impression d'occuper un trou d'égout sans chier. Non seulement ils sont une perte d'espace, mais ils affecteront également l'efficacité des requêtes.

Parce qu'il faut savoir que MySQL stocke et lit les données en unités de pages de données dans la couche inférieure. Chaque fois que vous lisez des données sur le disque, vous lisez une page de données. Cependant, chaque fois que vous accédez à une page de données, elle correspond. à une opération d’E/S disque. L’E/S disque est assez lente par rapport à la vitesse d’accès à la mémoire.

Donc, si vous y réfléchissez, s'il y a un grand nombre de trous de données dans une table, les données qui n'ont besoin à l'origine que d'une seule page de données pour être enregistrées seront occupées par de nombreux trous, et d'autres pages de données devront être ajoutées à En conséquence, lorsque MySQL interroge les mêmes données, il doit augmenter les opérations d'E/S sur le disque, affectant ainsi la vitesse de requête.

En fait, non seulement les opérations de suppression provoqueront des trous de données, mais les insertions et les mises à jour provoqueront également des trous. Je n'entrerai pas dans les détails ici, sachez-le simplement.

Par conséquent, après qu'une table de données ait subi un grand nombre d'ajouts, de suppressions et de modifications fréquents, elle produira inévitablement des trous de données, un gaspillage d'espace et affectera l'efficacité des requêtes. Habituellement, dans un environnement de production, cela se manifestera directement comme à l'origine. la requête rapide deviendra de plus en plus lente.

Dans ce cas, nous pouvons généralement utiliser la commande suivante pour résoudre le problème du trou de données.

optimize table t

Le principe de cette commande est de reconstruire la table, c'est à dire de créer une table temporaire B, puis d'interroger toutes les données de la table A (la table avec des trous de données), puis de réinsérer toutes les données dans la table temporaire B, et enfin, remplacez simplement la table A par la table temporaire B. C'est le processus de reconstruction de la table.

Essayons à nouveau.

Regardez l'effet

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)

Vous pouvez voir que la taille du fichier de table est devenue 0,02M, indiquant que l'espace table a été libéré. ​​Ce 0,02M devrait être la taille du fichier qui définit la structure de la table.

De plus, la commande suivante peut également être utilisée pour reconstruire la table, ce qui peut obtenir le même effet que ci-dessus. Il est recommandé d'utiliser la commande suivante. Vous pouvez l'essayer.

alter table t engine=InnoDB

Notez que le contenu de cet article est basé sur le moteur InnoDB, et il peut y avoir quelques différences pour d'autres moteurs.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!