运行分析网站...1 大量插入与少量选择和插入
目前我的数据库设计尚未规范化。
当用户访问网站时，所有数据（例如完整的浏览器名称、操作系统名称等）都会被存储。这导致数据库以每天约 10GB 的速度快速增长，而每天只有 200 万人（每天 200 万次插入）。然而，这仅意味着每个数据只有一次插入。我正在使用 mysql 和 php。
现在我想知道如果我标准化数据是否会更快。我的意思是为操作系统名称和操作系统 ID、浏览器名称和浏览器 ID 创建单独的表。然后使用流量日志表中的 ID。 在这种情况下，将在浏览器表和操作系统表中进行两次查找以获取 id，如果它们不存在，则进行两次插入，然后在流量日志表中使用浏览器 id 和操作系统 id 进行另一次插入。因此，2 个选择、2 个潜在插入和 1 个集合插入。
哪一个可能会更快。是否还有其他选项用于基于文件的方法来存储操作系统等，或者可能将所有可能的浏览器、操作系统存储在数组中，然后仅在访问者拥有这些浏览器或操作系统之一时才插入，否则只需将它们放入“无”。
Currently my database design is not normalised.
When a user visits the website all the data such as the full browser name, OS name, etc are stored. This is causing the database to grow very fast at a rate of about 10GB a day with just 2 million people a day (2 million inserts per day). However this only means that there is only one insert per data. I am using mysql and php.
Now what I was wondering if it would be faster if I normalised the data. What I mean is create separate tables for OS name and OS ids, browser name and browser id. Then use the ids in the traffic log table.
In this situation there would be two lookups in the browser table and the os table for the ids and if they dont exist then two inserts and then another insert with the browser id and os id in the traffic log table. So 2 selects and 2 potential inserts and 1 set insert.
Which one is likely to be faster. Also are there any other options for file based approach for storing the os etc or maybe store the all possible browsers, os in arrays and then only insert if the visitor has one of those browsers or os else just put them in as none.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
你实际上是在考虑速度与空间的问题；更复杂的表结构可能会比将大量批量数据插入到单个表中慢一些。在这种情况下，牺牲一点速度来降低数据库大小的增长速度通常被认为是合适的；按照您描述的方式标准化表格应该可以提供准确的折衷方案。
此外，将数据拆分为多个表并进行交叉引用将使您以后可以更轻松地对数据进行查询，例如您决定要对网站的用户运行分析。
You're really looking at a question of speed versus space; the more complex table structure will likely be a bit slower than inserting a large quantity of bulk data into a single table. In these sorts of situations, a little bit of speed sacrifice to reduce the rate of database size growth is usually considered to be appropriate; normalizing the tables in the way you describe should provide exactly that compromise.
Additionally, splitting up the data into multiple tables and having cross-referencing will allow you to later do easier queries on the data, say if you decide that you want to run analytics on the users to your website.