Master MySQL EXPLAIN: Decode Execution Plans with Real Examples

Introduction

MySQL's EXPLAIN statement simulates query execution and returns a plan that helps developers analyze and improve SQL performance. By prefixing any SELECT statement with EXPLAIN, you can see how MySQL intends to retrieve data.

Preparation

Two sample tables are created: t_order (orders) and t_customer (customers). The customer_id column in t_order references the primary key of t_customer. Simple test data is inserted into both tables.

CREATE TABLE `t_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `order_no` int(11) DEFAULT NULL COMMENT '订单号',
  `customer_id` int(11) DEFAULT NULL COMMENT '客户id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `t_order` VALUES ('1','1001','1');
INSERT INTO `t_order` VALUES ('2','1002','26');

CREATE TABLE `t_customer` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `customer_name` varchar(255) DEFAULT NULL COMMENT '客户姓名 ',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `t_customer` VALUES ('1','John');
INSERT INTO `t_customer` VALUES ('2','Tom');

Initial Execution Plan

Running EXPLAIN SELECT * FROM t_order produces a table with many columns. The article includes screenshots of the output and a list of column meanings.

Detailed Column Explanation

The following columns are explained one by one:

id : Execution order of SELECT statements. Larger values have higher priority; subqueries receive incremented ids.

select_type : Type of SELECT (e.g., SIMPLE, PRIMARY, UNION, UNION RESULT, SUBQUERY, DERIVED).

table : Table name or alias used. Special values like <unionM,N>, <derivedN>, <subqueryN> indicate derived or union results.

partitions : Partition name if the table is partitioned; otherwise NULL.

type : Join type, ordered from best to worst performance (e.g., system, const, eq_ref, ref, range, index, ALL).

possible_keys : Indexes that could be used.

key : Index actually chosen by the optimizer.

key_len : Number of bytes used from the chosen index (shorter is better).

ref : Columns or constants compared to the index.

rows : Estimated number of rows examined.

filtered : Approximate percentage of rows that satisfy the condition (max 100%).

extra : Additional information such as Using index (covering index), Using temporary (temporary table), Using filesort (external sort).

Indexing Example

Adding an index on customer_id in t_order and then querying by that column shows how possible_keys and key change.

ALTER TABLE `t_order`
ADD INDEX `idx_customer` (`customer_id`) USING BTREE ;

EXPLAIN SELECT * FROM t_order WHERE customer_id = 1;

References

Official MySQL documentation and additional tutorials are listed for further reading.