时间戳

首页 > 解决方案 > 如何在mysql中对一个非常大的消息表进行分区

mysql - 如何在mysql中对一个非常大的消息表进行分区

问题描述

我有一个包含来自用户的消息的表。该设计将为发送者和接收者保留一份消息副本（每个人都有自己的消息已读/已删除标志）。

CREATE TABLE cloob_msg.cl_inbox (
  id int(11) NOT NULL AUTO_INCREMENT,
  `user` int(11) NOT NULL,
  contact int(11) NOT NULL,
  sdate timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  body text NOT NULL,
  userstatus tinyint(4) NOT NULL DEFAULT 1 COMMENT '0: new, 1:read, 2: deleted',
  contactstatus tinyint(4) NOT NULL DEFAULT 0,
  class tinyint(4) NOT NULL DEFAULT 0,
  attachtype tinyint(4) NOT NULL DEFAULT 0,
  attachsrc varchar(255) DEFAULT NULL,
  PRIMARY KEY (id),
  INDEX i_class (class),
  INDEX i_contact_user (contact, user),
  INDEX i_contactstatus (contactstatus),
  INDEX i_user_contact (user, contact),
  INDEX i_userstatus (userstatus)
)

为了列出两个人之间的对话，我们使用以下查询：

select * from cl_inbox
    where (user=user1 and contact=user2 and userstatus<>2)
       or (user=user2 and contact=user1 and contactstatus<>2)
    order by id limit ?,?

现在，我如何对表进行分区（我们有数十亿条消息，所以它真的需要分区）？我应该选择哪些唯一键和哪些分区字段？

谢谢。

标签： mysqlpartitioning

解决方案

为什么要分区？它本身不会提供任何性能。

要加快该查询，请更改or为union：

( select * from cl_inbox
    where (user=user1 and contact=user2 and userstatus<>2)
    order by id limit ?,?
) UNION ALL
( select * from cl_inbox
    where (user=user2 and contact=user1 and contactstatus<>2)
    order by id limit ?,?
)

现在每个部分都可以独立使用i_contact_useror i_user_contact。（您的版本必须进行全表扫描。）这将运行得更快。顺便说一句，对于这个查询，这两个索引同样好。除非您同时需要其他查询，否则我建议删除其中一个。（和其他“标志”）上的索引*status可能没用，对于这个查询肯定没用。

下一个问题：OFFSET用于分页是有问题的。当切换到 时UNION，它现在坏了。

所以，相反，“记住你离开的地方”。既然你说order by id，我假设 id 是 UI 所需的顺序？删除OFFSET并使用id：

( select * from cl_inbox
    where (user=user1 and contact=user2 and userstatus<>2)
      AND id < $left_off
    ORDER BY id DESC
    LIMIT ?
) UNION ALL
( select * from cl_inbox
    where (user=user2 and contact=user1 and contactstatus<>2)
      AND id < $left_off
    ORDER BY id DESC
    LIMIT ?
)
ORDER BY id DESC
LIMIT ?

（是的，我故意重复ORDER BYand LIMIT。）我冒昧地扭转了局面——你不想要最新消息吗？

更多讨论：http: //mysql.rjweb.org/doc.php/pagination

如果您希望拥有一个巨大的表，并希望删除“旧”记录，那么我们可以讨论分区以方便旧行。但这是我看到的对该表进行分区的唯一用途。

推荐阅读