文档章节

或许你不知道的10条SQL技巧

kim_o
 kim_o
发布于 2017/07/20 15:46
字数 914
阅读 11
收藏 0

原创 2017-07-16 58沈剑 架构师之路

这几天在写索引,想到一些有意思的TIPS,希望大家有收获。

 

一、一些常见的SQL实践

(1)负向条件查询不能使用索引

  • select * from order where status!=0 and stauts!=1

not in/not exists都不是好习惯

 

可以优化为in查询:

  • select * from order where status in(2,3)

 

(2)前导模糊查询不能使用索引

  • select * from order where desc like '%XX'

而非前导模糊查询则可以:

  • select * from order where desc like 'XX%'

 

(3)数据区分度不大的字段不宜使用索引

  • select * from user where sex=1

原因:性别只有男,女,每次过滤掉的数据很少,不宜使用索引。

 

经验上,能过滤80%数据时就可以使用索引。对于订单状态,如果状态值很少,不宜使用索引,如果状态值很多,能够过滤大量数据,则应该建立索引。

 

(4)在属性上进行计算不能命中索引

  • select * from order where YEAR(date) < = '2017'

即使date上建立了索引,也会全表扫描,可优化为值计算:

  • select * from order where date < = CURDATE()

或者:

  • select * from order where date < = '2017-01-01'

 

二、并非周知的SQL实践

(5)如果业务大部分是单条查询,使用Hash索引性能更好,例如用户中心

  • select * from user where uid=?

  • select * from user where login_name=?

原因:

B-Tree索引的时间复杂度是O(log(n))

Hash索引的时间复杂度是O(1)

 

(6)允许为null的列,查询有潜在大坑

单列索引不存null值,复合索引不存全为null的值,如果列允许为null,可能会得到“不符合预期”的结果集

  • select * from user where name != 'shenjian'

如果name允许为null,索引不存储null值,结果集中不会包含这些记录。

 

所以,请使用not null约束以及默认值。

 

(7)复合索引最左前缀,并不是值SQL语句的where顺序要和复合索引一致

用户中心建立了(login_name, passwd)的复合索引

  • select * from user where login_name=? and passwd=?

  • select * from user where passwd=? and login_name=?

都能够命中索引

 

  • select * from user where login_name=?

也能命中索引,满足复合索引最左前缀

 

  • select * from user where passwd=?

不能命中索引,不满足复合索引最左前缀

 

(8)使用ENUM而不是字符串

ENUM保存的是TINYINT,别在枚举中搞一些“中国”“北京”“技术部”这样的字符串,字符串空间又大,效率又低。

 

三、小众但有用的SQL实践

(9)如果明确知道只有一条结果返回,limit 1能够提高效率

  • select * from user where login_name=?

可以优化为:

  • select * from user where login_name=? limit 1

原因:

你知道只有一条结果,但数据库并不知道,明确告诉它,让它主动停止游标移动

 

(10)把计算放到业务层而不是数据库层,除了节省数据的CPU,还有意想不到的查询缓存优化效果

  • select * from order where date < = CURDATE()

这不是一个好的SQL实践,应该优化为:

$curDate = date('Y-m-d');

$res = mysql_query(

    'select * from order where date < = $curDate');

原因:

释放了数据库的CPU

多次调用,传入的SQL相同,才可以利用查询缓存

 

(11)强制类型转换会全表扫描

  • select * from user where phone=13800001234

你以为会命中phone索引么?大错特错了,这个语句究竟要怎么改?

 

末了,再加一条,不要使用select *(潜台词,文章的SQL都不合格 =_=),只返回需要的列,能够大大的节省数据传输量,与数据库的内存使用量哟。

 

本文转载自:

共有 人打赏支持
kim_o
粉丝 3
博文 79
码字总数 28082
作品 0
深圳
程序员
私信 提问
如果你在找 CSS 学习资源,那就收藏这篇文章

本文由伯乐在线 -伯小乐 翻译。未经许可,禁止转载! 英文出处:Grace Smith。欢迎加入翻译组。 如果你在寻找一些 CSS 基础和高级的指南或资源,那这篇文章应该有你要的 CSS 初级和高级指南:...

伯乐在线
2014/11/07
0
0
SQL优化核心思想:或许你不知道的5条优化技巧

点击关注 异步图书,置顶公众号 每天与你分享 IT好书 技术干货 职场知识 参与文末话题讨论,每日赠送异步图书。 ——异步小编 随着系统的数据量逐年增加,并发量也成倍增长,SQL性能越来越成...

异步社区
04/25
0
0
SQL --select a+b from table

今天做工程时,遇到了select a+b from table的问题,记录下来。 工程需求:取出用户关注/粉丝表(tuserfollow)中某个用户A的粉丝数。 表设计如下 由表设计可知,用户A既可以在userida位置,...

求是科技
2016/01/07
39
0
一言不合就学习:PHP优秀学习文章资料汇总整理

发现一个不错的网站,DevStore,移动互联网企业运营解决方案整合平台,产品前期-设计阶段-开发测试-推广阶段-运维运营,各个阶段都有涉及。 下面整理一些针对包括程序员、运营、设计师等在内...

FrancisSoung
2016/08/25
0
0
MySQL 关于分页查询一个非常有用的查询参数

MySQL 的 SELECT 语句中有这么一个参数:这个参数可以配合 MySQL 提供的函数 FOUND_ROWS() 来使用。 当我们使用 LIMIT ?,? 进行分页查询时,返回的只是你想要的记录,例如 LIMIT 10 则是返回...

红薯
2010/04/10
2.3K
3

没有更多内容

加载失败,请刷新页面

加载更多

《资治通鉴》读书笔记及阅读感悟2200字

《资治通鉴》读书笔记及阅读感悟2200字: 1.用夏桀,商纣暴虐昏君和商汤,周武王仁德明君做比较,因为人民归心,上天赐名要不然就永远要恪守臣节至死不渝。 又用商朝的微子取代纣王,商朝可以...

原创小博客
13分钟前
0
0
十万个为什么之为什么大家都说dubbo

Dubbo是什么? 使用背景 dubbo为什么这么流行, 为什么大家都这么喜欢用dubbo; 通过了解分布式开发了解到, 为适应访问量暴增,业务拆分后, 子应用部署在多台服务器上,而多台服务器通过可以通过d...

尾生
51分钟前
2
0
Docker搭建代码质量检测平台-SonarQube(中文版)

Sonar是一个用于代码质量管理的开源平台,用于管理源代码的质量,可以从七个维度检测代码质量。通过插件形式,可以支持包括java,C#,C/C++,PL/SQL,Cobol,JavaScrip,Groovy等等二十几种编程语言...

Jacktanger
58分钟前
2
0
Windows / Linux / MacOS 设置代理上网的方法汇总

本文汇总了 Windows / Linux / MacOS 设置代理上网的各种方法,总结如下: 1、设置系统代理(Windows、Linux、MacOS) 2、设置代理插件(Chrome、Chromium、Firefox、Opera、QQ等浏览器) 3、...

sunboy2050
昨天
4
0
自定义 Maven 的 repositories

有时,应用中需要一些比较新的依赖,而这些依赖并没有正式发布,还是处于milestone或者是snapshot阶段,并不能从中央仓库或者镜像站上下载到。此时,就需要 自定义Maven的<repositories>。 ...

waylau
昨天
3
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部