Question

我有一个数据库,有两张表格:一个是保存每个用户的信息,另一个是保存每个用户游戏的信息。每个用户浏览器和每个游戏网都有独特的主要身份识别器,这样,只要我想要打上某个特定囚室,我就可以通过查询独一无二的识别器(根据我的理解,这要更快,因为可以通过指数化而不是坐在桌上的每一行)来浏览。

Now, each entry in the game table contains information about the user, so if I wanted to fetch every game for a particular user I would be able to. However, this is how I am currently fetching each game for a user:

我为每个用户每门游戏保存每个独一无二的识别资料。然后,我将这份名单分开,并对EACH游戏ID单独进行询问。因此,我的问题是,利用每个游戏的主要身份进行单独询问,是否效率更高,或者说,“从WHERERE用户ID=UsersID游戏中选取的SlectT* 更好。

更笼统地说,从长远来看,最好对几个独一无二的识别资料提出若干询问,还是进行查询,但必须从数据库的每个条目看一看? 关于我数据库负荷的想法,我通常有10 000个用户,每台有大约10个游戏。因此,比较点数为10万个查询点,每个查询点使用一个主要关键点,或10 000个查询点,但每个查询点必须经过所有各行。

感谢。

Answer 1

MySQL won t even skip a beat with the low volume you are considering. Allow future flexibility. Your "games per user" table should be UniqueID, UserID, GameID --- plus anything else you might want to track on a per user, per game combination... which is most frequent, last time played for a particular game, etc. I would NEVER suggest concatenation of unique games by any delimiter. This way, you can easily query...

谁喜欢特别游戏,对游戏进行问询,或者有多少不同的游戏,普通人喜欢游戏...... 用户之间的共同游戏是什么。

Simple to have multiple indexes on the table.. One on the Primary ID (required), one by User ID and Game ID (for optimized search for game by user first), another by Game ID and User ID (for searching for particular games)

Answer 2

当你“从WHERE用户ID=UsersID”的游戏中选取数据时,数据库的发动机实际上不必研究数据库的所有条目。

If this is a common query, then the userID column should have an index, so that the restriction on the where can be processed quickly. In the long run, it ll be much more efficient to do only one query then one for each game.

Answer 3

因此,就我的理解而言,使用者和运动会之间的关系很多。

Each User play many games Each Game has many users.

and you are currently storing the games played by each user in the users table

User ID | Games ID s
1 | 45:23:12
2 | 23:66:11

因此,你的表格没有实现正常化。甚至连1个NF。考虑您的数据。

One table for Games, which you already have. One table for users, you have this too. One Glue Table, users_games, with :

ID | userID | GameID
1 | 1 | 45
2 | 1 | 23
3 | 1 | 12
4 | 2 | 23
5 | 2 | 66
6 | 2 | 11

So for retrieving Data about one user, you will use Joins

Select GameID from users u join users_games ug on u.id=ug.userID where u.id= 2 ;

并且你可以延长用户单位表中的栏目。

This model will be scalable and more manageable.

希望:

Answer 4

SlectT * 摘自WHERE用户ID=用户ID的最佳模式;

如果你把多个游戏连接到一个用户使用LEFT JOIN,那么你就能够用一个问点进行。

in the games table have foloving columns id | userID | on id put Unique AI and userID can be simple index

Answer 5

As far as i understand, your tables are built like this:

Table_User:
UserId (PK);
UserInformation;
UserInformation2; 
...

Table_Games:
GameId (PK);
UserId (PK);
GameInformation;
...

因此,采取公平贸易原则:

SELECT * 
FROM Table_Games G 
LEFT JOIN Table_User U on (U.UserId = G.UserId) 
WHERE UserId =  MyUserId

也许会帮助你。

Here, you can also SELECT the User by an unique Name or sth. else based on Table_User!

Regards!

Answer 6

通常比许多小小小小小小小小小小小小区更喜欢做一个大问题,因为:

It can be performed in just one database round-trip instead of many. This is especially important when database round-trips need to go over the network.
Allows database engine to use more advanced query plan than simple NESTED LOOPS that you are emulating in your code ("big" databases such as Oracle are also capable of doing MERGE or HASH JOINs, not sure about MySQL).

在您的两张桌子上打一只JOIN,应该 do。

友情链接