MySQL 对 Where 子句中函数查询的优化

Question

让我们假设我有一个员工表 (employee)，并且其中有数千条记录。我需要根据您的登录限制您可以看到的员工以实现可重用性我创建了一个函数员工与否。

此函数看起来像这样(尽管它会进行其他检查，例如，您无法访问自己的个人资料，无法访问与您共享职位的其他员工等):

 FUNCTION fn_HasEmployeeAccess(t_userid INT, t_employee_id INT)
             DECLARE isAdmin BOOL;
             DECLARE isDirector BOOL;
             DECLARE empStatus INT;

             SELECT admin, director INTO isAdmin, isDirector FROM users 
             WHERE users.userid=t_userid;

             /*select the status of this user if it is in your dept
             if employee is not in your department - status will be null 
             which will fail the checks below and return 0*/
             SELECT employee.status INTO empStatus FROM employee 
             INNER JOIN users ON employee.department = users.department
            WHERE employee.employeeID=t_employee_id;

             IF empStatus IN (1,2) AND isAdmin THEN
                     RETURN 1;
             ELSE IF empStatus=2 AND userIsDirector THEN
                     RETURN 1;
            ELSE
                     return 0;
            END IF;
    END FUNCTION

现在所有这些都有效并且很棒，除非您有大量数据并且需要使用多个连接进行查询，然后基于此函数的结果在该过滤器之上 - 性能下降了很多，我相信我理解 - 该函数正在为员工表中的每一行运行。

例如:

SELECT * FROM employee e
INNER JOIN employee_attachments a ON e.employeeID = a.employeeID
WHERE fn_HasEmployeeAccess(12345, e.employeeID)=1

如何防止由于函数使用而导致的全表扫描？他们是这种模式的替代品吗？

该功能几乎用于过滤应用程序中的所有地方(仪表板、 View 、报告)等，如果我想调整对员工的访问权限，我目前只需调整该功能。有人建议我只是内联所有这些逻辑，但每次我们添加状态或想要更改访问逻辑时，这将需要费力地遍历系统以更改与员工有关的每个查询、每个报告、每个部分。

此外 - 我听说函数可以利用索引，但前提是函数是确定性的。这样的函数是确定性的吗？给定相同的 employeeID 和 userID，它应该返回相同的结果……除非用户从管理员更改为主管……那么该函数将返回不同的结果。这个函数是确定性的还是非确定性的？

感谢任何见解

Answer 1

在较新版本的 MySQL/MariaDB 中，您可以创建一个虚拟(计算)列并为其编制索引。新列将通过您的函数填充。这样，不要调用 WHERE 子句中的函数，只需使用新列即可。

如果(当)您添加了新状态，请执行适当的 ALTER TABLE 以重建列和索引。