MySQL自连接性能: fact or just bad indexing?

Question

举个例子:我有一个数据库来检测访问者(机器人等)，并且由于并非每个访问者都具有相同数量的“凭据”，因此我制作了一个“动态”表，如下所示:请参阅 fiddle :http://sqlfiddle.com/#!9/ca4c8/1 (简化版)。

这会返回我用来收集有关每个配置文件(在另一个数据库中)的信息的配置文件 ID。根据配置文件类型，我使用不同的 name 子句 (name='something')(ei:主机名、ipAddr、userAgent、HumanId 等)查询表。

我不是 SQL 专家，但我熟悉索引、约束、主键、唯一键、外键等。从这些搜索结果中我看到:

• Mysql Self-Join Performance
• How to tune self-join table in mysql like this?
• Optimize MySQL self join query
• JOIN Performance Issue MySQL
• MySQL JOIN performance issue

他们中的大多数人都对自连接的不良性能有评论，但答案往往是由于缺少索引原因。

所以最后一个问题是:假设所有内容都正确索引，自连接表是否会使其更容易出现性能不佳的情况？

<小时/>

顺便说一句，有关该表的更多信息:可能与问题无关，但很适合我的特定情况:

• 列标志用于标记要删除的记录，因为我在 php 中使用的用户没有对此数据库的 DELETE 权限。抱歉，安全比性能更重要
• 我添加了“类型”，该“类型”将与我从用户代理获得的信息一起使用。 (即:如果有任何东西(至少看起来是)机器人，我们将只搜索类型 5000。
• 不幸的是，列“name”是在主键(具有配置文件和类型)中索引的 varchar。
• 我尝试在 SELECT 查询中使用尽可能多的 INT 和过滤 (WHERE)，以减少最终的性能损失(如果这很重要的话)
• 如果需要，我愿意研究和调整这个东西，除非具有较高 mysql 背景的人告诉我这确实不是一件好事。

这是我正在开发的一个大项目，所以我现在无法用数百万条记录来测试它，但我想知道随着这个项目的增长，性能是否会成为一个问题。任何输入、链接、引用、文档或测试程序(可能在评论中)将不胜感激。

Answer 1

自联接与联接两个不同的表没有什么不同。优化器通常会根据 WHERE 选择一个“表”，然后对另一个进行嵌套循环连接。就您而言，您通过LEFT暗示它应该仅以一种方式工作。 (优化器将忽略该如果它认为不需要它。

已找到该 fiddle 的 key 。

真正的问题是“实体-属性-值”，这是一种在表中布置数据的困惑方式。您的查询似乎是在说“找到一个(限制 1)profile(实体)，该实体具有一对特定的属性(name = Googlebot AND addr = ...)。

如果有两列(name 和 addr)和一个“复合”INDEX(name, addr)，会更容易、更快。

我建议对常见“属性”执行此操作，然后将其余内容放入带有 JSON 字符串的单个列中。请参阅here .