.Net内部缓存System.Web.Caching.Cache 和Redis缓存缓存工厂切换_naileiforever的专栏-CSDN博客_.net cache和redis

mikel阅读(858)

来源: .Net内部缓存System.Web.Caching.Cache 和Redis缓存缓存工厂切换_naileiforever的专栏-CSDN博客_.net cache和redis

有个问题,以前系统采用的是System.Web.Caching.Cache
但是redis缓存的流行
分布式的流行,缓存就被切换了.
但是在redis缓存的环境需要配置,有时候要切换回来.
这时候就弄个缓存工厂切换.
改动小小的地方就ok.


namespace Yestar.Cache.Factory
{
///

/// Zhruanjian
/// 创建人:TD
/// 描 述:缓存工厂类
///

public class CacheFactory
{
///

/// 定义通用的Repository
///

///
public static ICache Cache()
{
return new Cache();
}
}
}

定义缓存抽象类
using System;

namespace Yestar.Cache
{
///

/// Zhruanjian
/// 创建人:TD
/// 描 述:定义缓存接口
///

public interface ICache
{
///

/// 读取缓存
///

///键 ///
T GetCache(string cacheKey) where T : class;
///

/// 写入缓存
///

///对象数据 ///键 void WriteCache(T value, string cacheKey) where T : class;
///

/// 写入缓存
///

///对象数据 ///键 ///到期时间 void WriteCache(T value, string cacheKey, DateTime expireTime) where T : class;
///

/// 移除指定数据缓存
///

///键 void RemoveCache(string cacheKey);
///

/// 移除全部缓存
///

void RemoveCache();
}
}
System.Web.Caching.Cache 缓存继承抽象类using System;
using System.Collections;
using System.Web;

namespace Yestar.Cache
{
///

/// 版 本 6.1
/// Zhruanjian
/// 描 述:缓存操作
///

public class Cache : ICache
{
private static System.Web.Caching.Cache cache = HttpRuntime.Cache;

///

/// 读取缓存
///

///键 ///
public T GetCache(string cacheKey) where T : class
{
if (cache[cacheKey] != null)
{
return (T)cache[cacheKey];
}
return default(T);
}
///

/// 写入缓存
///

///对象数据 ///键 public void WriteCache(T value, string cacheKey) where T : class
{
cache.Insert(cacheKey, value, null, DateTime.Now.AddMinutes(10), System.Web.Caching.Cache.NoSlidingExpiration);
}
///

/// 写入缓存
///

///对象数据 ///键 ///到期时间 public void WriteCache(T value, string cacheKey, DateTime expireTime) where T : class
{
cache.Insert(cacheKey, value, null, expireTime, System.Web.Caching.Cache.NoSlidingExpiration);
}
///

/// 移除指定数据缓存
///

///键 public void RemoveCache(string cacheKey)
{
cache.Remove(cacheKey);
}
///

/// 移除全部缓存
///

public void RemoveCache()
{
IDictionaryEnumerator CacheEnum = cache.GetEnumerator();
while (CacheEnum.MoveNext())
{
cache.Remove(CacheEnum.Key.ToString());
}
}
}
}
RedisCache 缓存类using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ServiceStack.Redis;

namespace Yestar.Cache.Redis
{
///

/// 版 本 6.1
/// Zhruanjian
/// 描 述:定义缓存接口
///

public class Cache : ICache
{
///

/// 读取缓存
///

///键 ///
public T GetCache(string cacheKey) where T : class
{
return RedisCache.Get(cacheKey);
}

///

/// 写入缓存
///

///对象数据 ///键 public void WriteCache(T value, string cacheKey) where T : class
{
RedisCache.Set(cacheKey, value);
}
///

/// 写入缓存
///

///对象数据 ///键 ///到期时间 public void WriteCache(T value, string cacheKey, DateTime expireTime) where T : class
{
RedisCache.Set(cacheKey, value, expireTime);
}
///

/// 移除指定数据缓存
///

///键 public void RemoveCache(string cacheKey)
{
RedisCache.Remove(cacheKey);
}
///

/// 移除全部缓存
///

public void RemoveCache()
{
RedisCache.RemoveAll();
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ServiceStack.Redis;

namespace Yestar.Cache.Redis
{
///

/// 版 本 6.1
/// Zhruanjian
/// 描 述:定义缓存接口
///

public class RedisCache
{
#region — 连接信息 —
///

/// redis配置文件信息
///

private static RedisConfigInfo redisConfigInfo = RedisConfigInfo.GetConfig();
private static PooledRedisClientManager prcm;
///

/// 静态构造方法,初始化链接池管理对象
///

static RedisCache()
{
CreateManager();
}
///

/// 创建链接池管理对象
///

private static void CreateManager()
{
string[] writeServerList = SplitString(redisConfigInfo.WriteServerList, “,”);
string[] readServerList = SplitString(redisConfigInfo.ReadServerList, “,”);

prcm = new PooledRedisClientManager(readServerList, writeServerList,
new RedisClientManagerConfig
{
MaxWritePoolSize = redisConfigInfo.MaxWritePoolSize,
MaxReadPoolSize = redisConfigInfo.MaxReadPoolSize,
AutoStart = redisConfigInfo.AutoStart,
});
}
private static string[] SplitString(string strSource, string split)
{
return strSource.Split(split.ToArray());
}
#endregion

#region — Item —
///

/// 设置单体
///

///
////////////
public static bool Set(string key, T t)
{
using (IRedisClient redis = prcm.GetClient())
{
return redis.Set(key, t);
}
}
///

/// 设置单体
///

///
////////////
public static bool Set(string key, T t, TimeSpan timeSpan)
{
using (IRedisClient redis = prcm.GetClient())
{
return redis.Set(key, t, timeSpan);
}
}
///

/// 设置单体
///

///
////////////
public static bool Set(string key, T t, DateTime dateTime)
{
using (IRedisClient redis = prcm.GetClient())
{
return redis.Set(key, t, dateTime);
}
}

///

/// 获取单体
///

///
//////
public static T Get(string key) where T : class
{
using (IRedisClient redis = prcm.GetClient())
{
return redis.Get(key);
}
}

///

/// 移除单体
///

///public static bool Remove(string key)
{
using (IRedisClient redis = prcm.GetClient())
{
return redis.Remove(key);
}
}
///

/// 清空所有缓存
///

public static void RemoveAll()
{
using (IRedisClient redis = prcm.GetClient())
{
redis.FlushAll();
}
}
#endregion

#region — List —

public static void List_Add(string key, T t)
{
using (IRedisClient redis = prcm.GetClient())
{

var redisTypedClient = redis.As();
redisTypedClient.AddItemToList(redisTypedClient.Lists[key], t);
}
}

public static bool List_Remove(string key, T t)
{
using (IRedisClient redis = prcm.GetClient())
{
var redisTypedClient = redis.As();
return redisTypedClient.RemoveItemFromList(redisTypedClient.Lists[key], t) > 0;
}
}
public static void List_RemoveAll(string key)
{
using (IRedisClient redis = prcm.GetClient())
{
var redisTypedClient = redis.As();
redisTypedClient.Lists[key].RemoveAll();
}
}

public static long List_Count(string key)
{
using (IRedisClient redis = prcm.GetClient())
{
return redis.GetListCount(key);
}
}

public static List List_GetRange(string key, int start, int count)
{
using (IRedisClient redis = prcm.GetClient())
{
var c = redis.As();
return c.Lists[key].GetRange(start, start + count – 1);
}
}

public static List List_GetList(string key)
{
using (IRedisClient redis = prcm.GetClient())
{
var c = redis.As();
return c.Lists[key].GetRange(0, c.Lists[key].Count);
}
}

public static List List_GetList(string key, int pageIndex, int pageSize)
{
int start = pageSize * (pageIndex – 1);
return List_GetRange(key, start, pageSize);
}

///

/// 设置缓存过期
///

//////public static void List_SetExpire(string key, DateTime datetime)
{
using (IRedisClient redis = prcm.GetClient())
{
redis.ExpireEntryAt(key, datetime);
}
}
#endregion

#region — Set —
public static void Set_Add(string key, T t)
{
using (IRedisClient redis = prcm.GetClient())
{
var redisTypedClient = redis.As();
redisTypedClient.Sets[key].Add(t);
}
}
public static bool Set_Contains(string key, T t)
{
using (IRedisClient redis = prcm.GetClient())
{
var redisTypedClient = redis.As();
return redisTypedClient.Sets[key].Contains(t);
}
}
public static bool Set_Remove(string key, T t)
{
using (IRedisClient redis = prcm.GetClient())
{
var redisTypedClient = redis.As();
return redisTypedClient.Sets[key].Remove(t);
}
}
#endregion

#region — Hash —
///

/// 判断某个数据是否已经被缓存
///

///
/////////
public static bool Hash_Exist(string key, string dataKey)
{
using (IRedisClient redis = prcm.GetClient())
{
return redis.HashContainsEntry(key, dataKey);
}
}

///

/// 存储数据到hash表
///

///
/////////
public static bool Hash_Set(string key, string dataKey, T t)
{
using (IRedisClient redis = prcm.GetClient())
{
string value = ServiceStack.Text.JsonSerializer.SerializeToString(t);
return redis.SetEntryInHash(key, dataKey, value);
}
}
///

/// 移除hash中的某值
///

///
/////////
public static bool Hash_Remove(string key, string dataKey)
{
using (IRedisClient redis = prcm.GetClient())
{
return redis.RemoveEntryFromHash(key, dataKey);
}
}
///

/// 移除整个hash
///

///
/////////
public static bool Hash_Remove(string key)
{
using (IRedisClient redis = prcm.GetClient())
{
return redis.Remove(key);
}
}
///

/// 从hash表获取数据
///

///
/////////
public static T Hash_Get(string key, string dataKey)
{
using (IRedisClient redis = prcm.GetClient())
{
string value = redis.GetValueFromHash(key, dataKey);
return ServiceStack.Text.JsonSerializer.DeserializeFromString(value);
}
}
///

/// 获取整个hash的数据
///

///
//////
public static List Hash_GetAll(string key)
{
using (IRedisClient redis = prcm.GetClient())
{
var list = redis.GetHashValues(key);
if (list != null && list.Count > 0)
{
List result = new List();
foreach (var item in list)
{
var value = ServiceStack.Text.JsonSerializer.DeserializeFromString(item);
result.Add(value);
}
return result;
}
return null;
}
}
///

/// 设置缓存过期
///

//////public static void Hash_SetExpire(string key, DateTime datetime)
{
using (IRedisClient redis = prcm.GetClient())
{
redis.ExpireEntryAt(key, datetime);
}
}
#endregion

#region — SortedSet —
///

/// 添加数据到 SortedSet
///

///
/////////public static bool SortedSet_Add(string key, T t, double score)
{
using (IRedisClient redis = prcm.GetClient())
{
string value = ServiceStack.Text.JsonSerializer.SerializeToString(t);
return redis.AddItemToSortedSet(key, value, score);
}
}
///

/// 移除数据从SortedSet
///

///
/////////
public static bool SortedSet_Remove(string key, T t)
{
using (IRedisClient redis = prcm.GetClient())
{
string value = ServiceStack.Text.JsonSerializer.SerializeToString(t);
return redis.RemoveItemFromSortedSet(key, value);
}
}
///

/// 修剪SortedSet
///

//////保留的条数 ///
public static long SortedSet_Trim(string key, int size)
{
using (IRedisClient redis = prcm.GetClient())
{
return redis.RemoveRangeFromSortedSet(key, size, 9999999);
}
}
///

/// 获取SortedSet的长度
///

//////
public static long SortedSet_Count(string key)
{
using (IRedisClient redis = prcm.GetClient())
{
return redis.GetSortedSetCount(key);
}
}

///

/// 获取SortedSet的分页数据
///

///
////////////
public static List SortedSet_GetList(string key, int pageIndex, int pageSize)
{
using (IRedisClient redis = prcm.GetClient())
{
var list = redis.GetRangeFromSortedSet(key, (pageIndex – 1) * pageSize, pageIndex * pageSize – 1);
if (list != null && list.Count > 0)
{
List result = new List();
foreach (var item in list)
{
var data = ServiceStack.Text.JsonSerializer.DeserializeFromString(item);
result.Add(data);
}
return result;
}
}
return null;
}

///

/// 获取SortedSet的全部数据
///

///
////////////
public static List SortedSet_GetListALL(string key)
{
using (IRedisClient redis = prcm.GetClient())
{
var list = redis.GetRangeFromSortedSet(key, 0, 9999999);
if (list != null && list.Count > 0)
{
List result = new List();
foreach (var item in list)
{
var data = ServiceStack.Text.JsonSerializer.DeserializeFromString(item);
result.Add(data);
}
return result;
}
}
return null;
}

///

/// 设置缓存过期
///

//////public static void SortedSet_SetExpire(string key, DateTime datetime)
{
using (IRedisClient redis = prcm.GetClient())
{
redis.ExpireEntryAt(key, datetime);
}
}
#endregion
}
}
看起来是不是差不多了,然后redis缓存配置,配置不是大功告成.

在.NET Core中使用CSRedis - XSCAPE' - 博客园

mikel阅读(1476)

来源: 在.NET Core中使用CSRedis – XSCAPE’ – 博客园

CSRedis

为什么选择CSRedis

ServiceStack.Redis 是商业版,免费版有限制;

StackExchange.Redis 是免费版,但是内核在 .NETCore 运行有问题经常 Timeout,暂无法解决;

CSRedis于2016年开始支持.NETCore一直迭代至今,实现了低门槛、高性能,和分区高级玩法的.NETCore redis-cli SDK;

在v3.0版本更新中,CSRedis中的所有方法名称进行了调整,使其和redis-cli保持一致,如果你熟悉redis-cli的命令的话,CSRedis可以直接上手,这样学习成本就降低很多。

安装CSRedis

直接使用Visual Studio中的Nuget包管理器搜索安装

开始之前

使用连接字符串创建redis实例,执行RedisHelper.Initialization()进行初始化。

var csredis = new CSRedis.CSRedisClient("127.0.0.1:6379,password=YourPassword");
RedisHelper.Initialization(csredis);

如果你没有给redis设置密码,那么直接写上ip就行,否则的话要把password写进连接字符串中。

var csredis = new CSRedis.CSRedisClient("127.0.0.1:6379");
RedisHelper.Initialization(csredis);

然后就可以进行redis操作了。

字符串(string)

since Redis keys are strings, when we use the string type as a value too, we are mapping a string to another string.

关于字符串的value

  • value可以用来存储任意格式的数据,如json、jpg甚至是视频文件;
  • value的最大容量是512M;
  • value可以存储3种类型的值:字节串(byte string)、整数(int)、浮点数(double);

其中,整数的取值范围和系统的长整数取值范围相同,在32位的操作系统上,整数就是32位的;在64位操作系统上,整数就是64位有符号整数。浮点数的取值范围和IEEE 754标准的双精度浮点数相同。

使用CSRedis进行简单操作

// 添加字符串键-值对
csredis.Set("hello", "1");
csredis.Set("world", "2");
csredis.Set("hello", "3");

// 根据键获取对应的值
csredis.Get("hello");

// 移除元素
csredis.Del("world");

在对同一个键多次赋值时,该键的值是最后一次赋值时的值,实例中hello对应的值最终为3

由于redis可以对字符串的类型进行“识别”,所以除了对字符串进行增、删、查、之外,我们还可以对整数类型进行自增、自减操作,对字节串的一部分进行读取或者写入。

/*    数值操作    */
csredis.Set("num-key", "24");

// value += 5
csredis.IncrBy("num-key",5); 
// output -> 29

// value -= 10
csredis.IncrBy("num-key", -10); 
// output -> 19

/*    字节串操作    */
csredis.Set("string-key", "hello ");

// 在指定key的value末尾追加字符串
csredis.Append("string-key", "world"); 
// output -> "hello world"

// 获取从指定范围所有字符构成的子串(start:3,end:7)
csredis.GetRange("string-key",3,7)  
// output ->  "lo wo"
    
// 用新字符串从指定位置覆写原value(index:4)
csredis.SetRange("string-key", 4, "aa"); 
// output -> "hellaaword"

非正常情况

  • 对字节串进行自增、自减操作时,redis会报错。
  • 使用AppendSetRange方法对value进行写入时,字节串的长度可能不够用,这时redis会使用空字符(null)将value扩充到指定长度,然后再进行写入操作。

列表(list)

The speed of adding a new element with the LPUSH command to the head of a list with ten elements is the same as adding an element to the head of list with 10 million elements.

使用LPUSH命令,向包含10个元素的列表添加新元素的速度等于向包含一千万个元素的列表添加新元素的速度。

  • 列表可以有序的存储多个字符串(字符串可以重复)等操作;
  • 列表是通过链表来实现的,所以它添加新元素的速度非常快。
// 从右端推入元素
csredis.RPush("my-list", "item1", "item2", "item3", "item4"); 
// 从右端弹出元素
csredis.RPop("my-list");
// 从左端推入元素
csredis.LPush("my-list","LeftPushItem");
// 从左端弹出元素
csredis.LPop("my-list");

// 遍历链表元素(start:0,end:-1即可返回所有元素)
foreach (var item in csredis.LRange("my-list", 0, -1))
{
    Console.WriteLine(item);
}
// 按索引值获取元素(当索引值大于链表长度,返回空值,不会报错)
Console.WriteLine($"{csredis.LIndex("my-list", 1)}"); 

// 修剪指定范围内的元素(start:4,end:10)
csredis.LTrim("my-list", 4, 10);

除了对列表中的元素进行以上简单的处理之外,还可以将一个列表中的元素复制到另一个列表中。在语义上,列表的左端默认为“头部”,列表的右端为“尾部”。

// 将my-list最后一个元素弹出并压入another-list的头部
csredis.RPopLPush("my-list", "another-list");

集合(set)

集合以无序的方式存储各不相同的元素,也就是说在集合中的每个元素的Key都不重复。在redis中可以快速地对集合执行添加、移除等操作。

// 实际上只插入了两个元素("item1","item2")
csredis.SAdd("my-set", "item1", "item1", "item2"); 

// 集合的遍历
foreach (var member in csredis.SMembers("my-set"))
{
    Console.WriteLine($"集合成员:{member.ToString()}");
}

// 判断元素是否存在
string member = "item1";
Console.WriteLine($"{member}是否存在:{csredis.SIsMember("my-set", member)}"); 
// output -> True

// 移除元素
csredis.SRem("my-set", member);
Console.WriteLine($"{member}是否存在:{csredis.SIsMember("my-set", member)}"); 
// output ->  False

// 随机移除一个元素
csredis.SPop("my-set");

以上是对一个集合中的元素进行操作,除此之外还可以对两个集合进行交、并、差操作

csredis.SAdd("set-a", "item1", "item2", "item3","item4","item5");
csredis.SAdd("set-b", "item2", "item5", "item6", "item7");

// 差集
csredis.SDiff("set-a", "set-b"); 
// output -> "item1", "item3","item4"

// 交集
csredis.SInter("set-a", "set-b"); 
// output -> "item2","item5"

// 并集
csredis.SUnion("set-a", "set-b");
// output -> "item1","item2","item3","item4","item5","item6","item7"

另外还可以用SDiffStore,SInterStore,SUnionStore将操作后的结果存储在新的集合中。

散列(hashmap)

在redis中我们可以使用散列将多个键-值对存储在一个redis键上,从而达到将一系列相关数据存放在一起的目的。例如添加一个redis键Article:1001,然后在这个键中存放ID为1001的文章的标题、作者、链接、点赞数等信息。我们可以把这样数据集看作是关系数据库中的行。

// 向散列添加元素
csredis.HSet("ArticleID:10001", "Title", "了解简单的Redis数据结构");
csredis.HSet("ArticleID:10001", "Author", "xscape");
csredis.HSet("ArticleID:10001", "PublishTime", "2019-01-01");

// 根据Key获取散列中的元素
csredis.HGet("ArticleID:10001", "Title");

// 获取散列中的所有元素
foreach (var item in csredis.HGetAll("ArticleID:10001"))
{
    Console.WriteLine(item.Value);
}

HGetHSet方法执行一次只能处理一个键值对,而HMGetHMSet是他们的多参数版本,一次可以处理多个键值对。

var keys = new string[] { "Title","Author","publishTime"};
csredis.HMGet("ID:10001", keys);

虽然使用HGetAll可以取出所有的value,但是有时候散列包含的值可能非常大,容易造成服务器的堵塞,为了避免这种情况,我们可以使用HKeys取到散列的所有键(HVals可以取出所有值),然后再使用HGet方法一个一个地取出键对应的值。

foreach (var item in csredis.HKeys("ID:10001"))
{
	Console.WriteLine($"{item} - {csredis.HGet("ID:10001", item)}");
}

和处理字符串一样,我们也可以对散列中的值进行自增、自减操作,原理同字符串是一样的。

csredis.HSet("ArticleID:10001", "votes", "257");
csredis.HIncrBy("ID:10001", "votes", 40);
// output -> 297

有序集合

有序集合可以看作是可排序的散列,不过有序集合的val成为score分值,集合内的元素就是基于score进行排序的,score以双精度浮点数的格式存储。

// 向有序集合添加元素
csredis.ZAdd("Quiz", (79, "Math"));
csredis.ZAdd("Quiz", (98, "English"));
csredis.ZAdd("Quiz", (87, "Algorithm"));
csredis.ZAdd("Quiz", (84, "Database"));
csredis.ZAdd("Quiz", (59, "Operation System"));

//返回集合中的元素数量
csredis.ZCard("Quiz");

// 获取集合中指定范围(90~100)的元素集合
csredis.ZRangeByScore("Quiz",90,100);

// 获取集合所有元素并升序排序
csredis.ZRangeWithScores("Quiz", 0, -1);

// 移除集合中的元素
csredis.ZRem("Quiz", "Math");

事务

基本事务

事务可以保证一个客户端在执行多条命令时,不被其他客户端打断,这跟关系型数据库的事务是不一样的。事务需要使用MULTIEXEC命令,redis会将被MULTIEXEC所包围的代码依次执行,当该事务结束之后,redis才会处理其他客户端的命令。

管道(pipeline)

redis的事务是通过pipeline实现的,使用pipeline时,客户端会自动调用MULTIEXEX命令,将多条命令打包并一次性地发送给redis,然后redis再将命令的执行结果全部打包并一次性返回给客户端,这样有效的减少了redis与客户端的通信次数,提升执行多次命令时的性能。

var pl = csredis.StartPipe();
for (int i = 0; i < COUNT; i++)
{
	csredis.IncrBy("key-one");
}
pl.EndPipe();
Console.WriteLine($"{csredis.Get("key-one")}");
Console.ReadKey();

Key的过期

redis还允许我们为key设置有效期,当key过期之后,key就不存在了。

redis.Set("MyKey", "hello,world");
Console.WriteLine(redis.Get("MyKey")); 
// output -> "hello,world"

redis.Expire("MyKey", 5); // key在5秒后过期,也可以使用ExpireAt方法让它在指定时间自动过期

Thread.Sleep(6000); // 线程暂停6秒
Console.WriteLine(redis.Get("MyKey"));
// output -> ""

引用

sql server在高并发状态下同时执行查询与更新操作时的死锁问题_ajianchina的博客-CSDN博客_sql server联合查询死锁

mikel阅读(554)

来源: sql server在高并发状态下同时执行查询与更新操作时的死锁问题_ajianchina的博客-CSDN博客_sql server联合查询死锁

最近在项目上线使用过程中使用SQLServer的时候发现在高并发情况下,频繁更新和频繁查询引发死锁。通常我们知道如果两个事务同时对一个表进行插入或修改数据,会发生在请求对表的X锁时,已经被对方持有了。由于得不到锁,后面的Commit无法执行,这样双方开始死锁。但是select语句和update语句同时执行,怎么会发生死锁呢?看完下面的分析,你会明白的…

首先看到代码中使用的查询的方法Select

  1. /// <summary>
  2. /// 根据学生ID查询教师信息。用于前台学生评分主页面显示
  3. /// </summary>
  4. /// <param name=”enTeacherCourseStudent”>教师课程学生关系实体:StudentID</param>
  5. public DataTable QueryTeacherByStudent(TeacherCourseStudentLinkEntity enTeacherCourseStudent)
  6. {
  7. //TODO:QueryTeacherByStudent string strSQL = “SELECT ID, CollegeTeacherID,CollegeTeacherName,TeacherID,TeacherCode,” +
  8. //”TeacherName,CourseID,CourseName,CourseTypeID,CourseTypeName,” +
  9. //”StudentID,StudentName,IsEvluation FROM TA_TeacherCourseStudentLink WITH(NOLOCK) ” +
  10. //”WHERE StudentID = @StudentID”;
  11. //根据学生ID查询该学生对哪些教师评分的SQL语句
  12. string strSQL = “SELECT ID, CollegeTeacherID,CollegeTeacherName,TeacherID,TeacherCode,” +
  13. “TeacherName,CourseID,CourseName,CourseTypeID,CourseTypeName,” +
  14. “StudentID,StudentName,IsEvluation FROM TA_TeacherCourseStudentLink WITH(NOLOCK) “ +
  15. “WHERE StudentID = @StudentID”;
  16. //参数
  17. SqlParameter[] para = new SqlParameter[] {
  18. new SqlParameter(“@StudentID”,enTeacherCourseStudent.StudentID) //学生ID
  19. };
  20. //执行带参数的sql查询语句或存储过程
  21. DataTable dtStuTeacher = sqlHelper.ExecuteQuery(strSql, para, CommandType.Text);
  22. //返回查询结果
  23. return dtStuTeacher;
  24. }

更新方法

  1. /// <summary>
  2. /// 学生对教师评分完毕,是否评估由N变为Y
  3. /// </summary>
  4. /// <param name=”enTeacherCourseStudent”>教师课程学生关系实体:StudentID、TeacherID、CourseID</param>
  5. /// <return>是否修改成功,true成功,false失败</return>
  6. public Boolean EditIsEvaluation(TeacherCourseStudentLinkEntity enTeacherCourseStudent, SqlConnection sqlCon, SqlTransaction sqlTran)
  7. {
  8. //更改是否评估字段为”Y”的sql语句
  9. string strSql = “UPDATE TA_TeacherCourseStudentLink WITH(UPDLOCK) SET IsEvluation=’Y’ WHERE TeacherID=@TeacherID AND StudentID=@StudentID AND CourseID=@CourseID”;
  10. //参数
  11. SqlParameter[] paras = new SqlParameter[]{
  12. new SqlParameter(“@TeacherID”,enTeacherCourseStudent.TeacherID), //教师ID
  13. new SqlParameter(“@StudentID”,enTeacherCourseStudent.StudentID), //学生ID
  14. new SqlParameter(“@CourseID”,enTeacherCourseStudent.CourseID) //课程ID
  15. };
  16. //李社河添加2014年12月29日
  17. Boolean flagModify = false;
  18. try
  19. {
  20. //执行带参数的增删改sql语句或存储过程
  21. flagModify = sqlHelper.ExecNoSelect(strSql, paras, CommandType.Text, sqlCon, sqlTran);
  22. }
  23. catch (Exception e)
  24. {
  25. throw e;
  26. }
  27. //返回修改结果
  28. return flagModify;
  29. }

 

现在分析,在数据库系统中,死锁是指多个用户(进程)分别锁定了一个资源,并又试图请求锁定对方已经锁定的资源,这就产生了一个锁定请求环,导致多个用户(进程)都处于等待对方释放所锁定资源的状态。还有一种比较典型的死锁情况是当在一个数据库中时,有若干个长时间运行的事务执行并行的操作,当查询分析器处理一种非常复杂的查询例如连接查询时,那么由于不能控制处理的顺序,有可能发生死锁现象。

那么,什么导致了死锁?

现象图

通过查询SQLServer的事务日志视图,发生的错误日志视图知道是在高并发的情况下引发的update和select发生的死锁,接下来我们看例子;

  1. CREATE PROC p1 @p1 int AS
  2. SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1
  3. GO
  4. CREATE PROC p2 @p1 int AS
  5. UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1
  6. UPDATE t1 SET c2 = c2-1 WHERE c1 = @p1
  7. GO

p1没有insert,没有delete,没有update,只是一个select,p2才是update。那么,什么导致了死锁?

  1. 需要从事件日志中,看sql的死锁信息:
  2. Spid X is running this query (line 2 of proc [p1], inputbuffer “… EXEC p1 4 …”):
  3. SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1
  4. Spid Y is running this query (line 2 of proc [p2], inputbuffer “EXEC p2 4”):
  5. UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1
  6. The SELECT is waiting for a Shared KEY lock on index t1.cidx. The UPDATE holds a conflicting X lock.
  7. The UPDATE is waiting for an eXclusive KEY lock on index t1.idx1. The SELECT holds a conflicting S lock.

首先,我们看看p1的执行计划。怎么看呢?可以执行set statistics profile on,这句就可以了。下面是p1的执行计划

  1. SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1
  2. |–Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1002], [t1].[c1]))
  3. |–Index Seek(OBJECT:([t1].[idx1]), SEEK:([t1].[c2] >= [@p1] AND [t1].[c2] <= [@p1]+(1)) ORDERED FORWARD)
  4. |–Clustered Index Seek(OBJECT:([t1].[cidx]), SEEK:([t1].[c1]=[t1].[c1] AND [Uniq1002]=[Uniq1002]) LOOKUP ORDERED FORWARD)

我们看到了一个nested loops,第一行,利用索引t1.c2来进行seek,seek出来的那个rowid,在第二行中,用来通过聚集索引来查找整行的数据。这是什么?就是bookmark lookup啊!为什么?因为我们需要的c2、c3不能完全的被索引t1.c1带出来,所以需要书签查找。

好,我们接着看p2的执行计划。

  1. UPDATE t1 SET c2 = c2+1 WHERE c1 = @p1
  2. |–Clustered Index Update(OBJECT:([t1].[cidx]), OBJECT:([t1].[idx1]), SET:([t1].[c2] = [Expr1004]))
  3. |–Compute Scalar(DEFINE:([Expr1013]=[Expr1013]))
  4. |–Compute Scalar(DEFINE:([Expr1004]=[t1].[c2]+(1), [Expr1013]=CASE WHEN CASE WHEN …
  5. |–Top(ROWCOUNT est 0)
  6. |–Clustered Index Seek(OBJECT:([t1].[cidx]), SEEK:([t1].[c1]=[@p1]) ORDERED FORWARD)

通过聚集索引的seek找到了一行,然后开始更新。这里注意的是,update的时候,它会申请一个针对clustered index的X锁的。

实际上到这里,我们就明白了为什么update会对select产生死锁。update的时候,会申请一个针对clustered index的X锁,这样就阻塞住了(注意,不是死锁!)select里面最后的那个clustered index seek。死锁的另一半在哪里呢?注意我们的select语句,c2存在于索引idx1中,c1是一个聚集索引cidx。问题就在这里!我们在p2中更新了c2这个值,所以sqlserver会自动更新包含c2列的非聚集索引:idx1。而idx1在哪里?就在我们刚才的select语句中。而对这个索引列的更改,意味着索引集合的某个行或者某些行,需要重新排列,而重新排列,需要一个X锁。

问题就这样被发现了,就是说,某个query使用非聚集索引来select数据,那么它会在非聚集索引上持有一个S锁。当有一些select的列不在该索引上,它需要根据rowid找到对应的聚集索引的那行,然后找到其他数据。而此时,第二个的查询中,update正在聚集索引上忙乎:定位、加锁、修改等。但因为正在修改的某个列,是另外一个非聚集索引的某个列,所以此时,它需要同时更改那个非聚集索引的信息,这就需要在那个非聚集索引上,加第二个X锁。select开始等待update的X锁,update开始等待select的S锁,死锁,就这样发生鸟。

添加了针对select和update同一个表的非聚集索引解决问题。那么,为什么我们增加了一个非聚集索引,死锁就消失鸟?我们看一下,按照上文中自动增加的索引之后的执行计划:

 

  1. SELECT c2, c3 FROM t1 WHERE c2 BETWEEN @p1 AND @p1+1
  2. |–Index Seek(OBJECT:([deadlocktest].[dbo].[t1].[_dta_index_t1_7_2073058421__K2_K1_3]), SEEK:([deadlocktest].[dbo].[t1].[c2] >= [@p1] AND [deadlocktest].[dbo].[t1].[c2] <= [@p1]+(1)) ORDERED FORWARD)

 

哦,对于clustered index的需求没有了,因为增加的覆盖索引已经足够把所有的信息都select出来。就这么简单。
实际上,在sqlserver 2005中,如果用profiler来抓eventid:1222,那么会出现一个死锁的图,很直观的说。

下面的方法,有助于将死锁减至最少(详细情况,请看SQLServer联机帮助,搜索:将死锁减至最少即可)。

·  按同一顺序访问对象。

·  避免事务中的用户交互。

·  保持事务简短并处于一个批处理中。

·  使用较低的隔离级别。

·  使用基于行版本控制的隔离级别。

–    将 READ_COMMITTED_SNAPSHOT 数据库选项设置为 ON,使得已提交读事务使用行版本控制。

–    使用快照隔离。

·   使用绑定连接。

下面我们在测试的同时开启trace profiler跟踪死锁视图(locks:deadlock graph).(当然也可以开启跟踪标记,或者应用扩展事件(xevents)等捕捉死锁)

创建测试对象code

  1. create table testklup
  2. ( clskey int not null,
  3. nlskey int not null,
  4. cont1 int not null,
  5. cont2 char(3000)
  6. )
  7. create unique clustered index inx_cls on testklup(clskey)
  8. create unique nonclustered index inx_nlcs on testklup(nlskey) include(cont1)
  9. insert into testklup select 1,1,100,‘aaa’
  10. insert into testklup select 2,2,200,‘bbb’
  11. insert into testklup select 3,3,300,‘ccc’

开启会话1 模拟高频update操作

—-模拟高频update操作

  1. declare @i int
  2. set @i=100
  3. while 1=1
  4. begin
  5. update testklup set cont1=@i
  6. where clskey=1
  7. set @i=@i+1
  8. end

开启会话2 模拟高频select操作

—-模拟高频select操作

  1. declare @cont2 char(3000)
  2. while 1=1
  3. begin
  4. select @cont2=cont2 from testklup where nlskey=1
  5. end

此时开启会话2执行一小段时间时我们就可以看到类似错误信息:

而在我们开启的跟踪中捕捉到了死锁.

死锁分析:可以看出由于读进程(108)请求写进程(79)持有的X锁被阻塞的同时,写进程(79)又申请读进程(108)锁持有的S锁.读执行计划图,写执行计划图。

(由于在默认隔离级别下(读提交)读申请S锁只是瞬间过程,读完立即释放,不会等待事务完成),所以在并发,执行频率不高的情形下不易出现.但我们模拟的高频情况使得S锁获得频率非常高,此时就出现了仅仅两个会话,一个读,一个写就造成了死锁现象.

死锁原因:读操作中的键查找造成的额外锁(聚集索引)需求

解决方案:在了解了死锁产生的原因后,解决起来就比较简单了.

我们可以从以下几个方面入手.

a 消除额外的键查找锁需的锁

b 读操作时取消获取锁

a.1我们可以创建覆盖索引使select语句中的查询列包含在指定索引中

CREATE NONCLUSTERED INDEX [inx_nlskey_incont2] ON [dbo].[testklup] ([nlskey] ASC) INCLUDE ( [cont2])

a.2 根据查询需求,分步执行,通过聚集索引获取查询列,避免键查找.’

declare @cont2 char(3000) declare @clskey intwhile 1=1 begin  select @clskey=clskey from testklup where nlskey=1     select @cont2=cont2 from testklup where clskey=@clskey end

b 通过改变隔离级别,使用乐观并发模式,读操作时源行无需锁

  1. declare @cont2 char(3000)
  2. while 1=1
  3. begin
  4. select @cont2=cont2 from testklup with(nolock) where nlskey=1
  5. end

结束语.我们在解决问题时,最好弄清问题的本质原因,通过问题点寻找出适合自己的环境的解决方案再实施.

SqlServer性能优化 提高并发性能(八) - 孙丽媛 - 博客园

mikel阅读(1156)

来源: SqlServer性能优化 提高并发性能(八) – 孙丽媛 – 博客园

并发访问:

当多个线程访问同一个资源,会产生并发性问题

并发控制与处理:

乐观并发控制:一种方式是“后来的更新者获胜”   这意味着先来的用户提交的值会在没有察觉的情况下丢失。

为记录加锁以阻止其他事物访问某些记录,是避免产生并发冲突的一种技术

 

悲观并发控制:

1.一个线程操作表,造成整个表被锁定

2.其他线程访问与操作任何记录都被阻止

3.其他线程可以添加记录

4.最小的吞吐量、最差的性能

 

事物恢复与检查点:

 

事物指南:

1.事物尽量简单

2.事物尽量只包含必要的语句;验证与查询等语句放置在事物之外

3.避免事物与用户的交互

 

避免锁的问题:

1.丢失的更新

2.脏读

3.不一致性分析

4.幻象集

 

锁的粒度:

锁的类型:

 

平衡乐观与悲观并发访问:

1.建立合适的索引

2.操作语句尽量放到短事物中

3.操作语句尽量指定特定的筛选条件、窄的访问列

4.索引查询提示

5.应用程序访问模式

 

创建表:

1
2
3
4
5
6
7
8
create table Employee(id int identity(1,1),name varchar(500),age int)
insert   Employee values('caochao',34)
insert   Employee values('ligang',28)
insert Employee values('zhangqing',36)
insert Employee values('huang',23)
go
begin tran
update Employee set age=age+1 where age>=30

新建一个查询窗口:

1
select *from Employee

访问就被阻塞掉了。没有结果。

执行删改查的方法都没什么用。

1
2
3
4
    select from Employee where age>30
select from name,age from employee where age<30
update employee set age=age+1 where age<30
delete employee where age=20

添加是可以的:

1
insert Employee values('xili',50)

查看锁的命令:

1
sp_lock

 

进行回滚把锁释放:

1
rollback tran

没有排他锁:

创建非聚簇索引:

1
create nonclustered index nc_Employee_age on Employee(age) include(name)

模拟开启事物不结束:

1
2
begin tran
 update Employee set age=age+1 where age>=30

锁的情况:

 

访问如下两个语句不行:

1
2
    select *from Employee
select from Employee where age>30

这条语句可以访问(锁住的行不能访问,不锁的是可以访问的):

1
select  name,age from  employee where age<30

在非聚集的索引页面进行了age进行了物理排序,访问的是在被锁住行排序的上面。并不需要穿透锁住的行

这句语句是不能执行的:(无法穿透>30的记录)

1
select from Employee where age<20

在执行跟新语句:

1
update employee set age=age+1 where age<30

无法执行,查看执行计划,直接进行了表扫描:

在执行一条查询语句:执行的表扫描  不能进行查询

1
select from Employee where age<30

 

执行删除语句:(可以)  执行的是非聚集索引

1
delete employee where age=20

 

应用索引提示的方法:

1
select from  employee with(index=nc_Employee_age) where age<30

查看执行计划:

 

update 不能用索引提示:

硬性访问: readpast  绕过被排他锁锁住的行,直接往下面进行访问

1
select from Employee with(readpast)

结果:(只能访问不被锁住的)

 

SQL Server数据库(时间戳timestamp)类型 (转载) - PowerCoder - 博客园

mikel阅读(1050)

来源: SQL Server数据库(时间戳timestamp)类型 (转载) – PowerCoder – 博客园

timestamp介绍


 

  1. 公开数据库中自动生成的唯一二进制数字的数据类型。
  2. timestamp 通常用作给表行加版本戳的机制。
  3. 存储大小为 8 个字节。 不可为空的 timestamp 列在语义上等价于 binary(8) 列。可为空的 timestamp 列在语义上等价于 varbinary(8) 列。这将导致在C#程序中获取到的timestamp类型则变成了byte[]类型。所以如果我们需要从数据库中获取并使用这个时间戳的话就必需经过转换。
  4. timestamp 数据类型只是递增的数字,不保留日期或时间。 若要记录日期或时间,请使用 datetime 数据类型。
  5. 一个表只能有一个 timestamp 列。每次插入或更新包含 timestamp 列的行时,timestamp 列中的值均会更新。对行的任何更新都会更改 timestamp 值。
  6. 总结SQL Server timestamp 数据类型与时间和日期无关SQL Server timestamp 是二进制数字,它表明数据库中数据修改发生的相对顺序。实现 timestamp 数据类型最初是为了支持 SQL Server 恢复算法。每次修改页时,都会使用当前的 @@DBTS 值对其做一次标记,然后 @@DBTS 加1。这样做足以帮助恢复过程确定页修改的相对次序,但是 timestamp 值与时间没有任何关系。@@DBTS 返回当前数据库最后使用的时间戳值。插入或更新包含 timestamp 列的行时,将产生一个新的时间戳值。

 

如何使用SQL语句插入timestamp字段值?
我们从上面的timestamp定义中知道了timestamp这个值一般都是数据库自动添加和修改的,相当于自动增长标识一样(而且执行update修改语句这个字段也会自动更新),所以一般这个字段我们只做查询操作。如果要更新这个字段则会提示这个错误信息:不能更新时间戳列。但是这个字段是可以手动添加的,不过也只能使用DEFALUT字段(default字段为SQL Server数据库的一个默认值),如果传入其它值则会提示错误信息:不能将显式值插入时间戳列。请对列列表使用 INSERT 来排除时间戳列,或将 DEFAULT 插入时间戳列。

首先我们创建一个带TIMESTAMP类型的表[tb_Ts]:

CREATE TABLE [tb_Ts]([TS] TIMESTAMP)

 

下面是添加timestamp的SQL语句:

INSERT INTO [tb_Ts]([TS]) VALUES(DEFAULT)

 

我们可以将timestamp转换为十六进制或bigint,来方便进行比较和查看:

复制代码
SELECT TS
,CAST(TS AS VARBINARY(8)) AS 'timestamp转十六进制' 
,CONVERT(BIGINT,TS) AS 'timestamp转bigint类型'
FROM tb_Ts
WHERE
CONVERT(BIGINT,TS)>=18004
复制代码

结果:

 

当然,我们也可以将bigint转换为timestamp类型,来进行比较:

复制代码
DECLARE @TsValue BIGINT=82006

SELECT TS
,CAST(TS AS VARBINARY(8)) AS 'timestamp转十六进制' 
,CONVERT(BIGINT,TS) AS 'timestamp转bigint类型'
FROM tb_Ts
WHERE
TS<=CONVERT(TIMESTAMP,@TsValue)
复制代码

或者:

复制代码
DECLARE @TsValue BIGINT=82006
DECLARE @Ts TIMESTAMP=CONVERT(TIMESTAMP,@TsValue)

SELECT TS
,CAST(TS AS VARBINARY(8)) AS 'timestamp转十六进制' 
,CONVERT(BIGINT,TS) AS 'timestamp转bigint类型'
FROM tb_Ts
WHERE
TS<=@Ts
复制代码

结果:

 

 

时间戳字段在数据库中起什么作用:


 

1.给一个表加一个时间戳字段(timestamp),假设某条记录同时被两个人A和B读取并且正在修改。A先修改完成然后保存了,然后B再保存的时候,会由于时间戳不一致(因为A之前先保存修改了时间戳)导致B保存失败。timestamp是数据库记录版本控制的好东西,Linq to sql, entity framework都有很好的支持。

 

我们这里举一个EF Core的例子,假如我们在数据库中有一个表Book,它有一个timestamp类型的列TimeStampNumber,如下所示:

复制代码
CREATE TABLE [dbo].[Book](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [BookCode] [nvarchar](20) NULL,
    [PersonCode] [nvarchar](20) NULL,
    [BookName] [nvarchar](50) NULL,
    [ISBN] [nvarchar](20) NULL,
    [TimeStampNumber] [timestamp] NULL,
 CONSTRAINT [PK_Book] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
复制代码

我们用EF Core的DB First模式,将表Book映射为实体后,其生成的实体类Book如下:

复制代码
public partial class Book
{
    public int Id { get; set; }
    public string BookCode { get; set; }
    public string PersonCode { get; set; }
    public string BookName { get; set; }
    public string Isbn { get; set; }
    public byte[] TimeStampNumber { get; set; }
}
复制代码

可以看到数据库中timestamp类型的列TimeStampNumber,在EF Core中的确被映射为了byte[]类型。

我们还可以看看实体类Book的Fluent API如下:

复制代码
modelBuilder.Entity<Book>(entity =>
{
    entity.Property(e => e.Id).HasColumnName("ID");

    entity.Property(e => e.BookCode).HasMaxLength(20);

    entity.Property(e => e.BookName).HasMaxLength(50);

    entity.Property(e => e.Isbn)
        .HasColumnName("ISBN")
        .HasMaxLength(20);

    entity.Property(e => e.PersonCode).HasMaxLength(20);

    entity.Property(e => e.TimeStampNumber).IsRowVersion();
});
复制代码

可以看到EF Core用IsRowVersion方法对属性TimeStampNumber作了标识。

 

如何在C#中将byte[]类型转换为long类型,请查看这里

 

2.数据库优化:当在处理几十万条并发数据时,我们就可以在使用最频繁的表中添加一列字段,类型为timestamp,添加完毕后系统会自动生成相应的唯一值,如果数据记录有任何改动,timestamp值也会做相应的调整。

 

 

最近被这个时间戳虐到了,,,这几篇博文解释的很好,感谢博主
1.http://www.cnblogs.com/iampkm/p/4082916.html
2.http://www.cnblogs.com/windows/articles/2149701.html

Fiddler抓取抖音视频数据_lx-CSDN博客_fiddler 抖音

mikel阅读(2337)

来源: Fiddler抓取抖音视频数据_lx-CSDN博客_fiddler 抖音

本文仅供参考学习,禁止用于任何形式的商业用途,违者自行承担责任。

准备工作:

(1)、手机(安卓、ios都可以)/安卓模拟器,今天主要以安卓模拟器为主,操作过程一致。

(2)、抓包工具:Fiddel 下载地址:(https://www.telerik.com/download/fiddler )

(3)、编程工具:pycharm

(4)、安卓模拟器上安装抖音(逍遥安装模拟器)

一、fiddler配置
在tools中的options中,按照图中勾选后点击Actions

配置远程链接:

选择允许监控远程链接,端口可以随意设置,只要别重复就行,默认8888

然后:重启fiddler!!!这样配置才能生效。

二、安卓模拟器/手机配置
首先查看本机的IP:在cmd中输入ipconfig,记住这个IP

手机确保和电脑在同一局域网下。

手机配置:配置已连接的WiFi,代理选择手动,然后输入上图ip端口号为8888

模拟器配置:设置中长按已连接wifi,代理选择手动,然后输入上图ip端口号为8888

代理设置好后,在浏览器中输入你设置的ip:端口,例如10.10.16.194:8888,就会打开fiddler的页面。然后点击fiddlerRoot certificate安装证书,要不手机会认为环境不安全。

证书名称随便设,可能还需要设置一个锁屏密码。

接下来就可以在fiddler中抓到手机/模拟器软件的包了。

三、抖音抓包
打开抖音,然后观察fiddler中所有的包
其中有个包,包类型为json(json就是网页返回的数据,具体百度),主机地址如图,包大小一般不小,这个就是视频包。

点击这个json包,在fidder右侧,点击解码,我们将视频包的json解码

解码后:点击aweme_list,其中每个大括号代表一个视频,这个和bilibili弹幕或者快手一样,每次加载一点出来,等你看完预加载的,再重新加载一些。
Json是一个字典,我们的视频链接在:aweme_list中,每个视频下的video下的play_addr下的url_list中,一共有6个url,是完全一样的视频,可能是为了应付不同环境,但是一般第3或4个链接的视频不容易出问题,复制链接,浏览器中粘贴就能看到视频了。
接下来解决几个问题,

1、视频数量,每个包中只有这么几个视频,那如何抓取更多呢?

这时候需要借助模拟器的模拟鼠标翻页,让模拟器一直翻页,这样就不断会出现json包了。

2、如何json保存在本地使用

一种方法可以手动复制粘贴,但是这样很low。

所以我们使用fidder自带的脚本,在里面添加规则,当视频json包刷出来后自动保存json包。

自定义规则包:

链接:https://pan.baidu.com/s/1wmtUUMChzuSDZFYGSyUhCg

提取码:7z0l

点击规则脚本,然后将自定义规则放在如图所示位置:

这个脚本有两点需要修改的:

(1)第一行的网址:

这个是从视频包的url中摘出来的,抖音会时不时更新这个url,所以不能用了也要去更新:

比如现在的已经和昨天不同了,记着修改。

(2)路径,那个是我设置json包保存的地址,自己一定要去修改,并创建文件夹,修改完记着点保存。
打开设置好模拟器和脚本后,等待一会,就可以看到文件夹中保存的包了:

四、爬虫脚本

接下来在pycharm中写脚本获取json包里的视频链接:

导包:

import os,json,requests

伪装头:
headers = {‘User-Agent’: ‘Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/72.0.3626.119 Safari/537.36’}
逻辑代码:

运行代码:
效果:

源码:

import os,json,requests
#伪装头
headers = {‘User-Agent’: ‘Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/72.0.3626.119 Safari/537.36’}

videos_list = os.listdir(‘C:/Users/HEXU/Desktop/抖音数据爬取/抖音爬取资料/raw_data/’) #获取文件夹内所有json包名

count = 1 #计数,用来作为视频名字

for videos in videos_list: #循环json列表,对每个json包进行操作
a = open(‘./抖音爬取资料/raw_data/{}’.format(videos),encoding=’utf-8′) #打开json包
content = json.load(a)[‘aweme_list’] #取出json包中所有视频

for video in content: #循环视频列表,选取每个视频
video_url = video[‘video’][‘play_addr’][‘url_list’][4] #获取视频url,每个视频有6个url,我选的第5个
videoMp4 = requests.request(‘get’,video_url,headers=headers).content #获取视频二进制代码
with open(‘./抖音爬取资料/VIDEO/{}.mp4′.format(count),’wb’) as f: #以二进制方式写入路径,记住要先创建路径
f.write(videoMp4) #写入
print(‘视频{}下载完成’.format(count)) #下载提示
count += 1 #计数+1
————————————————
版权声明:本文为CSDN博主「考古学家lx」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/weixin_43582101/article/details/89600007

SqlServer性能优化 查询和索引优化(十二) - 孙丽媛 - 博客园

mikel阅读(530)

来源: SqlServer性能优化 查询和索引优化(十二) – 孙丽媛 – 博客园

查询优化的过程:

查询优化:

功能:分析语句后最终生成执行计划

分析:获取操作语句参数

索引选择

Join算法选择

 

创建测试的表:

1
select into EmployeeOp from AdventureWorks2014.HumanResources.Employee

建立非聚集索引:

1
create nonclustered index  nc_employee_vacationhours on employeeop(vacationhours)

执行语句:

1
select LoginID,JobTitle,VacationHours,SickLeaveHours from EmployeeOp where VacationHours>40   --table  scan>10%

执行语句:

1
select LoginID,JobTitle,VacationHours,SickLeaveHours from EmployeeOp where VacationHours>99   --nonclustered index

查询结果集的数据范围影响对索引的选择。

 

两个查询条件:

1
2
    select LoginID,JobTitle,VacationHours,SickLeaveHours from EmployeeOp where VacationHours>40
and SickLeaveHours>60--scan

 

SQLServer 的查询结果集会认为用哪个列查询的结果少,就选择哪个。在去and 的第二个结果,最终返回结果集。

1
2
select LoginID,JobTitle,VacationHours,SickLeaveHours from EmployeeOp where VacationHours>99
and SickLeaveHours>60--nonclustered index nc_employee_vacationhours

单独选择:

1
select LoginID,JobTitle,VacationHours,SickLeaveHours from EmployeeOp where SickLeaveHours>60--table scan

 

创建非聚集索引:

1
create nonclustered index nc_employee_sickleavehours on EmployeeOp(SickLeaveHours)

执行:

1
select LoginID,JobTitle,VacationHours,SickLeaveHours from EmployeeOp where SickLeaveHours>60--table scan

执行:

1
select LoginID,JobTitle,VacationHours,SickLeaveHours from EmployeeOp where SickLeaveHours>88--nc_employee_sickleavehours

执行:

1
2
select LoginID,JobTitle,VacationHours,SickLeaveHours from EmployeeOp where VacationHours>99
and SickLeaveHours>88--nonclustered index nc_employee_vacationhours

 

在两列上做一个索引:

1
create nonclustered index nc_employee_vacationsickleavehours on EmployeeOp(VacationHours,SickLeaveHours)

执行语句:(使用了符合索引)

1
2
select LoginID,JobTitle,VacationHours,SickLeaveHours from EmployeeOp where VacationHours>99
and SickLeaveHours>88-- nc_employee_vacationsickleavehours

执行:(随机)

1
select LoginID,JobTitle,VacationHours,SickLeaveHours from EmployeeOp where VacationHours>99<br>--nc_employee_vacationhours  nc_employee_vacationsickleavehours

执行:

1
2
select LoginID,JobTitle,VacationHours,SickLeaveHours from EmployeeOp where SickLeaveHours>88
--nc_employee_sickleavehours

执行:

1
select from EmployeeOp where SickLeaveHours>88 --nc_employee_sickleavehours

创建聚集索引:

1
create clustered index c_Employee_BusinessEntityID on EmployeeOp(BusinessEntityID)

执行:

1
select from EmployeeOp where SickLeaveHours>88 --nc_employee_sickleavehours key连 c_ID聚集索引

 

建立include索引:

1
2
create nonclustered index nc_employee_vacationsickleavehoursinclude on EmployeeOp(VacationHours,
SickLeaveHours) include(LoginID,JobTitle)

执行:

1
2
select LoginID,JobTitle,VacationHours,SickLeaveHours from EmployeeOp where VacationHours>99
and SickLeaveHours>88 --nc_employee_vacationsickleavehoursinclude

执行:(采用覆盖索引)

1
2
select LoginID,JobTitle,VacationHours,SickLeaveHours from EmployeeOp where VacationHours>60
and SickLeaveHours>10--nc_employee_vacationsickleavehoursinclude--0.0048<br><br>select LoginID,JobTitle,VacationHours,SickLeaveHours from EmployeeOp where VacationHours>60<br>--nc_employee_vacationsickleavehoursinclude

执行:(指定使用的索引)

1
2
3
select LoginID,JobTitle,VacationHours,SickLeaveHours from EmployeeOp
with(index=0) where VacationHours>60
and SickLeaveHours>10

 

 

索引的优化:

1
select from EmployeeOp<br>--创建非聚集索引<br>create nonclustered index nc_EmployeeOp on employeeop (VacationHours,SickLeaveHours) include (LoginID,JobTitle)<br><br>create nonclustered index nc_EmployeeOp_Vacation on employeeop(VacationHours)<br>include(LoginID,JobTitle)<br><br>--创建聚集索引<br>set statistics io on<br>create clustered index c_Employee_id on employeeop(BusinessEntityID)  --7,9,9<br>set statistics io off

总结:先创建聚集索引在创非聚集索引

 

聚集索引键宽与窄:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create table temptable(c1 int not null,c2 int)
 
 declare @c int
  set @c=0
  while @c<50000
  begin
  insert temptable values(@c,@c)
  set @c=@c+1
  end
create clustered index c_temptable_c1 on temptable(c1)
set statistics io on
select from temptable where c1<=25000  --0.07
set statistics io off

 

创建Guid的列:

1
2
3
4
5
6
7
8
9
10
11
12
create table temptable(c1 uniqueidentifier,c2 int)
declare @c int
  set @c=0
  while @c<50000
  begin
  insert temptable values(newid(),@c)
  set @c=@c+1
  end
  create clustered index c_temptable_c1 on temptable(c1)
set statistics io on
select from temptable where c1<='D144242D-BFA3-4A8C-8DCE-C35A880E8BBE'  --0.11
set statistics io off

 

 

索引设计建议:
1.where子句与连接条件列(where子句后面的列建立非聚集索引,有多列查询做成组合索引,并用inclued的方式把经常访问的列信息给包含到非聚集索引的页集,查询用到链接时(join):join的条件列做到非聚集索引中)

2.使用窄索引:索引列少、索引列数据类型空间少

1.减少IO数量

2.提高缓存效率

3.减少数据存储的空间

4.动态管理视图: sys.dm_db_index_physical_stats

选择性能高的列应该创建索引,如果有多列筛选,并尽量放置经常筛选的列和低密度的列到组合索引前面

int类型上创建索引与char 型上创建索引

 

1
2
3
4
5
6
7
create nonclustered  index nc_employee_vacationsickleavehours on  employeeop(vacationhours,
sickleavehours) include(LoginID,JobTitle)
create nonclustered index nc_employee_sickvacationleavehours  on employeeop(sickleavehours,vacationhours)
include(LoginID,JobTitle)
select LoginID,JobTitle from EmployeeOp where VacationHours>40 and SickLeaveHours>90  -- nc_sickleavevacation

1
select loginid,jobtitle from EmployeeOp where VacationHours>99 and SickLeaveHours>10--nc_vacationsickleave

总结:会自动进行筛选与and的顺序无关。(谁的选择性度高)

 

非聚集索引:RID指针指向堆得行标识符或聚集索引的键值

如果有非聚集索引,一定要创建一个聚集索引

先创建聚集索引,在创建非聚集索引

保持聚集索引窄:提高非聚集索引性能,提高聚集索引性能

使用聚集索引的时机:

1.Group by列

2.Order by 列

3.没有针对某个筛选条件的非聚集索引

不合适使用聚集索引:

1.索引列值频繁跟新:频繁跟新非聚集索引降低性能

2.并发的大量的插入

 

如果非聚集索引需要书签查找,则建议通过聚集索引查找

建议创建覆盖索引

不适合使用非聚集索引:

1.需要获取大量的行

2.需要获取大量的字段

交叉索引:针对筛选条件分别建立非聚集索引,在查询时,获得两个子集的索引交叉,解决覆盖索引非常宽的问题

建议使用过滤索引:针对查询必然需要筛选掉的条件做成索引的过滤条件

1
create nonclustered index nc_employee_sickvacationleavehours on employeeop(sickleavehours,vacationhours) include (LoginID,JobTitle) where salariedFlag=1

恰当使用索引视图使连接与聚合实物化,平衡查询性能提升与维护视图性能开销

 

复合索引每列可以不按照相同排序规则

可以在计算列上创建索引,建议使用持久化的计算列

指定并行度CPU个数、制定联机索引

经常使用数据库引擎优化顾问

尽量减少书签查找

 

 

查询优化统计方面的应用:

查询优化器对索引的选择依赖于统计

统计被自动创建和更新,也可以设置异步更新统计

通过Profiler跟踪统计事件

过时统计造成查询优化器无法选择最优的执行计划

自动创建统计也会在非索引列上创建统计

 

跟新自动统计:

 

SQL完成情况:

 

开启跟踪:

 

验证事件:

创建跟踪统计的表:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
create table StatisticsTB(c1 int identity(1,1),c2 int)
declare @n int
set @n=0
while @n<5000
begin
 insert StatisticsTB values(@n)
 set @n=@n+1
end
create nonclustered index  nc_StatisticsTB_t2 on StatisticsTB(c2)
declare @n int
set @n=5001
while @n<50000
begin
insert StatisticsTB values(@n)
set @n=@n+1
end
select from StatisticsTB where c2<10--index
 select from StatisticsTB where c2>10--Scan

 

自动统计功能出现故障:

1
2
3
4
5
6
7
8
9
--自动统计出现故障后
declare @n int
set @n=50001
while @n<130000
begin
insert StatisticsTB values(@n)
set @n=@n+1
end

本来是表扫描的就弄成索引。

1
select from StatisticsTB where c2>4990--index

查看统计信息:

1
2
3
4
--查看统计信息
dbcc show_statistics('Employeeop',nc_Employee_vacation)--密度:0.01
dbcc show_statistics('Employeeop',nc_Employee_vacationsickleave)--密度:0.009

 

更新统计:

1
2
3
4
--更新统计
  use HRDB
go
Sp_Updatestats

 

–创建统计:

1
create statistics s_Employee_c2 on StatisticsTB(c2)

 

 

在非索引列上创建统计:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
create table t1(c1 int identity(1,1),c2 int)
insert t1 values(2)
declare @count int
set @count=0
while @count<1000
begin
insert t1 values(1)
set @count=@count+1
end
create table t2(c1 int identity(1,1),c2 int)
insert t2 values(1)
declare @count int
set @count=0
while @count<1000
begin
insert t1 values(2)
set @count=@count+1
end

 

关闭统计的情况:

1
2
select t.c1,t.c2,tt.c1,tt.c2 from t1 as t inner join t2 as tt on
t.c2=tt.c2--0.045

 

 

删除重新创建表:

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
drop table t1
drop table t2
create table t1(c1 int identity(1,1),c2 int)
insert t1 values(2)
declare @count int
set @count=0
while @count<1000
begin
insert t1 values(1)
set @count=@count+1
end
create table t2(c1 int identity(1,1),c2 int)
insert t2 values(1)
declare @count int
set @count=0
while @count<1000
begin
insert t1 values(2)
set @count=@count+1
end
select t.c1,t.c2,tt.c1,tt.c2 from t1 as t inner join t2 as tt on
t.c2=tt.c2--0.045

 

统计建议:

 

查看索引是否有碎片:

1
2
3
--查看索引是否有碎片
select from sys.dm_db_index_physical_stats(db_id('HRDB'),object_id('EmployeeOp'),null,
null,'Detailed')

 

做碎片的整理:

1
--对页面进行重排:<br>alter index nc_Employee_Vacation on EmployeeOp Reorganize

重建索引:

1
alter index nc_Employee_Vacation on employeeop rebuild with(fillfactor=40)

填充因子的方式重建索引:

1
2
--指定填充因子重建索引
create nonclustered index nc_Employee_Vacation on Employeeop (VacationHours) with(fillfactor=40,drop_existing=on)

 

 

查询优化器Join的选择:

1.嵌套循环的join  NestedLoop Join

2.合并的join   Merge Join算法

1.链接表记录数都比较多,并且针对连接列进行了物理排序

2.Inner表的行有范围约束

3.Hash join算法

 

对Join算法的选择:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create table parenttb(c1 int,name varchar(500))
declare @c int
set @c=0
while @c<10
begin
insert parenttb values(@c,GETDATE())
set @c=@c+1
end
go
create table subtb(c1 int,cardid uniqueidentifier)
declare @c int
set @c=0
while @c<250
begin
insert subtb values(@c,NEWID())
set @c=@c+1
end

执行语句:

1
select p.name,s.cardid from parenttb as p inner join subtb  as s  on p.c1=s.c1   --hash --0.29  io:

 

手工指定:

1
2
3
4
   set statistics io on
select p.name,s.cardid from parenttb as p inner loop join subtb as s
 on p.c1=s.c1--nested loop --0.21 io:p 1 s 20
set statistics io off

 

 

多添加一些记录:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create table parenttb(c1 int,name varchar(500))
declare @c int
set @c=0
while @c<1000
begin
insert parenttb values(@c,getdate())
set @c=@c+1
end
go
create table subtb(c1 int,cardid uniqueidentifier)
declare @c int
set @c=0
while @c<25000
begin
insert subtb values(@c,NEWID())
set @c=@c+1
end

执行语句:

1
2
3
4
5
6
7
set statistics io on
select p.name,s.cardid from parenttb as p inner join subtb as on p.c1=s.c1--hash --0.5 io:p 7 s 140
set statistics io off
set statistics io on
select p.name,s.cardid from parenttb as p inner loop join subtb as on p.c1=s.c1--loop --64 io:p 7 s 560
set statistics io off

 

 

创建唯一的聚集索引:

1
2
3
--创建唯一的聚集索引
create unique clustered index c_parent_c1  on Parenttb(c1)
create unique clustered index c_sub_c1  on Subtb(c1)

执行:

1
2
3
set statistics io on
select p.name,s.cardid from parenttb as p inner join subtb as on p.c1=s.c1--Merge --0.16 io:p 6 s 7
set statistics io off

 

 

SqlServer性能优化 即席查询(十三) - 孙丽媛 - 博客园

mikel阅读(626)

来源: SqlServer性能优化 即席查询(十三) – 孙丽媛 – 博客园

执行计划,查询类别:

1.即席查询     2.预定义查询

1
2
3
4
5
6
7
8
9
10
select c.EnglishProductCategoryName,p.EnglishProductName,p.Color,p.Size
from Product as p inner join ProductCategory as on p.ProductSubcategoryKey=
c.ProductCategoryKey where p.Size>'1'
<br>--查询执行计划是否被缓存
 select c.usecounts,c.size_in_bytes,c.objtype,t.text from sys.dm_exec_cached_plans as c
cross apply sys.dm_exec_SQL_text(c.plan_handle) as t
dbcc freeproccache--清空执行计划
<br>--没有join 的形式会生成简单参数化
select EnglishProductName,Color,Size from Product where size>'1'--简单参数化<br><br>select EnglishProductName,Color,Size from Product where size>'2'--简单参数化

 

1
2
3
select c.EnglishProductCategoryName,p.EnglishProductName,p.Color,p.Size
from Product as p inner join ProductCategory as on p.aProductSubcategoryKey=
c.ProductCategoryKey where p.Size>'2'

语句一样时即席查询才会重用执行计划。

 

优化:打开开关

1
2
exec sp_configure 'show advanced options',1
reconfigure with override

为ad hoc的查询优化:

1
2
exec sp_configure 'Optimize for ad hoc workloads',1
reconfigure with override

 

1
2
3
--使用参数化
alter database HRDB
set Parameterization forced

set Parameterization forced 强制参数化(like无法识别 )

1
2
3
4
5
6
7
select c.EnglishProductCategoryName,p.EnglishProductName,p.Color,p.Size
from Product as inner join ProductCategory as on p.ProductSubcategoryKey=
c.ProductCategoryKey where p.Size>'2'
select c.EnglishProductCategoryName,p.EnglishProductName,p.Color,p.Size
from Product as inner join ProductCategory as on p.ProductSubcategoryKey=
c.ProductCategoryKey where p.Size like '2%'

 

 

预定义查询:

预定义查询–参数化执行计划:

     存储过程:

           1.创建时延时检查

           2.第一次执行时编译并生成执行计划

           3.减少网络传输量

           4.封装变化点

           5.增强安全性,隔离访问控制

创建存储过程:

1
2
3
4
5
create procedure p_querycp @size varchar(500)
as
select c.EnglishProductCategoryName,p.EnglishProductName,p.Color,p.size
from Product as inner join ProductCategory as on p.ProductSubcategoryKey=c.ProductCategoryKey
where p.Size>@size

做跟踪(以前有对应得截图):

执行存储过程:

1
2
3
4
5
6
7
8
9
10
create procedure p_querycp @size varchar(500)
as
select c.EnglishProductCategoryName,p.EnglishProductName,p.Color,p.size
from Product as inner join ProductCategory as on p.ProductSubcategoryKey=c.ProductCategoryKey
where p.Size>@size
--清空执行计划
dbcc freeproccache
--执行
exec p_querycp '1'

执行重复的语句:

1
2
3
4
5
dbcc freeproccache
exec p_querycp @size='1'
exec p_querycp @size='2'

查看缓存计划:

1
2
select c.usecounts,c.size_in_bytes,c.objtype,t.text from sys.dm_exec_cached_plans as c
cross apply sys.dm_exec_SQL_text(c.plan_handle) as t

 

预定义查询—参数化执行计划:

SP_ExecuteSQL

避免了自己维护存储过程管理成本

可重用执行计划

Unicode字符串作为参数值与类型

大小写敏感

 

把存储过程定义成传递参数的:

1
2
3
4
5
6
7
8
9
declare @sqltext nvarchar(500)
set @sqltext=N'
select c.EnglishProductCategoryName,p.EnglishProductName,p.Color,p.size
from Product as p inner join ProductCategory as c on p.ProductSubcategoryKey=c.ProductCategoryKey
where p.Size>@size
'
declare @params nvarchar(500)
set @params=N'@size varchar(500)'
 exec sp_executesql @sqltext,@params,@size='1'

 

1
size 的大小换成  2

 

在.net中调用:(两种写法)

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
public object getCp(string size)
{
    HRUser dbcontext = new HRUser();
    var cps = from in dbcontext.Product
              join in dbcontext.ProductCategory
              on p.ProductSubcategoryKey equals c.ProductCategoryKey
              where p.Size == size
              //返回匿名对象
              select new
              {
                  CName = c.EnglishProductCategoryName,
                  PName = p.EnglishProductName,
                  Color = p.Color,
                  Size = p.Size
              };
    return cps.ToList();
}<br>
public object  getcp(string size)
{
    HRUser dbcontext = new HRUser();
    var cps = dbcontext.Product.Join(dbcontext.ProductCategory, a => a.ProductSubcategoryKey, ar => ar.ProductCategoryKey, (a, ar) => new
    {
        CName = ar.EnglishProductCategoryName,
        PName = a.EnglishProductName,
        Color = a.Color,
        Size = a.Size
    }).Where(p => p.Size == size);
    return cps.ToList();
}

页面:

1
2
3
4
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
  <asp:Button ID="Button2" runat="server" OnClick="Button2_Click" Text="显示产品" />
  <asp:GridView ID="GridView1" runat="server">
  </asp:GridView>

点击后的事件:

1
2
3
4
5
6
7
protected void Button2_Click(object sender, EventArgs e)
      {
          Product p = new Product();
          var cps = p.getCp(TextBox1.Text.Trim());
          GridView1.DataSource = cps;
          GridView1.DataBind();
      }

 

 

1
2
3
4
5
6
7
--动态构建语句(执行带参数的方法)
 declare @size varchar(500)
set @size='2'
execute('select c.EnglishProductCategoryName,p.EnglishProductName,p.Color,p.size
from Product as p inner join ProductCategory as c on p.ProductSubcategoryKey=c.ProductCategoryKey
where p.Size>'+''''+@size+'''')
   

 

1
2
3
4
5
dbcc freeproccache
--执行计划 缓存
select c.usecounts,c.size_in_bytes,c.objtype,t.text from sys.dm_exec_cached_plans as c
cross apply sys.dm_exec_sql_text(c.plan_handle) as t

形成两个缓存计划:

 

创建存储过程:

1
2
3
create  procedure p_querye @vacationhours  int
as
select e.LoginID,e.JobTitle from EmployeeOp as where VacationHours>@vacationhours
1
2
3
exec p_querye 2--实际执行计划 表扫描
exec p_querye 99--实际执行计划 表扫描 应用用索引更好

 

1
2
--重新编译的执行计划
exec p_querycp 99 with recompile

 

手工的指定执行几乎:

1
2
3
4
5
6
7
8
9
10
--手工的指定执行计划
exec  sp_create_plan_guide @name='执行任务计划指南之EmployeeOp Vacation',
--转成Unicode编码格式
@stmt=N'select e.LoginID,e.JobTitle from EmployeeOp as e where VacationHours>@vacationhours',
@type=N'Object',
--执行计划的名字
@module_or_batch ='p_querye',
@params =null,
--提示
@hints =N'OPTION(OPTIMIZE FOR(@vacationhours=''99''))'

 

清除执行计划:执行(会参考上面指定的执行计划)

1
exec p_querye 2

存储过程重编译:

 

临时结果集:

定义跟踪的模板:

一:使用物理表进行临时结果集

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
--1.做一张物理表
create procedure p_physicaltb
as
CREATE TABLE PhysicalTB(
    [SalesOrderID] [intNOT NULL,
    [SalesOrderDetailID] [int]  NOT NULL,
    [CarrierTrackingNumber] [nvarchar](25) NULL,
    [OrderQty] [smallintNOT NULL,
    [ProductID] [intNOT NULL,
    [SpecialOfferID] [intNOT NULL,
    [UnitPrice] [money] NOT NULL,
    [UnitPriceDiscount] [money] NOT NULL,
    [LineTotal] [numeric](38, 6) NOT NULL,
    [rowguid] [uniqueidentifier] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
)
insert into PhysicalTB select from OrderDetail
select from PhysicalTB
set statistics time on
exec p_physicaltb   --cpu:391  total:1762
set statistics time off

跟踪的情况:

 

删除之后再次创建执行。

物理表每次执行都会有重编译的过程(不建议使用物理表来存储临时结果集)

 

第二种方式:

临时表存储临时结果集

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
create procedure p_temptb
as
CREATE TABLE #PhysicalTB(
    [SalesOrderID] [intNOT NULL,
    [SalesOrderDetailID] [int]  NOT NULL,
    [CarrierTrackingNumber] [nvarchar](25) NULL,
    [OrderQty] [smallintNOT NULL,
    [ProductID] [intNOT NULL,
    [SpecialOfferID] [intNOT NULL,
    [UnitPrice] [money] NOT NULL,
    [UnitPriceDiscount] [money] NOT NULL,
    [LineTotal] [numeric](38, 6) NOT NULL,
    [rowguid] [uniqueidentifier] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
)
insert into #PhysicalTB select from OrderDetail
select from #PhysicalTB
drop table #PhysicalTB
set statistics time on
exec p_temptb  --cpu:110  total:1494
set statistics time off
sp_helpdb 'tempdb'--16064,768

第一次执行时重新编译,第二次就不会重新编译了。

用到了tempdb临时表:

 

第三种方式:表变量存储临时结果集

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
--表变量存储临时结果集
create procedure p_tabletb
as
--申明表变量
declare @PhysicalTB table(
    [SalesOrderID] [intNOT NULL,
    [SalesOrderDetailID] [int]  NOT NULL,
    [CarrierTrackingNumber] [nvarchar](25) NULL,
    [OrderQty] [smallintNOT NULL,
    [ProductID] [intNOT NULL,
    [SpecialOfferID] [intNOT NULL,
    [UnitPrice] [money] NOT NULL,
    [UnitPriceDiscount] [money] NOT NULL,
    [LineTotal] [numeric](38, 6) NOT NULL,
    [rowguid] [uniqueidentifier] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
)
insert into @PhysicalTB select from OrderDetail
select from @PhysicalTB
set statistics time on
exec p_tabletb  --cpu:110  total:1494
set statistics time off
sp_helpdb 'tempdb'--17064,768

执行时不会重新编译

 

第四种方式:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
--CTE(通用表表达式)存储临时结果集  完全放在内存中 不会操作任何数据库中的东西
create procedure p_ctetb
as
begin
--会自动推断数据类型
;with PhysicalTB(
    [SalesOrderID],
    [SalesOrderDetailID],
    [CarrierTrackingNumber],
    [OrderQty] ,
    [ProductID],
    [SpecialOfferID],
    [UnitPrice] ,
    [UnitPriceDiscount] ,
    [LineTotal] ,
    [rowguid] ,
    [ModifiedDate]
as (select from OrderDetail)
--访问通用表表达式
select from PhysicalTB
end

跟踪的结果:

没有重新编译的过程,纯粹操作内存。tempdb数据库不会有任何的变化。

1
2
3
4
5
set statistics time on
exec p_ctetb  --cpu:100  total:1300
set statistics time off
sp_helpdb 'tempdb'--17064,768

 

高版本的通用表达式可以进行多次的使用:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
create procedure p_ctetb1
as
begin
;with PhysicalTB(
    [SalesOrderID],
    [SalesOrderDetailID],
    [CarrierTrackingNumber],
    [OrderQty] ,
    [ProductID],
    [SpecialOfferID],
    [UnitPrice] ,
    [UnitPriceDiscount] ,
    [LineTotal] ,
    [rowguid] ,
    [ModifiedDate]
as (select from OrderDetail)
select from PhysicalTB
select from PhysicalTB
end
exec p_ctetb1

08之前的数据库,只要把表达式在创建一次就可以了

 

临时数据集的优化处理:

 

 

优化查询:编译指南。

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
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
--清空执行计划
dbcc freeproccache
select from EmployeeOp where VacationHours>1 option(use plan N'
<ShowPlanXML xmlns="http://schemas.microsoft.com/SQLServer/2004/07/showplan" Version="1.2" Build="12.0.2000.8">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementText="select * from EmployeeOp where VacationHours>99" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.00657038" StatementEstRows="1" StatementOptmLevel="FULL" QueryHash="0x7E06C77E90EB9FBB" QueryPlanHash="0x64478FC6152D2A83" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="120" ParameterizedText="(@1 tinyint)SELECT * FROM [EmployeeOp] WHERE [VacationHours]>@1">
          <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />
          <QueryPlan CachedPlanSize="40" CompileTime="6" CompileCPU="6" CompileMemory="232">
            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="206857" EstimatedPagesCached="51714" EstimatedAvailableDegreeOfParallelism="2" />
            <RelOp NodeId="0" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-006" AvgRowSize="830" EstimatedTotalSubtreeCost="0.00657038" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
              <OutputList>
                <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="BusinessEntityID" />
                <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="NationalIDNumber" />
                <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="LoginID" />
                <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="OrganizationNode" />
                <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="OrganizationLevel" />
                <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="JobTitle" />
                <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="BirthDate" />
                <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="MaritalStatus" />
                <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="Gender" />
                <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="HireDate" />
                <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="SalariedFlag" />
                <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="VacationHours" />
                <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="SickLeaveHours" />
                <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="CurrentFlag" />
                <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="rowguid" />
                <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="ModifiedDate" />
              </OutputList>
              <NestedLoops Optimized="0">
                <OuterReferences>
                  <ColumnReference Column="Bmk1000" />
                </OuterReferences>
                <RelOp NodeId="1" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="19" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="290" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
                  <OutputList>
                    <ColumnReference Column="Bmk1000" />
                    <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="VacationHours" />
                    <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="SickLeaveHours" />
                  </OutputList>
                  <IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore">
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Column="Bmk1000" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="VacationHours" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="SickLeaveHours" />
                      </DefinedValue>
                    </DefinedValues>
                    <Object Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Index="[nc_Employee_vacationsickleave]" IndexKind="NonClustered" Storage="RowStore" />
                    <SeekPredicates>
                      <SeekPredicateNew>
                        <SeekKeys>
                          <StartRange ScanType="GT">
                            <RangeColumns>
                              <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="VacationHours" />
                            </RangeColumns>
                            <RangeExpressions>
                              <ScalarOperator ScalarString="(99)">
                                <Const ConstValue="(99)" />
                              </ScalarOperator>
                            </RangeExpressions>
                          </StartRange>
                        </SeekKeys>
                      </SeekPredicateNew>
                    </SeekPredicates>
                  </IndexScan>
                </RelOp>
                <RelOp NodeId="3" PhysicalOp="RID Lookup" LogicalOp="RID Lookup" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="826" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="290" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row">
                  <OutputList>
                    <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="BusinessEntityID" />
                    <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="NationalIDNumber" />
                    <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="LoginID" />
                    <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="OrganizationNode" />
                    <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="OrganizationLevel" />
                    <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="JobTitle" />
                    <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="BirthDate" />
                    <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="MaritalStatus" />
                    <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="Gender" />
                    <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="HireDate" />
                    <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="SalariedFlag" />
                    <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="CurrentFlag" />
                    <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="rowguid" />
                    <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="ModifiedDate" />
                  </OutputList>
                  <IndexScan Lookup="1" Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore">
                    <DefinedValues>
                      <DefinedValue>
                        <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="BusinessEntityID" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="NationalIDNumber" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="LoginID" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="OrganizationNode" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="OrganizationLevel" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="JobTitle" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="BirthDate" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="MaritalStatus" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="Gender" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="HireDate" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="SalariedFlag" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="CurrentFlag" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="rowguid" />
                      </DefinedValue>
                      <DefinedValue>
                        <ColumnReference Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" Column="ModifiedDate" />
                      </DefinedValue>
                    </DefinedValues>
                    <Object Database="[HRDB]" Schema="[dbo]" Table="[EmployeeOp]" TableReferenceId="-1" IndexKind="Heap" Storage="RowStore" />
                    <SeekPredicates>
                      <SeekPredicateNew>
                        <SeekKeys>
                          <Prefix ScanType="EQ">
                            <RangeColumns>
                              <ColumnReference Column="Bmk1000" />
                            </RangeColumns>
                            <RangeExpressions>
                              <ScalarOperator ScalarString="[Bmk1000]">
                                <Identifier>
                                  <ColumnReference Column="Bmk1000" />
                                </Identifier>
                              </ScalarOperator>
                            </RangeExpressions>
                          </Prefix>
                        </SeekKeys>
                      </SeekPredicateNew>
                    </SeekPredicates>
                  </IndexScan>
                </RelOp>
              </NestedLoops>
            </RelOp>
            <ParameterList>
              <ColumnReference Column="@1" ParameterCompiledValue="(99)" />
            </ParameterList>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>
')  --表扫描
select from sys.dm_exec_cached_plans
select from sys.dm_exec_sql_text
select from  sys.dm_exec_query_plan
select from EmployeeOp where VacationHours>99  -- 索引
select c.plan_handle,p.text from sys.dm_exec_cached_plans as cross apply  sys.dm_exec_sql_text(c.plan_handle) as p
select from  sys.dm_exec_query_plan(0x06000A00CD253E14207CA0290200000001000000000000000000000000000000000000000000000000000000)

 

3 .6 .5 优化Ad-Hoc工作负载 - 笑一笑十年少!!! - 博客园

mikel阅读(588)

来源: 3 .6 .5 优化Ad-Hoc工作负载 – 笑一笑十年少!!! – 博客园

执行计划生成后会存储在plan cache中,以便重用,如果计划缓存从来都没有被重用 过,将会造成内存资源的浪费,这有可能是由于非参数化的Ad-hoc (即席查询)引起的。 当执行代码时,会产生一个hash值,用于匹配计划缓存中的hash值,相同的hash值代表 语句是相同的。如果执行一个存储过程,会按照存储过程名来创建hash值,如果在存储过 程之外执行代码(Ad-hoc T-SQL),那么hash值会根据整个语句产生。你的代码有一点点字 面上的改变,都会产生不同的hash值,导致计划无法重用。当有大量Ad-hoc执行时,会 导致计划缓存的膨胀。

针对这类问题,可以考虑使用存储过程、函数或者参数化Ad-hoc,但是有时候的确没 有办法,必须使用非参数化的Ad-hoc。从 SQL Server 2008开始,引人了一个“针对即席 工作负荷进行优化”的选项,如图3-9所示。

找到该选项的具体步骤是:右键实例,然后选择“属性”,再选择“高级”,之后把图3-9

箭头处的False改成True。下面是针对该选项的官方解释:

“针对即席工作负荷进行优化”选项用于提高包含许多一次性临时批处理的工作负荷计

划缓存的效率。如果该选项设置为True,则数据库引擎将在首次编译批处理时在计划缓存 中存储一个编译的小计划存根,而不是存储完全编译的计划。在这种情况下,不会让未重

复使用的编译计划填充计划缓存,从而有助于缓解内存压力。

编译的计划存根使数据库引擎能够识别此临时批处理以前已经编译过,但只存储了编

译计划存根,因此当再次调用(编译或执行)此批处理时,数据库引擎会对此批处理进行编

译,从计划缓存中删除编译计划存根并将完全编译的计划添加到计划缓存中。

将 “针对即席工作负荷进行优化”设置为1 只会影响新计划,而已在计划缓存中的计

划不受影响。

编译计划存根是sys.dm_exec_cached_plans目录视图显示的cacheobjtype之一。它具有

唯一的SQL句柄和计划句柄。编译计划存根没有与其关联的执行计划,并且查询计划句柄

不会返回XML显示计划。

可以用下面的脚本来查看缓存对象的对应内存数。

SELECT objtype AS ‘Cached Object Type * , COUNT(*) AS ‘Number of Plans’, SUM(CAST{size_in_bytes AS BIGINT)) / 1024 / 1024 AS ‘Plan Cache Size (MB)’, AVG(usecounts) AS ‘Avg Use Count * FROM sys.dm_exec_cached_plans GROUP BY objtype

在笔者计算机上的结果如图3-10所示。

 

在 没 有 开 启 上 面 选 项 的 系 统 中 ,Ad-hoc通 常 是 内 存 占 用 最 多 的 部 分 。所 以 从 SQL Server 2008开 始 ,建议开启这个选项。

SQL Server 内存优化之即席式工作负荷优化_Panda-CSDN博客_sql server 2005 针对即席工作进行优化

mikel阅读(686)

来源: SQL Server 内存优化之即席式工作负荷优化_Panda-CSDN博客_sql server 2005 针对即席工作进行优化

SQL Server中执行代码时,代码会生成一个哈希值,并使用哈希值确认计划的重用,如果执行一个存储过程,存储过程的名称会生成一个哈希值,后续的每一个存储过程调用不管使用的参数是否与之前的相同,都会重用该计划。

如果运行除了存储过程以外的同样的代码啊(也就是即席T-SQL),包含代码中所有文字的整段代码将会生成哈希值。当改变了代码中的任何文字再次执行语句,新的哈希值与之前的就不同乐,因此会生成一个新的执行计划。

这种情况会导致被称为计划缓存膨胀(plan cache bloat)的场景,既可能有成千上万的即席查询的执行计划被生成和缓存,虽然从根本上说代码时相同的,单这些计划的使用次数只有1。理想的解决方案是用时存储过程或函数,或讲所有即席T-SQL都进行参数化,但是往往无法实现。

针对即席工作负荷进行优化(Optimize for Ad-hoc Workloads)选项

启用这个选项是,当一段即席T-SQL第一次执行时,SQL Server将执行计划的存根进行缓存,而不是完整计划。如果SQL Server随后尝试重用改计划,执行计划将再次生成,但这次执行计划全部被缓存,这避免了成千上万地一次性使用的执行计划在缓存中占用宝贵空间。

例如,显示计划缓存大小:

— Single use adhoc plans
select count(*) as ‘Number of Plans’,
sum(cast(size_in_bytes as bigint))/1024/1024 as ‘Plan Cache Size(MB)’
from sys.dm_exec_cached_plans

结果
Number of Plans Plan Cache Size(MB)
————— ——————–
2 0
显示计划缓存中各缓存对象类型的大小:

— Cache size by object type
SELECT objtype AS ‘Cached Object Type’,
count(*) AS ‘Number of Plans’,
sum(cast(size_in_bytes AS BIGINT))/1024/1024 AS ‘Plan Cache Size (MB)’,
avg(usecounts) AS ‘Avg Use Count’
FROM sys.dm_exec_cached_plans
GROUP BY objtype

结果
Cached Object Type Number of Plans Plan Cache Size (MB) Avg Use Count
——————– ————— ——————– ————-
View 1 0 6
Adhoc 3 2 1
其中Adhoc为即席计划,Plan Cache Size(MB)为大小,Avg User Count 为平均使用次数

显示即席计划缓存中,只使用了一次的执行计划:

— Single use adhoc plans
SELECT count(*) AS ‘Number of Plans’,
sum(cast(size_in_bytes AS BIGINT))/1024/1024 AS ‘Plan Cache Size (MB)’
FROM sys.dm_exec_cached_plans
WHERE usecounts = 1
AND objtype = ‘adhoc’

结果
Number of Plans Plan Cache Size (MB)
————— ——————–
1 0
总结:针对即席工作负荷进行优化的选中能够确保不缓存不能重复使用的计划,可以节省服务器内存,并且仅影响即席计划,建议在所有SQL Server安装中默认都打开这个选项。
————————————————
版权声明:本文为CSDN博主「gulugulu。o0」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/qq_28617547/article/details/93262623