mysql - 为什么 MySQL 不使用我的索引进行 JOIN？

问题描述

我有一个由 ORM 生成的非常复杂的查询，但是为了这个问题，这里是相关部分：

SELECT
    ...
FROM
    `Broadcast` `t`
    LEFT OUTER JOIN
        `Site` `site`
        ON (`t`.`site_id` = `site`.`id`)
    LEFT OUTER JOIN
        `Customer` `customer`
        ON (`site`.`customer_id` = `customer`.`id`)
    LEFT OUTER JOIN
        `Domain` `domain`
        ON (`customer`.`domain_id` = `domain`.`id`)
    ...

我的字段定义如下：

>SHOW FIELDS FROM Broadcast;
+---------------------------+--------------------------------------+------+-----+------------------+----------------+
| Field                     | Type                                 | Null | Key | Default          | Extra          |
+---------------------------+--------------------------------------+------+-----+------------------+----------------+
| id                        | int(10) unsigned                     | NO   | PRI | NULL             | auto_increment |
| site_id                   | int(10) unsigned                     | NO   | MUL | NULL             |                |
...

>SHOW FIELDS FROM Site;
+---------------------------+---------------------+------+-----+------------------+----------------+
| Field                     | Type                | Null | Key | Default          | Extra          |
+---------------------------+---------------------+------+-----+------------------+----------------+
| id                        | int(10) unsigned    | NO   | PRI | NULL             | auto_increment |
| customer_id               | int(11)             | NO   | MUL | 0                |                |
...

>SHOW FIELDS FROM Customer;
+---------------------------+------------------+------+-----+---------+----------------+
| Field                     | Type             | Null | Key | Default | Extra          |
+---------------------------+------------------+------+-----+---------+----------------+
| id                        | int(11)          | NO   | PRI | NULL    | auto_increment |
| domain_id                 | int(10) unsigned | NO   | MUL | 1       |                |
...

>SHOW FIELDS FROM Domain;
+---------------------------------+------------------+------+-----+---------+----------------+
| Field                           | Type             | Null | Key | Default | Extra          |
+---------------------------------+------------------+------+-----+---------+----------------+
| id                              | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
...

为什么我们的一些 ID 是int(11)而其他的int(10) unsigned只是历史背景问题。重要的是X_id外键始终与相关id列共享一个类型。我们从不尝试将 aint(11)与 a 联系起来int(10) unsigned。此外，该id字段始终是表的主键。

我们还有所有外键的索引：

>SHOW INDEXES FROM Broadcast;
+-----------+------------+---------------------------------------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name                              | Seq_in_index | Column_name               | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+---------------------------------------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Broadcast |          0 | PRIMARY                               |            1 | id                        | A         |      139708 |     NULL | NULL   |      | BTREE      |         |               |
| Broadcast |          1 | site_id                               |            1 | site_id                   | A         |        1060 |     NULL | NULL   |      | BTREE      |         |               |
...

>SHOW INDEXES FROM Site;
+-------+------------+----------------------------------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name                         | Seq_in_index | Column_name               | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------------------------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Site  |          0 | PRIMARY                          |            1 | id                        | A         |        1876 |     NULL | NULL   |      | BTREE      |         |               |
| Site  |          1 | customer_id                      |            1 | customer_id               | A         |         250 |     NULL | NULL   |      | BTREE      |         |               |
...

>SHOW INDEXES FROM Customer;
+----------+------------+--------------------------------------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name                             | Seq_in_index | Column_name               | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+--------------------------------------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Customer |          0 | PRIMARY                              |            1 | id                        | A         |         292 |     NULL | NULL   |      | BTREE      |         |               |
| Customer |          1 | domain_id                            |            1 | domain_id                 | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
...

>SHOW INDEXES FROM Domain;
+--------+------------+-------------------------------------------+--------------+---------------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name                                  | Seq_in_index | Column_name                     | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-------------------------------------------+--------------+---------------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Domain |          0 | PRIMARY                                   |            1 | id                              | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
...

这些索引中的每一个都包含一个字段，因此该字段是索引中的第一个序列。

当我运行EXPLAIN ...我的查询时，我得到的输出如下所示：

Site 和 Customer 以eq_ref类型连接，但 Domain 以ALL类型连接。为什么 MySQL 不使用我的索引？

我可以通过在 Customer 表名之后添加来强制 MySQL 使用我的索引USE INDEX FOR JOIN (domain_id)，但这会导致一个新问题：

问题只是从Customer->Domain关系上升到Site->Customer关系。我可以在 Site 表名称之后将问题进一步上移USE INDEX FOR JOIN (customer_id)，正如您所料，这只会导致Site表与ALL类型而不是eq_ref

我一辈子都想不通为什么 MySQL 拒绝使用我的索引

标签： mysqlsqlperformancequery-optimization

mysql - 为什么 MySQL 不使用我的索引进行 JOIN？

问题描述

解决方案

推荐阅读