mysqlの削除操作が実際には偽の削除であるという問題を解決する方法

mysql の削除操作は実際には偽の削除です

InnoDB では、削除操作は実際にはデータを削除しません。Mysql は実際には、削除されたデータを削除済みとしてマークするだけです。したがって、delete を使用してデータを削除すると、テーブル内でテーブル ファイルが占有するディスク上の領域は減りませんので、当面これを 偽削除 と呼びます。

例で確認できます

前の記事の例に従い、まずストアド プロシージャを作成し、100,000 個のデータを挿入して、100,000 個のうちどれだけのデータが保存されているかを確認します。データがスペースを占有します。

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)

100,000 個のデータが mysql の 3.52M のスペースを占有していることがわかります。次に、削除コマンド delete from t を実行して調べます。

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

結果から、テーブル データがクリアされた後、テーブルが占有する領域は変化していないことがわかります。これにより、上記の結論が検証されます。削除操作では実際にデータは削除されません。テーブルのスペースは解放されません。

これらの削除されたレコード行は削除対象としてマークされるだけで、再利用できます。次回、条件を満たすレコードをこのマークされた位置に直接挿入できます。

例えば、id=300～600のレコードのうち、id=500のレコードを削除すると、このレコードは削除対象となり、次回挿入するid=400のレコードがあった場合には削除対象となります。どうぞ、id=500 で削除対象としてマークされた場所を再利用できます。この状況は ライン レコードの再利用 と呼ばれます。

もう 1 つの状況は データ ページの再利用で、これはデータ ページ全体がマークされて削除されているため、行レコードとともにデータ ページ全体を再利用できることを意味します。データ ページの再利用では、挿入するデータにほとんど制限がありません。

上記の挿入を例にとると、挿入するレコードが id=1000 の場合、id=500 の位置は再利用できませんが、再利用できるデータ ページ全体があれば、関係なく、 ID の値はこのページで再利用できます。

削除対象としてマークされたこれらのレコードは、実際には穴です。マンホールを占拠しているように感じますが、まったく問題になっていません。スペースの無駄であるだけでなく、クエリの効率にも影響します。 。

mysql は最下層のデータ ページ単位でデータの保存と読み取りを行うことを知っておく必要があるため、ディスクからデータを読み取るたびにデータ ページを読み取りますが、データにアクセスするたびにデータ ページを読み取ります。ディスク IO 操作、ディスク IO はメモリ アクセス速度に比べて非常に遅いです。

考えてみてください。テーブルに多数のデータ ホールがある場合、本来は 1 データ ページのみを保存する必要があるデータは、占有スペースの関係で他のデータ ページを追加して保存する必要があります。したがって、mysql が同じデータをクエリする場合、ディスク IO 操作を増やす必要があり、クエリ速度に影響します。

実際には、削除操作だけでなくデータ ホールが発生するだけでなく、挿入や更新でもデータ ホールが発生します。ここでは詳しく説明しませんが、知っておいてください。

したがって、データ テーブルが頻繁に多数の追加、削除、変更を受けると、データ ホールが発生することは避けられず、スペースが無駄になり、クエリの効率に影響を及ぼします。通常、運用環境では、これが直接的に表れます。もともと高速だったクエリがどんどん遅くなるということです。

この場合、通常は次のコマンドを使用してデータ ホールの問題を解決できます。

optimize table t

このコマンドの原理は、テーブルを再構築することです。つまり、一時テーブル B を作成し、次にテーブル A (データ ホールのあるテーブル) 内のすべてのデータをクエリし、すべてのデータをテーブル A に再挿入します。一時テーブル B を作成し、最後にテーブル A を一時テーブル B に置き換えます。これはテーブルを再構築するプロセスです。

もう一度試してみましょう。

効果を見てください

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)

テーブル ファイルのサイズが 0.02M になっていることがわかります。これは、テーブル スペースが解放されたことを示しています。この 0.02M は、テーブル構造を定義します。

さらに、次のコマンドを使用してテーブルを再構築することもでき、上記と同じ効果を得ることができます。次のコマンドを使用することをお勧めします。試してみてください。

alter table t engine=InnoDB

この記事の内容は InnoDB エンジンに基づいており、他のエンジンでは一部異なる場合があることに注意してください。

以上がmysqlの削除操作が実際には偽の削除であるという問題を解決する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。