Why does MySQL primary key increase automatically?
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.
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
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!

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics



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.

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 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.

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.

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 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 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.

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.
