Why MySQL Sometimes Skips Indexes: A Deep Dive into B+Tree Structures and Optimizer Decisions

Index Data Structures

Database indexes are specialized data structures designed to speed up row retrieval; common structures include hash tables, B‑trees, and the most widely used B+Tree. MySQL stores both primary (clustered) and secondary (non‑clustered) indexes as B+Trees.

InnoDB and MyISAM Indexes

In InnoDB, the primary key forms the clustered index, while secondary indexes store the primary key value for each entry. MyISAM uses a similar structure but enforces uniqueness on the clustered index.

Composite Index Column Order

For a single‑column index, column order is irrelevant. With composite indexes, the order of columns matters: the optimizer sorts rows left‑to‑right, applying the first column’s ordering before considering the next.

Environment Setup

MySQL 5.7.30 (InnoDB) is used. The table tbl_customer_recharge_record is created with a primary key on id and secondary indexes on customer_id ( idx_c_id) and a composite index on customer_name, recharge_type, recharge_time ( idx_name_type_time).

DROP TABLE IF EXISTS tbl_customer_recharge_record;
CREATE TABLE tbl_customer_recharge_record (
  id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  customer_id INT(11) NOT NULL COMMENT '顾客ID',
  customer_name VARCHAR(50) NOT NULL COMMENT '顾客姓名',
  recharge_type TINYINT(2) NOT NULL COMMENT '充值方式 1:支付宝, 2:微信,3:QQ,4:京东,5:银联,6:信用卡,7:其他',
  recharge_amount DECIMAL(15,2) NOT NULL COMMENT '充值金额, 单位元',
  recharge_time DATETIME NOT NULL COMMENT '充值时间',
  remark VARCHAR(500) NOT NULL DEFAULT 'remark' COMMENT '备注',
  PRIMARY KEY (id),
  KEY idx_c_id (customer_id),
  KEY idx_name_type_time (customer_name, recharge_type, recharge_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='顾客充值记录表';

INSERT INTO tbl_customer_recharge_record(customer_id, customer_name, recharge_type, recharge_amount, recharge_time) VALUES
(1,'李小龙',1,10000,NOW()),
(2,'李连杰',2,20000,NOW()),
(1,'李小龙',2,10000,NOW()),
(1,'李小龙',3,10000,NOW()),
(2,'李连杰',7,20000,NOW()),
(3,'成龙',3,15000,NOW()),
(1,'李小龙',1,10000,NOW());

Why the Optimizer May Prefer Full‑Table Scans

The optimizer evaluates possible execution plans and selects the one with the lowest estimated cost. When the cost of scanning the whole table is lower than using an index, it chooses a full‑table scan.

EXPLAIN SELECT * FROM tbl_customer_recharge_record WHERE customer_id = 2;

Even though idx_c_id could be used, the optimizer decides that scanning all 7 rows costs less (cost 2.4 vs 2.8 for the index).

MySQL 5.6 introduced OPTIMIZER_TRACE, which can be enabled with SET optimizer_trace='enabled=on'; and inspected via SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;. The trace shows detailed steps, such as join preparation, condition processing, and cost estimation.

Scenarios Where Indexes Are Ignored

LIKE patterns with a leading % : WHERE name LIKE '%abc' cannot use an index.

EXPLAIN SELECT * FROM tbl_customer_recharge_record WHERE customer_name LIKE '%杰';

OR conditions where only one side can use an index : if any side forces a full scan, the whole query falls back to a table scan.

EXPLAIN SELECT * FROM tbl_customer_recharge_record WHERE customer_id = 1 OR recharge_type = 5;

Composite indexes without leftmost column matching : the optimizer can only use the index if the query predicates start with the first indexed column.

EXPLAIN SELECT * FROM tbl_customer_recharge_record WHERE recharge_type = 2 AND customer_id = 1;

Implicit type conversion : comparing a CHAR column with a numeric constant forces conversion and disables index usage.

EXPLAIN SELECT * FROM tbl_char WHERE type = 2;

Negation operators ( <>, !=, NOT IN, NOT EXISTS) also trigger full scans.

Functions or arithmetic on the indexed column’s left side prevent index usage, while the same operation on the right side is safe.

Recommendations

When using composite indexes, always place the first indexed column at the beginning of the WHERE clause and keep the column order unchanged.

Write predicates with the column on the left side and a constant of matching type on the right; avoid functions or calculations on the indexed column.

Define columns as NOT NULL with sensible defaults to eliminate NULL‑related pitfalls.