MySQL Index

An index is a data structure that satisfies a specific query algorithm, and these data structures point to data in a certain way to achieve efficient data retrieval.

Why Need an Index

To improve the lookup speed of the database.

Pros and Cons of Using an Index

Pros:

• A unique index guarantees the uniqueness of data in each row of a database table.
• An index can accelerate data query speed and reduce query time.

Cons:

• Creating and maintaining an index consumes time.
• Indices occupy physical space; in addition to the data table occupying data space, each index occupies a certain amount of physical space.
• When performing insert, delete, or update operations on data within the table, the index must also be dynamically maintained.

Various Types of Indexes

• Primary Key Index: Data columns are not allowed to have duplicates and cannot be NULL; only one primary key is allowed per table.
• Unique Index: Data columns are not allowed to have duplicates but allow NULL values; multiple columns in a table can have unique indexes created.
• Normal Index: A basic index type with no uniqueness restrictions and allows NULL values.
• Full-text Index: A key technology currently used by search engines, which performs tokenization and searching on text content.
• Covering Index: Query columns must be covered by the created index so that data rows do not need to be read.
• Composite Index: Multiple column values form an index, used for combined searches, offering better efficiency than index merging.

Reasons for Using B+ Trees as Database Indexes

Well-suited for disk storage, capable of fully utilizing spatial locality and disk prefetching.
Very low tree height, allowing the storage of large amounts of data.
The index itself occupies very little memory.
Can effectively support point queries, range queries, and ordered queries.