Cakephp项目重构——查询优化

正在开发一个工程管理系统，项目层级上分为实体项目和单项工程，实体项目是按区域区分的，比如闵行区项目、静安区项目；一个实体项目有多个单项工程。其中每个单项工程都有很多控制节点，每个节点由不同的用户负责。节点之间有前后置关系，前置节点完成后，本节点成为用户的待办节点。

功能需求：用户可以查看自己负责的节点、已经完成的节点、待办节点，可以通过时间段、区域筛选节点。

查询功能的UI：

数据结构：

单项工程-project_engineerings

实体项目-entity_projects

区域-material_areas

单项工程节点-project_engineering_nodes

用户-users

用户待办节点-users_project_engineering_nodes

查询页面要根据单项工程进行分页展示：

优化前的性能：

memory占用18MB，耗时4.03s，SQL占用2371ms

优化后：

memory占用15.63MB，耗时0.22秒，SQL耗时103ms

可以看到效果还是比较显著的。下面来看看完整优化的代码：

public function myPreNodeIndex($quickSearch = '')
    {
        $projectEngineeringNodeMatchCondition = [];//查询中关联单项工程节点的过滤条件
        $usersProjectEngineeringNodesMatchCondition = [];//查询中关联待办节点的过滤条件
        $projectEngineeringsCondition = [];//查询中单项工程的过滤条件

//        $this->request->session()->destroy();
        $userId = $this->request->session()->read('tcm_userId');
        $usersProjectEngineeringNodesMatchCondition = ['UsersProjectEngineeringNodes.user_id' => $userId];//默认查询用户待办节点

        //->搜索框的提交
        if ($this->request->is('post')) {
            $usersProjectEngineeringNodesMatchCondition = [];

            $this->request->session()->delete('projectEngineeringNodes.myPreNodeIndex.projectEngineeringNodeMatchCondition');
            $this->request->session()->delete('projectEngineeringNodes.myPreNodeIndex.usersProjectEngineeringNodesMatchCondition');
            $this->request->session()->delete('projectEngineeringNodes.myPreNodeIndex.projectEngineeringsCondition');

            $projectName = $_POST['project_name'];//单项工程名称
            $code = $_POST['code'];//单项工程编号
            $materialAreaId = $_POST['material_area_id'];//区域
            $nodePlanStart = $_POST['node_plan_start'];//节点计划开始时间
            $nodePlanEnd = $_POST['node_plan_end'];//节点计划结束时间
            $nodeState = $_POST['node_state'];//节点状态
            if ($projectName) {//单项工程名称
                $projectEngineeringsCondition[] = ['ProjectEngineerings.name LIKE ' => "%{$projectName}%"];
            }
            if ($code) {//单项工程编号
                $projectEngineeringsCondition[] = ['ProjectEngineerings.code LIKE ' => "%{$code}%"];
            }
            if ($materialAreaId) {//区域
                $projectEngineeringsCondition[] = ['EntityProjects.material_area_id' => $materialAreaId];
            }

            //查询节点开始/结束时间/节点状态
            $this->request->session()->delete('schMemery.projectEngineeringIds');
            if ($nodePlanStart) {//节点计划开始时间
                $projectEngineeringNodeMatchCondition[] = ['ProjectEngineeringNodes.start_date >=' => $nodePlanStart];
            }
            if ($nodePlanEnd) {//节点计划结束时间
                $projectEngineeringNodeMatchCondition[] = ['ProjectEngineeringNodes.end_date <' => $nodePlanEnd];
            }

            if ($nodeState) {//节点状态
                switch ($nodeState) {
                    case 1://待办
                        //获得用户包含待办节点的单项工程
                        $usersProjectEngineeringNodesMatchCondition[] = ['UsersProjectEngineeringNodes.user_id' => $userId];
                        break;
                    case 2://已完成
                        $projectEngineeringNodeMatchCondition[] = ['ProjectEngineeringNodes.is_complete' => 1];
                        break;
                    case 3://未完成
                        $projectEngineeringNodeMatchCondition[] = ['ProjectEngineeringNodes.is_complete' => 0];
                        break;
                    default:
                        $usersProjectEngineeringNodesMatchCondition = [];
                        break;
                }
            }

            // 把搜索条件写入session
            $schMemery = [
                'projectName' => $projectName, 'projectCode' => $code, 'materialAreaId' => $materialAreaId, 'nodePlanStart' => $nodePlanStart, 'nodePlanEnd' => $nodePlanEnd, 'nodeState' => $nodeState
            ];
            $this->request->session()->write('schMemery.ProjectEngineeringNodes-myPreNodeIndex', $schMemery);
        }
        //是否有缓存的查询条件
        if($this->request->session()->check('schMemery.ProjectEngineeringNodes-myPreNodeIndex'))
            $realSchMem = $this->request->session()->read('schMemery.ProjectEngineeringNodes-myPreNodeIndex');
        //<-搜索框的提交

        //快捷查询
        if ($quickSearch) {
            //本月第一天与最后一天
            $thisMonthBeginDate = date('Y-m-01', strtotime(date('Y-m-d')));
            $thisMonthEndDate = date('Y-m-d', strtotime("$thisMonthBeginDate +1 month"));

            switch ($quickSearch) {
                case 'todoPerMonth'://我的本月待办节点
                    $projectEngineeringNodeMatchCondition[] = ['ProjectEngineeringNodes.start_date >=' => $thisMonthBeginDate];//开始时间
                    $projectEngineeringNodeMatchCondition[] = ['ProjectEngineeringNodes.end_date <' => $thisMonthEndDate];//结束时间
                    $usersProjectEngineeringNodesMatchCondition[] = ['UsersProjectEngineeringNodes.user_id' => $userId];//待办
                    break;
                case 'completePerMonth'://我的本月已完成节点
                    $projectEngineeringNodeMatchCondition[] = ['ProjectEngineeringNodes.start_date >=' => $thisMonthBeginDate];//开始时间
                    $projectEngineeringNodeMatchCondition[] = ['ProjectEngineeringNodes.end_date <' => $thisMonthEndDate];//结束时间
                    $projectEngineeringNodeMatchCondition[] = ['ProjectEngineeringNodes.is_complete' => 1];//已完成节点
                    break;
                case 'allPerMonth'://我的本月未完成节点
                    $projectEngineeringNodeMatchCondition[] = ['ProjectEngineeringNodes.start_date >=' => $thisMonthBeginDate];//开始时间
                    $projectEngineeringNodeMatchCondition[] = ['ProjectEngineeringNodes.end_date <' => $thisMonthEndDate];//结束时间
                    $projectEngineeringNodeMatchCondition[] = ['ProjectEngineeringNodes.is_complete' => 0];//未完成节点
                    break;
                default:
                    break;
            }
        }

        //将查询写入session, 保持分页
        if ($projectEngineeringNodeMatchCondition)
            $this->request->session()->write('projectEngineeringNodes.myPreNodeIndex.projectEngineeringNodeMatchCondition', $projectEngineeringNodeMatchCondition);
        if ($usersProjectEngineeringNodesMatchCondition)
            $this->request->session()->write('projectEngineeringNodes.myPreNodeIndex.usersProjectEngineeringNodesMatchCondition', $usersProjectEngineeringNodesMatchCondition);
        if ($projectEngineeringsCondition)
            $this->request->session()->write('projectEngineeringNodes.myPreNodeIndex.projectEngineeringsCondition', $projectEngineeringsCondition);

        //->构造查询:结合了搜索结果及分页
        if ($this->request->session()->check('projectEngineeringNodes.myPreNodeIndex.projectEngineeringNodeMatchCondition'))//查询缓存
            $projectEngineeringNodeMatchCondition = $this->request->session()->read('projectEngineeringNodes.myPreNodeIndex.projectEngineeringNodeMatchCondition');
        if ($this->request->session()->check('projectEngineeringNodes.myPreNodeIndex.usersProjectEngineeringNodesMatchCondition'))//查询缓存
            $usersProjectEngineeringNodesMatchCondition = $this->request->session()->read('projectEngineeringNodes.myPreNodeIndex.usersProjectEngineeringNodesMatchCondition');
        if ($this->request->session()->check('projectEngineeringNodes.myPreNodeIndex.projectEngineeringsCondition'))//查询缓存
            $projectEngineeringsCondition = $this->request->session()->read('projectEngineeringNodes.myPreNodeIndex.projectEngineeringsCondition');

        $projectEngineeringNodeMatchCondition[] = ['ProjectEngineeringNodes.user_id' => $userId];//用户负责节点
        $projectEngineeringNodeMatchCondition[] = ['ProjectEngineeringNodes.single_project_id !=' => 0];//节点包含单项工程ID
        $projectEngineeringsCondition[] = ['ProjectEngineerings.problem_flag' => 0];//单项工程没有标记有问题
        $projectEngineeringQuery = $this->ProjectEngineerings
            ->find()
            ->contain(['EntityProjects.MaterialAreas', 'ProjectEngineeringNodes', 'ProjectEngineeringNodes.UsersProjectEngineeringNodes'])
            ->select(['id', 'name', 'code', 'area_name' => 'MaterialAreas.name'])
            ->where($projectEngineeringsCondition)//单项工程没有标记有问题
            ->matching('ProjectEngineeringNodes', function ($q) use ($projectEngineeringNodeMatchCondition) {//节点过滤
                return $q->where($projectEngineeringNodeMatchCondition);
            });

        //待办
        if ($usersProjectEngineeringNodesMatchCondition) {
            $projectEngineeringQuery->matching('ProjectEngineeringNodes.UsersProjectEngineeringNodes', function ($q) use ($usersProjectEngineeringNodesMatchCondition) {//节点过滤
                return $q->where($usersProjectEngineeringNodesMatchCondition);
            });
        }

        // 设置分页页数
        $this->paginate = [
            'limit' => 10,
            'order' => ['ProjectEngineerings.created' => 'desc'],
        ];
        $projectEngineerings = $this->paginate($projectEngineeringQuery);
        //<-构造查询:结合了搜索结果及分页

        //构造显示数据, 节点按类型分组
        $projectEngineeringsView = [];
        foreach ($projectEngineerings as $key => $val) {
            $userNodes = [];//用户节点
            $projectNodes = [];//项目节点
            // 找到所有的节点类型
            $nodeTypes = $this->ProjectNodeTypes->find('list')->toArray();
            foreach ($val['project_engineering_nodes'] as $eachNode) {
                foreach ($nodeTypes as $nodeTypeId => $eachNodeTypeName) {
                    if ($eachNode->project_node_type_id == $nodeTypeId) {
                        $projectNodes[$nodeTypeId][] = $eachNode;
                        if ($eachNode->user_id == $userId) {
                            $userNodes[$nodeTypeId][] = $eachNode;
                        }
                    }
                }
            }

            $projectEngineeringsView[$key][] = $userNodes;
            $projectEngineeringsView[$key][] = $projectNodes;
        }

        //区域
        $materialAreas = $this->loadModel('MaterialAreas')->find('list');
        //节点类型
        $projectNodeTypes = $this->loadModel('ProjectNodeTypes')->find('list')->toArray();

        $this->set(compact('materialAreas', 'realSchMem', 'tmpQuery', 'projectEngineerings', 'userId', 'projectNodeTypes', 'projectEngineeringsView'));
        $this->viewBuilder()->layout('sanse');
    }

主要是将所有的查询构造了一个查询语句：

$projectEngineeringQuery = $this->ProjectEngineerings
            ->find()
            ->contain(['EntityProjects.MaterialAreas', 'ProjectEngineeringNodes', 'ProjectEngineeringNodes.UsersProjectEngineeringNodes'])
            ->select(['id', 'name', 'code', 'area_name' => 'MaterialAreas.name'])
            ->where($projectEngineeringsCondition)//单项工程没有标记有问题
            ->matching('ProjectEngineeringNodes', function ($q) use ($projectEngineeringNodeMatchCondition) {//节点过滤
                return $q->where($projectEngineeringNodeMatchCondition);
            });

最终的SQL：

SELECT 
  ProjectEngineerings.id AS `ProjectEngineerings__id`, 
  ProjectEngineerings.name AS `ProjectEngineerings__name`, 
  ProjectEngineerings.code AS `ProjectEngineerings__code`, 
  MaterialAreas.name AS `area_name` 
FROM 
  project_engineerings ProjectEngineerings 
  INNER JOIN project_engineerings_nodes ProjectEngineeringNodes ON (
    ProjectEngineeringNodes.start_date >= '2018-01-03' 
    AND ProjectEngineeringNodes.end_date < '2018-11-09' 
    AND ProjectEngineeringNodes.user_id = 125 
    AND ProjectEngineeringNodes.single_project_id != 0 
    AND ProjectEngineerings.id = (
      ProjectEngineeringNodes.project_engineerings_id
    )
  ) 
  INNER JOIN users_project_engineering_nodes UsersProjectEngineeringNodes ON (
    UsersProjectEngineeringNodes.user_id = 125 
    AND ProjectEngineeringNodes.id = (
      UsersProjectEngineeringNodes.project_engineer_node_id
    )
  ) 
  LEFT JOIN entity_projects EntityProjects ON EntityProjects.id = (
    ProjectEngineerings.entity_project_id
  ) 
  LEFT JOIN material_areas MaterialAreas ON MaterialAreas.id = (
    EntityProjects.material_area_id
  ) 
WHERE 
  ProjectEngineerings.problem_flag = '0' 
ORDER BY 
  ProjectEngineerings.created desc 
LIMIT 
  10 OFFSET 0