|
博主小时候看到鱼塘挖开小口子放水，水面有各种漂浮物。浮萍和树叶总能顺利通过出水口，而树枝会挡住其他物体通过，有时还会卡住，需要人工清理。MySQL就是鱼塘，最大并发数和网络带宽就是出水口，用户SQL就是漂浮物。
不带分页参数的查询或者影响大量数据的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 o.id from orders o left join user u on o.user_id = u.id where u.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已建立索引)：
- SELECT column FROM table WHERE field like '%keyword%';
这个查询未命中索引，换成下面的写法：
- SELECT column FROM table WHERE field like 'keyword%';
去除了前面的%查询将会命中索引，但是产品经理一定要前后模糊匹配呢?全文索引fulltext可以尝试一下，但Elasticsearch才是终极武器。
Join优化 （编辑：南平站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
