ライブラリ設計におけるプライマリ・キーの選択
12315 ワード
以前の記事「ネットワークアーキテクチャのライブラリ設計」では、MySQLライブラリ設計におけるプライマリ・キーの選択について言及しました.この文章では、前の文章に対する補足として、この問題について議論したいと思います.
前述のまたネットを打つでは、プライマリ・キーとしてグローバル一意のフィールドが使用されています.例えば、写真表を例にとると、異なるユーザの写真データは異なるShard(またはMySQLノード/インスタンス)に格納されているが、各写真はサイト全体で一意のIDを表示している.
なぜグローバル唯一なのか?
データベース・クラスタを拡張する場合、負荷のバランスを確保するためには、異なるShard間でデータの移動が必要です.プライマリ・キーが一意でなければ、このようにデータを勝手に移動することはできません.最初は,この問題を解決するために結合プライマリ・キーを用いることを考えた.一般的には、
次に、各Shardは2台のMySQLサーバで構成され、この2台のサーバはmaster-masterのレプリケーション方式を採用し、各Shardが常に書き込み可能であることを保証します.master-masterレプリケーション方式では、2台のサーバにそれぞれ挿入されたデータに異なるプライマリ・キーがあることを保証する必要があります.そうしないと、別のサーバにレプリケーションすると、プライマリ・キーの重複エラーが発生します.プライマリ・キーがグローバルで唯一であることを保証すれば、この問題は自然に解決されます.データ分割が採用されていない設計では、自己増加フィールドを使用する場合は、ネットワークアーキテクチャのライブラリ設計の解決策を参照してください.
可能なソリューション UUID
UUIDをメインキーとして使えるかもしれませんが、UUIDは長い列で、URLに入れるのは見苦しいですね.木がありますか.もちろんこれは重要ではありません.もっと重要な原因は性能です.UUIDの生成には順序性がないため、書き込み時にインデックスの異なる位置をランダムに変更する必要があり、これにはより多くのIO操作が必要であり、インデックスが大きすぎてメモリに保存できない場合はなおさらである.一方,UUIDインデックスの場合,1 keyに32バイト(もちろんバイナリ形式で格納すれば16バイトまで圧縮できる)が必要となるため,インデックス全体も比較的大きい. MySQL自増フィールド 単一のMySQLデータベースのアプリケーションでは、一般的に自己増加フィールドを設定すればよいが、水平ライブラリの設計では、この方法はグローバル一意性を保証することは明らかではない.では、Shardの各テーブルにはこのIDライブラリに対応するテーブルがあり、この対応するテーブルには1つのフィールドしかなく、このフィールドは自己増加しています.新しいデータを挿入する必要がある場合、まずIDライブラリの対応するテーブルにレコードを挿入し、新しいIDを取得し、Shardに挿入されたデータのプライマリ・キーとして使用します.この方法の欠点は,追加の挿入操作が必要であり,IDライブラリが大きくなると性能も低下することである.IDライブラリのデータセットがあまり大きくないことを保証するには、定期的に前の記録を整理する方法があります.は、他のツール を導入する.
ネットワークアーキテクチャのライブラリ設計、この文章などは、原子的なincrement操作をサポートしています.また、それらの優れた性能は、書き込み時の追加のオーバーヘッドを低減することができるので、シーケンスジェネレータとして使用できるかもしれません.Redisの問題は持続性にあるので、私たちは考えません.Memcachedもリアルタイムで永続的ではなく、もちろんリアルタイムに構成することもできますが、それはおかしいです.もちろん、Memcached、Redis、Kyoto Cabinet、Tokyo Cabinetなど、持続的なツールもあります.伝説的にはパフォーマンスは良いですが、他のツールを導入すると、アーキテクチャの複雑さが増加し、メンテナンスコストも増加します.私达のチームはとても小さくて、精力は有限で、私达は十分に使うことができる原则を奉行して、つまり特别な原因がなくて、受け入れることができる情况の下で、できるだけ私达の熟知したツールで问题を解决します.だから、私たちはやはりMySQLでこの問題を解決する方法を考えてみましょう.
より良いシナリオ
私たちは最初から上記のMySQL自増フィールドの方法を採用していましたが、「MongoDB」という文章で説明されている方法を見て、おおらかになりました.私はいつもこのように考えています:もしあれらのオープンソースの製品がなければ、あれらの無私に経験を分かち合う人がいなければ、私达の自分の能力だけでどの程度することができます.あの人たちに感謝しているので、私もできるだけ自分の経験を共有します.
まずFlickrの記事で説明した方法を説明します.彼らはTicket Servers: Distributed Unique Primary Keys on the CheapというMySQLの拡張機能を使用しています.REPLACE INTOはINSERTの機能と同じですが、REPLACE INTOを使用して新しいデータ行を挿入する場合、新しく挿入した行のプライマリ・キーまたはユニーク・キーの既存の行が重複すると、既存の行が削除されてから新しいデータ行が挿入されます.安心してください.これは原子操作です.
次のようなテーブルを作成します.
グローバル一意IDを取得する必要がある場合は、次のSQL文を実行します.
この文を初めて実行すると、ticket 64テーブルには次のデータが含まれます.
以降、前の文を再度実行すると、stubフィールド値が'a'の行が既に存在するため、MySQLはこの行を削除してから挿入します.したがって、2回目の実行後もticket 64テーブルには1行のデータしかなく、idフィールドの値が2であるだけである.このテーブルには常に1行のデータしかありません.
FlickrはPhoto,Group,Account,TaskのためにそれぞれのIDの連続性を維持するためにticketテーブルを作成した.他のビジネス・テーブルのIDは、同じticketテーブルを使用して生成されます.
いいですね.実はもっと素晴らしいです.例えば、ticketテーブル1枚で、すべてのトラフィックテーブルにそれぞれ連続したIDを提供することができる.次に、私たちの方法を見てみましょう.まず、表の構造を見てみましょう.
違いに注意して、idフィールドは自己増加ではなく、プライマリ・キーでもありません.使用する前に、初期化データを挿入する必要があります.
次に、次のSQL文を実行して、新しい写真IDを取得できます.
idフィールドを1増加させ、増加した値をREPLACE INTO関数に渡し、LAST_INSERT_IDの戻り値を指定する更新操作を行った.
実際には、シーケンスの名前を予め指定する必要はありません.新しいシーケンスが必要な場合は、次のSQL文を直接実行できます.
ここでは、LAST_INSERT_IDというMySQL拡張を採用しています.この拡張機能もINSERTと同じように新しいレコードを挿入しますが、新しく挿入した行のプライマリキーまたはユニークキー(UNIQUE Key)が既存の行と重複すると、既存の行に対してUPDATE操作が行われます.
なお、上記の文を初めて実行すると、nameが「new_Business’のフィールドなので、通常は挿入操作が実行され、UPDATEは実行されないため、INSERT … ON DUPLICATE KEY UPDATEに値が渡されません.その後SELECT LAST_を実行しますINSERT_ID()で返される値は確定できませんが、現在の接続がその前にどのような操作を行ったかによって、実行していないとLAST_に影響します.INSERT_ID値の操作は、戻り値が0になります.そうでなければ、その操作によって生成された値です.だから、私たちはできるだけこのような方法を避けるべきです.
UPDATE:この方法は、単一の問題を解決しやすく、2つのサーバに限らず、異なるサーバに対して異なる初期値(ただし連続的でなければならない)を設定し、インクリメンタルをサーバ数にすればよい.
まとめてみる
私はやはりその言葉で、十分使えばいいです.もちろん、他の製品や案を理解しないというわけではありません.LAST_INSERT_IDもいくつかの新興製品を使用しています.例えば、またネットを打つ(10年3月から正式な環境で使用され始めました.比較的早い使用者です).その導入は確かに私たちのいくつかの問題をよりよく、より便利で、より効率的に解決することができます.重要なのは、使用前に十分な理解が必要です.Redisの使用状況については後述する.
前述のまたネットを打つでは、プライマリ・キーとしてグローバル一意のフィールドが使用されています.例えば、写真表を例にとると、異なるユーザの写真データは異なるShard(またはMySQLノード/インスタンス)に格納されているが、各写真はサイト全体で一意のIDを表示している.
なぜグローバル唯一なのか?
データベース・クラスタを拡張する場合、負荷のバランスを確保するためには、異なるShard間でデータの移動が必要です.プライマリ・キーが一意でなければ、このようにデータを勝手に移動することはできません.最初は,この問題を解決するために結合プライマリ・キーを用いることを考えた.一般的には、
user_idと自己増加photo_idがプライマリ・キーとして使用されます.これは、モバイル・データがもたらす可能性のあるプライマリ・キー競合の問題を解決することができますが、Shard間のデータが関係すると、一意性を保証するためにより多くのフィールドでプライマリ・キーを構成する必要があります.したがって、プライマリ・キーのインデックスは大きくなります.これにより、クエリーのパフォーマンスに影響し、書き込みのパフォーマンスにも影響します.次に、各Shardは2台のMySQLサーバで構成され、この2台のサーバはmaster-masterのレプリケーション方式を採用し、各Shardが常に書き込み可能であることを保証します.master-masterレプリケーション方式では、2台のサーバにそれぞれ挿入されたデータに異なるプライマリ・キーがあることを保証する必要があります.そうしないと、別のサーバにレプリケーションすると、プライマリ・キーの重複エラーが発生します.プライマリ・キーがグローバルで唯一であることを保証すれば、この問題は自然に解決されます.データ分割が採用されていない設計では、自己増加フィールドを使用する場合は、ネットワークアーキテクチャのライブラリ設計の解決策を参照してください.
可能なソリューション
UUIDをメインキーとして使えるかもしれませんが、UUIDは長い列で、URLに入れるのは見苦しいですね.木がありますか.もちろんこれは重要ではありません.もっと重要な原因は性能です.UUIDの生成には順序性がないため、書き込み時にインデックスの異なる位置をランダムに変更する必要があり、これにはより多くのIO操作が必要であり、インデックスが大きすぎてメモリに保存できない場合はなおさらである.一方,UUIDインデックスの場合,1 keyに32バイト(もちろんバイナリ形式で格納すれば16バイトまで圧縮できる)が必要となるため,インデックス全体も比較的大きい.
ネットワークアーキテクチャのライブラリ設計、この文章などは、原子的なincrement操作をサポートしています.また、それらの優れた性能は、書き込み時の追加のオーバーヘッドを低減することができるので、シーケンスジェネレータとして使用できるかもしれません.Redisの問題は持続性にあるので、私たちは考えません.Memcachedもリアルタイムで永続的ではなく、もちろんリアルタイムに構成することもできますが、それはおかしいです.もちろん、Memcached、Redis、Kyoto Cabinet、Tokyo Cabinetなど、持続的なツールもあります.伝説的にはパフォーマンスは良いですが、他のツールを導入すると、アーキテクチャの複雑さが増加し、メンテナンスコストも増加します.私达のチームはとても小さくて、精力は有限で、私达は十分に使うことができる原则を奉行して、つまり特别な原因がなくて、受け入れることができる情况の下で、できるだけ私达の熟知したツールで问题を解决します.だから、私たちはやはりMySQLでこの問題を解決する方法を考えてみましょう.
より良いシナリオ
私たちは最初から上記のMySQL自増フィールドの方法を採用していましたが、「MongoDB」という文章で説明されている方法を見て、おおらかになりました.私はいつもこのように考えています:もしあれらのオープンソースの製品がなければ、あれらの無私に経験を分かち合う人がいなければ、私达の自分の能力だけでどの程度することができます.あの人たちに感謝しているので、私もできるだけ自分の経験を共有します.
まずFlickrの記事で説明した方法を説明します.彼らはTicket Servers: Distributed Unique Primary Keys on the CheapというMySQLの拡張機能を使用しています.REPLACE INTOはINSERTの機能と同じですが、REPLACE INTOを使用して新しいデータ行を挿入する場合、新しく挿入した行のプライマリ・キーまたはユニーク・キーの既存の行が重複すると、既存の行が削除されてから新しいデータ行が挿入されます.安心してください.これは原子操作です.
次のようなテーブルを作成します.
CREATE TABLE `tickets64` ( `id` bigint(20) unsigned NOT NULL auto_increment, `stub` char(1) NOT NULL default '', PRIMARY KEY (`id`), UNIQUE KEY `stub` (`stub`) ) ENGINE=MyISAM;グローバル一意IDを取得する必要がある場合は、次のSQL文を実行します.
REPLACE INTO `tickets64` (`stub`) VALUES ('a'); SELECT LAST_INSERT_ID();この文を初めて実行すると、ticket 64テーブルには次のデータが含まれます.
+--------+------+ | id | stub | +--------+------+ | 1 | a | +--------+------+以降、前の文を再度実行すると、stubフィールド値が'a'の行が既に存在するため、MySQLはこの行を削除してから挿入します.したがって、2回目の実行後もticket 64テーブルには1行のデータしかなく、idフィールドの値が2であるだけである.このテーブルには常に1行のデータしかありません.
FlickrはPhoto,Group,Account,TaskのためにそれぞれのIDの連続性を維持するためにticketテーブルを作成した.他のビジネス・テーブルのIDは、同じticketテーブルを使用して生成されます.
いいですね.実はもっと素晴らしいです.例えば、ticketテーブル1枚で、すべてのトラフィックテーブルにそれぞれ連続したIDを提供することができる.次に、私たちの方法を見てみましょう.まず、表の構造を見てみましょう.
CREATE TABLE `sequence` ( `name` varchar(50) NOT NULL, `id` bigint(20) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`name`) ) ENGINE=InnoDB;違いに注意して、idフィールドは自己増加ではなく、プライマリ・キーでもありません.使用する前に、初期化データを挿入する必要があります.
INSERT INTO `sequence` (`name`) VALUES ('users'), ('photos'), ('albums'), ('comments');次に、次のSQL文を実行して、新しい写真IDを取得できます.
UPDATE `sequence` SET `id` = LAST_INSERT_ID(`id` + 1) WHERE `name` = 'photos'; SELECT LAST_INSERT_ID();idフィールドを1増加させ、増加した値をREPLACE INTO関数に渡し、LAST_INSERT_IDの戻り値を指定する更新操作を行った.
実際には、シーケンスの名前を予め指定する必要はありません.新しいシーケンスが必要な場合は、次のSQL文を直接実行できます.
INSERT INTO `sequence` (`name`) VALUES('new_business') ON DUPLICATE KEY UPDATE `id` = LAST_INSERT_ID(`id` + 1); SELECT LAST_INSERT_ID();ここでは、LAST_INSERT_IDというMySQL拡張を採用しています.この拡張機能もINSERTと同じように新しいレコードを挿入しますが、新しく挿入した行のプライマリキーまたはユニークキー(UNIQUE Key)が既存の行と重複すると、既存の行に対してUPDATE操作が行われます.
なお、上記の文を初めて実行すると、nameが「new_Business’のフィールドなので、通常は挿入操作が実行され、UPDATEは実行されないため、INSERT … ON DUPLICATE KEY UPDATEに値が渡されません.その後SELECT LAST_を実行しますINSERT_ID()で返される値は確定できませんが、現在の接続がその前にどのような操作を行ったかによって、実行していないとLAST_に影響します.INSERT_ID値の操作は、戻り値が0になります.そうでなければ、その操作によって生成された値です.だから、私たちはできるだけこのような方法を避けるべきです.
UPDATE:この方法は、単一の問題を解決しやすく、2つのサーバに限らず、異なるサーバに対して異なる初期値(ただし連続的でなければならない)を設定し、インクリメンタルをサーバ数にすればよい.
まとめてみる
私はやはりその言葉で、十分使えばいいです.もちろん、他の製品や案を理解しないというわけではありません.LAST_INSERT_IDもいくつかの新興製品を使用しています.例えば、またネットを打つ(10年3月から正式な環境で使用され始めました.比較的早い使用者です).その導入は確かに私たちのいくつかの問題をよりよく、より便利で、より効率的に解決することができます.重要なのは、使用前に十分な理解が必要です.Redisの使用状況については後述する.