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通用二进制安装

--------------------------------------分割线 --------------------------------------

本文永久更新链接地址：

相关内容