异常500,程序异常,Exception
当前位置:Exception500 » MySQL » 第七部分:MySQL BTree索引使用技巧

第七部分:MySQL BTree索引使用技巧

来源:exception500.com    发布时间:2020-05-15 16-00-43    浏览次数:135次

###第七部分:MySQL BTree索引使用技巧


###索引使用

DROP INDEX idx_examId_examPaperId ON exam_student_subjective_fileurl_51_2019_10;

CREATE INDEX idx_examId ON exam_student_subjective_fileurl_51_2019_10(examId);

#精准匹配,允许使用btree索引

EXPLAIN SELECT * FROM exam_student_subjective_fileurl_51_2019_10 WHERE examId = 458;

#范围匹配,允许使用btree索引

EXPLAIN SELECT * FROM exam_student_subjective_fileurl_51_2019_10 WHERE examId > 458 AND examId < 490;

#查询优化器会自动进行类型转换,但仍然建议使用与定义相符的类型

EXPLAIN SELECT * FROM exam_student_subjective_fileurl_51_2019_10 WHERE examId = '458';


###模糊搜索索引利用

DROP INDEX idx_fileUrl ON exam_student_subjective_fileurl_51_2019_10;

CREATE INDEX idx_fileUrl ON exam_student_subjective_fileurl_51_2019_10(fileUrl);

#字符串字段btree索引允许进行"前缀查询"

EXPLAIN SELECT * FROM exam_student_subjective_fileurl_51_2019_10 WHERE fileUrl LIKE 'https://oss.jkydata.com/20191005/%';

#后缀查询与模糊匹配btree均不支持

EXPLAIN SELECT * FROM exam_student_subjective_fileurl_51_2019_10 WHERE fileUrl LIKE '%https://oss.jkydata.com/20191005/';

EXPLAIN SELECT * FROM exam_student_subjective_fileurl_51_2019_10 WHERE fileUrl LIKE '%https://oss.jkydata.com/20191005/%';


###复合索引

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 examId=458 AND examPaperId = 1223 ;

EXPLAIN SELECT * FROM exam_student_subjective_fileurl_51_2019_10 WHERE examPaperId = 1223 ;

#<>与not in会导致不使用索引

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 OR examId >= 458;



[关键词: EXPLAINMySQL ]

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