如何在 MyScaleDB 中运行复杂的 SQL 查询进行向量搜索

向量搜索是在基于向量表示的数据集中寻找相似向量或数据点的方法。结构化查询语言（SQL）是管理关系数据库的有效工具。基于 ClickHouse 构建的 MyScaleDB 将 SQL 与向量的强大功能结合起来，以处理 AI 相关的复杂问题。用户可以在结构化数据和向量嵌入（数据）上执行传统的 SQL 和向量查询，以统一且高效的方式解决复杂的查询和分析高维数据。

下面，我们首先讨论一些最流行的高级 SQL 技术。然后，我们通过一些示例来展示 MyScaleDB 如何将复杂的 SQL 查询与向量搜索结合在一个查询中，与ClickHouse 一起优化查询执行，从而更快、更高效地检索数据。

• 通用表达式（CTE）

• 子查询

• 连接多个表，并使用不同的连接类型

通用表达式（CTE）是在主查询中给子查询赋予的名称。这样做的主要原因是简化查询，使其更易于阅读和调试。它有时可以提高性能，这是另一个好处，但主要是为了可读性和简化。

假设你有一个包含客户数据的表，包括他们的姓名、年龄和他们购买的产品，想要确定购买特定产品的客户的平均年龄。

以下是使用 CTE 执行此计算的示例查询：

WITH product_customers AS (
  SELECT name, age
  FROM customer_data
  WHERE product = 'widget'
)
SELECT AVG(age) AS avg_age
FROM product_customers;

该查询使用了 CTE，它是一个临时命名的结果集（子查询），可以在单个查询中引用。

CTE 的名称是`product_customers`，使用一个`SELECT`语句创建。该语句从`customer_data`表中检索购买产品为'widget'的客户的姓名和年龄列。

将子查询移到查询的顶部并给它一个名称将使得更容易理解查询的作用。如果你的子查询选择了一个样本嵌入向量，你可以将子查询命名为`target_vector_embed`之类的名称。当你在主查询中引用它时，就会看到这个名称，并知道它指的是什么。

如果你有一个很长的查询并且需要在多个地方使用相同的逻辑，你可以在查询的顶部定义它，并在主查询中多次引用。

因此，每当有一个子查询时，都可以考虑使用 CTE 来提高查询的可读性。

子查询是嵌套在另一个查询中的简单 SQL 命令。通过嵌套查询，你可以对包含在结果集中的数据设置更大的限制。

子查询可以在查询的多个位置使用，但从 FROM 语句开始最容易。以下是一个基本子查询的示例：

SELECT sub.*
FROM (
  SELECT *
  FROM table
  WHERE conditions
) sub
WHERE sub.column_1 = 'MyScaleDB';

我们来分解一下运行上述查询时会发生什么：

首先，数据库运行“内部查询” - 在括号之间的部分。如果单独运行它，它会产生一个与任何其他查询一样的结果集。一旦内部查询运行，外部查询将使用内部查询的结果作为其基础表运行：

SELECT sub.*
FROM (
  <<内部查询的结果放在这里>>
) sub
WHERE sub.column_1 = 'MyScaleDB';

子查询需要有名称，名称是在括号之后添加的，类似将别名添加到常规表中一样。此查询使用名称为`sub`。

在条件逻辑中使用子查询

你可以在条件逻辑中使用子查询（与 WHERE、JOIN/ON 或 CASE 结合使用）。以下查询返回与数据集中指定条目相同日期的所有条目：

SELECT *
FROM table
WHERE Date = (SELECT Date
              FROM table
              WHERE id='00001');

此查询有效，因为子查询的结果只有一个单元格。大多数条件逻辑将与包含一个单元格结果的子查询一起工作。然而，只有当内部查询包含多个结果时，IN 是唯一一种在内部查询包含多个结果时工作的条件逻辑类型：

SELECT *
FROM table
WHERE Date IN (SELECT Date
              FROM table
              ORDER BY Date
              LIMIT 5);

请注意，在条件语句中编写子查询时，不应包含别名。这是因为子查询被视为一个单独的值（或在 IN 子句中的一组值），而不是作为一个表。

连接通过使用每个表共有的值，从一个或多个表中的列组合生成一个新表。不同类型的连接如下：

• `INNER JOIN`：仅返回匹配的记录。

• `LEFT JOIN`：返回左表的所有记录和右表的匹配记录。

• `RIGHT JOIN`：返回右表的所有记录和左表的匹配记录。

• `FULL JOIN`：当左表或右表中有匹配时，返回两个表的所有记录。

• `CROSS JOIN`：生成整个表的笛卡尔积，因为没有指定“连接键”。

SQL 向量数据库 MyScaleDB 已有的几个功能可帮助用户处理复杂的 SQL 和向量查询。以下的一些示例将展示如何使用 MyScaleDB 的复杂查询功能。

MyScaleDB 支持 CTE，并将在`WITH`子句中定义的代码替换为其余的`SELECT`查询。命名子查询可以在当前和子查询上下文中的任何允许表对象的地方包含。

向量搜索是一种将数据表示为向量的搜索方法。它通常用于图像搜索、视频搜索和文本搜索等应用程序。MyScaleDB 使用`distance()`函数执行向量搜索。它计算指定向量与指定列中的所有向量数据之间的距离，并返回前几个候选项。

在某些情况下，如果指定的向量来自另一个表，或者指定向量的维度很大且不方便表示，用户可以使用 CTE 或子查询。

假设你有一个名为`photo`的向量表，存储与照片库图像相关的元数据信息，包括`id`、`photo_id`和`photo_embed`用于特征向量。

以下示例将选择结果视为 CTE 中的目标向量，以执行向量搜索：

WITH target_photo_embed AS (
  SELECT photo_embed
  FROM photos
  LIMIT 1)
SELECT id, photo_id, distance(photo_embed, target_photo_embed) as dist
FROM photos
ORDER BY dist
LIMIT 10;

连接的支持有限，建议使用子查询作为解决方法。在 MyScaleDB 中，向量搜索基于具有向量列的表上的向量索引。尽管`distance()`函数出现在`SELECT`子句中，但它的值是在表上进行向量搜索时计算的，而不是在连接之后计算的。连接结果可能不是预期的结果。

以下是可能的解决方法：

• 可以在利用向量索引的子查询中使用`distance()...WHERE...ORDER BY...LIMIT`查询模式，并在向量表上获得预期的结果。

• 还可以在WHERE子句中使用子查询来重写连接。

假设你有另一个名为`photo_meta`的表，存储有关照片库图像的信息，包括`photo_id`、`photo_author`、`year`和`title`。以下示例从图像集合中检索2023年拍摄的相关照片：

SELECT t1.photo_id, distance(t1.photo_embed,[0.0269, 0.0316,...]) as dist
FROM photos t1
JOIN photo_meta t2 ON t1.photo_id = t2.photo_id
WHERE t2.year = 2023
ORDER BY dist
LIMIT 5;

运行上述查询时会发生以下情况：

首先，MyScaleDB在表`photos`上执行向量搜索，以获取所需的列`photo_id`和`distance()`函数的前五个相关记录的值：

SELECT photo_id, distance(photo_embed,[0.0269, 0.0316,...]) as dist
FROM photos
ORDER BY dist
LIMIT 5;

然后，join 使用向量表的结果作为其基础表运行：

SELECT t1.photo_id, t1.dist
FROM (<<向量表的结果放在这里>>) t1
JOIN photo_meta t2 ON t1.photo_id = t2.photo_id
WHERE t2.year = 2023;

因为向量搜索不考虑照片拍摄的年份，所以结果可能不正确。为了获得正确的结果，我们需要通过使用子查询来重写连接查询：

SELECT t1.photo_id, t1.dist
FROM (
  SELECT photo_id, distance(photo_embed,[0.0269, 0.0316,...]) as dist
  FROM photos
  WHERE photo_id IN (
    SELECT t1.photo_id
    FROM photos t1 JOIN photo_meta t2 ON t1.photo_id = t2.photo_id
    WHERE t2.year = 2023)
  ORDER BY dist
  LIMIT 5
) t1
ORDER BY dist
LIMIT 5;

写在最后

CTE、子查询和连接等高级 SQL 技术可以帮助用户以更高的精度和效率执行复杂的数据分析和操作。MyScaleDB 将 SQL 和向量的强大功能结合起来，以处理复杂的 AI 相关问题。使用 MyScaleDB，用户可以在结构化数据和向量数据上高效执行传统的 SQL 和向量查询，以解决复杂的查询和分析高维数据。