MySQL Database Design

The opposite is true for InnoDB tables; row-Level Lock tables
Internet services are not paid services. Interactive Products, news systems, and so on are generally read-only and rarely written. It is more appropriate to use MyISAM tables.
Table Design
Fixed Length table: The field lengths of all columns are fixed. You can check whether the variable length field of the MySQL manual is varchar, blob, or text. Int char is fixed length, and the space occupied by the fixed length table is large.
Dynamic table: the fields are not all fixed.
A fixed-length table is faster than a dynamic table.

The software system is designed to clearly distinguish each table. For example, a user table is user information. If you need to retrieve data from the message table at the same time, when user information is retrieved from the user table, joint queries are used. Sometimes complicated SQL statements such as left and join are used for some operations, and MySQL functions may be used. If it is for access traffic and Internet services with a large read volume, it can be read at the same time and the data volume is large, which is terrible. It is best if the data is not modified and there are redundant fields in common tables, so that the data can be read once and obtained; Redundant write operations can be performed, but complex query operations can be reduced.

When designing a table, you need to sum up the number of bytes occupied by all field types in the table and multiply it by your expectation (for example, to store million data records), that is, the whole table will occupy capacity in the future.

Split table and split Database
A split table is a copy of more than N tables, which respectively store data that does not need content. The data is stored using hashAlgorithmTo decide which table to add.
For example, if the user table is a traditional table, the split table means that the table is copied to user_01, user_02, and so on, all of which store different user data in the same format.

Database sharding is similar to table sharding, that is, database replication.

There are many hash algorithms for table sharding or database sharding. The main purpose is to reduce the data volume of the table. The algorithm is used to ensure the average data volume of each table. The request and read/write operations are apportioned to reduce the pressure and ensure security, if the problem occurs, most users are affected. The disadvantage is that retrieval is not convenient. You need to find another method.

Many websites adopt discuz products, such as BBS and blog, for the sake of convenience in the early stage. There are many introductions and optimization methods on this product on the Internet, which have not been carefully studied, when I have heard of some websites introducing their optimization methods, I mainly use the master-slave method for the database to improve the performance by separating the read/write of the database, however, I personally think that this method will be over when the data size reaches the upper limit. The concurrency and read/write operations are not improved, and the data will gradually exceed the limit.

Because Internet services need to deal with large data volumes and large request volumes, we should not be too academic in design and development, rather than trying to reach the database,ProgramThe design is "beautiful", performance is the most important.