快速掌握数据库建模
文章出处：
http://netkiller.sourceforge.net
http://netkiller.github.com
数据库性能问题，主要来自3三个部分。
1. 数据库配置
2. sql查询语句的性能
3. 建模的合理性
客户端性能这里不谈，硬件的影响这里也不讨论，这里只谈数据库服务器本身的问题。
首先是数据库的配置，这个比较好解决，有经验的dba都能搞定，无非是连接数，cpu与内存优化等等。
其次是sql 查询性能问题，比较要命，不可能每个开发人员都能写出高性能的查询语句，查询与索引息息相关，优化索引是一种手段，通过sql review 来解决索引失效的sql。数据也可能打印出查询性能差的sql，也是可控的。
最后是建模，很多企业忽略这块。数据库建模非常重要，数据结构一旦确认，后面变更都会影响整个项目的进行。所以对建模人员要求相当的高。目前招聘到一个合格的建模人员真的很难。
目前国内企业数据建模都是由开发人员完成。随心所欲建表，加字段，我就发现过一个表有200多个字段的情况，还有字段中存储以逗号分隔的数据。这样的情况非常普遍。
另外有些企业让dba负责建模，由于dba不参与开发，不了解宏观需求，根据开发人员的描述建表，也有很多不合理之处。
如何建立高性能，可伸缩的数据库呢？
经过多年总结与摸索，我找到一个小技巧，请阅读下面文章，当你阅读完后，你就会感觉大彻大悟。
如果你能掌握这个技巧，在未来开发或数据管理方面会得心应手。
如何设计user表
用户帐号表
用户帐号或通行证系统设计，下面以我的数库为例讲解。
我一般使用两个表 passport，profile 完成网站会员系统。
首先说说passport表，你也要以使用user或member等等命名，这个表设计尽可能地简单，不要使用过多字段。仅保存登录所必须用到的字段，如user,password,nickname,email... 登录帐号和密码做复合索引。
然后是profile表，这个表与passport是1:1关系，保存用户详细信息
这样设计可以保证海量用户登录时的速度。
+----------+
| user |
|----------|
|id | <---+
|user | |
|passwd | |
|nickname | |
|status | |
+----------+ |
1:1
| profile | |
|----------| |
|user_id | o---+
|name |
|sex |
|passwd |
|nickname |
|status |
如何设计分类表？
树形分类表
+-----------+
| category |
|-----------|
|id | <---+
|title | |
|description| 1:n
|status | |
|parent_id | o---+
create table `category` (
`id` smallint(10) unsigned not null auto_increment,
`name` varchar(10) not null,
`description` varchar(255) null,
`status` enum('enable','desable') not null default 'enable',
`parent_id` smallint(10) unsigned not null default '0',
primary key (`id`),
constraint `fk1` foreign key (`parent_id`) references `category` (`id`)
)
comment='goods category'
engine=innodb
row_format=default
多对多分类
多对多分类,主要用于满足，一个产品/文章属于多个分类的需求。
+------------+
| category |
|------------|
+--> |id | <---+
| |title | | +----------------------+
1:n |description | 1:n | categroy_has_product |
| |status | | +----------------------+
+--o |parent_id | | | id |
+------------+ +---o | category_id |
+---o | product_id |
+------------+ | +----------------------+
| product | 1:n
+------------+ |
|id | <---+
|price |
|quantity |
|... |
|status |
快速检索子分类设计
上面我刚刚讲过怎样实现“不限子树的分类树”，我们可以实现不限层次的无线分类表。
问题出来了，当我需要读取一个分类（任意分类）下的所有子分类，怎样实现，很多人会说用“递归”。 当然“递归”可是现实我们的需求，在几百个分类的项目中，使用递归也不是不可以的，但是当数量非常庞大时怎么办？
当然有更好的解决方案，请看下面
|path |
+-------------------------------------------------------------------------+
| category |
+----+-----------+-----------------------+--------+-----------+-----------+
| id | name | description | status | parent_id | path |
| 1 | 中国 | 中华人民共和家 | y | null | 1/ |
| 4 | 广东省 | 广东省 | y | 1 | 1/4 |
| 5 | 深圳市 | null | y | 4 | 1/4/5 |
| 6 | 宝安区 | null | y | 5 | 1/4/5/6 |
| 7 | 龙华镇 | null | y | 6 | 1/4/5/6/7 |
`id` int(10) unsigned not null auto_increment comment '分类id',
`name` varchar(50) not null comment '分类名称',
`description` varchar(200) null default null comment '分类描述',
`status` enum('y','n') not null default 'y' comment '分类状态有继承性',
`parent_id` int(10) null default '1' comment '分类父id',
`path` varchar(255) not null comment '分类递归路径索引',
index `pk` (`id`),
index `relation` (`id`, `parent_id`),
index `fk_category_category` (`parent_id`),
index `path` (`path`)
comment='分类表'
auto_increment=0
insert into category(`name`,`description`,`status`,`parent_id`,`path`) values('中国','中华人民共和家','y',null,'1/')
alter table `category`
add constraint `fk_category_category` foreign key (`parent_id`) references `category` (`id`)
抽取广东子树
select * from category where path like '1/4%';
mysql> select * from category where path like '1/4%';
+----+-----------+-------------+--------+-----------+-----------+
| id | name | description | status | parent_id | path |
| 4 | 广东省 | 广东省 | y | 1 | 1/4 |
| 5 | 深圳市 | null | y | 4 | 1/4/5 |
| 6 | 宝安区 | null | y | 5 | 1/4/5/6 |
| 7 | 龙华镇 | null | y | 6 | 1/4/5/6/7 |
4 rows in set (0.00 sec)
文章表设计
看具体情况，拆分表，可按“日”，“月”，“年”等等
+-----------+
| category |
|-----------|
+-->|id | <---+
| |title | |
| |description| 1:n
| |status | |
| |parent_id | o---+
| +-----------+
|
1:n
| +-----------------+ +------------------+
| | article_2008_01 | | feedback_2008_01 |
| |-----------------| |------------------|
| |id |<--1:n--+ |id |
| |title | | |title |
| |content | | |content |
| |datetime | | |datetime |
| |status | | |status |
+--o|category_id | +--o|news_id |
+--o|user_id | +-->|user_id |
| +-----------------+ | +------------------+
| |
1:n +----------+ +---1:n---+
| | user | |
| |----------| |
+-->|id | <---+
|user |
|passwd |
|nickname |
|status |
+----------+
4.1. 分区表设计
分区表可以通过表空间，等等技术实现，优点是解决了union查询问题，保证了数据的一致性。
| +-----------------+ +-----------------+
| | article | | feedback |
| |-----------------| |-----------------|
| |id |<--1:n--+ |id |
| |title | | |title |
| |content | | |content |
| |datetime | | |datetime |
| |status | | |status |
+--o|category_id | +--o|news_id |
+--o|user_id | +-->|user_id |
| +-----------------+ | +-----------------+
| | 2007,2008,2009 | | | 2007,2008,2009 |
评论表
1:n
+-----------+ | +-----------+
| feedback | | | news |
|-----------| | |-----------|
|id | | +-->|id |
|title | | | |title |
|content | | | |content |
|datetime | | 1:n |datetime |
|status | | | |status |
|user_id |o---+ | |user_id |
|news_id |o------+ +-----------+
记录点击率，阅读次数，及评分表
+--------------+ +--------------+
| article | | article_rank |
|--------------| |--------------|
|id | <---1:1---o |article_id |
|title | |click |
|content | |read |
|datetime | |score |
|status | |... |
|category_id | |... |
|user_id | |... |
产品属性表
7.1. 简单实现
+------------+ +--------------------------+ +-----------------------+
| product | | product_attribute | |product_attribute_key |
|id | <--1:1--o |product_id | +---> |id |
|price | |product_attribute_key_id | o---+ |name |
|quantity | |product_attribute_value_id| o---+ +-----------------------+
|... | +--------------------------+ | +-----------------------+
|category_id | 1:n |product_attribute_value|
+------------+ | +-----------------------+
+---> |id |
|name |
+-----------------------+
7.2. 实现属性组管理
product attribute group
+------------+ +--------------------------+ +--------------------------+ +-----------------------+
| category | | product_attribute_group | | product_attribute | |product_attribute_key |
|id | +---> |id | <--1:n--o |product_attribute_group_id| +---> |id |
|title | | |name | |product_attribute_key_id | o---+ |name |
|description | 1:1 |status | |product_attribute_value_id| o---+ +-----------------------+
|status | | +--------------------------+ +--------------------------+ | +-----------------------+
|parent_id | | 1:n |product_attribute_value|
|default_pag | o---+ | +-----------------------+
+------------+ +---> |id |
|name |
+-----------------------+
7.3. 可编辑属表
+------------+ +------------------+ +--------------------------+ +---------------------------------+
| category | | attribute_group | | group_has_attribute | |attribute_key |
+->|id | +--> |id | <--1:n--o |attribute_group_id | +-+-> |id |
| |title | | |name | |attribute_key_id | o---+ | |name |
| |description | 1:1 |status | | | | |type enum('bool','list','input') |
| |status | | +------------------+ +--------------------------+ | |default array() |
| |parent_id | | | +---------------------------------+
| |default_pag | o---+ |
| +------------+ |
1:n |
| +-------------+ +--------------------------+ |
| | product | | product_attribute | |
| |id | +-> |product_id | |
| |price | | |attribute_key_id | o---------------1:n---------------+
| |quantity | | |attribute_value |
| |... | | +--------------------------+
+-o|category_id | |
|attr_group_id| <--1:n--o
+-------------+
+--------------------------------------------------+
| product_attribute_key |
| 1 | color | list | red,green,blue |
| 2 | sex | bool | female,male |
| 3 | qty | input| '' |
国际化语言表
+-----------+ +---------------+
| category | .---+ | category_lang |
|-----------| / | +---------------+
+-->|id | <---+ +--o |category_id |
| |title | | |language_id | o---+
| |description| 1:n |name | | +-------------+
| |status | | +---------------+ . | language |
| |parent_id | o---+ \ +-------------+
| +-----------+ >--> |id |
1:n / |lang |
| +------------+ ' |status |
| | product | | +-------------+
| +------------+ +--------------+ |
| |id | <---+ | product_lang | |
| |price | | +--------------+ |
| |quantity | +---o |product_id | |
| |... | |language_id | o-----+
+-o |category_id | |name |
+------------+ +--------------+
workflow
+------------+ +---------------+ +-----------+
| user | | role_has_user | | role |
|id |o-+ |id | +->|id |<-+
|node_id | +->|user_id | | |name | |
|up_id | |role_id |o-+ |description| |
+------------+ +---------------+ +-----------+ |
|
+----------------+ +------------+ |
| workflow | | job | |
+->|id | +->|id | |
| |job_id |o-+ |name | |
+-o|up_id | |role_id |o------------------+
| | |description |
+----------------+ +------------+
内容版本控制
主表
create table `article` (
`article_id` mediumint(8) unsigned not null auto_increment,
`cat_id` smallint(5) not null default '0',
`title` varchar(150) not null default '',
`content` longtext not null,
`author` varchar(30) not null default '',
`keywords` varchar(255) not null default '',
primary key (`article_id`),
index `cat_id` (`cat_id`)
engine=myisam
auto_increment=1
本版控制表，用于记录每次变动
create table `article_history` (
`id` mediumint(8) unsigned not null auto_increment,
`article_id` mediumint(8) unsigned not null,
index `article_id` (`article_id`)
版本控制触发器
drop trigger article_history;
delimiter //
create trigger article_history before update on article for each row
begin
insert into article_history select * from article where article_id = old.article_id;
end; //
delimiter;
我的建表规则很多是基于form建模(但不是全部都按form走，这样说让各位好理解)，例如用户登录表单：
user
password
submit
当做用户认证的时候只需要读user即可。此时profile空闲
当点击编辑个人信息的时候才读取profile表。
用户user表只有 user,passwd等几个字段，性能远比一个大user表好。
像mysql这样的数据库，有些操作会锁表，将user 分为两个1：1的表可以避开一部分锁表影响
现在我们来设计个order(订单系统表)，会用到上面的product与user表。
order 表
id
user_id
sn 订单编号
...
created 创建时间
order_item表
order_id
product_id
....
当点击我的订单时查询 order表，当点击定点细节时读order_item表。以此类推，不多举例。
延伸阅读
http://netkiller.github.com
![【MySQL数据库】数据库索引事务1.索引2.事务[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)