• 极客专栏正式上线!欢迎访问 https://www.jikewenku.com/topic.html
  • 极客专栏正式上线!欢迎访问 https://www.jikewenku.com/topic.html

公司真实场景下的SQL优化总结

极客笔记 多啦H梦 7个月前 (09-01) 410次浏览 已收录 0个评论 扫描二维码
文章目录[隐藏]

写在前面的话

来我们这家公司一年了 刚开始网站上线时首页打开非常的卡 后来发现是sql的原因 仔细看了下sql慢语句 (这里可以使用阿里的监控工具 druid分析是哪条sql导致的响应缓慢) 刚开始不懂优化 就是在 客服端上瞎改知道改的快了为止 摸石头过河吧

让我直接说方法我也很懵逼 你给我一条sql让我调优我觉得我能做到 但是你让我说出来具体怎么调我也不知如何说起 举几个列子吧

通过上图发现 这几条sql3秒了不能容忍 我们先看看原因 原来的sql如下

接下来我们来优化它

通过 Explain分析来看 索引都有命中 OK 那我们就排除索引这一项 把注意放到sql上

可以看到 第一个查询了扫描了五万行的数据 我们现在直接拿掉left join 变成 内连接join

发现并没有啥用 那说明肯定不是 join连接导致的(注意一般的项目是不允许3表join的因为这样数据表如果非常大的话 会导致查询十分的缓慢 从这一点你无论sql写的有多好都是没用的,我从这个sql就能看出 当时写这段代码的程序猿的水平 应该是那种图个省事脑子里没有优化这个概念,写完代码就行了 坐等下班 手动滑稽) 哈哈吐槽下哈,刚才我说的是一般的项目都是最多两表 join 而且还是那种数据不是很大的表 开发人员写sql时就应当避免多表join,还有一种就是跟用户交互很强 用户流量很大的网站 是不允许有join查询的 都是分开查询 我们的新项目就是单条sql去查询 然后根据第一次的结果集再去做二次查询(不要想着这样麻烦)一个大的网站你只要按照这个套路来 再在数据库查询挡一层 redis缓存 基本数据库就不用太操心了 即使你的业务猛涨 需要分库分表时 以这样单条sql查询的方法 能让你的底层代码无需改动 因为一般的分库分表插件 最难的就是夸分片join 大名鼎鼎的Mycat也是硬伤

如果按照我刚才说的那种单查询 就不必考虑 再去动程序底层的sql了,当然你可能体会不到这种操蛋 的感觉 给你们看下我们老项目的一个搜索sql

这种复杂的sql 在分库分表的环境你肯定无法执行 插件也解析不了这么复制的 并且夸分片的sql 而这时你就要改你的底层代码了 一个一个的分开 然后再合并……..当然这个最初的表设计的合理性也有很大的关系

扯远了 我们来接着优化刚才的那个sql(手动滑稽)

我们刚刚换了join不行 那我们再想想是不是因为查询的列名太多导致的 我们拿掉了 select * 换成

这时候我们发现快了一秒 哎呦呦 竟然有用哎 (无比的开心)但是作为一个追求完美的程序猿怎么能容忍这种瑕疵了 (我个人的标准就是每个SQL速度不能超过1.5秒)

接着优化

既然跟join和 sele * 都不是导致sql慢的根本原因 那我们继续 这段SQL内的betten是硬条件 也就是说Betten这里我们不用考虑了 benen是硬性条件肯定不能改

(大致说下这段sql的意思 它是查询字数 0-30000之间的小说 这是硬条件无法改动)

因此我们跳过这段代码 进入sql的尾部

那么狠可能导致这个sql慢的原因就是在个排序

我们现在看下他的排序 是根据子表的id去排序的 十分的不合理 而且没有所以 既然是查询0-3000字的小说 肯定是根据时间来排序的 或者按照数据表的id排序

最新的在最前面

(这里说下一般按照时间排序时 可以先把时间字段转成13位long的时间戳然后让数据库对这个long类型的时间戳做排序 肯定比直接对字符串类型的时间做排序好的多 亲测有效)

我们换了id、做排序速度瞬间快了 而且我用了select * 原因是bookid 用到了索引所以非常的快(手动滑稽)

大工告成

这里只是说下我优化sql的思路和过程 希望大佬别喷(通过这个方法我优化了一条又一条的慢sql 哈哈哈) 具体怎么玩 还要你们自己体会

下面是优化总结

尽量不使用子查询

1.对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。

2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:

select id from t where num is null

最好不要给数据库留NULL,尽可能的使用 NOT NULL填充数据库.

备注、描述、评论之类的可以设置为 NULL,其他的,最好不要使用NULL。

不要以为 NULL 不需要空间,比如:char(100) 型,在字段建立时,空间就固定了, 不管是否插入值(NULL也包含在内),都是占用 100个字符的空间的,如果是varchar这样的变长字段, null 不占用空间。

可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:

select id from t where num =0

3.应尽量避免在 where 子句中使用 != 或 <> 操作符,否则将引擎放弃使用索引而进行全表扫描。

4.应尽量避免在 where 子句中使用 or 来连接条件,如果一个字段有索引,一个字段没有索引,将导致引擎放弃使用索引而进行全表扫描,如:

select id from t where num=<strong>10</strong> or Name = 'admin'

可以这样查询:

select id from t where num = 10
union all
select id from t where Name = 'admin'  但是 不建议这么用

5. in 和 not in 也要慎用,否则会导致全表扫描,如:

select id from t where num in(1,2,3) 一般不用in 用 between

很多时候用 exists 代替 in 是一个好的选择:

select num from a where num in(select num from b)

用下面的语句替换:

select num from a where exists(select 1 from b where num=a.num)

6.下面的查询也将导致全表扫描:

select id from t where name like ‘%abc%’

select id from t where name like ‘abc%’ 这样写是可以走索引了(有可能记错哈哈哈哈哈)

不行就把%放前面 哈哈哈哈

若要提高效率,可以考虑全文检索。 Solr等

7.如果在 where 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然 而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:

select id from t where num = @num

可以改为强制查询使用索引:

select id from t with(index(索引名)) where num = @num

应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:

select id from t where num/2 = 100

应改为:

select id from t where num = 100*2

8 复杂的查询 而且多次用到 应当考虑用视图 当然按照我上面说的不准有join也就不存在复杂的查询 因此当我没说(手动滑稽)

9.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:

select id from t where substring(name,1,3) = ’abc’ -–name以abc开头的id

select id from t where datediff(day,createdate,’20051130′) = 0 ––‘20051130’ –生成的id

应改为:

select id from t where name like ‘abc%’

select id from t where createdate >= ‘2005-11-30’ and createdate < ‘2005-12-1’

最后吧时间都转成long类型的时间戳

不会就百度 这就是我当场百度学会的哈哈哈哈哈哈

10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。

11.在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。

12.不要写一些没有意义的查询,如需要生成一个空表结构:

select col1,col2 into #t from t where 1=0

这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样:

create table #t(…)

13.Update 语句,如果只更改1、2个字段,不要Update全部字段,否则频繁调用会引起明显的性能消耗,同时带来大量日志。

14.对于多张大数据量(这里几百条就算大了)的表JOIN,要先分页再JOIN,否则逻辑读会很高,性能很差。

15.select count(*) from table;这样不带任何条件的count会引起全表扫描,并且没有任何业务意义,是一定要杜绝的。

16.索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有 必要。

17.应尽可能的避免更新 clustered 索引数据列,因为 clustered 索引数据列的顺序就是表记录的物理存储顺序,一旦该列值改变将导致整个表记录的顺序的调整,会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列,那么需要考虑是否应将该索引建为 clustered 索引。

18.尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连 接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。

19.尽可能的使用 varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。

20.任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。

21.尽量使用表变量来代替临时表。如果表变量包含大量数据,请注意索引非常有限(只有主键索引)。

22. 避免频繁创建和删除临时表,以减少系统表资源的消耗。临时表并不是不可使用,适当地使用它们可以使某些例程更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。但是,对于一次性事件, 最好使用导出表。

23.在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。

24.如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。

25.尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。

分析 拆分大的 DELETE 或INSERT 语句,批量提交SQL语句

如果你需要在一个在线的网站上去执行一个大的 DELETE 或 INSERT 查询,你需要非常小心,要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的,表一锁住了,别的操作都进不来了。

Apache 会有很多的子进程或线程。所以,其工作起来相当有效率,而我们的服务器也不希望有太多的子进程,线程和数据库链接,这是极大的占服务器资源的事情,尤其是内存。

如果你把你的表锁上一段时间,比如30秒钟,那么对于一个有很高访问量的站点来说,这30秒所积累的访问进程/线程,数据库链接,打开的文件数,可能不仅仅会让你的WEB服务崩溃,还可能会让你的整台服务器马上挂了。 你的老板会拿铁锅炖了你的 哈哈哈哈哈哈哈

终于完了 我在调sql的过程中 之前以为这种调优很简单也没有用心记 今天才意识到 sql是程序的根本

还有就是 一个表最好不要超过100w数据 别问我怎么办 我特么也不会 只能分库分表水平拆分 当然你要是个小公司 不用考虑分表的事情 分表只是 万不得已才用的减轻Mysql服务器的压力的做法 记住一旦使用了分库分表插件 mysql的性能肯定会下降的 因为你的sql被插件路由到不同的sql数据节点 肯定查询时要一个一个节点的挨着查询 肯定会损失性能

当然我们的公司是要去纳斯达克上市的公司 对数据要求非常的高(我特么被老板洗脑了 20多个人 还去去上市……..这特么确定不是传销洗脑)

我们的用户非常多 数据也非常多必须要考虑好分表

这些小说内的章节每个段落内的评论到时肯定是海量的 一个数据库肯定不行 所以考虑分表

哈哈哈哈

今天的逼就装到这里谢谢大家 希望大佬们别喷

C:\Users\Administrator\Desktop\表情包\QQ图片20171025183840.jpg


丨极客文库, 版权所有丨如未注明 , 均为原创丨
本网站采用知识共享署名-非商业性使用-相同方式共享 3.0 中国大陆许可协议进行授权
转载请注明原文链接:公司真实场景下的SQL优化总结
喜欢 (0)
[247507792@qq.com]
分享 (0)
多啦H梦
关于作者:
热爱开源,热爱分享,谢谢大家的资瓷!

您必须 登录 才能发表评论!

  • 精品技术教程
  • 编程资源分享
  • 问答交流社区
  • 极客文库知识库

客服QQ


QQ:2248886839


工作时间:09:00-23:00