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

###第七部分：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;

[关键词： EXPLAIN、 MySQL ]