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

一个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服务。求优化方法。。。

评论

18 楼 nihongye 2008-08-13  

那就是了，20万数据做like，开销很大。
你可以考虑跟title做联合索引,时间跟文本的联合索引很占空间 ; 或是在title做独立索引，并指定使用title上的索引.总之保证尽可能的少读取数据。
explain的结果rows列列出了需要验证的记录数，每一行的记录数相乘是总的记录数。

17 楼 eason007 2008-08-13  

nihongye ：explain的结果也是使用manager上的联合索引。毕竟title没有做索引。

weishuwei ：
原来就是单独索引的，但速度更慢。

内联结会使结果数据相对准确一些。

16 楼 weishuwei 2008-08-13  

(1)不建议做联合索引,建议分别做索引
(2)不知道你为啥要用内链接,从你的业务需求上貌似没这种必要,可以考虑用外连接.
(3)程序的事务处理是否有合理

15 楼 nihongye 2008-08-13  

你对title,like啊。explain 出来什么？

14 楼 eason007 2008-08-13  

from blog, user where blog.userID=user.id and blog.manager=2  and blog.createTime <=1218613810 and blog.createTime >=1218009010 and blog.title like '随%'
order by blog.createtime desc  limit 0,20

耗时21多s

13 楼 eason007 2008-08-13  

现在又出现这个问题了。

12 楼 eason007 2008-08-13  

Innodb_buffer_pool_reads          | 27193
Innodb_buffer_pool_read_requests  | 1270213

11 楼 nihongye 2008-08-13  

没有orderby加上limit，按理来说，只会出现很少的磁盘读了。不清楚为什么出现你说的现象。
另外用mysqladmin extended-status检查
Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests的比例是否小于1/100.

10 楼 eason007 2008-08-13  

回nihongye ：是用manager和createtime的索引没错。去掉order by的话，加上limit也是卡死

回Lucas Lee ：有加limit的。顶楼的我只是说即使加入时间条件，也会在大概20w以内的数据进行排序。


但今天回来再测试发现查询不会卡死进程，只是稍有延迟。不知道是不是因为昨天在测试前进行大量的数据导入所致。

9 楼 LucasLee 2008-08-13  

我觉得这个问题的关键暂时不在如何优化SQL本身--而在于逻辑：
为什么需要查出这20W条记录？
查出来再做什么处理？
是做统计汇总，还是直接显示给用户？
如果是后者，则需要加入分页逻辑。

8 楼 nihongye 2008-08-13  

先明确一下，explain后，key是否用了manager跟createtime的联合索引？
如果是，去掉orderby，加上limit 0,10，如果这样很快，就说明可能是读取数据多时过多的磁盘io，
查看数据库的状态，有两个变量说明innodbbuffer的命中情况的。

7 楼 eason007 2008-08-13  

优化filesort只能调mysql配置吗?

试过去掉order by ，结果一样的。

6 楼 nihongye 2008-08-12  

试了一下，createTime是一个range查询，跟id做联合索引后，没办法消除掉filesort。
manger跟id做联合索引，并优先使用它们的联合索引则可以消除filesort，但这样没用到createTime索引，开销可能更大。
大批量的数据加载的开销很大，你先看下去掉orderby是否能达到要求，然后再针对fielsort优化。

5 楼 eason007 2008-08-12  

楼上的意思是加联合索引？

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

楼上的意思是把主键id也加入到manager的联合索引中？

4 楼 nihongye 2008-08-12  

先尝试优化掉filesort，见
http://nihongye.iteye.com/admin/blogs/193442，
和
http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html。

3 楼 eason007 2008-08-12  

nihongye 写道

引用

即使如此，涉及的数据量也在20w以内。

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

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

执行explain的话，大概出现的内容：
blog，使用manager上的索引，最后一列有filesort
user，使用主键索引。

2 楼 nihongye 2008-08-12  

引用

即使如此，涉及的数据量也在20w以内。

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

1 楼 eason007 2008-08-12  

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

而hash分区好象没办法再做复合分区。