欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

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

 更新時間：2015年05月08日 09:20:01   作者：羅龍九  
這篇文章主要介紹了MySQL在關聯(lián)復雜情況下所能做出的一些優(yōu)化,作者通過添加索引來不斷優(yōu)化查詢時間,需要的朋友可以參考下

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

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

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

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

    第四.使用straight_join關鍵詞來強制表之間的關聯(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的表，所以驅動表不會選擇d表，我們在來看看a,b,c三表的大?。?/p> 

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表上基本沒有查詢過濾條件，所以驅動表選擇B的可能排除；

優(yōu)化器實際選擇了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);

執(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表作為驅動表

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表作為驅動表，所以我們這里來分析一下為什么還是以a表作為驅動表？

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

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

1 row in set (0.00 sec)

可以看到執(zhí)行計劃中的rows已經(jīng)大大降低，執(zhí)行時間也由原來的750ms降低到0 ms級別；

相關文章

  • select?into?from和insert?into?select的使用舉例詳解

    select?into?from和insert?into?select的使用舉例詳解

    select into from和insert into select都是用來復制表,下面這篇文章主要給大家介紹了關于select?into?from和insert?into?select使用的相關資料,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下
    2023-04-04
  • Navicat連接MySQL提示1045錯誤解決(重置MySQL密碼)

    Navicat連接MySQL提示1045錯誤解決(重置MySQL密碼)

    連接MySQL數(shù)據(jù)庫時難免會遇到1045錯誤,主要是因為用戶輸入的用戶名或密碼錯誤被拒絕訪問,如果不想重裝,需要找回密碼或者重置密碼,這篇文章主要給大家介紹了關于Navicat連接MySQL提示1045錯誤解決的方法,主要是重置MySQL密碼,需要的朋友可以參考下
    2023-04-04
  • MySQL慢SQL語句常見誘因以及解決方法

    MySQL慢SQL語句常見誘因以及解決方法

    在本篇文章里小編給大家整理的關于MySQL慢SQL語句常見誘因以及解決方法，有需要的朋友們可以學習下。
    2019-08-08
  • 詳解MySQL kill 指令的執(zhí)行原理

    詳解MySQL kill 指令的執(zhí)行原理

    這篇文章主要介紹了詳解MySQL kill 指令的執(zhí)行原理，幫助大家更好的理解和學習使用MySQL，感興趣的朋友可以了解下
    2021-03-03
  • Mysql數(shù)據(jù)庫慢查詢常用優(yōu)化方式

    Mysql數(shù)據(jù)庫慢查詢常用優(yōu)化方式

    數(shù)據(jù)庫SQL優(yōu)化是老生常談的問題,下面這篇文章主要給大家介紹了關于Mysql數(shù)據(jù)庫慢查詢常用優(yōu)化方式,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下
    2023-05-05
  • MySQL 外鍵約束和表關系相關總結

    MySQL 外鍵約束和表關系相關總結

    一個項目中如果將所有的數(shù)據(jù)都存放在一張表中是不合理的，比如一個員工信息，公司只有2個部門，但是員工有1億人，就意味著員工信息這張表中的部門字段的值需要重復存儲，極大的浪費資源，因此可以定義一個部門表和員工信息表進行關聯(lián)，而關聯(lián)的方式就是外鍵。
    2021-06-06
  • Mysql多層子查詢示例代碼(收藏夾案例)

    Mysql多層子查詢示例代碼(收藏夾案例)

    這篇文章主要介紹了Mysql多層子查詢示例代碼，以收藏夾案例給大家詳細介紹，代碼簡單易懂，對大家的學習或工作具有一定的參考借鑒價值,需要的朋友可以參考下
    2022-03-03
  • Mysql給普通分頁查詢結果加序號實操

    Mysql給普通分頁查詢結果加序號實操

    這篇文章主要介紹了Mysql給普通分頁查詢結果加序號實操，文章通過圍繞主題展開詳細的內(nèi)容介紹，具有一定的參考價值，需要的小伙伴可以參考一下
    2022-09-09
  • ubuntu16.04.1下 mysql安裝和卸載圖文教程

    ubuntu16.04.1下 mysql安裝和卸載圖文教程

    這篇文章主要介紹了ubuntu16.04.1下 mysql安裝和卸載圖文教程,非常不錯，具有參考借鑒價值，需要的朋友可以參考下
    2016-11-11
  • MySQL聯(lián)結表介紹以及使用詳解

    MySQL聯(lián)結表介紹以及使用詳解

    這篇文章主要給大家介紹了關于MySQL聯(lián)結表介紹及使用的相關資料,聯(lián)結SQL最強大的功能之一就是能在數(shù)據(jù)檢索查詢的執(zhí)行中聯(lián)結表,文中通過代碼介紹的非常詳細,需要的朋友可以參考下
    2024-03-03

最新評論