小编给大家分享一下数据库SQL调优的方式有哪些,相信大部分人都还不怎么了解,因此分享这篇文章给大家参考一下,希望大家阅读完这篇文章后大有收获,下面让我们一起去了解一下吧!
方式:1、创建索引时,尽量避免全表扫描;2、避免在索引上使用计算;3、尽量使用参数化SQL;4、尽量将多条SQL语句压缩到一句SQL中;5、用where字句替换HAVING字句;6、连接多个表时,使用表的别名;7、尽量避免使用游标等等。
本教程操作环境:windows7系统、mysql8版本、Dell G3电脑。
一.创建索引
1、要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引
2、(1)在经常需要进行检索的字段上创建索引,比如要按照表字段username进行检索,那么就应该在姓名字段上创建索引,如果经常要按照员工部门和员工岗位级别进行检索,那么就应该在员工部门和员工岗位级别这两个字段上创建索引。
(2)创建索引给检索带来的性能提升往往是巨大的,因此在发现检索速度过慢的时候应该首先想到的就是创建索引。
(3)一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有 必要。索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。
二.避免在索引上使用计算
在where字句中,如果索引列是计算或者函数的一部分,DBMS的优化器将不会使用索引而使用全表查询,函数
属于计算的一种,同时在in和exists中通常情况下使用EXISTS,因为in不走索引
效率低:
select * from user where salary*22>11000(salary是索引列)
效率高:
select * from user where salary>11000/22(salary是索引列)
三.使用预编译查询
程序中通常是根据用户的输入来动态执行SQL,这时应该尽量使用参数化SQL,这样不仅可以避免SQL注入漏洞
攻击,最重要数据库会对这些参数化SQL进行预编译,这样第一次执行的时候DBMS会为这个SQL语句进行查询优化
并且执行预编译,这样以后再执行这个SQL的时候就直接使用预编译的结果,这样可以大大提高执行的速度。
四.尽量将多条SQL语句压缩到一句SQL中
每次执行SQL的时候都要建立网络连接、进行权限校验、进行SQL语句的查询优化、发送执行结果,这个过程
是非常耗时的,因此应该尽量避免过多的执行SQL语句,能够压缩到一句SQL执行的语句就不要用多条来执行。
五.用where字句替换HAVING字句
避免使用HAVING字句,因为HAVING只会在检索出所有记录之后才对结果集进行过滤,而where则是在聚合前
刷选记录,如果能通过where字句限制记录的数目,那就能减少这方面的开销。HAVING中的条件一般用于聚合函数
的过滤,除此之外,应该将条件写在where字句中。
六.使用表的别名
当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减
少哪些友列名歧义引起的语法错误。
七.用union all替换union
当SQL语句需要union两个查询结果集合时,即使检索结果中不会有重复的记录,如果使用union这两个结果集
同样会尝试进行合并,然后在输出最终结果前进行排序,因此如果可以判断检索结果中不会有重复的记录时候,应
该用union all,这样效率就会因此得到提高。
八.考虑使用“临时表”暂存中间结果
简化SQL语句的重要方法就是采用临时表暂存中间结果,但是,临时表的好处远远不止这些,将临时结果暂存在临时表,后面的查询就在tempdb中了,这可以避免程序中多次扫描主表,也大大减少了程序执行中“共享锁”阻塞“更新锁”,减少了阻塞,提高了并发性能。
但是也得避免频繁创建和删除临时表,以减少系统表资源的消耗。
九.只在必要的情况下才使用事务begin translation
SQL Server中一句SQL语句默认就是一个事务,在该语句执行完成后也是默认commit的。其实,这就是begin tran的一个最小化的形式,好比在每句语句开头隐含了一个begin tran,结束时隐含了一个commit。
有些情况下,我们需要显式声明begin tran,比如做“插、删、改”操作需要同时修改几个表,要求要么几个表都修改成功,要么都不成功。begin tran 可以起到这样的作用,它可以把若干SQL语句套在一起执行,最后再一起commit。 好处是保证了数据的一致性,但任何事情都不是完美无缺的。Begin tran付出的代价是在提交之前,所有SQL语句锁住的资源都不能释放,直到commit掉。
可见,如果Begin tran套住的SQL语句太多,那数据库的性能就糟糕了。在该大事务提交之前,必然会阻塞别的语句,造成block很多。
Begin tran使用的原则是,在保证数据一致性的前提下,begin tran 套住的SQL语句越少越好!有些情况下可以采用触发器同步数据,不一定要用begin tran。
十.尽量避免使用游标
尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。
十一.用varchar/nvarchar 代替 char/nchar
以上是“数据库SQL调优的方式有哪些”这篇文章的所有内容,感谢各位的阅读!相信大家都有了一定的了解,希望分享的内容对大家有所帮助,如果还想学习更多知识,欢迎关注亿速云行业资讯频道!