快速掌握資料庫模組化
文章出處：
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=)