Clever Geek Handbook

1つのMySQL最適化の歴史

MySQLデータベースの最適化についてです。



これは、メールニュースレターのシステムを作成したときに起こりました。 私たちのシステムは、1日に数千万の手紙を送ることになっています。 手紙を送るのは簡単なことではありませんが、すべてが非常に原始的に見えます：



  1. HTMLクリエイティブから手紙を収集し、パーソナライズされたデータを置き換えます。
  2. レターを表示するためのピクセルを追加し、レター内のすべてのリンクを独自のものに置き換えます-クリックを追跡します。
  3. 送信する前に、メールがブラックリストにないことを確認してください。
  4. 特定のプールにメールを送信します。


2番目の段落について詳しく説明します。

マイクロサービスのメールビルダーは、送信用の手紙を準備しています：





したがって、すべてのソースリンクは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に変更したおかげで、ハードドライブのスペースをさらに節約できました。


More articles:


All Articles