同じデータに対して日付ごとに異なるテーブルを作成するのが賢明でしょうか?

Question

MYSQL InnoDB テーブル table には次の列が含まれています (テーブル名と列名は変更されています):

• 日付 (PK、日付)
• var_a (PK、FK、INT)
• var_b(PK,FK,INT)
• rel_ab(10進数)

ここで、rel_ab は、特定の日付における 2 つの変数 var_a と var_b の間の関係を説明する列です。 (var_a と var_b は別のテーブルを参照します)

データは毎日バッチでアップロードされ、1 日あたり合計約 700 万行になります。問題は、わずか数週間後に、新しい毎日のバッチをアップロードするたびに数時間かかるようになったことでした。テーブルの設計を改善する必要があるのは明らかです。ここでは、フォームに関する追加の詳細をいくつか示します。

• COMPRESSION="zlib" を使用します。
• 複合主キーに加えて、var_a 列と var_b 列の外部キーに必要なインデックスも確立しました。
• このテーブルからデータを抽出するときは、常に指定された日付のクエリ SELECT * FROM table WHERE date = を使用してください。選択には数分しかかかりません。
• var_a と var_b によって参照されるテーブルからエントリを削除する必要は (ほぼ確実に) ありません。
• データはパンダ関数 df.to_sql('temp', con, if_exists='replace',index=False, method='multi') によってアップロードされます。ここで、ignore を挿入します< code>temp table に移動し、temp を削除します。

したがって、私は次の少なくとも 1 つを行う予定です:

• 列 var_a および var_b の外部キー制約を削除し、データ アップロード プロセスを利用してすべてを正しく完了します。これは、この使用例ではどちらのインデックスも実際にはクエリ速度を向上させないためです。
• テーブルを日付ごとに異なるテーブルに分割します。たとえば、table_230501 という名前のテーブルがあり、列 var_a、var_b、rel_ab が含まれています。これは、一度に 1 つの日付のみを選択しているためです。

最初のソリューションはデータの整合性を脅かす可能性があり、2 番目のソリューションはアーキテクチャを乱雑にする可能性があることを理解しています。私の限られた経験では、2 番目のオプションについても聞いたことがありませんし、この設計の例をオンラインで見つけることもできません。これらのオプションの中に賢明な解決策はありますか?どちらもアップロード速度を向上させ、ディスク使用量を削減しますが、どちらにも欠点があります。それ以外の場合、アップロード速度を上げる他の方法はありますか?

編集: SHOW CREATE TABLE は

のようになります。 ああああ

Answer 1

MySQL テーブルのアップロード速度の向上に役立つ可能性のあるソリューションがいくつかあります。

var_a と var_b のインデックスを削除します。これらのインデックスはクエリを高速化するために使用しているわけではないため、インデックスを削除するとアップロード プロセスの高速化に役立ちます。ただし、外部キー制約を使用する場合は、通常、外部キーに属する列にインデックスを保持することをお勧めします。

テーブルを日付でパーティション分割する: パーティション分割すると、データベースが特定のクエリに関連するパーティションのみをスキャンできるため、クエリのパフォーマンスが向上します。ただし、メンテナンスとバックアップも複雑になりますが、クエリがすでに良好に実行されている場合は必要ない可能性があります。

一括挿入メソッドを使用する: df.to_sql を使用して個々の行を挿入する代わりに、LOAD DATA INFILE や MySQL 一括挿入 API などの一括挿入メソッドを使用してみることができます。これは、特に一度に 1 行ずつではなくバッチでデータをアップロードできる場合、個別に挿入するよりも高速です。

別の圧縮アルゴリズムを使用する: 現在 zlib 圧縮を使用していますが、データにとってより高速または効率的な他の圧縮アルゴリズムがあります。さまざまな圧縮オプションを試して、アップロード速度が向上するかどうかを確認してください。

サーバー リソースを増やす: 予算とリソースがある場合は、サーバー ハードウェアをアップグレードするかサーバーの数を増やすと、アップロード速度が向上する可能性があります。これはすべての人にとって実行可能なオプションではないかもしれませんが、他のオプションを使い果たした場合は検討する価値があります。

提案されたオプションに関する限り、外部キー制約を削除するとデータの整合性の問題が発生する可能性があるため、このアプローチはお勧めしません。クエリですでにパフォーマンスの問題が発生している場合は、日付によるパーティション分割が良い解決策になる可能性がありますが、クエリがすでに高速に実行されている場合は、その必要がない可能性があります。

Answer 2

アップロードを高速化するには、アップロードを削除してください。真剣な話、ファイル内の特定の日付の内容を正確に取得することしかやっていないのであれば、なぜデータをテーブルに入れる必要があるのでしょうか? (コメントでは、1 つのファイルが実際には複数のファイルであることが指摘されています。最初にそれらを結合することをお勧めします。)

テーブル内のデータが必要な場合は、これについて話し合いましょう...

• インデックスを決定する前に、 すべての主要なクエリを 確認する必要があります。
• PK 内の列の順序は、ロードとクエリの両方にとって重要です。
• パーティショニングは読み込みには役立つかもしれませんが、クエリには役に立たない可能性があります。例外: 「古い」データは削除しますか?
• 作成テーブルを表示を提供してください。提供したコンテンツには微妙な点が含まれていない可能性があります。
• 読み込みはどのように行われますか?膨大な データをロード中 ?一度に 1 行ずつ挿入しないことを願っています。パンダの仕組みが分かりません。 (また、MySQL アクセスを「簡素化」する他の 99 個のパッケージがどのように機能するのかもわかりません。) それが舞台裏で何をしているのか理解してください。パフォーマンスを向上させるには、Pandas をバイパスする必要がある場合があります。一括ロードは、行ごとのロードより少なくとも 10 倍高速です。
• ロード中に一時テーブルが必要であることは見たことがありません。多分。 (提案したように) FK を削除すると、クエリを実行して、他のテーブルの var_a と var_b の存在を確認できます。それが「アナログFK」です。
• 可能であれば、PK に基づいて受信データを並べ替えます。 (この が経済成長の鈍化の根本原因である可能性があります。)
補助キーはありますか?これらは読み込み速度に影響します。
あなたの FK は、他の
• テーブルの インデックスを暗示していると思います。
他のテーブルに新しい行を追加していますか?
"rel_ab (DECIMAL)" - 小数点以下何桁ですか?正確な声明は何ですか?それが何らかの測定である場合、
• FLOAT を考慮したことがありますか?
現在、他のテーブルには多くの行があります。つまり、それらを参照するために本当に 4 バイトの INT が必要なのでしょうか? 3 バイトに切り替える
• MEDIUMINT [UNSIGNED] 1 日あたり少なくとも 7MB を節約します。
その SELECT の 700 万行をどのように処理しますか?
圧縮なし。 InnoDB は非常に非効率的です。 4 つの列のうち 1 つだけが圧縮可能です。圧縮には追加のbuffer_pool_space が必要です。圧縮には大量の CPU が使用されます。 InnoDB の場合、2 倍の縮小が一般的です。

複数の「同一の」テーブルは常に賢明ではありません。テーブルは常に優れています。ただし、上で示唆したように、ゼロ テーブルの方がまだ優れています。