我有两个表格来制作我的搜索引擎,一个包含所有关键字,另一个包含每个关键字的所有可能目标。
Table: keywords
id (int)
keyword (varchar)
Table: results
id (int)
keyword_id (int)
table_id (int)
target_id (int)
对于这两个表,我将MyISAM设置为存储引擎,因为95%的时间我只是在这些表上运行选择查询,而在5%的情况下插入查询。当然,我已经比较了使用 InnoDB 的性能,考虑到我后来的查询,性能很差。
我还添加了以下索引
keywords.keyword (unique)
results.keyword_id (index)
results.table_id (index)
results.target_id (index)
在关键字表中,我有大约 120 万条记录,在结果表中,我有大约 980 万条记录。
现在的问题是我运行以下查询,结果在 0.0014 秒内完成
SELECT rs.table_id, rs.target_id
FROM keywords ky INNER JOIN results rs ON ky.id=rs.keyword_id
WHERE ky.keyword LIKE "x%" OR ky.keyword LIKE "y%"
但是当我添加 GROUP BY 时,结果在 0.2 秒内产生
SELECT rs.table_id, rs.target_id
FROM keywords ky INNER JOIN results rs ON ky.id=rs.keyword_id
WHERE ky.keyword LIKE "x%" OR ky.keyword LIKE "y%"
GROUP BY rs.table_id, rs.target_id
我测试了复合索引、单列索引,甚至删除了table_id和target_id索引,但在所有情况下,性能都是一样的,似乎在 Group By 子句中,索引没有应用。
解释计划显示:
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
1 | SIMPLE | ky | range | PRIMARY,keyword | keyword | 767 | NULL | 3271 | Using index condition; Using where; Using temporary; Using filesort
1 | SIMPLE | rs | ref | keyword_id | keyword_id | 4 | ky.id | 3
我已经添加了以下组合键
ALTER TABLE results ADD INDEX `table_id` (`table_id`, `target_id`) USING BTREE;
这是用于GROUP BY优化的MySQL文档,它是这样说的:
对 GROUP BY 使用索引的最重要先决条件是 所有 GROUP BY 列都引用来自同一索引的属性
因此,如果您在这两列上有不同的索引,则GROUP BY
不会使用它们。您应该尝试在table_id
和target_id
上创建复合索引。
此外,查询似乎正在使用LIKE
运算符。请注意,如果在LIKE
中比较的值具有前导通配符,那么MySQL无论如何都无法对该列使用任何索引。查看查询explain plan
,并查看使用了哪些索引。
JOIN
+GROUP BY
(或DISTINCT
)就是我所说的"爆炸-内爆"——首先JOIN
乘以要查看的"行"数,然后GROUP BY
放气行数。
避免这种情况的一种解决方法是将焦点放在主表上,然后检查另一个表中的EXISTS
:
SELECT rs.table_id, rs.target_id
FROM keywords ky
WHERE EXISTS(
SELECT 1
FROM results rs
WHERE ky.id = rs.keyword_id
AND ( ky.keyword LIKE "x%"
OR ky.keyword LIKE "y%" )
);
rs
需要INDEX(keyword_id)
。
对此的改进可能是通过以下方式摆脱OR
WHERE ky.id = rs.keyword_id
AND ky.keyword REGEXP "^[xy]"
但这不是很有帮助,因为它仍然需要 完全检查keyword
.
另一个改进可能是将OR
变成UNION
:
( SELECT rs.table_id, rs.target_id
FROM keywords ky
INNER JOIN results rs ON ky.id=rs.keyword_id
WHERE ky.keyword LIKE "x%"
) UNION ALL
( SELECT rs.table_id, rs.target_id
FROM keywords ky
INNER JOIN results rs ON ky.id=rs.keyword_id
WHERE ky.keyword LIKE "y%"
)
ky: INDEX(keyword, id)
rs: INDEX(keyword_id)
这里的优点(除了避免膨胀-放气)是可以使用该指数。
(请提供两个表的SHOW CREATE TABLE
;可能还有其他提示。