MySQL 人脸向量，欧几里得距离相似查询
作者：北桥苏
- 2023-11-13 广东
本文字数：9378 字
阅读完需：约 31 分钟
前言
如标题，就是通过提取的人脸特征向量，写一个欧几里得 SQL 语句，查询数据库里相似度排前 TOP_K 个的数据记录。做法虽然另类，业务层市面上有现成的面部检索 API，技术层现在有向量数据库。
用 MySQL 关系型存储 128 维人脸向量，先是进行欧式距离计算就要对每维循环，开根号后还要排序。数据一旦特别多的时候，这查询速度可想而知。但是可能就是有特别小众的需求，下面就从特征提取到 SQL 拼写几个步骤开始梳理。
环境
python 3.8
opencv-python 4.8
dlib 19.24.2
mysql 5.7
特征向量提取
其实这一步可以跳过，但是没有这个，随便造的人脸向量数据不标准。这里就以读取图片人脸方式，用 dlib 库的人脸关键点检测器和面部识别模型，生成一个 128D 向量。这里有一个注意的是在生成向量时，windows 可能会出现下面错误。
Could not locate zlibwapi.dll. Please make sure it is in your library path!
复制代码
确实 zlibwapi 库，这里要先下载，这里我已经放在 gitee.com/gaoxingqufuhchao/opencv_demo 上面。解压后 zlibwapi.lib 文件放到 CUDA 安装位置的 lib 中，zlibwapi.dll 文件放到 CUDA 安装位置的 bin 中，最后执行下面代码。
import cv2import dlibimport os
images_path = os.path.join("./imgs/", "62.jpg")# 返回图片的像素RGB值数组images = cv2.imread(images_path)
# 人脸检测器face_detector = dlib.get_frontal_face_detector()faces = face_detector(images, 1)face = faces[0] # 取第一张脸
# 人脸的上下左右坐标face_left = face.left()face_right = face.right()face_top = face.top()face_bottom = face.bottom()
# 绘制图片上人脸矩形位置# rectangle_img = images# cv2.rectangle(rectangle_img, (face_left, face_top), (face_right, face_bottom), (255, 0, 0), 2)# cv2.imwrite('./imgs/draw_imgs/rectangle_test.png', rectangle_img)
# 人脸关键点预测器predictor = dlib.shape_predictor("./lib/dlib/shape_predictor_68_face_landmarks.dat")shape = predictor(images, face)
# 绘制图片上人脸特征点位置point_img = imagesfor p in range(0, 68): cv2.circle(point_img, (shape.part(p).x, shape.part(p).y), 2, (0, 255, 0))# cv2.imwrite('./imgs/draw_imgs/point_test.png', point_img)
# 面部识别模型recognition_model = dlib.face_recognition_model_v1("./lib/dlib/dlib_face_recognition_resnet_model_v1.dat")features = recognition_model.compute_face_descriptor(point_img, shape)features_list = list(features)
print(features_list)
cv2.imshow("frame", point_img)
# 无限期等待用户按键，因此窗口会保持打开cv2.waitKey(0)
cv2.destroyAllWindows()
复制代码
向量数据采集入库 (表结构)
CREATE TABLE `face_data` ( `face_id` bigint(20) NOT NULL COMMENT '编号', `face_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT '人脸名称', `feature_vector` varchar(10240) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '特征向量', `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', `create_time` datetime DEFAULT NULL COMMENT '创建时间', `data_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '数据主键', PRIMARY KEY (`data_id`) USING BTREE) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC;
复制代码
自定义欧氏距离计算函数
由于欧几里得距离需要将两个点的每维数值求差，在 MySQL 中也就是要循环向量被逗号分割的每个数值，然后分别求差在平方累加，最后开根号就是距离值，数越小越相似。以下语句中用到了 SUBSTRING_INDEX 函数通过循环 1 到 128 取出指定长度串，最后的 SUBSTRING_INDEX 是取出最后的数值就是循环每维数值。
CREATE DEFINER=`root`@`localhost` FUNCTION `euclidean_distance`(vector1 VARCHAR(128), vector2 VARCHAR(128)) RETURNS float DETERMINISTICBEGIN DECLARE len1 INT; DECLARE len2 INT; DECLARE i INT default 1; DECLARE sum FLOAT; SET len1 = LENGTH(vector1); SET len2 = LENGTH(vector2); SET sum = 0; while i<129 do SET sum = sum + POW(SUBSTRING_INDEX(SUBSTRING_INDEX(vector1, ',', i), ',', -1) - SUBSTRING_INDEX(SUBSTRING_INDEX(vector2, ',', i), ',', -1), 2); SET i = i + 1; END while; RETURN SQRT(sum); END
复制代码
SQL 语句
查询就要调用上面函数，通过指定向量与每条库里的向量值算距离，随后按顺序排列查询 TOP5
1. 测试计算两个2维向量点的距离
select euclidean_distance("-0.0781729444861412,0.110044464468956", "-0.12611718475818634,0.13194166123867035")
2. 查询指定向量最相似的前5条
SELECT face_id,euclidean_distance('-0.0781729444861412,0.110044464468956,0.06956595182418823,-0.04195471480488777,-0.13797412812709808,-0.01051325537264347,-0.08628914505243301,-0.17037545144557953,0.10026334226131439,-0.12803488969802856,0.23731382191181183,-0.094626285135746,-0.196399986743927,-0.06725674867630005,-0.08001142740249634,0.15153078734874725,-0.14427754282951355,-0.18382087349891663,-0.05251418426632881,-0.031287774443626404,0.1022581234574318,0.0790780633687973,0.008861299604177475,0.020882003009319305,-0.06397232413291931,-0.2706142067909241,-0.107462577521801,-0.03476380184292793,0.043968893587589264,-0.05694590508937836,-0.06642886996269226,0.04123011231422424,-0.18131156265735626,-0.03576948121190071,0.04664576053619385,0.1326863318681717,0.003225848078727722,-0.047573719173669815,0.1651453822851181,-0.029888691380620003,-0.21578450500965118,0.08537548780441284,0.0766519159078598,0.2540043294429779,0.22466638684272766,0.06360028684139252,-0.019438330084085464,-0.1689058244228363,0.043127596378326416,-0.11864039301872253,0.15812252461910248,0.19117693603038788,0.09921862185001373,0.05779439955949783,0.020246407017111778,-0.15069229900836945,-0.029495960101485252,0.1517685353755951,-0.09771490097045898,0.055743880569934845,0.18273179233074188,-0.05583849176764488,0.004194958135485649,-0.11965519189834595,0.17633725702762604,0.07909074425697327,-0.09237003326416016,-0.22499722242355347,0.13512863218784332,-0.16307398676872253,-0.16911545395851135,0.07714464515447617,-0.16546982526779175,-0.1413039118051529,-0.266265332698822,0.022507959976792336,0.4201160669326782,0.12732553482055664,-0.2231403887271881,0.036990948021411896,0.010170978493988514,-0.00629305187612772,0.1687457412481308,0.10473113507032394,0.00399409607052803,-0.013406611979007721,-0.12538184225559235,0.005433365702629089,0.2502303421497345,-0.02349778823554516,-0.07866109907627106,0.21128004789352417,0.0021615102887153625,-0.0009294161573052406,0.06593659520149231,0.031842511147260666,0.008746307343244553,0.05120106786489487,-0.19502216577529907,-0.037121932953596115,-0.027497289702296257,-0.0628679022192955,-0.06642049551010132,0.09603795409202576,-0.10845164209604263,0.13225442171096802,0.005377473309636116,0.04529388248920441,-0.05451689288020134,-0.038580723106861115,-0.12509524822235107,0.016826599836349487,0.20309969782829285,-0.22045192122459412,0.2263873666524887,0.12779565155506134,0.118931345641613,0.12429559975862503,0.16083121299743652,0.15235833823680878,0.04558601975440979,-0.07488320767879486,-0.21370939910411835,-0.07309035211801529,0.0741523876786232,-0.013888441026210785,0.16118189692497253,0.10571793466806412', feature_vector) AS distancefrom face_data order by distance asc limit 5
复制代码
其他
在实际应用中，可能是有一个预登陆账号，然后人脸身份验证类似验证码，所以可能是先有了用户标识，也是取出库里已有的用户人脸特征，然后用库里的人脸向量和摄像头采集的向量做对比。下面就用 numpy 演示两个人脸向量的欧氏距离计算和 OBS 虚拟摄像头人脸采集。
欧氏距离
import osimport dlibimport globimport numpy as npimport cv2
# 计算人脸特征向量的欧式距离face_01 = [-0.11144012212753296,0.18558962643146515,0.0016858093440532684,-0.030448582023382187,-0.12307003140449524,-0.03573177754878998,-0.09033556282520294,-0.11640644073486328,0.1467275768518448,-0.020302172750234604,0.3062277138233185,-0.0453588105738163,-0.19605347514152527,-0.0651734471321106,-0.058096982538700104,0.16225126385688782,-0.21905581653118134,-0.1029929518699646,0.013068988919258118,0.04029736667871475,0.05545109510421753,0.048230767250061035,0.0634097084403038,0.08036009967327118,-0.024840131402015686,-0.30172669887542725,-0.07167031615972519,-0.04815453290939331,0.09843403100967407,-0.07043944299221039,-0.11155916005373001,0.06715665757656097,-0.1751995086669922,-0.09061796963214874,0.07401317358016968,0.06398393213748932,-0.05072097107768059,-0.021325048059225082,0.2341330200433731,-0.02269885316491127,-0.15249019861221313,0.05262574926018715,0.08381971716880798,0.3157000243663788,0.20683002471923828,0.07494638860225677,0.000228429795242846,-0.12610206007957458,0.046763330698013306,-0.1762111932039261,0.12894554436206818,0.18361130356788635,0.102451391518116,0.04922708123922348,0.006102066021412611,-0.17877379059791565,-0.04263974353671074,0.11318269371986389,-0.11839815974235535,0.07388490438461304,0.11303036659955978,-0.03952416777610779,0.013598351739346981,-0.09794041514396667,0.27298909425735474,0.08994753658771515,-0.11253070831298828,-0.18397797644138336,0.07603368163108826,-0.10888601094484329,-0.1495959311723709,0.06924907863140106,-0.13828963041305542,-0.162084698677063,-0.3005405366420746,0.017705515027046204,0.33277949690818787,0.1113307923078537,-0.21170896291732788,0.064120352268219,-0.011443777941167355,-0.08527453988790512,0.07820181548595428,0.11378934234380722,-0.055745672434568405,-0.016978316009044647,-0.15705107152462006,-0.021412618458271027,0.22962769865989685,-0.03235346078872681,-0.06656813621520996,0.22491349279880524,0.012072622776031494,0.029958534985780716,0.048272471874952316,0.0701068788766861,-0.043136656284332275,-0.02105512097477913,-0.20309726893901825,-0.07898224890232086,-0.0016847627703100443,-0.059206850826740265,-0.0697859525680542,0.14457839727401733,-0.17818854749202728,0.10272862762212753,0.012656494975090027,0.026718538254499435,-0.04024617373943329,-0.01651758886873722,-0.09254957735538483,-0.024667389690876007,0.13368789851665497,-0.22851689159870148,0.2718657851219177,0.10549997538328171,0.13540413975715637,0.13176411390304565,0.11028678715229034,0.06738141179084778,0.020261352881789207,-0.025652553886175156,-0.11222656071186066,-0.08695542067289352,0.0743352621793747,-0.005103417672216892,0.15722909569740295,0.08712321519851685]face_02 = [-0.06928954273462296,0.11818723380565643,0.040963172912597656,-0.03380622714757919,-0.125632643699646,0.014028718695044518,-0.09829825907945633,-0.13999347388744354,0.10254613310098648,-0.13002724945545197,0.2407195270061493,-0.07027815282344818,-0.25125718116760254,-0.032886065542697906,-0.023477450013160706,0.13404496014118195,-0.16187764704227448,-0.18711647391319275,-0.03598808869719505,-0.023680226877331734,0.06534431874752045,0.06392928957939148,-0.02046673744916916,0.026214662939310074,-0.07153397798538208,-0.3270975947380066,-0.12721474468708038,-0.011338643729686737,0.04254578426480293,-0.07672133296728134,-0.05328984931111336,0.05578911304473877,-0.19925925135612488,-0.0457644984126091,0.05274736136198044,0.10184930264949799,-0.010766003280878067,-0.04257964715361595,0.14281891286373138,-0.0182943232357502,-0.21731841564178467,0.04613770544528961,0.09813840687274933,0.291894793510437,0.19322234392166138,0.052529361099004745,-0.04576069116592407,-0.12129618227481842,0.024770550429821014,-0.12463519722223282,0.14057938754558563,0.19306844472885132,0.11266665160655975,0.03293035924434662,0.032030634582042694,-0.09783944487571716,-0.01481545064598322,0.15577413141727448,-0.1594703644514084,0.05806709825992584,0.1744152009487152,-0.06528271734714508,-0.012003951705992222,-0.09977130591869354,0.15240584313869476,0.07093586027622223,-0.0664982721209526,-0.2397862672805786,0.117387555539608,-0.1688736081123352,-0.12494431436061859,0.11812026053667068,-0.15014362335205078,-0.17380347847938538,-0.25874194502830505,0.0031708646565675735,0.4290091395378113,0.12104129791259766,-0.1617836207151413,0.06288817524909973,0.02954815700650215,-0.07710471004247665,0.17532427608966827,0.08251089602708817,0.007482852786779404,-0.034130796790122986,-0.12541988492012024,0.07749714702367783,0.24795527756214142,0.007125057280063629,-0.06481210887432098,0.2083437591791153,-0.013705004006624222,-0.030975420027971268,0.07249104231595993,0.06181362271308899,-0.010871338658034801,0.0194531362503767,-0.1996041238307953,-0.08055830746889114,-0.018493879586458206,-0.047192126512527466,-0.06708985567092896,0.11301972717046738,-0.1046549454331398,0.10359721630811691,0.03471799194812775,0.04366254806518555,0.004359336569905281,-0.01676999032497406,-0.1265866607427597,-0.05440763011574745,0.17379546165466309,-0.25932636857032776,0.14294737577438354,0.11041377484798431,0.07096583396196365,0.06915411353111267,0.08656369149684906,0.10719767212867737,0.02909727394580841,-0.11240153759717941,-0.24186159670352936,-0.06347794830799103,0.07049560546875,-0.013076946139335632,0.1388940066099167,0.04212008789181709]face_03 = [-0.12067002058029175,0.17179107666015625,0.005495276302099228,-0.0025373362004756927,-0.12682169675827026,-0.046837806701660156,-0.06272760033607483,-0.11881549656391144,0.131977841258049,-0.007651656866073608,0.30675050616264343,-0.06511575728654861,-0.19687950611114502,-0.05430234596133232,-0.061561085283756256,0.15335164964199066,-0.1840490847826004,-0.10880403220653534,0.003372782375663519,0.020364956930279732,0.07791364192962646,0.04028210788965225,0.06327678263187408,0.09058713912963867,-0.041233912110328674,-0.2671954035758972,-0.07270435988903046,-0.05469353869557381,0.10929089039564133,-0.07597753405570984,-0.12340617924928665,0.060622505843639374,-0.19064907729625702,-0.11399897933006287,0.09063999354839325,0.06096772477030754,-0.07348572462797165,-0.03499014303088188,0.2246333211660385,-0.04379507154226303,-0.14929009974002838,0.049476295709609985,0.09826625883579254,0.3019770681858063,0.1847151517868042,0.08934237062931061,-0.01426877174526453,-0.15356388688087463,0.03547971695661545,-0.16697046160697937,0.14273102581501007,0.18264640867710114,0.109134241938591,0.06253807246685028,0.01051153801381588,-0.16892385482788086,-0.024090711027383804,0.09763640910387039,-0.12262223660945892,0.07233531028032303,0.11958196014165878,-0.03347306326031685,0.018053846433758736,-0.11581023037433624,0.24381747841835022,0.07473541796207428,-0.09524650871753693,-0.18174171447753906,0.07709110528230667,-0.12344227731227875,-0.1506771594285965,0.09598767012357712,-0.11854802072048187,-0.1694779396057129,-0.3036326766014099,0.01272799912840128,0.355130672454834,0.11748693883419037,-0.1921916902065277,0.06253601610660553,-0.008992472663521767,-0.08943237364292145,0.07774092257022858,0.09980118274688721,-0.06231308355927467,-0.023926906287670135,-0.15287624299526215,-0.012958861887454987,0.23298338055610657,-0.044136106967926025,-0.06738908588886261,0.2136751115322113,0.020884856581687927,0.019170459359884262,0.05304054170846939,0.07699457556009293,-0.04709392040967941,-0.013687841594219208,-0.1895902156829834,-0.07537028193473816,-0.009934772737324238,-0.056975316256284714,-0.06765390932559967,0.13928769528865814,-0.17901428043842316,0.08535695821046829,0.014542719349265099,0.011990601196885109,-0.04733775928616524,-0.022746175527572632,-0.11427918076515198,-0.02713892236351967,0.11226294934749603,-0.2461158186197281,0.2587936222553253,0.10381574183702469,0.14032721519470215,0.11607213318347931,0.10593312233686447,0.061977699398994446,0.03079364448785782,-0.03547735884785652,-0.12035234272480011,-0.08648461103439331,0.09446684271097183,-0.008687352761626244,0.14321735501289368,0.08460132777690887]# 融合黄face_04 = [-0.0781729444861412,0.110044464468956,0.06956595182418823,-0.04195471480488777,-0.13797412812709808,-0.01051325537264347,-0.08628914505243301,-0.17037545144557953,0.10026334226131439,-0.12803488969802856,0.23731382191181183,-0.094626285135746,-0.196399986743927,-0.06725674867630005,-0.08001142740249634,0.15153078734874725,-0.14427754282951355,-0.18382087349891663,-0.05251418426632881,-0.031287774443626404,0.1022581234574318,0.0790780633687973,0.008861299604177475,0.020882003009319305,-0.06397232413291931,-0.2706142067909241,-0.107462577521801,-0.03476380184292793,0.043968893587589264,-0.05694590508937836,-0.06642886996269226,0.04123011231422424,-0.18131156265735626,-0.03576948121190071,0.04664576053619385,0.1326863318681717,0.003225848078727722,-0.047573719173669815,0.1651453822851181,-0.029888691380620003,-0.21578450500965118,0.08537548780441284,0.0766519159078598,0.2540043294429779,0.22466638684272766,0.06360028684139252,-0.019438330084085464,-0.1689058244228363,0.043127596378326416,-0.11864039301872253,0.15812252461910248,0.19117693603038788,0.09921862185001373,0.05779439955949783,0.020246407017111778,-0.15069229900836945,-0.029495960101485252,0.1517685353755951,-0.09771490097045898,0.055743880569934845,0.18273179233074188,-0.05583849176764488,0.004194958135485649,-0.11965519189834595,0.17633725702762604,0.07909074425697327,-0.09237003326416016,-0.22499722242355347,0.13512863218784332,-0.16307398676872253,-0.16911545395851135,0.07714464515447617,-0.16546982526779175,-0.1413039118051529,-0.266265332698822,0.022507959976792336,0.4201160669326782,0.12732553482055664,-0.2231403887271881,0.036990948021411896,0.010170978493988514,-0.00629305187612772,0.1687457412481308,0.10473113507032394,0.00399409607052803,-0.013406611979007721,-0.12538184225559235,0.005433365702629089,0.2502303421497345,-0.02349778823554516,-0.07866109907627106,0.21128004789352417,0.0021615102887153625,-0.0009294161573052406,0.06593659520149231,0.031842511147260666,0.008746307343244553,0.05120106786489487,-0.19502216577529907,-0.037121932953596115,-0.027497289702296257,-0.0628679022192955,-0.06642049551010132,0.09603795409202576,-0.10845164209604263,0.13225442171096802,0.005377473309636116,0.04529388248920441,-0.05451689288020134,-0.038580723106861115,-0.12509524822235107,0.016826599836349487,0.20309969782829285,-0.22045192122459412,0.2263873666524887,0.12779565155506134,0.118931345641613,0.12429559975862503,0.16083121299743652,0.15235833823680878,0.04558601975440979,-0.07488320767879486,-0.21370939910411835,-0.07309035211801529,0.0741523876786232,-0.013888441026210785,0.16118189692497253,0.10571793466806412]face_01_arr = np.array(face_01)face_02_arr = np.array(face_02)face_03_arr = np.array(face_03)face_04_arr = np.array(face_04)
# 一般小于0.4大概率是同一个人# distance = np.linalg.norm(face_02_arr - face_04_arr)distance = np.linalg.norm(np.array([10,51]) - np.array([15,78]))print(distance)exit()
复制代码
OBS 虚拟摄像头人脸采集
import cv2
indices = 0# 获取OBS虚拟摄像头，真实摄像头填写IP地址cap = cv2.VideoCapture(0)face_xml = cv2.CascadeClassifier("models/haarcascade_frontalface_default.xml") #导入XML文件while True: ret, frame = cap.read() if not ret: break
gray = cv2.cvtColor(frame, cv2.COLOR_BGR2GRAY) # 转换为灰度图 face = face_xml.detectMultiScale(gray, 1.3, 10) # 检测人脸，并返回人脸位置信息
if indices % 3000 == 0: print(face)
indices += 1 for (x, y, w, h) in face: cv2.rectangle(frame, (x, y), (x + w, y + h), (255, 0, 0), 2)
cv2.imshow("frame", frame)
if cv2.waitKey(1) & 0xFF == ord('q'): break
# 释放资源cap.release()cv2.destroyAllWindows()
复制代码
划线
评论
复制
发布于: 刚刚阅读数: 4
北桥苏
关注
公众号：ZERO开发 2023-05-08 加入
专注后端实战技术分享，不限于PHP，Python，JavaScript, Java等语言，致力于给猿友们提供有价值，有干货的内容。
评论