开发者社区> 努力滴码农> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

如何写出一手好 SQL ?(2)

简介: 最近频繁出现慢SQL告警,执行时间最长的竟然高达5分钟。导出日志后分析,主要原因竟然是没有命中索引和没有分页处理 。
+关注继续查看

OR优化

在Innodb引擎下or无法使用组合索引,比如:

select id,product_name from orders where mobile_no = '13421800407' or user_id = 100;

OR无法命中mobileno + userid的组合索引,可采用union,如下所示:

(select id,product_name from orders where mobile_no = '13421800407') union(select id,product_name from orders where user_id = 100);

此时id和product_name字段都有索引,查询才最高效。

IN优化

  1. IN适合主表大子表小,EXIST适合主表小子表大。由于查询优化器的不断升级,很多场景这两者性能差不多一样了。
  2. 尝试改为join查询,举例如下:

select id from orders where user_id in (select id from user where level = 'VIP');

采用JOIN如下所示:

select o.id from orders o left join user u on o.user_id = u.id where u.level = 'VIP';

不做列运算

通常在查询条件列运算会导致索引失效,如下所示:查询当日订单

select id from order where date_format(create_time,'%Y-%m-%d') = '2019-07-01';

date_format函数会导致这个查询无法使用索引,改写后:

select id from order where create_time between '2019-07-01 00:00:00' and '2019-07-01 23:59:59';

避免Select all

如果不查询表中所有的列,避免使用 SELECT *,它会进行全表扫描,不能有效利用索引。

Like优化

like用于模糊查询,举个例子(field已建立索引):

SELECT column FROM table WHERE field like '%keyword%';

这个查询未命中索引,换成下面的写法:

SELECT column FROM table WHERE field like 'keyword%';

去除了前面的%查询将会命中索引,但是产品经理一定要前后模糊匹配呢?全文索引fulltext可以尝试一下,但Elasticsearch才是终极武器。

Join优化

join的实现是采用Nested Loop Join算法,就是通过驱动表的结果集作为基础数据,通过该结数据作为过滤条件到下一个表中循环查询数据,然后合并结果。如果有多个join,则将前面的结果集作为循环数据,再次到后一个表中查询数据。

  1. 驱动表和被驱动表尽可能增加查询条件,满足ON的条件而少用Where,用小结果集驱动大结果集。
  2. 被驱动表的join字段上加上索引,无法建立索引的时候,设置足够的Join Buffer Size。
  3. 禁止join连接三个以上的表,尝试增加冗余字段。

Limit优化

limit用于分页查询时越往后翻性能越差,解决的原则:缩小扫描范围 ,如下所示:

select * from orders order by id desc limit 100000,10 耗时0.4秒select * from orders order by id desc limit 1000000,10耗时5.2秒

先筛选出ID缩小查询范围,写法如下:

select * from orders where id > (select id from orders order by id desc  limit 1000000, 1) order by id desc limit 0,10耗时0.5秒

如果查询条件仅有主键ID,写法如下:

select id from orders where id between 1000000 and 1000010 order by id desc耗时0.3秒

如果以上方案依然很慢呢?只好用游标了,感兴趣的朋友阅读JDBC使用游标实现分页查询的方法

其他数据库

作为一名后端开发人员,务必精通作为存储核心的MySQL或SQL Server,也要积极关注NoSQL数据库,他们已经足够成熟并被广泛采用,能解决特定场景下的性能瓶颈。

分类数据库特性
键值型Memcache用于内容缓存,大量数据的高访问负载
键值型Redis用于内容缓存,比Memcache支持更多的数据类型,并能持久化数据
列式存储HBaseHadoop体系的核心数据库,海量结构化数据存储,大数据必备。
文档型MongoDb知名文档型数据库,也可以用于缓存
文档型CouchDBApache的开源项目,专注于易用性,支持REST API
文档型SequoiaDB国内知名文档型数据库
图形Neo4J用于社交网络构建关系图谱,推荐系统等



版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
全网最全的 Notion 类软件盘点
在前几天我发布了《Notion Like 软件横向评测》。今天继续在此基础上盘点 Notion Like 相关软件,以供参考。 其中,由于尚未正式推出,微软 Loop 和印象笔记的 Verse 暂不介绍。此外,在《盘点那些具有特色的笔记软件》一文中介绍了另外一种强大但是与 Notion 设计有所区别的 All in One 产品 Fibery, 下文也不会再单列介绍。
285 0
来,教你写一手好SQL!(一)
导出日志后分析,主要原因竟然是没有命中索引和没有分页处理。其实这是非常低级的错误,我不禁后背一凉,团队成员的技术水平亟待提高啊。 改造这些 SQL 的过程中,总结了一些经验分享给大家,如果有错误欢迎批评指正。
30 0
如何写出高质量的SQL代码?
我们在写SQL代码的过程中,总会遇到一些奇奇怪怪的问题,比如少了个分号,标点符号写成全角了,表名多了个空格等等。这些问题一执行就报错,错了怎么也找不出问题所在。 今天给小伙伴讲讲如何写出高质量的SQL代码?
57 0
怎样才能写出高质量的SQL语句?
网上关于sql优化的答案数不胜数,可是篇幅太长,看一下滚动条就没有要看下去的欲望,这里自己总结精简了一下,全文1200字,阅读需要5分钟,还有很多不足之处望大佬多多指点~ 字段设计 价格使用定点数decimal 小单位大数额避免出现小数 单表字段不宜过多(最多30) 尽可能使用 not null 关联表的设计 一对多(使用外键) 多对多(单独新建一张表将多对多拆分成两个一对多) 一对一(如商品的基本信息(item)和商品的详细信息(item_intro),通常使用相同的主键或者增加一个外键字段(item_id)) 范式
84 0
SQL VS NoSQL 如何选择数据库
对SQL和NoSQL进行简单比较
3660 0
iosGCD用法
<p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px; color:rgb(85,85,85); font-family:'microsoft yahei'; font-size:15px"> Grand Central Dispatch(GCD)是异步执行任务的技术之一。一般将应
1605 0
sdl2.0示例
// gcc -o testDrone2_video testDrone2_video.c -lavcodec -lavformat -lswscale -lSDL2// g++ -o testDrone2_video testDrone2_video.
808 0
文章
问答
来源圈子
更多
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
PostgresChina2018_刘东明_PostgreSQL并行查询
立即下载
SQL Server在电子商务中的应用与实践
立即下载
SQL Sever迁移PG经验
立即下载