{"id":17896,"date":"2024-06-26T05:11:35","date_gmt":"2024-06-26T12:11:35","guid":{"rendered":"https:\/\/www.pingcap.com\/?post_type=article&#038;p=17896"},"modified":"2024-06-26T05:11:39","modified_gmt":"2024-06-26T12:11:39","slug":"unleashing-the-power-of-mysql-with-vector-columns-in-tidb","status":"publish","type":"article","link":"https:\/\/www.pingcap.com\/ko\/article\/unleashing-the-power-of-mysql-with-vector-columns-in-tidb\/","title":{"rendered":"Unleashing the Power of MySQL with Vector Columns in TiDB"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Introduction\"><\/span>Introduction<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>In the realm of modern data management, leveraging advanced search capabilities has become paramount. With the rise of artificial intelligence and machine learning, the need for efficient and effective search mechanisms has intensified. <a href=\"\/tidb\/\">TiDB<\/a>, a cutting-edge distributed SQL database, addresses this demand by introducing vector search capabilities. This article delves into how you can enhance your MySQL database with vector columns using TiDB, unlocking new potentials for your applications.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Understanding_Vector_Search\"><\/span>Understanding Vector Search<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Vector search, also known as similarity search or semantic search, allows you to search based on the meaning of the data rather than the data itself. This is particularly useful for applications involving texts, images, videos, and other types of data where traditional keyword-based searches fall short.<\/p>\n\n\n\n<p>In vector search, data is represented as embeddings\u2014numerical vectors that capture the semantic essence of the data. TiDB&#8217;s vector search uses these embeddings to perform searches based on their semantic similarity, enabling powerful applications in generative AI, semantic search, and more.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Creating_a_Vector_Column_in_TiDB\"><\/span>Creating a Vector Column in TiDB<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>TiDB makes it straightforward to integrate vector search capabilities into your database. Here\u2019s a step-by-step guide to creating a table with vector columns and performing basic operations.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Step 1: Create a TiDB Serverless Cluster<\/h3>\n\n\n\n<p>To get started, create a TiDB Serverless cluster with vector support enabled:<\/p>\n\n\n\n<ol class=\"wp-block-list\" start=\"1\">\n<li>Sign up at <a href=\"https:\/\/tidbcloud.com\/free-trial\/\">tidbcloud.com<\/a>.<\/li>\n\n\n\n<li>Select the <code>eu-central-1<\/code> region, as vector search is currently available only in this region.<\/li>\n\n\n\n<li>Follow the tutorial to create a TiDB Serverless Cluster with vector support.<\/li>\n<\/ol>\n\n\n\n<h3 class=\"wp-block-heading\">Step 2: Create a Table with a Vector Column<\/h3>\n\n\n\n<p>Once your cluster is set up, you can create a table with a vector column. Here\u2019s an example of creating a table with a 3-dimensional vector field:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE TABLE vector_table (\n    id INT PRIMARY KEY,\n    doc TEXT,\n    embedding VECTOR(3)\n);<\/code><\/pre>\n\n\n\n<p>You can then insert records into this table:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>INSERT INTO vector_table VALUES \n(1, 'apple', '&#91;1,1,1]'),\n(2, 'banana', '&#91;1,1,2]'),\n(3, 'dog', '&#91;2,2,2]');<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Step 3: Perform Vector Search Queries<\/h3>\n\n\n\n<p>TiDB allows you to search for nearest neighbors based on vector similarity. For example, to find the nearest neighbors to a given vector using cosine distance, you can run:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM vector_table \nORDER BY vec_cosine_distance(embedding, '&#91;1,1,3]') \nLIMIT 3;<\/code><\/pre>\n\n\n\n<p>This query will return the records closest to the vector <code>[1,1,3]<\/code> based on cosine similarity.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Enhancing_Performance_with_Vector_Indexes\"><\/span>Enhancing Performance with Vector Indexes<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>To speed up vector search queries, you can create an HNSW (Hierarchical Navigable Small World) index, which is optimized for vector searches. Here\u2019s how to create a table with an HNSW index:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>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<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Advanced_Usage_Integrating_with_AI_Frameworks\"><\/span>Advanced Usage: Integrating with AI Frameworks<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>TiDB\u2019s vector search can be seamlessly integrated with various AI frameworks, enabling sophisticated applications. For instance, you can use OpenAI\u2019s embedding models for semantic search. Here\u2019s a Python example using the <code>tidb-vector<\/code> library:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>import os\nfrom openai import OpenAI\nfrom peewee import Model, MySQLDatabase, TextField, SQL\nfrom tidb_vector.peewee import VectorField\n\n# Set up database connection\ndb = MySQLDatabase(\n    'test',\n    user=os.environ.get('TIDB_USERNAME'),\n    password=os.environ.get('TIDB_PASSWORD'),\n    host=os.environ.get('TIDB_HOST'),\n    port=4000,\n    ssl_verify_cert=True,\n    ssl_verify_identity=True\n)\n\n# Define model\nclass DocModel(Model):\n    text = TextField()\n    embedding = VectorField(dimensions=1536)\n\n    class Meta:\n        database = db\n        table_name = \"doc_test\"\n\n# Connect to database and create table\ndb.connect()\ndb.create_tables(&#91;DocModel])\n\n# Example documents\ndocuments = &#91;\n    \"TiDB is an open-source distributed SQL database...\",\n    \"TiFlash is the key component...\",\n    \"TiKV is a distributed and transactional key-value database...\"\n]\n\n# Generate embeddings using OpenAI\nclient = OpenAI(api_key=os.environ.get('OPENAI_API_KEY'))\nembeddings = &#91;client.embeddings.create(input=doc, model=\"text-embedding-3-small\").data&#91;0].embedding for doc in documents]\n\n# Insert documents into database\ndata_source = &#91;{\"text\": doc, \"embedding\": emb} for doc, emb in zip(documents, embeddings)]\nDocModel.insert_many(data_source).execute()\n\n# Query for similar documents\nquestion = \"what is TiKV?\"\nquestion_embedding = client.embeddings.create(input=question, model=\"text-embedding-3-small\").data&#91;0].embedding\nrelated_docs = DocModel.select(DocModel.text, DocModel.embedding.cosine_distance(question_embedding).alias(\"distance\")).order_by(SQL(\"distance\")).limit(3)\n\n# Display results\nfor doc in related_docs:\n    print(doc.distance, doc.text)\n\ndb.close()<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Conclusion\"><\/span>Conclusion<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>TiDB\u2019s vector search capabilities empower you to build advanced search applications by leveraging the semantic meaning of your data. Whether you\u2019re working with text, images, or other data types, integrating vector columns in TiDB can significantly enhance your database\u2019s search functionality.<\/p>\n\n\n\n<p>Start exploring TiDB\u2019s vector search today and take your applications to the next level. Sign up for TiDB Serverless at <a href=\"https:\/\/tidbcloud.com\/free-trial\/\">tidb.cloud<\/a>.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Call to Action<\/h3>\n\n\n\n<p>Ready to unlock the potential of vector search? Get started with TiDB Serverless and experience the future of database technology. <a href=\"https:\/\/tidbcloud.com\/free-trial\/\">Try TiDB Serverless now<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Introduction In the realm of modern data management, leveraging advanced search capabilities has become paramount. With the rise of artificial intelligence and machine learning, the need for efficient and effective search mechanisms has intensified. TiDB, a cutting-edge distributed SQL database, addresses this demand by introducing vector search capabilities. This article delves into how you can [&hellip;]<\/p>\n","protected":false},"author":8,"featured_media":0,"template":"","class_list":["post-17896","article","type-article","status-publish","hentry"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.9 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Unleashing the Power of MySQL with Vector Columns in TiDB<\/title>\n<meta name=\"description\" content=\"Delve into how you can enhance your MySQL database with vector columns using TiDB, unlocking new potentials for your applications.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.pingcap.com\/ko\/article\/unleashing-the-power-of-mysql-with-vector-columns-in-tidb\/\" \/>\n<meta property=\"og:locale\" content=\"ko_KR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Unleashing the Power of MySQL with Vector Columns in TiDB\" \/>\n<meta property=\"og:description\" content=\"Delve into how you can enhance your MySQL database with vector columns using TiDB, unlocking new potentials for your applications.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.pingcap.com\/ko\/article\/unleashing-the-power-of-mysql-with-vector-columns-in-tidb\/\" \/>\n<meta property=\"og:site_name\" content=\"TiDB\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/facebook.com\/pingcap2015\" \/>\n<meta property=\"article:modified_time\" content=\"2024-06-26T12:11:39+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/static.pingcap.com\/files\/2024\/09\/11005522\/Homepage-Ad.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1440\" \/>\n\t<meta property=\"og:image:height\" content=\"714\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:site\" content=\"@PingCAP\" \/>\n<meta name=\"twitter:label1\" content=\"\uc608\uc0c1 \ub418\ub294 \ud310\ub3c5 \uc2dc\uac04\" \/>\n\t<meta name=\"twitter:data1\" content=\"4\ubd84\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.pingcap.com\/article\/unleashing-the-power-of-mysql-with-vector-columns-in-tidb\/\",\"url\":\"https:\/\/www.pingcap.com\/article\/unleashing-the-power-of-mysql-with-vector-columns-in-tidb\/\",\"name\":\"Unleashing the Power of MySQL with Vector Columns in TiDB\",\"isPartOf\":{\"@id\":\"https:\/\/www.pingcap.com\/#website\"},\"datePublished\":\"2024-06-26T12:11:35+00:00\",\"dateModified\":\"2024-06-26T12:11:39+00:00\",\"description\":\"Delve into how you can enhance your MySQL database with vector columns using TiDB, unlocking new potentials for your applications.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.pingcap.com\/article\/unleashing-the-power-of-mysql-with-vector-columns-in-tidb\/#breadcrumb\"},\"inLanguage\":\"ko-KR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.pingcap.com\/article\/unleashing-the-power-of-mysql-with-vector-columns-in-tidb\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.pingcap.com\/article\/unleashing-the-power-of-mysql-with-vector-columns-in-tidb\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.pingcap.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Articles\",\"item\":\"https:\/\/www.pingcap.com\/article\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Unleashing the Power of MySQL with Vector Columns in TiDB\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.pingcap.com\/#website\",\"url\":\"https:\/\/www.pingcap.com\/\",\"name\":\"TiDB\",\"description\":\"TiDB | SQL at Scale\",\"publisher\":{\"@id\":\"https:\/\/www.pingcap.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.pingcap.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"ko-KR\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.pingcap.com\/#organization\",\"name\":\"PingCAP\",\"url\":\"https:\/\/www.pingcap.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"ko-KR\",\"@id\":\"https:\/\/www.pingcap.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/static.pingcap.com\/files\/2021\/11\/pingcap-logo.png\",\"contentUrl\":\"https:\/\/static.pingcap.com\/files\/2021\/11\/pingcap-logo.png\",\"width\":811,\"height\":232,\"caption\":\"PingCAP\"},\"image\":{\"@id\":\"https:\/\/www.pingcap.com\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/facebook.com\/pingcap2015\",\"https:\/\/x.com\/PingCAP\",\"https:\/\/linkedin.com\/company\/pingcap\",\"https:\/\/youtube.com\/channel\/UCuq4puT32DzHKT5rU1IZpIA\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Unleashing the Power of MySQL with Vector Columns in TiDB","description":"Delve into how you can enhance your MySQL database with vector columns using TiDB, unlocking new potentials for your applications.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.pingcap.com\/ko\/article\/unleashing-the-power-of-mysql-with-vector-columns-in-tidb\/","og_locale":"ko_KR","og_type":"article","og_title":"Unleashing the Power of MySQL with Vector Columns in TiDB","og_description":"Delve into how you can enhance your MySQL database with vector columns using TiDB, unlocking new potentials for your applications.","og_url":"https:\/\/www.pingcap.com\/ko\/article\/unleashing-the-power-of-mysql-with-vector-columns-in-tidb\/","og_site_name":"TiDB","article_publisher":"https:\/\/facebook.com\/pingcap2015","article_modified_time":"2024-06-26T12:11:39+00:00","og_image":[{"width":1440,"height":714,"url":"https:\/\/static.pingcap.com\/files\/2024\/09\/11005522\/Homepage-Ad.png","type":"image\/png"}],"twitter_card":"summary_large_image","twitter_site":"@PingCAP","twitter_misc":{"\uc608\uc0c1 \ub418\ub294 \ud310\ub3c5 \uc2dc\uac04":"4\ubd84"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.pingcap.com\/article\/unleashing-the-power-of-mysql-with-vector-columns-in-tidb\/","url":"https:\/\/www.pingcap.com\/article\/unleashing-the-power-of-mysql-with-vector-columns-in-tidb\/","name":"Unleashing the Power of MySQL with Vector Columns in TiDB","isPartOf":{"@id":"https:\/\/www.pingcap.com\/#website"},"datePublished":"2024-06-26T12:11:35+00:00","dateModified":"2024-06-26T12:11:39+00:00","description":"Delve into how you can enhance your MySQL database with vector columns using TiDB, unlocking new potentials for your applications.","breadcrumb":{"@id":"https:\/\/www.pingcap.com\/article\/unleashing-the-power-of-mysql-with-vector-columns-in-tidb\/#breadcrumb"},"inLanguage":"ko-KR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.pingcap.com\/article\/unleashing-the-power-of-mysql-with-vector-columns-in-tidb\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.pingcap.com\/article\/unleashing-the-power-of-mysql-with-vector-columns-in-tidb\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.pingcap.com\/"},{"@type":"ListItem","position":2,"name":"Articles","item":"https:\/\/www.pingcap.com\/article\/"},{"@type":"ListItem","position":3,"name":"Unleashing the Power of MySQL with Vector Columns in TiDB"}]},{"@type":"WebSite","@id":"https:\/\/www.pingcap.com\/#website","url":"https:\/\/www.pingcap.com\/","name":"\ud2f0DB","description":"TiDB | SQL at Scale","publisher":{"@id":"https:\/\/www.pingcap.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.pingcap.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"ko-KR"},{"@type":"Organization","@id":"https:\/\/www.pingcap.com\/#organization","name":"PingCAP","url":"https:\/\/www.pingcap.com\/","logo":{"@type":"ImageObject","inLanguage":"ko-KR","@id":"https:\/\/www.pingcap.com\/#\/schema\/logo\/image\/","url":"https:\/\/static.pingcap.com\/files\/2021\/11\/pingcap-logo.png","contentUrl":"https:\/\/static.pingcap.com\/files\/2021\/11\/pingcap-logo.png","width":811,"height":232,"caption":"PingCAP"},"image":{"@id":"https:\/\/www.pingcap.com\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/facebook.com\/pingcap2015","https:\/\/x.com\/PingCAP","https:\/\/linkedin.com\/company\/pingcap","https:\/\/youtube.com\/channel\/UCuq4puT32DzHKT5rU1IZpIA"]}]}},"card_markup":"        <a class=\"card-article\" href=\"https:\/\/www.pingcap.com\/ko\/article\/unleashing-the-power-of-mysql-with-vector-columns-in-tidb\/\">            <h3>Unleashing the Power of MySQL with Vector Columns in TiDB<\/h3>            <p>Introduction In the realm of modern data management, leveraging advanced search capabilities has become paramount. With the rise of artificial intelligence and machine learning, the need for efficient and effective search mechanisms has intensified. TiDB, a cutting-edge distributed SQL database, addresses this demand by introducing vector search capabilities. This article delves into how you can [&hellip;]<\/p>        <\/a>","_links":{"self":[{"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/article\/17896","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/article"}],"about":[{"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/types\/article"}],"author":[{"embeddable":true,"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/users\/8"}],"wp:attachment":[{"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/media?parent=17896"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}