我可以在这个表上应用mysql分区吗?
我有这张表
CREATE TABLE IF NOT EXISTS `posts` (
`id` int(10) unsigned NOT NULL auto_increment,
`thread_id` int(10) unsigned NOT NULL,
`forum_id` tinyint(5) unsigned NOT NULL,
`cat_id` tinyint(3) unsigned NOT NULL,
`message` mediumtext collate utf8_unicode_ci NOT NULL,
`userid` int(10) unsigned NOT NULL,
`date` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `thread_id` (`thread_id`),
KEY `userid` (`userid`),
KEY `date` (`date`),
KEY `forum_id` (`forum_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3273548 ;
,如何在其上应用分区以及我可以使用的最佳分区类型是什么?
查询是
SELECT id,user_id,message FROM posts WHERE thread_id = %
带有一些连接
PS:表是 2.5 GiB
- 对 MySQL 分区不太了解:(
谢谢
I have this table
CREATE TABLE IF NOT EXISTS `posts` (
`id` int(10) unsigned NOT NULL auto_increment,
`thread_id` int(10) unsigned NOT NULL,
`forum_id` tinyint(5) unsigned NOT NULL,
`cat_id` tinyint(3) unsigned NOT NULL,
`message` mediumtext collate utf8_unicode_ci NOT NULL,
`userid` int(10) unsigned NOT NULL,
`date` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `thread_id` (`thread_id`),
KEY `userid` (`userid`),
KEY `date` (`date`),
KEY `forum_id` (`forum_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3273548 ;
how can i apply partition on it and what is the best type of partition i can use ?
Query is
SELECT id,user_id,message FROM posts WHERE thread_id = %
with some joins
PS: table is 2.5 GiB
- don't know much about MySQL partition :(
thank you
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
从我的头脑中(不太了解您的应用程序)我会假设您的最高实体是“论坛”,我会为每个“论坛”进行分片。
请记住分片并不容易
首先编辑 1
:2.5GB 的数据大小并不算多,我们使用的表比这大得多。
提示:
会使查询更多一点
慢但不阻塞。
from the top of my head (without knowing much about your application) I would assume that your highest entity its "forum" I would do a sharding per "forum".
Remember sharding isn't easy
Edit 1
First: 2.5gb of data size isn't much, we work with tables much more big than that.
tips:
will make the queries a little more
slow but not blocking.