資料庫知識點積累

文章目錄

• 一、資料庫基礎
• • 理論
  • • 資料庫三範式？
    • 事務的ACID特性？
    • 一緻性與原子性的差別(待完善)
    • 分布式的CAP定理？
    • ACID一緻性與CAP一緻性的差別？
    • 資料庫設計的步驟
  • 查詢專題
  • • 連接配接分類(内外連接配接、自然連接配接、等值連接配接...待完善)
    • 子查詢和連接配接的差別(待完善)
    • 什麼是聯合操作？
  • 日志專題
  • • 日志概述
    • undo日志
    • redo日志
  • 事務專題
  • • 事務的作用
    • 沖突可串行化(優先圖待完善)
    • 資料庫的三種并發控制政策(重要，待完善)？
    • 如何了解COMMIT?
    • 兩階段送出(待完成)
    • 三階段送出(待完成)
    • 并發事務帶來哪些問題？
    • 什麼是MVCC(待補充)?
    • 事務隔離級别有哪些(有疑問未解決)？
    • 為什麼READ-COMMITED不能防止不可重複讀和幻讀(重要)？
    • 為什麼REAPEATABLE-READ不能防止幻讀(重要,TODO)？
    • 樂觀鎖與悲觀鎖詳解(感覺這裡整理的更傾向于Java)
  • 索引專題
  • • 什麼是最左比對
    • B樹與B+樹(重要)
    • B+樹索引與哈希索引的差別
    • AVL與紅黑樹
    • 為什麼通常使用B+樹做索引而不是紅黑樹(感覺沒講清楚)
    • 什麼是全文字檢索？
    • 什麼是聚簇索引/非聚簇索引、主索引/輔助索引?
    • 反向索引
  • 其他
  • • 大表優化方法？
    • 主鍵和外鍵的差別?
    • 什麼是存儲過程?
    • drop、delete與truncate差別？
• 二、MySQL
• • 什麼是存儲引擎？
  • InnoDB的存儲結構，MyISAM的存儲結構(待完成)？
  • MyISAM和InnoDB的差別(持續補充...)?
  • InnoDB下的預設事務隔離？
  • 表的資料量過大，有哪些優化方法？
  • 如何書寫高品質、高性能SQL(待看)?
  • 分庫分表後，原表中的id主鍵如何處理(todo)？
  • 建立索引的指導原則？
  • MySQL主從複制要點？
• 三、Redis
• • Redis基本資料類型？
  • Redis主從複制過程？
  • Redis為什麼可以單線程?
  • 如何保證緩存與資料庫雙寫時的資料一緻性?
  • 緩存穿透
  • 布隆過濾器(待完成)

一、資料庫基礎

理論

資料庫三範式？

• 1NF

  不能表中有表

  ：即確定每列的原子性，每列都是不可再分的最小資料單元
• 2NF

  沒有部分依賴

  ：即滿足1NF的基礎上，非主鍵列不存在對主鍵的部分依賴(比如主鍵由多個列構成，某個屬性不能隻依賴于主鍵中的一列或者部分列)
• 3NF

  沒有傳遞依賴

  :滿足2NF的基礎上，表中的列不存在對非主鍵列的傳遞依賴(A-> B,B->C，C是主鍵B是非主鍵)

  簡記：

  不傳

  => 部分函數依賴、傳遞函數依賴

事務的ACID特性？

什麼是事務?：

事務本質上就是一個線程/程序

，這個線程的控制流是一組操作的集合

A(原子性)：即事務中的各操作要麼全部完成，要麼全部沒有進行操作

C(一緻性)：

資料的某些限制(或者說是資料之間聯系的預期狀況)在事務發生前後沒有改變

(比如A=key(B)，在事務執行前後他們都需要滿足這個關系)；

I(隔離性)：表面看起來每個事務都是在沒有其他事務同時執行的情況下執行的(然而實際上是并發執行的，不過他們構成的是可串行化的排程)

D(持久性)：即一旦事務完成了，則事務對資料庫的影響就不會丢失(持久化到了磁盤)

一緻性與原子性的差別(待完善)

最常見的說法：

一緻性是事務的最終目的，原子性、隔離性、持久性都是為了實作一緻性

一緻性和原子性的差別(個人了解)：一緻性強調的最終狀态，要麼是初始狀态（有可能事務復原了），要麼是最終狀态，事務成功執行後；原子性強調的是操作的完整性，連續的操作不可分割，要麼全部成功，要麼全部失敗。

以下内容參考自：原子性和一緻性之間的關系是什麼

原子性并不能完全保證一緻性。在多個事務并行進行的情況下，即使保證了每一個事務的原子性，仍然可能導緻資料不一緻的結果。例如，事務1需要将100元轉入帳号A：先讀取帳号A的值，然後在這個值上加上100。但是，在這兩個操作之間，另一個事務2修改了帳号A的值，為它增加了100元。那麼最後的結果應該是A增加了200元。但事實上， 事務1最終完成後，帳号A隻增加了100元，因為事務2的修改結果被事務1覆寫掉了。

為了保證并發情況下的一緻性，引入了隔離性，即保證每一個事務能夠看到的資料總是一緻的，就好象其它并發事務并不存在一樣。用術語來說，就是多個事務并發執行後的狀态，和它們串行執行後的狀态是等價的。怎樣實作隔離性，已經有很多人回答過了，原則上無非是兩種類型的鎖：

一種是悲觀鎖，即目前事務将所有涉及操作的對象加鎖，操作完成後釋放給其它對象使用。為了盡可能提高性能，發明了各種粒度（資料庫級/表級/行級……）/各種性質（共享鎖/排他鎖/共享意向鎖/排他意向鎖/共享排他意向鎖……）的鎖。為了解決死鎖問題，又發明了兩階段鎖協定/死鎖檢測等一系列的技術。

一種是樂觀鎖，即不同的事務可以同時看到同一對象（一般是資料行）的不同曆史版本。如果有兩個事務同時修改了同一資料行，那麼在較晚的事務送出時進行沖突檢測。實作也有兩種，一種是通過日志UNDO的方式來擷取資料行的曆史版本，一種是簡單地在記憶體中儲存同一資料行的多個曆史版本，通過時間戳來區分。

分布式的CAP定理？

C(一緻性)：分布式系統中的所有資料備份，在同一時刻各結點的值是否是一緻的(或者說是否相同! 注意此一緻性與ACID的一緻性含義不用)

A(可用性)：不管向什麼結點發起請求，都能正常響應；

P(分區容忍性)：大多數分布式系統都分布在多個子網絡。每個子網絡就叫做一個區（partition）。

分區容錯的意思是，區間通信可能失敗 => 這時候隻能在C于A之間做出選擇

一緻性與可用性的沖突

當區間通信失敗時：

1.如果保證 Node2 的一緻性，那麼 Node1 必須在寫操作時，鎖定 Node2 的讀操作和寫操作。隻有資料同步後，才能重新開放讀寫。鎖定期間，Node2 不能讀寫，沒有可用性。

2.如果保證 Node2 的可用性，那麼勢必不能鎖定 Node2，是以一緻性不成立。

綜上所述，Node2 無法同時做到一緻性和可用性

ACID一緻性與CAP一緻性的差別？

參考：ACID一緻性與CAP一緻性淺談

資料庫設計的步驟

需求分析 : 分析使用者的需求，包括資料、功能和性能需求。

概念結構設計 : 主要采用E-R模型進行設計，包括畫E-R圖。

邏輯結構設計: 通過将E-R圖轉換成表，實作從E-R模型到關系模型的轉換。

實體結構設計 : 主要是為所設計的資料庫選擇合适的存儲結構和存取路徑。

資料庫實施 : 包括程式設計、測試和試運作

資料庫的運作和維護 : 系統的運作與資料庫的日常維護。

查詢專題

連接配接分類(内外連接配接、自然連接配接、等值連接配接…待完善)

内外連接配接的差別

内連接配接

：

結果僅包含符合連接配接條件的兩表中的行

外連接配接

：結果包含符合條件的行，同時包含不符合條件的行（分為左外連接配接、右外連接配接和全外連接配接

暫時參考：深入分析内連接配接、外連接配接、左連接配接、右連接配接、等值連接配接、自然連接配接和自連接配接之間的差別，看這篇就夠了！

如何畫關系代數的連接配接圖？（資料庫關系代數中笛卡兒積、θ連接配接、等值連接配接、自然連接配接、外連接配接）

各種連接配接（Natural Join, Outer Join等）的差別

子查詢和連接配接的差別(待完善)

簡單來看，子查詢會多次周遊所有的資料，而連接配接查詢隻會周遊一次。是以在資料量較大的情況下，應該盡量使用連接配接！

以下來自：sql子查詢和連接配接查詢的差別是什麼呢？

1、子查詢就如遞歸函數一樣,有時侯使用起來能達到事半功倍之效,隻是其執行效率同樣較低,有時用自身連接配接可代替某些子查詢,另外,某些相關子查詢也可改寫成非相關子查詢。

2、表連接配接都可以用子查詢，但不是所有子查詢都能用表連接配接替換，子查詢比較靈活，友善，形式多樣，适合用于作為查詢的篩選條件，而表連接配接更适合與檢視多表的資料。

3、子查詢是一種常用計算機語言SELECT-SQL語言中嵌套查詢下層的程式子產品。當一個查詢是另一個查詢的條件時，稱之為子查詢。

4、子查詢是本質上就是一個完整 的SELECT 語句，它可以使一個 SELECT、SELECT…INTO 語句、INSERT…INTO 語句、DELETE 語句、或 UPDATE 語句或嵌套在另一子查詢中。子查詢的輸出可以包括一個單獨的值(單行子查詢)、幾行值(多行子查詢)、或者多列資料(多列子查詢)。

5、連接配接查詢是關系資料庫中最主要的查詢，主要包括内連接配接、外連接配接和交叉連接配接等。通過連接配接運算符可以實作多個表查詢。連接配接是關系資料庫模型的主要特點，也是它差別于其它類型資料庫管理系統的一個标志。

什麼是聯合操作？

聯合查詢：

将多條select語句的查詢結果進行拼接

，注意這些查詢結果需要字段一緻

select 語句1  union  select 語句2

SELECT * 
FROM employees 
WHERE department_id > 90
OR email LIKE "%a%";

// 上面的OR其實就可以改為聯合查詢
SELECT * FROM employees
WHERE department_id > 90
UNION
SELECT * FROM employees
WHERE email LIKE "%a%";
           

日志專題

日志概述

概述

日志塊最初在主存中建立

，和DMBS所需的其他任何塊一樣

由緩沖區管理器配置設定

。一有可能，日志塊就被寫到磁盤的非易失性存儲中

幾種日志記錄格式/類型

< START T>： 表示事務T開始；

< T,X,v >： 表示事務T對元素X的更新，對于undo日志而言v是舊值；對于redo日志而言v是新值；

< COMMIT T >：表示事務T對資料庫的所有更新都已經反映到磁盤上了；

< ABORT T >：事務T未成功……

undo日志

undo日志記錄

undo日志記錄的是資料庫元素的舊值

(簡記：uncle -> 舅舅 -> 舊)，它是一個三元組：< T,X,v > => 事務T改變了資料庫元素X，而X原來的值是v；

undo日志規則

U1：如果事務T改變了資料庫元素X，那麼形如< T,X,v > 的日志記錄必須在X的新值寫到磁盤之前寫到磁盤中；

U2：如果事務送出，則其COMMIT日志記錄必須在事務改變的所有資料庫元素先寫到磁盤之後寫到磁盤中，但應盡快；

即寫到磁盤的順序為：

先寫三元組日志記錄 -> 再寫資料庫元素 -> 最後寫COMMIT日志記錄

舉例(重要)

事務T既要更改A也要更改B的值，注意以下幾點：

1.上面READ/WRITE隻是事務與記憶體緩沖區的讀/寫(若READ的資料在磁盤上則先執行INPUT……)；

2.

INPUT/OUTPUT才是記憶體緩沖區與磁盤間的讀寫，這個由緩沖區管理器控制，而非事務

；

3.

為了保證三元組日志記錄在資料元素更新前重新整理到磁盤上，注意先使用了FLUSH LOG，然後才是OUTPUT(A/B)

；

……

redo日志

redo日志記錄

redo日志記錄的是資料庫元素的新值

，它也是一個三元組：< T,X,v > => 事務T改變了資料庫元素X，而X的新值就是v；

redo日志規則

R1(先寫日志規則)：在修改磁盤上的任何資料庫元素以前，要保證與X這一修改相關的所有日志記錄，包括< T,X,v >以及< COMMIT T >都已經出現在磁盤上

即寫到磁盤的順序為：

先寫三元組日志記錄 -> 再寫COMMIT日志記錄 -> 最後寫資料庫元素

(先寫完所有日志再更新資料庫元素)

舉例

注意為了保證所有日志已經先寫到磁盤上，在調用OUTPUT(A/B)前必須先執行FLUSH LOG

!!!

事務專題

事務的作用

1.給日志管理器發送信号，使必須的資訊能夠以“日志記錄”的形式存儲在日志中；

2.保證并發執行的事務不會以引入錯誤的方式互相幹擾(事務并發控制的最終目的是保證資料的一緻性)；

(參考自《資料庫系統實作》)

沖突可串行化(優先圖待完善)

不沖突，可交換的動作(下面i、j分别代表事務i、事務j，且 i ! = j i!=j i!=j)

r i ( X ) r_i(X) ri​(X)與 r j ( Y ) r_j(Y) rj​(Y)從不會是沖突的，即使 X = Y X=Y X=Y；

r i ( X ) r_i(X) ri​(X)與 w j ( Y ) w_j(Y) wj​(Y)不會是沖突的，隻要 X ≠ Y X\neq Y X​=Y；

w i ( X ) w_i(X) wi​(X)與 r j ( Y ) r_j(Y) rj​(Y)不會是沖突的，隻要 X ≠ Y X\neq Y X​=Y；

w i ( X ) w_i(X) wi​(X)與 w j ( Y ) w_j(Y) wj​(Y)不會是沖突的，隻要 X ≠ Y X\neq Y X​=Y；

沖突，不可交換的動做(

重要

)

a) 同一事務的兩個動作，如 r i ( X ) r_i(X) ri​(X)與 w j ( Y ) w_j(Y) wj​(Y)總是沖突的 => 單個事務的動作順序是固定的，不能交換；

b) 不同僚務對同一資料庫元素的寫沖突，比如 w i ( X ) w_i(X) wi​(X)與 w j ( X ) w_j(X) wj​(X)。(其實隻要有一個是寫就會沖突，即c);

c) 不同僚務對同一資料庫元素的讀和寫也沖突，比如 r i ( X ) 與 w j ( X ) r_i(X)與w_j(X) ri​(X)與wj​(X)、 w i ( X ) w_i(X) wi​(X)與 r j ( X ) r_j(X) rj​(X)；

結論(重要)

1.

同一事務的兩個動作總是不能交換的

；

2.

不同僚務的兩個動作可以交換，除以下特殊情況

：

特殊情況：這兩個動作涉及同一個資料庫元素且至少有一個動作是寫!! => 這時候沖突不能交換

沖突可串行化

若某個排程能個根據上面的交換規則轉換為串行化的排程，則稱這個排程是沖突可串行化的。如下例子：

判斷沖突可串行化

優先圖，帶完善……

資料庫的三種并發控制政策(重要，待完善)？

概述

并發控制，即保證并發執行的事務

在某一隔離級别上

的正确執行的方法。

關于以下的内容，詳見《資料庫系統實作》第七章；另外還可參考：淺析資料庫并發控制

注：很多面經常提到的

MVCC

，其實就是封鎖、時間戳、有效性确認的多版本實作！

1.封鎖

2.時間戳

3.有效性确認

對将要執行的事務T，判斷它要操作的資料D1與其他并發事務正在操作的資料D2是否有交集，如果資料有交集，則不能執行事務T，否則可以執行，這就是有效性确認的原理

如何了解COMMIT?

commit隻是請求寫日志，表示事務已經操作完成

!!!

兩階段送出(待完成)

三階段送出(待完成)

并發事務帶來哪些問題？

1.髒讀

定義：若事務T1讀取的資料是事務T2剛修改但是尚未送出的資料，則這個資料為髒資料(dirty就是指記憶體中的資料與磁盤不一緻)。

解釋：事務送出前，修改的資料在記憶體緩沖區中。對于未送出的寫事務T2，緩沖區中的資料與磁盤上的資料不同。此時如果事務T1讀該資料，它讀到的是緩沖區中剛修改的資料，而不是磁盤上的資料。若T2在送出前發生了復原，則緩沖區中的資料變回原來的資料，與T1讀到的資料就不一緻了！

2.髒寫(丢失修改)

事務T1和T2并發修改同一個資料，假設T1先修改資料但是尚未送出，接着T2又修改資料(目前為止修改的資料都在緩沖區中，與磁盤上的資料并不一緻)。若T1崩潰，則T1復原導緻資料變回最開始的值，這種情況對T2來說就是“丢失修改”(髒寫)

3.不可重複讀

事務T1要重複讀取資料，若T1第一次讀取資料後，T2修改了資料，之後T1再第二次讀取資料。則對T1來說兩次讀的資料不一緻，即不可重複讀

4.幻讀

事務T1要重複讀取多行資料，若T1第一次讀取多行資料後，T2對這些資料進行了插入或者删除，之後T1再第二次讀取這些資料。則對T1來說讀了一些之前不存在的資料=> 幻讀

不可重複讀描述的是修改資料；幻讀描述的是插入删除資料

小結

造成這些問題的關鍵就是對資料的修改是在緩沖區進行，且在事務送出前緩沖區的資料與磁盤的資料不一緻。

什麼是MVCC(待補充)?

Multi-Version Concurrency Control，即多版本并發控制；

概述

應對高并發，MVCC比單純的加鎖高更高效；

MVCC隻在READ COMMITED和REPEATABLE READ兩個隔離級别下工作

；MVCC可以使用樂觀鎖和悲觀鎖來實作(不同資料庫中實作方式不同)

事務隔離級别有哪些(有疑問未解決)？

四個隔離級别

1.READ-UNCOMMITTED(讀取未送出)：最低隔離級别，允許讀取尚未送出的資料 => 可能導緻髒讀、不可重複讀、幻讀

2.READ-COMMITTED(讀取已送出)：隻能讀取其他事務已經送出的資料 => 防止了髒讀，但是不能避免不可重複讀、幻讀

3.REPEATABLE-READ(可重複讀)：讀同一字段的多次讀取結果都是一緻的，除非資料是被本事務自己所修改(可通過版本機制實作) => 避免了髒讀、不可重複讀；但是不能避免幻讀(為什麼

?

)

4.SERIALIZABLE(可串行化)：最高的隔離級别，完全滿足ACID。所有并發的事務等價于事務逐個依次執行 => 避免髒讀、不可重複讀、幻讀

• 小結

隔離級别	髒讀	不可重複讀	幻讀
READ-UNCOMMITTED	√	√	√
READ-COMMITTED	X	√	√
REAPEATABLE-READ	X	X	√
SERIALIZABLE	X	X	X

為什麼READ-COMMITED不能防止不可重複讀和幻讀(重要)？

參考：Read Committed 為什麼不能防止不可重複讀現象 => 講得挺好，必看!

Read Committed在事務中

每次讀操作都是讀取最新的行資料版本

，而這最新的資料行版本很可能是某個事務進行了修改操作後送出的，是以可能會發生多次讀取同一行資料，但是前後讀取的資料不一緻的情況。這就是不可重複讀現象，是以送出讀不能避免不可重複度現象。

Repeatable Read在事務發生

第一次讀的時候標明所要讀取的資料行的版本

，整個事務都讀取這一個版本的資料行，是以可以重複讀，每次讀取的資料都一緻。

=> 他們都沒有使用鎖!!!并不滿足真正的原子性…

為什麼REAPEATABLE-READ不能防止幻讀(重要,TODO)？

樂觀鎖與悲觀鎖詳解(感覺這裡整理的更傾向于Java)

1.悲觀鎖(适用于多寫)

每次讀寫資料的時候都認為别的線程會修改，是以

每次在通路資料(不管讀還是寫)的時候都會上鎖

，這樣其他線程想拿這個資料就會阻塞直到它拿到鎖（共享資源每次隻給一個線程使用，其它線程阻塞，用完後再把資源轉讓給其它線程）。

傳統的關系型資料庫裡邊就用到了很多這種鎖機制，比如行鎖，表鎖等，讀鎖，寫鎖等，都是在做操作之前先上鎖

。

Java悲觀鎖

:Java中synchronized和ReentrantLock等獨占鎖就是悲觀鎖思想的實作。

2.樂觀鎖(适用于多讀)

每次去讀資料的時候都認為别的線程不會修改，是以不會上鎖，但是在更新的時候會判斷一下在此期間别的線程有沒有去更新這個資料，可以使用版本号機制和CAS算法實作

。樂觀鎖适用于多讀的應用類型，這樣可以提高吞吐量，像資料庫提供的類似于

write_condition

機制，其實都是提供的樂觀鎖。

Java樂觀鎖

：在Java中java.util.concurrent.atomic包下面的原子變量類就是使用了樂觀鎖的一種實作方式CAS實作的。

樂觀鎖的兩種實作方式

1.版本号機制

一般是在資料表中加上一個資料版本号

version字段

，表示資料被修改的次數，當資料被修改時，version值會加一。當線程A要更新資料值時，在讀取資料的同時也會讀取version值，在送出更新時，若剛才讀取到的version值與目前資料庫中的version值相等時才更新，否則重試更新操作，直到更新成功。

2.CAS

即compare and swap（比較與交換），是一種有名的無鎖算法。無鎖程式設計，即不使用鎖的情況下實作多線程之間的變量同步，也就是在沒有線程被阻塞的情況下實作變量的同步，是以也叫非阻塞同步（Non-blocking Synchronization）。CAS算法涉及到三個操作數 =>(需要讀寫的記憶體值 V,進行比較的值 A,拟寫入的新值B)；當且僅當 V 的值等于 A時，CAS通過原子方式用新值B來更新V的值，否則不會執行任何操作（比較和替換是一個原子操作）。一般情況下是一個自旋操作，即不斷的重試。

CAS的問題：ABA => 詳見Java并發部分…

索引專題

什麼是最左比對

以最左邊的為起點任何連續的索引都能比對上。同時

遇到範圍查詢(>、<、between、like)就會停止比對

參考：最左比對原則

B樹與B+樹(重要)

B樹

對于一顆m階B樹：

1.每個結點最多m個孩子(m對應最大指針數)；

2.除了根節點和葉子節點外，其它每個節點至少有Ceil(m/2)個孩子；

3.關鍵字的個數n滿足：ceil(m/2)-1 <= n <= m-1；

4.ki(i=1,…n)為關鍵字，且

關鍵字升序排序

；

……

B+樹

(下圖為聚簇索引，因為葉子結點存儲了資料)

差別

1.

B+樹的非葉子節點隻存儲指針資訊，而B樹所有節點都可能存儲資料

(很重要)！

1.

B+樹的葉子節點從左到右進行了連結，進而友善範圍查詢

(這也是B+樹内部結點不存資料的原因)；

3.

B+樹的資料記錄都存放在葉子節點中(聚簇)

=>

不過也可能是放在葉子結點指針指向的資料檔案位置處(非聚簇)，比如redbase

B+樹索引與哈希索引的差別

如果是等值查詢，那麼哈希索引明顯有絕對優勢，因為隻需要經過一次算法即可找到相應的鍵值；當然了，這個前提是，鍵值都是唯一的。如果鍵值不是唯一的，就需要先找到該鍵所在位置，然後再根據連結清單往後掃描，直到找到相應的資料。

如果是範圍查詢檢索，這時候哈希索引就毫無用武之地了，因為原先是有序的鍵值，經過雜湊演算法後，有可能變成不連續的了，就沒辦法再利用索引完成範圍查詢檢索。

哈希索引也沒辦法利用索引完成排序

，以及like ‘xxx%’ 這樣的部分模糊查詢（這種部分模糊查詢，其實本質上也是範圍查詢）。

哈希索引也不支援多列聯合索引的最左比對規則

。

在有大量重複鍵值情況下，哈希索引的效率也是極低的，因為存在所謂的哈希碰撞問題

。

AVL與紅黑樹

暫時參考：面試題之資料庫

=> 滑到底部

為什麼通常使用B+樹做索引而不是紅黑樹(感覺沒講清楚)

1.作業系統從磁盤讀取檔案，通常是按頁讀取；

2.B+樹通常一個結點對應一頁；

3.B+樹層數更少，進而IO更少；

4.對于紅黑樹，一個父節點隻有 2 個子節點，并不能占滿整個頁，是以加載結點時存在浪費

更多參考：資料庫為什麼要用B+樹結構–MySQL索引結構的實作

什麼是全文字檢索？

與全文本搜尋功能類似的有通配符和正規表達式比對，但性能較低，通常會比對表的所有行，而且這些搜尋極少使用表索引，不能做到明确控制，且傳回的結果不智能化；

在使用全文本搜尋時，mysql不需要分别檢視每個行，不需要分析和處理每個詞，隻需索引被搜尋的列（需要随着資料的改變不斷重新索引 =>

簡單說就是對經常檢索的文本字段進行索引，進而提高檢索效率

!)

什麼是聚簇索引/非聚簇索引、主索引/輔助索引?

聚簇索引：簡單說就是資料就存放在索引檔案中，比如

InnoDB的B+樹葉子結點就存儲了資料

；

非聚簇索引：索引檔案與資料檔案分開存放，比如

MyISAM的B+樹葉子結點隻有指針，指向資料檔案中記錄的位置，結點中并無資料記錄

；

主索引：

主索引能确定記錄在資料檔案中的位置

，而輔助索引不能，通常以主鍵作為主索引；

輔助索引：

輔助索引不能決定記錄在資料檔案中存放的位置

，僅能告訴我們記錄的目前存放位置，這一

位置可能由建立在其他某個字段上的主索引确定

；因為輔助索引不能影響記錄的存儲位置，是以不能根據它來預測鍵值不在索引中顯式指明的任何記錄的位置 =>

輔助索引必須是稠密的

InnoDB的輔助索引：InnoDB的輔助索引的value存儲的是主鍵值，然後根據主鍵去查主索引，而不是說直接存儲了記錄位置…

更多資料可參考《資料庫系統實作》……

反向索引

不是由記錄編号(位置)來确定屬性值，而是由屬性值來确定記錄位置

=> 比如文字檢索，不是由字元串(記錄)來查找單詞，而是給單詞建立索引，查找單詞所在的記錄(文本行)

其他

大表優化方法？

1. 限定資料的範圍

務必禁止不帶任何限制資料範圍條件的查詢語句。比如：我們當使用者在查詢訂單曆史的時候，我們可以控制在一個月的範圍内；

2. 讀/寫分離

經典的資料庫拆分方案，主庫負責寫，從庫負責讀；

3. 垂直分區

資料表列的拆分，把一張列比較多的表拆分為多張表。

優點： 可以使得列資料變小，在查詢時

減少讀取的Block數，減少I/O次數

。此外，垂直分區可以

簡化表的結構，易于維護

。

缺點：

主鍵會出現備援

，需要管理備援列，并會引起Join操作，可以通過在應用層進行Join來解決。此外，垂直分區會讓

事務變得更加複雜

；

4. 水準分區

存儲資料分片。這樣每一片資料分散到不同的表或者庫中，達到了分布式的目的。水準拆分可以支撐非常大的資料量。

水準拆分是指資料表行的拆分，表的行數超過200萬行時，就會變慢，這時可以把一張的表的資料拆成多張表來存放。舉個例子：我們可以将使用者資訊表拆分成多個使用者資訊表，這樣就可以避免單一表資料量過大對性能造成影響。

優點：

水準拆分可以支援非常大的資料量

。需要注意的一點是：

分表僅僅是解決了單一表資料過大的問題，但由于表的資料還是在同一台機器上，其實對于提升MySQL并發能力沒有什麼意義，是以 水準拆分最好分庫

。

缺點：水準拆分能夠支援非常大的資料量存儲，應用端改造也少，但

分片事務難以解決，跨節點Join性能較差

，邏輯複雜

主鍵和外鍵的差別?

主鍵用于

唯一辨別一個元組

，

不能有重複

，

不允許為空

。一個表隻能有一個主鍵

外鍵：外鍵

用來和其他表建立聯系

用，外鍵是另一表的主鍵，外鍵是

可以有重複的，可以是空值

。一個表可以有多個外鍵。

什麼是存儲過程?

存儲過程是一些

SQL 語句的集合，中間加了點邏輯控制語句

。存儲過程在業務比較複雜的時候是非常實用的，比如很多時候我們完成一個操作可能需要寫一大串SQL語句，這時候我們就可以寫有一個存儲過程，這樣也友善了我們下一次的調用。存儲過程一旦調試完成通過後就能穩定運作，另外，

使用存儲過程比單純SQL語句執行要快，因為存儲過程是預編譯過的

。存儲過程在網際網路公司應用不多，因為

存儲過程難以調試和擴充，而且沒有移植性，還會消耗資料庫資源

。

drop、delete與truncate差別？

drop(丢棄資料): drop table 表名 ，直接将表都删除掉，在删除表的時候使用，

會删除表的定義

。

truncate (清空資料) : truncate table 表名 ，隻删除表中的資料，再插入資料的時候自增長

id又從1開始

，在清空表中資料的時候使用。

delete(删除資料) : delete from 表名 where 列名=值，删除某一列的資料，如果不加 where 子句和truncate table 表名作用類似。

truncate 和不帶 where 子句的 delete、以及 drop 都會删除表内的資料，但是 truncate 和 delete 隻删除資料不删除表的結構(定義)，執行drop語句，此表的結構也會删除，也就是執行 drop 之後對應的表不複存在。

執行速度

drop>truncate>delete

二、MySQL

什麼是存儲引擎？

MySQL中的資料用各種不同的技術存儲在檔案(或者記憶體)中。這些技術中的每一種技術都使用不同的

存儲機制

、

索引技巧

、

鎖定水準

并且最終提供廣泛的不同的功能和能力…(來自百度百科) => 可見存儲引擎主要負責檔案管理、索引管理、事務管理等

InnoDB的存儲結構，MyISAM的存儲結構(待完成)？

MyISAM和InnoDB的差別(持續補充…)?

索引

、

鎖

、

事務

、

外鍵

、

MVCC

• 0.索引結構

  InnoDB 使用的是聚集索引

  ，MyISAM使用非聚集索引。

  聚簇索引：檔案存放在主鍵索引的葉子節點上(即資料和索引檔案在同一個檔案中)，是以 InnoDB 必須要有主鍵，通過主鍵索引效率很高。但是輔助索引需要兩次查詢，先查詢到主鍵，然後再通過主鍵查詢到資料。是以，主鍵不應該過大，因為主鍵太大，其他索引也都會很大。

  非聚集索引的話，資料檔案是分離的，索引儲存的是資料檔案的指針。主鍵索引和輔助索引是獨立的。

• 1.是否支援行級鎖

  MyISAM隻有表級鎖

  ；

  InnoDB支援行級鎖和表級鎖，預設為行級鎖；

  (關于鎖的細節可參考《資料庫系統實作》第七章)

• 2.是否支援事務

  MyISAM強調性能，雖然每次查詢具有原子性，但是它

  不能保證事務的其他特性(CID)

  ；

  InnoDB支援事務，具有事務、復原、崩潰修複能力…

• 3.是否支援外鍵

  MyISAM不支援外鍵

  ；InnoDB支援外鍵

• 4.是否支援MVCC

  Multi-Version Concurrency Control，即多版本并發控制；

  MyISAM不支援MVCC

  ；

  僅InnoDB支援

• 5.崩潰後恢複

  MyISAM崩潰後無法安全恢複

  ；

  InnoDB可以從災難中恢複.

• x.InnoDB較于MyISAM的缺點

  InnoDB不支援全文字檢索；

  InnoDB多半比MyISAM慢(不是一定)

InnoDB下的預設事務隔離？

InnoDB預設的隔離級别是REAPEATABLE-READ(可重複讀)。不過，由于InnoDB使用了Next-Key Lock算法，可以避免幻讀，是以

它實質上達到了SERIALIZABLE的級别

;

在分布式事務的情況下，InnoDB一般會使用SERIALIZABLE隔離級别

表的資料量過大，有哪些優化方法？

• 限定查詢資料的範圍(條數)
• 讀寫分離
• 垂直拆分
• 水準拆分：

  水準拆分，不僅要分表，最好還要分庫

  …

  詳細參考：MySQL大表優化方案(必看)

如何書寫高品質、高性能SQL(待看)?

MySQL高性能優化規範建議

後端程式員必備：書寫高品質SQL的30條建議

分庫分表後，原表中的id主鍵如何處理(todo)？

建立索引的指導原則？

• 選擇唯一性索引

  ：即盡量選擇在整個屬性列中值重複較少的字段；

• 為常作為查詢條件的字段建立索引

  ；

• 為經常需要排序、分組、聯合操作的字段建立索引

  ；

• 盡量使用資料量少的字段建立索引

  ：如果索引值很長，那麼查詢速度受到影響

• 限制索引數目

  ：越多的索引，會是更新表表邊變得浪費時間

• 最左字首比對原則

  …

MySQL主從複制要點？

三、Redis

Redis基本資料類型？

五類：string、list、set、hash、sortset

注意：redis是K-V資料庫 =>

任何資料的K都是string類型，資料的V才是上述五種類型之一

； 注意不同類型的應用場景

Redis主從複制過程？

1.與master建立連接配接；

2.向master發送同步請求；

3.接收master發送來的RDB資料；

4.将RDB載入記憶體；

Redis為什麼可以單線程?

因為

cpu不是redis的性能瓶頸

(僅僅從記憶體取資料，并不是計算密集的)，真正的瓶頸在于記憶體或網絡帶寬(将資料傳輸到用戶端)

如何保證緩存與資料庫雙寫時的資料一緻性?

• 經典的緩存+資料庫模式

  1.讀的時候，先讀緩存，緩存沒有的話，就讀資料庫，然後取出資料後放入緩存，同時傳回響應；

  2.更新的時候，

  先更新資料庫，然後再删除緩存

  (要了解為什麼删除緩存而不是更新緩存)。

• 寫在前面：部分情況下允許不一緻

  如果系統不是嚴格要求緩存和資料庫必須一緻性的話，緩存可以稍微的跟資料庫偶爾有不一緻的情況，最好不要做這個方案；

• 初級的不一緻性問題

  對于經典的操作 => 先修改資料庫，再删除緩存。

  如果删除緩存失敗了，那麼會導緻資料庫中是新資料，緩存中是舊資料，資料就出現了不一緻

  。

  解決方法：

  先删除緩存，再修改資料庫

  。如果資料庫修改失敗了，那麼資料庫中是舊資料，緩存中是空的，那麼資料不會不一緻。因為讀的時候緩存沒有，則讀資料庫中舊資料，然後更新到緩存中。

• 複雜的不一緻性問題

  對于上面改進後的方式=> 先删除緩存，然後再修改資料庫。若此時還沒修改，一個請求過來，去讀緩存，發現緩存空了，去查詢資料庫，查到了修改前的舊資料，放到了緩存中。随後資料變更的程式完成了資料庫的修改。完了，資料庫和緩存中的資料不一樣了…

  解決方法：

  更新資料的時候

  ，根據資料的唯一辨別，将操作路由之後，發送到一個 jvm 内部隊列中；

  讀取資料的時候

  ，如果發現資料不在緩存中，那麼将重新讀取資料+更新緩存的操作，根據唯一辨別路由之後，也發送同一個 jvm 内部隊列中(這個解決方案

  本質就是通過異步的方法，讓并發的請求串行化了

  …)

  缺點：就會導緻系統的吞吐量會大幅度的降低，用比正常情況下多幾倍的機器去支撐線上的一個請求。

  注意：這個不一緻性問題其實是由于高并發造成的…

  更多講解參考

  ：如何保證緩存與資料庫的雙寫一緻性？(講得好，必看!)

緩存穿透

一般是黑客故意去請求緩存中不存在的資料，導緻所有的請求都落到資料庫上，造成資料庫短時間内承受大量請求而崩掉。

• 解決辦法

  有很多種方法可以有效地解決緩存穿透問題：

  1. 最常見的則是采用布隆過濾器(待學習)，将所有可能存在的資料哈希到一個足夠大的bitmap中，一個一定不存在的資料會被 這個bitmap攔截掉，進而避免了對底層存儲系統的查詢壓力；

  2. 另外也有一個更為簡單粗暴的方法（我們采用的就是這種），如果一個查詢傳回的資料為空（不管是資料不存在，還是系統故障），我們仍然把這個空結果進行緩存，但它的

  過期時間會很短

  ，最長不超過五分鐘。

布隆過濾器(待完成)

布隆過濾器-百度百科

……