多表连接查询

Posted by 小兵兵 on Tuesday, January 29, 2019

目录

查询用户拥有的权限

子查询和连接查询2种,比较推荐连接查询,子查询比较影响性能,子查询看起来比较容易理解,而连接查询需要比较熟悉各个表之间的关系

SELECT DISTINCT p.* FROM t_permission p
    LEFT JOIN t_role_permission trp ON trp.`permissionid`=p.`id`
    LEFT JOIN t_role tr ON tr.`id`=trp.`roleid`
    WHERE tr.id IN(
        SELECT t_r.id FROM t_role t_r
        LEFT JOIN t_user_role t_u_r
            ON t_u_r.`roleid`=t_r.`id`
        WHERE t_u_r.`userid`=31
    );

子查询不太推荐,比较影响性能
推荐连接查询
SELECT DISTINCT p.* FROM t_permission p
    LEFT JOIN t_role_permission trp ON trp.`permissionid`=p.`id`
    LEFT JOIN t_role tr ON tr.`id`=trp.`roleid`
    LEFT JOIN t_user_role tur ON tur.`roleid`=tr.`id`
    LEFT JOIN t_user tu ON tu.`id`=tur.`userid`
    WHERE tu.`id`=31;


Limit优化

limit用于分页查询时越往后翻性能越差,解决的原则:缩小扫描范围 ,如下所示:

select * from orders order by id desc limit 100000,10 耗时0.4select * from orders order by id desc limit 1000000,10耗时5.2

先筛选出ID缩小查询范围,写法如下:

select * from orders where id > (select id from orders order by id desc  limit 1000000, 1) order by id desc limit 0,10耗时0.5

如果查询条件仅有主键ID,写法如下:

select id from orders where id between 1000000 and 1000010 order by id desc耗时0.3

分批处理

举例说明:业务描述:更新用户所有已过期的优惠券为不可用状态。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 ++;
}

参考

写一手好 SQL 很有必要