从零开始java数据库SQL优化（⼆）：多个LEFTJOIN的SQL
优化
⼀：场景
我代码⾥需要在⽤户登录时将所有⽤户相关的⽤户，⾓⾊，部门，岗位，权限（其中权限放在菜单中，每2张表有⼀张关联表），不多说直接上SQL
SELECT
a.fk_user_id AS "fk_user_id",
a.user_realname AS "user_realname",
a.user_name AS "user_name",
a.user_type AS "user_type",
a.sex AS "sex",
a.phone AS "phone",
a.password AS "password",
a.user_addr AS "user_addr",
a.avater AS "avater",
a.status AS "status",
fd.dept_id as dept_id,
fd.dept_name as dept_name,
fd.parent_id    as dept_parent,sql left join 多表连接
fd.parent_ids    as dept_parents,
fdpany_name as company_name,
fp.post_id as post_id,
fp.post_name as post_name,
fp.post_split as post_split,
m.perms as perms
FROM fk_user a
LEFT JOIN fk_user_role fkur ON fkur.user_id = a.fk_user_id
LEFT JOIN fk_role fr le_id = le_id
LEFT JOIN fk_role_menu rm le_id = fr.role_id
LEFT JOIN fk_menu m u_id = rm.menu_id
LEFT JOIN fk_dept fd ON fd.dept_id = a.dept_id
LEFT JOIN fk_user_post fup ON fup.user_id = a.fk_user_id
LEFT JOIN fk_post fp ON fp.post_id = fup.post_id
WHERE a.user_name ='admin'
耗时：
⼆：优化索引
1. ⽤户表优化
（1）⽤户表添加唯⼀索引
由于⽤户名在程序中控制为唯⼀，因此⽤户名创建唯⼀索引。（TIPS: 由于我程序使⽤的框架原因，登录先更具⽤户
名查出⽤户信息再⽐对密码。如果是⽤户名和密码⼀起查询，则可以在密码字段添加⼀个普通索引。）
（2）查询语句添加limit 1
⾄于原因，理由放在这篇
（3）优化结果：
⽤户表 Type级别已经达到效率最⾼了。
查询⼀下计划：
2. 优化⽤户⾓⾊表
上图可以看出来fkur（即⽤户⾓⾊表）的类型还是ALL，这⾥我们对这张表做⼀个优化。
（1）⽤户⾓⾊添加组合索引
（2）优化结果
，数据较少效果不明显，但是由查询计划看到Type
3.优化⾓⾊菜单表
（1）添加组合索引
（2）优化结果
效果不明显，但是看⼀下查询计划的Type
4.优化⽤户部门表
（1）添加索引
（2）优化结果
看⼀下查询计划，它的类型还是ALL（这⾥有个坑，如果数据过少，查询会⾃动判断⾛索引还是全表，因此表中数据要 > 2*查询出的数据索引才能有效果）
5.总结
到这⾥索引的添加就完成了，主要就是添加唯⼀索引和普通索引的问题。
三：多LEFT JOIN的SQL优化
⽬前，在阿⾥Java开发的规范⼿册上明确提到Left join表，最多不得超过3个。很尬尴的是在我们本例中后台采⽤的是Security的安全权限框架。⾄少需要将，⽤户、⾓⾊以及权限查询出来放⼊缓存以做权
限检验。其次，由于数据权限，还需要将⽤户的部门，岗位查询出来，基本上表是没法少的。因此，才出现上⾯的优先添加索引的这个⽅法。但是这⾥我们提供⼀些优化多个Left Join的思路吧。
（1）Left Join查询的⽅式
顾名思义，Left join当然是以左边表作为查询条件，有以下⼏个特点：
第⼀点：左表为基准，右表做为关联，查不到返回NULL。⽐如说 User Left  Join  Dept。User有10条数据，Dept只有5条数据
那么查询出来的⼀定10条数据。那么这⾥查询的开销是多少？最接近User * Dept次查询（当然这个前提是没有索引）
接近2张表的笛卡尔开销。Left Join越多，笛卡尔开销越⼤。
第⼆点： Mysql中的Join的查询原理是⼀种叫做nested loop join的算法。这种算法是以驱动表作为循环依据，⼀条⼀条传⼊下⼀
个表作为查询。
第三点： ALL式查询，我们也是以这个SQL作为优化⽰例。在最初的 查询计划中所有关联都是采⽤ALL的⽅式，也就是没有添加
索引情况下。当然再添加完索引查询计划就被优化了。
(2) 优化建议
针对上述的3点我们提出⼀些优化建议：
第⼀点：这个没办法，只能说尽量减少多个Left Join。
第⼆点：在业务场景允许的情况下，将⼩数据的表作为左表关联。
第三点：⾸先，添加必要的索引。其次，如果出现Join表的查询条件把查询条件放⼊Join中。这点的原理实际上基于第⼆点的
查询算法，但是我把放⼊这⾥是因为它可以较低查询级别。举个简单的例⼦，⽐如  ...Left Join  LEFT JOIN fk_menu m
u_id = rm.menu_id，这个地⽅如果有只要查询出菜单类型为按钮的。不要在这个SQL最后添加
好么，最好放出最有效的⽅法，预编译。使⽤临时表或者视图或者存储过程来存储SQL，代码直接调⽤即可。
1.创建视图
CREATE VIEW  v_login_user
AS
SELECT
a.fk_user_id AS "fk_user_id",
a.user_realname AS "user_realname",
a.user_name AS "user_name",
a.user_type AS "user_type",
a.sex AS "sex",
a.phone AS "phone",
a.password AS "password",
a.user_addr AS "user_addr",
a.avater AS "avater",
a.status AS "status",
fd.dept_id as dept_id,
fd.dept_name as dept_name,
fd.parent_id    as dept_parent,
fd.parent_ids    as dept_parents,
fdpany_name as company_name,
fp.post_id as post_id,
fp.post_name as post_name,
fp.post_split as post_split,
m.perms as perms
FROM fk_user a
LEFT JOIN fk_user_role fkur ON fkur.user_id = a.fk_user_id
LEFT JOIN fk_role fr le_id = le_id
LEFT JOIN fk_role_menu rm le_id = fr.role_id
LEFT JOIN fk_menu m u_id = rm.menu_id
LEFT JOIN fk_dept fd ON fd.dept_id = a.dept_id
LEFT JOIN fk_user_post fup ON fup.user_id = a.fk_user_id
LEFT JOIN fk_post fp ON fp.post_id = fup.post_id
2.调⽤视图
视图的本质就是⼀张临时表，
SELECT * FROM  v_login_user  WHERE user_name = 'admin'
3.视图的更新
⽐较感⼈的是，表数据的更新并不会导致视图的更新。⽐如说，我将User的username改为Admin123.视图查询UserName=Adm in123并没有 。需要我们⼿动更新：更新语句如同更新⼀个表的⾏⼀样。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。