mysql 查询性能分析

1、原始速度 0.133 0.133 0.128
select studentExperimentID as id2 from studentexperiment where studentID like “%2005%”

2、包含非索引 0.263  0.268 0.264
select studentExperimentID,studentID,doScore from studentexperiment where studentID like “%2005%”

3、关联索引查询 0.338  0.315  0.321
select studentExperimentID,studentID,doScore from studentexperiment r join (select studentExperimentID as id2 from studentexperiment where studentID like “%2005%”)t on r.studentExperimentID=id2

4、in索引查询 0.581  0.406 0.406
select studentExperimentID,studentID,doScore from studentexperiment where studentExperimentID in (select studentExperimentID as id2 from studentexperiment where studentID like “%2005%”)

 

经过测试表明,对于非索引字段的查询,不需要进行专门的join或in优化,结果是反而变慢。

Leave a Reply