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+ ツリーの理解」を参照してください。

図（１）

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

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