其實我想說：mysql的join真的很low

一、 問題提出： 《阿里巴巴JAVA開發手冊》裡面寫超過三張表禁止join，這是為什麼？

 

二、問題分析：對任何結論，我首先持懷疑態度的，也不知道是哪位先哲說的不要人云亦云。要怎麼驗證這個問題呢？今天我就設計了一個實驗來驗證，為什麼 超過三張表禁止join。各位可以看看我這個實驗是怎麼做的。

三、 實驗環境：vmware10+centos7.4+mysql5.7.22 ，centos7記憶體4.5G，4核，50G硬碟。 mysql配置為2G，特別說明硬碟是SSD。

四、首先我選取了5張表：學生表_課程表_成績表_授課表—_教師表

表結構關係，資料量如下：

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<5000000 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()*100000),concat('note',i) );
      set i=i+1;
      end while;
      return 1;
    end$$
delimiter ;    
select  insert_student_data();
select count(*) from student;

create table course 
  ( 
     c_id int(11) not null auto_increment ,
     cname varchar(50), 
     t_id   int(11) ,
     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()*1000000),concat('note',i) );
      set i=i+1;
      end while;
      return 1;
    end$$
delimiter ;    
select  insert_course_data();
select count(*) from course;

drop table if exists sc;
create table sc 
  ( 
     s_id    int(11), 
     cname    varchar(50), 
     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<=5000000 do 
      insert into sc  values( i,concat('course',floor(rand()*1000)),floor(rand()*100)) ;
      set i=i+1;
      end while;
      return 1;
    end$$
delimiter ;    
select  insert_sc_data();
select count(*) from sc;

create table tc 
  ( 
     t_id    int(11), 
     cname    varchar(50)
  )  engine=innodb default charset=utf8mb4;
truncate table tc;
  delimiter $$
drop function if exists   insert_tc_data $$
create function insert_tc_data()
 returns  int deterministic
    begin
    declare  i int;
      set i=1;
      while  i<=1000000 do 
      insert into tc  values( i,concat('course',floor(rand()*1000))) ;
      set i=i+1;
      end while;
      return 1;
    end$$
delimiter ;    
select  insert_tc_data();
select count(*) from tc;

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<=1000000 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();
select count(*) from teacher;

這5張表，容易不容易，簡單不簡單？這些表的關係如你們所料，就是關係圖。同時每個表插入資料。如下表：

表	數量
student	500萬
teacher	100萬
course	1000
sc	1000萬
tc	200萬

一個學生選修了2門課程，每個老師教2門課程。總共有1000門課程。student有2個索引，teacher有2個索引，sc和tc暫時沒有索引，等下，隨著我的實驗深入，我會擇機加入索引。

表的數量：

五、實驗過程如下：

五、解決一個問題：查詢選修“tname553”老師所授課程的學生中，成績最高的學生姓名及其成績

這個sql怎麼寫呢？思考1分鐘。

給出答案:

    select Student.Sname,score 
    from Student,SC,tc,Course ,Teacher 
    where Student.s_id=SC.s_id 
    and SC.cname=Course.cname 
    and sc.cname=tc.cname 
    and   tc.t_id=teacher.t_id  
    and Teacher.Tname='tname553' 
    and SC.score=(select max(score)from SC where cname=Course.cname );

然後我放到mysql去執行，執行了很長時間，超過5分鐘，沒有得到結果。

六、我換個方式去執行：分步驟，一個一個步驟找出我要的資料來。

 select tc.t_id,tc.cname
    from tc,Course ,Teacher 
    where tc.cname=course.cname 
    and   tc.t_id=teacher.t_id   
    and Teacher.Tname='tname553'  ；

耗時：2min53sec

  select Student.Sname,score from   Student,SC  where Student.s_id=SC.s_id
  and cname in ('course735','course502') 
  and score=(select max(score) from sc where  cname in ('course735','course502'));

204 rows in set (7.72 sec)

分成2個sql，耗時3min。

七、在換一種方式，單個sql執行：

select t_id from teacher where tname='tname553';
select * from tc where tc.t_id=553;
select * from course  where cname in ('course735','course502');
select max(score) from sc where  cname in ('course735','course502');
select Student.Sname,score from   Student,SC  where Student.s_id=SC.s_id
and cname in ('course735','course502') and score=99;

總耗時：15秒

八、sc表和tc表加上索引會怎麼樣呢？

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30393770/viewspace-2650450/，如需轉載，請註明出處，否則將追究法律責任。