SQL语句的优化分析( 二 ) _SQL

文章插图

由这个图，分析下:
很明显带distinct的语句cpu时间和占用时间都高于不带distinct的语句。原因是当查询很多字段时，如果使用distinct，数据库引擎就会对数据进行比较，过滤掉重复数据，然而这个比较，过滤的过程则会毫不客气的占用系统资源，cpu时间。
3.慎用union关键字
此关键字主要功能是把各个查询语句的结果集合并到一个结果集中返回给你。用法
<select 语句1>union<select 语句2>union<select 语句3>...满足union的语句必须满足：1.列数相同。2.对应列数的数据类型要保持兼容。
执行过程：
依次执行select语句-->>合并结果集--->>对结果集进行排序，过滤重复记录。
select * from((orde o left join orderproduct op on o.orderNum = op.orderNum) inner join product p on op.proNum = p.productnum) where p.id < 10000unionselect * from((orde o left join orderproduct op on o.orderNum = op.orderNum) inner join product p on op.proNum = p.productnum) where p.id < 20000 and p.id >= 10000unionselect * from((orde o left join orderproduct op on o.orderNum = op.orderNum) inner join product p on op.proNum = p.productnum) where p.id > 20000-- -这里可以写p.id > 100 结果一样，因为他筛选过啦 -- -- -- -对比上下两个语句-- -- -- -- select * from((orde o left join orderproduct op on o.orderNum = op.orderNum) inner join product p on op.proNum = p.productnum)

文章插图

由此可见效率确实低，所以不是在必要情况下避免使用。其实有他执行的第三部：对结果集进行排序，过滤重复记录。就能看出不是什么好鸟。然而不对结果集排序过滤，显然效率是比union高的，那么不排序过滤的关键字有吗？答，有，他是union all，使用union all能对union进行一定的优化。。
4.判断表中是否存在数据
select count(*) from product select top(1) id from product很显然下面完胜
5.连接查询的优化
首先你要弄明白你想要的数据是什么样子的，然后再做出决定使用哪一种连接，这很重要。
各种连接的取值大小为：
内连接结果集大小取决于左右表满足条件的数量
左连接取决与左表大小，右相反。
完全连接和交叉连接取决与左右两个表的数据总数量
select * from ( (select * from orde where OrderId>10000) o left join orderproduct op on o.orderNum=op.orderNum )select * from ( orde o left join orderproduct op on o.orderNum=op.orderNum )where o.OrderId>10000

文章插图

由此可见减少连接表的数据数量可以提高效率。
四、insert插入优化--创建临时表create table# tb1 ( id int, name nvarchar(30), createTime datetime )declare@ i intdeclare@ sql varchar(1000)set@ i = 0while (@i < 100000) --循环插入10w条数据beginset@ i = @i + 1set@ sql = ' insert into #tb1 values ('+convert(varchar(10),@i)+', '' erzi '+ convert(nvarchar(30), @i) + '' ',' '' + convert(nvarchar(30), getdate()) + '' ')' exec(@sql) end我这里运行时间是51秒
--创建临时表create table# tb2 ( id int, name nvarchar(30), createTime datetime )declare@ i intdeclare@ sql varchar(8000)declare@ j intset@ i = 0while (@i < 10000) --循环插入10w条数据beginset@ j = 0set@ sql = ' insert into #tb2 select ' + convert(varchar(10), @i * 100 + @j) + ',''erzi' + convert(nvarchar(30), @i * 100 + @j) + ''',''' + convert(varchar(50), getdate()) + ''''set@ i = @i + 1while (@j < 10) beginset@ sql = @sql + ' union all select ' + convert(varchar(10), @i * 100 + @j) + ',''erzi '+convert(nvarchar(30),@i*100+@j)+''','''+convert(varchar(50),getdate())+''''set@ j = @j + 1endexec(@sql)enddrop table# tb2select count(1) from# tb2我这里运行时间大概是20秒
分析说明：insert into select批量插入，明显提升效率。所以以后尽量避免一个个循环插入。
五、优化修改删除语句如果你同时修改或删除过多数据，会造成cpu利用率过高从而影响别人对数据库的访问。
如果你删除或修改过多数据，采用单一循环操作，那么会是效率很低，也就是操作时间过程会很漫长。
这样你该怎么做呢？
折中的办法就是，分批操作数据。
delete product where id<1000delete product where id>=1000 and id<2000delete product where id>=2000 and id<3000.....当然这样的优化方式不一定是最优的选择，其实这三种方式都是可以的，这要根据你系统的访问热度来定夺，关键你要明白什么样的语句是什么样的效果。