我在 mysql 中有一个表，大约有 7000 亿行用于表示聊天消息。文本存储在单独的表中。
+------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+----------------+
| ID | int(10) unsigned | NO | PRI | NULL | auto_increment |
| Type | tinyint(4) | NO | | 0 | |
| FromUserID | int(10) unsigned | NO | PRI | 0 | |
| ToUserID | int(10) unsigned | YES | MUL | NULL | |
| TextID | int(10) unsigned | YES | MUL | NULL | |
| Ts | datetime | YES | | NULL | |
| IsNew | tinyint(4) | NO | | 0 | |
| Direction | tinyint(4) | NO | | 0 | |
| NeedStar | tinyint(4) | NO | | 0 | |
| NeedSend | tinyint(4) | NO | | 0 | |
| DirectID | int(10) unsigned | YES | MUL | NULL | |
| IdeaID | int(11) | YES | | NULL | |
| FilePos | int(11) | NO | | 0 | |
+------------+------------------+------+-----+---------+----------------+
到目前为止，它已被分区BY HASH(FromUserID) PARTITIONS 16;并像这样编制索引：
--------------------+--------------+-------------+-----------+-------------+
Key_name | Seq_in_index | Column_name | Collation | Cardinality |
--------------------+--------------+-------------+-----------+-------------+
PRIMARY | 1 | ID | A | 644937873 |
PRIMARY | 2 | FromUserID | A | 644937873 |
DoulikeMessage_k_2 | 1 | FromUserID | A | 5971646 |
DoulikeMessage_k_2 | 2 | ToUserID | A | 644937873 |
DoulikeMessage_k_3 | 1 | DirectID | A | 28 |
ToUserID | 1 | ToUserID | A | 37937521 |
TextID | 1 | TextID | A | 644937873 |
--------------------+--------------+-------------+-----------+-------------+
我正在考虑创建一个具有不同分区和索引的类似表，然后将记录传输到那里。对表最常见的查询是
SELECT DoulikeMessages.* FROM DoulikeMessages WHERE 1 AND (DoulikeMessages.FROMUSERID = '2048254') AND DoulikeMessages.TOUSERID >= '1' AND DoulikeMessages.TYPE <= '1' ORDER BY Ts DESC;
有时最多需要 20 秒来处理。那是因为 Ts(日期时间(上没有索引。我考虑做类似PARTITION BY RANGE( FromUserID ) PARTITIONS 50的事情.并按 Ts 索引。或者也许我应该按日期进行子分区？我可以在查询中添加类似"TS 现在>((- 1 个月"之类的内容，因为大多数时候只需要最近的消息。旧的只是稍后在代码中丢弃。
我还应该提到有人尝试手动对表进行分区(根据 fromuserid 创建 50 个新表并更改 api 以使用它们(，但这对我来说似乎是一个坏主意。我不明白人们如何从这种方法中受益。
分区不是使查询更快的工具。这就是索引的用途。
目前对数据的访问效率低下。虽然你在FromUserID上有一个索引，但要获得实际数据(你想要*(，MySQL必须在表中查找它。但是在那里，数据(随机(按自动增量 id 排序。
因此，MySQL首先读取索引的一行，通知表中存储完整数据的位置，跳转到表中的该位置，读取那里的数据，然后重复索引中的下一行。
此外，表中的数据以块形式存储，通常为 16kb。因此，要从表中读取一行，其中有41字节，MySQL实际上必须读取16kb。也许来自磁盘。这 41 个有用的字节还将使用 16kb 的缓冲池(MySQL 缓存(，并且可能会迫使您比需要的更快地从磁盘而不是内存中重新读取数据，因为旧数据会更快地被替换。或者换句话说：要将整个表缓存在内存中，您需要 28GB 的 ram(假设您在写入 7000 亿行时意味着 7 亿行(。如果当前只有 1% 的用户处于活动状态，在最坏的情况下，您仍然可能需要 28GB 的 RAM，因为超过 99% 的缓存数据可能无法使用，因为您只需要 41 个字节中的 16kb。有一些重叠(例如，大多数块将包含您需要的几行(，但您明白了。如果块将包含同一用户的大部分数据，则可能只需要 0,28gb 来缓存 1% 活跃用户的数据，其余部分用于缓存其他数据(或足以将其全部保存在内存中(。
只是要强调：从磁盘读取是一项非常非常昂贵的操作。只需为随机用户(尚未在内存中(运行查询两次。第一次从磁盘读取它时。第二次从内存中读取它。根据您的MySQL版本，您可能需要添加SQL_NO_CACHE以获得准确的结果，例如，获得缓冲池的实际效果，而不仅仅是查询缓存的效果。
为了优化您的访问，当您跳转到FromUserID时，您应该手头有所需的所有数据。您可以使用覆盖索引，这意味着您可以创建一个包含所需所有列的索引。如果您需要所有列都覆盖*，这可能意味着索引(FromUserID, Ts, ToUserID, Type, ..., FilePos)。它将占用与您当前表数据一样多的磁盘空间，但它将允许MySQL从该索引中读取一行，意识到它拥有所有数据，然后从索引中读取下一行。无需查阅表格以获取完整数据。这要快得多。
再加上MySQL一次性读取16kb的效果，所以如果所需的数据没有缓存并且必须从磁盘读取，那么您已经将接下来的16kb/41byte= 400行读取到内存中，因此最多减少了399次磁盘读取。这是否是您看到的效果将取决于您当前拥有多少缓存内存。但是对于一个非常简单的查询，20 秒表示内存中没有整个表。
您也可以考虑将主键更改为以FromUserID开头的内容，也许是FromUserID, Ts, ToUserID。虽然它为覆盖索引节省了磁盘空间，但这样做的决定更像是一个合乎逻辑的考虑因素，例如，如果这个(或列的其他一些组合(实际上在逻辑上是一个主键(例如唯一而不是空(，这取决于你的数据模型。您不应该仅仅为了节省磁盘空间而这样做。如果这样做，这将像覆盖索引一样，允许您一次性读取用户的所有数据，而无需查阅其他数据源。
根据这些查询返回的消息数量、磁盘速度(SSD 或 HDD(、RAM/缓冲池量和服务器负载，执行时间应以毫秒而不是秒为单位。