如何理解mysql5.6版本的致命點以及兩表關聯的字段類型相同的重要性

本篇文章為大家展示了如何理解mysql5.6版本的致命點以及兩表關聯的字段類型相同的重要性，內容簡明扼要并且容易理解，絕對能使你眼前一亮，通過這篇文章的詳細介紹希望你能有所收獲。

背景:  mysql 版本 5.6
由于我一個同事在做一個報表的時候,關聯的兩張表都有建索引,但是由于發現字段類型不同而導致雖然兩張表只有幾十萬級別的兩張表只關聯了7天的數據(查出來的結果其實只有1294條)竟然要關聯出近500S的時間,所以這個是當時設計數據庫表的時候沒設計好,然后挖的坑.版本5.7以上的好像會自動轉化成一樣的字符類型(在另外一個版本5.7以上的mysql 數據庫試下這種字段類型不同的發現依然會走索引)
發現此問題之后,我做了個轉化:
SELECT n.order_code .....(中間的腳本省略)
(select sum(re.order_rebate) from  t_bm_order_return re where re.order_id=concat(n.order_id,'') and  re.state_result='成功' and re.activity_no not in('商家慢必賠')) rebate_amount
FROM np_order n left join web114_order_ledger wol on n.order_id=wol.order_id   .............
        WHERE   
  n.create_time >= '2017-05-12'
 AND n.create_time < '2017-05-18' ;
主要是主表np_order  和 t_bm_order_return  的關聯,concat(n.order_id,'') 轉化成字符類型<img src="file://D:\Program Files\QQ\qq_info\709837562\Image\C2C\IU5((,時間大大的縮短:


但是這個還不是最優化的:后面又發現關聯表那邊有這樣的一個語句:
FROM np_order n left join web114_order_ledger wol on n.order_id=wol.order_id,
        (SELECT *  FROM (SELECT MAX(og.order_goods_id) ogd,SUM(goods_info_num) gin FROM np_order_goods og GROUP BY og.order_id) t
        LEFT JOIN np_order_goods ogs ON t.ogd=ogs.order_goods_id) nog
他這種寫法可以等價于:
FROM np_order n left join web114_order_ledger wol on n.order_id=wol.order_id,
        (SELECT og.order_id, og.goods_id, SUM(goods_info_num) gin
          FROM np_order_goods og
         GROUP BY og.order_id,og.goods_id) nog

這樣子的一個寫法 ,時間又少了6s:


還有select from 里面的子查詢中有兩條語句:
 (SELECT bos.shopname FROM web114_bm_outline_shop bos WHERE bos.shopcode=(SELECT osi.shopcode FROM web114_order_shop_info osi WHERE n.order_id=osi.order_id LIMIT 0,1)) order_shopname,
        (SELECT bos.isdn FROM web114_bm_outline_shop bos WHERE bos.shopcode=(SELECT osi.shopcode FROM web114_order_shop_info osi WHERE n.order_id=osi.order_id LIMIT 0,1)) order_shop_isdn,
這兩條語句其實可以提到 from 后面 跟主表直接關聯,也可以改成這樣:
 (SELECT bos.shopname FROM web114_bm_outline_shop bos ,web114_order_shop_info osi  WHERE bos.shopcode= osi.shopcode and  n.order_id=osi.order_id LIMIT 0,1) order_shopname,
(SELECT bos.isdn FROM web114_bm_outline_shop bos ,web114_order_shop_info osi WHERE bos.shopcode= osi.shopcode and  n.order_id=osi.order_id LIMIT 0,1) order_shop_isdn , 

性能進一步提升:
,
當然還有其他部分的子查詢可以進行再優化,這邊也沒需要優化到非得1s以內的,想要再優化,可以explain 進行分析

<img src="file://D:\Program Files\QQ\qq_info\709837562\Image\C2C\IU5((

MySQL性能分析及explain用法的知識是本文我們主要要介紹的內容，接下來就讓我們通過一些實際的例子來介紹這一過程，希望能夠對您有所幫助。

1.使用explain語句去查看分析結果

如explain select * from test1 where id=1;會出現：id  selecttype  table  type possible_keys  key key_len  ref rows  extra各列。

其中，
type=const表示通過索引一次就找到了；
key=primary的話，表示使用了主鍵；
type=all,表示為全表掃描；
key=null表示沒用到索引。type=ref,因為這時認為是多個匹配行，在聯合查詢中，一般為REF。

各個屬性的含義
id

select查詢的序列號

select_type

select查詢的類型，主要是區別普通查詢和聯合查詢、子查詢之類的復雜查詢。

table

輸出的行所引用的表。

type

聯合查詢所使用的類型。

type顯示的是訪問類型，是較為重要的一個指標，結果值從好到壞依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般來說，得保證查詢至少達到range級別，最好能達到ref。

possible_keys

指出MySQL能使用哪個索引在該表中找到行。如果是空的，沒有相關的索引。這時要提高性能，可通過檢驗WHERE子句，看是否引用某些字段，或者檢查字段不是適合索引。

key

顯示MySQL實際決定使用的鍵。如果沒有索引被選擇，鍵是NULL。

key_len

顯示MySQL決定使用的鍵長度。如果鍵是NULL，長度就是NULL。文檔提示特別注意這個值可以得出一個多重主鍵里mysql實際使用了哪一部分。

ref

顯示哪個字段或常數與key一起被使用。
rows
這個數表示mysql要遍歷多少數據才能找到，在innodb上是不準確的。

Extra 

如果是Only index，這意味著信息只用索引樹中的信息檢索出的，這比掃描整個表要快。
如果是where used，就是使用上了where限制。
如果是impossible where 表示用不著where，一般就是沒查出來啥。
如果此信息顯示Using filesort或者Using temporary的話會很吃力，WHERE和ORDER BY的索引經常無法兼顧，如果按照WHERE來確定索引，那么在ORDER BY時，就必然會引起Using filesort，這就要看是先過濾再排序劃算，還是先排序再過濾劃算。

上述內容就是如何理解mysql5.6版本的致命點以及兩表關聯的字段類型相同的重要性，你們學到知識或技能了嗎？如果還想學到更多技能或者豐富自己的知識儲備，歡迎關注億速云行業資訊頻道。