【SQL】查询优化|排序优化|group by优化|分页查询优化|覆盖索引

外连接与内连接的查询优化

# 左外连接: 左表的所有内容+左右表共有的内容
EXPLAIN SELECT SQL_NO_CACHE * FROM 'type' LEFT JOIN book ON type.card = book.card;
# 给book表添加索引
CREATE INDEX Y ON book(card);
# 此时，book表的type = ref，使用索引；type表的type = all，左表内容全要，所以全表查询

# 再给type表添加索引，查询时也会用到这个索引
CREATE INDEX X ON 'type'(card);
# 左表-驱动表，右表-被驱动表

# 注意：查询语句中相比较的两个字段type.card = book.card，名字可以不一样，但数据类型必须相同。

DROP INDEX X ON 'type';
DROP INDEX Y ON 'type';


# 内连接：两表共有的部分
EXPLAIN SELECT SQL_NO_CACHE * FROM 'type' INNER JOIN book ON type.card = book.card;
# 给book表添加索引
CREATE INDEX Y ON book(card);
# 此时，book表的type = ref，使用索引；type表的type = all

# 给type表加索引
CREATE INDEX X ON 'type'(card);
# 此时再执行上面的查询，两表都会用到索引。而哪个表作为驱动表哪个作为被驱动表，由查询优化器自行选择

# 结论：被驱动表加索引执行效率高。对于内连接来讲，如果表的连接条件中只能有一个字段有索引，则有索引的字段所在的表会被作为被驱动表
# 对于内连接来说，在两个表的连接条件都存在索引的情况下，会选择小结果集作为驱动表。小结果集驱动大结果集

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

小结：

• 保证被驱动表的JOIN字段已经创建了索引
• 需要JOIN 的字段，数据类型保持绝对一致。
• LEFT JOIN 时，选择小表作为驱动表， 大表作为被驱动表。减少外层循环的次数。
• INNER JOIN 时，MySQL会自动将小结果集的表选为驱动表。选择相信MySQL优化策略。
• 能够直接多表关联的尽量直接关联，不用子查询。(减少查询的趟数)
• 不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用 JOIN 来代替子查询。
• 衍生表建不了索引

子查询优化

子查询是 MySQL 的一项重要的功能，可以帮助我们通过一个 SQL 语句实现比较复杂的查询。但是，子查询的执行效率不高。原因：
① 执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。这样会消耗过多的CPU和IO资源，产生大量的慢查询。
② 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。
③ 对于返回结果集比较大的子查询，其对查询性能的影响也就越大。在MySQL中，可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引的话，性能就会更好。

#################### 子查询的优化 ###########################
# 创建班级表中班长的索引
CREATE INDEX idx_monitor ON class(monitor);

# 子查询
EXPLAIN SELECT * FROM student stu1
WHERE stu1.`stuno` IN (
SELECT monitor 
1
2
3
4
5
6
7
8