《Mysql必讀MySQL在關(guān)聯(lián)復(fù)雜情況下所能做出的一些優(yōu)化》要點(diǎn)：
本文介紹了Mysql必讀MySQL在關(guān)聯(lián)復(fù)雜情況下所能做出的一些優(yōu)化，希望對您有用。如果有疑問，可以聯(lián)系我們。
MYSQL數(shù)據(jù)庫昨天處理了一則復(fù)雜關(guān)聯(lián)SQL的優(yōu)化,這類SQL的優(yōu)化往往考慮以下四點(diǎn)：
MYSQL數(shù)據(jù)庫??? 第一.查詢所返回的結(jié)果集,通常查詢返回的結(jié)果集很少,是有信心進(jìn)行優(yōu)化的；
MYSQL數(shù)據(jù)庫??? 第二.驅(qū)動表的選擇至關(guān)重要,通過查看執(zhí)行計(jì)劃,可以看到優(yōu)化器選擇的驅(qū)動表,從執(zhí)行計(jì)劃中的rows可以大致反映出問題的所在；
MYSQL數(shù)據(jù)庫??? 第三.理清各表之間的關(guān)聯(lián)關(guān)系,注意關(guān)聯(lián)字段上是否有合適的索引；
MYSQL數(shù)據(jù)庫??? 第四.使用straight_join關(guān)鍵詞來強(qiáng)制表之間的關(guān)聯(lián)順序,可以方便我們驗(yàn)證某些猜想；
MYSQL數(shù)據(jù)庫SQL:
執(zhí)行時(shí)間：
MYSQL數(shù)據(jù)庫
mysql> select c.yh_id,
-> c.yh_dm,
-> c.yh_mc,
-> c.mm,
-> c.yh_lx,
-> a.jg_id,
-> a.jg_dm,
-> a.jg_mc,
-> a.jgxz_dm,
-> d.js_dm yh_js
-> from a, b, c
-> left join d on d.yh_id = c.yh_id
-> where a.jg_id = b.jg_id
-> and b.yh_id = c.yh_id
-> and a.yx_bj = ‘Y'
-> and c.sc_bj = ‘N'
-> and c.yx_bj = ‘Y'
-> and c.sc_bj = ‘N'
-> and c.yh_dm = '006939748XX' ;
1 row in set (0.75 sec)
MYSQL數(shù)據(jù)庫這條SQL查詢實(shí)際只返回了一行數(shù)據(jù),但卻執(zhí)行耗費(fèi)了750ms,查看執(zhí)行計(jì)劃：
MYSQL數(shù)據(jù)庫
mysql> explain
-> select c.yh_id,
-> c.yh_dm,
-> c.yh_mc,
-> c.mm,
-> c.yh_lx,
-> a.jg_id,
-> a.jg_dm,
-> a.jg_mc,
-> a.jgxz_dm,
-> d.js_dm yh_js
-> from a, b, c
-> left join d on d.yh_id = c.yh_id
-> where a.jg_id = b.jg_id
-> and b.yh_id = c.yh_id
-> and a.yx_bj = ‘Y'
-> and c.sc_bj = ‘N'
-> and c.yx_bj = ‘Y'
-> and c.sc_bj = ‘N'
-> and c.yh_dm = '006939748XX' ;
+―-+――――-+――-+――C+――――――+―――+―――+――――C+――-+――――-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+―-+――――-+――-+――C+――――――+―――+―――+――――C+――-+――――-+
| 1 | SIMPLE | a | ALL | PRIMARY,INDEX_JG | NULL | NULL | NULL | 52616 | Using where |
| 1 | SIMPLE | b | ref | PRIMARY | PRIMARY | 98 | test.a.JG_ID | 1 | Using index |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 98 | test.b.YH_ID | 1 | Using where |
| 1 | SIMPLE | d | index | NULL | PRIMARY | 196 | NULL | 54584 | Using index |
+―-+――――-+――-+――C+――――――+―――+―――+――――C+――-+――――-+
MYSQL數(shù)據(jù)庫可以看到執(zhí)行計(jì)劃中有兩處比較顯眼的性能瓶頸：
MYSQL數(shù)據(jù)庫
| 1 | SIMPLE | a | ALL | PRIMARY,INDEX_JG | NULL | NULL | NULL | 52616 | Using where |
| 1 | SIMPLE | d | index | NULL | PRIMARY | 196 | NULL | 54584 | Using index |
MYSQL數(shù)據(jù)庫由于d是left join的表,所以驅(qū)動表不會選擇d表,我們在來看看a,b,c三表的大小：
MYSQL數(shù)據(jù)庫
mysql> select count(*) from c;
+―――-+
| count(*) |
+―――-+
| 53731 |
+―――-+
mysql> select count(*) from a;
+―――-+
| count(*) |
+―――-+
| 53335 |
+―――-+
mysql> select count(*) from b;
+―――-+
| count(*) |
+―――-+
| 105809 |
+―――-+
MYSQL數(shù)據(jù)庫由于b表的數(shù)據(jù)量大于其他的兩表,同時(shí)b表上基本沒有查詢過濾條件,所以驅(qū)動表選擇B的可能排除；
MYSQL數(shù)據(jù)庫優(yōu)化器實(shí)際選擇了a表作為驅(qū)動表,而為什么不是c表作為驅(qū)動表?我們來分析一下：
MYSQL數(shù)據(jù)庫第一階段：a表作為驅(qū)動表
aC>bC>cC>d:
(1):a.jg_id=b.jg_id―>(b索引:PRIMARY KEY (`JG_ID`,`YH_ID`) )
MYSQL數(shù)據(jù)庫(2):b.yh_id=c.yh_id―>(c索引:PRIMARY KEY (`YH_ID`))
MYSQL數(shù)據(jù)庫(3):c.yh_id=d.yh_id―>(d索引:PRIMARY KEY (`JS_DM`,`YH_ID`))
由于d表上沒有yh_id的索引,索引在d表上添加索引：
MYSQL數(shù)據(jù)庫
alter table d add index ind_yh_id(yh_id);
MYSQL數(shù)據(jù)庫執(zhí)行計(jì)劃：
MYSQL數(shù)據(jù)庫
+―-+――――-+――-+――C+――――――+―――C+―――+――――C+――-+――――-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+―-+――――-+――-+――C+――――――+―――C+―――+――――C+――-+――――-+
| 1 | SIMPLE | a | ALL | PRIMARY,INDEX_JG | NULL | NULL | NULL | 52616 | Using where |
| 1 | SIMPLE | b | ref | PRIMARY | PRIMARY | 98 | test.a.JG_ID | 1 | Using index |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 98 | test.b.YH_ID | 1 | Using where |
| 1 | SIMPLE | d | ref | ind_yh_id | ind_yh_id | 98 | test.b.YH_ID | 272 | Using index |
+―-+――――-+――-+――C+――――――+―――C+―――+――――C+――-+――――-+
MYSQL數(shù)據(jù)庫執(zhí)行時(shí)間：
MYSQL數(shù)據(jù)庫
1 row in set (0.77 sec)
MYSQL數(shù)據(jù)庫在d表上添加索引后,d表的掃描行數(shù)下降到272行(最開始為：54584 )
MYSQL數(shù)據(jù)庫
| 1 | SIMPLE | d | ref | ind_yh_id | ind_yh_id | 98 | test.b.YH_ID | 272 | Using index |
MYSQL數(shù)據(jù)庫第二階段：c表作為驅(qū)動表
MYSQL數(shù)據(jù)庫d
^
|
cC>bC>a
由于在c表上有yh_dm過濾性很高的篩選條件,所以我們在yh_dm上創(chuàng)建一個索引：
MYSQL數(shù)據(jù)庫
mysql> select count(*) from c where yh_dm = '006939748XX';
+―――-+
| count(*) |
+―――-+
| 2 |
+―――-+
MYSQL數(shù)據(jù)庫添加索引：
MYSQL數(shù)據(jù)庫
alter table c add index ind_yh_dm(yh_dm)
MYSQL數(shù)據(jù)庫查看執(zhí)行計(jì)劃：
MYSQL數(shù)據(jù)庫
+―-+――――-+――-+――C+――――――-+―――C+―――+――――C+――-+――――-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+―-+――――-+――-+――C+――――――-+―――C+―――+――――C+――-+――――-+
| 1 | SIMPLE | a | ALL | PRIMARY,INDEX_JG | NULL | NULL | NULL | 52616 | Using where |
| 1 | SIMPLE | b | ref | PRIMARY | PRIMARY | 98 | test.a.JG_ID | 1 | Using index |
| 1 | SIMPLE | c | eq_ref | PRIMARY,ind_yh_dm | PRIMARY | 98 | test.b.YH_ID | 1 | Using where |
| 1 | SIMPLE | d | ref | ind_yh_id | ind_yh_id | 98 | test.b.YH_ID | 272 | Using index |
+―-+――――-+――-+――C+――――――-+―――C+―――+――――C+――-+――――-+
MYSQL數(shù)據(jù)庫執(zhí)行時(shí)間：
MYSQL數(shù)據(jù)庫
1 row in set (0.74 sec)
MYSQL數(shù)據(jù)庫在c表上添加索引后,索引還是沒有走上,執(zhí)行計(jì)劃還是以a表作為驅(qū)動表,所以我們這里來分析一下為什么還是以a表作為驅(qū)動表?
MYSQL數(shù)據(jù)庫1):c.yh_id=b.yh_id―>( PRIMARY KEY (`JG_ID`,`YH_ID`) )
MYSQL數(shù)據(jù)庫a.如果以c表為驅(qū)動表,則c表與b表在關(guān)聯(lián)的時(shí)候,由于在b表沒有yh_id字段的索引,由于b表的數(shù)據(jù)量很大,所以優(yōu)化器認(rèn)為這里如果以c表作為驅(qū)動表,則會與b表產(chǎn)生較大的關(guān)聯(lián)(這里可以使用straight_join強(qiáng)制使用c表作為驅(qū)動表)；
b.如果以a表為驅(qū)動表,則a表與b表在關(guān)聯(lián)的時(shí)候,由于在b表上有jg_id字段的索引,所以優(yōu)化器認(rèn)為以a作為驅(qū)動表的代價(jià)是小于以c作為驅(qū)動板的代價(jià)；
所以我們?nèi)绻訡表為驅(qū)動表,只需要在b上添加yh_id的索引：
MYSQL數(shù)據(jù)庫
alter table b add index ind_yh_id(yh_id);
MYSQL數(shù)據(jù)庫2):b.jg_id=a.jg_id―>( PRIMARY KEY (`JG_ID`) )
MYSQL數(shù)據(jù)庫3):c.yh_id=d.yh_id―>( KEY `ind_yh_id` (`YH_ID`) )
執(zhí)行計(jì)劃：
MYSQL數(shù)據(jù)庫
+―-+――――-+――-+――C+――――――-+―――C+―――+――――C+――+――――-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+―-+――――-+――-+――C+――――――-+―――C+―――+――――C+――+――――-+
| 1 | SIMPLE | c | ref | PRIMARY,ind_yh_dm | ind_yh_dm | 57 | const | 2 | Using where |
| 1 | SIMPLE | d | ref | ind_yh_id | ind_yh_id | 98 | test.c.YH_ID | 272 | Using index |
| 1 | SIMPLE | b | ref | PRIMARY,ind_yh_id | ind_yh_id | 98 | test.c.YH_ID | 531 | Using index |
| 1 | SIMPLE | a | eq_ref | PRIMARY,INDEX_JG | PRIMARY | 98 | test.b.JG_ID | 1 | Using where |
+―-+――――-+――-+――C+――――――-+―――C+―――+――――C+――+――――-+
MYSQL數(shù)據(jù)庫執(zhí)行時(shí)間：
MYSQL數(shù)據(jù)庫
1 row in set (0.00 sec)
MYSQL數(shù)據(jù)庫可以看到執(zhí)行計(jì)劃中的rows已經(jīng)大大降低,執(zhí)行時(shí)間也由原來的750ms降低到0 ms級別；
轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.snjht.com/jiaocheng/2144.html
