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

评论

38 楼 xfxlf 2008-08-16  

看了半天，感觉lz表达的还是不清楚

1.到底哪些东西需要从user表里面查询出来？
2.这个查询的用途是什么？是查询出blog的内容？还是内容+所有用户信息？还是内容+部分用户信息
3.查询从外部带来了哪些限定参数？比如uid？title？。。。

对于频繁的操作，不建议用join，把必要的数据重复，放到blog表中为好，这是考虑性能。

37 楼 dualface 2008-08-15  

引用

最终的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

分成两步查询，避免 JOIN，最后来组装数据。

第一步：查询 blog 表

SELECT ... FROM blog AS m 
WHERE m.manager = 2 AND m.createTime >= ? AND m.createTime <= ? 
ORDER BY m.ID DESC 
LIMIT 0,20

因为第一步查询的结果只有 20 条记录，所以可以把这 20 条记录的 blog.userID 够造成一个字符串，例如：

245, 533, 6234, 35353 ....

第二步：查询 user 表

SELECT ... FROM user AS s
WHERE s.ID IN (245, 533, 6234, 35353 ....)

这里虽然用了 IN ()，但 IN () 里面是一个常量表达式，所以效率没有问题。


第三步：组装数据

把从 user 查询出来的数据和从 blog 查询出来的数据组装起来。

36 楼 pufan 2008-08-15  

全文检索的需求吧，用like怎么优化都没用。

貌似mysql的中文全文检索支持度不高，不过也可以试试。

单独使用全文检索引擎也是不错的选择，比如lucene。

35 楼 nwangwei 2008-08-14  

把20w的数据全部取出来，时间无论如何都短不了！

如果数据量大，要想快：1.竭尽所能避免表扫描；2.竭尽所能取最少的数据。

34 楼 nihongye 2008-08-14  

yy一个变通的方法。搞几台机器，作为镜像库，将查询都发送给这些库，这些库用内存当硬盘用。

33 楼 nihongye 2008-08-14  

不明白你说：“需求如此，有什么办法绕过去”的意思。
你可能还没明白我的意思:
select * from blog as b where b.createTime < ? and b.createTime > ? and title like 'abc%' limit 0,20。
假如使用的索引是createTime,命中记录很多，而且又需要继续做like，发挥不出limit的作用，效果不好。
title索引命中的记录少，那便。
优先使用title上的索引，select * from blog as b use index (blog_title_idx) where b.createTime < ? and b.createTime > ? and title like 'abc%' limit 0,20。
主要就是让索引命中的记录数量足够小，并且能发挥limit的作用。

32 楼 eason007 2008-08-14  

但问题是需求如此，没有什么更好的办法去绕过。

能不能介绍一些优化配置的方法？

31 楼 nihongye 2008-08-14  

eason007 写道

最后弱弱的问一句，现在这种情况真的只有数据库配置调优的方法？

不是已经告诉你，问题是不要导致大批量的数据读取，比如，前面的语句，假如没有title条件，并且有limit，那肯定执行飞快。
如果有title条件，但是索引是对createtime起作用，那么mysql读取出十万的数据，然后再剔除掉不要的记录，即使有limit，也起不到作用，仍然导致10万的数据读取。
问题在于索引没用到刀刃上。工具就是善用explain。

30 楼 eason007 2008-08-14  

其实顶楼的sql，只是一个基准的sql，查找某种审核状态下的所有blog记录。而在这个基准sql上，可能会有更多的条件。例如我在之前提到，title搜索，或者对user属性的搜索。

这个问题我也很烦恼。因为最小的时间范围内——7天，也有大概9-20w的数据。

29 楼 eason007 2008-08-14  

andyao 写道

user表3000W的数据量,常理来说user表的每条记录不会存太多内容, 都是一些小文本字段,所以虽然有3000W的数据量, 但是占用的空间并不多.
只要你把InnoDB参数配置好, 单表3000W完全没有问题

至于blog表的300W记录, 就更不用分表.

请问能指导一下具体的配置参数和方向吗？

现有配置，2G内存，单CPU双核，freeBSD系统。开发机，web和db共存，支持myisam和innodb引擎。

blog表分区是想测试分区对大数据的效果，因为生产机上有大概2000w的数据，只是开发机上有300w的数据而已。

生产机是一台独立db，4G内存，双CPU双核，freeBSD系统。

28 楼 eason007 2008-08-14  

顶楼的sql是我写的时候没对应别名。实际上就是两个表在联合查询，一会我更正去。

至于select子句，我觉得对于这个问题来说，没必要列出。limit子句我也是省略了，既然会造成大家的阅读困难，那我加上就是。

in是可以用index。但如果是in一个子查询的话，两个字形容，巨慢。比我现在这样join更慢百倍。根本不需要十w的数据就可以测试出来。

至于我为什么用join，是因为担心blog表有不合格的数据，例如userid已不存在于user表中。当然，如果处理这个数据一致性，是另外一个问题。而实际上user表根本就没做删除的功能。

至于分两步查询的话，我个人来说不可接受。

最后弱弱的问一句，现在这种情况真的只有数据库配置调优的方法？

27 楼 andyao 2008-08-14  

nihongye 写道

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

title上的index可以指定长度 一般文本区15-30个字符, 可以有效减少title index的大小, 而且速度上并不会慢多少.

26 楼 andyao 2008-08-14  

user表3000W的数据量,常理来说user表的每条记录不会存太多内容, 都是一些小文本字段,所以虽然有3000W的数据量, 但是占用的空间并不多.
只要你把InnoDB参数配置好, 单表3000W完全没有问题

至于blog表的300W记录, 就更不用分表.

25 楼 andyao 2008-08-14  

引用

andyao同学没有仔细看贴，要打一下pp。
manager字段是在blog表的，join的作用是为了取user表的一部分字段。

我上面已经说了, 看错了.

引用

另外麻烦指出我在顶楼的sql有什么问题，自问数据库功力不差。

引用

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

引用

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

没有select语句, 怎么知道你这个查询要查什么出来?

u.ID u从何而来?

引用

而且我更没有说一定要取20w记录，只是说在需求可接受的最小时间范围内，也存在大概20w的记录，而我需要在这20w以内的记录进行分页。

你的sql分明是要把所有的都查出来,

引用

至于在mysql用in更是一个大笑话。

mysql为什么不能用in? in同样可以使用到index

拿你的例子来说
先取出20条blog

SELECT * from blog WHERE manager = 2 AND createTime >= ? AND m.createTime <= ? ORDER BY m.ID DESC LIMIT 20

通过blog列表很容易获取user id的列表
通过一条in语句将这20条blog需要的user信息全查出来

SELECT * FROM user WHERE ID in (?, ?, ? ....)

取出user以后, 随便你怎么组装数据.

24 楼 eason007 2008-08-14  

bigstudent 写道

除却上诉各位提到的因素，俺以为还可以考虑下表分区对索引等的实际影响到底是什么样子的，俺不熟悉mysql5，但比较熟悉oracle，oracle的分区索引形式多样，实际效果让人大跌眼镜。所以也可以考虑从mysql5的分区入手。200w数据量说大不大说小不小，结合explain实际测试一下为好。

mysql的分区相对简单。连全局索引都没有，所以估计碰上我现在这种针对非分区字段的查询，就比较吃力，需要每个分区都进行单独查询。

23 楼 eason007 2008-08-14  

andyao同学没有仔细看贴，要打一下pp。

manager字段是在blog表的，join的作用是为了取user表的一部分字段。

另外麻烦指出我在顶楼的sql有什么问题，自问数据库功力不差。

而且我更没有说一定要取20w记录，只是说在需求可接受的最小时间范围内，也存在大概20w的记录，而我需要在这20w以内的记录进行分页。

至于在mysql用in更是一个大笑话。

22 楼 bigstudent 2008-08-14  

除却上诉各位提到的因素，俺以为还可以考虑下表分区对索引等的实际影响到底是什么样子的，俺不熟悉mysql5，但比较熟悉oracle，oracle的分区索引形式多样，实际效果让人大跌眼镜。所以也可以考虑从mysql5的分区入手。200w数据量说大不大说小不小，结合explain实际测试一下为好。

21 楼 andyao 2008-08-13  

nihongye 写道

andyao 写道

引用

5、对manager和createTime做联合索引

manager只有3个值, 做index没有效果, 反而会降低性能

目前能想到的最好方法是在blog表冗余一个manager字段,  这样的话就不用join, 直接查询blog表就可以.

问题不是join，而是时间范围的索引虽然有效，但仍然匹配出大量的记录，而这些记录需要继续使用like进行过滤。

不好意思我看错了. 楼主的问题描述中有很多不清楚的地方, sql写的也有问题.

我觉得这种问题要从需求上再考虑.

为什么一定要取出所有的20w条记录?

如果是分页的话, 完全不用join, 先查blog,再查user, 虽然存在1+N查询, 但是比join两个大表性能要好很多.
如果讨厌1 + n 可以使用in语句优化成1 + 1

20 楼 nihongye 2008-08-13  

andyao 写道

引用

5、对manager和createTime做联合索引

manager只有3个值, 做index没有效果, 反而会降低性能

目前能想到的最好方法是在blog表冗余一个manager字段,  这样的话就不用join, 直接查询blog表就可以.

问题不是join，而是时间范围的索引虽然有效，但仍然匹配出大量的记录，而这些记录需要继续使用like进行过滤。

19 楼 andyao 2008-08-13  

引用

5、对manager和createTime做联合索引

manager只有3个值, 做index没有效果, 反而会降低性能

目前能想到的最好方法是在blog表冗余一个manager字段,  这样的话就不用join, 直接查询blog表就可以.