mysql database design

Innodb tables are just the opposite; row-level lock tables
Internet services, except for paid services, interactive products, news systems, etc. generally read more and write less. It is more appropriate to use the myisam table.
Table design
Fixed-length table: The field lengths of all columns are fixed-length. You can check the mysql manual. The variable length fields are VARCHAR, BLOB or TEXT. Int chars are all fixed-length, and fixed-length tables take up a lot of space.
Dynamic table: The fields are not all fixed length.
Fixed-length tables are retrieved faster than dynamic tables.

The design habit of software systems is to distinguish very clear functions for each table. For example, the user table contains user information. If you need to fetch data from the message table and user information from the user table at the same time, you will use a union. For queries, sometimes some operations will use various complex SQL statements such as left and join, and may also use MySQL functions. If it is an Internet service with a large number of visits and readings, and the data is read concurrently, the amount of data is huge, which is terrible. It is best to have redundant fields on commonly used tables if the data will not be modified, so that all data can be read in one go; redundant write operations can be used, but complex query operations are reduced.

When designing a table, sum up the number of bytes occupied by all field types in the table and multiply it by your expectation (for example: storing 100W of data), which is the capacity that the entire table will occupy in the future.

Dismantle the table and dismantle the database
Split the table is to copy a table to N multiple copies, and store the data with different contents respectively. The HASH algorithm is used to store the data to decide which table to put it into.
For example, the user table user is traditionally one table. Splitting the table means copying the table into user_01, user_02, etc., which all store different user data in the same format.

Deleting a library is similar to dismantling a table, it is a copy of the library.

There are many HASH algorithms for table or database dismantling. The main purpose is to reduce the data volume of the table. The algorithm is used to ensure that the data volume of each table is average. The requests, read and write operations are amortized to reduce the pressure, and it is safe. The most problems occur. Some users are affected. The disadvantage is that retrieval is inconvenient and you need to find another way.

Many websites use discuz products to save trouble in the early stage, such as bbs, blog, etc. There are many introductions and optimization methods about this product on the Internet. I have not studied them in detail. I have heard some websites introduce their optimization methods. For databases The main method is to use the master-slave method to separate the reading and writing of the database to improve performance. However, I personally feel that this method will be over when the amount of data reaches the scale. The concurrency and read and write operations will not be improved, and the data will gradually accumulate beyond the limit. .

Internet services have to deal with large amounts of data and requests, so when designing and developing, don’t be too academic and don’t strive to achieve “beautiful” design of databases and programs. Performance is the most important thing.

The above is the content of mysql database design. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!