Rough talk about Mysql -- will you create databases and tables?

The "build" mentioned in this article is not just about creating a database or a table, but about whether your database and table can cope with various events during project operations, next, let me talk about some of the problems I encountered in the project and how to solve them. This is a small experience and I will share it with you.

　　1. Are you still using a primary key for association between two tables?

For example, there are two tables, one for news topic and one for news. Now the two tables need to be associated, I think most people must create a news topic id in the News table, and then write the primary key ID (auto-increment) in the news topic table to this field, associate two tables in this way.

If you do, get rid of this habit. You may ask why the column id is a primary key and auto-increment. Why is this operation not feasible? The reason is actually very simple. We will add and delete columns, and deletion will lead to a disconnection between primary key IDs. Because the primary key is set to auto-increment, that is, the columns you deleted earlier, then, the id will not fill in the vacancy, but will continue to increase. In this case, if the database is optimized that day, the primary key is re-ordered (the mysql optimization software is not found to optimize the primary key, but the primary key can be deleted through code, then we can create a new auto-incrementing primary key to re-sort the primary key), so we can get it done, and the columns and articles are totally different. Therefore, we recommend that you create a numbered field instead of a primary key for association between two tables. here we can use the mysql uuid () function as the number, for more information about the relevant literature, see whether UUID is a good primary key or not. Therefore, only one table requires two primary keys, one physical primary key (auto-increment id), and one logical primary key (UUID ), the reason is: For engines like InnoDB that aggregate primary key types, data will be sorted by primary key. InnoDB will generate a huge IO pressure due to the unordered UUID, UUID is not suitable for the physical primary key. It can be used as the logical primary key. The physical primary key still uses the auto-increment ID. As for the performance, I tested it locally and there was basically no difference. Some people on the Internet also tested 10 million pieces of data-test MYSQL UUID performance.

　　2. Set the primary key type to bigint.

Bigint is all integer data from-2 ^ 63 (-9223372036854775808) to 2 ^ 63-1 (9223372036854775807). The storage size is 8 bytes. Int is an integer data from-2 ^ 31 (-2,147,483,648) to 2 ^ 31-1 (2,147,483,647). The storage size is 4 bytes. The storage space doubles, while the storage data expands by N times. When the primary key is an auto-increment field, we cannot control how many values it will increase, therefore, when I create a table, the primary key type is set to bigint. Similarly, the number field type mentioned above is also bigint.

　　3. Do not set the varchar length to "dead"

This is also a problem that I often encountered before. For example, I set my mobile phone to varchar (11), my zip code to varchar (6), and my name to varchar (10, it seems that every field is strictly set, but in actual project progress, this is completely self-defeating. For example, if a mobile phone is used, the user must enter 0 before the mobile phone number, such as the zip code, what if the user inputs A fullwidth number? Let alone the name. In case of a minority, the name should be seven or eight characters long. Therefore, I suggest that, since it is defined as varchar, it does not involve computing. Why not define a general length, such as varchar (50)? If you really want to limit the length, use a program to judge it, do not restrict the database. Otherwise, the user will lose a long string and mysql will save the first few characters, which makes people think this program is faulty.

In addition, if you are working on a general background such as cms, do not limit the field too much, because you cannot predict the needs of each project in the future, therefore, I still set the varchar to a larger value. Now I set it to 255 in a unified manner. If it is possible that there will be more than 255 fields, such as URL, I will simply set it to text, once and for all.

　　4. Create an index for common search fields

Do not explain, but do not blindly create an index.

　　5. You are welcome to reply and add