Table of Contents
1. What is a clustered index
2. Clustered index and primary key
3. Clustered index advantages and disadvantages
Home Database Mysql Tutorial What are the advantages and disadvantages of MySQL clustered index

What are the advantages and disadvantages of MySQL clustered index

May 27, 2023 pm 09:43 PM
mysql

1. What is a clustered index

Database indexes can be divided into different types from different perspectives, and clustered index is one of them.

Clustered index is Clustered Index in English. Sometimes you may see some people refer to it as clustered index, etc. The opposite is non-clustered index or secondary index.

Clustered index is not a separate index type, but a way of storing data. In MySQL's InnoDB storage engine, the so-called clustered index actually saves the index and data rows in the same B Tree: At this time, the data is placed in the leaf nodes, clustered, clustered, which means Said data rows and corresponding key values ​​exist compactly together.

Suppose I have the following data:

##2ac98广州男3af88北京女4bc80上海女5bg85Chongqing女6bw95 TianjinMale7bw99Haikou女8cc92武汉Male9ck90ShenzhenMale10 cx93ShenzhenMale

Then its clustered index probably looks like this:

What are the advantages and disadvantages of MySQL clustered index

# Then you can see that there are both primary key values ​​(indexes) and data rows on the leaves. There are only primary key values ​​(indexes) on the node.

Think about it, friends, the data in the MySQL table can only be saved in one copy on the disk, and it is impossible to save two copies. Therefore, in a table, there can only be one clustered index, and it is impossible to have one. Multiple.

2. Clustered index and primary key

Some friends are not clear about the relationship between the two, and even equate the two. This is a huge misunderstanding.

In some databases, developers are allowed to freely choose which index to use as a clustered index, but MySQL does not support this feature.

In MySQL, if the table itself has a primary key set, then the primary key is the clustered index; if the table itself does not set a primary key, a unique and non-empty index in the table will be selected as the clustered index. ; If there is no unique non-empty index in the table, the implicit primary key in the table will be automatically selected as the clustered index. Brother Song will introduce you to the implicit primary key of MySQL tables in future articles.

However, generally speaking, it is recommended that you set the primary key for the table yourself, because the implicit primary key is auto-incrementing, and there is a problem with auto-incrementing: there will be a very high auto-increment value. For lock competition issues, the upper bound of the primary key is called hot data. Because all insertion operations require the primary key to be incremented and cannot be repeated, lock competition will occur and performance will decrease.

Based on the above introduction, we can summarize the relationship between clustered index and primary key index in MySQL as follows:

  • Clustered index is not necessarily a primary key index .

  • The primary key index must be a clustered index.

3. Clustered index advantages and disadvantages

Let’s talk about the advantages first:

  • We can combine interrelated data Keep it together. For example, there is a user order table. We can aggregate all data based on User ID Order ID. User IDs may be repeated, but order IDs will not be repeated. In this way, we can aggregate all order data related to a user. Saved together, if you need to query all orders of a user, it will be very fast and only require a small amount of disk IO.

  • No need to return the table, so data access is faster. In a clustered index, both the index and the data are on the same B Tree, so retrieving data from the clustered index is faster than retrieving data from a non-clustered index (non-clustered indexes require table backing).

  • For the first case, if we want to query all the order IDs of this user based on the user ID, then there is no need to go to the leaf node at this time, because the branch node There is the data we need, so we can directly use the characteristics of the covering index to read the required data.

These are some common advantages of clustered indexes. In fact, we should make full use of these advantages in daily table design.

Let’s take a look at the shortcomings:

  • My friends found that the advantage of the clustered index we mentioned earlier is mainly that the clustered index reduces the number of IOs, thereby improving The performance of the database, but some IO-intensive applications may directly load a large enough memory to read all the data into the memory for operation. In this case, the clustered index has no advantage.

  • Random primary keys will cause page splitting problems. If the primary keys are inserted sequentially, it will be relatively more efficient, because in B Tree, you only need to keep appending to the back; but if the primary key If it is a non-sequential insertion, the efficiency will be much lower because page splitting may be involved. Taking the picture above as an example, assuming that each node can save three pieces of data, and now we want to insert a record with a primary key of 4.5, then we need to move the value of the primary key of 5 back, which will cause the node with a primary key of 8 to also Move back. Page splitting results in less efficient data insertion and takes up more storage space.

  • When querying non-clustered index (secondary index), you need to return the table. Because an index is an index tree, and the data is all on the clustered index, so if you use a non-clustered index to search, the leaves of the non-clustered index store the primary key value. Find the primary key value first, and then hold The primary key value is then searched on the clustered index, so that a total of two index trees are queried, which is the table return.

id (primary key) username age address gender
1 ab 99 Shenzhen

The above is the detailed content of What are the advantages and disadvantages of MySQL clustered index. 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

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

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.

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.

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.

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.

Monitor Redis Droplet with Redis Exporter Service Monitor Redis Droplet with Redis Exporter Service Apr 10, 2025 pm 01:36 PM

Effective monitoring of Redis databases is critical to maintaining optimal performance, identifying potential bottlenecks, and ensuring overall system reliability. Redis Exporter Service is a powerful utility designed to monitor Redis databases using Prometheus. This tutorial will guide you through the complete setup and configuration of Redis Exporter Service, ensuring you seamlessly build monitoring solutions. By studying this tutorial, you will achieve fully operational monitoring settings

See all articles