写一手好 SQL 很有必要(3)

    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无法命中mobileno + userid的组合索引，可采用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'; 

避免Select all

如果不查询表中所有的列，避免使用 SELECT *，它会进行全表扫描，不能有效利用索引。

Like优化

like用于模糊查询，举个例子(field已建立索引)：

（编辑：ASP站长网）