共計 3340 個字符，預(yù)計需要花費 9 分鐘才能閱讀完成。
行業(yè)資訊
數(shù)據(jù)庫
MySQL 數(shù)據(jù)庫
如何理解 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 ,
性能進一步提升:
,
當(dāng)然還有其他部分的子查詢可以進行再優(yōu)化, 這邊也沒需要優(yōu)化到非得 1s 以內(nèi)的, 想要再優(yōu)化, 可以 explain 進行分析
img src= file://D:\Program Files\QQ\qq_info\709837562\Image\C2C\IU5((
MySQL 性能分析及 explain 用法的知識是本文我們主要要介紹的內(nèi)容，接下來就讓我們通過一些實際的例子來介紹這一過程，希望能夠?qū)δ兴鶐椭?/p>
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
一般來說，得保證查詢至少達到 range 級別，最好能達到 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)注丸趣 TV 行業(yè)資訊頻道。
