mysql - 在多个表中快速搜索特定数据，然后引用主表

Question

我有一个为 MySQL 编写的查询，该查询应该在多个表中搜索特定的数据片段，然后提取“ReferenceID”来引用包含原始文档详细信息的主表。我可以快速搜索这些项目，但是当我尝试从主表中提取具体信息时，它似乎只是破坏了磁盘，而不仅仅是检索数据。

我尝试执行的查询如下所示:

SELECT ReferenceID as refid, Title as title, Contents as content, TimeSecs as age 
            FROM tabledata u
            WHERE u.ReferenceID IN (SELECT DISTINCT a.ReferenceID as refid FROM table1 a WHERE a.Table1Item = "[item]"
UNION SELECT DISTINCT b.ReferenceID as refid FROM table2 b WHERE b.Table2Item = "[item]"
UNION SELECT DISTINCT c.ReferenceID as refid FROM table3 c WHERE c.Table3Item = "[item]") 
ORDER BY TimeSecs DESC LIMIT 50

ReferenceID 搜索中的 SELECT 查询也可以包含多个 OR 语句，但无论如何，这样的查询可能需要 20-30 秒以上，即使跨三个表的查询搜索可能需要半秒。

此外，更令人困惑的是，它使用 filesort 来遍历“tabledata”表，尽管对于其他每个查询都不是这样。还为其适当的列和表创建了索引。

我确实输出了一个解释来证明这一点:

+----+------------------+--------------+----+----------------------+-----------+-------+-----+------+----------------------------+
|id  |select_type       |table         |type|possible_keys         |key        |key_len|ref  |rows  |Extra                       |
+----+------------------+--------------+----+----------------------+-----------+-------+-----+------+----------------------------+
|1   |PRIMARY           |u             |ALL |NULL                  |NULL       |NULL   |NULL |659659|Using where; Using filesort |
|2   |DEPENDENT SUBQUERY|a             |ref |ReferenceID,Table1Item|ReferenceID|98     |func |3     |Using where; Using temporary|
|3   |DEPENDENT UNION   |b             |ref |ReferenceID,Table2Item|Table2Item |386    |const|14    |Using where; Using temporary|
|4   |DEPENDENT UNION   |c             |ref |ReferenceID           |ReferenceID|98     |func |11    |Using where; Using temporary|
|5   |DEPENDENT UNION   |d             |ref |ReferenceID           |ReferenceID|98     |func |7     |Using where; Using temporary|
|NULL|UNION RESULT      |<union2,3,4,5>|ALL |NULL                  |NULL       |NULL   |NULL |NULL  |                            |
+----+------------------+--------------+----+----------------------+-----------+-------+-----+------+----------------------------+

总的来说，我确实得到了我想要的结果，但不是及时。

Answer 1

按照您表达查询的方式，SQL 引擎可能会生成完整的引用 ID 列表，然后开始查找它们。

试试这个:

SELECT ReferenceID as refid, Title as title, Contents as content, TimeSecs as age 
FROM tabledata u
WHERE exists (SELECT 1
              FROM table1 a
              WHERE a.Table1Item = "[item]" and u.ReferenceID = a.ReferenceID
             ) or
      exists (SELECT 1
              FROM table2 b
              WHERE b.Table2Item = "[item]" and u.ReferenceID = b.ReferenceID
             ) or
      exists (SELECT 1
              FROM table3 c
              WHERE c.Table3Item = "[item]" and u.ReferenceID = c.ReferenceID
             ) 
ORDER BY TimeSecs DESC
LIMIT 50;

然后确保三个表中每个表的 ReferenceID、TablexItem 都有索引。