MySQL 連接查詢中索引的關鍵作用：從 75 秒優化到 0.07 秒的實戰分析

MySQL 連接查詢中索引的關鍵作用：從 75 秒優化到 0.07 秒的實戰分析

測試場景

• 學生表 student：10,000 行，主鍵 student_id
• 選課表 student_to_class：20,000 行，每生 2 門課，外鍵 student_id 無索引
• 需求：左連接統計每生選課數

初始 SQL 與執行計劃

SELECT a.student_id, student_name, COUNT(*)
FROM student a
LEFT JOIN student_to_class b ON a.student_id = b.student_id
GROUP BY a.student_id;

EXPLAIN 结果：
+----+-------------+-------+------+---------------+------+---------+------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra                                        |
+----+-------------+-------+------+---------------+------+---------+------+-------+----------------------------------------------+
|  1 | SIMPLE      | a     | ALL  | NULL          | NULL | NULL    | NULL | 10649 | Using temporary; Using filesort              |
|  1 | SIMPLE      | b     | ALL  | NULL          | NULL | NULL    | NULL | 20287 | Using where; Using join buffer (Block Nested)|
+----+-------------+-------+------+---------------+------+---------+------+-------+----------------------------------------------+

現象： 兩表均 type=ALL 全表掃描，驅動表 1 萬行 × 被驅動表 2 萬行 = 2 億次比較，耗時 75.467 秒。

優化：給被驅動表連接列加索引

ALTER TABLE student_to_class ADD INDEX idx_student_id (student_id);

優化後執行計劃

EXPLAIN 结果：
+----+-------------+-------+------+---------------+-----------------+---------+------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key             | key_len | ref  | rows | Extra                                        |
+----+-------------+-------+------+---------------+-----------------+---------+------+------+----------------------------------------------+
|  1 | SIMPLE      | a     | ALL  | NULL          | NULL            | NULL    | NULL | 10649| Using temporary; Using filesort              |
|  1 | SIMPLE      | b     | ref  | idx_student_id| idx_student_id  | 5       | func | 1    | Using where; Using index                     |
+----+-------------+-------+------+---------------+-----------------+---------+------+------+----------------------------------------------+

關鍵變化：

• 被驅動表 type: ALL → ref
• key: idx_student_id 生效
• rows: 20287 → 1（每次驅動僅掃 1 行）
• Using index 覆蓋索引，無需回表

效果對比

核心原理：Nested Loop Join (NLJ)

						
						
 
						
 
	
						

						
						

							
上一篇文章： MySQL FEDERATED 引擎實現跨網絡異構資料庫表聯邦查詢與寫入實戰 
下一篇文章： MySQL 入門筆記（一）：服務啟動、登錄方式、配置修改、建庫刪庫與四大存儲引擎詳解 
						
					
				
				

					

						MySQL 綜合教學
						

					
					

						
						

							
							Navicat Premium 實現 MySQL 資料庫備份與還原圖文教程
							
前言
Navicat Premium 是功能強大的圖形化 M			
						
						
						

							
							MySQL 數據類型深度解析：字符串、整數、小數選型避坑指南
							
MySQL 數據類型深度解析：字符串、整數、小數選型避坑指南			
						
						
						

							
							MySQL UTF-8 字符集完整配置指南：my.cnf、服務端、客戶端、應用層全鏈路統一
							
MySQL 字符集亂碼 90% 來源於「鏈路不一致」。本文給			
						
						
						

							
							MS SQL Server Management Studio Express (SSMSE) 安裝圖文教程
							
MS SQL Server Management Studi			
						
						
						

					
				
			
			

				
				
 
	
				

					

						

					
					

						
						MySQL 修改預設字元集為 UTF-8：my.cnf 永久生效與命令列臨時修改
						
						MySQL LAST_QUERY_COST 解讀：優化器成本模型、適用場景與局限性
						
						MySQL 5.7.13 ZIP 免安裝版 Windows 部署與啟動失敗排查實戰
						
						MySQL 高性能字符串分割：數字輔助表 + 集合思維避免循環
						
						Ubuntu MySQL 中文亂碼徹底解決：配置文件持久化實戰
						
						解決 MySQL 登入報錯 ERROR 2002 (HY000)：Can't connect to local MySQL server through socket
						
						MySQL 主從複制詳解（一）：核心原理與架構
						
					
				
			
				

					
欄目導航
					

						
MySQL 入門
SQL 基礎
MySQL 資料庫相關知識
MariaDB 資料庫
MySQL 綜合教學
					
					
				
			
			

		
	
		

	
Copyright © 程式碼之家 All Rights Reserved