FreeCodeCamp - Learn to Use the MySQL Database

Introduction

一堂介紹SQL基礎的課程，因為平常只要SQL能撈到資料就好，回過頭來看一下除了指令之外的內容…
網址：https://www.youtube.com/watch?v=ER8oKX5myE0

Data Modeling

• Understand business data
  根據專案項目的複雜程度，資料多寡與使用情境，適合的DB架構也不同，且須考量日後擴充彈性。
  
  

• Create a logical design
  有關 Tables, Indexes, Constraints (ER Diagram)
  
  

• Eliminate/Reduce (Normalization)
  透過正規化，去除資料冗餘、異常值及未同步更新等問題，但正規化雖然提高了空間的使用效率，查詢時需使用到更多的Join
  
  

• Foreign Key Constraint
  外鍵限制可以預防被參照的表格欄位遭到異動，或是讓被參照的表格欄位進行異動時，連帶對參照此表的表格欄位跟著異動

Transaction SQL

• Isolation Levels
  
  
  
  
• Read Uncommitted Isolation
  最低的 Isolation Level，允許讀取還沒有被 Commit 的資料，有可能發生 Dirty Read 和其他所有的現象。
  
  
• Read Committed Isolation
  只允許讀取已經被 Commit 的資料，可以避免 Dirty Read，但是其他四個現象還是有可能發生。
  
  
• Repeatable Read Isolation
  只要能夠避免 Dirty Read 和 Non-repeatable Read 現象就可以被稱為 Repeatable Read Isolation。
  
  
• Serializable Isolation
  Serializable Isolation 可以保證在多個 Transaction 同時對資料庫進行讀寫所得到的結果，會跟一次只讓一個 Transaction 照順序進行讀寫所得到的結果完全一致，但因為必須犧牲 Concurrency，效能較差。
  
  
• Deadlock
  Deadlock 主要是多個 Transaction 手上握有對方需要的資源，等待對方資源釋放，同時也鎖死手上的資源，常發生在使用 update 卻順序剛好相反盡可能減少 Update / Delete 在單一 Transaction 中的數量
  
  
• 解決方式
• Lock 時依照同樣的順序進行(例如 select … for update)
• 降低 Lock 的層級，避免 lock tables 的操作
• 增加 update 操作欄位的 index，降低 lock table 的需求
• 考慮降低 isolation level

Clustered Index

資料索引可加快搜尋速度，MySQL 的索引大多都使用B-tree的資料結構

• Unique
  和 index 相同，差異在於不允許重複值存在。
  
  
• PRIMARY Key(PK):
  和 unique 相同，差異在於不允許 Null 存在。
  
  
• PK會自動建立index
  每個 table 只能有一個PK
  
  
• Conception
• 建立 index 會占用儲存空間，資料增刪修時會異動
• index 欄位長度越短越好
• index 欄位長度若是固定比變動好
• 單欄索引時若搜尋多個列會先使用索引縮小範圍再進行下一步的搜尋
• 多欄索引的第一欄索引在搜尋時，可用狀態下必定會被使用到，但效能不如設定第一欄為單欄索引來得好

MySQL Explain

用於解析查詢語法的指令 E.g., explain SELECT * FROM table1，要注意的是EXPLAIN不會提供關於快取、儲存過程或是 UDF 對於查詢語句的影響，其輸出結果如下：

• id
  執行順序由 id 大往小執行，但 id 相同者，上位者先。
• select_type
  查詢類型，是單表查詢、聯合查詢還是子查詢等
• table
  查詢的表名
• type
  連接使用的類型(重要項) 顯示連接使用的類型，按最優到最差的類型排序
• System
  該表格只有一列 (如同系統表)
• const
  使用主鍵或者唯一索引的時候，符合的表僅有一列
• eq_ref
  使用 PK 或 Unique key 進行多表關聯查詢時，僅有一列符合條件。
• ref
  同eq_ref，但未使用  PK 或 Unique key
• fulltext
  使用全文索引才會顯示此類
• ref_or_null
  查詢語法中搜尋了包含Null的資料列
• index_merge
  在一個查詢裡面使用到多個 range 類型的掃瞄並merge了搜尋結果
• unique_subquery
  同 eq_ref ，但使用了 in 的子查詢，且子查詢中使用了 PK 或 Unique key
• index_subquery
  同 unique_subquery，但子查詢裡非使用 PK 或 Unique key
• range
  使用索引返回一個範圍的結果，例如：使用大於 > 或小於 < 查詢時發生。
• index
  遍歷索引樹進行掃描。
• ALL
  遍歷表格進行掃描，此為最壞的情況，應該儘量避免
• prossible_keys
  能在該表中使用哪些索引有助於查詢
• key
  實際使用的索引
• key_len
  索引的長度，在不損失精確性的情況 下,長度越短越好
• ref
  索引的哪一列被使用了
• rows
  回傳的資料列數
• Extra
  其他說明

Parameter

• SQL_SAFE_UPDATE
  任何基於Non-Primary Key 進行搜尋的修改指令會被阻擋
• AUTOCOMMIT
  自動向SQL提交異動，MySQL的此項參數預設為開啟