Difference between clustered index and non-clustered index (secondary index) in InnoDB.

The difference between clustered indexes and non-clustered indexes is: 1. Clustered index stores data rows in the index structure, which is suitable for querying by primary key and range. 2. The nonclustered index stores index key values ​​and pointers to data rows, and is suitable for non-primary key column queries.

introduction

When exploring the mystery of InnoDB storage engine, indexing is undoubtedly a peak we must overcome. Today, we will dig into the differences between clustered indexes and non-clustered indexes (Non-Clustered Index, also known as secondary indexes, second-level indexes). This is not only a technological exploration, but also a collision of ideas about database performance optimization. By reading this article, you will master the core differences between these two indexes and be able to better design and optimize your database structure.

Review of basic knowledge

In InnoDB, indexing is the key to database performance optimization. Indexes are like library bibliography, helping us quickly find the information we need. Clustered indexes and non-clustered indexes are two different index types, and their design concepts and usage scenarios have their own advantages.

The basic concept of clustered indexing is to store data rows directly in the index structure, which means that the index and data are closely linked. A nonclustered index is different, it is just a pointer to a row of data, similar to a bibliography card in a library, pointing to an actual book.

Core concept or function analysis

Definition and function of clustered index

The definition of clustered indexes is simple and powerful: it combines index structures and data rows to form a complete storage structure. In InnoDB, each table has a clustered index, usually a primary key. If no primary key is explicitly defined, InnoDB selects a Unique Index as the clustered index, or in extreme cases, generates a hidden clustered index.

The role of clustered indexes is obvious: it makes query and range query by primary key extremely efficient. Because the data has been sorted by the primary key, the search operation can be performed directly on the index tree without additional search steps.

A simple clustered index example:

 CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    Salary DECIMAL(10, 2)
);

-- Clustered indexes are automatically created on the id field

Definition and function of nonclustered index

Nonclustered indexes are more flexible, which allows us to create indexes on any column of the table. A nonclustered index contains index key values ​​and a pointer to a row of data, not the data itself. This means that nonclustered indexes can have multiple, while clustered indexes can only have one.

The role of non-clustered index is to improve the query performance of non-primary key columns. For example, if we often query information based on employee names, creating a nonclustered index on name field will greatly improve query efficiency.

An example of a nonclustered index:

 CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10, 2),
    INDEX idx_name (name)
);

-- The nonclustered index idx_name is created on the name field

How it works

The working principle of clustered indexing is to store data through a B-tree structure, and the indexes and data rows are physically stored continuously. This means that when we do range queries, we can traverse directly on the index tree, avoiding additional I/O operations.

The working principle of nonclustered indexes is more complex. It first looks for matching index key values ​​on the index tree, and then jumps to the actual data row through the pointer. This method adds an I/O operation, but is still very efficient for non-primary key queries.

A deep understanding of the working principles of these two indexes can help us better design database structures and optimize query performance.

Example of usage

Basic usage of clustered indexes

The most common usage of clustered indexes is to query by primary keys. Suppose we are looking for employee information with ID 100:

 SELECT * FROM employees WHERE id = 100;

This will look up directly on the clustered index, which is very efficient.

Basic usage of nonclustered indexes

The basic usage of nonclustered indexes is to query through index fields. For example, we want to find an employee named "John Doe":

 SELECT * FROM employees WHERE name = 'John Doe';

This will first look for the matching name value on idx_name index and then find the actual data row through the pointer.

Advanced Usage

Advanced usage of clustered indexes includes scope query and sorting. For example, we want to find employees with salary between 5,000 and 10,000:

 SELECT * FROM employees WHERE salary BETWEEN 5000 AND 10000 ORDER BY id;

This will utilize the sorting characteristics of clustered indexes to improve query efficiency.

Advanced usage of nonclustered indexes includes combination indexes and overwrite indexes. For example, we create a composite index on name and salary fields:

 CREATE INDEX idx_name_salary ON employees (name, salary);

This will allow us to make efficient queries by name and salary:

 SELECT * FROM employees WHERE name = 'John Doe' AND salary > 5000;

Common Errors and Debugging Tips

Common errors when using indexes include:

• Inappropriate index column selection results in poor query performance.
• Overuse of indexes increases maintenance costs and overhead of insert/update operations.

Debugging skills include:

• Use EXPLAIN statement to analyze query plans and understand the usage of indexes.
• Regularly monitor and adjust the index to ensure it remains valid.

Performance optimization and best practices

In practical applications, optimizing indexing is the key to improving database performance. Clustered indexes and non-clustered indexes have their own advantages and disadvantages, and we need to choose according to our specific business needs.

The advantage of clustered indexes is their efficient range query and sorting capabilities, but the disadvantage is that there can only be one clustered index, and improper selection may lead to performance bottlenecks. The advantage of nonclustered indexes is their flexibility and can be created on any column, but the disadvantage is that additional I/O operations are added that may affect query performance.

Best practices include:

• Select the appropriate primary key as the clustered index, usually the auto-increment ID or UUID.
• Create nonclustered indexes on frequently queried columns, but avoid over-index.
• Maintain and optimize the index regularly to ensure it remains valid.

By deeply understanding the differences between clustered and nonclustered indexes, we can better design and optimize database structures and improve query performance. This is not only a technological exploration, but also a collision of ideas about database performance optimization. I hope this article can bring you new inspiration and thinking.

The above is the detailed content of Difference between clustered index and non-clustered index (secondary index) in InnoDB.. For more information, please follow other related articles on the PHP Chinese website!