如前幾次博文中所述，流程結(jié)束后的實(shí)例信息可以通過統(tǒng)一的入口即高級(jí)查詢（可以導(dǎo)出excel，也預(yù)留了生成各種報(bào)表的接口）查詢。但對(duì)于一些特殊的工作流，比如轉(zhuǎn)正、離職、考勤等我們也提供了專門的查詢模塊。比如本文中所述的離職模塊：離職模塊共分三個(gè)部分，分別為離職信息新增、審批中離職、已結(jié)束離職三個(gè)子模塊。離職信息新增功能主要是針對(duì)被動(dòng)離職，也即單位勸退、辭退或單方面解除合同的離職信息新增，此類離職一旦保存即可認(rèn)為是已結(jié)束離職，所以不像審批中離職查詢邏輯中十分清晰，已結(jié)束離職需要關(guān)聯(lián)多表進(jìn)行查詢。在測(cè)試系統(tǒng)中進(jìn)行測(cè)試時(shí)，我們發(fā)現(xiàn)直接執(zhí)行已結(jié)束離職查詢sql，在數(shù)據(jù)量為17條時(shí)，約1s，實(shí)際較慢，但尚可接受。該功能在正式系統(tǒng)上線后，離職數(shù)據(jù)約400條，用戶簡(jiǎn)單在前端計(jì)時(shí)，約需十余秒等待，用戶體驗(yàn)已經(jīng)極差。拿出該查詢sql，如下：

SELECT * 
 (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 

這是一個(gè)分頁(yè)查詢，查詢出所有結(jié)果的數(shù)量，如下：

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 

在測(cè)試系統(tǒng)我們對(duì)兩條sql在17條數(shù)據(jù)時(shí)分別進(jìn)行了測(cè)試，耗時(shí)都在0.5s以下。但在正式系統(tǒng)，測(cè)試時(shí)數(shù)據(jù)量398條時(shí)，***條的執(zhí)行時(shí)間約為9.313s，第二條耗時(shí)約4.341s。

顯然，398條數(shù)據(jù)僅查詢就超過10s顯然超過了用戶的忍耐，大大影響了系統(tǒng)的性能，在用戶體驗(yàn)大打折扣。

首先我們梳理一下sql，以***條為例，我們關(guān)聯(lián)查詢了多張表，而這多張表是否必要，是否有從邏輯角度優(yōu)化的可能。

我們查詢的主表是離職信息表，關(guān)聯(lián)了檔案、運(yùn)行、流程定義三張表，***又增加了前文提出的數(shù)據(jù)權(quán)限限制，關(guān)聯(lián)到用戶表。關(guān)聯(lián)檔案我們是希望通過檔案查詢出離職人員的信息，關(guān)聯(lián)運(yùn)行表信息則是希望查詢出當(dāng)前辦理者和當(dāng)前辦理階段，關(guān)于流程定義表則是希望查詢出流程定義的名稱。經(jīng)過分析，我們首先發(fā)現(xiàn)這個(gè)sql是工程師從高級(jí)查詢里照搬過來的，因?yàn)楦呒?jí)查詢應(yīng)用于所有流程，流程名需要通過processDefineId查詢，而我們的離職查詢，就是查詢的離職流程，不需要再關(guān)聯(lián)一張表去查詢。我們將這一關(guān)聯(lián)去掉，直接返回"離職流程" as processDefineName。

去掉這一關(guān)聯(lián)，sql的效率有所改善，但改善并不明顯。從邏輯角度我們已經(jīng)沒有優(yōu)化的空間。所以希望從數(shù)據(jù)庫(kù)技術(shù)角度去進(jìn)行優(yōu)化。在著手進(jìn)行優(yōu)化之前，我們先看一看當(dāng)前語句已經(jīng)使用的優(yōu)化技術(shù)（對(duì)于非專業(yè)DBA首先可以想到的優(yōu)化一般是index），而在mysql里提供了explain來查詢mysql如何使用索引來處理select語句以及連接表。下面，我們看看在未優(yōu)化之前，在該查詢語句是不是有用優(yōu)化技術(shù)，又使用了哪些優(yōu)化技術(shù)。在未進(jìn)行優(yōu)化之前，我們已經(jīng)有了針對(duì)檔案和用戶兩張表的staffid的索引，查詢索引的sql語句如下：

show index from t_per_staffinfo 

如下圖：

以及user表的索引：

查詢語句中還有兩張表分別為t_bpm_process_define和t_bpm_process_execution，我們?yōu)槠鋭?chuàng)建索引，希望加入索引后查詢效率有所改善：

ALTER TABLE t_bpm_process_execution ADD INDEX pkValue_index (pkValue); 

類似的我們?yōu)闋顟B(tài)status，以及t_bpm_process_define也加入了索引。

現(xiàn)在我們用explain看看我們目前的查詢語句，如下圖：

基于上圖我們看一下，使用explain查出的信息中的各列的含義，顧名思義，我們看下來，table指的是查詢的表名、type指的是連接使用的哪種類型（從好到差的連接類型依次是const、eq_reg、ref、range、index、all）、possible_key表示可能使用在該表中的索引、key指的是在本次查詢中實(shí)際使用到的索引（如果值為null表示沒有使用索引，mysql在很少情況下會(huì)使用未優(yōu)化的索引，但也可以使用using idex強(qiáng)制使用索引）、key_len表示索引長(zhǎng)度（在不損失精度的前提下，長(zhǎng)度越短越好）、ref則是哪一列使用了索引、rows是MySQL認(rèn)為需要檢查的用來請(qǐng)求返回?cái)?shù)據(jù)的長(zhǎng)度、Extra表示關(guān)于解析查詢的額外信息。通過分析Extra，我們可以看出哪些index需要優(yōu)化以及如何優(yōu)化。

Extra的值有Distinct、Not Exist、Range cheched for each record、using filesort、using temporary、Using index、where used、system、const、eq_ref、ref、index、all。當(dāng)出現(xiàn)using filesort（需要額外的步驟進(jìn)行排序）、using temporary（需要臨時(shí)表存儲(chǔ)中間結(jié)果）時(shí)表示查詢需要進(jìn)行優(yōu)化。

由圖中我們可以看出，一些索引還需要進(jìn)一步優(yōu)化，但我們查詢的速率已經(jīng)由近10s縮減為0.088s。對(duì)于非專業(yè)的DBA這次優(yōu)化已經(jīng)算是成功了。優(yōu)化到此結(jié)束，關(guān)于更進(jìn)一步優(yōu)化using temporary的問題我會(huì)進(jìn)一步與DBA溝通，將優(yōu)化進(jìn)行到底。

接下來，我們談一下查詢的基礎(chǔ)理論、索引對(duì)于查詢的改善和和索引的基礎(chǔ)知識(shí)。

對(duì)于MySQL的查詢機(jī)制，MySQL manual(7.2.1)中一段這樣的描述：

我們從第三句開始做一下簡(jiǎn)單的翻譯：Mysql從***張表讀取***行數(shù)據(jù)，然后在第二張表中查找匹配行，然后在查找第三張表，以此類推。當(dāng)所有表處理完畢，Mysql輸出選中的列然后回溯表的列表一直到能夠匹配更多行的表出現(xiàn)。從這張表中讀取下一行，然后繼續(xù)查詢下一張表。這個(gè)關(guān)聯(lián)查詢的過程的關(guān)鍵就是從上一張表來查詢當(dāng)前表的內(nèi)容。

了解到從上一張表查詢當(dāng)前表的原理后，我們創(chuàng)建index的目的就是告訴MySQL如何直接查詢下一張表的數(shù)據(jù)，以及如何按照需要的順序來join下一張表。

上文中我們也介紹了查看和創(chuàng)建索引的語句，更進(jìn)一步了解其他操作方法可以查看一些關(guān)于索引的基礎(chǔ)知識(shí)。