专栏首页数据和云SQL优化之道 - 或许你不知道的10条SQL技巧

SQL优化之道 - 或许你不知道的10条SQL技巧

一、一些常见的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都不合格 =_=),只返回需要的列,能够大大的节省数据传输量,与数据库的内存使用量哟。

思路比结论重要,希望有收获。

本文分享自微信公众号 - 数据和云(OraNews),作者:沈剑

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2017-07-21

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 性能优化之查询转换 - 子查询类

    作者简介 ? 韩锋 精通包括Oracle、MySQL、informix等多种关系型数据库,有丰富的数据库架构设计开发经验。就职于宜信。 子查询,是SQL中常...

    数据和云
  • 效率提高N倍的19条MySQL优化秘籍

    MySQL对于IN做了相应的优化,即将IN中的常量全部存储在一个数组里面,而且这个数组是排好序的。但是如果数值较多,产生的消耗也是比较大的。再例如:select...

    数据和云
  • 【快讯】在线体验Oracle Database 12.2 SQL新特性

    Oracle Database 12.2 已经让广大粉丝望眼欲穿,虽然文档已然发布,但是实验无从做起。 现在,可以通过 Oracle Live SQL 站点(文...

    数据和云
  • 书写高质量SQL的30条建议,这下够用了!

    本文将结合实例demo,阐述30条有关于优化SQL的建议,多数是实际开发中总结出来的,希望对大家有帮助。

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

    这几天在写索引,想到一些有意思的TIPS,希望大家有收获。 一、一些常见的SQL实践 (1)负向条件查询不能使用索引 select * from order w...

    架构师之路
  • 浅谈 Mybatis中的 ${ } 和 #{ }的区别

    好了,真正做开发也差不多一年了。一直都是看别人的博客,自己懒得写,而且也不会写博客,今天就开始慢慢的练习一下写博客吧。前段时间刚好在公司遇到这样的问题。 一、举...

    Dato
  • Mysql入门 原

    晓歌
  • SQL Server基础SQL脚本之内外连接、交叉连接;函数、子查询

    代码大概200行左右 本系列,几乎都是代码,记得当时写的时候用的是微软的官方实例数据库AdventureWorks_Data.mdf、AdventureWor...

    赵腰静
  • MySQL和Oracle中的半连接测试总结(一)(r10笔记第31天)

    SQL中的半连接在MySQL和Oracle还是存在一些差距,从测试的情况来看,Oracle的处理要更加全面。 首先我们来看看在MySQL中怎么测试,对于MySQ...

    jeanron100
  • mysql注入高级篇3--报错注入

    lonelyvaf

扫码关注云+社区

领取腾讯云代金券