資料庫常見問題

---

MyISAM和InnoDB

---

主要區別：

1).［事務］MyISAM是非事務安全型的，而InnoDB是事務安全型的，預設開啟自動提交，宜合併事務，一同提交，減小資料庫多次提交導致的開銷，大大提高效能。
2).［鎖］MyISAM鎖的粒度是表級，而InnoDB支援行級鎖定。
3).［全文索引］MyISAM支援全文型別索引，而InnoDB不支援全文索引。
4).［查詢效率］MyISAM相對簡單，所以在效率上要優於InnoDB，小型應用可以考慮使用MyISAM。
5).［外健］MyISAM不支援外健，InnoDB支援。
6).［count］MyISAM保有表的總行數，InnoDB只能遍歷。
6).MyISAM表是儲存成檔案的形式，在跨平臺的資料轉移中使用MyISAM儲存會省去不少的麻煩。
7).InnoDB表比MyISAM表更安全，可以在保證資料不會丟失的情況下，切換非事務表到事務表（alter table tablename type=innodb）。
8）MyIsam索引和資料分離，InnoDB在一起，MyIsam天生非聚簇索引，最多有一個unique的性質，InnoDB的資料檔案本身就是主鍵索引檔案，這樣的索引被稱為“聚簇索引”
9）InnoDB提供多版本資料支援 ，MyIsam不支援
10）兩者都僅支援B＋樹索引，不支援hash索引

應用場景：
1).MyISAM管理非事務表。它提供高速儲存和檢索，以及全文搜尋能力。如果應用中需要執行大量的SELECT查詢，那麼MyISAM是更好的選擇。
2).InnoDB用於事務處理應用程式，具有眾多特性，包括ACID事務支援。如果應用中需要執行大量的INSERT或UPDATE操作，則應該使用InnoDB，這樣可以提高多使用者併發操作的效能。

---

資料庫索引

---

（一）在瞭解資料庫索引之前，首先了解一下資料庫索引的資料結構基礎，B+tree

B＋tree 是一個n叉樹，每個節點有多個葉子節點，一顆B+樹包含根節點，內部節點，葉子節點。根節點可能是一個葉子節點，也可能是一個包含兩個或兩個以上葉子節點的節點。

B＋tree的性質：

1.n棵子tree的節點包含n個關鍵字，不用來儲存資料而是儲存資料的索引。

2.所有的葉子結點中包含了全部關鍵字的資訊，及指向含這些關鍵字記錄的指標，且葉子結點本身依關鍵字的大小自小而大順序連結。

3.所有的非終端結點可以看成是索引部分，結點中僅含其子樹中的最大（或最小）關鍵字。

B＋tree結構原型圖大概如下（引用）：

由於B+tree的性質， 它通常被用於資料庫和作業系統的檔案系統中。NTFS, ReiserFS, NSS, XFS, JFS, ReFS 和BFS等檔案系統都在使用B+樹作為後設資料索引，因為B+ 樹的特點是能夠保持資料穩定有序，其插入與修改擁有較穩定的對數時間複雜度（B+ 樹元素自底向上插入）。

（二）資料庫索引

資料庫索引是用於提高資料庫表的資料訪問速度的。

資料庫索引的特點：

a）避免進行資料庫全表的掃描，大多數情況，只需要掃描較少的索引頁和資料頁，而不是查詢所有資料頁。而且對於非聚集索引，有時不需要訪問資料頁即可得到資料。

b）聚集索引可以避免資料插入操作，集中於表的最後一個資料頁面。

c）在某些情況下，索引可以避免排序操作。

聚集（clustered）索引，也叫聚簇索引。

定義：資料行的物理順序與列值（一般是主鍵的那一列）的邏輯順序相同，一個表中只能擁有一個聚集索引。

一、為什麼要建立索引呢（優點）？
這是因為，建立索引可以大大提高系統的效能。
第一， 通過建立唯一性索引，可以保證資料庫表中每一行資料的唯一性。
第二， 可以大大加快資料的檢索速度，這也是建立索引的最主要的原因。
第三， 可以加速表和表之間的連線，特別是在實現資料的參考完整性方面特別有意義。
第四， 在使用分組和排序子句進行資料檢索時，同樣可以顯著減少查詢中分組和排序的時間。
第五， 通過使用索引，可以在查詢的過程中，使用優化隱藏器，提高系統的效能。

二、建立方向索引的不利因素（缺點）
也許會有人要問：增加索引有如此多的優點，為什麼不對錶中的每一個列建立一個索引呢？這種想法固然有其合理性，然而也有其片面性。雖然，索引有許多優點，但是，為表中的每一個列都增加索引，是非常不明智的。這是因為，增加索引也有許多不利的一個方面。

第一， 建立索引和維護索引要耗費時間，這種時間隨著資料量的增加而增加。
第二， 索引需要佔物理空間，除了資料表佔資料空間之外，每一個索引還要佔一定的物理空間，如果要建立聚簇索引，那麼需要的空間就會更大。
第三， 當對錶中的資料進行增加、刪除和修改的時候，索引也要動態的維護，這樣就降低了資料的維護速度。

三、建立方向索引的準則
索引是建立在資料庫表中的某些列的上面。因此，在建立索引的時候，應該仔細考慮在哪些列上可以建立索引，在哪些列上不能建立索引。
一般來說，應該在這些列上建立索引。
第一， 在經常需要搜尋的列上，可以加快搜尋的速度；
第二， 在作為主鍵的列上，強制該列的唯一性和組織表中資料的排列結構；
第三， 在經常用在連線的列上，這些列主要是一些外來鍵，可以加快連線的速度；
第四， 在經常需要根據範圍進行搜尋的列上建立索引，因為索引已經排序，其指定的範圍是連續的；
第五， 在經常需要排序的列上建立索引，因為索引已經排序，這樣查詢可以利用索引的排序，加快排序查詢時間；
第六， 在經常使用在WHERE子句中的列上面建立索引，加快條件的判斷速度。

---

資料庫優化

---

一、百萬級資料庫優化方案

1.對查詢進行優化，要儘量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引。

2.應儘量避免在 where 子句中對欄位進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描

3.應儘量避免在 where 子句中使用 != 或 <> 操作符，否則將引擎放棄使用索引而進行全表掃描。

4.應儘量避免在 where 子句中使用 or 來連線條件，如果一個欄位有索引，一個欄位沒有索引，將導致引擎放棄使用索引而進行全表掃描

5.in和 not in 也要慎用，否則會導致全表掃描

6.下面的查詢也將導致全表掃描：

   select id from t where name like ‘%abc%’

7.如果在 where 子句中使用引數，也會導致全表掃描。因為SQL只有在執行時才會解析區域性變數，但優化程式不能將訪問計劃的選擇推遲到執行時；它必須在編譯時進行選擇。然 而，如果在編譯時建立訪問計劃，變數的值還是未知的，因而無法作為索引選擇的輸入項。

8.應儘量避免在 where子句中對欄位進行表示式操作，這將導致引擎放棄使用索引而進行全表掃描。

9.應儘量避免在where子句中對欄位進行函式操作，這將導致引擎放棄使用索引而進行全表掃描。

SQL什麼條件會使用索引？

當欄位上建有索引時，通常以下情況會使用索引：

INDEX_COLUMN = ?

INDEX_COLUMN > ?

INDEX_COLUMN >= ?

INDEX_COLUMN < ?

INDEX_COLUMN <= ?

INDEX_COLUMN between ? and ?

INDEX_COLUMN in (?,?,...,?)

INDEX_COLUMN like ?||'%'（後導模糊查詢）

T1. INDEX_COLUMN=T2. COLUMN1（兩個表通過索引欄位關聯）

分庫分表

分庫分表的目的就是講一個資料庫切分成多個部分放到不同的資料庫上，以便緩解單一資料庫的效能問題。

舉個例子吧，當你面對海量資料的資料庫時，若歸因為表多導致的資料量大的話，建議採用垂直切分，就是把關係緊密（比如一個應用模組的表）的表

放在一個資料庫（server）。如果表並不多，但是每張表的資料非常的多，那就要採用水平切分了。就是將表按照某種規則(常用方法是按ID雜湊)切分到多個資料庫（server）上。當然這只是舉了一個例子，因為現實情況要比這個複雜，需要將兩種切分方式聯合使用才能使得系統效能更優（因為可能一張表的資料量很大，超出了一臺伺服器的能力範圍，這時候就要在垂直拆分的基礎上再進行水平拆分）。

資料庫的垂直切分和水平切分

垂直切分：就是按功能模組拆分，比如分為訂單庫，商品庫，使用者庫。垂直切分最大的特點就是簡單明瞭，實施方便。

水平切分：將同一個表的資料進行分塊儲存到不同的資料庫。

上面呢是兩個概念，我們來看一個具體的例子，有助於我們理解：

案例：簡單購物系統，涉及以下幾張表

1、產品表（資料量10w,穩定）

2、訂單表（資料量200W，且有增長趨勢）

3、使用者表（資料量100w，且有增長趨勢）

以mysql為例講述下水平拆分和垂直拆分，mysql資料庫能容忍的資料量級在百萬靜態資料到千萬

垂直拆分：

解決問題：表與表之間的io競爭

不解決的問題：但表中資料量增長出現的壓力

方案：把產品表和使用者表放到一個server上，訂單表單獨放到一個server上

水平拆分：

解決問題：單表中資料量增長出現的壓力

不解決的問題：表與表之間的io競爭

方案：使用者表通過性別拆分為男使用者表和女使用者表；訂單表通過已完成和完成中拆分為已完成訂單和未完成訂單；產品表未完成訂單放一個server上，已完成訂單表和男使用者表放一個server上，女使用者表放一個server上（女的愛購物，資料增量大）

---

ACID與事物

---

一個事務本質上有四個特點ACID：

Atomicity原子性
Consistency一致性
Isolation隔離性
Durability耐久性

原子性

原子性任務是一個獨立的操作單元，是一種要麼全部是，要麼全部不是的原子單位性的操作。
一致性

一個事務可以封裝狀態改變（除非它是一個只讀的）。事務必須始終保持系統處於一致的狀態，不管在任何給定的時間併發事務有多少。

一致性有下面特點：

如果一個操作觸發輔助操作（級聯，觸發器），這些也必須成功，否則交易失敗。
如果系統是由多個節點組成，一致性規定所有的變化必須傳播到所有節點（多主複製）。如果從站節點是非同步更新，那麼我們打破一致性規則，系統成為“最終一致性”。
一個事務是資料狀態的切換，因此，如果事務是併發多個，系統也必須如同序列事務一樣操作。

在現實中，事務系統遭遇併發請求時，這種序列化是有成本的， Amdahl法則描述如下：它是描述序列序列執行和併發之間的關係。

“一個程式在平行計算情況下使用多個處理器所能提升的速度是由這個程式中序列執行部分的時間決定的。”

大多數資料庫管理系統選擇（預設情況下）是放寬一致性，以達到更好的併發性。

隔離性

事務是併發控制機制，他們交錯使用時也能提供一致性。隔離讓我們隱藏來自外部世界未提交的狀態變化，一個失敗的事務不應該破壞系統的狀態。隔離是通過用悲觀或樂觀鎖機制實現的。

耐久性

一個成功的事務將永久性地改變系統的狀態，所以在它結束之前，所有導致狀態的變化都記錄在一個持久的事務日誌中。如果我們的系統突然受到系統崩潰或斷電，那麼所有未完成已提交的事務可能會重演。

事務指邏輯上的一組操作，組成這組操作的各個單元，要不全部成功，要不全部不成功。

---

mysql

---

Mysql的索引的型別

Mysql目前主要有以下幾種索引型別：
FULLTEXT，HASH，BTREE，RTREE

FULLTEXT

即為全文索引，目前只有MyISAM引擎支援。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不過目前只有 CHAR、VARCHAR ，TEXT 列上可以建立全文索引。值得一提的是，在資料量較大時候，現將資料放入一個沒有全域性索引的表中，然後再用CREATE INDEX建立FULLTEXT索引，要比先為一張表建立FULLTEXT然後再將資料寫入的速度快很多。

HASH

由於hash索引可以一次定位，不需要像樹形索引那樣逐層查詢,因此具有極高的效率。那為什麼還需要其他的樹形索引呢？
（1）Hash 索引僅僅能滿足"=","IN"和"<=>"查詢，不能使用範圍查詢。
由於 Hash 索引比較的是進行 Hash 運算之後的 Hash 值，所以它只能用於等值的過濾，不能用於基於範圍的過濾，因為經過相應的 Hash 演算法處理之後的 Hash 值的大小關係，並不能保證和Hash運算前完全一樣。
（2）Hash 索引無法被用來避免資料的排序操作。
由於 Hash 索引中存放的是經過 Hash 計算之後的 Hash 值，而且Hash值的大小關係並不一定和 Hash 運算前的鍵值完全一樣，所以資料庫無法利用索引的資料來避免任何排序運算；
（3）Hash 索引不能利用部分索引鍵查詢。
對於組合索引，Hash 索引在計算 Hash 值的時候是組合索引鍵合併後再一起計算 Hash 值，而不是單獨計算 Hash 值，所以通過組合索引的前面一個或幾個索引鍵進行查詢的時候，Hash 索引也無法被利用。
（4）Hash 索引在任何時候都不能避免表掃描。
前面已經知道，Hash 索引是將索引鍵通過 Hash 運算之後，將 Hash運算結果的 Hash 值和所對應的行指標資訊存放於一個 Hash 表中，由於不同索引鍵存在相同 Hash 值，所以即使取滿足某個 Hash 鍵值的資料的記錄條數，也無法從 Hash 索引中直接完成查詢，還是要通過訪問表中的實際資料進行相應的比較，並得到相應的結果。
（5）Hash 索引遇到大量Hash值相等的情況後效能並不一定就會比B-Tree索引高。
對於選擇性比較低的索引鍵，如果建立 Hash 索引，那麼將會存在大量記錄指標資訊存於同一個 Hash 值相關聯。這樣要定位某一條記錄時就會非常麻煩，會浪費多次表資料的訪問，而造成整體效能低下。

BTREE

BTREE索引就是一種將索引值按一定的演算法，存入一個樹形的資料結構中，如二叉樹一樣，每次查詢都是從樹的入口root開始，依次遍歷node，獲取leaf。

BTREE在MyISAM裡的形式和Innodb稍有不同

在 Innodb裡，有兩種形態：一是primary key形態，其leaf node裡存放的是資料，而且不僅存放了索引鍵的資料，還存放了其他欄位的資料。二是secondary index，其leaf node和普通的BTREE差不多，只是還存放了指向主鍵的資訊.

而在MyISAM裡，主鍵和其他的並沒有太大區別。不過和Innodb不太一樣的地方是在MyISAM裡，leaf node裡存放的不是主鍵的資訊，而是指向資料檔案裡的對應資料行的資訊.

RTREE

TREE在mysql很少使用，僅支援geometry資料型別，支援該型別的儲存引擎只有MyISAM、BDb、InnoDb、NDb、Archive幾種。

相對於BTREE，RTREE的優勢在於範圍查詢.

各種索引的使用情況
（1）對於BTREE這種Mysql預設的索引型別，具有普遍的適用性

（2）由於FULLTEXT對中文支援不是很好，在沒有外掛的情況下，最好不要使用。其實，一些小的部落格應用，只需要在資料採集時，為其建立關鍵字列表，通過關鍵字索引，也是一個不錯的方法，至少愚安我是經常這麼做的。

（3）對於一些搜尋引擎級別的應用來說，FULLTEXT同樣不是一個好的處理方法，Mysql的全文索引建立的檔案還是比較大的，而且效率不是很高，即便是使用了中文分詞外掛，對中文分詞支援也只是一般。真要碰到這種問題，Apache的Lucene或許是你的選擇。

（4）正是因為hash表在處理較小資料量時具有無可比擬的素的優勢，所以hash索引很適合做快取（記憶體資料庫）。如mysql資料庫的記憶體版本Memsql，使用量很廣泛的快取工具Mencached，NoSql資料庫redis等，都使用了hash索引這種形式。當然，不想學習這些東西的話Mysql的MEMORY引擎也是可以滿足這種需求的。

mysql事物隔離性

事務隔離級別	髒讀	不可重複讀	幻讀
讀未提交（read-uncommitted）	是	是	是
不可重複讀（read-committed）	否	是	是
可重複讀（repeatable-read）	否	否	是
序列化（serializable）	否	否	否

mysql預設的事務隔離級別為repeatable-read

Mysql(Innodb)如何避免幻讀
幻讀問題是指一個事務的兩次不同時間的相同查詢返回了不同的的結果集。例如:一個 select 語句執行了兩次，但是在第二次返回了第一次沒有返回的行,那麼這些行就是“phantom” row.

建立了一個以SELECT操作的時間為基準點的 read view避免了幻讀的產生
一致性讀是通過 MVCC 為查詢提供了一個基於時間的點的快照。這個查詢只能看到在自己之前提交的資料，而在查詢開始之後提交的資料是不可以看到的。一個特例是,這個查詢可以看到於自己開始之後的同一個事務產生的變化。這個特例會產生一些反常的現象
InnoDB通過Nextkey lock解決了當前讀時的幻讀問題
Next-Key Lock:
行鎖與間隙鎖組合起來用就叫做Next-Key Lock。鎖定一個範圍，並且鎖定記錄本身。對於行的查詢，都是採用該方法，主要目的是解決幻讀的問題。