【数据库】-【索引优化分析：问题分析】

一、存储引擎简介

1.MyISAM和InnoDB

二、索引优化问题

1.问题：性能下降SQL慢、执行时间长、等待时间长

①查阅语句写的差

太多连接，子查询。考虑能不能拆，条件过滤尽量少。

②索引失效

建立索引，并没有使用。
单值索引

id name email weixinNumber

select * from user where name = '';

#建立单值索引，底层进行排序，随数据量增加，效率提高
create index idx_user_name on user(name)

---

复合索引

id name email weixinNumber

select * from user where name = '';

#常用字段，建立复合索引
create index idx_user_nameEmail on user(name，email)

③关联了太多的表，太多join

设计缺陷或不得已的需求。
join 原理
用A表的每一条数据扫描B表的所有数据。所以尽量先过滤。

④服务器调优及各个参数设置（缓冲、线程数等）(不重要DBA的工作)

三、常见通用的Join查询

1.SQL执行顺序

手写顺序

机读顺序

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

总结

SQL执行顺序：

2.join图

内联

SELECT <select_list>
FROM TableA A
INNER JOIN TableB B
ON A.Key = B.Key

共有左联

SELECT <select_list>
FROM TableA A
LEFT JOIN Table B
ON A.Key = B.Key

共有右联

SELECT <select_list>
FROM TableA A
RIGHT JOIN Table B
ON A.Key = B.Key

独有左联

SELECT <select_list>
FROM Table A
LEFT JOIN Table B
ON A.Key = B.Key
WHERE B.Key IS NULL

独有右联

SELECT <select_list>
FROM Table A
RIGHT JOIN Table B
ON A.Key = B.Key
WHERE A.Key IS NULL

共有全连接

SELECT<select_list>
FROM TableA A
FULL OUTER JOIN TableB B
ON A.Key = B.Key

独有全连接

SELECT<select_list>
FROM TableA A
FULL OUTER JOIN TableB B
ON A.Key = B.Key
WHERE A.Key IS NULL
OR B.Key IS NULL

什么叫共有，什么叫独有？
共有：满足 a.deptid = b.id 的叫共有
A独有: A 表中所有不满足 a.deptid = b.id 连接关系的数据

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,
 PRIMARY KEY (`id`),
 KEY `fk_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) VALUES('风清扬',90,1);
INSERT INTO t_emp(NAME,age,deptId) VALUES('岳不群',50,1);
INSERT INTO t_emp(NAME,age,deptId) VALUES('令狐冲',24,1); 
INSERT INTO t_emp(NAME,age,deptId) VALUES('洪七公',70,2);
INSERT INTO t_emp(NAME,age,deptId) VALUES('乔峰',35,2);
INSERT INTO t_emp(NAME,age,deptId) VALUES('灭绝师太',70,3);
INSERT INTO t_emp(NAME,age,deptId) VALUES('周芷若',20,3); 
INSERT INTO t_emp(NAME,age,deptId) VALUES('张三丰',100,4);
INSERT INTO t_emp(NAME,age,deptId) VALUES('张无忌',25,5);
INSERT INTO t_emp(NAME,age,deptId) VALUES('韦小宝',18,null);

① A、B两表共有

select * from t_emp a inner join t_dept b on a.deptId = b.id;

② A、B两表共有+A的独有

select * from t_emp a left join t_dept b on a.deptId = b.id;

③ A、B两表共有+B的独有

select * from t_emp a right join t_dept b on a.deptId = b.id;

④ A的独有

select * from t_emp a left join t_dept b on a.deptId = b.id where b.id is null; 

⑤B的独有

select * from t_emp a right join t_dept b on a.deptId = b.id where a.deptId is null;  

⑥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

⑦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;