Mysql tips on building libraries and tables
- 2020-05-10 23:03:14
- OfStack
1. If there is an association between two tables, are you still using the primary key for the association?
For example, now there are 2 tables, 1 news column table and 1 news column table. Now the two tables need to be correlated. I think most people would like to build a news column id in the news table, and then write the primary key ID (self-increment) in the news column table into this field, and then associate the two tables in this way.
If that's what you do, break the habit. You may ask why, column id is the primary key, is also self-increment, why does this not work? The reason is actually very simple, we will add columns, also delete, delete will cause the primary key id will break the number, because the primary key is set to increase, that is to say, you delete the column before, and then add, id will not fill any vacancy, but 1 straight increase. This can cause a kind of circumstance, if that optimize the database, the primary key for the reorder (had not found mysql software will optimize the primary keys, but can pass code to delete a primary key, and then the new build on the primary key to implement the primary key reordering), that is completely glass, columns and articles in the completely. So I don't suggest correlation between two table primary key, but a separate build a number of fields, we can use mysql uuid here () function as a number, can refer to relevant literature UUID family key good or bad, so only one table to two primary keys, a physical primary key (from increasing id), a logic primary key (UUID), the reason is: For an engine like InnoDB, which gathers primary keys, the data will be sorted by primary keys. Due to the disorder of UUID, InnoDB will generate huge pressure of IO. At this time, UUID is not suitable for physical primary keys, but it can be used as logical primary keys. As for performance, I tested it locally and there was basically no difference. Someone else did a test of 10W data on the Internet -- "measured MYSQL UUID performance".
2. Set the primary key type to bigint
bigint stores all integer data from -2^63 (-9223372036858575808) to 2^63-1 (9223372036854775807), with a storage size of 8 bytes. While int is an integer from -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647), with a storage size of four bytes. The storage space is expanded by 1 times, while the storage data is expanded by N times. In addition, the primary key is a self-incrementing field, so we have no control over how much value it will self-incrementing. Therefore, I usually set the primary key type to bigint when building the table, and the numbered field type mentioned above is also bigint.
3. Don't make varchar too "dead"
Before this is also I often make a problem, such as mobile phone, I will set to varchar (11), zip code set to varchar (6), the name is set to varchar (10), and so on and so on, seemingly every field set very strict, but in the actual project going on, it is hard to eat, such as mobile phone, the user must be in front of the mobile phone number lost 0, and zip code, for example, if the user input the number which is the Angle? The name is not to mention, 10000 is a minority people, the name of 78 characters. So I suggest, since defined as varchar, means will not involve the calculation, why not just defines the length of a general, such as varchar (50), if you really want to limit the length, use the program to determine, don't let the database to limit, or the user loses 1 long string, the result mysql will have the first few characters, let a person feel this program has a problem.
cms also, if you do this general background, more don't limit the field too "death", because you can't expect after each project demand, so set varchar big 1 point, I am now system is set to 1, 255, if is likely to be more than 255 fields, such as URL, I simply set to text, 1 LaoYongYi.
4. Index common search fields
Do not explain, but do not blindly index.
5. Your reply and supplement are welcome
For example, now there are 2 tables, 1 news column table and 1 news column table. Now the two tables need to be correlated. I think most people would like to build a news column id in the news table, and then write the primary key ID (self-increment) in the news column table into this field, and then associate the two tables in this way.
If that's what you do, break the habit. You may ask why, column id is the primary key, is also self-increment, why does this not work? The reason is actually very simple, we will add columns, also delete, delete will cause the primary key id will break the number, because the primary key is set to increase, that is to say, you delete the column before, and then add, id will not fill any vacancy, but 1 straight increase. This can cause a kind of circumstance, if that optimize the database, the primary key for the reorder (had not found mysql software will optimize the primary keys, but can pass code to delete a primary key, and then the new build on the primary key to implement the primary key reordering), that is completely glass, columns and articles in the completely. So I don't suggest correlation between two table primary key, but a separate build a number of fields, we can use mysql uuid here () function as a number, can refer to relevant literature UUID family key good or bad, so only one table to two primary keys, a physical primary key (from increasing id), a logic primary key (UUID), the reason is: For an engine like InnoDB, which gathers primary keys, the data will be sorted by primary keys. Due to the disorder of UUID, InnoDB will generate huge pressure of IO. At this time, UUID is not suitable for physical primary keys, but it can be used as logical primary keys. As for performance, I tested it locally and there was basically no difference. Someone else did a test of 10W data on the Internet -- "measured MYSQL UUID performance".
2. Set the primary key type to bigint
bigint stores all integer data from -2^63 (-9223372036858575808) to 2^63-1 (9223372036854775807), with a storage size of 8 bytes. While int is an integer from -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647), with a storage size of four bytes. The storage space is expanded by 1 times, while the storage data is expanded by N times. In addition, the primary key is a self-incrementing field, so we have no control over how much value it will self-incrementing. Therefore, I usually set the primary key type to bigint when building the table, and the numbered field type mentioned above is also bigint.
3. Don't make varchar too "dead"
Before this is also I often make a problem, such as mobile phone, I will set to varchar (11), zip code set to varchar (6), the name is set to varchar (10), and so on and so on, seemingly every field set very strict, but in the actual project going on, it is hard to eat, such as mobile phone, the user must be in front of the mobile phone number lost 0, and zip code, for example, if the user input the number which is the Angle? The name is not to mention, 10000 is a minority people, the name of 78 characters. So I suggest, since defined as varchar, means will not involve the calculation, why not just defines the length of a general, such as varchar (50), if you really want to limit the length, use the program to determine, don't let the database to limit, or the user loses 1 long string, the result mysql will have the first few characters, let a person feel this program has a problem.
cms also, if you do this general background, more don't limit the field too "death", because you can't expect after each project demand, so set varchar big 1 point, I am now system is set to 1, 255, if is likely to be more than 255 fields, such as URL, I simply set to text, 1 LaoYongYi.
4. Index common search fields
Do not explain, but do not blindly index.
5. Your reply and supplement are welcome