Mysql索引被忽略了 [英] Mysql Index Being Ignored

问题描述

EXPLAIN SELECT
*
FROM
content_link link
STRAIGHT_JOIN
content
ON
link.content_id = content.id
WHERE
link.content_id = 1
LIMIT 10;

+----+-------------+---------+-------+---------------+------------+---------+-------+------+-------+
| id | select_type | table   | type  | possible_keys | key        | key_len | ref   | rows | Extra |
+----+-------------+---------+-------+---------------+------------+---------+-------+------+-------+
|  1 | SIMPLE      | link    | ref   | content_id    | content_id | 4       | const |    1 |       |
|  1 | SIMPLE      | content | const | PRIMARY       | PRIMARY    | 4       | const |    1 |       |
+----+-------------+---------+-------+---------------+------------+---------+-------+------+-------+

然而，当我删除WHERE时，查询停止使用密钥（即使我明确强迫它）

However, when I remove the WHERE, the query stops using the key (even when i explicitly force it to)

EXPLAIN SELECT
*
FROM
content_link link FORCE KEY (content_id)
STRAIGHT_JOIN
content
ON
link.content_id = content.id
LIMIT 10;

+----+-------------+---------+--------+---------------+---------+---------+------------------------+---------+-------------+
| id | select_type | table   | type   | possible_keys | key     | key_len | ref                    | rows    | Extra       |
+----+-------------+---------+--------+---------------+---------+---------+------------------------+---------+-------------+
|  1 | SIMPLE      | link    | index  | content_id    | PRIMARY | 7       | NULL                   | 4555299 | Using index |
|  1 | SIMPLE      | content | eq_ref | PRIMARY       | PRIMARY | 4       | ft_dir.link.content_id |       1 |             |
+----+-------------+---------+--------+---------------+---------+---------+------------------------+---------+-------------+

这有什么解决方法吗？

我意识到我在第二个例子中选择了整个表，但为什么mysql突然出现决定它无论如何都会忽略我的FORCE并且不使用密钥？没有密钥，查询需要10分钟..呃。

I realize I'm selecting the entire table in the second example, but why does mysql suddenly decide that it's going to ignore my FORCE anyway and not use the key? Without the key the query takes like 10 minutes.. ugh.

推荐答案

FORCE有点用词不当。以下是MySQL文档所说的内容（强调我的）：

FORCE is a bit of a misnomer. Here's what the MySQL docs say (emphasis mine):

您还可以使用FORCE INDEX，其作用类似于USE INDEX（index_list）但添加了一个表扫描假定非常昂贵。换句话说，只有在无法使用某个给定索引在表中查找行时才使用表扫描。

由于您实际上并没有找到任何行（您正在全部选择它们），因此表格扫描始终将变得最快，并且优化程序足够聪明以便知道尽管你告诉他们。

Since you aren't actually "finding" any rows (you are selecting them all), a table scan is always going to be fastest, and the optimizer is smart enough to know that in spite of what you are telling them.

ETA：

尝试在主键上添加ORDER BY一次，我打赌它会使用索引。

Try adding an ORDER BY on the primary key once and I bet it'll use the index.

这篇关于Mysql索引被忽略了的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！