SQL计算DeepFace人脸嵌入欧氏距离无匹配问题排查

需求 #

我将包含Angelina Jolie的合影中所有人脸的embedding存储到SQLite数据库，现在想用她的单人照片生成的embedding在库中匹配对应记录。

问题现象 #

使用SQL计算欧氏距离的查询语句未返回任何结果，而将库中数据加载到内存后用Python计算欧氏距离却能得到匹配结果（距离为8.263514）。

相关素材 #

• 存储到数据库的合影：
• 用于查询的单人照片：

SQL计算欧氏距离的代码及结果 #

代码 #

import math
import pandas as pd
from deepface import DeepFace
import sqlite3

with sqlite3.connect('your_db.db') as conn:
    cur = conn.cursor()

    # 生成目标embedding
    target_img = "angie_single.jpg"
    target_represent = DeepFace.represent(img_path=target_img, model_name="Facenet", detector_backend="retinaface")[0]
    target_embedding = target_represent["embedding"]

    # 构造目标embedding的SQL子查询
    target_statement = ""
    for i, value in enumerate(target_embedding):
        target_statement += 'select %d as dimension, %s as value' % (i, str(value))
        
        if i < len(target_embedding) - 1:
            target_statement += ' union all '

    # 构造距离计算查询语句
    select_statement = f'''
      select * 
      from (
          select img_name, sum(subtract_dims) as distance_squared
          from (
              select img_name, (source - target) * (source - target) as subtract_dims
              from (
                  select meta.img_name, emb.value as source, target.value as target
                  from face_meta meta left join face_embeddings emb
                  on meta.id = emb.face_id
                  left join (
                      {target_statement}  
                  ) target
                  on emb.dimension = target.dimension
              )
          )
          group by img_name
      )
      where distance_squared < 100
      order by distance_squared asc
  '''

    # 执行查询并处理结果
    results = cur.execute(select_statement)
    instances = []

    for result in results:
        img_name = result[0]
        distance_squared = result[1]
        instances.append([img_name, math.sqrt(distance_squared)])
    
    result_df = pd.DataFrame(instances, columns = ['img_name', 'distance'])
    print(result_df)

查询结果 #

Empty DataFrame
Columns: [img_name, distance]
Index: []

Python计算欧氏距离的代码及结果 #

代码 #

import numpy as np

def findEuclideanDistance(row):
    source = np.array(row['embedding'])
    target = np.array(row['target'])
    distance = (source - target)
    return np.sqrt(np.sum(np.multiply(distance, distance)))

计算结果 #

img_name                                          embedding                                             target  distance
0  angie_group.jpg  [0.10850527882575989, 0.5568691492080688, 0.81...  [-0.6434235572814941, 0.5883399248123169, 0.29...  8.263514

疑问 #

SQL代码中缺少了什么？为何无法匹配到结果？

问题分析与解决 #

问题出在JOIN类型的选择上：

1. 原查询使用LEFT JOIN，会导致当face_embeddings或target子查询中无匹配维度时，source或target字段出现NULL值。SQLite中NULL参与算术运算的结果仍是NULL，最终sum(subtract_dims)得到的distance_squared为NULL，无法满足distance_squared < 100的过滤条件，因此返回空结果。
2. Facenet生成的embedding是固定维度（如128维），数据库中存储的face_embeddings和target子查询的维度完全一一对应，应使用INNER JOIN确保只保留维度匹配的有效记录，避免NULL值产生。

修改后的核心查询语句 #

将原查询中的两处LEFT JOIN替换为INNER JOIN：

select * 
from (
    select img_name, sum(subtract_dims) as distance_squared
    from (
        select img_name, (source - target) * (source - target) as subtract_dims
        from (
            select meta.img_name, emb.value as source, target.value as target
            from face_meta meta inner join face_embeddings emb
            on meta.id = emb.face_id
            inner join (
                {target_statement}  
            ) target
            on emb.dimension = target.dimension
        )
    )
    group by img_name
)
where distance_squared < 100
order by distance_squared asc

修改后，sum(subtract_dims)会计算出正确的欧氏距离平方（约68.28，对应开方后的8.263514），满足过滤条件，即可返回预期的匹配结果。

内容的提问来源于stack exchange，提问作者doejoe