這篇文章主要介紹了MySQL在關(guān)聯(lián)復(fù)雜情況下所能做出的一些優(yōu)化,作者通過添加索引來不斷優(yōu)化查詢時(shí)間,需要的朋友可以參考下
昨天處理了一則復(fù)雜關(guān)聯(lián)SQL的優(yōu)化，這類SQL的優(yōu)化往往考慮以下四點(diǎn)：
第一.查詢所返回的結(jié)果集，通常查詢返回的結(jié)果集很少，是有信心進(jìn)行優(yōu)化的;
第二.驅(qū)動(dòng)表的選擇至關(guān)重要，通過查看執(zhí)行計(jì)劃，可以看到優(yōu)化器選擇的驅(qū)動(dòng)表,從執(zhí)行計(jì)劃中的rows可以大致反映出問題的所在;
第三.理清各表之間的關(guān)聯(lián)關(guān)系，注意關(guān)聯(lián)字段上是否有合適的索引;
第四.使用straight_join關(guān)鍵詞來強(qiáng)制表之間的關(guān)聯(lián)順序，可以方便我們驗(yàn)證某些猜想;
SQL:
執(zhí)行時(shí)間：
- 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)
這條SQL查詢實(shí)際只返回了一行數(shù)據(jù)，但卻執(zhí)行耗費(fèi)了750ms，查看執(zhí)行計(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' ;
- +—-+————-+——-+——–+——————+———+———+————–+——-+————-+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +—-+————-+——-+——–+——————+———+———+————–+——-+————-+
- | 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 |
- +—-+————-+——-+——–+——————+———+———+————–+——-+————-+
可以看到執(zhí)行計(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 |
由于d是left join的表，所以驅(qū)動(dòng)表不會選擇d表，我們在來看看a,b,c三表的大小：
- mysql> select count(*) from c;
- +———-+
- | count(*) |
- +———-+
- | 53731 |
- +———-+
- mysql> select count(*) from a;
- +———-+
- | count(*) |
- +———-+
- | 53335 |
- +———-+
- mysql> select count(*) from b;
- +———-+
- | count(*) |
- +———-+
- | 105809 |
- +———-+
由于b表的數(shù)據(jù)量大于其他的兩表，同時(shí)b表上基本沒有查詢過濾條件，所以驅(qū)動(dòng)表選擇B的可能排除;
優(yōu)化器實(shí)際選擇了a表作為驅(qū)動(dòng)表，而為什么不是c表作為驅(qū)動(dòng)表?我們來分析一下：
第一階段：a表作為驅(qū)動(dòng)表
a–>b–>c–>d:
(1):a.jg_id=b.jg_id—>(b索引:PRIMARY KEY (`JG_ID`,`YH_ID`) )
(2):b.yh_id=c.yh_id—>(c索引:PRIMARY KEY (`YH_ID`))
(3):c.yh_id=d.yh_id—>(d索引:PRIMARY KEY (`JS_DM`,`YH_ID`))
由于d表上沒有yh_id的索引，索引在d表上添加索引：
- alter table d add index ind_yh_id(yh_id);
執(zhí)行計(jì)劃：
- +—-+————-+——-+——–+——————+———–+———+————–+——-+————-+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +—-+————-+——-+——–+——————+———–+———+————–+——-+————-+
- | 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 |
- +—-+————-+——-+——–+——————+———–+———+————–+——-+————-+
執(zhí)行時(shí)間：
- 1 row in set (0.77 sec)
在d表上添加索引后，d表的掃描行數(shù)下降到272行(最開始為：54584 )
- | 1 | SIMPLE | d | ref | ind_yh_id | ind_yh_id | 98 | test.b.YH_ID | 272 | Using index |
第二階段：c表作為驅(qū)動(dòng)表
d
^
|
c–>b–>a
由于在c表上有yh_dm過濾性很高的篩選條件，所以我們在yh_dm上創(chuàng)建一個(gè)索引：
- mysql> select count(*) from c where yh_dm = '006939748XX';
- +———-+
- | count(*) |
- +———-+
- | 2 |
- +———-+
添加索引：
- alter table c add index ind_yh_dm(yh_dm)
查看執(zhí)行計(jì)劃：
- +—-+————-+——-+——–+——————-+———–+———+————–+——-+————-+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +—-+————-+——-+——–+——————-+———–+———+————–+——-+————-+
- | 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 |
- +—-+————-+——-+——–+——————-+———–+———+————–+——-+————-+
執(zhí)行時(shí)間：
- 1 row in set (0.74 sec)
在c表上添加索引后，索引還是沒有走上，執(zhí)行計(jì)劃還是以a表作為驅(qū)動(dòng)表，所以我們這里來分析一下為什么還是以a表作為驅(qū)動(dòng)表?
1):c.yh_id=b.yh_id—>( PRIMARY KEY (`JG_ID`,`YH_ID`) )
a.如果以c表為驅(qū)動(dòng)表，則c表與b表在關(guān)聯(lián)的時(shí)候，由于在b表沒有yh_id字段的索引，由于b表的數(shù)據(jù)量很大，所以優(yōu)化器認(rèn)為這里如果以c表作為驅(qū)動(dòng)表，則會與b表產(chǎn)生較大的關(guān)聯(lián)(這里可以使用straight_join強(qiáng)制使用c表作為驅(qū)動(dòng)表);
b.如果以a表為驅(qū)動(dòng)表，則a表與b表在關(guān)聯(lián)的時(shí)候，由于在b表上有jg_id字段的索引，所以優(yōu)化器認(rèn)為以a作為驅(qū)動(dòng)表的代價(jià)是小于以c作為驅(qū)動(dòng)板的代價(jià);
所以我們?nèi)绻訡表為驅(qū)動(dòng)表，只需要在b上添加yh_id的索引：
- alter table b add index ind_yh_id(yh_id);
2):b.jg_id=a.jg_id—>( PRIMARY KEY (`JG_ID`) )
3):c.yh_id=d.yh_id—>( KEY `ind_yh_id` (`YH_ID`) )
執(zhí)行計(jì)劃：
- +—-+————-+——-+——–+——————-+———–+———+————–+——+————-+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +—-+————-+——-+——–+——————-+———–+———+————–+——+————-+
- | 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 |
- +—-+————-+——-+——–+——————-+———–+———+————–+——+————-+
執(zhí)行時(shí)間：
- 1 row in set (0.00 sec)
可以看到執(zhí)行計(jì)劃中的rows已經(jīng)大大降低，執(zhí)行時(shí)間也由原來的750ms降低到0 ms級別;
新聞熱點(diǎn)
疑難解答
圖片精選
