1 Oracle：
--创建表空间： create tablespace mydb datafile 'e:\oracle\mydb\mydb.ora' size 100M autoextend on; --创建用户并授权： create user yuanlin identified by 123 default tablespace mydb quota 10m on users; --将对表空间的所有操作功能: grant connect,resource,dba,sysdba to yuanlin; --用新用户连接： conn yuanlin 123 --表two,four,six,eight分别插入2、4、6、8条记录，为了多表连接得到如6000W这样数据的返回记录: CREATE TABLE two( id NUMBER, uname VARCHAR(10), age NUMBER); INSERT INTO two(id,uname,age) VALUES(1,'twe2',2); INSERT INTO two(id,uname,age) VALUES(2,'twe2',2); CREATE TABLE four( id NUMBER PRIMARY KEY, uname VARCHAR(10), age NUMBER); INSERT INTO four(id,uname,age) VALUES(1,'four4',4); INSERT INTO four(id,uname,age) VALUES(2,'four4',4); INSERT INTO four(id,uname,age) VALUES(3,'four4',4); INSERT INTO four(id,uname,age) VALUES(4,'four4',4); CREATE TABLE six( id NUMBER PRIMARY KEY, uname VARCHAR(10), age NUMBER); INSERT INTO six(id,uname,age) VALUES(1,'six6',6); INSERT INTO six(id,uname,age) VALUES(2,'six6',6); INSERT INTO six(id,uname,age) VALUES(3,'six6',6); INSERT INTO six(id,uname,age) VALUES(4,'six6',6); INSERT INTO six(id,uname,age) VALUES(5,'six6',6); INSERT INTO six(id,uname,age) VALUES(6,'six6',6); CREATE TABLE eight( id NUMBER PRIMARY KEY, uname VARCHAR(10), age NUMBER); INSERT INTO eight(id,uname,age) VALUES(1,'eight8',8); INSERT INTO eight(id,uname,age) VALUES(2,'eight8',8); INSERT INTO eight(id,uname,age) VALUES(3,'eight8',8); INSERT INTO eight(id,uname,age) VALUES(4,'eight8',8); INSERT INTO eight(id,uname,age) VALUES(5,'eight8',8); INSERT INTO eight(id,uname,age) VALUES(6,'eight8',8); INSERT INTO eight(id,uname,age) VALUES(7,'eight8',8); INSERT INTO eight(id,uname,age) VALUES(8,'eight8',8); ---------表test1,触发器trig1,序列test1_seq搭配实现表ID的自动增长------------ DROP TABLE test1; DROP SEQUENCE test1_seq; DROP TRIGGER trig1; CREATE TABLE test1( id NUMBER PRIMARY KEY, uname VARCHAR(10), age NUMBER); CREATE SEQUENCE test1_seq INCREMENT BY 1 -- 每次加几个 START WITH 1 -- 从1开始计数 NOMAXVALUE -- 不设置最大值 NOCYCLE -- 一直累加，不循环 NOCACHE -- 不建缓冲区; CREATE TRIGGER trig1 BEFORE INSERT ON test1 FOR EACH ROW WHEN (new.id IS NULL) BEGIN SELECT test1_seq.nextval INTO:new.id FROM dual; END; / ---------表test2,触发器trig2,序列test2_seq搭配实现表ID的自动增长------------ DROP TABLE test2; DROP SEQUENCE test2_seq; DROP TRIGGER trig2; CREATE TABLE test2( id NUMBER PRIMARY KEY, uname VARCHAR(10), age NUMBER); CREATE SEQUENCE test2_seq INCREMENT BY 1 -- 每次加几个 START WITH 1 -- 从1开始计数 NOMAXVALUE -- 不设置最大值 NOCYCLE -- 一直累加，不循环 NOCACHE -- 不建缓冲区; CREATE TRIGGER trig2 BEFORE INSERT ON test2 FOR EACH ROW WHEN (new.id IS NULL) BEGIN SELECT test2_seq.nextval INTO:new.id FROM dual; END; / ---------表test3,触发器trig3,序列test3_seq搭配实现表ID的自动增长------------ DROP TABLE test3; DROP SEQUENCE test3_seq; DROP TRIGGER trig3; CREATE TABLE test3( id NUMBER PRIMARY KEY, uname VARCHAR(10), age NUMBER); CREATE SEQUENCE test3_seq INCREMENT BY 1 -- 每次加几个 START WITH 1 -- 从1开始计数 NOMAXVALUE -- 不设置最大值 NOCYCLE -- 一直累加，不循环 NOCACHE -- 不建缓冲区; CREATE TRIGGER trig3 BEFORE INSERT ON test3 FOR EACH ROW WHEN (new.id IS NULL) BEGIN SELECT test3_seq.nextval INTO:new.id FROM dual; END; / ---------表test4,触发器trig4,序列test4_seq搭配实现表ID的自动增长------------ DROP TABLE test4; DROP SEQUENCE test4_seq; DROP TRIGGER trig4; CREATE TABLE test4( id NUMBER PRIMARY KEY, uname VARCHAR(10), age NUMBER); CREATE SEQUENCE test4_seq INCREMENT BY 1 -- 每次加几个 START WITH 1 -- 从1开始计数 NOMAXVALUE -- 不设置最大值 NOCYCLE -- 一直累加，不循环 NOCACHE -- 不建缓冲区; CREATE TRIGGER trig4 BEFORE INSERT ON test4 FOR EACH ROW WHEN (new.id IS NULL) BEGIN SELECT test4_seq.nextval INTO:new.id FROM dual; END; / ---------表test5,触发器trig5,序列test5_seq搭配实现表ID的自动增长------------ DROP TABLE test5; DROP SEQUENCE test5_seq; DROP TRIGGER trig5; CREATE TABLE test5( id NUMBER PRIMARY KEY, uname VARCHAR(10), age NUMBER); CREATE SEQUENCE test5_seq INCREMENT BY 1 -- 每次加几个 START WITH 1 -- 从1开始计数 NOMAXVALUE -- 不设置最大值 NOCYCLE -- 一直累加，不循环 NOCACHE -- 不建缓冲区; CREATE TRIGGER trig5 BEFORE INSERT ON test5 FOR EACH ROW WHEN (new.id IS NULL) BEGIN SELECT test5_seq.nextval INTO:new.id FROM dual; END; / ---------表test6,触发器trig6,序列test6_seq搭配实现表ID的自动增长------------ DROP TABLE test6; DROP SEQUENCE test6_seq; DROP TRIGGER trig6; CREATE TABLE test6( id NUMBER PRIMARY KEY, uname VARCHAR(10), age NUMBER); CREATE SEQUENCE test6_seq INCREMENT BY 1 -- 每次加几个 START WITH 1 -- 从1开始计数 NOMAXVALUE -- 不设置最大值 NOCYCLE -- 一直累加，不循环 NOCACHE -- 不建缓冲区; CREATE TRIGGER trig6 BEFORE INSERT ON test6 FOR EACH ROW WHEN (new.id IS NULL) BEGIN SELECT test6_seq.nextval INTO:new.id FROM dual; END; / ---------表test7,触发器trig7,序列test7_seq搭配实现表ID的自动增长------------ DROP TABLE test7; DROP SEQUENCE test7_seq; DROP TRIGGER trig7; CREATE TABLE test7( id NUMBER PRIMARY KEY, uname VARCHAR(10), age NUMBER); CREATE SEQUENCE test7_seq INCREMENT BY 1 -- 每次加几个 START WITH 1 -- 从1开始计数 NOMAXVALUE -- 不设置最大值 NOCYCLE -- 一直累加，不循环 NOCACHE -- 不建缓冲区; CREATE TRIGGER trig7 BEFORE INSERT ON test7 FOR EACH ROW WHEN (new.id IS NULL) BEGIN SELECT test7_seq.nextval INTO:new.id FROM dual; END; / -------------------------------创建过程插入数据------------------------------------ ---------过程insert_pro1,插入10条数据----------- CREATE OR REPLACE PROCEDURE insert_pro1(NUMS NUMBER) AS i NUMBER; BEGIN FOR i IN 1 .. NUMS loop INSERT INTO test1(uname,age) VALUES('zhangsan',11); END LOOP; END; / call insert_pro1(10); ---------创建过程2,插入100条数据----------- CREATE OR REPLACE PROCEDURE insert_pro2(NUMS NUMBER) AS i NUMBER; BEGIN FOR i IN 1 .. NUMS loop INSERT INTO test2(uname,age) VALUES('lisi',22); END LOOP; END; / call insert_pro2(100); ---------创建过程3,插入1000条数据----------- CREATE OR REPLACE PROCEDURE insert_pro3(NUMS NUMBER) AS i NUMBER; BEGIN FOR i IN 1 .. NUMS loop INSERT INTO test3(uname,age) VALUES('wangwu',33); END LOOP; END; / call insert_pro3(1000); ---------创建过程4,插入10000条数据----------- CREATE OR REPLACE PROCEDURE insert_pro4(NUMS NUMBER) AS i NUMBER; BEGIN FOR i IN 1 .. NUMS loop INSERT INTO test4(uname,age) VALUES('zhaoliu',44); END LOOP; END; / call insert_pro4(10000); ----10W 16.86s---- SELECT count(*) FROM test1 t1 join test4 t4 on 1=1; INSERT INTO test5(uname,age) SELECT t4.uname , t1.age FROM test1 t1 join test4 t4 on 1=1; COMMIT; ----100w 02m 53.63s---- SELECT count(*) FROM test2 t2 join test4 t4 on 1=1; INSERT INTO test6(uname,age) SELECT t4.uname , t2.age FROM test2 t2 join test4 t4 on 1=1; COMMIT; ----400w 13m 19.41s---- SELECT count(*) FROM test2 t2 join test4 t4 on 1=1 join four on 1=1; INSERT INTO test7(uname,age) SELECT t4.uname , t2.age FROM test2 t2 join test4 t4 on 1=1 join four on 1=1 where id=3244; commit;
2 Mysql:
查询测试记录：
查询时间与返回记录数、字段数、连接表的数目关系最大，一般为正比关系。
说明：查询结果写入硬盘，并未直接输出,因为硬盘关系，无法做数据更大的查询测试。
1，没有加入条件的测试
|
记录数/万 |
表数量 |
字段数 |
耗时/秒 |
|
1 |
2 |
2 |
0.02 |
|
10 |
2 |
2 |
0.03 |
|
100 |
2 |
2 |
0.23 |
|
1000 |
2 |
2 |
2.14 |
|
2000 |
3 |
2 |
4.37 |
|
4000 |
3 |
2 |
8.61 |
|
6000 |
3 |
2 |
12.82 |
|
10000 |
2 |
2 |
21.34 |
|
20000 |
3 |
2 |
42.93 |
|
40000 |
3 |
2 |
1 分27.81 |
2，加入条件的测试
|
记录数/万 |
表数量 |
字段数 |
耗时/秒 |
|
1 |
2 |
2 |
0.02 |
|
19 |
2 |
2 |
0.11 |
|
199 |
2 |
2 |
1.22 |
|
599 |
2 |
2 |
2.89 |
|
800 |
2 |
2 |
6.21 |
|
1600 |
2 |
2 |
12.96 |
|
2400 |
2 |
2 |
20.31 |
总结：数量级上亿级都能正常查询。
导入测试记录:
说明：从硬盘导入，并非直接写Insert语句，导入前都先把表清空。
|
记录数/万 |
表数量 |
字段数 |
耗时/秒 |
|
1 |
1 |
2 |
0.25 |
|
10 |
1 |
2 |
0.87 |
|
100 |
1 |
2 |
6.85 |
|
400 |
1 |
2 |
37.56 |
|
600 |
1 |
2 |
58.85 |
|
1000 |
1 |
2 |
1分 47.53 |
|
2000 |
1 |
2 |
4分 6.12 |
|
4000 |
1 |
2 |
8分 3.79 |
|
10000 |
1 |
2 |
28分 52.54 |
总结：1，当导入数量上千万时，服务就出现短暂假死现象。
2，如果是编写过程插入数据则很查，数目上万就很慢了。
修改测试记录：
|
记录数/万 |
表数量 |
字段数 |
耗时/秒 |
|
1 |
1 |
1 |
0.42 |
|
10 |
1 |
1 |
2.64 |
|
100 |
1 |
1 |
28.39 |
|
400 |
1 |
1 |
1分 49.21 |
|
1000 |
1 |
1 |
5分 39.32 |
CREATE DATABASE mydb; USE mydb; --表two,four,six分别插入2、4、6条记录，为了多表连接得到如6000W这样数据的返回记录； CREATE TABLE two( uid INT PRIMARY KEY AUTO_INCREMENT, uname VARCHAR(10), age INT); INSERT two(uname,age) VALUES('twe2',2); INSERT two(uname,age) VALUES('twe2',2); CREATE TABLE four( uid INT PRIMARY KEY AUTO_INCREMENT, uname VARCHAR(10), age INT); INSERT four(uname,age) VALUES('four4',4); INSERT four(uname,age) VALUES('four4',4); INSERT four(uname,age) VALUES('four4',4); INSERT four(uname,age) VALUES('four4',4); CREATE TABLE six( uid INT PRIMARY KEY AUTO_INCREMENT, uname VARCHAR(10), age INT); INSERT six(uname,age) VALUES('six6',6); INSERT six(uname,age) VALUES('six6',6); INSERT six(uname,age) VALUES('six6',6); INSERT six(uname,age) VALUES('six6',6); INSERT six(uname,age) VALUES('six6',6); INSERT six(uname,age) VALUES('six6',6); ---------创建过程1和表test1，并向其中插入10条数据，插入数据用时0.18------------ DROP TABLE test1; DROP PROCEDURE insert_pro1; CREATE TABLE test1( uid INT PRIMARY KEY AUTO_INCREMENT, uname VARCHAR(10), age INT); DELIMITER // CREATE PROCEDURE insert_pro1(IN count INT) BEGIN DECLARE i INT; SET i=0; WHILE i<count DO INSERT test1(uname,age) VALUES('zhangsan',11); SET i=i+1; END WHILE; END; // DELIMITER ; SET @count=10; CALL insert_pro1(@count); ---------创建过程2和表test2，并插入100条数据，插入数据用时1.31------------ DROP TABLE test2; DROP PROCEDURE insert_pro2; CREATE TABLE test2( uid INT PRIMARY KEY AUTO_INCREMENT, uname VARCHAR(10), age INT); DELIMITER // CREATE PROCEDURE insert_pro2(IN count INT) BEGIN DECLARE i INT; SET i=0; WHILE i<count DO INSERT test2(uname,age) VALUES('lisi',22); SET i=i+1; END WHILE; END; // DELIMITER ; SET @count=100; CALL insert_pro2(@count); ---------创建过程3和表test3，并插入1000条数据，插入数据用时13.06------ DROP TABLE test3; DROP PROCEDURE insert_pro3; CREATE TABLE test3( uid INT PRIMARY KEY AUTO_INCREMENT, uname VARCHAR(10), age INT); DELIMITER // CREATE PROCEDURE insert_pro3(IN count INT) BEGIN DECLARE i INT; SET i=0; WHILE i<count DO INSERT test3(uname,age) VALUES('wangwu',33); SET i=i+1; END WHILE; END; // DELIMITER ; SET @count=1000; CALL insert_pro3(@count); ---------创建过程4和表test4，插入10000条数据，插入数据用时3m 45.08s-------- DROP TABLE test4; DROP PROCEDURE insert_pro4; CREATE TABLE test4( uid INT PRIMARY KEY AUTO_INCREMENT, uname VARCHAR(10), age INT); DELIMITER // CREATE PROCEDURE insert_pro4(IN count INT) BEGIN DECLARE i INT; SET i=0; WHILE i<count DO INSERT test4(uname,age) VALUES('zhaoliu',44); SET i=i+1; END WHILE; END; // DELIMITER ; SET @count=10000; CALL insert_pro4(@count); 1， 查询测试源代码： SELECT age,uname FROM test4 INTO OUTFILE "e:/mydb/t_1w.txt"; --0.02 SELECT t1.age,t4.uname FROM test1 t1 join test4 t4 INTO OUTFILE "e:/mydb/t_10w.txt";
--0.03 SELECT t2.age, t4.uname FROM test2 t2 join test4 t4 INTO OUTFILE "e:/mydb/t_100w.txt"; --0.23 SELECT t3.age,t4.uname FROM test3 t3 join test4 t4 INTO OUTFILE "e:/mydb/t_1000w.txt"; --2.14 SELECT t3.age,t4.uname FROM test3 t3 join test4 t4 join two INTO OUTFILE "e:/mydb/t_2000w.txt"; --4.37 SELECT t3.age,t4.uname FROM test3 t3 join test4 t4 join four f INTO OUTFILE "e:/mydb/t_4000w.txt"; --8.61 SELECT t3.age,t4.uname FROM test3 t3 join test4 t4 join six s INTO OUTFILE "e:/mydb/t_6000w.txt"; --12.82 SELECT t41.age,t42.uname FROM test4 t41 join test4 t42 INTO OUTFILE "e:/mydb/t_10000w.txt"; --21.34 SELECT t41.age,t42.uname FROM test4 t41 join test4 t42 join two INTO OUTFILE "e:/mydb/t_20000w.txt"; --42.93 SELECT t41.age,t42.uname FROM test4 t41 join test4 t42 join four INTO OUTFILE "e:/mydb/t_40000w.txt"; --1min 27.81 ------------------------------------------------------准备工作----------------------------------------------------
加入测试条件：
SELECT t5.uname,t6.age FROM test5 t5,test6 t6 WHERE t5.uid>t6.uid INTO OUTFILE "e:/mydb/select_1yw.txt"; --0.03 --1w--0.02s SELECT t6.uname,t.age FROM test6 t6 JOIN two t ON t6.uid>t.uid INTO OUTFILE "e:/mydb/join_19w.txt"; --19w--0.11s SELECT t7.uname,t.age FROM test7 t7 JOIN two t ON t7.uid>t.uid INTO OUTFILE "e:/mydb/join_199w.txt"; --199w--1.22s SELECT t7.uname,s.age FROM test7 t7 JOIN six s ON t7.uid>s.uid JOIN four f INTO OUTFILE "e:/mydb/join_599y9w.txt"; --599w--2.89s SELECT t7.uname,e.age FROM test7 t7 JOIN eight e ON t7.uid>e.uid INTO OUTFILE "e:/mydb/join_799w.txt"; --799w--2.89s SELECT t8.uname,t.age FROM test8 t8 JOIN two s ON t8.uid>t.uid INTO OUTFILE "e:/mydb/join_5999w.txt"; --800w--6.21s SELECT t8.uname,f.age FROM test8 t8 JOIN four f ON t8.uid>f.uid INTO OUTFILE "e:/mydb/join_29989w.txt"; --1600w--12.96s SELECT t8.uname,s.age FROM test8 t8 JOIN six s ON t8.uid>s.uid INTO OUTFILE "e:/mydb/join_59799w.txt"; --2400w--20.31s SELECT t7.uname,t8.age FROM test8 t8,test7 t7 WHERE t8.uid=t7.uid INTO OUTFILE "e:/mydb/select_100w.txt"; --3.09 SELECT t7.uname,t8.age FROM test8 t8 JOIN test7 t7 ON t8.uid=t7.uid INTO OUTFILE "e:/mydb/join_100w.txt"; --1.54 SELECT t7.uname,t8.age FROM test8 t8,test7 t7 WHERE t8.uid>t7.uid INTO OUTFILE "e:/mydb/select_300w.txt"; --3.09 SELECT t7.uname,t8.age FROM test8 t8 JOIN test7 t7 ON t8.uid>t7.uid INTO OUTFILE "e:/mydb/join_100w.txt"; --1.54
