Loading

python 联表查询耗时长 

    @staticmethod
    def get_user_kb_visit_records(tenant_id: str, user_id: str, page: int, limit: int) -> dict:
        query = KnowledgeBaseUserVisitLog.query.filter(
            KnowledgeBaseUserVisitLog.user_id == user_id,
            KnowledgeBaseUserVisitLog.tenant_id == tenant_id)
        # ).join(
        #     KnowledgeBase,
        #     KnowledgeBaseUserVisitLog.knowledge_base_id == KnowledgeBase.id
        # )
        query = query.order_by(desc(KnowledgeBaseUserVisitLog.last_visit_at))
        pagination = query.paginate(page=page, per_page=limit, error_out=False)

        return pagination

class KnowledgeBaseUserVisitLog(db.Model):
    """
    用户知识库访问记录表
    """
    __tablename__ = 'knowledge_base_user_visit_logs'
    __table_args__ = (
        db.PrimaryKeyConstraint('id', name='kb_user_visit_log_pkey'),
        db.Index('idx_kb_user_visit_log_user_kb_tenant', 'user_id', 'knowledge_base_id', 'tenant_id'),
        db.Index('idx_kb_user_visit_log_kb_tenant', 'knowledge_base_id', 'tenant_id'),
        db.ForeignKeyConstraint(['knowledge_base_id'], ['knowledge_bases.id'],
                                name='fk_user_kb_visit_log_kb_id', ondelete='CASCADE'),
        {
            'comment': '用户知识库访问记录表'
        }
    )
    id = db.Column(db.String(32), server_default=db.text("replace(cast(uuid_generate_v1() as varchar), '-', '')"))
    user_id = db.Column(StringUUID, nullable=False, comment='访问用户ID')
    knowledge_base_id = db.Column(db.String(32), nullable=False, comment='被访问的知识库ID')
    tenant_id = db.Column(StringUUID, nullable=False, comment='租户ID')

    last_visit_at = db.Column(db.DateTime, nullable=False, server_default=db.text('CURRENT_TIMESTAMP(0)'),
                              comment='最后访问时间')
    visit_count = db.Column(db.Integer, nullable=False, server_default=db.text('1'), comment='访问次数')

    # Relationships
    knowledge_base = db.relationship('KnowledgeBase', backref=db.backref('visit_logs', lazy='dynamic'))

    def __init__(self, user_id: str, knowledge_base_id: str, tenant_id: str, **kwargs):
        self.user_id = user_id
        self.knowledge_base_id = knowledge_base_id
        self.tenant_id = tenant_id
        super().__init__(**kwargs)

class KnowledgeBase(db.Model):
    """
    知识库表
    """
    __tablename__ = 'knowledge_bases'
    __table_args__ = (
        db.PrimaryKeyConstraint('id', name='knowledge_base_pkey'),
        db.CheckConstraint('id != pid', name='prevent_self_reference'),
        # 添加外键约束
        db.ForeignKeyConstraint(['pid'], ['knowledge_bases.id'], name='fk_knowledge_base_pid'),
        db.Index('idx_kb_tenant_permission', 'tenant_id', 'permission'),
        db.Index('idx_kb_tenant_pid', 'tenant_id', 'pid'),
        db.Index('idx_kb_name', 'name'),
        {
            'comment': '知识库表'
        }
    )

    id = db.Column(db.String(32), server_default=db.text("replace(cast(uuid_generate_v1() as varchar), '-', '')"))
    pid = db.Column(db.String(32), db.ForeignKey('knowledge_bases.id'), nullable=True, comment='父级知识库ID')
    is_folder = db.Column(db.Boolean, nullable=False, server_default=db.text('false'),
                          comment='是否文件夹，指用户自定义文件夹')
    # 是否与部门绑定的文件夹，与部门绑定的文件夹不允许删除
    is_department_folder = db.Column(db.Boolean, nullable=False, server_default=db.text('false'),
                                     comment='是否与部门绑定的文件夹')
    # 是否部门默认数据文件夹，部门默认数据文件夹不允许删除
    is_department_default_data_folder = db.Column(db.Boolean, nullable=False, server_default=db.text('false'),
                                                  comment='是否部门默认数据文件夹')

    name = db.Column(db.String(256), nullable=False)
    description = db.Column(db.Text, nullable=True)
    permission = db.Column(db.String(256), nullable=False,
                           server_default=db.text("'all_team_members'::character varying"))
    language = db.Column(db.String(64), nullable=False, server_default=db.text("'Chinese'::character varying"))
    avatar = db.Column(db.Text, nullable=True)

    # 知识库类型：unstructured-非结构化知识库，structured-结构化知识库
    kb_type = db.Column(db.String(32), nullable=False, server_default=db.text("'unstructured'::character varying"),
                        comment='知识库类型: unstructured-非结构化知识库，structured-结构化知识库')
    # 非结构化是否智能构建结构化数据
    auto_structured = db.Column(db.Boolean, nullable=False, server_default=db.text('false'),
                                comment='非结构化是否智能构建结构化数据')
    # 智能构建的结构化数据库文件名，使用SQLite存储
    structured_db = db.Column(db.String(256), nullable=True, comment='智能构建的结构化数据库文件名，使用SQLite存储')

    embedding_model_provider = db.Column(db.String(256), nullable=True)
    embedding_model = db.Column(db.String(256), nullable=True)
    reranking_model_provider = db.Column(db.String(256), nullable=True)
    reranking_model = db.Column(db.String(256), nullable=True)

    # 分块关键词生成
    open_keyword_gen = db.Column(db.Boolean, nullable=False, server_default=db.text('true'))

    # # 知识图谱
    open_knowledge_graph = db.Column(db.Boolean, nullable=False, server_default=db.text('false'), comment='是否开启知识图谱')
    knowledge_graph_entity_type = db.Column(db.String(128), nullable=True, comment='知识图谱实体类型')
    knowledge_graph_method = db.Column(db.String(32), nullable=True, comment='知识图谱解析方法') # 默认 light
    knowledge_graph_resolution = db.Column(db.Boolean, nullable=True, comment='是否开启实体归一化') # 默认 false
    knowledge_graph_community = db.Column(db.Boolean, nullable=True, comment='是否开启社区知识图谱') # 默认 false

    similarity_threshold = db.Column(db.Float, nullable=False, server_default=db.text('0.2'))
    vector_similarity_weight = db.Column(db.Float, nullable=False, server_default=db.text('0.3'))

    parser_id = db.Column(db.String(128), nullable=True)
    parser_config = db.Column(db.JSON, nullable=True)

    meta = db.Column(db.JSON, nullable=True)

    tenant_id = db.Column(StringUUID, nullable=False)
    created_by = db.Column(StringUUID, nullable=False)

    created_at = db.Column(db.DateTime, nullable=False, server_default=db.text('CURRENT_TIMESTAMP(0)'))
    updated_at = db.Column(db.DateTime, nullable=False, server_default=db.text('CURRENT_TIMESTAMP(0)'))

    # Add relationships with proper joins
    sub_knowledge_bases = db.relationship(
        'KnowledgeBase',
        backref=db.backref('parent', remote_side=[id]),
        primaryjoin="KnowledgeBase.pid==KnowledgeBase.id",
        foreign_keys=[pid],
        lazy='selectin'
    )

    department_relation = db.relationship(
        'Department',
        secondary='knowledge_base_department_bindings',
        primaryjoin="and_(KnowledgeBase.id==KnowledgeBaseDepartmentBindings.knowledge_base_id, "
                    "KnowledgeBase.tenant_id==KnowledgeBaseDepartmentBindings.tenant_id)",
        secondaryjoin="Department.id==KnowledgeBaseDepartmentBindings.department_id",
        lazy='selectin',
        viewonly=True
    )

    def __init__(self, **kwargs):
        super().__init__(**kwargs)

ai解答：
好的，结合您提供的完整的 KnowledgeBase 和 KnowledgeBaseUserVisitLog 表结构，我们来详细分析为什么添加 join 操作可能导致查询耗时很长。

核心问题：N+1 查询 和 索引利用

最常见的原因，以及在这个特定场景下最可能的原因，是 N+1 查询问题 和 不完全优化的索引利用。

1. N+1 查询问题 (最可能的原因)

即使您在 get_user_kb_visit_records 函数中加入了 join，但如果您的 KnowledgeBaseUserVisitLog 模型中的 relationship 声明是 lazy='select' (这是默认值) 或者 lazy='joined'，并且您在后续的 to_dict 方法中才去访问 record.knowledge_base.name 等关联属性，就可能会触发 N+1 查询。

解释：

  1. 当您不加 join 时：

    query = KnowledgeBaseUserVisitLog.query.filter(...)
# ...
pagination = query.paginate(...) # 获取 limit 条 KnowledgeBaseUserVisitLog 对象
# to_dict 方法中：
# record.knowledge_base.name
# record.knowledge_base.description

    在这种情况下，SQLAlchemy 会执行：

    • 1 个 SQL 查询获取 limit 条 KnowledgeBaseUserVisitLog 记录。
    • 然后，对于这 limit 条记录中的每一条，当您在 to_dict 方法中第一次访问 record.knowledge_base 属性时，SQLAlchemy 会单独执行一个查询来加载对应的 KnowledgeBase 对象。这将导致 limit 个额外的 SQL 查询。
    • 总共 1 + limit 个查询。

  2. 当您显式地加了 join 时：

    query = KnowledgeBaseUserVisitLog.query.filter(...).join(
    KnowledgeBase,
    KnowledgeBaseUserVisitLog.knowledge_base_id == KnowledgeBase.id
)
# ...
pagination = query.paginate(...) # 获取 limit 条 KnowledgeBaseUserVisitLog 对象
# to_dict 方法中：
# record.knowledge_base.name
# record.knowledge_base.description

    您可能认为显式 join 解决了 N+1 问题。然而，query.join(...) 仅仅是将 JOIN 操作添加到 SQL 查询中，它并没有告诉 SQLAlchemy ORM 在加载 KnowledgeBaseUserVisitLog 对象时同时加载关联的 KnowledgeBase 对象。

    默认情况下，db.relationship('KnowledgeBase', backref=db.backref('visit_logs', lazy='dynamic')) 中的 lazy='dynamic' 是针对反向关系 visit_logs 的，而正向关系 knowledge_base 的默认 lazy 行为仍然是 select（惰性加载）。

    因此，即使您在主查询中 JOIN 了 KnowledgeBase 表，SQLAlchemy 仍然会执行：

    • 1 个 SQL 查询（包含 JOIN）来获取 limit 条 KnowledgeBaseUserVisitLog 记录。
    • 但当您在 to_dict 中访问 record.knowledge_base.name 时，它仍然会为 每条 记录再次执行一个单独的查询来加载 KnowledgeBase。
    • 结果仍然是 1 + limit 个查询！甚至可能更慢，因为那个主查询本身带有 JOIN，其开销也增加了。

如何验证 N+1 查询：

在您的应用程序代码中启用 SQLAlchemy 的 SQL 日志：

import logging
logging.basicConfig()
logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO) # 或 DEBUG

然后运行您的查询代码。如果日志中出现大量如下模式的 SQL 语句，那么就是 N+1 问题：

INFO:sqlalchemy.engine.Engine:SELECT knowledge_base_user_visit_logs.id AS knowledge_base_user_visit_logs_id, ... FROM knowledge_base_user_visit_logs JOIN knowledge_bases ON knowledge_base_user_visit_logs.knowledge_base_id = knowledge_bases.id WHERE knowledge_base_user_visit_logs.user_id = %s AND knowledge_base_user_visit_logs.tenant_id = %s ORDER BY knowledge_base_user_visit_logs.last_visit_at DESC LIMIT %s OFFSET %s
INFO:sqlalchemy.engine.Engine:[generated in 0.00035s] ('user_id_val', 'tenant_id_val', 10, 0)
INFO:sqlalchemy.engine.Engine:SELECT knowledge_bases.id AS knowledge_bases_id, ... FROM knowledge_bases WHERE knowledge_bases.id = %s
INFO:sqlalchemy.engine.Engine:[generated in 0.00021s] ('kb_id_1',)
INFO:sqlalchemy.engine.Engine:SELECT knowledge_bases.id AS knowledge_bases_id, ... FROM knowledge_bases WHERE knowledge_bases.id = %s
INFO:sqlalchemy.engine.Engine:[generated in 0.00020s] ('kb_id_2',)
# ... 重复 limit 次

2. 索引利用和 JOIN 开销

即使解决了 N+1 问题，JOIN 操作本身也会引入开销：

  • KnowledgeBase 表的 id 索引： KnowledgeBase.id 是主键，通常有高效索引。

  • KnowledgeBaseUserVisitLog.knowledge_base_id 索引： 您有 idx_kb_user_visit_log_user_kb_tenant (user_id, knowledge_base_id, tenant_id)。这个索引包含了 knowledge_base_id，所以 JOIN 操作可能会利用到它。

    • 潜在问题： 如果这个索引的顺序不是最优的，或者 knowledge_base_id 的选择性不高，数据库可能需要扫描索引的大部分。

  • 中间结果集： 数据库执行 JOIN 时会生成一个包含两表合并数据的中间结果集。即使最终只取 limit 条，这个中间结果集可能在 JOIN 阶段就很大，增加了内存和 CPU 消耗。

  • 查询优化器的选择： 数据库的查询优化器可能会根据统计信息选择不同的 JOIN 算法（嵌套循环、哈希 JOIN、合并 JOIN）。如果统计信息不准确，或者数据分布不均匀，优化器可能选择一个效率较低的算法。

优化方案 (代码和索引)

  1. 解决 N+1 问题 (使用 joinedload) - 强烈推荐
    这是最直接且通常最有效的优化。它告诉 SQLAlchemy 在执行主查询时，通过 LEFT JOIN 一次性加载关联的 KnowledgeBase 对象。

    from sqlalchemy.orm import joinedload
from sqlalchemy import desc

@staticmethod
def get_user_kb_visit_records(tenant_id: str, user_id: str, page: int, limit: int) -> dict:
    query = KnowledgeBaseUserVisitLog.query.filter(
        KnowledgeBaseUserVisitLog.user_id == user_id,
        KnowledgeBaseUserVisitLog.tenant_id == tenant_id
    ).options(
        # 预先加载 knowledge_base 关系，避免 N+1 查询
        # 这会自动在 SQL 中添加一个 LEFT JOIN
        joinedload(KnowledgeBaseUserVisitLog.knowledge_base)
    )

    query = query.order_by(desc(KnowledgeBaseUserVisitLog.last_visit_at))
    pagination = query.paginate(page=page, per_page=limit, error_out=False)

    return pagination

  2. 优化 KnowledgeBaseUserVisitLog 表索引 (数据库级别)
    为了支持过滤 (user_id, tenant_id) 和排序 (last_visit_at DESC) 以及 JOIN (knowledge_base_id)，可以考虑以下复合索引：

    • 最匹配现有查询的索引： (user_id, tenant_id, last_visit_at DESC)

      CREATE INDEX idx_kb_user_visit_log_user_tenant_visit_at
ON knowledge_base_user_visit_logs (user_id, tenant_id, last_visit_at DESC);

      这将极大加速过滤和排序操作。如果 knowledge_base_id 也经常被用于过滤，可以考虑将其加入到这个索引中，但要注意索引字段过多会增加写入开销。

    • 如果 JOIN 字段的索引是瓶颈： 确保 KnowledgeBaseUserVisitLog.knowledge_base_id 字段本身有索引（您的 idx_kb_user_visit_log_user_kb_tenant 已经包含了它）。

总结：

在您的情况下，join 耗时很长的最主要原因很可能是显式 join 没有同时解决 ORM 层的 N+1 查询问题，导致在获取 KnowledgeBase 关联对象时仍然执行了大量额外查询。其次，才是 JOIN 操作本身的计算开销和索引利用效率。使用 joinedload 配合适当的复合索引可以有效解决这些性能问题。

posted @ 2025-09-30 17:51  踩坑大王  阅读(6)  评论(0)    收藏  举报