一句T-SQL语句引发的思考

最新推荐文章于 2024-04-22 23:31:26 发布
最新推荐文章于 2024-04-22 23:31:26 发布
阅读量41 收藏
点赞数
文章标签： 数据库 大数据

优化一句T-SQL语句引发的思考

最近WWW.CSDN.NET上有一个帖子：
http://expert.csdn.net/Expert/topic/2630/2630484.xml?temp=.9921686

问题是这样:
 有表Stress_test(id  int, key char(2))
        id 上有普通索引;
        key 上有簇索引;
        id 有有限量的重复;
        key 有无限量的重复;

现在我需要按逻辑与查询表中key='Az' AND key='Bw' AND key='Cv' 的id

求教高手最有效的查询语句

测试环境：
     Hardware:P4 2.6+512M+80G
     Software:windows server 2003(Enterprise Edition)+Sqlserver 2000 +sp3a

  首先我们建立一个测试的数据，为使数据尽量的分布和随即，我们通过RAND()来随机产生2个随机数再组合成一个字符串，首先插入的数据是1,000,000条记录，然后在循环插入到58,000,000条记录。
   因为是随机产生的数据，所以如果你自己测试的数据集和我测试的会不一样，但对索引的优化和运行的效率是一样的。
   下面的“--//测试脚本”是产生测试数据的脚本，你可以根据需要修改 @maxgroup， @maxLoop的值，比如测试1百万的记录可以:

     Select @maxgroup=1000
     Select @maxLoop=1000

如果要测试5千万：

     Select @maxgroup=5000
     Select @maxLoop=10000

所以如果你的SERVER或PC比较慢，请耐心等待.....，
 （在我的PC上运行的速度是插入1百万条的时间是1.14m，插入5千八百万条的时间是19.41m，重新建立INDEX的时间是34.36m)

 

作为一般的开发人员很容易就想到的语句：

   --语句1

    select a.[id] from 
    (select distinct [id] from stress_test where [key] = 'Az') a,
    (select distinct [id] from stress_test where [key] = 'Bw') b ,
    (select distinct [id] from stress_test where [key] = 'Cv') c
    where a.id = b.id and a.id = c.id

   --语句2

     select [id] 
     from stress_test 
     where [key]='Az' or [key]='Bw' or [key]='Cv'
     group by id having(count(distinct [key])=3)  

   --语句5

    SELECT distinct a.[id] FROM stress_test AS a,stress_test AS b,stress_test AS c
    WHERE a.[key]='Az' AND b.[key]='Bw' AND c.[key]='Cv'
      AND a.[id]=b.[id] AND a.[id]=c.[id]

但作为T-SQL的所谓“高手”可能会认为这种写法很“土”，也显得没有水平，所以会选择一些子查询和外连接的写法，按常理子查询的效率是比较高的：

   --语句3

    select distinct [id] from stress_test A where
    not exists (
    select 1 from
     (select 'Az' as k union all select 'Bw' union all select 'Cv') B
    left join stress_test C on  C.id=A.id and B.[k]=C.[key]
    where C.id is null)

   --语句4

     select distinct a.id from stress_test a
     where not exists
     ( select * from keytb c
      where not exists
      ( select * from stress_test b
       where
             b.id = a.id
             and
             c.kf1 = b.[key]
       )
     )

我们先分析这几条语句（针对5千8百万条数据进行分析）：

请大家要特别留心Estimated row count的值。

语句1:从执行规划中我们可以看出，MSSQLSERVER选择的索引优化非常有规律，先通过CLUSTERED INDEX筛选出符合[KEY]='Az'条件的ID,然后进行HASH MATCH,在找出ID相等的；依次类推最终检索到符合所有条件的记录。中间的Estimated row count的值都不大。

语句2:从执行规划中我们可以看出，是先通过CLUSTERED INDEX筛选出符合 [key]='Az' or [key]='Bw' or [key]='Cv' 符合所有条件的ID，然后分组进行2次HASH MATCH 所有的ID。我们可以看出Estimated row count的值是越来越少，从最初的369,262到最后排序的只有402。

语句3:从执行规划中我们可以看是非常复杂的，是先通过3组 通过CONSTANT SCAN和NON-CLUSTERED INDEX检索出符合 A.ID=C.ID AND [key]='**' 的记录3组，然后分组进行外键匹配，再将3组的数据合并，排序，然后再和一个NON-CLUSTERED INDEX检索出的记录集进行外键匹配，我们可以看出MSSQLSERVER会对所有的记录（5千万条）记录进行分组，Estimated row count的值是：58,720,000,所以这句T-SQL的瓶颈是对5千万条记录进行分组。

语句4:从执行规划中我们可以看和语句3有相似之处，都要对所有的记录（5千万条）记录进行分组，所以这是检索的瓶颈，而且使用的索引都是NON-CLUSTERED INDEX。

语句5：从执行规划中我们可以看出，先通过CLUSTERED INDEX检索出符合[Key]='Az'的记录集，然后进行HASH MATCH和SORTS,因为数量少所以是非常会的，在和通过NON-CLUSTERED INDEX检索[KEY]='Bw'的记录进行INNER JOIN,在和通过CLUSTERED INDEX检索[KEY]='Cv'的记录进行合并,最后是对4百万条数据进行分组检索，如果是6列，我们可以看出Estimated row count的值是递增，越来越大，最后的分组检索的Estimated row count的值是3.46E+15,这已经形成巨大的瓶颈。

我们可以先测试一下小的数据量（50000条）；

大家可以下面测试脚本的：

   Select @maxgroup=500
   Select @maxLoop=100

----------------------------------------------------------------------
 |------------------语句 1----语句 2----语句 3----语句 4----语句 5----|
 | 5万（3列）        5ms       19ms     37ms     59ms      0ms
 | 5万（6列）        1ms       26ms     36ms     36ms     1ms
 

从测试的的数据来看，语句5的效率是最高的，几乎没有花费时间，而语句2的效率只能说是一般。如果测试到这里就结束了，我们可以毫不犹豫的选择语句 5 :-(,继续进行下面的测试.....

我们测试百万条以上的记录：
 1.先对1百万条记录进行测试（选取3列）
 2.先对1百万条记录进行测试（选取6列）
 3.对5千万条数据测试（选取3列）
 4.对5千万条数据测试（选取6列）

统计表1：
 ----------------------------------------------------------------------
 |------------------语句 1----语句 2----语句 3----语句 4----语句 5----|
 | 1百万（3列）    0.77%     0.41%    49.30%     48.99%     0.52%
 | 1百万（6列）     1.61%     0.81%    48.99%     47.44%     1.14%
 | 5千万（3列）     0.14%     0.18%    48.88%     48.86%     1.93%
 | 5千万（6列）     0.00%     0.00%     0.00%      0.00%   100.00%
统计表2：
 ----------------------------------------------------------------------
 |------------------语句 1----语句 2----语句 3----语句 4----语句 5----|
 | 1百万（3列）     9ms       22ms     723ms     753ms      4ms
 | 1百万（6列）      15ms      38ms     764ms     773ms     11ms
 | 5千万（3列）     575ms     262ms  110117ms  110601ms  12533ms
 | 5千万（6列）    1070ms     576ms  107988ms  109704ms     10m以上


测试总结：（我们可以比较关注：语句 2和语句 5）
1.在1百万条记录的情况下，语句 5是最快的，但在5千万条记录下是最慢的。这说明INDEX的优化一定的情况下，数据量不同，检索的效率也是不同的。我们平时在写T-SQL时一般关注的时INDEX的使用，只要我们写的T-SQL是利用CLUSTERED INDEX，我们就认为是最优化了，其实这是一个误区，我们还要关注Estimated row count的值，大量的I/O操作是我们应该关注的，所以我们应该根据数据量的不同选择相应的T-SQL语句，不要认为在小数据量下是最高的在大数据量的状态下也许是最慢的:-(。

2.在执行规划中最快的，并不是运行最快的，我们可以看在1百万（6列）在这行中，语句 2和语句 5的比例是0.81％：1.14％，但实际的运行效率是，38ms:11ms。所以，我们在选择T-SQL是要考虑本地I/O的速度，所以在优化语句时不仅要看执行规划还要计算一下具体的效率。

在测试的语句上加入：

    SET STATISTICS TIME  ON/OFF
    SET STATISTICS IO  ON/OFF
是一个很好的调试方法。


3.综合评价，语句 2的效率是最高的，执行效率没有随数据量变化而有很大的差别。

4.执行规划越简单的语句（语句1），综合效率越高，反之则越低（语句3，语句4）。

5.在平时写T-SQL语句时，一定要根据不同的数据量进行测试，虽然都是用CLUSTERED INDEX，但检索的效率却大相径庭。

--//测试脚本
USE Northwind
GO
if exists(select * from sysobjects where name=N'stress_test' and type='U')
Drop table stress_test
GO
--//定义测试的表stress_test,存放所有的测试数据
Create table stress_test([id] int,[key] char(2))

GO
--//插入测试的数据
Set nocount on
--//变量定义
Declare @id int   --//Stress_test ID 值
Declare @key char(2)  --//Stress_test [key] 值
Declare @maxgroup int  --//组最大的循环数
Declare @maxLoop int  --//ID最大的循环数
Declare @tempGroup int  --//临时变量
Declare @tempLoop int  --//临时变量
Declare @tempint1 int  --//临时变量
Declare @tempint2 int  --//临时变量
Declare @rowcount int  --//记录事务提交的行数

--//初始化变量
Select @id=1
Select @maxgroup=1000
Select @maxLoop=1000
Select @tempGroup=1
Select @tempLoop=1
Select @key=''
Select @rowcount=0

while @tempLoop<=@maxLoop
begin
 while @tempGroup<=@maxGroup
 begin
  select @tempint1=65+convert(int,rand()*50)
  select @tempint2=65+convert(int,rand()*100)
  if (@tempint1>=122 or @tempint2>=122)
    begin
    select @tempint1=@tempint1-100
    select @tempint2=@tempint2-100
   
    if (@tempint1<=65 or @tempint2<=65)
     begin
     select @tempint1=@tempint1+57
     select @tempint2=@tempint2+57
    end
   end
  select @key=char(@tempint1)+char(@tempint2)
  if @rowcount=0
  begin tran ins
     insert into stress_test([id],[key])values(@id,@key)
       select @rowcount=@rowcount+1
  
   if @rowcount>3000 --//判断当行数达到3000条时，开始提交事务
   begin
       commit tran ins
      select @rowcount=0
   end
  
  select @tempGroup=@tempgroup+1
 end
 if @rowcount>0
 begin
  commit tran ins
  select @rowcount=0
 end

 select @tempGroup=1
 select @id=@id+1
 select @tempLoop=@tempLoop+1
end
GO
--//删除KEY值为NULL的记录
delete stress_test where [key]is null
GO
--//建立簇索引PK_STRESS
Create Clustered index pk_stress on stress_test([Key])
--//建立非簇索引NI_STRESS_ID
Create NonClustered index NI_stress_id on stress_test([id])
GO
--//定义测试的表keytb
if exists(select * from sysobjects where name=N'keytb' and type='U')
Drop table keytb
GO
create table keytb   -----//存放你需要匹配的值的表
(
  kf1  varchar(20)
)

--//存放你需要匹配的值，暂定为三个
insert into keytb(kf1) values('Az');
insert into keytb(kf1) values('Bw');
insert into keytb(kf1) values('Cv');

--insert into keytb(kf1) values('Du');
--insert into keytb(kf1) values('Ex');
--insert into keytb(kf1) values('Fy');
GO


下面我们就开始测试几种T-SQL的INDEX优化问题：

--先对1百万条/1亿条记录进行测试（选取3列）的T-SQL:

PRINT '第一种语句：'
SET STATISTICS TIME  ON
SET STATISTICS IO  ON
select a.[id] from
(select distinct [id] from stress_test where [key] = 'Az') a,
(select distinct [id] from stress_test where [key] = 'Bw') b ,
(select distinct [id] from stress_test where [key] = 'Cv') c
where a.id = b.id and a.id = c.id
GO
PRINT '第二种语句：'
select [id]
from stress_test 
where [key]='Az' or [key]='Bw' or [key]='Cv'
group by id having(count(distinct [key])=3)
GO
PRINT '第三种语句：'
select distinct [id] from stress_test A where
not exists (
select 1 from
(select 'Az' as k union all select 'Bw' union all select 'Cv') B
left join stress_test C on  C.id=A.id and B.[k]=C.[key]
where C.id is null)
GO
PRINT '第四种语句：'
select distinct a.id from stress_test a
 where not exists
 ( select * from keytb c
   where not exists
   ( select * from stress_test b
     where
       b.id = a.id
       and
       c.kf1 = b.[key]
   )
 )
GO
PRINT '第五种语句：'
SELECT distinct a.[id] FROM stress_test AS a,stress_test AS b,stress_test AS c
WHERE a.[key]='Ac' AND b.[key]='Bb' AND c.[key]='Ca'
      AND a.[id]=b.[id] AND a.[id]=c.[id]

GO
SET STATISTICS TIME  OFF
SET STATISTICS IO  OFF

--先对1百万条/1亿条记录进行测试（选取6列）的T-SQL:
PRINT '第一种语句：'
SET STATISTICS TIME  ON
SET STATISTICS IO  ON
select a.[id] from
(select distinct [id] from stress_test where [key] = 'Az') a,
(select distinct [id] from stress_test where [key] = 'Bw') b ,
(select distinct [id] from stress_test where [key] = 'Cv') c,
(select distinct [id] from stress_test where [key] = 'Du') d,
(select distinct [id] from stress_test where [key] = 'Ex') e,
(select distinct [id] from stress_test where [key] = 'Fy') f
where a.[id] = b.[id] and a.[id] = c.[id] and a.[id] = d.[id] and a.[id] = e.[id] and a.[id] = f.[id]
GO
PRINT '第二种语句：'
select [id]
from stress_test 
where [key]='Az' or [key]='Bw' or [key]='Cv' or [Key]='Du'or [Key]='Ex'or [Key]='Fy'
group by id having(count(distinct [key])=6)
GO
PRINT '第三种语句：'
select distinct [id] from stress_test A where
not exists (
select 1 from
(select 'Az' as k union all select 'Bw' union all select 'Cv'union all select 'Du'union all select 'Ex'union all select 'Fy') B
left join stress_test C on  C.id=A.id and B.[k]=C.[key]
where C.id is null)
GO
PRINT '第四种语句：'
select distinct a.id from stress_test a
 where not exists
 ( select * from keytb c
   where not exists
   ( select * from stress_test b
     where
       b.id = a.id
       and
       c.kf1 = b.[key]
   )
 )
GO
PRINT '第五种语句：'
SELECT distinct a.[id] FROM stress_test AS a,stress_test AS b,stress_test AS c,stress_test AS d,stress_test AS e,stress_test AS f
WHERE a.[key]='Az' AND b.[key]='Bw' AND c.[key]='Cv' AND d.[key]='Du' AND e.[key]='Ex' AND f.[key]='Fy'
     and a.[id] = b.[id] and a.[id] = c.[id] and a.[id] = d.[id] and a.[id] = e.[id] and a.[id] = f.[id]

GO
SET STATISTICS TIME  OFF
SET STATISTICS IO  OFF

GO

weixin_34192816
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
记不住sql命令怎么办？试试用这款工具为你自动补全吧
CN_Eden
12-27 1021
mycli介绍： mycli是MySQL的客户端工具，主要用于在命令行连接MySQL服务器使用。这个工具的主要好处就是可以实现sql语法的自动补全和语法高亮，这对于初学MySQL的新手来说，是非常友好的。 如果你的环境是全新的（刚安装的centos系统），还没有将默认的镜像文件替换为国内的镜像文件，或者还不会配置，那么可以用下面的方法实现： 第一步：安装wget命令： 如果没有现成的已经配置好的国内镜像的其他服务器（即不能直接传镜像文件过来），那么可以先利用系统自带的镜像文件安装wget命令。安装好后，在利
通过T-SQL语句实现数据库备份与还原的代码
09-11
利用T-SQL语句,实现数据库的备份与还原的功能，需要的朋友可以参考下。
sap hana 迁移 oracle,系统迁移HANA 示例及问题诊断（一）
weixin_34221599的博客
04-09 645
系统迁移HANA 示例及问题诊断1.简介本blog演示将bw on oracle系统迁移至bw on HANA，并对常见问题作出解答。2.最佳实践2.1. 导出源系统 (BW on Oracle)2.1.1 SMIGR_CREATE_DDL (SE38)这个ABAP程序会针对BW表进行准备工作。这一步，将会生成用于在目标系统创建表的DDL语句。生成的rowstorelist.txt列出了将会以行存...
第三章 T-SQL基本语句（学习笔记）
weixin_34378969的博客
01-10 124
一：基本的SELECT 语句 select [ALL|DISTINCT] [TOP(<expression>) [PERCENT] [WITH TIES] ] <column list> [FROM <source tables/views>] [WHERE <restrictive condition...
使用T-SQL语句创建和删除数据库
12-14
** 使用T-SQL语句创建和删除数据库** 使用T-SQL语句创建“学生管理库”数据库及删除数据库。要求“学生管理库”数据库的主数据文件名为：StudentDB.mdf，初始大小为5MB，最大为20MB，增长方式为10%；日志文件名为：...
T-sql语句修改SQL Server数据库逻辑名、数据库名、物理名的方法
12-15
本文实例讲述了T-sql语句修改SQL Server数据库逻辑名、数据库名、物理名的方法。分享给大家供大家参考，具体如下： 更改MSSQL数据库物理文件名Sql语句的写法 注意：要在活动监视器里面确保没有进程连接你要改名的...
SQL语句(T-SQL汇总) 用T-SQL画出这些图形
09-11
今天和大家一起来分享一下SQL语句，T-SQL的用法和语法，以及一些例子，目的是帮助大家一起来复习一下T-SQL的基本语句，以便在项目中更快速的运用，当然对于新手来说就更应该去学习和总结了
用T-SQL语句完成创建与管理数据库操作练习.doc
11-17
用T-SQL语句完成创建与管理数据库操作练习.doc
asp中常用的sql操作语句
weixin_30924239的博客
06-05 224
添加、删除、修改使用db.Execute(Sql)命令执行操作╔----------------╗☆ 数据记录筛选 ☆╚----------------╝注意：单双引号的用法可能有误(没有测式)Sql = "Select Distinct 字段名 From 数据表"Distinct函数，查询数据库存表内不重复的记录Sql = "Select Count(*) From 数据表 where 字段名1...
SQLsever数据库问题：com.microsoft.sqlserver.jdbc.SQLServerException: 索引 1 超出范围
热门推荐
code进击者
11-03 2万+
在用java封装sqlsever数据库的时候，遇到了一个很奇怪的问题 主要还是在防止SQL注入的部分代码上： package dbsqlsever; import java.sql.*; public class SQLinject { static void read(String name) throws SQLException { Connection conn = null;
SQL常用易忘命令
dabao1989的专栏
05-31 424
-- show global variables like "binlog_format"; -- set global binlog_format=ROW; -- SHOW SLAVE STATUS; -- STOP SLAVE; SHOW SLAVE STATUS; -- SHOW PROCESSLIST; -- START SLAVE;
sqlserver性能优化之索引的使用和优化
胡杰的专栏
03-29 8286
sqlserver性能优化之索引的使用和优化
mysql语句记不住_命令太多记不住？MySQL数据库
weixin_42550597的博客
02-01 320
基本的SQL语句有select、insert、update、delete、create、drop、grant、revoke等，具体使用方式见表：一.库操作创建数据库：create database shujuku创建带字符集的数据库：create database mydb2 CHARACTER SET=UTF8创建带校验的数据库：create database mydb3 CHARACTER S...
SQL常用命令使用方法
qq279102484的博客
12-04 372
SQL常用命令使用方法： (1) 数据记录筛选： sql="select * from 数据表 where 字段名=字段值 order by 字段名 [desc]" sql="select * from 数据表 where 字段名 like %字段值% order by 字段名 [desc]" sql="select top 10 * from 数据表 where 字段名 or...
Sql Server查询性能优化之索引篇【推荐】
胡杰的专栏
03-31 1万+
Sql Server查询性能优化之索引篇 索引基础知识 聚集索引和非聚集索引 索引碎片 索引设计指南
MS SqlServer学习笔记（索引）
weixin_34248118的博客
04-15 157
1.索引分类 MS SqlServer提供了两种索引：聚集索引和非聚集索引； 聚集索引是将数据按照索引的顺序存放 非聚集索引是将索引和数据分离存放，通过指针将二者联系到一起。 因为两种索引对比： 使用聚集索引查询效率更高，但对表的更新效率影响较大，因此适用于更新读写比较大的表，例如商品详情（创建后很少改动，查看的频率却相对较高） 使用非聚集索引则查询效率较低，对表的更新效率...
【kettle001】访问国产达梦数据库并处理数据至execl文件
最新发布
kngines
04-22 404
一直以来想写下基于kettle的系列文章，作为较火的数据ETL工具，也是日常项目开发中常用的一款工具，最近刚好挤时间梳理、总结下这块儿的知识体系。熟悉、梳理、总结下达梦（DM）关系型数据库相关知识体系。
介绍一下t-sql语句
07-28
T-SQL（Transact-SQL）是一种用于Microsoft SQL Server数据库管理系统的编程语言。它是对标准SQL语言的扩展，提供了更多的功能和特性。 T-SQL语句用于管理和操作数据库中的数据，包括创建、查询、更新和删除表格...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值