mysql多个程序同时一张表,Mysql：多个表还是一个大表？

朴灿烈づ我的快乐病毒、 2022-08-30 11:17 174阅读 0赞

![Image 1][]

This question has been already asked but I've not found a "1 voice answer".

Is it better to do :

1 big table with :

user\_id | attribute\_1 | attribute\_2 | attribute\_3 | attribute\_4

or 4 smal tables with :

user\_id | attribute\_1

user\_id | attribute\_2

user\_id | attribute\_3

user\_id | attribute\_4

1 big table or many small tables ? Each user can only have 1 value for attribute\_X. We have a lot of data to save (100 millions users). We are using innoDB. Performance are really important for us (10 000 queries / s).

Thanks !

François

解决方案

If you adhere to the Zero, One or Many principle, whereby there is either no such thing, one of them, or an unlimited number, you would always build properly normalized tables to track things like this.

For instance, a possible schema:

CREATE TABLE user\_attributes (

id INT PRIMARY KEY NOT NULL AUTO\_INCREMENT,

user\_id INT NOT NULL,

attribute\_name VARCHAR(255) NOT NULL,

attribute\_value VARCHAR(255),

UNIQUE INDEX index\_user\_attributes\_name(user\_id, attribute\_name)

);

This is the basic key-value store pattern where you can have many attributes per user.

Although the storage requirements for this is higher than a fixed-columns arrangement with the perpetually frustrating names like attribute1, the cost is small enough in the age of terabyte-sized hard-drives that it's rarely an issue.

Generally you'd create a single table for this data until insertion time becomes a problem. So long as your inserts are fast, I wouldn't worry about it. At that point you would want to consider a sharding strategy to divide this data into multiple tables with an identical schema, but only if it's required.

I would imagine that would be at the ~10-50 million rows stage, but could be higher if the amount of insert activity in this table is relatively low.

Don't forget that the best way to optimize for read activity is to use a cache: The fastest database query is the one you don't make. For that sort of thing you usually employ something like memcached to store the results of previous fetches, and you would invalidate this on a write.

As always, benchmark any proposed schema at production scale.

[Image 1]: