索引优化分析_预热_JOIN

1.性能下降SQL慢 执行时间长 等待时间长

1. 数据过多——分库分表 mycat
2. 索引失效，没有充分利用到索引——索引建立
3. 关联查询太多join（设计缺陷或不得已的需求）——SQL优化
4. 服务器调优及各个参数设置（缓冲、线程数等）——调整my.cnf

2.常见通用的Join查询

2.1.SQL执行顺序

1.手写

2.机读
随着MySQL版本的更新换代，其优化器也在不断的升级，优化器会分析不同执行顺序产生的性能消耗不同而动态调整执行顺序。
下面是经常出现的查询顺序：

3.总结(鱼骨图)

2.2.Join图

2.3.建表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(11) 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(id,deptName,address) VALUES(1,'华山','华山');
INSERT INTO t_dept(id,deptName,address) VALUES(2,'丐帮','洛阳');
INSERT INTO t_dept(id,deptName,address) VALUES(3,'峨眉','峨眉山');
INSERT INTO t_dept(id,deptName,address) VALUES(4,'武当','武当山');
INSERT INTO t_dept(id,deptName,address) VALUES(5,'明教','光明顶');
INSERT INTO t_dept(id,deptName,address) VALUES(6,'少林','少林寺');

INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(1,'风清扬',90,1,100001);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(2,'岳不群',50,1,100002);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(3,'令狐冲',24,1,100003);

INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(4,'洪七公',70,2,100004);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(5,'乔峰',35,2,100005);

INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(6,'灭绝师太',70,3,100006);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(7,'周芷若',20,3,100007);

INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(8,'张三丰',100,4,100008);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(9,'张无忌',25,5,100009);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(10,'韦小宝',18,NULL,100010);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

2.4 7种JOIN

1. A、B两表共有(查询所有有部门的员工->员工和部门之间必须存在关联的数据)

SELECT * FROM t_emp a INNER JOIN t_dept b ON a.deptid = b.id ;
1

2. A、B两表共有+A的独有（列出所有用户，并显示其机构信息）A的全集

 SELECT * FROM t_emp a LEFT JOIN t_dept b ON a.deptid = b.id ;
1

3. A、B两表共有+B的独有（列出所有部门，并显示其部门的员工信息 ）B的全集

SELECT * FROM t_emp a RIGHT JOIN t_dept b ON a.deptid = b.id ;
1

4. A的独有 （查询没有加入任何部门的员工）

SELECT * FROM t_emp a LEFT JOIN t_dept b ON a.deptid = b.id WHERE b.id IS NULL ;
1

5. B的独有（查询没有任何员工的部门）

SELECT * FROM t_emp a RIGHT JOIN t_dept b ON a.deptid = b.id WHERE a.id IS NULL ;
1

6. AB全有（查询所有员工和所有部门）
#MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法
#left join + union(去重)+ right join

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 ;
1
2
3

SELECT * FROM t_emp a LEFT JOIN t_dept b ON a.deptid = b.id 
UNION ALL
SELECT * FROM t_emp a RIGHT JOIN t_dept b ON a.deptid = b.id ;
1
2
3

1.UNION ALL 不会自动去重。
2.UNION在使用时，两张表的字段保证一致，如果不一致，请在slect后面列选字段，不要使用*

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.id IS NULL ;
1
2
3

2.5.扩展(掌门人)

1.增加掌门人字段

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;
1
2
3
4
5
6

2.求各个门派对应的掌门人名称

SELECT b.deptname,a.name FROM t_dept b LEFT JOIN t_emp a ON b.ceo = a.id ;
1

3.求所有当上掌门人的平均年龄:

SELECT AVG(a.age) FROM t_emp a INNER JOIN t_dept b ON a.id = b.ceo ;
1

4.求所有人，对应的掌门是谁(4种写法分析)

#临时表连接方式  
#step1根据ceo 去查找每个部门的掌门是谁
{SELECT b.id AS deptId,a.name AS ceoname FROM t_emp a INNER JOIN t_dept b ON a.id = b.ceo }
#step2 与员工表整合
SELECT c.name,ab.ceoname FROM t_emp c LEFT JOIN
( SELECT b.id AS deptId,a.name AS ceoname FROM t_emp a INNER JOIN t_dept b ON a.id = b.ceo ) ab
ON c.deptId = ab.deptId ;
 
#临时表连接方式 根据员工id查找
#step1 根据部门Id 查询员工的信息
SELECT a.name,b.ceo FROM t_emp a LEFT JOIN t_dept b ON a.deptid = b.id 
# step2 与员工表整合
SELECT ab.name,c.name AS 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 ;
 
#三表左连接方式
SELECT a.name,c.name AS 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 ;
 
#子查询方式
SELECT a.name ,(SELECT c.name FROM t_emp c WHERE c.id=b.ceo) AS ceoname
FROM t_emp a LEFT JOIN t_dept b 
ON a.deptid = b.id ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25