MySQL在關(guān)聯(lián)復(fù)雜情況下所能做出的一些優(yōu)化

更新時間：2015年05月08日 09:20:01 作者：羅龍九

這篇文章主要介紹了MySQL在關(guān)聯(lián)復(fù)雜情況下所能做出的一些優(yōu)化,作者通過添加索引來不斷優(yōu)化查詢時間,需要的朋友可以參考下

昨天處理了一則復(fù)雜關(guān)聯(lián)SQL的優(yōu)化，這類SQL的優(yōu)化往往考慮以下四點：

第一.查詢所返回的結(jié)果集，通常查詢返回的結(jié)果集很少，是有信心進行優(yōu)化的；

第二.驅(qū)動表的選擇至關(guān)重要，通過查看執(zhí)行計劃，可以看到優(yōu)化器選擇的驅(qū)動表,從執(zhí)行計劃中的rows可以大致反映出問題的所在；

第三.理清各表之間的關(guān)聯(lián)關(guān)系，注意關(guān)聯(lián)字段上是否有合適的索引；

第四.使用straight_join關(guān)鍵詞來強制表之間的關(guān)聯(lián)順序，可以方便我們驗證某些猜想；

SQL:
執(zhí)行時間：

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ù)據(jù)，但卻執(zhí)行耗費了750ms，查看執(zhí)行計劃：

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í)行計劃中有兩處比較顯眼的性能瓶頸：

| 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表，我們在來看看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ù)量大于其他的兩表，同時b表上基本沒有查詢過濾條件，所以驅(qū)動表選擇B的可能排除；

優(yōu)化器實際選擇了a表作為驅(qū)動表，而為什么不是c表作為驅(qū)動表？我們來分析一下：

第一階段：a表作為驅(qū)動表
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í)行計劃：

+—-+————-+——-+——–+——————+———–+———+————–+——-+————-+
| 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í)行時間：

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
^
|
c–>b–>a
由于在c表上有yh_dm過濾性很高的篩選條件，所以我們在yh_dm上創(chuàng)建一個索引：

mysql> select count(*) from c where yh_dm = '006939748XX';
+———-+
| count(*) |
+———-+
| 2 |
+———-+

添加索引：

alter table c add index ind_yh_dm(yh_dm)

查看執(zhí)行計劃：

+—-+————-+——-+——–+——————-+———–+———+————–+——-+————-+
| 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í)行時間：

1 row in set (0.74 sec)

在c表上添加索引后，索引還是沒有走上，執(zhí)行計劃還是以a表作為驅(qū)動表，所以我們這里來分析一下為什么還是以a表作為驅(qū)動表？

1):c.yh_id=b.yh_id—>( PRIMARY KEY (`JG_ID`,`YH_ID`) )

a.如果以c表為驅(qū)動表，則c表與b表在關(guān)聯(lián)的時候，由于在b表沒有yh_id字段的索引，由于b表的數(shù)據(jù)量很大，所以優(yōu)化器認為這里如果以c表作為驅(qū)動表，則會與b表產(chǎn)生較大的關(guān)聯(lián)（這里可以使用straight_join強制使用c表作為驅(qū)動表）；
b.如果以a表為驅(qū)動表，則a表與b表在關(guān)聯(lián)的時候，由于在b表上有jg_id字段的索引，所以優(yōu)化器認為以a作為驅(qū)動表的代價是小于以c作為驅(qū)動板的代價；
所以我們?nèi)绻訡表為驅(qū)動表，只需要在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í)行計劃：

+—-+————-+——-+——–+——————-+———–+———+————–+——+————-+
| 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í)行時間：