如何在MySQL数据库中存储特定(例如民意测验)数据? [英] How to store the specific (polls eg.) data in a MySQL database?

问题描述

假设我想将投票的投票存储在mysql数据库中.

Let's say I would like to store votes to polls in mysql database.

据我所知，我有两个选择:

As far as I know I have two options:

1 .用 poll_id，user_id，selected_option_id，vote_date 等字段创建一个表(假设为投票).

1. Create one table (let's say votes) with fields like poll_id, user_id, selected_option_id, vote_date and so on..

2 .创建一个新的投票数据库(假设为 votes_base )，并为每个民意调查在此基础上添加一个表(一个表，该表由名称中的民意调查ID组成)，比如说 poll[民意调查的编号] .

2. Create a new database for votes (let's say votes_base) and for each poll add a table to this base (a table, which consist the id of the poll in the name), let's say poll[id of the poll].

第一个选项的问题在于表很快就会变大.假设我有1000个民意调查，每个民意调查都有1000票-表中已经有100万条记录.我不知道它将花费多少速度性能.

The problem with the first option is that the table will become big very soon. Let's say I have 1000 polls and each poll has 1000 votes - that's already a million records in the table. I don't know how much of the speed performance that will costs.

第二个选项的问题是，从编程规则的角度来看，我不确定这是否是正确的解决方案.但是我敢肯定，使用此选项可以更快地找到某项民意调查的所有选票.

The problem with the second option is I'm not sure if this is the correct solution from the programming rules point of view. But I'm sure with this option it will be (much?) faster to find all votes to some poll.

或者也许有更好的选择?

Or maybe there is a better option?

推荐答案

您的第一个选择是更好的选择.从结构上讲，声音更响亮.从MySQL来看，表中的数百万行是没有问题的.每次轮询有一个新表是反模式.

Your first option is the better option. It is structurally more sound. Millions of rows in a table is no problem from MySQL. A new table per poll is an antipattern.

编辑第一条评论:

即使获得十亿或更多的票数，MySQL也应处理.索引是这里的关键.具有相同表100倍的数据库或具有行100倍的表的数据库之间有什么区别?

Even for a billion or more votes, MySQL should handle. Indexes are the key here. What is the difference between one database with 100 times the same table, or one table with 100 times the rows?

从技术上讲，第二个选项同样适用.有时可能会更好.但是我们经常看到这种情况:

Technically, the second option works as well. Sometimes it might be even better. But we frequently see this:

• 具有10列的用户而不是一个表
• 制作100个表，users_uk，users_us等，具体取决于用户来自何处.

太好了，不是吗?可以，对吗?很好，直到您要选择所有男性用户，或将users表联接到另一个表上.您将拥有一个巨大的UNION，您甚至事先都不知道桌子.

Great, no? Works, yes? Well it does, until you want to select all the male users, or join the users table onto another table. You'll have a huge UNION coming, and you won't even know the tables beforehand.

一个具有适当索引的大用户表会更好.如果它太大了(或您的磁盘)，您可以从PARTITIONING开始:您仍然可以使用一张表，但是分区存储在不同的位置.

One big users table, with the appropriate indexes, is better. If it gets too big for your liking (or your disk), you can start with PARTITIONING: you still have the benefit of one table, but the partitions are stored on different locations.

现在，通过您的民意调查，可能不会出现此类查询.在那种情况下，一个大的InnoDB表或几千个小表可能都可以工作..但是第一个选项的编程容易得多，并且没有第二个选项的缺点.为什么选择第二个选项?

Now, with your polls, these kind of queries might not happen. In that case, one big InnoDB table or 1000s of small tables might both work.. but the first option is a lot easier to program, and has no drawbacks over the second option. Why choose the second option?

这篇关于如何在MySQL数据库中存储特定(例如民意测验)数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！