论坛首页 综合技术论坛

mysql5下大数据量查询优化的问题

浏览 26214 次
精华帖 (0) :: 良好帖 (0) :: 新手帖 (0) :: 隐藏帖 (0)
作者 正文
   发表时间:2008-08-12  

一个blog表,目前数据大概90多w,短期内可预见的数据量会是200w以内。

一个user表,目前数据大概2000w,短期内可预见数据量在3000w以内。

 

其中blog表有userid做外键,与user表的主键id关联。同时有manager字段,代表审核状态,分别为0、1、2。预计0的记录非常少,1的记录相对较少,2的比较非常多。createTime 字段为数字型,保存unix时间戳。

 

而实际查询中的SQL为

FROM blog AS m INNER JOIN user as s ON m.userID = s.ID WHERE m.manager = 2 ORDER BY m.ID DESC

 

优化的手段包括:

1、使用innodb引擎

2、对blog表做hash分区,分区字段为主键ID

3、对user表做hash分区,分区字段为主键ID

4、查询时加入时间条件限制范围

5、对manager和createTime做联合索引

 

最终的SQL为:

FROM blog AS m INNER JOIN user as s ON m.userID = s.ID WHERE m.manager = 2 AND m.createTime >= ? AND m.createTime <= ? ORDER BY m.ID DESC LIMIT 0,20

 

即使如此,涉及的数据量也在20w以内。因此这条SQL会卡死mysql服务。求优化方法。。。

   发表时间:2008-08-12  
本来想blog表是用createTime做rang分区的,但这样的话blog表就不能有主键和外键。而且没有主键的话,使代码相对复杂一点去生成主键。当然,用uid也可以,不过不想用。

而hash分区好象没办法再做复合分区。
0 请登录后投票
   发表时间:2008-08-12  
引用
即使如此,涉及的数据量也在20w以内。

什么意思?
执行explain,出来什么?
0 请登录后投票
   发表时间:2008-08-12  
nihongye 写道
引用
即使如此,涉及的数据量也在20w以内。

什么意思?
执行explain,出来什么?


我的意思是说即使最后加上时间条件,只查7天内的数据,也有20w以内的记录。

执行explain的话,大概出现的内容:
blog,使用manager上的索引,最后一列有filesort
user,使用主键索引。
0 请登录后投票
   发表时间:2008-08-12  
先尝试优化掉filesort,见
http://nihongye.iteye.com/admin/blogs/193442,

http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html。
0 请登录后投票
   发表时间:2008-08-12  
楼上的意思是加联合索引?

blog表的外键userID有单独索引,关联字段的ID是主键,也有独立索引。而条件中的manager和createTime有联合索引。

楼上的意思是把主键id也加入到manager的联合索引中?
0 请登录后投票
   发表时间:2008-08-12  
试了一下,createTime是一个range查询,跟id做联合索引后,没办法消除掉filesort。
manger跟id做联合索引,并优先使用它们的联合索引则可以消除filesort,但这样没用到createTime索引,开销可能更大。
大批量的数据加载的开销很大,你先看下去掉orderby是否能达到要求,然后再针对fielsort优化。
0 请登录后投票
   发表时间:2008-08-13  
优化filesort只能调mysql配置吗?

试过去掉order by ,结果一样的。
0 请登录后投票
   发表时间:2008-08-13  
先明确一下,explain后,key是否用了manager跟createtime的联合索引?
如果是,去掉orderby,加上limit 0,10,如果这样很快,就说明可能是读取数据多时过多的磁盘io,
查看数据库的状态,有两个变量说明innodbbuffer的命中情况的。
0 请登录后投票
   发表时间:2008-08-13  
我觉得这个问题的关键暂时不在如何优化SQL本身--而在于逻辑:
为什么需要查出这20W条记录?
查出来再做什么处理?
是做统计汇总,还是直接显示给用户?
如果是后者,则需要加入分页逻辑。
0 请登录后投票
论坛首页 综合技术版

跳转论坛:
Global site tag (gtag.js) - Google Analytics