如何优化Linq查询执行速度？附具体场景与代码示例

问题描述 #

我有一段Linq查询，执行逻辑如下：

1. 从数据库拉取childrens数据
2. 筛选出无法与AvailableItems关联（不在AvailableItems表中）的childrens记录
3. 筛选AvailableItems并加载到内存
4. 对AvailableItems和childrensWithoutItems执行左连接，再对左连接结果做笛卡尔积
5. 对AvailableItems和childrens执行内连接

请问有哪些可以提升该查询执行速度的技巧？

原代码示例 #

var authorizeType = new List<TreeNodeType> {
        TreeNodeType.Equipment,
        TreeNodeType.StructuredArea
    };

        
var childrens = await (from n in _db.TreeNodes.Where(n => n.TreeNodeId == treeNodeParentId)
                        from path in _db.TreeNodes.Where(p => (EF.Functions.Like(p.Path, n.Path + "%")) && authorizeType.Contains(p.Type))
                        select new
                        {
                            path.Name,
                            path.FullName,
                            path.Type,
                            path.HorusId,
                        }).ToListAsync();

var childrensWithoutItems = childrens.Where(i => !_db.AvailableItems.Select(d => d.HorusId).Contains(i.HorusId));

var availableitems = await _db.AvailableItems
    .Where(x => x.HorusId == null || childrens.Select(d => d.HorusId).Contains(x.HorusId.Value))
    .ToListAsync();

//without items
var withoutItems =
        (from c in childrensWithoutItems
        from co in availableitems
        join tree in childrens on co.HorusId equals tree.HorusId into left
        from le in left.DefaultIfEmpty()
        join i in items on co.ItemIdentifier equals i.Identifier
        where le == null
        select new OrganizationItemDetail {
            OrganizationPath = c.FullName,
            OrganizationChildName = c.Name,
            OrganizationType = c.Type,
            Name = i.Name,
            Packaging = i.Packaging.ToString(),
            ShortName = i.ShortName,
            Type = i.Type.ToString(),
            UnitValue = i.UnitValue,
        });


//with items 
var withItems = (from co in availableitems
                                join tree in childrens on co.HorusId equals tree.HorusId
                                join i in items on co.ItemIdentifier equals i.Identifier
                                select new OrganizationItemDetail {
                                    OrganizationPath = tree.FullName,
                                    OrganizationChildName = tree.Name,
                                    OrganizationType = tree.Type,
                                    Name = i.Name,
                                    Packaging = i.Packaging.ToString(),
                                    ShortName = i.ShortName,
                                    Type = i.Type.ToString(),
                                    UnitValue = i.UnitValue,
                                });

var res = withoutItems.Concat(withItems);

预期结果 #

1. 拉取childrens后，将其与AvailableItems表执行内连接
2. 对无法关联到childrens的AvailableItems（左连接结果）和不在AvailableItems中的childrens（右连接结果）执行笛卡尔积
3. 将两个查询结果合并后返回

核心优化技巧 #

1. 减少数据库往返次数，让数据库承担更多计算 #

原代码多次调用ToListAsync()将数据加载到内存后再处理，会增加数据库请求次数和内存压力。应尽量将连接、过滤逻辑放到数据库查询中，利用数据库的查询优化能力。

2. 避免内存中的笛卡尔积操作 #

原代码在内存中对childrensWithoutItems和availableitems做笛卡尔积，数据量大时会产生大量临时数据，性能极低。可以通过数据库层面的关联查询替代，或者提前过滤出需要做笛卡尔积的子集。

3. 利用数据库索引加速查询 #

为以下字段添加索引：

• TreeNodes表：TreeNodeId、Path、Type、HorusId
• AvailableItems表：HorusId、ItemIdentifier
  索引可以大幅提升Like查询、关联查询和Contains查询的速度。

4. 提前过滤数据，减少内存加载量 #

在数据库查询阶段就筛选出所需数据，避免加载不必要的字段和记录：

• 只选择需要的字段（如原代码中的匿名类已做这一步，保持即可）
• 过滤AvailableItems时，直接在数据库中关联TreeNodes的HorusId，而非加载childrens后再用Contains过滤

5. 优化Linq查询逻辑，避免重复数据库请求 #

原代码中childrensWithoutItems的过滤逻辑每次都会触发数据库查询（因为_db.AvailableItems是IQueryable，每次Where都会执行查询），应提前将AvailableItems的HorusId加载到内存，或者在数据库中直接关联过滤。

优化后的代码示例 #

var authorizeType = new List<TreeNodeType> { TreeNodeType.Equipment, TreeNodeType.StructuredArea };

// 先获取childrens的HorusId集合，同时保留所需字段
var childrensQuery = from n in _db.TreeNodes.Where(n => n.TreeNodeId == treeNodeParentId)
                     from path in _db.TreeNodes.Where(p => EF.Functions.Like(p.Path, n.Path + "%") && authorizeType.Contains(p.Type))
                     select new
                     {
                         path.Name,
                         path.FullName,
                         path.Type,
                         path.HorusId,
                     };

// 一次性获取childrens和对应的AvailableItems关联状态，减少数据库请求
var childrensWithItemFlag = await childrensQuery
    .Select(c => new
    {
        c.Name,
        c.FullName,
        c.Type,
        c.HorusId,
        HasItem = _db.AvailableItems.Any(ai => ai.HorusId == c.HorusId)
    })
    .ToListAsync();

var childrensWithoutItems = childrensWithItemFlag.Where(c => !c.HasItem).ToList();
var childrensHorusIds = childrensWithItemFlag.Select(c => c.HorusId).ToList();

// 在数据库中筛选AvailableItems，直接关联childrens的HorusId
var availableitems = await _db.AvailableItems
    .Where(x => x.HorusId == null || childrensHorusIds.Contains(x.HorusId.Value))
    .ToListAsync();

// 优化withoutItems逻辑：先过滤出无关联的AvailableItems，再和childrensWithoutItems做笛卡尔积
var availableItemsWithoutChildren = availableitems.Where(ai => ai.HorusId != null && !childrensHorusIds.Contains(ai.HorusId.Value)).ToList();

var withoutItems = from c in childrensWithoutItems
                   from ai in availableItemsWithoutChildren
                   join i in items on ai.ItemIdentifier equals i.Identifier
                   select new OrganizationItemDetail
                   {
                       OrganizationPath = c.FullName,
                       OrganizationChildName = c.Name,
                       OrganizationType = c.Type,
                       Name = i.Name,
                       Packaging = i.Packaging.ToString(),
                       ShortName = i.ShortName,
                       Type = i.Type.ToString(),
                       UnitValue = i.UnitValue,
                   };

// withItems逻辑保持，但可以考虑在数据库中完成关联
var withItems = from ai in availableitems
                join c in childrensWithItemFlag on ai.HorusId equals c.HorusId
                join i in items on ai.ItemIdentifier equals i.Identifier
                select new OrganizationItemDetail
                {
                    OrganizationPath = c.FullName,
                    OrganizationChildName = c.Name,
                    OrganizationType = c.Type,
                    Name = i.Name,
                    Packaging = i.Packaging.ToString(),
                    ShortName = i.ShortName,
                    Type = i.Type.ToString(),
                    UnitValue = i.UnitValue,
                };

var res = withoutItems.Concat(withItems);

初始表结构及步骤结果 #

Items列表 #

Childrens表 #

AvailablesItems表 #

步骤1结果：（childrens与availableItems内连接） #

步骤2结果：（availableItems与childrens左连接 + 左连接结果笛卡尔积） #

左连接结果

左连接结果笛卡尔积

步骤3结果：（与Items列表内连接） #

步骤4结果：（与步骤1结果执行相同操作） #

（注：原步骤4未给出完整结果，此处省略）

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