当前位置:网站首页>The boss asked me, "why do you set an auto increment ID when building a watch? Is it not right to use serial number as primary key? "

The boss asked me, "why do you set an auto increment ID when building a watch? Is it not right to use serial number as primary key? "

2020-11-08 23:46:10 Liu Zhihang

It's time to start a new project again , A meal operation as fierce as a tiger , Sort out the process and draw a picture . see , Start to process and table structure .


I : Come on, come on ……
The eldest brother : Why does this table create an auto increment id ? Use the serial number directly ( The user no. / Product number ) Just be the primary key ?
I : This is DBA Stipulated , Create table id、create_time、update_time All three fields should have .《Java The development of specification 》 It's the same rule .
buddy :( Attach to ) Yes , The rules are like this !
The eldest brother : Serial number is the only index in you ? Set to primary key , So you don't have to id 了 , It also reduces one return table query ?
I :…… ( It seems to make a lot of sense , We dare not speak .)
The eldest brother : Since they stipulate , Then you go back and find out why you want to design an auto increment id ?
I : Take out the little book ( Go back to the information ~).

why-id-8ZFv04

Make a schedule

Java  Development Manual - Song Mountain version

At work , When creating a table ,DBA I'll also check and build the table SQL, Check for compliance with specifications and indexing of common fields .

CREATE TABLE `xxxx` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT ' Since the primary key ',
  `create_time` datetime(3) NOT NULL DEFAULT current_timestamp(3) COMMENT ' Creation time ',
  `update_time` datetime(3) NOT NULL DEFAULT current_timestamp(3) ON UPDATE current_timestamp(3) COMMENT ' Update time ',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_create_time` (`create_time`) USING BTREE,
  KEY `idx_update_time` (`update_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COMMENT=' Table annotation ';

So in the course of my use , The serial number has a separate field , For example trans_no, But this time there was a question :trans_no Since it's the only one , Why not use it directly trans_no treat as id Well ?

Now we start by looking up the relevant information , Step by step, understand why ?

why-id-2-pMeutc

Primary key

What is a primary key

MySQL-primary-key-z64UFB

https://dev.mysql.com/doc/refman/8.0/en/glossary.html#glos_primary_key

We mainly focus on the last sentence of this definition :

When choosing primary key values, consider using arbitrary values (a synthetic key) rather than relying on values derived from some other source (a natural key).

When creating a primary key, try to use MySQL Auto increment primary key instead of using business generated value should be used as primary key .

The characteristics of primary keys

MySQL-primary-key-1-QDuU4H

In short :

Non empty 、 only 、 Change little or no change .

How to add a primary key

hfPeDL-r5M5YU

Can be in create When creating a table, specify , You can also use alter Add the primary key after the statement , However, the official recommendation is to specify when creating a table .

Why add a primary key

  1. The primary key can uniquely identify this row of data , This ensures that when the update operation is deleted , It's just a line of data .
  2. The index needs , Every InnoDB The table has a special index , Clustering index , Used to store row data . Usually , Cluster index is synonymous with primary key .
    1. Declaration of primary key ,InnoDB The primary key will be used as a cluster index .
    2. When not stated , Will be in UNIQUE Find the first index where all key columns are located ,NOT NULL And use it as a cluster index
    3. Not declared and no suitable UNIQUE Indexes , A hidden cluster index is generated internally GEN_CLUST_INDEX, This hidden line ID yes 6 Bytes and monotonically increase .

chart -> So what is an index

Indexes

This is only about InnoDB engine , Please refer to official documents for details , And the introduction is relatively simple .

Index classification

  1. Cluster index : Table storage is organized according to the values of primary key columns , To speed up queries and sorts involving primary key columns . In the introduction of primary key, cluster index is also introduced .
  2. Secondary indexes : It can also be called a secondary index , The primary key column and the secondary index column are recorded in the secondary index . When searching based on the secondary index , The corresponding primary key column will be obtained according to the auxiliary index , Then search in the cluster index according to the primary key . It is generally not recommended that the primary key is very long , Because the primary key is long, the secondary index uses more space .

Add :

Back to the table : First, query the corresponding primary key value in the secondary index , Then according to the primary key, the query is retrieved from the cluster index .
Index overlay : The secondary index records the primary key column and the secondary index column , If I only query the values of primary key columns and secondary index columns , Then there's no need to go back to the watch .

The physical structure of the index

InnoDB The use of B+ Data structure , According to the cluster index value ( Primary key /UNQIUE/ Or make it yourself ) Build a B+ Trees , The leaf node stores row record data , So each leaf node can also be called a data page . The default size of each data page is 16k, Support customization .

B+Tree-UgFQjS

Data insertion

When data is inserted ,InnoDB Will make the page 1/16 Free , To insert and update index records in the future .

  1. Order insert ( In ascending or descending order ): The remaining index pages will be about 15/16 pack full
  2. Insert randomly : Only use capacity 1/2 To 15/16

In random insertion , Will move frequently 、 Pagination , And it creates a lot of debris , And make the index tree not compact enough . And using sequential insertion , The data is more compact , There is a higher utilization of space .

summary

Q&A

Q: What are backtable and index overlay ?

A:

  1. Back to the table : First, query the corresponding primary key value in the secondary index , Then according to the primary key, the query is retrieved from the cluster index .
  2. Index overlay : The secondary index records the primary key column and the secondary index column , If I only query the values of primary key columns and secondary index columns , Then there's no need to go back to the watch .

Q: Why set auto increment primary key id ?

A:

  1. Can uniquely identify a row of data , stay InnoDB The primary key is used when building an index tree .
  2. Self increasing id It's sequential , It can ensure that the data on the index tree is relatively compact , It has higher space utilization and reduces the splitting and merging of data pages , Increase of efficiency .
  3. Usually use mobile phone number 、 ID number as primary key does not guarantee sequentially .
  4. The serial number is generally relatively long , such as 28 position ,32 I'm waiting for you , If it is too long, the secondary index will occupy more space . At the same time, for business needs , Serial number has certain randomness .

Conclusion

This paper mainly through the access to information , Understand why you want to set a business independent auto increment id Used as a primary key , A lot of content is relatively simple , such as InnoDB Of B+ Trees , Page splitting and page merging , The insertion process has not been studied in depth , Interested partners can further study .

At the same time, in addition to setting an auto increment when creating a table id Used as a primary key , Whether partners will encounter a situation in the process of business development : User logoff , Data deletion and so on are all logical deletions , Instead of physically deleting .

The introduction of this article is relatively simple , deficiencies , I hope you can correct me more .

版权声明
本文为[Liu Zhihang]所创,转载请带上原文链接,感谢