最快的方式来存储数据表和检索 [英] Fastest way to store table of data and retrieve

问题描述

我存储为用户将来检索我网站上的一些历史信息。因此，当他们访问某些网页会记录下他们参观了页面，时间，然后将其存储在其用户ID为将来扩展/检索。

I am storing some history information on my website for future retrieval of the users. So when they visit certain pages it will record the page that they visited, the time, and then store it under their user id for future additions/retrievals.

所以，我原来的计划是所有数据存储在一个数组，然后序列化/反序列化它每次检索，然后将它存回数据库中的文本字段。现在的问题是：我不知道效率如何，这将与大型数据阵列得到，如果用户建立了（例如）10K页面的历史记录

So my original plan was to store all of the data in an array, and then serialize/unserialize it on each retrieval and then store it back in a TEXT field in the database. The problem is: I don't know how efficient or inefficient this will get with large arrays of data if the user builds up a history of (e.g.) 10k pages.

编辑：所以我想知道什么是最有效的方式做到这一点？我也考虑只在数据库中每一个历史插入新行，但随后这将使大型数据库从选择的事情。

So I want to know what is the most efficient way to do this? I was also considering just inserting a new row in the database for each and every history, but then this would make a large database for selecting things from.

问题是什么是数据库或块状序列化阵列更快/高效，行巨量？任何其他更好的解决方案显然是值得欢迎的。我最终将切换到Python，但目前这在PHP做。

The question is what is faster/efficient, massive amount of rows in database or massive serialized array? Any other better solutions are obviously welcome. I will eventually be switching to Python, but for now this has to be done in PHP.

推荐答案

有对存储数据的序列化数组没有任何好处。检索数据，反序列化的大斑点，对其进行修改和重新序列化，更新速度慢 - 更糟的是，会越慢的数据块（正是你担心）放大

There is no benefit to storing the data as serialized arrays. Retrieving a big blob of data, de-serializing, modifying it and re-serializing to update is slow - and worse, will get slower the larger the piece of data (exactly what you're worried about).

数据库是专门设计用来处理大量行的，所以使用它们。您每次插入不增加成本的数据的增长，不像你提出的方法，而你仍然存储的 的数据量是相同的，所以让数据库做它最好的，让您的$ C $ç简单。

Databases are specifically designed to handle large numbers of rows, so use them. You have no extra cost per insert as the data grows, unlike your proposed method, and you're still storing the same amount of data, so let the database do what it does best, and keep your code simple.

存储数据作为数组也使任何类型的查询和几乎不可能聚集。如果系统的目的是（例如）看到某个特定页面有多少人访问了，你就必须反序列化每​​个记录，找到所有匹配的页面，等等。如果你拥有的数据与一系列的行用户和页面，这是一个简单的SQL查询计数

Storing the data as an array also makes any sort of querying and aggregation near impossible. If the purpose of the system is to (for example) see how many visits a particular page got, you would have to de-serialize every record, find all the matching pages, etc. If you have the data as a series of rows with user and page, it's a trivial SQL count query.

如果有一天，你发现你有这么多行（10,000还不是很多行），你开始看到性能问题，想方设法优化它，也许是通过聚集和解正常化。

If, one day, you find that you have so many rows (10,000 is not a lot of rows) that you're starting to see performance issues, find ways to optimize it, perhaps through aggregation and de-normalization.

这篇关于最快的方式来存储数据表和检索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！