程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL在聯系關系龐雜情形下所能做出的一些優化

MySQL在聯系關系龐雜情形下所能做出的一些優化

編輯:MySQL綜合教程

MySQL在聯系關系龐雜情形下所能做出的一些優化。本站提示廣大學習愛好者:(MySQL在聯系關系龐雜情形下所能做出的一些優化)文章只能為提供參考,不一定能成為您想要的結果。以下是MySQL在聯系關系龐雜情形下所能做出的一些優化正文


昨天處置了一則龐雜聯系關系SQL的優化,這類SQL的優化常常斟酌以下四點:

    第一.查詢所前往的成果集,平日查詢前往的成果集很少,是有信念停止優化的;

    第二.驅動表的選擇相當主要,經由過程檢查履行籌劃,可以看到優化器選擇的驅動表,從履行籌劃中的rows可以年夜致反應出成績的地點;

    第三.理清各表之間的聯系關系關系,留意聯系關系字段上能否有適合的索引;

    第四.應用straight_join症結詞來強迫表之間的聯系關系次序,可以便利我們驗證某些料想;

SQL:
履行時光:

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查詢現實只前往了一行數據,但卻履行消耗了750ms,檢查履行籌劃:

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 |
+—-+————-+——-+——–+——————+———+———+————–+——-+————-+

可以看到履行籌劃中有兩處比擬顯眼的機能瓶頸:

| 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的表,所以驅動表不會選擇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表的數據量年夜於其他的兩表,同時b表上根本沒有查詢過濾前提,所以驅動表選擇B的能夠消除;

優化器現實選擇了a表作為驅動表,而為何不是c表作為驅動表?我們來剖析一下:

第一階段:a表作為驅動表
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);

履行籌劃:

+—-+————-+——-+——–+——————+———–+———+————–+——-+————-+
| 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 |
+—-+————-+——-+——–+——————+———–+———+————–+——-+————-+

履行時光:

1 row in set (0.77 sec)

在d表上添加索引後,d表的掃描行數降低到272行(最開端為:54584 )

| 1 | SIMPLE | d | ref | ind_yh_id | ind_yh_id | 98 | test.b.YH_ID | 272 | Using index |

第二階段:c表作為驅動表

d
^
|
c–>b–>a
因為在c表上有yh_dm過濾性很高的挑選前提,所以我們在yh_dm上創立一個索引:

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

添加索引:

alter table c add index ind_yh_dm(yh_dm)

檢查履行籌劃:

+—-+————-+——-+——–+——————-+———–+———+————–+——-+————-+
| 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 |
+—-+————-+——-+——–+——————-+———–+———+————–+——-+————-+

履行時光:

1 row in set (0.74 sec)

在c表上添加索引後,索引照樣沒有走上,履行籌劃照樣以a表作為驅動表,所以我們這裡來剖析一下為何照樣以a表作為驅動表?

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

a.假如以c表為驅動表,則c表與b表在聯系關系的時刻,因為在b表沒有yh_id字段的索引,因為b表的數據量很年夜,所以優化器以為這裡假如以c表作為驅動表,則會與b表發生較年夜的聯系關系(這裡可使用straight_join強迫應用c表作為驅動表);
b.假如以a表為驅動表,則a表與b表在聯系關系的時刻,因為在b表上有jg_id字段的索引,所以優化器以為以a作為驅動表的價值是小於以c作為驅動板的價值;
所以我們假如要以C表為驅動表,只須要在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`) )
履行籌劃:

+—-+————-+——-+——–+——————-+———–+———+————–+——+————-+
| 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 |
+—-+————-+——-+——–+——————-+———–+———+————–+——+————-+

履行時光:

1 row in set (0.00 sec)

可以看到履行籌劃中的rows曾經年夜年夜下降,履行時光也由本來的750ms下降到0 ms級別;

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved