您的位置:永利集团登录网址 > 计算机教学 > 执行计划的缓存

执行计划的缓存

2019-10-04 21:43

今天介绍个小工具,Django的插件—— https://github.com/BertrandBordage/django-cachalot (Django和抹香鲸?)。这个工具的主要应用场景就是自动帮你缓存你执行过的query结果,以此来帮助网站优化访问速度。毕竟同样查询没必要hit两次甚至多次数据库。

前些日子,对 plan cache 不太熟悉。就在msdn 发了一个问题。

在oracle数据库中,有一个变量绑定的用法,很多人都比较熟悉,可以调高数据库效率,应对高并发等,好吧,这其中并不包括我,当同事问我MySQL中有没有类似的写法时,我是很茫然的,于是就上网查,找到了如下一种写法:

一个遗留的问题场景

关于sys.dm_exec_cached_plans 和sys.dm_exec_query_stats 一些疑问

view sourceprint?DELIMITER $$ 

话说很久很久之前,遇到过这样的一个问题,当时还是在用django自带得admin做项目,新闻的数据量很大,导致每次打开新闻的列表页都非常慢,把Django上能用的优化都用上之后好了些,但是还是没有彻底解决。当时貌似没有考虑用缓存来解决,具体原因忘了,毕竟后台项目体验上过得去就行。

联机手册里面对执行计划的解释如下:

  set @stmt = select userid,username from myuser where userid between ? and ?; 

看到cachalot之后,突然就想到了这个场景,当时没有用缓存可能是考虑数据增加很快,缓存的时间设置不好把握,也可能是其他原因。但是cachalot确实是一个值得考虑的方案。

SQL Server 执行计划包含下列主要组件:

  prepare s1 from @stmt; 

怎么使用cachelot

  • 查询计划

    执行计划的主体是一个重入的只读数据结构,可由任意数量的用户使用。这称为查询计划。查询计划中不存储用户上下文。内存中查询计划副本永远不超过两个:一个副本用于所有的串行执行,另一个用于所有的并行执行。并行副本覆盖所有的并行执行,与并行执行的并行度无关。

  • 执行上下文

    每个正在执行查询的用户都有一个包含其执行专用数据(如参数值)的数据结构。此数据结构称为执行上下文。执行上下文数据结构可以重新使用。如果用户执行查询而其中的一个结构未使用,将会用新用户的上下文重新初始化该结构。

  set @s1 = 2; 

上面说了些毫无营养的联想,再来说说怎么使用,github上说的很清楚了,使用相当简单:

但是其实,在过程缓存中,并不是只有这2个。

  set @s2 = 100; 

pip install django-cachalot

1.已编译的计划

  execute s1 using @s1,@s2; 

#然后Django的installs_app中加入

2.执行上下文

  deallocate prepare s1; 

INSTALLED_APPS = (
    ...
    "cachalot",
    ...
)

3.游标

$$ 

即可

4.词法分析树

  

原理

(大家如果对这4个比较有兴趣可以看 《sql server 求生秘籍》 chr4.1)里面有比较多的解释。

DELIMITER ;

这个项目代码很少,做的事基本上就是给Django执行sql的那个方法: compiler.execute_sql = patch_execute_sql(compiler.execute_sql) 打个patch,大体逻辑就是,这个sql如果没执行过就执行,然后缓存结果。 这个是正常得缓存逻辑,那么如果数据进行了更改怎么处理呢,cachalot也对执行写数据库的方法打了patch,在写的时候根据语句对缓存进行删除。

 关于执行上下文的复用,sql server 求生秘籍也讲的不是很清楚或者我还没理解它要表达的意思。

 用这种形式写的查询,可以随意替换参数,给出代码的人称之为预处理,我想这个应该就是MySQL中的变量绑定吧……但是,在查资料的过程中我却听到了两种声音,一种是,MySQL中有类似Oracle变量绑定的写法,但没有其实际作用,也就是只能方便编写,不能提高效率,这种说法在几个09年的帖子中看到:

总结

下面是:Inside Microsoft® SQL Server™ 2005: Query Tuning and Optimization 中提到的 关于执行上下文的问题

Djang本身提供了三个层级的cache:整站,view,自定义。cachalot相当于提供了query层级的cache。不过这东西对于单独的应用是有些帮助,但是对于相互依赖的多个应用就不太适用了,比如:A系统只是用来读数据的,本身并不写,这样缓存始终无法更新。

Execution Plans

Executable plans, or execution contexts, are considered to be dependent on compiled plans and do not show up in thesys.dm_exec_cached_plans view. Executable plans are runtime objects created when a compiled plan is executed. Just as for compiled plans, executable plans can be object plans stored in the object store, or SQL plans, stored in the SQL store. Each executable plan exists in the same cache store as the compiled plan to which it is dependent. Executable plans contain the particular runtime information for one execution of a compiled plan, and include the actual runtime parameters, any local variable information, object ids for objects created at run time, the user ID, and information about the currently executing statement in the batch.

When SQL Server starts executing a compiled plan, it generates an executable plan from that compiled plan. Each individual statement in a compiled plan gets its own executable plan, which you can think of as a runtime query plan. Unlike compiled plans, executable plans are for a single session. For example, if there are 100 users simultaneously executing the same batch, there will be 100 executable plans for the same compiled plan. Executable plans can be regenerated from their associated compiled plan, and they are relatively inexpensive to create. Later in this section, we’ll look at the sys.dm_exec_cached_plan_dependent_objects view, which contains information about your executable plans.

sql server 求生秘籍中说执行上下文能够复用。但是前提是不是并发的

insider说可以通过 

sys.dm_exec_cached_plan_dependent_objects 来查执行上下文,里面存放的是对于 plan_handle的执行上下文(当然还有clr的)。

 

我再 msdn的问题,今天想拿出来解释一下。

环境:Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86)   Apr  2 2010 15:53:02   Copyright (c) Microsoft Corporation  Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2) 
测试数据库:AdventureWorks
当我给 AdventureWorks 数据库设置强制参数 化后。
使用如下代码分别在3个session中逐个运行

SELECT soh.SalesOrderNumber ,
sod.ProductID
FROM Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.SalesOrderNumber = 'SO43662'
SELECT soh.SalesOrderNumber ,
sod.ProductID
FROM Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.SalesOrderNumber = 'SO58928'
SELECT soh.SalesOrderNumber ,
sod.ProductID
FROM Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.SalesOrderNumber = 'SO70907'
再查询

如下sql:
SELECT b.text,a.plan_handle,a.sql_handle FROM sys.dm_exec_query_stats   a
    CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b
    --CROSS APPLY  sys.dm_exec_query_plan(a.plan_handle) c  
WHERE b.text LIKE '%select%SalesOrderDetail%' AND b.text NOT LIKE ' %'

SELECT b.text,c.*,a.plan_handle FROM sys.dm_exec_cached_plans  a
    CROSS APPLY sys.dm_exec_sql_text(a.plan_handle) b
    CROSS APPLY  sys.dm_exec_query_plan(a.plan_handle) c  
WHERE b.text LIKE '%select%SalesOrderDetail%' AND b.text NOT LIKE ' %'
按理来说 已经使用了强制化参数,那么应该只会产生一条 计划。 但是当我使用上面的sql 查询是 发下 以下结果
  • 图片 1

    sys.dm_exec_cached_plans :针对 SQL Server 为了加快查询执行而缓存的每个查询计划返回一行

    sys.dm_exec_query_stats:返回缓存查询计划的聚合性能统计信息。缓存计划中的每个查询语句在该视图中对应一行,并且行的生存期与计划本身相关联。

    为什么 使用sys.dm_exec_cached_plans 会出现4条但是用 sys.dm_exec_query_stats 只出现1条呢

解释:

     

我们对比 

sys.dm_exec_cached_plan_dependent_objects 和 sys.dm_exec_cached_plans 的usecounts 是不是能够从中猜测出,sql server 是如何获取已编译计划的,和执行上下文的。

  下面 我们用 我下面的测试代码来测试 看看 usecount 有什么变化,我们是不是从中能探知一些原理

第一遍运行:

图片 2

第二遍运行:

图片 3

 

sys.dm_exec_cached_plans中具体的sql 使用都是1,那个预编译的计划使用是3,cache类型是 complied plan,对于的也生产了执行上下文预编译的是3,其他的是1。为啥预编译的是3,因为已经使用了预编译产生的已编译计划。既然已经强制参数化了,应该只有1条才对。为什么是多了3条呢?我再msdn得到的答案是这3条是假的只是shell plan。这样的解释让我觉得有点不详细。比较第一遍运行的结果和第二遍运行的结果,强制参数化,在提交的时候已经使用了预编译计划,根据预编译又产生了3条已编译计划。当你第二遍运行的时候是不会去引用cache_plan 里面的计划,但是它复用了s

 

ys.dm_exec_cached_plan_dependent_objects 里面的执行上下文,所以说是shell plan,真正被复用的是 预编译的计划。

 

 

写完仔细想想,这东西貌似没有太多的应用场景。对于小站点或许有用,比如我的博客。但博客已经添加了view层的缓存。或许可以参考cachalot来做些缓存策略上的优化。

另一种说法是MySQL中的变量绑定是能确实提高效率的,这个是希望有的,那到底有木有,还是自己去试验下吧。

试验是在本机进行的,数据量比较小,具体数字并不具有实际意义,但是,能用来说明一些问题,数据库版本是mysql-5.1.57-win32免安装版。

  本着对数据库不是很熟悉的态度^_^,试验过程中走了不少弯路,此文以结论为主,就不列出实验的设计过程,文笔不好,文章写得有点枯燥,写出来是希望有人来拍砖,因为我得出的结论是:预处理在有没有cache的情况下的执行效率都不及直接执行…… 我对自己的实验结果不愿接受。。如果说预处理只为了规范下Query,使cache命中率提高的话个人觉得大材小用了,希望有比较了解的人能指出事实究竟是什么样子的——NewSilen

实验准备

  第一个文件NormalQuery.sql

NormalQuery
Set profiling=1;Select * From MyTable where DictID = 100601000004;Select DictID from MyTable limit 1,100;Select DictID from MyTable limit 2,100;/*从limit 1,100 到limit 100,100 此处省略重复代码*/......Select DictID from MyTable limit 100,100;SELECT query_id,seq,STATE,10000*DURATION FROM information_schema.profiling INTO OUTFILE d:/NormalResults.csv FIELDS TERMINATED BY , LINES TERMINATED BY ;
  第二个sql文件 StmtQuery.sql

StmtQuery
Set profiling=1;Select * From MyTable where DictID = 100601000004;set @stmt = Select DictID from MyTable limit ?,?;prepare s1 from @stmt;set @s = 100;set @s1 = 101;set @s2 = 102;......set @s100 =200;execute s1 using @s1,@s;execute s1 using @s2,@s;......execute s1 using @s100,@s;SELECT query_id,seq,STATE,10000*DURATION FROM information_schema.profiling INTO OUTFILE d:/StmtResults.csv FIELDS TERMINATED BY , LINES TERMINATED BY ;
做几点小说明:

  1. Set profiling=1; 执行此语句之后,可以从information_schema.profiling这张表中读出语句执行的详细信息,其实包含不少内容,包括我需要的时间信息,这是张临时表,每新开一个会话都要重新设置profiling属性才能从这张表中读取数据

  2. Select * From MyTable where DictID = 100601000004;

  这行代码貌似和我们的实验没什么关系,本来我也是这么认为的,之所以加这句,是我在之前的摸索中发现,执行过程中有个步骤是open table,如果是第一次打开某张表,那时间是相当长的,所以在执行后面的语句前,我先执行了这行代码打开试验用的表

3. MySQL默认在information_schema.profiling表中保存的查询历史是15条,可以修改profiling_history_size属性来进行调整,我希望他大一些让我能一次取出足够的数据,不过最大值只有100,尽管我调整为150,最后能够查到的也只有100条,不过也够了

4. SQL代码我没有全列出来,因为查询语句差不多,上面代码中用省略号表示了,最后的结果是两个csv文件,个人习惯,你也可以把结果存到数据库进行分析

  实验步骤

重启数据库,执行文件NormalQuery.sql,执行文件StmtQuery.sql,得到两个结果文件

再重启数据库,执行StmtQuery.sql,执行文件NormalQuery.sql,得到另外两个结果文件

  实验结果

详细结果在最后提供了附件下载,有兴趣的朋友可以看下

  结果分析

每一个SQL文件中执行了一百个查询语句,没有重复的查询语句,不存在查询cache,统计执行SQL的平均时间得出如下结果

图片 4

从结果中可以看出,无论是先执行还是后执行,NormalQuery中的语句都比使用预处理语句的要快一些=.=!

那再来看看每一句查询具体的情况,Normal和Stmt的query各执行了两百次,每一步的详细信息如下:

图片 5

从这里面可以看出,第一个,normalquery比stmtquery少一个步骤,第二个,虽然stmt在不少步骤上是优于normal的,但在executing一步上输掉太多,最后结果上也是落败

 最后,再给出一个查询缓存的实验结果,具体步骤就不列了

图片 6

在查询缓存的时候,Normal完胜……

 

...

本文由永利集团登录网址发布于计算机教学,转载请注明出处:执行计划的缓存

关键词:

  • 上一篇:没有了
  • 下一篇:没有了