从零开始java数据库SQL优化（二）：多个LEFT JOIN的SQL优化

最新推荐文章于 2024-08-21 16:18:42 发布

qq_35755863

最新推荐文章于 2024-08-21 16:18:42 发布

阅读量4.3k

点赞数 1

文章标签：多Left Join的优化

本文链接：https://blog.csdn.net/qq_35755863/article/details/102236637

版权

本文详细介绍了如何优化包含多个LEFT JOIN的SQL查询，包括对用户表、用户角色表、角色菜单表和用户部门表的索引优化，遵循阿里Java开发手册的建议，并探讨了LEFT JOIN的查询原理和优化策略。此外，还讨论了预编译SQL的视图方式，阐述了视图的创建、使用场景及其与存储过程的区别。

摘要由CSDN通过智能技术生成

一：场景

我代码里需要在用户登录时将所有用户相关的用户，角色，部门，岗位，权限（其中权限放在菜单中，每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",
			
			
			
			fr.role_id as role_id,
			fr.role_code as role_code,
			fr.role_name as role_name,
			fr.role_range as role_range,
			
			
			fd.dept_id as dept_id,
			fd.dept_name as dept_name,
			fd.parent_id     as dept_parent,
			fd.parent_ids     as dept_parents,
			fd.company_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 ON fr.role_id = fkur.role_id
			LEFT JOIN fk_role_menu rm ON rm.role_id = fr.role_id
			LEFT JOIN fk_menu m ON m.menu_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'

耗时：