Vector

VECTOR 数据类型用于存储由 32 位浮点数组成的多维数组，专为机器学习、AI 应用和相似性搜索操作而设计。每个向量在创建时都具有指定的固定维度（长度）。

语法

column_name VECTOR(<dimension>)

其中：

• dimension：向量的维度（长度）。必须是正整数，最大值为 4096。
• 元素为 32 位浮点数。

向量索引

TiDB Cloud Lake 支持使用 HNSW (Hierarchical Navigable Small World) 算法创建向量索引，以实现快速的近似最近邻搜索，查询性能可提升 23 倍。

索引语法

VECTOR INDEX index_name(column_name) distance='cosine,l1,l2'

其中：

• index_name：向量索引的名称
• column_name：要建立索引的 VECTOR 列名称
• distance：支持的距离函数。可以是 'cosine'、'l1'、'l2'，或它们的组合，例如 'cosine,l1,l2'

支持的距离函数

基本用法

第 1 步：创建包含向量的表

-- Create table with vector index for efficient similarity search
CREATE OR REPLACE TABLE simple_wiki (
    content VARCHAR,
    embedding VECTOR(1024),
    VECTOR INDEX idx_embedding(embedding) distance='cosine'
);

注意：向表中插入数据时，会自动构建向量索引。

第 2 步：插入向量数据

-- Copy simple wiki embedding vectors
COPY INTO simple_wiki
FROM 'https://lakesql-bin.tidbcloud.com/datasets/simple_wiki.csv'
FILE_FORMAT = (
    TYPE = 'CSV',
    FIELD_DELIMITER = '|',
    RECORD_DELIMITER = '\n',
    SKIP_HEADER = 0
);

第 3 步：执行相似性搜索

-- Find simple wiki similar to a query vector
SELECT
    content,
    cosine_distance(embedding, [-0.039933037,-0.021560734,0.024440102,0.002521759,-0.010272103,-0.0014329843,0.0027769457,0.007870142,-0.046696488,0.008686037,-0.01836951,-0.060036782,0.028973266,0.019946467,-0.0018736516,-0.034127645,-0.029007245,-0.038226556,-0.049822643,-0.022114972,0.044502974,-0.024243174,-0.0054523097,-0.0049053733,0.037541658,0.005481121,0.0020597691,0.019144949,0.037816584,0.060224015,0.02804823,-0.057770427,0.012200337,-0.0456118,-0.0158223,0.010360265,0.006539595,-0.052356012,0.008947708,-0.0233614,0.034112025,-0.006252221,-0.015725758,-0.044914573,-0.023845563,0.00955626,-0.031100346,-0.009765235,-0.029881598,-0.03866581,0.029224394,-0.014723449,-0.008826784,-0.026858984,0.05309897,-0.01766305,0.04784403,-0.02885405,-0.017036816,0.011578803,0.026849564,-0.021650068,0.02662856,0.015286329,0.03861724,0.024659181,-0.010742011,0.026483327,0.02818955,-0.00084676413,0.02860798,-0.030394774,0.017738463,-0.039278828,0.031396013,-0.012227515,-0.015788797,0.039025042,-0.04956346,0.032142702,0.0053956425,-0.009891509,0.024410622,-0.010725118,0.04047523,-0.014926191,0.033690933,0.060940236,0.027889939,-0.020630544,-0.00084394595,-0.004017284,-0.0124509,0.028217629,0.024844026,-0.010981587,0.019735651,0.036471285,-0.010195774,0.016049545,0.023868993,0.02659397,-0.056206383,0.058364574,-0.014834511,0.0030844177,-0.0063245185,-0.02207232,0.006551744,-0.052488152,-0.017905239,-0.015412277,0.022892827,0.016487084,-0.029899051,-0.012708291,-0.035586096,0.02596589,0.0029379118,0.038380604,-0.007877647,0.03518495,-0.002010242,0.010625737,-0.005567636,0.034396384,-0.0014768809,0.031846795,-0.052709747,-0.010257822,0.0061065704,-0.010274223,-0.011405558,-0.016665878,-0.004968878,0.07772871,0.011310182,0.0219365,-0.033417672,-0.04040535,0.052248668,0.014371049,0.041794937,-0.0033441822,-0.019891461,-0.0053792787,0.0023917176,0.0025531051,-0.05433755,0.014126035,-0.012321753,-0.005712273,0.05721316,0.0020843758,0.013004933,-0.019866217,-0.004618878,0.008496106,0.004900125,0.0006457499,-0.041960247,-0.0075987596,0.010396549,0.032008134,-0.002487335,0.015960924,0.08054704,-0.015961738,-0.015006569,0.0034933898,-0.0012687037,0.04909167,-0.018214887,0.04728702,0.017690593,-0.013703448,0.041250657,0.00080612017,0.015576544,0.025256036,-0.026594903,-0.040572796,0.013373454,0.06724198,0.023664603,0.0113675045,0.014927227,-0.012118405,-0.056916624,-0.024240792,0.0033584728,-0.025588699,0.008981375,-0.0014575748,-0.015312046,-0.011540035,0.0064686704,0.015048653,-0.008708635,-0.014301029,-0.0024832096,0.023337826,0.0070429966,0.013144369,0.02041083,0.006277923,0.03897181,0.053174395,-0.016995518,-0.0004172065,0.0076942863,-0.003356909,0.007097678,-0.027790003,-0.003991756,-0.045612056,-0.045199793,0.042348653,-0.02947529,0.01554058,-0.016467432,-0.0026772153,0.0019185699,-0.023279784,-0.008291998,0.011236731,-0.03558683,0.06794362,0.026192881,-0.019645019,-0.039347414,-0.013998269,-0.01699295,0.07261961,-0.020888437,-0.048694804,0.02853197,0.030358918,0.031464137,0.017864136,-0.012815341,-0.017518146,0.023409694,0.02714983,0.012034626,0.023927186,-0.010070786,-0.059033453,0.014911582,0.005095107,-0.03787305,0.01246049,0.017725075,-0.021064913,-0.058367312,-0.003428461,0.020181729,0.012336753,-0.031503335,-0.004799914,-0.05646105,-0.01984429,0.058336996,0.0021011438,-0.008577994,0.01385879,0.024231413,0.029866416,-0.0068756547,-0.04194058,-0.01213994,0.0054214345,0.006465198,-0.010105255,-0.030127116,0.0013121354,0.011868225,0.0066204313,0.042110376,0.023357615,-0.01036481,0.008366333,-0.07369769,0.052336935,-0.005812693,0.03108532,0.02044665,-0.0054136915,-0.027532678,-0.02634361,0.0131402025,-0.011917434,-0.032253496,-0.024929227,0.068166055,-0.0055996566,-0.0024826033,-0.028492251,-0.016555956,-0.007588926,-0.016382232,-0.030875143,0.007845721,0.019659696,-0.009922441,-0.012108754,-0.034166787,-0.037560873,0.075659946,0.016890664,-0.06802754,0.01123077,0.042838987,-0.025188228,-0.027680581,0.044700317,0.0453996,0.04640234,0.01726848,-0.020473402,-0.010681011,-0.0031328944,0.003095883,0.020775313,-0.053987157,0.013772471,-0.07178051,-0.013557999,0.013618166,0.020771785,-0.08447924,-0.03225293,-0.030190913,0.024338268,-0.011804759,-0.061444994,-0.016974993,-0.008051971,0.03636079,-0.02141964,-0.024815733,-0.020394482,0.04416552,0.030860247,-0.041991297,-0.02579322,-0.03452515,0.018474394,0.008934209,-0.028817058,0.028967876,0.038335036,0.03724225,-0.01925004,0.0064753983,0.05628201,0.033970427,0.049811717,0.009217419,0.01841436,0.071094945,0.042066,-0.01651293,-0.03706452,-0.022843873,-0.003728514,-0.020151021,0.014640702,0.021030718,-0.08772823,-0.013650267,0.0051809773,0.009917115,0.011039957,-0.014342247,-0.025776912,0.054495063,0.036790144,0.022757804,-0.026672449,0.054716066,0.02900926,0.0059405244,0.030001359,0.008519217,0.0015233222,0.023925865,-0.0154374195,0.010654856,-0.017145056,0.011398358,-0.013738738,-0.029031495,-0.005606388,0.003882481,-0.027220976,0.044363245,0.013492364,0.032712296,0.017945819,0.015551315,0.0019034904,-0.00018370914,0.013732345,-0.035307534,0.035718374,0.0045968452,0.004034745,-0.03336048,-0.055343043,-0.019631283,0.004699886,0.013996215,0.029814066,-0.000154678,-0.027949505,-0.030347785,-0.028985916,-0.024995457,-0.017796125,-0.018648788,-0.004302015,0.017083483,0.031144693,0.041394103,-0.069151685,0.039736934,-0.009535066,0.04143036,0.04953277,0.0145748975,-0.007152605,-0.017511394,-0.04840339,0.027094962,0.026918083,0.057830565,-0.007880084,0.015743539,-0.0036878956,-0.0567741,-0.022091798,-0.010698142,-0.02245472,-0.01757799,0.037471205,-0.052648116,0.049900196,0.053788442,-0.038865305,0.0057106987,-0.02473526,-0.023820246,0.0045047947,0.012621999,-0.018678498,-0.01813632,-0.00012195826,0.07853716,0.008315526,-0.037119206,-0.020903006,0.041795038,0.0030253849,-0.017176986,0.031759594,-0.009211045,0.03144215,0.029955527,0.031503484,-0.031826288,-0.027378425,0.008766635,-0.03924142,0.03364401,0.010653025,-0.0022892836,-0.035691667,-0.035004787,0.009842149,0.010822758,0.012703574,0.008685773,-0.055794135,0.006090148,-0.03345982,0.0129244495,-0.030476095,-0.032982323,-0.035629928,-0.0490593,0.010204472,0.043598823,-0.012672735,-0.04832159,0.03594714,0.016997708,0.05538169,-0.0023494037,-0.029142532,-0.018542767,-0.051356334,-0.01117003,0.037861805,0.029137477,-0.023386285,-0.007015338,-0.026613887,-0.0028003643,0.025743905,-0.01245939,0.0036800557,0.007767204,0.016269377,-0.025229849,0.01201922,-0.023647215,-0.05849589,-0.07951695,0.024513802,-0.026385475,-0.045055453,-0.0077016805,0.06532769,0.0058879885,0.06656787,0.014269071,0.06382564,-0.0034135026,-0.024335757,-0.030015176,-0.017545601,-0.018646596,-0.108097345,-0.041027952,0.009003027,0.0012762699,0.008280786,-0.019606998,-0.0032755614,-0.027661273,-0.0063646864,0.002834647,-0.041689403,-0.016574679,0.030792264,0.017821124,0.05870724,-0.021904483,-0.0074986443,0.025092801,-0.020244813,0.02656664,-0.0059659267,-0.05309905,-0.075604044,-0.0010960719,0.033456154,0.019391162,-0.020096036,0.010127672,0.020431953,-0.014261546,0.019422999,-0.016376399,-0.022349846,-0.003644317,0.036822524,0.010911726,-0.04329986,0.07033871,-0.020313853,0.04484996,0.011330265,0.051181715,0.005774083,0.00947131,-0.028569514,-0.018492075,0.024055328,-0.016621815,0.02473131,0.03791043,0.016186867,0.024546374,0.022638643,0.04991907,0.04418161,0.006206315,-0.04311258,-0.013379863,-0.04812626,0.009906926,0.010288657,-0.003212413,-0.061669104,-0.024754656,-0.0017176361,0.03030152,-0.074314594,-0.03829444,0.012611487,0.0102494275,0.0045574442,0.0048285653,-0.047645736,-0.011894211,-0.021926671,0.04538916,0.01580292,0.042314507,0.0033757954,-0.034430943,0.010440009,0.025956932,0.028568642,0.0050932574,-0.043323226,0.02765941,-0.0037114113,0.009628618,0.056398317,0.00892722,-0.03103889,-0.016217224,-0.017720226,-0.0053833914,-0.031780284,0.0356075,0.0051690512,-0.04703213,-0.035289314,-0.01132067,0.03265562,0.046341296,0.014659944,-0.03623943,-0.02730759,-0.067547664,-0.0059156655,0.068728045,-0.06053017,0.033377975,-0.034934875,-0.02187493,-0.0012026525,0.03545188,0.007382351,-0.0031839162,0.018858379,0.018395567,0.011549551,-0.0011182075,0.0048722075,-0.050581027,-0.01635241,-0.017237768,0.011315683,-0.028737592,0.019962503,-0.007387627,0.050763622,-0.017169124,0.018606586,0.07163366,0.03734185,0.04736131,-0.06403581,-0.025738036,-0.021992266,-0.01319928,-0.08022283,-0.011005046,-0.03884887,0.006379696,-0.0014145619,-0.008193122,-0.006398966,0.0014773665,0.011084989,0.0044709737,-0.028264794,-0.025398243,0.0111127375,-0.054623317,-0.0068070847,-0.01406893,-0.003096321,0.033400264,-0.028150529,0.03965596,0.013693899,0.043754064,-0.00923469,-0.0072740354,0.04488809,-0.012617044,-0.035217658,-0.008500491,0.0034630534,0.031313743,0.04289801,-0.08151663,0.018859988,0.002810951,0.0005882988,0.0036537999,-0.009238664,0.04078265,-0.028712142,-0.011367192,-0.00848377,0.016381364,-0.021466913,0.04730318,-0.0038116602,0.0405969,-0.04121037,-0.021030288,0.018664027,-0.021762447,-0.011776265,-0.04551895,-0.013061067,-0.00058201264,-0.03639096,-0.021912543,0.020368917,0.027740637,0.04082436,-0.027459636,0.035794843,-0.057347205,0.02196459,-0.022118025,-0.047281083,-0.02563404,-0.06805774,-0.006911853,0.058185928,0.027218588,0.038427565,-0.0042237733,-0.03366896,-0.016229564,-0.029186584,-0.03176718,-0.070049964,0.0093576955,0.0032395318,-0.027114615,0.017352665,-0.013284534,-0.07080266,0.054075196,0.03762294,0.03469,0.044409204,-0.015622288,0.018894294,0.032763142,-0.016044721,-0.0058554937,-0.0087420745,0.03271165,-0.002017524,-0.022812437,0.028922994,0.038672272,0.02727705,0.008183337,0.0029323525,0.004047639,-0.018050514,-0.01703084,0.013070722,0.015186862,-0.02212554,0.04957366,-0.010246647,0.036387265,-0.017691536,0.040001247,0.0073721935,0.024487464,0.023548111,0.014214968,0.021323888,0.0039181644,0.035652403,-0.027979897,0.006655018,-0.014366088,-0.0136551345,0.013268369,0.054273065,0.031008286,0.032894958,-0.016528238,0.025737615,0.020457614,-0.024138859,0.021961076,0.009723186,-0.009472737,-0.0621166,0.006848464,-0.041066024,0.016326025,-0.002710949,0.016825637,0.009205761,0.020563355,0.027524417,0.029369347,-0.027464641,0.06149259,-0.021857154,0.040990386,0.0293085,-0.02500536,-0.0056850496,0.034397673,-0.00606149,-0.085631646,-0.018467443,0.056293353,-0.008269984,0.023202974,-0.0110984435,-0.04166591,-0.012643009,0.00012581052,0.015131127,0.037259698,-0.041649986,-0.06952646,-0.025137953,-0.05989198,-0.0039297743,0.0087265195,-0.0022604368,0.019198727,-0.00920593,0.002025578,-0.002004555,0.02481305,0.025386881,-0.018276915,0.002014735,-0.052969497,-0.0045547136,0.0031341368,-0.008278597,0.041760366,0.05500903,-0.016768616,0.05209667,-0.0033320175,0.0048231543,-0.0057336916,0.015312739,-0.027620234,-0.036065836,0.0051241387,-0.05891828,0.027523719,-0.0031922078,0.010368188,0.04026298,0.06313892,-0.029885303,-0.045074854,-0.023634521,0.028227627,-0.013199961,0.030160183,0.018712047,0.0043861,-0.018602764,-0.021192629,-0.029887471,-0.023583911,0.0038232335,-0.044232395,0.0453464,-0.027457736,0.02174932,-0.014318914,0.010380995,-0.0092946645,0.042118732,-0.05783332,-0.022720316,-0.004284339,0.037610166,-0.007278208,0.027652888,0.02568048,-0.023180764,0.014012257,0.02757744,-0.01648688,-0.00992361,0.031669445,0.0023115673,0.0059848484,-0.02707144,-0.012591426,-0.035704438,-0.036750987,0.00034554247,-0.018280635,-0.018120416,-0.0008562952,0.018715842,0.016269296,-0.010571697,0.021552436,-0.0027777094,-0.015317732,-0.024549993,-0.012991394,-0.03699913,0.04451807,0.015323537,-0.075593166,-0.0030781005,-0.055921625,0.19328497,0.0376175,0.023317544,0.0014488162,0.042639796,0.033370387,-0.017134832,-0.019266812,0.051158763,0.009435641,0.057999156,-0.0063074976,-0.00019830484,-0.01905141,0.0011231634,0.054753337,-0.006751183,0.038803753,0.00080272136,-0.036399104,0.000633084,0.031152539,0.0036446073,0.003571354,-0.014301032,0.03885238,0.030267972,0.05781101,-0.06889907,-0.04174844,0.023709996,-0.022390082,0.0039440948,0.01973794,-0.04242749,0.07741949,-0.047385674,0.00030847068,-0.023209738,0.01804641,-0.018974567,0.015027577,-0.009096456,-0.044668037,0.029737286,-0.020110145,-0.06669809,-0.08157275,0.04192294,-0.020739991,0.010827575,-0.0076488387,0.048057295,-0.016977197,-0.008187232,0.042178452,0.008117904,-0.045084924,0.028771017,-0.036470123,0.040879026,-0.031779155,-0.017179752,0.0033341474,0.018291174,-0.0010442613,0.061321683,-0.005425243,-0.06447074,-0.016938401,0.04149427,-0.076094925,0.042653468,-0.03496534,-0.03362807,0.0046461085,-0.013435743,-0.0010403148,-0.058112156,0.039158575,0.03977117,-0.029119678,0.013936549,-0.043197438,-0.010892551,-0.0072019505,-0.024094881,-0.0034056911,-0.013931113,0.027026255,0.016728511,0.04623942,-0.020803045,-0.024414537,-0.014496899]::VECTOR(1024)) AS distance
FROM simple_wiki
ORDER BY distance ASC
LIMIT 3;

结果：

╭────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────╮
│                                                                           content                                                                           │   distance   │
├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼──────────────┤
│ Bernd FÃ rster ( born 3 May , 1956 ) is a former German football player .                                                                                   │ 0.0031585693 │
│ Nuneaton also has a football team called Nuneaton Town .                                                                                                    │    0.6006012 │
│ Dr Patrick John Hillery ( ; 2 May , 1923 -- April 12 , 2008 ) is an Irish Fianna FÃ il politician and the sixth President of Ireland from 1976 until 1990 . │    0.6014652 │
╰────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────╯

说明：该查询会找出与搜索向量最相似的 3 条 simple wiki。余弦距离值越小，表示相似度越高。

卸载和加载向量数据

卸载向量数据

-- Export vector data to stage
COPY INTO @mystage/unload/
FROM (
    SELECT
        content,
        embedding
    FROM simple_wiki
)
FILE_FORMAT = (TYPE = 'PARQUET');

加载向量数据

-- Create target table for import
CREATE OR REPLACE TABLE simple_wiki_imported (
    content VARCHAR,
    embedding VECTOR(1024),
    VECTOR INDEX idx_embedding(embedding) distance='cosine'
);

-- Import vector data
COPY INTO simple_wiki_imported (content, embedding)
FROM (
    SELECT
        content,
        embedding
    FROM @mystage/unload/
)
FILE_FORMAT = (TYPE = 'PARQUET');

向量函数

参见 向量函数。