mysql - 不使用索引的查询

Question

我正在对 2 个表运行查询，但由于某种原因它没有使用索引。我一生都无法想象这是我们的。

这是我的表格:table1 和 table2。表 1 是一个包含 154 行的类别列表。表 1 的表结构如下所示:

category_id (int11) - also has an index on it
name (varchar30) - also has an index on it
urltext (text)
nametext (text)

category_id     name    urltext   nametext
1           category1    blahblah     blahblah
2           category2    blahblah     blahblah
3           category3    blahblah     blahblah
4           category4    blahblah     blahblah
etc to rows
154           category154    blahblah     blahblah

表 2 有超过 100 万行，并且关联产品类别。表字段结构如下所示:

row_id (bigint11) - also has an index
category_id (int3) - also has an index
product_id (int9) - also has an index

表2中的数据如下所示:

row_id     category_id     product_id 
1               1            123456
2               4            123456
3               17            456789
4               42            456789
5               7            456789
6               88            888555

这是我的查询:

select * 
from table2 
INNER JOIN table1 
ON table2.category_id=table1.category_id 
where table2.product_id = 123456;

现在，当我对此查询运行解释时:

id   select_type  table type  possible_keys     key       key_len  ref                 rows  Extra
1    simple       table1 ALL    PRIMARY          NULL      NULL     NULL                 154
1    simple       table2 ref    product_id,...   poduct_id  10      category_id, const   1     Using Where

任何产品通常只涉及 1 到 4 个不同的类别。因此，您可能会认为，如果正确使用索引，您将看到最多 4 行，而不是在解释结果中看到 table1 的 154 行。

我最初将 table1.name 设置为 text 而不是 varchar(30)，但结果仍然没有改变。如果这是一个随机使用的查询，那么这并不重要，但这个查询每天都会被点击数万次。

我在这里做错了什么，这个查询没有使用可用的索引来大量减少行数？

我希望我提供了正确的信息。提前致谢。

Answer 1

您的查询可以重新格式化为:

SELECT *
FROM table2
INNER JOIN table1 ON table2.category_id = table1.category_id
WHERE table2.product_id = 123456

为了加快速度，应该存在以下索引:

table1(category_id)

通过 table1.category_id 快速查找。

还有

table2(category_id, product_id)

或

table2(product_id, category_id)

此多列(复合)索引将同时满足 JOIN 条件和 WHERE 条件。

请注意，在 product_id 和 category_id 上建立单独的索引是不够的。但是，如果您在 (category_id, Product_id) 上有复合索引，则可以删除 (category_id) 上的索引，除非它是唯一索引(如主键)。