您现在的位置是:首页 > 关键词排名关键词排名

项目中优化sql语句执行效率的方法是什么?

江湖快报网2023-05-26 13:59:18【关键词排名】人已围观

简介项目中优化sql语句执行效率的方法是什么?
1. SQL优化的原则是:将一次操作需要读取的BLOCK数减到最低,即在最短的时间达到最大的数据吞吐量。 \r\n调整不良SQL通常可以从以下几

项目中优化sql语句执行效率的方法是什么?

1. SQL优化的原则是:将一次操作需要读取的BLOCK数减到最低,即在最短的时间达到最大的数据吞吐量。 \r\n调整不良SQL通常可以从以下几点切入: \r\n? 检查不良的SQL,考虑其写法是否还有可优化内容 \r\n? 检查子查询 考虑SQL子查询是否可以用简单连接的方式进行重新书写 \r\n? 检查优化索引的使用 \r\n? 考虑数据库的优化器 \r\n\r\n2. 避免出现SELECT * FROM table 语句,要明确查出的字段。 \r\n\r\n3. 在一个SQL语句中,如果一个where条件过滤的数据库记录越多,定位越准确,则该where条件越应该前移。 \r\n\r\n4. 查询时尽可能使用索引覆盖。即对SELECT的字段建拍巧立复合索引,这样查询时只进行索引扫描,不读取数据块。 \r\n\r\n5. 在判断有无符合条件的记录时建议不要用SELECT COUNT (*)和select top 1 语句。 \r\n\r\n6. 使用内层限定原则,在拼写SQL语句袭散键时,将查询条件分解、分类,并尽量在SQL语句的最里层进行限定,以减少数据的处理量。 \r\n\r\n7. 应绝对避免在order by子句中使用表达式。 \r\n\r\n8. 如果需要从关联表读数据,关联的表一般不要超过7个。 \r\n\r\n9. 小心使用 IN 和 OR,需要注意In集合中的数据量。建议集合中的数据不超过200个。 \r\n\r\n10. 用 代替,>用>=代替,1000)。应该用如下语句代替:select name from customer inner join order on customer.customer_id=order.customer_id where order.money>100。 \r\n\r\n15. 在WHERE 子句中,避免对列的四则运算,特别是where 条件的左边,严禁使用运算与函数对列进行处理。比如有些地方 substring 可以用like代替。 \r\n\r\n16. 如果在语句中有not in(in)操作,应考虑用not exists(exists)来重写,最好的办法是使用外连接实现。 \r\n\r\n17. 对一个业务过程的处理,应该使事物的开始与结束之间的时间间隔越短越好,原则上做到数据库的读操作在前面完成,数据库写操作在后面完成,避免交叉。 \r\n\r\n18. 请小心不要对过多的列使用列函数和order by,group by等,谨慎使用disti软件开发t。 \r\n\r\n19. 用union all 代替 union,数据库执掘大行union操作,首先先分别执行union两端的查询,将其放在临时表中,然后在对其进行排序,过滤重复的记录。 \r\n当已知的业务逻辑决定query A和query B中不会有重复记录时,应该用union all代替union,以提高查询效率。

如何进行SQL性能优化

进行SQL性能优化的方法:

1、SQL语句不要写的太复杂。一个SQL语句要尽量简单,不要嵌套太多层。

2、使用『临时表』缓存中间结果。简化SQL语句的重要方法就是采用临时表暂存中间结果,这样可以避免程序中多次扫描主表,也大大减少了阻塞,提高了并发性能。

3、使用like的时候要注意是否会导致全表扫,有的时候会需要进行一些模糊查询例如:select id from table where username like ‘%hollis%’关键兆纯词%hollis%,由于hollis前面用到了“%”,因此该查询会使用全表扫描,除非必要,否则不要在关键词前加%。

4、尽量避免使用!=或<>操作符。在where语句中使用!=或<>,引擎将放弃使用索引而进行全表扫描。

5、尽量避免使用 or 来连接条件;在 where 子句中使用 or 来连接条件,引擎将放弃使用索引而进行全表扫描。可以使用

select id from t where num=10

union all

select id from t where num=20

替代

select id from t where num=10 or num=20

6、尽量避免使用in和not in:在 where 子句中使用 in和not in,引擎将放弃使用索引而进行全表扫描。可以使用

select id from t where num between 10 and 20

替代

select id from t where num in (10,20)

7、可以考虑强制查询使用索引

select * from table force index(PRI) limit 2;(强制使用主键)

select * from table force index(hollis_index) limit 2;(强制使用索引hollis_index)

select * from table force index(PRI,hollis_index) limit 2;(强制使用索引PRI和hollis_index)

8、尽量避免使用表达式、函数等操作作为查询条件;尽量避免大事务操作,提高系统并发能力。尽量避免使用游标;任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。

9、尽可能的使用 varchar/nvarchar 代替 char/nchar。尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。

10、索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率、并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的好拦,当索引列有大量数据重复时,SQL查询可能不会去友猜胡利用索引。

1、模糊查询like。

使用like进行模糊查询时应该特别注意,这个很基本,基本上大家都知道。

select * from contact where username like ‘%yue%’

关键词%yue%,由于yue前面用到了“%”,因此该查询必然走全表扫描,除非必要,否则不要在关键词前加%。

2、where条件查携迟询

尽量避免使用in,not in,having,可以使用 exist 和not exist代替 in和not in。不要以字符格式声明数字,要以数字格式声明字符值。

3、前面提到的from子句中有多个表进行关联查询时

在from子句中包含多个表的情况下,选择记录条数最少的表作为基础表,在某种程度上将会极大的提高其性能。如果有3个以上的表,则选择交叉表作为基础表

4、select *查询

尽量不要使用

select * from tablename

取而代之的则是:

select columnname1,columnname2 from tablename

5、排序操作

避免使用耗费资源的操作,带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎 执行,耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序。

6、索引表操作

对于此处,个人还没有弄明白,首先对于索引还不明白,那么性能优化更谈不上了。反正很多大辩锋李牛都是操作索引表,需要特别注意。以后明白了再补充吧。

...

7、LEFT JOIN 和 inner join的区别,是否真的需要left join,否则选用inner join 来减少不必要的数据返回。

个人因为编程习惯问题,总喜欢写left join,看来以后要用大脑思考思考了。

同时,SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误。

8、统一规范sql语句

编写规范的sql语句,这一点是最重要的一点,不管对于系统还是个人来说,都是相当的重要。

扩展资料:

使用like进行模糊查询时应该特别注意

select * from contact where username like ‘%yue%’

关键词%yue%,由于yue前面用到了“%”,因此该查询必然走全表扫描,除非必要,否则不要在关键词前加%。

尽量避免使用in,not in,having,可以使用 exist 和not exist代替 in和not in。不要以字符格式声明数字,要基埋以数字格式声明字符值。

索引

加索引,本地模拟现场的业务场景,插入了大量的测试数据,在sql的where条件查询字段下加了索引,查询时间进入到秒级,完全满足项目要求。现场提供的视图,而且视图的厂家没有人维护了,不可能创建其它东西的,所以虽然索引有效但是无法使用。

参数

现场系统可以通过配置参数来对业务进行调整,执行的sql语句中加入了@参数Name=@Name or @Name = '',上网经过搜索,发现参数不会对sql执行造成影响,但是如果你的where条件中的@参数正好加入了索引,那么影响就相当显著了。加入强制执行索引:

with(index(IX_Name)),效率有显示提升,奈何现场的视图已无参加维护。

Join

查询数据源采用了left join联表查询,问题来了,主表2w多行的数据,副表也是3w多行的数据,比较奇葩的使用了两个视图联表查询,还是那句没有厂家维护。联表查询n*m,那么减少基础表的记录数目可以有效的提高效率。那么把条件搜索放入到基础表先进性过滤,然后再进行联合查询。

select top 500 * from(select  * from  [dbo].[table1] where (ss between @a1 and @a2)) a

LEFT JOIN  dbo.[table2] ON a.m = dbo.[table2].n

结构化查询语言(Structured Query Language)简称SQL(发音:/ˈes kjuː ˈel/ S-Q-L),是一种特殊目的的编程语言,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统;同时也是数据库脚本文件的扩展名。

结构化查询语言是高级的非过程化编程语言,允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法,也不需要用户了解具体的数据存放方式,所以具有完全不同底层结构的不同数据库系统, 可以使用相同的结构化查询语言作为数据输入与管理的接口。结构化查询语言语句可以嵌套,这使它具有极大的灵活性和强大的功能。

1986年10月,美国国家标准协会对SQL进行规范后,以此作为关系式数据库管理系统的标准语言(ANSI X3. 135-1986),1987年得到国际标准组织的支持下成为国际标准。不过各种通行的数据库系统在其实践过程中都对SQL规范作了某些编改和扩充。所以,实际上不同数据库系统之间的SQL不能完全相互通用。

参考资料:

sql优化-百度百科

在SQL查询中,为了提高查询的效率,我们常常采取一些措施对查询语句进行SQL性能优化。本文我们总结了一些优化措施,接下来我们就一一介绍。

1.查询的模糊匹配

尽量避免在一个复杂查询里面使用 LIKE '%parm1%'―― 红色标识位置的百分号会导致相关列的索引无法使用,最好不要用。

解决办法:

其实只需要对该脚本略做改进,查询速度便会提高近百倍。改进方法如下:

a、修改前台程序――把查询条件的供应商名称一栏由原来的文本输入改为下拉列表,用户模糊输入供应商名称时,直接在前台就帮忙定位到具体的供应商,这样在调用后台程序时,这列就可以直接用等于来关联了。

b、直接修改后台――根据输入条件,先查出符合条件的供应商,并把相关记录保存在一个临时表里头,然后再用临时表去做复杂关联。

2.索引问题

在做性能跟踪分析过程中,经常发现有不少后台程序的性能问题是因为缺少合适索引造成的,有些表甚至一个索引都没有。这种情况往往都是因为在设计表时,没去定义索引,而开发初期,由于表记录很少,索引创建与否,可能对性能没啥影响,开发人员因此也未多加重视。然一旦程序发布到生产环境,随着时间的推移,表记录越来越多。这时缺少索引,对性能的影响便会越来越大了。

法则:不要在建立的索引的数据列上进行下列操作:

避免对索引字段进行计算操作

避免在索引字段上使用not,<>,!=

避免在索引列上使用IS NULL和IS NOT NULL

避免在索引列上出现数据类型转换

避免在索引字段上使用函数

避免建立索引的列中使用空值

3.复杂操作

部分UPDATE、御悄陪SELECT 语句 写得很复杂(经常嵌套多级子查询)――可以考虑适当拆成几步镇蠢,先生成一些临时数据表,再进行关联操作。

4.update

同一个表的修改在一运瞎个过程里出现好几十次,如:

update table1 set col1=... where col2=...; update table1 set col1=... where col2=... ...

这类脚本其实可以很简单就整合在一个UPDATE语句来完成(前些时候在协助xxx项目做性能问题分析时就发现存在这种情况)

5.在可以使用UNION ALL的语句里,使用了UNION

UNION 因为会将各查询子集的记录做比较,故比起UNION ALL ,通常速度都会慢上许多。一般来说,如果使用UNION ALL能满足要求的话,务必使用UNION ALL。还有一种情况大家可能会忽略掉,就是虽然要求几个子集的并集需要过滤掉重复记录,但由于脚本的特殊性,不可能存在重复记录,这时便应该使用 UNION ALL,如xx模块的某个查询程序就曾经存在这种情况,见,由于语句的特殊性,在这个脚本中几个子集的记录绝对不可能重复,故可以改用UNION ALL)。

6.在WHERE 语句中,尽量避免对索引字段进行计算操作

这个常识相信绝大部分开发人员都应该知道,但仍有不少人这么使用,我想其中一个最主要的原因可能是为了编写写简单而损害了性能,那就不可取了。9月份在对XX系统做性能分析时发现,有大量的后台程序存在类似用法,如:where trunc(create_date)=trunc(:date1),虽然已对create_date 字段建了索引,但由于加了TRUNC,使得索引无法用上。此处正确的写法应该是where create_date>=trunc(:date1) and create_date< pre=><>或者是where create_date between trunc(:date1) and trunc(:date1)+1-1/(24*60*60)。

注意:因between 的范围是个闭区间(greater than or equal to low value and less than or equal to high value.),故严格意义上应该再减去一个趋于0的小数,这里暂且设置成减去1秒(1/(24*60*60)),如果不要求这么精确的话,可以略掉这步。

7.对Where 语句的法则

7.1 避免在WHERE子句中使用in,not in,or 或者having。

可以使用 exist 和not exist代替in和not in。

可以使用表链接代替 exist。Having可以用where代替,如果无法代替可以分两步处理。

例子

SELECT * FROM ORDERS WHERE CUSTOMER_NAME NOT IN (SELECT CUSTOMER_NAME FROM CUSTOMER)

优化

SELECT * FROM ORDERS WHERE CUSTOMER_NAME not exist (SELECT CUSTOMER_NAME FROM CUSTOMER)

7.2 不要以字符格式声明数字,要以数字格式声明字符值。(日期同样)否则会使索引无效,产生全表扫描。

例子使用:

SELECT emp.ename, emp.job FROM emp WHERE emp.empno = 7369;

--不要使用:

SELECT emp.ename, emp.job FROM emp WHERE emp.empno = '7369'

8.对Select语句的法则

在应用程序、包和过程中限制使用select * from table这种方式。看下面例子

--使用

SELECT empno,ename,category FROM emp WHERE empno = '7369'

--而不要使用

SELECT * FROM emp WHERE empno = '7369'

9. 排序

避免使用耗费资源的操作,带有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL语句会启动SQL引擎 执行,耗费资源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要执行两次排序。

10.临时表

慎重使用临时表可以极大的提高系统性能。

关于SQL性能优化的知识就介绍到这里了,希望本次的介绍能够带给您一些收获,谢谢!

这里分享下mysql优化的几种方法。

1、首先在打开的软件中,需要分别为每一个表创建 InnoDB FILE的文件。

2、这样能保证从内存衡绝中读取数据不会太大,如果太大就达不到优化效果。

3、此时,可以利用语句iostat -d -x -k 1 命令,直接查看硬盘的操作,如下图所示。

4、如果成立就对mysql的数据进行拷贝,这样键搭就达到了mysql优化咐亮姿的功能了,如下图所示就完成了。

建立并使用恰当的索引,能极大地提高查询效率

Tags:性能   优化   进行

很赞哦! ()

文章评论

    共有条评论来说两句吧...

    用户名:

    验证码:

本站推荐