1、select * from t_student where sname like 'yezi100%' 会使用索引
2、select * from t_student where sname like '%yezi100%' 不会使用索引
3、select * from t_student where id in (select m.id from t_student m where m.id=123) 不会使用索引
4、select * from t_student where id in (123,1233) 会使用索引
5、select * from t_student m where exists (select * from t_student n where n.id=1 and n.id=m.id) 不会使用
使用 exists一定不会使用索引,所以慎用exists
6、表t_user外键关联t_student表,关联字段为student_id
Execution Time : 00:00:00:951
Transfer Time : 00:00:00:000
Total Time : 00:00:00:951
explain select * from t_student m,t_user n where m.id=n.student_id and saddress='yezi1'
Execution Time : 00:00:04:852
Transfer Time : 00:00:00:000
Total Time : 00:00:04:852
explain select * from t_user n join (select * from t_student m where m.saddress='yezi1') t on n.student_id=t.id
2、select * from t_student where sname like '%yezi100%' 不会使用索引
3、select * from t_student where id in (select m.id from t_student m where m.id=123) 不会使用索引
4、select * from t_student where id in (123,1233) 会使用索引
5、select * from t_student m where exists (select * from t_student n where n.id=1 and n.id=m.id) 不会使用
使用 exists一定不会使用索引,所以慎用exists
6、表t_user外键关联t_student表,关联字段为student_id
Execution Time : 00:00:00:951
Transfer Time : 00:00:00:000
Total Time : 00:00:00:951
explain select * from t_student m,t_user n where m.id=n.student_id and saddress='yezi1'
Execution Time : 00:00:04:852
Transfer Time : 00:00:00:000
Total Time : 00:00:04:852
explain select * from t_user n join (select * from t_student m where m.saddress='yezi1') t on n.student_id=t.id