关于大数据量数据的处理,数据量可能很大很大

[mifan]

我写了个站, 去天涯抓数据, 昨天 100多万的数据, 系统响应不错, 今天数据量 到达了 200多W, 系统反应极其慢....
我查了一下, 主要是这种sql非常慢 (posts 表 数据量 2468234 )

SELECT `posts`.* FROM `posts` WHERE (`posts`.topic_id = 11036) ORDER BY id ASC LIMIT 20 OFFSET 1082340;


20 rows in set (34.13 sec)

`posts`.topic_id 上有索引,

更要命的是, 要是想抓的话, 每天我都能加 100w 的数据, 这样的话 即使 把现在的innodb 换成 myisam引擎估计也没啥效果, 同时又不想分表(用的是rails, 就是图个省事儿),

求一解决方案, 或者其他办法

我想先换成 mongodb ? 不知可行否 ?(没玩过大数据量数据库, 还请同学们帮忙 :) )

对了 机器是在linode 上的vps , 1G 内存....

[mifan]

补充一个有这种问题的url

http://tianya.lu/posts/pages/123406

也许会很快, 那是因为有缓存缓存这个页面的时间是10 min, 但是10分钟过期后,,, 用户体验为零. 点了就没反应, 半分钟之后才能动......

[mifan]

上面的url 给错了, 应该是这样的url:
http://tianya.lu/topics/10000/pages/23492 才会更慢....

[mifan]

create_table "posts", :force => true do |t|
t.integer "topic_id", :null => false
t.integer "author_id", :null => false
t.integer "page_id", :null => false
t.datetime "posted_at", :null => false
t.datetime "updated_at"
t.integer "post_favorites_count", :limit => 2, :default => 0, :null => false
t.boolean "valuable", :default => false, :null => false
t.integer "post_content_id"
end

add_index "posts", ["author_id"], :name => "index_posts_on_author_id"
add_index "posts", ["post_favorites_count"], :name => "index_posts_on_post_favorites_count"
add_index "posts", ["posted_at"], :name => "index_posts_on_posted_at"
add_index "posts", ["topic_id", "author_id"], :name => "index_posts_on_topic_id_and_author_id"
add_index "posts", ["topic_id"], :name => "index_posts_on_topic_id"
add_index "posts", ["updated_at"], :name => "index_posts_on_updated_at"
add_index "posts", ["valuable"], :name => "index_posts_on_valuable"


posts 表 的结构

[Platinum]

建一个 topic_id,id 的复合索引

主要是你的 OFFSET 太大了，可以考虑 cursor 式分页 http://timyang.net/web/pagination/

你这种是标准的 SQL 操作，不是 key-value 性质的，跟 mongodb 没关系，先不用惦记

[leolmncn]

简单的方法是采用mysql 5.1以上版本支持的partitioning。可以根据日期或ID范围进行物理分表。但是逻辑表对于rails还是一个。插入时更新索引的速度和个别记录的查询性能都会提升。

mysql 官方文档：
http://dev.mysql.com/tech-resources/articles/mysql_5.1_partitioning_with_dates.html

[mifan]

@Platinum 的这个符合索引应该是最简单的, 所有查询排序都能在这个索引中完成, 加了这个索引应该可以提高不少速度
cursor 的 方式 也可以考虑, 但是需要加额外的字段(索引) , 等直接加完索引在看吧


@leolmncn 的这个方式很不错, 等 cursor 改造完, 试试 逻辑分表....


谢谢 :)

[napoleonu]

妈妈说select语句不让写*

一天100W，换机器吧。

[napoleonu]

随意分页也可以做到，好像在 @fuchaoqun 博客上看过。

[Livid]

你计算过 posts 表每一行的平均长度么？

如果其中有一个字段是 post 的内容，那么建议把这个长字段删掉，然后把 post 的内容放到一个 key-value 数据库中。

这样的话，整个 posts 表的尺寸会降低一个数量级。

[Los]

VPS的32G硬盘很快会满吧？

[mifan]

@Livid 有一个 post_contents 表 ,

posts 表 有 "post_content_id" 作为关联 , 呵呵 , 只在这个表上有 select * from post_contents where id = xxx 的操作, 这个到成为不了瓶颈.

我的上一次优化就是 把 posts 表 拆成了 posts 和 post_contents 2个表 , 不然 50 W 条数据 就已经跑不动了 :)

[mifan]

Update ...

根据@Platinum的建议,加了3个index... , 现在 最长的查询大概3s, 性能10x...,
想办法用cursor中...

def self.up
change_table :posts do |t|
add_index "posts", ["id", "topic_id"]
add_index "posts", ["id", "author_id"]
add_index "posts", ["id", "topic_id", "author_id"]
end
end

[mifan]

@Los 估计抓 10000 w 数据就差不多了,,, 现在上面还放着好多我下的 片片... :)

[Platinum]

http://hi.baidu.com/thinkinginlamp/blog/item/a352918fe70d96fd503d925e.html

这属于进阶问题了，文章最下面的那个 INNER JOIN 可谓大杀器，有些场合很明显，可以继续试试

还有就是 @Livid 的拆字段问题，我认为对于 Covering Index 的查询，表的大小无所谓……因为这时候主要是看索引文件的大小

[Platinum]

另外，复合索引的顺序很重要，我怎么看你那几个索引都是 id 在最前……

[Platinum]

说乱了，那个不是 Covering Index ……应该说是不需要扫描全表（只匹配索引就够了）的查询

[Livid]

SELECT * 类型的查询的性能，和表尺寸是有关系的，会影响查询完成后读取时的 IO 数量。

[manhere]

不想分表 可以分库呀

[napoleonu]

数据量不小，1G内存，还要跑APP，怎么折腾就那样，用户稍微多那么几个就直接当机吧。