MySQL テーブルスペースのリカバリに対する正しいアプローチについての簡単な説明

MySQL テーブルスペースのリカバリに対する正しいアプローチについての簡単な説明

このような状況に遭遇したことがあるかどうかはわかりません。オンラインビジネスでは、MySQL テーブルに対して追加、削除、変更、クエリなどの操作を実行します。時間が経つにつれて、テーブル内のデータはますます増え、テーブル データ ファイルはますます大きくなり、データベースが占めるスペースは自然に徐々に増加します。

ディスク上のテーブル データ ファイルが占有するスペースを削減するために、削除コマンドを使用して、最大のビジネス テーブルの古いデータの半分を削除しました。削除後、ディスク上のテーブル データ ファイルは縮小されませんでした。テーブル全体のデータを削除しても、ファイルは小さくなりませんでした。なぜでしょうか。

この記事では、上記の問題を詳細に分析し、テーブルスペースを再利用するための正しい方法を紹介します。

予備的注釈

現在、ほとんどの MySQL データベースは InnoDB エンジンを使用しているため、特に指定がない限り、この記事の例は InnoDB エンジンに基づいています。

MySQLの設定には、 innodb_file_per_tableという設定項目があります。これを1に設定すると、
各テーブルのデータは、拡張子が.ibdのファイルに個別に保存されます。

innodb_file_per_tableが有効になっていない場合、
テーブルのデータはシステムの共有テーブルスペースに保存されるため、テーブルが削除されても共有テーブルスペースはこのスペースを解放しません。

そのため、通常はinnodb_file_per_tableオプションは 1 に設定されます。同時に、テーブル データ ファイルのサイズ変更を直感的に確認できるように、この記事の例もこのオプションをオンにして説明しています。

問題の再現

新しいテーブルtaを作成します。テーブルの構造は次のようになります。

mysql> テーブル ta\G の作成を表示します
************************** 1. 行 ****************************
       テーブル:
テーブルの作成: CREATE TABLE `ta` (
  `id` int(11) NULLではない、
  `ia` int(11) NULLではない、
  主キー (`id`)
) エンジン=InnoDB デフォルト文字セット=utf8
セット内の 1 行 (0.00 秒)

次のストアドプロシージャを使用して、データをバッチで挿入しますta

区切り文字 //
プロシージャ multinsert(in beg int, in cnt int) を作成します。
始める
	icnt int をデフォルトで 0 と宣言します。
	tmp int のデフォルトを 0 と宣言します。
	icnt < cnt の場合
		icnt = icnt + 1 を設定します。
		tmp = beg + icnt を設定します。
		ta(id,ia) に値(tmp,tmp) を挿入します。
	終了しながら;
終わり//

区切り文字 ;

MySQL コンソールでcall multinsert(0,100000)コマンドを実行して、 taテーブルに 100,000 件のレコードを挿入します。

mysql> multinsert(0,100000) を呼び出します。
mysql> taからcount(*)を選択します。
+----------+
| カウント(*) |
+----------+
| 100000 |
+----------+
セット内の1行(0.02秒)

ディスク上のtaテーブルのta.ibdデータファイルのサイズを確認します

[root@ecs-centos-7 test]# cd /var/lib/mysql/test/
[root@ecs-centos-7 テスト]# ls -l ta.ibd 
-rw-r----- 1 mysql mysql 11534336 1月3日 23:14 ta.ibd

上記の結果から、 taテーブルに100,000件のレコードを挿入した後、 ta.ibdのサイズは次のようになることがわかります。 11534336バイト(約11M)

ここで、 deleteコマンドを使用してテーブルデータの半分(50,000行)を削除します。

mysql> id が 1 から 50000 の間の ta から削除します。
クエリは正常、10000 行が影響を受けました (0.03 秒)
mysql> taからcount(*)を選択します。
+----------+
| カウント(*) |
+----------+
| 50000 |
+----------+
セット内の1行(0.02秒)

削除が完了したら、ディスク上のta.ibdのサイズを再度確認します。

[root@ecs-centos-7 test]# cd /var/lib/mysql/test/
[root@ecs-centos-7 テスト]# ls -l ta.ibd 
-rw-r----- 1 mysql mysql 11534336 1月3日 23:14 ta.ibd

上記の結果から、 taテーブルの半分、つまり50,000行のデータを削除した後、 ta.ibdのサイズは次のようになることがわかります。 11534336バイト(約11M)

つまり、 taテーブルからデータが削除される前と後で、ディスク上のデータ ファイルは縮小されません。

データ ファイルが縮小されない理由を理解するには、データを削除する原理を理解する必要があります。

データ削除の原則

ご存知のとおり、InnoDB のデータは B+ ツリーを使用して編成されます。B+ ツリーの詳細については、「B+ ツリーの理解」を参照してください。

図(1)

上記は InnoDB インデックスの概略図です。点線で囲まれたノードは Page1 データ ページに属します。リーフ ノードにはインデックスに対応するデータが格納されます。これらは、インデックスの小さい順から大きい順に配列されます。

上図の赤い部分である、Page1 のインデックス キー値が 13 のデータを削除するとします。

InnoDB エンジンは、インデックス キー値が 13 のノードを削除済みとしてマークします。ノードの実際の物理スペースは再利用されず、後で再利用できる削除済みノードとしてマークされるだけです。したがって、テーブル レコードを削除しても、ディスク上のデータ ファイルは縮小されません。

上記では Page1 の 1 つのノードのデータのみが削除されているので、Page1 のすべてのノード データが削除されると、Page1 のスペースが再利用されるはずだと言うかもしれませんね。

答えはリサイクルなし

Page1 のデータがすべて削除されると、データ ページ全体が削除済みとしてマークされ、データ ページ全体が再利用できるようになります。したがって、この場合、ディスク上のデータ ファイルは縮小されません。

データの再利用

データの再利用には、データノードの挿入、削除、転送、データページのマージなどの操作が含まれます。具体的な操作プロセスの詳細については、「B+ ツリーの理解」を参照してください。ここでは繰り返しません。

  • データノードの再利用

上図(1)では、インデックスキー値が13のノードが削除されると、このノードは再利用可能としてマークされます。

後でインデックス キー値が 7 ~ 18 のレコードが挿入されると、元のインデックス キー値が 13 のデータ ノードが再利用されます。

ただし、後で挿入されたレコードのインデックス キー値が 7 ~ 18 の範囲外の場合、元のインデックス キー値が 13 のデータ ノードは再利用されない可能性があります。

つまり、データ ノードを再利用するには、インデックス キー値が特定の範囲条件を満たしている必要があります。

  • データページの再利用

図(1)では、Page1のデータノードをすべて削除した後、Page1全体が再利用可能になります。挿入されたレコードが新しいページを使用する必要がある場合、Page1を再利用できます。

隣接するデータ ページの使用率が低い場合、それらのページがいずれかのデータ ページにマージされることがあります。このとき、もう一方のデータ ページは空きになり、空きになったデータ ページは再利用できるようになります。

どの操作がデータホールの原因になりますか?

deleteコマンドでレコードを削除すると、InnoDB は対応するデータ ノードを削除済みかつ再利用可能としてマークするだけです。使用を待機しているこれらの空のデータ ノードは、1 つずつデータ ホールと見なすことができます。

  • データの削除

データを削除すると、データホールが作成されます。これは以前に説明されているため、ここでは繰り返しません。

  • データの挿入

データがインデックス サイズの順に挿入されると、データ ページはコンパクトになり、データ ホールは発生しません。

インデックスの途中からデータを挿入すると、ページ分割が発生し、分割後のページにデータ ホールが発生する可能性があります。次の図は、挿入によって発生したページ分割の例を示しています。

図に示すように、分割前はリーフ ページがいっぱいになっており、データは非常にコンパクトに配置されています。

ここで、インデックス キー値が 15 のデータを挿入します。挿入後、上図の Page1 はPage1,Page2 2 つのページに分割されます。

分割後、Page1 に 2 つの穴が現れました。これらの 2 つのデータ ノードは再利用可能ですが、Page2 はいっぱいです。

  • データの更新

データの更新は、最初に削除してから挿入すると見なされ、データホールが発生する可能性もあります。

たとえば、 idはテーブル ta の主キーです。update ta set update ta set id = 10 where id = 1ステートメントはid = 1 id = 10に変更します。これは、最初にid = 1レコードを削除してからid = 10のレコードを挿入するのと同じです。これにより、データ ギャップが発生します。

ただし、ステートメントがupdate ta set ia = ia + 1 where id = 1のように主キーの値を変更しない場合は、ホールは発生しません。

そのため、データを更新するとデータにギャップが生じる可能性があります。

まとめると、テーブルの追加、削除、変更操作によってデータ ホールが発生する可能性があり、オンライン サービスではテーブルに対して大量の追加、削除、変更操作が実行されるため、データ ホールが発生する可能性が比較的高くなります。

表領域を縮小する方法

不規則な追加、削除、変更操作が多数行われると、テーブルには多くのデータホールが発生するため

データ ホールのある元のテーブルと同じ構造の新しいテーブルを作成し、古いテーブルのデータをインデックスの昇順で新しいテーブルに挿入し、古いテーブルのすべてのデータを新しいテーブルに挿入した後、古いテーブルを削除し、新しいテーブルの名前を古いテーブルの名前に変更するとどうなるでしょうか。

新しいテーブルのリーフ ノード データは順番に追加されるため、ページは非常にコンパクトで、ページの使用率は非常に高くなります。必要なページは、古いテーブルよりもはるかに少なくなります。このように、古いテーブルのインデックスの穴は新しいテーブルには存在しません。新しいテーブルのデータ ファイルが占有するディスク領域は自然に削減され、テーブル領域を縮小するという目的が達成されます。

テーブル領域を縮小する方法はいくつかあります。方法はそれぞれ異なりますが、基本的にはテーブルを再構築することで目的を達成します。

  • truntace テーブル テーブル名

この操作は、 drop + createと同等です。まずテーブルを削除し、同じ名前で新しいテーブルを作成します。もちろん、 truncate tableコマンドを実行する前に、古いテーブルのデータのコピーを保存する必要があります。コマンドを実行した後、データを新しいテーブルにインポートします。

  • テーブル名を変更するエンジン = InnoDB

この操作は、古いテーブルの主キー インデックスのデータ ページをトラバースし、データ ページ内のレコードの B+ ツリー構造を生成し、それをディスク上の一時ファイルに保存します。データ ページのトラバースが完了すると、一時ファイルを使用して古いテーブルのデータ ファイルが置き換えられます。

MySQL 5.6 以降では、この操作はオンライン DDL です。この方法ではテーブルデータファイルのスキャンが必要となり、大きなテーブルの場合は非常に時間がかかることに注意してください。オンラインサービスの場合は、業務のピーク時間を避けて慎重に操作する必要があります。

知らせ:
テーブルを再構築する場合、InnoDB はテーブル全体を占有しません。各ページのデータ ノードの約 10% を後続の更新用に予約します。つまり、再構築後、テーブルは最もコンパクトではありません。

このようなプロセスがあると仮定します: テーブルtを一度再構築し、
いくつかのデータを挿入しましたが、挿入されたデータは予約されたスペースの一部を使用します。
この場合、テーブル t を再度再構築すると、再構築されたテーブルが占めるスペースが再構築前よりも大きくなる可能性があります。

まとめ

この記事は、実際の問題から始まり、問題を再現し、問題を分析し、問題を解決します。各ステップは詳細に分析されています。スペースが限られているため、詳細が詳細に説明されていない部分があり、読者は自分で理解する必要があります。

これで、MySQL テーブルスペースのリカバリに対する正しいアプローチに関するこの記事は終了です。MySQL テーブルスペースのリカバリに関するより関連性の高いコンテンツについては、123WORDPRESS.COM で以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません:
  • MySQL オンラインリカバリ UNDO テーブルスペース 実戦記録

<<:  Vue の新しい組み込みコンポーネントの使用方法の詳細な説明

>>:  Nofollowタグの書き方と使い方

推薦する

React Hooksコンポーネント間で値を渡す方法の詳細な説明(tsを使用)

目次父から息子へ息子から父へクロスレベルコンポーネント(親から子孫)父から息子へpropsを通じて値...

MySQLマスタースレーブ遅延現象と原理の詳細な分析

1. 現象早朝、オンライン テーブルにインデックスが追加されました。テーブル内のデータ量が大きすぎた...

Vue3 でタイマーコンポーネントをカプセル化する方法

背景一部のショッピング モールの Web ページで商品の詳細を開くと、購入数量を選択するためのカウン...

docker を使用して Django テクノロジー スタック プロジェクトをデプロイする方法

Docker の人気と成熟に伴い、Docker は徐々にプロジェクトをデプロイするための第一の選択肢...

iframeを使用してページを呼び出すとページがキャッシュされるかどうかの簡単な分析

最近、毎日変更されるページを iframe を使用して呼び出す必要があるプロジェクトがあります。その...

MySQL の NOT IN 充填ピットの NULL 列の問題の解決方法

以前、会社で小さな機能に取り組んでいたとき、特定の状況でデータがいくつあるかを数えてから問題を修正し...

CSSプリコンパイル言語とその違いの詳細な説明

1. 何ですか マークアップ言語として、CSSは比較的シンプルな構文とユーザーに対する要件が低いが、...

プレーヤー機能を実現するためのvue + element uiのサンプルコード

効果画像のない表示は単なる空虚な言葉です。 1. オーディオをベースにし、elementUI と組み...

HTML 要素 (タグ) とその使用法

a : ハイパーリンクの開始位置または宛先位置を示します。頭字語: 単語の最初の文字からなる略語を示...

例によるMySql CURRENT_TIMESTAMP関数の分析

時間フィールドを作成するときデフォルトのCURRENT_TIMESTAMPデータを挿入する際、このフ...

ヘッダーのチェックボックスをテキスト実装コードに変更するための選択テーブルを持つ要素

方法1: テーブル属性を使用する: header-cell-class-name テーブルインターフ...

Linux lnコマンドの使用

1. コマンドの紹介ln コマンドは、ファイルのリンクを作成するために使用されます。リンクは、ハード...

SpringBoot + Vue プロジェクトを Linux サーバーにデプロイするための詳細なチュートリアル

序文SpringBoot + Vueのフロントエンドとバックエンドを分離したプロジェクトをどのように...

Vue の this.$router と this.$route の違いと push() メソッド

公式文書には次のように記されている。ルーターを挿入することで、どのコンポーネントでも this.$r...

HTML テーブルタグチュートリアル (25): 垂直配置属性 VALIGN

垂直方向では、行の配置を上、中央、下に設定できます。基本的な構文<TR VALIGN=&quo...