我构建了一个 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 查询。使用子查询可以很容易地完成，但出于性能原因，我宁愿避免使用它们。 Documents 和 tags 表可能各自包含数百万条记录。

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')   
    )

如何重写此查询以使用更高效的连接？

我可以将前两个 Tag 规则添加为 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

我正在测试的查询...

此查询查找所有文档及其标签同时具有标签“blue”和“green”但没有“red”的标签。

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
• 将 DocsWithValidTagRules 与打开状态的 Documents 表联接
• 执行分页

鉴于该描述，这里是结果查询:

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);

试一试!!!

关于mysql - 从嵌套规则生成器生成 MySQL 查询，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/10874159/