Perform vector retrieval using the MySQL protocol

Scope

Before you use this feature, make sure that your cluster meets the following requirements: The kernel version is MySQL 8.0.2, and the revision version must be 8.0.2.2.30 or later.

Version update history

Supported features vary by kernel version. We recommend that you upgrade to the latest version.

Vector type

-- The following example defines column v1 in table t1 with a vector dimension of 4
CREATE TABLE t1 (id INT PRIMARY KEY, v1 VECTOR(4));

SELECT STRING_TO_VECTOR('[1,2,3,4]');
+-------------------------------+
| STRING_TO_VECTOR('[1,2,3,4]') |
+-------------------------------+
|   �?   @  @@  �@              |
+-------------------------------+

SELECT HEX(STRING_TO_VECTOR('[1,2,3,4]'));
+------------------------------------+
| HEX(STRING_TO_VECTOR('[1,2,3,4]')) |
+------------------------------------+
| 0000803F000000400000404000008040   |
+------------------------------------+

SELECT VECTOR_TO_STRING(0x0000803F000000400000404000008040);
+------------------------------------------------------+
| VECTOR_TO_STRING(0x0000803F000000400000404000008040) |
+------------------------------------------------------+
| [1.00000e+00,2.00000e+00,3.00000e+00,4.00000e+00]    |
+------------------------------------------------------+

Related examples

CREATE TABLE t1 (id INT PRIMARY KEY, v1 VECTOR(4));

ALTER TABLE t1 ADD COLUMN v1 VECTOR(4);

INSERT INTO t1 VALUES (1, STRING_TO_VECTOR('[1.0, 2.0, 3.0, 4.0]')) ON DUPLICATE KEY UPDATE v1 = STRING_TO_VECTOR('[1.0, 2.0, 3.0, 4.0]');

UPDATE t1 SET v1 = STRING_TO_VECTOR('[1.0, 2.0, 3.0, 4.0]') WHERE id = 1;

DELETE FROM t1 WHERE v1 = STRING_TO_VECTOR('[1.0, 2.0, 3.0, 4.0]');

Use vector indexes

PolarDBCreate a vector index by defining a specially formatted string in the COMMENT of a column.

Prerequisites

Before you use a vector index, you must first complete the step Add a read-only IMCI node, and create a clustered columnar index for the column or the entire table. Only read-only nodes of the clustered columnar index can build vector indexes to support vector search.

COMMENT 'imci_vector_index=vector_index_algorithm(index_parameter_list) [other notes]'

-- Option 1: Enable the clustered columnar index for the entire table, and then define a vector index on column v1
CREATE TABLE t1 (
    id INT PRIMARY KEY,
    v1 VECTOR(4) COMMENT 'imci_vector_index=HNSW(metric=COSINE, max_degree=16)'
) COMMENT 'COLUMNAR=1';

-- Option 2: Enable the clustered columnar index and a vector index only for column v1
CREATE TABLE t1 (
    id INT PRIMARY KEY,
    description TEXT,
    v1 VECTOR(4) COMMENT 'COLUMNAR=1, imci_vector_index=HNSW(metric=COSINE)'
);

-- Assume that the existing table is t1
CREATE TABLE t1 (
    id int auto_increment PRIMARY KEY,
    v1 vector(4)
);

-- Option 1: Add a table-level clustered columnar index and modify the comment of the VECTOR column to specify the vector index algorithm and parameters
ALTER TABLE t1 comment "COLUMNAR=1", MODIFY COLUMN v1 vector(4) COMMENT "imci_vector_index=HNSW(metric=COSINE,max_degree=16,ef_construction=300)";


-- Option 2: Create a clustered columnar index only for column v1. When the clustered columnar index is initialized, a vector index is automatically created based on the comment of column v1
ALTER TABLE t1 MODIFY COLUMN v1 vector(4) COMMENT "COLUMNAR=1 imci_vector_index=HNSW(metric=COSINE,max_degree=16,ef_construction=300)";

Vector queries

SELECT VECTOR_TO_STRING(v1) FROM t1;

DISTANCE(vector1, vector2, '<metric>')

SELECT DISTANCE(v1, STRING_TO_VECTOR('[1.2,2.3,3.4,4.5]'), 'COSINE') FROM t1;

-- Find the 10 records most similar to the target
SELECT id, VECTOR_TO_STRING(v1) FROM t1
ORDER BY DISTANCE(v1, STRING_TO_VECTOR('[...]'), 'COSINE') LIMIT 10;

SELECT id, VECTOR_TO_STRING(v1) FROM t1 ORDER BY DISTANCE(v1, STRING_TO_VECTOR('[1.2,2.3,3.4,4.5]'), 'COSINE') LIMIT 1;

Vector queries with predicates

You can use additional columns as predicates to filter vector query results. For example:

SELECT * FROM t1 WHERE id < 10 ORDER BY DISTANCE(v1, STRING_TO_VECTOR('[1.2, 2.3, 3.4, 4.5]'), 'COSINE') LIMIT 2;

When you perform vector queries by using SQL statements that contain predicates, you can adjust the values of related parameters to control the order of vector index recall and predicate filtering.

Execution methods

Vector search is usually accompanied by filtering on other attributes. The optimizer intelligently chooses an execution strategy based on the selectivity of the filter conditions:

• Prefilter: When the filter conditions greatly reduce the amount of data (for example, when the number of matching rows is less than imci_vector_search_prefilter_rows, default: 10000), the system first filters the entire table by using the predicate to obtain the matching records, then sorts these records, and finally returns the number of records specified by LIMIT in order.

• Postfilter: When the selectivity of the filter conditions is low (for example, when the match ratio is higher than imci_vector_search_filter_pct, default: 20%), the system first uses the vector index for recall, and then applies the predicate filter to the recalled results. If the number of results after filtering is less than the number specified by LIMIT, the process is repeated.

• Inline filter: If the optimizer does not choose prefilter or postfilter, it uses the inline filter method. The system first filters the entire table by using the predicate and builds a bitmap filter based on the row IDs of the matching records. Then, it performs recall by using the vector index. During vector index search, the system uses the bitmap filter to screen results so that the returned most similar records meet the predicate conditions.

Adaptive execution optimization

Because the optimizer may misestimate the number of rows matched by predicate filtering, the selected execution method may not be optimal. Therefore, adaptive execution is used to dynamically switch the execution method based on the actual number of matching rows during execution.

When an inline filter is used to execute a vector query that contains a predicate, if the actual number of matching records after filtering the entire table is less than imci_vector_search_prefilter_rows, the execution method is dynamically switched to prefilter.

Partition optimization

Vector queries usually contain predicates such as tenant IDs or tags. For such queries, you can use the LIST DEFAULT HASH partitioning method to create separate LIST partitions for tenants or tags with large amounts of data, and create DEFAULT HASH partitions for other tenants or tags. The optimizer can identify such queries and, based on the partition pruning result, choose postfilter for vector queries of large tenants or tags, and choose prefilter or inline filter for vector queries of other tenants, so that the optimal execution method is selected for tenants or tags of different sizes.

Parameter description

Monitor vector index status

Vector index data building and clustered columnar index snapshot advancement are different background tasks. With continuous writes, the vector index snapshot position may lag behind the clustered columnar index data snapshot. Before you perform vector search, you must confirm that the vector index is available. System views show the vector index status, including the vector index snapshot position and the status of vector indexes on vector columns.

Example

1. To ensure that the vector index can be built properly with a small amount of data in the following example, go to the PolarDB console and set the imci_vector_index_dump_rows_thresholdvalue to 1.

2. Create a test table and insert data:

   CREATE TABLE t1 (
       id int auto_increment PRIMARY KEY,
       v1 vector(4) COMMENT "imci_vector_index=HNSW(metric=COSINE,max_degree=16,ef_construction=300)"
   ) comment "COLUMNAR=1";
   
   INSERT INTO t1 VALUES(1, STRING_TO_VECTOR('[1.1,1.2,1.3,1.4]')), (2, STRING_TO_VECTOR('[2,2.2,2.4,2.6]'));

3. Check the index snapshot status and the vector index status:

   SELECT schema_name,table_name,row_id FROM information_schema.imci_indexes;
   +-------------+------------+--------+
   | schema_name | table_name | row_id |
   +-------------+------------+--------+
   | test        | t1         |      2 |
   +-------------+------------+--------+
   1 row in set (0.00 sec)
   
   SELECT schema_name,table_name,vector_rows FROM information_schema.imci_index_stats;
   +-------------+------------+-------------+
   | schema_name | table_name | vector_rows |
   +-------------+------------+-------------+
   | test        | t1         |           2 |
   +-------------+------------+-------------+
   1 row in set (0.00 sec)
   
   SELECT schema_name,table_name,column_name,vectors FROM information_schema.imci_vector_index_stats;
   +-------------+------------+-------------+---------+
   | schema_name | table_name | column_name | vectors |
   +-------------+------------+-------------+---------+
   | test        | t1         | v1          |       2 |
   +-------------+------------+-------------+---------+
   1 row in set (0.00 sec)

4. Then execute the following write:

   INSERT INTO t1 VALUES(3, STRING_TO_VECTOR("[8.8,8.88,8.888,8.8888]"));

   You can observe that in the query results of INFORMATION_SCHEMA.IMCI_VECTOR_INDEX_STATS the value of the VECTORS field is 3 (increased by 1), which indicates that the data inserted by the preceding INSERT statement has been written to the vector index by the background task:

   SELECT schema_name,table_name,column_name,vectors FROM information_schema.imci_vector_index_stats;
   +-------------+------------+-------------+---------+
   | schema_name | table_name | column_name | vectors |
   +-------------+------------+-------------+---------+
   | test        | t1         | v1          |       3 |
   +-------------+------------+-------------+---------+
   1 row in set (0.00 sec)

5. Then restart the read-only node of the clustered columnar index and check the index status:

   Important

   Restarting the read-only node of the clustered columnar index makes the node unavailable for a short period of time.

   SELECT schema_name,table_name,row_id,state FROM information_schema.imci_indexes;
   +-------------+------------+--------+-----------+
   | schema_name | table_name | row_id | state     |
   +-------------+------------+--------+-----------+
   | test        | t1         |      3 | COMMITTED |
   +-------------+------------+--------+-----------+
   1 row in set (0.00 sec)
   
   SELECT schema_name,table_name,vector_rows FROM information_schema.imci_index_stats;
   +-------------+------------+-------------+
   | schema_name | table_name | vector_rows |
   +-------------+------------+-------------+
   | test        | t1         |           3 |
   +-------------+------------+-------------+
   1 row in set (0.00 sec)
   
   SELECT schema_name,table_name,column_name,vectors FROM information_schema.imci_vector_index_stats;
   +-------------+------------+-------------+---------+
   | schema_name | table_name | column_name | vectors |
   +-------------+------------+-------------+---------+
   | test        | t1         | v1          |       0 |
   +-------------+------------+-------------+---------+
   1 row in set (0.00 sec)

   You can see that IMCI_VECTOR_INDEX_STATS in the system view, VECTORS is 0, while the vector index snapshot position is 3. Use the execution plan to confirm vector index availability, and check the vector index status again after executing an approximate query:

   SET cost_threshold_for_imci = 0;
   
   
   EXPLAIN SELECT * FROM t1 ORDER BY DISTANCE(v1, STRING_TO_VECTOR("[1.2, 2.3, 3.4, 4.5]"), "COSINE") LIMIT 1;
   +----+-----------------------+------+--------+--------+---------------------------------------------------------------------------------------+
   | ID | Operator              | Name | E-Rows | E-Cost | Extra Info                                                                            |
   +----+-----------------------+------+--------+--------+---------------------------------------------------------------------------------------+
   |  1 | Select Statement      |      |        |        | IMCI Execution Plan (max_dop = 4, max_query_mem = 858993459)                          |
   |  2 | +-Compute Scalar      |      |        |        |                                                                                       |
   |  3 |   +-Limit             |      |        |        | Offset=0 Limit=1                                                                      |
   |  4 |     +-Sort            |      |        |        | Sort Key: VECTOR_DISTANCE(t1.v1,"[1.200000,2.300000,3.400000,4.500000]","COSINE") ASC |
   |  5 |       +-Vector Search | t1   |        |        |                                                                                       |
   +----+-----------------------+------+--------+--------+---------------------------------------------------------------------------------------+
   5 rows in set (0.00 sec)
   
   SELECT id, vector_to_string(v1) FROM t1 ORDER BY DISTANCE(v1, STRING_TO_VECTOR("[1.2, 2.3, 3.4, 4.5]"), "COSINE") LIMIT 1;
   +----+---------------------------------------------------+
   | id | vector_to_string(v1)                              |
   +----+---------------------------------------------------+
   |  2 | [2.00000e+00,2.20000e+00,2.40000e+00,2.60000e+00] |
   +----+---------------------------------------------------+
   1 row in set (0.00 sec)
   
   SELECT schema_name,table_name,column_name,vectors FROM information_schema.imci_vector_index_stats;
   +-------------+------------+-------------+---------+
   | schema_name | table_name | column_name | vectors |
   +-------------+------------+-------------+---------+
   | test        | t1         | v1          |       3 |
   +-------------+------------+-------------+---------+
   1 row in set (0.00 sec)

   You can observe that after the approximate query is executed, the vector index is loaded into memory and the number of vectors returns to 3.

FAQ