MySQL のテーブル内のレコード数を制限する方法

MySQL のテーブル内のレコード数を制限する方法に対する簡単な答えはありません。たとえば、コマンドを実行したり、単にパラメータを設定したりしても、問題を完全に解決することはできません。次に、いくつかのオプションのソリューションを紹介します。

データベースの場合、問題に対する解決策は一般に 2 つあります。1一種是在應用端、另外一種是在數據庫端。

まず、データベース側では (假設表硬性限制為1W條記錄)、

1. トリガーソリューション

トリガーの考え方は非常にシンプルです。毎回新しいレコードを挿入する前に、テーブル内のレコード数が制限数に達しているかどうかを確認します。達していない場合は挿入を続行し、達している場合は最初に新しいレコードを挿入してから最も古いレコードを削除するか、その逆を行います。テーブル内のレコードの総数を確認するために毎回テーブル全体をスキャンするのを避けるために、別のテーブルを現在のテーブルのカウンターとして使用することを計画しています。挿入する前に、カウンターテーブルを確認するだけで済みます。この要件を満たすには、2 つのトリガーとカウンターテーブルが必要です。
t1 はレコード数を制限する必要があるテーブルであり、 t1_count はカウンターテーブルです。

mysql:ytt_new>テーブルt1を作成します(id int auto_increment主キー、r1 int);
クエリは正常、影響を受けた行は 0 行 (0.06 秒)
   
mysql:ytt_new>テーブルt1_count(cnt smallint unsigned);を作成します。
クエリは正常、影響を受けた行は 0 行 (0.04 秒)
   
mysql:ytt_new>t1_count を挿入し、cnt=0 を設定します。
クエリは正常、1 行が影響を受けました (0.11 秒)

挿入アクション用のトリガーを 1 つ、合計 2 つ記述する必要があります。

区切り文字 $$

`ytt_new`$$ を使用します

DROP TRIGGER /*!50032 が存在する場合 */ `tr_t1_insert`$$

作成する
    /*!50017 定義者 = 'ytt'@'%' */
    `t1` の INSERT 後に `tr_t1_insert` をトリガーする 
    各行の開始
       t1_count を更新し、cnt= cnt+1 に設定します。
    終わり;
$$

区切り文字 ;

もう 1 つは削除アクショントリガーです。

区切り文字 $$

`ytt_new`$$ を使用します

DROP TRIGGER /*!50032 が存在する場合 */ `tr_t1_delete`$$

作成する
    /*!50017 定義者 = 'ytt'@'%' */
    `t1` の削除後に `tr_t1_delete` をトリガーする 
    各行の開始
       t1_count を更新し、cnt= cnt-1 を設定します。
    終わり;
$$

区切り文字 ;

テーブル t1 に 10,000 件のレコードを作成し、上限に達します。

mysql:ytt_new> t1 (r1) を再帰的に tmp(a,b) として挿入します (select 1,1 union all select a+1,ceil(rand()*20) from tmp where a<10000 ) select b from tmp;
クエリは正常、10000 行が影響を受けました (0.68 秒)
レコード: 10000 重複: 0 警告: 0

カウンターテーブルt1_countは1Wを記録します。

mysql:ytt_new>t1_countからcntを選択します。
+-------+
|件数|
+-------+
| 10000 |
+-------+
セット内の 1 行 (0.00 秒)

挿入する前に、カウンターテーブルが制限に達しているかどうかを確認する必要があります。制限に達している場合は、まず古いレコードを削除します。ロジックを簡単に整理するためにストアドプロシージャを作成しました。

区切り文字 $$

`ytt_new`$$ を使用します

存在する場合はプロシージャを削除します `sp_insert_t1`$$

CREATE DEFINER=`ytt`@`%` PROCEDURE `sp_insert_t1`(
    f_r1 で
    ）
始める
      v_cnt INT をデフォルト 0 として宣言します。
      t1_count から cnt を v_cnt に選択します。
      v_cnt >=10000の場合
        t1 から id で ORDER BY し、ASC LIMIT 1 で削除します。
      終了の場合;
      t1(r1) に値 (f_r1) を挿入します。          
    終わり$$

区切り文字 ;

この時点で、ストアドプロシージャの呼び出しが可能になります。

mysql:ytt_new>sp_insert_t1(9999)を呼び出します。
クエリは正常、1 行が影響を受けました (0.02 秒)

mysql:ytt_new>t1からcount(*)を選択します。
+----------+
| カウント(*) |
+----------+
| 10000 |
+----------+
セット内の1行（0.01秒）

このストアドプロシージャの処理ロジックは、バッチ処理にさらに最適化することもできます。たとえば、テーブルレコードを毎回 2 倍ずつキャッシュする場合、判断ロジックは、20,000 レコードまでは只插入新記錄，并不刪除老記錄になります。20,000當到達2W條后，一次性刪除舊的1W條記錄。

このソリューションには次の欠点があります。

カウンターテーブルのレコード更新は、挿入/削除によってトリガーされます。テーブルが切り捨てられると、カウンターテーブルは更新されず、データの不整合が発生します。
テーブルを削除すると、トリガーも削除されます。トリガーを再構築し、カウンターテーブルをリセットする必要があります。
テーブルは、ストアドプロシージャなどの単一のエントリを通じてのみ書き込むことができ、他のエントリは使用できません。

2. パーティションテーブルソリューション

rangeパーティションを作成します。最初のパーティションには 10,000 件のレコードがあります。2 番目のパーティションはデフォルトのパーティションです。テーブルレコードの数が制限に達したら、最初のパーティションを削除し、パーティション定義を再調整します。

パーティションテーブルの初期定義:

mysql:ytt_new>create table t1(id int auto_increment primary key, r1 int)partition by range(id) (partition p1 values less than(10001),partition p_max values less than(maxvalue));
クエリは正常、影響を受けた行は 0 行 (0.45 秒)

最初のパーティションがいっぱいかどうかを確認します。

mysql:ytt_new>t1パーティション(p1)からcount(*)を選択します。
+----------+
| カウント(*) |
+----------+
| 10000 |
+----------+
セット内の 1 行 (0.00 秒)

最初のパーティションを削除し、パーティションテーブルのサイズを変更します。

mysql:ytt_new>テーブル t1 を変更してパーティション p1 を削除します。
クエリは正常、影響を受けた行は 0 行 (0.06 秒)
レコード: 0 重複: 0 警告: 0

mysql:ytt_new>テーブルt1を変更して、パーティションp_maxを（パーティションp1の値が（20001）未満、パーティションp_maxの値が（maxvalue）未満）に再編成します。
クエリは正常、影響を受けた行は 0 行 (0.60 秒)
レコード: 0 重複: 0 警告: 0

このアプローチの利点は明らかです。

テーブル挿入エントリは、ランダム、INSERT ステートメント、ストアドプロシージャ、またはインポートファイルになります。
最初のパーティションを削除するのは DROP 操作であり、非常に高速です。

しかし、欠点もあります。テーブルレコードにギャップがあってはなりません。ギャップがある場合は、パーティションテーブルの定義を変更する必要があります。たとえば、パーティション p1 の最大値を 20001 に変更した場合、このパーティション内のレコードの半分が不連続であっても、取得パーティション内のレコードの合計数には影響しません。

3. 一般的な表領域ソリューション

このテーブルの 10,000 レコードに必要なディスク容量を事前に計算し、このテーブルのデータを格納するためにディスク上にゾーンを割り当てます。
パーティションをマウントし、 InnoDBテーブルスペースの代替ディレクトリ(/tmp/mysql/)。

mysql:ytt_new>テーブルスペース ts1 を作成し、データファイル '/tmp/mysql/ts1.ibd' を追加します。エンジンは innodb です。
クエリは正常、影響を受けた行は 0 行 (0.11 秒)
mysql:ytt_new>テーブル t1 のテーブルスペース ts1 を変更します。
クエリは正常、影響を受けた行は 0 行 (0.12 秒)
レコード: 0 重複: 0 警告: 0

大まかな計算をしましたが、あまり正確ではないので、記録に多少の誤りがあるかもしれませんが、意味は非常に明確です。テーブルに「TABLE IS FULL」と表示されるまで待ちます。

mysql:ytt_new>t1(r1)の値を挿入します（200）;
エラー 1114 (HY000): テーブル 't1' がいっぱいです

mysql:ytt_new>t1からcount(*)を選択します。
+----------+
| カウント(*) |
+----------+
|10384|
+----------+
セット内の1行（0.20秒）

テーブルがいっぱいになったら、テーブルスペースを削除し、テーブルをクリアしてから、新しいレコードを挿入します。

mysql:ytt_new>テーブル t1 のテーブルスペース innodb_file_per_table を変更します。
クエリは正常、影響を受けた行は 0 行 (0.18 秒)
レコード: 0 重複: 0 警告: 0

mysql:ytt_new>テーブルスペースts1を削除します。
クエリは正常、影響を受けた行は 0 行 (0.13 秒)

mysql:ytt_new>テーブルt1を切り捨てます。
クエリは正常、影響を受けた行は 0 行 (0.04 秒)

もう 1 つは、アプリケーション側で処理することです。

アプリケーション側でテーブルデータを事前にキャッシュしておき、一定件数に達したらデータベース側に一括で書き込むことができます。データベースに書き込む前に、テーブルをクリアするだけです。
たとえば、テーブルt1のデータはファイルt1.csvにキャッシュされます。 t1.csvが1W行に達すると、データベースはテーブルデータをクリアし、 t1.csvをインポートします。

結論：

MySQL の MyISAM 時代には、テーブル属性max_rowsを使用してテーブル内のレコード数を見積もっていましたが、これは厳格なルールではありませんでした。これは、一般的なテーブルスペースを使用してテーブル内のレコード数を制限することについて上で書いたことと似ています。InnoDB InnoDBには直感的な方法はなく、上記の方法によって問題が解決されることが多くなっています。選択する具体的なソリューションは、ニーズによって異なります。

これで、MySQL のテーブル内のレコード数を制限する方法についての記事は終了です。MySQL のテーブル内のレコード数を制限する方法の詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

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