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

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

背景:  mysql 版本 5.6
由于我一個同事在做一個報表的時候,關(guān)聯(lián)的兩張表都有建索引,但是由于發(fā)現(xiàn)字段類型不同而導(dǎo)致雖然兩張表只有幾十萬級別的兩張表只關(guān)聯(lián)了7天的數(shù)據(jù)(查出來的結(jié)果其實只有1294條)竟然要關(guān)聯(lián)出近500S的時間,所以這個是當(dāng)時設(shè)計數(shù)據(jù)庫表的時候沒設(shè)計好,然后挖的坑.版本5.7以上的好像會自動轉(zhuǎn)化成一樣的字符類型(在另外一個版本5.7以上的mysql 數(shù)據(jù)庫試下這種字段類型不同的發(fā)現(xiàn)依然會走索引)
發(fā)現(xiàn)此問題之后,我做了個轉(zhuǎn)化:
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  的關(guān)聯(lián),concat(n.order_id,'') 轉(zhuǎn)化成字符類型<img src="file://D:\Program Files\QQ\qq_info\709837562\Image\C2C\IU5((,時間大大的縮短:


但是這個還不是最優(yōu)化的:后面又發(fā)現(xiàn)關(guān)聯(lián)表那邊有這樣的一個語句:
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 后面 跟主表直接關(guān)聯(lián),也可以改成這樣:
 (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 , 

性能進(jìn)一步提升:
,
當(dāng)然還有其他部分的子查詢可以進(jìn)行再優(yōu)化,這邊也沒需要優(yōu)化到非得1s以內(nèi)的,想要再優(yōu)化,可以explain 進(jìn)行分析

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

MySQL性能分析及explain用法的知識是本文我們主要要介紹的內(nèi)容，接下來就讓我們通過一些實際的例子來介紹這一過程，希望能夠?qū)δ兴鶐椭?br/>
1.使用explain語句去查看分析結(jié)果

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

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

各個屬性的含義
id

select查詢的序列號

select_type

select查詢的類型，主要是區(qū)別普通查詢和聯(lián)合查詢、子查詢之類的復(fù)雜查詢。

table

輸出的行所引用的表。

type

聯(lián)合查詢所使用的類型。

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

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

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

possible_keys

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

key

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

key_len

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

ref

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

Extra 

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

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