为什么阿里巴巴规定超过3张表禁止JOIN?

dotNET全栈开发

共 2432字,需浏览 5分钟

 ·

2020-09-20 17:21







点击上方“dotNET全栈开发”,“设为星标

加“星标”,每天21.45,好文必达


来源:http://blog.itpub.net/30393770/viewspace-2650450

一、 问题提出

《阿里巴巴JAVA开发手册》里面写超过三张表禁止join,这是为什么?

二、问题分析

对这个结论,你是否有怀疑呢?也不知道是哪位先哲说的不要人云亦云,今天我设计sql,来验证这个结论。(实验没有从代码角度分析,目前达不到。可以把mysql当一个黑盒,使用角度来验证这个结论) 验证结论的时候,会有很多发现,各位往后看。

三、 实验环境

vmware10+centos7.4+mysql5.7.22
  • centos7内存4.5G,4核,50G硬盘。
  • mysql配置为2G,特别说明硬盘是SSD。

四、我概述下我的实验

有4张表,student学生表,teacher老师表,course课程表,sc中间关系表,记录了学生选修课程以及分数。具体sql脚本,看文章结尾,我附上。中间我自己写了造数据的脚本,也在结尾。
实验是为解决一个问题的:查询选修“tname553”老师所授课程的学生中,成绩最高的学生姓名及其成绩。
查询sql是:
我来分析一下这个语句:4张表等值join,还有一个子查询。算是比较简单的sql语句了(相比ERP动就10张表的哦,已经很简单了)。我 还会分解这个语句成3个简单的sql:
我来分析下:第一句,就是查询最高分,得到最高分590分。第二句就是查询出最高分的学生id,得到
第三句就是查询出学生名字和分数。这样这3个语句的就可以查询出来 成绩最高的学生姓名及其成绩 。
接下来我会分别造数据:1千万选课记录(一个学生选修2门课),造500万学生,100万老师(一个老师带5个学生,挺高端的吧),1000门课,。用上面查询语句查询。其中sc表我测试了下有索引和没有索引情况,具体见下表。
再接下来,我会造1亿选课记录(一个学生选修2门课),5000万学生,1000万老师,1000门课。然后分别执行上述语句。最后我会在oracle数据库上执行上述语句。

五、下面两张表是测试结果

六、仔细看上表,可以发现:

1、步骤3.1没有在连接键上加索引,查询很慢,说明:“多表关联查询时,保证被关联的字段需要有索引”;
2、步骤6.1,6.2,6.3,换成简单sql,在数据量1亿以上, 查询时间还能勉强接受。此时说明mysql查询有些吃力了,但是仍然嫩查询出来。
3、步骤5.1,mysql查询不出来,4表连接,对我本机mysql来说,1.5亿数据超过极限了(我调优过这个SQL,执行计划和索引都走了,没有问题,show profile显示在sending data.这个问题另外文章详谈。)
4、对比1.1 和5.1 步骤sql查询,4表连接,对我本机mysql来说 ,1.5千万数据查询很流利,是一个mysql数据量流利分水岭。(这个只是现象,不太准确,需要同时计算表的容量)。
5、步骤5.1对比6.1,6.2,6.3,多表join对mysql来说,处理有些吃力。
6、超过三张表禁止join,这个规则是针对mysql来说的。后续会看到我用同样机器,同样数据量,同样内存,可以完美计算 1.5亿数据量join。针对这样一个规则,对开发来说 ,需要把一些逻辑放到应用层去查询。
总结: 这个规则 超过三张表禁止join ,由于数据量太大的时候,mysql根本查询不出来,导致阿里出了这样一个规定。(其实如果表数据量少,10张表也不成问题,你自己可以试试)而我们公司支付系统朝着大规模高并发目标设计的,所以,遵循这个规定。
在业务层面来讲,写简单sql,把更多逻辑放到应用层,我的需求我会更了解,在应用层实现特定的join也容易得多。

七、让我们来看看oracle数据库的优秀表现:

看步骤7.1,就是没有索引,join表很多的情况下,oracle仍然26秒查询出结果来。所以我会说mysql的join很弱。那么问题来了,为什么现在使用很多人使用mysql呢?这是另外一个问题,我会另外说下我的思考。
看完本篇文章,另外我还附加赠送,所谓搂草打兔子。就是快速造数据。你可以自己先写脚本造数据,看看我是怎么造数据的,就知道我的技巧了。

八、附上部分截图

九、附上sql语句和造数据脚本

use stu;
drop table if exists student;
create table student 
  (  s_id int(11) not null auto_increment ,
     sno    int(11), 
     sname varchar(50), 
     sage  int(11), 
     ssex  varchar(8) ,
     father_id int(11),
      mather_id int(11),
      note varchar(500),
     primary key (s_id),
   unique key uk_sno (sno)
  ) engine
=innodb default charset=utf8mb4;
truncate table student;
  delimiter $$
drop function if exists   insert_student_data $$
create function insert_student_data()
 returns  int deterministic
    begin
    declare  i int
;
      set i=1;
      while  i<50000000 do 
      insert into student  values(i ,i, concat( name ,i),i,case when floor(rand()*10)%2
=0 then  f  else  m  end,floor(rand()*100000),floor(rand()*1000000),concat( note ,i) );
      set i=i+1;
      end while;
      return 1;
    end$$
delimiter ;    
select  insert_student_data();
select count(*from student;
use stu;
create table course 
  ( 
     c_id int(11
) not null auto_increment ,
     cname varchar(50)
     note varchar(500), primary key (c_id)
  )  engine
=innodb default charset=utf8mb4;
truncate table course;
  delimiter $$
drop function if exists   insert_course_data $$
create function insert_course_data()
 returns  int deterministic
    begin
    declare  i int
;
      set i=1;
      while  i<=1000 do 
      insert into course  values(i , concat( course ,i),floor(rand()*1000),concat( note ,i) )
;
      set i=i+1;
      end while;
      return 1;
    end$$
delimiter ;    
select  insert_course_data();
select count(*from course;
use stu;
drop table if exists sc;
create table sc 
  ( 
     s_id    int(11
), 
     c_id    int(11), 
     t_id    int(11),
     score int(11
  )  engine
=innodb default charset=utf8mb4;
truncate table sc;
  delimiter $$
drop function if exists   insert_sc_data $$
create function insert_sc_data()
 returns  int deterministic
    begin
    declare  i int
;
      set i=1;
      while  i<=50000000 do 
      insert into sc  values( i,floor(rand()*1000),floor(rand()*10000000),floor(rand()*750)) 
;
      set i=i+1;
      end while;
      return 1;
    end$$
delimiter ;    
select  insert_sc_data();
commit;
select  insert_sc_data();
commit;
create index idx_s_id  on sc(s_id)   
create index idx_t_id  on sc(t_id)   
create index idx_c_id  on sc(c_id)   
select count(*from sc;
use stu;
drop table if exists teacher;
create table teacher 
  ( 
    t_id  int(11
) not null auto_increment ,
     tname varchar(50) ,
     note varchar(500),primary key (t_id)
  )  engine
=innodb default charset=utf8mb4;

  truncate table teacher;
  delimiter $$
drop function if exists   insert_teacher_data $$
create function insert_teacher_data()
 returns  int deterministic
    begin
    declare  i int
;
      set i=1;
      while  i<=10000000 do 
      insert into teacher  values(i , concat( tname ,i),concat( note ,i) )
;
      set i=i+1;
      end while;
      return 1;
    end$$
delimiter ;    
select  insert_teacher_data();
commit;
select count(*from teacher;

这个是oracle的测试和造数据脚本

create tablespace scott_data  datafile   /home/oracle/oracle_space/sitpay1/scott_data.dbf   size 1024autoextend on
create tablespace scott_index   datafile   /home/oracle/oracle_space/sitpay1/scott_index.dbf   size 64m  autoextend on
create temporary tablespace scott_temp  tempfile   /home/oracle/oracle_space/sitpay1/scott_temp.dbf   size 64autoextend on
drop user  scott cascade;
create user  scott  identified by  tiger  default tablespace scott_data  temporary tablespace scott_temp  ;
grant resource,connect,dba to  scott;
drop table student;
create table student  
  (  s_id number(11) ,
     sno    number(11) , 
     sname varchar2(50), 
     sage  number(11), 
     ssex  varchar2(8) ,
     father_id number(11),
      mather_id number(11),
      note varchar2(500)
  ) nologging;
truncate table student;
create or replace procedure insert_student_data
 is 
   q number(11);
    begin 
     q:=0;
      for i in  1..50 loop 
      insert /*+append*/ into student   select rownum+q as s_id,rownum+q  as sno, concat( sutdent ,rownum+q ) as sname,floor(dbms_random.value(1,100)) as sage, f  as ssex,rownum+q  as father_id,rownum+q  as mather_id,concat( note ,rownum+q ) as note from dual connect by level<=1000000;
      q:=q+1000000;
      commit;
      end loop
end insert_student_data;
/
call insert_student_data();
alter table student  add constraint  pk_student primary key (s_id);
commit;    
select count(*) from student;
create table course 
  ( 
     c_id number(11) primary key,
     cname varchar2(50),
     note varchar2(500
  )  ;
truncate table course;
 create or replace procedure insert_course_data
 is 
   q number(11);
    begin 

      for i in  1..1000 loop 
      insert /*+append*/ into course  values(i , concat( name ,i),concat( note ,i) );      
      end loop
end insert_course_data;
/
call insert_course_data();
commit;    
select count(*) from course;
create table sc 
  ( 
     s_id    number(11), 
     c_id    number(11), 
     t_id    number(11),
     score number(11
  ) nologging;
truncate table sc;
 create or replace procedure insert_sc_data
 is 
   q number(11);
    begin 
     q:=0;
      for i in  1..50 loop 
      insert /*+append*/ into sc   select rownum+q as s_id, floor(dbms_random.value(0,1000))  as c_id,floor(dbms_random.value(0,10000000)) t_id,floor(dbms_random.value(0,750)) as score from dual connect by level<=1000000;
      q:=q+1000000;
      commit;
      end loop
end insert_sc_data;
/
call insert_sc_data();
create index idx_s_id  on sc(s_id)   ; 
create index idx_t_id  on sc(t_id)   ; 
create index idx_c_id  on sc(c_id)   ; 
select count(*) from sc;
create table teacher 
  ( 
    t_id  number(11) ,
     tname varchar2(50) ,
     note varchar2(500)
  )nologging ;
    truncate table teacher;
create or replace procedure insert_teacher_data
 is 
   q number(11);
    begin 
     q:=0;
      for i in  1..10 loop 
      insert /*+append*/ into teacher   select rownum+q as t_id, concat( teacher ,rownum+q ) as tname,concat( note ,rownum+q ) as note from dual connect by level<=1000000;
      q:=q+1000000;
      commit;
      end loop
end insert_teacher_data;
/
call insert_teacher_data();
alter table teacher  add constraint  pk_teacher primary key (t_id);
select count(*) from teacher;


---END--





























































































































推荐阅读

神器 开源神器:你的微信头像可以卡通化了

开源 如何关闭微信朋友圈广告,这四步就完了!

开源 可以查看微信访客记录!这款神器你知道吗?

收藏Chrome 11个骚操作让你为所欲为

原创细恐至微,那些与闰年有关的bug

学习程序员:我终于知道.NET Core分层框架设计








微信后台回复“core”,获取全网最强.NET Core学习资料精选
回复“实战”,获取20套实战成品项目源码

回复“进群”,可加入dotnet core开发者交流群



.NET Core已经崛起

长按关注,刷新认知

dotNet全栈开发

浏览 12
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报
评论
图片
表情
推荐
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报