在慢查询日志看这个sql 慢,
感觉可以优化
select ** from User u,Role r where u.id=r.role and exists (select id from User r2 where r2.role<>3 and r.role=r2.role);这个有时候在线20s ,Lock_time: 0 Rows_sent: 310 Rows_examined: 618740有没有其他方法来简化这个sql
感觉可以优化
select ** from User u,Role r where u.id=r.role and exists (select id from User r2 where r2.role<>3 and r.role=r2.role);这个有时候在线20s ,Lock_time: 0 Rows_sent: 310 Rows_examined: 618740有没有其他方法来简化这个sql
INNER JOIN User r2 ON r.role=r2.role AND r2.role<>3
在连接字段上建立索引
INNER JOIN User r2 ON r.role=r2.role AND r2.role<>3
返回数据是 29013说明数据有差异 (INNER JOIN 是否应该换成left join 或者 right join )
select ** from User u,Role r where u.id=r.role and exists (select id from User r2 where r2.role<>3 and r.role=r2.role); 数据不同, 替换失败exists (select id from User r2 where r2.role<>3 and r.role=r2.role);
分析是同一表之间某个字段有父子关系
与
select ** from User u INNER JOIN Role r ON u.id=r.role
INNER JOIN User r2 ON r.role=r2.role AND r2.role<>3差异的记录是哪些
与
select ** from User u INNER JOIN Role r ON u.id=r.role
INNER JOIN User r2 ON r.role=r2.role AND r2.role<>3 group by id ,role因为是多对多的噶 关系
最后就group by id ,role