MySQL EXPLAIN 深度解析：從連接類型到 Extra 欄位實戰讀法

測試環境建表

CREATE TABLE `class` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `i_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `student` (
  `studentId` varchar(11) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`studentId`),
  KEY `i_studentId` (`studentId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `student_class` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `classId` int(11) DEFAULT NULL,
  `studentId` varchar(11) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_studentId` (`studentId`),
  KEY `fk_student_classId` (`classId`),
  CONSTRAINT `fk_student_classId` FOREIGN KEY (`classId`) REFERENCES `class` (`id`),
  CONSTRAINT `fk_studentId` FOREIGN KEY (`studentId`) REFERENCES `student` (`studentId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `teacher` (
  `teacherId` varchar(11) NOT NULL DEFAULT '',
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`teacherId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `teacher_class` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `teacherId` varchar(11) DEFAULT NULL,
  `classId` int(11) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_teacherId` (`teacherId`),
  KEY `fk_teacher_classId` (`classId`),
  CONSTRAINT `fk_teacher_classId` FOREIGN KEY (`classId`) REFERENCES `class` (`id`),
  CONSTRAINT `fk_teacherId` FOREIGN KEY (`teacherId`) REFERENCES `teacher` (`teacherId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

EXPLAIN 輸出欄位詳細說明

連接類型實戰範例

const：主鍵/唯一鍵精確匹配

EXPLAIN SELECT * FROM student WHERE studentId = "100001";
-- type=const, key=PRIMARY, rows=1

ref：非唯一索引或最左前綴匹配

EXPLAIN SELECT * FROM teacher_class A, student_class B 
WHERE A.classId = B.classId AND B.classId = 2;
-- 兩表均 type=ref, key=fk_xxx_classId, rows=1

unique_subquery / index_subquery：IN 子查詢優化

-- unique_subquery：子查詢主鍵/唯一鍵
EXPLAIN SELECT * FROM student_class A 
WHERE A.classId IN (SELECT B.id FROM class B WHERE B.name="04");

-- index_subquery：子查詢非唯一索引
EXPLAIN SELECT * FROM student A 
WHERE A.studentId IN (SELECT B.studentId FROM student_class B WHERE B.classId=1);

range：索引範圍掃描

EXPLAIN SELECT * FROM student_class WHERE id BETWEEN 1 AND 2;
-- type=range, key=PRIMARY

index：全索引掃描（比 ALL 快）

EXPLAIN SELECT count(*) FROM student_class;
-- type=index, key=PRIMARY, Extra=Using index

ALL：全表掃描（最差）

EXPLAIN SELECT * FROM student WHERE name="wangwu";
-- type=ALL, key=NULL, Extra=Using where

Extra 欄位紅綠燈

• Using filesort：需額外排序，優化索引或調整 ORDER BY
• Using temporary：建立臨時表，優化 GROUP BY/ORDER BY 列順序
• Using index：覆蓋索引，無需回表
• Using where：WHERE 過濾，正常

實戰檢查清單

1. 無 ALL、index 類型（除非極小表）
2. key 不為 NULL
3. rows 預估合理
4. Extra 無 Using temporary/Using filesort