10分钟掌握数据库建模
数据库性能问题,主要来自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 |
|易做图 |
|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 &nb