MySQL 2,000 万のデータの最適化と移行

最近、最適化と移行が必要な 2000W レコードを含むデータ テーブルがあります。 2,000 万のデータは MySQL にとって非常に恥ずかしいことです。インデックスの作成速度は依然として非常に速く、いくら最適化を行っても速度を大幅に向上させることはできないからです。しかし、これらのデータには冗長なフィールドやエラー情報が多数含まれており、統計や分析には非常に不便です。したがって、新しいテーブルを作成し、古いテーブルのデータを 1 つずつ取り出して最適化し、それを新しいテーブルに戻す必要があります

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

2000Wクエリ条件として使用できるデータ内のフィールド 私たちは予言的です。したがって、データのこの部分に対して個別に新しいフィールドを作成し、通常のデータのフィールド構造を合理的に変更します。たとえば、ID カードは varchar(18) です。重要でないデータについてはマージし、テキスト構造のフィールドを作成します。

ID カードの種類などの一般的なデータは、正確な性別、出身地、誕生日、年齢を取得するためにいくつかの関連データを計算する必要があります。

2. データ移行

データベースから古いデータを取り出し、計算と処理を通じて必要な新しいデータを取得し、最後に新しいデータを新しいテーブルに挿入します。しかし、新たなデータを取得する際に以下の問題が発生しました。

1. データ量が多すぎて一度に取得できません (2000W のデータをメモリに放り込むのは怖いです); MySQL の制限構文を使用してバッチで取得できます。たとえば、50,000 を取得するたびに、SQL ステートメントは次のようになります。

   select * from table_name limit 15000000,50000;

   この方法では、データ量が大きすぎる問題を解決できますが、制限の最初のパラメーターがどんどん大きくなるにつれて、クエリ速度が低下します。非常に遅いです (上記のステートメント SQL の実行には 35 秒かかります)。時間は命であるため、SQL ステートメントの最適化を開始しました。最適化後、次のようになりました。

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

   は 2000W のデータを二分法で分割できます。1000W のデータが実行されると、データは反転されます。最適化後、SQL の実行効率は 35 秒から 9 秒に大幅に改善されました

   しかし、それでも非常に遅いです。時間が命です...幸いなことに、自動インクリメント ID (データ作成の第一法則) があります。テーブルには自動インクリメント フィールドが必要です) )、最適化された SQL は次のとおりです:

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

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

データの量が多すぎるため、データのインポートが失敗する可能性があります

2. 。新しいデータをインポートするための 3 つのオプション 次のように、データを新しいテーブルに保存します。

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

1. 挿入するたびにデータベースが必要になるため、最初はこの解決策はうまくいかないと思います。 IO 操作。ただし、このソリューションの利点は、問題のあるデータを適時に検出し、変更後に実行を継続できることです。Oracle で「バインド変数」を使用するとパフォーマンスが向上し、MySQL も「バインド変数」機能を提供します。したがって、ロジックを変更せずに、データ ストレージ速度の最適化を試みます。コードは次のとおりです。

   public function actionTest(array $data)
   {
       $mysqli = new mysqli("192.168.1.106", "username", "password", "test");
       $sql = "insert into table_name(name,identity) values (?,?)";
   
       $stmt = $connection->prepare($sql);
       $name = "";
       $identity = "";
       //使用绑定变量
       $stmt->bind_param("si", $name, $identity);
       foreach($data as $val)
       {
           $name = $val[name];
           $identity = $val[card_id];
           //执行
           $stmt->execute();
       }
       $stmt->close();
   }

   MySQL の「バインド変数」は明らかな速度向上をもたらしませんが、一度に 50,000 個のデータを挿入できます。

   これが私が最終的に選んだ解決策です。第一に、問題のあるデータを時間内に発見できること、第二に、インポートされたデータが非常に安定していることです。ブレークポイント再開のサポートと同様に、各ステップで効果を確認できます。スクリプトを実行するときに、同時に分析ロジックの作成を開始することもできます。

2. それを SQL ファイルにアセンブルし、最後にそれを均一にインポートすることもできます。

   大きな SQL ファイルをアセンブルして、最終的に MySQL 独自のファイルを介してインポートすることもできます。道具。ただし、SQL ステートメントのいずれかに問題がある場合は、スクリプトを再実行する必要がある場合があります。 9Gテキストファイルのシンボルを修正するのは非常に面倒なので...

3. 概要

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

上記は MySQL 2000 万件のデータの最適化と移行の内容です。その他の関連コンテンツについては、こちらをご覧ください。 PHP 中国語 Web サイト (www.php.cn) に注意してください。