MySQL———SQL优化

1.SQL优化准备

1.建立三张表

course表

create table course(
	cid int(3),
	cname varchar(20),
	tid int(3)
);

teacher表

create table teacher(
	tid int(3),
	tname varchar(20),
	tcid int(3)
);

teacherCard

create table teacherCard(
	tcid int(3),
	tcdesc varchar(200)
);

2.插入数据

insert into course values(1,'java',1);
insert into course values(2,'html',1);
insert into course values(3,'sql',2);
insert into course values(4,'web',3);

insert into teacher values(1,'tz',1);
insert into teacher values(2,'tw',2);
insert into teacher values(3,'tl',3);

insert into teacherCard values(1,'tzdesc');
insert into teacherCard values(2,'twdesc');
insert into teacherCard values(3,'tldesc');

2.SQL查询（explain）

1.查询课程编号为2，或教师证编号为3的老师信息

主干：

select t.* from teacher t；

需要课程表，教师证编号表，三表关联查询

select t.* from teacher t,course c,teacherCard tc where t.tid=c.tid
and t.tcid=tc.tcid；

查询条件：

select t.* from teacher t,course c,teacherCard tc where t.tid=c.tid and t.tcid=tc.tcid and (c.cid=2 or tc.tcid=3);

explain+sql：

执行顺序：t——tc——c，

id：id值相同，从上往下，顺序执行 t3 tc 3 c4

添加数据：t6 tc3 c4

再次查询

表的执行顺序，因数量的个数改变而改变：笛卡尔积 ： 2 3 4 4 3 2 ,数据小的表，优先查询，

id值不同，id值越大越优先查询

2.查询教授SQL课程的老师的描述（desc）

主干

select tc.tcdesc from teacherCard tc;

课程和老师信息没关联，必须通过中间表当桥梁来查询

select tc.tcdesc from teacherCard tc,course c,teacher t where t.tid=c.tid and t.tcid=tc.tcid and c.cname="SQL";

explain+sql

---

将以上多表查询转为子查询

explain select tc.tcdesc from teacherCard tc where tc.tcid=(select t.tcid from teacher t where t.tid=(select c.tid from course c where c.cname='sql'));

在嵌套子查询时，先查内层，在查外层

---

子查询+多表

explain select t.tname,tc.tcdesc from teacher t,teacherCard tc where t.tcid=tc.tcid and t.tid=(select c.tid from course c where cname='sql');

id值有相同，又有不同：id值越大越优先；id值相同，从上往下，顺序执行

3.select_type

PRIMARY:包含子查询SQL中的 主查询 （最外层）

SUBQUERY：包含子查询SQL中的 子查询 （非最外层）

simple：简单查询（不包含子查询，union查询）

deruver：衍生查询

​ a.在from子查询中只有一张表

​ explain select cr.cname from（select * from course where tid in (1,2)）

​ b.在from子查询中，如果有table1 union table2，则table1就是deriver,table2就是union

​ explain select cr.cname from (select * from course where tid =1 union)

union ：上例

union result

type:索引类型、类型

​ system>const>eq_ref>ref>range>index>all，要对type进行优化的前提：有索引

其中：system，const只是理想情况；实际能达到 ref>range

system：只有一条数据的系统表；或衍生表只有一条数据的主查询