没有怎么搞过数据库,可能下面的描述会有误,请见谅。涉及到的几个表:
1. entry 文章表
id 主键
member 外键,对应member表的id
postdate 文章的发表时间2. member 会员表
id 主键
premiumprofile 外键,对应premiumprofile表的id
points 当前会员的积分3. premiumprofile 高级会员资料表
id 主键
duedate 高级会员到期时间
member 外键,对应member表的id如果一个member,在premiumprofile表里面有对应的记录,而且这条记录的duedate是一个将来的时间的话,说明这个member现在是高级会员。现在我需要从entry表里面查询出来所有的文章,但是需要文章按照一个计算出来的分数来排序,分数的计算方法如下:
发这篇文章的会员本身的积分数 + 高级会员的奖励分数(高级会员会奖励20万分,如果该会员不是高级会员则没有奖励分数) + 计算出来的文章的分数(这个分数是一个负的,文章发表的时间越长,则这个分数越低) 最后,按照这个总体算出来的分数,从高到低到进行排序。
这么排序的目的是为了让一定时间内,积分高的会员发的文章排在积分低的会员的前面,而高级会员发的文章在绝大多数情况下排在普通会员发的文章的前面。
(因为高级会员的文章会有20万分的奖励分数)下面是我写的几个视图:
1. memberpoints 视图:
select `m`.`id` AS `id`,if((`p`.`duedate` > now()),(`m`.`points` + 200000),`m`.`points`) AS `points` from (`member` `m` left join `premiumprofile` `p` on((`m`.`id` = `p`.`member`))) 这个视图是为了得到会员本身的积分数 + 如果是高级会员的话奖励的20万分2. entrieswithpointsoffset 视图:
select id, -100000 * (TIMESTAMPDIFF(DAY,postdate,CURRENT_TIMESTAMP())) as pointsoffset from entry
这个视图是为了算文章本身的积分数,文章发表的时间每过一天,就扣掉10万分。3. entryview 视图:
select e.*,mp.points,ep.pointsoffset, (mp.points + ep.pointsoffset) as orderpoints from entry as e inner join memberpoints as mp on e.member = mp.id inner join entrieswithpointsoffset as ep on e.id = ep.id order by orderpoints desc, points desc, postdate desc 这个视图就是最终的文章的排序结果了。
但是现在这条语句的执行速度很慢。
现在的数据量:
entry 769
member 1308
premiumprofile 94 数据并不多,但是查询一次entryview,大概需要100多毫秒的时间,我感觉这个速度很慢。因为现在数据量很小。
但是我不知道该怎么优化它。 下面是explain语句的执行结果:求各位前辈指导一下应该如何优化这些视图?
谢谢。
1. entry 文章表
id 主键
member 外键,对应member表的id
postdate 文章的发表时间2. member 会员表
id 主键
premiumprofile 外键,对应premiumprofile表的id
points 当前会员的积分3. premiumprofile 高级会员资料表
id 主键
duedate 高级会员到期时间
member 外键,对应member表的id如果一个member,在premiumprofile表里面有对应的记录,而且这条记录的duedate是一个将来的时间的话,说明这个member现在是高级会员。现在我需要从entry表里面查询出来所有的文章,但是需要文章按照一个计算出来的分数来排序,分数的计算方法如下:
发这篇文章的会员本身的积分数 + 高级会员的奖励分数(高级会员会奖励20万分,如果该会员不是高级会员则没有奖励分数) + 计算出来的文章的分数(这个分数是一个负的,文章发表的时间越长,则这个分数越低) 最后,按照这个总体算出来的分数,从高到低到进行排序。
这么排序的目的是为了让一定时间内,积分高的会员发的文章排在积分低的会员的前面,而高级会员发的文章在绝大多数情况下排在普通会员发的文章的前面。
(因为高级会员的文章会有20万分的奖励分数)下面是我写的几个视图:
1. memberpoints 视图:
select `m`.`id` AS `id`,if((`p`.`duedate` > now()),(`m`.`points` + 200000),`m`.`points`) AS `points` from (`member` `m` left join `premiumprofile` `p` on((`m`.`id` = `p`.`member`))) 这个视图是为了得到会员本身的积分数 + 如果是高级会员的话奖励的20万分2. entrieswithpointsoffset 视图:
select id, -100000 * (TIMESTAMPDIFF(DAY,postdate,CURRENT_TIMESTAMP())) as pointsoffset from entry
这个视图是为了算文章本身的积分数,文章发表的时间每过一天,就扣掉10万分。3. entryview 视图:
select e.*,mp.points,ep.pointsoffset, (mp.points + ep.pointsoffset) as orderpoints from entry as e inner join memberpoints as mp on e.member = mp.id inner join entrieswithpointsoffset as ep on e.id = ep.id order by orderpoints desc, points desc, postdate desc 这个视图就是最终的文章的排序结果了。
但是现在这条语句的执行速度很慢。
现在的数据量:
entry 769
member 1308
premiumprofile 94 数据并不多,但是查询一次entryview,大概需要100多毫秒的时间,我感觉这个速度很慢。因为现在数据量很小。
但是我不知道该怎么优化它。 下面是explain语句的执行结果:求各位前辈指导一下应该如何优化这些视图?
谢谢。
explain select ...
show index from ..
以供分析。
不要贴图,不方便分析。
不好意思,我刚才贴的是文本,但是因为貌似出来后格式乱了,所以才改成贴图的。
下面是文本:explain SELECT * from entryview;
1 SIMPLE e ALL PRIMARY,member 771 Using temporary; Using filesort
1 SIMPLE m eq_ref PRIMARY PRIMARY 4 chineselearning.e.member 1
1 SIMPLE entry eq_ref PRIMARY PRIMARY 4 chineselearning.e.id 1
1 SIMPLE p ref member member 4 chineselearning.e.member 11
show index from entry;
entry 0 PRIMARY 1 id A 771 BTREE
entry 1 member 1 member A 257 BTREE
show index from premiumprofile;
premiumprofile 0 PRIMARY 1 id A 94 BTREE
premiumprofile 1 member 1 member A BTREE
show index from member;
member 0 PRIMARY 1 id A 1308 BTREE
谢谢。
memberpoints
entrieswithpointsoffset
select e.*,mp.points,ep.pointsoffset, (mp.points + ep.pointsoffset) as orderpoints from entry as e inner join memberpoints as mp on e.member = mp.id inner join entrieswithpointsoffset as ep on e.id = ep.id order by orderpoints desc, points desc, postdate descorder by orderpoints desc, points desc, postdate desc
postdate来自entry表
points来自memberpoints视图
orderpoints就是前面的 (mp.points + ep.pointsoffset)
你的意思是把所有用view的地方展开吗?
还有, 好多排序哦, 排序很占资源的select
e.id, e.postdate,
if((p.duedate > now()),(m.points + 200000), m.points) AS points,
-100000 * (TIMESTAMPDIFF(DAY,postdate,CURRENT_TIMESTAMP())) as pointsoffsetfrom entry as e
inner join member as m
on m.id = e.member inner join premiumprofile as p
on p.id = m.premiumprofile order by orderpoints desc, points desc, postdate desc
你这个句子造成 order by orderpoints 那里出错了,没有 orderpoints这个字段了。。
麻烦再指点一下, 谢谢
你的意思是把所有用view的地方展开吗?是的, 写一条SQL语句,不使用任何VIEW,这样再做 explain 容易分析问题。
你的意思是把所有用view的地方展开吗?是的, 写一条SQL语句,不使用任何VIEW,这样再做 explain 容易分析问题。你好,展开后是这个样子的:
explain select e.*,(-100000 * (TIMESTAMPDIFF(DAY,e.postdate,CURRENT_TIMESTAMP()))) as epoints,if((`p`.`duedate` > now()),(`m`.`points` + 200000),`m`.`points`) as mpoints from entry as e INNER JOIN member as m on e.member = m.id left join premiumprofile as p on p.member = m.id order by (mpoints + epoints) desc, mpoints desc, e.postdate desc;下面是explain
1 SIMPLE e ALL member 785 Using temporary; Using filesort
1 SIMPLE m eq_ref PRIMARY PRIMARY 4 chineselearning.e.member 1
1 SIMPLE p ref member member 4 chineselearning.e.member 11
麻烦帮忙 看一下,谢谢!
在上述3个字段+postdate建立复合索引
e.id, e.postdate,
(CASE
WHEN (p.duedate > NOW()) THEN m.points + 200000
ELSE m.points END) AS points,
-100000 * (TIMESTAMPDIFF(DAY, postdate,CURRENT_TIMESTAMP())) AS pointsoffset,
(CASE
WHEN (p.duedate > NOW()) THEN
m.points + 200000 + (-100000 * (TIMESTAMPDIFF(DAY, postdate,CURRENT_TIMESTAMP())))
ELSE m.points + (-100000 * (TIMESTAMPDIFF(DAY, postdate,CURRENT_TIMESTAMP()))) END) AS orderpointsFROM entry AS e
INNER JOIN member AS m
ON m.id = e.member
INNER JOIN premiumprofile AS p
ON p.id = m.premiumprofile
ORDER BY orderpoints DESC, points DESC, e.postdate DESC
难道要写一个程序定期去计算更新这些字段的值?
CREATE INDEX IX_entry_member ON entry(member);
CREATE INDEX IX_entrieswithpointsoffset_id ON entrieswithpointsoffset(id);
已经有索引了啊, 而且entrieswithpointsoffset这是一个视图,可以在视图上建索引的吗?
谢谢。