用于子查询的mysql强制索引

Question

在这个查询中，我们只讨论为子查询设置索引联接的能力。不要担心查询的性能

SELECT cate.id, cate.name as category_name, b.id book_id, b.name book_name
FROM categories as cate
LEFT JOIN (select * from books where books.name='some pets 1') as b
ON b.category_id = cate.id;

​

​

我希望通过使用子查询创建临时表'b‘来减少表书的行数，然后尝试为'b’强制连接索引，但它没有工作("force index (category_id)")。我的草案代码：

SELECT cate.id, cate.name as category_name, b.id book_id, b.name book_name
FROM categories as cate
LEFT JOIN (select * from books force index (idx_books_name) where books.name='some pets 1') b force index (category_id)
ON b.category_id = cate.id;

它通过语法错误在“强制索引”。你觉得这个想法有可能吗？

Answer 1

您的“子查询”b是一个派生表。派生表(基本上)是一个全新的表。这就是您想要的，您想要创建一个小的临时表b。但是这个新表不能访问原始索引，因为它是一个新表。这就是为什么您会收到错误消息。

想象一下MySQL需要如何使用类别索引。该索引本身并没有改变，因此它仍然包含原始表的所有行的条目。因此，您可以使用该索引在整个表中使用该category_id查找记录(因为该索引就是这样做的)，然后需要检查该条目是否在派生表中。由于派生表几乎可以由任何东西组成，即使没有对基表的任何引用，这些都不是“某种方式”这样做的通用方法。

在您的示例中，这可以通过重新评估条件where books.name='some pets 1'来实现，但这意味着您将失去使用idx_books_name索引和派生表可能获得的任何优势，因此它将与

LEFT JOIN books b ON b.category_id = cate.id AND b.name='some pets 1');

它将与索引books(category_id, name)一起使用您想要的索引。

使用派生表的方式基本上已经是一种优化，它希望阻止MySQL按照通常的方式进行连接(尽管允许MySQL将条件合并回外部查询)。如果您有特殊情况，例如，如果只有很小一部分符合此条件的行，或者在category_id上没有有用的索引，您就会这样做。

MySQL可能决定在category_id上使用generate index for your derived table。您(目前)无法强制通过优化器提示生成这些索引，值得注意的是，小型表上的索引可能没有您想象的那么有用。索引以对数方式扩展，因此，虽然对大型表的影响很大，但对于小型表，生成这样一个索引的开销可能大于好处。