MySQL Index

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


Ownership of this post data is guaranteed by blockchain and smart contracts to the creator alone.