从零开始java数据库SQL优化（二）：多个LEFT JOIN的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",
			
			
			
			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'

耗时：

二：优化索引

 1. 用户表优化

  （1）用户表添加唯一索引

                    由于用户名在程序中控制为唯一&