mysql大数据量测试

环境准备

数据库

------------------------------------------------
create database bigData;
use bigData;

------------------------------------------------

CREATE TABLE dept( /*部门表*/
deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0,
dname VARCHAR(20)  NOT NULL  DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

------------------------------------------------
CREATE TABLE emp
(empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0,
ename VARCHAR(20) NOT NULL DEFAULT "",
job VARCHAR(9) NOT NULL DEFAULT "",
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
hiredate DATE NOT NULL,
sal DECIMAL(7,2)  NOT NULL,
comm DECIMAL(7,2) NOT NULL,
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0
)ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

------------------------------------------------
CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
losal DECIMAL(17,2)  NOT NULL,
hisal DECIMAL(17,2)  NOT NULL
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);


------------------------------------------------
create function rand_string(n INT)
returns varchar(255)
begin 
 declare chars_str varchar(100) default
   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
 declare return_str varchar(255) default '';
 declare i int default 0;
 while i < n do 
   set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
   set i = i + 1;
   end while;
  return return_str;
  end
	
------------------------------------------------

create function rand_num( )
returns int(5)
begin 
 declare i int default 0;
 set i = floor(10+rand()*500);
return i;
  end

------------------------------------------------
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0; 
 set autocommit = 0;  
 repeat
 set i = i + 1;
 insert into emp values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
  until i = max_num
 end repeat;
   commit;
 end

------------------------------------------------
create procedure insert_dept(in start int(10),in max_num int(10))
begin
declare i int default 0; 
 set autocommit = 0;  
 repeat
 set i = i + 1;
 insert into dept values ((start+i) ,rand_string(10),rand_string(8));
  until i = max_num
 end repeat;
   commit;
 end;

-- 执行创建部门
call insert_dept(1,500);

-- 执行创建员工
call insert_emp(100001,4000000); 

-- call insert_emp(100001,1800000)
-- > OK
-- > 时间: 230.094s

• 500 和个部门 和 400万员工准备就绪
• window10
• mysql 5.7
• navicat 15

全量单表查询

select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp
> OK
> 时间: 5.479s

select * from emp
> OK
> 时间: 5.643s

select empno,ename from emp
> OK
> 时间: 1.849s

* 不会减少查询速度
列的数量会影响查询速度

条件查询

select empno,ename from emp where empno = '1000001'
> OK
> 时间: 0.416s

select * from emp where empno = '1000001'
> OK
> 时间: 0.409s

-- 全部满足 和查询全表时间一样
select * from emp where hiredate = '2021-05-04' 
> OK
> 时间: 5.697s

select * from emp where hiredate = '2021-05-04' or empno='1000001'
> OK
> 时间: 6.225s

select * from emp where hiredate = '2021-05-04' and empno='1000001'
> OK
> 时间: 0.583s

select * from emp where empno='1000001' and hiredate = '2021-05-04'
> OK
> 时间: 0.589s

-- ename 设置B树索引
select * from emp where ename  like '%gz%'
> OK
> 时间: 0.6s

select * from emp where ename like 'gz%'
> OK
> 时间: 0.024s

select * from emp where ename like '%gz'
> OK
> 时间: 0.609s

-- ename 设置HASH索引
select * from emp where ename like '%gx'
> OK
> 时间: 0.591s

select * from emp where ename like '%gx%'
> OK
> 时间: 0.619s

select * from emp where ename like 'gx%'
> OK
> 时间: 0.022s

表连接

-- deptno没有主键索引
select * from emp,dept where dept.deptno=emp.deptno
> OK
> 时间: 70.327s

-- deptno有主键索引
select * from emp,dept where dept.deptno=emp.deptno
> OK
> 时间: 18.622s

select empno from emp,dept where dept.deptno=emp.deptno
> OK
> 时间: 2.307s

select * from emp left join dept on dept.deptno=emp.deptno
> OK
> 时间: 19.96s

select * from emp right join dept on dept.deptno=emp.deptno
> OK
> 时间: 72.87s

select * from emp inner join dept on dept.deptno=emp.deptno where dept.deptno = 107
> OK
> 时间: 0.714s

select * from emp RIGHT join dept on dept.deptno=emp.deptno where dept.deptno = 107
> OK
> 时间: 0.701s

select * from emp left join dept on dept.deptno=emp.deptno where dept.deptno = 107
> OK
> 时间: 0.691s

select empno from emp inner join dept on dept.deptno=emp.deptno where dept.deptno = 107
> OK
> 时间: 0.465s

select empno from emp left join dept on dept.deptno=emp.deptno where dept.deptno = 107
> OK
> 时间: 0.457s

select empno from emp right join dept on dept.deptno=emp.deptno where dept.deptno = 107
> OK
> 时间: 0.468s

select * from emp inner join dept  on dept.deptno=emp.deptno where emp.deptno in(105,107)
> OK
> 时间: 1.57s

select empno from emp right join dept on dept.deptno=emp.deptno limit 10000,10
> OK
> 时间: 0.134s

select empno from emp right join dept on dept.deptno=emp.deptno limit 3000000,10
> OK
> 时间: 39.429s

select empno from emp left join dept on dept.deptno=emp.deptno limit 3000000,10
> OK
> 时间: 1.609s

select empno from dept left join emp  on dept.deptno=emp.deptno limit 3000000,10
> OK
> 时间: 40.029s

-- inner join  表1,表2 最优选择  
select empno from dept inner join emp  on dept.deptno=emp.deptno limit 3000000,10
> OK
> 时间: 1.433s

select empno from emp inner join dept  on dept.deptno=emp.deptno limit 3000000,10
> OK
> 时间: 1.497s

-- 总结 多数据 left join 少数据
-- 左连接时使用主表在右侧