redis+websocket(秒杀,模拟客户端)_皇家大院的博客-CSDN博客_redis websocket

mikel阅读(797)

来源: redis+websocket(秒杀,模拟客户端)_皇家大院的博客-CSDN博客_redis websocket

一.设计背景
最近学了redis,websocket这些知识,想着弄出个

目录

一.设计背景

二.websocket实现:

1.Java代码:

2.前端HTML(简单写了一个文本框)

三.redis部分的代码

1.处理redis的key类:

2.redis缓存数据类型枚举类

3.redis数据操作封装类

4.抢购会员的信息实体类

四.截图展示

五.总结

啥来体验一下学习成果,便想到秒杀系统,然后一结合,就弄出了一个demo(写的不多,毕竟只有一个下午的时间)

废话不多说,直接上代码—这几个知识点的定义用法就不用介绍了,有疑问的兄弟请自行百度,或者可以提问(虽然我不一定回答)

二.websocket实现:
1.Java代码:
package com.server;

import java.io.IOException;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.atomic.AtomicInteger;

import javax.websocket.OnClose;
import javax.websocket.OnMessage;
import javax.websocket.OnOpen;
import javax.websocket.Session;
import javax.websocket.server.ServerEndpoint;
import com.xiaoleilu.hutool.util.StrUtil;

import redis.LinkToRedis;
import redis.RedisDataEnum;
import redis.RedisKeyBuilder;
import redis.clients.jedis.Jedis;
/**
* 在tomcat7中存在WebSocketServlet类(但已经过时),在tomcat8中彻底删除
* 此处使用@ServerEndpoint注解,主要是将目前的类定义成一个websocket服务器端
* 注解的值将被用于监听用户连接的终端访问URL地址
* onMessage|onOpen方法可能包含一个javax.websocket.Session可选参数
* 如果有这个参数,容器将会把当前发送消息客户端的连接Session注入进去
*/
@ServerEndpoint(“/websocketSecKill”)
public class WebSocketSecKill {
/*上线人数*/
private static AtomicInteger appOnlineAcount = new AtomicInteger(0);
/*会员编号和session对应的集合*/
private static Map<Session, String> appSessionMap = new ConcurrentHashMap<>();
/**
* 当服务器接收到客户端发送的消息时所调用的方法
* @param message
* @param session
* @throws IOException
* @throws InterruptedException
*/
@OnMessage
public void onMessage(String message,Session session) throws IOException, InterruptedException {
// 打印从客户端获取到的信息
System.out.println(“从客户端接收到的信息: ” + message);
System.out.println(“当前会员序号==”+session.getId());
System.out.println(“当前会员编号==”+appSessionMap.get(session));
String[] str=message.toString().split(“\\|”);
if(str[0].toString().equals(“抢购”.toString())) {
Jedis jedis = LinkToRedis.open();
String key = RedisKeyBuilder.buildKey(RedisDataEnum.COUNT);
System.out.println(“===”+key);
if(Integer.valueOf(jedis.get(key))<=4){//库存仅5,从0开始
String count=LinkToRedis.addCount(appSessionMap.get(session));
session.getBasicRemote().sendText(“即时发送信息,当前是第 ” + count+”次…”);
}else{
session.getBasicRemote().sendText(“即时发送信息,您的手速慢了”);
}
}

if(str[0].toString().equals(“0”.toString())) {//当前已抢购数目清零
LinkToRedis.returnCount();
session.getBasicRemote().sendText(“即时发送信息,抢购数目已清零” );
}
if(str[0].toString().equals(“1”.toString())) {//删除redis上面抢购会员信息,释放内存
LinkToRedis.del();
session.getBasicRemote().sendText(“即时发送信息,抢购会员信息已清空” );
}
}
/**
* 当一个新用户连接时所调用的方法
* @param session
*/
@OnOpen
public void onOpen(Session session) {
System.out.println(“客户端连接成功”);
System.out.println(session.getId());
connect(session.getId(),session);
}
/**
* 当一个用户断开连接时所调用的方法
* @param session
*/
@OnClose
public void onClose(Session session) {
System.out.println(“客户端关闭”);
appSessionMap.remove(session);
closeSession(session);
}

/**
* websocket连接
* @param memberNo 会员编号
* @param session 对应的session
* @description
*/
public static void connect(String memberNo, Session session) {
if(StrUtil.isEmpty(memberNo) || session == null) {
System.out.println(“app websocket连接,但参数为空”);
return;
}
String oldSession = appSessionMap.get(session);
if(oldSession != null) {
System.out.println(“app的websocket删除旧session”);
closeSession(session);
appSessionMap.remove(session);
}
int count = appOnlineAcount.incrementAndGet();
appSessionMap.put(session, memberNo+”520500″);
System.out.println(“app用户websocket连接上来,当前连接数:” + count+”–“+session.toString());
}

/**
* 关闭session
* @param session
* @description
*/
public static void closeSession(Session session) {
try{
session.close();
}catch(Exception ex){
System.out.printf(“关闭websocket出现异常”,ex);
}
}
}
2.前端HTML(简单写了一个文本框)
<!DOCTYPE html>
<html>
<head>
<meta charset=”UTF-8″>
<title>WebSocket测试</title>
</head>
<body>
<div>
<input type=”button” value=”stop” onclick=”stop()” />
<input type=”button” value=”send” onclick=”clock()” />
</div>
<input id=”message” type=”text” value=””>
<input id=”time” type=”hidden” value=”5000″>
<div id=”messages”></div>
<script type=”text/JavaScript”>

var webSocket = new WebSocket(‘ws://localhost:8080/websocketDemo/websocketSecKill’);
webSocket.onerror = function(event) {
alert(event.data);
};
//与WebSocket建立连接
webSocket.onopen = function(event) {
document.getElementById(‘messages’).innerHTML = ‘与服务器端建立连接’;
};
//处理服务器返回的信息
webSocket.onmessage = function(event) {
document.getElementById(‘messages’).innerHTML += ‘<br />’+ event.data;
};
function stop() {

//向服务器发送请求
document.getElementById(“time”).value=1000;
alert(document.getElementById(“time”).value);
}

//var int=self.setInterval(“clock()”,document.getElementById(“time”).value);
function clock()
{
//向服务器发送请求
var message=document.getElementById(“message”).value;
webSocket.send(message);
}
</script>
</body>
</html>
三.redis部分的代码
1.处理redis的key类:
package redis;

import java.util.Collections;
import java.util.List;

import redis.RedisDataEnum;
import com.xiaoleilu.hutool.util.StrUtil;

public class RedisKeyBuilder {
/*分隔符*/
protected final static String SEPARATOR = “:”;
/**
* 生成redis键
* @param redisData 保存到redis中的类型
* @param patterns 组成键的元素
* @return String
* @date 2018年7月26日
*/
public static String buildKey(RedisDataEnum redisData ,Object… patterns) {
StringBuilder key = new StringBuilder(redisData.getPrefix()).append(SEPARATOR);
for(int i = 0 , size = patterns.length ; i < size; i++) {
key.append(patterns[i]);
if(i != size – 1) {
key.append(SEPARATOR);
}
}
return key.toString();
}
/**
* 拆分键
* @param key 需要拆分的键
* @return List<String>
* @description
*/
public List<String> splitKey(String key){
if(StrUtil.isEmpty(key)) {
return Collections.emptyList();
}
return StrUtil.split(key, ‘:’);
}

}
2.redis缓存数据类型枚举类
package redis;

/**
* redis缓存数据类型
*/
public enum RedisDataEnum {
COUNT(“COUNT”,”当前抢购数量”),
Buyer(“Buyer”,”买家”);

private String prefix;
private String description;

private RedisDataEnum(String prefix, String description) {
this.description = description;
this.prefix = prefix;
}
public String getPrefix() {
return prefix;
}
public void setPrefix(String prefix) {
this.prefix = prefix;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}

}
3.redis数据操作封装类
import bean.FastBuyer;
import redis.clients.jedis.Jedis;

public class LinkToRedis {
public static Jedis open() {
Jedis jedis = new Jedis(“127.0.0.1”,端口号);//本地存储,—已测
jedis.auth(“—密码–“);
System.out.println(“Connection to server sucessfully”);
return jedis;
}
public static String addCount(String memberNo) {
Jedis jedis = LinkToRedis.open();
String key = RedisKeyBuilder.buildKey(RedisDataEnum.COUNT);
System.out.println(“===”+key);
if(StrUtil.isEmpty(jedis.get(key))){
System.out.println(“初始化,插入第一条数据”);
jedis.set(key, “1”);
}

String countNew=String.valueOf(Integer.valueOf(jedis.get(key))+1);
System.out.println(“======”+countNew);
//设置 redis 字符串数据
jedis.set(key, countNew);
String keybuyer = RedisKeyBuilder.buildKey(RedisDataEnum.Buyer,memberNo);
FastBuyer fastBuyer=new FastBuyer();
fastBuyer.setMemberNo(memberNo);
fastBuyer.setTime((new Date()).toString());
jedis.set(keybuyer, fastBuyer.toString());
// 获取存储的数据并输出
System.out.println(“Stored string in redis:: “+ jedis.get(keybuyer));
return countNew;
}
public static void returnCount() {
Jedis jedis = LinkToRedis.open();
String key = RedisKeyBuilder.buildKey(RedisDataEnum.COUNT);
System.out.println(“抢购数目清零”);
jedis.set(key, “0”);
}
/**
* 入mySQL(oracle)数据库后
* 删除redis上面抢购会员信息,释放内存
*/
public static void del() {

Jedis jedis = LinkToRedis.open();

Set<String> set = jedis.keys(“Buyer*”);

Iterator it = set.iterator();
while (it.hasNext()) {
String key = (String) it.next();
jedis.del(key);
System.out.println(key);
}
}

// public static void main(String[] args) {
// del();
// }
}
4.抢购会员的信息实体类
package bean;

import java.io.Serializable;

public class FastBuyer implements Serializable{
private static final long serialVersionUID = 1L;
private String memberNo;
private String time;
public String getMemberNo() {
return memberNo;
}
public void setMemberNo(String memberNo) {
this.memberNo = memberNo;
}
public String getTime() {
return time;
}
public void setTime(String time) {
this.time = time;
}
@Override
public String toString() {
return “FastBuyer [memberNo=” + memberNo + “, time=” + time + “]”;
}

}
四.截图展示
1.首先运行项目,连续在浏览器开出6个同样的页面(主要是为了模拟客户端多用户)来测试,对redis数据库进行清空初始化(页面设置了个0.1来实现,主要是为了清除我之前测试的数据)

 

对应的后台tomcat输出日志:

 

2.下面就是测试截图了:

图1:

 

图2:

 

图3:

 

图4:

 

图5:(tomcat部分测试运行日志)

 

3.既然是对redis高速缓存数据库操作,那么肯定有记录:

如下图,redis客户端可以看到我本地的数据:

 

 

————————————————
版权声明:本文为CSDN博主「皇家大院」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/qq_40919170/article/details/81220297

sql server 临时表(中) Tempdb监控 - 花阴偷移 - 博客园

mikel阅读(670)

来源: sql server 临时表(中) Tempdb监控 – 花阴偷移 – 博客园

一. 监控概述

Tempdb库空间使用的一大特点,是只有一部分对象,例如用户创建的临时表、table变量等,可以用sys.allocation_units和sys.partitions这样的管理视图来管理,许多内部对象和版本存储在这些管理视图中没有体现,所以sp_spaceused的结果和真实的使用情况会有很大差异,tempdb的空间使用是不能用sp_spaceused来跟踪的。使用dbcc showfilestats 可以反映数据文件使用的整体情况,但不能监控到空间被谁以什么样的方式用掉。

对于Tempdb的磁盘空间不足,可能会严重破坏 SQL Server 生产环境,并会使正在运行的应用程序无法完成操作。可以使用 sys.dm_db_file_space_usage 动态管理视图来监视 tempdb 文件中的这些功能使用的磁盘空间。此外,若要在会话级或任务级监视 tempdb 中的页分配或页释放活动,可以使用动态管理视图 sys.dm_db_session_space_usage 和 sys.dm_db_task_space_usage。这些视图可用于标识使用 tempdb 中大量磁盘空间的大型查询、临时表或表变量。还可以使用若干个windows性能计数器来监视 tempdb 中的可用空间以及使用 tempdb 的资源。

(1)使用sys.dm_db_file_space_usage 视图能反映tempdb在几个大类里的空间使用分布, 使用sys.dm_db_session_space_usage 视图能反映tempdb在各会话中空间使用分布。(2)监控磁盘I/O来确认是否有瓶颈。(3)监视TempDB相关的DDL。

二. 监视tempdb空间分布

2.1 sys.dm_db_file_space_usage视图

使用该视图可以:返回数据库中每个文件的空间使用信息。下面例出该视图的主要信息,详细视图查看msdn。通过这个视图就能知道tempdb的空间是被哪一块对象使用掉的,是用户对象,还是内部对象,还是版本存储。

列名 数据类型 说明
database_id smallint 数据库 ID
file_id smallint 文件 ID
unallocated_extent_page_count bigint 文件的未分配区中的总页数。

不包括已分配区中的未使用页

version_store_reserved_page_count bigint 版本存储分配的统一区中的总页数
user_object_reserved_page_count bigint 从统一区为数据库中的用户对象分配的总页数
internal_object_reserved_page_count bigint 从统一区为文件中的内部对象分配的总页数

2.2 sys.dm_db_session_space_usage视图

使用该视图,能返回数据库每个会话分配和释放的页数。此视图仅适用于tempdb数据库。只有在任务结束时才更新计数器; 统计不反映正在运行的任务。

列名 数据类型 描述
SESSION_ID SMALLINT 会话ID。
user_objects_alloc_page_count BIGINT 此会话为用户对象保留或分配的页数
user_objects_dealloc_page_count BIGINT 此会话取消分配且不再为用户对象保留的页数
internal_objects_alloc_page_count BIGINT 此会话为内部对象保留或分配的页数
internal_objects_dealloc_page_count BIGINT 此会话取消分配且不再为内部对象保留的页数。
user_objects_deferred_dealloc_page_count BIGINT 已标记为延迟释放的页数。

2.3 监视的SQL脚本  

对于监视tempdb三类对象空间分布,这里使用比较”轻量极”对系统影响不大的方法。通过间隔时间运行来监视空间使用,监视包括 DBCC,DMV,DMF 等,把结果输出到一个文件或数据库表里。

复制代码
-- 间隔每秒执行一次,手工终止或加入作业
use tempdb
while 1=1
BEGIN

     select GETDATE() AS '当前时间'
     
     --------------query1 从文件级查看tempdb使用情况------------------
     /*
       TotalExtents 是磁盘占用的空间,一个extent为64k
       UsedExtents  是磁盘使用的空间
     */
     dbcc showfilestats
     
     ------------- query2 查看当前Tempdb各对象,占用总空间-------------
     /*
      user_object_reserved_page_count 用户对象包括: 临时表,表变量,表值函数中返回的表,定义的表和索引
      internal_object_reserved_page_count 内部对象包括:排序段,哈希联接,游标
      version_store_reserved_page_count 行版本包括: 触发器,运行联机索引,快照隔离级别或使用行版本控制
      unallocated_extent_page_count:未分配空间(可用空间)
    */
      select  'Tempdb' as DB, GETDATE() as  [Time],
              SUM(user_object_reserved_page_count)*8.0 as  '用户对象占用总空间_kb',
              SUM(internal_object_reserved_page_count)*8.0 as '内部对象占用总空间_kb',
              SUM(version_store_reserved_page_count) * 8.0 as '行版本占用总空间_kb',
              SUM(unallocated_extent_page_count) *8.0 as '未分配总空间_kb'
       from sys.dm_db_file_space_usage
         where database_id=2 
  
     -----------------query3 查询tempdb各会话,占用和释放的空间-------------
     SELECT 
        t1.session_id,
        (t1.internal_objects_alloc_page_count * 8.0) AS internal_objects_alloc_kb,
        (t1.user_objects_alloc_page_count * 8.0)AS user_objects_alloc_kb,
        (t1.internal_objects_dealloc_page_count* 8.0) AS internal_objects_dealloc_kb,
        (t1.user_objects_dealloc_page_count * 8.0)  AS user_objects_dealloc_kb
    from sys.dm_db_session_space_usage as t1, 
    sys.dm_exec_sessions as t3 
    where t1.session_id = t3.session_id 
    and (t1.internal_objects_alloc_page_count >0
    or t1.user_objects_alloc_page_count>0
    or t1.internal_objects_dealloc_page_count>0
    or t1.user_objects_dealloc_page_count>0)
    and t1.session_id <>@@SPID
    
     -----------------query4 查询tempdb 各会话以及sql语句,占用空间-------------
    /*
     会话的内部对象和用户对象的保留或释放的页数
    */
    SELECT t1.session_id,  
    (t1.internal_objects_alloc_page_count * 8.0) AS internal_objects_alloc_kb,
    (t1.user_objects_alloc_page_count * 8.0)AS user_objects_alloc_kb,
    (t1.internal_objects_dealloc_page_count* 8.0) AS internal_objects_dealloc_kb,
    (t1.user_objects_dealloc_page_count * 8.0)  AS user_objects_dealloc_kb,                                                
    st.text                                                        
    from sys.dm_db_session_space_usage as t1,                               
    sys.dm_exec_requests as t4                                              
    CROSS APPLY sys.dm_exec_sql_text(t4.sql_handle) AS st                   
     where  t1.session_id = t4.session_id                                       
       and t1.session_id >50                                                
    and (t1.internal_objects_alloc_page_count>0
    or t1.user_objects_alloc_page_count >0
    or t1.internal_objects_dealloc_page_count>0
    or t1.user_objects_dealloc_page_count>0) 
    and t1.session_id <>@@SPID

END
复制代码

监听如下图所示:

2.4  行版本监控

行版本是可以跨会话的,所以在sys.dm_db_session_space_usage中只有内部对象和用户对象所占用的空间查看。如果要监听和回收这部分空间,可使用下面脚本:

复制代码
--(1)另外查询行版本运行最长的事务
select top 2 
    transaction_id, 
    transaction_sequence_num, 
    elapsed_time_seconds 
from sys.dm_tran_active_snapshot_database_transactions
order by elapsed_time_seconds DESC

--(2)根据得到当前会话
select   session_id from sys.dm_tran_session_transactions where transaction_id=@transaction_id
复制代码

 

三. 监视TempDB的I/O磁盘

  由于TempDB被多个地方广泛使用,有可能造成磁盘的消耗较大,成为I/O瓶颈。除了监视windows性能计数器,还有就是DMV视图。

3.1 对于windows性能计数器主要包括三个

复制代码
-- 每次数据传输的平均时间
AVG.Disk sec/Transfer
--磁盘读数据所需的平均时间
AVG.Disk sec/Read
--磁盘写数据所需的平均时间
AVG.Disk sec/Write
复制代码

3.2 DMV视图

下面使用sys.dm_io_virtual_file_stats来确认IO瓶颈,它返回数据文件和日志文件的 I/O 统计信息,包括对文件发出的读取/写入次数以及总字节数, file_id字段中1 是数据文件,2是日志文件,脚本如下:

复制代码
SELECT DB_NAME(database_id) AS 'DBName',
file_id,
io_stall_read_ms/num_of_reads AS 'Avg Read ms/Transfer',
io_stall_write_ms/num_of_writes AS 'Avg Write ms/Transfer'
 FROM sys.dm_io_virtual_file_stats(NULL,null)
WHERE  database_id=2
复制代码

下面是数据文件及日志文件性能指标建议列表,仅供参考

数据文件 日志文件
目标:<10ms 目标:<5ms
可接受:10 ~ 20 ms 可接受:5~15ms
不可接受:>20ms 不可接受:>15ms

 

四 监视TempDB相关的DDL

DDL主要是用在定义或改变表的结构,数据类型,表之间的链接和约束等初始化工作上。对于大量,频繁地创建和删除临时表和表变量会引起元数据上的争用。在2008版本中在一定条件下局部临时表和表变量会被缓存,以减少元数据的争用。通过windows性能计数器或sys.dm_os_waiting_tasks 视图可以周期性的检查等待时间较长的会话。

4.1 sys.dm_os_waiting_tasks

使用该视图返回有关正在等待某些资源的任务的等待队列的信息。脚本如下

SELECT session_id,wait_duration_ms,resource_description,GETDATE()AS '当前时间'
FROM sys.dm_os_waiting_tasks 
WHERE resource_description LIKE '2:%' AND wait_type LIKE 'PAGE%LATCH_%'
ORDER BY wait_duration_ms desc

4.2 windows性能计数器

SQL Server:Access Methods\Workfiles Created/Sec
SQL Server:Access Methods\Worktables Created/Sec
SQL Server:Access Methods\Mixed Page Allocations/Sec
SQL Server:Access Methods\Temp Tables Created/Sec
SQL Server:Access Methods\Temp Tables for destruction

 

最后还可参考msdn文档:解决 tempdb 中磁盘空间不足的问题

sql server 临时表(上) Tempdb概述 - 花阴偷移 - 博客园

mikel阅读(800)

来源: sql server 临时表(上) Tempdb概述 – 花阴偷移 – 博客园

sql server 临时表(上) Tempdb概述

一.概述

SQL server里临时表存储在TempDB库中,TempDB是一个系统数据库,它只有Simple恢复模式,也是最小日志记录操作。主要用于存放局部临时表,全局临时表,表变量,都是基于临时特征,每次服务器或服务重启后,都会按照Model库的配置重新创建TempDB库。在SQL server 2012中TempDB表可以配置在故障转移中。在TempDB库中存放三类对象包括:用户对象, 内部对象, 行版本存储。TempDB库只有一个文件组,就是primary文件组,增加其它文件组会提示报错。在权限方面,所有用户默认都具有访问TempDB库权限。

 

1.1 Tempdb 的物理属性

下表列出了 tempdb 数据和日志文件的初始配置值。 对于不同版本的 SQL Server,这些文件的大小可能略有不同。

文件 逻辑名称 物理名称 文件增长
主数据 tempdev tempdb.mdf 按 10% 自动增长,直到磁盘已满
日志 templog templog.ldf 以 10% 的速度自动增长到最大 2 TB

 

  1.2 使用Tempdb的作用

(1) 通过tempdb库可以缓存临时表和表变量,用于减少数据表的查询次数以及锁问题。缓存允许删除和创建临时对象的操作能快速地执行,并减少页分配的争用问题。

(2) 分配页闩锁协议得到改善。 从而减少使用的 UP(更新)闩锁数。

(3) 减少了 tempdb 的日志开销。 从而减少了 tempdb 日志文件上的磁盘 I/O 带宽消耗。

(4) 分配混合的页中的算法tempdb得到了改进。

 

 1.3 使用tempdb的限制, 不能对 tempdb 数据库执行以下操作:

(1)添加文件组。

(2)备份或还原数据库。

(3)更改排序规则。 默认排序规则为服务器排序规则。

(4)更改数据库所有者。 tempdb 的所有者是 sa。

(5)创建数据库快照。

(6)删除数据库。

(7)从数据库中删除 guest 用户。

(8)启用变更数据捕获。

(9)参与数据库镜像。

(10)删除主文件组、主数据文件或日志文件。

(11)重命名数据库或主文件组。

(12)运行 DBCC CHECKALLOC。

(13)运行 DBCC CHECKCATALOG。

(14)将数据库设置为 OFFLINE。

 

  1. 二. TempDB存储的三类对象

在SQL 2005以后,引入了一张新的管理视图:sys.dm_db_file_space_usage。通过查询这张视图,能了解tempdb的空间使用情况,能知道tempdb的空间是被哪一块对象使用掉的,是用户对象(user_object_reserved_page_count字段),还是系统对象(internal_object_reserved_page_count字段),还是版本存储区(version_store_reserved_page_count字段。在了解这个dmv视图之前,先了解tempdb库的三类对象。

 

  2.1 用户临时对象(user_object_reserved_page_count)

由用户会话显示创建的对象,可以在系统表目录中找到,用户临时对象主要包括以下内容:

(a) 创建临时表和索引,不过这些表在重启后清空。

(b) 全局临时表以 ##开头的表。适用于所有会话操作该表信息。需要显示的删除或重启服务才能清除表。

(c) 局部临时表以 #开头的表。适用于当前会话操作该表信息。会话结束清除表。

(d) 表变量, 以@开头。

下面是简单示例,演示局部临时表,临时表索引,全局临时表,表变量,脚本如下:

— 创建局部临时表 CREATE TABLE #TempTable(id INT,NAME VARCHAR(10)) — 根据已有表,创建临时表 SELECT * INTO #TempTable2 FROM tablename –创建局部临时表, 带有聚集索引 CREATE TABLE #tempWithCLUSTERED([SID] INT PRIMARY KEY CLUSTERED, model VARCHAR(50)) — 创建全局临时表 CREATE TABLE ##TempTable3(id INT,NAME VARCHAR(10)) — 根据已有表创建全局临时表 SELECT * INTO ##TempTable4 FROM dbo.ACT_AnswerTab — 删除全局临时表 DROP TABLE ##TempTable4 — 创建表变量 DECLARE @tablevar TABLE (id int,NAME varchar(10))

下面简单介绍一下,临时对象中表变量与临时表区别

(1)统计信息

表变量没有统计信息,统计信息要么为0,要么为1。sql server查询优化器只会把表变量当作里面只有1条数据或没有数据的表对待,脚本演示如下所示:

— 创建表变量示例 DECLARE @tablevar TABLE (id int,NAME varchar(10)) INSERT INTO @tablevar(id,name) SELECT TOP 1000 ROW_NUMBER() OVER(ORDER BY number ) AS id, number FROM master..spt_values SELECT * FROM @tablevar

(2) 索引

表变量使用索引,只能是主键(可以是聚集和非聚集索引)或者唯一约束,需要预先定义好,定义后不能再添加索引了,注意:即使是有索引,也没有统计信息。表变量使用聚集索引如下所示:

DECLARE @tablevar TABLE (id INT PRIMARY KEY CLUSTERED,NAME varchar(10)) INSERT INTO @tablevar(id,name) SELECT TOP 1000 ROW_NUMBER() OVER(ORDER BY number ) AS id, number FROM master..spt_values SELECT * FROM @tablevar WHERE id=1

(3) 架构修改

架构修改可以出现在临时表上,但不会出现在表变量上面,修改会产生重编译,非预期的重编译不是好事。下面是临时表架构修改,将name字段长改为50, 脚本如下:

ALTER TABLE #TempTable ALTER COLUMN name NVARCHAR (50) NULL USE tempdb go sp_help #TempTable

 

(4) 对比表格

特征 临时表 变量表
命名 以#开头 以@开头
统计信息
索引 仅约束可用
架构修改 允许 不允许
在sp_executesql中使用 可以 不可以
使用insert into xxx exec 可以 不可以
存在于内存中

总结: 建议:小于100行使用表变量。数据都不是存入于内存中,而是存放在TempDB表中,通过sys.dm_db_session_space_usage这个dmv可查看空间占用情况。后面监控时再说这dmv。

 

 2.2 内部对象(internal_object_reserved_page_count)

内部对象是根据需要 由 SQL Server 数据库引擎创建的,用于处理 SQL Server 语句。主要是查询过程中存储临时数据的对象,这些临时数据的对象可以在语句的作用域中自动创建和删除。 内部对象主要包括以下内容:

(a) 游标

(b) 哈希(Hash)联接或哈希聚合操作的查询

(c) 某些 GROUP BY、ORDER BY 或 UNION 查询的中间排序结果.

在内部对象中比较常见的是Worktable, 这个临时表是自动生成,自动销毁,主要用于游标,假脱机,临时大对象的数据类型(LOB)存储,这个内部临时表用于返回查询的中间结果,监视分析Worktable可以通过打开statistics io 选项,执行sql语句再查看,要注意的是:高效的查询下很少出现这种现象。

 

 2.3 版本存储(version_store_reserved_page_count)

版本存储区主要用来支持Snapshot事务隔离级别,以及SQL 2005以后推出的一些其他提高数据库并发度的新功能。这类并发模式会借用TempDB来存放修改前的版本数据,第一行数据被修改前,都会在TempDB中创建一个相同的行,并加上14bytes的长度,包含: (1)修改前的事务序列号(XSN)长度为6 bytes (2) TempDB的行标识符(RID)长度为8bytes。版本存储用于存储行版本、MARS、联机索引、触发器、基于快照的隔离级别。如果开启了乐观并发模式(已提交读快照和快照二种隔离级别),可能造成Tempdb库的非预期增长, 需要对Tempdb库进行监控。

程序猿是如何解决SQLServer占CPU100%的 - 马非码 - 博客园

mikel阅读(816)

来源: 程序猿是如何解决SQLServer占CPU100%的 – 马非码 – 博客园

文章目录

 

遇到的问题

有同事反应服务器CPU过高,一看截图基本都是100%了,my god,这可是大问题,赶紧先看看。

让同事查看系统进程,发现是SQLServer的CPU占用比较高。首先想到的是不是报表生成的时候高,因为这块之前出现过问题,关掉服务程序,还是高。难道是客户端程序引发的?但是这么多的客户端连接,难不成每个都叫人关闭,很简单,把网络断开即可。网络断开之后,CPU立马下降。那么问题到底在哪里呢,是时候祭出我们的利器了——SQLServer Profiler。

使用SQLServer Profiler监控数据库

让同事使用SQLProfiler监控了大概20分钟左右,然后保存为跟踪文件*.rtc。

我们来看看到底是哪句SQL有问题:

SQL1:查找最新的30条告警事件

select top 30  a.orderno,a.AgentBm,a.AlarmTime,a.RemoveTime,c.Name as AddrName,b.Name as MgrObjName,a.Ch,a.Value,a.Content,a.Level
	,ag.Name as AgentServerName,a.EventBm,a.MgrObjId,a.Id,a.Cfmoper,a.Cfm,a.Cfmtime,a.State,a.IgnoreStartTime,a.IgnoreEndTime
	,a.OpUserId,d.Name as MgrObjTypeName,l.UserName as userName,f.Name as AddrName2 
from eventlog as a left join mgrobj as b on a.MgrObjId=b.Id and a.AgentBm=b.AgentBm 
	left join addrnode as c on b.AddrId=c.Id left join mgrobjtype as d on b.MgrObjTypeId=d.Id 
	left join eventdir as e on a.EventBm=e.Bm left join agentserver as ag on a.AgentBm=ag.AgentBm 
	left join loginUser as l on a.cfmoper=l.loginGuid left join addrnode as f on ag.AddrId=f.Id 
where ((MgrObjId in (
	select Id from MgrObj 
	where AddrId in ('','02100000','02113000','02113001','02113002','02113003','02113004'
		,'02113005','02113006','02113007','02113008','02113009','02113010','02113011','02113012'
		,'02113013','02113014','02113015','02113016','02113017','02113018','02113019','02113020'
		,'02113021','02113022','02113023','02113024','02113025','02113026'))) 
	or (mgrobjid in ('00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000'
		,'00000000-0000-0000-0000-000000000000','11111111-1111-1111-1111-111111111111'
		,'11111111-1111-1111-1111-111111111111'))
	) 
order by alarmtime DESC

 

SQL2:获取当前的总报警记录数

select count(*)  from eventlog as a left join mgrobj as b on a.MgrObjId=b.Id and a.AgentBm=b.AgentBm 
	left join addrnode as c on b.AddrId=c.Id left join mgrobjtype as d on b.MgrObjTypeId=d.Id 
	left join eventdir as e on a.EventBm=e.Bm 
where MgrObjId in (
	select Id from MgrObj where AddrId in 
		('','02100000','02100001','02100002','02100003','02100004','02100005','02100006','02100007'
		,'02100008','02100009','02100010','02100011','02100012','02100013','02100014','02100015'
		,'02100016','02100017','02100018','02100019','02101000','02101001','02101002','02101003'
		,'02101004','02101005','02101006','02101007','02101008','02101009','02101010','02101011','02101012'
		,'02101013','02101014','02101015','02101016','02101017','02101018','02101019','02101020','02101021'
		,'02101022','02101023','02101024','02101025','022000','022001','022101','022102','0755','0755002')
	) 
	and mgrobjid not in (
		'00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000','00000000-0000-0000-0000-000000000000'
		,'11111111-1111-1111-1111-111111111111','11111111-1111-1111-1111-111111111111')

这是典型的获取数据并分页的数据,一条获取最新分页记录总数,一条获取分页记录,正是获取最新事件这里导致的CPU过高。这里的业务大概是每个客户端,每3秒执行一次数据库查找,以便显示最新的告警事件。好了,元凶找到了,怎么解决?

有哪些SQL语句会导致CPU过高?

上网查看了下文章,得出以下结论:

1.编译和重编译

编译是 Sql Server 为指令生成执行计划的过程。Sql Server 要分析指令要做的事情,分析它所要访问的表格结构,也就是生成执行计划的过程。这个过程主要是在做各种计算,所以CPU 使用比较集中的地方。

执行计划生成后会被缓存在 内存中,以便重用。但是不是所有的都可以 被重用。在很多时候,由于数据量发生了变化,或者数据结构发生了变化,同样一句话执行,就要重编译。

2.排序(sort) 和 聚合计算(aggregation)

在查询的时候,经常会做 order by、distinct 这样的操作,也会做 avg、sum、max、min 这样的聚合计算,在数据已经被加载到内存后,就要使用CPU把这些计算做完。所以这些操作的语句CPU 使用量会多一些。

3.表格连接(Join)操作

当语句需要两张表做连接的时候,SQLServer 常常会选择 Nested Loop 或 Hash 算法。算法的完成要运行 CPU,所以 join 有时候也会带来 CPU 使用比较集中的地方。

4.Count(*) 语句执行的过于频繁

特别是对大表 Count() ,因为 Count() 后面如果没有条件,或者条件用不上索引,都会引起 全表扫描的,也会引起 CPU 的大量运算

大致的原因,我们都知道了,但是具体到我们上述的两个SQL,好像都有上述提到的这些问题,那么到底哪个才是最大的元凶,我们能够怎么优化?

查看SQL的查询计划

SQLServer的查询计划很清楚的告诉了我们到底在哪一步消耗了最大的资源。我们先来看看获取top30的记录:

排序竟然占了94%的资源。原来是它!同事马上想到,用orderno排序会不会快点。先把上述语句在SQLServer中执行一遍,清掉缓存之后,大概是2~3秒,然后排序字段改为orderno,1秒都不到,果然有用。但是orderno的顺序跟alarmTime的顺序是不完全一致的,orderno的排序无法替代alarmTime排序,那么怎么办?我想,因为选择的是top,那么因为orderno是聚集索引,那么选择前30条记录,可以立即返回,根本无需遍历整个结果,那么如果alarmTime是个索引字段,是否可以加快排序?

选择top记录时,尽量为order子句的字段建立索引

先建立索引:

IF NOT EXISTS(SELECT * FROM sysindexes WHERE id=OBJECT_ID('eventlog') AND name='IX_eventlog_alarmTime')
	CREATE NONCLUSTERED INDEX IX_eventlog_alarmTime ON dbo.eventlog(AlarmTime)

在查看执行计划:

看到没有,刚才查询耗时的Sort已经消失不见了,那么怎么验证它能够有效的降低我们的CPU呢,难道要到现场部署,当然不是。

查看SQL语句CPU高的语句

SELECT TOP 10 TEXT AS 'SQL Statement'
    ,last_execution_time AS 'Last Execution Time'
    ,(total_logical_reads + total_physical_reads + total_logical_writes) / execution_count AS [Average IO]
    ,(total_worker_time / execution_count) / 1000000.0 AS [Average CPU Time (sec)]
    ,(total_elapsed_time / execution_count) / 1000000.0 AS [Average Elapsed Time (sec)]
    ,execution_count AS "Execution Count",qs.total_physical_reads,qs.total_logical_writes
    ,qp.query_plan AS "Query Plan"
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_elapsed_time / execution_count DESC

我们把建索引前后CPU做个对比:

已经明显减低了。

通过建立相关索引来减少表扫描

我们再来看看count(*)这句怎么优化,因为上面的这句跟count这句差别就在于order by的排序。老规矩,用查询计划看看。

用语句select count(0) from eventlog一看,该表已经有20多w的记录,每次查询30条数据,竟然要遍历这个20多w的表两次,能不耗CPU吗。我们看看是否能够利用相关的条件来减少表扫描。很明显,我们可以为MgrObjId建立索引:

CREATE NONCLUSTERED INDEX IX_eventlog_moid ON dbo.eventlog(MgrObjId)

但是无论我怎么试,都是没有利用到索引,难道IN子句和NOT IN子句是没法利用索引一定会引起表扫描。于是上网查资料,找到桦仔的文章,这里面有解答:

SQLSERVER对筛选条件(search argument/SARG)的写法有一定的建议

对于不使用SARG运算符的表达式,索引是没有用的,SQLSERVER对它们很难使用比较优化的做法。非SARG运算符包括

NOT、<>、NOT EXISTS、NOT IN、NOT LIKE和内部函数,例如:Convert、Upper等

但是这恰恰说明了IN是可以建立索引的啊。百思不得其解,经过一番的咨询之后,得到了解答:

不一定是利用索引就是好的,sqlserver根据你的查询的字段的重复值的占比,决定是表扫描还是索引扫描

有道理,但是我查看了下,重复值并不高,怎么会有问题呢。

关键是,你select的字段,这个地方使用索引那么性能更差,你select字段 id,addrid,agentbm,mgrobjtypeid,name都不在索引里。

真是一语惊醒梦中人,缺的是包含索引!!!关于包含索引的重要性我在这篇文章《我是如何在SQLServer中处理每天四亿三千万记录的》已经提到过了,没想到在这里又重新栽了个跟头。实践,真的是太重要了!

通过建立包含索引来让SQL语句走索引

好吧,立马建立相关索引:

IF NOT EXISTS(SELECT * FROM sysindexes WHERE id=OBJECT_ID('eventlog') AND name='IX_eventlog_moid')
	CREATE NONCLUSTERED INDEX IX_eventlog_moid ON dbo.eventlog(MgrObjId) INCLUDE(EventBm,AgentBM)

我们再来看看查询计划:

看到没有,已经没有eventlog表的表扫描了。我们再来比较前后的CPU:

很明显,这个count的优化,对查询top的语句依然的生效的。目前为止,这两个查询用上去之后,再也没有CPU过高的现象了。

其他优化手段

  • 通过服务端的推送,有事件告警或者解除过来才查询数据库。
  • 优化上述查询语句,比如count(*)可以用count(0)替代——参考《SQL开发技巧(二)
  • 优化语句,先查询出所有的MgrObjId,然后在做连接
  • 为管理对象、地点表等增加索引
  • 添加了索引之后,事件表的插入就会慢,能够再怎么优化呢?可以分区建立索引,每天不忙的时候,把新的记录移入到建好索引的分区

当然,这些优化的手段是后续的事情了,我要做的事情基本完了。

总结

  • 服务器CPU过高,首先查看系统进程,确定引发CPU过高的进程
  • 通过SQLServer Profiler能够轻易监控到哪些SQL语句执行时间过长,消耗最多的CPU
  • 通过SQL语句是可以查看每条SQL语句消耗的CPU是多少
  • 导致CPU高的都是进行大量计算的语句:包括内存排序、表扫描、编译计划等。
  • 如果使用Top刷选前面几条语句,则尽量为Order By子句建立索引,这样可以减少对所有的刷选结果进行排序
  • 使用Count查询记录数时,尽量通过为where字句的相关字段建立索引以减少表扫描。如果多个表进行join操作,则把相关的表连接字段建立在包含索引中
  • 通过服务端通知的方式,减少SQL语句的查询
  • 通过表分区,尽量降低因为添加索引而导致表插入较慢的影响

参考文章

最后,感谢博客园DBA桦仔的热心指点。

记一次SQLServer的分页优化兼谈谈使用Row_Number()分页存在的问题 - 马非码 - 博客园

mikel阅读(758)

来源: 记一次SQLServer的分页优化兼谈谈使用Row_Number()分页存在的问题 – 马非码 – 博客园

最近有项目反应,在服务器CPU使用较高的时候,我们的事件查询页面非常的慢,查询几条记录竟然要4分钟甚至更长,而且在翻第二页的时候也是要这么多的时间,这肯定是不能接受的,也是让现场用SQLServerProfiler把语句抓取了上来。

用ROW_NUMBER()进行分页

我们看看现场抓上来的分页语句:

select top 20 a.*,ag.Name as AgentServerName,,d.Name as MgrObjTypeName,l.UserName as userName 
from eventlog as a 
	left join mgrobj as b on a.MgrObjId=b.Id and a.AgentBm=b.AgentBm 
	left join addrnode as c on b.AddrId=c.Id 
	left join mgrobjtype as d on b.MgrObjTypeId=d.Id 
	left join eventdir as e on a.EventBm=e.Bm 
	left join agentserver as ag on a.AgentBm=ag.AgentBm 
	left join loginUser as l on a.cfmoper=l.loginGuid 
where a.OrderNo not in  (
	select top 0 OrderNo  
	from eventlog  as a 
		left join mgrobj as b on a.MgrObjId=b.Id 
		left join addrnode as c on b.AddrId=c.Id  
	where 1=1 and a.AlarmTime>='2014-12-01 00:00:00' and a.AlarmTime<='2014-12-26 23:59:59' 
		and b.AddrId in ('02109000',……,'02109002') 
	order by  AlarmTime desc 
	)  
and 1=1 and a.AlarmTime>='2014-12-01 00:00:00' and a.AlarmTime<='2014-12-26 23:59:59' 
	and b.AddrId in ('02109000',……,'02109002') 
order by  AlarmTime DESC

这是典型的使用两次top来进行分页的写法,原理是:先查出pageSize*(pageIndex-1)(T1)的记录数,然后再TopPageSize条不在T1中的记录,就是当前页的记录。这种查询效率不高主要是使用了not in。参考我之前文章《程序猿是如何解决SQLServer占CPU100%的》提到的:“对于不使用SARG运算符的表达式,索引是没有用的”

那么改为使用ROW_NUMBER分页:

WITH cte AS(
	select a.*,ag.Name as AgentServerName,d.Name as MgrObjTypeName,l.UserName as userName,b.AddrId
			,ROW_NUMBER() OVER(ORDER BY AlarmTime DESC) AS RowNo
		from eventlog as a WITH(FORCESEEK) 
			left join mgrobj as b on a.MgrObjId=b.Id and a.AgentBm=b.AgentBm 
			left join addrnode as c on b.AddrId=c.Id 
			left join mgrobjtype as d on b.MgrObjTypeId=d.Id 
			left join eventdir as e on a.EventBm=e.Bm 
			left join agentserver As ag on a.AgentBm=ag.AgentBm 
			left join loginUser as l on a.cfmoper=l.loginGuid 
		where a.AlarmTime>='2014-12-01 00:00:00' and a.AlarmTime<='2014-12-26 23:59:59' 
			AND b.AddrId in ('02109000',……,'02109002')
)
SELECT * FROM cte WHERE RowNo BETWEEN 1 AND 20;

执行时间从14秒提升到5秒,这说明Row_Number分页还是比较高效的,而且这种写法比top top分页优雅很多。

“欺骗”查询引擎让查询按你的期望去查询

但是为什么查询20条记录竟然要5秒呢,尤其在这个表是加上了时间索引的情况下——参考《程序猿是如何解决SQLServer占CPU100%的》中提到的索引。

我尝试去掉这句AND b.AddrId in ('02109000',……,'02109002'),结果不到1秒就把538条记录查询出来了,而加上地点限制这句,结果是204行。为什么结果集不大,花费的时间却相差这么多呢?查看执行计划,发现走的是另外的索引,而非时间索引。

把这个疑问放到了SQLServer群上,很快,高桑给了回复:要想达到跟去掉地点限制这句的效果,就使用AdddrId+'' in

什么意思?一时没看明白,是高桑没看懂我的语句?很快,有人补充,要欺骗查询引擎。“欺骗”?还是不懂,不过我照做了,把上述cte的语句原封不动的Copy出来,然后把这句AND b.AddrId in ('02109000',……,'02109002')更改为了AND b.AddrId+'' in ('02109000',……,'02109002'),一点执行,神了!!!不到1秒就执行完了。在把执行计划一对,果然走的是时间索引:

后来回味了一下,记起之前看到的查询引擎优化原理,如果你的条件中带有运算符或者使用函数等,则查询引擎会放弃优化,而执行表扫描。脑袋突然转过来了,在使用b.AddrId+''前查询引擎尝试把mgrObj表加入一起做优化,那么两个表联查,会导致预估的记录数大大增加,而使用了b.AddrId+'',查询引擎则会先按时间索引把记录刷选出来,这样就达到了效果,即强制先做cte在执行in条件,而不是在cte中进行in条件刷选。原来如此!有时候,查询引擎过度的优化,会导致相反的效果,而你如果能够知道优化的原理,那么就可以通过一些小的技巧让查询引擎按你的期望去进行优化

ROW_NUMBER()分页在页数较大时的问题

事情到这里,还没完。后面同事又跟我反应,查询到后面的页数,又卡了!what?我重新执行上述语句,把时间范围放到2011-12-01到2014-12-26,记录数限制为为19981到20000,果然,查询要30秒左右,查看执行计划,都是一样的,为什么?

高桑怀疑是key lookup过多导致的,建议先分页取出rid 再做key lookup。不懂这么一句是什么意思。把执行计划和IO打印出来:

看看IO,很明显,主要是越到后面的页数,其他的几个关联表读取的页数就越多。我推测,在Row_Number分页的时候,如果有表连接,则按排序一致到返回的记录数位置,前面的记录都是要参与表连接的,这就导致了越到后面的分页,就越慢,因为要扫描的关联表就越多。

难道就没有了办法了吗?这个时候宋桑英勇的站了出来:“你给表后加一个forceseek提示可破”。这真是犹如天籁之音,马上进行尝试。

使用forceseek提示可以强制表走索引

查了下资料:

SQL Server2008中引入的提示ForceSeek,可以用它将索引查找来替换索引扫描

那么,就在eventlog表中加上这句看看会怎样?

果然,查询计划变了,开始提示,缺少了包含索引。赶紧加上,果然,按这个方式进行查询之后查询时间变为18秒,有进步!但是查看IO,跟上面一样,并没有变少。不过,总算学会了一个新的技能,而宋桑也很热心说晚上再帮忙看看。

把其他没参与where的表放到cte外面

根据上面的IO,很快,又有人提到,把其他left join的表放到cte外面。这是个办法,于是把除eventlogmgrobjaddrnode的表放到外面,语句如下:

WITH cte AS(
	select a*,b.AddrId,b.Name as MgrObjName,b.MgrObjTypeId          
			,ROW_NUMBER() OVER(ORDER BY AlarmTime DESC) AS RowNo
		from eventlog as a
			left join mgrobj as b on a.MgrObjId=b.Id and a.AgentBm=b.AgentBm 
			left join addrnode as c on b.AddrId=c.Id 
		where a.AlarmTime>='2011-12-01 00:00:00' and a.AlarmTime<='2014-12-26 23:59:59' 
			AND b.AddrId+'' in ('02109000',……,'02109002')
)
SELECT a.* 
	,ag.Name as AgentServerName
	,d.Name as MgrObjTypeName,l.UserName as userName
FROM cte a left join eventdir as e on a.EventBm=e.Bm 
			left join mgrobjtype as d on a.MgrObjTypeId=d.Id 
			left join agentserver As ag on a.AgentBm=ag.AgentBm 
			left join loginUser as l on a.cfmoper=l.loginGuid 
WHERE RowNo BETWEEN 19980 AND 20000;

果然有效,IO大大减少了,然后速度也提升到了16秒。

 'loginuser'。扫描计数 1,逻辑读取 63 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
 'agentserver'。扫描计数 1,逻辑读取 1617 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
 'mgrobjtype'。扫描计数 1,逻辑读取 126 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
 'eventdir'。扫描计数 1,逻辑读取 42 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
 'addrnode'。扫描计数 1,逻辑读取 119997 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
 'eventlog'。扫描计数 1,逻辑读取 5027 次,物理读取 3 次,预读 5024 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
 'mgrobj'。扫描计数 1,逻辑读取 24 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

我们看到,addrNode表还是扫描计数很大。那还能不能提升,这个时候,我想到了,先把addrNodemgrobjmgrobjtype三个表联合查询,放到一个临时表,然后再和eventloginner join,然后查询结果再和其他表做left join,这样还能减少IO。

使用临时表存储分页记录在进行表连接减少IO

IF OBJECT_ID('tmpMgrObj') IS NOT NULL DROP TABLE tmpMgrObj
SELECT m.Id,AddrId,MgrObjTypeId,AgentBM,m.Name,a.Name AS AddrName 
	INTO tmpMgrObj  
	FROM dbo.mgrobj m
		INNER JOIN dbo.addrnode a ON a.Id=m.AddrId
	WHERE AddrId IN('02109000',……,'02109002');
WITH cte AS(
	select a.*,b.AddrId,b.MgrObjTypeId          
			,ROW_NUMBER() OVER(ORDER BY AlarmTime DESC) AS RowNo
			,ag.Name as AgentServerName
	,d.Name as MgrObjTypeName,l.UserName as userName
		from eventlog as a
			INNER join tmpMgrObj as b on a.MgrObjId=b.Id and a.AgentBm=b.AgentBm
			left join mgrobjtype as d on b.MgrObjTypeId=d.Id 
			left join agentserver As ag on a.AgentBm=ag.AgentBm 
			left join loginUser as l on a.cfmoper=l.loginGuid 
	WHERE AlarmTime>'2011-12-01 00:00:00' AND AlarmTime<='2014-12-26 23:59:59'
) 
SELECT * FROM cte WHERE RowNo BETWEEN 19980 AND 20000
IF OBJECT_ID('tmpMgrObj') IS NOT NULL DROP TABLE tmpMgrObj

这次查询仅用了10秒。我们来看看IO:

 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
 'mgrobj'。扫描计数 1,逻辑读取 24 次,物理读取 2 次,预读 23 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
 'addrnode'。扫描计数 1,逻辑读取 6 次,物理读取 3 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
----------
 'loginuser'。扫描计数 0,逻辑读取 24 次,物理读取 1 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
 'eventlog'。扫描计数 93,逻辑读取 32773 次,物理读取 515 次,预读 1536 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
 'tmpMgrObj'。扫描计数 1,逻辑读取 3 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
 'mgrobjtype'。扫描计数 1,逻辑读取 6 次,物理读取 1 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
 'agentserver'。扫描计数 1,逻辑读取 77 次,物理读取 2 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

除了eventlog之外,其他的表的IO大大减少,有木有?

强制使用hash join

经网友提示,在大的页数时,可以强制使用hash join来减少IO,而且经过尝试,可以通过建立两个子查询来避免使用临时表。经过调整,最终优化的SQL语句如下:

SELECT  *
	,ag.Name AS AgentServerName
	, l.UserName AS userName
FROM    ( 
	SELECT    a.*,ROW_NUMBER() OVER (ORDER BY AlarmTime DESC) AS RowNo
		, b.AddrName , b.Name AS MgrObjName
	FROM
		(SELECT    * 
			FROM      eventlog
			WHERE     AlarmTime>= '2011-12-01 00:00:00' AND AlarmTime< '2014-12-26 23:59:59') AS a
		INNER HASH JOIN (
			SELECT m.Id,AddrId,MgrObjTypeId,AgentBM,m.Name,a.Name AS AddrName,t.Name AS MgrObjTypeName
			FROM dbo.mgrobj m
				INNER JOIN dbo.addrnode a ON a.Id=m.AddrId
				INNER JOIN dbo.mgrobjtype t ON m.MgrObjTypeId=t.Id
			WHERE AddrId IN('02109000',……,'02109002')
		) AS b ON a.MgrObjId=b.Id AND a.AgentBM=b.AgentBm
		
) tmp 
	LEFT JOIN agentserver AS ag ON tmp.AgentBm = ag.AgentBm
	LEFT JOIN eventdir AS e ON tmp.EventBm = e.Bm
	LEFT JOIN loginUser AS l ON tmp.cfmoper = l.loginGuid
WHERE tmp.RowNo BETWEEN 190001 AND 190020

在大的分页的时候,通过hash查询,不必扫描前面的页数,可以大大减少IO,但是,由于hash join是强制性的,所以使用的时候要注意,我这里应该是个特例。

查询分析器的提示:“警告: 由于使用了本地联接提示,联接次序得以强制实施。”

我们来看看对应情况下的IO:

 'eventlog'。扫描计数 5,逻辑读取 5609 次,物理读取 34 次,预读 5636 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
 'Worktable'。扫描计数 3,逻辑读取 375 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
 'mgrobj'。扫描计数 5,逻辑读取 24 次,物理读取 8 次,预读 40 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
 'mgrobjtype'。扫描计数 1,逻辑读取 6 次,物理读取 1 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
 'addrnode'。扫描计数 3,逻辑读取 18 次,物理读取 6 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
 'loginuser'。扫描计数 1,逻辑读取 60 次,物理读取 2 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
 'eventdir'。扫描计数 1,逻辑读取 40 次,物理读取 0 次,预读 0 次,lob 逻辑读取 30 次,lob 物理读取 0 次,lob 预读 0 次。
 'agentserver'。扫描计数 1,逻辑读取 1540 次,物理读取 1 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。

这次的IO表现非常的好,没有因为查询后面的页数增大而导致较大的IO,查询时间从没有使用hash join的50秒提升为只需12秒,查询时间的开销应该耗费了在hash查找上了。

再看看对应的查询计划,这个时候,主要是因为排序的开销较大。

我们再看看他的预估的和执行的区别,为什么会让排序占如此大的开销?

很明显,预估的时候只需对刷选的结果排序,但是实际执行是对前面所有的页数进行了排序,最终排序占了大部分的开销。那么,这种情况能破吗?请留下您的回复!

其他优化参考

在另外的群上讨论时,发现使用ROW_NUMBER分页查询到后面的页数会越来越慢的这个问题的确困扰了不少的人。

有的人提出,谁会这么无聊,把页数翻到几千页以后?一开始我也是这么想的,但是跟其他人交流之后,发现确实有这么一种场景,我们的软件提供了最后一页这个功能,结果……当然,一种方法就是在设计软件的时候,就去掉这个最后一页的功能;另外一种思路,就是查询页数过半之后,就反向查询,那么查询最后一页其实也就是查询第一页。

还有一些人提出,把查询出来的内容,放到一个临时表,这个临时表中的加入自增Id的索引,这样,可以通过辨别Id来进行快速刷选记录。这也是一种方法,我打算稍后尝试。但是这种方法也是存在问题的,就是无法做到通用,必须根据每个表进行临时表的构建,另外,在超大数据查询时,插入的记录过多,因为索引的存在也是会慢的,而且每次都这么做,估计CPU也挺吃紧。但是不管怎么样,这是一种思路。

你有什么好的建议?不妨把你的想法在评论中提出来,一起讨论讨论。

总结

现在,我们来总结下在这次优化过程中学习到什么内容:

  • 在SQLServer中,ROW_NUMBER的分页应该是最高效的了,而且兼容SQLServer2005以后的数据库
  • 通过“欺骗”查询引擎的小技巧,可以控制查询引擎部分的优化过程
  • ROW_NUMBER分页在大页数时存在性能问题,可以通过一些小技巧进行规避
    • 尽量通过cte利用索引
    • 把不参与where条件的表放到分页的cte外面
    • 如果参与where条件的表过多,可以考虑把不参与分页的表先做一个临时表,减少IO
    • 在较大页数的时候强制使用hash join可以减少io,从而获得很好的性能
  • 使用with(forceseek)可以强制查询因此进行索引查询

最后,感谢SQLServer群的高桑、宋桑、肖桑和其他群友的大力帮助,这个杜绝吹水的群非常的棒,让我这个程序猿学到了很多数据库的知识!

注:经网友提示,2015-01-07 09:15做以下更新

  • 可以在记录数超过10000条,则采用hash join强制进行hash连接,减少IO(感谢27楼riccc)
  • 去掉最先给定的结果中采用left join而不是inner join的连接——left join的结果相当于没有用上addrId in ()的条件(感谢32楼夏浩)

参考文章

捕获和记录SQLServer2016中发生的死锁_包子大叔的笔记-CSDN博客

mikel阅读(716)

来源: 捕获和记录SQLServer2016中发生的死锁_包子大叔的笔记-CSDN博客

1 建表记录死锁日志
–创建阻塞记录表
CREATE TABLE [dbo].[SysBlockLog]
(
[Id] INT IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[BlockingSessesionId] [smallint] NULL,
[ProgramName] [nchar] (128) NULL,
[HostName] [nchar] (128) NULL,
[ClientIpAddress] [varchar] (48) NULL,
[DatabaseName] [sysname] NOT NULL,
[WaitType] [nvarchar] (60) NULL,
[BlockingStartTime] [datetime2] NOT NULL,
[WaitDuration] [bigint] NULL,
[BlockedSessionId] [int] NULL,
[BlockedSQLText] [nvarchar] (MAX) NULL,
[BlockingSQLText] [nvarchar] (MAX) NULL,
[CreationTime] [datetime2] NOT NULL
)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2 创建数据库作业监控和记录死锁
2.1 给作业命名(要先命名)
2.2 新建步骤,选择数据库,作业脚本如下

INSERT INTO SysBlockLog(BlockingSessesionId, ProgramName, HostName, ClientIpAddress, DatabaseName, WaitType, BlockingStartTime,
WaitDuration, BlockedSessionId, BlockedSQLText, BlockingSQLText, CreationTime)
SELECT wt.blocking_session_id AS BlockingSessesionId, sp.program_name AS ProgramName,
COALESCE(sp.LOGINAME, sp.nt_username) AS HostName, ec1.client_net_address AS ClientIpAddress,
db.name AS DatabaseName, wt.wait_type AS WaitType,
ec1.connect_time AS BlockingStartTime, wt.WAIT_DURATION_MS / 1000 AS WaitDuration,
ec1.session_id AS BlockedSessionId, h1.TEXT AS BlockedSQLText,
h2.TEXT AS BlockingSQLText, getdate()
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
LEFT OUTER JOIN master.dbo.sysprocesses sp ON SP.spid = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
2.3 新建“报警”
类型:选择”SQL Server性能条件警报”
对象:SQLServer:General Statistics
计数器:Processes blocked
计数器满足以下条件时触发警报:高于, 值:0

保存作业就可以了
————————————————
版权声明:本文为CSDN博主「zheyiw」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/zheyiw/article/details/103516839

 SQLServer2016重装常见错误和解决办法(菜鸟级呕心沥血经验)_Xue_Yang_的博客-CSDN博客_wincc安装sql2016出错

mikel阅读(1302)

来源: (1条消息) SQLServer2016重装常见错误和解决办法(菜鸟级呕心沥血经验)_Xue_Yang_的博客-CSDN博客_wincc安装sql2016出错

在这里主要说明了一些我所遇见的错误和经验跟大家分享一下,当然也是前辈们的经验。

相信有很多刚入职场或者刚刚尝试使用SQLServer2016的人也可能遇到相同的问题。

在重装SQLServer2016之前,一定要!!!一定要把之前安装过的所有东西清除干净!

第一步:一定要先停止你已经安装过的服务!!打开你的任务管理器切换到服务,左下角打开服务,把所有SQLServer相关的服务都停掉。

 

 

第二步:打开你的控制面板——程序卸载——选择所有跟SQLServer有关的程序进行卸载,卸载顺序order by SQLServer版本desc。记住,优先卸载高版本的主要安装程序!比如

第三步:下载Windows Install Clean Up,选择所有的SQLServer相关程序,点击Remove清除,这里我所显示的是我安装的相关软件

第四步:清理注册表;WIN+R打开控制台输入regedit打开注册表

删除——进入 你的计算机\HKEY_CURRENT_USER\Software\Microsoft\ 删除Microsoft SQL Server 一项

\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft

 

 

圈注的地方都要删掉

 

第四步:清除安装目录文件夹,以及相关文件

找到你的安装目录删除Miscrosoft SQLServer2016

 

我这里安装在C盘 C:\Program Files\Microsoft SQL Server

不要忘了映射文件C:\Program Files (x86)\Microsoft SQL Server

还有实例存放的文件C:\Users

第五步:重启电脑,检查是否清楚完毕,进行重装!这个安装步骤我就不详细说了,大佬们写的都没啥毛病,这里我找了一个觉得还不错的https://blog.csdn.net/andrewniu/article/details/78485312,大家可以去看看。但是要注意两点!!!!第一:安装的时候,在选择实例的时候,自命名一定不要跟之前安装过的实例名字重复,会避免很多错误。第二:选择ReportingService配置的时候,如果没有实际需要,选择仅安装即可。

 

 

下面简单说下常见的几个错误:

1402没有权限访问Components里面的某一项例如计算机\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\UserData\S-1-5-18\Components\676AB5FE5D83B0E4D899B39BA2109BF3\33BFF4F8507743445A8C433D1A1F2DE9

解决办法:打开注册表进入计算机\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\UserData\S-1-5-18\Components

点击Components右键点击权限,高级:将所有者更改为Administrators,在审核一栏添加上Administrators并启用继承将应用改成该项及其子项。应用,确定,关闭,把上面重装之前的卸载步骤进行一边后,重启电脑再次安装。

如果还是不行,就进入C:\Windows将System32和SysWOW64的权限所有者改为Administrators,将完全控制,读取写入打钩,应用即可。他会提示你不能更改,或者降低权限什么的,你就点确定,最后那个一直弹窗就点×,然后确定,再重启,重新安装。

有什么其他的问题,请留言,本菜鸟所有的问题都碰见了,我会一一分享我的解决步骤,让你少走弯路。
————————————————
版权声明:本文为CSDN博主「Xue_Yang_」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/Xue_Yang_/article/details/80240578

SQL Server ->> SQL Server 2016新特性之 --- Query Store - Jerry_Chen - 博客园

mikel阅读(557)

来源: SQL Server ->> SQL Server 2016新特性之 — Query Store – Jerry_Chen – 博客园

前言

SQL Server 2016引入新的查询语句性能监控、调试和优化工具/功能 — Query Store。以前我们发现一条查询语句性能突然下降,我们要去找出问题的所在往往需要通过调用一些DMV(比如sys.dm_exec_query_stats, sys.dm_exec_SQL_text和sys.dm_exec_query_plan)来获取查询计划的一些信息,比如XML格式的执行计划,查询语句的代码,执行了多少次以及一些资源和时间的的使用消耗情况。然后根据这些信息来判断这条语句是否存在性能问题。问题在于但一条语句出现了性能下降,我们可能需要和过去的信息进行一个比较,才能知道性能是否下降了。比如一条语句如果过去花了1秒,现在3-5秒,我们可能第一眼会认定它是没有问题的,但是其实它性能是下降了的。所以我还需要一个任务去定时收集这些资料。比如下面这条语句就是用来收集这些信息的。

复制代码
SELECT s2.dbid, 
    (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 , 
      ( (CASE WHEN statement_end_offset = -1 
         THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2) 
         ELSE statement_end_offset END)  - statement_start_offset) / 2+1))  AS sql_statement,
    s3.query_plan,
    execution_count,     plan_generation_num,     last_execution_time,       total_worker_time,     last_worker_time,     min_worker_time, 
    max_worker_time,    total_physical_reads,     last_physical_reads, 
    min_physical_reads,      max_physical_reads,      total_logical_writes,     last_logical_writes,     min_logical_writes,     max_logical_writes
FROM sys.dm_exec_query_stats AS s1 
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2  
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS s3
复制代码

 

SQL Server 2016引入了Query Store来自动收集数据库的查询计划和相关的一些性能信息,还提供了4种不同类型的报表可以来查看收集的数据。不通过报表,我们也可以通过一些新增的DMV来查询收集好的数据。

那么究竟Query Store可以在哪些方面帮到我们?

1)快速定位查询语句的查询计划性能回退,找出哪些查询语句最近因为查询计划的改变出现性能回退?这当中可能是因为删除了某条索引、过去某个时间点有大量的数据涌入数据表中而统计数据没有及时得以更新、或者最近代码发现了改变等原因。

2)获知查询在某段时间内的资源使用/占用情况以及执行次数。有时语句本身可能执行计划没有回退的情况出现,但是由于语句本身写法问题或者缺乏合理的索引,导致语句的执行非常消耗资源,对于SQL Server整体的性能或者服务器的性能造成很大的印象。作为DBA本身也需要及时去定位这些类型的语句,最后得出一些解决办法。

3)获知数据库过去某段时间内的整体查询工作负荷,包括承受的并发查询压力(量级),资源消耗情况。

 

架构

Query Store存储分两部分,一部分是用于存储编译好的执行计划的Plan Store,另一部分用于存储语句执行执行过程中的一些统计数据。这些数据先是驻留在内存,随后会根据你设定好的时间间隔写入到数据库的主文件组中。

因为Query Store也需要背面的线程来定时写入数据,所以启用Query Store大概会付出3-5%的性能代价。间隔越短,性能的代价越大。间隔大了,由于SQL Server重启导致丢失收集好在内存中但是还没有写入磁盘的那些数据的损失就越大。

Query Store在访问收集好的数据时是会先查看数据是否是否已经在内存中,这样就必须去磁盘找了。只有不在内存中才去磁盘找。它会先调用一个叫QUERY_STORE_RUNTIME_STATS_IN_MEM的表函数去访问内存中的数据,同时访问plan_persist_runtime_stats这张表去访问磁盘的数据。

 

启用Query Store功能

 

通过代码启用Query Store

复制代码
ALTER DATABASE XXXXX SET  QUERY_STORE = ON;

ALTER DATABASE XXXXX
SET QUERY_STORE
  (
  MAX_STORAGE_SIZE_MB = 250,
  SIZE_BASED_CLEANUP_MODE = AUTO,
  CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30)
  );
复制代码

 

上面括号中的选项对应的GUI上选项列表

 

内置Query Store报表

这大概是Query Store最有用的功能。通过报表的形式和已经定制好的性能调优类型的报表来让用户知道过去一段时间内的数据情况。

目前一共提供了4种类型的报表,分别的:回归的查询,总体资源的使用,前几个资源使用的查询,跟踪的查询。

 

Query Store相关的DMV

 

他们的关系

复制代码
select *
from sys.query_store_query qsq join
        sys.query_context_settings qcs on qsq.context_settings_id = qcs.context_settings_id join
        sys.query_store_query_text qst on qst.query_text_id = qsq.query_text_id join
        sys.query_store_plan qsp on qsp.query_id = qsq.query_id join
        sys.query_store_runtime_stats qsrs on qsrs.plan_id = qsp.plan_id join
        sys.query_store_runtime_stats_interval qsrsi on qsrsi.runtime_stats_interval_id = qsrs.runtime_stats_interval_id
复制代码

 

 

参考:

SQL Server 2016:通过Query Store定位回归缺陷

The SQL Server 2016 Query Store: Overview and Architecture

表变量与临时表的优缺点_Gordennizaicunzai的博客-CSDN博客

mikel阅读(554)

来源: 表变量与临时表的优缺点_Gordennizaicunzai的博客-CSDN博客

表变量:

DECLARE @tb  table(id   int   identity(1,1), name   varchar(100))
INSERT @tb

SELECT id, name  FROM mytable   WHERE name like ‘zhang%’

 

临时表:

SELECT name, address
INTO #ta   FROM mytable
WHERE name like ‘zhang%’

(if exists (select * from tempdb.dbo.sysobjects where id = object_id(N’tempdb..#ta’) and type=’U’)
drop table #ta)

 

表变量和临时表的比较:

  • 临时表是利用了硬盘(tempdb数据库) ,表名变量是占用内存,因此小数据量当然是内存中的表变量更快。当大数据量时,就不能用表变量了,太耗内存了。大数据量时适合用临时表。
  • 表变量缺省放在内存,速度快,所以在触发器,存储过程里如果数据量不大,应该用表变量。
  • 临时表缺省使用硬盘,一般来说速度比较慢,那是不是就不用临时表呢?也不是,在数据量比较大的时候,如果使用表变量,会把内存耗尽,然后使用 TEMPDB的空间,这样主要还是使用硬盘空间,但同时把内存基本耗尽,增加了内存调入调出的机会,反而降低速度。这种情况建议先给TEMPDB一次分配合适的空间,然后使用临时表。
  • 临时表相对而言表变量主要是多了I/O时间,但少了对内存资源的占用。数据量较大的时候,由于对内存资源的消耗较少,使用临时表比表变量有更好的性能。
  • 建议:触发器、自定义函数用表变量;存储过程看情况,大部分用表变量;特殊的应用,大数据量的场合用临时表。
  • 表变量有明确的作用域,在定义表变量的函数、存储过程或批处理结束时,会自动清除表变量。
  • 在存储过程中使用表变量与使用临时表相比,减少了存储过程的重新编译量。
  • 涉及表变量的事务只在表变量更新期间存在。这样就减少了表变量对锁定和记录资源的需求。
  • 表变量需要事先知道表结构,普通临时表,只在当前会话中可用与表变量相同into一下就可以了,方便;全局临时表:可在多个会话中使用存在于temp中需显示的drop。(不知道表结构情况下临时表方便一些)
  • 全局临时表的功能是表变量没法达到的。
  • 表变量不必删除,也就不会有命名冲突,临时表特别是全局临时表用的时候必须解决命名冲突。
  • 应避免频繁创建和删除临时表,减少系统表资源的消耗。
  • 在新建临时表时,如果一次性插入数据量很大,那么可以使用select into代替create table,避免log,提高速度;如果数据量不大,为了缓和系统表的资源,建议先create table,然后insert。
  • 如果临时表的数据量较大,需要建立索引,那么应该将创建临时表和建立索引的过程放在单独一个子存储过程中,这样才能保证系统能够很好的使用到该临时表的索引。
  • 如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先truncate table,然后drop table,这样可以避免系统表的较长时间锁定。
  • 慎用大的临时表与其他大表的连接查询和修改,减低系统表负担,因为这种操作会在一条语句中多次使用tempdb的系统表。
————————————————–
问题 1:为什么在已经有了临时表的情况下还要引入表变量?

解答 1:与临时表相比,表变量具有下列优点:

SQL Server 联机丛书“表”(Table) 一文中所述,表变量(如局部变量)具有明确定义的范围,在该范围结束时会自动清除这些表变量。
与临时表相比,表变量导致存储过程的重新编译更少。
涉及表变量的事务仅维持表变量上更新的持续时间。因此,使用表变量时,需要锁定和记录资源的情况更少。因为表变量具有有限的范围并且不是持久性数据库的一部分,所以事务回滚并不影响它们。

问题 2:如果说使用表变量比使用临时表导致存储过程的重新编译更少,这意味着什么?

解答 2:下面的文章讨论了重新编译存储过程的一些原因:

243586  (http://support.microsoft.com/kb/243586/) 存储过程重新编译的疑难解答

“由于某些临时表操作引起的重新编译”一节还列出了为避免一些问题(例如使用临时表导致重新编译)而需要满足的一些要求。这些限制不适用于表变量。

表变量完全独立于创建这些表变量的批,因此,当执行 CREATE 或 ALTER 语句时,不会发生“重新解析”,而在使用临时表时可能会发生“重新解析”。临时表需要此“重新解析”,以便从嵌套存储过程引用该表。表变量完全避免了此问题,因此存储过程可以使用已编译的计划,从而节省了处理存储过程的资源。

问题 3:表变量有哪些缺陷?

解答 3:与临时表相比,它存在下列缺陷:

在表变量上不能创建非聚集索引(为 PRIMARY 或 UNIQUE 约束创建的系统索引除外)。与具有非聚集索引的临时表相比,这可能会影响查询性能。
表变量不像临时表那样可以维护统计信息。在表变量上,不能通过自动创建或使用 CREATE STATISTICS 语句来创建统计信息。因此,在大表上进行复杂查询时,缺少统计信息可能会妨碍优化器确定查询的最佳计划,从而影响该查询的性能。
在初始 DECLARE 语句后不能更改表定义。
表变量不能在 INSERT EXEC 或 SELECT INTO 语句中使用。
表类型声明中的检查约束、默认值以及计算所得的列不能调用用户定义的函数。
如果表变量是在 EXEC 语句或 sp_executeSQL 存储过程外创建的,则不能使用 EXEC 语句或sp_executesql 存储过程来运行引用该表变量的动态 SQL Server 查询。由于表变量只能在它们的本地作用域中引用,因此 EXEC 语句和 sp_executesql 存储过程将在表变量的作用域之外。但是,您可以在 EXEC 语句或 sp_executesql 存储过程内创建表变量并执行所有处理,因为这样表变量本地作用域将位于 EXEC 语句或 sp_executesql 存储过程中。

问题 4:与临时表或永久表相比,表变量的仅存在于内存中的结构保证了更好的性能,是否因为它们是在驻留在物理磁盘上的数据库中维护的?

解答 4:表变量不是仅存在于内存中的结构。由于表变量可能保留的数据较多,内存中容纳不下,因此它必须在磁盘上有一个位置来存储数据。与临时表类似,表变量是在  tempdb 数据库中创建的。如果有足够的内存,则表变量和临时表都在内存(数据缓存)中创建和处理。

问题 5:必须使用表变量来代替临时表吗?

解答 5:答案取决于以下三个因素:

插入到表中的行数。
从中保存查询的重新编译的次数。
查询类型及其对性能的指数和统计信息的依赖性。

在某些情况下,可将一个具有临时表的存储过程拆分为多个较小的存储过程,以便在较小的单元上进行重新编译。

通常情况下,应尽量使用表变量,除非数据量非常大并且需要重复使用表。在这种情况下,可以在临时表上创建索引以提高查询性能。但是,各种方案可能互不相同。Microsoft 建议您做一个测试,来验证表变量对于特定的查询或存储过程是否比临时表更有效。

转自:http://blog.csdn.net/leamonjxl/article/details/6602716
一直以来大家对临时表与表变量的孰优孰劣争论颇多,一些技术群里的朋友甚至认为表变量几乎一无是处,比如无统计信息,不支持事务等等.但事实并非如此.这里我就临时表与表变量做个对比,对于大多数人不理解或是有歧义的地方进行详细说明.
注:这里只讨论一般临时表,对全局临时表不做阐述.
生命周期
临时表:会话中,proc中,或使用显式drop
表变量:batch中
这里用简单的code说明表变量作用域
复制代码
DECLARE @t TABLE(i int) ----定义表变量@t

SELECT *FROM @t        -----访问OK

insert into @t select 1 -----插入数据OK

select * from  @t      -------访问OK
go                     -------结束批处理
select * from @t       -------不在作用域出错
复制代码
注意:虽然说sqlserver在定义表变量完成前不允许你使用定义的变量.但注意下面情况仍然可正常运行!

 


if 'a'='b'
begin
DECLARE @t TABLE(i int)
end
SELECT *FROM @t        -----仍然可以访问!

日志机制
临时表与表变量都会记录在tempdb中记录日志
不同的是临时表的活动日志在事务完成前是不能截断的.
这里应注意的是由于表变量不支持truncate,所以完全清空对象结果集时临时表有明显优势,而表变量只能delete
事务支持
临时表:支持
表变量:不支持
我们通过简单的实例加以说明
复制代码
create table #t (i int)
declare @t table(i int)
BEGIN TRAN ttt
insert into #t select 1
insert into @t select 1
SELECT * FROM #t  ------returns 1 rows
SELECT * FROM @t  ------returns 1 rows
ROLLBACK tran ttt
SELECT * FROM #t    -------no rows
SELECT * FROM @t    -------still 1 rows
drop table #t       ----no use drop @t in session
复制代码

 

锁机制(select)
临时表 会对相关对象加IS(意向共享)锁
表变量 会对相关对象加SCH-S(架构共享)锁(相当于加了nolock hint)
可以看出虽说锁的影响范围不同,但由于作用域都只是会话或是batch中,临时表的IS锁虽说兼容性不如表变量的SCH-S但绝大多数情况基本无影响.
感兴趣的朋友可以用TF1200测试
索引支持
临时表  支持
表变量  条件支持(仅SQL2014)
没错,在sql2014中你可以在创建表的同时创建索引 图1-1
注:在sql2014之前表变量只支持创建一个默认的唯一性约束
code
复制代码
DECLARE @t TABLE 
(
col1 int index inx_1 CLUSTERED, 
col2 int  index index_2 NONCLUSTERED,
       index index_3 NONCLUSTERED(col1,col2)
)

复制代码

图1-1

 

用户自定义函数(UDFs)
临时表 不支持作为UDF的结果集返回
表变量 支持作为UDF的结果集返回
注:当表变量作为UDF的结果集返回时分为TVF(Table-Valued Function),TVP(Table-Valued Parameters)两种类型,只有TVF支持plan cache
如图1-2
Code
复制代码
CREATE FUNCTION TVP_Customers (@cust nvarchar(10))
RETURNS TABLE
AS
 RETURN
 (SELECT RowNum, CustomerID, OrderDate, ShipCountry
 FROM BigOrders
 WHERE CustomerID = @cust);
GO
CREATE FUNCTION TVF_Customers (@cust nvarchar(10))
RETURNS @T TABLE (RowNum int, CustomerID nchar(10), OrderDate date,
 ShipCountry nvarchar(30))
AS
BEGIN
 INSERT INTO @T
  SELECT RowNum, CustomerID, OrderDate, ShipCountry
  FROM BigOrders
  WHERE CustomerID = @cust
  RETURN
END;

DBCC FREEPROCCACHE
GO
SELECT * FROM TVF_Customers('CENTC');
GO
SELECT * FROM TVP_Customers('CENTC');
GO
SELECT * FROM TVF_Customers('SAVEA');
GO
SELECT * FROM TVP_Customers('SAVEA');
GO

select b.text,a.execution_count,a.* from sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(a.sql_handle) b
where b.text like '%_Customers%'
复制代码

图1-2

 

其它方面
表变量不支持select into,alter,truncate,dbcc等
表变量不支持table hint 如(force seek)

 

执行计划预估
我想这里可能是引起使用何种方式争论比较突出的地方,由于表变量没有统计信息,无法添加索引等使得大家对其在执行计划中的性能表现嗤之以鼻,但实际情况呢?我们需要深入分析.
关于临时表的预估这里我就不做介绍了,主要对表变量的预估做详细阐述.
表变量在sql2000引入的一个原因就是为了在一些执行过程中减少重编译.以获得更好的性能.当然带来好处的同时也会带来一定弊端.由于其不涉及重编译,优化器其实并不知道表变量中的具体行数,此时他采取了保守的预估方式:预估行数为1行.如图2-1

Code

复制代码
declare @t table (i int)
select * from @t-----此时0行预估行数为1行
insert into @t select 1
select * from @t-----此时1行,预估行数仍为1行
insert into @t values (2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(14),(15),(16),(17),(18),(19),(20)
select * from @t ----此时19行,预估行数仍为1行

--....无论实际@t中有多少行,由于没有重编译,预估均为1行
复制代码

 

图2-1

所以当我们加上重编译的的操作,此时优化器就知道了表变量的具体行数.如图2-2

Code

 

复制代码
declare @t table (i int)
select * from @t option(recompile)-----此时0行预估行数为1行
insert into @t select 1
select * from @t  option(recompile)-----此时1行,预估行数为1行
insert into @t values (2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(14),(15),(16),(17),(18),(19),(20)
select * from @t  option(recompile)----此时19行,预估行数为19行
--....当加入重编译hint时,优化器就知道的表变量的行数.
复制代码

 

图2-2

 

至此,我们可以看到优化器知道了表变量中的行数.这样在表变量扫描的过程中,尤其针对数据量较大的情形,不会因为预估总是1而引起一些问题.
如果你刚知道这里的预估原理,现有的代码都加上重编译那工作量可想而知了..这里介绍一个新的跟踪标记,Trace Flag 2453.
TF2453可以一定程度上替代重编译Hint,但只是在非简单计划(trivial plans)的情形下
注:TF2453只在sql2012 SP2和SQL2014中的补丁中起作用

表变量谓词预估
由于表变量木有统计信息,在优化器知道整体行数的前提下将会根据谓词的情形
采用不同的规则"猜"来进行预估.
注:这里有些规则笔者未找到微软相应的算法文档,经过自己根据数据推算得出.
看到这里的朋友请为我点个赞J(很长时间推算得出.可能数学忘得差不多了)
注:由于检索对象本身及为变量,谓词为变量,或是常数无影响
常见谓词下预估算法:
a ">", "<" 运算符 按照表变量数据量的30%进行预估
b "like" 运算符 按照表变量数据量的10%进行预估
c "="  运算符 按照表变量数据量的0.75次方预估
实例如图2-3
code
复制代码
declare @i int
set @i=13
DECLARE @T TABLE(I INT);
INSERT INTO @T VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(14),(15),(16),(17),(18),(19),(20)
------表变量中存在个数字
select * from @T where I < 1  option(recompile) ------20*30% 预估数为6
select * from @T where I > @i option(recompile) --------20*30%预估数为6
select * from @T where I like @i  option(recompile) --------20*10% 预估数为2
select * from @T where I like 1  option(recompile)  --------20*10 预估数为2
select * from @T where I = @i  option(recompile) --------POWER(20.00000,0.75) 预估数为9.45742
select * from @T where I = 1  option(recompile)  --------POWER(20.00000,0.75) 预估数为9.45742

insert into @T
select DatabaseLogID from AdventureWorks2008R2.dbo.DatabaseLog------insert new records
select * from @T option(recompile) ------------此时数据为行
select * from @T where I = 1  option(recompile)--------------------POWER(1617.00000,0.75) 预估数为254.99550
复制代码

图2-3

可以看出根据不同的谓词优化器会采用不同的预估方式,虽然它不如统计信息下的密度,直方图等来的精确(尤其是等值预估,在数据量巨大的情形下,其效果可能接近统计信息),但在了解数据的前提下如果适合表变量我们还是可以大胆使用的.

Tempdb竞争
tempdb的竞争本身涵盖的知识面比较大,这里我们只讨论临时表与表变量的孰优孰劣.
通过前面的介绍我们知道临时表是支持事务的,而表变量时不支持的.正因如此很多人放弃了表变量的使用.但任何事情都有两方面,支持就一定好吗?由于临时表对事务的支持,在高并发的情形中可能正因为其事务的支持造成系统表锁,总而影响并发.

我们通过一个简单的实例来说明

日常管理中,我发现很多开发人员在使用临时表时采用select * into #t from …的语法,这样的写法如果数据量稍大,将会造成事务持有系统表锁的时间变长,从而影响并发,吞吐.我们通过一个简单的实例说明.如图3-1

 

Code 我们通过sqlquerystress模拟并发

复制代码
----SSMS测试数据
Use tempdb
create table t
( id int identity,str1 char(8000))----more pages for many records

insert into t select 'a'
go 100

----sqlquerystress
select * into #t
from t----57s

----sqlquerystress
declare @t table
( id int,str1 char(8000))
insert into @t
select * from t-----1s
复制代码

 

图3-1

 

通过图3-1可以看出上述情形中临时表简直不堪重负.临时表与表变量到底该如何应用不是看谁比谁的优点多,应视具体情形而定

结语:借用火影忍者中宇智波. 鼬的一句名言:”任何术都是有缺陷的” 同样,在数据库的世界里没有哪项技术是完美无缺的.根据实际的场景,情形,选择合理的实现方式才是我们的初衷.

转自:http://www.cnblogs.com/shanksgao/p/3988089.html

临时表VS表变量--因地制宜,合理使用 - ShanksGao - 博客园

mikel阅读(676)

来源: 临时表VS表变量–因地制宜,合理使用 – ShanksGao – 博客园

一直以来大家对临时表与表变量的孰优孰劣争论颇多,一些技术群里的朋友甚至认为表变量几乎一无是处,比如无统计信息,不支持事务等等.但事实并非如此.这里我就临时表与表变量做个对比,对于大多数人不理解或是有歧义的地方进行详细说明.
注:这里只讨论一般临时表,对全局临时表不做阐述.
生命周期
临时表:会话中,proc中,或使用显式drop
表变量:batch中
这里用简单的code说明表变量作用域
复制代码
DECLARE @t TABLE(i int) ----定义表变量@t

SELECT *FROM @t        -----访问OK

insert into @t select 1 -----插入数据OK

select * from  @t      -------访问OK
go                     -------结束批处理
select * from @t       -------不在作用域出错
复制代码
注意:虽然说sqlserver在定义表变量完成前不允许你使用定义的变量.但注意下面情况仍然可正常运行!

 


if 'a'='b'
begin
DECLARE @t TABLE(i int)
end
SELECT *FROM @t        -----仍然可以访问!

日志机制
临时表与表变量都会记录在tempdb中记录日志
不同的是临时表的活动日志在事务完成前是不能截断的.
这里应注意的是由于表变量不支持truncate,所以完全清空对象结果集时临时表有明显优势,而表变量只能delete
事务支持
临时表:支持
表变量:不支持
我们通过简单的实例加以说明
复制代码
create table #t (i int)
declare @t table(i int)
BEGIN TRAN ttt
insert into #t select 1
insert into @t select 1
SELECT * FROM #t  ------returns 1 rows
SELECT * FROM @t  ------returns 1 rows
ROLLBACK tran ttt
SELECT * FROM #t    -------no rows
SELECT * FROM @t    -------still 1 rows
drop table #t       ----no use drop @t in session
复制代码

 

锁机制(select)
临时表 会对相关对象加IS(意向共享)锁
表变量 会对相关对象加SCH-S(架构共享)锁(相当于加了nolock hint)
可以看出虽说锁的影响范围不同,但由于作用域都只是会话或是batch中,临时表的IS锁虽说兼容性不如表变量的SCH-S但绝大多数情况基本无影响.
感兴趣的朋友可以用TF1200测试
索引支持
临时表  支持
表变量  条件支持(仅SQL2014)
没错,在sql2014中你可以在创建表的同时创建索引 图1-1
注:在sql2014之前表变量只支持创建一个默认的唯一性约束
code
复制代码
DECLARE @t TABLE 
(
col1 int index inx_1 CLUSTERED, 
col2 int  index index_2 NONCLUSTERED,
       index index_3 NONCLUSTERED(col1,col2)
)

复制代码

图1-1

 

用户自定义函数(UDFs)
临时表 不支持作为UDF的结果集返回
表变量 支持作为UDF的结果集返回
注:当表变量作为UDF的结果集返回时分为TVF(Table-Valued Function),TVP(Table-Valued Parameters)两种类型,只有TVF支持plan cache
如图1-2
Code
复制代码
CREATE FUNCTION TVP_Customers (@cust nvarchar(10))
RETURNS TABLE
AS
 RETURN
 (SELECT RowNum, CustomerID, OrderDate, ShipCountry
 FROM BigOrders
 WHERE CustomerID = @cust);
GO
CREATE FUNCTION TVF_Customers (@cust nvarchar(10))
RETURNS @T TABLE (RowNum int, CustomerID nchar(10), OrderDate date,
 ShipCountry nvarchar(30))
AS
BEGIN
 INSERT INTO @T
  SELECT RowNum, CustomerID, OrderDate, ShipCountry
  FROM BigOrders
  WHERE CustomerID = @cust
  RETURN
END;

DBCC FREEPROCCACHE
GO
SELECT * FROM TVF_Customers('CENTC');
GO
SELECT * FROM TVP_Customers('CENTC');
GO
SELECT * FROM TVF_Customers('SAVEA');
GO
SELECT * FROM TVP_Customers('SAVEA');
GO

select b.text,a.execution_count,a.* from sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(a.sql_handle) b
where b.text like '%_Customers%'
复制代码

 

图1-2

 

其它方面
表变量不支持select into,alter,truncate,dbcc等
表变量不支持table hint 如(force seek)

 

执行计划预估
我想这里可能是引起使用何种方式争论比较突出的地方,由于表变量没有统计信息,无法添加索引等使得大家对其在执行计划中的性能表现嗤之以鼻,但实际情况呢?我们需要深入分析.
关于临时表的预估这里我就不做介绍了,主要对表变量的预估做详细阐述.
表变量在sql2000引入的一个原因就是为了在一些执行过程中减少重编译.以获得更好的性能.当然带来好处的同时也会带来一定弊端.由于其不涉及重编译,优化器其实并不知道表变量中的具体行数,此时他采取了保守的预估方式:预估行数为1行.如图2-1

Code

复制代码
declare @t table (i int)
select * from @t-----此时0行预估行数为1行
insert into @t select 1
select * from @t-----此时1行,预估行数仍为1行
insert into @t values (2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(14),(15),(16),(17),(18),(19),(20)
select * from @t ----此时19行,预估行数仍为1行

--....无论实际@t中有多少行,由于没有重编译,预估均为1行
复制代码

 

 

图2-1

所以当我们加上重编译的的操作,此时优化器就知道了表变量的具体行数.如图2-2

Code

 

复制代码
declare @t table (i int)
select * from @t option(recompile)-----此时0行预估行数为1行
insert into @t select 1
select * from @t  option(recompile)-----此时1行,预估行数为1行
insert into @t values (2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(14),(15),(16),(17),(18),(19),(20)
select * from @t  option(recompile)----此时19行,预估行数为19行
--....当加入重编译hint时,优化器就知道的表变量的行数.
复制代码

 

图2-2

 

至此,我们可以看到优化器知道了表变量中的行数.这样在表变量扫描的过程中,尤其针对数据量较大的情形,不会因为预估总是1而引起一些问题.
如果你刚知道这里的预估原理,现有的代码都加上重编译那工作量可想而知了..这里介绍一个新的跟踪标记,Trace Flag 2453.
TF2453可以一定程度上替代重编译Hint,但只是在非简单计划(trivial plans)的情形下
注:TF2453只在sql2012 SP2和SQL2014中的补丁中起作用

表变量谓词预估
由于表变量木有统计信息,在优化器知道整体行数的前提下将会根据谓词的情形
采用不同的规则"猜"来进行预估.
注:这里有些规则笔者未找到微软相应的算法文档,经过自己根据数据推算得出.
看到这里的朋友请为我点个赞J(很长时间推算得出.可能数学忘得差不多了)
注:由于检索对象本身及为变量,谓词为变量,或是常数无影响
常见谓词下预估算法:
a ">", "<" 运算符 按照表变量数据量的30%进行预估
b "like" 运算符 按照表变量数据量的10%进行预估
c "="  运算符 按照表变量数据量的0.75次方预估
实例如图2-3
code
复制代码
declare @i int
set @i=13
DECLARE @T TABLE(I INT);
INSERT INTO @T VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(14),(15),(16),(17),(18),(19),(20)
------表变量中存在个数字
select * from @T where I < 1  option(recompile) ------20*30% 预估数为6
select * from @T where I > @i option(recompile) --------20*30%预估数为6
select * from @T where I like @i  option(recompile) --------20*10% 预估数为2
select * from @T where I like 1  option(recompile)  --------20*10 预估数为2
select * from @T where I = @i  option(recompile) --------POWER(20.00000,0.75) 预估数为9.45742
select * from @T where I = 1  option(recompile)  --------POWER(20.00000,0.75) 预估数为9.45742

insert into @T
select DatabaseLogID from AdventureWorks2008R2.dbo.DatabaseLog------insert new records
select * from @T option(recompile) ------------此时数据为行
select * from @T where I = 1  option(recompile)--------------------POWER(1617.00000,0.75) 预估数为254.99550
复制代码

 

图2-3

可以看出根据不同的谓词优化器会采用不同的预估方式,虽然它不如统计信息下的密度,直方图等来的精确(尤其是等值预估,在数据量巨大的情形下,其效果可能接近统计信息),但在了解数据的前提下如果适合表变量我们还是可以大胆使用的.

Tempdb竞争
tempdb的竞争本身涵盖的知识面比较大,这里我们只讨论临时表与表变量的孰优孰劣.
通过前面的介绍我们知道临时表是支持事务的,而表变量时不支持的.正因如此很多人放弃了表变量的使用.但任何事情都有两方面,支持就一定好吗?由于临时表对事务的支持,在高并发的情形中可能正因为其事务的支持造成系统表锁,总而影响并发.

我们通过一个简单的实例来说明

日常管理中,我发现很多开发人员在使用临时表时采用select * into #t from …的语法,这样的写法如果数据量稍大,将会造成事务持有系统表锁的时间变长,从而影响并发,吞吐.我们通过一个简单的实例说明.如图3-1

 

Code 我们通过SQLquerystress模拟并发

复制代码
----SSMS测试数据
Use tempdb
create table t
( id int identity,str1 char(8000))----more pages for many records

insert into t select 'a'
go 100

----sqlquerystress
select * into #t
from t----57s

----sqlquerystress
declare @t table
( id int,str1 char(8000))
insert into @t
select * from t-----1s
复制代码

 

 

图3-1

 

通过图3-1可以看出上述情形中临时表简直不堪重负.临时表与表变量到底该如何应用不是看谁比谁的优点多,应视具体情形而定

结语:借用火影忍者中宇智波. 鼬的一句名言:”任何术都是有缺陷的” 同样,在数据库的世界里没有哪项技术是完美无缺的.根据实际的场景,情形,选择合理的实现方式才是我们的初衷.