【SQL】索引优化与查询优化
外连接与内连接的查询优化
# 左外连接: 左表的所有内容+左右表共有的内容
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);
# 此时再执行上面的查询,两表都会用到索引。而哪个表作为驱动表哪个作为被驱动表,由查询优化器自行选择
# 结论:被驱动表加索引执行效率高。对于内连接来讲,如果表的连接条件中只能有一个字段有索引,则有索引的字段所在的表会被作为被驱动表
# 对于内连接来说,在两个表的连接条件都存在索引的情况下,会选择小结果集作为驱动表。小结果集驱动大结果集
小结:
- 保证被驱动表的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
FROM class c
WHERE monitor IS NOT NULL
);
# 将子查询改写成多表连接
EXPLAIN SELECT stu1.* FROM<