一個(gè)left join SQL 簡單優(yōu)化分析

有個(gè)關(guān)聯(lián)查詢的sql,需要2秒多，于是進(jìn)行查看一番：

SELECT
	a.id,
	a.brand_id,
	a.series_id,
	a.product_id,
	a.material_id,
	a.custom_category_id,
	a.price,
	a.product_url,
	a.organ_id,
	.....
FROM
	pm_brand_xxxx a
LEFT JOIN pm_brand_yyyyy d ON a.series_id = d.id
WHERE
	a.is_delete = 0
AND d.is_delete = 0
AND a.organ_id = 'Cxxx'
AND a.brand_id = 6491603
AND d.brand_id = 6491603
AND a.model_flag = 14;

mysql> show profile for query 4;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000072 |
| checking permissions | 0.000002 |
| checking permissions | 0.000002 |
| Opening tables       | 0.000011 |
| init                 | 0.000026 |
| System lock          | 0.000007 |
| optimizing           | 0.000016 |
| statistics           | 0.000142 |
| preparing            | 0.000018 |
| executing            | 0.000002 |
| Sending data         | 2.281192 |<<<<<<<執(zhí)行的主要時(shí)間消耗
| end                  | 0.000007 |
| query end            | 0.000011 |
| closing tables       | 0.000011 |
| freeing items        | 0.000030 |
| logging slow query   | 0.000003 |
| logging slow query   | 0.000102 |
| cleaning up          | 0.000022 |
+----------------------+----------+

+----+-------------+-------+------------+-------------+---------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------+---------+-------+-------+----------+------------------------------------------------------------------------------------------------------------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys                                                                                                 | key                                                                       | key_len | ref   | rows  | filtered | Extra                                                                                                                                          |
+----+-------------+-------+------------+-------------+---------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------+---------+-------+-------+----------+------------------------------------------------------------------------------------------------------------------------------------------------+
|  1 | SIMPLE      | d     | NULL       | ref         | PRIMARY,idx_pm_yyyy_bid                                                                                        | idx_pm_yyyyy_bid                                                        | 9       | const |     1 |    10.00 | Using where                                                                                                                                    |
|  1 | SIMPLE      | a     | NULL       | index_merge | idx_pm_xxxx_sid,idx_pm_xxx_bid,idx_pm_brand_xxxx_organ                                                         | idx_pm_xxx_organ,idx_pm_brand_xxxx_bid                                   | 99,9    | NULL  | 11314 |     0.04 | Using intersect(idx_pm_xxxxx_organ,idx_pm_xxxx_bid); Using where; Using join buffer (Block Nested Loop)                                        |
+----+-------------+-------+------------+-------------+---------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------+---------+-------+-------+----------+------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

從執(zhí)行計(jì)劃來看，d表是做了驅(qū)動(dòng)表，a做了被驅(qū)動(dòng)表

d表 type = ref ，使用非唯一性索引或者唯一索引的前綴掃描，返回匹配某個(gè)單獨(dú)值的記錄行,這里使用了索引idx_pm_yyyyy_bid，該索引正是brand_id上的索引，

即是說，在和a表的關(guān)聯(lián)中d先通過brand_id來查找記錄行，再通過相應(yīng)記錄的id去和a表的series_id做匹配。

我查看相應(yīng)的記錄數(shù)，發(fā)現(xiàn)a表145萬的大表,d表是4075的小表。

a表

mysql> select count(*) from pm_xxxxxx;

+----------+

| count(*) |

+----------+

|  1459777 |

+----------+

1 row in set (0.27 sec)

d表：

mysql> select count(*) from pm_yyyyyy;

+----------+

| count(*) |

+----------+

|     4075 |

+----------+

1 row in set (0.00 sec)

而 a表是type=index_merge 索引合并,這里走了idx_pm_xxx_organ(organ_id),idx_pm_brand_xxxx_bid(brand_id) ,extra 是

Using intersect(idx_pm_xxxxx_organ,idx_pm_xxxx_bid); Using where; Using join buffer (Block Nested Loop) 

Using intersect正說明了這里使用了(idx_pm_xxxxx_organ,idx_pm_xxxx_bid)的交集

Using where 是用model_flag等這些其他條件的過濾

Using join buffer (Block Nested Loop) 說明使用BNL的算法進(jìn)行匹配

 BNL 算法是將外層循環(huán)的行/結(jié)果集（驅(qū)動(dòng)表）存入join buffer, 內(nèi)層循環(huán)的每一行與整個(gè)buffer中的記錄做比較，從而減少內(nèi)層循環(huán)的次數(shù).

舉例來說，外層循環(huán)的結(jié)果集是100行，使用NLJ 算法需要掃描內(nèi)部表100次，如果使用BNL算法，先把對(duì)Outer Loop表(外部表)每次讀取的10行記錄放到j(luò)oin buffer,然后在InnerLoop表(內(nèi)部表)中直接匹配這10行數(shù)據(jù)，內(nèi)存循環(huán)就可以一次與這10行進(jìn)行比較, 這樣只需要比較10次，對(duì)內(nèi)部表的掃描減少了9/10。所以BNL算法就能夠顯著減少內(nèi)層循環(huán)表掃描的次數(shù).

在這里就是d表中取得結(jié)果集分批放入buffer中與a表進(jìn)行匹配。

而這個(gè)語句無論如何都要2秒中，也在我們的認(rèn)識(shí)中小表驅(qū)動(dòng)大表并沒錯(cuò)，我的猜想應(yīng)該就是在進(jìn)行BNL時(shí)消耗了時(shí)間，表現(xiàn)到過程中就是 Sending data 的時(shí)間消耗增多。

吐槽的是mysql中貌似沒有什么辦法來多方面看查詢消耗了。

我想到的是如果該表現(xiàn)有sql關(guān)聯(lián)的順序是否性能能改善，在該sql中，我發(fā)現(xiàn)了兩個(gè)條件：

AND a.brand_id = 6491603

AND d.brand_id = 6491603

在業(yè)務(wù)邏輯上這兩個(gè)表的字段應(yīng)該是一致的，如果我將d表的d.brand_id = 6491603去掉，以上的執(zhí)行計(jì)劃應(yīng)該會(huì)改變，于是去掉之后執(zhí)行，執(zhí)行時(shí)間非常小。

mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000080 |
| checking permissions | 0.000002 |
| checking permissions | 0.000002 |
| Opening tables       | 0.000012 |
| init                 | 0.000030 |
| System lock          | 0.000006 |
| optimizing           | 0.000014 |
| statistics           | 0.000130 |
| preparing            | 0.000016 |
| executing            | 0.000001 |
| Sending data         | 0.027325 |
| end                  | 0.000003 |
| query end            | 0.000015 |
| closing tables       | 0.000005 |
| freeing items        | 0.000014 |
| cleaning up          | 0.000009 |
+----------------------+----------+
16 rows in set, 1 warning (0.00 sec)

看其執(zhí)行計(jì)劃：
+----+-------------+-------+------------+-------------+---------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------+---------+-------------------------+-------+----------+---------------------------------------------------------------------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys                                                                                                 | key                                                                       | key_len | ref                     | rows  | filtered | Extra                                                                                                   |
+----+-------------+-------+------------+-------------+---------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------+---------+-------------------------+-------+----------+---------------------------------------------------------------------------------------------------------+
|  1 | SIMPLE      | a     | NULL       | index_merge | idx_pm_xxxxx_sid,idx_pm_xxxxx_bid,idx_pm_xxxx_organ                                                           | idx_pm_xxxxx_organ,idx_pm_xxxx_bid                                        | 99,9    | NULL                    | 11315 |     1.00 | Using intersect(idx_pm_xxxxx_organ,idx_pm_xxxx_bid); Using where                                        |
|  1 | SIMPLE      | d     | NULL       | eq_ref      | PRIMARY                                                                                                       | PRIMARY                                                                   | 8       | xxxx.a.series_id |     1 |    10.00 | Using where                                                                                             |
+----+-------------+-------+------------+-------------+---------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------+---------+-------------------------+-------+----------+---------------------------------------------------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

發(fā)現(xiàn)變成了a表做驅(qū)動(dòng)表，d表做被驅(qū)動(dòng)表,從extra列看

a表是Using intersect(idx_pm_xxxxx_organ,idx_pm_xxxx_bid); Using where   依然是使用索引合并，where條件來取結(jié)果，使用了idx_pm_xxxxx_organ,idx_pm_xxxx_bid 連個(gè)索引。

d表走PRIMARY 主鍵索引，從ref列來看是通過a表的series_id 來關(guān)聯(lián)，這樣效率表提升了。

需要說的一點(diǎn)是，小結(jié)果集并不代表就是小表，大表也可以有小結(jié)果集，當(dāng)大表用來被匹配并被掃描多次，自然效率并不高.