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

评论

58 楼 xianglei 2009-01-06  

试一下分表查

57 楼 ahua3515 2008-09-09  

def getDataFromDB={
new File((debug==0?remote:local)).withWriterAppend{
wwa->
def all=0,p=0,maxid=1022470056;
while (true){
def runSql=mainSql+" id>"+maxid+selectTime+" order by id limit "+limit;
println runSql
p=0;
thisdb.eachRow(runSql){
  w->
  p++;all++;
  String result=w.username
  if(!usernameMap.containsKey(result) ){
    usernameMap.put(result,0)
  wwa.writeLine(result+",") ;
    if(result.indexOf("@188")!=-1 || result.indexOf("@yeah")!=-1 || result.indexOf("@126")!=-1 || result.endsWith(".popo") || result.endsWith(".vip")  )
           new File((debug==0?"/home/mine/scripts/txt/pxh/通行证.txt":"D:/通行证.txt")).withWriterAppend{t1-> t1.writeLine(result)  }
    else
       new File((debug==0?"/home/mine/scripts/txt/pxh/非通行证.txt":"D:/非通行证.txt")).withWriterAppend{t2-> t2.writeLine(result)}
  }
 
  maxid=w.id
  }
if(p<limit)
break;
}//-end while
}
}

56 楼 ahua3515 2008-09-09  

俺是统计组的

55 楼 ahua3515 2008-09-09  

查询优化还是数据库优化 ？

当你在数据库这一层做了该做的优化，该索引的索引，该干吗的干吗之后

可以在查询方式上做些策略：象这种大数据量的查询，短时间是出不了结果的
数据超过500万以上的，去关连，那恐怖

如果只是要求出结果方案很多的，比如一张5000万的表数据，再相关一另一张表数据

mysql 可以 ordery id limit 10000 再一条条去抓取另一张表的相关记录
分开查比一次50万 500万中取结果集 快很多，而且一直听说mysql自己的游标有问题

可以自己模拟游标形式去操作，5000万条记录，每次抓取1万条 到最后一次抓取小于1万，结束

54 楼 eason007 2008-09-01  

jacklondon 写道

如果没有用到全文索引，那么查询肯定要用 where createTime > ? and  createTime < ? and blog_text like '%?%' order by createTime desc, id desc;
数据库排序，肯定先要按照用户能够看到的字段排序，不然用户就会觉得你根本没有排序！因为从他看到的页面数据里，他看不出来你的排序规则，只有你自己心知肚明，这样很不好。

楼主写的 SQL: FROM blog AS m INNER JOIN user as s ON m.userID = s.ID WHERE m.manager = 2 ORDER BY m.ID DESC
这基本上是进行全表扫描了，既然你的 where 中什么数据过滤条件都没有，加不加索引都是一样慢。难道在 manager 字段上加索引？也不可能。

数据量上讲 blog表 200w，user表 3000w, 平均每 15 个用户对应一个 blog，呵呵，怎么看都有问题。
用户分两种，一种是要写 blog 的，一种是不写 blog 的。
不写 blog 的用户很多，但是这些人是不会去注册成正式用户的。
登记成用户的，一般都会写一两个 blog, 否则登记做什么？这样 user 数量应该大于 blog 数量。

我说了，按ID排序就是按时间排序，用户体验也是感觉按时间排序的。这个没任何问题。是你理解不清楚。

53 楼 eason007 2008-09-01  

jacklondon 写道

正常情况下，blog 表数据应该超过 user 表 n 多。
按照专业术语讲，user 表是 master 表， blog 表是 transaction 表。
Transaction 表指的是数据量虽时间成正比增长的表，这里 blog 肯定是。
至于 user 表，按道理不会有 2000w 的数据量。能否把 user 表的表结构给大家看看？
2000w 什么概念？中国全部网名的数量，也许还没有这么多。全中国的网站，自互联网开始，到现在有 2000w 注册用户的网站也不多。
顺便说一句，如果你的网站有 2000w 注册用户，随随便便都能卖上个好价钱，几辈子都花不完。

正常情况就是user表比blog表大n多。或许你参加的都是些比较专业性的网站，或者是bsp。但我们现在的不是，blog只是众多服务中的一种。有很多用户来我们网站注册并不是为了写blog，而是其他服务。

2000w用户不是很多吧，那些csdn、网易之类都不至这个数的多少倍了。能做得出名点的网站都是一堆无用的注册用户，我们网站也不可避免。

当然我们现在也卖了个相对好一点的价钱，以最近一轮融资来算的话，大概一个用户卖一美刀，最近正在做上市前的最后审计。。。

52 楼 leton2008 2008-09-01  

数据库优化是一门高深的学问啊。

51 楼 b051 2008-08-31  

你要搜title,不用lucene是解决不了问题的. 21s就能出来结果我感觉很惊讶.
如果你非不用, 那可能还是要改改需求. 比如, 不用like, 把blog归纳为既定的几个tag里, 然后对tag搜索?

50 楼 eason007 2008-08-29  

当然，日后也是打算上Lucene的了，只是还没纳入现在的开发计划中，因此只好辛苦一下了。：（

49 楼 eason007 2008-08-29  

恩，综上所述，看来应该稍为改一下程序，改为N+1就好了，不过目前尚未有时间去做这个大动作，所以暂无法回复大家修改后的效果，请见谅。

另外回复一部分朋友的问题：

blog就是博客表，user是用户表。user表有2000w用户很奇怪吗？

由于createTime目前不提供修改的功能，所以涉及时间排序的时候，我都用主键ID，而不是createTime。

另外对文本字段的like查找，使用like 'xx%'是可以使用索引的。但like '%xx%'则不行。

48 楼 liangguanhui 2008-08-29  

1、貌似使用like后，该column的index就不起作用，所以你的title的index建了也是白建。倒不如来个全文索引。like简直是性能杀手。

2、比较倾向分两部查询。即先查blog，再查user。大表的连接查询还是比较耗时的。

47 楼 [e-mail] 2008-08-22  

如果像细致入微的进行分析问题的所在，需要做这样的事情。
　　explain来看看SQL执行的状态。 用show来看看索引的情况。 Handler_read_key 值高表示索引效果好，Handler_read_rnd_next值高表示索引低效。

　　用show processlist 查看当前运行状态。

要细致入微的查找，一般情况慢的原因大部分为i/o的写和读，还有就是分区是否是关键字段，好好的针对情况作处理。不要盲目的优化。可能事倍功半

46 楼 cats_tiger 2008-08-20  

7thbyte 写道

珍惜生命，远离like。

所有使用like的情况都考虑是否应该使用全文检索。

大部分普通需求下，使用现在的众多手段实现基本可用的全文检索，轻松简单。

严重同意。
尤其是楼主的情况，哪有title like 'xx%'的博客呀，凡是需要title like 'xx%'的情况的博客，都用全文检索实现的。不信你问问robin。
BTW：两个表数据量大的时候，即使不用like，join也会很慢，用冗余字段或者N+1提高性能吧。
再次BTW：N+1很多时候还是很快的，比如limit的情况。

45 楼 7thbyte 2008-08-20  

珍惜生命，远离like。

所有使用like的情况都考虑是否应该使用全文检索。

大部分普通需求下，使用现在的众多手段实现基本可用的全文检索，轻松简单。

44 楼 lovejuan1314 2008-08-18  

nihongye 写道

lovejuan1314 写道

第一：如果该做innodb引擎的话，它的效率是没有myisam快

第二：如果索引正确的话，应该检查是否有锁死的事务(前提是innodb)，或者在insert时是否用了delay等

第三：检查mysql服务器配置参数设置

第四：最小时间段内也有9-20w的话就应该考虑用分页

第五： 在数据量大到一定级别的时候（如果每天都有3、4百万的数据），mysql本身的负载就成问题。如果初期考虑到这些的时候，请lz及时更换数据库吧。否则即使做集群，也会带来很大的问题

老兄，你真正在mysql下做过sql优化没有！

XD，你说呢？？有什么不妥的地方还是我没说清楚？

43 楼 nihongye 2008-08-18  

lovejuan1314 写道

第一：如果该做innodb引擎的话，它的效率是没有myisam快

第二：如果索引正确的话，应该检查是否有锁死的事务(前提是innodb)，或者在insert时是否用了delay等

第三：检查mysql服务器配置参数设置

第四：最小时间段内也有9-20w的话就应该考虑用分页

第五： 在数据量大到一定级别的时候（如果每天都有3、4百万的数据），mysql本身的负载就成问题。如果初期考虑到这些的时候，请lz及时更换数据库吧。否则即使做集群，也会带来很大的问题

老兄，你真正在mysql下做过sql优化没有！

42 楼 lovejuan1314 2008-08-18  

第一：如果该做innodb引擎的话，它的效率是没有myisam快

第二：如果索引正确的话，应该检查是否有锁死的事务(前提是innodb)，或者在insert时是否用了delay等

第三：检查mysql服务器配置参数设置

第四：最小时间段内也有9-20w的话就应该考虑用分页

第五： 在数据量大到一定级别的时候（如果每天都有3、4百万的数据），mysql本身的负载就成问题。如果初期考虑到这些的时候，请lz及时更换数据库吧。否则即使做集群，也会带来很大的问题

41 楼 QuakeWang 2008-08-17  

eason007 写道

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

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

这样的需求应该用全文检索来做，用sql来做是很难优化的。

40 楼 xfxlf 2008-08-17  

我看问题关键是，为何要筛选出20万数据？

39 楼 oooooo 2008-08-17  

对20万数据排序非常耗时，建议将聚集索引使用ID倒序