C# 表达式树Lambda扩展（四） - 园子的蜗牛 - 博客园

2020-07-07mikel阅读(636)

来源： C# 表达式树Lambda扩展（四） – 园子的蜗牛 – 博客园

一、前言

本来计算这篇文章在后面需要运用的时候写的，但是既然写到表达式的扩展呢，就一起写完吧。

看到这个标题就有一种疑问，Lambda表达式本来就是表达式树，还需要怎么扩展？那就看看下面的内容，你就知道了。

表达式系列目录

C# 表达式树讲解（一）

C# 表达式树遍历（二）

C# 表达式树分页扩展（三）

C# 表达式树Lambda扩展（四）

二、Lambda扩展

这里先不忙解答上面的问题，我们先看下这样一个应用场景。

一个页面的请求，里面带有一些条件查询，请求类如下

public class ScoreRequest
{
    public string CourseName { get; set; }
    public string StudentName { get; set; }
}

要求查询与课程名称和学生名称匹配的数据

数据源我们就以上一例子的数据源

数据源类

public class ScoreClass
{
    public string CourseName { get; set; }
    public string StudentName { get; set; }
    public decimal Score { get; set; }
}

添加数据

var datas = new List<ScoreClass>();
datas.Add(new ScoreClass
{
    CourseName = "数学",
    StudentName = "学生A",
    Score = 60
});
datas.Add(new ScoreClass
{
    CourseName = "数学",
    StudentName = "学生B",
    Score = 65
});
datas.Add(new ScoreClass
{
    CourseName = "数学",
    StudentName = "学生C",
    Score = 70
});
datas.Add(new ScoreClass
{
    CourseName = "数学",
    StudentName = "学生D",
    Score = 75
});
datas.Add(new ScoreClass
{
    CourseName = "数学",
    StudentName = "学生E",
    Score = 80
});
datas.Add(new ScoreClass
{
    CourseName = "数学",
    StudentName = "学生F",
    Score = 81
});
datas.Add(new ScoreClass
{
    CourseName = "数学",
    StudentName = "学生G",
    Score = 82
});
datas.Add(new ScoreClass
{
    CourseName = "数学",
    StudentName = "学生H",
    Score = 83
});
datas.Add(new ScoreClass
{
    CourseName = "数学",
    StudentName = "学生I",
    Score = 84
});

好了现在我们就查询数据

var request = new ScoreRequest()
            {
                CourseName = "数",
                StudentName = "H"
            };
            var resultDatas = datas.Where(e => e.CourseName.Contains(request.CourseName) && e.StudentName.Contains(request.StudentName))
                .ToList();

如果查询对象里面CourseName和StudentName字段都有值得话，这样写没问题。如果没值，那就最后的数据，就不准确了。

如果是直接拼凑SQL语句，我们可以用if(String.IsNullOrEmpty())来判断，但是现在判断了，怎么拼凑Lambda表达式呢？

所以就需要我们对Lambda表达式进行扩展，让他支持这种情况。那上面的问题，就不用再专门回答了吧！！！！

创建一个LambdaExtension的类，代码如下

public static class LambdaExtension
{
    public static Expression<Func<T, bool>> True<T>() { return param => true; }
    public static Expression<Func<T, bool>> False<T>() { return param => false; }
    public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second)
    {
        return first.Compose(second, Expression.AndAlso);
    }
    public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second)
    {
        return first.Compose(second, Expression.OrElse);
    }
    private static Expression<T> Compose<T>(this Expression<T> first, Expression<T> second, Func<Expression, Expression, Expression> merge)
    {
        var map = first.Parameters
            .Select((f, i) => new { f, s = second.Parameters[i] })
            .ToDictionary(p => p.s, p => p.f);
        var secondBody = PFTParameterExtension.ReplaceParameters(map, second.Body);
        return Expression.Lambda<T>(merge(first.Body, secondBody), first.Parameters);
    }

    private class PFTParameterExtension : ExpressionVisitor
    {
        private readonly Dictionary<ParameterExpression, ParameterExpression> map;

        public PFTParameterExtension()
        {

        }

        public PFTParameterExtension(Dictionary<ParameterExpression, ParameterExpression> map)
        {
            this.map = map ?? new Dictionary<ParameterExpression, ParameterExpression>();
        }

        /// <summary>
        /// 替换参数
        /// </summary>
        /// <param name="map">The map.</param>
        /// <param name="exp">The exp.</param>
        /// <returns>Expression</returns>
        public static Expression ReplaceParameters(Dictionary<ParameterExpression, ParameterExpression> map, Expression exp)
        {
            return new PFTParameterExtension(map).Visit(exp);
        }

        protected override Expression VisitParameter(ParameterExpression p)
        {
            ParameterExpression replacement;
            if (map != null && map.Count > 0 && map.TryGetValue(p, out replacement))
            {
                p = replacement;
            }
            return base.VisitParameter(p);
        }

    }

}

这里面私有化了一个表达式树访问器，他的作用主要是用来同步Lambda表达式里面的参数。

下面是调用方式

            var expression = LambdaExtension.True<ScoreClass>();
            if (!string.IsNullOrWhiteSpace(request.CourseName))
                expression = expression.And(e => e.CourseName.Contains(request.CourseName));
            if (!string.IsNullOrWhiteSpace(request.StudentName))
                expression = expression.And(et => et.StudentName.Contains(request.StudentName));

            var resultDatas = datas.Where(expression.Compile())
                .ToList();
            Console.WriteLine($"查询结果：\n{string.Join("\n", resultDatas.Select(e => $"{e.StudentName} {e.CourseName} {e.Score}"))}");

where条件里面只能带委托，而我们的expression是Lambda表达式，所以需要Compile进行委托编译。

运行结果：

仔细看代码，第一个条件And里面的参数是“e”，第二个条件里面的参数是et，同一个Lambda表达式里面（这里只有一个参数），参数肯定是一致的，所以在LambdaExtension类中，在合并两个Lambda表达式的时候，就需要将参数合并成一个。

经过这样的扩展，我们就可以根据我们的实际情况，拼凑好需要的表达式，得到我们想要的结果。

三、总结

表达式树方面的讲解，终于可以告一段落了。一直后没有这样的写文章，现在觉得写文章还是真的挺累的，今年中秋节的这三天，算是全部的给博客园了。不过这三天讲解的内容，基本上把后面Dapper的扩展需要用的技术都铺垫了，后面我们就继续对ORM的讲解了。其实没写一篇博文，蜗牛都会去罗列和梳理相关知识点，这也让蜗牛获益匪浅，也希望蜗牛的博客能帮助到园友，这就是所谓的“赠人玫瑰，手留余香”吧。

C# 表达式树分页扩展（三） - 园子的蜗牛 - 博客园

2020-07-07mikel阅读(644)

来源： C# 表达式树分页扩展（三） – 园子的蜗牛 – 博客园

一、前言

前面我们知道了表达树的基本知识，也明白了怎么遍历和修改一个表达式，这里我们就一个实际的场景来进行功能开发。

表达式系列目录

C# 表达式树讲解（一）

C# 表达式树遍历（二）

C# 表达式树分页扩展（三）

C# 表达式树Lambda扩展（四）

二、分页扩展

在实际的开发中，肯定会遇到这样的应用场景，一个数据源需要在页面上进行分页显示，并且页面上需要对该数据源有一个排名。本来分页可以在数据库层面完成，但是因为这里有一个排名功能，所谓的排名，就是需要查询出所有满足条件的数据，然后按照某个算法升序或者降序排列，然后按照进行排名。排名之后，然后根据分页信息，将当前页的数据返回给页面，当然中间还有自定义排序的因素。

怎么取数据源和怎么排序，这里先不做介绍，我们就实现对一个数据源分页的功能。

我们先定义好分页的实体对象

分页请求对象PageRequest.cs，因为在【ORM之Dapper运用】已经说明，所以这里我就只粘贴处代码

public class PageRequest
    {
        /// <summary>
        /// 每页条数
        /// </summary>
        public int PageSize { get; set; }

        /// <summary>
        /// 当前页数
        /// </summary>
        public int PageIndex { get; set; }

        /// <summary>
        /// 排序字段
        /// </summary>
        public string SortBy { get; set; }

        /// <summary>
        /// 排序方式（desc、asc）
        /// </summary>
        public string Direction { get; set; }

        /// <summary>
        /// 获取排序sql语句
        /// </summary>
        /// <returns></returns>
        public string GetOrderBySql()
        {
            if (string.IsNullOrWhiteSpace(SortBy))
            {
                return "";
            }
            var resultSql = new StringBuilder(" ORDER BY ");
            string dir = Direction;
            if (string.IsNullOrWhiteSpace(dir))
            {
                dir = "ASC";
            }
            if (SortBy.Contains("&"))
            {
                resultSql.Append("").Append(string.Join(",", SortBy.Split('&').Select(e => $" {e} {dir}").ToArray()));
            }
            else
            {
                resultSql.Append(SortBy).Append("").Append(dir);//默认处理方式
            }
            return resultSql.ToString();
        }
    }

分页的返回对象PageResponse.cs

/// <summary>
    /// 通用分页返回
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public class PageResponse<T>
    {
        /// <summary>
        /// 总条数
        /// </summary>
        public long TotalCount { get; set; }

        /// <summary>
        /// 返回
        /// </summary>
        public List<T> Items { get; set; }

        /// <summary>
        /// 当前页
        /// </summary>
        public long PageIndex { get; set; }

        /// <summary>
        /// 每页条数
        /// </summary>
        public long PageSize { get; set; }

        /// <summary>
        /// 总页数
        /// </summary>
        public long TotalPages { get; set; }

        /// <summary>
        /// 返回筛选集合
        /// </summary>
        public Dictionary<string, List<string>> ResultFilter = new Dictionary<string, List<string>>();

    }

对数据集分页方法的实现

public class PFTPage
    {
        /// <summary>
        /// 对数据集分页
        /// </summary>
        /// <typeparam name="T">数据集对象</typeparam>
        /// <param name="source">数据集</param>
        /// <param name="page">分页信息</param>
        /// <returns></returns>
        public static PageResponse<T> DataPagination<T>(IQueryable<T> source, PageRequest page) where T : class, new()
        {
            var sesultData = new PageResponse<T>();
            bool isAsc = page.Direction.ToLower() == "asc" ? true : false;
            string[] _order = page.SortBy.Split('&');
            MethodCallExpression resultExp = null;
            foreach (string item in _order)
            {
                string _orderPart = item;
                _orderPart = Regex.Replace(_orderPart, @"\s+", "");
                string[] _orderArry = _orderPart.Split(' ');
                string _orderField = _orderArry[0];
                bool sort = isAsc;
                if (_orderArry.Length == 2)
                {
                    isAsc = _orderArry[1].ToUpper() == "ASC" ? true : false;
                }
                var parameter = Expression.Parameter(typeof(T), "t");
                var property = typeof(T).GetProperty(_orderField);
                var propertyAccess = Expression.MakeMemberAccess(parameter, property);
                var orderByExp = Expression.Lambda(propertyAccess, parameter);
                resultExp = Expression.Call(typeof(Queryable), isAsc ? "OrderBy" : "OrderByDescending", new Type[] { typeof(T), property.PropertyType }, source.Expression, Expression.Quote(orderByExp));
            }
            var tempData = source.Provider.CreateQuery<T>(resultExp);
            sesultData.PageIndex = page.PageIndex;
            sesultData.PageSize = page.PageSize;
            sesultData.TotalCount = tempData.Count();

            sesultData.TotalPages = sesultData.TotalCount / sesultData.PageSize;
            if (sesultData.TotalCount % sesultData.PageSize > 0)
            {
                sesultData.TotalPages += 1;
            }

            sesultData.Items = tempData.Skip(page.PageSize * (page.PageIndex - 1)).Take(page.PageSize).ToList();
            return sesultData;
        }
    }

为了测试，我们定义一个学生课程成绩的测试类

public class ScoreClass
{
    public string CourseName { get; set; }
    public string StudentName { get; set; }
    public decimal Score { get; set; }
}

调用代码

var datas = new List<ScoreClass>();
            datas.Add(new ScoreClass
            {
                CourseName = "数学",
                StudentName = "学生A",
                Score = 60
            });
            datas.Add(new ScoreClass
            {
                CourseName = "数学",
                StudentName = "学生B",
                Score = 65
            });
            datas.Add(new ScoreClass
            {
                CourseName = "数学",
                StudentName = "学生C",
                Score = 70
            });
            datas.Add(new ScoreClass
            {
                CourseName = "数学",
                StudentName = "学生D",
                Score = 75
            });
            datas.Add(new ScoreClass
            {
                CourseName = "数学",
                StudentName = "学生E",
                Score = 80
            });
            datas.Add(new ScoreClass
            {
                CourseName = "数学",
                StudentName = "学生F",
                Score = 81
            });
            datas.Add(new ScoreClass
            {
                CourseName = "数学",
                StudentName = "学生G",
                Score = 82
            });
            datas.Add(new ScoreClass
            {
                CourseName = "数学",
                StudentName = "学生H",
                Score = 83
            });
            datas.Add(new ScoreClass
            {
                CourseName = "数学",
                StudentName = "学生I",
                Score = 84
            });
            //按照Score降序排序取第一个（5条数据）
            var page = new PageRequest()
            {
                Direction= "desc",
                PageIndex=1,
                PageSize=5,
                SortBy= "Score"
            };
            var result = PFTPage.DataPagination(datas.AsQueryable(), page);

            Console.WriteLine($"分页结果：\n{string.Join("\n", result.Items.Select(e=>$"{e.StudentName} {e.CourseName} {e.Score}"))}");

运行结果

监控一下result

返回的都是我们希望返回的数据。

分页公共方法里面，就是根据PageRequest里面的内容，动态的生成表达式树的查询，然后在对数据集使用我们生成的查询表达式树，就返回我们想到的数据集。

三、总结

实现数据分页的公共方法，在后面的遇到数据分页的时候，就会显得非常的方便。有没有感觉很好玩，那么下一篇我们在利用这些知识对Lambda表达式进行扩展。

C# 表达式树遍历（二） - 园子的蜗牛 - 博客园

2020-07-07mikel阅读(707)

来源： C# 表达式树遍历（二） – 园子的蜗牛 – 博客园

一、前言

上一篇我们对表达式树有了初步的认识，这里我们将对表达式树进行遍历，只有弄清楚了他的运行原理，我们才可以对他进行定制化修改。

表达式系列目录

C# 表达式树讲解（一）

C# 表达式树遍历（二）

C# 表达式树分页扩展（三）

C# 表达式树Lambda扩展（四）

二、表达式树的遍历

要查看表达式树的遍历，肯定不能直接用.Net Framework封装的方法，因为.Net Framework框架是闭源的，除了看中间语言（IL）去查看。我们就用ExpressionVisitor类查看一下他的运行原理，看了下ExpressionVisitor类，里面都是对各个表达式的访问，而且都是虚拟函数，我们可以对他进行override。

ExpressionVisitor类里面都是对各个类型的表达式进行访问，为了更好的理解里面的访问顺序，蜗牛把里面的虚函数都override了一遍，然后跟踪里面的执行顺序。【傻眼了，35个虚函数需要override，内心是很拒绝的，vs2019有没有重写父类虚函数的快捷键啊！！！！！！！】

2.1、ExpressionVisitor类的跟踪

为了不改变ExpressionVisitor类原来的访问，创建的SnailExpressionVisitor.cs 文件只在重写方法里面添加日志打印。

代码如下：

public class SnailExpressionVisitor : ExpressionVisitor
    {
        public override Expression Visit(Expression node)
        {
            Console.WriteLine($"访问了 Visit，内容：{node.ToString()}");
            return base.Visit(node);
        }

        protected override CatchBlock VisitCatchBlock(CatchBlock node)
        {

            Console.WriteLine($"访问了 VisitCatchBlock，内容：{node.ToString()}");
            return base.VisitCatchBlock(node);
        }

        protected override ElementInit VisitElementInit(ElementInit node)
        {
            Console.WriteLine($"访问了 VisitElementInit，内容：{node.ToString()}");
            return base.VisitElementInit(node);
        }
        protected override LabelTarget VisitLabelTarget(LabelTarget node)
        {

            Console.WriteLine($"访问了 VisitLabelTarget，内容：{node.ToString()}");
            return base.VisitLabelTarget(node);
        }
        protected override MemberAssignment VisitMemberAssignment(MemberAssignment node)
        {

            Console.WriteLine($"访问了 VisitMemberAssignment，内容：{node.ToString()}");
            return base.VisitMemberAssignment(node);
        }
        protected override MemberBinding VisitMemberBinding(MemberBinding node)
        {

            Console.WriteLine($"访问了 VisitMemberBinding，内容：{node.ToString()}");
            return base.VisitMemberBinding(node);
        }

        protected override MemberListBinding VisitMemberListBinding(MemberListBinding node)
        {

            Console.WriteLine($"访问了 VisitMemberListBinding，内容：{node.ToString()}");
            return base.VisitMemberListBinding(node);
        }
        protected override MemberMemberBinding VisitMemberMemberBinding(MemberMemberBinding node)
        {

            Console.WriteLine($"访问了 VisitMemberMemberBinding，内容：{node.ToString()}");
            return base.VisitMemberMemberBinding(node);
        }
        protected override SwitchCase VisitSwitchCase(SwitchCase node)
        {
            Console.WriteLine($"访问了 VisitSwitchCase，内容：{node.ToString()}");
            return base.VisitSwitchCase(node);
        }
        protected override Expression VisitBinary(BinaryExpression node)
        {
            Console.WriteLine($"访问了 VisitBinary，内容：{node.ToString()}");
            return base.VisitBinary(node);
        }
        protected override Expression VisitBlock(BlockExpression node)
        {
            Console.WriteLine($"访问了 VisitBlock，内容：{node.ToString()}");
            return base.VisitBlock(node);
        }

        protected override Expression VisitConditional(ConditionalExpression node)
        {
            Console.WriteLine($"访问了 VisitConditional，内容：{node.ToString()}");
            return base.VisitConditional(node);
        }

        protected override Expression VisitConstant(ConstantExpression node)
        {
            Console.WriteLine($"访问了 VisitConstant，内容：{node.ToString()}");
            return base.VisitConstant(node);
        }
        protected override Expression VisitDebugInfo(DebugInfoExpression node)
        {
            Console.WriteLine($"访问了 VisitDebugInfo，内容：{node.ToString()}");
            return base.VisitDebugInfo(node);
        }
        protected override Expression VisitDefault(DefaultExpression node)
        {
            Console.WriteLine($"访问了 VisitDefault，内容：{node.ToString()}");
            return base.VisitDefault(node);
        }

        protected override Expression VisitDynamic(DynamicExpression node)
        {
            Console.WriteLine($"访问了 VisitDynamic，内容：{node.ToString()}");
            return base.VisitDynamic(node);
        }
        protected override Expression VisitExtension(Expression node)
        {
            Console.WriteLine($"访问了 VisitExtension，内容：{node.ToString()}");
            return base.VisitExtension(node);
        }
        protected override Expression VisitGoto(GotoExpression node)
        {
            Console.WriteLine($"访问了 VisitGoto，内容：{node.ToString()}");
            return base.VisitGoto(node);
        }
        protected override Expression VisitIndex(IndexExpression node)
        {
            Console.WriteLine($"访问了 VisitIndex，内容：{node.ToString()}");
            return base.VisitIndex(node);
        }
        protected override Expression VisitInvocation(InvocationExpression node)
        {
            Console.WriteLine($"访问了 VisitInvocation，内容：{node.ToString()}");
            return base.VisitInvocation(node);
        }
        protected override Expression VisitLabel(LabelExpression node)
        {
            Console.WriteLine($"访问了 VisitLabel，内容：{node.ToString()}");
            return base.VisitLabel(node);
        }
        protected override Expression VisitLambda<T>(Expression<T> node)
        {
            Console.WriteLine($"访问了 VisitLambda，内容：{node.ToString()}");
            return base.VisitLambda(node);
        }

        protected override Expression VisitListInit(ListInitExpression node)
        {
            Console.WriteLine($"访问了 VisitListInit，内容：{node.ToString()}");
            return base.VisitListInit(node);
        }
        protected override Expression VisitLoop(LoopExpression node)
        {
            Console.WriteLine($"访问了 VisitLoop，内容：{node.ToString()}");
            return base.VisitLoop(node);
        }
        protected override Expression VisitMember(MemberExpression node)
        {
            Console.WriteLine($"访问了 VisitMember，内容：{node.ToString()}");
            return base.VisitMember(node);
        }
        protected override Expression VisitMemberInit(MemberInitExpression node)
        {
            Console.WriteLine($"访问了 VisitMemberInit，内容：{node.ToString()}");
            return base.VisitMemberInit(node);
        }
        protected override Expression VisitMethodCall(MethodCallExpression node)
        {
            Console.WriteLine($"访问了 VisitMethodCall，内容：{node.ToString()}");
            return base.VisitMethodCall(node);
        }
        protected override Expression VisitNew(NewExpression node)
        {
            Console.WriteLine($"访问了 VisitNew，内容：{node.ToString()}");
            return base.VisitNew(node);
        }
        protected override Expression VisitNewArray(NewArrayExpression node)
        {
            Console.WriteLine($"访问了 VisitNewArray，内容：{node.ToString()}");
            return base.VisitNewArray(node);
        }

        protected override Expression VisitParameter(ParameterExpression node)
        {
            Console.WriteLine($"访问了 VisitParameter，内容：{node.ToString()}");
            return base.VisitParameter(node);
        }
        protected override Expression VisitRuntimeVariables(RuntimeVariablesExpression node)
        {
            Console.WriteLine($"访问了 VisitRuntimeVariables，内容：{node.ToString()}");
            return base.VisitRuntimeVariables(node);
        }

        protected override Expression VisitSwitch(SwitchExpression node)
        {
            Console.WriteLine($"访问了 VisitSwitch，内容：{node.ToString()}");
            return base.VisitSwitch(node);
        }
        protected override Expression VisitTry(TryExpression node)
        {
            Console.WriteLine($"访问了 VisitTry，内容：{node.ToString()}");
            return base.VisitTry(node);
        }

        protected override Expression VisitTypeBinary(TypeBinaryExpression node)
        {
            Console.WriteLine($"访问了 VisitTypeBinary，内容：{node.ToString()}");
            return base.VisitTypeBinary(node);
        }
        protected override Expression VisitUnary(UnaryExpression node)
        {
            Console.WriteLine($"访问了 VisitUnary，内容：{node.ToString()}");
            return base.VisitUnary(node);
        }



    }

调用方法：

Expression<Func<int, int, bool>> fun = (x, y) => x - y > 5;

var treeModifier = new SnailExpressionVisitor();
Expression modifiedExpr = treeModifier.Visit(fun);

运行结果：

从打印的日志里面可以看出，

1、每次访问表达式类时，都会先去调用Visit函数，估计他是在Visit里面判定表达式类，然后在根据表达式类的类型，调用访问改表达式的函数

2、对Lambda表达式类，是先访问的是Expression<T>。Expression<T>是不是很熟悉，上一章说过他的作用是将强类型Lambda表达式表示为表达式树形式的数据结构，解析成功之后才对表达式的访问

3、对于表达式先解析的是左边，左边的内容解析完了之后在解析右边，如(x-y)>5，解析的顺序是：x-y=>x=>y=>5

2.2、修改表达式树

既然我们弄清楚了表达式树的访问，现在我们就可以对他进行编辑修改了。

上面我们判断的是x-y>5，现在我们规定，将“-”改成“+”，“>”改成“>=”

对VisitBinary方法修改代码如下：

protected override Expression VisitBinary(BinaryExpression node)
{
    Console.WriteLine($"访问了 VisitBinary，内容：{node.ToString()}");
    if (node.NodeType == ExpressionType.GreaterThan)
    {
        Expression left = this.Visit(node.Left);
        Expression right = this.Visit(node.Right);

        var result = Expression.MakeBinary(ExpressionType.GreaterThanOrEqual, left, right, node.IsLiftedToNull, node.Method);
        Console.WriteLine($"访问了 VisitBinary，更改之后的内容：{result.ToString()}");
        return result;
    }
    else if (node.NodeType == ExpressionType.Subtract || node.NodeType == ExpressionType.SubtractChecked)
    {
        Expression left = this.Visit(node.Left);
        Expression right = this.Visit(node.Right);

        var result = Expression.MakeBinary(ExpressionType.Add, left, right, node.IsLiftedToNull, node.Method);
        Console.WriteLine($"访问了 VisitBinary，更改之后的内容：{result.ToString()}");
        return result;
    }
    else
    {
        return base.VisitBinary(node);
    }
}

调用方法：

Expression<Func<int, int, bool>> fun = (x, y) => x - y > 5;

var treeModifier = new SnailExpressionVisitor();
Expression modifiedExpr = treeModifier.Visit(fun);

Console.WriteLine($"Lambda的转换最后结果：{modifiedExpr.ToString()}");

运行结果如下

三、总结

对表达树的讲解已经完成了，但是说了这么久，对真实的开发有什么作用呢？后面我将利用Lambda表达式写一个对现有数据分页的公共方法，同时在对Dapper的扩展也会用到相关知识点，大家拭目以待吧……

C# 表达式树讲解（一） - 园子的蜗牛 - 博客园

2020-07-07mikel阅读(901)

来源： C# 表达式树讲解（一） – 园子的蜗牛 – 博客园

一、前言

一直想写一篇Dpper的定制化扩展的文章，但是里面会设计到对Lambda表达式的解析，而解析Lambda表达式，就必须要知道表达式树的相关知识点。我希望能通过对各个模块的知识点或者运用能够多一点的讲解，能够帮助到园友了解得更多。虽然讲解得不全面，如果能成为打开这块的一把钥匙，也是蜗牛比较欣慰的。

表达式系列目录

C# 表达式树讲解（一）

C# 表达式树遍历（二）

C# 表达式树分页扩展（三）

C# 表达式树Lambda扩展（四）

二、表达树理解

表达式树以树形数据结构表示代码，其中每一个节点都是一种表达式，它将我们原来可以直接由代码编写的逻辑以表达式的方式存储在树状的结构里，从而可以在运行时去解析这个树，然后执行，实现动态的编辑和执行代码。在.Net 里面的Linq to SQL就是对表达式树的解析。

这里先讲解下表达式和表达式树，表达式相信大家都知道，比如x+5或者5，都可以算是表达式，而表达式树里面的树指的二叉树，也就是表达式的集合，C#中的Expression类就是表达式类。对于一棵表达式树，其叶子节点都是参数或者常数，非叶子节点都是运算符或者控制符。

2.1、表达式的创建

Lambda表达式方法：

Expression<Func<int, int,bool>> fun = (x, y) => x < y

这种方法创建出的表达式根节点类型为ExpressionType.Lambda，Type类型为返回值类型typeof(bool)

组装法（通过 API 创建表达式树）：

ParameterExpression numParam = Expression.Parameter(typeof(int), "num");
ConstantExpression five = Expression.Constant(5, typeof(int));
BinaryExpression numLessThanFive = Expression.LessThan(numParam, five);
Expression<Func<int, bool>> lambda1 =
    Expression.Lambda<Func<int, bool>>(
        numLessThanFive,
        new ParameterExpression[] { numParam });

我们先创建了两个参数表达式num和5，然后用LessThan组装在一起，最终的表达式为“num<5”，expr的节点类型为LessThan，Type类型为typeof(bool)

我们先看看表达式树里面的构造

首先Expression<TDelegate>的功能是将强类型Lambda表达式表示为表达式树形式的数据结构，他的父类是LambdaExpression，比较他们代码可知，Lambda表达式的主体，名称和参数全部保存在LambdaExpression里面。

Expression<TDelegate>与LambdaExpression代码截图：

LambdaExpression里面的Body就是我们的表达式。

C#表达式给我们提供了丰富的表达式类，进入到LambdaExpression类里面

方法返回类型以“Expression”结尾的，基本上都是一个表达式类。

每个表达式代表的定义和创建方法，可以参照微软官方文档https://docs.microsoft.com/zh-cn/dotnet/api/system.linq.expressions.binaryexpression?view=netframework-4.8

下面是平常使用最多的表达式

ConstantExpression：常量表达式

ParameterExpression：参数表达式

UnaryExpression：一元运算符表达式

BinaryExpression：二元运算符表达式

TypeBinaryExpression：is运算符表达式

ConditionalExpression：条件表达式

MemberExpression：访问字段或属性表达式

MethodCallExpression：调用成员函数表达式

Expression<TDelegate>：委托表达式

2.2、表达式的解析

表达式树解析

通过LambdaExpression类我们可以知道，表达式树包含：参数[Parameters]，表达式树类型[NodeType]，表达式[Body]，返回类型[ReturnType]，Lambda表达式的委托[Compile]以及Lambda表达式名称[name]，如图所示：

表达式解析：

所有的表达式都包含：左节点【Left】，右节点【Right】，类型【NodeType】，不同的表达式还会有其他属性，这里的左右节点依旧是表达式。

下图是BinaryExpression表达式截图

表达式树和表达式里面的类型NodeType是一个枚举，一共有85个类型，有兴趣的朋友可以去了解下。

常用的类型如下：

ExpressionType.And：C#中类似于&

ExpressionType.AndAlso：C#中类似于&&

ExpressionType.Or：C#中类似于|

ExpressionType.OrElse：C#中类似于||

ExpressionType.Equal：C#中类似于==

ExpressionType.NotEqual：C#中类似于!=

ExpressionType.GreaterThan：C#中类似于>

ExpressionType.GreaterThanOrEqual：C#中类似于>=

ExpressionType.LessThan：C#中类似于<

ExpressionType.LessThanOrEqual：C#中类似于<=

ExpressionType.Add：C#中类似于+

ExpressionType.AddChecked：C#中类似于+

ExpressionType.Subtract：C#中类似于-

ExpressionType.SubtractChecked：C#中类似于-

ExpressionType.Divide：C#中类似于/

ExpressionType.Multiply：C#中类似于*

ExpressionType.MultiplyChecked：C#中类似于*

2.3、编译表达式树

在表达式创建那，我们组合创建了一个Lambda表达式，那么应该怎么使用它呢？在“表达式的解析”里面，LambdaExpression类和Expression<TDelegate>类都有一个Compile的方法，学名是Lambda表达式的委托，其实就是Lambda表达式编译函数的委托，所以我们只需要调用他，得到的结果就是一个函数方法。

代码修改如下：

ParameterExpression numParam = Expression.Parameter(typeof(int), "num");
ConstantExpression five = Expression.Constant(5, typeof(int));
BinaryExpression numLessThanFive = Expression.LessThan(numParam, five);
Expression<Func<int, bool>> lambda1 =
    Expression.Lambda<Func<int, bool>>(
        numLessThanFive,
        new ParameterExpression[] { numParam });

Console.WriteLine($"Lambda的内容：{lambda1.ToString()}");

//表达式的编译
var func = lambda1.Compile();
Console.WriteLine($"Lambda的运行结果：{func(6)}");

运行结果

三、总结

这里我们对表达式做了基本的讲解，相信大家对Lambda表达式有了初步的了解，下面我们将继续讲解对一个表达式树的遍历。

C# SQL优化及 Linq 分页 - 天才卧龙 - 博客园

2020-07-07mikel阅读(1038)

来源： C# SQL优化及 Linq 分页 – 天才卧龙 – 博客园

每次写博客，第一句话都是这样的：程序员很苦逼，除了会写程序，还得会写博客！当然，希望将来的一天，某位老板看到此博客，给你的程序员职工加点薪资吧！因为程序员的世界除了苦逼就是沉默。我眼中的程序员大多都不爱说话，默默承受着编程的巨大压力，除了技术上的交流外，他们不愿意也不擅长和别人交流，更不乐意任何人走进他们的内心！

悟出来一个道理，在这儿分享给大家：学历代表你的过去，能力代表你的现在，学习代表你的将来。我们都知道计算机技术发展日新月异，速度惊人的快，你我稍不留神，就会被慢慢淘汰！因此：每日不间断的学习是避免被淘汰的不二法宝。

最近真的是忙的不能再忙了，我一直这样喃喃自语：把自己分成三个也不够用的！还好，一期项目接近尾声，虽说还有些没完成的功能，但也不多了！索性抽点时间写篇博客吧，也希望大家能喜欢这篇博客。

在讲Linq分页之前，首先和大家探讨下SQL，如下：

1)SQL的优化(嘻嘻：因为：我负责公司项目的查询模块，连续做了一个多月的查询及半月多的BUG修改，因此：对SQL的优化还是颇有感悟的，在此分享给大家，希望大家喜欢。谢谢、)：

1、要想写出优美的SQL语句，就必须明白索引查询和非索引查询，SQL在执行的过程中，索引查询的效率要大大高于非索引查询，因此：我们在写SQL语句的时候，应尽量避免非索引查询。当然，实在避免不了的情况下，也应尽可能的优化自己的SQL语句。

1.1、索引查询：要想使用索引查询，就必须首先弄清楚数据表中的索引字段，在此以PLSQL和SQLServer为例进行说明：如下(右键一张表，选择编辑/设计)

PLSQL查看索引字段：

SQLServer查看索引字段：

基本原则就是能通过索引字段进行查询的，就通过索引字段进行查询。

索引查询基本都是精确查询，在使用过程中应避免使用Like、in、ont in、EXISTS、DisTinct等关键词，在此关于索引查询不作太多说明，大家只需把下面的非索引查询搞明白就可以了。

1.2、非索引查询：

上文中提高：要尽可能的使用索引字段进行查询，那么，使用索引字段进行的查询都能称之为索引查询吗？

答案是否定的，譬如：主键字段A是通过GUID生成的，A作为主键可能是索引字段，但是如果你针对A进行Like、in、not in 等操作就会造成非索引查询。当然：一般没有人针对GUID进行LIKE查询，因此：此处举的例子不怎么恰当，勿怪。

那么在什么情况下会执行非索引查询呢？我们应当避免使用哪些关键词呢？如下：

1、DisTinct 关键词：使用此关键字会造成全表比对，慎用！

2、Like 关键词：LIKE操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，如LIKE ‘%5400%’ 这种查询不会引用索引，而LIKE ‘X5400%’则会引用全范围比对查找。

3、in 关键词和 not in 关键词：用IN和NOT IN写出来的SQL的优点是比较容易写及清晰易懂，这比较适合现代软件开发的风格。但是用IN的SQL性能总是比较低的，从Oracle执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别：

ORACLE试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录，如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功，但对于含有分组统计等方面的SQL就不能转换了。

话说另一个关键词：Exists的执行效率要比in高出很多，因此：在这儿建议大家如果能用Exists解决问题时，就尝试用Exists去解决问题吧！

4、IS NULL关键词和IS NOT NULL关键词：索引是不会索引空值的，因此会全局非索引查询。性能低下。

5、UNION关键词：UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。如：

select * from gc_dfys
union
select * from ls_jg_dfys
这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。

推荐方案：采用UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回。

select * from gc_dfys
union all
select * from ls_jg_dfys

6、>= 及 <= 及 != 关键词：>= 和 <=作用于索引字段时会采取索引查询，但是如果作用于非索引字段，就会全表比对查询，因此，要根据情况而定。

7、Order by group by 关键词：order by group by 谈不上属于非索引关键词的范畴，但是使用order by group by 时，排序/分组的字段要使用索引字段，否则效率大大降低

以上是非索引查询涉及到的关键词，请大家慎重使用；

8、慎重 or 关键字，即使用了，也应该结合索引字段进行查询

2）SQL的书写规则：

1、同一功能同一性能不同写法SQL的影响。

如一个SQL在A程序员写的为 Select * from zl_yhjbqk

B程序员写的为 Select * from dlyx.zl_yhjbqk（带表所有者的前缀）

C程序员写的为 Select * from DLYX.ZLYHJBQK（大写表名）

D程序员写的为 Select * from DLYX.ZLYHJBQK（中间多了空格）

以上四个SQL在ORACLE分析整理之后产生的结果及执行的时间是一样的，但是从ORACLE共享内存SGA的原理，可以得出ORACLE对每个SQL 都会对其进行一次分析，并且占用共享内存，如果将SQL的字符串及格式写得完全相同，则ORACLE只会分析一次，共享内存也只会留下一次的分析结果，这不仅可以减少分析SQL的时间，而且可以减少共享内存重复的信息，ORACLE也可以准确统计SQL的执行频率。

2、WHERE后面的条件顺序影响

SQL1、select * from A where A.Name like ‘%陈%’

SQL2、select * from A where A.Id>30 and A.id<=50 and A.Name like ‘%陈%’

以上两个SQL中，第一个会全局查找，第二个会先执行索引查询(将范围锁定为20条记录)，然后在执行非索引查询。因此：SQL1的性能会大大低于SQL2的性能。

3、SQL的执行顺序是先执行子查询，在逐步执行外层的查询，因此：在书写SQL的过程中，应尽可能的针对子查询进行精确查询且应尽可能的缩小子查询的结果集、

4、如果您仅仅只需要查询三个字段，请勿查询所有字段，譬如：select A.Id ,A.Name ,A.Sex from A 和 Select * from A 执行效率绝对是不一样滴。

5、统计查询时：譬如：select Count(1) from A 和 Select Count(*) from A 执行效率绝对是不一样滴。Count(1)的效率要远远大于Count(*)

6、能使用视图的话，就尽可能使用视图，如果你将A，B，C三张表进行连接查询，那么A，B，C三张表将会进行一个笛卡尔积连接，这个过程会大大增加系统的开销，因此：连接查询效率也不会很高。如果做成了视图，就相当于数据库事先做好了三张表的笛卡尔积，这样就省去了这一步骤，因此效率会增加！

7、针对时间字段：如果数据库中时间字段定义成varchar或nvarchar类型，请尽可能的进行数据类型转换，然后使用between and 进行查询，因为between and 执行的是索引查询。

8、批量插入数据时应尽可能的一次执行而非一次一次执行：例如：插入一千条数据：

程序员A：

for(int i=0;i<1000;i++){

insert into A values(….)

连接数据库，执行插入操作，单独执行1000次插入

}

程序员B：

StringBuilder sb = new StringBuilder();

for(int i=0;i<1000;i++){

sb.Append(“insert into A values(….);”);

}

连接数据库，执行插入操作(将所有的插入语句作为一个整体)

同样是插入一千条数据，程序员B的效率要大大高于程序员A

除了以上的叙述外，我们在设计数据库的时候，也应做到最好，譬如：尽量不用text等类型作为字段类型，关于数据库设计的优化，大家自行学习吧！

讲了这么多，上述都是铺垫，还没有涉及到Linq分页，下面开讲

咱继续哈：话说领导昨天找我谈话了，说我做的部分查询模块效率不错，但是没能做到实时刷新，在这儿透漏给大家一个消息，我用的就是Linq查询和分页，效率高了，但又达不到客户的需求，哎…等着被炒鱿鱼吧，领导就是这样，只看结果，不管你怎么实现的。

嘿嘿，在这儿也要提醒大家，用Linq做的查询和分页，是不和数据库交互的，因此做不到实时刷新的哈。实现的基本方式就是：在数据库中进行查询，把查询的结果转化为一个大的集合，然后通过LINQ操作这个集合，所谓的操作也就是查询和分页。因此，你的查询模块和分页模块都是通过LINQ操作咱们首次查出来的大集合实现的，所以：你做的查询和分页就不会和数据库进行交互了，因此做不到实时刷新。

咱继续吹哈，话说，我今天装一把B，给大家演示一个LINQ查询和分页的案列，实现方式就通过C#控制台应用程序吧。

示例代码如下(通过lambda表达式进行相关查询)：

namespace LINQ
{
    class Program
    {
        static void Main(string[] args)
        {
            List<Preson> Lst = LoadData();
            foreach (var P in Lst)
            {
                Console.WriteLine(P.UserName + "&&" + P.Sex + "&&" + P.Age);
            }
            Console.Read();
        }

        /// <summary>
        /// 初始化10000条数据
        /// </summary>
        /// <returns></returns>
        public static List<Preson> LoadData()
        {
            List<Preson> result = new List<Preson>();
            for (int i = 0; i < 10000; i++)
            {
                Preson P = new Preson()
                {
                    UserName = "名字" + i,
                    Sex = (i % 2) == 0 ? "男" : "女",
                    Age = i % 100
                };
                result.Add(P);
            }
            return result;
        }
    }

    class Preson
    {
        private string userName;

        public string UserName
        {
            get { return userName; }
            set { userName = value; }
        }
        private string sex;

        public string Sex
        {
            get { return sex; }
            set { sex = value; }
        }
        private int age;

        public int Age
        {
            get { return age; }
            set { age = value; }
        }
    }
}

这样就生成了年龄0~9999岁的男人、女人各5000个，如下

我们提出如下需求：

1、根据人的年龄由大到小排序(类似于SQL语句中的Order BY DESC 、 ORDER BY ASC 关键字)

 static void Main(string[] args)
        {
            List<Preson> Lst = LoadData();
            Lst = Lst.OrderBy(p => p.Age).Reverse().ToList();
            foreach (var P in Lst)
            {
                Console.WriteLine("姓名："+P.UserName + "   性别：" + P.Sex + "   年龄：" + P.Age);
            }
            Console.Read();
        }

2、基于上述条件、查询年龄介于9000至91000之间的人(类似于SQL语句> < =关键字)

 static void Main(string[] args)
        {
            List<Preson> Lst = LoadData();
            Lst = Lst.OrderBy(p => p.Age).Reverse().ToList();
            Lst = Lst.Where(p => p.Age >= 9000).Reverse().ToList();
            Lst = Lst.Where(p => p.Age <= 9100).Reverse().ToList();
            foreach (var P in Lst)
            {
                Console.WriteLine("姓名："+P.UserName + "   性别：" + P.Sex + "   年龄：" + P.Age);
            }
            Console.Read();
        }

3、基于上述条件、查询姓名中带有‘龙’的人(类似于SQL语句Like关键字中的左右%)

static void Main(string[] args)
        {
            List<Preson> Lst = LoadData();
            Lst = Lst.OrderBy(p => p.Age).Reverse().ToList();
            Lst = Lst.Where(p => p.Age >= 9000).Reverse().ToList();
            Lst = Lst.Where(p => p.Age <= 9100).Reverse().ToList();
            Lst = Lst.Where(p => p.UserName.Contains("龙")).ToList();
            foreach (var P in Lst)
            {
                Console.WriteLine("姓名："+P.UserName + "   性别：" + P.Sex + "   年龄：" + P.Age);
            }
            Console.Read();
        }

4、基于上述条件、查询姓名以‘天才’开通的人(类似于SQL语句Like关键字中的左%)

static void Main(string[] args)
        {
            List<Preson> Lst = LoadData();
            Lst = Lst.OrderBy(p => p.Age).Reverse().ToList();
            Lst = Lst.Where(p => p.Age >= 9000).Reverse().ToList();
            Lst = Lst.Where(p => p.Age <= 9100).Reverse().ToList();
            Lst = Lst.Where(p => p.UserName.Contains("龙")).ToList();
            Lst = Lst.Where(p => p.UserName.StartsWith("天才")).ToList();
            foreach (var P in Lst)
            {
                Console.WriteLine("姓名："+P.UserName + "   性别：" + P.Sex + "   年龄：" + P.Age);
            }
            Console.Read();
        }

5、基于上述条件、查询姓名以’卧龙’结尾的人(类似于SQL语句Like关键字中的右%)

 static void Main(string[] args)
        {
            List<Preson> Lst = LoadData();
            Lst = Lst.OrderBy(p => p.Age).Reverse().ToList();
            Lst = Lst.Where(p => p.Age >= 9000).Reverse().ToList();
            Lst = Lst.Where(p => p.Age <= 9100).Reverse().ToList();
            Lst = Lst.Where(p => p.UserName.Contains("龙")).ToList();
            Lst = Lst.Where(p => p.UserName.StartsWith("天才")).ToList();
            Lst = Lst.Where(p => p.UserName.EndsWith("卧龙")).ToList();
            foreach (var P in Lst)
            {
                Console.WriteLine("姓名："+P.UserName + "   性别：" + P.Sex + "   年龄：" + P.Age);
            }
            Console.Read();
        }

6、基于上述条件、去除重复元素(类似于SQL语句中的Distinct 关键字)

 static void Main(string[] args)
        {
            List<Preson> Lst = LoadData();
            Lst = Lst.OrderBy(p => p.Age).Reverse().ToList();
            Lst = Lst.Where(p => p.Age >= 9000).Reverse().ToList();
            Lst = Lst.Where(p => p.Age <= 9100).Reverse().ToList();
            Lst = Lst.Where(p => p.UserName.Contains("龙")).ToList();
            Lst = Lst.Where(p => p.UserName.StartsWith("天才")).ToList();
            Lst = Lst.Where(p => p.UserName.EndsWith("卧龙")).ToList();
            Lst = Lst.Distinct().ToList();
            foreach (var P in Lst)
            {
                Console.WriteLine("姓名："+P.UserName + "   性别：" + P.Sex + "   年龄：" + P.Age);
            }
            Console.Read();
        }

7、查询年龄总和及平均年龄

 static void Main(string[] args)
        {
            List<Preson> Lst = LoadData();
            Lst = Lst.OrderBy(p => p.Age).Reverse().ToList();
            Lst = Lst.Where(p => p.Age >= 9000).Reverse().ToList();
            Lst = Lst.Where(p => p.Age <= 9100).Reverse().ToList();
            Lst = Lst.Where(p => p.UserName.Contains("龙")).ToList();
            Lst = Lst.Where(p => p.UserName.StartsWith("天才")).ToList();
            Lst = Lst.Where(p => p.UserName.EndsWith("卧龙")).ToList();
            Lst = Lst.Distinct().ToList();
            int A = Lst.Sum(p => p.Age); //查询年龄总和
            double B = Lst.Average(p => p.Age);//查询平均年龄

            foreach (var P in Lst)
            {
                Console.WriteLine("姓名："+P.UserName + "   性别：" + P.Sex + "   年龄：" + P.Age);
            }
            Console.Read();
        }

上述示例代码中是通过lambda表达式进行相关查询、Lambda表达式简单易用、通俗易懂！当然，我们也可以通过LINQ表达式来写以上查询案例。那么，通过LINQ我们应当怎么去写呢？

代码如下：

   static void Main(string[] args)
        {
            List<Preson> Lst = LoadData();
            //--------------------LINQ查询如下--------------------//
            var Result = from r in Lst
                         where r.Age>=9000 && r.Age<=9100 && r.UserName.Contains("龙") &&r.UserName.StartsWith("天才")&&r.UserName.EndsWith("卧龙") orderby r.Age descending
                         select r;
            var Lst_2 = new List<Preson>();
            foreach (var A in Result)//需要进行一次Foreach操作哦
            {
                Lst_2.Add(A);
            }
            Lst = Lst_2;
            foreach (var P in Lst)
            {
                Console.WriteLine("姓名："+P.UserName + "   性别：" + P.Sex + "   年龄：" + P.Age);
            }
            Console.Read();
        }

上述LINQ的查询结果和Lambda表达式查询的结果是一样滴，以上便是Linq和Lambda的查询。LoadData()方法制造的10000条数据可以看作SQL查询得到的结果集。

那么，LINQ实现分页应该怎么写呢？谈到这儿，总算是谈到了LINQ分页，其实LINQ分页只需要用好两个关键字，一个叫做：Skip、另一个叫做：Take

其中Take关键字微软的定义为：

        //
        // 摘要:
        //     从序列的开头返回指定数量的连续元素。
        //
        // 参数:
        //   source:
        //     要从其返回元素的序列。
        //
        //   count:
        //     要返回的元素数量。
        //
        // 类型参数:
        //   TSource:
        //     source 中的元素的类型。
        //
        // 返回结果:
        //     一个 System.Collections.Generic.IEnumerable<T>，包含输入序列开头的指定数量的元素。
        //
        // 异常:
        //   System.ArgumentNullException:
        //     source 为 null。
        public static IEnumerable<TSource> Take<TSource>(this IEnumerable<TSource> source, int count);

其中Skip关键字微软的定义为：

        //
        // 摘要:
        //     跳过序列中指定数量的元素，然后返回剩余的元素。
        //
        // 参数:
        //   source:
        //     一个 System.Collections.Generic.IEnumerable<T>，用于从中返回元素。
        //
        //   count:
        //     返回剩余元素前要跳过的元素数量。
        //
        // 类型参数:
        //   TSource:
        //     source 中的元素的类型。
        //
        // 返回结果:
        //     一个 System.Collections.Generic.IEnumerable<T>，包含输入序列中指定索引后出现的元素。
        //
        // 异常:
        //   System.ArgumentNullException:
        //     source 为 null。
        public static IEnumerable<TSource> Skip<TSource>(this IEnumerable<TSource> source, int count);

看到上述微软给出的定义，我突然想到一个面试题，在此分享给大家，大家也看看他们之间是不是有类似之处，是不是非常雷同呢？

写出一条Sql语句：取出表A中第31到第40记录（SQLServer,以自动增长的ID

作为主键,注意：ID可能不是连续的。

上述斜线的面试题如果用LINQ中的SKIP和TAKE怎么实现呢？

基于上述查询结果，我们接着来查询第31到第40记录，示例代码如下：

 static void Main(string[] args)
        {
            List<Preson> Lst = LoadData();
            //--------------------LINQ查询如下--------------------//
            var Result = from r in Lst
                         where r.Age>=9000 && r.Age<=9100 && r.UserName.Contains("龙") &&r.UserName.StartsWith("天才")&&r.UserName.EndsWith("卧龙") orderby r.Age descending
                         select r;
            var Lst_2 = new List<Preson>();
            foreach (var A in Result)//需要进行一次Foreach操作哦
            {
                Lst_2.Add(A);
            }
            Lst = Lst_2;
            Lst = Lst.Skip(30).Take(10).ToList();
            foreach (var P in Lst)
            {
                Console.WriteLine("姓名："+P.UserName + "   性别：" + P.Sex + "   年龄：" + P.Age);
            }
            Console.Read();
        }

Lst.Skip(30)就是跳过前30行记录，Take(10)就是取10行记录，也就是取出31至40之间的记录。

在此我们作个假设：如果PageSize代表页容量(假设每页有十条数据)、PageIndex代表页码，上述面试题无非就是让我们取出第4页的数据。
其示例代码如下：

 static void Main(string[] args)
        {
            List<Preson> Lst = LoadData();
            //--------------------LINQ查询如下--------------------//
            var Result = from r in Lst
                         where r.Age>=9000 && r.Age<=9100 && r.UserName.Contains("龙") &&r.UserName.StartsWith("天才")&&r.UserName.EndsWith("卧龙") orderby r.Age descending
                         select r;
            var Lst_2 = new List<Preson>();
            foreach (var A in Result)//需要进行一次Foreach操作哦
            {
                Lst_2.Add(A);
            }
            Lst = Lst_2;
            //Lst = Lst.Skip(30).Take(10).ToList();
            int PageSize = 10;//页容量10，每页十条数据
            int PageIndex = 1;//假设页码是从1开始的
            //根据分析得知：我们现在要取第4页的数据，所以PageIndex=4，作如下赋值
            PageIndex = 4;
            Lst = Lst.Skip((PageIndex-1)*PageSize).Take(PageSize).ToList();

            foreach (var P in Lst)
            {
                Console.WriteLine("姓名："+P.UserName + "   性别：" + P.Sex + "   年龄：" + P.Age);
            }
            Console.Read();
        }

上述代码已经作了详细注释，在此不作解读了！

至此：C#SQL优化和LINQ查询、分页就讲完了，感谢您的查阅，谢谢！

我想说的是：我么应当根据需求的不同，灵活选择SQL查询、分页还是LINQ查询分页。如果您的数据量很大，并且系统要求效率高，不需要实时刷新，那么您可以采用LINQ查询。

不管多牛逼的LINQ查询，在有数据库的前提下，其数据源都是通过SQL查询得到的，我们之所以用LINQ查询分页，无非就是为了减少和数据库之间的交互，减少了和数据库之间的交互，性能自然会提升。

怎么用，自己看着办吧！嘿嘿。

最后做一个总结：

        #region Linq 分页
        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="Source"></param>
        /// <param name="PageSize">页容量</param>
        /// <param name="CurrentPageIndex">页码索引 0 代表 第一页</param>
        /// <returns></returns>
        public static IQueryable<T> Pagination<T>(IOrderedQueryable<T> Source, int PageSize, int CurrentPageIndex)
        {
            return Source.Skip(CurrentPageIndex * PageSize).Take(PageSize);
        }
        #endregion

@陈卧龙的博客

[C#] 走进 LINQ 的世界 - 反骨仔 - 博客园

2020-07-05mikel阅读(766)

来源： [C#] 走进 LINQ 的世界 – 反骨仔 – 博客园

走进 LINQ 的世界

序

在此之前曾发表过三篇关于 LINQ 的随笔：

进阶：《LINQ 标准查询操作概述》（强烈推荐）

技巧：《Linq To Objects – 如何操作字符串》和《Linq To Objects – 如何操作文件目录》

现在，自己打算再整理一篇关于 LINQ 入门的随笔，也是图文并茂的哦。

LINQ 简介

语言集成查询 (LINQ) 是 Visual Studio 2008 和 .NET Framework 3.5 版中引入的一项创新功能。

传统上，针对数据的查询都是以简单的字符串表示，而没有编译时类型检查或 IntelliSense 支持。此外，您还必须针对以下各种数据源学习一种不同的查询语言：SQL 数据库、XML 文档、各种 Web 服务等等。通过LINQ，您可以使用语言关键字和熟悉的运算符针对强类型化对象集合编写查询。

　　在 Visual Studio 中，可以为以下数据源编写 LINQ 查询：SQL Server 数据库、XML 文档、ADO.NET 数据集，以及支持 IEnumerable 或泛型 IEnumerable<T> 接口的任意对象集合。

　　使用要求：项目 ≥ .NET Framework 3.5 。

一、介绍 LINQ 查询

查询是一种从数据源检索数据的表达式。随着时间的推移，人们已经为各种数据源开发了不同的语言；例如，用于关系数据库的 SQL 和用于 XML 的 XQuery。因此，开发人员不得不针对他们必须支持的每种数据源或数据格式而学习新的查询语言。LINQ 通过提供一种跨数据源和数据格式使用数据的一致模型，简化了这一情况。在 LINQ 查询中，始终会用到对象。可以使用相同的编码模式来查询和转换 XML 文档、SQL 数据库、ADO.NET 数据集、.NET 集合中的数据以及对其有 LINQ 提供程序可用的任何其他格式的数据。

1.1 查询操作的三个部分

操作三部曲：①取数据源 ②创建查询 ③执行查询

 1 internal class Program
 2 {
 3         private static void Main(string[] args)
 4         {
 5             //1.获取数据源
 6             var nums = new int[7] { 0, 1, 2, 3, 4, 5, 6 };
 7 
 8             //2.创建查询
 9             var numQuery =
10                 from num in nums
11                 where (num % 2) == 0
12                 select num;
13 
14             //3.执行查询
15             foreach (var num in numQuery)
16             {
17                 Console.WriteLine("{0}", num);
18             }
19         }
20 }

下图显示了完整的查询操作。在 LINQ 中，查询的执行与查询本身截然不同；换句话说，查询本身指的是只创建查询变量，不检索任何数据。

1.2 数据源

在上一个示例中，由于数据源是数组，因此它隐式支持泛型 IEnumerable<T> 接口。支持 IEnumerable<T> 或派生接口（如泛型 IQueryable<T>）的类型称为可查询类型。

　　可查询类型不需要进行修改或特殊处理就可以用作 LINQ 数据源。如果源数据还没有作为可查询类型出现在内存中，则 LINQ 提供程序必须以此方式表示源数据。例如，LINQ to XML 将 XML 文档加载到可查询的 XElement 类型中：

　　//从 XML 中创建数据源
　　//using System.Xml.Linq;
　　var contacts = XElement.Load(@"c:\xxx.xml");

在 LINQ to SQL 中，首先需要创建对象关系映射。针对这些对象编写查询，然后由 LINQ to SQL 在运行时处理与数据库的通信。

1     var  db = new Northwnd(@"c:\northwnd.mdf");
2     
3     //查询在伦敦的客户
4     var custQuery =
5         from cust in db.Customers
6         where cust.City == "London"
7         select cust;

1.3 查询

查询指定要从数据源中检索的信息。查询还可以指定在返回这些信息之前如何对其进行排序、分组和结构化。查询存储在查询变量中，并用查询表达式进行初始化。

　　之前的示例中的查询是从整数数组中返回所有的偶数。该查询表达式包含三个子句：from、where 和 select。（如果您熟悉 SQL，您会注意到这些子句的顺序与 SQL 中的顺序相反。）from 子句指定数据源，where 子句指定应用筛选器，select 子句指定返回的元素的类型。目前需要注意的是，在 LINQ 中，查询变量本身不执行任何操作并且不返回任何数据。它只是存储在以后某个时刻执行查询时为生成结果而必需的信息。

1.4 查询执行

　　1.延迟执行

如前所述，查询变量本身只是存储查询命令。实际的查询执行会延迟到在 foreach 语句中循环访问查询变量时发生。此概念称为“延迟执行”。

　　2.强制立即执行

对一系列源元素执行聚合函数的查询必须首先循环访问这些元素。Count、Max、Average 和 First 就属于此类查询。由于查询本身必须使用 foreach 以便返回结果，因此这些查询在执行时不使用显式 foreach 语句。另外还要注意，这些类型的查询返回单个值，而不是 IEnumerable 集合。

1     var numbers = new int[7] { 0, 1, 2, 3, 4, 5, 6 };
2 
3     var evenNumQuery =
4         from num in numbers
5         where (num % 2) == 0
6         select num;
7 
8     var evenNumCount = evenNumQuery.Count();

若要强制立即执行任意查询并缓存其结果，可以调用 ToList<TSource> 或 ToArray<TSource> 方法。

1     var numQuery2 =
2            (from num in numbers
3             where (num % 2) == 0
4             select num).ToList();
5 
6     var numQuery3 =
7           (from num in numbers
8            where (num % 2) == 0
9             select num).ToArray();

此外，还可以通过在紧跟查询表达式之后的位置放置一个 foreach 循环来强制执行查询。但是，通过调用 ToList 或 ToArray，也可以将所有数据缓存在单个集合对象中。

二、基本 LINQ 查询操作

2.1 获取数据源：from

在 LINQ 查询中，第一步是指定数据源。像在大多数编程语言中一样，必须先声明变量，才能使用它。在 LINQ 查询中，最先使用 from 子句的目的是引入数据源和范围变量。

1     //queryAllCustomers 是 IEnumerable<Cutsomer> 类型
2     //数据源 (customers) 和范围变量 (cust)
3     var queryAllCustomers = from cust in customers
4                                            select cust;

范围变量类似于 foreach 循环中的迭代变量，但在查询表达式中，实际上不发生迭代。执行查询时，范围变量将用作对 customers 中的每个后续元素的引用。因为编译器可以推断 cust 的类型，所以您不必显式指定此类型。

2.2 筛选：where

也许最常用的查询操作是应用布尔表达式形式的筛选器。此筛选器使查询只返回那些表达式结果为 true 的元素。使用 where 子句生成结果。实际上，筛选器指定从源序列中排除哪些元素。

1     var queryLondonCustomers = from cust in customers
2                 　　　　　　            where cust.City = "London"
3                  　　　　　              select cust;

您可以使用熟悉的 C# 逻辑 AND（&&）和 OR（||） 运算符来根据需要在 where 子句中应用任意数量的筛选表达式。

where cust.City = "London" && cust.Name = "Devon"

where cust.City = "London" || cust.Name = "Paris"

2.3 排序：orderby

通常可以很方便地将返回的数据进行排序。orderby 子句将使返回的序列中的元素按照被排序的类型的默认比较器进行排序。

1     var queryLondonCustomers = from cust in customers
2                                where cust.City = "London"
3                                orderby cust.Name descending 
4                                select cust;

因为 Name 是一个字符串，所以默认比较器执行从 A 到 Z 的字母排序。若要按相反顺序（从 Z 到 A）对结果进行排序，请使用 orderby…descending 子句。

2.4 分组：group

使用 group 子句，您可以按指定的键分组结果。

 1     var queryLondonCustomers = from cust in customers
 2                     group cust by cust.City;
 3 
 4     foreach (var queryLondonCustomer in queryLondonCustomers)
 5     {
 6        Console.WriteLine(queryLondonCustomer.Key);
 7        foreach (var cust in queryLondonCustomer)
 8        {
 9           Console.WriteLine(cust.Name);
10        }
11     }

在本例中，cust.City 是键。

在使用 group 子句结束查询时，结果采用列表的列表形式。列表中的每个元素是一个具有 Key 成员及根据该键分组的元素列表的对象。在循环访问生成组序列的查询时，您必须使用嵌套的 foreach 循环。外部循环用于循环访问每个组，内部循环用于循环访问每个组的成员。

如果您必须引用组操作的结果，可以使用 into 关键字来创建可进一步查询的标识符。

1     //custQuery 是 IEnumable<IGrouping<string, Customer>> 类型
2     var custQuery = from cust in customers
3                     group cust by cust.City
4                     into custGroup
5                     where custGroup.Count() > 2
6                     orderby custGroup.Key
7                     select custGroup;

2.5 联接：join

联接运算创建数据源中没有显式建模的序列之间的关联。例如，您可以执行联接来查找位于同一地点的所有客户和经销商。在 LINQ 中，join 子句始终针对对象集合而非直接针对数据库表运行。

1     var innerJoinQuery = from cust in customers
2                      　　join dist in distributors on cust.City equals dist.City
3                      　　select new {CustomerName = cust.Name, DistributorName = dist.Name};

在 LINQ 中，join 子句始终针对对象集合而非直接针对数据库表运行。

在 LINQ 中，您不必像在 SQL 中那样频繁使用 join，因为 LINQ 中的外键在对象模型中表示为包含项集合的属性。

    from order in Customer.Orders...

2.6 选择（投影）：select

　　select 子句生成查询结果并指定每个返回的元素的“形状”或类型。

例如，您可以指定结果包含的是整个 Customer 对象、仅一个成员、成员的子集，还是某个基于计算或新对象创建的完全不同的结果类型。当 select 子句生成除源元素副本以外的内容时，该操作称为“投影”。

三、使用 LINQ 进行数据转换

语言集成查询 (LINQ) 不仅可用于检索数据，而且还是一个功能强大的数据转换工具。通过使用 LINQ 查询，您可以将源序列用作输入，并采用多种方式修改它以创建新的输出序列。您可以通过排序和分组来修改该序列，而不必修改元素本身。但是，LINQ 查询的最强大的功能是能够创建新类型。这一功能在 select 子句中实现。例如，可以执行下列任务：

3.1 将多个输入联接到一个输出序列

 1     class Student
 2     {
 3         public string Name { get; set; }
 4 
 5         public int Age { get; set; }
 6 
 7         public string City { get; set; }
 8 
 9         public List<int> Scores { get; set; }
10     }
11 
12     class Teacher
13     {
14         public int Id { get; set; }
15 
16         public string Name { get; set; }
17 
18         public int Age { get; set; }
19 
20         public string City { get; set; }
21 
22     }

 1     internal class Program
 2     {
 3         private static void Main(string[] args)
 4         {
 5             //创建第一个数据源
 6             var students = new List<Student>()
 7             {
 8                 new Student()
 9                 {
10                     Age = 23,
11                     City = "广州",
12                     Name = "小C",
13                     Scores = new List<int>(){85,88,83,97}
14                 },
15                 new Student()
16                 {
17                     Age = 18,
18                     City = "广西",
19                     Name = "小明",
20                     Scores = new List<int>(){86,78,85,90}
21                 },
22                 new Student()
23                 {
24                     Age = 33,
25                     City = "梦里",
26                     Name = "小叁",
27                     Scores = new List<int>(){86,68,73,97}
28                 }
29             };
30 
31             //创建第二个数据源
32             var teachers = new List<Teacher>()
33             {
34                 new Teacher()
35                 {
36                     Age = 35,
37                     City = "梦里",
38                     Name = "啵哆"
39                 },
40                 new Teacher()
41                 {
42                     Age = 28,
43                     City = "云南",
44                     Name = "小红"
45                 },
46                 new Teacher()
47                 {
48                     Age = 38,
49                     City = "河南",
50                     Name = "丽丽"
51                 }
52             };
53 
54             //创建查询
55             var peopleInDreams = (from student in students
56                             where student.City == "梦里"
57                             select student.Name)
58                             .Concat(from teacher in teachers
59                                     where teacher.City == "梦里"
60                                     select teacher.Name);
61 
62             //执行查询
63             foreach (var person in peopleInDreams)
64             {
65                 Console.WriteLine(person);
66             }
67 
68             Console.Read();
69         }
70     }

3.2 选择各个源元素的子集

1. 若要只选择源元素的一个成员，请使用点运算。

1     var query = from cust in Customers
2                     select cust.City;

2. 若要创建包含源元素的多个属性的元素，可以使用具有命名对象或匿名类型的对象初始值设定项。

1     var query = from cust in Customer
2                    select new {Name = cust.Name, City = cust.City};

3.3 将内存中的对象转换为 XML

 1             //创建数据源
 2             var students = new List<Student>()
 3             {
 4                 new Student()
 5                 {
 6                     Age = 18,
 7                     Name = "小A",
 8                     Scores = new List<int>() {88,85,74,66 }
 9                 },
10                 new Student()
11                 {
12                     Age = 35,
13                     Name = "小B",
14                     Scores = new List<int>() {88,85,74,66 }
15                 },
16                 new Student()
17                 {
18                     Age = 28,
19                     Name = "小啥",
20                     Scores = new List<int>() {88,85,74,66 }
21                 }
22             };
23 
24             //创建查询
25             var studentsToXml = new XElement("Root",
26                 from student in students
27                 let x = $"{student.Scores[0]},{student.Scores[1]},{student.Scores[2]},{student.Scores[3]}"
28                 select new XElement("student",
29                 new XElement("Name", student.Name),
30                 new XElement("Age", student.Age),
31                 new XElement("Scores", x))
32             );
33 
34             //执行查询
35             Console.WriteLine(studentsToXml);

3.4 对源元素执行操作

　　输出序列可能不包含源序列的任何元素或元素属性。输出可能是通过将源元素用作输入参数计算出的值的序列。

 1             //数据源
 2             double[] radii = {1, 2, 3};
 3 
 4             //创建查询
 5             var query = from radius in radii
 6                 select $"{radius * radius * 3.14}";
 7 
 8             //执行查询
 9             foreach (var i in query)
10             {
11                 Console.WriteLine(i);
12             }

【备注】$”{radius * radius * 3.14}” 相当于 string.Format(“{0}”,radius * radius * 3.14)，这里采用的是 C# 6.0 的语法。

四、LINQ 查询操作的类型关系

LINQ 查询操作在数据源、查询本身及查询执行中是强类型的。查询中变量的类型必须与数据源中元素的类型和 foreach 语句中迭代变量的类型兼容。强类型可以保证在编译时捕获类型错误，以便及时改正。

4.1 不转换源数据的查询

下图演示不对数据执行转换的 LINQ to Objects 查询操作。源包含一个字符串序列，查询输出也是一个字符串序列。

①数据源的类型参数决定范围变量的类型。

②选择的对象的类型决定查询变量的类型。此处的 name 为一个字符串。因此，查询变量是一个 IEnumerable<字符串>。

③在 foreach 语句中循环访问查询变量。因为查询变量是一个字符串序列，所以迭代变量也是一个字符串。

4.2 转换源数据的查询

　　下图演示对数据执行简单转换的 LINQ to SQL 查询操作。查询将一个 Customer 对象序列用作输入，并只选择结果中的 Name 属性。因为 Name 是一个字符串，所以查询生成一个字符串序列作为输出。

①数据源的类型参数决定范围变量的类型。

②select 语句返回 Name 属性，而非完整的 Customer 对象。因为 Name 是一个字符串，所以 custNameQuery 的类型参数是 string，而非Customer。

③因为 custNameQuery 是一个字符串序列，所以 foreach 循环的迭代变量也必须是 string。

下图演示另一种转换。select 语句返回只捕获原始 Customer 对象的两个成员的匿名类型。

①数据源的类型参数始终为查询中的范围变量的类型。

②因为 select 语句生成匿名类型，所以必须使用 var 隐式类型化查询变量。

③因为查询变量的类型是隐式的，所以 foreach 循环中的迭代变量也必须是隐式的。

4.3 让编译器推断类型信息

您也可以使用关键字 var，可用于查询操作中的任何局部变量。但是，编译器为查询操作中的各个变量提供强类型。

五、LINQ 中的查询语法和方法语法

我们编写的 LINQ 查询语法，在编译代码时，CLR 会将查询语法转换为方法语法。这些方法调用标准查询运算符的名称类似 Where、Select、GroupBy、Join、Max和 Average，我们也是可以直接使用这些方法语法的。

查询语法和方法语法语义相同，但是，许多人员发现查询语法更简单、更易于阅读。某些查询必须表示为方法调用。例如，必须使用方法调用表示检索元素的数量与指定的条件的查询。还必须使用方法需要检索元素的最大值在源序列的查询。System.Linq 命名空间中的标准查询运算符的参考文档通常使用方法语法。

5.1 标准查询运算符扩展方法

 1         static void Main(string[] args)
 2         {
 3             var nums = new int[4] { 1, 2, 3, 4 };
 4             
 5             //创建查询表达式
 6             var qureyNums = from n in nums
 7                             where n % 2 == 0
 8                             orderby n descending
 9                             select n;
10 
11             Console.WriteLine("qureyNums:");
12             foreach (var n in qureyNums)
13             {
14                 Console.WriteLine(n);
15             }
16     
17             //使用方法进行查询
18             var queryNums2 = nums.Where(n => n % 2 == 0).OrderByDescending(n => n);
19 
20             Console.WriteLine("qureyNums2:");
21             foreach (var n in queryNums2)
22             {
23                 Console.WriteLine(n);
24             }
25 
26             Console.Read();
27         }

两个示例的输出是相同的。您可以看到两种形式的查询变量的类型是相同的：IEnumerable<T>。

若要了解基于方法的查询，让我们进一步地分析它。注意，在表达式的右侧，where 子句现在表示为对 numbers 对象的实例方法，在您重新调用该对象时其类型为 IEnumerable<int>。如果您熟悉泛型 IEnumerable<T> 接口，那么您就会了解，它不具有 Where 方法。但是，如果您在 Visual Studio IDE 中调用 IntelliSense 完成列表，那么您不仅将看到 Where 方法，而且还会看到许多其他方法，如 Select、SelectMany、Join 和Orderby。下面是所有标准查询运算符。

尽管看起来 IEnumerable<T> 似乎已被重新定义以包括这些附加方法，但事实上并非如此。这些标准查询运算符都是作为“扩展方法”实现的。

5.2 Lambda 表达式

在前面的示例中，通知该条件表达式 (num % 2 == 0) 是作为内联参数。Where 方法：Where(num => num % 2 == 0) 此内联表达式称为 lambda 表达式。将代码编写为匿名方法或泛型委托或表达式树是一种便捷的方法，否则编写起来就要麻烦得多。=> 是 lambda 运算符，可读为“goes to”。运算符左侧的 num 是输入变量，与查询表达式中的 num 相对应。编译器可推断 num 的类型，因为它了解 numbers 是泛型 IEnumerable<T> 类型。lambda 表达式与查询语法中的表达式或任何其他 C# 表达式或语句中的表达式相同；它可以包括方法调用和其他复杂逻辑。“返回值”就是表达式结果。

5.3 查询的组合性

在上面的代码示例中，请注意 OrderBy 方法是通过在对 Where 的调用中使用点运算符来调用的。Where 生成筛选序列，然后 Orderby 通过对该序列排序来对它进行操作。因为查询会返回 IEnumerable，所以您可通过将方法调用链接在一起，在方法语法中将这些查询组合起来。这就是在您通过使用查询语法编写查询时编译器在后台所执行的操作。并且由于查询变量不存储查询的结果，因此您可以随时修改它或将它用作新查询的基础，即使在执行它后。

传送门

入门：《走进 LINQ 的世界》

进阶：《LINQ 标准查询操作概述》（强烈推荐）

技巧：《Linq To Objects – 如何操作字符串》和《Linq To Objects – 如何操作文件目录》

本文首联：http://www.cnblogs.com/liqingwen/p/5832322.html

【参考】https://msdn.microsoft.com/zh-cn/library/bb397897(v=vs.100).aspx 等

【来源】本文引用部分微软官方文档的图片

黑马程序员_ADO.Net（ExecuteReader，Sql注入与参数添加，DataSet，总结DataSet与SqlDataReader ）_雨后春笋_aspx专栏-CSDN博客_.net sqldatareader 防注入

2020-07-05mikel阅读(646)

来源： 黑马程序员_ADO.Net（ExecuteReader，Sql注入与参数添加，DataSet，总结DataSet与SqlDataReader ）_雨后春笋_aspx专栏-CSDN博客_.net sqldatareader 防注入

一．执行有多行结果集的用ExecuteReader

SQLDateReader reader=cmd.ExecuteReader();//查询结果在数据库中，不占客户端电脑内存

While(reader.Reader()){

Console.WriteLine(reader.GetString(1));

}

//初始指针指向第一条数据之前，每调用一次Reader(),指针下移一条，只要没有移到最后一条之后，就返回true;

private void Button_Click(object sender, RoutedEventArgs e)

{

using (SQLConnection conn = new SqlConnection(“server=.;database=ADO.NET;uid=sa;pwd=***”))

{

conn.Open();

using (SqlCommand com = conn.CreateCommand())

{

//需要执行的T-sql语句

com.CommandText = “select * from T_User”;

//执行查询语句并获取数据集

using (SqlDataReader read = com.ExecuteReader())

{

while (read.Read())

{

//read作为服务器端查询的数据集，

//通过调用方法：read.Get要查找的数据类型（第几列）

string username = read.GetString(1);

string userpwd = read.GetString(2);

MessageBox.Show(username+”,”+userpwd);

}

二．Sql注入与参数添加

//SQL注入例子：1’or ‘1’=’1

cmd.CommandText = “select Userpwd from T_User where UserName='”+txtName.Text+”‘”;

//防止sql注入，使用参数更加严密：

string name = this.textBox1.Text;

string pwd = this.textBox2.Text;

using (SqlConnection conn = new SqlConnection(“server=.;database=ADO.NET;uid=sa;pwd=***”))

{

conn.Open();

using (SqlCommand cmd = conn.CreateCommand())

{

cmd.CommandText = “select * from T_User where UserName=@username and UserPwd=@userpwd”;

cmd.Parameters.Add(new SqlParameter(“@username”,name));

cmd.Parameters.Add(new SqlParameter(“@userpwd”, pwd));

using (SqlDataReader read = cmd.ExecuteReader())

{

if (read.Read())

{

MessageBox.Show(“登陆成功”);

}

else

{

MessageBox.Show(“登陆失败”);

}

三．DataSet

1.SqlDataReader是连接相关的，SqlDataReader中的查询结果并不是放在程序中的，而是放在数据库服务器中，SqlDataReader只是相当于放了一个指针（游标），只能读取当前游标指向的行，一旦连接断开就不能再读取。这样的好处就是无论查询结果有多少条，对程序占用的内存几乎没有影响；

2.SqlDataReader对于小数据量的数据来说带来的只有麻烦。ADO.Net中提供了数据集的机制，将查询结果集填充到本地内存中，这样连接中断，不会影响数据的读取，数据集的好处是降低数据库服务器压力，编程也简单

3.DataSet的用法：

using (SqlConnection conn = new SqlConnection(“server=.;database=ADO.NET;uid=sa;pwd=***”))

{

conn.Open();

using (SqlCommand cmd = conn.CreateCommand())

{

cmd.CommandText = “select * from T_User”;

SqlDataAdapter sda = new SqlDataAdapter(cmd);

DataSet ds = new DataSet();

sda.Fill(ds);

DataTable dt = ds.Tables[0];

for (int i = 0; i < dt.Rows.Count; i++)

{

string name = dt.Rows[i][1].ToString();

MessageBox.Show(name);

}

四．总结DataSet与SqlDataReader

DataSet 是通过SqlDataAdapter类通过cmd查询的数据集，它是放在客户端内存中，SqlDataReader是执行cmd.ExecuteReader()查询语句所获得的数据集，它是放在数据库服务器端的，两者各有利弊，总的来说，如果数据集较小，则使用DataSet好，如果数据集非常大，还是在数据库中的好，无论哪一种都是需要连接数据库的，所以都要使用Using()查询完以后自动关闭连接。

SqlDataReader类,SqlDataAdapter类都是是实现了IDisposable接口(这个接口自动关闭连接)，所以也要像SqlConnection类，SqlCommand类一样使用Using()进行资源管理;
————————————————
版权声明：本文为CSDN博主「tyzshare」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/u010796875/article/details/17386131

构建属于自己的ORM框架之二--IQueryable的奥秘 - Poiuyt_cyc - 博客园

2020-07-05mikel阅读(654)

来源： 构建属于自己的ORM框架之二–IQueryable的奥秘 – Poiuyt_cyc – 博客园

上篇文章标题乱起，被吐槽了，这次学乖了。

上篇文章中介绍了如何解析Expression生成对应的SQL语句，以及IQueryable的一些概念，以及我们所搭建的框架的思想等。但还没把它们结合并应用起来。这一篇文章将更黄更暴力，揭露IQueryable在实际使用中延迟加载的实现原理，结合上篇对Expression的解析，我们来实现一个自己的“延迟加载”

如果还不太了解如何解析Expression和IQueryable的一些基本概念，可以先看看我的上篇文章

我们先来做些基本工作，定义一个IDataBase接口，里面可以定义些查询，删除，修改，新增等方法，为了节约时间，我们就定义一个查询和删除的方法，再定义一个获取IQueryable<T>实例的方法

 　　public interface IDataBase
    {
        List<T> FindAs<T>(Expression<Func<T, bool>> lambdawhere);
        int Remove<T>(Expression<Func<T, bool>> lambdawhere);
        IQueryable<T> Source<T>();
    }

再添加一个类DBSQL，实现我们上面的IDataBase接口，这个类是负责提供对SQL数据库的操作

 public class DBSql : IDataBase
    {
        public List<T> FindAs<T>(Expression<Func<T, bool>> lambdawhere)
        {
            throw new NotImplementedException();
        }

        public int Remove<T>(Expression<Func<T, bool>> lambdawhere)
        {
            throw new NotImplementedException();
        }

        public IQueryable<T> Source<T>()
        {
            throw new NotImplementedException();
        }
    }

IQueryable<T>

上篇文章中有个朋友的回复对IQueryable的解释十分到位，“IQueryable只存贮条件，不立即运行，从而可以实现延迟加载。”那它是如何存贮条件，如何延迟加载的？

这时我们为了提供 public IQueryable<T> Source<T>() 所需的对象。我们再来建一个SqlQuery类，实现IQueryable<T>。

 　　public class SqlQuery<T> : IQueryable<T>
    {
        public IEnumerator<T> GetEnumerator()
        {
            throw new NotImplementedException();
        }

        System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
        {
            throw new NotImplementedException();
        }

        public Type ElementType
        {
            get { throw new NotImplementedException(); }
        }

        public Expression Expression
        {
            get { throw new NotImplementedException(); }
        }

        public IQueryProvider Provider
        {
            get { throw new NotImplementedException(); }
        }
    }

看到这里大家都不陌生吧？

GetEnumerator()是IEnumerable<T>里的。有了它我们就能foreach了。有泛型和非泛型版本，所以有2个

Type提供访问当前对象的类型（反正由你定义。。。）

Expression是贮存查询条件的

IQueryProvider简单的翻译过来就是查询提供者，它是负责创建查询条件和执行查询的。我们写一个SqlProvider类来实现它

  public class SqlProvider<T> : IQueryProvider
    {

        public IQueryable<TElement> CreateQuery<TElement>(Expression expression)
        {
            throw new NotImplementedException();
        }

        public IQueryable CreateQuery(Expression expression)
        {
            throw new NotImplementedException();
        }

        public TResult Execute<TResult>(Expression expression)
        {
            throw new NotImplementedException();
        }

        public object Execute(Expression expression)
        {
            throw new NotImplementedException();
        }
    }

CreateQuery是创建查询条件。。

平时我们

IQueryable query=xxx源;

query=query.Where(x=>x.Name==”123″);

这时Where方法里做的其实就是将前面query的Expression属性和Where里的(x=>x.Name==”123″)相并，并且调用Provider属性里的CreateQuery方法。我们可以把我们的代码改成这样，来看看到底是不是这么回事。

   public class DBSql : IDataBase
    {
        public IQueryable<T> Source<T>()
        {
            return new SqlQuery<T>();
        }

        public List<T> FindAs<T>(Expression<Func<T, bool>> lambdawhere)
        {
            throw new NotImplementedException();
        }

        public int Remove<T>(Expression<Func<T, bool>> lambdawhere)
        {
            throw new NotImplementedException();
        }
    }

    public class SqlQuery<T> : IQueryable<T>
    {

        private Expression _expression;
        private IQueryProvider _provider;

        public SqlQuery()
        {
            _provider = new SqlProvider<T>();
            _expression = Expression.Constant(this);
        }

        public SqlQuery(Expression expression, IQueryProvider provider)
        {
            _expression = expression;
            _provider = provider;
        }

        public IEnumerator<T> GetEnumerator()
        {
            throw new NotImplementedException();
        }

        System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
        {
            throw new NotImplementedException();
        }

        public Type ElementType
        {
            get { return typeof(SqlQuery<T>); }
        }

        public Expression Expression
        {
            get { return _expression; }
        }

        public IQueryProvider Provider
        {
            get { return _provider; }
        }
    }

    public class SqlProvider<T> : IQueryProvider
    {
        public IQueryable<TElement> CreateQuery<TElement>(Expression expression)
        {
            IQueryable<TElement> query = new SqlQuery<TElement>(expression, this);
            return query;
        }

        public IQueryable CreateQuery(Expression expression)
        {
            throw new NotImplementedException();
        }

        public TResult Execute<TResult>(Expression expression)
        {
            throw new NotImplementedException();
        }

        public object Execute(Expression expression)
        {
            throw new NotImplementedException();
        }
    }

　　　　 public class Staff
        {
            public int ID { get; set; }
            public string Code { get; set; }
            public string Name { get; set; }
            public DateTime? Birthday { get; set; }
            public bool Deletion { get; set; }
        }

        static void Main(string[] args)
        {
            IDataBase db = new DBSql();
            IQueryable<Staff> query = db.Source<Staff>();
            string name = "张三";
            Expression express = null;
            query = query.Where(x => x.Name == "赵建华");
            express = query.Expression;
            query = query.Where(x => x.Name == name);
            express = query.Expression;
        }

段点打在

public IQueryable<TElement> CreateQuery<TElement>(Expression expression)

每次query.Where都会跑这里来。并且Expression都是前后相并的结果。

到了这一步，相信大家都明白了IQueryable只存贮条件这个概念了吧。

那延迟加载呢？什么时候加载啊！当我们foreach或者ToList/ToArray时啊。这时你想到了什么？GetEnumerator()。在调用GetEnumerator()时。我们再调用Provider里的Execute(Expression)。里面解析Expression，生成SQL语句，通过反射的方式生成实例，再一个个返回回去。完成！下面我直接给代码了。解析Expression的类我也改了，这个更黄更暴力。

  public class ResolveExpression
    {
        public Dictionary<string, object> Argument;
        public string SqlWhere;
        public SqlParameter[] Paras;
        private int index = 0;
        /// <summary>
        /// 解析lamdba，生成Sql查询条件
        /// </summary>
        /// <param name="expression"></param>
        /// <returns></returns>
        public void ResolveToSql(Expression expression)
        {
            this.index = 0;
            this.Argument = new Dictionary<string, object>();
            this.SqlWhere = Resolve(expression);
            this.Paras = Argument.Select(x => new SqlParameter(x.Key, x.Value)).ToArray();
        }

        private object GetValue(Expression expression)
        {
            if (expression is ConstantExpression)
                return (expression as ConstantExpression).Value;
            if (expression is UnaryExpression)
            {
                UnaryExpression unary = expression as UnaryExpression;
                LambdaExpression lambda = Expression.Lambda(unary.Operand);
                Delegate fn = lambda.Compile();
                return fn.DynamicInvoke(null);
            }
            if (expression is MemberExpression)
            {
                MemberExpression member = expression as MemberExpression;
                string name = member.Member.Name;
                var constant = member.Expression as ConstantExpression;
                if (constant == null)
                    throw new Exception("取值时发生异常" + member);
                return constant.Value.GetType().GetFields().First(x => x.Name == name).GetValue(constant.Value);
            }
            throw new Exception("无法获取值" + expression);
        }

        private string Resolve(Expression expression)
        {
            if (expression is LambdaExpression)
            {
                LambdaExpression lambda = expression as LambdaExpression;
                expression = lambda.Body;
                return Resolve(expression);
            }
            if (expression is BinaryExpression)//解析二元运算符
            {
                BinaryExpression binary = expression as BinaryExpression;
                if (binary.Left is MemberExpression)
                {
                    object value = GetValue(binary.Right);
                    return ResolveFunc(binary.Left, value, binary.NodeType);
                }
                if (binary.Left is MethodCallExpression && (binary.Right is UnaryExpression || binary.Right is MemberExpression))
                {
                    object value = GetValue(binary.Right);
                    return ResolveLinqToObject(binary.Left, value, binary.NodeType);
                }
            }
            if (expression is UnaryExpression)//解析一元运算符
            {
                UnaryExpression unary = expression as UnaryExpression;
                if (unary.Operand is MethodCallExpression)
                {
                    return ResolveLinqToObject(unary.Operand, false);
                }
                if (unary.Operand is MemberExpression)
                {
                    return ResolveFunc(unary.Operand, false, ExpressionType.Equal);
                }
            }
            if (expression is MethodCallExpression)//解析扩展方法
            {
                return ResolveLinqToObject(expression, true);
            }
            if (expression is MemberExpression)//解析属性。。如x.Deletion
            {
                return ResolveFunc(expression, true, ExpressionType.Equal);
            }
            var body = expression as BinaryExpression;
            if (body == null)
                throw new Exception("无法解析" + expression);
            var Operator = GetOperator(body.NodeType);
            var Left = Resolve(body.Left);
            var Right = Resolve(body.Right);
            string Result = string.Format("({0} {1} {2})", Left, Operator, Right);
            return Result;
        }

        /// <summary>
        /// 根据条件生成对应的sql查询操作符
        /// </summary>
        /// <param name="expressiontype"></param>
        /// <returns></returns>
        private string GetOperator(ExpressionType expressiontype)
        {
            switch (expressiontype)
            {
                case ExpressionType.And:
                    return "and";
                case ExpressionType.AndAlso:
                    return "and";
                case ExpressionType.Or:
                    return "or";
                case ExpressionType.OrElse:
                    return "or";
                case ExpressionType.Equal:
                    return "=";
                case ExpressionType.NotEqual:
                    return "<>";
                case ExpressionType.LessThan:
                    return "<";
                case ExpressionType.LessThanOrEqual:
                    return "<=";
                case ExpressionType.GreaterThan:
                    return ">";
                case ExpressionType.GreaterThanOrEqual:
                    return ">=";
                default:
                    throw new Exception(string.Format("不支持{0}此种运算符查找！" + expressiontype));
            }
        }


        private string ResolveFunc(Expression left, object value, ExpressionType expressiontype)
        {
            string Name = (left as MemberExpression).Member.Name;
            string Operator = GetOperator(expressiontype);
            string Value = value.ToString();
            string CompName = SetArgument(Name, Value);
            string Result = string.Format("({0} {1} {2})", Name, Operator, CompName);
            return Result;
        }

        private string ResolveLinqToObject(Expression expression, object value, ExpressionType? expressiontype = null)
        {
            var MethodCall = expression as MethodCallExpression;
            var MethodName = MethodCall.Method.Name;
            switch (MethodName)//这里其实还可以改成反射调用，不用写switch
            {
                case "Contains":
                    if (MethodCall.Object != null)
                        return Like(MethodCall);
                    return In(MethodCall, value);
                case "Count":
                    return Len(MethodCall, value, expressiontype.Value);
                case "LongCount":
                    return Len(MethodCall, value, expressiontype.Value);
                default:
                    throw new Exception(string.Format("不支持{0}方法的查找！", MethodName));
            }
        }

        private string SetArgument(string name, string value)
        {
            name = "@" + name;
            string temp = name;
            while (Argument.ContainsKey(temp))
            {
                temp = name + index;
                index = index + 1;
            }
            Argument[temp] = value;
            return temp;
        }

        private string In(MethodCallExpression expression, object isTrue)
        {
            var Argument1 = expression.Arguments[0];
            var Argument2 = expression.Arguments[1] as MemberExpression;
            var fieldValue = GetValue(Argument1);
            object[] array = fieldValue as object[];
            List<string> SetInPara = new List<string>();
            for (int i = 0; i < array.Length; i++)
            {
                string Name_para = "InParameter" + i;
                string Value = array[i].ToString();
                string Key = SetArgument(Name_para, Value);
                SetInPara.Add(Key);
            }
            string Name = Argument2.Member.Name;
            string Operator = Convert.ToBoolean(isTrue) ? "in" : " not in";
            string CompName = string.Join(",", SetInPara);
            string Result = string.Format("{0} {1} ({2})", Name, Operator, CompName);
            return Result;
        }

        private string Like(MethodCallExpression expression)
        {
            Expression argument = expression.Arguments[0];
            object Temp_Vale = GetValue(argument);
            string Value = string.Format("%{0}%", Temp_Vale);
            string Name = (expression.Object as MemberExpression).Member.Name;
            string CompName = SetArgument(Name, Value);
            string Result = string.Format("{0} like {1}", Name, CompName);
            return Result;
        }

        private string Len(MethodCallExpression expression, object value, ExpressionType expressiontype)
        {
            object Name = (expression.Arguments[0] as MemberExpression).Member.Name;
            string Operator = GetOperator(expressiontype);
            string CompName = SetArgument(Name.ToString(), value.ToString());
            string Result = string.Format("len({0}){1}{2}", Name, Operator, CompName);
            return Result;
        }

    }

　　public interface IDataBase
    {
        List<T> FindAs<T>(Expression<Func<T, bool>> lambdawhere);
        int Remove<T>(Expression<Func<T, bool>> lambdawhere);
        IQueryable<T> Source<T>();
    }

namespace Data.DataBase
{
    public class DBSql : IDataBase
    {
        private readonly static string ConnectionString = @"Data Source=.;Initial Catalog=btmmcms-Standard;Persist Security Info=True;User ID=sa;Password=sa;";

        public IQueryable<T> Source<T>()
        {
            return new SqlQuery<T>();
        }

        public List<T> FindAs<T>(Expression<Func<T, bool>> lambdawhere)
        {
            using (SqlConnection Conn = new SqlConnection(ConnectionString))
            {
                using (SqlCommand Command = new SqlCommand())
                {
                    try
                    {
                        Command.Connection = Conn;
                        Conn.Open();
                        string sql = string.Format("select * from {0}", typeof(T).Name);
                        if (lambdawhere != null)
                        {
                            ResolveExpression resolve = new ResolveExpression();
                            resolve.ResolveToSql(lambdawhere);
                            sql = string.Format("{0} where {1}", sql, resolve.SqlWhere);
                            Command.Parameters.AddRange(resolve.Paras);
                        }
                        //为了测试，就在这里打印出sql语句了
                        Console.WriteLine(sql);
                        Command.CommandText = sql;
                        SqlDataReader dataReader = Command.ExecuteReader();
                        List<T> ListEntity = new List<T>();
                        while (dataReader.Read())
                        {
                            var constructor = typeof(T).GetConstructor(new Type[] { });
                            T Entity = (T)constructor.Invoke(null);
                            foreach (var item in Entity.GetType().GetProperties())
                            {
                                var value = dataReader[item.Name];
                                if (value == null)
                                    continue;
                                if (value is DBNull)
                                    value = null;
                                item.SetValue(Entity, value, null);
                            }
                            ListEntity.Add(Entity);
                        }
                        if (ListEntity.Count == 0)
                            return null;
                        return ListEntity;
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        Conn.Close();
                    }
                }
            }
        }

        public int Remove<T>(Expression<Func<T, bool>> lambdawhere)
        {
            throw new NotImplementedException();
        }
    }

    public class SqlQuery<T> : IQueryable<T>
    {

        private Expression _expression;
        private IQueryProvider _provider;

        public SqlQuery()
        {
            _provider = new SqlProvider<T>();
            _expression = Expression.Constant(this);
        }

        public SqlQuery(Expression expression, IQueryProvider provider)
        {
            _expression = expression;
            _provider = provider;
        }

        public IEnumerator<T> GetEnumerator()
        {
            var result = _provider.Execute<List<T>>(_expression);
            if (result == null)
                yield break;
            foreach (var item in result)
            {
                yield return item;
            }
        }

        System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
        {
            throw new NotImplementedException();
        }

        public Type ElementType
        {
            get { return typeof(SqlQuery<T>); }
        }

        public Expression Expression
        {
            get { return _expression; }
        }

        public IQueryProvider Provider
        {
            get { return _provider; }
        }
    }

    public class SqlProvider<T> : IQueryProvider
    {

        public IQueryable<TElement> CreateQuery<TElement>(Expression expression)
        {
            IQueryable<TElement> query = new SqlQuery<TElement>(expression, this);
            return query;
        }

        public IQueryable CreateQuery(Expression expression)
        {
            throw new NotImplementedException();
        }

        public TResult Execute<TResult>(Expression expression)
        {
            MethodCallExpression methodCall = expression as MethodCallExpression;
            Expression<Func<T, bool>> result = null;
            while (methodCall != null)
            {
                Expression method = methodCall.Arguments[0];
                Expression lambda = methodCall.Arguments[1];
                LambdaExpression right = (lambda as UnaryExpression).Operand as LambdaExpression;
                if (result == null)
                {
                    result = Expression.Lambda<Func<T, bool>>(right.Body, right.Parameters);
                }
                else
                {
                    Expression left = (result as LambdaExpression).Body;
                    Expression temp = Expression.And(right.Body, left);
                    result = Expression.Lambda<Func<T, bool>>(temp, result.Parameters);
                }
                methodCall = method as MethodCallExpression;
            }
            var source = new DBSql().FindAs<T>(result);
            dynamic _temp = source;
            TResult t = (TResult)_temp;
            return t;
        }

        public object Execute(Expression expression)
        {
            throw new NotImplementedException();
        }
    }
}

搞定，这时可以改下数据库连接，连到自己的数据库，然后像下面这样，添加一个实体类（要与数据库表对应），就可以使用了

   class Program
    {
        public class Staff
        {
            public int ID { get; set; }
            public string Code { get; set; }
            public string Name { get; set; }
            public DateTime? Birthday { get; set; }
            public bool Deletion { get; set; }
        }

        static void Main(string[] args)
        {
            IDataBase db = new DBSql();
            IQueryable<Staff> query = db.Source<Staff>();
            query = query.Where(x => x.Name == "张三");
            foreach (var item in query)
            {

            }
        }
    }

是不是很简单？

虽然信息量有点大，但慢慢理清并消化，我相信会对你又很大帮助！

抛弃EF，20分构建一个属于自己的ORM框架 - Poiuyt_cyc - 博客园

2020-07-05mikel阅读(652)

来源： 抛弃EF，20分构建一个属于自己的ORM框架 – Poiuyt_cyc – 博客园

相信EF大家都不陌生了，因为数据库表跟程序实体是一一对应的原因，我们能够通过lambda这种函数式的编程方式进行操作数据库，感觉非常清晰明了。与我们直接写SQL相比，lambda是强类型，拥有更好的扩展性，伸缩性，而且编程更加的方便，快捷。。下面我们就基于Expression和lambda来与大家构建一个属于自己的ORM框架。

思路的话很简单，就是将lambda转换成我们对应的数据库所需的查询条件，然后执行查询，再将结果以反射的方式封装成List<T>返回出去。

Expression

大家使用EF的时候多多少少会留意到有Expression这个东西。特别是查询时会看到要你传入Expression<Func<T,bool>>这样类型的参数，它又和Func<T,bool>有什么比同呢？

Expression<Func<T,bool>>是表达式树，我们可以通过它来分析我们的委托中的函数。当调用Compile方法后就会变成委托，才能执行。

Func<T,bool>只是一个普通的委托。

例如我们现在有个实体类Staff

 public class Staff
    {
        public string Name { get; set; }
        public int Age { get; set; }
        public string Code { get; set; }
        public DateTime? Birthday { get; set; }
        public bool Deletion { get; set; }
    }

我们还有一个这样的方法

  class Program
    {
        static void Main(string[] args)
        {
            FindAs<Staff>(x => x.Code == "张三" && x.Name.Contains("张"));
        }

        public static List<T> FindAs<T>(Expression<Func<T, bool>> func)
        {
            //将func转换成对应数据库的查询条件，然后执行查询
            return null;//将结果返回
        }
    }

我们希望通过 FindAs<Staff>(x => x.Age <50 && x.Name.Contains(“张”)); 就能查询出Staff表中Age<50并且Name包含有“张”字的人的信息。而生成的SQL语句应该是select * from staff where Age<50 and Name like ‘%张%’。现在我们就来分析下这个func

从上面的图我们可以看到当前的Expression是一个lambda表达式，我们点开它的body看看。

我们可以看到body里分为左边和右边，还有NodeType。和我们的lambda对比下看看’x => x.Code ==”张三” && x.Name.Contains(“张”)’是不是找到点灵感了？我们再继续把左边和右边拆开看看。

可以看到我们需要的信息都有了，看来转换成SQL已经不是什么难事了，动手开搞了。

 class Program
    {
        static void Main(string[] args)
        {
            FindAs<Staff>(x => x.Code == "张三" && x.Name.Contains("张"));
            FindAs<Staff>(x => x.Age <= 12 && x.Name.Contains("张"));
            Console.ReadKey();
        }

        public static List<T> FindAs<T>(Expression<Func<T, bool>> func)
        {
            BinaryExpression Binary = func.Body as BinaryExpression;
            string left = ResovleFunc(Binary.Left);
            string right = ResovleLinqToObject(Binary.Right);
            string oper = GetOperator(Binary.NodeType);
            string sql = string.Format("select * from {0} where {1}", typeof(T).Name, left + oper + right);
            Console.WriteLine(sql);
            return null;//将结果返回
        }

        //解析一般的条件，例如x=>x.name==xxxx   x.age==xxx
        public static string ResovleFunc(Expression express)
        {
            var inner = express as BinaryExpression;
            string Name = (inner.Left as MemberExpression).Member.Name;
            object Value = (inner.Right as ConstantExpression).Value;
            var Operator = GetOperator(inner.NodeType);
            string Result = string.Format("({0} {1} '{2}')", Name, Operator, Value);
            return Result;
        }

        //解析linq to object这类扩展方法
        public static string ResovleLinqToObject(Expression expression)
        {
            var MethodCall = expression as MethodCallExpression;
            var MethodName = MethodCall.Method.Name;
            if (MethodName == "Contains")
            {
                object Temp_Vale = (MethodCall.Arguments[0] as ConstantExpression).Value;
                string Value = string.Format("%{0}%", Temp_Vale);
                string Name = (MethodCall.Object as MemberExpression).Member.Name;
                string Result = string.Format("{0} like '{1}'", Name, Value);
                return Result;
            }
            return null;
        }

        public static string GetOperator(ExpressionType expressiontype)
        {
            switch (expressiontype)
            {
                case ExpressionType.And:
                    return "and";
                case ExpressionType.AndAlso:
                    return "and";
                case ExpressionType.Or:
                    return "or";
                case ExpressionType.OrElse:
                    return "or";
                case ExpressionType.Equal:
                    return "=";
                case ExpressionType.NotEqual:
                    return "<>";
                case ExpressionType.LessThan:
                    return "<";
                case ExpressionType.LessThanOrEqual:
                    return "<=";
                case ExpressionType.GreaterThan:
                    return ">";
                case ExpressionType.GreaterThanOrEqual:
                    return ">=";
                default:
                    throw new Exception(string.Format("不支持{0}此种运算符查找！" + expressiontype));
            }
        }

    }

已经初步的达到了我们的目的了，但是我们的查询条件不可能固定是2个，有可能是N个，这时左边和右边又要继续再分下去，直到无法再分(想到递归了吧？)。而且我们还需要将查询条件参数化。而且我们的条件删除时也会用到。所以我们应该把它独立出来。传入一个lambda，生成sql where部分的语句，生成sqlparameter[]。这才是关键。。于是我们来构建一个解析Expresstion的类。。下面我就直接给出我自己写的实现代码了。。

 public class ResolveExpress
    {
        public Dictionary<string, object> Argument;
        public string SqlWhere;
        public SqlParameter[] Paras;

        /// <summary>
        /// 解析lamdba，生成Sql查询条件
        /// </summary>
        /// <param name="expression"></param>
        /// <returns></returns>
        public void ResolveExpression(Expression expression)
        {
            this.Argument = new Dictionary<string, object>();
            this.SqlWhere = Resolve(expression);
            this.Paras = Argument.Select(x => new SqlParameter(x.Key, x.Value)).ToArray();
        }

        private string Resolve(Expression expression)
        {
            if (expression is LambdaExpression)
            {
                LambdaExpression lambda = expression as LambdaExpression;
                expression = lambda.Body;
                return Resolve(expression);
            }
            if (expression is BinaryExpression)
            {
                BinaryExpression binary = expression as BinaryExpression;
                if (binary.Left is MemberExpression && binary.Right is ConstantExpression)//解析x=>x.Name=="123" x.Age==123这类
                    return ResolveFunc(binary.Left, binary.Right, binary.NodeType);
                if (binary.Left is MethodCallExpression && binary.Right is ConstantExpression)//解析x=>x.Name.Contains("xxx")==false这类的
                {
                    object value = (binary.Right as ConstantExpression).Value;
                    return ResolveLinqToObject(binary.Left, value, binary.NodeType);
                }
                if (binary.Left is MemberExpression && binary.Right is MemberExpression)//解析x=>x.Date==DateTime.Now这种
                {
                    LambdaExpression lambda = Expression.Lambda(binary.Right);
                    Delegate fn = lambda.Compile();
                    ConstantExpression value = Expression.Constant(fn.DynamicInvoke(null), binary.Right.Type);
                    return ResolveFunc(binary.Left, value, binary.NodeType);
                }
            }
            if (expression is UnaryExpression)
            {
                UnaryExpression unary = expression as UnaryExpression;
                if (unary.Operand is MethodCallExpression)//解析!x=>x.Name.Contains("xxx")或!array.Contains(x.Name)这类
                    return ResolveLinqToObject(unary.Operand, false);
                if (unary.Operand is MemberExpression && unary.NodeType == ExpressionType.Not)//解析x=>!x.isDeletion这样的 
                {
                    ConstantExpression constant = Expression.Constant(false);
                    return ResolveFunc(unary.Operand, constant, ExpressionType.Equal);
                }
            }
            if (expression is MemberExpression && expression.NodeType == ExpressionType.MemberAccess)//解析x=>x.isDeletion这样的 
            {
                MemberExpression member = expression as MemberExpression;
                ConstantExpression constant = Expression.Constant(true);
                return ResolveFunc(member, constant, ExpressionType.Equal);
            }
            if (expression is MethodCallExpression)//x=>x.Name.Contains("xxx")或array.Contains(x.Name)这类
            {
                MethodCallExpression methodcall = expression as MethodCallExpression;
                return ResolveLinqToObject(methodcall, true);
            }
            var body = expression as BinaryExpression;
            if (body == null)
                throw new Exception("无法解析" + expression);
            var Operator = GetOperator(body.NodeType);
            var Left = Resolve(body.Left);
            var Right = Resolve(body.Right);
            string Result = string.Format("({0} {1} {2})", Left, Operator, Right);
            return Result;
        }

        /// <summary>
        /// 根据条件生成对应的sql查询操作符
        /// </summary>
        /// <param name="expressiontype"></param>
        /// <returns></returns>
        private string GetOperator(ExpressionType expressiontype)
        {
            switch (expressiontype)
            {
                case ExpressionType.And:
                    return "and";
                case ExpressionType.AndAlso:
                    return "and";
                case ExpressionType.Or:
                    return "or";
                case ExpressionType.OrElse:
                    return "or";
                case ExpressionType.Equal:
                    return "=";
                case ExpressionType.NotEqual:
                    return "<>";
                case ExpressionType.LessThan:
                    return "<";
                case ExpressionType.LessThanOrEqual:
                    return "<=";
                case ExpressionType.GreaterThan:
                    return ">";
                case ExpressionType.GreaterThanOrEqual:
                    return ">=";
                default:
                    throw new Exception(string.Format("不支持{0}此种运算符查找！" + expressiontype));
            }
        }


        private string ResolveFunc(Expression left, Expression right, ExpressionType expressiontype)
        {
            var Name = (left as MemberExpression).Member.Name;
            var Value = (right as ConstantExpression).Value;
            var Operator = GetOperator(expressiontype);
            string CompName = SetArgument(Name, Value.ToString());
            string Result = string.Format("({0} {1} {2})", Name, Operator, CompName);
            return Result;
        }

        private string ResolveLinqToObject(Expression expression, object value, ExpressionType? expressiontype = null)
        {
            var MethodCall = expression as MethodCallExpression;
            var MethodName = MethodCall.Method.Name;
            switch (MethodName)//这里其实还可以改成反射调用，不用写switch
            {
                case "Contains":
                    if (MethodCall.Object != null)
                        return Like(MethodCall);
                    return In(MethodCall, value);
                case "Count":
                    return Len(MethodCall, value, expressiontype.Value);
                case "LongCount":
                    return Len(MethodCall, value, expressiontype.Value);
                default:
                    throw new Exception(string.Format("不支持{0}方法的查找！", MethodName));
            }
        }

        private string SetArgument(string name, string value)
        {
            name = "@" + name;
            string temp = name;
            while (Argument.ContainsKey(temp))
            {
                int code = Guid.NewGuid().GetHashCode();
                if (code < 0)
                    code *= -1;
                temp = name + code;
            }
            Argument[temp] = value;
            return temp;
        }

        private string In(MethodCallExpression expression, object isTrue)
        {
            var Argument1 = (expression.Arguments[0] as MemberExpression).Expression as ConstantExpression;
            var Argument2 = expression.Arguments[1] as MemberExpression;
            var Field_Array = Argument1.Value.GetType().GetFields().First();
            object[] Array = Field_Array.GetValue(Argument1.Value) as object[];
            List<string> SetInPara = new List<string>();
            for (int i = 0; i < Array.Length; i++)
            {
                string Name_para = "InParameter" + i;
                string Value = Array[i].ToString();
                string Key = SetArgument(Name_para, Value);
                SetInPara.Add(Key);
            }
            string Name = Argument2.Member.Name;
            string Operator = Convert.ToBoolean(isTrue) ? "in" : " not in";
            string CompName = string.Join(",", SetInPara);
            string Result = string.Format("{0} {1} ({2})", Name, Operator, CompName);
            return Result;
        }

        private string Like(MethodCallExpression expression)
        {
            object Temp_Vale = (expression.Arguments[0] as ConstantExpression).Value;
            string Value = string.Format("%{0}%", Temp_Vale);
            string Name = (expression.Object as MemberExpression).Member.Name;
            string CompName = SetArgument(Name, Value);
            string Result = string.Format("{0} like {1}", Name, CompName);
            return Result;
        }

        private string Len(MethodCallExpression expression, object value, ExpressionType expressiontype)
        {
            object Name = (expression.Arguments[0] as MemberExpression).Member.Name;
            string Operator = GetOperator(expressiontype);
            string CompName = SetArgument(Name.ToString(), value.ToString());
            string Result = string.Format("len({0}){1}{2}", Name, Operator, CompName);
            return Result;
        }

    }

 static void Main(string[] args)
        {
            string[] Names = { "Andy", "Amy", "Mike" };
            Expression<Func<Staff, bool>> func = x => (!Names.Contains(x.Name) && (x.Name == "A" || x.Name.Count() > 5));
            ResolveExpress resolve = new ResolveExpress();
            resolve.ResolveExpression(func);
            Console.WriteLine(resolve.SqlWhere);
            foreach (var item in resolve.Paras)
            {
                Console.WriteLine(item.ParameterName + ":" + item.Value);
            }
            Console.ReadKey();
        }

结果：

这里有几个重要的东西要给大家讲下

string[] Names={“Andy”,”Amy”,”Mike”};

1.）x => Names.Contains(x.Name);

2.）x => Names.Contains(x.Name)==false;

3.）x => !Names.Contains(x.Name);

这3种在Expression中的表现都不一样

1的话会看成是一个静态方法（MethodCallExpression）

2的话会看成是一个2元运算（BinaryExpression）

3的话会看成是一个1元运算（UnaryExpression）

所以我们都要支持，处理都有所不同。

还有

x=>x.Birthday<DateTime.Now;

string name=”123″;

x=>x.Name==name;

和

x=>x.Name==”123″

的处理也不一样。大家可以在例子中细细的看看。

这样的构造使得我们切换数据库变得非常简单。因为我们程序中的查询都是基于lambda。换了数据库只要添加一个对应的lamdba转数据库查询条件的实现就可以了。写得够多了。至于数据层怎么封装，到了这一步它已经变得没什么难度了。希望大家能从文章中有所启发和帮助

下篇文章将结合解析Expression和IQueryable<T>来实现延迟加载

补充点东西

IEnumerable和IQueryable有什么不同？

为什么EF查询后返回的是IQueryable<T>而不是IEnumerable<T>。我们对着IQueryable<T>F12去看看。

啥都没，就继承了几个接口。鼠标移到IQueryable上。F12

IQueryable中有3个属性。

Type是类型。

Expresstion是表达式。

那IQueryProvider是什么？

再看看IQueryProvider接口的定义。

CreateQuery是创建查询条件

Execute是执行查询（通常在GetEnumerator()中调用）

当我们IQueryable<T>.Where(x=>x.xxx==”123″)时。其实Where方法内部应该是调用了IQueryable接口中的IQueryProvider属性的CreateQuery(Expresstion expresstion)方法，然后将方法的返回值又返回出来。

而参数（Expresstion ）呢？则是IQueryable<T>.Where(x=>x.xxx==”123″)2部分的Expresstion相并。所以IQueryable只是创建条件。所以51楼的朋友说得非常对。

那什么时候执行呢？因为我们的IQueryable<T>继承了IEnumabler<T>，所以我们必须实现GetEnumerator()。我们ToList或foreach时，其实就会调用GetEnumerator()。这时我们就调用Execute进行解析Expresstion，从而得到我们想要的结果。

总结就是IQueryable只是创建条件,当我们调用a.Where(x=>xxx)时，其实是将a与后面的条件相并，生成一个新的IQueryable。当我们foreach时就会调用GetEnumerator()。这时我们一般会调用IQueryProvider里的Execute去解析Expresstion并查询出我们想要的结果

我也知道这篇文章介绍的和我们所说的“ORM”相差很远，但是所谓的ORM最复杂的莫非查询部分了，而依照我这思路走下去，我觉得是可以自己完成一个的。。

我刚开始写博客第二天，没想到这文章反响这么大。我承认有点重复造轮子，也非常不成熟，但我还是想通过自己的思考去构造属于自己的东西。

不知道大家有没看过头文字D，里头有个组织叫东堂垫，他们里面的人是拆掉ABS的。因为他们会长说，你要先学会不使用ABS进行刹车才知道ABS的真谛

SQL运行内幕：从执行原理看调优的本质 - arthinking-itzhai - 博客园

2020-07-02mikel阅读(701)

来源： SQL运行内幕：从执行原理看调优的本质 – arthinking-itzhai – 博客园

相信大家看过无数的MySQL调优经验贴了，会告诉你各种调优手段，如：

避免 select *；
join字段走索引；
慎用in和not in，用exists取代in；
避免在where子句中对字段进行函数操作；
尽量避免更新聚集索引；
group by如果不需要排序，手动加上 order by null；
join选择小表作为驱动表；
order by字段尽量走索引…

其中有些手段也许跟随者MySQL版本的升级过时了。我们真的需要背这些调优手段吗？我觉得是没有必要的，在掌握MySQL存储架构和SQL执行原理的情况下，我们就很自然的明白，为什么要提议这么优化了，甚至能够发现别人提的不太合理的优化手段。

在洞悉MySQL底层架构：游走在缓冲与磁盘之间这篇文章中，我们已经介绍了MySQL的存储架构，详细对你在MySQL存储、索引、缓冲、IO相关的调优经验中有了一定的其实。

本文，我们重点讲解常用的SQL的执行原理，从执行原理，以及MySQL内部对SQL的优化机制，来分析SQL要如何调优，理解为什么要这样…那样…那样…调优。

如果没有特别说明，本文以MySQL5.7版本作为讲解和演示。

阅读完本文，您将了解到：

COUNT： MyISAM和InnoDB存储引擎处理count的区别是什么？
COUNT： count为何性能差？
COUNT： count有哪些书写方式，怎么count统计会快点？
ORDER BY： order by语句有哪些排序模式，以及每种排序模式的优缺点？
ORDER BY： order by语句会用到哪些排序算法，在什么场景下会选择哪种排序算法
ORDER BY： 如何查看和分析sql的order by优化手段(执行计划 + OPTIMIZER_TRACE日志)
ORDER BY： 如何优化order by语句的执行效率？(思想：减小行查询大小，尽量走索引，能够走覆盖索引最佳，可适当增加sort buffer内存大小)
JOIN： join走索引的情况下是如何执行的？
JOIN： join不走索引的情况下是如何执行的？
JOIN： MySQL对Index Nested-Loop Join做了什么优化？(MMR，BKA)
JOIN： BNL算法对缓存会产生什么影响？有什么优化策略？
JOIN： 有哪些常用的join语句？
JOIN： 针对join语句，有哪些优化手段？
UNION： union语句执行原理是怎样的？
UNION： union是如何去重的？
GROUP BY： group by完全走索引的情况下执行计划如何？
GROUP BY： 什么情况下group by会用到临时表？什么情况下会用到临时表+排序？
GROUP BY： 对group by有什么优化建议？
DISTINCT： distinct关键词执行原理是什么？
子查询： 有哪些常见的子查询使用方式？
子查询： 常见的子查询优化有哪些？
子查询： 真的要尽量使用关联查询取代子查询吗？
子查询：in 的效率真的这么慢吗？
子查询： MySQL 5.6之后对子查询做了哪些优化？(SEMIJOIN，Materializatioin，Exists优化策略)
子查询： Semijoin有哪些优化策略，其中Materializatioin策略有什么执行方式，为何要有这两种执行方式？
子查询： 除了in转Exists这种优化优化，MariaDB中的exists转in优化措施有什么作用？

1、count

存储引擎的区别

MyISAM引擎每张表中存放了一个meta信息，里面包含了row_count属性，内存和文件中各有一份，内存的count变量值通过读取文件中的count值来进行初始化。^[1]但是如果带有where条件，还是必须得进行表扫描
InnoDB引擎执行count()的时候，需要把数据一行行从引擎里面取出来进行统计。

下面我们介绍InnoDB中的count()。

count中的一致性视图

InnoDB中为何不像MyISAM那样维护一个row_count变量呢？

前面洞悉MySQL底层架构：游走在缓冲与磁盘之间一文我们了解到，InnoDB为了实现事务，是需要MVCC支持的。MVCC的关键是一致性视图。一个事务开启瞬间，所有活跃的事务(未提交)构成了一个视图数组，InnoDB就是通过这个视图数组来判断行数据是否需要undo到指定的版本。

如下图，假设执行count的时候，一致性视图得到当前事务能够取到的最大事务ID DATA_TRX_ID=1002，那么行记录中事务ID超过1002都都要通过undo log进行版本回退，最终才能得出最终哪些行记录是当前事务需要统计的：

row1是其他事务新插入的记录，当前事务不应该算进去。所以最终得出，当前事务应该统计row2，row3。

执行count会影响其他页面buffer pool的命中率吗？

我们知道buffer pool中的LRU算法是是经过改进的，默认情况下，旧子列表(old区)占3/8，count加载的页面一直往旧子列表中插入，在旧子列表中淘汰，不会晋升到新子列表中。所以不会影响其他页面buffer pool的命中率。

count(主键)

count(主键)执行流程如下：

执行器请求存储引擎获取数据；
为了保证扫描数据量更少，存储引擎找到最小的那颗索引树获取所有记录，返回记录的id给到server。返回记录之前会进行MVCC及其可见性的判断，只返回当前事务可见的数据；
server获取到记录之后，判断id如果不为空，则累加到结果记录中。

count(1)

count(1)与count(主键)执行流程基本一致，区别在于，针对查询出的每一条记录，不会取记录中的值，而是直接返回一个”1″用于统计累加。统计了所有的行。

count(字段)

与count(主键)类似，会筛选非空的字段进行统计。如果字段没有添加索引，那么会扫描聚集索引树，导致扫描的数据页会比较多，效率相对慢点。

count(*)

count(*)不会取记录的值，与count(1)类似。

执行效率对比：count(字段) < count(主键) < count(1)

2、order by

以下是我们本节作为演示例子的表，假设我们有如下表：

索引如下：

对应的idx_d索引结构如下(这里我们做了一些夸张的手法，让一个页数据变小，为了展现在索引树中的查找流程)：

2.1、如何跟踪执行优化

为了方便分析sql的执行流程，我们可以在当前session中开启 optimizer_trace:

SET optimizer_trace=’enabled=on’;

然后执行sql，执行完之后，就可以通过以下堆栈信息查看执行详情了：

SELECT * FROM information_schema.OPTIMIZER_TRACE\G;

以下是

select a, b, c, d from t20 force index(idx_abc) where a=3 order by d limit 100,2;

的执行结果，其中符合a=3的有8457条记录，针对order by重点关注以下属性：

"filesort_priority_queue_optimization": { // 是否启用优先级队列 "limit": 102, // 排序后需要取的行数，这里为 limit 100,2，也就是100+2=102 "rows_estimate": 24576, // 估计参与排序的行数 "row_size": 123, // 行大小 "memory_available": 32768, // 可用内存大小，即设置的sort buffer大小 "chosen": true // 是否启用优先级队列 }, ... "filesort_summary": { "rows": 103, // 排序过程中会持有的行数 "examined_rows": 8457, // 参与排序的行数，InnoDB层返回的行数 "number_of_tmp_files": 0, // 外部排序时，使用的临时文件数量 "sort_buffer_size": 13496, // 内存排序使用的内存大小 "sort_mode": "sort_key, additional_fields" // 排序模式 }

2.1.1、排序模式

其中 sort_mode有如下几种形式：

sort_key, rowid：表明排序缓冲区元组包含排序键值和原始表行的行id，排序后需要使用行id进行回表，这种算法也称为original filesort algorithm(回表排序算法)；
sort_key, additional_fields：表明排序缓冲区元组包含排序键值和查询所需要的列，排序后直接从缓冲区元组取数据，无需回表，这种算法也称为modified filesort algorithm(不回表排序)；
sort_key, packed_additional_fields：类似上一种形式，但是附加的列(如varchar类型)紧密地打包在一起，而不是使用固定长度的编码。

如何选择排序模式

选择哪种排序模式，与max_length_for_sort_data这个属性有关，这个属性默认值大小为1024字节：

如果查询列和排序列占用的大小超过这个值，那么会转而使用sort_key, rowid模式；
如果不超过，那么所有列都会放入sort buffer中，使用sort_key, additional_fields或者sort_key, packed_additional_fields模式；
如果查询的记录太多，那么会使用sort_key, packed_additional_fields对可变列进行压缩。

2.1.2、排序算法

基于参与排序的数据量的不同，可以选择不同的排序算法：

如果排序取的结果很小，小于内存，那么会使用优先级队列进行堆排序；
- 例如，以下只取了前面10条记录，会通过优先级队列进行排序：
- ```
select a, b, c, d from t20 force index(idx_abc) where a=3 order by d limit 10;
```
如果排序limit n, m，n太大了，也就是说需要取排序很后面的数据，那么会使用sort buffer进行快速排序：
- 如下，表中a=1的数据又三条，但是由于需要limit到很后面的记录，MySQL会对比优先级队列排序和快速排序的开销，选择一个比较合适的排序算法，这里最终放弃了优先级队列，转而使用sort buffer进行快速排序：
- ```
select a, b, c, d from t20 force index(idx_abc) where a=1 order by d limit 300,2;
```
如果参与排序的数据sort buffer装不下了，那么我们会一批一批的给sort buffer进行内存快速排序，结果放入排序临时文件，最终使对所有排好序的临时文件进行归并排序，得到最终的结果;
- 如下，a=3的记录超过了sort buffer，我们要查找的数据是排序后1000行起，sort buffer装不下1000行数据了，最终MySQL选择使用sort buffer进行分批快排，把最终结果进行归并排序：
- ```
select a, b, c, d from t20 force index(idx_abc) where a=3 order by d limit 1000,10;
```

2.2、order by走索引避免排序

执行如下sql：

select a, b, c, d from t20 force index(idx_d) where d like 't%' order by d limit 2;

我们看一下执行计划：

发现Extra列为：Using index condition，也就是这里只走了索引。

执行流程如下图所示：

通过idx_d索引进行range_scan查找，扫描到4条记录，然后order by继续走索引，已经排好序，直接取前面两条，然后去聚集索引查询完整记录，返回最终需要的字段作为查询结果。这个过程只需要借助索引。

如何查看和修改sort buffer大小？

我们看一下当前的sort buffer大小：

可以发现，这里默认配置了sort buffer大小为512k。

我们可以设置这个属性的大小：

SET GLOBAL sort_buffer_size = 32*1024;

或者

SET sort_buffer_size = 32*1024;

下面我们统一把sort buffer设置为32k

SET sort_buffer_size = 32*1024;

2.3、排序算法案例

2.3.1、使用优先级队列进行堆排序

如果排序取的结果很小，并且小于sort buffer，那么会使用优先级队列进行堆排序；

例如，以下只取了前面10条记录：

select a, b, c, d from t20 force index(idx_abc) where a=3 order by d limit 10;

a=3的总记录数：8520。查看执行计划：

发现这里where条件用到了索引，order by limit用到了排序。我们进一步看看执行的optimizer_trace日志：

"filesort_priority_queue_optimization": { "limit": 10, "rows_estimate": 27033, "row_size": 123, "memory_available": 32768, "chosen": true // 使用优先级队列进行排序 }, "filesort_execution": [ ], "filesort_summary": { "rows": 11, "examined_rows": 8520, "number_of_tmp_files": 0, "sort_buffer_size": 1448, "sort_mode": "sort_key, additional_fields" }

发现这里是用到了优先级队列进行排序。排序模式是：sort_key, additional_fields，即先回表查询完整记录，把排序需要查找的所有字段都放入sort buffer进行排序。

所以这个执行流程如下图所示：

通过where条件a=3扫描到8520条记录；
回表查找记录；
把8520条记录中需要的字段放入sort buffer中；
在sort buffer中进行堆排序；
在排序好的结果中取limit 10前10条，写入net buffer，准备发送给客户端。

2.3.2、内部快速排序

如果排序limit n, m，n太大了，也就是说需要取排序很后面的数据，那么会使用sort buffer进行快速排序。MySQL会对比优先级队列排序和归并排序的开销，选择一个比较合适的排序算法。

如何衡量究竟是使用优先级队列还是内存快速排序？
一般来说，快速排序算法效率高于堆排序，但是堆排序实现的优先级队列，无需排序完所有的元素，就可以得到order by limit的结果。
MySQL源码中声明了快速排序速度是堆排序的3倍，在实际排序的时候，会根据待排序数量大小进行切换算法。如果数据量太大的时候，会转而使用快速排序。

有如下SQL：

select a, b, c, d from t20 force index(idx_abc) where a=1 order by d limit 300,2;

我们把sort buffer设置为32k：

SET sort_buffer_size = 32*1024;

其中a=1的记录有3条。查看执行计划：

可以发现，这里where条件用到了索引，order by limit 用到了排序。我们进一步看看执行的optimizer_trace日志：

"filesort_priority_queue_optimization": { "limit": 302, "rows_estimate": 27033, "row_size": 123, "memory_available": 32768, "strip_additional_fields": { "row_size": 57, "sort_merge_cost": 33783, "priority_queue_cost": 61158, "chosen": false // 对比发现快速排序开销成本比优先级队列更低，这里不适用优先级队列 } }, "filesort_execution": [ ], "filesort_summary": { "rows": 3, "examined_rows": 3, "number_of_tmp_files": 0, "sort_buffer_size": 32720, "sort_mode": "<sort_key, packed_additional_fields>" }

可以发现这里最终放弃了优先级队列，转而使用sort buffer进行快速排序。

所以这个执行流程如下图所示：

通过where条件a=1扫描到3条记录；
回表查找记录；
把3条记录中需要的字段放入sort buffer中；
在sort buffer中进行快速排序；
在排序好的结果中取limit 300, 2第300、301条记录，写入net buffer，准备发送给客户端。

2.3.3、外部归并排序

当参与排序的数据太多，一次性放不进去sort buffer的时候，那么我们会一批一批的给sort buffer进行内存排序，结果放入排序临时文件，最终使对所有排好序的临时文件进行归并排序，得到最终的结果。

有如下sql：

select a, b, c, d from t20 force index(idx_abc) where a=3 order by d limit 1000,10;

其中a=3的记录有8520条。执行计划如下：

可以发现，这里where用到了索引，order by limit用到了排序。进一步查看执行的optimizer_trace日志：

"filesort_priority_queue_optimization": { "limit": 1010, "rows_estimate": 27033, "row_size": 123, "memory_available": 32768, "strip_additional_fields": { "row_size": 57, "chosen": false, "cause": "not_enough_space" // sort buffer空间不够，无法使用优先级队列进行排序了 } }, "filesort_execution": [ ], "filesort_summary": { "rows": 8520, "examined_rows": 8520, "number_of_tmp_files": 24, // 用到了24个外部文件进行排序 "sort_buffer_size": 32720, "sort_mode": "<sort_key, packed_additional_fields>" }

我们可以看到，由于limit 1000，要返回排序后1000行以后的记录，显然sort buffer已经不能支撑这么大的优先级队列了，所以转而使用sort buffer内存排序，而这里需要在sort buffer中分批执行快速排序，得到多个排序好的外部临时文件，最终执行归并排序。（外部临时文件的位置由tmpdir参数指定）

其流程如下图所示：

2.4、排序模式案例

2.4.1、SORT_KEY, ADDITIONAL_FIELDS模式

sort_key, additional_fields，排序缓冲区元组包含排序键值和查询所需要的列（先回表取需要的数据，存入排序缓冲区中），排序后直接从缓冲区元组取数据，无需再次回表。

上面 2.3.1、2.3.2节的例子都是这种排序模式，就不继续举例了。

2.4.2、<SORT_KEY, PACKED_ADDITIONAL_FIELDS>模式

sort_key, packed_additional_fields：类似上一种形式，但是附加的列(如varchar类型)紧密地打包在一起，而不是使用固定长度的编码。

上面2.3.3节的例子就是这种排序模式，由于参与排序的总记录大小太大了，因此需要对附加列进行紧密地打包操作，以节省内存。

2.4.3、<SORT_KEY, ROWID>模式

前面我们提到，选择哪种排序模式，与max_length_for_sort_data^[2]这个属性有关，max_length_for_sort_data规定了排序行的最大大小，这个属性默认值大小为1024字节：

也就是说如果查询列和排序列占用的大小小于这个值，这个时候会走sort_key, additional_fields或者sort_key, packed_additional_fields算法，否则，那么会转而使用sort_key, rowid模式。

现在我们特意把这个值设置小一点，模拟sort_key, rowid模式：

SET max_length_for_sort_data = 100;

这个时候执行sql：

select a, b, c, d from t20 force index(idx_abc) where a=3 order by d limit 10;

这个时候再查看sql执行的optimizer_trace日志：

"filesort_priority_queue_optimization": { "limit": 10, "rows_estimate": 27033, "row_size": 49, "memory_available": 32768, "chosen": true }, "filesort_execution": [ ], "filesort_summary": { "rows": 11, "examined_rows": 8520, "number_of_tmp_files": 0, "sort_buffer_size": 632, "sort_mode": "<sort_key, rowid>" }

可以发现这个时候切换到了sort_key, rowid模式，在这个模式下，执行流程如下：

where条件a=3扫描到8520条记录；
回表查找记录；
找到这8520条记录的id和d字段，放入sort buffer中进行堆排序；
排序完成后，取前面10条；
取这10条的id回表查询需要的a，b，c，d字段值；
依次返回结果给到客户端。

可以发现，正因为行记录太大了，所以sort buffer中只存了需要排序的字段和主键id，以时间换取空间，最终排序完成，再次从聚集索引中查找到所有需要的字段返回给客户端，很明显，这里多了一次回表操作的磁盘读，整体效率上是稍微低一点的。

2.5、order by优化总结

根据以上的介绍，我们可以总结出以下的order by语句的相关优化手段：

order by字段尽量使用固定长度的字段类型，因为排序字段不支持压缩；
order by字段如果需要用可变长度，应尽量控制长度，道理同上；
查询中尽量不用用select *，避免查询过多，导致order by的时候sort buffer内存不够导致外部排序，或者行大小超过了max_length_for_sort_data导致走了sort_key, rowid排序模式，使得产生了更多的磁盘读，影响性能；
尝试给排序字段和相关条件加上联合索引，能够用到覆盖索引最佳。

3、join

为了演示join，接下来我们需要用到这两个表：

CREATE TABLE `t30` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) NOT NULL, `b` int(11) NOT NULL, `c` int(11) NOT NULL, PRIMARY KEY (`id`), KEY idx_a(a) ) ENGINE=InnoDB CHARSET=utf8mb4; CREATE TABLE `t31` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) NOT NULL, `f` int(11) NOT NULL, `c` int(11) NOT NULL, PRIMARY KEY (`id`), KEY idx_a(a) ) ENGINE=InnoDB CHARSET=utf8mb4; insert into t30(a,b,c) values(1, 1, 1),(12,2,2),(3,3,3),(11, 12, 31),(15,1,32),(33,33,43),(5,13,14),(4,13,14),(16,13,14),(10,13,14); insert into t31(a,f,c) values(1, 1, 1),(21,2,2),(3,3,3),(12, 1, 1),(31,20,2),(4,10,3),(2,23,24),(22,23,24),(5,23,24),(20,23,24);

在MySQL官方文档中 8.8.2 EXPLAIN Output Format^[3] 提到：MySQL使用Nested-Loop Loin算法处理所有的关联查询。使用这种算法，意味着这种执行模式：

从第一个表中读取一行，然后在第二个表、第三个表…中找到匹配的行，以此类推；
处理完所有关联的表后，MySQL将输出选定的列，如果列不在当前关联的索引树中，那么会进行回表查找完整记录；
继续遍历，从表中取出下一行，重复以上步骤。

下面我们所讲到的都是Nested-Loop Join算法的不同实现。

多表join：不管多少个表join，都是用的Nested-Loop Join实现的。如果有第三个join的表，那么会把前两个表的join结果集作为循环基础数据，在执行一次Nested-Loop Join，到第三个表中匹配数据，更多多表同理。

3.1、join走索引（Index Nested-Loop Join）

3.1.1、INDEX NESTED-LOOP JOIN

我们执行以下sql：

select * from t30 straight_join t31 on t30.a=t31.a;

查看执行计划：

可以发现：

t30作为驱动表，t31作为被驱动表；
通过a字段关联，去t31表查找数据的时候用到了索引。

该sql语句的执行流程如下图：

首先遍历t30聚集索引；
针对每个t30的记录，找到a的值，去t31的idx_a索引中找是否存在记录；
如果存在则拿到t30对应索引记录的id回表查找完整记录；
分别取t30和t31的所有字段作为结果返回。

由于这个过程中用到了idx_a索引，所以这种算法也称为：Index Nested-Loop(索引嵌套循环join)。其伪代码结构如下：

// A 为t30聚集索引 // B 为t31聚集索引 // BIndex 为t31 idx_a索引 void indexNestedLoopJoin(){ List result; for(a in A) { for(bi in BIndex) { if (a satisfy condition bi) { output <a, b>; } } } }

假设t30记录数为m，t31记录数为n，每一次查找索引树的复杂度为log2(n)，所以以上场景，总的复杂度为：m + m*2*log2(n)。

也就是说驱动表越小，复杂度越低，越能提高搜索效率。

3.1.2、INDEX NESTED-LOOP JOIN的优化

我们可以发现，以上流程，每次从驱动表取一条数据，然后去被驱动表关联取数，表现为磁盘的随记读，效率是比较低低，有没有优化的方法呢？

这个就得从MySQL的MRR（Multi-Range Read）^[4]优化机制说起了。

3.1.2.1、Multi-Range Read优化

我们执行以下代码，强制开启MMR功能：

set optimizer_switch="mrr_cost_based=off"

然后执行以下SQL，其中a是索引：

select * from t30 force index(idx_a) where a<=12 limit 10;

可以得到如下执行计划：

可以发现，Extra列提示用到了MRR优化。

这里为了演示走索引的场景，所以加了force index关键词。

正常不加force index的情况下，MySQL优化器会检查到这里即使走了索引还是需要回表查询，并且表中的数据量不多，那干脆就直接扫描全表，不走索引，效率更加高了。

如果没有MRR优化，那么流程是这样的：

在idx_a索引中找到a<10的记录；
取前面10条，拿着id去回表查找完整记录，这里回表查询是随机读，效率较差；
取到的结果通过net buffer返回给客户端。

使用了MRR优化之后，这个执行流程是这样的：

在idx_abc索引中找到a<10的记录；
取10条，把id放入read rnd buffer;
read rnd buffer中的id排序；
排序之后回表查询完整记录，id越多，排好序之后越有可能产生连续的id，去磁盘顺序读；
查询结果写入net buffer返回给客户端；

3.1.2.2、Batched Key Access

与Multi-Range Read的优化思路类似，MySQL也是通过把随机读改为顺序读，让Index Nested-Loop Join提升查询效率，这个算法称为Batched Key Access(BKA)^[5]算法。

我们知道，默认情况下，是扫描驱动表，一行一行的去被驱动表匹配记录。这样就无法触发MRR优化了，为了能够触发MRR，于是BKA算法登场了。

在BKA算法中，驱动表通过使用join buffer批量在被驱动表的辅助索引中关联匹配数据，得到一批结果，一次性传递个数据库引擎的MRR接口，从而可以利用到MRR对磁盘读的优化。

为了启用这个算法，我们执行以下命令（BKA依赖于MRR）：

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

我们再次执行以下关联查询sql：

select * from t30 straight_join t31 on t30.a=t31.a;

我们可以得到如下的执行计划：

可以发现，这里用到了：Using join buffer(Batched Key Access)。

执行流程如下：

把驱动表的数据批量放入join buffer中；
在join buffer中批与被驱动表的辅助索引匹配结果，得到一个结果集；
把上一步的结果集批量提交给引擎的MRR接口；
MRR接口处理同上一节，主要进行了磁盘顺序读的优化；
组合输出最终结果，可以看到，这里的结果与没有开启BKA优化的顺序有所不同，这里使用了t31被驱动表的id排序作为输出顺序，因为最后一步对被驱动表t31读取进行MRR优化的时候做了排序。

如果join条件没走索引，又会是什么情况呢，接下来我们尝试执行下对应的sql。

3.2、join不走索引（Block Nested-Loop Join）

3.2.1、BLOCK NESTED-LOOP JOIN (BNL)

我们执行以下sql：

select * from t30 straight_join t31 on t30.c=t31.c;

查看执行计划：

可以发现：

t30作为驱动表，t31作为被驱动表；
通过c字段关联，去t31表查找数据的时候没有用到索引；
join的过程中用到了join buffer，这里提示用到了Block Nested Loop Join；

该语句的执行流程如下图：

t30驱动表中的数据分批(分块)存入join buffer，如果一次可以全部存入，则这里会一次性存入；
t31被驱动表中扫描记录，依次取出与join buffer中的记录对比(内存中对比，快)，判断是否满足c相等的条件；
满足条件的记录合并结果输出到net buffer中，最终传输给客户端。

然后清空join buffer，存入下一批t30的数据，重复以上流程。

显然，每批数据都需要扫描一遍被驱动表，批次越多，扫描越多，但是内存判断总次数是不变的。所以总批次越小，越高效。所以，跟上一个算法一样，驱动表越小，复杂度越低，越能提高搜索效率。

3.2.2、BNL问题

在洞悉MySQL底层架构：游走在缓冲与磁盘之间一文中，我们介绍了MySQL Buffer Pool的LRU算法，如下：

默认情况下，同一个数据页，在一秒钟之后再次访问，那么就会晋升到新子列表(young区)。

恰巧，如果我们用到了BNL算法，那么分批执行的话，就会重复扫描被驱动表去匹配每一个批次了。

考虑以下两种会影响buffer pool的场景：

如果这个时候join扫描了一个很大的冷表，那么在join这段期间，会持续的往旧子列表(old区)写数据页，淘汰队尾的数据页，这会影响其他业务数据页晋升到新子列表，因为很可能在一秒内，其他业务数据就从旧子列表中被淘汰掉了；
而如果这个时候BNL算法把驱动表分为了多个批次，每个批次扫描匹配被驱动表，都超过1秒钟，那么这个时候，被驱动表的数据页就会被晋升到新子列表，这个时候也会把其他业务的数据页提前从新子列表中淘汰掉。

3.2.3、BNL问题解决方案

3.2.3.1、调大 join_buffer_size

针对以上这种场景，为了避免影响buffer pool，最直接的办法就是增加join_buffer_size的值，以减少对被驱动表的扫描次数。

3.2.3.2、把BNL转换为BKA

我们可以通过把join的条件加上索引，从而避免了BNL算法，转而使用BKA算法，这样也可以加快记录的匹配速度，以及从磁盘读取被驱动表记录的速度。

3.2.3.3、通过添加临时表

有时候，被驱动表很大，但是关联查询又很少使用，直接给关联字段加索引太浪费空间了，这个时候就可以通过把被驱动表的数据放入临时表，在零时表中添加索引的方式，以达成3.2.3.2的优化效果。

3.2.3.4、使用hash join

什么是hash join呢，简单来说就是这样的一种模型：

把驱动表满足条件的数据取出来，放入一个hash结构中，然后把被驱动表满足条件的数据取出来，一行一行的去hash结构中寻找匹配的数据，依次找到满足条件的所有记录。

一般情况下，MySQL的join实现都是以上介绍的各种nested-loop算法的实现，但是从MySQL 8.0.18^[6]开始，我们可以使用hash join来实现表连续查询了。感兴趣可以进一步阅读这篇文章进行了解：[Hash join in MySQL 8 | MySQL Server Blog](https://mysqlserverteam.com/hash-join-in-mysql-8/#:~:text=MySQL only supports inner hash,more often than it does.)

3.3、各种join

我们在平时工作中，会遇到各种各样的join语句，主要有如下：

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL OUTER JOIN

LEFT JOIN EXCLUDING INNER JOIN

RIGHT JOIN EXCLUDING INNER JOIN

OUTER JOIN EXCLUDING INNER JOIN

更详细的介绍，可以参考：

3.3、join使用总结

join优化的目标是尽可能减少join中Nested-Loop的循环次数，所以请让小表做驱动表；
关联字段尽量走索引，这样就可以用到Index Nested-Loop Join了；
如果有order by，请使用驱动表的字段作为order by，否则会使用 using temporary；
如果不可避免要用到BNL算法，为了减少被驱动表多次扫描导致的对Buffer Pool利用率的影响，那么可以尝试把 join_buffer_size调大；
为了进一步加快BNL算法的执行效率，我们可以给关联条件加上索引，转换为BKA算法；如果加索引成本较高，那么可以通过临时表添加索引来实现；
如果您使用的是MySQL 8.0.18，可以尝试使用hash join，如果是较低版本，也可以自己在程序中实现一个hash join。

4、union

通过使用union可以把两个查询结果合并起来，注意：

union all不会去除重复的行，union则会去除重复读的行。

4.1、union all

执行下面sql：

(select id from t30 order by id desc limit 10) union all (select c from t31 order by id desc limit 10)

该sql执行计划如下图：

执行流程如下：

从t30表查询出结果，直接写出到net buffer，传回给客户端；
从331表查询出结果，直接写出到net buffer，传回给客户端。

4.2、union

执行下面sql：

(select id from t30 order by id desc limit 10) union (select c from t31 order by id desc limit 10)

该sql执行计划如下图：

执行流程如下：

从t30查询出记录，写入到临时表；
从t30查询出记录，写入临时表，在临时表中通过唯一索引去重；
把临时表的数据通过net buffer返回给客户端。

5、group by

5.1、完全走索引

我们给t30加一个索引：

alter table t30 add index idx_c(c);

执行以下group bysql：

select c, count(*) from t30 group by c;

执行计划如下：

发现这里只用到了索引，原因是idx_c索引本身就是按照c排序好的，那么直接顺序扫描idx_c索引，可以直接统计到每一个c值有多少条记录，无需做其他的统计了。

5.2、临时表

现在我们把刚刚的idx_c索引给删掉，执行以下sql：

select c, count(*) from t30 group by c order by null;

为了避免排序，所以我们这里添加了 order by null，表示不排序。

执行计划如下：

可以发现，这里用到了内存临时表。其执行流程如下：

扫描t30聚集索引；
建立一个临时表，以字段c为主键，依次把扫描t30的记录通过临时表的字段c进行累加；
把最后累加得到的临时表返回给客户端。

5.3、临时表 + 排序

如果我们把上一步的order by null去掉，默认情况下，group by的结果是会通过c字段排序的。我们看看其执行计划：

可以发现，这里除了用到临时表，还用到了排序。

我们进一步看看其执行的OPTIMIZER_TRACE日志：

"steps": [ { "creating_tmp_table": { "tmp_table_info": { "table": "intermediate_tmp_table", // 创建中间临时表 "row_length": 13, "key_length": 4, "unique_constraint": false, "location": "memory (heap)", "row_limit_estimate": 1290555 } } }, { "filesort_information": [ { "direction": "asc", "table": "intermediate_tmp_table", "field": "c" } ], "filesort_priority_queue_optimization": { "usable": false, "cause": "not applicable (no LIMIT)" // 由于没有 limit，不采用优先级队列排序 }, "filesort_execution": [ ], "filesort_summary": { "rows": 7, "examined_rows": 7, "number_of_tmp_files": 0, "sort_buffer_size": 344, "sort_mode": "<sort_key, rowid>" // rowid排序模式 } } ]

通过日志也可以发现，这里用到了中间临时表，由于没有limit限制条数，这里没有用到优先级队列排序，这里的排序模式为sort_key, rowid。其执行流程如下：

扫描t30聚集索引；
建立一个临时表，以字段c为主键，依次把扫描t30的记录通过临时表的字段c进行累加；
把得到的临时表放入sort buffer进行排序，这里通过rowid进行排序；
通过排序好的rowid回临时表查找需要的字段，返回给客户端。

临时表是存放在磁盘还是内存？

tmp_table_size 参数用于设置内存临时表的大小，如果临时表超过这个大小，那么会转为磁盘临时表：

可以通过以下sql设置当前session中的内存临时表大小：SET tmp_table_size = 102400;

5.5、直接排序

查看官方文档的 SELECT Statement^[9]，可以发现SELECT后面可以使用许多修饰符来影响SQL的执行效果：

SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr [, select_expr] ... [into_option] [FROM table_references [PARTITION partition_list]] [WHERE where_condition] [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_condition] [ORDER BY {col_name | expr | position} [ASC | DESC], ...] [LIMIT {[offset,] row_count | row_count OFFSET offset}] [PROCEDURE procedure_name(argument_list)] [into_option] [FOR UPDATE | LOCK IN SHARE MODE] into_option: { INTO OUTFILE 'file_name' [CHARACTER SET charset_name] export_options | INTO DUMPFILE 'file_name' | INTO var_name [, var_name] ... }

这里我们重点关注下这两个：

SQL_BIG_RESULT：可以在包含group by 和distinct的SQL中使用，提醒优化器查询数据量很大，这个时候MySQL会直接选用磁盘临时表取代内存临时表，避免执行过程中发现内存不足才转为磁盘临时表。这个时候更倾向于使用排序取代二维临时表统计结果。后面我们会演示这样的案例；
SQL_SMALL_RESULT：可以在包含group by 和distinct的SQL中使用，提醒优化器数据量很小，提醒优化器直接选用内存临时表，这样会通过临时表统计，而不是排序。

当然，在平时工作中，不是特定的调优场景，以上两个修饰符还是比较少用到的。

接下来我们就通过例子来说明下使用了SQL_BIG_RESULT修饰符的SQL执行流程。

有如下SQL：

select SQL_BIG_RESULT c, count(*) from t30 group by c;

执行计划如下：

可以发现，这里只用到了排序，没有用到索引或者临时表。这里用到了SQL_BIG_RESULT修饰符，告诉优化器group by的数据量很大，直接选用磁盘临时表，但磁盘临时表存储效率不高，最终优化器使用数组排序的方式来完成这个查询。（当然，这个例子实际的结果集并不大，只是作为演示用）

其执行结果如下：

扫描t30表，逐行的把c字段放入sort buffer；
在sort buffer中对c字段进行排序，得到一个排序好的c数组；
遍历这个排序好的c数组，统计结果并输出。

5.4、group by 优化建议

尽量让group by走索引，能最大程度的提高效率；
如果group by结果不需要排序，那么可以加上group by null，避免进行排序；
如果group by的数据量很大，可以使用SQL_BIG_RESULT修饰符，提醒优化器应该使用排序算法得到group的结果。

6、distinct^[10]

在大多数情况下，DISTINCT可以考虑为GROUP BY的一个特殊案例，如下两个SQL是等效的：

select distinct a, b, c from t30; select a, b, c from t30 group by a, b, c order by null;

这两个SQL的执行计划如下：

由于这种等效性，适用于Group by的查询优化也适用于DISTINCT。

区别：distinct是在group by之后的每组中取出一条记录，distinct分组之后不进行排序。

6.1、Extra中的distinct

在一个关联查询中，如果您只是查询驱动表的列，并且在驱动表的列中声明了distinct关键字，那么优化器会进行优化，在被驱动表中查找到匹配的第一行时，将停止继续扫描。如下SQL：

explain select distinct t30.a from t30, t31 where t30.c=t30.c;

执行计划如下，可以发现Extra列中有一个distinct，该标识即标识用到了这种优化^[10:1]：

7、子查询

首先，我们来明确几个概念：

子查询：可以是嵌套在另一个查询(select insert update delete)内，子查询也可以是嵌套在另一个子查询里面。

MySQL子查询称为内部查询，而包含子查询的查询称为外部查询。子查询可以在使用表达式的任何地方使用。

接下来我们使用以下表格来演示各种子查询：

create table class ( id bigint not null auto_increment, class_num varchar(10) comment '课程编号', class_name varchar(100) comment '课程名称', pass_score integer comment '课程及格分数', primary key (id) ) comment '课程'; create table student_class ( id bigint not null auto_increment, student_name varchar(100) comment '学生姓名', class_num varchar(10) comment '课程编号', score integer comment '课程得分', primary key (id) ) comment '学生选修课程信息'; insert into class(class_num, class_name, pass_score) values ('C001','语文', 60),('C002','数学', 70),('C003', '英文', 60),('C004', '体育', 80),('C005', '音乐', 60),('C006', '美术', 70); insert into student_class(student_name, class_num, score) values('James', 'C001', 80),('Talor', 'C005', 75),('Kate', 'C002', 65),('David', 'C006', 82),('Ann', 'C004', 88),('Jan', 'C003', 70),('James', 'C002', 97), ('Kate', 'C005', 90), ('Jan', 'C005', 86), ('Talor', 'C006', 92);

子查询的用法比较多，我们先来列举下有哪些子查询的使用方法。

7.1、子查询的使用方法

7.1.1、WHERE中的子查询

7.1.1.1、比较运算符

可以使用比较运算法，例如=，>，<将子查询返回的单个值与where子句表达式进行比较，如

查找学生选择的编号最大的课程信息：

SELECT class.* FROM class WHERE class.class_num = ( SELECT MAX(class_num) FROM student_class );

7.1.1.2、in和not in

如果子查询返回多个值，则可以在WHERE子句中使用其他运算符，例如IN或NOT IN运算符。如

查找学生都选择了哪些课程：

SELECT class.* FROM class WHERE class.class_num IN ( SELECT DISTINCT class_num FROM student_class );

7.1.2、FROM子查询

在FROM子句中使用子查询时，从子查询返回的结果集将用作临时表。该表称为派生表或实例化子查询。如查找最热门和最冷门的课程分别有多少人选择：

SELECT max(count), min(count) FROM (SELECT class_num, count(1) as count FROM student_class group by class_num) as t1;

7.1.3、关联子查询

前面的示例中，您注意到子查询是独立的。这意味着您可以将子查询作为独立查询执行。

与独立子查询不同，关联子查询是使用外部查询中的数据的子查询。换句话说，相关子查询取决于外部查询。对于外部查询中的每一行，对关联子查询进行一次评估。

下面是比较运算符中的一个关联子查询。

查找每门课程超过平均分的学生课程记录：

SELECT t1.* FROM student_class t1 WHERE t1.score > ( SELECT AVG(score) FROM student_class t2 WHERE t1.class_num = t2.class_num);

关联子查询中，针对每一个外部记录，都需要执行一次子查询，因为每一条外部记录的class_num可能都不一样。

7.1.3.1、EXISTS和NOT EXISTS

当子查询与EXISTS或NOT EXISTS运算符一起使用时，子查询将返回布尔值TRUE或FALSE。

查找所有学生总分大于100分的课程：

select * from class t1 where exists( select sum(score) as total_score from student_class t2 where t2.class_num=t1.class_num group by t2.class_num having total_score > 100 )

7.2、子查询的优化

上面我们演示了子查询的各种用法，接下来，我们来讲一下子查询的优化^[11]。

子查询主要由以下三种优化手段：

Semijoin，半连接转换，把子查询sql自动转换为semijion；
Materialization，子查询物化；
EXISTS策略，in转exists；

其中Semijoin只能用于IN，= ANY，或者EXISTS的子查询中，不能用于NOT IN，<> ALL，或者NOT EXISTS的子查询中。

下面我们做一下详细的介绍。

真的要尽量使用关联查询取代子查询吗？

在《高性能MySQL》^[12]一书中，提到：优化子查询最重要的建议就是尽可能使用关联查询代替，但是，如果使用的是MySQL 5.6或者更新版本或者MariaDB，那么就可以直接忽略这个建议了。因为这些版本对子查询做了不少的优化，后面我们会重点介绍这些优化。

in的效率真的这么慢吗？

在MySQL5.6之后是做了不少优化的，下面我们就逐个来介绍。

7.2.1、SEMIJOIN

Semijoin^[13]，半连接，所谓半连接，指的是一张表在另一张表栈道匹配的记录之后，返回第一张表的记录。即使右边找到了几条匹配的记录，也最终返回左边的一条。

所以，半连接非常适用于查找两个表之间是否存在匹配的记录，而不关注匹配了多少条记录这种场景。

半连接通常用于IN或者EXISTS语句的优化。

7.2.1.1、优化场景

上面我们讲到：接非常适用于查找两个表之间是否存在匹配的记录，而不关注匹配了多少条记录这种场景。

in关联子查询

这种场景，如果使用in来实现，可能会是这样：

SELECT class_num, class_name FROM class WHERE class_num IN (SELECT class_num FROM student_class where condition);

在这里，优化器可以识别出IN子句要求子查询仅从student_class表返回唯一的class_num。在这种情况下，查询会自动优化为使用半联接。

如果使用exists来实现，可能会是这样：

SELECT class_num, class_name FROM class WHERE EXISTS (SELECT * FROM student_class WHERE class.class_num = student_class.class_num);

优化案例

统计有学生分数不及格的课程：

SELECT t1.class_num, t1.class_name FROM class t1 WHERE t1.class_num IN (SELECT t2.class_num FROM student_class t2 where t2.score < t1.pass_score);

我们可以通过执行以下脚本，查看sql做了什么优化：

explain extended SELECT t1.class_num, t1.class_name FROM class t1 WHERE t1.class_num IN (SELECT t2.class_num FROM student_class t2 where t2.score < t1.pass_score); show warnings\G;

得到如下执行执行计划，和SQL重写结果：

从这个SQL重写结果中，可以看出，最终子查询变为了semi join语句：

/* select#1 */ select `test`.`t1`.`class_num` AS `class_num`,`test`.`t1`.`class_name` AS `class_name` from `test`.`class` `t1` semi join (`test`.`student_class` `t2`) where ((`test`.`t2`.`class_num` = `test`.`t1`.`class_num`) and (`test`.`t2`.`score` < `test`.`t1`.`pass_score`))

而执行计划中，我们看Extra列：

Using where; FirstMatch(t1); Using join buffer (Block Nested Loop)

Using join buffer这项是在join关联查询的时候会用到，前面讲join语句的时候已经介绍过了，现在我们重点看一下FirstMatch(t1)这个优化项。

FirstMatch(t1)是Semijoin优化策略中的一种。下面我们详细介绍下Semijoin有哪些优化策略。

7.2.1.2、Semijoin优化策略

MySQL支持5中Semijoin优化策略，下面逐一介绍。

7.2.1.2.1、FirstMatch

在内部表寻找与外部表匹配的记录，一旦找到第一条，则停止继续匹配。

案例 – 统计有学生分数不及格的课程：

SELECT t1.class_num, t1.class_name FROM class t1 WHERE t1.class_num IN (SELECT t2.class_num FROM student_class t2 where t2.score < t1.pass_score);

执行计划：

执行流程，图比较大，请大家放大观看：

扫描class表，把class表分批放入join buffer中，分批处理；
在批次中依次取出每一条记录，在student_class表中扫描查找符合条件的记录，如果找到，则立刻返回，并从该条匹配的class记录取出查询字段返回；
依次继续扫描遍历。

您也可以去MariaDB官网，查看官方的FirstMatch Strategy^[14]解释。

7.2.1.2.2、Duplicate Weedout

将Semijoin作为一个常规的inner join，然后通过使用一个临时表去重。

具体演示案例，参考MariaDB官网：DuplicateWeedout Strategy^[15]，以下是官网例子的图示：

可以看到，灰色区域为临时表，通过临时表唯一索引进行去重。

7.2.1.2.3、LooseScan

把内部表的数据基于索引进行分组，取每组第一条数据进行匹配。

具体演示案例，参考MariaDB官网：LooseScan Strategy^[16]，以下是官网例子的图示：

7.2.1.4、Materialization^[17]

如果子查询是独立的(非关联子查询)，则优化器可以选择将独立子查询产生的结果存储到一张物化临时表中。

为了触发这个优化，我们需要往表里面添加多点数据，好让优化器认为这个优化是有价值的。

我们执行以下SQL：

select * from class t1 where t1.class_num in(select t2.class_num from student_class t2 where t2.score > 80) and t1.class_num like 'C%';

执行流程如下：

执行子查询：通过where条件从student_class 表中找出符合条件的记录，把所有记录放入物化临时表；
通过where条件从class表中找出符合条件的记录，与物化临时表进行join操作。

物化表的唯一索引

MySQL会报物化子查询所有查询字段组成一个唯一索引，用于去重。如上面图示，灰色连线的两条记录冲突去重了。

join操作可以从两个方向执行：

从物化表关联class表，也就是说，扫描物化表，去与class表记录进行匹配，这种我们称为Materialize-scan；
从class表关联物化表，也就是，扫描class表，去物化表中查找匹配记录，这种我们称为Materialize-lookup，这个时候，我们用到了物化表的唯一索引进行查找，效率会很快。

下面我们介绍下这两种执行方式。

Materialize-lookup

还是以上面的sql为例：

select * from class t1 where t1.class_num in(select t2.class_num from student_class t2 where t2.score > 80) and t1.class_num like 'C%';

执行计划如下：

可以发现：

t2表的select_type为MATERIALIZED，这意味着id=2这个查询结果将存储在物化临时表中。并把该查询的所有字段作为临时表的唯一索引，防止插入重复记录；
id=1的查询接收一个subquery2的表名，这个表正式我们从id=2的查询得到的物化表。
id=1的查询首先扫描t1表，依次拿到t1表的每一条记录，去subquery2执行eq_ref，这里用到了auto_key，得到匹配的记录。

也就是说，优化器选择了对t1(class)表进行全表扫描，然后去物化表进行所以等值查找，最终得到结果。

执行模型如下图所示：

原则：小表驱动大表，关联字段被驱动表添加索引

如果子查询查出来的物化表很小，而外部表很大，并且关联字段是外部表的索引字段，那么优化器会选择扫描物化表去关联外部表，也就是Materialize-scan，下面演示这个场景。

Materialize-scan

现在我们尝试给class表添加class_num唯一索引：

alter table class add unique uk_class_num(class_num);

并且在class中插入更多的数据。然后执行同样的sql，得到以下执行计划：

可以发现，这个时候id=1的查询是选择了subquery2，也就是物化表进行扫描，扫描结果逐行去t1表(class)进行eq_ref匹配，匹配过程中用到了t1表的索引。

这里的执行流程正好与上面的相反，选择了从class表关联物化表。

现在，我问大家：Materialization策略什么时候会选择从外部表关联内部表？相信大家心里应该有答案了。

执行模型如下：

原则：小表驱动大表，关联字段被驱动表添加索引

现在留给大家另一个问题：以上例子中，这两种Materialization的开销分别是多少(从行读和行写的角度统计)

答案：

Materialize-lookup：40次读student_class表，40次写物化临时表，42次读外部表，40次lookup检索物化临时表；

Materialize-scan：15次读student_class表，15次写物化临时表，15次扫描物化临时表，执行15次class表索引查询。

7.2.2、MATERIALIZATION

优化器使用Materialization(物化)来实现更加有效的子查询处理。物化针对非关联子查询进行优化。

物化通过把子查询结果存储为临时表（通常在内存中）来加快查询的执行速度。MySQL在第一次获取子查询结果时，会将结果物化为临时表。随后如果再次需要子查询的结果，则直接从临时表中读取。

优化器可以使用哈希索引为临时表建立索引，以使查找更加高效，并且通过索引来消除重复项，让表保持更小。

子查询物化的临时表在可能的情况下存储在内存中，如果表太大，则会退回到磁盘上进行存储。

为何要使用物化优化

如果未开启物化优化，那么优化器有时会将非关联子查询重写为关联子查询。

可以通过以下命令查询优化开关(Switchable Optimizations^[18])状态：

SELECT @@optimizer_switch\G;

也就是说，如下的in独立子查询语句：

SELECT * FROM t1 WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);

会重写为exists关联子查询语句：

SELECT * FROM t1 WHERE EXISTS (SELECT t2.b FROM t2 WHERE where_condition AND t1.a=t2.b);

开启了物化开关之后，独立子查询避免了这样的重写，使得子查询只会查询一次，而不是重写为exists语句导致外部每一行记录都会执行一次子查询，严重降低了效率。

7.2.3、EXISTS策略

考虑以下的子查询：

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

MySQL“从外到内”来评估查询。也就是说，它首先获取外部表达式outer_expr的值，然后运行子查询并获取其产生的结果集用于比较。

7.2.3.1、condition push down 条件下推

如果我们可以把outer_expr下推到子查询中进行条件判断，如下：

EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr=inner_expr)

这样就能够减少子查询的行数了。相比于直接用IN来说，这样就可以加快SQL的执行效率了。

而涉及到NULL值的处理，相对就比较复杂，由于篇幅所限，这里作为延伸学习，感兴趣的朋友可以进一步阅读：

8.2.2.3 Optimizing Subqueries with the EXISTS Strategy^[19]

延伸：
除了让关联的in子查询转为exists进行优化之外。在MariaDB 10.0.2版本中，引入了另一种相反的优化措施：可以让exists子查询转换为非关联in子查询，这样就可以用上非关联资产性的物化优化策略了。

详细可以阅读：EXISTS-to-IN Optimization^[20]

7.2.4、总结

总结一下子查询的优化方式：

首先优先使用Semijoin来进行优化，消除子查询，通常选用FirstMatch策略来做表连接；
如果不可以使用Semijoin进行优化，并且当前子查询是非关联子查询，则会物化子查询，避免多次查询，同时这一步的优化会遵循选用小表作为驱动表的原则，尽量走索引字段关联，分为两种执行方式：Materialize-lookup，Materialization-scan。通常会选用哈希索引为物化临时表提高检索效率；
如果子查询不能物化，那就只能考虑Exists优化策略了，通过condition push down把条件下推到exists子查询中，减少子查询的结果集，从而达到优化的目的。

8、limit offset, rows

limit的用法：

limit [offset], [rows]

其中 offset表示偏移量，rows表示需要返回的行数。

offset limit 表中的剩余数据 _||_ __||__ __||__ | | | | | RRRRRR RRRRRRRR RRR... |______| || 结果集

8.1、执行原理

MySQL进行表扫描，读取到第 offset + rows条数据之后，丢弃前面offset条记录，返回剩余的rows条记录。

比如以下sql：

select * from t30 order by id limit 10000, 10;

这样总共会扫描10010条。

8.2、优化手段

如果查询的offset很大，避免直接使用offset，而是通过id到聚集索引中检索查找。

利用自增索引，如：

select * from t30 where id > 10000 limit 10;

当然，这也是会有问题的，如果id中间产生了非连续的记录，这样定位就不准确了。写到这里，篇幅有点长了，最后这个问题留给大家思考，感兴趣的朋友可以进一步思考探讨与延伸。

这篇文章的内容就差不多介绍到这里了，能够阅读到这里的朋友真的是很有耐心，为你点个赞。

本文为arthinking基于相关技术资料和官方文档撰写而成，确保内容的准确性，如果你发现了有何错漏之处，烦请高抬贵手帮忙指正，万分感激。

大家可以关注我的博客：itzhai.com 获取更多文章，我将持续更新后端相关技术，涉及JVM、Java基础、架构设计、网络编程、数据结构、数据库、算法、并发编程、分布式系统等相关内容。

如果您觉得读完本文有所收获的话，可以关注我的账号，或者点赞吧，码字不易，您的支持就是我写作的最大动力，再次感谢！

关注我的公众号，及时获取最新的文章。

开发笔记 第144页

一、前言

二、Lambda扩展

三、总结

一、前言

二、分页扩展

三、总结

一、前言

二、表达式树的遍历

2.1、ExpressionVisitor类的跟踪

2.2、修改表达式树

三、总结

一、前言

二、表达树理解

2.1、表达式的创建

2.2、表达式的解析

2.3、编译表达式树

三、总结

走进 LINQ 的世界

序

目录

LINQ 简介

一、介绍 LINQ 查询

1.1 查询操作的三个部分

1.2 数据源

1.3 查询

1.4 查询执行

1.延迟执行

2.强制立即执行

二、基本 LINQ 查询操作

2.1 获取数据源：from

2.2 筛选：where

2.3 排序：orderby

2.4 分组：group

2.5 联接：join

2.6 选择（投影）：select

三、使用 LINQ 进行数据转换

3.1 将多个输入联接到一个输出序列

3.2 选择各个源元素的子集

3.3 将内存中的对象转换为 XML

3.4 对源元素执行操作

四、LINQ 查询操作的类型关系

4.1 不转换源数据的查询

4.2 转换源数据的查询

4.3 让编译器推断类型信息

五、LINQ 中的查询语法和方法语法

5.1 标准查询运算符扩展方法

5.2 Lambda 表达式

5.3 查询的组合性

传送门

1、count

count中的一致性视图

count(主键)

count(1)

count(字段)

count(*)

2、order by

2.1、如何跟踪执行优化

2.1.1、排序模式

如何选择排序模式

2.1.2、排序算法

2.2、order by走索引避免排序

2.3、排序算法案例

2.3.1、使用优先级队列进行堆排序

2.3.2、内部快速排序

2.3.3、外部归并排序

2.4、排序模式案例

2.4.1、SORT_KEY, ADDITIONAL_FIELDS模式

2.4.2、<SORT_KEY, PACKED_ADDITIONAL_FIELDS>模式

2.4.3、<SORT_KEY, ROWID>模式

2.5、order by优化总结

3、join

3.1、join走索引（Index Nested-Loop Join）

3.1.1、INDEX NESTED-LOOP JOIN

3.1.2、INDEX NESTED-LOOP JOIN的优化

3.1.2.1、Multi-Range Read优化

3.1.2.2、Batched Key Access

3.2、join不走索引（Block Nested-Loop Join）

3.2.1、BLOCK NESTED-LOOP JOIN (BNL)

3.2.2、BNL问题

开发笔记第144页

　　1.延迟执行

　　2.强制立即执行

6、distinct^[10]

7.2.1.4、Materialization^[17]