2023年6月23日发(作者：)

从零开始java数据库SQL优化（⼆）：多个LEFTJOIN的SQL优化⼀：场景 我代码⾥需要在⽤户登录时将所有⽤户相关的⽤户，⾓⾊，部门，岗位，权限（其中权限放在菜单中，每2张表有⼀张关联表），不多说直接上SQLSELECT

_user_id AS "fk_user_id", _realname AS "user_realname", _name AS "user_name", _type AS "user_type", AS "sex", AS "phone", rd AS "password", _addr AS "user_addr", AS "avater", AS "status",

_id as role_id, _code as role_code, _name as role_name, _range as role_range,

_id as dept_id, _name as dept_name, _id as dept_parent, _ids as dept_parents, y_name as company_name,

_id as post_id, _name as post_name, _split as post_split,

as perms

FROM fk_user a LEFT JOIN fk_user_role fkur ON _id = _user_id LEFT JOIN fk_role fr ON _id = _id LEFT JOIN fk_role_menu rm ON _id = _id LEFT JOIN fk_menu m ON _id = _id

LEFT JOIN fk_dept fd ON _id = _id LEFT JOIN fk_user_post fup ON _id = _user_id LEFT JOIN fk_post fp ON _id = _id

WHERE _name ='admin'耗时：⼆：优化索引 1. ⽤户表优化 （1）⽤户表添加唯⼀索引 由于⽤户名在程序中控制为唯⼀，因此⽤户名创建唯⼀索引。（TIPS: 由于我程序使⽤的框架原因，登录先更具⽤户 名查出⽤户信息再⽐对密码。如果是⽤户名和密码⼀起查询，则可以在密码字段添加⼀个普通索引。）

（2）查询语句添加limit 1

⾄于原因，理由放在这篇

（3）优化结果：

查询⼀下计划：

⽤户表 Type级别已经达到效率最⾼了。

2. 优化⽤户⾓⾊表 上图可以看出来fkur（即⽤户⾓⾊表）的类型还是ALL，这⾥我们对这张表做⼀个优化。（1）⽤户⾓⾊添加组合索引

（2）优化结果 ，数据较少效果不明显，但是由查询计划看到Type3.优化⾓⾊菜单表（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 ON _id = _id，这个地⽅如果有只要查询出菜单类型为按钮的。不要在这个SQL最后添加 _type='BUTTON'，⽽是在Left Join（select * from fk_menu where mene_type = 'BUTTON' ） m on ....。

四：预编译SQL-视图 好么，最好放出最有效的⽅法，预编译。使⽤临时表或者视图或者存储过程来存储SQL，代码直接调⽤即可。1.创建视图CREATE VIEW v_login_user AS SELECT

_user_id AS "fk_user_id", _realname AS "user_realname", _name AS "user_name", _type AS "user_type", AS "sex", AS "phone", rd AS "password", _addr AS "user_addr", AS "avater", AS "status",

_id as role_id, _code as role_code, _name as role_name, _range as role_range,

_id as dept_id, _name as dept_name, _id as dept_parent, _ids as dept_parents, y_name as company_name,

_id as post_id, _name as post_name, _split as post_split,

as perms

FROM fk_user a LEFT JOIN fk_user_role fkur ON _id = _user_id LEFT JOIN fk_role fr ON _id = _id LEFT JOIN fk_role_menu rm ON _id = _id LEFT JOIN fk_menu m ON _id = _id

LEFT JOIN fk_dept fd ON _id = _id LEFT JOIN fk_user_post fup ON _id = _user_id LEFT JOIN fk_post fp ON _id = _id

2.调⽤视图 视图的本质就是⼀张临时表，SELECT * FROM v_login_user WHERE user_name = 'admin'3.视图的更新

⽐较感⼈的是，表数据的更新并不会导致视图的更新。⽐如说，我将User的username改为Admin123.视图查询UserName=Admin123并没有 。需要我们⼿动更新：更新语句如同更新⼀个表的⾏⼀样。4.视图新增和删除 从上⾯可以看出，新增和删除需要我们⼿动来喽，当然同新增/删除⼀张表⼀致。5.说明视图的使⽤场景 视图的创建就是将当前数据缓存到⼀张临时表中，然后查询，⼀般不适⽤可以修改的数据，因为修改要改表，也要改视图很⿇烦。那么视图有什么作⽤呢？作⽤就是对前⼀天或者⼏天数据做统计。很明显本例不适⽤。五：创建存储过程 存储过程的SQL⽐较复杂，这⾥就不上传了。其实在本例中也不适⽤。Mysql的视图是⼀张临时表，它是将查询结果放⼊其中，当被查询的表出现改动，视图临时表数据不会改动。⽽Mysql的存储过程则是预先将SQL语句编译成可执⾏的机器语⾔，只⽤⽤的时候才去查询数据。