我构建了一个UI小部件,允许我创建一组嵌套规则。例如,我可以指定以下规则:
Match ALL of these rules
- Document Status == Open
- Has Tag = 'sales'
- Has Tag = 'question'
- Match ANY of these rules
- Has Tag = 'important'
- Has Tag = 'high-priority'
- Has Tag = 'critical-priority'
在英语中,这将转换为以下查询:
Find Documents where status = Open AND has tag 'sales' AND has tag 'question'
AND has at least one of these tags: 'important', 'high-priority', 'critical-priority'
表结构与此类似。
Documents {id, title, status}
Tags {document_id, tag_value}
现在,此时我需要将这组规则转换为 SQL 查询。使用子查询可以相当容易地完成它,但由于性能原因,Id 宁愿避免使用它们。"文档和标记"表可能包含数百万条记录。
SELECT
d.id
FROM
Documents d
WHERE
d.status = 'open'
AND EXISTS (SELECT * FROM Tags t WHERE t.doc_id = d.id AND t.value = 'sales')
AND EXISTS (SELECT * FROM Tags t WHERE t.doc_id = d.id AND t.value = 'question')
AND (
EXISTS (SELECT * FROM Tags t WHERE t.doc_id = d.id AND t.value = 'important')
OR EXISTS (SELECT * FROM Tags t WHERE t.doc_id = d.id AND t.value = 'high-priority')
OR EXISTS (SELECT * FROM Tags t WHERE t.doc_id = d.id AND t.value = 'critical-priority')
)
如何重写此查询以使用更高效的连接?
我可以将前两个标签规则添加为 INNER 联接,但如何处理规则集的后半部分?如果有进一步的规则要求存在标签才能显示文档,该怎么办?
请记住,可以将规则集设置为匹配其中的所有或任何规则,并且理论上它可以嵌套很多次。
关于解决这个问题的大方向有什么想法吗?
更新:
我已经优化了我的表,并找到了一种似乎非常快速的查询表的方法(除了计算匹配记录的数量,这是另一个问题)。我永远不会一次选择超过 100 个文档,并且使用包含 ~600k 和 ~200 万个标签的文档集,此解决方案在 ~0.02 秒内返回结果,这比以前好得多。
有问题的表...
CREATE TABLE `app_documents` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`account_id` int(11) NOT NULL,
`status_id` int(11) DEFAULT NULL,
`subject` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`created` datetime NOT NULL,
`updated` datetime NOT NULL,
PRIMARY KEY (`id`),
KEY `IDX_B91B1DB99B6B5FBA` (`account_id`),
KEY `IDX_B91B1DB96BF700BD` (`status_id`),
KEY `created_idx` (`created`),
KEY `updated_idx` (`updated`),
CONSTRAINT `FK_B91B1DB96BF700BD` FOREIGN KEY (`status_id`) REFERENCES `app_statuses` (`id`),
CONSTRAINT `FK_B91B1DB99B6B5FBA` FOREIGN KEY (`account_id`) REFERENCES `app_accounts` (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=500001 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `app_tags` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`value` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
KEY `value_idx` (`value`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `app_documents_tags` (
`document_id` int(11) NOT NULL,
`tag_id` int(11) NOT NULL,
PRIMARY KEY (`document_id`,`tag_id`),
KEY `IDX_A849587A700047D2` (`document_id`),
KEY `IDX_A849587ABAD26311` (`tag_id`),
CONSTRAINT `FK_A849587ABAD26311` FOREIGN KEY (`tag_id`) REFERENCES `app_tags` (`id`) ON DELETE CASCADE,
CONSTRAINT `FK_A849587A700047D2` FOREIGN KEY (`document_id`) REFERENCES `app_documents` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
我正在测试的查询...
此查询查找同时具有"蓝色"和"绿色"但不是"红色"的所有文档及其标记。
SELECT
d.*
FROM
app_documents d
LEFT JOIN
app_documents_tags dtg ON ttg.document_id = d.id
LEFT JOIN
app_tags tg ON tg.id = dtg.tag_id
WHERE
d.account_id = 1
AND EXISTS (
SELECT
*
FROM
app_tags t1
CROSS JOIN
app_tags t2
CROSS JOIN
app_tags t3
INNER JOIN
app_documents_tags dtg1 ON t1.id = ttg1.tag_id
INNER JOIN
app_documents_tags dtg2 ON dtg1.ticket_id = dtg2.ticket_id AND dtg2.tag_id = t2.id
LEFT JOIN
app_documents_tags dtg3 ON dtg2.ticket_id = dtg3.ticket_id AND dtg3.tag_id = t3.id
WHERE
t1.value = 'blue' AND t2.value = 'green' AND t3.value = 'red' AND dtg3.ticket_id IS NULL AND dtg2.document_id = t.id
)
ORDER BY
d.created
LIMIT 45
我相信这可以使用更好的索引来改进。
按如下方式讨论问题中的查询:
- 收集同时具有销售和问题标签的文档 ID(子查询 AA)
- 收集具有其中一个标记的文档 ID(重要、高优先级、关键优先级)(子查询 BB)
- 合并 AA 和 BB,你会得到子查询 DocsWithValidTagRules
- 将文档与有效标记规则与打开状态的文档表联接
- 执行分页
鉴于该描述,下面是生成的查询:
SELECT Documents.id
FROM
(
SELECT AA.document_id
(
SELECT B.document_id,COUNT(1) tagcount FROM
(
SELECT id FROM app_tags
WHERE `value` IN ('sales','question')
) A
INNER JOIN app_documents_tags B
ON A.id = B.tag_id
GROUP BY B.document_id
HAVING COUNT(1) = 2
) AA
INNER JOIN
(
SELECT B.document_id,COUNT(1) tagcount FROM
(
SELECT id FROM app_tags
WHERE `value` IN ('important','high-priority','critical-priority')
) A
INNER JOIN app_documents_tags B
ON A.id = B.tag_id
GROUP BY B.document_id
) BB
) DocsWithValidTagRules
INNER JOIN Documents
ON DocsWithValidTagRules.document_id = Documents.id
WHERE Documents.status = 'open'
LIMIT page_offset,page_size;
确保文档中有此索引
ALTER TABLE Documents ADD INDEX status_id_index (status,id);
试一试!!
它必须是纯粹的 sql 解决方案吗?
您可以使用这样的东西缩小数据集的范围,它具有单个连接然后,使用检索数据时使用的任何语言来筛选较小的数据集和适当的逻辑。
SELECT
d.id,
t.value
FROM
Documents d
JOIN Tags t_required ON t.doc_id=d.id
WHERE
d.status = 'open'
and t.value IN ('sales', 'question', 'important', 'high-priority', 'critical-priority' )
你有没有考虑过Lucene/Solr
这是我为这个问题所做的。除了上面的关系模型之外,我还将创建另一个表,该表将只有两列"DocumentID"|"MetadataXML"
。当我创建/更新任何文档时,我将创建一个 XML 文档(最好是经过架构验证的文档),该文档准确包含每个文档的所有元数据。然后,我将使用 XPATH 表达式来搜索文档。
它可能不是炽热的,甚至不是快速的。但这个想法的最大优点是,你的数据模型、索引和工作流程是稳定的。所有的复杂性都将被 XML 架构抽象化。
此外,我将在此基础上实现Lucene/Solr,以提供快速的基本搜索。
Fast basic full text search -> Lucene/Solr
Advanced Search -> XML/XPATH expression search
Federated Searches, Rest APIs etc -> SQL
它可能不是炽热的快,甚至不是快。但这个想法的最大优点是,你的数据模型、索引和工作流程是稳定的。所有的复杂性都将被 XML 架构抽象化。
此外,我将在此基础上实现Lucene/Solr,以提供快速的基本搜索。