mysql - JOIN 不以可预测的方式使用索引

Question

假设我有三张 table 。

CREATE TABLE movies (
    id INT AUTO_INCREMENT,
    name VARCHAR(255),
    PRIMARY KEY (id)
);

CREATE TABLE movies_actors (
    id INT AUTO_INCREMENT,
    movie_id INT,
    actor_id INT,
    current_salary_id INT,
    PRIMARY KEY (id),
    KEY movie_id (movie_id),
    KEY actor_id (actor_id),
    KEY current_salary_id (current_salary_id)
);

CREATE TABLE movies_actors_salaries (
    id INT AUTO_INCREMENT,
    actor_id INT,
    compensation_type ENUM('salary','hourly','commission','lumpsum'),
    amount DECIMAL(9,2),
    date_agreed_upon DATETIME,
    PRIMARY KEY (id),
    KEY actor_id (actor_id)
);

我正在尝试连接表来执行一些查询，但索引的使用非常零散，我不知道为什么。

SELECT COUNT(1)
FROM movies m
JOIN movies_actors ma ON m.id = ma.movie_id
JOIN movies_actors_salaries mas ON ma.current_salary_id = mas.id;

如果我对 ma 表的 Extra 列进行解释，则不会显示“使用索引”。我是否执行LEFT JOIN movie_actors_salaries或JOIN movie_actors_salaries并不重要 - 它只是没有被使用。我不明白，因为 m.id 是电影表的主键，而 ma.movi​​e_id 是键。

我也尝试了另一个查询:

SELECT COUNT(1)
FROM movies m
JOIN movies_actors ma ON m.id = ma.movie_id
JOIN movies_actors_salaries mas ON ma.id = mas.actor_id;

如果我对 ma 表的 Extra 列没有说“使用索引”进行解释，但如果我执行 LEFT JOIN movie_actors_salaries 而不是 JOIN索引确实被使用了。同样，我不明白 - 为什么 movie_actor 表使用的索引取决于我加入 movie_actors_salaries 表的方式？

老实说，我一点也不明白。在我看来，当 EXPLAIN 完成时，所有四个(即上面两个带有 JOIN movie_actors_salaries 和带有 LEFT JOIN movie_actors_salaries)的 Extra 列应该显示“使用索引”。

我正在使用 Percona MySQL 5.5.35-33.0。有什么想法吗？

Answer 1

比 rows=1 和 Using where for ma 更受关注:

mysql> explain SELECT COUNT(m.id) FROM movies m JOIN movies_actors ma ON m.id = ma.movie_id JOIN movies_actors_salaries mas ON ma.current_salary_id = mas.id;
+----+-------------+-------+--------+----------------------------+---------+---------+-----------------------------------+------+-------------+
| id | select_type | table | type   | possible_keys              | key     | key_len | ref                               | rows | Extra       |
+----+-------------+-------+--------+----------------------------+---------+---------+-----------------------------------+------+-------------+
|  1 | SIMPLE      | ma    | ALL    | movie_id,current_salary_id | NULL    | NULL    | NULL                              |    1 | Using where |
|  1 | SIMPLE      | mas   | eq_ref | PRIMARY                    | PRIMARY | 4       | so_gibberish.ma.current_salary_id |    1 | Using index |
|  1 | SIMPLE      | m     | eq_ref | PRIMARY                    | PRIMARY | 4       | so_gibberish.ma.movie_id          |    1 | Using index |
+----+-------------+-------+--------+----------------------------+---------+---------+-----------------------------------+------+-------------+
3 rows in set (0.05 sec)

是此处看到的最后一个键的掉落:

-- drop table movies_actors;
CREATE TABLE movies_actors (
    id INT AUTO_INCREMENT,
    movie_id INT,
    actor_id INT,
    current_salary_id INT,
    PRIMARY KEY (id),
    KEY movie_id (movie_id),
    KEY actor_id (actor_id)
    -- KEY current_salary_id (current_salary_id)
);

导致新的可怕 解释，其中 rows=1024 和 Using where;在上述架构更改和堵塞行之后，使用连接缓冲区( block 嵌套循环)或使用文件排序或使用临时:

+----+-------------+-------+--------+---------------+----------+---------+--------------------------+------+----------------------------------------------------+
| id | select_type | table | type   | possible_keys | key      | key_len | ref                      | rows | Extra                                              |
+----+-------------+-------+--------+---------------+----------+---------+--------------------------+------+----------------------------------------------------+
|  1 | SIMPLE      | mas   | index  | PRIMARY       | actor_id | 5       | NULL                     |    1 | Using index                                        |
|  1 | SIMPLE      | ma    | ALL    | movie_id      | NULL     | NULL    | NULL                     | 1024 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | m     | eq_ref | PRIMARY       | PRIMARY  | 4       | so_gibberish.ma.movie_id |    1 | Using index                                        |
+----+-------------+-------+--------+---------------+----------+---------+--------------------------+------+----------------------------------------------------+

要点

Explain 很神秘，就好像您不知道一样，但与刚刚提到的替代方案(即:1k 行和文件排序、临时表)相比，您的行数较低的事实应该是令人欣慰的)。

解释也是谎言。这是一个异想天开的幻想之地，预计会在几秒钟内渲染几行文字，但当 Explain 被删除时，它会根据地面的现实情况改变路线。

我可以在 movies_actors_salaries 中有 1 行与您的连接匹配，使用索引会建议 mas 使用它，但我向您保证这不是因为这个Manual Page摘录:

Indexes are less important for queries on small tables, or big tables where report queries process most or all of the rows. When a query needs to access most of the rows, reading sequentially is faster than working through an index. Sequential reads minimize disk seeks, even if not all the rows are needed for the query.

所以你可以走了。请密切关注Explain行计数以及文件排序和临时警告的使用。