Mysql Build table skill sharing _mysql

If there is an association between the two tables, are you still using the primary key to correlate?
For example, now there are 2 tables, a news column, a news sheet, now two tables need to be related, I think most people's approach is definitely to build a news column ID in the news table, and then the News column of the primary key ID (self-added) to this field, through the two-table association.
If that's what you're doing, get rid of the habit. Maybe you will ask why, the column ID is the primary key Ah, but also since the increase, why this operation not? The reason is actually very simple, column we will increase, will also delete, delete will cause the primary key ID will be broken between the situation, because the primary key set to the self, that is, you deleted before the column, and then add, ID is not to fill in which vacancy, but has been increasing. This creates a situation where, if the database is optimized for that day, the primary key was reordered (not found at the moment MySQL optimization software will optimize the primary key, but you can delete the primary key through the code, and then create a new from the primary key to achieve the primary key reordering), it is a complete cup, columns and articles are completely not on the number. So I suggest that the correlation between the two tables is not a primary key, but to build a separate number of fields, we can use the MySQL uuid () function as a number, the relevant literature can refer to the "UUID key good or bad", only so a table to 2 primary keys, a physical primary key (since the ID), A logical primary KEY (UUID), the reason: for an engine that INNODB this clustered primary key type, the data is sorted according to the primary key, and because of the unordered nature of the UUID, InnoDB creates a huge IO pressure, which is not appropriate to use the UUID as a physical primary key, as a logical primary key, The physical primary key still uses the self-increasing ID. As for the performance, I measured the local basically no difference, online also someone did 10W data test-"measured MySQL uuid performance."
Second, unify the primary key type set to BigInt bar
bigint are all integer data from -2^63 (-9223372036854775808) to 2^63-1 (9223372036854775807), with a storage size of 8 bytes. The int is an integer data from -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) with a storage size of 4 bytes. The storage space expands by one times, while the stored data expands n times. Plus, the primary key is an bigint field, and we simply can't control how many values it's going to get, so I usually have the primary key type set to the table when I'm building it, and again, the Number field type mentioned above is also bigint.
third, do not set varchar length too "dead"
That's what I've always done before, for example, the mobile phone, I set to varchar (11), the postcode set to varchar (6), the name set to varchar (10) and so on, and so on, seemingly each field is set very rigorous, but in the actual project, this is to get bitter to eat, For example, the mobile phone, the user must be in front of the mobile phone number 0, or, for example, ZIP code, if the user input is the full angle of the number? The name is not to mention, if it is a minority people, the name seven or eight words. So I suggest that since the definition of varchar, on behalf of not involved in the calculation, why not simply define a general length, such as varchar (50), if you really want to limit the length, use the procedure to judge, do not let the database to limit, otherwise the user lost a long string, the result of MySQL saved the first few characters It makes people feel that there is something wrong with this procedure.
There is, if you are doing a CMS this generic backstage, do not limit the field too "dead", because you can not anticipate the requirements of each project after, so still put the varchar a little bit bigger, I am now unified all set to 255, if it is likely that more than 255 of the fields, such as URLs, I simply set it as text, once and for all.
establish an index for the commonly used search fields
Do not explain, but do not blindly build the index.
welcome your reply to add