MySQLデータベースの最適化についてです。
これは、メールニュースレターのシステムを作成したときに起こりました。 私たちのシステムは、1日に数千万の手紙を送ることになっています。 手紙を送るのは簡単なことではありませんが、すべてが非常に原始的に見えます：
- HTMLクリエイティブから手紙を収集し、パーソナライズされたデータを置き換えます。
- レターを表示するためのピクセルを追加し、レター内のすべてのリンクを独自のものに置き換えます-クリックを追跡します。
- 送信する前に、メールがブラックリストにないことを確認してください。
- 特定のプールにメールを送信します。
2番目の段落について詳しく説明します。
マイクロサービスのメールビルダーは、送信用の手紙を準備しています：
- レター内のすべてのリンクを見つけます。
- リンクごとに一意の32文字のuuidが生成されます。
- 元のリンクを新しいリンクに置き換え、データをデータベースに保存します。
したがって、すべてのソースリンクはuuidに置き換えられ、ドメインは私たちのものに変更されます。 このリンクを使用してGETリクエストを取得すると、元の画像をプロキシするか、元のリンクにリダイレクトします。 MySQLデータベースで保存が行われ、生成されたuuidが元のリンクといくつかのメタ情報（ユーザーの電子メール、メーリングID、その他のデータ）とともに保存されます。 非正規化は、1回のリクエストで統計を保存するために必要なすべてのデータを取得するか、何らかのトリガーチェーンを開始するのに役立ちます。
問題番号1
私たちのuuidの生成はタイムスタンプに依存していました。
郵送は通常一定期間に行われ、レターを組み立てるためのマイクロサービスの多くのインスタンスが起動されるため、いくつかのuuidは非常に類似していることが判明しました。
これにより、選択性が低くなりました。 UPD：データが類似していたため、バイツリーでの作業はあまり効果的ではありませんでした。
時間の依存性がないPythonのuuidモジュールを使用して、この問題を解決しました。
このような暗黙のことにより、インデックスの速度が低下しました。
ストレージはどうなっていますか？
テーブルの構造は次のとおりです。
CREATE TABLE IF NOT EXISTS `Messages` ( `UUID` varchar(32) NOT NULL, `Message` json NOT NULL, `Inserted` DATE NOT NULL, PRIMARY KEY (`UUID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
作成時には、すべてが論理的に見えました。
UUIDは主キーであり、クラスター化インデックスでもあります。 このフィールドで選択を行うとき、すべての値がそこに保存されているため、単にレコードを選択します。 これは意図的な決定でした。
クラスターインデックスの詳細をご覧ください。テーブルが成長するまで、すべてが素晴らしかった。
問題番号2
クラスターインデックスの詳細を読むと、このニュアンスを確認できます。
テーブルに新しい行を追加する場合、ファイルの最後ではなく、フラットリストの最後ではなく、ソートによってそれに対応するツリー構造の目的のブランチに追加されます。
したがって、負荷が増加すると、挿入時間が増加しました。
解決策は、異なるテーブル構造を使用することでした。
CREATE TABLE IF NOT EXISTS `Messages` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `UUID` varchar(32) NOT NULL, `Message` json NOT NULL, `Inserted` DATE NOT NULL, PRIMARY KEY (`ID`), UNIQUE KEY `UUID` (`UUID`, `Inserted`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
主キーは自動インクリメントになり、mysqlは最後の挿入場所のキャッシュを保存するため、挿入は常に最後に行われます。つまり、Innodbは連続的に増加する値を書き込むために最適化されます。
この最適化の詳細は
postgresのソースコードで見つけました
。 Mysqlは非常によく似た最適化を実装しています。
もちろん、競合が発生しないように一意のキーを追加する必要がありましたが、挿入速度は向上しました。
データベースがさらに大きくなったため、古いデータを削除することを考えました。 挿入フィールドでDELETEを使用することは絶対に最適ではありません。これは非常に長い時間であり、
最適化テーブルコマンドを実行するまで場所は解放されません。 ちなみに、この操作はテーブルを完全にブロックします-これは私たちにはまったく合いませんでした。
したがって、テーブルをパーティションに分割することにしました。
1日-1つのパーティション。古いパーティションは、時間が来ると自動的にドロップします。
問題番号3
古いデータを削除する機会はありましたが、希望するパーティションから選択する機会がありませんでした。select`eではuuidのみを指定するため、mysqlはどのパーティションで検索する必要があるかを認識せず、すべてを探しているためです。
ソリューションは問題＃1から生まれました-生成されたuuidにタイムスタンプを追加します。 今回だけ少し違ったやり方をしました。最初や最後ではなく、行のランダムな場所にタイムスタンプを挿入しました。
ダッシュ記号を追加
する前後に正規表現で取得できるようにします。
この最適化により、uuidが生成された日付を取得し、挿入フィールドの特定の値を示す選択をすでに行うことができました。 ここで、必要なパーティションからすぐにデータを読み取ります。
また、
ROW_FORMAT = COMPRESSEDのようなものと、エンコーディングを
latin1に変更したおかげで、ハードドライブのスペースをさらに節約できました。