mysql 中LIKE 与FIND_IN_SET 与关联表left join 速度效率比较

--方式二先 left join on再 where条件 用时 5.641s 5.488s 7.679s 6.230
select a.* from af_archive a left join archiveedituser b on a.ArchiveUUID=b.ArchiveUUID where b.EditUserID=6 and a.CreateUserID=10 order by a.CreateTime desc limit 11,20;

-- 方式二用where in 用时： 6.397s 5.842s 5.621s
select a.* from af_archive a where 6 in (select b.EditUserID from archiveedituser b where a.ArchiveUUID=b.ArchiveUUID) and a.CreateUserID=10 order by a.CreateTime desc limit 11,20;

=================================

后面把数据添加到150W条数据测试结果如下

-- Like 7.256s 6.072s 8.108s 6.013s 6.060s
-- select * from jsj_af.af_archive where concat(‘,‘,EditUserIDArr,‘,‘) like CONCAT(‘%,‘,‘6‘,‘,%‘) and CreateUserID=10 order by CreateTime desc limit 11,20;

-- FIND_IN_SET 5.624s 5.574s 5.608s 7.465s 5.931s
-- select * from jsj_af.af_archive where FIND_IN_SET(‘6‘,EditUserIDArr) and CreateUserID=10 order by CreateTime desc limit 11,20;

-- left join on 5.872s 7.254s 7.317s 5.671s 6.305s
-- select a.* from jsj_af.af_archive a left join jsj_af.archiveedituser b on a.ArchiveUUID=b.ArchiveUUID and b.EditUserID=6 where a.CreateUserID=10 order by a.CreateTime desc limit 11,20;

mysql 中LIKE 与FIND_IN_SET 与关联表left join 速度效率比较

标签：bsp   user   速度   col   select   style   order by   sele   ike