当前位置：极客教程 > MySQL > MySQL 问答 > MySQL如何让JOIN查询使用索引？

MySQL如何让JOIN查询使用索引？

MySQL如何让JOIN查询使用索引？

在MySQL中，JOIN是一种非常常用的查询方式。通常情况下，MySQL会为表中的列建立索引以提高查询效率，但在JOIN查询中，默认情况下可能不会使用索引，导致查询效率变得极低。本文将介绍如何让JOIN查询使用索引以提高查询效率。

阅读更多：MySQL 教程

JOIN查询和索引

在MySQL中，JOIN是将两个或多个表按照指定的条件进行连接的过程。JOIN查询是一种比较复杂的查询方式，需要对多个表的数据进行关联，并且会使用到WHERE子句进行过滤。

MySQL中建立索引的方式很多，最常见的是B树索引。B树索引能够快速定位需要查询的数据，提高查询效率。但是，在JOIN查询中，如果不恰当地使用索引，可能会导致查询效率下降。

JOIN查询的性能问题

在JOIN查询中，默认情况下可能会使用到全表扫描，而不是索引。这是因为MySQL的优化器会根据查询的数据和表结构来决定查询的执行计划，如果优化器认为全表扫描效率更高，就会采用全表扫描的方式进行查询。这种情况下，即使已经建立了索引，也无法提高查询效率。

下面以一个例子说明JOIN查询的性能问题。

假设有两张表：学生表和成绩表。学生表中包含学生信息，成绩表中包含学生的考试成绩。它们的结构分别如下：

CREATE TABLE students (
  id INT NOT NULL PRIMARY KEY,
  name VARCHAR(10) NOT NULL
);

CREATE TABLE scores (
  id INT NOT NULL PRIMARY KEY,
  student_id INT NOT NULL,
  subject VARCHAR(10) NOT NULL,
  score INT NOT NULL,
  KEY student_id_idx (student_id)
);
Mysql

现在要查询每个学生的所有成绩，可以使用如下的SQL语句：

SELECT students.name, scores.subject, scores.score
FROM students
JOIN scores ON students.id = scores.student_id;
Mysql

这个SQL语句在两张表中进行JOIN操作，并没有指定使用哪个索引，因此MySQL会根据自己的优化规则来选择执行计划。如果执行计划中使用了全表扫描，查询将非常慢，尤其是在数据量大的时候。

让JOIN查询使用索引

为了让JOIN查询使用索引，有一些技巧可以使用。

1. 使用EXPLAIN查看执行计划

在优化查询的时候，首先需要使用EXPLAIN命令来查看查询的执行计划。EXPLAIN命令可以显示查询语句的执行计划，包括使用到的索引、使用的连接方式、处理行数等信息。

对于上面的查询语句，使用EXPLAIN命令可以得到如下的执行计划：

EXPLAIN SELECT students.name, scores.subject, scores.score
FROM students
JOIN scores ON students.id = scores.student_id;

+----+-------------+--------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| 1  | SIMPLE      | scores | NULL       | ALL  | student_id_idx| NULL         | NULL    | NULL  | 100  | 100.00   | NULL  |
| 1  | SIMPLE      | students| NULL       | ref  | PRIMARY       | PRIMARY      | 4       | scores.student_id | 1    | 100.00  | NULL  |
+----+-------------+--------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
Mysql

可以看到，上述执行计划中，有一个ALL类型的索引，表示查询将会使用全表扫描。这种情况下，JOIN查询的性能将是非常低下的。

2. 对JOIN的条件列建立索引

如果JOIN的条件列没有索引，MySQL则无法使用索引进行JOIN操作。因此，最简单的方法是为JOIN的条件列建立索引。例如，在上面的例子中，可以为scores表的student_id列建立索引，因为它是连接两张表的关键列。

ALTER TABLE scores ADD INDEX student_id_idx (student_id);
Mysql

建立了索引后，再次运行该查询，可以得到不同的执行计划：

EXPLAIN SELECT students.name, scores.subject, scores.score
FROM students
JOIN scores ON students.id = scores.student_id;

+----+-------------+--------+------------+-------+---------------+----------------+---------+-------------------+------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys | key            | key_len | ref               | rows | filtered | Extra       |
+----+-------------+--------+------------+-------+---------------+----------------+---------+-------------------+------+----------+-------------+
| 1  | SIMPLE      | students| NULL       | index | PRIMARY       | PRIMARY        | 4       | NULL              | 10   | 100.00   | Using index |
| 1  | SIMPLE      | scores | NULL       | ref   | student_id_idx| student_id_idx | 5       | dbname.students.id |  2   | 100.00  | Using index |
+----+-------------+--------+------------+-------+---------------+----------------+---------+-------------------+------+----------+-------------+
Mysql

这次执行计划中，JOIN查询将会使用索引了。

3. 小表驱动大表

在执行JOIN查询时，如果其中有一个表很小，而另一个表很大，可以把小表放在连接的第一位。这样可以使得查询先处理小表，再处理大表，从而减少处理的行数。

例如，在上面的例子中，如果学生表中的数据很少，而成绩表中的数据很多，则可以将学生表放在JOIN操作的第一位。

SELECT students.name, scores.subject, scores.score
FROM scores
JOIN students ON students.id = scores.student_id;
Mysql

这个查询将执行的效率将会比上面的查询更快。

4. 使用FORCE INDEX强制使用索引

在一些情况下，MySQL的优化器可能会忽略已经建立的索引，导致JOIN操作使用了全表扫描。此时可以使用FORCE INDEX关键字强制指定使用索引进行查询。

例如，在上面的例子中，可以使用以下的SQL语句强制使用student_id_idx索引：

SELECT students.name, scores.subject, scores.score
FROM students
FORCE INDEX (student_id_idx)
JOIN scores ON students.id = scores.student_id;
Mysql

使用FORCE INDEX关键字需要注意，因为它可能会导致查询性能下降。因此，使用该方法需要结合具体情况进行判断。

总结

在MySQL中，JOIN查询是一种非常常用的查询方式。为了提高JOIN查询的性能，需要注意如下几点：

  • 使用EXPLAIN命令查看执行计划；
  • 为JOIN的条件列建立索引；
  • 将小表放在连接的第一位；
  • 使用FORCE INDEX关键字强制指定使用索引进行查询。

通过以上的优化，可以大大提高JOIN查询的效率。

Python教程

Python 教程

Python 教程
Tkinter 教程

Tkinter 教程
Pandas 教程

Pandas 教程
NumPy 教程

NumPy 教程
Flask 教程

Flask 教程
Django 教程

Django 教程
PySpark 教程

PySpark 教程
wxPython 教程

wxPython 教程
SymPy 教程

SymPy 教程
Seaborn 教程

Seaborn 教程
SciPy 教程

SciPy 教程
RxPY 教程

RxPY 教程
Pycharm 教程

Pycharm 教程
Pygame 教程

Pygame 教程
PyGTK 教程

PyGTK 教程
PyQt 教程

PyQt 教程
PyQt5 教程

PyQt5 教程
PyTorch 教程

PyTorch 教程
Matplotlib 教程

Matplotlib 教程
Web2py 教程

Web2py 教程
BeautifulSoup 教程

BeautifulSoup 教程

Java教程

Java 教程

Java 教程

Web教程

HTML 教程

HTML 教程
CSS 教程

CSS 教程
CSS3 教程

CSS3 教程
jQuery 教程

jQuery 教程
Ajax 教程

Ajax 教程
AngularJS 教程

AngularJS 教程
TypeScript 教程

TypeScript 教程
WordPress 教程

WordPress 教程
Laravel 教程

Laravel 教程
Next.js 教程

Next.js 教程
PhantomJS 教程

PhantomJS 教程
Three.js 教程

Three.js 教程
Underscore.JS 教程

Underscore.JS 教程
WebGL 教程

WebGL 教程
WebRTC 教程

WebRTC 教程
VueJS 教程

VueJS 教程

数据库教程

SQL 教程

SQL 教程
MySQL 教程

MySQL 教程
MongoDB 教程

MongoDB 教程
PostgreSQL 教程

PostgreSQL 教程
SQLite 教程

SQLite 教程
Redis 教程

Redis 教程
MariaDB 教程

MariaDB 教程

图形图像教程

Vulkan 教程

Vulkan 教程
OpenCV 教程

OpenCV 教程

大数据教程

R语言 教程

R语言 教程

开发工具教程

Git 教程

Git 教程
VSCode 教程

VSCode 教程
Docker 教程

Docker 教程
Gerrit 教程

Gerrit 教程
Excel 教程

Excel 教程

计算机教程

Go语言 教程

Go语言 教程
C++ 教程

C++ 教程

MySQL 精品教程

登录

找回密码？

注册