一個MySQL視圖的優化過程_ZenDei技術網路在線

一個mySQL視圖的優化，從索引到union all，再到存儲過程。 ...

1.需要優化的sql

最近做一個基於.net mvc和MySQL的倉儲系統的優化工作，遇到了一個執行特別慢的SQL語句，經過一番折騰，終於搞定啦，分享一下過程。問題就是下麵這個家伙：

create or replace view view_task_meter_info
as
select t1.TASK_ID,t1.task_no,t1.BINDBOX_BARCODE as box_barcode,t1.EQUIP_BAR_CODE,t1.METER_STATUS,t1.ENTITY_TYPE as RSLT_CODE,
-- 設備類別
(SELECT name from data_dictionary_info t01 where t01.domain ='設備類別' and t01.code = t3.EQUIP_CATEG) as T_Equip_categ,
-- 類別
(select name from data_dictionary_info t09 where t09.domain = '類型' and t09.code = t3.TYPE_CODE) as 
T_TYPE_CODE,
-- 類型
(select name from data_dictionary_info t09 where t09.domain = '類別' and t09.code = t3.RATED_CURRENT) as T_SORT_CODE,
t3.EQUIP_CATEG,t3.TYPE_CODE,t3.SORT_CODE
from
data_task_asset t1 left join data_meter_info t3 on t1.EQUIP_BAR_CODE=t3.BAR_CODE
union all 
select t1.CHK_TASK_ID as task_id,t1.TASK_NO,t1.BOX_BARCODE,t1.BAR_CODE as Equip_bar_code,'00' as METER_STATUS,t1.RSLT_CODE,
-- 設備類別
(SELECT name from data_dictionary_info t01 where t01.domain ='設備類別' and t01.code = t3.EQUIP_CATEG) as T_Equip_categ,
-- 類別
(select name from data_dictionary_info t09 where t09.domain = '類型' and t09.code = t3.TYPE_CODE) as 
T_TYPE_CODE,
-- 類型
(select name from data_dictionary_info t09 where t09.domain = '類別' and t09.code = t3.RATED_CURRENT) as T_SORT_CODE,
t3.EQUIP_CATEG,t3.TYPE_CODE,t3.SORT_CODE
from data_check_asset_info t1
LEFT JOIN data_meter_info t3 on t1.BAR_CODE = t3.BAR_CODE

解釋一下業務：

倉儲系統業務分為三塊:出入庫、盤點。data_task_asset是出入庫任務資產明細，data_check_asset_info是盤點任務明細，data_meter_info是資產檔案表。data_task_asset和data_check_asset_info表都使用資產條碼(EQUIP_BAR_CODE/BAR_CODE)和資產檔案表關聯(BAR_CODE是檔案表主鍵)。這個視圖的業務意義就是展示出入庫、盤點任務的資產明細(包括檔案信息),同時需要把檔案信息裡面的大量代碼欄位翻譯成文字信息。上面視圖中只列出3個欄位作為示例，實際上需要翻譯的欄位有十幾個。

這個視圖剛開始沒有感覺慢，但是有一天測試做了一個7萬多條明細的盤點任務後，每次查詢一個任務的明細都要等上十幾到幾十秒，是在難以忍受。開工吧，先診斷一下。

2.查看執行情況

下麵是這個視圖在查詢那個7萬多條明細的盤點任務的執行時間，太可怕了，三十多秒。

看看執行計劃

額滴神呀，這可肯定不行呀，每個欄位的翻譯都要查詢字典表的1207條記錄，一個記錄需要翻譯10次，7萬條記錄，需要查詢字典表70萬次，每次搜索記錄1000多條，這個當然不行啦。

3.在字典表上加索引！！！

根據查詢字典表的sql語句，我們在domain和code上加聯合索引

SELECT name from data_dictionary_info t01 where t01.domain ='設備類別' and t01.code = t3.EQUIP_CATEG

來看現在的執行情況

執行時間：
執行時間一下子降到了兩三秒，效果顯著呀！

執行計劃：
看看執行計劃你也許就不吃驚啦，建立索引後每次查詢字典表，只搜索一條記錄。

4.再加兩個索引

既然索引這麼厲害，那就繼續加索引唄，可以看到視圖在查詢出入庫和盤點任務明細表時，也是全表查詢。我們加個索引看看效果如何，我分別在data_task_asset的task_id和task_no、data_check_asset_info的chk_task_id和task_no上添加了聯合索引。下麵看看執行情況：

執行時間：
執行時間好像比剛纔還長了一點，這就不合心意啦。

執行計劃：
從執行計劃來看，查詢根本就沒有用到索引，why?

5.mysql視圖演算法及不使用索引的情況

普及一些百度知識：

當用戶創建視圖時，mysql預設使用一種undefine的處理演算法，就是會自動在合併和臨時表內進行選擇

對於MERGE，會將引用視圖的語句的文本與視圖定義合併起來，使得視圖定義的某一部分取代語句的對應部分。
對於TEMPTABLE，視圖的結果將被置於臨時表中，然後使用它執行語句。
對於UNDEFINED，MySQL將選擇所要使用的演算法。如果可能，它傾向於MERGE而不是TEMPTABLE，這是因為MERGE通常更有效，而且如果使用了臨時表，視圖是不可更新。

對於使用MERGE演算法處理的視圖，可以使用索引。但是，對於使用臨時表演算法處理的視圖，不能在其基表上利用索引提供的優點。MERGE演算法要求視圖中的行和基表中的行具有一對一的關係。如果不具有該關係。必須使用臨時表取而代之。如果視圖包含下述結構中的任何一種，將失去一對一的關係：

聚合函數（SUM(),MIN(),MAX(),COUNT()等)
DISTINCT
GROUP BY
HAVING
UNION或UNION ALL

通常的不使用索引的查詢

如果MySQL估計使用索引比全表掃描更慢，則不使用索引。例如，如果列key均勻分佈在1和100之間，下麵的查詢使用索引就不是很好：select * from table_name where key>1 and key<90;
如果使用MEMORY/HEAP表，並且where條件中不使用“=”進行索引列，那麼不會用到索引，head表只有在“=”的條件下才會使用索引
用or分隔開的條件，如果or前的條件中的列有索引，而後面的列沒有索引，那麼涉及到的索引都不會被用到，例如：select * from table_name where key1='a' or key2='b';如果在key1上有索引而在key2上沒有索引，則該查詢也不會走索引
複合索引，如果索引列不是複合索引的第一部分，則不使用索引（即不符合最左首碼），例如，複合索引為(key1,key2),則查詢select * from table_name where key2='b';將不會使用索引
如果like是以‘%’開始的，則該列上的索引不會被使用。例如select * from table_name where key1 like '%a'；該查詢即使key1上存在索引，也不會被使用
如果列為字元串，則where條件中必須將字元常量值加引號，否則即使該列上存在索引，也不會被使用。例如,select * from table_name where key1=1;如果key1列保存的是字元串，即使key1上有索引，也不會被使用。

6.幹掉union all

為了證明確實是union all影響了索引的使用，我們去掉視圖中的union all，讓視圖只負責查詢盤點任務的明細及檔案信息，看看效果如何。

-執行時間：
時間又比剛纔短了一秒鐘，不錯，不錯。

-執行計劃
從執行計劃可以看出，這次用到了明細表的索引。

7.存儲過程使用

我們看到取到union all之後，明細表的索引在查詢中被使用。儘管我們從查詢的時間上感覺明細表使用索引和不使用索引沒有太大差別。但這其實只是數據太少反映不出問題，隨著明細表數據的增多，有索引時每個任務搜索的記錄數只與明細數量有關；而無索引時，每個任務明細查詢是全表搜索。所以，union all必須去掉。

那麼問題來啦，程式結構已經基本定型，單個視圖必須使用union all。這裡有兩種方案：

分開為兩個視圖，出入庫明細一個視圖，盤點明細一個視圖，在程式中控制使用不同視圖；
使用存儲過程，在存儲過程中判斷查詢出入庫明細還是盤點明細。

在Entity Framework中使用存儲過程還沒嘗試過，就用存儲過程啦：

mysql存儲過程代碼：

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_task_meter_info`(IN `taskId` DECIMAL(16,0), IN `taskNo` VARCHAR(32), IN `ioFlag` VARCHAR(8))
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
if ioFlag = '盤點' then
-- 返回盤點明細
select * from ...
where ...
else
-- 返回出入庫明細
select * from ...
where ...
end if;
END

Entity Framework調用存儲過程(看上去也挺方便的):

var dataListProc =
                DbContext.Database.SqlQuery<view_task_meter_info>(
                    string.Format("CALL `sp_task_meter_info`({0}, '{1}','{2}')",searchModel.task_id,searchModel.task_no,searchModel.task_type)).ToList();

8.疑問

總感覺自己的翻譯代碼欄位有點太費時，不知各位園友是怎麼處理這種問題的。

好了，就到這裡啦。這篇博客其實早該發出來的，因為一些耽擱，今天總算趕出來啦。我感覺每次要寫一篇博客前，總感覺有好多東西要說，可是很多時候趕緊文章都寫不流暢啦。動不動就想分條陳述，動不動就想來個問題原因，解決方案。而且一旦不能集中時間寫完，再迴首已是食之無味。成了個整天宥在自己項目、任務圈裡的程式猿啦。這不行！