Mysql的那些事兒(部分涉及資料庫知識總結)

1. 資料庫常見索引型別：

   1、B-Tree索引
   2、雜湊索引
   3、空間資料索引(R-Tree)
   4、全文索引

2. 資料庫併發問題：

1、Lost Update 更新丟失
    a. 第一類更新丟失，回滾覆蓋：撤消一個事務時，在該事務內的寫操作要回滾，把其它已提交的事務寫入的資料覆蓋了。
    b. 第二類更新丟失，提交覆蓋：提交一個事務時，寫操作依賴於事務內讀到的資料，讀發生在其他事務提交前，寫發生在其他事務提交後，把其他已提交的事務寫入的資料覆蓋了。
2、Dirty Read 髒讀：一個事務讀到了另一個未提交的事務寫的資料。
3、Non-Repeatable Read 不可重複讀：一個事務中兩次讀同一行資料，可是這兩次讀到的資料不一樣。
4、Phantom Read 幻讀：一個事務中兩次查詢，但第二次查詢比第一次查詢多了或少了幾行或幾列資料。

參考：併發問題介紹

3.事務的四個特性

事務四大特性ACID
1、原子性（Atomicity）：一個事務內包含的所有操作要麼成功要麼失敗
2、一致性（Consistency）：指事務的執行並不改變資料庫中資料的一致性.例如,完整性約束了a+b=10,一個事務改變了a,那麼b也應該隨之改變.
3、隔離性（Isolation）：併發事務之間要有隔離性，事務之間的隔離級別是可以設定的
4、永續性（Durability）：指事務如果執行成功後，對資料庫所做的更改會持久的儲存在資料庫裡，不會被無緣無故的回滾。

4.資料庫事務隔離級別：

1、Read Uncommitted 讀未提交:事務讀不阻塞其他事務讀和寫，事務寫阻塞其他事務寫但不阻塞讀。基本不用，會出現髒讀，兩次讀取能讀取到其他事務在期間未提交事務的資料。
2、Read Committed 讀已提交:事務讀不會阻塞其他事務讀和寫，事務寫會阻塞其他事務讀和寫。一個事務只能看見已經提交事務所做的改變，會出現一個事務內兩次select的結果不一樣。這個是常用的事務隔離級別，綜合考慮了效能和資料問題。
3、Repeatable Read 可重複讀:事務讀會阻塞其他事務事務寫但不阻塞讀，事務寫會阻塞其他事務讀和寫。這是MySQL的預設事務隔離級別，它確保同一事務的多個例項在併發讀取資料時，會看到同樣的資料行。可重複讀應該是無法解決提交覆蓋的問題。
4、Serializable 序列化：最高的隔離級別，它通過強制事務排序，使之不可能相互衝突，從而解決幻讀問題。簡言之,它是在每個讀的資料行上加上共享鎖，可能導致大量的超時現象和鎖競爭。

參考：事務隔離介紹

5.資料庫鎖鎖的種類：

共享鎖：特點不阻止其他session讀同一個資源，阻塞update,共享鎖可以同時在同一個資源，容易產生死鎖。
更新鎖：特點不阻止其他session讀同一個資源，阻塞update,共享鎖和更新鎖可以同時在同一個資源上,可以解決死鎖
排他鎖：其它事務既不能讀，又不能改排他鎖鎖定的資源。（可以去實現悲觀鎖）
意向鎖：
計劃鎖：DL語句都會加Sch-M鎖，該鎖不允許任何其它session連線該表。

6.悲觀鎖和樂觀鎖
悲觀鎖：在關聯式資料庫管理系統裡，悲觀併發控制（又名“悲觀鎖”，Pessimistic Concurrency Control，縮寫“PCC”）是一種併發控制的方法。它可以阻止一個事務以影響其他使用者的方式來修改資料。如果一個事務執行的操作都某行資料應用了鎖，那只有當這個事務把鎖釋放，其他事務才能夠執行與該鎖衝突的操作。悲觀鎖的實現，往往依靠資料庫層提供的鎖機制。Mysql InnoDB如果想使用悲觀鎖，需要關閉自動提交屬性（autocommit）,這個是Mysql預設的，Mysql裡可以通過select…for update的方式開啟悲觀鎖，不過Mysql預設是行鎖，而行級鎖都是基於索引的，如果sql用不到索引則不會使用行級鎖，會把整個表鎖住。悲觀鎖實際上是先取鎖再訪問，效率低，降低了並行性，而且會會阻塞其他讀事務，造成了不必要鎖，增加了系統負載。
樂觀鎖：在關聯式資料庫管理系統裡，樂觀併發控制（又名“樂觀鎖”，Optimistic Concurrency Control，縮寫“OCC”）是一種併發控制的方法。它假設多使用者併發的事務在處理時不會彼此互相影響，各事務能夠在不產生鎖的情況下處理各自影響的那部分資料。樂觀鎖並不需要使用資料庫提供的鎖機制，一般只需要比對資料版本即可。樂觀鎖在多個條件巧合下回出現丟失更新的問題。

7、Mysql常用資料庫引擎InnoDB,支援事務、行級鎖、併發效能更好。MYISAM不支援事務，只有表級鎖。

8.專案裡DB層面很多未提交的事務，原因是基本上都是因為出現的併發的DML同一行資料導致的，比如說兩個併發的update同一行資料，後面的update語句而開啟的事務就會等待第一個update執行完畢提交事務才能執行。

9、對於資料庫字符集的選擇上，能用utf8mb4字符集就用64吧，不然還得過濾偏僻字和emoj表情

10、專案裡使用內網域名連結資料庫，不要直接使用ip

11、定義欄位時禁止使用列舉，使用tinyint代替,因為增加列舉型別和減少都得DDL操作，而且資料列舉實際儲存的也是整數

12、禁止在識別度不高的欄位上建立索引，因為基本與全表掃描差不多，比如sex欄位基本只有0/1，而shop_id的識別度就會很高，建立聯合索引的時候，區分度高的放在前面

13、禁止使用屬性隱式轉換，比如phone欄位是varchar型別，但是在用select * from t where phone = 123123會出現無法命中索引的問題。也禁止在where後的欄位上作函式或者表示式。

14、update語句禁止不帶條件，萬一寫錯有很大風險

15、千萬記住後端開發的一點就是，能在伺服器上做的運算、排序儘量在專案裡做，資料庫只做資料的查詢、篩選，將資料庫的壓力轉化到機器。

16、mysql語句執行順序：開始->FROM子句->WHERE子句->GROUP BY子句->HAVING子句->SELECT子句->ORDER BY子句->LIMIT子句->最終結果

17、任何欄位如果為非負數，必須是 unsigned

18、小數型別為 decimal，禁止使用 float 和 double。float 和 double 在儲存的時候，存在精度損失的問題，很可能在值的比較時，得到不正確的結果。

19、合理預估數值的大小，列舉、人類年齡用tinyint等，像shopId、skuId等數值如果預估後面可能會超過int，那麼請定義成bigint.

20、mysql中InnoDB表為什麼要以自增id作為主鍵？

1、因為InnoDB引擎表是基於B+樹的索引組織表(IOT)。而B+樹的特點是
  （1）所有關鍵字都出現在葉子結點的連結串列中(稠密索引)，且連結串列中的關鍵字恰好是有序的;
  （2）不可能在非葉子結點命中;
  （3）非葉子結點相當於是葉子結點的索引(稀疏索引)，葉子結點相當於是儲存(關鍵字)資料的資料層;
  
2、如果我們定義了主鍵(PRIMARY KEY)，那麼InnoDB會選擇主鍵作為聚集索引、如果沒有顯式定義主鍵，則InnoDB會選擇第一個不包含有NULL值的唯一索引作為主鍵索引、如果也沒有這樣的唯一索引，則InnoDB會選擇內建6位元組長的ROWID作為隱含的聚集索引(ROWID隨著行記錄的寫入而主鍵遞增，這個ROWID不像ORACLE的ROWID那樣可引用，是隱含的)。

3、資料記錄本身被存於主索引（一顆B+Tree）的葉子節點上。這就要求同一個葉子節點內（大小為一個記憶體頁或磁碟頁）的各條資料記錄按主鍵順序存放，因此每當有一條新的記錄插入時，MySQL會根據其主鍵將其插入適當的節點和位置，如果頁面達到裝載因子（InnoDB預設為15/16），則開闢一個新的頁（節點）

4、如果表使用自增主鍵，那麼每次插入新的記錄，記錄就會順序新增到當前索引節點的後續位置，當一頁寫滿，就會自動開闢一個新的頁

5、如果使用非自增主鍵（如果身份證號或學號等），由於每次插入主鍵的值近似於隨機，因此每次新紀錄都要被插到現有索引頁得中間某個位置，此時MySQL不得不為了將新記錄插到合適位置而移動資料，甚至目標頁面可能已經被回寫到磁碟上而從快取中清掉，此時又要從磁碟上讀回來，這增加了很多開銷，同時頻繁的移動、分頁操作造成了大量的碎片，得到了不夠緊湊的索引結構，後續不得不通過OPTIMIZE TABLE來重建表並優化填充頁面。