关于关联查询sql的一次优化过程及其他

如前几次博文中所述，流程结束后的实例信息可以通过统一的入口即高级查询（可以导出excel，也预留了生成各种报表的接口）查询。但对于一些特殊的工作流，比如转

　　如前几次博文中所述，流程结束后的实例信息可以通过统一的入口即高级查询（可以导出excel，也预留了生成各种报表的接口）查询。但对于一些特殊的工作流，比如转正、离职、考勤等我们也提供了专门的查询模块。比如本文中所述的离职模块：离职模块共分三个部分，分别为离职信息新增、审批中离职、已结束离职三个子模块。离职信息新增功能主要是针对被动离职，也即单位劝退、辞退或单方面解除合同的离职信息新增，此类离职一旦保存即可认为是已结束离职，所以不像审批中离职查询逻辑中十分清晰，已结束离职需要关联多表进行查询。在测试系统中进行测试时，我们发现直接执行已结束离职查询sql，，在数据量为17条时，约1s，实际较慢，但尚可接受。该功能在正式系统上线后，离职数据约400条，用户简单在前端计时，约需十余秒等待，用户体验已经极差。拿出该查询sql，如下：

SELECT * FROM (SELECT DISTINCT leaveinfo.id, f_sqrgh, f_sqrbm, f_sqr, f_sqbmbm , f_sqbm, f_lxdhfj, f_sjhm, f_sqrq, f_rzrq , f_ndlzrq, f_qrlzrq, f_zw, f_gw, f_gwlx , f_gwcj, f_szdq, f_gzdd, f_lzyy, f_lzyyzs , f_yggxbmtjl, f_lzlx, f_inputtype, belongCompany, postDirection , techLevel, idCard, staffinfo.sex, staffinfo.birthday, exec.id AS 'processExecutionId' , exec.status AS 'processExecutionStatus', exec.formDefineId, exec.processDefineId, exec.processInstanceId, exec.tableName , process.`name` AS 'processDefineName' FROM T_DYMC_20140625100255 leaveinfo LEFT JOIN t_per_staffinfo staffinfo ON staffinfo.staffId = leaveinfo.f_sqrgh LEFT JOIN t_bpm_process_execution exec ON exec.pkValue = leaveinfo.id LEFT JOIN t_bpm_process_define process ON process.id = exec.processDefineId WHERE leaveinfo.f_sqrgh = staffinfo.staffId AND (exec.`status` = 2 AND leaveinfo.f_inputtype = 'FLOW' OR leaveinfo.f_inputtype = 'MANUAL') ) allData LEFT JOIN t_sys_user sysUser ON allData.f_sqrgh = sysUser.staffId

　　这是一个分页查询，查询出所有结果的数量，如下：

SELECT COUNT(DISTINCT allData.id) FROM (SELECT DISTINCT leaveinfo.id, leaveinfo.f_sqrgh FROM T_DYMC_20140625100255 leaveinfo LEFT JOIN t_per_staffinfo staffinfo ON staffinfo.staffId = leaveinfo.f_sqrgh LEFT JOIN t_bpm_process_execution exec ON exec.pkValue = leaveinfo.id LEFT JOIN t_bpm_process_define process ON process.id = exec.processDefineId WHERE leaveinfo.f_sqrgh = staffinfo.staffId AND (exec.`status` = 2 AND leaveinfo.f_inputtype = 'FLOW' OR leaveinfo.f_inputtype = 'MANUAL') ) allData LEFT JOIN t_sys_user sysUser ON allData.f_sqrgh = sysUser.staffId

　　

　　去掉这一关联，sql的效率有所改善，但改善并不明显。从逻辑角度我们已经没有优化的空间。所以希望从数据库技术角度去进行优化。在着手进行优化之前，我们先看一看当前语句已经使用的优化技术（对于非专业DBA首先可以想到的优化一般是index），而在mysql里提供了explain来查询mysql如何使用索引来处理select语句以及连接表。下面，我们看看在未优化之前，在该查询语句是不是有用优化技术，又使用了哪些优化技术。在未进行优化之前，我们已经有了针对档案和用户两张表的staffid的索引，查询索引的sql语句如下：

show index from t_per_staffinfo

　　如下图：

　　查询语句中还有两张表分别为t_bpm_process_define和t_bpm_process_execution，我们为其创建索引，希望加入索引后查询效率有所改善：

ALTER TABLE t_bpm_process_execution ADD INDEX pkValue_index (pkValue);

　　类似的我们为状态status，以及t_bpm_process_define也加入了索引。

现在我们用explain看看我们目前的查询语句，如下图：

　　基于上图我们看一下，使用explain查出的信息中的各列的含义，顾名思义，我们看下来，table指的是查询的表名、type指的是连接使用的哪种类型（从好到差的连接类型依次是const、eq_reg、ref、range、index、all）、possible_key表示可能使用在该表中的索引、key指的是在本次查询中实际使用到的索引（如果值为null表示没有使用索引，mysql在很少情况下会使用未优化的索引，但也可以使用using idex强制使用索引）、key_len表示索引长度（在不损失精度的前提下，长度越短越好）、ref则是哪一列使用了索引、rows是MySQL认为需要检查的用来请求返回数据的长度、Extra表示关于解析查询的额外信息。通过分析Extra，我们可以看出哪些index需要优化以及如何优化。