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 ~）.
Make a schedule
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 ？
What is a 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
In short ：
Non empty 、 only 、 Change little or no change .
How to add a primary key
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
- 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 .
- 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 .
- Declaration of primary key ,InnoDB The primary key will be used as a cluster index .
- 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
- 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
This is only about InnoDB engine , Please refer to official documents for details , And the introduction is relatively simple .
- 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 .
- 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 .
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 .
When data is inserted ,InnoDB Will make the page 1/16 Free , To insert and update index records in the future .
- Order insert （ In ascending or descending order ）： The remaining index pages will be about 15/16 pack full
- 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 .
Q: What are backtable and index overlay ？
- 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 .
Q: Why set auto increment primary key id ？
- Can uniquely identify a row of data , stay InnoDB The primary key is used when building an index tree .
- 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 .
- Usually use mobile phone number 、 ID number as primary key does not guarantee sequentially .
- 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 .
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 .