性能下降SQL慢 执行时间长 等待时间长
数据过多：分库分表关联了太多的表，太多join：SQL优化没有充分利用到索引：索引建立服务器调优及各个参数设置：调整my.cnf
SQL预热：常见通用的Join查询
Join图
建表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
`)
) 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,100010);
7种join
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 join t_dept b
on a
.deptId
= b
.id
;
3 列出所有门派 （B的全集）
select * from t_dept b
4 所有不入门派的人员 （A的独有）
select * from t_emp a
left join t_dept b
on a
.deptId
= b
.id
where b
.id
is null;
5 所有没人入的门派 （B的独有）
select * from t_dept b
left join t_emp a
on a
.deptId
= b
.id
where a
.deptId
is null;
6 列出所有人员和机构的对照关系 (AB全有)
SELECT * FROM t_emp A
LEFT JOIN t_dept B
ON A
.deptId
= B
.id
UNION
SELECT * FROM t_emp A
RIGHT JOIN t_dept B
ON A
.deptId
= B
.id
7 列出所有没入派的人员和没人入的门派 （A的独有+B的独有）
SELECT * FROM t_emp A
LEFT JOIN t_dept B
ON A
.deptId
= B
.id
WHERE B
.`id
` IS NULL
UNION
SELECT * FROM t_emp A
RIGHT JOIN t_dept B
ON A
.deptId
= B
.id
WHERE A
.`deptId
` 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_dept
as b
left join t_emp
as a
on b
.CEO
=a
.id
;
求所有当上掌门人的平均年龄:
select avg(a
.age
) from t_emp a
inner join t_dept b
on a
.id
=b
.CEO
;
求所有人物对应的掌门名称:
SELECT
c
.`name
`,
ab
.`name
` ceoname
FROM
t_emp c
LEFT JOIN ( SELECT b
.id
, a
.NAME
FROM t_emp a
INNER JOIN t_dept b
ON b
.CEO
= a
.id
) ab
ON c
.deptId
= ab
.id
