MySQL の 2,000 万のデータを最適化して移行する方法

最近、最適化と移行が必要な 2,000 万件のレコードを含むデータ テーブルがあります。 2000W のデータは、MySQL にとって非常に恥ずかしいことです。これは、index を適切に作成する速度が、どんなに最適化されていても非常に速いためです。速度は大幅には向上しませんが、これらのデータには冗長なフィールドとエラー メッセージが多く、統計や分析には非常に不便です。そのため、新しいテーブルを作成して古いテーブルからデータを抽出する必要があります。 1 つずつ増やして最適化します。

1. 冗長なデータを削除し、フィールド構造を最適化します

2000W データでは、

クエリ

条件として使用できるフィールドがすでにわかっています。したがって、データのこの部分に新しいフィールドを個別に作成します。たとえば、ID カードは varchar(18) であり、テキスト構造のフィールドにマージされます。 たとえば、ID カードの種類から正確な性別、生年月日、年齢を取得する必要があります

2. データの移行

を作成し、計算と処理を通じて新しいデータを取得します。しかし、新しいデータを取得するときに次の問題が発生します

データの量が多すぎて一度に取得できません。時間 (2000W のデータをメモリに投入するのは非常に恐ろしいです);

1. MySQL の制限構文を使用して、一度に 50,000 個を取得できます。データが多すぎるという問題がありますが、制限の最初のパラメータが大きくなるにつれて、クエリの速度が恐ろしく遅くなります (上記の SQL の実行には 35 秒かかります)。そのため、最適化を開始しました。最適化後、

   select * from table_name limit 15000000,50000;

   は 2000W のデータを二分法で分割することができ、最適化後は SQL の実行効率が 35 秒から大幅に向上しました。 9 秒まで;

   しかし、それでも非常に遅いです、時間が命です...幸いなことに、私たちは自己増加する ID (データテーブル作成の第一法則、自動インクリメントフィールドでなければなりません) を持っており、最適化された SQL は次のようになります。

   select * from table_name order by id desc limit 5000000,50000;

   視覚的なデモンストレーションのために、同じ関数を使用して 2 つの SQL を作成しました。最初のものと比較すると、2 番目の SQL の制限により SQL のインデックス ヒットが悪化しますが、効率は同じです。最初の SQL の実行時間は 2 ミリ秒、2 番目の SQL の実行時間は 5 ミリ秒です (私が取得した平均値)。各データ クエリの速度は 35 秒から 2 ミリ秒に直接低下します。

   データの量が多すぎるため、データを推定できません。一部の特殊なデータはデータのインポートに失敗します。

新しいデータを新しいテーブルに保存するには、次の 3 つのオプションがあります。

2. データを 1 つずつ挿入します

   最初は、挿入するたびにデータベース IO 操作が発生するため、このソリューションは機能しないと思いました。しかし、このソリューションの利点は、問題のあるデータを検出できることです。 Oracle では「バインディング」を使用して、パフォーマンスを向上させることができます。MySQL にも「バインディング変数」の機能があります。したがって、ロジックを変更せずに、データ ストレージ速度の最適化を試みます。コードは次のとおりです。

   1. select * from table_name where id>15000000 and id<15050000； 2. select * from table_name where id>15000000 limit 50000;

MySQL の「バインド変数」は明らかな速度向上をもたらしませんが、一度に 50,000 個のデータを挿入できます。
1. これが私が最終的に選んだ解決策です。第一に、問題のあるデータを時間内に発見できること、第二に、インポートされたデータが非常に安定していることです。ブレークポイント再開のサポートと同様に、各ステップで効果を確認できます。スクリプトを実行するときに、同時に分析ロジックの作成を開始することもできます。
   は SQL ファイルにアセンブルされ、最終的には均一にインポートされます。

   大きな SQL ファイルを組み立てて、最終的に MySQL に付属のツールを使用してインポートすることもできます。ただし、SQL ステートメントのいずれかに問題がある場合は、スクリプトを再実行する必要がある場合があります。 9G テキスト ファイル内のシンボルを変更するのは非常に面倒なので...

3. 概要

さまざまな最適化により、スクリプトの実行時間は最終的に 20 分未満に短縮されました。最適化後のデータ品質は非常に保証されています。次回は 2 億件のデータの最適化と移行を試みます...

。

以上がMySQL の 2,000 万のデータを最適化して移行する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。