MySQL动态优化，使用变量AND OR NOT运算符

Question

更新-此问题的最终解决方案

我们的动态系统允许对姓名、职务、电话号码等内容进行BOOLEAN插值匹配。因此，我们可以说：

姓名("ted"OR"mike"OR"david"AND"martin"(与职务("developer"AND"senior"NOT"CTO"(与城市("san diego"(

实现这一点的方法是遵循下面的分组示例，该示例是动态创建的。它非常简单，但是HAVING COUNT的使用对于正确定义AND索引是必要的。

在这个例子中，access_indexes也不是帐户可以访问的ID索引的列表，所以如果"；搜索"；返回帐户无法访问的人，该人将不会出现。

感谢大家的帮助，尤其是@BillKarwin！

WITH filter0 AS
(
SELECT pm.ID FROM person_main pm
WHERE MATCH(pm.name_full) AGAINST ('(ted)' IN BOOLEAN MODE)
), 
filter1 AS
(
SELECT ram.object_ref_id AS ID
FROM ras_assignment_main ram
WHERE ram.object_type_c = 1
AND ram.assignment_type_c = 1
AND ram.assignment_ref_id IN (2)
), 
persongroup0_and AS 
( 
SELECT pg0_a.ID FROM
(
SELECT ID FROM filter0            
) pg0_a
GROUP BY pg0_a.ID
HAVING COUNT(pg0_a.ID) = 1
),
persongroup0 AS 
(
SELECT pm.ID
FROM person_main pm 
JOIN persongroup0_and pg0_and ON pm.ID = pg0_and.ID 
),
persongroup1_and AS 
( 
SELECT pg1_a.ID FROM
(
SELECT ID FROM filter1            
) pg1_a
GROUP BY pg1_a.ID
HAVING COUNT(pg1_a.ID) = 1
),  
persongroup1 AS 
(
SELECT pm.ID
FROM person_main pm 
JOIN persongroup1_and pg1_and ON pm.ID = pg1_and.ID
),  
person_all_and AS 
(     
SELECT paa.ID FROM
(
SELECT ID FROM persongroup0 
UNION ALL (SELECT ID FROM persongroup1)             
) paa
GROUP BY paa.ID
HAVING COUNT(paa.ID) = 2
), 
person_all AS 
(
SELECT pm.ID
FROM person_main pm 
JOIN person_all_and pa_and ON pm.ID = pa_and.ID 
),
person_access AS
(
SELECT pa.ID
FROM person_all pa
LEFT JOIN access_indexes ai ON pa.ID = ai.ID
)
SELECT (JSON_ARRAYAGG(pm.ID))
FROM 
(        
SELECT person_sort.ID
FROM
(
SELECT pa.ID
FROM person_access pa
GROUP BY pa.ID
) person_sort    
) pm;

---

我们的前端系统能够使用AND/OR/NOT从多个表中定义动态SQL查询，核心系统运行良好，但由于IN的复合扫描，它的运行速度变慢，无法使用。就我而言，如果不使用IN，我不知道如何拥有这种级别的动态功能。以下是工作得非常好的代码(滤波器匹配非常快(，但IN扫描的复合需要>60秒，因为有些过滤器返回的记录超过50000条。

WITH filter0 AS
(
SELECT pm.ID FROM person_main pm
WHERE MATCH(pm.name_full) AGAINST ('mike meyers' IN BOOLEAN MODE)
), 
filter1 AS
(
SELECT phw.person_main_ref_id AS ID
FROM person_history_work phw
WHERE MATCH(phw.work_title) AGAINST('developer' IN BOOLEAN MODE)                
), 
filter2 AS
(
SELECT pa.person_main_ref_id AS ID
FROM person_address pa
WHERE pa.global_address_details_ref_id IN
(
SELECT gad.ID
FROM global_address_details gad
WHERE gad.address_city LIKE '%seattle%'
)
),        
all_indexes AS
(         
SELECT ID FROM filter0
UNION (SELECT ID FROM filter1)
UNION (SELECT ID FROM filter2)
),         
person_filter AS
(
SELECT ai.ID
FROM all_indexes ai
WHERE 
( 
ai.ID IN            (SELECT ID FROM filter0) 
AND ai.ID NOT IN    (SELECT ID FROM filter1) 
OR ai.ID IN         (SELECT ID FROM filter2)
) 
)

SELECT (JSON_ARRAYAGG(pf.ID)) FROM person_filter pf;

筛选器0有461条记录，筛选器1有48480条，筛选器2有750条。

关键问题在于WHERE语句；因为前端可以在任何"与"上说"与"one_answers"非"；加入"；查询

所以如果我把它改成：

ai.ID IN      (SELECT ID FROM filter0) 
AND ai.ID IN  (SELECT ID FROM filter1) 
AND ai.ID IN  (SELECT ID FROM filter2)

查询耗时超过60秒。因为它在扫描461*448480*750=16761960,00。啊。

当然，如果它是静态存储过程或调用，我可以对此进行硬编码，但它是一个动态插值系统，采用用户定义的设置，因此用户可以定义以上内容。

正如你所看到的，我所做的是创建一个包含所有索引的列表，然后根据前端web工具定义的AND/OR/NOT值来选择它们。

显然IN不适用于此；问题是，我还可以使用哪些不涉及IN的技术，以允许与AND/OR/NOT具有相同级别的灵活性？

---

在评论中更新@BillKarwin

因此，下面的代码可以很好地执行AND、NOT和OR：

SELECT pm.ID
FROM person_main pm
JOIN filter0 f0 ON f0.ID = pm.ID -- AND
LEFT JOIN filter1 f1 ON pm.ID = f1.ID WHERE f1.ID IS NULL -- NOT
UNION (SELECT ID FROM filter2) -- OR

我相信我可以用我们的系统做到这一点；我只需要存储不同的类型(AND/NOT/OR(并在过程中执行它们；让我更新一下，我会给你回复的。

Answer 1

如以上评论中所述：

从逻辑上讲，当很多子查询是表达式的AND项时，您可以用JOIN替换它们，当它们是表达式的or项时，可以用UNION替换它们。同时了解排除联接。

但这并不一定意味着查询会运行得更快，除非您已经创建了支持联接条件和用户定义条件的索引。

但是应该创建哪些索引？

最终，不可能优化用户提出的所有动态查询。您可能可以运行他们的查询(正如您已经在做的那样(，但它们不会有效率。

允许用户指定任意条件是一种失败的游戏。最好给他们一组固定的选项，这些选项是您花时间优化的查询类型。然后允许它们运行"；用户指定的"；查询，但要清楚地标明它并没有优化，而且可能需要很长时间。

Answer 2

避免IN ( SELECT ... )——使用JOIN或EXISTS

避免SELECT ID FROM ( SELECT ID FROM .... )——外部SELECT是不必要的。

将UNION移至外层(在某些情况下(

all_indexes似乎简化为

( SELECT phw.person_main_ref_id AS ID
FROM  person_history_work AS phw
WHERE  MATCH(phw.work_title) AGAINST('developer' IN BOOLEAN MODE) 
) UNION ALL
( SELECT gad.ID
FROM  global_address_details AS gad
WHERE  gad.address_city LIKE '%seattle%' 
) 

你能把最后一部分改成WHERE address_city = 'seattle'吗？如果是，那么您可以使用INDEX(address_city)。如果不是，FULLTEXT索引和MATCH是否适用？

看看你是否可以效仿我，简化剩下的。

WITH最近才被添加到MySQL的语法中。我怀疑它还需要一两个版本才能得到很好的优化；尽量避开CCD_ 10。既然你是"；建筑物；查询，您可以"构建"；UNION、LEFT JOIN等。