###第十部分：MySQL多表关联优化
#关闭查询缓存
SET GLOBAL query_cache_size = 0;
SET GLOBAL query_cache_type = 0;
###创建和删除索引
DROP INDEX idx_examId ON exam_student_subjective_fileurl_51_2019_10;
DROP INDEX idx_examId_examPaperId ON exam_student_subjective_fileurl_51_2019_10;
EXPLAIN
SELECT F.*, E.courseName
FROM exam_student_subjective_fileurl_51_2019_10 F,exam_paper E
WHERE F.examPaperId = E.examPaperId AND F.examId = E.examId AND E.examId = 458;
#explain type值的含义
#ALL 全表扫描
#eq_ref 联表查询的情况，按联表的主键或唯一键联合查询。
#ref 非主键或唯一索引的等值检索
#explain 默认第一行出现的表就是驱动表,由查询优化器自动选择
#关联查询优化要点
#1. 外键上加索引
CREATE INDEX idx_examId ON exam_paper(examId);
#2. 查询条件上加索引
CREATE INDEX idx_examId_examPaperId ON exam_student_subjective_fileurl_51_2019_10(examId,examPaperId);
#移除索引
DROP INDEX idx_examId ON exam_paper;
DROP INDEX idx_examId_examPaperId ON exam_student_subjective_fileurl_51_2019_10;
