SQL调整与优化方案_图文_百度文库
两大类热门资源免费畅读
续费一年阅读会员，立省24元！
SQL调整与优化方案
上传于||文档简介
&&S​Q​L​调​整​与​优​化​方​案
阅读已结束，如果下载本文需要使用1下载券
想免费下载本文？
定制HR最喜欢的简历
下载文档到电脑，查找使用更方便
还剩9页未读，继续阅读
定制HR最喜欢的简历
你可能喜欢一次非常有意思的 SQL 优化经历
我用的数据库是mysql5.6，下面简单的介绍下场景。
文/风过无痕-唐
create table Course(c_id int PRIMARY KEY,name varchar(10))
create table Student(id int PRIMARY KEY,name varchar(10))
数据70000条
学生成绩表SC
CREATE table SC(
sc_id int PRIMARY KEY,
score int)
查询目的：
查找语文考100分的考生
查询语句：
select s.* from Student s where s.s_id in (select s_id from SC sc where sc.c_id = 0 and sc.score = 100 )
执行时间：s
晕,为什么这么慢，先来查看下查询计划：
EXPLAIN select s.* from Student s where s.s_id in (select s_id from SC sc where sc.c_id = 0 and sc.score = 100 )
发现没有用到索引，type全是ALL，那么首先想到的就是建立一个索引，建立索引的字段当然是在where条件的字段。
先给sc表的c_id和score建个索引
CREATE index sc_c_id_index on SC(c_id);
CREATE index sc_score_index on SC(score);
再次执行上述查询语句，时间为: 1.054s
快了3w多倍，大大缩短了查询时间，看来索引能极大程度的提高查询效率，建索引很有必要，很多时候都忘记建
索引了，数据量小的的时候压根没感觉，这优化的感觉挺爽。
但是1s的时间还是太长了，还能进行优化吗，仔细看执行计划：
查看优化后的sql:
`YSB`.`s`.`s_id` AS `s_id`,
`YSB`.`s`.`name` AS `name`FROM
`YSB`.`Student` `s`WHERE
& in_optimizer & (
`YSB`.`s`.`s_id` ,& EXISTS & (
`YSB`.`SC` `sc`
(`YSB`.`sc`.`c_id` = 0)
AND (`YSB`.`sc`.`score` = 100)
& CACHE & (`YSB`.`s`.`s_id`) = `YSB`.`sc`.`s_id`
补充：这里有网友问怎么查看优化后的语句
方法如下：
在命令窗口执行
有type=all
按照我之前的想法，该sql的执行的顺序应该是先执行子查询
select s_id from SC sc where sc.c_id = 0 and sc.score = 100
耗时：0.001s
得到如下结果：
然后再执行
select s.* from Student s where s.s_id in(7,29,5000)
耗时：0.001s
这样就是相当快了啊，Mysql竟然不是先执行里层的查询，而是将sql优化成了exists子句，并出现了EPENDENT SUBQUERY，
mysql是先执行外层查询，再执行里层的查询，这样就要循环70007*8次。
那么改用连接查询呢？
SELECT s.* from Student sINNER JOIN SC scon sc.s_id = s.s_idwhere sc.c_id=0 and sc.score=100
这里为了重新分析连接查询的情况，先暂时删除索引sc_c_id_index，sc_score_index
执行时间是：0.057s
效率有所提高，看看执行计划：
这里有连表的情况出现，我猜想是不是要给sc表的s_id建立个索引
CREATE index sc_s_id_index on SC(s_id);
show index from SC
在执行连接查询
时间: 1.076s，竟然时间还变长了，什么原因？查看执行计划：
优化后的查询语句为：
`YSB`.`s`.`s_id` AS `s_id`,
`YSB`.`s`.`name` AS `name`FROM
`YSB`.`Student` `s`JOIN `YSB`.`SC` `sc`WHERE
`YSB`.`sc`.`s_id` = `YSB`.`s`.`s_id`
AND (`YSB`.`sc`.`score` = 100)
AND (`YSB`.`sc`.`c_id` = 0)
貌似是先做的连接查询，再进行的where条件过滤
回到前面的执行计划：
这里是先做的where条件过滤，再做连表，执行计划还不是固定的，那么我们先看下标准的sql执行顺序：
正常情况下是先join再进行where过滤，但是我们这里的情况，如果先join，将会有70w条数据发送join做操，因此先执行where
注：相关网站建设技巧阅读请移步到频道。
看过本文的人还看过
最新图文推荐
最新专栏文章
大家感兴趣的内容
网友热评的文章开门见山，问题所在
sql语句性能达不到你的要求，执行效率让你忍无可忍，一般会时下面几种情况。
网速不给力，不稳定。
服务器内存不够，或者SQL 被分配的内存不够。
sql语句设计不合理
没有相应的索引，索引不合理
没有有效的索引视图
表数据过大没有有效的分区设计
数据库设计太2，存在大量的数据冗余
索引列上缺少相应的统计信息，或者统计信息过期
那么我们如何给找出来导致性能慢的的原因呢？
首先你要知道是否跟sql语句有关，确保不是机器开不开机，服务器硬件配置太差，没网你说p啊
接着你使用我上一篇文章中提到的2柯南sql性能检测工具--sql server profiler，分析出sql慢的相关语句，就是执行时间过长，占用系统资源，cpu过多的
然后是这篇文章要说的，sql优化方法跟技巧，避免一些不合理的sql语句，取暂优sql
再然后判断是否使用啦，合理的统计信息。sql server中可以自动统计表中的数据分布信息，定时根据数据情况，更新统计信息，是很有必要的
确认表中使用啦合理的索引，这个索引我前面博客中也有提过，不过那篇博客之后，还要进一步对索引写篇文章
数据太多的表，要分区，缩小查找范围
分析比较执行时间计划读取情况
select * from dbo.Product
执行上面语句一般情况下只给你返回结果和执行行数，那么你怎么分析呢，怎么知道你优化之后跟没有优化的区别呢。
下面给你说几种方法。
1.查看执行时间和cpu占用时间
set statistics time on
select * from dbo.Product
set statistics time off
打开你查询之后的消息里面就能看到啦。
2.查看查询对I/0的操作情况
set statistics io on
select * from dbo.Product
set statistics io off
扫描计数：索引或表扫描次数
逻辑读取：数据缓存中读取的页数
物理读取：从磁盘中读取的页数
预读：查询过程中，从磁盘放入缓存的页数
lob逻辑读取：从数据缓存中读取，image，text，ntext或大型数据的页数
lob物理读取：从磁盘中读取，image，text，ntext或大型数据的页数
lob预读：查询过程中，从磁盘放入缓存的image，text，ntext或大型数据的页数
如果物理读取次数和预读次说比较多，可以使用索引进行优化。
如果你不想使用sql语句命令来查看这些内容，方法也是有的，哥教你更简单的。
查询---&&查询选项---&&高级
被红圈套上的2个选上，去掉sql语句中的set statistics io/time on/off 试试效果。哦也，你成功啦。。
3.查看执行计划，
选中查询语句，点击然后看消息里面，会出现下面的图例
首先我这个例子的语句太过简单，你整个复杂的，包涵啊。
分析：鼠标放在图标上会显示此步骤执行的详细内容，每个表下面都显示一个开销百分比，分析站百分比多的的一块，可以根据重新设计数据结构，或这重写sql语句，来对此进行优化。如果存在扫描表，或者扫描聚集索引，这表示在当前查询中你的索引是不合适的，是没有起到作用的，那么你就要修改完善优化你的索引，具体怎么做，你可以根据我上一篇文章中的sql优化利器--数据库引擎优化顾问对索引进行分析优化。
select查询艺术
1.保证不查询多余的列与行。
尽量避免select * 的存在，使用具体的列代替*，避免多余的列
使用where限定具体要查询的数据，避免多余的行
使用top，distinct关键字减少多余重复的行
2.慎用distinct关键字
distinct在查询一个字段或者很少字段的情况下使用，会避免重复数据的出现，给查询带来优化效果。
但是查询字段很多的情况下使用，则会大大降低查询效率。
由这个图，分析下:
很明显带distinct的语句cpu时间和占用时间都高于不带distinct的语句。原因是当查询很多字段时，如果使用distinct，数据库引擎就会对数据进行比较，过滤掉重复数据，然而这个比较，过滤的过程则会毫不客气的占用系统资源，cpu时间。
3.慎用union关键字
此关键字主要功能是把各个查询语句的结果集合并到一个结果集中返回给你。用法
&select 语句1&
&select 语句2&
&select 语句3&
满足union的语句必须满足：1.列数相同。 2.对应列数的数据类型要保持兼容。
执行过程：
依次执行select语句--&&合并结果集---&&对结果集进行排序，过滤重复记录。
select * from
left join orderproduct op on o.orderNum=op.orderNum )
inner join product p on op.proNum=p.productnum)
where p.id&10000
select * from
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&=10000
select * from
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
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
name nvarchar(30),
createTime datetime
declare @i int
declare @sql varchar(1000)
while (@i&100000)
--循环插入10w条数据
set @i=@i+1
set @sql=' insert into #tb1 values('+convert(varchar(10),@i)+',''erzi'+convert(nvarchar(30),@i)+''','''+convert(nvarchar(30),getdate())+''')'
exec(@sql)
我这里运行时间是51秒
--创建临时表
create table #tb2
name nvarchar(30),
createTime datetime
declare @i int
declare @sql varchar(8000)
declare @j int
while (@i&10000)
--循环插入10w条数据
set @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+1
while(@j&10)
set @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+1
exec(@sql)
drop table #tb2
select count(1) from #tb2
我这里运行时间大概是20秒
分析说明：insert into select批量插入，明显提升效率。所以以后尽量避免一个个循环插入。
优化修改删除语句
如果你同时修改或删除过多数据，会造成cpu利用率过高从而影响别人对数据库的访问。
如果你删除或修改过多数据，采用单一循环操作，那么会是效率很低，也就是操作时间过程会很漫长。
这样你该怎么做呢？
折中的办法就是，分批操作数据。
delete product where id&1000
delete product where id&=1000 and id&2000
delete product where id&=2000 and id&3000
当然这样的优化方式不一定是最优的选择，其实这三种方式都是可以的，这要根据你系统的访问热度来定夺，关键你要明白什么样的语句是什么样的效果。
总结：优化，最重要的是在于你平时设计语句，数据库的习惯，方式。如果你平时不在意，汇总到一块再做优化，你就需要耐心的分析，然而分析的过程就看你的悟性，需求，知识水平啦。
阅读(...) 评论()工具类服务
编辑部专用服务
作者专用服务
基于SQL的关系数据查询优化策略
SQL语句的执行效率对于提高应用系统的响应速度,减少网络流量,提高工作效率具有很重要的作用.本文通过分析数据库应用系统中常见的几种运算,提出了如何构建SQL语句,才能达到运算的优化,提高系统运行速度.
作者单位：
西安石油大学计算机学院,陕西,西安,710065
年，卷(期)：
机标分类号：
在线出版日期：
基金项目：
国家自然科学基金
本文读者也读过
相关检索词
万方数据知识服务平台--国家科技支撑计划资助项目（编号：2006BAH03B01）(C)北京万方数据股份有限公司
万方数据电子出版社