文档章节

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

kim_o
 kim_o
发布于 2017/07/20 15:46
字数 914
阅读 9
收藏 0
点赞 0
评论 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
粉丝 1
博文 59
码字总数 32160
作品 0
深圳
程序员
如果你在找 CSS 学习资源,那就收藏这篇文章

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

伯乐在线 ⋅ 2014/11/07 ⋅ 0

SQL优化核心思想:或许你不知道的5条优化技巧

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

异步社区 ⋅ 04/25 ⋅ 0

SQL --select a+b from table

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

求是科技 ⋅ 2016/01/07 ⋅ 0

MySQL 关于分页查询一个非常有用的查询参数

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

红薯 ⋅ 2010/04/10 ⋅ 3

postgres:技巧

postgres:技巧 1.使用空间索引进行快速间隔数据类型的搜索.   间隔搜索有时候很慢,大部分原因是索引优化器不使用索引,并且在 开始列和结束列比较独立。一个解决方案是使用空间索引,它可...

funix ⋅ 2014/02/20 ⋅ 1

MySQL查询:查询一个表中类别字段中Max()最大值对应的记录

问题是: 数据库有一个表 code,里面有个点击量字段click_num和一个类别字段kind以及其它信息字段, 现在要搜出每个类别中点击量最大的那条记录,如果是10个类别,那么结果应该是10条记录, ...

hanzhankang ⋅ 2014/03/22 ⋅ 0

程序员的10大成功面试技巧

我遍览群书,发现关于程序员的面试技巧,好的建议其实并不多。 我们也发现,很多很有才华的程序员在面试的时候总是掉链子,这大概是因为他们把大多数时间都用来搞技术开发,而忽略了学习面试...

Lemon_C ⋅ 2014/12/02 ⋅ 0

SQL查询一个表中类别字段中Max()最大值对应的记录

问题是: 数据库有一个表 code,里面有个点击量字段click_num和一个类别字段kind以及其它信息字段, 现在要搜出每个类别中点击量最大的那条记录,如果是10个类别,那么结果应该是10条记录, ...

mac_zhao ⋅ 2014/11/21 ⋅ 0

PHP性能优化的小技巧

今天我们来分享10条PHP性能优化的小技巧: (1)循环内部不要声明变量,尤其是对象这样的变量 (2)foreach效率更高,尽量用foreach代替while和for循环 (3)在多重嵌套循环中,如有可能,应当将最...

京牛教育科技 ⋅ 2016/08/26 ⋅ 0

Mysql 一条SQL语句实现批量更新数据,update结合case、when和then的使用案例

如何用一条sql语句实现批量更新?mysql并没有提供直接的方法来实现批量更新,但是可以用点小技巧来实现。 复制代码 代码如下: UPDATE mytable SET myfield = CASE id WHEN 1 THEN 'value' WH...

stone_ryan ⋅ 2017/09/25 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

Greys Java在线问题诊断工具

Greys是一个JVM进程执行过程中的异常诊断工具。 在不中断程序执行的情况下轻松完成JVM相关问题排查工作 目标群体 有时候突然一个问题反馈上来,需要入参才能完成定位,但恰恰没有任何日志。回...

素雷 ⋅ 26分钟前 ⋅ 0

git从远程仓库拉取代码的常用指令

一种(比较麻烦的)拉代码的方法 git clone //克隆代码库,与远程代码库的主干建立连接,如果主干已经在就不用再clone啦,克隆路径为当前路径下的新创建的文件夹 git checkout -b //本地建立...

Helios51 ⋅ 40分钟前 ⋅ 0

005. 深入JVM学习—Java堆内存参数调整

1. JVM整体内存调整图解(调优关键) 实际上每一块子内存区域都会存在一部分可变伸缩区域,其基本流程:如果内存空间不足,则在可变的范围之内扩大内存空间,当一段时间之后,内存空间不紧张...

影狼 ⋅ 45分钟前 ⋅ 0

内存障碍: 软件黑客的硬件视图

此文为笔者近日有幸看到的一则关于计算机底层内存障碍的学术论文,并翻译(机译)而来[自认为翻译的还行],若读者想要英文原版的论文话,给我留言,我发给你。 内存障碍: 软件黑客的硬件视图...

Romane ⋅ 今天 ⋅ 0

SpringCloud 微服务 (七) 服务通信 Feign

壹 继续第(六)篇RestTemplate篇 做到现在,本机上已经有注册中心: eureka, 服务:client、order、product 继续在order中实现通信向product服务,使用Feign方式 下面记录学习和遇到的问题 贰 or...

___大侠 ⋅ 今天 ⋅ 0

gitee、github上issue标签方案

目录 [TOC] issue生命周期 st=>start: 开始e=>end: 结束op0=>operation: 新建issueop1=>operation: 评审issueop2=>operation: 任务负责人执行任务cond1=>condition: 是否通过?op3=>o......

lovewinner ⋅ 今天 ⋅ 0

浅谈mysql的索引设计原则以及常见索引的区别

索引定义:是一个单独的,存储在磁盘上的数据库结构,其包含着对数据表里所有记录的引用指针. 数据库索引的设计原则: 为了使索引的使用效率更高,在创建索引时,必须考虑在哪些字段上创建索...

屌丝男神 ⋅ 今天 ⋅ 0

String,StringBuilder,StringBuffer三者的区别

这三个类之间的区别主要是在两个方面,即运行速度和线程安全这两方面。 首先说运行速度,或者说是, 1.执行速度 在这方面运行速度快慢为:StringBuilder(线程不安全,可变) > StringBuffer...

时刻在奔跑 ⋅ 今天 ⋅ 0

java以太坊开发 - web3j使用钱包进行转账

首先载入钱包,然后利用账户凭证操作受控交易Transfer进行转账: Web3j web3 = Web3j.build(new HttpService()); // defaults to http://localhost:8545/Credentials credentials = Wallet......

以太坊教程 ⋅ 今天 ⋅ 0

Oracle全文检索配置与实践

Oracle全文检索配置与实践

微小宝 ⋅ 今天 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部