Mysql資料庫的join演算法介紹，優美的執行優化

胖琪的升級之路發表於2019-03-25

MySql資料庫演算法優化

資料庫的連結

前幾天，小夥伴們在群裡面討論進行優化join語句，大家都很積極的發言討論，結論是圍繞索引與大小表關係來進行操作，重要的是業務進行繫結。

部分內容來源於極客時間的Mysql實戰45講。

在Mysql的資料庫中，我們知道join連結主要使用的有大致三種情況。

inner join：內連線
left joinL：左連結
right join:右連結

那這些join我們需要怎麼使用呢？並且可以使用的很好，需要我們在資料庫裡面嘗試下。

資料準備

該資料表來源於網路。

-- 建立測試資料庫
CREATE DATABASE join_test CHARSET UTF8;

-- 人員資訊表
CREATE TABLE `Persons` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `LastName` char(16) NOT NULL DEFAULT '',
  `FirstName` char(16) NOT NULL DEFAULT '',
  `Address` varchar(128) NOT NULL DEFAULT '',
  `City` varchar(128) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 訂單表
CREATE TABLE `Orders` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `OrderNo` int(11) NOT NULL DEFAULT '0',
  `Pid` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `Persons` (`LastName`, `FirstName`, `Address`, `City`)
VALUES
('Adams', 'John', 'Oxford Street', 'London'),
('Bush', 'George', 'Fifth Avenue', 'New York'),
('Carter', 'Thomas', 'Changan Street', 'Beijing');

INSERT INTO `Orders` (`OrderNo`, `Pid`)
VALUES (77895, 3), (44678, 3), (22456, 1), (24562, 1), (34764, 65);
複製程式碼

建立了兩個欄位的關聯關係，並且關聯關係這裡沒有使用索引欄位。

join中使用的演算法

使用的演算法有幾種，一個是Index Nested-Loop join，另外就是Block Nested-Loop Join.

關聯表Peoples,與Order表

Block Nested-Loop Join

explain select  p.* from Persons p
INNER JOIN Orders  o on p.id = o.Pid

複製程式碼

執行結果圖：

從圖上可以看出驅動表是Peoples,被驅動表是Order,由於我們的關聯關係中，被驅動表沒有索引，所以在執行關聯的時候第二張表要全盤掃描。

那執行流程是怎樣的呢？

將驅動表裡面需要讀取的資料放入到記憶體中（useing join buffer）。
從被驅動表中取出來一行內容，與記憶體中的資料進行匹配，符合結果的結果集取出來。
迴圈執行被驅動表中的所有資料，
被驅動表執行完畢後，在執行驅動表的下一條資料。
2-4，直到驅動表的資料執行完畢，結束。

結果執行的數量是笛卡爾積，進行乘法。

如果join buffer 裡面的資料放不下怎麼辦？

就先取出來一部分驅動表裡面的資料，進行與第二個表對比，迴圈執行，對比結束後清空buffer中的內容，再處理。

從上面可以看出，當被驅動表上沒有使用索引的時候會涉及全盤掃描，並且是兩個表都全盤掃描，雖然第一個表內容讀取到記憶體中可以加快資料的讀取，但是全盤掃描對於效能屬於一個損耗。

所以我們需要儘可能的建立索引

Index Nested-Loop

那麼如果我們建立索引了呢？


增加索引  索引的名字 與索引的列
CREATE INDEX Pid ON Orders (Pid)

EXPLAIN select  p.* from Persons p
STRAIGHT_JOIN Orders o on  p.id = o.Pid;
複製程式碼

因為我們在被驅動的表上增加了索引，所以當我們需要的是Persons表中的資料時候，可以利用到索引，執行結果如下。

當兩個表關聯的時候，我們的People表，還有Order表。選擇People選擇為驅動表，Order為被動表，使用On關聯的時候Order 欄位上有索引，那麼就會使用該執行演算法語句。

演算法內容如下：

取出驅動表的裡面的一條資料
拿著這條資料去表二中查詢到合適的結果集，返回。
重複以上迴圈。

可以看到是使用的迴圈驅動表中的資料然後去被驅動表中查詢，利用索引，減少第二個迴圈的次數。這樣就能加快速度。

兩個演算法總結

從上面可以看出，在選擇使用join的時候，一定要避免sql語句將關聯的第二個上使用join語句，我們可以每次將自己執行的語句加上explain簡單的看下sql執行計劃，在優化我們的sql語句。

還有作為驅動表的資料儘可能少，迴圈的資料就很少了。

這就有我們前面所說的小表作為驅動表，大表加索引。這個概念。

Join

上面我們說了使用的兩個演算法，那麼我們在執行過程中會遇到哪些呢？

Inner join

重點強調，我們的語句都每次使用explain來檢視輸出

inner join 與join語句執行結果是一致的，所以在看執行結果，我們不必要關注某個點。

在使用inner join 的時候，以哪個左表還是右表作為依賴表都是存在可能的，所以我們可以使用straight_join來強制使用某個表作為依賴表，並且在使用inner join語句的時候該straight_join 也是一個優化的方式。

強制採用某個表作為依賴表。


// 注意當我們使用join語句，需要查詢第二個表資料的時候，如果我們的where 條件中沒有增加 篩選條件可能會導致使用Block Nested-Loop Join
EXPLAIN select  p.id,o.OrderNo from Persons p
STRAIGHT_JOIN Orders o on  p.id = o.Pid;
複製程式碼

那麼這種情況下怎麼優化的呢？

從圖上可以看出來，我們的表二沒有走索引，導致我們資料進行全盤的掃描。

在每一個資料庫的表上，我們都瞭解會有主鍵索引，那麼我們是否可以根據主鍵索引來排除呢？

我們通過sql來看。


當OrderNo上沒有索引的時候
EXPLAIN select  p.id,o.OrderNo from Persons p
STRAIGHT_JOIN Orders o on  p.id = o.Pid
where o.OrderNo > 30000

複製程式碼

走的是全盤掃描，如果我們在OrderNo上加上索引呢？

增加普通索引
CREATE INDEX Pid ON Orders (Pid)

//在我建立索引的時候，有時候條件語句是可以用上索引的，有的時候是用不上的。由於資料量太小的原因導致部分索引使用補上的情況。在這裡根據不同的欄位內容是能使用上索引的。

EXPLAIN select  p.id,o.OrderNo from Persons p
STRAIGHT_JOIN Orders o on  p.id = o.Pid
where o.Pid > 3

複製程式碼