天齐锂业-必威体育官方网站入口-必威体育官方首页-首页

天齐锂业-必威体育官方网站入口-必威体育官方首页-首页

(给视学算法加星标,进步Java技术)

转自:ITPUB博客,作者:e71hao

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是:

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

我来剖析一下这个句子:4张表等值join,还有一个子查询。算是比较简略的sql句子了(比较ERP动就标签910张表的哦,现已很简略了)。我 还会分化这个句子成3个简略的sql:

select max(score) from SC ,Teacher where sc.t_id=teacher.t_Id and Teacher.Tname='tname553';
select sc.t_id,sc.s_id,score from SC ,Teacher
where sc.t_id=teacher.t_Id
and score=590
and Teacher.Tname='tname553';
select Stude天齐锂业-必威体育官方网站进口-必威体育官方主页-主页nt.Sname,course.cname,score
from Student,SC ,course
where Student.s_id=SC.s_id and sc.s_id in (20769800,48525000,26280200) and course.c_id = sc.c_id;

我来剖析下:榜首句,便是查询最高分,得到最高分590分。第二句便是查询出最高分的学生id,得到

20769800,48525000,26280200

第三句便是查询出学生名字和分数。这样这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,my标签14sql查询不出来,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标签19对mysql来说,处理有些费劲。
  6. 超越三张表制止join,这个规矩是针对mysql来说的。后续会看到我用相同机器,相同数据量,相同内存,能够完美核算 1.5亿数据量join。针对这样一个规矩,对开发来说 ,需求把一些逻辑放到应用层去查询。

总结:这个规矩 超越三张表制止join ,因为数据量太大的时分,mysql底子查询不出来,导致阿里出了这样一个规则。(其实假如表数据量少,10张表也不成问题,你自己能够试试)而咱们公司付出体系朝着大标签6规划高并发方针规划的,所以,遵从这个规则。在事务层面来讲,写简略sql,把更多逻辑放到应用层,我的需求我会更了解,标签9在应用层标签6完成特定的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=innod标签23b defa标签11ult charset=utf8mb4;
t天齐锂业-必威体育官方网站进口-必威体育官方主页-主页runcate table course;
delimiter $$
drop function if exists 标签25insert_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
in标签23sert 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 1024m autoextend 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 64m autoextend 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(标签2350),
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 fat天齐锂业-必威体育官方网站进口-必威体育官方主页-主页her_id,rownum+q as mather_id,concat('note',rownum+q ) as note from dual connect by level<=1000000;
q:=q+1000000;
commit;
标签4end loop;
end insert_student_data;
/
call insert_student_data();
alter tabl标签27e 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 lo标签16op
insert /*+append*/ into cou标签1rse 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;标签20
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;