记录一次My SQL Join和IN的效率区别

最近发现公司内部的项目管理系统登录越来越慢了，于是看一下日志输出，用户登录时获取权限最长花了几十秒的时间。
很基础的5张表 ,除了主键未添加任何索引
数据量
用户表hrm_resource: 700+
用户角色表sys_user_role: 6000+
角色表sys_role:30+
角色权限表sys_role_permission :6000+
权限表sys_permission :400+
无关表 sys_system：6条
第一版:

SELECT
	sys_permission.*
FROM
	hrm_resource
JOIN sys_user_role ON hrm_resource.id = sys_user_role.user_id
JOIN sys_role ON sys_user_role.role_id = sys_role.id
JOIN sys_role_permission ON sys_role_permission.role_id = sys_role.id
JOIN sys_permission ON sys_permission.id = sys_role_permission.permission_id
JOIN sys_system
WHERE
	hrm_resource.loginid = 13027432749
AND sys_role.role_status = '0'
AND sys_permission.permission_system_code = 'SUB_SYS_0005'
GROUP BY id

第二版:

SELECT
	sys_permission.*
FROM
	hrm_resource
JOIN sys_user_role ON hrm_resource.id = sys_user_role.user_id
JOIN sys_role ON sys_user_role.role_id = sys_role.id
JOIN sys_role_permission ON sys_role_permission.role_id = sys_role.id
JOIN sys_permission ON sys_permission.id = sys_role_permission.permission_id
JOIN sys_system
WHERE
	hrm_resource.loginid = 13027432749
AND sys_role.role_status = '0'
AND sys_permission.permission_system_code = 'SUB_SYS_0005'

很传统的从用户查到权限，但是我发现在最后一行多联 JOIN sys_system 一张无关表 ，
于是我去掉 GROUP BY id 分组
刚开始还挺奇怪的 为什么要加分组，好家伙因为多联了一张无关表导致 sys_system的N条记录使结果翻了N倍数据

去掉分组的时间发现只快了一点点。
总结：第一版到第二版优化的时间是MySql分组去重的时间。
第三版:

SELECT
	sys_permission.*
FROM
	hrm_resource
JOIN sys_user_role ON hrm_resource.id = sys_user_role.user_id
JOIN sys_role ON sys_user_role.role_id = sys_role.id
JOIN sys_role_permission ON sys_role_permission.role_id = sys_role.id
JOIN sys_permission ON sys_permission.id = sys_role_permission.permission_id
WHERE
	hrm_resource.loginid = 13027432749
AND sys_role.role_status = '0'
AND sys_permission.permission_system_code = 'SUB_SYS_0005'

继续接着优化 删除无关联的表
时间基本上少了10倍，但还是花了3秒+，速度还是接受不了。
总结：第二版到第三版的优化，主要集中在 1* N的问题上，由于没有加ON条件导致主表每产生一条数据，就要关联子表的NN调数据。而且速度的快慢取决于 sys_system 的数量多少，还好数据量不大只有几条数据，否则会更慢

第四版：

SELECT
	sys_permission.*
FROM
	  (SELECT
	sys_role_permission.permission_id
FROM
	hrm_resource
JOIN sys_user_role ON hrm_resource.id = sys_user_role.user_id
JOIN sys_role ON sys_user_role.role_id = sys_role.id
JOIN sys_role_permission ON sys_role_permission.role_id = sys_role.id
WHERE
	hrm_resource.loginid = 13027432749
AND
	sys_role.role_system_code  = 'SUB_SYS_0005'
AND sys_role.role_status = '0') t
JOIN sys_permission ON t.permission_id  = sys_permission.id   

SELECT
	sys_role_permission.permission_id
FROM
	hrm_resource
JOIN sys_user_role ON hrm_resource.id = sys_user_role.user_id
JOIN sys_role ON sys_user_role.role_id = sys_role.id
JOIN sys_role_permission ON sys_role_permission.role_id = sys_role.id
WHERE
	hrm_resource.loginid = 13027432749
AND
	sys_role.role_system_code  = 'SUB_SYS_0005'
AND sys_role.role_status = '0'

根据上述理论，接着优化，是不是只要把子表的数据变少，减少数据的关联 效率是不是就提高了？
忽略JOIN 的子查询速度，发现 该结果速度还不如一张一张表关联下来速度快。
MySQL JOIN的原理就不细讲了，https://www.cnblogs.com/shengdimaya/p/7123069.html
总结下来就是：左表和右边 用ON 关联的时候有一个条件 t.permission_id = sys_permission.id
那就导致，左表拿到结果集，然后和右表的结果一条条的匹配数据，由于没有加索引的情况只能左表数据每条数据都要扫描右表的全部数据找出匹配的结果集

第五版：

SELECT
	*
FROM
	  sys_permission
WHERE
sys_permission.id IN (SELECT
	sys_role_permission.permission_id
FROM
	hrm_resource
JOIN sys_user_role ON hrm_resource.id = sys_user_role.user_id
JOIN sys_role ON sys_user_role.role_id = sys_role.id
JOIN sys_role_permission ON sys_role_permission.role_id = sys_role.id
WHERE
	hrm_resource.loginid = 13027432749
AND
	sys_role.role_system_code  = 'SUB_SYS_0005'
AND sys_role.role_status = '0')

用子查询速度明显起飞。
总结：此处原理是sys_permission.id 为主键 IN 可以直接找到结果，而无需每次扫描子查询的每条数据，逐条处理，在这里速度的因素就只剩下子查询的速度了，前面已经测试该SQL的子查询速度很快，所以无需优化。

第六版: 使用索引 sys_role_permission.permission_id 普通索引
下图均为多次执行，取得中间值

使用第一版的SQL 523毫秒

使用第二版的SQL:505毫秒

使用第三版的SQL:505毫秒

使用第四版的SQL:72毫秒

使用第五版的SQL:70毫秒

结论：当没有索引的情况：JOIN 的关联会扫描全表进行数据匹配使得查询速度变慢
有索引的情况：JOIN 和 IN 的差距并不大。
建议：外键能加索引还是要加上的索引的。不能加索引那就去找到有索引的字段，并使用它
项目太过古老，内部使用也没人在乎卡不卡，所以一直拖到现在才解决这个问题。