MariaDB：group_concat子查询失败

问题描述：

在MySQL 5.6.34（我的新开发服务器）和MariaDB 10.2.8（我的新生产服务器，我认为我今天终于部署代码的地方 - 完全相同的数据库结构和数据 - 叹气！），MySQL正在工作，而MariaDB不在。这是在MySQL 5.0.95上工作良好的代码。我将查询简化为显示问题的最小示例 - 似乎GROUP_CONCAT()和子查询不混合。下面是该查询：MariaDB：group_concat子查询失败

SELECT person.PersonID, 
GROUP_CONCAT(CategoryID ORDER BY CategoryID SEPARATOR ',') AS categories 
FROM person LEFT JOIN percat ON person.PersonID=percat.PersonID 
WHERE person.PersonID IN (SELECT PersonID FROM action WHERE ActionTypeID=3) 
GROUP BY person.PersonID

，这里是屏幕截图，显示所有三个表所涉及的结构的复合图像：

在MySQL，它工作得很好，因为它一直多年。下面是结果和EXPLAIN：

这是疯狂的结果我得到MariaDB的：

我不知道数据库引擎不够好内部工作按照EXPLAIN，但我认为线索是在那里。我发现这听起来有点相关，但我不太明白他们在说什么，更重要的是，我应该怎么处理这件事。

答

这是一个错误，显然它与您发现的错误并不完全相同（因为上述错误报告中的测试用例在10.2.8上正常工作，而您的确没有）。请随时在MariaDB JIRA举报新的报告。

同时，我认为你应该能够在您的CNF文件中设置

optimizer_switch=orderby_uses_equalities=off

解决它。这是一个新启用的优化，显然不完美。

UPDATE：该错误是现在报告为https://jira.mariadb.org/browse/MDEV-13694

我似乎无法打开如果关闭 - 显然我做错了。在'/ etc/my.cnf'，section ['mysqld]'中，我添加了'optimizer_switch = orderby_uses_equalities = off'（我尝试了使用和不使用单引号 - 我在网络上看到了这两个示例）并重新启动了mysqld也有奇怪的行为 - 它不会给我的ssh提示符，直到我等待一段时间后按^ C）。但是'SELECT @@ optimizer_switch \ G'表示'orderby_uses_equalities'仍然打开，并且查询行为没有变化。 – OsakaWebbie

该部分和线条都很好。首先，检查你的服务器实际上是否重启（例如，通过验证'SHOW STATUS LIKE'Uptime''和/或错误日志）。听起来服务器并没有真正关闭，然后尝试重新启动，并开始争夺数据库，例如用于锁定咏叹调控制文件等。如果服务器确实重新启动，另一个可能的原因是您的安装不使用此cnf文件。你可以在optimizer_switch之后放置一些独特的东西，例如'lock_wait_timeout = 42'（或者你会认识的任何东西），看看它是否被拾取。 – elenst

是的，就是这样 - 它没有真正重新启动（我错误地执行了命令）。你的解决方法就像一个魅力！当我有更多的时间时，我会致力于简化我的代码，但现在，我可以继续推动我的新服务器，谢谢你。 – OsakaWebbie

答

解决方法这不会回答为什么是有区别的，但你应该添加DISTINCT到GROUP_CONCAT。

“为什么”可能的答案是根植于Optimizers。自5.0版本以来发生了很多变化。 5.6有很多新的代码;与此同时，MariaDB正在分成10.0。在这个分叉中，优化者显着地分歧。 10.2已经向前移动了，但未必在优化这种类型的查询。

改进的查询可以对查询做几件事情。有些可能会使其更快：

SELECT p.PersonID, 
     (SELECT GROUP_CONCAT(pc.CategoryID 
           ORDER BY CategoryID SEPARATOR ',') 
      FROM percat 
      WHERE PersonID = p.PersonID 
     ) AS categories 
    FROM person 
    JOIN action AS a ON p.PersonID = a.PersonID 
    WHERE ActionTypeID = 3 
    GROUP BY p.PersonID

改造LEFT JOIN可能会减少对GROUP BY负载。可能GROUP BY可以被删除。由于PRIMARY KEY(PersonID, CategoryID)，应该不需要DISTINCT。

需要索引这个“覆盖索引”会加快速度：INDEX(ActionTypeID, PersonID)。

添加DISTINCT只消除了重复的CategoryID - 它对主要问题没有影响：当应该有倍数时，我仍然只获得一个人记录。关于通过将子查询更改为连接进行优化的想法，请注意我在此处共享的查询是超级简化的 - 在我的真实应用程序中，用户可以组合各种搜索条件，并且必须以编程方式将其选择一个工作查询。如果没有子查询，我一直无法想出办法。 – OsakaWebbie

'GROUP BY PersonID' _causes_每个人只有一行。我想我不知道你想要什么。而你的样本输出每人只有一行。模拟你想要的输出。 –

有四个人匹配WHERE，所以应该返回四行。正确的输出可以在“On MySQL ...”屏幕截图中看到。我不知道为什么前三个人不回来，但这是主要问题。 – OsakaWebbie

MariaDB：group_concat子查询失败

相关推荐