MySQL Index Optimization: Table Lookup and Index Optimization

Table Lookup Query Explained

Basic Concepts

Indexes in InnoDB storage engine are divided into two types:

  1. Clustered Index: Leaf nodes store complete row record data
  2. Secondary Index: Leaf nodes only store index column values and corresponding record primary key values

Table Lookup Mechanism

Table lookup query refers to when using secondary index for queries, requiring two index tree accesses:

  1. First time: Find primary key value through secondary index
  2. Second time: Use primary key value to find complete record in clustered index

Performance Analysis

  • Requires two index lookups
  • May require two disk I/O operations

Covering Index Explained

Concept Definition

Covering index is an index optimization technique. When all columns required by a query are included in the index, index data can be directly used to satisfy the query.

Performance Advantages

  • Reduces I/O operations
  • Improves query speed
  • Lowers resource consumption

Implementation Method

-- Create covering index
ALTER TABLE users ADD INDEX idx_name_age (name, age);

-- Use covering index
SELECT name, age FROM users WHERE name = 'John';

Leftmost Prefix Principle Explained

Principle Description

The leftmost prefix principle stipulates that when using composite indexes, query conditions must start from the leftmost column of the index to effectively utilize the index.

Specific Manifestations

  1. Query starting from leftmost column: Effectively uses index
  2. Skipping middle column becomes invalid: Can only use partial index
  3. Completely invalid without leftmost column: Index cannot be used

Special Cases

  1. Columns after range query become invalid
  2. Optimizer automatically adjusts

MySQL LIKE Query and Index Usage

Index Usage Situations

  1. ‘%name%’: Index does not work
  2. ‘%name’: Index does not work
  3. ‘name%’: Index can work

Impact of NULL Values on Indexes in MySQL

NULL Value Characteristics

  1. Cannot use ordinary equals (=) operator for comparison, must use IS NULL or IS NOT NULL
  2. Any arithmetic operation containing NULL results in NULL
  3. COUNT() does not count NULL value rows

Best Practices

  1. Try to set columns to NOT NULL and set reasonable default values
  2. NOT NULL columns usually have better query performance