mysql database design
- 2020-05-07 20:33:52
- OfStack
The innodb table is the opposite; Row-level lock table
Internet services, excluding payment services, interactive products, news systems, etc. 1 is generally read more, write less. myisam is appropriate.
The design of the table
Fixed-length table: all columns have fixed-length fields. You can look up mysql's manual variable fields as VARCHAR, BLOB, or TEXT. int char are all fixed-length, so fixed-length tables take up a lot of space.
Dynamic table: the fields are not all fixed in length.
Fixed - length tables are faster to retrieve than dynamic tables.
Software system design is used to put the function of every table distinguish clearly, such as user tables are user information, if you need data from the message list at the same time, also from the users table of user information, check will adopt combination, sometimes 1 some operations will use left, join etc all kinds of complicated sql statement, perhaps also use mysql function. If it is for the traffic, read a lot of Internet services, and at the same time sent to read, the data is large, very scary. It is better if the data will not be modified, in the common table has redundant fields, can do 1 read, get the data; You can have redundant write operations, but less complex query operations.
When designing a table, sum up the number of bytes consumed by all the field types in the table and multiply them by your expectation (for example, the amount of data stored in 100W) that the entire table will consume in the future.
Table down the library
Open table is to copy a table N more than one, which is not the storage of the content of the data, data storage is used to determine which table HASH algorithm.
For example, the user table user, the traditional situation is one table, the table is copied into user_01,user_02, and other tables are stored in the format of 1 sample of different user data.
Debunking a library is similar to debunking a table, in that it is a copy of the library.
There are a lot of HASH algorithms for tabbing or debunking, the main purpose of which is to reduce the amount of data in the table. The algorithm is used to ensure the average amount of data in each table. The request, read and write operations are Shared to reduce the pressure, and the security. The disadvantage is that retrieval is not convenient, need to think of another way.
Many website early convenient using discuz products, such as bbs blog etc., there are many online about this product introduction and the optimization method, not studied, heard 1 some website their optimization method for the database is mainly adopt the method of master-slave, separate the database reads and writes to improve performance, but people feel this way in the amount of data when the scale OVER, concurrent and read and write operations didn't improve, the data is also gradually accumulated more than limit.
Internet service due to deal with the large amount of data, large request, so in the design and development of the time not too academic, do not try to achieve the design of the database, procedures "beautiful", performance is the most important.
Internet services, excluding payment services, interactive products, news systems, etc. 1 is generally read more, write less. myisam is appropriate.
The design of the table
Fixed-length table: all columns have fixed-length fields. You can look up mysql's manual variable fields as VARCHAR, BLOB, or TEXT. int char are all fixed-length, so fixed-length tables take up a lot of space.
Dynamic table: the fields are not all fixed in length.
Fixed - length tables are faster to retrieve than dynamic tables.
Software system design is used to put the function of every table distinguish clearly, such as user tables are user information, if you need data from the message list at the same time, also from the users table of user information, check will adopt combination, sometimes 1 some operations will use left, join etc all kinds of complicated sql statement, perhaps also use mysql function. If it is for the traffic, read a lot of Internet services, and at the same time sent to read, the data is large, very scary. It is better if the data will not be modified, in the common table has redundant fields, can do 1 read, get the data; You can have redundant write operations, but less complex query operations.
When designing a table, sum up the number of bytes consumed by all the field types in the table and multiply them by your expectation (for example, the amount of data stored in 100W) that the entire table will consume in the future.
Table down the library
Open table is to copy a table N more than one, which is not the storage of the content of the data, data storage is used to determine which table HASH algorithm.
For example, the user table user, the traditional situation is one table, the table is copied into user_01,user_02, and other tables are stored in the format of 1 sample of different user data.
Debunking a library is similar to debunking a table, in that it is a copy of the library.
There are a lot of HASH algorithms for tabbing or debunking, the main purpose of which is to reduce the amount of data in the table. The algorithm is used to ensure the average amount of data in each table. The request, read and write operations are Shared to reduce the pressure, and the security. The disadvantage is that retrieval is not convenient, need to think of another way.
Many website early convenient using discuz products, such as bbs blog etc., there are many online about this product introduction and the optimization method, not studied, heard 1 some website their optimization method for the database is mainly adopt the method of master-slave, separate the database reads and writes to improve performance, but people feel this way in the amount of data when the scale OVER, concurrent and read and write operations didn't improve, the data is also gradually accumulated more than limit.
Internet service due to deal with the large amount of data, large request, so in the design and development of the time not too academic, do not try to achieve the design of the database, procedures "beautiful", performance is the most important.