一文教你MySQL如何优化无索引的join

当我们在数据库中进行join操作时，如果缺少索引，通常会导致查询速度变慢。因此，优化join操作是数据库性能优化的重要一步。在本篇文章中，我们将通过以下步骤教你如何优化无索引的join操作。

1. 确认join操作是否缺少索引

在进行join操作之前，我们应该首先确认join操作是否缺少索引。我们可以通过explain命令查看join操作的执行计划，根据执行计划中的信息来判断是否缺少索引。

EXPLAIN SELECT *
FROM table_a
JOIN table_b ON table_a.id = table_b.a_id;

执行上述命令后，我们可以得到执行计划的结果。在执行计划中，我们应该关注Extra字段中是否有"Using join buffer (Block Nested Loop)"的提示。如果有，则说明该join操作缺少索引。

2. 创建索引

为了优化join操作，我们需要为缺少索引的列创建索引。我们可以使用以下命令创建索引：

ALTER TABLE table_a ADD INDEX index_name(column_name);

执行上述命令后，就可以为table_a表中的column_name列创建索引。同样，我们也可以为table_b表中的a_id列创建索引。

ALTER TABLE table_b ADD INDEX index_name(a_id);

3. 再次执行join操作

为缺少索引的列创建索引后，我们可以再次执行join操作，如下：

SELECT *
FROM table_a
JOIN table_b ON table_a.id = table_b.a_id;

执行上述命令后，我们可以得到相应的结果。此时，执行计划中的Extra字段应该没有"Using join buffer (Block Nested Loop)"的提示，说明join操作的执行计划已经得到优化。

示例

下面，我们通过一个示例说明如何优化无索引的join操作：

假设我们有两张表student和score，它们的结构如下：

CREATE TABLE student (
  id INT UNSIGNED PRIMARY KEY,
  name VARCHAR(20) NOT NULL,
  age TINYINT UNSIGNED NOT NULL,
  gender ENUM('M', 'F') NOT NULL,
  class_id INT UNSIGNED NOT NULL
);

CREATE TABLE score (
  id INT UNSIGNED PRIMARY KEY,
  student_id INT UNSIGNED NOT NULL,
  subject VARCHAR(10) NOT NULL,
  score DECIMAL(3, 1) NOT NULL
);

现在，我们需要查询每个学生的平均分以及所在班级的平均分。我们可以使用以下SQL语句进行查询：

SELECT student.id, AVG(student_score.score), AVG(class_score.score)
FROM student
JOIN (
  SELECT student_id, AVG(score) AS score
  FROM score
  GROUP BY student_id
) AS student_score
ON student.id = student_score.student_id
JOIN (
  SELECT class_id, AVG(score) AS score
  FROM score
  JOIN student
  ON score.student_id = student.id
  GROUP BY class_id
) AS class_score
ON student.class_id = class_score.class_id
GROUP BY student.id;

执行上述查询后，我们可以发现查询速度比较慢，需要优化。我们可以使用explain命令查看执行计划：

EXPLAIN SELECT student.id, AVG(student_score.score), AVG(class_score.score)
FROM student
JOIN (
  SELECT student_id, AVG(score) AS score
  FROM score
  GROUP BY student_id
) AS student_score
ON student.id = student_score.student_id
JOIN (
  SELECT class_id, AVG(score) AS score
  FROM score
  JOIN student
  ON score.student_id = student.id
  GROUP BY class_id
) AS class_score
ON student.class_id = class_score.class_id
GROUP BY student.id;

执行上述命令后，我们可以看到执行计划中有"Using join buffer (Block Nested Loop)"的提示，说明join操作缺少索引。我们可以为student表的class_id列和score表的student_id列创建索引，如下：

ALTER TABLE student ADD INDEX index_name(class_id);
ALTER TABLE score ADD INDEX index_name(student_id);

创建完索引后，我们再次执行上述查询：

SELECT student.id, AVG(student_score.score), AVG(class_score.score)
FROM student
JOIN (
  SELECT student_id, AVG(score) AS score
  FROM score
  GROUP BY student_id
) AS student_score
ON student.id = student_score.student_id
JOIN (
  SELECT class_id, AVG(score) AS score
  FROM score
  JOIN student
  ON score.student_id = student.id
  GROUP BY class_id
) AS class_score
ON student.class_id = class_score.class_id
GROUP BY student.id;

执行上述查询后，我们可以发现查询速度明显提升，优化成功。