优化这个SQL查询

Question

这个SQL查询让我感到厌恶。虽然不是我写的，但它是我们服务器上出现问题的主要原因。我愿意将其拆分为多个查询，并通过PHP进行一些处理（例如RAND()）。

$sql = "SELECT a.code, a.ad_id, a.position, a.type, a.image, a.url, a.height, a.width
    FROM " . AD_TABLE ." a, " . USER_GROUP_TABLE . " g
    WHERE (a.max_views >= a.views OR a.max_views = '0')
    AND (FIND_IN_SET(" .$forum_id. ", a.show_forums) > 0 OR a.show_all_forums = '1')
    AND g.user_id = " . $user->data['user_id'] . "
    AND FIND_IN_SET(g.group_id, a.groups)
    AND FIND_IN_SET(" . $user->data['user_rank'] . ", a.ranks)
    AND a.start_time < " . time() . "
    AND a.end_time > " . time() . "
    AND (a.clicks <= a.max_clicks OR a.max_clicks = '0')
    ORDER BY rand()";

哎呀，粘贴那个之后我感觉很不舒服...

编辑：以下是格式为逗号分隔的样本查询的“解释”结果：

"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
1,"SIMPLE","g","ref","user_id","user_id","3","const",6,"Using temporary; Using filesort"
1,"SIMPLE","a","ALL","max_views","","","",10,"Using where"

It is

- Eric Martindale

---

你是在提问，还是想免费获得一些SQL优化的建议？ - Mathew

你介意执行一个 EXPLAIN SELECT 并发布结果吗？ - alexn

厌恶是修复的引擎。 - D'Arcy Rittich

将您的连接条件从find-in-set更改为“like”语法并尝试。而不是：find-in-set（g.group_id，a.groups） 尝试：a.groups like'％'。g.group_id。'％' - blispr

Answer 1

您在这里有三个主要问题：

1. FIND_IN_SET。

   它不支持SARGABLE，索引无法使其更快。请创建一个多对多关系表（或表）。

   • a.start_time < GETDATE() AND a.end_time > GETDATE()

   MySQL 在优化此方面不太好。您可以将时间跨度保存为几何框并在其上创建 SPATIAL INDEX，这会快得多（尽管不太易读）

   • ORDER BY RAND()

   如果您使用它来抽样数据（即您不需要所有行，而是需要一个小的随机子集），则有一种更有效的方法可以完成此操作，我在博客中描述了该方法：

   • 选择随机行

- Quassnoi

Answer 2

• 在此查询中，表a和表g之间没有显式的连接：它们只通过find_in_set(g.group_id, a.groups)相关联。
• "a.groups"中的集合有多大，即CSV字符串大部分时间是否包含一个组ID？
• 如果99%的情况下只包含1个组，则在php中对"a.groups"进行foreach循环，并从查询中执行实际连接（或可能完全消除您的user_group_table）。
• 对于少数具有多个组成员资格的情况，该查询仍将使用显式连接正常运行。

这将在您的php类/函数中添加一些代码。

- blispr

---

好的，再试一次。这可能非常简单。使用 "and a.groups like " . "%" . g.group_id . "%"这将评估为 "and 'grp1,grp2,grp4' like '%grp1%' "，然后您将得到匹配结果。 - blispr

Answer 3

我猜你的问题可能与日期有关。

AND a.start_time < " . time() . "
AND a.end_time > " . time() . "

我建议在这些字段上创建索引，看看是否有帮助。比较日期会导致数据库与表中的每一行进行比较。

- northpole

Answer 4

如果你经常执行此操作，请尝试使用绑定变量（而不是字符串拼接），这样查询就不必每次都解析。

编辑：抱歉，没有看到 MYSQL 标签。除非最近有改变，我认为 MySQL 不喜欢准备语句。另一方面，Oracle 非常喜欢它们。

- ShoeLace