江苏龙网

  1. 主页 > 生活百科 > >

经验分享：如何去写一手好SQL？( 二 )

SQL


避免空值MySQL中字段为NULL时依然占用空间，会使索引、索引统计更加复杂 。从NULL值更新到非NULL无法做到原地更新，容易发生索引分裂影响性能 。尽可能将NULL值用有意义的值代替，也能避免SQL语句里面包含is not null的判断 。微信搜索web_resource 关注获取更多推送 。微信搜索web_resource 关注获取更多推送 。
text类型优化由于text字段储存大量数据，表容量会很早涨上去，影响其他字段的查询性能 。建议抽取出来放在子表里，用业务主键关联 。
索引优化索引分类

  • 普通索引：最基本的索引 。
  • 组合索引：多个字段上建立的索引，能够加速复合查询条件的检索 。
  • 唯一索引：与普通索引类似，但索引列的值必须唯一，允许有空值 。
  • 组合唯一索引：列值的组合必须唯一 。
  • 主键索引：特殊的唯一索引，用于唯一标识数据表中的某一条记录，不允许有空值，一般用primary key约束 。
  • 全文索引：用于海量文本的查询，MySQL5.6之后的InnoDB和MyISAM均支持全文索引 。由于查询精度以及扩展性不佳，更多的企业选择Elasticsearch 。
索引优化
  • 分页查询很重要，如果查询数据量超过30%，MYSQL不会使用索引 。
  • 单表索引数不超过5个、单个索引字段数不超过5个 。
  • 字符串可使用前缀索引，前缀长度控制在5-8个字符 。
  • 字段唯一性太低，增加索引没有意义，如：是否删除、性别 。
合理使用覆盖索引，如下所示：
select login_name, nick_name from member where login_name = ?【经验分享：如何去写一手好SQL？】login_name, nick_name两个字段建立组合索引，比login_name简单索引要更快 。
SQL优化分批处理博主小时候看到鱼塘挖开小口子放水，水面有各种漂浮物 。浮萍和树叶总能顺利通过出水口，而树枝会挡住其他物体通过，有时还会卡住，需要人工清理 。MySQL就是鱼塘，最大并发数和网络带宽就是出水口，用户SQL就是漂浮物 。微信搜索web_resource 关注获取更多推送 。
不带分页参数的查询或者影响大量数据的update和delete操作，都是树枝，我们要把它打散分批处理，举例说明：
业务描述：更新用户所有已过期的优惠券为不可用状态 。
SQL语句：
update status=0 FROM `coupon` WHERE expire_date <= #{currentDate} and status=1;如果大量优惠券需要更新为不可用状态，执行这条SQL可能会堵死其他SQL，分批处理伪代码如下：
int pageNo = 1;int PAGE_SIZE = 100;while(true) {List<Integer> batchIdList = queryList('select id FROM `coupon` WHERE expire_date <= #{currentDate} and status = 1 limit #{(pageNo-1) * PAGE_SIZE},#{PAGE_SIZE}');if (CollectionUtils.isEmpty(batchIdList)) {return;}update('update status = 0 FROM `coupon` where status = 1 and id in #{batchIdList}')；pageNo ++;}操作符<>优化通常<>操作符无法使用索引，举例如下，查询金额不为100元的订单：
select id from orders where amount != 100;如果金额为100的订单极少，这种数据分布严重不均的情况下，有可能使用索引 。鉴于这种不确定性，采用union聚合搜索结果，改写方法如下：
(select id from orders where amount > 100) union all(select id from orders where amount < 100 and amount > 0)OR优化在Innodb引擎下or无法使用组合索引，比如：
select id，product_name from orders where mobile_no = '13421800407' or user_id = 100;OR无法命中mobile_no + user_id的组合索引，可采用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优化IN适合主表大子表小，EXIST适合主表小子表大 。由于查询优化器的不断升级，很多场景这两者性能差不多一样了 。
尝试改为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';


推荐阅读


上一篇：为什么网站被挂码？要怎么处理？

下一篇：为什么你的云服务器总被入侵？