Hamming Distance for Hybrid Search mu SQLite

Hamming distance ndi njira yofananira yoyambira yomwe imawerengera magawo osiyanasiyana pakati pa zingwe ziwiri, zomwe zimapangitsa kuti ikhale imodzi mwa njira zofulumira komanso zogwira mtima kwambiri pakufufuza moyandikira pafupi ndi oyandikana nawo mumadatabeta. Ikagwiritsidwa ntchito ku SQLite kudzera muzomanga zosaka zosakanika, Hamming mtunda umatsegula luso lofufuza labizinesi popanda kupitilira pazida zodzipatulira za vekitala.

Kodi Hamming Distance Ndi Chiyani Ndipo N'chifukwa Chiyani Zili Zofunika Pakusaka pa Database?

Kutalikirana kumayesa kuchuluka kwa malo pomwe zingwe ziwiri zofananira kutalika zimasiyana. Mwachitsanzo, zingwe zamabinala 10101100 ndi 10001101 zili ndi mtunda wa Hamming wa 2, chifukwa zimasiyana m'malo awiri ndendende. Posakasaka mu database, mawerengedwe owoneka ngati osavutawa amakhala amphamvu kwambiri.

Kusaka kwachikhalidwe kwa SQL kumadalira kufananiza kapena kusanja mawu athunthu, komwe kumakhala kovuta ndi kufanana kwa mawu - kupeza zotsatira kutanthauza zomwezo m'malo mogawana mawu ofanana. Hamming distance bridges the gap this pogwiritsa ntchito manambala a hashi a binary omwe amachokera ku zomwe zili mkati, kulola nkhokwe ngati SQLite kufananitsa ma rekodi mamiliyoni ambiri mu milliseconds pogwiritsa ntchito magwiridwe antchito a XOR.

Chiyerekezocho chinayambitsidwa ndi Richard Hamming mu 1950 munkhani yokonza zolakwika. Zaka makumi angapo pambuyo pake, idakhala yofunika kwambiri pakubweza zidziwitso, makamaka m'makina omwe liwiro limakhala lofunika kwambiri kuposa kulondola kwenikweni. Kuwerengera kwake kwa O(1) pakuyerekeza (pogwiritsa ntchito malangizo a CPU popcount) kumapangitsa kuti ikhale yoyenera mwapadera pamainjini ophatikizika komanso opepuka.

Kodi Kusaka kwa Hybrid Kumaphatikiza Motani Kutalikirana ndi Mafunso Achikhalidwe a SQLite?

Kusaka kophatikiza mu SQLite kumaphatikiza njira ziwiri zopezeranso: kusaka kwa mawu osakira (pogwiritsa ntchito SQLite's build-in FTS5 full-text search extension) ndi kusaka kofanana (pogwiritsa ntchito Hamming mtunda pa zoyika za binary). Palibe njira yokhayo yomwe ingakwanire pazofunikira zamakono.

Njira yosakira yosakanizidwa imagwira ntchito motere:

1. Kukhazikitsa: Chikalata chilichonse kapena rekodi imasinthidwa kukhala vekita yoyandama yotalikirapo pogwiritsa ntchito chilankhulo cha chilankhulo kapena kabisidwe.
2. Kuchulukira kwa Binary: Vekita yoyandama imapanikizidwa kukhala hashi yophatikizika (monga ma bits 64 kapena 128) pogwiritsa ntchito njira ngati SimHash kapena kuyerekezera mwachisawawa, kuchepetsa kwambiri zofunika zosungira.
3. Hamming index storage: Hashi ya binary imasungidwa ngati INTEGER kapena BLOB column mu SQLite, zomwe zimathandiza kuti muzichita zinthu mwachangu panthawi yofunsa mafunso.
4. Zigoli zanthawi ya mafunso: Wogwiritsa ntchito akatumiza funso, SQLite imawerengera mtunda wa Hamming kudzera pa XOR ndi popcount, kubwezera anthu osankhidwa molingana pang'ono.
5. Score fusion: Zotsatira zochokera ku Hamming-based semantic search ndi FTS5 keyword search aphatikizidwa pogwiritsa ntchito Reciprocal Rank Fusion (RRF) kapena zigoli zolemetsa kuti apange mndandanda womaliza.

Kukula kwa SQLite kudzera muzowonjezera zochulukira kapena ntchito zomwe zaphatikizidwa zimapangitsa kuti zomangidwezi zitheke popanda kusamukira kumalo osungira olemera. Zotsatira zake ndi injini yosakira yokha yomwe imagwira ntchito kulikonse komwe SQLite imagwiritsa ntchito - kuphatikiza zida zophatikizika, mapulogalamu a m'manja, ndi zida zam'mphepete.

Key Insight: Kusaka kwa Binary Hamming pa 64-bit hashi kuli pafupifupi 30–50x mwachangu kuposa kufanana kwa cosine pamavekita a float32 ofanana dimensionality. Pamapulogalamu omwe amafunikira kusaka kwa ma sub-10ms pazakale mamiliyoni ambiri opanda zida zapadera, Hamming distance mu SQLite nthawi zambiri ndiye njira yabwino kwambiri yolumikizirana pakati pa kulondola ndi magwiridwe antchito.

Kodi Zochita za Hamming Search mu SQLite ndi Zotani?

SQLite ndi fayilo imodzi, yopanda seva, yomwe imapanga zopinga zapadera ndi mwayi wogwiritsa ntchito kusaka kwakutali kwa Hamming. Popanda zolozera vekitala ngati HNSW kapena IVF (yomwe imapezeka m'masitolo odzipatulira), SQLite imadalira kusaka kwa Hamming - koma izi ndizochepa kuposa momwe zimamvekera.

Kuwerengera mtunda wa 64-bit Hamming kumangofunika XOR yotsatiridwa ndi kuchuluka kwa anthu (chiwerengero cha anthu, kuwerengera magawo). Ma CPU amakono amachita izi mu malangizo amodzi. Kujambula kwathunthu kwa 1 miliyoni 64-bit hashes kumamaliza pafupifupi 5-20 milliseconds pa hardware hardware, kupangitsa SQLite kukhala yothandiza kwa ma dataset mpaka ma rekodi mamiliyoni angapo popanda maupangiri owonjezera.

Pazinthu zokulirapo, kusintha kwa magwiridwe antchito kumabwera kuchokera pakusefa koyambirira: kugwiritsa ntchito ziganizo za SQLite's WHERE kuchotsa mizere ndi metadata (madeti, magulu, magawo a ogwiritsa ntchito) musanagwiritse ntchito mtunda wa Hamming, kuchepetsa kukula kwa sikaniyo molingana ndi malamulo akulu. Apa ndipamene masakidwe amitundu yosiyanasiyana amawaliradi - mawu osakira achinsinsi amakhala ngati sefa yachangu, ndipo mtunda wa Hamming amayikanso omwe adatsala.

Kodi Mumakhazikitsa Bwanji Ntchito Yoyenda Distance mu SQLite?

SQLite sichiphatikiza ntchito yakutali ya Hamming, koma C extension API imapangitsa kuti scalar igwire ntchito molunjika kuti mulembetse. Ku Python pogwiritsa ntchito sqlite3 gawo, mutha kulembetsa ntchito yomwe imawerengera mtunda wa Hamming pakati pa magawo awiri:

Ntchitoyi imavomereza mfundo ziwiri zofananira zoimira ma hashi a binary, ndikuwerengera XOR yawo, kenako ndikuwerengera ma bits pogwiritsa ntchito Python's bin().count('1') kapena njira yosinthira mwachangu. Akalembetsa, ntchitoyi imapezeka muzofunso za SQL monga momwe zimapangidwira, kupangitsa mafunso monga kusankha mizere pomwe mtunda wa Hamming kupita ku hashi wamafunso umagwera pansi pamlingo, wolamulidwa ndi kukwera mtunda kuti atenge machesi oyandikira kaye.

Pakutumiza zopanga, kupanga logic ya popcount ngati chiwonjezeko cha C pogwiritsa ntchito SQLite's sqlite3_create_function API imabweretsa magwiridwe antchito 10–100x kuposa Python yotanthauziridwa, kubweretsa kusaka kwa SQLite's Hamming kuti athe kufikika kwa nkhokwe za vector zapadera pazantchito zambiri.

Kodi Mabizinesi Ayenera Kusankha Liti Kusaka kwa SQLite Hamming Pa Ma Database Odzipereka?

Kusankha pakati pa kusaka kwa SQLite-based Hamming ndi nkhokwe zodzipatulira za vekitala monga Pinecone, Weaviate, kapena pgvector zimatengera kukula, zovuta zogwirira ntchito, ndi zolepheretsa kutumiza. Kusaka kwa SQLite Hamming ndiye chisankho choyenera ngati kuphweka, kusuntha, ndi mtengo wake ndizofunikira kwambiri - zomwe ndizochitika pazambiri zamabizinesi.

Masamba odzipatulira a vector amayambitsa ntchito yayikulu: zomanga padera, latency ya netiweki, zovuta zamalumikizidwe, ndi mtengo wokulirapo. Pamapulogalamu omwe amathandizira makumi masauzande mpaka mamiliyoni ochepa a zolemba, kusaka kwa SQLite Hamming kumapereka zofunikira zofananira ndi ogwiritsa ntchito ndi ziro zowonjezera. Imaphatikiza zolozera zanu zosaka ndi data yanu ya pulogalamu, ndikuchotsa gulu lonse lamitundu yolephera yamakina.

Mafunso Ofunsidwa Kawirikawiri

Kodi kusaka kwa mtunda wa Hamming ndikokwanira kuti mufufuze zomwe zapangidwa?

Kutalikirana kwa zophatikizika zama binary kusinthanitsa kulondola pang'ono kukumbukira kuti mupeze phindu lalikulu. M'malo mwake, kuchulukitsa kwa binary nthawi zambiri kumasunga 90-95% ya kukumbukira kwamtundu wathunthu wakusaka kofanana kwa float32 cosine. Pazinthu zambiri zofufuzira zamabizinesi - kupeza zinthu, kubweza zolemba, zidziwitso zothandizira makasitomala - kusinthanitsa uku ndikovomerezeka, ndipo ogwiritsa ntchito sangathe kuzindikira kusiyana kwa zotsatira zake.

Kodi SQLite imatha kuwerenga ndikulemba nthawi imodzi panthawi yakusaka kwa Hamming?

SQLite imathandizira kuwerengera nthawi imodzi kudzera mu WAL (Write-Ahead Logging), kulola owerenga angapo kufunsa nthawi imodzi popanda kutsekereza. Lembani concurrency ndi malire - SQLite imalemba motsatira - koma izi nthawi zambiri sizimalepheretsa ntchito zolemetsa zomwe zolemba sizimawerengedwa pafupipafupi. Pamafunso osakira osakanizidwa, SQLite's WAL mode ndiyokwanira.

Kodi kuchulukitsidwa kwa binary kumakhudza bwanji zosungirako poyerekeza ndi ma vector oyandama?

Kusungirako ndikodabwitsa. Kuyika kwa 768-dimensional float32 kumafuna 3,072 byte (3 KB) pa mbiri iliyonse. 128-bit binary hashi yoyika komweko imafuna ma byte 16 okha - kuchepetsedwa kwa 192x. Pagulu la ma rekodi 1 miliyoni, izi zikutanthauza kusiyana pakati pa 3 GB ndi 16 MB ya malo osungira, zomwe zimapangitsa kuti kufufuza kochokera ku Hamming kutheke m'malo osakumbukira momwe kusungirako sikungakhale kosatheka.

Kupanga zinthu zanzeru, zosakanika ndi mtundu womwe umalekanitsa mabizinesi omwe akukula ndi omwe adayima. Mewayzndi OS ya bizinesi yonse yodalirika ndi ogwiritsa ntchito oposa 138,000, yopereka ma modules ophatikizika a 207 - kuchokera ku CRM ndi analytics kupita ku kasamalidwe kazinthu ndi kupitirira - kuyambira pa $ 19 / mwezi. Siyani kusonkha pamodzi zida zolumikizidwa ndikuyamba kumanga papulatifomu yopangidwira sikelo.

Yambitsani ulendo wanu wa Mewayz lero pa app.mewayz.com ndikuwona zomwe mabizinesi ogwirizana angachitire gulu lanu.