{"id":17028,"date":"2024-05-21T22:48:13","date_gmt":"2024-05-22T05:48:13","guid":{"rendered":"https:\/\/www.pingcap.com\/?post_type=article&p=17028"},"modified":"2024-05-22T17:23:09","modified_gmt":"2024-05-23T00:23:09","slug":"in-age-of-llm-how-to-store-vectors-with-mysql-sql-grammar","status":"publish","type":"article","link":"https:\/\/www.pingcap.com\/ko\/article\/in-age-of-llm-how-to-store-vectors-with-mysql-sql-grammar\/","title":{"rendered":"In Age of LLM: How to Store Vectors with MySQL SQL Grammar"},"content":{"rendered":"\n

As the landscape of artificial intelligence evolves, leveraging large language models (LLMs) for semantic search and AI-driven applications has become increasingly prevalent. One critical component of this new wave is vector search, which allows for the retrieval of data based on its semantic meaning rather than simple keyword matching. This post explores how to store and query vectors in a MySQL-compatible SQL database, TiDB Serverless<\/a>, which offers robust vector search capabilities<\/a>.<\/p>\n\n\n\n

<\/span>Introduction to Vector Search<\/span><\/h2>\n\n\n\n

Vector search is a technique used to find similar items in a dataset based on their vector representations. These vectors, often referred to as embeddings, capture the semantic essence of the data. Whether you’re working with text, images, or other data types, embeddings enable the comparison and retrieval of similar items through distance metrics like cosine similarity or Euclidean distance.<\/p>\n\n\n\n

<\/span>Setting Up TiDB for Vector Search<\/span><\/h2>\n\n\n\n

TiDB Serverless is a distributed SQL database that integrates vector search functionalities, enabling the storage and querying of vector embeddings directly within the database. Here’s a step-by-step guide to getting started:<\/p>\n\n\n\n

    \n
  1. Sign Up for TiDB Serverless:<\/strong>\n
      \n
    1. Join the waitlist<\/a> for the private beta of built-in vector search\u00a0in TiDB Serverless.<\/li>\n\n\n\n
    2. Visit tidbcloud.com<\/a> and sign up.<\/li>\n\n\n\n
    3. Select the eu-central-1<\/code> region as vector search is currently available there.<\/li>\n\n\n\n
    4. Follow the instructions to create a TiDB Serverless cluster with vector support enabled.<\/li>\n<\/ol>\n<\/li>\n\n\n\n
    5. Connect to Your TiDB Cluster:<\/strong>\n
        \n
      1. Navigate to the Clusters page on the TiDB Cloud console.<\/li>\n\n\n\n
      2. Click on your cluster name to access its overview page.<\/li>\n\n\n\n
      3. Use the “Connect” option to get the connection details.<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n\n\n
        \n
        \n

        Try TiDB Serverless with Vector Search<\/p><\/div>\n

        \n

        Join the waitlist for the private beta of built-in vector search.<\/p><\/div>\n

        \n \n

        Join Now<\/p><\/a><\/div><\/div>\n\n\n

        \n
        \n

        Try TiDB Serverless with Vector Search<\/p><\/div>\n

        \n

        Join the waitlist for the private beta of built-in vector search.<\/p><\/div>\n

        \n \n

        Join Now<\/p><\/a><\/div><\/div>\n\n\n\n

        Creating and Managing Vector Data in TiDB<\/h3>\n\n\n\n

        To illustrate how to store and query vectors, let’s walk through creating a table, inserting data, and performing a vector search.<\/p>\n\n\n\n

        Create a Table with Vector Field:<\/strong><\/p>\n\n\n\n

        CREATE TABLE vector_table (\n    id INT PRIMARY KEY,\n    doc TEXT,\n    embedding VECTOR(3)\n);<\/code><\/pre>\n\n\n\n
        Insert Vector Data:<\/strong><\/p>\n\n\n\n
        INSERT INTO vector_table (id, doc, embedding) VALUES\n(1, 'apple', '[1,1,1]'),\n(2, 'banana', '[1,1,2]'),\n(3, 'dog', '[2,2,2]');<\/code><\/pre>\n\n\n\n
        Querying for Nearest Neighbors:<\/strong><\/p>\n\n\n\n
        To find vectors closest to a given vector [1,1,3]<\/code>, you can use cosine similarity:<\/p>\n\n\n\n
        SELECT * FROM vector_table ORDER BY vec_cosine_distance(embedding, '[1,1,3]') LIMIT 3;<\/code><\/pre>\n\n\n\n
        Creating an HNSW Index:<\/strong><\/p>\n\n\n\n
        To optimize vector searches, you can create an HNSW (Hierarchical Navigable Small World) index:<\/p>\n\n\n\n
        CREATE TABLE vector_table_with_index (\n    id INT PRIMARY KEY,\n    doc TEXT,\n    embedding VECTOR(3) COMMENT 'hnsw(distance=cosine)'\n);<\/code><\/pre>\n\n\n\n
        Using TiDB Vector Functions<\/h3>\n\n\n\n
        TiDB supports various vector functions for calculating distances and transforming vectors:<\/p>\n\n\n\n