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;

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

  • qq_43638135
    妲己再美究为妃: 博主没有想过自己接一些私活干吗?我现在还没毕业,但是我也确实听说外挂市场自动化游戏脚本市场挺火热的,并且报酬也很丰厚,但是具体的我也不是很清楚,求解答。 (1个月前 #47楼) 查看回复(2) 举报 回复
    22