MySQL key/value 表的两种设计

Friendfeed的MySQL key/value存储

这是一篇2009年初的资料How FriendFeed uses MySQL to store schema-less data,相信大部分人已经看过了。如Fenng的中文介绍FriendFeed 使用 MySQL 的经验。本文从不同的角度再补充下。作者几个月前也曾经在广州技术沙龙作过一次Key value store漫谈的演讲，许多参会人员对key value方向存在强烈的使用意愿，但同时也对完全抛弃MySQL存在疑虑，本文介绍的方案也可以给这些人员一些架构参考。
需求250M entities, entities表共有2.5亿条记录，当然是分库的。
典型解决方案:RDBMS问题：由于业务需要不定期更改表结构，但是在2.5亿记录的表上增删字段、修改索引需要锁表，最长需要1小时到1天以上。
Key value方案评估Document类型数据库，如CouchDB
CouchDB问题： 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表中，它的结构为
mysql> 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. 程序停止写索引表(实际就是一个普通表)，2. 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个字段)
后面再想添加/删除用户属性，也变得很简单。

下面再考虑一下这种模式的缺点。

缺点一：
mysql的字段有一个天然的优势：它限制了字段中数据的类型。比如int char等。
Key/Value模式下，字段类型丢失。
比如下面这条sql语句：

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

本来应该是date类型的字段，却保存了一个莫名其妙的字符串。

话说在php层面，检测一下数据的类型，就可以克服这个缺点了。

缺点二：
假如我们只有一张users表，所有字段都在一起，
要查找所在城市为’北京市’的用户，只要极其简单的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 = '北京市'

或许你觉得这也不算什么大问题。

如果要同时在二个或多个字段上过滤。。。就要死人了：

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-10′)。

好了，最后再总结一下：
Key/Value的表设计模式虽然灵活，但是最好不要在上面做多个字段的过滤。