MySQL 2,000 万のデータの最適化と移行-mysql チュートリアル-php.cn

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

黄舟

リリース： 2017-02-21 10:22:44

オリジナル

1802 人が閲覧しました

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

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

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

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

2. データ移行

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

データ量が多すぎて一度に取得できません (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 ミリ秒に直接低下しました...
。新しいデータをインポートするための 3 つのオプション次のように、データを新しいテーブルに保存します。

挿入するたびにデータベースが必要になるため、最初はこの解決策はうまくいかないと思います。 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 個のデータを挿入できます。

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

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