mysql - 优化表以减少索引大小

Question

我有这个保存聊天消息的架构。目前我有大约 100k 行，大约 5.5MB 的数据。索引大小为 6.5MB。当数据大小约为 4MB 时，索引大小约为 3MB，因此它呈指数级增长？

CREATE TABLE `messages` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `author` int(11) unsigned DEFAULT NULL,
  `time` int(10) unsigned DEFAULT NULL,
  `text` text,
  `dest` int(11) unsigned DEFAULT NULL,
  `type` tinyint(4) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `history` (`author`,`dest`,`id`) USING BTREE,
  KEY `messages_ibfk_1` (`dest`),
  FULLTEXT KEY `msg` (`text`),
  CONSTRAINT `au` FOREIGN KEY (`author`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `messages_ibfk_1` FOREIGN KEY (`dest`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=105895 DEFAULT CHARSET=utf8;

我针对此表运行并尝试对其进行优化的主要查询是当我需要显示 2 人之间聊天的分页历史记录时

SELECT id, time, text, dest, type, author 
FROM `messages` 
WHERE (
    (author = ? AND dest = ?) OR (author = ? AND dest = ?)
) AND id <= ? ORDER BY id DESC LIMIT ?, 25

历史记录的其他查询是相同的，只是它们具有针对搜索词或日期范围的附加过滤器。

有什么办法可以减少索引大小并保持最佳性能吗？

Answer 1

不用担心索引的增长。这可能是一个侥幸；当然不是“指数”。

假设主要问题是性能

SELECT id, time, text, dest, type, author 
FROM `messages` 
WHERE (
    (author = ? AND dest = ?) OR (author = ? AND dest = ?)
) AND id <= ? ORDER BY id DESC LIMIT ?, 25

我发现三种技术将有很大帮助:更改 OR至UNION ，处理LIMIT在UNION ，并且不要使用OFFSET用于分页。

        ( SELECT id, time, text, dest, type, author 
            FROM `messages` 
            WHERE author = ?  -- one author & dest
              AND dest = ?
              AND id < ?    -- where you "left off"
            ORDER BY id DESC
            LIMIT 25
        ) UNION ALL
        ( SELECT id, time, text, dest, type, author 
            FROM `messages` 
            WHERE author = ?  -- the other author & dest
              AND dest = ?
              AND id < ?     -- same as above
            ORDER BY id DESC
            LIMIT 25
        )
        ORDER BY id DESC
        LIMIT 25;           -- get the desired 25 from the 50 above

Pagination discussion解释了为什么 OFFSET应该被删除。它讨论了其他技术，包括使用 26(在所有三个地方)而不是 25，以便您知道这是否是“最后”页面。

在第一次迭代中，AND id < ?可以省略。或者(更简单)，您可以替换一个非常大的数字。

您的索引( author 、 dest 、 id )最适合我的公式。

这种复杂的配方将闪耀如 messages变得更大和/或用户在列表中翻页得更远。