PHP和MySQL统计系统

Question

什么是最佳的数据库模型来存储用户访问并使用IP计算唯一用户，在一个比如100万行的大型数据库中？

SELECT COUNT(DISTINCT ip) FROM visits

但是有1,000,000个不同的IP地址可能导致查询变慢。缓存不会返回真正的数字。

大型统计系统如何计算独立访问量？

- Wiliam

Answer 1

有另一个只有IP列和唯一索引的MyISAM表。你会在很短的时间内得到正确的计数（MyISAM缓存表中行数）。

[评论后添加]

如果您还需要统计每个IP的访问次数，请添加一个名为visitCount的列并使用它。

INSERT INTO 
  visitCounter (IP,visitCount) 
VALUES 
  (INET_ATON($ip),1) 
ON DUPLICATE KEY UPDATE 
  SET visitCount = visitCount+1

- Mchl

---

@Mchl 如果IP列是唯一的，那么该表每个IP将始终返回COUNT = 1吗？ - Frankie

可以这样做，但我理解威廉想要计算所有不同IP的数量。这仍然可以通过添加一个“count”字段并使用“INSERT ... ON DUPLICATE KEY UPDATE ...”语法来修改它。 - Mchl

对于独特的访问，这是一个不错的解决方案。保存唯一的IP和实际时间戳。 - Wiliam

@Mchl：将IP地址存储为整数比存储为字符串更好吗？更快吗？ - Wiliam

当然。它存储的是一个4字节整数，而不是长达15个字节的整数。此外，它不需要字符集和排序规则例程来进行搜索和检索。 - ircmaxell

没错。只是不要使用 WHERE INET_NTOA(ip) = '127.0.0.1'，而是使用 WHERE ip = INET_ATON('127.0.0.1')。区别在于：第二个使用索引。 - Mchl

Answer 2

不要使用关系型数据库来存储那种类型的信息，因为它并不是为此而设计的。

你可以尝试使用 NoSQL 数据库，如 Mongo（我知道很多地方使用它来记录日志，因为它几乎没有什么开销）。

如果你必须坚持使用 MySQL，你可以对 ip 列添加索引，这应该会显著提高速度...

- ircmaxell

---

这是我建议的。此外，考虑计算独立用户的概念。只需计算一次，然后重复使用它。昨天的独立访问者数量不会改变。上周的独立访问者数量也不会改变。 - dwich

基于此，您可以每天/每周/每月或其他方式进行分片，并为每个新时期创建一个新表。这样，您仍然保留信息（如果您确实需要它），并且获得处理相对较小的表的性能提升。但是我必须问一下，为什么您需要保留那么多数据？为什么不每天总结一次，然后在一个月或两个月后删除？ - ircmaxell

我知道如何使用索引... 我只是要求针对高流量数据库的DB模型。我需要保存所有数据，因为我的框架需要所有客户的所有信息存储在不同服务器上以进行统计和其他事情。谢谢。（哪个更快，INDEX IP还是另一种解决方案，创建一个带有唯一IP的表？） - Wiliam

这完全取决于服务器。别忘了，假设正在实时写入，这个表将会是极度写入密集型的。因此，“on duplicate key update”可能会对性能产生影响，因为它需要读取索引，然后定位到插入位置，而插入只需要定位（听起来像是多了一点点，但对于每秒数千次的查询来说，这是很重要的）。此外，它使得写入可以连续进行，而不需要在各个位置上进行查找。底线：测试它。创建一个测试数据库和一个用于写入的脚本，然后看看…… - ircmaxell