Mysql table building best practice

This article primarily summarizes some good practical experience over the years of work in the construction of watches, hoping to give you some insight or support.

1.An auto-incrementing primary key id

2.Two creation time, update time

3.Add index to three fields

4.Four data logical deletion

5. Five flexible use of database coding

6. Six add version field

1.An auto-incrementing primary key id
Why do I need to increase the primary key id?

Consider from two perspectives of performance and storage space :

Performance : The auto-incrementing primary key belongs to sequential writing when adding records. The utilization of disk data pages is high and will not trigger data page splitting; business-related fields are used as primary keys, which can easily trigger random writes, in order to maintain the orderliness of the index , It is necessary to move the sub-nodes of the index tree page, which will easily cause data page split.

Storage space : The length of the auto-incrementing primary key is generally smaller than that of the business primary key. The leaf nodes of the non-primary key index store the value of the primary key. Obviously, the smaller the primary key length, the smaller the leaf node of the non-primary key index. The space occupied by the primary key index is also smaller.

Therefore, from the perspective of performance and storage space, self-incrementing the primary key is reasonable.

Nothing is absolute, is it possible not to create an id when creating a table?

Don’t tell me, I really encountered it. At the beginning, there was a student following teacher table in this section. This table does not have a primary key id. Student id and teacher id are used as joint primary key id. To be honest, add students to the teacher table Since the primary key id is incremented, this field has no business meaning.

If it is said that creating an auto-incrementing primary key id has no business meaning and can accept a certain performance impact from file writing, I think it is okay not to add an auto-increment id.

2. Two creation time, update time
Why do we need to add creation time and update time?

With creation time and update time, it is of great significance for data statistics and data tracking ; the update time is naturally a concept of version, which is convenient for the realization of optimistic lock.

Where is the creation time and update time? WEB server time? DB server time?

It is recommended to use the WEB server time (Tomcat, Jboss, Apache), but not the DB server time . Don’t say anything, I have encountered the problem of DB server time being disordered, causing online data errors. It’s all bloody lessons.

3. Add index to three fields
Why do you need an index?

The purpose of the index is to reduce the number of queries and improve query efficiency. You can fully use the covering index, the leftmost prefix principle, and the unique index to optimize the query.

4.Four data logical deletion
What is data tombstone?

The so-called logical deletion is to label the data for deletion.

Why is it not recommended to physically delete?

Because the data is gone once it is physically deleted, it is inconvenient to check problems and trace the data in the future. Besides, we are principled people. From deleting the library to running away, we are determined not to do it .

5. Flexible use of database coding
If the table needs to store special characters such as emoji, you can use utf8mb4 encoding, and it is not recommended to use utf8 encoding.

6.Add version field
The version field is the version field added to the table. Every time the table is updated, the version field is also updated. The version field is used to implement optimistic locking.

I like to learn new and better ways of doing things when working on a scale, and feel free to ask questions and make suggestions.
Also, check out another story on this.
Thanks for reading this.