## Module 5: Vector Search (v8.5.0) Driven by advancements in AI and the need for efficient data handling in high-dimensional spaces, TiDB supports vector data types and in-database vector search. In this module, you will create a table with the vector data type, and insert records with vector data, use the HNSW index to improve the vector searching performance. + You can employ TiDB as a vectors store that provides immediate advantages, including: + Efficient approximates nearest neighbor searches. + Zero planned and unplanned downtime with limitless scale. + Unified storage for both relational and vector data, scalable to extreme large volumes. ### Step 1. Create a test table with vector data type + Create a table with vector data type and populate random data (32768 rows) into it. The table name is `vs_example.vector_table`. Note that as shown in the output the vector data type column is `embedding`. ```sql CREATE DATABASE vs_example; USE vs_example; DROP SEQUENCE IF EXISTS vs_seq1; CREATE SEQUENCE vs_seq1; DROP TABLE IF EXISTS vector_table; CREATE TABLE vector_table ( id BIGINT AUTO_RANDOM PRIMARY KEY, doc TEXT, embedding VECTOR(3) ); SOURCE populate_vector_table.sql DESC vector_table; SELECT COUNT(1) FROM vector_table; SHOW CREATE TABLE vector_table\G ``` ```output ... Query OK, 16384 rows affected (0.06 sec) Records: 16384 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.10 sec) +-----------+-----------+------+------+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+-----------+------+------+---------+-------+ | id | bigint | NO | PRI | NULL | | | doc | text | YES | | NULL | | | embedding | vector(3) | YES | | NULL | | +-----------+-----------+------+------+---------+-------+ 3 rows in set (0.00 sec) +----------+ | COUNT(1) | +----------+ | 32768 | +----------+ 1 row in set (0.02 sec) *************************** 1. row *************************** Table: vector_table Create Table: CREATE TABLE `vector_table` ( `id` bigint NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */, `doc` text DEFAULT NULL, `embedding` vector(3) DEFAULT NULL, PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin /*T![auto_rand_base] AUTO_RANDOM_BASE=625585 */ 1 row in set (0.01 sec) ``` ### Step 2. Run a K-Nearest neighbor search and examine the execution plan + Review the following query. It shows a typical K-nearest neighbor search by using the cosine distance. + The algorithm seeks the nearest nodes (top 3) to a reference node `[0.10752534,0.3524283,0.46134815]` within the dataset - table `vector_table`. Your result will differ from the sample output due to the use of random data in Step 1. ```sql SELECT doc, embedding FROM vector_table ORDER BY VEC_COSINE_DISTANCE(embedding, '[0.10752534,0.3524283,0.46134815]') LIMIT 3; ``` ```output +-------+-----------------------------------+ | doc | embedding | +-------+-----------------------------------+ | 3343 | [0.10953534,0.3534283,0.46134815] | | 20717 | [0.18479975,0.59234893,0.7714548] | | 19597 | [0.20609,0.6679932,0.8865328] | +-------+-----------------------------------+ 3 rows in set (0.02 sec) ``` + Review the execution plan to verify that a `FullTableScan` is used in the query. Considering the table `vector_table` comprises only 32768 rows, the response time is notably swift, it's crucial to consider the potential performance ramifications, especially when dealing with billions of rows where the impact is substantial and cannot be overlooked. Take note of the operation at the bottom of the explain output, where the estimated number of rows (`estRows`) is a number much larger than `3.00`. ```sql EXPLAIN SELECT doc, embedding FROM vector_table ORDER BY VEC_COSINE_DISTANCE(embedding, '[0.10752534,0.3524283,0.46134815]') LIMIT 3; ``` ```output +--------------------------------+----------+-----------+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +--------------------------------+----------+-----------+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------+ | Projection_14 | 3.00 | root | | vs_example.vector_table.doc, vs_example.vector_table.embedding | | └─TopN_7 | 3.00 | root | | Column#4, offset:0, count:3 | | └─Projection_15 | 3.00 | root | | vs_example.vector_table.doc, vs_example.vector_table.embedding, vec_cosine_distance(vs_example.vector_table.embedding, [0.11,0.35,0.46])->Column#4 | | └─TableReader_13 | 3.00 | root | | data:TopN_12 | | └─TopN_12 | 3.00 | cop[tikv] | | vec_cosine_distance(vs_example.vector_table.embedding, [0.11,0.35,0.46]), offset:0, count:3 | | └─TableFullScan_11 | 30179.00 | cop[tikv] | table:vector_table | keep order:false | +--------------------------------+----------+-----------+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------+ 6 rows in set (0.00 sec) ``` ### Step 3. Add a HNSW index on vector column + Enable TiFlash replica. ```sql ALTER TABLE vector_table SET TIFLASH REPLICA 1; ``` + Create a HNSW vector index on column `embedding`. The HNSW index provides a good performance with approximated accuracy (greater than 98% in most cases). ```sql CREATE VECTOR INDEX embedding_idx ON vector_table ((VEC_COSINE_DISTANCE(embedding))) USING HNSW; ``` + Confirm the column is completely indexed. Verify that the `ROWS_STABLE_INDEXED` is `32768`. ```sql SELECT * FROM information_schema.tiflash_indexes WHERE tidb_table='vector_table'\G ``` ```output *************************** 1. row *************************** TIDB_DATABASE: vs_example TIDB_TABLE: vector_table TABLE_ID: 114 COLUMN_NAME: embedding INDEX_NAME: embedding_idx COLUMN_ID: 3 INDEX_ID: 1 INDEX_KIND: HNSW ROWS_STABLE_INDEXED: 32768 ROWS_STABLE_NOT_INDEXED: 0 ROWS_DELTA_INDEXED: 0 ROWS_DELTA_NOT_INDEXED: 0 ERROR_MESSAGE: TIFLASH_INSTANCE: 10.90.4.131:3930 1 row in set (0.00 sec) ``` ### Step 4. Run the K-Nearest neighbor query against the indexed table + Run the same k-nearest neighbors search against table `vector_table`. ```sql SELECT doc, embedding FROM vector_table ORDER BY VEC_COSINE_DISTANCE(embedding, '[0.10752534,0.3524283,0.46134815]') LIMIT 3; ``` ```output +-------+------------------------------------+ | doc | embedding | +-------+------------------------------------+ | 3343 | [0.10953534,0.3534283,0.46134815] | | 20717 | [0.18479975,0.59234893,0.7714548] | | 24641 | [0.21949558,0.73159087,0.94375384] | +-------+------------------------------------+ 3 rows in set (0.03 sec) ``` ### Step 5. Review the execution plan + Review the execution plan to verify that an approximate nearest neighbor index (`index:embedding_idx(embedding)` object under `access object` column and `annIndex:COSINE` operator under `operator info` column in the output) is used in the query. In a production environment operating at scale, the table `vector_table` is expected to outperform `vector_table_noidx` when serving this query. Take note of the operation at the bottom of the explain output, where the estimated number of rows (`estRows`) is ONLY `3.00`, which is significantly more efficient than the `TableFullScan` in Step 2. In addition to the index optimization, you can also find the `mpp[tiflash]` tasks under `task` column in the output, it's a columnar storage query optimization implemented by TiDB. ```sql EXPLAIN SELECT doc, embedding FROM vector_table ORDER BY VEC_COSINE_DISTANCE(embedding, '[0.10752534,0.3524283,0.46134815]') LIMIT 3; ``` ```output +--------------------------------------+---------+--------------+----------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +--------------------------------------+---------+--------------+----------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------+ | Projection_28 | 3.00 | root | | vs_example.vector_table.doc, vs_example.vector_table.embedding | | └─TopN_10 | 3.00 | root | | Column#5, offset:0, count:3 | | └─Projection_29 | 3.00 | root | | vs_example.vector_table.doc, vs_example.vector_table.embedding, vec_cosine_distance(vs_example.vector_table.embedding, [0.11,0.35,0.46])->Column#5 | | └─TableReader_25 | 3.00 | root | | MppVersion: 2, data:ExchangeSender_24 | | └─ExchangeSender_24 | 3.00 | mpp[tiflash] | | ExchangeType: PassThrough | | └─Projection_26 | 3.00 | mpp[tiflash] | | vs_example.vector_table.doc, vs_example.vector_table.embedding | | └─TopN_23 | 3.00 | mpp[tiflash] | | Column#4, offset:0, count:3 | | └─Projection_27 | 3.00 | mpp[tiflash] | | vs_example.vector_table.doc, vs_example.vector_table.embedding, vec_cosine_distance(vs_example.vector_table.embedding, [0.11,0.35,0.46])->Column#4 | | └─TableFullScan_22 | 3.00 | mpp[tiflash] | table:vector_table, index:embedding_idx(embedding) | keep order:false, annIndex:COSINE(embedding..[0.11,0.35,0.46], limit:3) | +--------------------------------------+---------+--------------+----------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------+ 9 rows in set (0.00 sec) ``` + As previously mentioned, HNSW indexing relies on TiFlash. If you are new to TiDB's columnar storage TiFlash, please check the other hands-on lab - Getting Started with TiDB Cloud Serverless or enroll the self-paced e-learning [Introduction to TiDB](https://www.pingcap.com/education/course-introduction-to-tidb/). Proceed to the next module. + Unlike the 3-dimensional vectors in this module, real-world scenarios often involve vectors with many more dimensions, such as 1536-dimensional vectors generated by an embedding model. We also offer hands-on labs focused on these topics - [Generative AI at TiDB](/labs?category=Generative+AI+at+TiDB). + Please proceed to the next module.