• 暂时停更一段时间!
  • 近期网站将陆续进行前端页面改造!
  • 招募网站编辑,联系站长!

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

文章目录[隐藏]

写在前面的话

来我们这家公司一年了 刚开始网站上线时首页打开非常的卡 后来发现是 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梦
关于作者:
热爱开源,热爱分享,谢谢大家的资瓷!

邀请您免费 注册账号 登录 即可参与讨论!