时间戳

首页 > 解决方案 > MySQL JOIN 两个表和 ORDER BY 似乎没有使用正确的索引

mysql - MySQL JOIN 两个表和 ORDER BY 似乎没有使用正确的索引

问题描述

我在 MySQL 查询的性能方面遇到问题。总而言之，我想将表 A 连接到表 B，并根据表 A 中的表两列对结果进行排序。我的方法是在 (i) 连接表 A 和 B 的列上创建组合索引，并且 ( ii) 我想对结果排序的两列。但是，当我加入这两个表时，行为似乎出乎意料，并且该ORDER BY子句似乎不再使用索引。

我使用以下查询：

SELECT SQL_NO_CACHE *
FROM
    t_patent_documents A USE INDEX (idx_t_patent_documents_result_order) INNER JOIN
    t_inv_title_int_content_combined B ON A.publication_id=B.publication_id
ORDER BY
    A.language_id ASC,
    A.result_order ASC
LIMIT 100

对于表t_patent_documents，我将索引组合索引idx_t_patent_documents_result_order定义为列上(publication_id, language_id, result_order)。此外，publication_id是 的主键t_patent_documents。解释计划如下：

| id | select_type | table | partitions | type | possible_keys                       | key                                 | key_len | ref              | rows      | filtered | Extra                             | 
|----|-------------|-------|------------|------|-------------------------------------|-------------------------------------|---------|------------------|-----------|----------|-----------------------------------| 
| 1  | SIMPLE      | B     | NULL       | ALL  | NULL                                | NULL                                | NULL    | NULL             | 132162247 | 100.00   | "Using temporary; Using filesort" | 
| 1  | SIMPLE      | A     | NULL       | ref  | idx_t_patent_documents_result_order | idx_t_patent_documents_result_order | 4       | B.publication_id | 1         | 100.00   | NULL                              |

如果我执行以下操作（不强制索引）：

SELECT SQL_NO_CACHE *
FROM
    t_patent_documents A INNER JOIN
    t_inv_title_int_content_combined B ON A.publication_id=B.publication_id
ORDER BY
    A.language_id ASC,
    A.result_order ASC
LIMIT 100

然后优化器选择只使用主键：

|----|-------------|-------|------------|--------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|---------|---------|------------------|-----------|----------|-----------------------------------| 
| 1  | SIMPLE      | B     | NULL       | ALL    | NULL                                                                                                                                                                                                                                                                                  | NULL    | NULL    | NULL             | 132162247 | 100.00   | "Using temporary; Using filesort" | 
| 1  | SIMPLE      | A     | NULL       | eq_ref | "PRIMARY,idx_t_patent_documents_pubid_country_priority_dt,idx_t_patent_documents_pubid_priority_dt,idx_t_patent_documents_pubid_country_ucid,idx_t_patent_documents_pubid_ucid_priority_dt,idx_t_patent_documents_pubid_country_ucid_priority_dt,idx_t_patent_documents_result_order" | PRIMARY | 4       | B.publication_id | 1         | 100.00   | NULL                              |

现在，当我没有在表 A 上加入表 B 时，但ORDER BY我定义了索引的三列，即(publication_id, language_id, result_order)，它似乎正确地获取了索引。这里key_len确实是 14：

SELECT SQL_NO_CACHE *
FROM
    t_patent_documents A USE INDEX(idx_t_patent_documents_result_order)
ORDER BY
    A.publication_id ASC,
    A.language_id ASC,
    A.result_order ASC
LIMIT 100

这导致以下解释计划：

| id | select_type | table | partitions | type  | possible_keys | key                                 | key_len | ref  | rows | filtered | Extra | 
|----|-------------|-------|------------|-------|---------------|-------------------------------------|---------|------|------|----------|-------| 
| 1  | SIMPLE      | A     | NULL       | index | NULL          | idx_t_patent_documents_result_order | 14      | NULL | 100  | 100.00   | NULL  |

有人理解这种行为吗？理想情况下，我将能够加入另一个表，并且仍然能够快速排序结果。

提前致谢！

更新 1：我也尝试publication_id在ORDER BY子句中添加。尽管从输出的角度来看，这根本没有意义，因为它publication_id是独一无二的。查询如下所示：

SELECT SQL_NO_CACHE *
FROM
    t_patent_documents A USE INDEX (idx_t_patent_documents_result_order) INNER JOIN
    t_inv_title_int_content_combined B ON A.publication_id=B.publication_id
ORDER BY
    A.publication_id ASC,
    A.language_id ASC,
    A.result_order ASC
LIMIT 100

由此产生的解释计划：

| id | select_type | table | partitions | type | possible_keys                       | key                                 | key_len | ref              | rows      | filtered | Extra                             | 
|----|-------------|-------|------------|------|-------------------------------------|-------------------------------------|---------|------------------|-----------|----------|-----------------------------------| 
| 1  | SIMPLE      | B     | NULL       | ALL  | NULL                                | NULL                                | NULL    | NULL             | 132162247 | 100.00   | "Using temporary; Using filesort" | 
| 1  | SIMPLE      | A     | NULL       | ref  | idx_t_patent_documents_result_order | idx_t_patent_documents_result_order | 4       | B.publication_id | 1         | 100.00   | NULL                              |

更新 2： 我还尝试在所有三列上运行没有多重索引的查询，但只在我要排序的两列上运行：(language_id, result_order)称为x_temp_idx_1. 这确实似乎在key_len再次为 14 的意义上产生了效果，但是，现在连接部分需要永远。我运行的查询：

explain SELECT SQL_NO_CACHE A.language_id, A.result_order
FROM
    t_patent_documents A USE INDEX(x_temp_idx_1) INNER JOIN
    t_inv_title_int_content_combined B ON A.publication_id=B.publication_id
ORDER BY
    A.language_id ASC,
    A.result_order ASC
LIMIT 100

对应的解释方案：

| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows    | filtered | Extra                                          | 
|----|-------------|-------|------------|-------|---------------|--------------|---------|------|---------|----------|------------------------------------------------| 
| 1  | SIMPLE      | A     | NULL       | index | NULL          | x_temp_idx_1 | 14      | NULL | 1795275 | 100.00   | "Using index; Using temporary; Using filesort" | 
| 1  | SIMPLE      | B     | NULL       | ALL   | NULL          | NULL         | NULL    | NULL | 2469412 | 10.00    | "Using where; Using join buffer (hash join)"   |

请注意，此查询在信息较少的另一个数据库（开发）上运行。这就是为什么行数与之前的解释计划不对应的原因。

更新 3： 以上所有查询都是我想运行的实际查询的简化，以免问题过于复杂。在实际用例中，我需要过滤表 B 中的全文索引。该表在列上包含全文索引invention_title：

explain SELECT SQL_NO_CACHE *
FROM
    t_patent_documents A INNER JOIN
    t_inv_title_int_content_combined B ON A.publication_id=B.publication_id
WHERE
    MATCH(B.invention_title) AGAINST("+hydraulic" IN BOOLEAN MODE)
ORDER BY
    A.language_id ASC,
    A.result_order ASC
LIMIT 100

结果解释再次表明它仅使用主键进行连接，但未能使用多索引。如果我再次强制索引，key_len则为 4，它实际上似乎并没有使用多索引：

|    |             |       |            |          |                                                                                                                                                                                                                                                                                                    |                                                 |         |                  |      |          |                                                                      | 
|----|-------------|-------|------------|----------|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|-------------------------------------------------|---------|------------------|------|----------|----------------------------------------------------------------------| 
| id | select_type | table | partitions | type     | possible_keys                                                                                                                                                                                                                                                                                      | key                                             | key_len | ref              | rows | filtered | Extra                                                                | 
| 1  | SIMPLE      | B     | NULL       | fulltext | idx_ft_inv_title_int_content_combined_inv_title                                                                                                                                                                                                                                                    | idx_ft_inv_title_int_content_combined_inv_title | 0       | const            | 1    | 100.00   | "Using where; Ft_hints: no_ranking; Using temporary; Using filesort" | 
| 1  | SIMPLE      | A     | NULL       | eq_ref   | "PRIMARY,idx_t_patent_documents_pubid_country_priority_dt,idx_t_patent_documents_pubid_priority_dt,idx_t_patent_documents_pubid_country_ucid,idx_t_patent_documents_pubid_ucid_priority_dt,idx_t_patent_documents_pubid_country_ucid_priority_dt,idx_t_patent_documents_result_order,x_temp_idx_2" | PRIMARY                                         | 4       | B.publication_id | 1    | 100.00   | NULL                                                                 |

标签： mysqljoinindexingsql-order-bylimit

解决方案

对于您的第一个查询，删除USE INDEX并将索引更改为以下顺序：

(language_id, result_order, publication_id)

这个想法是有INDEX匹配的ORDER BY。优化器优先考虑WHEREand GROUP BY，但您也没有。因此，您的案例的目的是能够LIMIT通过使索引与ORDER BY. 这将很好地工作A。

更改SELECT *以仅指定必要的行可能会有所帮助。`

如果每个 A 行恰好有 1 个 B 行，那么还有另一个优化。由于情况并非如此，因此JOIN必须执行，然后排序，最后才交付LIMIT行。

回复：更新 3

这正如预期的那样。

  1. 使用 FT 索引来查找（希望）几行匹配。
  2. 以非常有效的方式进入另一张桌子，即通过 PK。
  3. 抓住需要的其他列。
  4. 排序实现ORDER BY。
  5. 剥离LIMIT行。

没有进一步的优化。一个可能的拖累是如果*包含一些笨重的TEXT或BLOB列。

推荐阅读