As a back-end developer, we often need to design database tables. I have compiled 21 empirical guidelines for designing MySQL tables and shared them with you, and everyone will definitely be helpful after reading them.
1. Naming conventions
Database table names, field names, index names, etc. all need naming conventions, high readability (generally required in English), so that others can see the name, you know what this field means.
For example, the account number field of a table, but the following:
Primary key index name pk_ field name; Unique index name uk_ field name; Normal index names are idx_ field names.
2. Select the appropriate field type
Choose a field type with little storage space as much as possible, just like a numeric type, starting from tinyint, smallint, int, bigint from left to right
For decimal types such as amounts, select decimal and disable the use of float and double.
If the stored strings are almost equal in length, use the char fixed-length string type.
The varchar is a variable-length string that does not pre-allocate storage space and does not exceed 5000 in length.
If the stored value is too large, it is recommended that the field type be changed to text, and a separate table is extracted and corresponded to with a primary key.
In the same table, the length of all varchar fields can add up to no greater than 65535. If you have such a requirement, use the TEXT/LONGTEXT type.
3. The primary key design should be reasonable
4. Select the appropriate field length
5. Prioritize tombstones over physical deletions
6. Each table needs to be added
These are several common fields
Tables must be general, or have these fields:
id: primary key, a table must have a primary key, must
create_time: Creation time, required
modifed_time: Modified at the time, must be updated, when the record is updated, it needs to be updated
version : The version number of the data record, used for optimistic locks, not required
remark : Data record remarks, not required
modified_by: Modification person, not required
7. A table should not have too many fields
8. Define fields with not null whenever possible
First, NOT NULL prevents null pointer problems.
Secondly, the NULL value store also requires additional space, which also causes more complex comparison operations and makes it difficult for the optimizer to optimize SQL.
A NULL value may cause the index to be invalidated
9. When designing the table, evaluate which fields
Indexing is required
Do not build too many indexes, generally do not exceed 5 indexes per table. Because too many indexes are created, the writing speed is reduced.
Fields that are not highly distinguished cannot be indexed, such as gender, etc
After the index is created, it is still necessary to pay attention to avoid the failure of the index, such as the use of mysql’s built-in function, which will cause the index to fail
10. Do not need to strictly adhere to 3NF,
Reduce table associations through business field redundancy
The first normal form: for the atomicity of the property, it is required that the property be atomical and can no longer be decomposed;
The second paradigm: for the uniqueness of the record, it is required that the record has a unique identification, that is, the uniqueness of the entity, that is, there is no partial dependency;
We design the relationship between tables and their fields as much as possible to meet the third normal form. But sometimes, there is a lot of redundancy to improve efficiency. For example, the following table
The product name
The total amount
The above is a basic table for storing product information. The existence of this field of total amount indicates that the design of the table does not meet the third normal form, because the total amount can be obtained from the unit price* quantity, indicating that the total amount is a redundant field. However, adding the redundant field of total amount can improve the speed of query statistics, which is the practice of exchanging space for time.
11. Avoid using MySQL reserved words
12. Do not engage in foreign key association,
Generally in code maintenance
13. The INNODB storage engine is generally selected
Let’s review the comparison and difference between the three storage engines of MySQL:
14. Choose the appropriate unified character set.
utf8: Support Chinese and English mixed scenes, international pass, 3 bytes length
utf8mb4: Fully compatible with utf8, 4 bytes in length, generally used to store emoji emoticons.
GBK : Chinese supported, but not for international character sets, 2 bytes in length
15. If your database fields are of enumeration type,
Need to be clearly commented in the comment
If you design a database field that is an enumeration type, you need to annotate the meaning of each enumeration after the comment for ease of maintenance
16. Time type selection
Date : Represents the date value, in the format yyyy-mm-dd, ranging from 1000-01-01 to 9999-12-31, 3 bytes
Time : Represents the time value, in the format hh:mm:ss, in the range -838:59:59 to 838:59:59, 3 bytes
datetime: Represents a datetime value in the format yyyy-mm-dd hh:mm:ss, ranging from 1000-01-01 00:00:00 to 9999-12-31 23:59:59”’, 8 bytes, independent of time zone
timestamp: Represents a timestamp value in the format yyyymmddhhmmss, ranging from 1970-01-01 00:00:01 to 2038-01-19 03:14:07, 4 bytes, depending on the time zone
17. Stored procedure is not recommended
(including stored procedures, triggers)
Cascading changes can be implemented through related tables in the database.
Real-time monitoring of changes to a field in a table requires appropriate processing.
For example, you can generate numbers for certain transactions.
18. Design of the 1:N relationship
19. Large fields
20. Consider whether you need to sub-database and table sub-tables
Sort the problem
21. Some optimization experience with sqL writing
Query SQL try not to use select *, but select specific fields
If you know that there is only one query result or only one record maximum/minimum, we recommend that you use limit 1
You should try to avoid using or to join conditions in the Where clause
– EOF –
How to achieve database read consistency
Dig three feet into the Redis and MySQL data consistency issues
Got a harvest after reading this article? Please forward and share it with more people
Follow “ImportNew” to improve your Java skills
Likes and looks are the biggest support ❤️