MYSQL 评论表结构优化

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

› MySQL 5.5 Community Server

› MySQL 5.6 Community Server

› Percona Configuration Wizard

› XtraBackup 搭建主从复制

Great Sites on MySQL

› Percona

› MySQL Performance Blog

› Severalnines

推荐管理工具

› Sequel Pro

› phpMyAdmin

推荐书目

› MySQL Cookbook

MySQL 相关项目

› MariaDB

› Drizzle

参考文档

› http://mysql-python.sourceforge.net/MySQLdb.html

这是一个创建于 2395 天前的主题，其中的信息可能已经有所发展或是发生改变。

数据表结构

CREATE TABLE comments (

id bigint(20) unsigned NOT NULL AUTO_INCREMENT,

aid bigint(20) unsigned NOT NULL DEFAULT '0',

uid bigint(20) unsigned NOT NULL DEFAULT '0',

contents text NOT NULL,

likes int(10) unsigned NOT NULL DEFAULT '0',

comments int(10) unsigned NOT NULL DEFAULT '0',

time int(10) unsigned NOT NULL DEFAULT '0',

PRIMARY KEY (id),

KEY aid (aid),

KEY time (time)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

现在有 7 千万数据，表大小 3.60 GB 索引大小 2.55 GB ； AID 是新闻的 ID ； UID 是评论用户； contents 是评论内容；随着数据越来越多表越来越大，如果分表，业务逻辑很麻烦，又达不到需求，有什么好的优化方案？

我现在想把 contents 内容单独一个表存放，就是说这个评论表增加一个表；

CREATE TABLE comments_data (

id bigint(20) unsigned NOT NULL DEFAULT '0',

contents text NOT NULL,

PRIMARY KEY (id)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

这样虽然能优化一些，但终究不是最终解决方案，不知道 V2 朋友们有没做过类似的数据结构优化；

44 条回复 • 2018-05-19 17:42:16 +08:00

breadenglish

2018-05-02 17:17:12 +08:00

mongoDB 了解一下

aliipay

2018-05-02 17:19:44 +08:00

业务逻辑很麻烦---具体有哪些？

monsterxx03

2018-05-02 17:23:00 +08:00

你现在瓶颈在哪, 查询速度太慢还是怎么的, 看看 MySQL 自己的 partition 功能满不满足你需求, 那样不用动业务代码.

老能看到动不动给人推荐 MongoDB, ElasticSearch 的, 连人家问题都不了解一下，也是醉了

aliipay

2018-05-02 17:23:30 +08:00

简单猜测一下，有根据 aid 查询和根据时间范围查询，
可以设计一个 key，由 time+id 组成，如 2018050200000123，用来代替现有的 aid 和 time。然后根据新的 key 进行分表，应该是满足上面 2 个查询需求。

Aluhao

2018-05-02 17:28:37 +08:00

@aliipay aid 是新闻 ID，就是每打开新闻，都得用这个 ID 去查询用户的评论，还要翻页等，所以如果按日期存放，数量查出来估计是全部，要做成分页估计麻烦些；

breadenglish

2018-05-02 17:28:45 +08:00

@monsterxx03 兄弟你理解能力没问题吧，人家的问题很明确了，你要不要再仔细看看。

xiaoban

2018-05-02 17:28:59 +08:00

如果单用数据库可以按照新闻 id 分表，这样可能会造成数据分布不均匀。

新闻评论的业务类型，如果插入数据后大多不会修改只是查询，可以使用数据库做持久数据，使用 es 这种专门做查询的搜索服务,插入数据库后就可以往 es 抛，同一个机房抛送基本等于实时。
由于查询不会走到数据库，插入慢的时候，老数据可以做封存，也可以建立新表保存新的数据。

如有不合理还请大佬指出

enhancer

2018-05-02 17:29:16 +08:00

不分表的话，不论如何优化都只是暂时的。一个评论表，一般不会在业务上跟其他表有多少 JOIN 关系，上层逻辑按照分表规则 ID 来读取就好，建议分表。

Aluhao

2018-05-02 17:29:58 +08:00

@monsterxx03 因为数量量增加很快，如果走出一个亿查询就会慢了，索引虽然只建了 id 和 aid 二个，但占用空间很大;

cout2013mr

2018-05-02 17:30:29 +08:00

comments 表考虑一下用时间分区？

Aluhao

2018-05-02 17:32:34 +08:00

@enhancer 我们还有一个业务逻辑就是，一个页面要显示一些新闻列表，列表里面又要包含一些新闻的评论，如果分表，这个页面估计就很难实现；
如
1、新闻 1111
新闻 1 评论 1
新闻 1 评论 2
新闻 1 评论 3
....
2、新闻 2222
新闻 2 评论 1
新闻 2 评论 2
新闻 2 评论 3
....
下面还有

aliipay

2018-05-02 17:35:41 +08:00

@Aluhao 你再仔细看看，分页也容易实现

micean

2018-05-02 17:35:53 +08:00

评论表冗余新闻表的时间用来 partition ？

glacer

2018-05-02 18:08:51 +08:00

优先考虑分区表。分区表在 MySQL 的底层存储同样也是多表，不同分区的数据和索引都是独立的 idb 文件，和分表区别不大，还不需要修改业务代码。
若用评论时间来作为 key 进行分区，楼主的部分业务逻辑虽然不需要改代码，但涉及到跨分区的查询依然无法提高性能，还可能比未分区前性能更低。
我建议对 aid 来进行 hash 分区，即对 aid 的值求 hash 后对分区数 n 取模，这样不会造成明显的数据倾斜现象。MySQL 有现成的 hash 分区。
缺点就是分区数固定，不好扩展，但要是能预估好表的大小，分区数可以设置多一些，问题也不是很大。

monsterxx03

2018-05-02 18:16:55 +08:00

慢的查询 pattern 是什么, 才一个亿, IOPS 没饱和的话，index 设计合理，这个量级不会慢的, 就算你分了表，或者把 content, comment 拆出去, 如果这两个索引是确实需要的话, index size 还是那么大.

建议和楼上一样, 用 MySQL 自己的 partition table, 对 aid 做 hash 分区, 时间分区如果你逻辑总是加在最新的文章的话，会造成热点, 意义不大.

MasterC

2018-05-02 18:37:24 +08:00

@glacer #14 根据新闻 aid 进行 hash 分区取模存储，表面上看是不会造成数据倾斜，但是实际上每个新闻的评论数都不同，热点新闻和一般新闻的评论数天差地别，所以最终依然会有数据倾斜的问题

Aluhao

2018-05-02 18:38:10 +08:00

@monsterxx03
@glacer
感谢建议！

monsterxx03

2018-05-02 18:52:24 +08:00

还有 db engine 为什么是 MyISAM? 写入会锁表啊

akira

2018-05-02 19:40:46 +08:00

优化都是针对性的，需要有明确的瓶颈点别人才好给优化方案和意见。不然只能给出来的就是泛泛而谈了

enenaaa

2018-05-02 19:50:00 +08:00

只是嫌查询慢的话，何不简单除暴地加缓存，或者建个小表，存放热门内容。

jetyang

2018-05-02 20:14:15 +08:00

分表，按 aid 分表
likes、comments 看上去是收藏数和评论数，如果经常更新建议拆出去

Leigg

2018-05-02 20:32:06 +08:00 via Android

兄 dei，首先你这个肯定要做缓存的，至少把最近一周或一个月的新闻的评论放到缓存服务器上，至于分表的话我建议做水平分表，垂直分没什么作用，毕竟查的是评论这种占空间的文本内容。最后我想说你的这种结构用 mongo 很好做，mongo 可以把一条新闻下的所有用户的评论存为一条记录，算起来这个记录数怕不是要比 mysql 少多少倍。。

sheldoner

2018-05-02 20:48:45 +08:00

@Leigg 请教一下 “ mongo 可以把一条新闻下的所有用户的评论存为一条记录” 所有评论怎么存为一条记录

Aluhao

2018-05-02 20:49:30 +08:00

@Leigg 是呀，求方法

qiayue

2018-05-02 21:19:23 +08:00

@sheldoner @Aluhao mongo 存的是 bjson 文档，可以简单理解为 json 格式数据，把所有评论放到一个 json 里不就是一条记录么

xudaiqing

2018-05-02 21:21:39 +08:00

MySQL 不是很熟，但我记得 MyISAM 各方面都不如 Innodb 而且已经几乎停止开发了。
3.60 GB 数据量很小，一般的索引就够了。如果性能不行，内存太小或者索引和查询的设计有问题的的可能性比较大。
索引大小 2.55 GB 感觉太大了，我觉得索引可能有问题。
“索引虽然只建了 id 和 aid 二个” 对 id 建索引意义何在？

Leigg

2018-05-02 21:46:46 +08:00

@sheldoner 酱紫的，类 json 格式存储一条新闻下的所有评论，
{新闻 id：xxx, 评论数：xxx, 评论信息：[ {
评论 id:xxx,
评论文本:xxx，
评论用户:xxx,
回复楼层:xxx,
赞数:xxx,
},{},{} ]
}
评论信息的 value 按评论楼层排序，这是 mongo 中大概的字段设计模型，具体怎么设计要看业务需求。但肯定比 mysql 快，索引也省不少空间。楼主可以当做参考哈~

iyaozhen

2018-05-02 21:55:03 +08:00 via Android

几亿以下的数据都可以考虑表分区，按照 aid 或者时间分区，where 条件带上分区字段，索引正确很快。主要是业务代码不需要改动
然后数据库建议升级到 5.7 或者 8.0，使用 innodb

hwiiago

2018-05-02 22:08:51 +08:00

水平分表、mongo、缓存、冷热分离、#7 楼 es 方案，视具体业务场景选择。

kran

2018-05-02 22:51:16 +08:00 via iPhone

单文章评论不会急剧增长，应该考虑按照评论数分表，比如一千万。文章表存储评论表 id，新建文章时确定评论表 id，如果最大 id 评论表存储的数目大于一千万，就增一并新建相应评论表。完全不需要引入其他依赖。

blueskit

2018-05-02 23:05:13 +08:00 via Android

数据量不大应该并不迫切分表什么的。
建议从查询日志分析具体的慢查询类别、然后对症下药。
不管怎么说，加缓存可以先上

msg7086

2018-05-02 23:19:26 +08:00

@xudaiqing 主键索引。

xudaiqing

2018-05-03 06:34:21 +08:00 via Android

@msg7086 主键是自动强制索引的,单独拿出来说就怕是 id 的常规索引

very9527

2018-05-03 09:07:18 +08:00

content 的字段是不是太大了？

enhancer

2018-05-03 09:50:14 +08:00

@Aluhao 那就应该考虑根据评论表中的 [新闻 ID] 来分表，确保某条新闻，根据其新闻 ID 加载的评论都全部来自某张评论分表。

sheldoner

2018-05-03 11:59:08 +08:00

@qiayue 谢谢，我们用的是 redis，没太关注 mongo 的文档
@Leigg 像这样的话，系统启动，评论信息就初始化到 mongo 里面了。评论的东西感觉也有很大了，这样初始化，mongo 性能够吗，内存占用也会很高吧

Leigg

2018-05-03 13:26:36 +08:00

@sheldoner 可以通过配置“缓存大小”来优化，数据量到一定级别后且高并发查询和写入的话要做副本集或分片，像题主这种情况不至于高并发写入，做副本集应该足以应对。但是做分片可以避免单台实例过于臃肿的情况。

MeteorCat

2018-05-03 13:31:53 +08:00

@sheldoner mongo 不是什么灵丹妙药，大量数据加载进 mongo 之后内存很快吃光了，如果并发量并不是那么高[比如有的内容仅仅三五个人看一遍没必要上 mongo]，本地文件读取都行；还有另外一种优化方法，就是加载渲染内容的时候不一起加载评论[这也是一个普遍做法]，评论另外走 js 接口拼接到内容下面，判断用户是否拉到最下面准备读取评论