优化表以减少索引大小

问题描述：

我有这个模式可以保存聊天消息。目前我有大约10万行，大约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

之间的主查询对历史记录的其他查询是相同的，除了它们具有用于搜索词或日期范围的附加过滤器。

有什么可以做的，以减少索引大小和保持最佳性能？

为什么你认为索引大小与性能有关？您的查询运行缓慢吗？毕竟，如果你没有索引，那么你会节省很多空间，但是你的查询会慢很多，所以显然有一个索引是一个空间性能折衷，通过索引，你我们表达了以牺牲太空为代价的表现。 –

如果MySQL为了将来插入而在btree中留下一些未填充的空间，那么您的索引可能会大于表本身。 –

顺便说一下，您可以通过存储“user1”和“user2”而不是“author”和“dest”，按字母顺序排列这两个用户，并使“user1”成为第一个用户，从而减少索引大小并提高查询性能。第二个是“user2”。所以如果你想找到马克和爱丽丝之间的对话，爱丽丝将永远是“用户1”，马克将永远是“用户2”。然后，您可以添加另一列来指示“user1”是作者还是收件人。 –

答

不要担心指数的增长。这可能是一种侥幸;当然不是“指数级”。

假设的主要问题是

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变得更大和/或用户页面进入列表更远，这种复杂的公式将会发光。

无论表格大小或页码如何，此表达式最多可触及50行（加上写入50到tmp和重新读取50）。原始配方总是触及大部分或全部行。 –

谢谢你的回答，这是最有帮助的 - 特别是关于避免使用偏移量进行分页的部分，我过去遇到过这个问题。然而，问题是这个指数的大小是否可以接受，以及如何减少它，因为指数大于我的数据的IMH（noob）O看起来不合时宜 - 这就是为什么我不会将答案标记为已接受的原因。 –

还有一些我认为值得指出 - 运行原始查询从1毫秒没有偏移返回的问题，但获得相同的结果与50毫秒的联合返回。我记得我在设计桌子时也测试了这一点，过去也是如此 - 'OR'多次超过'UNION'。 –

优化表以减少索引大小

相关推荐