MySQLがデータの削除を推奨しない理由

序文

私は、業務量が増大する複数のシステムを担当しており、MySQL に保存されるデータ量も急増していました。当時、業務側が不道徳な行動を取り、私の対応力のなさにつけ込んで奇襲を仕掛け、多くのテーブルのサイズをあっという間に 10 億レベルにまで増大させているのだと思いました。私は不注意で回避しなかったため、テーブルを結合する SQL が非常に遅くなり、アプリケーションインターフェイスの応答時間も長くなり、ユーザーエクスペリエンスに影響を及ぼしました。

その後、私はそのビジネスパーティーを見つけ、彼らを批判し、社会の倫理に従うように伝えました。彼らはすぐに私に謝罪し、問題は解決しました。私はその場を去る際に、次回はこのようなことをしないように、そして間違いを反省するように伝えました。

いくら悪態をついても、問題は解決しなければなりません。原因を分析すると、一部のテーブルのデータ量が急増し、対応するSQLが大量の無効なデータをスキャンし、SQLの速度が低下していることがわかりました。確認したところ、これらの大きなテーブルはすべてフロー、レコード、ログタイプのデータであり、1〜3か月間保持するだけで済みました。このとき、スリム化を実現するためにテーブルデータをクリーンアップする必要がありました。一般的には、挿入+削除方式を使用してクリーンアップすることを考えます。

この記事では、InnoDB のストレージ領域の配分、削除によるパフォーマンスへの影響、最適化の提案の観点から、データの削除が推奨されない理由について説明します。

InnoDB ストレージアーキテクチャ

この図からわかるように、InnoDB ストレージ構造は主に論理ストレージ構造と物理ストレージ構造の 2 つの部分で構成されています。

論理的には、テーブルスペース -> セグメントまたは inode -> エクステント -> データページで構成されます。Innodb の論理的な管理単位はセグメントであり、スペース割り当ての最小単位はエクステントです。各セグメントには、テーブルスペース FREE_PAGE から 32 ページが割り当てられます。この 32 ページが足りない場合は、次の原則に従って拡張されます。現在のエクステントが 1 未満の場合は、1 つのエクステントに拡張されます。テーブルスペースが 32MB 未満の場合は、一度に 1 つのエクステントが拡張されます。テーブルスペースが 32MB より大きい場合は、一度に 4 つのエクステントが拡張されます。

物理的には、主にシステムユーザーデータファイルとログファイルで構成されています。データファイルには主に MySQL 辞書データとユーザーデータが格納されます。ログファイルにはデータページの変更が記録され、MySQL がクラッシュしたときにリカバリするために使用されます。

Innodb テーブルスペース

InnoDB ストレージには、システムテーブルスペース、ユーザーテーブルスペース、および UNDO テーブルスペースの 3 種類のテーブルスペースが含まれます。

**システムテーブルスペース: **information_schema の下のデータなど、主に MySQL 内部データディクショナリデータを格納します。

**ユーザーテーブルスペース: **innodb_file_per_table=1 が有効な場合、データテーブルはシステムテーブルスペースから分離され、table_name.ibd という名前のデータファイルに保存され、構造情報は table_name.frm ファイルに保存されます。

**UNDO 表領域:**UNDO 情報を格納します。たとえば、スナップショット一貫性読み取りとフラッシュバックはどちらも UNDO 情報を使用します。

MySQL 8.0 以降では、ユーザーがテーブルスペースを定義できるようになりました。具体的な構文は次のとおりです。

テーブルスペースの作成 テーブルスペース名
  ADD DATAFILE 'file_name' #データファイル名 USE LOGFILE GROUP logfile_group #カスタム ログ ファイル グループ。通常はグループごとに 2 つのログファイル。
  [EXTENT_SIZE [=] extend_size] #エクステントサイズ[INITIAL_SIZE [=] initial_size] #初期化サイズ[AUTOEXTEND_SIZE [=] autoextend_size] #自動拡張サイズ[MAX_SIZE [=] max_size] #単一ファイルの最大サイズ、最大値は 32G です。
  [NODEGROUP [=] nodegroup_id] #ノードグループ [WAIT]
  [コメント [=] コメントテキスト]
  ENGINE[=] エンジン名

これの利点は、ホットデータとコールドデータを分離し、それぞれ HDD と SSD を使用して保存できることです。これにより、効率的なデータアクセスが実現できるだけでなく、コストも節約できます。たとえば、500G のハードドライブを 2 つ追加し、ボリュームグループ vg を作成し、論理ボリューム lv を分割し、データディレクトリを作成して、対応する lv をマウントします。分割された 2 つのディレクトリが /hot_data と /cold_data であるとします。

これにより、ユーザーテーブルや注文テーブルなどの基幹業務テーブルを高性能 SSD ディスクに保存し、一部のログやフローテーブルを通常の HDD に保存することができます。主な操作手順は次のとおりです。

#ホット データ テーブルスペースを作成します。create tablespace tbs_data_hot add datafile '/hot_data/tbs_data_hot01.dbf' max_size 20G;
# コアビジネステーブルを作成し、ホットデータテーブルスペースに保存します。create table booking(id bigint not null primary key auto_increment, …… ) tablespace tbs_data_hot;
#コールド データ テーブルスペースを作成します。create tablespace tbs_data_cold add datafile '/hot_data/tbs_data_cold01.dbf' max_size 20G;
# ログ、ジャーナル、バックアップ テーブルを作成し、コールド データ テーブルスペースに保存します。create table payment_log(id bigint not null primary key auto_increment, …… ) tablespace tbs_data_cold;
# テーブルを別のテーブルスペースに移動できます。alter table payment_log tablespace tbs_data_hot;

インドストレージディストリビューション

スペースの変更を確認するために空のテーブルを作成する

mysql> テーブル user(id bigint not null 主キー auto_increment, を作成します。 
  -> name varchar(20) not null default '' コメント 'Name', 
  -> age tinyint not null デフォルト 0 コメント 'age', 
  -> 性別 char(1) NULLでない デフォルト 'M' コメント '性別',
  -> 電話 varchar(16) NULLでない デフォルト '' コメント '電話番号',
  -> create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '作成時刻',
  -> update_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '変更時刻'
  -> ) engine = InnoDB DEFAULT CHARSET=utf8mb4 COMMENT 'ユーザー情報テーブル';
クエリは正常、影響を受けた行は 0 行 (0.26 秒)

# ls -lh ユーザー1.ibd 
-rw-r----- 1 mysql mysql 96K 11月6日 12:48 user.ibd

パラメータ innodb_file_per_table を 1 に設定すると、テーブルの作成時にセグメントが自動的に作成され、データを格納するための 32 のデータページを含むエクステントが割り当てられます。この方法で作成された空のテーブルのデフォルトサイズは 96 KB です。エクステントを使い切った後は、64 の接続ページが要求されます。このようにして、一部の小さなテーブルまたは UNDO セグメントでは、最初に要求するスペースを少なくして、ディスク容量のオーバーヘッドを節約できます。

# python2 py_innodb_page_info.py -v /data2/mysql/test/user.ibd
ページ オフセット 00000000、ページ タイプ <ファイル スペース ヘッダー>
ページ オフセット 00000001、ページ タイプ <バッファー ビットマップの挿入>
ページ オフセット 00000002、ページ タイプ <ファイル セグメント inode>
ページ オフセット 00000003、ページ タイプ <B ツリー ノード>、ページ レベル <0000>
ページ オフセット 00000000、ページ タイプ <新しく割り当てられたページ>
ページ オフセット 00000000、ページ タイプ <新しく割り当てられたページ>
ページの合計数: 6: #割り当てられたページの合計数新しく割り当てられたページ: 2 #使用可能なデータ ページ挿入バッファー ビットマップ: 1 #挿入バッファー ページファイル スペース ヘッダー: 1 #ファイル スペース ヘッダーB ツリー ノード: 1 #データ ページファイル セグメント inode: 1 #ファイル側 inode。ibdata1.ibd 上にある場合は、複数の inode が存在します。

データ挿入後の空間変化

mysql> 区切り文字 $$
mysql> CREATE PROCEDURE insert_user_data(num INTEGER) 
  -> 開始
  -> v_i int unsigned DEFAULT 0 を宣言します。
  ->自動コミットを 0 に設定します。
  -> v_i < num の間
  -> user(`name`, age, gender, phone) に値 (CONCAT('lyn',v_i), mod(v_i,120), 'M', CONCAT('152',ROUND(RAND(1)*100000000))) を挿入します。
  -> v_i = v_i+1 に設定します。
  -> END WHILE;
  -> コミット;
  -> 終了 $$
クエリは正常、影響を受けた行は 0 行 (0.01 秒)
mysql> 区切り文字 ;

# 100,000 個のデータを挿入mysql> call insert_user_data(100000);
クエリは正常、影響を受けた行は 0 行 (6.69 秒)

# ls -lh ユーザー.ibd
-rw-r----- 1 mysql mysql 14M 11月6日 10:58 /data2/mysql/test/user.ibd

# python2 py_innodb_page_info.py -v /data2/mysql/test/user.ibd
ページ オフセット 00000000、ページ タイプ <ファイル スペース ヘッダー>
ページ オフセット 00000001、ページ タイプ <バッファー ビットマップの挿入>
ページ オフセット 00000002、ページ タイプ <ファイル セグメント inode>
ページ オフセット 00000003、ページ タイプ <B ツリー ノード>、ページ レベル <0001> # 非リーフ ノードが追加され、ツリーの高さが 1 から 2 に変更されます。
.............................................................
ページ オフセット 00000000、ページ タイプ <新しく割り当てられたページ>
総ページ数: 896:
新しく割り当てられたページ: 493
バッファビットマップを挿入: 1
ファイル スペース ヘッダー: 1
Bツリーノード: 400
ファイルセグメント inode: 1

データを削除した後のスペースの変更

mysql> ユーザーから min(id),max(id),count(*) を選択します。
+---------+---------+----------+
| 最小(ID) | 最大(ID) | カウント(*) |
+---------+---------+----------+
| 1 | 100000 | 100000 |
+---------+---------+----------+
セット内の1行（0.05秒）
#50,000 個のデータ項目を削除します。理論的には、スペースは 14 MB から約 7 MB に増加するはずです。
mysql> ユーザー制限 50000 から削除します。
クエリは正常、50000 行が影響を受けました (0.25 秒)

 
#データファイルのサイズは依然として 14MB のままで、縮小されていません。
# ls -lh /data2/mysql/test/user1.ibd 
-rw-r----- 1 mysql mysql 14M 11月6日 13:22 /data2/mysql/test/user.ibd

#データページはリサイクルされません。
# python2 py_innodb_page_info.py -v /data2/mysql/test/user.ibd
ページ オフセット 00000000、ページ タイプ <ファイル スペース ヘッダー>
ページ オフセット 00000001、ページ タイプ <バッファー ビットマップの挿入>
ページ オフセット 00000002、ページ タイプ <ファイル セグメント inode>
ページ オフセット 00000003、ページ タイプ <B ツリー ノード>、ページ レベル <0001>
.............................................................
ページ オフセット 00000000、ページ タイプ <新しく割り当てられたページ>
総ページ数: 896:
新しく割り当てられたページ: 493
バッファビットマップを挿入: 1
ファイル スペース ヘッダー: 1
Bツリーノード: 400
ファイルセグメント inode: 1
#MySQL 内では削除対象としてマークされます。

mysql> information_schema を使用します。

データベースが変更されました
mysql> SELECT A.SPACE AS TBL_SPACEID, A.TABLE_ID, A.NAME AS TABLE_NAME, FILE_FORMAT, ROW_FORMAT, SPACE_TYPE, B.INDEX_ID, B.NAME AS INDEX_NAME, PAGE_NO, B.TYPE AS INDEX_TYPE FROM INNODB_SYS_TABLES A LEFT JOIN INNODB_SYS_INDEXES B ON A.TABLE_ID = B.TABLE_ID WHERE A.NAME = 'test/user1';
+-------------+------------+--------------+--------------+-------------+------------+------------+------------+------------+------------+
| TBL_SPACEID | TABLE_ID | TABLE_NAME | FILE_FORMAT | ROW_FORMAT | SPACE_TYPE | INDEX_ID | INDEX_NAME | PAGE_NO | INDEX_TYPE |
+-------------+------------+--------------+--------------+-------------+------------+------------+------------+------------+------------+
| 1283 | 1207 | テスト/ユーザー | Barracuda | 動的 | シングル | 2236 | プライマリ | 3 | 3 |
+-------------+------------+-------------+-------------+-------------+--------------+--------------+------------+------------+-------------+
セット内の1行（0.01秒）

PAGE_NO = 3 は、B ツリーのルート ページがページ 3 であり、INDEX_TYPE = 3 がクラスター化インデックスであることを示します。 INDEX_TYPEの値は次のとおりです。
0 = 一意でないセカンダリインデックス。 
1 = 自動的に生成されたクラスター化インデックス (GEN_CLUST_INDEX) 
2 = 一意の非クラスター化インデックス。 
3 = クラスター化インデックス; 
32 = 全文インデックス;
#スペースを縮小して後で観察する

MySQL は実際には内部的にスペースを削除するのではなく、削除対象としてマークします。つまり、delflag:N を delflag:Y に変更します。コミット後、削除リストに消去されます。次回、より大きなレコードが挿入された場合、削除後のスペースは再利用されません。挿入されたレコードが削除されたレコードより小さいか等しい場合、スペースは再利用されます。この内容は、Zhishutang の innblock ツールを使用して分析できます。

InnoDB の断片化

フラグメントの生成

ファイルシステムに保存されたデータは、割り当てられた物理スペースを常に 100% 使用できるとは限らないことはわかっています。データを削除すると、ページに「穴」が残ります。また、ランダム書き込み (クラスター化インデックスの非線形増加) によってページ分割が発生します。ページ分割により、ページ使用スペースは 50% 未満になります。さらに、テーブルの追加、削除、変更によって、対応するセカンダリインデックス値にランダムな追加、削除、変更が発生し、インデックス構造のデータページに「穴」が発生します。これらの穴は再利用できますが、最終的には物理スペースの一部が未使用になり、断片化が発生します。

同時に、フィルファクタが 100% に設定されている場合でも、Innodb は更新による行オーバーフローを防ぐために、クラスター化インデックスページの 1/16 のスペースを将来のインデックスの拡張に備えて事前に空けておきます。

mysql> table_schema を選択し、
  -> テーブル名,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
  -> information_schema.TABLES から、TABLE_SCHEMA= 'test'
  -> かつ TABLE_NAME = 'user';
+--------------+------------+---------+-----------+-----------+-----------+-----------+-----------+------------+
| テーブル スキーマ | テーブル名 | ENGINE | 合計 MB | TABLE_ROWS | データ MB | インデックス MB | 空き MB | 空き比率 |
+--------------+------------+---------+-----------+-----------+-----------+-----------+-----------+------------+
| テスト | ユーザー | InnoDB | 4 | 50000 | 4 | 0 | 6 | 149.42 |
+--------------+------------+---------+-----------+-----------+-----------+-----------+-----------+------------+
セット内の 1 行 (0.00 秒)

Data_free は、使用されていない割り当て済みバイト数であり、領域が完全に断片化されていることを示すものではありません。

廃棄物のリサイクル

InnoDB テーブルの場合、次のコマンドを使用してフラグメントをリサイクルし、スペースを解放できます。これはランダム読み取り IO 操作であり、時間がかかり、テーブルに対する通常の DML 操作をブロックします。また、より多くのディスク領域が必要になります。RDS の場合、ディスク領域が瞬時にいっぱいになり、インスタンスが瞬時にロックされ、アプリケーションが DML 操作を実行できなくなる可能性があります。したがって、オンライン環境で実行することは禁止されています。

#InnoDB フラグメントリカバリを実行します。mysql> alter table user engine=InnoDB;
クエリは正常、影響を受けた行は 0 行 (9.00 秒)
レコード: 0 重複: 0 警告: 0

##実行後、データファイルのサイズは14MBから10Mに減少します。
# ls -lh /data2/mysql/test/user1.ibd 
-rw-r----- 1 mysql mysql 10M 11月6日 16:18 /data2/mysql/test/user.ibd

mysql> select 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 from information_schema.TABLES where TABLE_SCHEMA= 'test' and TABLE_NAME= 'user';
+--------------+------------+---------+-----------+-----------+-----------+-----------+-----------+------------+
| テーブル スキーマ | テーブル名 | ENGINE | 合計 MB | TABLE_ROWS | データ MB | インデックス MB | 空き MB | 空き比率 |
+--------------+------------+---------+-----------+-----------+-----------+-----------+-----------+------------+
| テスト | ユーザー | InnoDB | 5 | 50000 | 5 | 0 | 2 | 44.29 |
+--------------+------------+---------+-----------+-----------+-----------+-----------+-----------+------------+
セット内の 1 行 (0.00 秒)

SQL での削除の影響

削除前のSQL実行ステータス

# 100 万個のデータを挿入mysql> call insert_user_data(1000000);
クエリは正常、影響を受けた行は 0 行 (35.99 秒)

#関連するインデックスを追加しますmysql> alter table user add index idx_name(name), add index idx_phone(phone);
クエリは正常、影響を受けた行は 0 行 (6.00 秒)
レコード: 0 重複: 0 警告: 0

#テーブルのインデックス統計mysql> show index from user;
+-------+------------+------------+--------------+--------------+---------------+------------+-------+---------+----------+----------+---------------+
| テーブル | 非一意 | キー名 | インデックス内のシーケンス | 列名 | 照合 | カーディナリティ | サブパート | パック | Null | インデックス タイプ | コメント | インデックス コメント |
+-------+------------+------------+--------------+--------------+---------------+------------+-------+---------+----------+----------+---------------+
| ユーザー | 0 | プライマリ | 1 | ID | A | 996757 | NULL | NULL | | BTREE | | |
| ユーザー | 1 | idx_name | 1 | 名前 | A | 996757 | NULL | NULL | | BTREE | | |
| ユーザー | 1 | idx_phone | 1 | 電話 | A | 2 | NULL | NULL | | BTREE | | |
+-------+------------+------------+--------------+--------------+---------------+------------+-------+---------+----------+----------+---------------+
セット内の 3 行 (0.00 秒)

#ステータス変数をリセット countmysql> flush status;
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

#SQL ステートメントを実行します。mysql> select id, age, phone from user where name like 'lyn12%';
+--------+-----+--------------+
| ID | 年齢 | 電話番号 |
+--------+-----+--------------+
| 124 | 3 | 15240540354 |
| 1231 | 30 | 15240540354 |
| 12301 | 60 | 15240540354 |
.............................
| 129998 | 37 | 15240540354 |
| 129999 | 38 | 15240540354 |
| 130000 | 39 | 15240540354 |
+--------+-----+--------------+
セット内の行数は 11111 です (0.03 秒)

mysql> explain select id, age ,phone from user where name like 'lyn12%';
+----+-------------+-------+-------+---------------+-----------+-------+-------+------------+------------------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+-------+-------+---------------+-----------+-------+-------+------------+------------------------+
| 1 | SIMPLE | ユーザー | 範囲 | idx_name | idx_name | 82 | NULL | 22226 | インデックス条件を使用 |
+----+-------------+-------+-------+---------------+-----------+-------+-------+------------+------------------------+
セット内の 1 行 (0.00 秒)

#関連するステータス変数を表示します。mysql> 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');
+-------------------+----------------+
| 変数名 | 変数値 |
+-------------------+----------------+
| HANDLER_READ_NEXT | 11111 | #読み取り要求された行数 | INNODB_DATA_READS | 7868409 | #物理データ読み取りの合計数 | INNODB_PAGES_READ | 7855239 | #論理読み取りの合計数 | LAST_QUERY_COST | 10.499000 | #主に IO_COST と CPU_COST を含む SQL ステートメントのコスト。
+-------------------+----------------+
セット内の 4 行 (0.00 秒)

削除後のSQL実行

#500,000 個のデータを削除しますmysql> delete from user limit 500000;
クエリは正常、500000 行が影響を受けました (3.70 秒)

#テーブル統計を分析する mysql> analyze table user;
+-----------+----------+----------+----------+
| テーブル | Op | メッセージ タイプ | メッセージ テキスト |
+-----------+----------+----------+----------+
| test.user | 分析 | ステータス | OK |
+-----------+----------+----------+----------+
セット内の1行（0.01秒）

#ステータス変数をリセット countmysql> flush status;
クエリは正常、影響を受けた行は 0 行 (0.01 秒)

mysql> 名前が 'lyn12%' のようなユーザーから id、age、phone を選択します。
空セット（0.05秒）

mysql> explain select id, age ,phone from user where name like 'lyn12%';
+----+-------------+-------+-------+---------------+---------+-------+-------+-------+------------------------+
| id | select_type | テーブル | タイプ | possible_keys | key | key_len | ref | 行 | 追加 |
+----+-------------+-------+-------+---------------+---------+-------+-------+-------+------------------------+
| 1 | SIMPLE | ユーザー | 範囲 | idx_name | idx_name | 82 | NULL | 22226 | インデックス条件を使用 |
+----+-------------+-------+-------+---------------+-----------+-------+-------+------------+------------------------+
セット内の 1 行 (0.00 秒)

mysql> information_schema.session_status から * を選択します。ここで、variable_name は ('Last_query_cost'、'Handler_read_next'、'Innodb_pages_read'、'Innodb_data_reads'、'Innodb_pages_read') です。
+-------------------+----------------+
| 変数名 | 変数値 |
+-------------------+----------------+
| ハンドラー_READ_NEXT | 0 |
| INNODB_DATA_READS | 7868409 |
| INNODB_PAGES_READ | 7855239 |
| 最終クエリコスト | 10.499000 |
+-------------------+----------------+
セット内の 4 行 (0.00 秒)

結果の統計分析

操作する	料金	物理的な読み取り時間	論理読み取り時間	走査線	行数を返します	実行時間
初期化プラグ100W	10.499000	7868409	7855239	22226	11111	30ミリ秒
100W ランダムに 50W を削除	10.499000	7868409	7855239	22226	0	50ミリ秒

これは、通常の大きなテーブルの場合、データを削除してテーブルをスリム化することは非現実的であることも示しています。そのため、データを削除するために delete を常に使用せず、エレガントなマーク削除を使用してください。

最適化の提案を削除

ビジネスアカウントの権限を制御する

大規模なシステムの場合、業務特性に応じてサブシステムを分割する必要があります。各サブシステムはサービスと見なすことができます。たとえば、Meituan APP には多くのサービスがあります。コアサービスには、ユーザーサービス、検索サービス、製品サービス、位置情報サービス、価格サービスなどがあります。各サービスはデータベースに対応しています。データベースごとに別のアカウントが作成されます。DML 権限のみ付与され、削除権限は付与されません。データベース間のアクセスは禁止されています。

# ユーザー データベースを作成し、承認します。create database mt_user charset utf8mb4;
't$W*g@gaHTGi123456' で識別される 'w_user'@'%' に、mt_user.* に対する USAGE、SELECT、INSERT、UPDATE 権限を付与します。
権限をフラッシュします。

削除マークを付けるために削除する

MySQL データベースモデリング仕様には、基本的にすべてのテーブルに必要な 4 つの共通フィールドがあります。同時に、create_time 列にインデックスを作成すると、次の 2 つの利点があります。

一部のクエリビジネスシナリオには、7 日間や 1 か月などのデフォルトの期間があり、create_time によってフィルター処理されるため、インデックススキャンが高速になります。
一部のコアビジネステーブルは、データウェアハウスから T + 1 方式で抽出する必要があります。たとえば、前日のデータを毎晩 00:30 に抽出する必要があり、そのすべてが create_time でフィルタリングされます。

`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主キーID',
`is_deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT '論理的に削除されたかどうか: 0: 削除されていない、1: 削除済み',
`create_time` タイムスタンプ NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '作成時刻',
`update_time` タイムスタンプ NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '変更時刻'

#削除マーカーを使用すると、ビジネスインターフェースの削除操作を更新に変換できます
ユーザーを更新し、user_id = 1213 の場合、is_deleted = 1 を設定します。

# クエリを実行するときは、is_deleted フィルター select id, age, phone from user where is_deleted = 0 and name like 'lyn12%'; を使用する必要があります。

データアーカイブ方法

一般的なデータアーカイブ方法

#1. 通常は元のテーブル名の後に _bak を追加してアーカイブ テーブルを作成します。
テーブル `ota_order_bak` を作成します (
 `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主キー',
 `order_id` varchar(255) デフォルト NULL コメント '注文ID',
 `ota_id` varchar(255) デフォルト NULL コメント 'ota',
 `check_in_date` varchar(255) DEFAULT NULL COMMENT 'チェックイン日',
 `check_out_date` varchar(255) DEFAULT NULL COMMENT 'チェックアウト日',
 `hotel_id` varchar(255) デフォルト NULL コメント 'ホテルID',
 `guest_name` varchar(255) デフォルト NULL コメント '顧客',
 `purcharse_time` タイムスタンプ NULL デフォルト NULL コメント '購入時間',
 `create_time` datetime デフォルト NULL、
 `update_time` 日時 デフォルト NULL、
 `create_user` varchar(255) デフォルト NULL,
 `update_user` varchar(255) デフォルト NULL,
 `status` int(4) デフォルト '1' コメント 'ステータス: 1 正常、0 削除',
 `hotel_name` varchar(255) デフォルト NULL,
 `price` 小数点(10,0) デフォルト NULL,
 `コメント` 長文、
 主キー (`id`)、
 キー `IDX_order_id` (`order_id`) BTREE 使用、
 キー `hotel_name` (`hotel_name`) BTREE 使用、
 キー `ota_id` (`ota_id`) BTREE 使用、
 キー `IDX_purcharse_time` (`purcharse_time`) BTREE 使用、
 キー `IDX_create_time` (`create_time`) BTREE の使用
) エンジン=InnoDB デフォルト文字セット=utf8
範囲によるパーティション分割 (to_days(create_time)) ( 
パーティション p201808 の値は (to_days('2018-09-01')) より小さいです。 
パーティション p201809 の値は (to_days('2018-10-01')) より小さいです。 
パーティション p201810 の値は (to_days('2018-11-01')) より小さいです。 
パーティション p201811 の値は (to_days('2018-12-01')) より小さい 
パーティション p201812 の値は (to_days('2019-01-01')) より小さいです。 
パーティション p201901 の値は (to_days('2019-02-01')) より小さいです。 
パーティション p201902 の値は (to_days('2019-03-01')) より小さいです。 
パーティション p201903 の値は (to_days('2019-04-01')) より小さいです。 
パーティション p201904 の値は (to_days('2019-05-01')) より小さいです。 
パーティション p201905 の値は (to_days('2019-06-01')) より小さいです。 
パーティション p201906 の値は (to_days('2019-07-01')) より小さいです。 
パーティション p201907 の値は (to_days('2019-08-01')) より小さいです。 
パーティション p201908 の値は (to_days('2019-09-01')) より小さいです。 
パーティション p201909 の値は (to_days('2019-10-01')) より小さいです。 
パーティション p201910 の値は (to_days('2019-11-01')) より小さいです。 
パーティション p201911 の値は (to_days('2019-12-01')) より小さいです。 
パーティション p201912 の値は (to_days('2020-01-01')) より小さいです);

#2. 元のテーブルに無効なデータを挿入する（開発者にデータ保持範囲を確認する必要があります）
'2018-08-01 00:00:00' から '2018-08-31 23:59:59' までの範囲で、select * from ota_order としてテーブル tbl_p201808 を作成します。

#3. パーティションをアーカイブ テーブル パーティションと交換します。 alter table ota_order_bak exchange partition p201808 with table tbl_p201808;

#4. 元のテーブル内の標準化されたデータを削除します。delete from ota_order where create_time between '2018-08-01 00:00:00' and '2018-08-31 23:59:59' limit 3000;

最適化されたアーカイブ方法

#1. 中間テーブルを作成する CREATE TABLE `ota_order_2020` (........) ENGINE=InnoDB DEFAULT CHARSET=utf8
範囲によるパーティション分割 (to_days(create_time)) ( 
パーティション p201808 の値は (to_days('2018-09-01')) より小さいです。 
パーティション p201809 の値は (to_days('2018-10-01')) より小さいです。 
パーティション p201810 の値は (to_days('2018-11-01')) より小さいです。 
パーティション p201811 の値は (to_days('2018-12-01')) より小さい 
パーティション p201812 の値は (to_days('2019-01-01')) より小さいです。 
パーティション p201901 の値は (to_days('2019-02-01')) より小さいです。 
パーティション p201902 の値は (to_days('2019-03-01')) より小さいです。 
パーティション p201903 の値は (to_days('2019-04-01')) より小さいです。 
パーティション p201904 の値は (to_days('2019-05-01')) より小さいです。 
パーティション p201905 の値は (to_days('2019-06-01')) より小さいです。 
パーティション p201906 の値は (to_days('2019-07-01')) より小さいです。 
パーティション p201907 の値は (to_days('2019-08-01')) より小さいです。 
パーティション p201908 の値は (to_days('2019-09-01')) より小さいです。 
パーティション p201909 の値は (to_days('2019-10-01')) より小さいです。 
パーティション p201910 の値は (to_days('2019-11-01')) より小さいです。 
パーティション p201911 の値は (to_days('2019-12-01')) より小さいです。 
パーティション p201912 の値は (to_days('2020-01-01')) より小さいです);

#2. 有効なデータを元のテーブルに挿入します。データ量が 100 万程度であれば、オフピーク時に直接挿入できます。比較的大きい場合は、頻度とサイズを制御できる dataX を使用することをお勧めします。以前、Go で dataX をカプセル化して、json ファイルを自動的に生成し、カスタム サイズで実行しました。
ota_order_2020 に挿入し、ota_order から * を選択します。create_time が '2020-08-01 00:00:00' から '2020-08-31 23:59:59' の間です。

#3. テーブル名の変更 alter table ota_order rename to ota_order_bak; 
テーブル ota_order_2020 を変更し、名前を ota_order に変更します。
#4. 差異データを挿入します。insert into ota_order select * from ota_order_bak a where not exists (select 1 from ota_order b where a.id = b.id);
#5. ota_order_bak をパーティション テーブルに変換します。テーブルが大きい場合は、直接変換することはお勧めしません。最初にパーティション テーブルを作成し、dataX を介してそのテーブルにインポートすることができます。

#6. 後続のアーカイブ方法 #中間の一般テーブルを作成する create table ota_order_mid like ota_order;
# 元のテーブルの無効なデータ パーティションを通常のテーブルに交換します。 alter table ota_order exchange partition p201808 with table ota_order_mid; 
##共通テーブルのデータをアーカイブ テーブルの対応するパーティションに交換します。alter table ota_order_bak exchange partition p201808 with table ota_order_mid;

この方法では、元のテーブルとアーカイブテーブルの両方が月ごとにパーティション分割されます。中間の通常テーブルを作成し、業務の閑散期に 2 回のパーティション交換を実行するだけで済みます。これにより、無効なデータを削除できるだけでなく、スペースをリサイクルすることもできます。スペースの断片化は発生せず、テーブル上のインデックスや SQL 実行プランに影響しません。

要約する

InnoDB ストレージ領域の配分と削除によるパフォーマンスへの影響から、物理的な削除ではディスク領域を解放することも、大量の断片化を生成することもできず、頻繁なインデックス分割を引き起こし、SQL 実行プランの安定性に影響を与えることがわかります。

同時に、フラグメントが回復されると、大量の CPU とディスク領域が消費され、テーブル上の通常の DML 操作に影響します。

ビジネスコードレベルでは、物理的な削除を避けるために論理的なマーキングを行う必要があります。データアーカイブの要件を満たすには、すべての DDL 操作で断片化が生成されない MySQL パーティションテーブル機能を使用できます。

もう 1 つの優れた解決策は、Clickhouse を使用することです。Clickhouse を使用すると、ライフサイクルを持つデータテーブルを保存でき、TTL 機能を使用して無効なデータを自動的にクリーンアップできます。

これで、MySQL がデータ削除を推奨しない理由に関するこの記事は終了です。MySQL がデータ削除を推奨しない理由の詳細については、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません: