这种情况需要InnoDB还是MyISAM？
我正在对一个表（很少有内部联接）进行搜索，该搜索最多需要 5 秒才能运行（750 万行）。这是一个 MyISAM 表，我没有在其上使用全文索引，因为我发现在这种情况下使用 MATCH AGAINST 和正常的“like”语句时速度没有差异。
我现在“遭受”锁定的表和查询在完成之前运行几分钟的痛苦。
尝试将引擎切换到 InnoDB 对我有好处吗？或者这仅在我需要插入或更新行时才有帮助...而不仅仅是选择它们？这整个锁桌子的事情正忙着磨我的球......
I'm doing a search on a table (few inner joins) which takes max 5 seconds to run (7.5 million rows). It's a MyISAM table and I'm not using full-text indexing on it as I found there to be no difference in speed when using MATCH AGAINST and a normal "like" statement in this case from what I can see.
I'm now "suffering" from locked tables and queries running for several minutes before they complete because of it.
Would it benefit me at all to try and switch the engine to InnoDB? Or does that only help if I need to insert or update rows... not just select them? This whole table-locking thing is busy grinding my balls...
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
InnoDB 支持行级锁定而不是表级锁定...因此这应该可以缓解您的问题（尽管我不确定它会完全删除它）。
最好的选择是使用专用的搜索系统（例如 Sphinx、Lucene 或 Solr）
InnoDB supports row-level locking instead of table-level locking... so that should alleviate your problem (although I'm not sure it will remove it entirely).
Your best bet would be to use a dedicated search system (like Sphinx, Lucene, or Solr)
行级锁定和表级锁定之间的区别仅对于插入和更新查询很重要。如果您主要进行选择（因此插入/更新不会太频繁地发生以锁定表），则差异不会那么大（即使在最近的基准测试中，InnoDB 似乎优于 MyISAM）。
您可以考虑的其他方法是重新组织数据结构，可能包括带有“标签”或“关键字”的附加查找表。按照 webdestroya 的建议实施更高效的全文引擎。
最后但并非最不重要的一点是，我也很惊讶您使用 FULL TEXT 与 LIKE 得到了相似的结果。如果您要搜索的字段不是很宽，则可能会发生这种情况，在这种情况下，带有 = 搜索的标准 B-TREE 索引可能就足够了？
The difference between row-level and table-level locking is only important for insert and update queries. If you're mostly do selects (so the inserts/updates do not happen too often to lock the table) the difference will not be all that much (even though in recent benchmarks InnoDB seems to be outperforming MyISAM).
Other ways you could think about is to reorganise your data structure, perhaps including additional lookup table with 'tags' or 'keywords'. Implementing more efficient full text engine as suggested by webdestroya.
Last but not least, I'm also surprised that you got similar results with FULL TEXT vs LIKE. This could happen if the fields you're searching are not really wide, in which case maybe a stndard B-TREE index with = search would be enough?