###第八部分：MySQL选择性差的索引
###创建和删除索引
CREATE INDEX idx_examId ON exam_student_subjective_fileurl_51_2019_10(examId);
DROP INDEX idx_examId ON exam_student_subjective_fileurl_51_2019_10;
###索引选择性好
EXPLAIN SELECT * FROM exam_student_subjective_fileurl_51_2019_10 WHERE examId = 458;
###索引选择性差
EXPLAIN SELECT * FROM exam_student_subjective_fileurl_51_2019_10 WHERE examId > 458;
#<>/not in 无法使用索引
EXPLAIN SELECT * FROM exam_student_subjective_fileurl_51_2019_10 WHERE examId <> 458;
#where 子句跳过左侧索引列,直接查询右侧索引字段
DROP INDEX idx_examId_examPaperId ON exam_student_subjective_fileurl_51_2019_10;
CREATE INDEX idx_examId_examPaperId ON exam_student_subjective_fileurl_51_2019_10(examId,examPaperId);
#复合索引查询条件必须包含左侧列
EXPLAIN SELECT * FROM exam_student_subjective_fileurl_51_2019_10 WHERE examId = 458;
#直接书写右侧列将导致数据无法查询
EXPLAIN SELECT * FROM exam_student_subjective_fileurl_51_2019_10 WHERE examPaperId = 1223;
EXPLAIN SELECT * FROM exam_student_subjective_fileurl_51_2019_10 WHERE examId = 458 AND examPaperId = 1223;
#对索引列进行计算或者使用函数,将会使索引失效
EXPLAIN SELECT * FROM exam_student_subjective_fileurl_51_2019_10 WHERE examId + 1 = 459;
EXPLAIN SELECT * FROM exam_student_subjective_fileurl_51_2019_10 WHERE CAST(examId AS CHAR) = '458';
#当排序出现了索引左侧列,则允许使用索引排序，左侧字段单字段排序时,索引支持升、降序
EXPLAIN SELECT * FROM exam_student_subjective_fileurl_51_2019_10 WHERE examId < 458 ORDER BY examId;
#当排序出现了索引右侧列,会使用索引，但是排序规则为：Using filesort（将查询结果重新排序）
EXPLAIN SELECT * FROM exam_student_subjective_fileurl_51_2019_10 WHERE examId < 458 ORDER BY examPaperId;
#在多字段情况下,左侧字段必须是升序,且顺序不允许打乱
EXPLAIN SELECT * FROM exam_student_subjective_fileurl_51_2019_10 WHERE examId < 458 ORDER BY examId,examPaperId;
#在多字段情况下,左侧字段如果是降序,则排序规则为：Using filesort（将查询结果重新排序）
EXPLAIN SELECT * FROM exam_student_subjective_fileurl_51_2019_10 WHERE examId < 458 ORDER BY examId DESC,examPaperId;
