前言:sql优化是个老生常谈的话题。个人理解的话,优化主要是对查询的优化。
在业务逻辑中,遇到一个有意思的sql语句 ,它同时使用exists和or运算,效率的话的确慢好几百拍。
初始sql语句(1-1):
select `id` from `students` where `school_id` = '1' and (exists (select 1 from `school_tag` as `tag` where `type_id` in ('1', '2') and `student_type` = 'normal' and `tag`.`student_id` = `students`.`id`) or (`student_nation` = 1));
接下来,我们应该对or运算符进行一定的分析和优化
一、关于or运算符
or运算符:mysql5.7关于使用到OR是否会用到索引并提高查询效率的探讨(必看)
关于or运算符详细的知识,我也就不再赘叙,从上述文章中分析到关键的地方:
- 在or条件如果不在同一个表内执行某个表的查询不走索引。可以使用union all来改写扫描行数减少且会走索引。
通过union all优化sql语句(1-2):
select `st`.`id` from `students` as `st` where `st`.`school_id` = '1' and exists (select 1 from `school_tag` as `tag` where `tag`.`type_id` in ('1', '2') and `tag`.`student_type` = 'normal' and `tag`.`student_id` = `st`.`id` union all select `st`.`id` from `students` as `st`. where `st`.`school_id` = '1' and `st`.`student_nation` = 1
二、关于exists运算符
exists运算符: mysql中EXISTS与IN用法比较(必看)
关于exists运算符详细的知识,我也就不再赘叙,从上述文章中分析到关键的地方:
- 外查询表大,子查询表小,选择IN;外查询表小,子查询表大,选择EXISTS;若两表差不多大,则差不多。
初始sql中,外查询表指的是students表,子查询表为school_tag表。我们选择IN实现EXISTS的效果。
通过IN运算优化sql语句(1-3):
select `st`.`id` from `students` as `st` where `st`.`school_id` = '1' and `st`.`id` in (select `tag`.`id` from `school_tag` as `tag` where `tag`.`tag_id` in ('1', '2') and `tag`.`student_type` = 'normal') union all sselect `st`.`id` from `students` as `st` where `st`.`school_id` = '1' and `st`.`student_nation` = 1
三、优化结果
students表数据条数在60万条,school_tag表数据条数在10万条之间
(1)初始sql在本地进行执行的时候时间较长,且执行奔溃
(2)在优化之后,效率有较大的提升。查询数16718,执行时间在403ms,查询数16718
结论:
此次的优化适用于存在or运算如果不在同一个表内和exises运算外查询表大,子查询表小,选择IN同存的sql语句。但是举一反三,程序员们不成问题。