10分钟掌握数据库建模-白红宇

快速掌握数据库建模

文章出处：

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 |

+------------+

快速检索子分类设计

上面我刚刚讲过怎样实现“不限子树的分类树”，我们可以实现不限层次的无线分类表。

+-----------+

| category |

|-----------|

|id | <---+

|title | |

|description| 1:n

|status | |

|parent_id | o---+

+-----------+

问题出来了，当我需要读取一个分类（任意分类）下的所有子分类，怎样实现，很多人会说用“递归”。当然“递归”可是现实我们的需求，在几百个分类的项目中，使用递归也不是不可以的，但是当数量非常庞大时怎么办？

当然有更好的解决方案，请看下面

+-----------+

| category |

|-----------|

|id | <---+

|title | |

|description| 1:n

|status | |

|parent_id | o---+

|path |

+-----------+

+-------------------------------------------------------------------------+

| category |

+----+-----------+-----------------------+--------+-----------+-----------+

+----+-----------+-----------------------+--------+-----------+-----------+

| 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 |

+----+-----------+-----------------------+--------+-----------+-----------+

CREATE TABLE `category` (

`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='分类表'

ENGINE=InnoDB

ROW_FORMAT=DEFAULT

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%';

+----+-----------+-------------+--------+-----------+-----------+

+----+-----------+-------------+--------+-----------+-----------+

| 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

| +-----------------+ +------------------+

| |-----------------| |------------------|

| |id |<--1:n--+ |id |

+--o|category_id | +--o|news_id |

+--o|user_id | +-->|user_id |

| +-----------------+ | +------------------+

| |

1:n +----------+ +---1:n---+

| | user | |

| |----------| |

+-->|id | <---+

|user |

|passwd |

|nickname |

|status |

+----------+

4.1. 分区表设计

分区表可以通过表空间，等等技术实现，优点是解决了Union查询问题，保证了数据的一致性。

+-----------+

| category |

|-----------|

+-->|id | <---+

| |title | |

| |description| 1:n

| |status | |

| |parent_id | o---+

| +-----------+

1:n

| +-----------------+ +-----------------+

| |-----------------| |-----------------|

| |id |<--1:n--+ |id |

+--o|category_id | +--o|news_id |

+--o|user_id | +-->|user_id |

| +-----------------+ | +-----------------+

| | 2007,2008,2009 | | | 2007,2008,2009 |

| +-----------------+ | +-----------------+

| |

1:n +----------+ +---1:n---+

| | user | |

| |----------| |

+-->|id | <---+

|user |

|passwd |

|nickname |

|status |

+----------+

评论表

+----------+

| user |

|----------|

|id | <---+

|user | |

|passwd | |

|nickname | |

|status | |

+----------+ |

1:n

+-----------+ | +-----------+

|-----------| | |-----------|

|id | | +-->|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. 简单实现

+------------+ +--------------------------+ +-----------------------+

+------------+ +--------------------------+ +-----------------------+

|id | <--1:1--o |product_id | +---> |id |

|quantity | |product_attribute_value_id| o---+ +-----------------------+

|... | +--------------------------+ | +-----------------------+

|category_id | 1:n |product_attribute_value|

+------------+ | +-----------------------+

+---> |id |

|name |

+-----------------------+

7.2. 实现属性组管理

product attribute group

+------------+ +--------------------------+ +--------------------------+ +-----------------------+

+------------+ +--------------------------+ +--------------------------+ +-----------------------+

|id | +---> |id | <--1:n--o |product_attribute_group_id| +---> |id |

|status | | +--------------------------+ +--------------------------+ | +-----------------------+

|default_pag | o---+ | +-----------------------+

+------------+ +---> |id |

|name |

+-----------------------+

7.3. 可编辑属表

product attribute group

+------------+ +------------------+ +--------------------------+ +---------------------------------+

+------------+ +------------------+ +--------------------------+ +---------------------------------+

+->|id | +--> |id | <--1:n--o |attribute_group_id | +-+-> |id |

| |parent_id | | | +---------------------------------+

| |default_pag | o---+ |

| +------------+ |

1:n |

| +-------------+ +--------------------------+ |

| +-------------+ +--------------------------+ |

| |id | +-> |product_id | |

| |... | | +--------------------------+

+-o|category_id | |

|attr_group_id| <--1:n--o

+-------------+

+--------------------------------------------------+

| product_attribute_key |

+--------------------------------------------------+

| 2 | sex | bool | Female,Male |

| 3 | qty | input| '' |

+--------------------------------------------------+

国际化语言表

+-----------+ +---------------+

| category | .---+ | category_lang |

|-----------| / | +---------------+

+-->|id | <---+ +--o |category_id |

| |description| 1:n |name | | +-------------+

| |parent_id | o---+ \ +-------------+

| +-----------+ >--> |id |

1:n / |lang |

| +------------+ ' |status |

| | product | | +-------------+

| +------------+ +--------------+ |

| |id | <---+ | product_lang | |

| |price | | +--------------+ |

| |... | |language_id | o-----+

+-o |category_id | |name |

+------------+ +--------------+

Workflow

+------------+ +---------------+ +-----------+

+------------+ +---------------+ +-----------+

|id |o-+ |id | +->|id |<-+

+------------+ +---------------+ +-----------+ |

+----------------+ +------------+ |

| workflow | | job | |

+----------------+ +------------+ |

+->|id | +->|id | |

+-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

ROW_FORMAT=DEFAULT

AUTO_INCREMENT=1

本版控制表，用于记录每次变动

CREATE TABLE `article_history` (

`id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,

`article_id` MEDIUMINT(8) UNSIGNED NOT NULL,

`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 (`id`),

INDEX `article_id` (`article_id`)

)

ENGINE=MyISAM

ROW_FORMAT=DEFAULT

AUTO_INCREMENT=1

版本控制触发器

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 表

user_id

sn 订单编号

...

created 创建时间

order_item表

order_id

product_id

....

当点击我的订单时查询 order表，当点击定点细节时读order_item表。以此类推，不多举例。