Selecting and Creating Optimal Indexes for Applications to Accelerate Data Read

What Is a MySQL Index?

An index is a structure that sorts values of one or more columns in a database table. Using indexes accelerates the retrieval of specific data from tables.

This section provides examples to illustrate how to create optimal indexes for database tables.

Precautions

• Avoid implicit conversion. Ensure that the data types of indexed columns match query criteria. Type mismatches trigger implicit conversions, which affects index usage.
• When creating an index on a VARCHAR column, specify an appropriate index length instead of indexing the entire column. Determine the length based on the column's data cardinality and selectivity.
• Avoid left-fuzzy (such as LIKE '%hk') or full-fuzzy queries. These patterns may invalidate indexes and result in full table scans.
• Limit the number of indexes per table to 5 or no more than 20% of the total column count. This prevents excessive indexes from affecting write performance.
• Use an EXPLAIN command to view the SQL execution plan. Ensure that indexes are correctly used, and avoid performance deterioration like "Using File Sort" or "Using Temporary."
• Avoid redundant indexes. For example, if INDEX(a,b,c) already exists, do not create INDEX(a,b).
• For ultra-large tables, create indexes based on query requirements to avoid unnecessary indexes. You can use the Online DDL to create indexes.
• Before deleting unnecessary indexes, fully evaluate the necessity and back up data to prevent accidental loss.

Main Index Types

• Unique index: You are advised to create unique indexes on columns that must contain distinct values. For example, if columns a and b in a table are unique, you can create a unique index on these columns.
• Covering index: Use covering indexes to avoid table lookups and improve query efficiency. A covering index contains all the columns retrieved by a query. Data can be directly fetched from the index.
• Composite index: When creating a composite index, place the column with the highest selectivity in the leftmost position to take advantage of the B-tree's leftmost prefix matching.

Recommended Index Design

Create an order table (cbc_order).

CREATE TABLE cbc_order (
`order_id` VARCHAR(64) NOT NULL,
`order_channel` VARCHAR(32) NOT NULL,
`order_time` VARCHAR(32),
`pay_amount` DOUBLE,
`real_pay` DOUBLE,
`pay_time` VARCHAR(32),
`user_id` VARCHAR(32),
`user_name` VARCHAR(32),
`area_id` VARCHAR(32)
) ENGINE = InnoDB 
DEFAULT CHARACTER SET = utf8mb4;

The following uses the order table cbc_order as an example to illustrate how to design indexes.

• Primary key design

  Add an auto-incrementing ID as the primary key.

  ALTER TABLE cbc_order ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY;

  Standards compliance: It is strongly recommended that each InnoDB table have a primary key. You are advised to select a column with monotonically increasing values as the primary key.

• Composite index design

  Create the following indexes based on query scenarios.

  • INDEX idx_user_order (user_id, order_time): used for user order queries
  • INDEX idx_channel_time (order_channel, order_time): used for channel statistics
  • INDEX idx_area_time (area_id, order_time): used for area analysis
• Index length optimization

  For VARCHAR columns, specify an appropriate index length.

  INDEX idx_user_name (user_name(20)): The index length of the string is 20 characters, which can provide over 90% selectivity.

Index Validity Check Method

The following uses the order table cbc_order as an example to illustrate how to verify index validity.

• Use EXPLAIN to analyze the query plan.

  EXPLAIN SELECT * FROM cbc_order WHERE user_id = '123' AND order_time > '2024-01-01';

  Check whether the key field in the result indicates that the created index is used.

• Perform a performance comparison test.
  1. Record the query time before the index is added.
  2. Execute the same query again after the index is added.
  3. Compare the query execution times to assess the improvement in response time.
• Monitor the index usage.

  Run the SHOW INDEX command in MySQL to view index statistics.

  SHOW INDEX FROM cbc_order;