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

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 をよろしくお願いいたします。

以下もご興味があるかもしれません:
  • MySQL が delete コマンドを使用してデータを削除しないようにする 2 つの方法
  • MySQL の Delete および Truncate ステートメントの比較
  • MySQLでデータを削除した後にディスク領域を解放する方法

<<:  zabbix を使用して ogg プロセスを監視する (Linux プラットフォーム)

>>:  共有サイドバーを実装するためのネイティブJS

推薦する

Linux (Ubuntu 18.04) に vim エディタをインストールする方法

デスクトップ システムをダウンロードするには、Ubuntu の公式 Web サイト (https:/...

CSS3 で King of Glory マッチング人員読み込みページを実装する方法

King of Glory をプレイしたことがある人なら、このページの効果をよくご存知でしょう。なぜ...

いくつかの CSS3 タグの短縮形 (推奨)

border-radius: CSS3 丸い角構文: border-radius: 25px;楕円...

Dockerはコンテナ外のコンテナ内でコマンドを実行します

コンテナ内でコマンドを実行したいが、コンテナに入りたくない場合があります。ではどうすればいいでしょう...

MySQL の遅いクエリ操作の例の分析 [有効化、テスト、確認など]

この記事では、MySQL のスロー クエリ操作について例を挙げて説明します。ご参考までに、詳細は以下...

Kubernetes の応用分野の概要

Kubernetes は、アプリケーションの移植性とハイブリッド クラウド/マルチクラウドの展開をサ...

CSS3 背景コントロールプロパティと色遷移を使用してグラデーション効果を実現します。

CSS3 背景画像関連互換性: IE9+背景クリップ 背景画像描画領域background-cli...

MySQL でテーブルデータをクリアする 2 つの方法とその違い

MySQL でデータを削除するには 2 つの方法があります。切り詰めは大まかな伐採の一種である削除は...

JavaScriptでポインターの位置を取得する方法を教えます

JavaScript でポインターの位置を取得する方法は、イベント オブジェクトの pageX と ...

Windows10システムにMySQL 5.7.17をインストールする

オペレーティング システム win10 MySQL は、公式 Web サイトからダウンロードした 6...

Flex モバイルレイアウトにおけるシングルラインレイアウトとダブルラインレイアウトの違いと使い方

レイアウトにul>liを使用した単一行レイアウトを以下に示します。 <ul class=...

MySQL の 4 つのトランザクション分離レベルの詳細な説明

この実験のテスト環境: Windows 10+cmd+MySQL5.6.36+InnoDB 1. ト...

Docker 実行時にユーザーとグループを管理する方法

Docker はプロセスを中核としてシステムリソースを分離する管理ツールです。分離は、オペレーティン...

Vue 初心者ガイド: 最初の Vue-cli スキャフォールディング プログラムの作成

1. Vue - 最初の vue-cli プログラムVueの開発はNodeJSに基づいています。実際...

M1 チップに MySQL 8.0 データベースをインストールする方法 (画像とテキスト)

1. ダウンロードまず、MySQLの国内ミラーをお勧めします。特に速いわけではありませんが、それで...