求数据库设计优化方案

eonezhang 发布于 2013/08/08 20:00
阅读 619
收藏 0
问题,
有三张表
userinfo(id,username) 存放用户信息
usersubs(userid,subs) 存放用户的选课信息(一个用户可以选择多门课程)
userarea(userid,area) 存放用户的地区信息(一个用户可以选择多个地区)
其中usersubs和userarea的userid列是参考userinfo#id,可以理解为外键。


查询
1. 查询选了某些课的用户
2. 查询某个地区选修了某些课的用户
目前我对如上两个查询使用的语句
1. select u.* from user_info u where u.id in(select s.userid from user_subjects where subs='a')


2.select u.* from userinfo u where u.id in(select s.userid from user_subjects s,user_area a where s.userid=a.userid and s.subject='a' and a.area='b')


问题:
以上sql在大数据量高并发情况下性能表现拙劣,寻求优化方案或更优的数据库设计方案。


建库脚本如下:
CREATE TABLE `user_info`(id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,`name` VARCHAR(32) NOT NULL);
CREATE TABLE user_subjects(userid INT UNSIGNED NOT NULL,`subject` CHAR(4) NOT NULL,PRIMARY KEY(userid,`subject`));
CREATE TABLE user_area(userid INT UNSIGNED NOT NULL,`area` CHAR(7) NOT NULL,PRIMARY KEY(userid,`area`));
加载中
0
mark35
mark35
最简单的办法:换掉mysql这货
0
gtd
gtd

1. select u.* from user_info u left join user_subjects s on u.id=s.userid where s.subject in('bbbb', 'aaaa', 'cccc') group by u.id;


2.   select u.* from user_info u left join user_subjects s on u.id=s.userid
       left join user_area a on u.id=a.userid

 where s.subject in('bbbb', 'aaaa', 'cccc') and a.area in ('a1', 'a2') group by u.id;

不知道行不行



0
宏哥
宏哥

引用来自“mark35”的答案

最简单的办法:换掉mysql这货
每次看到这些俺就想笑
0
ipenglei
ipenglei

select 后面请不要跟 * ...

只跟具体需要用到的字段名...

0
gKiwi
gKiwi

1.给where后面的查询条件subject,area建立相应索引,这个非常重要!

2.按道理一个学校能有多少人呢?总觉得数据不会太多。。如果是多个学校,多个地区的话,可以考虑按照地区,学期,地区等维度来进行分表(可以考虑其中一个或多个)。比如按照学期,这样子已经过去的学期可以单独存储在沉淀表里面,因为基本上大家只会查询当前时期~

3.其他就是些sql的优化,google一下基本的几个就应该够用了。

如上,欢迎指正。

gKiwi
gKiwi
@mark35 subId,areaId~这个设计时候都会有常量字段映射表的。。
mark35
mark35
subject,area顾名思义是字符类型,B-tree索引效率不高的
0
xmut
xmut

最简单的方法:升级到MySQL 5.6,这个版本提升了 子查询效率~

当然,MySQL内存也从50M左右,攀升到4OOM左右~

0
恺哥
恺哥

就你目前提出的需求,这样的表结构也许相对更合理一些;查询起来也比较容易;

另外,人员的姓名、课程名称、区域名称更改频度不高,为了去掉关联查询,可以在关联表中冗余记录名称字段,提高查询效率

在user_areas_subs表中,根据sql建立组合索引,会提高查询效率;

待今后数据量大的时候,可以考虑在相应的字段上建立分区或分表策略

eonezhang
eonezhang
回复 @恺哥 : 业务是这样的,一个人可以选择要去哪些地方上课,同时,他还要选择自己要上哪些课。 然后我要查询的内容:1.查看某些课程(可能同时查两样课程)有哪些老师在上(比如我要查询上英语和语文的老师有哪些)。2.按地区查找上课的老师(比如我要查询在成都和重庆上课的老师有哪些)3.综合1和2的查询,既,要查询在四川和重庆有哪些老师在上英语和语文课。
恺哥
恺哥
回复 @eonezhang : 这主要看你的业务,地区和课程之间没有业务关系吗?一个用户可以在某个区下而没有课程?
eonezhang
eonezhang
这样设计好像有个问题,一个人可以选择多门课程,也会选择多个地区进行授课。 如果一个用户选择了两门课程,三个地区的话,存储起来有些问题吧。
mark35
mark35
头上3张表都冗余进user_areas_subs有点夸张
0
王瑞平
王瑞平
课程和地区表里冗余用户信息
小苏打
小苏打
回复 @王瑞平 : 可是课程和地区表里面只用到了用户表的主键作为外键啊
王瑞平
王瑞平
回复 @小苏打 : 你这样理解就反了
小苏打
小苏打
用户信息并不冗余,冗余的是subject 和 area.
0
梅开源
梅开源
索引做没
eonezhang
eonezhang
做了索引也不能提高多大效率,我用explain我的sql语句一看就得优化。
返回顶部
顶部