Day2——存储引擎、sql预热

一. 回顾

前面学习了Day1——权限、sql_mode、逻辑架构，后面学习存储引擎、sql预热。

二. 存储引擎

2.1 概述

使用show engines;可以查看mysql中有哪些引擎，如下：

使用show variables like '%storage_engine';查看MySQL的默认引擎，如下：

总结：MySQL中比较常用的引擎是MyISAM以及InnoDB。后面对他们的关键点进行介绍。

2.2 MyISAM以及InnoDB

对比项	MyISAM	InnoDB
外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作	行锁，操作时只锁某一行，不对其他行有影响，适合高并发
缓存	只缓存索引，不缓存真实数据	不仅缓存索引，还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性作用

三. 关联查询类型

注意：如上图所示，MySQL没有full outer join，因此可以使用union或者union all来查A表全有+B表独有这样。

四. sql预热

上面讲述关联查询的类型，现在来练习几个查询来预热一下，为索引优化知识做准备。

首先建表插入数据，如下：

create table t_dept(
    id int(11) not null auto_increment,
		deptName varchar(30) default null,
		address VARCHAR(40) default null,
		PRIMARY KEY (id)
)ENGINE=INNODB auto_increment=1 DEFAULT CHARSET=utf8;

CREATE TABLE t_emp(
    id int(11) not null auto_increment,
		name varchar(20) default null,
		age int(3) DEFAULT null,
		deptId int(11) DEFAULT NULL,
		empno int not null,
		PRIMARY KEY (id),
		key idx_dept_id (deptId)
		#CONSTRAINT fk_dept_id FOREIGN KEY (deptId) REFERENCES t_dept(Id)
)ENGINE=INNODB auto_increment=1 DEFAULT CHARSET=utf8;

INSERT into t_dept(deptName, address) values('华山', '华山');
INSERT into t_dept(deptName, address) values('丐帮', '洛阳');
INSERT into t_dept(deptName, address) values('峨眉', '峨眉山');
INSERT into t_dept(deptName, address) values('武当山', '武当山');
INSERT into t_dept(deptName, address) values('明教', '光明顶');
INSERT into t_dept(deptName, address) values('少林', '少林寺');

insert into t_emp(name, age, deptId, empno) VALUES('风清扬', 90, 1, 100001);
insert into t_emp(name, age, deptId, empno) VALUES('岳不群', 50, 1, 100002);
insert into t_emp(name, age, deptId, empno) VALUES('令狐冲', 24, 1, 100003);
insert into t_emp(name, age, deptId, empno) VALUES('洪七公', 70, 2, 100004);
insert into t_emp(name, age, deptId, empno) VALUES('乔峰', 35, 2, 100005);
insert into t_emp(name, age, deptId, empno) VALUES('灭绝师太', 70, 3, 100006);
insert into t_emp(name, age, deptId, empno) VALUES('周芷若', 20, 3, 100007);
insert into t_emp(name, age, deptId, empno) VALUES('张三丰', 100, 4, 100008);
insert into t_emp(name, age, deptId, empno) VALUES('张无忌', 25, 5, 100009);
insert into t_emp(name, age, deptId, empno) VALUES('韦小宝', 18, null, 1000010);

练习：

#1.所有有门派的人员信息(A、B两表共有)
select * from t_emp a INNER join t_dept b on a.deptId = b.id;

#2.列出所有用户，并显示其机构信息(A的全集)
SELECT * from t_emp a LEFT OUTER join t_dept b 
on a.deptId = b.id;

#3.列出所有门派（B的全集）
SELECT * from t_dept;

#4.所有不入门派的人员(A的独有)
SELECT * from t_emp a LEFT OUTER join t_dept b 
on a.deptId = b.id 
where b.id is null;

#5.所有没人入的门派（B表独有，将B表放在左表即可）
SELECT * from t_dept b LEFT OUTER join t_emp a 
on a.deptId = b.id 
where a.id is null;#a.id、a.deptId都可以

#6.列出所有人员和机构的对照关系（AB表全有）
#用union显示的列顺序要一致,union是去重的，如果能确定两个sql没有重复数据，则用union all（不会去重，也就不会去比较两个sql，性能会更高）
SELECT a.*, b.* from t_emp a LEFT OUTER join t_dept b 
on a.deptId = b.id
union
SELECT a.*, b.* from t_dept b LEFT OUTER join t_emp a 
on a.deptId = b.id 
where a.id is null;

#7.列出所有没入派的人员和没入派的门派
SELECT a.*, b.* from t_emp a LEFT OUTER join t_dept b 
on a.deptId = b.id 
where b.id is null
union all
SELECT a.*, b.* from t_dept b LEFT OUTER join t_emp a 
on a.deptId = b.id 
where a.id is null;

#增加掌门字段
ALTER table t_dept add CEO int(11);

update t_dept set ceo = 2 where id = 1;
update t_dept set ceo = 4 where id = 2;
update t_dept set ceo = 6 where id = 3;
update t_dept set ceo = 8 where id = 4;
update t_dept set ceo = 9 where id = 5;

#求各个门派对应的掌门人名称
SELECT * from t_emp a INNER JOIN t_dept b
on b.ceo = a.id;

#求所有当上掌门人的平均年龄
SELECT avg(age) from t_emp a inner join t_dept b
on b.ceo = a.id;

#求所有人物对应的掌门人名称
#写法1
select c.name, ab.name ceoname from t_emp c left JOIN (
SELECT name, deptId from t_emp a inner join t_dept b
on a.id = b.ceo)ab
on c.deptId = ab.deptId;
#写法2
SELECT ab.name, c.name ceoname from (
SELECT a.name, b.ceo from t_emp a left join t_dept b
on a.deptId = b.id)ab
left join t_emp c
on ab.ceo = c.id;
#写法3
SELECT a.name, c.name ceoname from t_emp a 
left join t_dept b on a.deptId = b.id
left join t_emp c on b.ceo = c.id;