MySQL 覆盖索引和 LIKE 运算符

Question

我正在阅读“高性能 MySQL”，O'Reilly，第 2 版。第 122 页:

MySQL can't perform the LIKE operation in the index. etc

在同一页面中，后续示例是(actor,title,prod_id 上有一个索引):

解释选择 *
从产品
加入 (
选择产品编号
从产品
WHERE actor='SEAN CARREY' 和 title LIKE '%APOLLO%'
)
AS t1 ON (t1.prod_id=products.prod_id)\G

然后它说:

Now MySQL uses the covering index in the first stage of the query, when it finds matching rows in the subquery in the FROM clause.

我不明白为什么，LIKE语句还在...

Answer 1

我假设表是InnoDB。

SELECT prod_id
     FROM products
     WHERE actor='SEAN CARREY' AND title LIKE '%APOLLO%'

在 INDEX(actor, title, prod_id) 中处理，因为它是“覆盖”

SELECT * FROM ...

没有被“覆盖”，因为(我假设)* 中还有很多其他字段。

他们展示的“技巧”是:

1. 假设索引比表小得多，在索引中进行所有搜索。注意:索引是一个BTree；该表是一个不同的 BTree。
2. 从步骤 1 中获取 PRIMARY KEY 值。即获取 prod_id 的列表。
3. 使用 PRIMARY KEY 到表 products 中高效地查找其余数据 (*)。

假设有 40 行 actor='SEAN CARREY'，而只有 3 他们有 title LIKE '%APOLLO%' .让我们比较两种方法:

SELECT * FROM products
    WHERE actor='SEAN CARREY' AND title LIKE '%APOLLO%'

1. 查看 INDEX(actor, ...) 找到 40 prod_id。
2. 深入研究数据 40 次。
3. 过滤掉那些不符合要求的title。
4. 传送 3 行。

在 I/O 范围内(读取:巨大的表)，这将是 I/O 计数:

1. 1 个 block 可能包含所有 40 个 index 条目；它们会放在一起，因为索引的第一部分是相同的。
2. 需要在表中查看近 40 个 block 。

总计:大约 41 个作业单元。

现在看看带有子查询的查询。

1. 只扫描索引。又是 1 个街区。
2. 伸手进入主 table 4 次。

总计:4 个工作单元。

4 << 41，所以更复杂的查询实际上运行得更快。 (您不太可能真正看到完整的 10 倍改进——因为我遗漏了很多细节。)