SQL优化-多表优化

前面已经写了一篇单表优化的博客，这里继续关于多表优化的内容。其实，SQL优化路很长，我也是初学，在这里记录的优化方案都是比较基础的，但是日常写SQL的过程中如果能够根据优化知识注意SQL的书写，那性能肯定是会有一定提升的。一起努力吧！
这里附上官网上关于优化的描述（我用的5.7，这里就附上5.7）：
https://dev.mysql.com/doc/refman/5.7/en/optimization.html

一、两表优化：

CREATE TABLE teacher2(
 tid int(4) primary key,
 cid int(4) not null
);
INSERT INTO teacher2 VALUES(1,2);
INSERT INTO teacher2 VALUES(2,1);
INSERT INTO teacher2 VALUES(3,3);

CREATE TABLE course2(
 cid int(4),
 cname varchar(20)
);
INSERT INTO course2 VALUES(1,'java');
INSERT INTO course2 VALUES(2,'python');
INSERT INTO course2 VALUES(3,'sql'); 

左外连接：
EXPLAIN SELECT * FROM teacher2 t LEFT OUTER JOIN course2 c ON t.cid = c.cid WHERE c.cname = ‘java’;

优化：
加索引，索引往哪加？
—小表驱动大表(条件语句中小表写在左边)，本题两表数据都是3条，在这点上是没有影响的
—索引建立在经常使用的字段上(在小表驱动大表的基础上，小表每个字段需要使用300次（内层循环300次），即小表使用更频繁，所以索引建立在小表字段上)【一般情况对于左外连接，给左表加索引；右外连接，给右表加索引】
WHERE 小表.x = 大表.y

为什么小表驱动大表？
小表：10
大表：30
SELECT…WHERE 小表.x10 = 大表.x300

//小表写左边的时候：
for(int i=0;i<小表.length10;i++){
 for(int j=0;j<大表.length300;j++){
  ...
 }
}
//反之：
for(int i=0;i<大表.length300;i++){
 for(int j=0;j<小表.length10;j++){
  ...
 }
}

—以上两个两层for循环，最终都会循环3000次；但是，对于双层循环来说，一般建议，将数据小的循环放外层，数据大的循环放内层。–性能越强

小表驱动大表的进一步理解：
　　分析以上代码可以看到两行代码除了循环的次序不一致意外，其他并无区别，在实际执行时两者所消耗的时间和空间应该也是一致的。但是这仅仅是在Java中，现在我们转化一下情景，最外层循环是数据库中的连接操作，内层循环为查找操作，那么现在两次的结果将相差巨大。
　　之所以出现这样的原因是数据库的特点决定的，数据库中相比较于查询操作而言，建立连接是更消耗资源的。第一段代码建立了300次连接，每一次连接却只做了10次查询，显然是很浪费的。
　　因此在我们对数据库进行操作时需要遵循的操作应当是小表驱动大表(小的数据集驱动大的数据集)。

在左表上加索引：
ALTER TABLE teacher2 ADD INDEX index_teacher2_cid(cid);

ALTER TABLE course2 ADD INDEX index_course2_cname(cname);

Using join buffer—extra中的字段，作用：MySQL引擎使用了连接缓存（sql写的太差了）。

二、三（多）表优化：
1.小表驱动大表
2.索引建立在经常查询的字段上

三、避免索引失效的一些原则
1.复合索引，不要跨列或无序使用，否则会使组合索引失效；
复合索引，尽量使用全索引匹配

2.不要在索引上进行任何操作（计算、函数、类型转换），否则索引失效
EXPLAIN SELECT * FROM book WHERE authorid = 1 AND typeid = 2;–用到了at2个索引
EXPLAIN SELECT * FROM book WHERE authorid = 1 AND typeid2 = 2;–用到了a1个索引
EXPLAIN SELECT * FROM book WHERE authorid2 = 1 AND typeid2 = 2;–用到了0个索引
EXPLAIN SELECT * FROM book WHERE authorid2 = 1 AND typeid = 2;–用到了0个索引，原因：对于复合索引，左边失效了右边全部失效。（a,b,c）中b失效，那b,c都失效，a没有失效。（如果不是复合索引，则没有关系）

DROP INDEX idx_atb ON book;
ALTER TABLE book ADD INDEX idx_authroid(authroid);
ALTER TABLE book ADD INDEX idx_typeid(typeid);
EXPLAIN SELECT * FROM book WHERE authorid*2 = 1 AND typeid = 2;

3.复合索引不能使用不等于（！=或<>）或is null(is not null),否则自身以及右侧索引全部失效。
EXPLAIN SELECT * FROM book WHERE authorid = 1 AND typeid = 2;

只用了一个索引，原因：
–SQL优化，是一种概率层面的优化。至于是否实际使用了我们的优化，需要通过explain进行推测

EXPLAIN SELECT * FROM book WHERE authorid != 1 AND typeid = 2;

由图看出上面的结论是针对复合索引，对单独的索引没有上述影响。

这篇博客暂且写到这里，关于SQL优化在官网上有更多的解释，可自行查阅。