MySQL串連查詢中索引的重要性

MySQL串連查詢中索引的重要性

在MySQL中，我們要從多張表中讀取資料時，往往需要用到串連查詢。串連查詢通過兩張表中符合串連關係的欄位來建立兩張表的關聯，通常包括內串連、左外串連、右外串連和全串連。內串連會保留兩張表中共有的那部分記錄，因此最後產生的串連表記錄數最少；全串連會保留兩張表中所有的記錄，因此最後產生的串連表記錄數最多；而左外串連會保留左表的全部記錄，右外串連會保留右表的全部記錄，因此最後產生的串連表記錄數處於內串連和外串連之間。

下面我們以一個學生選課的例子，來分析下左外串連的效能。

首先定義一張學生表（student）：

 

插入學生記錄，共10000條：

然後定義一張學生選課表(student_to_class)：

插入學生選課記錄，每個學生選擇2門課，共20000條記錄：

現在我們要統計每個學生的個人資訊，包括他的姓名和他選擇的課程數，這樣我們需要使用左外串連，具體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;

但是，這個查詢的執行速度非常慢，花費了75.467s，當然這與我在本機，而不是在伺服器上搭建資料庫也有關係，可這樣的查詢效率肯定不能忍受。

 

下面我們來分析一下為什麼這麼慢：

首先用explain查看這個語句的查詢執行計畫，可以看到type都為ALL，即在student表和student_to_class表中都使用的全表掃描，其中student表（a）中掃描了10649行，student_to_class表（b）中掃描了20287行，這樣無疑效率是非常低的。

對此，我們試著給student_to_class表的student_id欄位添加索引：

然後再次執行查詢，發現速度非常快，只有0.077s，改進得非常多。而相應的查詢執行計畫如所示，發現在查詢student_to_class表時使用了索引student_index，使得只需要掃描1行就行了，相當於原來的兩萬分之一，這就是效率改進的關鍵點所在。

因此，當串連查詢時產生的串連表過大時，為了防止查詢次數過多，我們要經常使用索引來減少查詢次數，提高查詢效率。

--------------------------------------分割線 --------------------------------------

Ubuntu 14.04下安裝MySQL

《MySQL權威指南(原書第2版)》清晰中文掃描版 PDF

Ubuntu 14.04 LTS 安裝 LNMP Nginx\PHP5 (PHP-FPM)\MySQL

Ubuntu 14.04下搭建MySQL主從伺服器

Ubuntu 12.04 LTS 構建高可用分布式 MySQL 叢集

Ubuntu 12.04下原始碼安裝MySQL5.6以及Python-MySQLdb

MySQL-5.5.38通用二進位安裝

--------------------------------------分割線 --------------------------------------

本文永久更新連結地址：