单表查询和多表连接查询哪个效率更快


一.第一个解答来源于《高性能Mysql》中的回答

很多高性能的应用都会对关联查询进行分解。简单地,可以对每个表进行一次单表查询,然后将结果在应用程序中进行关联。

例如,下面这个查询:

select * from tag

join tag_post on tag_post.tag_id=tag.id

join post on tag_post.post_id=post.id

where tag.tag=’mysql’;

可以分解成下面这些查询来代替:

Select * from tag where tag=’mysql’;

Select * from tag_post where tag_id=1234;

Select * from post where id in(123,456,567,9989,8909);

事实上,用分解关联查询的方式重构查询具有如下优势:(高并发、高性能的应用中,一般建议使用单表查询)

  1. 让缓存的效率更高。

许多应用程序可以方便地缓存单表查询对应的结果对象。另外对于MySQL的查询缓存来说,如果关联中的某个表发生了变化,那么就无法使用查询缓存了,而拆分后,如果某个表很少改变,那么基于该表的查询就可以重复利用查询缓存结果了。

  1. 将查询分解后,执行单个查询可以减少锁的竞争。

  2. 在应用层做关联,可以更容易对数据库进行拆分,更容易做到高性能和可扩展。

  3. 查询本身效率也可能会有所提升。

  4. 可以减少冗余记录的查询。

  5. 更进一步,这样做相当于在应用中实现了哈希关联,而不是使用MySQL的嵌套环关联,某些场景哈希关联的效率更高很多。

  6. 单表查询有利于后期数据量大了分库分表,如果联合查询的话,一旦分库,原来的sql都需要改动。

  7. 上次看到某个CTO技术分享,公司规定底层禁止用join联合查询。数据大的时候确实慢。

  8. 联合查询或许确实快,但是mysql的资源通常比程序代码的资源紧张的多。

二.单表多次查询和多表联合查询效果对比

在同等条件下,以用户、角色、及用户角色关联表为例。

select * from sys_user where user_name='admin';
select * from sys_user_role where user_id ='1';
select * from sys_role where role_id ='1';

select * from sys_user su LEFT JOIN sys_user_role sur ON su.user_id = sur.user_id
LEFT JOIN sys_role sr ON sur.role_id = sr.role_id
where user_name='admin';

>>>>>>>>>
[SQL]select * from sys_user where user_name='admin';
受影响的行: 0
时间: 0.004s

[SQL]
select * from sys_user_role where user_id ='1';
受影响的行: 0
时间: 0.002s

[SQL]
select * from sys_role where role_id ='1';
受影响的行: 0
时间: 0.004s

[SQL]

select * from sys_user su LEFT JOIN sys_user_role sur ON su.user_id = sur.user_id
LEFT JOIN sys_role sr ON sur.role_id = sr.role_id
where user_name='admin';
受影响的行: 0
时间: 0.001s

查询包装单位

select unit_name from erp_unit where unit_id=1;
select unit_name from erp_unit where unit_id=2;
select unit_name from erp_unit where unit_id=3;

SELECT 
        eus.unit_name AS smallName, 
        eum.unit_name AS middleName, 
        eub.unit_name AS bigName 
        FROM 
        erp_goods_detail egd 
        LEFT JOIN erp_unit eus ON egd.small_unit_id = eus.unit_id 
        LEFT JOIN erp_unit eum ON egd.middle_unit_id = eum.unit_id 
        LEFT JOIN erp_unit eub ON egd.big_unit_id = eub.unit_id 
        WHERE 
        egd.goods_id = '1357597885043163138';

>>>>>>>>>>>>>>>>>>>>>
[SQL]select unit_name from erp_unit where unit_id=1;
受影响的行: 0
时间: 0.002s

[SQL]
select unit_name from erp_unit where unit_id=2;
受影响的行: 0
时间: 0.002s

[SQL]
select unit_name from erp_unit where unit_id=3;
受影响的行: 0
时间: 0.002s

[SQL]

SELECT 
        eus.unit_name AS smallName, 
        eum.unit_name AS middleName, 
        eub.unit_name AS bigName 
        FROM 
        erp_goods_detail egd 
        LEFT JOIN erp_unit eus ON egd.small_unit_id = eus.unit_id 
        LEFT JOIN erp_unit eum ON egd.middle_unit_id = eum.unit_id 
        LEFT JOIN erp_unit eub ON egd.big_unit_id = eub.unit_id 
        WHERE 
        egd.goods_id = '1357597885043163138';
受影响的行: 0
时间: 0.002s

看一下代码中的运行时间

@Service
public class AServiceImpl implements AService {

    @Autowired
    private UnitMapper unitMapper;

    @Override
    public void testA() {
        singleSearch();
        allSearch();
    }

    private void singleSearch() {
        Long start = System.currentTimeMillis();
        for (int i = 0; i < 10; i++) {
            unitMapper.selectNameByUnitId((long) 1);
            unitMapper.selectNameByUnitId((long) 2);
            unitMapper.selectNameByUnitId((long) 3);
        }
        Long end = System.currentTimeMillis();
        System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>" + (end - start));
    }

    private void allSearch() {
        Long start = System.currentTimeMillis();
        for (int i = 0; i < 10; i++) {
            unitMapper.searchAll();
        }
        Long end = System.currentTimeMillis();
        System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>" + (end - start));
    }
}

singleSearch方法的总耗时4533,allSearch方法的总耗时1378

在遍历中每个查询方法的用时对比
singleSearch(单位ms):[4、4、3、4、4、3、5、3、3、4、3、7、4、5、19、4、4、3、3、3、4、4、11、4、5、4、4、4、3、220]

allSearch(单位ms):[5、4、4、4、5、4、4、4、5、9]

还是上边的方法,我们将遍历次数调整到1000

@Service
public class AServiceImpl implements AService {

    @Autowired
    private UnitMapper unitMapper;

    @Override
    public Map<String, Long> testA() {
        Long single = singleSearch();
        Long all = allSearch();
        Map map = new HashMap();
        map.put("single", single);
        map.put("all", all);
        return map;
    }

    private Long singleSearch() {
        Long start = System.currentTimeMillis();
        for (int i = 0; i < 1000; i++) {
            unitMapper.selectNameByUnitId((long) 1);
            unitMapper.selectNameByUnitId((long) 2);
            unitMapper.selectNameByUnitId((long) 3);
        }
        Long end = System.currentTimeMillis();
        System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>" + (end - start));
        return end - start;
    }

    private Long allSearch() {
        Long start = System.currentTimeMillis();
        for (int i = 0; i < 1000; i++) {
            unitMapper.searchAll();
        }
        Long end = System.currentTimeMillis();
        System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>" + (end - start));
        return end - start;
    }
}

输出结果

{"code":200,"success":true,"data":{"single":362416,"all":7388},"msg":"操作成功"}

文章作者: LeePandar
版权声明: 本博客所有文章除特別声明外,均采用 CC BY 4.0 许可协议。转载请注明来源 LeePandar !
评论
 上一篇
SpringBoot动态定时任务 SpringBoot动态定时任务
其实SchedulingConfigurer实现方法很简单,只需要实现SchedulingConfigurer并重写configureTasks方法,在启动类必须加上@EnableScheduling注解即可。 @Configurati
2021-05-26
下一篇 
Oracle转Mysql Oracle转Mysql
1.将表中时间类型的字段更改类型,比如CREATE_TIME,UPDATE_TIMEALTER TABLE ZFTJ_HALF MODIFY CREATE_TIME TIMESTAMP WITH LOCAL TIME ZON
2020-11-17
  目录