通过等值连接检索向量

在带索引的片段表中检索,同时只保留属于已知文档的结果。这个 v4.2.6 流程使用内等值连接,并展示一个片段在匹配多条文档记录时会多次出现在结果中。

为片段向量创建 IVF-FLAT 索引,并创建文档表。c0 和 c1 离 [0,0] 最近,但它们的 document_id 没有匹配的文档:

DROP DATABASE IF EXISTS vector_ivf_equi_join;
CREATE DATABASE vector_ivf_equi_join;
USE vector_ivf_equi_join;
SET @saved_experimental_ivf_index = @@experimental_ivf_index;
SET experimental_ivf_index = 1;
SET probe_limit = 1;
CREATE TABLE chunks (id VARCHAR(32) PRIMARY KEY, v VECF32(2) NOT NULL, document_id VARCHAR(32), category VARCHAR(32));
INSERT INTO chunks VALUES ('c0','[0,0]','u','x'),('c1','[0.01,0]','u','x'),('c2','[0.1,0]','d1','x'),('c3','[0.2,0]','d2','z'),('c4','[0.3,0]','d1','y'),('c5','[0.6,0]','d3','outside'),('c6','[0.15,0]',NULL,'null');
CREATE INDEX chunks_ivf USING IVFFLAT ON chunks(v) LISTS=1 OP_TYPE 'vector_l2_ops';
CREATE TABLE documents (row_id INT PRIMARY KEY, document_id VARCHAR(32), label VARCHAR(32), payload VARCHAR(32));
INSERT INTO documents VALUES (1,'d1','x','first'),(2,'d1','y','second'),(3,'d2','z','third'),(4,'d3','outside','fourth'),(5,NULL,'null','fifth');

运行连接后的 Top-K 查询。EXPLAIN 中应包含 ivf_search。结果 ID 依次为 c2、c2、c3:两条文档记录的 document_id='d1',因此 c2 出现两次。mode=force 查询使用精确路径核对结果:

EXPLAIN SELECT a.id FROM chunks a JOIN documents b ON a.document_id=b.document_id ORDER BY l2_distance(a.v,'[0,0]') LIMIT 3;
SELECT a.id FROM chunks a JOIN documents b ON a.document_id=b.document_id ORDER BY l2_distance(a.v,'[0,0]') LIMIT 3;
SELECT a.id FROM chunks a JOIN documents b ON a.document_id=b.document_id ORDER BY l2_distance(a.v,'[0,0]') LIMIT 3 BY RANK WITH OPTION 'mode=force';
SET experimental_ivf_index=@saved_experimental_ivf_index;
DROP DATABASE vector_ivf_equi_join;

该改写适用于内等值连接以及等价的 IN 成员查询。LEFT JOIN 和非等值连接不会使用此改写。IVF-FLAT 是近似检索;这个小数据集用于核对查询行为,不用于衡量生产环境的召回率或速度。过滤、索引提示及更多查询形态详见 IVF 等值连接向量查询。对应的 MatrixOne 回归测试覆盖了该功能。