使用 MySQL 搜索大型数据集？

Question

现在我是一名非常高级的 PHP 开发人员，并且对小型 MySQL 集非常了解，但是我现在正在为我最近加入的一家初创公司构建大型基础设施，他们的服务器每天使用它们的服务器推送大约 100 万行数据巨大的服务器能力和以前的架构。

我需要知道搜索数据库大小为 394.4 GB 的大型数据集（目前有 8490 万行）的最佳方法是什么。它使用 Amazon RDS 托管，因此没有任何停机时间或速度缓慢，只是我想知道内部访问大型数据集的最佳方式是什么。

例如，如果我想搜索包含 8400 万行的数据库，则需要 6 分钟。现在，如果我直接请求特定的 ID 或标题，它会立即提供服务。那么我将如何搜索大型数据集。

只是提醒您，通过传入一个变量来通过数据库查找信息很快，但在搜索时执行速度非常慢。

MySQL 查询示例：

SELECT u.*, COUNT(*) AS user_count, f.* FROM users u LEFT JOIN friends f ON u.user_id=(f.friend_from||f.friend_to) WHERE u.user_name LIKE ('%james%smith%') GROUP BY u.signed_up LIMIT 0, 100

84m 行以下的查询速度明显慢。具体来说，独立执行此查询需要 47.41 秒，大家有什么想法吗？

我所需要的只是解决这个挑战，然后我就能掌握方向。另外，我知道 MySQL 不太适合大型数据集以及 Oracle 或 MSSQL 之类的数据集，但我被告知目前要在 MySQL 而不是其他数据库解决方案上重建它。

Answer 1

由于多种原因，LIKE 非常慢：

• 除非您的 LIKE 表达式以常量开头，不会使用索引。< /p>

  例如，LIKE ('james%smith%') 很好，LIKE ('%james%smith%') 不利于索引。您的示例不会在“user_name”字段上使用任何索引。

• 与常规运算符相比，字符串匹配是一项复杂（算法上）的业务。

解决方法：

• 如果您可以使用该字段上的索引，请确保您的 LIKE 表达式以常量而不是通配符开头。

• 如果您搜索整个单词，请考虑创建一个索引表（在“索引”一词的文献/图书馆上下文中，而不是数据库索引上下文中）。或者如果搜索随机经常重复的子字符串，则使用子字符串查找表。

  例如，如果所有用户名的形式为“FN LN”或“LN，FN” - 将它们分开并将名字和/或姓氏存储在字典表中，加入到该表（并执行直接相等）在您的查询中。

Answer 2

LIKE ('%james%smith%')

避免这些事情就像避免瘟疫一样。一般的 DBMS 不可能对其进行优化。

正确的方法是在插入或更新数据时计算类似的内容（名字和姓氏），以便在所有读取中分摊成本。这可以通过添加两个新列（索引）并使用插入/更新触发器来完成。

或者，如果您想要列中的所有单词，请让触发器将数据分解为单词，然后使用应用程序级索引表来查找相关记录，例如：

main_table:
    id integer primary key
    blah blah blah
    text varchar(60)
appl_index:
    id index
    word varchar(20)
    primary key (id,word)
    index (word)

然后您可以查询 appl_index 找到那些同时包含 james 和 smith 的 id，比像 '% 这样可恶的 快得多...'。您还可以将实际单词分解到一个单独的表中并使用单词 ID，但这只是一个品味问题 - 它对性能的影响值得怀疑。

您很可能在使用 f.friend_from||f.friend_to 时遇到类似的问题，但我以前没有见过该语法（如果上下文是 u.user_id 可以是其中之一）。

基本上，如果您希望数据库能够扩展，请不要在您的选择中执行任何看起来像每行函数的操作。这是从使用大型机数据库的人那里得到的，其中 8400 万行大约是我们的配置表的大小:-)

并且，与所有优化问题一样，衡量，不要猜测！