Shulou(Shulou.com)05/31 Report--

This article mainly explains "build a table why also set a self-increasing id", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "why build a table also set up a self-increasing id" bar!

1. Table-building specification

At work, when creating a table, DBA will also review the table creation SQL to see if it conforms to the specification and whether the commonly used fields are indexed.

CREATE TABLE `xxxx` (`id` bigint (20) NOT NULL AUTO_INCREMENT COMMENT 'self-increment primary key', `update_ 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 `timetime` (`create_ time`) USING BTREE KEY `update_ time` (`update_ time`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COMMENT=' Notes'

So in the process of using the serial number, I set a separate field, such as trans_no, but this time I encountered a question: since trans_no is unique, why not just use trans_no as id?

Next, by consulting the relevant information, step by step to understand why?

two。 Primary key

What is the primary key?

MySQL 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)."

It means that when creating a primary key, try to use the MySQL self-increasing primary key instead of using the business-generated value as the primary key.

Characteristics of primary key

In short:

Non-empty, unique, little or no change.

How to add a primary key

You can specify it when create creates the table, or you can add a primary key after the alter statement, but it is officially recommended that you specify it when you create the table.

Why add a primary key

The primary key uniquely identifies this row of data, thus ensuring that only this row of data is manipulated when the update operation is deleted.

Indexes are required, and each InnoDB table has a special index, the clustered index, to store row data. Typically, a clustered index is synonymous with a primary key.

Declare the primary key, and InnoDB uses the primary key as a clustered index.

When undeclared, the first index, NOT NULL, is found in the location of all the key columns of UNIQUE and used as a clustered index

If a suitable UNIQUE index is not declared and cannot be found, a hidden clustered index GEN_CLUST_INDEX is generated internally, and the hidden row ID is 6 bytes and monotonously incremented.

3. Indexes

Only the InnoDB engine is introduced here. You can refer to the official documentation for details, and the introduction is relatively simple.

Classification of indexes

Clustered index: table storage is organized based on the values of primary key columns to speed up queries and sorting involving primary key columns. The clustering index is also introduced when introducing the primary key.

Secondary index: can also be called a secondary index, in which the corresponding primary key columns and secondary index columns are recorded. When searching according to the secondary index, we will first get the corresponding primary key column according to the secondary index, and then search in the clustered index according to the primary key. It is generally not recommended that the primary key be long, because the secondary index uses more space if the primary key is too long.

"add:

Back to the table: first query the corresponding primary key value in the secondary index, and then retrieve the query in the clustered index according to the primary key.

Index coverage: the secondary index records the primary key column and the secondary index column. If I only query the values of the primary key column and the secondary index column, then I don't need to go back to the table. "

Physical structure of the index

The B+ data structure used by InnoDB builds a B+ tree according to the cluster index value (primary key / UNQIUE/ or self-generated). The leaf node stores the row record data, so each leaf node can also be called a data page. The default size of each data page is 16k and customization is supported.

Data insertion

When the data is inserted, InnoDB makes page 1 prime 16 free in case index records are inserted and updated in the future.

Sequential insert (ascending or descending): fills the rest of the index page with approximately 15Universe 16

Random insertion: only use 1max 2 to 15max 16 of the capacity

In random insertion, it will be moved and paged frequently, resulting in a large number of fragments and making the index tree not compact enough. Using the sequential insertion method, the data is more compact and has higher space utilization.

4. Summary

Quan A

Q: what are table returns and index overrides?

A:

Back to the table: first query the corresponding primary key value in the secondary index, and then retrieve the query in the clustered index according to the primary key.

Index coverage: the secondary index records the primary key column and the secondary index column. If I only query the values of the primary key column and the secondary index column, then I don't need to go back to the table.

Q: why set the self-incrementing primary key id?

A:

A row of data can be uniquely identified, and the primary key is used when InnoDB builds the index tree.

The self-increasing id is sequential, which can ensure that the data on the index tree is more compact, have higher space utilization, and reduce the splitting and merging of data pages to improve efficiency.

Generally, the use of mobile phone number and ID card number as the primary key does not guarantee the sequence.

The serial number is generally relatively long, such as 28 bits, 32 bits, etc., if it is too long, the secondary index will take up more space. At the same time, in order to meet the business requirements, the serial number has a certain degree of randomness.

Thank you for your reading, the above is the content of "Why do you still set up a self-increasing id"? after the study of this article, I believe you have a deeper understanding of why you still set up a self-increasing id, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.