在MySQL中，可以使用连接（JOIN）查询来替代子查询。连接查询 不需要建立临时表 ，其 速度比子查询 要快 ，如果查询中使用索引的话，性能就会更好。

之前我们在数据库中建立了两张表学生表和班级表，它们表结构如下

举例1：查询学生表中班长的学生信息

-- 创建班级表中的班长索引
-- 语句一
ALTER TABLE `class` ADD INDEX idx_monitor(monitor)
-- 查看班级表的索引
SHOW INDEX FROM class
-- 查询学生表中的班长信息
EXPLAIN SELECT * FROM student s
WHERE s.stuno IN(
		SELECT monitor
		FROM class c
		WHERE monitor IS NOT NULL
		);

-- 推荐使用多表查询
-- 语句二
EXPLAIN SELECT 	s.* FROM student s JOIN class c
ON s.stuno=c.monitor;	

语句一分析结果如下，其多一个临时表

语句二没有临时表

?

?

结论：尽量不要使用NOT IN 或者 NOT EXISTS，用LEFT JOIN xxx ON xx WHERE xx IS NULL替代

排序优化

案例：

删除掉student以及class上的所有索引

过程一：

EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classId;

?

EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classId LIMIT 10;

?过程二：order by时，不limit，索引失效

-- 创建索引
CREATE INDEX idx_age_classid_name ON student(age,classId,NAME);

-- 不限制，索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classId;

?

#增加limit条件，使用上了索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classId LIMIT 10;

?过程三：order by时，顺序 错误 以及规则不一致 索引失效

-- 创建索引age classId stuno
CREATE INDEX idx_age_classid_stuno ON student(age,classId,stuno);

-- 不会使用到索引，age没有加入，与我们之前讲到的最左前缀匹配矛盾
EXPLAIN SELECT * FROM student ORDER BY classId LIMIT 10;

-- 不会使用到索引，age没有加入，与我们之前讲到的最左前缀匹配矛盾
EXPLAIN SELECT * FROM student ORDER BY classId,NAME LIMIT 10;

-- 会使用
EXPLAIN SELECT * FROM student ORDER BY age, classId,stuno LIMIT 10;

-- 不会使用，因为我们的索引都是默认的asc ，而语句中出现了desc
EXPLAIN SELECT * FROM student ORDER BY age, classId DESC,stuno LIMIT 10;

-- 会使用，虽然索引都是asc，但是我们order by中都是desc而不是只有某一个是desc
EXPLAIN SELECT * FROM student ORDER BY age DESC, classId DESC,stuno DESC LIMIT 10;

-- 会使用
EXPLAIN SELECT * FROM student ORDER BY age,classId LIMIT 10;

-- 会使用
EXPLAIN SELECT * FROM student ORDER BY age LIMIT 10;

过滤四： 无过滤，不索引

-- yes
EXPLAIN SELECT * FROM student WHERE age=45 ORDER BY classId;

-- yes
EXPLAIN SELECT * FROM student WHERE age=45 ORDER BY classId, NAME;

-- no
EXPLAIN SELECT * FROM student WHERE classId=45 ORDER BY age;

-- yes

EXPLAIN SELECT * FROM student WHERE classId=45 ORDER BY age LIMIT 10;

小结

?实战

order by子句，尽量使用index方式排序，避免使用Filesort方式排序

执行案例前先清楚student表上的索引。

场景：查询年龄为30岁的且学生编号小于101000的学生 按用户名排序

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND stuno<101000 ORDER BY NAME;

运行后的结果为

?

方案一：为了去掉Filesort我们可以建立索引

CREATE INDEX idx_age_name ON student(age,NAME);

然后

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND stuno<101000 ORDER BY NAME;

看结果，我们去除了Filesort且使用了索引

且查询效率提升

?

?方案二?

CREATE INDEX idx_age_stuno_name ON student(age,stuno,NAME);


EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND stuno<101000 ORDER BY NAME;

?

?结果竟然有Filesort的sql运行速度超过了已经优化掉filesort的sql，而且快了很多，几乎一瞬间就出现了结果。

原因：所有的排序都是在条件过滤之后才执行的。所以，如果条件过滤掉大部分数据的话，剩下几百几千条数据进行排序其实不是很消耗性能，即使索引优化了排序，但实际提升性能有限。相对的stuno<101000这个条件，如果没有用到索引的话，要对几万条的数据进行扫描，这是非常消耗性能的，所以索引放在这个紫断殇性价比最高，是最优选择。

filesort算法：双路排序和单路排序

排序的字段如果不再索引列上，则Filesort会有两种算法 双路排序和单路排序

?