文档章节

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

kim_o
 kim_o
发布于 2017/07/20 15:46
字数 914
阅读 10
收藏 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
粉丝 2
博文 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
MySQL 关于分页查询一个非常有用的查询参数

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

红薯
2010/04/10
2.2K
3
一言不合就学习:PHP优秀学习文章资料汇总整理

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

FrancisSoung
2016/08/25
0
0

没有更多内容

加载失败,请刷新页面

加载更多

SQL count(*) 和count(1)的区别

开发中经常会使用这两个聚合函数,作用都是用来统计记录行,今天查找资料发现,其实这两个函数并没有区别, 实践才是检验的标准,首先看执行计划(表是我自己建立的): 可以看到,两个执行计...

一曲图森破
14分钟前
1
0
ppwjs之bootstrap文字排版:字体设置

<!DOCTYPT html><html><head><meta http-equiv="content-type" content="text/html; charset=utf-8" /><title>ppwjs欢迎您</title><link rel="icon" href="/favicon.ico" ......

ppwjs
17分钟前
1
0
区块链100讲:详解区块链之P2P网络

1 P2P网络 如果我们简单来看 P2P 技术,它的应用领域已经非常广泛了,从流媒体到点对点通讯、从文件共享到协同处理,多种领域都有它的身影出现。 同样的,P2P 的网络协议也有很多,比较常见的...

HiBlock
32分钟前
0
0
74.expect脚本同步文件以及指定host同步文件 构建分发系统文件和命令

20.31 expect脚本同步文件: 在expect脚本中去实现在一台机器上把文件同步到另外一台机器上去。核心命令用的是rsync ~1.自动同步文件 #!/usr/bin/expect set passwd "123456" spawn rsync -a...

王鑫linux
56分钟前
1
0
TypeScript项目引用(project references)

转发 TypeScript项目引用(project references) TypeScript新特性之项目引用(project references) 项目引用是TypeScript 3.0中的一项新功能,允许您将TypeScript程序构建为更小的部分。 通过这...

durban
今天
1
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部