mysql 索引 优化 面试

mysql 索引 优化 面试题目：

问如何优化下面的Mysql SQL语句？

select * from employee where employee.deptName in ( "departA", "departB", "departC") and tbl.locationID = 3 and tbl.level > 5;

思路：

1:  肯定要建立二级联合索引： index( locationID, deptName, level)

2: 这里要优化一下 SQL IN 语句。用union all 改写

select * from employee where employee.deptName = "departA" and tbl.locationID = 3 and tbl.level > 5 union all  select * from employee where employee.deptName = "departB" and tbl.locationID = 3 and tbl.level > 5  union all  select * from employee where employee.deptName = "departC" and tbl.locationID = 3 and tbl.level > 5  ;

这样就全利用上了上面的联合索引。