测试

create table dept(
    id int unsigned primary key auto_increment,
    deptno mediumint unsigned not null default 0,
    dname varchar(20) not null default "",
    loc varchar(13) not null default ""
)ENGINE=innodb DEFAULT CHARSET =utf8;
create table emp(
    id int unsigned primary key auto_increment,
    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=innodb default charset=utf8;
-- 创建函数，例如报错： This function has none of deterministic
show variables like 'log_bin_trust_function_creators';
set global log_bin_trust_function_creators = 1;
-- 创建函数，例如报错： This function has none of deterministic
show variables like 'log_bin_trust_function_creators';
set global log_bin_trust_function_creators = 1;
-- 存储工程
-- 随机生成字符串
DELIMITER $$
create FUNCTION rand_string(n int) returns varchar(255)
begin
    declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    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 $$;
-- 随机生成部门编号
delimiter $$
create function rand_num()
returns int(5)
begin
declare i int default 0;
set i = floor(100+rand()*10);
return i;
end $$
-- 插入数据给emp
delimiter $$
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(empno,ename,job,mgr,hiredate,sal,comm,deptno)
    values((start+i),rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
    until i = max_num
    end repeat;
    commit;
end $$
-- 插入dept数据
delimiter $$
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 (deptno,dname,loc) values((start+i),rand_string(10),rand_string(8));
until i = max_num
end repeat;
commit;
end $$
call insert_dept(100,10);
call insert_emp(100001,500000);
select * from dept;

实战

in还有exists

-- 前提 dept :10行    emp: 500000行

-- 用in的时候，in后面的表是小表
-- 耗时 1.561s
select * from emp e where e.deptno in (select deptno from dept);
-- 耗时 5.538s
select * from emp e where exists (select 1 from dept d where d.deptno = e.deptno)



-- 用exists时，exists后面出现的是大表,虽然时间看起来很小

-- 耗时 0.230s
select * from dept d where d.deptno in (select deptno from emp);
-- 耗时 0.122s
select * from dept d where exists (select 1 from emp e where e.deptno = d.deptno)

两表联查

-- 前提  所有都没有index
-- 耗时 2.400s
select e.* from emp e left join dept d on e.deptno = d.deptno;

-- left join在右边添加索引
alter table dept add index idx_dno(deptno);


-- left join在右边添加索引，耗时1.891s

select e.* from emp e left join dept d on e.deptno = d.deptno;





-- 我们测试一下在大表上添加索引，并删除之前创建的索引
-- alter table dept drop index idx_dno;

-- 耗时1.426s
alter table dept add index idx_dno(deptno);
select e.* from dept d left join emp e on d.deptno = e.deptno




-- 两张表都加索引
alter table emp add index idx_dno(deptno);

alter table dept add index idx_dno(deptno);


-- 耗时 2.143s
select * from dept d left join emp e on d.deptno = e.deptno；

-- 耗时 2.579s
select * from emp e left join dept d on e.deptno = d.deptno;


-- 推荐使用小表驱动大表