Java 資料庫知識整理

此文記錄了自己學習Java 資料庫的基礎知識，紀念自己面試前的準備。

資料架構

面試：如何設計一個關係型資料庫？

• 邏輯（儲存管理、快取機制、SQL解析、日誌管理、許可權、容災、索引管理、鎖機制）
• 儲存（檔案系統）

資料庫索引

面試：為什麼要用索引

如果不使用索引，需要使用到全表掃描。
使用索引可以快速的查詢資料。

面試：什麼樣的資訊能成為索引？

索引可能的資料結構

• 二叉查詢樹進行二分查詢
• 使用B-Tree結構
• 使用B±Tree結構
• 建立Hash結構

B+Tree 更適合做儲存索引

• B+樹的磁碟讀寫代價更低
• B+樹的查詢效率更加穩定O(logn)
• B+樹更有利於對資料庫的掃描

面試：如何定位並優化慢查詢SQL

• 根據慢日誌定位慢查詢SQL
• 使用explain等工具分析SQL
• 修改SQL使其儘量走索引

聯合索引的最左匹配原則

MySQL會一直向右匹配直到遇到範圍查詢（> < between like）就會停止匹配，比如 a=2 and b=3 and c>5 and d=6如果建立的索引是（a b c d），那麼d是用不到索引的，如果建立的索引是（a b d c）就可以用到。
= 和 in 可以亂序，如果 a=1 and b=2 and c=3 建立（a b c）索引順序任意，MySQL可以優化這種形式。

索引建立的越多越好嗎

• 資料量小的表不需要建立索引，索引需要額外的開銷
• 資料變更需要維護索引，更多的索引需要更多的維護成本
• 索引需要空間

資料庫鎖

MyISAM與InnoDB關於鎖方面的區別

• MyISAM預設使用的表級鎖，不支援行級鎖
• InnoDB預設使用的是行級鎖，也支援表級鎖

給MyISAM表加上讀|寫鎖：
lock tables table_name read | write

給MyISAM表解鎖:
unlock tables;

共享鎖：S鎖，讀鎖
排他鎖：X鎖，寫鎖

給select語句上排它鎖：

select * from table_name for update

給InnoDB表加共享鎖：

select * from table_name where id=xx lock in share mode;

InnoDB在沒有用到索引的時候，用的是表級鎖，用到索引的時候，用的是行級鎖。

MyISANM、InnoDB適用場景

• MyISANM適合全表count語句
• MyISANM適合對資料的增刪改頻率不高，查詢非常頻繁
• MyISANM適合沒有事務
• InnoDB適合增刪改頻率高
• InnoDB適合需要事務支援

鎖的分類

• 按粒度分，分為表級鎖，行級鎖，頁級鎖
• 按級別分，分為共享鎖，排他鎖
• 按方式分，分為自動鎖，顯示鎖
• 按使用分，分為樂觀鎖，悲觀鎖

事務

ACID

• 原子性 Atomic
• 一致性 Consistency
• 隔離性 Isolation
• 永續性 Durability

事務隔離級別

事務在併發情況下出現的問題？

• 更新丟失：事務A的更新記錄被事務B的更新回滾了
• 髒讀：事務A讀到了事務B沒有提交的記錄
• 不可重複讀：由於事務B在更新提交事務，事務A每次讀取的資料可能都不一樣，就算程式能使用了最後一次讀到的資料，總感覺不可靠
• 幻讀：對多行資料的操作，事務A讀到了多行資料，事務B對資料刪除、新增，事務A的更新操作就多了行、少了行。

next-key 鎖 （行鎖+Gap鎖）

語法

GROUP BY

• 滿足’SELECT子句中的列名必須為分組列或函式’
• 列函式對於GROUP BY子句定義的每一個組返回一個結果

HAVING

• 通常與GROUP BY子句一起使用
• WHERE過濾行，HAVING過濾組
• 出現順序，WHERE -> GROUP BY -> HAVING