MYSQL性能调优及架构设计-影响MYSQL性能的相关因素之实例分析_MySQL
- WBOY原創
- 2016-06-01 13:45:13884瀏覽
bitsCN.com
需求概述
一个简单的讨论区系统，需要有用户，用户组，组讨论区这三部分基本功能
简要分析
1） 须要存放用户数据的表；
2） 须要存放分组信息和用户与组关系的表；
3） 须要存放讨论信息的表
解决方案
原始方案一：
分别用4个表来存放用户，用户组，用户与组关系，以及各组的讨论帖子的信息。
user用户表
Field
Type
Null
Key
Default
Extra
id
int（11）
NO
nick_name
varchar(32)
NO
NULL
password
char(64)
YES
NULL
varchar(32)
NO
NULL
status
varchar(16)
NO
NULL
sexuality
char(1)
NO
NULL
msn
varchar(32)
YES
NULL
sign
varchar(64)
YES
NULL
brithday
date
YES
NULL
hobby
varchar(64)
YES
NULL
location
varchar(64)
YES
NULL
description
varchar(1024)
YES
NULL
groups分组表
Field
Type
Null
Key
Default
Extra
id
int（11）
NO
gmt_create
datetime
NO
NULL
gmt_modified
datetime
NO
NULL
name
varchar(32)
NO
NULL
status
varchar(16)
NO
NULL
description
varchar(1024)
YES
NULL
user_group关系表
Field
Type
Null
Key
Default
Extra
user_id
int(11)
NO
MUL
NULL
group_id
int(11)
NO
MUL
NULL
user_type
int(11)
NO
NULL
gmt_create
datetime
NO
NULL
gmt_modified
datetime
NO
NULL
status
varchar(16)
NO
NULL
group_message讨论组帖子表
Field
Type
Null
Key
Default
Extra
id
int(11)
NO
NULL
gmt_create
datetime
NO
NULL
gmt_modified
datetime
NO
NULL
group_id
int(11)
NO
NULL
user_id
int(11)
NO
NULL
subject
varchar(128)
NO
NULL
content
text
YES
NULL
优化后方案二如下
user用户表分成user用户表与user_profile表
group_message讨论组表分成group_message讨论组与group_message_content
user用户表
Field
Type
Null
Key
Default
Extra
id
int（11）
NO
nick_name
varchar(32)
NO
NULL
password
char(64)
YES
NULL
varchar(32)
NO
NULL
status
varchar(16)
NO
NULL
user_profile用户属性表
Field
Type
Null
Key
Default
Extra
id
int（11）
NO
sexuality
char(1)
NO
NULL
msn
varchar(32)
YES
NULL
sign
varchar(64)
YES
NULL
brithday
date
YES
NULL
hobby
varchar(64)
YES
NULL
location
varchar(64)
YES
NULL
description
varchar(1024)
YES
NULL
group_message讨论组帖子表
Field
Type
Null
Key
Default
Extra
id
int(11)
NO
NULL
gmt_create
datetime
NO
NULL
gmt_modified
datetime
NO
NULL
group_id
int(11)
NO
NULL
user_id
int(11)
NO
NULL
subject
varchar(128)
NO
NULL
author
varchar(32)
NO
NULL
group_message_content帖子内容表
Field
Type
Null
Key
Default
Extra
group_msg_id
int（11）
NO
content
text
NO
NULL
分析考虑：
1. 从实际出发，一个讨论区系统，访问最多的页面应该是帖子标题列表页面。而帖子标题列表页面最主要的信息都来自于group_message表中，同时帖子标题后面的作者一般都是通过用户名（昵称）来展示。因此：
1） 按照第一种解决方案：
SELECT t.id, t.subject, user.id, u.nick_name
FROM
(
SELECT id, user_id, subject
FROM group_message
WHERE group_id = ?
ORDER BY gmt_modified DESC LIMIT 20
) t, user u
WHERE t.user_id = u.id
2） 按照第二种解决方案：
SELECT t.id, t.subject, t.user_id, t.author
FROM group_message t
HWERE group_id = ?
ORDER BY gmt_modified DESC LIMIT 20
两个查询一比较，打搅就能很明显地看出谁优谁劣了。
2. 由于第一方案中的group_message 表中还包含一个大字段’content’，该字段存放的信息要占整个表的绝大部分存储空间，但在1中表现的最频繁的Query完全不需要该字段所存放的信息，所以，造成了Query读取大量没有任何意义的数据。因此，需要把content字段单独分出来存放在group_message_content帖子内容表中。
作者”邪恶的小Y的博客“
bitsCN.com