MySQL key/valueテーブルの2つのデザイン

FriendfeedのMySQL key/valueストレージ
これは2009年初頭の資料ですHow FriendFeed uses MySQL to store schema-less dataは、ほとんどの人が見たことがあると信じています.Fenngの中国語でFriendFeedがMySQLを使った経験を紹介します.本文は異なる角度から再補充する.著者は数ヶ月前に広州技術サロンでKey value store漫談の講演を行ったことがあり、多くの参加者はkey value方向に強い使用意欲を持っているが、MySQLを完全に捨てることに疑問を持っている.需要250 M entities、entitiesテーブルには2億5000万件の記録があり、もちろん分庫されています.典型的な解決策:RDBMSの問題:ビジネスでは不定期にテーブル構造を変更する必要があるが、2.5億レコードのテーブルにフィールドを追加削除し、インデックスを変更するにはテーブルをロックする必要があり、最長1時間から1日以上かかる.CouchDBCouchDBの問題:Performance?広く使われていますか?安定性?耐圧性?MySQLシナリオMySQLはDocument storeより優れている:
データの紛失や破損の心配はありません.
Replication
その特性と不足をよく知っていて、どのように解決するかを知っています.
結論:総合的に取捨選択し、MySQLを使用してkey/value(schema-less)データを格納し、valueに次のように配置できます.
# |7 u. ~& s: q! y. C0 T% I( m
Python dict
+ N5 W# e/|+ w; i
JSON object
- B$ l. K5 b! `2 O"`0 o: D6 |- |
実際のfriendfeedにはzlib圧縮のPython dictデータが格納されており、もちろんこのような言語をバインドする方法は論争的である.
5 l4 W* s: h"|* [-\
テーブル構造およびIndex設計モードfeedデータは基本的にentitiesテーブルに存在し、その構造は
my
sql
> desc entities;
8 t0 e7 Q1 w$ T2 F5 f
+----------+------------+------+-----+-------------------+----------------+
4 H"@7 V( o0 D
| Field    | Type       | Null | Key | Default           | Extra          |
+----------+------------+------+-----+-------------------+----------------+
- N- K  H4 ~. R, B+ y
| added_id | int(11)    | NO   | PRI | NULL              | auto_increment |
7\! o7 C) X( R+ B"V% ]3 w
| id       | binary(16) | NO   | UNI |                   |                |
  w"n8 s6 F2 j+ {' `* ?
| updated  | timestamp  | YES  | MUL | CURRENT_TIMESTAMP |                |
| body     | mediumblob | YES  |     | NULL              |                |
+----------------------------+--------------+--------------+-----------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  ^8 y% n"n2 U, `9 P"U$ P
{
! x8 B6 e. w/{0 o/{
"id": "71f0c4d2291844cca2df6f486e96e37c",
( a* A% T. w+ H: y
"user_id": "f48b0440ca0c4f66991c4d5f6a078eaf",
  e  s- [. a: n0 n7 S1 g1 o
"feed_id": "f48b0440ca0c4f66991c4d5f6a078eaf",
# }$ G8 A- ^0 O# _4 E4 X
"title": "We just launched a new backend system for FriendFeed!",
"link": "http://friendfeed.com/e/71f0c4d2-2918-44cc-a2df-6f486e96e37c",
"published": 1235697046,
+ d+ {2 ~3 P8 j: t  X' b
"updated": 1235697046,
/c0 L"c( f- w& W: L
}linkフィールドをインデックスする場合は、別のテーブルで格納します.
mysql> desc index_link;
7 H9 H& ^( c"x% q"m"R3 f
+-----------+--------------+------+-----+---------+-------+
6 w5 B  a# }2 H+ H( h5 ~
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
1 Z2 m/y2 l& O; `
| link      | varchar(255) | NO   | PRI |         |       |
| entity_id | binary(16)   | NO   | PRI |         |       |
, J, M2 j( a& J
+-----------+--------------+------+-----+---------+-------+
"k4 q+ b9 y+ Y0 Z
2 rows in set(0.00 sec)の利点は
インデックスを追加するには1.CREATE TABLE,2.更新プログラムインデックスを削除するには1.プログラムはインデックステーブルの書き込みを停止します(実際には普通のテーブルです).DROP TABLEインデックステーブルこのインデックス方式も参考になる設計モデルであり、特にkey valueタイプのデータがコンテンツをインデックスする必要がある場合である.
 
 
Mysqlテーブル設定モードのKey Value Table
 
Key/value approach in database design could come in handy when we need to store some arbitrary data about another table.
Key/Valueモードは、別のテーブルに関する任意の関連データを格納するために使用されます.もういい,やはりひっくり返さないで,恥ずかしい.
例:usersテーブルがあり、簡単にユーザー名とパスワードを保存し、自己増加フィールドがあります.

 
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL,
  `pass` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM ;

オンラインで運行してから、とても使いやすいです.しかし、ある日、顧客はユーザーの電話番号、年齢、性別、住所などの情報を保存することにした.
通常はusersテーブルにフィールドを拡張します

ALTER TABLE `users` ADD `phone` CHAR(32);
ALTER TABLE `users` ADD `mobile` CHAR(32);
......
......

または別のuser_を作成しますdetailsテーブル、user_idフィールドとusersテーブルの関連付け:

CREATE TABLE user_details (
`id` INT AUTO_INCREMENT,
`user_id` INT,
`phone` CHAR(32),
`mobile` CHAR(32),
......
PRIMARY KEY(`id`)
) ;

このような問題は、追加するフィールドがかなり多ければ、テーブルが耐えられないほど横方向に増加することです.
Key/Valueモードの使用方法を示す表を見てみましょう.

 
CREATE TABLE IF NOT EXISTS `user_details` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `user_id` INT(11) NOT NULL,
  `key` VARCHAR(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `value` VARCHAR(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;

依然としてuser_を介してidフィールドはusersプライマリテーブルに関連付けられています.keyとvalueフィールドには、keyが「都市」、valueが「北京市」、keyが「誕生日」、valueが「1982-02-10」などのユーザーの資料が保存されます.
利点:このような構造では、データは縦方向に増加するだけで、任意の多くのユーザー資料を保存することができます.このモードでは、最初のデータベース設計が非常に簡単になり、(usersテーブルには3つのフィールドしかありません)後でユーザー属性を追加/削除したい場合も簡単になります.
このパターンの欠点をもう一度考えてみましょう.
欠点1:mysqlのフィールドには天然の利点があります.フィールド内のデータのタイプを制限します.例えばint charなど.Key/Valueモードでは、フィールドタイプが失われます.たとえば、次のsql文です.

UPDATE user_data SET VALUE = '111111111' WHERE KEY = '  '

dateタイプのはずのフィールドに、わけのわからない文字列が保存されています.
ところでphpレベルでは、データのタイプを検出すれば、この欠点を克服することができます.
欠点2:もし私たちがusersテーブルが1枚しかないならば、すべてのフィールドが一緒にいて、所在都市が「北京市」のユーザーを探すには、極めて簡単なsql文でいいです.

SELECT username FROM users WHERE city = '   '

Key/Valueモードでは、JOIN操作を使用します.

 
SELECT username FROM users a
INNER JOIN user_data b
    ON a.id = b.user_id
WHERE b.KEY = '  '
AND b.VALUE = '   '

これも大した問題ではないと思います.
2つ以上のフィールドを同時にフィルタする場合..もうすぐ死ぬ:

SELECT username FROM users WHERE city = '   ' AND DATE= '1980-10-10'

JOINに二度と言わざるを得ない.

SELECT username FROM users a
INNER JOIN user_data b
ON a.id = b.user_id
    INNER JOIN user_data c
    ON b.user_id = c.user_id
WHERE
    (b.KEY = '  ' AND b.VALUE = '   ')
AND
    (c.KEY = '  ' AND c.VALUE = '1980-10-10')

JOINを一度やってみるのは間違いです.

 
SELECT username FROM users a
    INNER JOIN user_data b
    ON a.id = b.user_id
WHERE
    (b.KEY = '  ' AND b.VALUE = '   ')
AND
    (b.KEY = '  ' AND b.VALUE = '1980-10-10')

user_にあるからdata bテーブルには、(b.key="都市"AND b.value="北京市")と(b.key="誕生日"AND b.value="1980-10")を同時に満たす行はないに違いない.
では、最後にまとめてみましょう.Key/Valueのテーブル設計モデルは柔軟ですが、上に複数のフィールドのフィルタリングをしないほうがいいです.