异常500,程序异常,Exception
当前位置:Exception500 » MySQL » 第八部分:MySQL选择性差的索引

第八部分:MySQL选择性差的索引

来源:exception500.com    发布时间:2020-05-15 16-01-25    浏览次数:143次

###第八部分: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;



[关键词: EXPLAINMySQL ]

软件开发 程序错误 异常 500错误 Exception Copyright© 2019-2020  Exception500 版权所有  【蜀ICP备15020376号-9】  网站地图