-
EAV表建模探索
Posted on 十二月 11th, 2008 1 commentEAV模型代表Entity-Attribute-Value,最早用于医学用途，医生在就诊时需要记录很多病人的参数，如体温，年龄，过敏药等情况，而这些参数并不是每个病人都需要记录的。
由于商品的多样性，用EAV表来描述商品的各种属性也很合适。老牌电子商务应用oscommerce的表设计(为了简洁，我将商品属性名和属性值的关系表略去)：
-- 商品表CREATE TABLE `products` (`id` int(11) NOT NULL auto_increment,`products_name` varchar(50) default NULL,PRIMARY KEY (`id`));-- 商品属性表
CREATE TABLE `products_attributes` (`id` int(11) NOT NULL auto_increment,`products_id` int(11) NOT NULL default '0',`attribute_name` varchar(50) default NULL,PRIMARY KEY (`id`),KEY `products_id_attribute_name` (`products_id`,`attribute_name`));
-- 属性值
CREATE TABLE `attribute_values` (
`attribute_id` int(11) NOT NULL default '0',
`attribute_value` varchar(100) default NULL,
UNIQUE KEY `attribute_id` (`attribute_id`,`attribute_value`)
);
EAV表模型带来了数据的灵活性，是的增加对象的属性不需要用增加数据库的字段，有很高的灵活性。但是EAV表也有较大的性能问题。通常，EAV表带来的一个问题是当查找多个字段时，需要进行关联查询join,这样的查询效率比较低。为了提高查询效率，我们可以对商品属性表进行矩阵转积处理(pivoting),
"SELECTitems.item_name,ia.attribute_name,av.attribute_valueFROMattribute_values AS avJOIN item_attributes AS ia
ON (ia.id = av.attribute_id)
JOIN items AS items
ON (items.id = ia.item_id);
";
一种方式是在php代码中读出后存入memcache, 当修改attributes表后php触发更新memcache或用cron定期更新;另一种方法是将关联信息组成一张大的临时表，或者view(mysql 5), 利于warehouse的查询，数据的更新可以用数据库的触发器触发更新。由于大量数据在php中进行处理带来了DB的额外IO和服务器性能问题，比较建议用后一种方式更新。
著名的ecommerce软件magento就采用了EAV表作为核心架构，下面看一下通常的表设计：
这里是EAV表的设计:
magento的做法是遵循php|architect的一些建议，通常有如下表等：
CREATE TABLE field_names (fid INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,field_name VARCHAR(50) NOT NULL DEFAULT '',field_type ENUM('VARCHAR', 'INTEGER', 'DOUBLE','DATE', 'TEXT') NOT NULL DEFAULT 'VARCHAR',UNIQUE KEY (field_name));CREATE TABLE varchar_values (
vid INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
value VARCHAR(255) NOT NULL DEFAULT '',
UNIQUE KEY (value)
);
CREATE TABLE integer_values (
vid INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
value INT(11) NOT NULL DEFAULT 0,
UNIQUE KEY (value)
);
CREATE TABLE double_values (
vid INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
value DOUBLE NOT NULL DEFAULT 0,
UNIQUE KEY (value)
);
CREATE TABLE date_values (
vid INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
value DATE NOT NULL DEFAULT '0000-00-00',
UNIQUE KEY (value)
);
CREATE TABLE text_values (
vid INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
value TEXT NOT NULL DEFAULT '',
UNIQUE KEY (value(100))
);
可以定义一些mysql函数，方便数据类型到具体表的转换
CREATE FUNCTION `value_display` (`type` enum('NUMBER', 'ENUM', 'DATE', 'TIME', 'TEXT'), `value` INT, `option` VARCHAR(255), `text` TEXT, `precision` INT, `date_format` VARCHAR(50)) RETURNS VARCHAR(255) CHARACTER SET latin1 NO SQLBEGINCASE typeWHEN 'NUMBER' THEN RETURN `value` / POW(10, `precision`);WHEN 'ENUM' THEN RETURN `option`;WHEN 'DATE' THEN RETURN DATE_FORMAT(FROM_DAYS(`value`), `date_format`);WHEN 'TIME' THEN RETURN FROM_UNIXTIME(`value`, `date_format`);WHEN 'TEXT' THEN RETURN `text`;
ELSE RETURN NULL;
END CASE;
RETURN NULL;
END;
当使用EAV表模型时，InnoDB比MYISAM的性能要好不少。
Leave a reply
