喜欢和排序在不同的表/列上

Question

有关信息，在以下示例中，big_table由数百万行和数百small_table组成。

这是我尝试执行的基本查询：

SELECT b.id 
FROM big_table b 
LEFT JOIN small_table s 
ON b.small_id=s.id
WHERE s.name like 'something%' 
ORDER BY b.name 
LIMIT 10, 10;

这很慢，我可以理解为什么两个索引都不能使用。

我最初的想法是将查询拆分为多个部分。

这很快：

SELECT id FROM small_table WHERE name like 'something%';

这也很快：

SELECT id FROM big_table WHERE small_id IN (1, 2) ORDER BY name LIMIT 10, 10;

但是，放在一起，它变得很慢：

SELECT id FROM big_table 
WHERE small_id 
IN (
SELECT id 
FROM small_table WHERE name like 'something%'
) 
ORDER BY name 
LIMIT 10, 10;

除非为每一行重新计算子查询，否则它不应该比分别执行两个查询慢吧？

我正在寻找任何帮助来优化初始查询并了解为什么第二个查询不起作用。

---

最后一个查询的解释结果：

| id   | select_type | table | type | possible_keys | key | key_len | ref | rows  | Extra
| 1 | PRIMARY | small_table | range | PRIMARY, ix_small_name | ix_small_name | 768 | NULL | 1 | Using where; Using index; Using temporary; Using filesort |
| 1 | PRIMARY | big_table | ref | ix_join_foreign_key   | ix_join_foreign_key | 9 | small_table.id | 11870 | |

---

临时解决方案：

SELECT id FROM big_table ignore index(ix_join_foreign_key)
WHERE small_id 
IN (
SELECT id 
FROM small_table ignore index(PRIMARY)
WHERE name like 'something%'
) 
ORDER BY name 
LIMIT 10, 10;

(结果和解释与存在而不是IN相同)

解释输出变为：

| 1 | PRIMARY | big_table | index  | NULL | ix_big_name | 768 | NULL | 20 | |
| 1 | PRIMARY | <subquery2> | eq_ref | distinct_key | distinct_key | 8 | func | 1 | |
| 2 | MATERIALIZED | small_table | range | ix_small_name | ix_small_name | 768 | NULL | 1 | Using where; Using index |

如果有人有更好的解决方案，我仍然感兴趣。

Answer 1

您面临的问题是您在小表上有条件，但试图避免在大表中排序。 在MySQL中，我认为您至少需要进行全表扫描。

一个步骤是使用exists编写查询，正如其他人所提到的：

SELECT b.id
FROM big_table b
WHERE EXISTS (SELECT 1
FROM small_table s
WHERE s.name LIKE 'something%' AND s.id = b.small_id
)
ORDER BY b.name;

问题是：你能欺骗MySQL使用索引进行ORDER BY吗？ 一种可能性是使用适当的索引。 在这种情况下，适当的索引是：big_table(name, small_id, id)和small_table(id, name)。 索引中键的顺序很重要。 因为第一个是覆盖索引，MySQL可能会按名称顺序读取索引，选择适当的id。

Answer 2

您正在寻找EXISTS或IN查询。众所周知，MySQL在IN上很弱，尽管我更喜欢IN的简单性，但我会尝试EXISTS。

select id
from big_table b
where exists
(
select *
from small_table s
where s.id = b.small_id
and s.name = 'something%'
)
order by name 
limit 10, 10;

对big_table有一个好的索引会很有帮助。它应首先包含查找匹配项的small_id，然后包含排序的name。据我所知，该ID会自动包含在MySQL索引中(否则也应将其添加到索引中)。因此，您将拥有一个索引，其中包含big_table所需的所有字段(称为覆盖索引)，因此可以单独从索引中读取所有数据，并且不必访问表本身。

create index idx_big_quick on big_table(small_id, name);

Answer 3

你可以试试这个：

SELECT b.id
FROM big_table b
JOIN small_table s
ON b.small_id = s.id
WHERE s.name like 'something%'
ORDER BY b.name;

或

SELECT b.id FROM big_table b
WHERE EXISTS(SELECT 1 FROM small_table s
WHERE s.name LIKE 'something%' AND s.id = b.small_id)
ORDER BY b.name;

注意：您似乎不需要LEFT JOIN。左外连接几乎总是会导致big_table的全表扫描

PS 确保您有一个索引big_table.small_id

Answer 4

计划 A

SELECT  b.id
FROM  big_table b
JOIN  small_table s  ON b.small_id=s.id
WHERE  s.name like 'something%'
ORDER BY  b.name
LIMIT  10, 10;

(注意删除LEFT。

你需要

small_table:  INDEX(name, id)
big_table:    INDEX(small_id), or, for 'covering': INDEX(small_id, name, id)

它将使用s索引来查找'something%'并演练。 但它必须找到所有这样的行，JOINb在那里找到所有这些行。 只有这样，它才能做ORDER BY、OFFSET和LIMIT。将有一个文件排序(可能发生在RAM中)。

索引中的列顺序很重要。

B计划

另一个建议可能很有效;这取决于各种事情。

SELECT  b.id
FROM  big_table b
WHERE  EXISTS 
( SELECT  *
FROM  small_table s
WHERE  s.name LIKE 'something%'
AND  s.id = b.small_id 
)
ORDER BY  b.name
LIMIT 10, 10;

这需要这些：

big_table:    INDEX(name), or for 'covering',  INDEX(name, small_id, id)
small_table:  INDEX(id, name), which is 'covering'

(警告：如果你在做SELECT b.id以外的事情，我关于覆盖的评论可能是错误的。

哪个更快(A 或 B)？ 如果不了解"某物%"的频率以及多对一映射的"多"，就无法预测。

设置

如果这些表是 InnoDB，请确保innodb_buffer_pool_size设置为可用RAM 的 70% 左右。

分页

您使用OFFSET意味着您正在通过数据"分页"？OFFSET是一种低效的方法。 请参阅我的博客，但请注意，只有B计划可以使用它。