mysql 人脸向量，欧几里得距离相似查询 | mysql 技术论坛-江南app体育官方入口

如标题，就是通过提取的人脸特征向量，写一个欧几里得 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 库，这里要先下载，这里我已经放在 上面。解压后 zlibwapi.lib 文件放到 cuda 安装位置的 lib 中，zlibwapi.dll 文件放到 cuda 安装位置的 bin 中，最后执行下面代码。

import cv2
import dlib
import 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 = images
for 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
    deterministic
begin
    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

查询就要调用上面函数，通过指定向量与每条库里的向量值算距离，随后按顺序排列查询 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 distance
from face_data 
order by distance asc limit 5

在实际应用中，可能是有一个预登陆账号，然后人脸身份验证类似验证码，所以可能是先有了用户标识，也是取出库里已有的用户人脸特征，然后用库里的人脸向量和摄像头采集的向量做对比。下面就用 numpy 演示两个人脸向量的欧氏距离计算和 obs 虚拟摄像头人脸采集。

欧氏距离

import os
import dlib
import glob
import numpy as np
import 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()

本作品采用《cc 协议》，转载必须注明作者和本文链接