How to Design Effective MySQL Indexes for Service Log Queries

1. Create Indexes Based on Search Conditions

After defining the service_log table structure, we add indexes that match the most common query patterns.

CREATE TABLE `service_log` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
  `service_type` smallint NOT NULL DEFAULT -1 COMMENT 'Interface type',
  `service_name` varchar(30) DEFAULT '' COMMENT 'Interface name',
  `service_method` tinyint NOT NULL DEFAULT -1 COMMENT 'Method 1-HTTP 2-TCP',
  `serial_no` int DEFAULT -1 COMMENT 'Message sequence',
  `service_caller` tinyint DEFAULT -1 COMMENT 'Caller',
  `service_receiver` tinyint DEFAULT -1 COMMENT 'Receiver',
  `status` tinyint DEFAULT 10 COMMENT 'Status 10-success 20-failure',
  `error_message` varchar(200) DEFAULT '' COMMENT 'Error info',
  `message` varchar(1000) DEFAULT '' COMMENT 'Message content',
  `create_user` varchar(50) DEFAULT '' COMMENT 'Creator',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
  `update_user` varchar(50) DEFAULT '' COMMENT 'Updater',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Update time',
  `is_delete` tinyint NOT NULL DEFAULT 0 COMMENT 'Delete flag',
  `ts` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Timestamp',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Interface call log';

Search by interface name

Search by status (success/failure)

Search by interface name and status

Search by message sequence

Search by a time range on creation time

Search by message content (full‑text)

Indexes are the most effective way to improve query performance because they reduce the amount of data scanned and turn random I/O into sequential I/O.

Keep Index Columns Small

Choosing columns with a small data type reduces index size, allowing more rows per data page and lowering I/O cost. Integer columns are generally faster to compare than strings, so we index service_type instead of service_name:

ALTER TABLE service_log ADD INDEX index_service_type(`service_type`);

We also add an index for the serial_no column:

ALTER TABLE service_log ADD INDEX index_serial_no(`serial_no`);

And an index for the status column:

ALTER TABLE service_log ADD INDEX index_status(`status`);

Composite Index for Interface Type + Status

For queries that filter by both service_type and status, we replace the single‑column index on service_type with a composite index:

-- Drop the old single‑column index
ALTER TABLE service_log DROP INDEX index_service_type;
-- Add composite index
ALTER TABLE service_log ADD INDEX index_service_type_status(`service_type`, `status`);

When creating a composite index, place the column with the lowest cardinality first to minimize the number of rows examined.

Low‑Cardinality Columns

The status column is mostly "success" (value 10). Indexing it for the common case can cause many unnecessary back‑table lookups. For queries that need only successful rows we can force a full table scan with IGNORE INDEX:

SELECT * FROM service_log IGNORE INDEX(index_status) WHERE status = 10;

Time‑Range Index

A simple index on create_time speeds up both exact and range queries:

ALTER TABLE service_log ADD INDEX index_create_time(`create_time`);

Full‑Text Index for Message Content

To search keywords inside the message column we create a FULLTEXT index:

ALTER TABLE service_log ADD FULLTEXT fulltext_message(`message`);
-- Example query
SELECT * FROM service_log WHERE MATCH(message) AGAINST('123456');

Final Table DDL with All Indexes

CREATE TABLE `service_log` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
  `service_type` smallint NOT NULL DEFAULT -1 COMMENT 'Interface type',
  `service_name` varchar(30) DEFAULT '' COMMENT 'Interface name',
  `service_method` tinyint NOT NULL DEFAULT -1 COMMENT 'Method 1-HTTP 2-TCP',
  `serial_no` int DEFAULT -1 COMMENT 'Message sequence',
  `service_caller` tinyint DEFAULT -1 COMMENT 'Caller',
  `service_receiver` tinyint DEFAULT -1 COMMENT 'Receiver',
  `status` tinyint DEFAULT 10 COMMENT 'Status 10-success 20-failure',
  `error_message` varchar(200) DEFAULT '' COMMENT 'Error info',
  `message` varchar(1000) DEFAULT '' COMMENT 'Message content',
  `create_user` varchar(50) DEFAULT '' COMMENT 'Creator',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation time',
  `update_user` varchar(50) DEFAULT '' COMMENT 'Updater',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Update time',
  `is_delete` tinyint NOT NULL DEFAULT 0 COMMENT 'Delete flag',
  `ts` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Timestamp',
  PRIMARY KEY (`id`),
  INDEX index_serial_no(`serial_no`),
  INDEX index_status(`status`),
  INDEX index_create_time(`create_time`),
  INDEX index_service_type_status(`service_type`, `status`),
  FULLTEXT fulltext_message(`message`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Interface call log';

2. Index Fundamentals You Must Know

Unless otherwise specified, "index" refers to a B+ Tree index.

B+ Tree stores all data in leaf nodes and links them with a doubly‑linked list.

Clustered Index

A clustered index stores rows and their primary key together in the same data pages, making range scans fast. In InnoDB the primary key is the clustered index; a table can have only one.

CREATE TABLE demo (
  c1 INT,
  c2 INT,
  c3 CHAR(1),
  PRIMARY KEY (c1)
) ENGINE=InnoDB;

Data pages are 16KB; each page contains a minimum record (Infimum) and a maximum record (Supremum). Leaf nodes hold full rows, while non‑leaf nodes store only the key and a pointer to the child page.

Secondary Index

Secondary (non‑clustered) indexes store only the indexed columns and the primary key. Queries that cannot be satisfied by the index alone need a back‑table lookup.

Document‑Based Partitioning

Each partition maintains its own local index. Example: a car‑sales site partitions by document ID and creates a secondary index on the color field.

Keyword‑Based Partitioning (Global Index)

Indexes are built across partitions based on keyword ranges, allowing queries to be routed only to the relevant partition.

Covering Index

A covering index contains all columns needed by a query, eliminating the need to read the table rows.

When a covering index is used, the EXPLAIN output shows Using index in the Extra column.

Adaptive Hash Index

InnoDB can automatically create an in‑memory hash index on hot B+ Tree entries to speed up point lookups.

References

《数据密集型应用系统设计》: Chapters 3 & 6

《高性能 MySQL 第四版》: Chapter 7

《MySQL 是怎样运行的》: Chapters 4‑7

InnoDB Full‑Text Indexes (14.6.2.4)