Home Database Mysql Tutorial Why does MySQL primary key increase automatically?

Why does MySQL primary key increase automatically?

May 07, 2019 pm 03:27 PM
mysql

The reasons for mysql primary key auto-increment: 1. The data record itself is stored on the leaf node of the primary index; 2. MySQL will insert it into the appropriate node and location based on its primary key; 3. The table uses auto-increment Primary key, each time a new record is inserted, the record will be added sequentially to the subsequent position of the current index node.

Why does MySQL primary key increase automatically?

The reason why MySQL uses auto-incrementing primary keys is because InnoDB tables are very convenient to use with it and the efficiency is significantly improved.

Recommended courses: MySQL Tutorial.

Features of InnoDB engine table

1. InnoDB engine table is an index-organized table (IOT) based on B-tree

About B-tree

Why does MySQL primary key increase automatically?

Characteristics of B tree:

All keywords appear in the linked list of leaf nodes (dense index), and the keywords in the linked list happen to be Sequential;

It is impossible to hit on non-leaf nodes;

Non-leaf nodes are equivalent to the index of leaf nodes (sparse index), and leaf nodes are equivalent to storage (keywords) Data layer of data;

2. If we define the primary key (PRIMARY KEY), then InnoDB will select the primary key as the clustered index. If the primary key is not explicitly defined, InnoDB will select the first one that does not contain NULL. The unique index of the value is used as the primary key index. If there is no such unique index, InnoDB will choose the built-in 6-byte long ROWID as the implicit clustered index (ROWID is incremented as the row record is written and the primary key is incremented. This ROWID is not like ORACLE's ROWID can be quoted, which is implicit).

3. The data record itself is stored on the leaf node of the main index (a B Tree). This requires that each data record in the same leaf node (the size of one memory page or disk page) is stored in primary key order, so whenever a new record is inserted, MySQL will insert it into the appropriate node based on its primary key. and position, if the page reaches the loading factor (InnoDB default is 15/16), a new page (node) will be opened

4. If the table uses an auto-increasing primary key, then every time a new record is inserted, the record It will be added sequentially to the subsequent position of the current index node. When a page is full, a new page will be automatically opened

5. If a non-auto-increasing primary key is used (if the ID number or student number, etc.) , since the value of the primary key inserted each time is approximately random, each new record must be inserted somewhere in the middle of the existing index page. At this time, MySQL has to move the data in order to insert the new record into the appropriate location, and even The target page may have been written back to the disk and cleared from the cache. At this time, it needs to be read back from the disk, which adds a lot of overhead. At the same time, frequent moving and paging operations cause a lot of fragments, resulting in a less compact image. The index structure requires OPTIMIZE TABLE to rebuild the table and optimize the filling page.

To sum up, if the data writing order of the InnoDB table can be consistent with the order of the leaf nodes of the B-tree index, the access efficiency will be the highest at this time, which is the access efficiency in the following situations. Highest:

Use an auto-incrementing column (INT/BIGINT type) as the primary key. At this time, the writing order is auto-incrementing, which is consistent with the splitting order of B number of leaf nodes;

This table does not specify The auto-increment column is used as the primary key, and there is no unique index that can be selected as the primary key (the above condition). At this time, InnoDB will choose the built-in ROWID as the primary key, and the writing order is consistent with the ROWID growth order;

In addition, if an InnoDB table does not display a primary key, there is a unique index that can be selected as the primary key, but the unique index may not be in an incremental relationship (such as string, UUID, multi-field joint unique index) , the access efficiency of the table will be relatively poor.

The above is the detailed content of Why does MySQL primary key increase automatically?. For more information, please follow other related articles on the PHP Chinese website!

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
1 months ago By 尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Best Graphic Settings
1 months ago By 尊渡假赌尊渡假赌尊渡假赌
Will R.E.P.O. Have Crossplay?
1 months ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

MySQL: Simple Concepts for Easy Learning MySQL: Simple Concepts for Easy Learning Apr 10, 2025 am 09:29 AM

MySQL is an open source relational database management system. 1) Create database and tables: Use the CREATEDATABASE and CREATETABLE commands. 2) Basic operations: INSERT, UPDATE, DELETE and SELECT. 3) Advanced operations: JOIN, subquery and transaction processing. 4) Debugging skills: Check syntax, data type and permissions. 5) Optimization suggestions: Use indexes, avoid SELECT* and use transactions.

How to open phpmyadmin How to open phpmyadmin Apr 10, 2025 pm 10:51 PM

You can open phpMyAdmin through the following steps: 1. Log in to the website control panel; 2. Find and click the phpMyAdmin icon; 3. Enter MySQL credentials; 4. Click "Login".

MySQL: An Introduction to the World's Most Popular Database MySQL: An Introduction to the World's Most Popular Database Apr 12, 2025 am 12:18 AM

MySQL is an open source relational database management system, mainly used to store and retrieve data quickly and reliably. Its working principle includes client requests, query resolution, execution of queries and return results. Examples of usage include creating tables, inserting and querying data, and advanced features such as JOIN operations. Common errors involve SQL syntax, data types, and permissions, and optimization suggestions include the use of indexes, optimized queries, and partitioning of tables.

Why Use MySQL? Benefits and Advantages Why Use MySQL? Benefits and Advantages Apr 12, 2025 am 12:17 AM

MySQL is chosen for its performance, reliability, ease of use, and community support. 1.MySQL provides efficient data storage and retrieval functions, supporting multiple data types and advanced query operations. 2. Adopt client-server architecture and multiple storage engines to support transaction and query optimization. 3. Easy to use, supports a variety of operating systems and programming languages. 4. Have strong community support and provide rich resources and solutions.

How to use single threaded redis How to use single threaded redis Apr 10, 2025 pm 07:12 PM

Redis uses a single threaded architecture to provide high performance, simplicity, and consistency. It utilizes I/O multiplexing, event loops, non-blocking I/O, and shared memory to improve concurrency, but with limitations of concurrency limitations, single point of failure, and unsuitable for write-intensive workloads.

MySQL's Place: Databases and Programming MySQL's Place: Databases and Programming Apr 13, 2025 am 12:18 AM

MySQL's position in databases and programming is very important. It is an open source relational database management system that is widely used in various application scenarios. 1) MySQL provides efficient data storage, organization and retrieval functions, supporting Web, mobile and enterprise-level systems. 2) It uses a client-server architecture, supports multiple storage engines and index optimization. 3) Basic usages include creating tables and inserting data, and advanced usages involve multi-table JOINs and complex queries. 4) Frequently asked questions such as SQL syntax errors and performance issues can be debugged through the EXPLAIN command and slow query log. 5) Performance optimization methods include rational use of indexes, optimized query and use of caches. Best practices include using transactions and PreparedStatemen

MySQL and SQL: Essential Skills for Developers MySQL and SQL: Essential Skills for Developers Apr 10, 2025 am 09:30 AM

MySQL and SQL are essential skills for developers. 1.MySQL is an open source relational database management system, and SQL is the standard language used to manage and operate databases. 2.MySQL supports multiple storage engines through efficient data storage and retrieval functions, and SQL completes complex data operations through simple statements. 3. Examples of usage include basic queries and advanced queries, such as filtering and sorting by condition. 4. Common errors include syntax errors and performance issues, which can be optimized by checking SQL statements and using EXPLAIN commands. 5. Performance optimization techniques include using indexes, avoiding full table scanning, optimizing JOIN operations and improving code readability.

How to build a SQL database How to build a SQL database Apr 09, 2025 pm 04:24 PM

Building an SQL database involves 10 steps: selecting DBMS; installing DBMS; creating a database; creating a table; inserting data; retrieving data; updating data; deleting data; managing users; backing up the database.

See all articles