mysql学习（三）SQL性能研究

1 SQL性能研究

sql性能研究主要是分析SQL的执行计划，需要注意的是MySQL优化器可能会干扰我们的优化。

1.1 分析SQL的执行计划

分析sql的执行计划，explain可以模拟sql优化器执行sql语句，从而让开发人员知道自己编写的语句执行方式，更好的优化sql编码。

优化方法.官网：https://dev.mysql.com/doc/refman/5.5/en/optimization.html

查询执行计划：explain+sql语句

EXPLAIN SELECT * FROM `user`

 

 

各个字段含义

属性	含义
id	编号
select_type	查询类型
table	表
type	类型
possible_keys	预测用到的索引
key	实际使用的索引
key_len	实际使用索引的长度
ref	表之间的引用
rows	通过索引查询到的数据量
Extra	额外的信息

准备测试数据：

-- 创建表
create table course
(
cid int(3),
cname varchar(20),
tid int(3)
);
create table teacher
(
tid int(3),
tname varchar(20),
tcid int(3)
);

create table teacherCard
(
tcid int(3),
tcdesc varchar(200)
);
-- 添加数据
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') ;

1.2 数据模型

 

 

 

1.查询条件：查询课程编号为2或教师证编号为3的老师信息？　　

分析sql执行计划：

EXPLAIN 
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)

分析结果图：

 

分析执行顺序：

(1)id: id值相同，根据rows 从上往下 顺序执行。  t（3）-tc（3）-c（4）

　　改变数据量：

insert into teacher values(4,'ta',4);
insert into teacher values(5,'tb',5);
insert into teacher values(6,'tc',6);

 

 再次分析sql执行计划

 

 

此时执行顺序： tc（3）-c（4）-t（6）

id相同时，表的执行顺序随rows的大小改变而改变，原理：笛卡尔积

c	t	tc	结果集
3	4	6	3*4=12*6=72
6	4	3	6*4=24*3=72

从表格中可以看出虽然两种组合方式都要比较72次，但是显然第一种3*4=12这个中间结果集比6*4=24小，数据量消耗的内存小。

结论：id值相同时，数据量小的表优先查询。

 

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

分析sql执行计划

 

EXPLAIN SELECT
    tc.tcdesc
FROM
    teacherCard tc,
    course c,
    teacher t
WHERE
    c.tid = t.tid
AND t.tcid = tc.tcid
AND c.cname = 'sql';

 

 

 

 结果图：

 

 

 

这里我们发现id都为1 顺序tc-c-t。

1.我们在将以上 多表查询 转为子查询形式：

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')
);

 

 结果图：

 

 

 

 结论：id值不同时，id值越大越优先查询 (本质：在嵌套子查询时，先查内层 再查外层)

 2.我们在将以上转换为 子查询+多表：

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值相同，从上往下 按rows大小顺序执行