Brief Summary#
- Index: A data structure that helps storage engines quickly retrieve data, acting as a "directory" of data (similar to a table of contents in a book) based on the design principle of trading space for time.
- Categories:
- By [Data Structure]: B+Tree index, Hash index, Full-text index
- InnoDB engine does not support Hash index.
- The primary key index and secondary indexes are created using B+Tree index by default.
- By [Physical Storage]: Clustered index (primary key index), Secondary index (non-clustered index)
- The leaf nodes of the B+Tree for the primary key index store the actual data, and all complete data records are stored in the leaf nodes of the primary key index's B+Tree.
- The leaf nodes of the B+Tree for the secondary index store the primary key values, not the actual data.
- When querying using a secondary index:
- If the query can be found in the B+Tree of the secondary index, it is called a covering index (only querying the B+Tree of the secondary index).
- If the query cannot be found, it needs to go to the B+Tree of the clustered index to search. This process is called index lookup (first query the B+Tree of the secondary index to get the primary key value, then query the B+Tree of the primary key index for the actual data).
- When querying using a secondary index:
- By [Field Characteristics]: Primary key index, Unique index, Normal index, Prefix index
- Primary key index: An index built on the primary key field. A table can only have one primary key index, and the indexed column values cannot be empty.
- Unique index: An index built on a UNIQUE field. A table can have multiple unique indexes, and the indexed column values must be unique, but can be empty.
- Normal index: An index built on a normal field. It does not require the field to be a primary key or unique.
- Prefix index: An index built on the first few characters of a string-type field, rather than the entire field. It saves index storage space and improves query efficiency.
- Allowed field types: char, varchar, binary, varbinary
- By [Number of Fields]: Single-column index, Composite index
- Single-column index: Built on a single column.
- Composite index: Built on multiple columns.
- By [Data Structure]: B+Tree index, Hash index, Full-text index
- Applicable Scenarios for Index:
- Fields with unique values, such as product codes.
- Fields frequently used in WHERE queries.
- Fields frequently used in GROUP BY and ORDER BY.
- Inapplicable Scenarios for Index (Counterexamples):
- Fields not used in WHERE conditions, GROUP BY, or ORDER BY.
- Fields with a large amount of repeated data (evenly distributed values), such as gender.
- When the table has too few data.
- Fields that are frequently updated.
- Index Optimization:
- Prefix index optimization.
- Covering index optimization.
- It is best to use an auto-increment value for the primary key index.
- Set the index column as NOT NULL.
- Prevent index invalidation.