LIKE查询对多百万行表，MySQL的性能 [英] Performance of LIKE queries on multmillion row tables, MySQL

问题描述

从任何有真实经验的人，如果LIKE查询在数百万行表中的MySQL中执行，在速度和效率方面，如果字段有一个普通的INDEX？

From anybody with real experience, how do LIKE queries perform in MySQL on multi-million row tables, in terms of speed and efficiency, if the field has a plain INDEX?

是否有更好的替代方法（不过滤结果，如FULLTEXT 50％规则）在数百万行表上执行数据库字段搜索？

Is there a better alternative (that doesn't filter results out, like the FULLTEXT 50% rule) for perform database field searches on multi-million row tables?

示例：

Schema (comments table)

id (PRIMARY) title(INDEX) content time stamp

Query

SELECT * FROM 'comments' WHERE 'title' LIKE '%query%'

推荐答案

任何具有真实经验的人，LIKE查询如何在
MySQL在数百万行表上，在速度和效率方面，如果
字段有一个普通的INDEX？

From anybody with real experience, how do LIKE queries perform in MySQL on multimillion row tables, in terms of speed and effiency, if the field has a plain INDEX?

不太好（我认为我在900k的范围内进行了一些搜索，不能说我有多百万行LIKE的经验）。

Not so well (I think I had some searches in the range of 900k, can't say I have experience in multimillion row LIKEs).

通常你应该限制

此外，在一些 Web 使用案例中，有可能实际上通过一些技巧来提高性能和用户体验，例如索引单独的关键字并创建关键字表和rows_contains_keyword（id_keyword，id_row）表。关键字表与AJAX一起用于建议搜索词（简单单词）并将其编译为整数 - id_keywords。此时，查找包含这些关键字的行会变得更快。 每次更新表格一行也是相当有效的;当然，批量更新变成了一个确定的不要。

Also, in some Web use cases it's possible to actually improve performances and user experience with some tricks, like indexing separate keywords and create a keyword table and a rows_contains_keyword (id_keyword, id_row) table. The keyword table is used with AJAX to suggest search terms (simple words) and to compile them to integers -- id_keywords. At that point, finding the rows containing those keywords becomes really fast. Updating the table one row at a time is also quite performant; of course, batch updates become a definite "don't".

你能给出更多关于具体案例的信息吗？

Can you give more information on the specific case?

这篇关于LIKE查询对多百万行表，MySQL的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！