DB 基礎 - N+1 problem, Transaction, ACID

自嗨的 check list：

ORM操作

• Laravel Model建一些關聯，但我自己設計 Schema 滿慘ㄉ
• 大致上會用 Laravel orm 去 query，但常常忘記還是要看文件><
• 知道什麼時候用 orm 什麼時候用 query builder
• 知道 ORM 有 N+1 問題，知道遇到的時候要用 with()
• 知道 debug 工具

資料庫觀念

• 簡答前三個正規化是什麼
• 簡答 transaction
• 簡答ACID是什麼
• 寫個 transaction

資料庫查詢 & ORM

N+1 是什麼

N+1 是做關聯查詢的時候，先做一次查詢整個 model（1次 SQL 查詢），再逐步執行關聯查詢（N次 SQL 查詢）。

可以參考 Laravel 官網上 Eager Loading 的部分

官網上的範例是：

foreach (Book::all() as $book)
{
    echo $book->author->name;
}

Book::all() 執行一次 SELECT * FROM book
$book->author->name 每一圈執行一次 SELECT name FROM author WHERE authors.book_id = (書本id)

因為程式不知道你查 Book 其實是想查 Author ， 就一行一行執行（lazy loading）。

如果希望告訴程式第一次的查詢等下會用到，就要用 eager loading，可以用 with() 方法。

foreach (Book::with('author')->get() as $book)
{
    echo $book->author->name;
}

Book::with('author')->get()

eager loading 知道你的 book 查詢是為了 author，就會把 author 的資料全倒出來，所以會幫你執行兩次 SQL：

select * from books
select * from authors where id in (1, 2, 3, 4, 5, ...)

總結

ORM 小心 N+1，解法要用 with

Query Builder

Ｊ：什麼時候用 eager loading，什麼時候用 join？
Ｒ：這個問題的本質是對 ORM 使用方式的問題。換個問題，如果用 Laravel ORM 要用 join 的方法，你會怎麼做？

$names = DB::table('books')
                ->join('authors', 'books.author_id', '=', 'authors.books_id')
                ->select('authors.name')
                ->get();

Ｊ：應該是這樣吧
Ｒ：你什麼時候會純用 ORM 什麼時候會願意參雜一些 query builder
Ｊ：orm 查不到。 只好用 query builder

結論

看了些討論，好像是說，如果比較重視可讀跟方便就用 orm，但本身熟悉 SQL 比較在意高效能可以用 query builder。
但用 orm，最基本常識就是不要寫出 n+1。

討論

• If you are more aware of efficiency rather than ease of development, go for query builder.
• If you dealing with one entity go for ORM, (Eloquent).
• If you dealing with multiple entities it’s better to deal with query builder.
• If you are new to mysql or your application is not very complex, definitely choose ORM.
• If you need more complex query, I recommend to use query builder.

Debug

建議把 query Log 印出來

• https://laravel.com/docs/5.0/database#query-logging
• https://laravel.com/docs/master/queries#debugging
• laravel-debugbar

Ｒ：懷疑可能出現類似的效能問題 就把 query 印出來檢查確認 看有沒有特別多或者特別慢的 實際練習一下 印一次就會了

正規化

第一正規是單元值
第二正規是所有欄位要與主建相依
第三正規是與主建相依，欄位間不能相依

Transaction

先講結論

新手會名詞解釋和寫語法就好

名詞解釋：

• transaction 的目的是什麼？
  寫入資料庫可靠
• ACID分別是什麼？
  達到可靠的四個特性

語法：

• 用熟悉的工具（orm/SQL）寫一個 transaction
  begin transaction, commit, rollback

transaction：
一串 SQL 可以組成交易，這一串都成功就 commit，中間有失敗 就 rollback。
laravel 可以用 try-catch 包住 beginTransaction() ，用 commit() / roallback() 來做

try {

    DB::beginTransaction();

    $member = member::create([]);

    // $member->id 會是 null，因為 transaction 被卡住了
    $memberData = new MemberDate();
    $listUpdateLog->member_id = $member->id;
    $listUpdateLog->save();

    DB::commit();
} catch (\Exception $e) {
    DB::rollback();
}

ACID

ACID，是指資料庫管理系統（DBMS）在寫入或更新資料的過程中，為保證事務（transaction）是正確可靠的，所必須具備的四個特性：原子性（atomicity，或稱不可分割性）、一致性（consistency）、隔離性（isolation，又稱獨立性）、持久性（durability）。

該怎麼記得這些東西，而不是背書，可以用想像一筆交易。

• 一致性
  你的交易前後 資料庫內容要保持一致 不能有的事件沒有觸發有的沒有
• 隔離性
  多筆交易之間不能互相干擾
• 持久性
  資料庫短暫當機之類的 不能導致資料錯誤或者遺失

結論

• transaction 用來要確保資料庫的一連串操作可靠
• ACID 是確保可靠的特性
• 有沒有辦法用 transcation 這個功能 看資料庫有沒有支援
• transaction 應該要有這四個特性 然後這四個特性成立與否 要看資料庫的能力 有的資料庫不保證支援這四件事情
• ACID 是資料庫系統對寫入的保証，跟你的程式、框架，無關。知道這是什麼就好。

對 transaction 的誤解

以為關聯式資料庫都支援 transaction，非關聯式資料庫都不支援。其實並不是，跟資料庫系統有關。ACID 也可以用來形容 NoSQL 的交易。

你可以討論 MongoDB 支援不支援 符合 ACID 特性的 transaction
如果不支援 那麼你可能不會用它做金流系統

如果資料庫的程式 你 rollback 完 有機會沒有完全恢復到交易前的狀況 那你怎麼寫還是沒法保證原子性 重點是他的 rollback 怎麼做

新手要知道什麼

Ｊ：怎麼覺得 我只要知道有 begin transaction, commit, rollback，其他的都是資料庫設定
Ｒ：對呀 但是你要知道資料庫的特性 才能選擇跟使用這些事情

A&C
跟 rollback 有關
isolation
跟 lock 有關
surability
跟資料庫有沒有支援復原有關