2020年9月24日 By mikel 分类: ASP.NET MVC, ASP.NET, C#, Debug

来源: .NET MVC 简单实现GZIP – 无记 – 博客园

GZIP的好处大家都知道,不过一般系统来说页面不会太大,很多人也没注意过这玩意儿。前段时间做一个系统用了一个国人开发的JQuery富客户端框架DWZ(个人感觉这个框架还是蛮不错的),类似EXT的AJAX框架,框架的JS加上我自己的真不小。

 

 

打算用GZIP来做压缩,之前在IIS6上做过,这个项目用MVC做,发现在.NET MVC下有更简单好用的办法来解决:写一个ActionFilter来实现GZIP,优点大家用了就知道,呵呵

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
public class CompressAttribute : ActionFilterAttribute
{
    public override void OnActionExecuting(ActionExecutingContext filterContext)
    {
        var acceptEncoding = filterContext.HttpContext.Request.Headers["Accept-Encoding"];
        if (!string.IsNullOrEmpty(acceptEncoding))
        {
            acceptEncoding = acceptEncoding.ToLower();
            var response = filterContext.HttpContext.Response;
            if (acceptEncoding.Contains("gzip"))
            {
                response.AppendHeader("Content-encoding", "gzip");
                response.Filter = new GZipStream(response.Filter, CompressionMode.Compress);
            }
            else if (acceptEncoding.Contains("deflate"))
            {
                response.AppendHeader("Content-encoding", "deflate");
                response.Filter = new DeflateStream(response.Filter, CompressionMode.Compress);
            }
        }
    }
}

用法:

1
2
3
4
5
6
7
[Longin]
[Compress]
public ActionResult Index()
{
    
        return View();
}

只要在打算用GZIP压缩的Action上写上[Compress]就OK了,是不是很简单,呵呵~

2020年9月24日 By mikel 分类: C#, Debug, 数据库

来源: MVC页面加载速度优化小记 – g_cheng – 博客园

 前言:

最近做一个地图展示页面,业务初期没什么问题,运行一阵后报错:

Error during serialization or deserialization using the JSON JavaScriptSerializer. 
The length of the string exceeds the value set on the maxJsonLength property.

Google 了一下,原来是由于业务数据的增多, ajax 后台返回时报错,参考网上的方法,在返回时设置一下 MaxJsonLength

            var jsonResult = Json(jsonString);
            jsonResult.MaxJsonLength = int.MaxValue;
            return jsonResult;

报错的问题是解决了,可还有一个问题就是页面加载时间过长。为了地图展示的效果,不能进行数据分页。

期间尝试了很多方法,记录如下:

 列表与地图同步:

页面的布局是左边显示数据列表,右边显示 GoogleMap 。刚开始拼列表的 html 和地图的初始化、加载是在同一个方法里面执行的,这样的效果就是列表和地图同时显示,由于地图的初始化、加载比较耗时,就想让列表先显示,地图初始化完后再显示。也就是列表显示和地图初始化同步执行,改后代码:

复制代码
                    if(pageData.length>0){
                        SetListHtml(1);
                        $("#loadGif").hide();
                        setTimeout(function () {SetMap();}, 0);
                    }
                    else{
                        $("#loadGif").hide();
                    }
复制代码

 

 存储过程和缓存:

列表与地图同步可以让用户的体验好一些,但是页面加载时间还是过长,于是就把后台取数据改成存储过程、把一些不是经常改变的数据做成缓存。在做缓存的过程中,由于不是经常改变的数据有时也会改变,因此缓存依赖要设成 SQLCacheDependency 。具体方法网上有很多。

复制代码
        public DataTable GetCacheGpsBoundary()
        {
            string cacheKey = "dtCustomerBoundary";
            DataTable dtCustomerBoundary = (DataTable)HttpRuntime.Cache.Get(cacheKey);
            if (dtCustomerBoundary == null)
            {
                log.Debug("dtCustomerBoundary从数据库中获取");
                dtCustomerBoundary = new DataTable();
                string sql = "";
                SqlCacheDependency sqlCacheDependency = null;
                string connectionString = ConfigurationManager.AppSettings[""];
                using (SqlConnection cn = new SqlConnection(connectionString))
                {
                    using (SqlCommand cmd = cn.CreateCommand())
                    {
                        cn.Open();
                        cmd.CommandText = sql;
                        sqlCacheDependency = new SqlCacheDependency(cmd);
                        //当有DML操作时,onChange事件会接收来自Sql Server通过sq_DispatcherProc存储过程发送给应用程序的消息。
                        using (SqlDataAdapter adapter = new SqlDataAdapter()) //查询数据
                        {
                            adapter.SelectCommand = cmd;
                            adapter.Fill(dtCustomerBoundary);
                        }
                    }
                }
                HttpContext.Current.Cache.Insert(cacheKey, dtCustomerBoundary, sqlCacheDependency);
            }
            else
            {
                log.Debug("dtCustomerBoundary从缓存中获取");
            }
            return dtCustomerBoundary;
        }
复制代码

 

 GZip压缩:

存储过程和缓存在本机测试的确加快了页面加载速度,可是发布到服务器后页面加载速度还是不理想。想分析具体原因在哪里,在网上找到一个工具:

HttpWatch Basic 9.2
Copyright: Copyright 2002 -2014 Simtec Limited
Version: 9.2.6

先看下图例颜色的意义:

我理解 Wait 就是服务器执行用时, Receive 就是传输用时。

没有加存储过程和缓存:

再来看下改成存储过程和加缓存后

最后是启用 GZip 后的图

通过 HttpWatch 我们可以看出压缩后数据的大小 853424 ,为压缩前 4309641 的 19.80% 、用时也由 8.623 缩短到 3.119 ,由于服务器当前资源、网络情况等每次时间会有所不同。

启用 GZip 压缩后,服务器和客户端 CPU 负担会加大,但对页面加载速度效果还是很明显的,刚开始通过 HttpWatch 看出是由于传输数据过大使页面加载时间过长时,就想到要用压缩,可在后台 ajax 方法中对返回的 Json 字符串 采用 GZipStream 压缩后,在前台 JavaScript 一直找不到好的方法去解压,尝试了很多加解压算法 Zlib、LZString 等等,加解压的时间都太长了,没什么效果。

看了 单程列车 博客上 .NET MVC 简单实现GZIP  后在后台添加一个 Filter 就可以实现服务端 gzip 压缩,客户端会自动解压,客户端 ajax 时可能要加一句

headers: { “Accept-Encoding” : “gzip” } ,其实我觉得 ajax 就是异步的去打开某一网页。

 总结:

分析、看清问题,找到问题真实原因,才有可能找到好的解决方法;

刚开始想在取数据这块优化,看 SQL 的执行计划看的一头雾水,今年要买些数据库优化方法的书补补;

JavaScript、HTTP、C#、MVC、SQL 等等掌握的是否系统、全面在综合运用时起着决定性的作用

解决一个问题的时候还是很高兴的。

 参考:

 http://stackoverflow.com/questions/5692836/maxjsonlength-exception-in-asp-net-mvc-during-javascriptserializer

http://www.cnblogs.com/zhaojingjing/archive/2011/01/20/1940357.html

http://www.cnblogs.com/willick/p/3331520.html

HTTP权威指南

2020年9月24日 By mikel 分类: C#, Debug, 数据库

来源: windows配置mongodb开放局域网访问_qq_42783610的博客-CSDN博客

重装电脑c盘后,mongodb局域网访问不了,于是去网上查找一下资料。

win10:

打开网络和internet设置

点开网络,选择专用网络

然后配置mongodb bin目录下的mongodb.cfg

然后安装mongoDB服务  (没有config局域网就无法访问)

mongod –config D:\Mongo\bin\mongod.cfg –logpath D:\Mongo\log\mongod.log
–logappend –dbpath D:\Mongo\data
–serviceName MongoDB  –install

回车执行完后执行下面命令启动mongodb服务

net start MongoDB

然后执行下面

mongo 局域网ip   例如mongo 10.11.124.78

出现下面情况代表成功

ps:

安装mongodb服务时:

若报错出现logpath 不对,就到日志文件夹下把mongod.log给删掉再执行

若报错TLS,则可以忽略

MongoDB启动服务时:

若报错误代码100,则到data目录下把mongod.lock和storage.bson文件删除

mongo 局域网ip 测试时:

若还不行.则关闭防火墙或增加防火墙入站规则开放mongodb 27017端口

2020年9月22日 By mikel 分类: C#, Debug, 数据库

来源: Find objects between two dates in MongoDB?

Use operator gteandgteandlt to find objects between two dates in MongoDB. To understand these operators, let us create a collection.

Creating a collection here:

>db.order.insert({"OrderId":1,"OrderAddrees":"US","OrderDateTime":ISODate("2019-02-19")};
WriteResult({ "nInserted" : 1 })

>db.order.insert({"OrderId":2,"OrderAddrees":"UK","OrderDateTime":ISODate("2019-02-26")};
WriteResult({ "nInserted" : 1 })

Display all documents from the collection with the help of find() method. The query is as follows:

> db.order.find().pretty();

The following is the output:

{
   "_id" : ObjectId("5c6c072068174aae23f5ef57"),
   "OrderId" : 1,
   "OrderAddrees" : "US",
   "OrderDateTime" : ISODate("2019-02-19T00:00:00Z")
}
{
   "_id" : ObjectId("5c6c073568174aae23f5ef58"),
   "OrderId" : 2,
   "OrderAddrees" : "UK",
   "OrderDateTime" : ISODate("2019-02-26T00:00:00Z")
}

Here is the query to find objects between two dates:

> db.order.find({"OrderDateTime":{ $gte:ISODate("2019-02-10"), $lt:ISODate("2019-02-21") }
}).pretty();

The following is the output:

{
   "_id" : ObjectId("5c6c072068174aae23f5ef57"),
   "OrderId" : 1,
   "OrderAddrees" : "US",
   "OrderDateTime" : ISODate("2019-02-19T00:00:00Z")
}
Find objects between two dates in MongoDB?已关闭评论
2020年9月22日 By mikel 分类: 开发笔记, C#, Debug, 数据库

调试要看拼接的filter最终生成的mongodb查询语句的代码如下:

        //调试输出mognodb的查询语句
        var documentSerializer = MongoDB.Bson.Serialization.BsonSerializer.SerializerRegistry.GetSerializer<ClientInfo>();
        var renderedFilter = query.filter.Render(documentSerializer, MongoDB.Bson.Serialization.BsonSerializer.SerializerRegistry).ToString();
        Console.WriteLine(renderedFilter);

动态拼接filter查询条件的,我是通过Lambda表达式的表达式树解析过程中将查询条件插入到表达式树解析的字典中,同时生成filter查询条件,然后到LambdaQuery类中拼接mongodb的filter实现的动态条件查询

    public Dictionary<String, FilterDefinition<T>> filters = new Dictionary<String, FilterDefinition<T>>();//动态条件字典
//表达式数普通条件拼接
    public string BinaryExpressionHandler(Expression left, Expression right, ExpressionType type)
    {
        StringBuilder sb = new StringBuilder();
        //sb.Append("(");
        string needParKey = "=,>,<,>=,<=,<>";
        string leftPar = RouteExpressionHandler(left);
        string typeStr = ExpressionTypeCast(type);
        var isRight = needParKey.IndexOf(typeStr) > -1;
        string rightPar = RouteExpressionHandler(right, isRight);


        string appendLeft = leftPar;

        sb.Append(appendLeft);//字段名称

        if (rightPar.ToUpper() == "NULL")
        {
            if (typeStr == "=")
                rightPar = " IS NULL ";
            else if (typeStr == "<>")
                rightPar = " IS NOT NULL ";
        }
        else
        {
            // sb.Append(")");
            sb.Append(typeStr);
            //sb.Append("(");
        }
        sb.Append(rightPar);
        Console.WriteLine("left:"+leftPar+",right:"+rightPar+",typeStr:"+typeStr);
        //sb.Append(")");
        var builder = Builders<T>.Filter;
        FilterDefinition<T> d;
        switch (type)
        {
            case ExpressionType.Equal:
                d = builder.Eq(leftPar, rightPar.Replace("'",""));
                filters.Add(typeStr.Trim() + ":"+Guid.NewGuid(), d);
                break;
            case ExpressionType.NotEqual:
                d = builder.Eq(leftPar, rightPar.Replace("'", ""));
                filters.Add(typeStr.Trim() + ":" + Guid.NewGuid(), d);
                break;
            case ExpressionType.AndAlso:
                filters.Add(typeStr.Trim() + ":" + Guid.NewGuid(), null);
                break;
            case ExpressionType.OrElse:
                filters.Add(typeStr.Trim() + ":" + Guid.NewGuid(), null);
                break;

        }
        return sb.ToString();
    }

表达式树自定义查询方法的字典filter条件查询

        if (MethodName == "in")
        {
            object Temp_Vale = (MethodCall.Arguments[1] as ConstantExpression).Value;
            string Value = string.Format("{0}", Temp_Vale);
            object Name = (MethodCall.Arguments[0] as MemberExpression).Member.Name;
            string Result = string.Format("{0} IN ({1})", Name, Value);
            //mongodb查询条件拼接
            var builder = Builders<T>.Filter;
            FilterDefinition<T> d;
            var values=Value.Split(',');
            d = builder.In(Name.ToString(), values);
            filters.Add("Call" + ":" + Guid.NewGuid(), d);

            return Result;
        }

LambdaQuery最终动态查询filter拼接的代码:

    internal string FormatExpression(Expression<Func<T, bool>> expression)
    {
        string condition;
        var visitor = new ExpressionVisitor<T>();
        if (expression == null)
            return "";
        condition = visitor.RouteExpressionHandler(expression.Body);
        Console.WriteLine(JsonConvert.SerializeObject(visitor.filter));
        //filter = visitor.filter;
        //字典拼接
        var builder = Builders<T>.Filter;
        FilterDefinition<T> leftfilter = null;
        FilterDefinition<T> rightfilter = null;
        foreach (String key in visitor.filters.Keys)
        {
            String k = key.Substring(0, key.IndexOf(":"));
            if (k == "=" || k == "Call")
            {
                if (leftfilter == null)
                {
                    leftfilter = visitor.filters[key];
                }
                else
                {
                    rightfilter = visitor.filters[key];
                }

            }

            if (visitor.filters[key] == null)
            {
                if (k.Equals("AND"))
                {
                    if (rightfilter == null)
                    {
                        if (filter.Equals(Builders<T>.Filter.Empty))
                        {
                            filter = leftfilter;
                        }
                        else
                        {
                            filter = filter & leftfilter;
                        }
                    }
                    else
                    {
                        if (filter.Equals(Builders<T>.Filter.Empty))
                        {
                            filter = leftfilter & rightfilter;
                        }
                        else
                        {
                            filter = filter & leftfilter & rightfilter;
                        }

                    }
                }
                if (k.Equals("OR"))
                {
                    if (rightfilter == null)
                    {
                        if (filter.Equals(Builders<T>.Filter.Empty))
                        {
                            filter = leftfilter;
                        }
                        else
                        {
                            filter = builder.Or(filter ,leftfilter);
                        }
                    }
                    else
                    {
                        if (filter.Equals(Builders<T>.Filter.Empty))
                        {
                            filter = builder.Or(leftfilter,rightfilter);
                        }
                        else
                        {
                            filter = builder.Or(filter , builder.Or(leftfilter,rightfilter));
                        }
                    }
                }
                leftfilter = null;
                rightfilter = null;
            }

            Console.WriteLine(key + ":" + JsonConvert.SerializeObject(visitor.filters[key]));
        }
        return condition;
    }
c#的mongodb动态查询filter拼接已关闭评论
2020年9月22日 By mikel 分类: C#, 数据库

来源: Mongodb Csharp Filter 构建时使用的语法 – C_ZQ – 博客园

版本1 与版本2 差别还是蛮大的,好容易找到一篇,留下使用

 

两个以上的条件联合查询,语句可参考下面的这个,使用 And 进行串接

var _filter = Builders<PostModel>.Filter.And(
Builders<PostModel>.Filter.Where(x => x.PostId == like.PostId),
Builders<PostModel>.Filter.Eq(“Comments.CommentId”, like.CommentId));

string name=”abc”;
var wheres = Builders<T>.Filter.Exists(“unique_id”);
// 逻辑或 | 逻辑与& 根据实际逻辑判断
if (!string.IsNullOrEmpty(name))
wheres = wheres & (Builders<T>.Filter.Regex(“name”, new BsonRegularExpression(new Regex(name, RegexOptions.IgnoreCase)))) | Builders<T>.Filter.Regex(“other.name”, new BsonRegularExpression(name));
Filter.All(“name”, “a”, “b”);//通过多个元素来匹配数组
Filter.And(Query.EQ(“name”, “a”), Query.EQ(“title”, “t”));//同时满足多个条件
Filter.EQ(“name”, “a”);//等于
Filter.Exists(“type”, true);//判断键值是否存在
Filter.GT(“value”, 2);//大于>
Filter.GTE(“value”, 3);//大于等于>=
Filter.In(“name”, “a”, “b”);//包括指定的所有值,可以指定不同类型的条件和值
Filter.LT(“value”, 9);//小于<
Filter.LTE(“value”, 8);//小于等于<=
Filter.Mod(“value”, 3, 1);//将查询值除以第一个给定值,若余数等于第二个给定值则返回该结果
Filter.NE(“name”, “c”);//不等于
Filter.Nor(Array);//不包括数组中的值
Filter.Not(“name”);//元素条件语句
Filter.NotIn(“name”, “a”, 2);//返回与数组中所有条件都不匹配的文档
Filter.Or(Query.EQ(“name”, “a”), Query.EQ(“title”, “t”));//满足其中一个条件
Filter.Size(“name”, 2);//给定键的长度
Filter.Type(“_id”, BsonType.ObjectId );//给定键的类型
Filter.Where(BsonJavaScript);//执行JavaScript
Filter.Matches(“Title”,str);//模糊查询 相当于SQL中like — str可包含正则表达式

Mongodb Csharp Filter 构建时使用的语法 – C_ZQ – 博客园已关闭评论
2020年9月21日 By mikel 分类: 数据库

来源: SQL查询语句的执行顺序解析 – 51CTO.COM

SQL语句执行顺序

SQL查询语句的执行顺序解析

结合上图,整理出如下伪SQL查询语句。

SQL查询语句的执行顺序解析

从这个顺序中我们可以发现,所有的查询语句都是从 FROM 开始执行的。在实际执行过程中,每个步骤都会为下一个步骤生成一个虚拟表,这个虚拟表将作为下一个执行步骤的输入。 接下来,我们详细的介绍下每个步骤的具体执行过程。

1 FROM 执行笛卡尔积

FROM 才是 SQL 语句执行的第一步,并非 SELECT 。对FROM子句中的前两个表执行笛卡尔积(交叉联接),生成虚拟表VT1,获取不同数据源的数据集。

FROM子句执行顺序为从后往前、从右到左,FROM 子句中写在最后的表(基础表 driving table)将被最先处理,即最后的表为驱动表,当FROM 子句中包含多个表的情况下,我们需要选择数据最少的表作为基础表。

2 ON 应用ON过滤器

对虚拟表VT1 应用ON筛选器,ON 中的逻辑表达式将应用到虚拟表 VT1中的各个行,筛选出满足ON 逻辑表达式的行,生成虚拟表 VT2 。

3 JOIN 添加外部行

如果指定了OUTER JOIN保留表中未找到匹配的行将作为外部行添加到虚拟表 VT2,生成虚拟表 VT3。保留表如下:

  • LEFT OUTER JOIN把左表记为保留表
  • RIGHT OUTER JOIN把右表记为保留表
  • FULL OUTER JOIN把左右表都作为保留表

在虚拟表 VT2表的基础上添加保留表中被过滤条件过滤掉的数据,非保留表中的数据被赋予NULL值,最后生成虚拟表 VT3

如果FROM子句包含两个以上的表,则对上一个联接生成的结果表和下一个表重复执行步骤1~3,直到处理完所有的表为止。

4 WHERE 应用WEHRE过滤器

对虚拟表 VT3应用WHERE筛选器。根据指定的条件对数据进行筛选,并把满足的数据插入虚拟表 VT4

  • 由于数据还没有分组,因此现在还不能在WHERE过滤器中使用聚合函数对分组统计的过滤。
  • 同时,由于还没有进行列的选取操作,因此在SELECT中使用列的别名也是不被允许的。

5 GROUP BY 分组

按GROUP BY子句中的列/列表将虚拟表 VT4中的行唯一的值组合成为一组,生成虚拟表VT5。如果应用了GROUP BY,那么后面的所有步骤都只能得到的虚拟表VT5的列或者是聚合函数(count、sum、avg等)。原因在于最终的结果集中只为每个组包含一行。

同时,从这一步开始,后面的语句中都可以使用SELECT中的别名。

AGG_FUNC 计算聚合函数

计算 max 等聚合函数。SQL Aggregate 函数计算从列中取得的值,返回一个单一的值。常用的 Aggregate 函数包涵以下几种:

  • AVG:返回平均值
  • COUNT:返回行数
  • FIRST:返回第一个记录的值
  • LAST:返回最后一个记录的值
  • MAX: 返回最大值
  • MIN:返回最小值
  • SUM: 返回总和

7 WITH 应用ROLLUP或CUBE

对虚拟表 VT5应用ROLLUP或CUBE选项,生成虚拟表 VT6

CUBE 和 ROLLUP 区别如下:

  • CUBE 生成的结果数据集显示了所选列中值的所有组合的聚合。
  • ROLLUP 生成的结果数据集显示了所选列中值的某一层次结构的聚合。

8 HAVING 应用HAVING过滤器

对虚拟表VT6应用HAVING筛选器。根据指定的条件对数据进行筛选,并把满足的数据插入虚拟表VT7。

HAVING 语句在SQL中的主要作用与WHERE语句作用是相同的,但是HAVING是过滤聚合值,在 SQL 中增加 HAVING 子句原因就是,WHERE 关键字无法与聚合函数一起使用,HAVING子句主要和GROUP BY子句配合使用。

9 SELECT 选出指定列

将虚拟表 VT7中的在SELECT中出现的列筛选出来,并对字段进行处理,计算SELECT子句中的表达式,产生虚拟表 VT8

10 DISTINCT 行去重

将重复的行从虚拟表 VT8中移除,产生虚拟表 VT9。DISTINCT用来删除重复行,只保留唯一的。

11 ORDER BY 排列

将虚拟表 VT9中的行按ORDER BY 子句中的列/列表排序,生成游标 VC10 ,注意不是虚拟表。因此使用 ORDER BY 子句查询不能应用于表达式。同时,ORDER BY子句的执行顺序为从左到右排序,是非常消耗资源的。

12 LIMIT/OFFSET 指定返回行

从VC10的开始处选择指定数量行,生成虚拟表 VT11,并返回调用者。

实例

接下来,我们看一个实例,以下SQL查询语句是否存在问题?

SQL查询语句的执行顺序解析

首先,我们先看下如上SQL的执行顺序,如下:

  1. 首先执行 FROM 子句, 从学生成绩表中组装数据源的数据。
  2. 执行 WHERE 子句, 筛选学生成绩表中所有学生的数学成绩不为 NULL 的数据 。
  3. 执行 GROUP BY 子句, 把学生成绩表按 “班级” 字段进行分组。
  4. 计算 avg 聚合函数, 按找每个班级分组求出 数学平均成绩
  5. 执行 HAVING 子句, 筛选出班级 数学平均成绩大于 75 分的。
  6. 执行SELECT语句,返回数据,但别着急,还需要执行后面几个步骤。
  7. 执行 ORDER BY 子句, 把最后的结果按 “数学平均成绩” 进行排序。
  8. 执行LIMIT ,限制仅返回3条数据。结合ORDER BY 子句,即返回所有班级中数学平均成绩的前三的班级及其数学平均成绩。

思考一下,如果我们将上面语句改成,如下会怎样?

SQL查询语句的执行顺序解析

我们发现,若将 avg(数学成绩) > 75 放到WHERE子句中,此时GROUP BY语句还未执行,因此此时聚合值 avg(数学成绩) 还是未知的,因此会报错。

SQL查询语句的执行顺序解析 – 51CTO.COM已关闭评论
2020年9月21日 By mikel 分类: C#, 数据库

来源: 自己实现一个SQL解析引擎-阿里云开发者社区

自己实现一个SQL解析引擎

功能:将用户输入的SQL语句序列转换为一个可执行的操作序列,并返回查询的结果集。
SQL的解析引擎包括查询编译与查询优化和查询的运行,主要包括3个步骤:

  1. 查询分析:
  2. 制定逻辑查询计划(优化相关)
  3. 制定物理查询计划(优化相关)
  • 查询分析: 将SQL语句表示成某种有用的语法树.
  • 制定逻辑查询计划: 把语法树转换成一个关系代数表达式或者类似的结构,这个结构通常称作逻辑计划。
  • 制定物理查询计划:把逻辑计划转换成物理查询计划,要求指定操作执行的顺序,每一步使用的算法,操作之间的传递方式等。
    查询分析各模块主要函数间的调用关系:

    图1.SQL引擎间模块的调用关系

FLEX简介

flex是一个词法分析工具,其输入为后缀为.l的文件,输出为.c的文件. 示例是一个类似Unix的单词统计程序wc

%option noyywrap
%{
    int chars = 0;
    int words = 0;
    int lines = 0;
%}

%%

[_a-zA-Z][_a-zA-Z0-9]+ { words++; chars += strlen(yytext); }
\n { chars++ ; lines++; }
.  { chars++; }

%%

int main()
{
       yylex();
       printf("%8d %8d %8d\n",lines,words,chars);
    return 0;
}

 

.l文件通常分为3部分:

%{
    definition
%}

%%
    rules
%%
    code

 

definition部分为定义部分,包括引入头文件,变量声明,函数声明,注释等,这部分会被原样拷贝到输出的.c文件中。
rules部分定义词法规则,使用正则表达式定义词法,后面大括号内则是扫描到对应词法时的动作代码。
code部分为C语言的代码。yylex为flex的函数,使用yylex开始扫描。
%option 指定flex扫描时的一些特性。yywrap通常在多文件扫描时定义使用。常用的一些选项有
noyywrap 不使用yywrap函数
yylineno 使用行号
case-insensitive 正则表达式规则大小写无关

flex文件的编译

 flex  –o wc.c wc.l
    cc wc.c –o wc

 

Bison简介

Bison作为一个语法分析器,输入为一个.y的文件,输出为一个.h文件和一个.c文件。通常Bison需要使用Flex作为协同的词法分析器来获取记号流。Flex识别正则表达式来获取记号,Bison则分析这些记号基于逻辑规则进行组合
计算器的示例:calc.y

%{
#include <stdio.h>
%}

%token NUMBER
%token ADD SUB MUL DIV ABS
%token OP CP
%token EOL

%%

calclist:
    | calclist exp EOL {printf("=%d \n> ",$2);}
    | calclist EOL {printf("> ");}
    ;
exp: factor
    | exp ADD factor  {$$ = $1 + $3;}
    | exp SUB factor  {$$ = $1 - $3;}
    ;
factor:term
    | factor MUL term {$$ = $1 * $3;}
    | factor DIV term {$$ = $1 / $3;}
    ;
term:NUMBER
    | ABS term ABS { $$ = ($2 >= 0 ? $2 : -$2);}
    | OP exp CP    { $$ = $2;}
    ;
%%
int main(int argc,char *argv[])
{
    printf("> ");
    yyparse();

    return 0;
}
void yyerror(char *s)
{
    fprintf(stderr,"error:%s:\n",s);
}

Flex与Bison共享记号,值通过yylval在Flex与Bison间传递。对应的.l文件为

%option noyywrap
%{
#include "fb1-5.tab.h"
#include <string.h>
%}

%%
"+" { return ADD;}
"-" { return SUB;}
"*" { return MUL;}
"/" { return DIV;}
"|" { return ABS;}
"(" { return OP;}
")" { return CP;}
[0-9]+ { 
                 yylval = atoi(yytext);
                 return NUMBER;
           }

\n { return EOL; }
"//".*

[ \t] {}
"q" {exit(0);}
.   { yyerror("invalid char: %c\n;",*yytext); }
%%

 

Bision文件编译

  bison -d cacl.y
    flex cacl.l
    cc -o cacl cacl.tab.c lex.yy.c

 

通常,Bison默认是不可重入的,如果希望在yyparse结束后保留解析的语法树,可以采用两种方式,一种是增加一个全局变量,另一种则是设置一个额外参数,其中ParseResult可以是用户自己定义的结构体。
%parse-param {ParseResult *result}
在规则代码中可以引用该参数:

stmt_list: stmt ';'  { $$ = $1; result->result_tree = $$; }
| stmt_list stmt ';' { $$ = (($2 != NULL)? $2 : $1); result->result_tree = $$;}
stmt_list: stmt ';'  { $$ = $1; result->result_tree = $$; }
| stmt_list stmt ';' { $$ = (($2 != NULL)? $2 : $1); result->result_tree = $$;}

 

调用yyparse时则为:
ParseResult p;
yyparse(&p);

SQL解析引擎中的数据结构

语法树结构

在实现的时候可以把语法树和逻辑计划都看成是树结构和列表结构,而物理计划更像像是链式结构。树结构要注意区分叶子节点(也叫终止符节点)和非叶子节点(非终止符节点)。同时叶子节点和非叶子节点都可能有多种类型。

语法树的节点:包含两个部分,节点的类型的枚举值kind,表示节点值的联合体u,联合体中包含了各个节点所需的字段。

typedef struct node{
   NODEKIND kind;

   union{
         //...
           /* query node */
         struct{
             int         distinct_opt;
              struct node *limit; 
              struct node *select_list;
              struct node *tbl_list;
              struct node *where_clause;
              struct node *group_clause;
              struct node *having_clause;
              struct node *order_clause;
         } SELECT;
         /* delete node */
        struct{
            struct node *limit;
            struct node *table;
            struct node *where_clause;
            struct node *group_clause;
         } DELETE;
/* relation node */
          struct{
                char * db_name;
                char * tbl_name;
                char * alias_name;
          } TABLE;
        //其他结构体
   }u;
}NODE ;
NODEKIND枚举了所有可能出现的节点类型.其定义为

typedef enum NODEKIND{
    N_MIN,
    /* const node*/
    N_INT,    //int or long
    N_FLOAT,  //float
    N_STRING, //string
    N_BOOL,   //true or false or unknown
    N_NULL,   //null
    /* var node*/
    N_COLUMN, // colunm name
    //其他类型
    /*stmt node*/    
    N_SELECT,
    N_INSERT,
    N_REPLACE,
    N_DELETE,
    N_UPDATE,
    //其他类型
    N_MAX
} NODEKIND;

 

在语法树中,分析树的叶子节点为数字,字符串,属性等,其他为内部节点。因此有些数据库的实现中将语法树的节点定义为如下的ParseNode结构。

typedef struct _ParseNode
{
  ObItemType   type_;//节点的类型,如T_STRING,T_SELECT等

  /* 终止符节点,具有实际的值 */
  int64_t      value_;
  const char*  str_value_;

  /* 非终止符节点,拥有多个孩子 */
  int32_t      num_child_;//子节点的个数
  struct _ParseNode** children_;//子节点指针链

} ParseNode;

 

逻辑计划结构

逻辑计划的内部节点是算子,叶子节点是关系.

typedef struct plannode{

    PLANNODEKIND kind;

    union{
        /*stmt node*/
        struct {
            struct plannode *plan;
        }SELECT;

        /*op node*/
        struct {
            struct plannode *rel;
            struct plannode *filters; //list of filter
        }SCAN;
        struct {
            struct plannode *rel;
            NODE *expr_filter; //list of compare expr
        }FILTER;
        struct {
            struct plannode *rel;
            NODE *select_list;    
        }PROJECTION;
        struct {
            struct plannode *left;
            struct plannode *right;
        }JOIN;
        /*leaf node*/
        struct {
            NODE *table;
        }FILESCAN;
        //其他类型节点    
    }u;
}PLANNODE;

 

逻辑计划节点的类型PLANNODEKIND的枚举值如下:

typedef enum PLANNODEKIND{
    /*stmt node tags*/
    PLAN_SELECT,
    PLAN_INSERT,
    PLAN_DELETE,
    PLAN_UPDATE,
    PLAN_REPLACE,
    /*op node tags*/
    PLAN_FILESCAN, /* Relation     关系,叶子节点 */
    PLAN_SCAN,       
    PLAN_FILTER,   /* Selection  选择   */
    PLAN_PROJ,     /* Projection 投影*/
    PLAN_JOIN,     /* Join       连接 ,指等值连接*/
    PLAN_DIST,     /* Duplicate elimination( Distinct) 消除重复*/
    PLAN_GROUP,    /* Grouping   分组(包含了聚集)*/
    PLAN_SORT,     /* Sorting    排序*/
    PLAN_LIMIT,
    /*support node tags*/
    PLAN_LIST    
}PLANNODEKIND;

 

物理计划结构

物理逻辑计划中关系扫描运算符为叶子节点,其他运算符为内部节点。拥有3个迭代器函数open,close,get_next_row。其定义如下:

typedef int (*IntFun)(PhyOperator *);
typedef int (*RowFun)(Row &row,PhyOperator *);
struct phyoperator{
    PHYOPNODEKIND kind;

    IntFun open;
    IntFun close;
    RowFun get_next_row;//迭代函数

    union{
        struct {
            struct phyoperator *inner;
            struct phyoperator *outter;
            Row one_row;
        }NESTLOOPJOIN;
        struct {
            struct phyoperator *inner;
            struct phyoperator *outter;
        }HASHJOIN;
        struct {
            struct phyoperator *inner;
        }TABLESCAN;
        struct {
            struct phyoperator *inner;
            NODE * expr_filters;
        }INDEXSCAN;
        //其他类型的节点
    }u;
}PhyOperator;

 

物理查询计划的节点类型PHYOPNODEKIND枚举如下:

typedef enum PHYOPNODEKIND{
    /*stmt node tags*/
    PHY_SELECT,
    PHY_INSERT,
    PHY_DELETE,
    PHY_UPDATE,
    PHY_REPLACE,
    /*phyoperator node tags*/
    PHY_TABLESCAN,
    PHY_INDEXSCAN,
    PHY_FILESCAN,
    PHY_NESTLOOPJOIN,
    PHY_HASHJOIN,
    PHY_FILTER,
    PHY_SORT,
    PHY_DIST,
    PHY_GROUP,
    PHY_PROJECTION,
    PHY_LIMIT
}PHYOPNODEKIND;

 

节点内存池

可以看到分析树,逻辑计划树和物理查询树都是以指针为主的结构体,如果每次都动态从申请的话,会比较耗时。需要使用内存池的方式,一次性申请多个节点内存,供以后调用。下面是一种简单的方式,每次创建节点时都使用newnode函数即可。程序结束时再释放内存池即可。

static NODE *nodepool = NULL;
static int MAXNODE = 256;
static int nodeptr = 0;

NODE *newnode(NODEKIND kind)
{
    //首次使用时申请MAXNODE个节点
    if(nodepool == NULL){
        nodepool = (NODE *)malloc(sizeof(NODE)*MAXNODE);
        assert(nodepool);
    }

    assert(nodeptr <= MAXNODE);
    //当节点个数等于MAXNODE时realloc扩展为原来的两倍节点
    if (nodeptr == MAXNODE){
        MAXNODE *= 2;
        NODE *newpool = 
(NODE *)realloc(nodepool,sizeof(NODE)*MAXNODE) ; 
        assert(newpool);
        nodepool = newpool;
    }

    NODE *n = nodepool + nodeptr;
    n->kind = kind ;
    ++nodeptr;

    return n;
}

 

查询分析

查询分析需要对查询语句进行词法分析和语法分析,构建语法树。词法分析是指识别SQL语句中的有意义的逻辑单元,如关键字(SELECT,INSERT等),数字,函数名等。语法分析则是根据语法规则将识别出来的词组合成有意义的语句。 词法分析工具LEX,语法分析工具为Yacc,在GNU的开源软件中对应的是Flex和Bison,通常都是搭配使用。

词法和语法分析

SQL引擎的词法分析和语法分析采用Flex和Bison生成,parse_sql为生成语法树的入口,调用bison的yyparse完成。源文件可以这样表示

文件 意义
parse_node.h parse_node.cpp 定义语法树节点结构和方法,入口函数为parse_sql
print_node.cpp 打印节点信息
psql.y 定义语法结构,由Bison语法书写
psql.l 定义词法结构,由Flex语法书写

SQL查询语句语法规则

熟悉Bison和Flex的用法之后,我们就可以利用Flex获取记号,Bison设计SQL查询语法规则。一个SQL查询的语句序列由多个语句组成,以分号隔开,单条的语句又有DML,DDL,功能语句之分。

    stmt_list : stmt ‘;’
    | stmt_list stmt ‘;’
    ;
    stmt: ddl
    | dml    
    | unility
    | nothing
    ;
    dml: select_stmt   
    | insert_stmt   
    | delete_stmt   
    | update_stmt   
    | replace_stmt  
    ;

以DELETE 单表语法为例

DELETE  [IGNORE] [FIRST|LAST row_count] 
FROM tbl_name 
[WHERE where_definition]  
[ORDER BY ...]

 

用Bison可以表示为:

delete_stmt:DELETE opt_ignore opt_first FROM table_ident opt_where opt_groupby 
{
           $$ = delete_node(N_DELETE,$3,$5,$6,$7);
}  
;
opt_ignore:/*empty*/
            | IGNORE
;

opt_first: /* empty */{ $$ = NULL;}
| FIRST INTNUM { $$ = limit_node(N_LIMIT,0,$2);}
| LAST INTNUM { $$ = limit_node(N_LIMIT,1,$2);}
;

 

然后在把opt_where,opt_groupbytable_ident等一直递归下去,直到不能在细分为止。
SQL语句分为DDL语句和DML语句和utility语句,其中只有DML语句需要制定执行计划,其他的语句转入功能模块执行。

制定逻辑计划

执行顺序

语法树转为逻辑计划时各算子存在先后顺序。以select语句为例,执行的顺序为:
FROM > WHERE > GROUP BY> HAVING > SELECT > DISTINCT > UNION > ORDER BY > LIMIT
没有优化的逻辑计划应按照上述顺序逐步生成或者逆向生成。转为逻辑计划算子则对应为:
JOIN –> FILTER -> GROUP -> FILTER(HAVING) -> PROJECTION -> DIST -> UNION -> SORT -> LIMIT

逻辑计划的优化

逻辑计划的优化需要更细一步的粒度,将FILTER对应的表达式拆分成多个原子表达式。如WHERE t1.a = t2.a AND t2.b = '1990'可以拆分成两个表达式:
1)t1.a = t2.a
2)t2.b = '1990'
不考虑谓词LIKE,IN的情况下,原子表达式实际上就是一个比较关系表达式,其节点为列名,数字,字符串,可以将原子表达式定义为

struct CompExpr
{
    NODE * attr_or_value;
    NODE * attr_or_value;
    CompOpType kind;
};

 

CompOpType为“>”, ”<” ,”=”等各种比较操作符的枚举值。

如果表达式符合 attr comp value 或者 value comp attr,则可以将该原子表达式下推到对应的叶子节点之上,增加一个Filter。
如果是attr = value类型,且attr是关系的索引的话,则可以采用索引扫描IndexScan。
当计算三个或多个关系的并交时,先对最小的关系进行组合。

还有其他的优化方法可以进一步发掘。内存数据库与存储在磁盘上的数据库的代价估计不一样。根据处理查询时CPU和内存占用的代价,主要考虑以下一些因素:

  • 查询读取的记录数;
  • 结果是否排序(这可能会导致使用临时表);
  • 是否需要访问索引和原表。

制定物理计划

物理查询计划主要是完成一些算法选择的工作。如关系扫描运算符包括:
TableScan(R):按任意顺序读入所以存放在R中的元组。
SortScan(R,L):按顺序读入R的元组,并以列L的属性进行排列
IndexScan(R,C): 按照索引C读入R的元组。

根据不同的情况会选择不同的扫描方式。其他运算符包括投影运算Projection,选择运算Filter,连接运算包括嵌套连接运算NestLoopJoin,散列连接HashJoin,排序运算Sort等。
算法的一般策略包括基于排序的,基于散列的,或者基于索引的。

流水化操作与物化

由于查询的结果集可能会很大,超出缓冲区,同时为了能够提高查询的速度,各运算符都会支持流水化操作。流水化操作要求各运算符都有支持迭代操作,它们之间通过GetNext调用来节点执行的实际顺序。迭代器函数包括open,getnext,close3个函数。
NestLoopJoin的两个运算符参数为R,S,NestLoopJoin的迭代器函数如下:

void NestLoopJoin::Open()
{
    R.Open();
    S.Open();
    r =R.GetNext();
}
void NestLoopJoin::GetNext(tuple &t)
{
    Row r,s;
    S.GetNext(s);
    if(s.empty()){
        S.Close();
        R.GetNext(r);
        if(r.empty())
            return;
        S.Open();
        S.GetNext(s);
    }
    t = join(r,s)
}
void NestLoopJoin::Close()
{
        R.Close();
        S.Close();
}

 

如果TableScan,IndexScan,NestLoopJoin 3个运算符都支持迭代器函数。则图5中的连接NestLoopJoin(t1,t2’)可表示为:
phy = Projection(Filter(NestLoopJoin(TableScan(t1),IndexScan(t2’))));

执行物理计划时:

phy.Open();
    while(!tuple.empty()){
        phy.GetNext(tuple);
    }
    phy.Close();

 

这种方式下,物理计划一次返回一行,执行的顺序由运算符的函数调用序列来确定。程序只需要1个缓冲区就可以向用户返回结果集。
也有些情况需要等待所有结果返回才进行下一步运算的,比如Sort , Dist运算,需要将整个结果集排好序后才能返回,这种情况称作物化,物化操作通常是在open函数中完成的。

一个完整的例子

接下来以一个例子为例表示各部分的结构,SQL命令:
SELECT t1.a,t2.b FROM t1,t2 WHERE t1.a = t2.a AND t2.b = '1990';
其对应的分析树为:

图2. SQL例句对应的分析树

分析树的叶子节点为数字,字符串,属性等,其他为内部节点。
将图2的分析树转化为逻辑计划树,如图3所示。

图3. 图2分析树对应的逻辑计划

逻辑计划是关系代数的一种体现,关系代数拥有种基本运算符:投影 (π),选择 (σ),自然连接 (⋈),聚集运算(G)等算子。因此逻辑计划也拥有这些类型的节点。
逻辑计划的内部节点是算子,叶子节点是关系,子树是子表达式。各算子中最耗时的为连接运算,因此SQL查询优化的很大一部分工作是减小连接的大小。如图3对应的逻辑计划可优化为图4所示的逻辑计划。

图4. 图3优化后的逻辑计划

完成逻辑计划的优化后,在将逻辑计划转化为物理查询计划。图4的逻辑计划对应的物理查询计划如下:

图5. 图4对应的物理查询计划

物理查询计划针对逻辑计划中的每一个算子拥有对应的1个或多个运算符,生成物理查询计划是基于不同的策略选择合适的运算符进行运算。其中,关系扫描运算符为叶子节点,其他运算符为内部节点。

后记

开源的数据库代码中可以下载OceanBase或者RedBaseOceanBase 是淘宝的开源数据库,RedBase是斯坦福大学数据库系统实现课程的一个开源项目。后面这两个项目都是较近开始的项目,代码量较少,结构较清晰,相对简单易读,在github上都能找到。但是OceanBase目前SQL解析部分也没有全部完成,只有DML部分完成;RedBase设计更简单,不过没有设计逻辑计划。
本文中就是参考了RedBase的方式进行解析。

参考文献:

《数据库系统实现》
《flex与bison》

自己实现一个SQL解析引擎-阿里云开发者社区已关闭评论
2020年9月21日 By mikel 分类: C#, 数据库

来源: 在C#中使用Irony实现SQL语句的解析_语法

在上一篇博文 LogoSharp:Logo语言的C#实现中,我介绍了LOGO语言的C#实现,在该项目中,我使用了.NET下的语法解析框架:Irony。Irony框架最开始的时候是由Roman Ivantsov发布在codeplex上的一个开源项目,它使用MIT协议开源。由于2017年的时候,微软关闭了codeplex服务,为了让这个优秀的项目能够继续为人所用,我将其复制到了我的Github账号下,并将其移植到NET Standard 2.0,地址是:https://github.com/daxnet/irony。于是,Irony目前可以在.NET Core下使用了。事实上,Github中有很多Irony的版本,但大多数都不支持.NET Core。

不仅如此,我还在Azure DevOps上配置了持续集成,因此,你可以通过NuGet很方便地使用Irony。Irony相关的NuGet包有两个,你需要在你的项目中同时安装以下两个NuGet包:

1

2

Install-Package Irony.NetCore -Version 1.1.11

Install-Package Irony.Interpreter.NetCore -Version 1.1.11

接下来,我介绍一下如何用Irony实现一个语法解析器,我们以SQL语句为例进行介绍。

所有使用Irony框架的语法定义,都必须继承于Grammar类,并加上Language特性。以下就是我定义的一个SQL语句的语法,我称之为“Mini SQL”,因为它目前只支持SELECT语句,而且不支持WHERE子句。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

[Language( “Mini SQL” , “1.0” , “A SQL Sample” )]

public class MiniSqlGrammar : Grammar

{

public MiniSqlGrammar

: base ( false )

{

var identifier = new IdentifierTerminal( “Identifier” );

var table = new NonTerminal( “table” );

var fields = new NonTerminal( “fields” );

var sql_statement = new NonTerminal( “SQL” );

table.Rule = identifier;

fields.Rule = “*” | MakePlusRule(fields, ToTerm( “,” ), identifier);

sql_statement.Rule = “SELECT” + fields + “FROM” + table;

Root = sql_statement;

}

}

代码非常简单,也很容易理解,它可以使用人类比较容易读懂的语法定义规则进行语法定义。这里简单介绍一下上面的代码:

  • Language特性用于描述我们自定义的语法
  • 语法类需要继承于Grammar类
  • 基类构造函数的false参数表示我们的语言是大小写不敏感的
  • 所有的程序语言标识符都可以用IdentifierTerminal进行定义,Irony已经自带了标识符的识别逻辑,非常简单
  • table是一个标识符
  • fields可以是*,也可以是由一些标识符通过逗号分隔的一个列表
  • SQL语句由四个部分组成:SELECT关键字、fields字段列表、FROM关键字以及table数据表名
  • Root=sql_statement表示语言的解析入口就是SQL语句

编译通过后,我们就可以使用这个语法进行SELECT语句的解析了。

Irony有一个非常不错的功能,就是它提供一个用户界面,用来测试我们定义的语法。这个用户界面是一个Windows Forms的应用程序,我也将其发布到我的Github账号下,地址是:https://github.com/daxnet/irony-explorer。下载以后编译执行GrammarExplorer项目,就可以打开这个测试界面。在测试界面中,打开包含有自定义语法的.NET DLL,就可以将其添加到Grammar Explorer中。例如,下图展示了我用Grammar Explorer测试上面的SQL语法的界面效果:

在Grammar Explorer中,你可以看到我们自定义语法是否存在错误,还可以使用一个具有代码着色功能的编辑器进行测试,点击Parse按钮,即可生成语法解析树。

使用Irony进行语法解析的基本步骤是:

  1. 基于语法(Grammar)定义,创建语言数据(Language Data)
  2. 基于语言数据,产生解析器(Parser)
  3. 使用解析器,解析输入的字符串,生成语法树
  4. 遍历语法树,执行相应的程序逻辑

下面的代码展示了这一过程:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

sealed class SqlParser

{

private static readonly LanguageData languageData = new LanguageData( new MiniSqlGrammar);

private static readonly Parser parser = new Parser(languageData);

private readonly List< string > parsedFields = new List< string >;

public void Execute( string command)

{

var tree = parser.Parse(command);

if (tree.HasErrors)

{

return ;

}

ParseTree(tree.Root);

}

private void ParseTree(ParseTreeNode node)

{

switch (node.Term.Name)

{

case “SQL” :

foreach ( var child in node.ChildNodes)

{

ParseTree(child);

}

break ;

case “fields” :

foreach ( var child in node.ChildNodes)

{

parsedFields.Add(child.Token.Text);

}

break ;

case “table” :

ParsedTable = node.ChildNodes[0].Token.Text;

break ;

}

}

public IEnumerable< string > ParsedFields => parsedFields;

public string ParsedTable { get ; private set ; }

}

SqlParser会基于我们自定义的语法类,生成一个Irony的Parser对象,然后使用Parse方法来解析传入的SQL语句,并产生语法解析树。之后,就可以根据树状结构来解析语句中的每一个部分,并执行相应的操作。

最后,就是使用这个SqlParser类来解析SQL语句并得到结果。代码如下:

1

2

3

4

5

6

7

8

9

10

11

12

static void Main( string [] args)

{

var sqlParser = new SqlParser;

sqlParser.Execute( “SELECT firstName, LastName FROM tbl_students” );

Console.WriteLine($ “获得的数据表: {sqlParser.ParsedTable}” );

Console.WriteLine( “获得的字段:” );

foreach ( var field in sqlParser.ParsedFields)

{

Console.WriteLine(field);

}

}

代码很简单:调用SqlParser的Execute方法来解析给定的SQL语句,解析结果会分别保存在SqlParser的ParsedTable和ParsedFields两个属性中,上面的控制台程序只需要输出这两个结果就行了。在实际应用中,可以通过ParsedTable来获取某一种类型的对象列表,然后通过ParsedFields来投影列表中每个对象的属性,从而得到基于C#的一系列对象来执行SQL查询的效果。

本案例执行结果如下:

本文简要介绍了.NET语法解析框架Irony的使用,并通过SQL语句解析的案例,介绍了Irony使用的一些技巧与步骤。就SQL语句而言,它的定义还是相对比较复杂的,不可能单靠一篇博客就能完全覆盖所有内容,也是因为这个原因,本文也没有涉及WHERE子句的设计,但Irony框架是完全可以做到这一点的。事实上,在Irony Explorer项目中,就有一个IronyExplorer.Samples的工程,其中包含了很多编程语言语法定义的案例代码,比如Java、C#、SQL等,有兴趣的可以参考。Irony框架本身已经帮我们做了很多工作,比如大小写敏感的特性,比如运算符优先级的定义,比如标识符、整数、小数、正负数的定义,再比如块注释与行注释的处理等等。在我用C#实现LOGO语言时,基本上也是按照上面所述步骤进行编写和测试,因此,我就不再另起篇幅来介绍LOGO语言的语法定义了。

在C#中使用Irony实现SQL语句的解析_语法已关闭评论
2020年9月21日 By mikel 分类: 数据库

来源: SQL语句的解析过程 – 独上高楼 – 博客园

由于最近需要做一些SQL query性能提升的研究,因此研究了一下SQL语句的解决过程。在园子里看了下,大家写了很多相关的文章,大家的侧重点各有不同。本文是我在看了各种资料后手机总结的,会详细的,一步一步的讲述一个SQL语句的各个关键字的解析过程,欢迎大家互相学习。


SQL语句的解析顺序

简单的说一个sql语句是按照如下的顺序解析的:

  • 1. FROM FROM后面的表标识了这条语句要查询的数据源。和一些子句如,(1-J1)笛卡尔积,(1-J2)ON过滤,(1-J3)添加外部列,所要应用的对象。FROM过程之后会生成一个虚拟表VT1。
  • (1-J1)笛卡尔积 这个步骤会计算两个相关联表的笛卡尔积(CROSS JOIN) ,生成虚拟表VT1-J1。
  • (1-J2)ON过滤 这个步骤基于虚拟表VT1-J1这一个虚拟表进行过滤,过滤出所有满足ON 谓词条件的列,生成虚拟表VT1-J2。
  • (1-J3)添加外部行  如果使用了外连接,保留表中的不符合ON条件的列也会被加入到VT1-J2中,作为外部行,生成虚拟表VT1-J3。
  • 2. WHERE 对VT1过程中生成的临时表进行过滤,满足where子句的列被插入到VT2表中。
  • 3. GROUP BY 这个子句会把VT2中生成的表按照GROUP BY中的列进行分组。生成VT3表。
  • 4. HAVING 这个子句对VT3表中的不同的组进行过滤,满足HAVING条件的子句被加入到VT4表中。
  • 5. SELECT 这个子句对SELECT子句中的元素进行处理,生成VT5表。
  • (5-1)计算表达式 计算SELECT 子句中的表达式,生成VT5-1
  • (5-2)DISTINCT 寻找VT5-1中的重复列,并删掉,生成VT5-2
  • (5-3)TOP 从ORDER BY子句定义的结果中,筛选出符合条件的列。生成VT5-3表
  • ORDER BY 从VT5-3中的表中,根据ORDER BY 子句的条件对结果进行排序,生成VC6表。

客户,订单的查询例子

首先创建一个Customers表,插入如下数据:

customerid city
FISSA Madrid
FRNDO Madrid
KRLOS Madrid
MRPHS Zion

创建一个Orders表,插入如下数据:

orderid customerid
1 FRNDO
2 FRNDO
3 KRLOS
4 KRLOS
5 KRLOS
6 MRPHS
7 NULL

假如我们想要查询来自Madrid的,订单数小于3的客户,并把他们的订单数显示出来,结果按照订单数从小到大进行排序。

复制代码
SELECT C.customerid, COUNT(O.orderid) AS numorders
FROM dbo.Customers AS C
  LEFT OUTER JOIN dbo.Orders AS O
    ON C.customerid = O.customerid
WHERE C.city = 'Madrid'
GROUP BY C.customerid
HAVING COUNT(O.orderid)

<

 3
ORDER BY numorders
复制代码

查询结果为:

customerid numorders
FISSA 0
FRNDO 2

下面我们会详细的讲述sql是怎样计算出这个结果的:

 

FROM子句

FROM子句标识了需要查询的表,如果指定了表操作,会从左到右的处理,每一个基于一个或者两个表的表操作都会返回一个输出表。左边表的输出结果会作为下一个表操作的输入结果。例如,交表相关的操作有 (1-J1)笛卡尔积,(1-J2)ON过滤器,(1-J3)添加外部列。FROM句子生成虚拟表VT1。

Step 1-J1:执行笛卡尔积(CROSS JOIN)

笛卡尔积会把左右两个表每一行所有可能的组合都列出来生成表VT1-J1,如果左表有m列,右表有n列,那么笛卡尔积之后生成的VT1-J1表将会有m×n列。

Step 1-J1这个步骤等价于执行:

SELECT * from Customers C  CROSS JOIN Orders O

执行结果为:(共有4×7列)

C.customerid C.city O.orderid O.customerid
FISSA Madrid 1 FRNDO
FISSA Madrid 2 FRNDO
FISSA Madrid 3 KRLOS
FISSA Madrid 4 KRLOS
FISSA Madrid 5 KRLOS
FISSA Madrid 6 MRPHS
FISSA Madrid 7 NULL
FRNDO Madrid 1 FRNDO
FRNDO Madrid 2 FRNDO
FRNDO Madrid 3 KRLOS
FRNDO Madrid 4 KRLOS
FRNDO Madrid 5 KRLOS
FRNDO Madrid 6 MRPHS
FRNDO Madrid 7 NULL
KRLOS Madrid 1 FRNDO
KRLOS Madrid 2 FRNDO
KRLOS Madrid 3 KRLOS
KRLOS Madrid 4 KRLOS
KRLOS Madrid 5 KRLOS
KRLOS Madrid 6 MRPHS
KRLOS Madrid 7 NULL
MRPHS Zion 1 FRNDO
MRPHS Zion 2 FRNDO
MRPHS Zion 3 KRLOS
MRPHS Zion 4 KRLOS
MRPHS Zion 5 KRLOS
MRPHS Zion 6 MRPHS
MRPHS Zion 7 NULL

 

Step 1-J2:应用ON过滤,(JOIN 条件)

ON过滤条件是sql的三个过滤条件(ON,WHERE,HAVING)中最先执行的,ON过滤条件应用于前一步生成的虚拟表(VT1-J1),满足ON过滤条件的行会被加入到虚拟表VT1-J2中。在应用了ON 过滤之后,生成的VT1-J2表如下所示:

C.customerid C.city O.orderid O.customerid
FRNDO Madrid 1 FRNDO
FRNDO Madrid 2 FRNDO
KRLOS Madrid 3 KRLOS
KRLOS Madrid 4 KRLOS
KRLOS Madrid 5 KRLOS
MRPHS Zion 6 MRPHS

 

Step 1-J3:添加外部列

这个步骤只会出现在使用了外连接的情况。对于外连接(LEFT,RIGHT, or FULL),你可以标记一个或者两个表作为保留表。作为保留表意味着你希望这个表里面的所有列都被返回,即使它里面的数据不满足ON子句的过滤条件。LEFT OUTER JOIN 把左边的表标记为保留表,RIGHTOUTER JOIN把右边的表作为保留表,FULL OUTER JOIN把两个表都标记为保留表.Step 1-J3为根据VT1-J2中的虚拟表,添加了保留表中不满足ON 条件的列,在未保留表中没有对应的列,因此标记为NULL。这个过程生成了虚拟表VT1-J3。

C.customerid C.city O.orderid O.customerid
FISSA Madrid NULL NULL
FRNDO Madrid 1 FRNDO
FRNDO Madrid 2 FRNDO
KRLOS Madrid 3 KRLOS
KRLOS Madrid 4 KRLOS
KRLOS Madrid 5 KRLOS
MRPHS Zion 6 MRPHS

如果FROM子句中有多个表操作运算,sql会按照从左到右的顺序处理,左边生成的临时表结果作为右边表的输入表。

Step 2 WHERE 子句

WHERE过滤被应用到前一步生成的临时表中,根据WHERE过滤条件生成临时表VT2。

注意:由于数据现在还没有被分组,因此现在你不能使用聚合运算-例如:你不能使用这样的句子 WHERE orderdate = MAX(orderdate)。另外你也不能使用SELECT子句中创建的变量别名,因为现在还没有处理SELECT子句-例如你不能写这样的句子:SELECT YEAR(orderdate) AS orderyear . . . WHERE orderyear > 2008.

应用这个过滤

WHERE C.city = 'Madrid'

这时生成的临时表VT2的内容如下:

C.customerid C.city O.orderid O.customerid
FISSA Madrid NULL NULL
FRNDO Madrid 1 FRNDO
FRNDO Madrid 2 FRNDO
KRLOS Madrid 3 KRLOS
KRLOS Madrid 4 KRLOS
KRLOS Madrid 5 KRLOS

在这个例子中,你需要在ON子句中使用ON C.customerid = O.customerid过滤,没有订单的客户在1-J2这一步中被过滤掉,但是在1-J3这一步中作为外部列又被加回来。但是,由于你只想返回来自Madrid的客户,因此你需要在WHERE子句中过滤城市(WHERE C.city = ‘Madrid’),如果你放在ON过滤中,不属于Madrid的客户在添加外部列中会被添加回来。

关于ON 和 WHERE 的区别需要在这里说明一下,ON 和WHERE 的主要区别在于 ON 实在添加外部列之前进行过滤,WHERE 是在之后。ON过滤掉的列会在1-J3中添加回来。如果你不需要添加外部列,那么这两个过滤是相同的。

Step 3 GROUP BY子句

这个子句会把前一步中生成的临时表中的数据进行分组,每一行都会分到并且只分到一个组里,生成虚拟表VT3。VT3表中包含了VT2表中所有的数据,和分组标识符。

这是生成的临时表VT3的内容如下:

Groups
C.customerid
C.customerid C.city O.orderid O.customerid
FISSA FISSA Madrid     NULL NULL
FRNDO FRNDO Madrid 1 FRNDO
FRNDO Madrid 2 FRNDO
KRLOS Madrid 3 KRLOS
KRLOS KRLOS Madrid 4 KRLOS
KRLOS Madrid 5 KRLOS

sql最终返回的结果中,每一个分组必须只能返回一行(除非被过滤掉),因此当一个sql语句中使用了GROUP BY时,在GROUP BY后面处理的子句,如SELECT,HAVING子句等,只能使用出现在GROUP BY后面的列,对于没有出现GROUP BY后面的列必须使用聚合函数(如 MAX ,MIN,COUNT,AVG等),保证每一个GROUP只返回一行。

Step 4 HAVING子句

HAVING子句用来过滤前一步生成的临时表,并且只作用于分组后的数据,满足HAVING条件的GROUP被添加到虚拟表VT4中。

当应用了这个过滤:

HAVING COUNT(O.orderid) < 3

之后,生成的VT4表内容如下:

Groups
C.customerid
C.customerid C.city O.orderid O.customerid
FISSA FISSA Madrid     NULL NULL
FRNDO FRNDO Madrid 1 FRNDO
FRNDO Madrid 2 FRNDO

需要注意的一点是,这里面使用的是COUNT(O.orderid),而不是COUNT(*),由于这个查询中添加了外部列,COUNT方法会忽略NULL的列,导致出现了你不想要的结果。

Step 5 SELECT 子句

尽管出现在sql语句的最前面,SELECT在第五步的时候才被处理,SELECT子句返回的表会最终返回给调用者。这个子句包含三个子阶段:(5-1)计算表达式,(5-2) 处理DISTINCT,(5-3)应用TOP过滤。

Step 5-1 计算表达式

SELECT子句中的表达式可以返回或者操作前一步表中返回的基本列。如果这个sql语句是一个聚合查询,在Step 3之后,你只能使用GROUP BY中的列,对不属于GROUP集合中的列必须使用聚合运算。不属于FROM表中基本列的必须为其起一个别名,如YEAR(orderdate) AS orderyear。

注意:在SELECT子句中创建的别名,不能在之前的Step中使用,即使在SELECT子句中也不能。原因是sql的很多操作是同时操作(all at once operation),至于什么是all-at-once operation这里就不再介绍了。因此,SELECT子句中创建的别名只能在后面的子句中使用,如ORDER BY。例如:SELECT YEAR(orderdate) AS orderyear . . . ORDER BY orderyear。

在这个例子中:

SELECT C.customerid, COUNT(O.orderid) AS numorders

结果会得到一个虚拟表VT5-1:

C.customerid numorders
FIFSSA 0
FRNDO 2

Step 5-2:应用DISTINCT子句

如果sql语句中使用了DISTINCT,sql会把重复列去掉,生成虚拟表VT5-2。

Step 5-3:应用TOP选项

TOP选项是T-SQL提供的一个功能,用来表示显示多少行。基于ORDER BY子句定义的顺序,指定个数的列会被查询出来。这个过程生成虚拟表VT5-3。

正如上文提到的,这一步依赖于ORDER BY定义的顺序来决定哪些列应该显示在前面。如果你没有指定结果的ORDER BY顺序,也没有使用WITH TIES子句 ,每一次的返回结果可能会不一致。

在我们的例子中,Step 5-3被省略了,因为我们没有使用TOP关键字。

Step 6:ORDER BY子句

前一步返回的虚拟表在这一步被排序,根据ORDER BY子句指定的顺序,返回游标VC6。ORDER BY子句也是唯一一个可以使用SELECT子句创建的别名的地方。

注意:这一步和之前不同的地方在于,这一步返回的结果是一个游标,而不是表。sql是基于集合理论的,一个集合没有对他的行定义顺序,它只是一个成员的逻辑集合,因此成员的顺序并不重要。带有ORDER BY子句的sql返回一个按照特定序列组织每一行的对象。ANSI 把这样的一个对象叫游标。理解这一点对你了解sql很重要。


上面的步骤如图所示:

image

 

本书中主要内容是参照 Inside Microsoft SQL Server 2008:T-SQL Query,中的内容,大家如果想深入了解sql查询相关的知识,可以找这本书看看,我这有英文原版的pdf,需要的可以找我要。

SQL语句的解析过程 – 独上高楼 – 博客园已关闭评论
备案信息冀ICP 0007948