優質文章，及時送達/<font>/<font>

作者| /<font>e71hao/<font>/<font>

來源| /<font>blog.itpub.net/30393770/viewspace-2650450/<font>/<font>

上篇| /<font>Nginx從入門到實戰/<font>/<font>

一，問題提出/<font>/<font>

《阿里巴巴JAVA開發手冊》裡面寫超過三張表禁止join，這是為什麼？/<font>/<font>

二，問題分析/<font>/<font>

對這個物體，你是否有懷疑呢？也不知道是哪位先哲說的不要人云亦云，今天我設計sql，來驗證這個物體。（實驗沒有從代碼角度分析，目前達不到。可以把mysql當一個黑盒，使用角度來驗證這個指標）驗證驗證的時候，會有很多發現，各位往後看。/<font>/<font>

三，實驗環境/<font>/<font>

vmware10 + centos7.4 + mysql5.7.22/<font>/<font>

• centos7內存4.5G，4核，50G硬盤。/<font>/<font>

• mysql配置為2G，特別說明硬盤是SSD。/<font>/<font>

四，我概述下我的實驗/<font>/<font>

有4張表，student學生表，teacher老師表，course課程表，sc中間關係表，記錄了學生選修課程以及分數。具體sql腳本，看文章結尾，我附上。中間我自己寫了造數據的腳本，也在結尾。/<font>/<font>

實驗是為解決一個問題的：查詢選修“ tname553”老師所授課程的學生中，成績最高的學生姓名及其成績。/<font>/<font>

查詢sql是：/<font>/<font>

我來分析一下這個語句：4張表等值join，還有一個子查詢。算是比較簡單的sql語句了（引用ERP動就10張表的哦，已經很簡單了）。我即將分解這個語句成3個簡單的sql：/<font>/<font>

我來分析下：第一句，就是查詢最高分，得到最高分590分。第二句就是查詢出最高分的學生id，得到/<font>/<font>

這樣的3個語句的就可以查詢出來成績最高的學生姓名及其成績。/<font>/<font>

接下來我會分別造數據：1千萬選課記錄（一個學生選修2門課），造500萬學生，100萬老師（一個老師帶5個學生，挺高端的吧），1000門課，。。上面的查詢語句查詢。其中sc表我測試了下有索引和沒有索引情況，具體見如何。/<font>/<font>

再接下來，我會造1億選課記錄（一個學生選修2門課），5000萬學生，1000萬老師，1000門課。然後分別執行上述語句。最後我會在oracle數據庫上執行上述語句。/<font>/<font>

五，下面兩張表是測試結果/<font>/<font>

六，仔細看上表，可以發現：/<font>/<font>

1，步驟3.1沒有在連接鍵上加索引，查詢很慢，說明：“多表關聯查詢時，保證被關聯的變量需要有索引”；/<font>/<font>

2，步驟6.1、6.2、6.3，換成簡單sql，在數據量10億以上，查詢時間還能勉強接受。此時說明mysql查詢有些吃力了，但是仍然嫩查詢出來。/<font>/<font>

3，步驟5.1，mysql查詢不出來，4表連接，對我本機mysql而言，1.5億數據超過極限了（我調優過這個SQL，執行計劃和索引都走了，沒有問題，顯示配置文件顯示在發送數據。這個問題另外文章詳談。）/<font>/<font>

4，對比1.1和5.1步驟sql查詢，4表連接，對我本機mysql而言，1.5千萬數據查詢很流利，是一個mysql數據量流利分水嶺。表的容量）。/<font>/<font>

5，步驟5.1對比6.1,6.2,6.3，多表join對mysql來說，處理有些吃力。/<font>/<font>

6，超過三張表禁止聯接，這個規則是針對mysql的。後續會看到我用同樣的機器，同樣數據量，同樣內存，可以完美計算1.5億數據量join。針對這樣一個規則，對開發來說，需要把一些邏輯放到應用層去查詢。/<font>/<font>

在業務層面上，寫簡單sql，把更多邏輯放到應用層，我的需求我會更瞭解，在應用層實現特定的加入也容易整合。/<font>/<font>

溫馨提示：歡迎/<font>/<font>關注/<font>/<font>微信/<font>/<font>公眾號/<font>/<font>：Java Java，獲取更多技術博文推送。/<font>/<font>

七，讓我們來看看oracle數據庫的優秀表現：/<font>/<font>

看步驟7.1，就是沒有索引，join表很多的情況下，oracle仍然26秒查詢出結果來。所以我會說mysql的join很弱。那麼問題來了，為什麼現在使用很多人使用mysql呢？這是另外一個問題，我會另外說下我的思考。/<font>/<font>

看完本篇文章，另外我還附加附件，所謂摟草打兔子。就是快速造數據。你可以自己先寫腳本造數據，看看我是怎麼造數據的，就知道我的技巧了。/<font>/<font>

八，附上部分截圖/<font>/<font>

九，附上sql語句和造數據腳本/<font>/<font>

<code>use stu;
drop table if exists student;
create table student
( s_id int(11) not 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 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 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;/<code>

這個是oracle的測試和造數據腳本/<font>/<font>

<code>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(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;/<code>

閱讀更多 程序猿久一 的文章

關鍵字: Sage 阿里 數據庫