當前位置：
首頁 > 知識 > SQL｜MySQL 深入學習（優化，事務，鎖，索引，並發）

SQL｜MySQL 深入學習（優化，事務，鎖，索引，並發）

知識 10-29

MySQL資料庫

1.常用基礎SQL

資料庫常用語句

2.優化事項

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

2.　應盡量避免在 where 子句中對欄位進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描，如：select id from t where num is null可以在num上設置默認值0，確保表中num列沒有null值，然後這樣查詢：select id from t where num=0

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

4.　應盡量避免在 where 子句中使用or 來連接條件，否則將導致引擎放棄使用索引而進行全表掃描，如：select id from t where num=10 or num=20可以這樣查詢：select id from t where num=10 union all select id from t where num=20

5.　in 和 not in 也要慎用，否則會導致全表掃描，如：select id from t where num in(1，2，3) 對於連續的數值，能用 between 就不要用 in 了：select id from t where num between 1 and 3

6.　下面的查詢也將導致全表掃描：select id from t where name like 『%李%"若要提高效率，可以考慮全文檢索。

7．　如果在 where 子句中使用參數，也會導致全表掃描。因為SQL只有在運行時才會解析局部變數，但優化程序不能將訪問計劃的選擇推遲到運行時；它必須在編譯時進行選擇。然 而，如果在編譯時建立訪問計劃，變數的值還是未知的，因而無法作為索引選擇的輸入項。如下面語句將進行全表掃描：select id from t where num=@num可以改為強制查詢使用索引：select id from t with(index(索引名)) where num=@num

8.　應盡量避免在 where 子句中對欄位進行表達式操作，這將導致引擎放棄使用索引而進行全表掃描。如：select id from t where num/2=100應改為:select id from t where num=100*2

9.　應盡量避免在where子句中對欄位進行函數操作，這將導致引擎放棄使用索引而進行全表掃描。如：select id from t where substring(name，1，3)="abc" ，name以abc開頭的id應改為:select id from t where name like 『abc%"

10.　不要在 where 子句中的「=」左邊進行函數、算術運算或其他表達式運算，否則系統將可能無法正確使用索引。

11.　在使用索引欄位作為條件時，如果該索引是複合索引，那麼必須使用到該索引中的第一個欄位作為條件時才能保證系統使用該索引，否則該索引將不會被使用，並且應儘可能的讓欄位順序與索引順序相一致。

12.　不要寫一些沒有意義的查詢，如需要生成一個空表結構：select col1，col2 into #t from t where 1=0　這類代碼不會返回任何結果集，但是會消耗系統資源的，應改成這樣：create table #t(…)

13.　很多時候用 exists 代替 in 是一個好的選擇：select num from a where num in(select num from b)　用下面的語句替換：

select num from a where exists(select 1 from b where num=a.num)

14.　並不是所有索引對查詢都有效，SQL是根據表中數據來進行查詢優化的，當索引列有大量數據重複時，SQL查詢可能不會去利用索引，如一表中有欄位sex，male、female幾乎各一半，那麼即使在sex上建了索引也對查詢效率起不了作用。

15.　索引並不是越多越好，索引固然可 以提高相應的 select 的效率，但同時也降低了 insert 及 update 的效率，因為 insert 或 update 時有可能會重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。一個表的索引數最好不要超過6個，若太多則應考慮一些不常使用到的列上建的索引是否有 必要。

16.　應儘可能的避免更新 clustered 索引數據列，因為 clustered 索引數據列的順序就是表記錄的物理存儲順序，一旦該列值改變將導致整個表記錄的順序的調整，會耗費相當大的資源。若應用系統需要頻繁更新 clustered 索引數據列，那麼需要考慮是否應將該索引建為 clustered 索引。

17.　盡量使用數字型欄位，若只含數值信息的欄位盡量不要設計為字元型，這會降低查詢和連接的性能，並會增加存儲開銷。這是因為引擎在處理查詢和連接時會逐個比較字元串中每一個字元，而對於數字型而言只需要比較一次就夠了。

18.　儘可能的使用 varchar/nvarchar 代替 char/nchar ，因為首先變長欄位存儲空間小，可以節省存儲空間，其次對於查詢來說，在一個相對較小的欄位內搜索效率顯然要高些。

19.　任何地方都不要使用 select * from t ，用具體的欄位列表代替「*」，不要返回用不到的任何欄位。

20.　盡量使用表變數來代替臨時表。如果表變數包含大量數據，請注意索引非常有限（只有主鍵索引）。

21.　避免頻繁創建和刪除臨時表，以減少系統表資源的消耗。

22.　臨時表並不是不可使用，適當地使用它們可以使某些常式更有效，例如，當需要重複引用大型表或常用表中的某個數據集時。但是，對於一次性事件，最好使用導出表。

23.　在新建臨時表時，如果一次性插入數據量很大，那麼可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果數據量不大，為了緩和系統表的資源，應先create table，然後insert。

24.　如果使用到了臨時表，在存儲過程的最後務必將所有的臨時表顯式刪除，先 truncate table ，然後 drop table ，這樣可以避免系統表的較長時間鎖定。

25.　盡量避免使用游標，因為游標的效率較差，如果游標操作的數據超過1萬行，那麼就應該考慮改寫。

26.　使用基於游標的方法或臨時表方法之前，應先尋找基於集的解決方案來解決問題，基於集的方法通常更有效。

27.　與臨時表一樣，游標並不是不可使 用。對小型數據集使用 FAST_FORWARD 游標通常要優於其他逐行處理方法，尤其是在必須引用幾個表才能獲得所需的數據時。在結果集中包括「合計」的常式通常要比使用游標執行的速度快。如果開發時 間允許，基於游標的方法和基於集的方法都可以嘗試一下，看哪一種方法的效果更好。

28.　在所有的存儲過程和觸發器的開始處設置 SET NOCOUNT ON ，在結束時設置 SET NOCOUNT OFF 。無需在執行存儲過程和觸發器的每個語句後向客戶端發送DONE_IN_PROC 消息。

29.　盡量避免大事務操作，提高系統並發能力。

30.　盡量避免向客戶端返回大數據量，若數據量過大，應該考慮相應需求是否合理。

1、事務四大特性（ACID）

  • 原子性(Atomicity):原子性是指事務是一個不可分割的工作單位，事務中的操作要麼都發生，要麼都不發生。
  • 一致性(Consistency):如果事務執行之前資料庫是一個完整性的狀態，那麼事務結束後，無論事務是否執行成功，資料庫仍然是一個完整性狀態。 (資料庫的完整性狀態:當一個資料庫中的所有的數據都符合資料庫中所定義的所有的約束，此時可以稱資料庫是一個完整性狀態。)
  • 隔離性(Isolation):事務的隔離性是指多個用戶並發訪問資料庫時，一個用戶的事務不能被其它用戶的事務所干擾，多個並發事務之間數據要相互隔離。
  • 持久性(durability):持久性是指一個事務一旦被提交，它對資料庫中數據的改變就是永久性的，接下來即使資料庫發生故障也不應該對其有任何影響。

引申：NOSQL CAP BASE

1.關係型資料庫和非關係型資料庫區別？

優點

  • 成本：nosql資料庫簡單易部署，基本都是開源軟體，不需要像使用oracle那樣花費大量成本購買使用，相比關係型資料庫價格便宜。當然還有免費的
  • 查詢速度：nosql資料庫將數據存儲於緩存之中，關係型資料庫將數據存儲在硬碟中，自然查詢速度遠不及nosql資料庫。
  • 存儲數據的格式：nosql的存儲格式是key，value形式、文檔形式、圖片形式等等，所以可以存儲基礎類型以及對象或者是集合等各種格式，而資料庫則只支持基礎類型。
  • 擴展性：關係型資料庫有類似join這樣的多表查詢機制的限制導致擴展很艱難。

缺點

  • 維護的工具和資料有限，因為nosql是屬於新的技術，不能和關係型資料庫10幾年的技術同日而語。
  • 不提供對sql的支持，如果不支持sql這樣的工業標準，將產生一定用戶的學習和使用成本。
  • 不提供關係型資料庫對事物的處理。即：無ACID特性。

非關係型資料庫的優勢

  • 性能NOSQL是基於鍵值對的，可以想像成表中的主鍵和值的對應關係，而且不需要經過SQL層的解析，所以性能非常高。
  • 可擴展性同樣也是因為基於鍵值對，數據之間沒有耦合性，所以非常容易水平擴展。

關係型資料庫的優勢

  • 複雜查詢可以用SQL語句方便的在一個表以及多個表之間做非常複雜的數據查詢。
  • 事務支持使得對於安全性能很高的數據訪問要求得以實現。對於這兩類資料庫，對方的優勢就是自己的弱勢，反之亦然。

2.CAP 分散式系統不可能同時滿足一致性（C：Consistency）、可用性（A：Availability）和分區容忍性（P：Partition Tolerance），最多只能同時滿足其中兩項

dubbo+zookeeper 主要實現CP
springcloud eureka [hystrix] 主要實現AP
以上與服務註冊細節相關

3.BASE 是基本可用（Basically Available）、軟狀態（Soft State）和最終一致性（Eventually Consistent）三個短語的縮寫。 BASE 理論是對 CAP 中一致性和可用性權衡的結果，它的理論的核心思想是：即使無法做到強一致性，但每個應用都可以根據自身業務特點，採用適當的方式來使系統達到最終一致性。

4.兩階段提交 在分散式系統的提交階段之前增加了準備階段，事務中多個資源在準備階段均成功後，才允許事務提交，否則回滾所有資源。

更多原理具體參考《大型網站系統與Java中間件實戰》、《大型網站技術架構 核心原理與案例分析》、《從Paxos到ZooKeeper》

2、資料庫隔離級別，每個級別會引發什麼問題，MySQL默認是哪個級別？

SQL標準定義了4類隔離級別，包括了一些具體規則，用來限定事務內外的哪些改變是可見的，哪些是不可見的。低級別的隔離級一般支持更高的並發處理，並擁有更低的系統開銷。

  • Read Uncommitted（讀取未提交內容 - 瀏覽訪問）
  • 在該隔離級別，所有事務都可以看到其他未提交事務的執行結果。本隔離級別很少用於實際應用，因為它的性能也不比其他級別好多少。讀取未提交的數據，也被稱之為臟讀（Dirty Read）。
  • Read Committed（讀取提交內容 - 游標穩定）
  • 這是大多數資料庫系統的默認隔離級別（但不是MySQL默認的）。它滿足了隔離的簡單定義：一個事務只能看見已經提交事務所做的改變。 這種隔離級別也支持所謂的不可重複讀（Nonrepeatable Read），因為同一事務的其他實例在該實例處理其間可能會有新的commit，所以同一select可能返回不同結果。
  • Repeatable Read（可重讀 - 2.99990度隔離）
  • 這是MySQL的默認事務隔離級別，它確保同一事務的多個實例在並發讀取數據時，會看到同樣的數據行。不過理論上，這會導致另一個棘手的問題：幻讀 （Phantom Read）。 簡單的說，幻讀指當用戶讀取某一範圍的數據行時，另一個事務又在該範圍內插入了新行，當用戶再讀取該範圍的數據行時，會發現有新的「幻影」 行。InnoDB和Falcon存儲引擎通過多版本並發控制（MVCC，Multiversion Concurrency Control）機制解決了該問題。
  • Serializable（可串列化 - 隔離、3度隔離）
  • 這是最高的隔離級別，它通過強制事務排序，使之不可能相互衝突，從而解決幻讀問題。簡言之，它是在每個讀的數據行上加上共享鎖。在這個級別，可能導致大量的超時現象和鎖競爭。 這四種隔離級別採取不同的鎖類型來實現，若讀取的是同一個數據的話，就容易發生問題。

SQL｜MySQL 深入學習（優化，事務，鎖，索引，並發）

  • 臟讀(Drity Read)：某個事務已更新一份數據，另一個事務在此時讀取了同一份數據，由於某些原因，前一個RollBack了操作，則後一個事務所讀取的數據就會是不正確的。
  • 不可重複讀(Non-repeatable read):在一個事務的兩次查詢之中數據不一致，這可能是兩次查詢過程中間插入了一個事務，更新了原有的數據。
  • 幻讀(Phantom Read):在一個事務的兩次查詢中數據筆數不一致，例如有一個事務查詢了幾行(Row)數據，而另一個事務卻在此時插入了新的幾行數據，先前的事務在接下來的查詢中，就會發現有幾行數據是它先前所沒有的。
  • 讀不影響寫：事務以排他鎖的形式修改原始數據，讀時不加鎖，因為 MySQL 在事務隔離級別Read committed 、Repeatable Read下，InnoDB 存儲引擎採用非鎖定性一致讀－－即讀取不佔用和等待表上的鎖。即採用的是MVCC中一致性非鎖定讀模式。 因讀時不加鎖，所以不會阻塞其他事物在相同記錄上加 X鎖來更改這行記錄。
  • 寫不影響讀：事務以排他鎖的形式修改原始數據，當讀取的行正在執行 delete 或者 update 操作，這時讀取操作不會因此去等待行上鎖的釋放。相反地，InnoDB 存儲引擎會去讀取行的一個快照數據。
  • 間隙鎖：間隙鎖主要用來防止幻讀，用在repeatable-read隔離級別下，指的是當對數據進行條件，範圍檢索時，對其範圍內也許並存在的值進行加鎖！ 當查詢的索引含有唯一屬性（唯一索引，主鍵索引）時，InnoDB存儲引擎會對next-key lock進行優化，將其降為record lock，即僅鎖住索引本身，而不是範圍！若是普通輔助索引，則會使用傳統的next-key lock進行範圍鎖定！

3、MySQL的鎖演算法

  • Record Lock：單個行記錄上的鎖。
  • Gap Lock：間隙鎖，鎖定一個範圍，但不包括記錄本身。GAP鎖的目的，是為了防止同一事務的兩次當前讀，出現幻讀的情況。
  • Next-Key Lock：Record + Gap，鎖定一個範圍，並且鎖定記錄本身。對於行的查詢，都是採用該方法，主要目的是解決幻讀的問題。

4、MySQL的MVCC

MVCC的全稱是「多版本並發控制」。這項技術使得InnoDB的事務隔離級別下執行一致性讀操作有了保證，換言之，就是為了查詢一些正在被另一個事務更新的行，並且可以看到它們被更新之前的值。 這是一個可以用來增強並發性的強大的技術，因為這樣一來的話查詢就不用等待另一個事務釋放鎖。這項技術在資料庫領域並不是普遍使用的。一些其它的資料庫產品，以及MySQL其它的存儲引擎並不支持它。

MySQL的InnoDB採用的是行鎖，而且採用了多版本並發控制來提高讀操作的性能。

1.什麼是多版本並發控制呢 ？

其實就是在每一行記錄的後面增加兩個隱藏列，記錄創建版本號和刪除版本號，而每一個事務在啟動的時候，都有一個唯一的遞增的版本號。 在InnoDB中，給每行增加兩個隱藏欄位來實現MVCC，兩個列都用來存儲事務的版本號，每開啟一個新事務，事務的版本號就會遞增。

2.默認的隔離級別（REPEATABLE READ）下，增刪查改？

SELECT
讀取創建版本小於或等於當前事務版本號，並且刪除版本為空或大於當前事務版本號的記錄。這樣可以保證在讀取之前記錄是存在的
INSERT
將當前事務的版本號保存至行的創建版本號
UPDATE
新插入一行，並以當前事務的版本號作為新行的創建版本號，同時將原記錄行的刪除版本號設置為當前事務版本號
DELETE
將當前事務的版本號保存至行的刪除版本號

3.什麼是快照讀和當前讀？

快照讀：讀取的是快照版本，也就是歷史版本
當前讀：讀取的是最新版本
普通的SELECT就是快照讀，而UPDATE、DELETE、INSERT、SELECT … LOCK IN SHARE MODE、SELECT … FOR UPDATE是當前讀。

4.什麼是鎖定讀？

在一個事務中，標準的SELECT語句是不會加鎖，但是有兩種情況例外。
SELECT ... LOCK IN SHARE MODE 給記錄加上共享鎖，這樣一來的話，其它事務只能讀不能修改，直到當前事務提交
SELECT ... FOR UPDATE 給索引記錄加鎖，這種情況下跟UPDATE的加鎖情況是一樣的

5.什麼是一致性非鎖定讀？

consistent read （一致性讀），InnoDB用多版本來提供查詢資料庫在某個時間點的快照。如果隔離級別是REPEATABLE READ，那麼在同一個事務中的所有一致性讀都讀的是事務中第一個這樣的讀讀到的快照； 如果是READ COMMITTED，那麼一個事務中的每一個一致性讀都會讀到它自己刷新的快照版本。Consistent read（一致性讀）是READ COMMITTED和REPEATABLE READ隔離級別下普通SELECT語句默認的模式。 一致性讀不會給它所訪問的表加任何形式的鎖，因此其它事務可以同時並發的修改它們。

MVCC實現一致性非鎖定讀，這就有保證在同一個事務中多次讀取相同的數據返回的結果是一樣的，解決了不可重複讀的問題。

6.什麼是悲觀鎖和樂觀鎖？

悲觀鎖：
正如它的名字那樣，資料庫總是認為別人會去修改它所要操作的數據，因此在資料庫處理過程中將數據加鎖。其實現依靠資料庫底層。
樂觀鎖：
如它的名字那樣，總是認為別人不會去修改，只有在提交更新的時候去檢查數據的狀態。通常是給數據增加一個欄位來標識數據的版本。

7.select時怎麼加排它鎖？

使用鎖定讀，普通select不會引起加鎖，而是去讀取最新的快照。同上4
事務以排他鎖的形式修改原始數據，當讀取的數據正在進行更新等操作，則直接去讀取快照，而不是等鎖釋放

5、MySQL的兩種存儲引擎區別（事務、鎖級別等等），各自的適用場景

MyISAM

  • 不支持事務，但是每次查詢都是原子的；
  • 支持表級鎖，即每次操作是對整個表加鎖；
  • 存儲表的總行數；
  • 一個MyISAM表有三個文件：索引文件、表結構文件、數據文件；
  • 採用非聚集索引，索引文件的數據域存儲指向數據文件的指針。輔索引與主索引基本一致，但是輔索引不用保證唯一性。
  • 適用OLAP

InnoDB

  • 支持ACID的事務，支持事務的四種隔離級別；
  • 支持行級鎖及外鍵約束：因此可以支持寫並發；
  • 不存儲總行數；
  • 一個InnoDB引擎存儲在一個文件空間（共享表空間，表大小不受操作系統控制，一個表可能分布在多個文件里），也有可能為多個（設置為獨立表空間，表大小受操作系統文件大小限制，一般為2G），受操作系統文件大小的限制；
  • 主鍵索引採用聚集索引（索引的數據域存儲數據文件本身），輔索引的數據域存儲主鍵的值；因此從輔索引查找數據，需要先通過輔索引找到主鍵值，再訪問輔索引；
  • 最好使用自增主鍵，防止插入數據時，為維持B+樹結構，文件的大調整。
  • 適用OLTP

InnoDB主要特性

主要包括：插入緩存（insert buffer）、兩次寫(double write)、自適應哈希(Adaptive Hash index)、非同步IO(Async IO)、刷新鄰接頁(Flush Neighbor Page)

感興趣可以參考書籍《MySQL技術內幕:InnoDB存儲引擎》 網上找了一個博客InnoDB關鍵特性

6、索引有B+索引和hash索引，各自的區別？

主要區別

  • 如果是等值查詢，那麼哈希索引明顯有絕對優勢，因為只需要經過一次演算法即可找到相應的鍵值；當然了，這個前提是，鍵值都是唯一的。如果鍵值不是唯一的，就需要先找到該鍵所在位置，然後再根據鏈表往後掃描，直到找到相應的數據；
  • 如果是範圍查詢檢索，這時候哈希索引就毫無用武之地了，因為原先是有序的鍵值，經過哈希演算法後，有可能變成不連續的了，就沒辦法再利用索引完成範圍查詢檢索；
  • 同理，哈希索引也沒辦法利用索引完成排序，以及like 『xxx%』 這樣的部分模糊查詢（這種部分模糊查詢，其實本質上也是範圍查詢）；
  • 哈希索引也不支持多列聯合索引的最左匹配規則；
  • B+樹索引的關鍵字檢索效率比較平均，不像B樹那樣波動幅度大，在有大量重複鍵值情況下，哈希索引的效率也是極低的，因為存在所謂的哈希碰撞問題。

7、為什麼B+樹適合作為索引的結構？

  • B樹：有序數組+平衡多叉樹
  • B+樹：有序數組鏈表+平衡多叉樹 葉子存儲數據，空間佔用小，且是雙鏈表，修改效率快
  • 不同於B樹只適合隨機檢索，B+樹同時支持隨機檢索和順序檢索

補充1：k近鄰演算法

  • kd樹是一種對k維空間中的實例點進行存儲以便對其進行快速檢索的樹形數據結構，且kd樹是一種二叉樹，表示對k維空間的一個劃分。

補充2：伸展樹

  • 伸展樹（Splay Tree），也叫分裂樹，是一種二叉排序樹，它能在O(log n)內完成插入、查找和刪除操作
  • 在伸展樹上的一般操作都基於伸展操作：假設想要對一個二叉查找樹執行一系列的查找操作，為了使整個查找時間更小，被查頻率高的那些條目就應當經常處於靠近樹根的位置。
  • 於是想到設計一個簡單方法， 在每次查找之後對樹進行重構，把被查找的條目搬移到離樹根近一些的地方。伸展樹應運而生。伸展樹是一種自調整形式的二叉查找樹，它會沿著從某個節點到樹根之間的路徑， 通過一系列的旋轉把這個節點搬移到樹根去。 它的優勢在於不需要記錄用於平衡樹的冗餘信息。

資料庫索引採用B+樹的主要原因是B樹在提高了磁碟IO性能的同時並沒有解決元素遍歷的效率低下的問題。 正是為了解決這個問題，B+樹應運而生。B+樹只要遍歷葉子節點就可以實現整棵樹的遍歷。而且在資料庫中基於範圍的查詢是非常頻繁的，而B樹不支持這樣的操作（或者說效率太低）。

平衡二叉樹沒能充分利用磁碟預讀功能，而B樹是為了充分利用磁碟預讀功能來而創建的一種數據結構，也就是說B樹就是為了作為索引才被發明出來的的。

1.局部性原理與磁碟預讀

  • 由於存儲介質的特性，磁碟本身存取就比主存慢很多，再加上機械運動耗費，磁碟的存取速度往往是主存的幾百分分之一，因此為了提高效率，要盡量減少磁碟I/O。 為了達到這個目的，磁碟往往不是嚴格按需讀取，而是每次都會預讀，即使只需要一個位元組，磁碟也會從這個位置開始，順序向後讀取一定長度的數據放入內存。這樣做的理論依據是計算機科學中著名的局部性原理：
  • 當一個數據被用到時，其附近的數據也通常會馬上被使用。 程序運行期間所需要的數據通常比較集中。 由於磁碟順序讀取的效率很高（不需要尋道時間，只需很少的旋轉時間），因此對於具有局部性的程序來說，預讀可以提高I/O效率。

2.為什麼說紅黑樹沒能充分利用磁碟預讀功能？

  • 紅黑樹這種結構，h明顯要深的多。由於邏輯上很近的節點（父子）物理上可能很遠，無法利用局部性，所以紅黑樹的I/O漸進複雜度也為O(h)，效率明顯比B-Tree差很多。
  • 也就是說，使用紅黑樹（平衡二叉樹）結構的話，每次磁碟預讀中的很多數據是用不上的數據。因此，它沒能利用好磁碟預讀的提供的數據。然後又由於深度大（較B樹而言），所以進行的磁碟IO操作更多。
  • 樹形結構使用C語言的指針實現（指針保存的就是節點的物理地址），層級之間是使用指針指向子節點，也就是隨機IO，因此深度越大，IO越多。 而在B+樹中，葉子之間還使用了雙鏈表連接，並且同層葉子的數據是有序的，因此可以通過順序向前後進行查詢，而不需要通過其他父節點的定址再找到葉子節點。

8、B+索引數據結構，和B樹的區別 ？

除了以上的，主要區別，其他請看B- B+ B*樹 實際上B-就是B樹，二叉樹不叫B樹，像這種寫法B-Tree，可以是B-樹也可以說是B樹[-可能是連接符，可能是翻譯問題]，B+ B* 是改善的B樹

  • MyISAM和InnoDB都使用了B+樹作為索引存儲結構，但是葉子上數據的存儲方式不同。前者索引文件和數據文件是分離的，索引文件僅保存記錄所在頁的指針（物理位置）， 而後者直接存儲數據，或者存儲主鍵值（存儲主鍵值並檢索輔助索引，此時實際上進行了二次查詢，增加IO次數）。

InnoDB:

MyISAM:

9、索引的分類（主鍵索引、唯一索引），最左前綴原則，哪些情況索引會失效？

1.關於索引失效與優化最前面已有了，不再羅列

2.各種索引區別

普通索引：最基本的索引，沒有任何限制。
唯一索引：與「普通索引」類似，不同的就是：索引列的值必須唯一，但允許有空值。
主鍵索引：它 是一種特殊的唯一索引，不允許有空值。
全文索引：僅可用於 MyISAM 表，針對較大的數據，生成全文索引很耗時耗空間。（MATCH... AGAINST...）
組合索引：為了更多的提高MySQL效率可建立組合索引，遵循「最左前綴」原則。
覆蓋索引：包含（覆蓋）所有需要查詢的欄位的值的索引

3.ElasticSearch、Lucene中的倒排索引

  • 倒排索引(Inverted Index)：倒排索引是實現「單詞-文檔矩陣」的一種具體存儲形式，通過倒排索引，可以根據單詞快速獲取包含這個單詞的文檔列表。倒排索引主要由兩個部分組成：「單詞詞典」和「倒排文件」。
  • 單詞詞典(Lexicon)：搜索引擎的通常索引單位是單詞，單詞詞典是由文檔集合中出現過的所有單詞構成的字元串集合，單詞詞典內每條索引項記載單詞本身的一些信息以及指向「倒排列表」的指針。
  • 倒排文件(Inverted File)：所有單詞的倒排列表往往順序地存儲在磁碟的某個文件里，這個文件即被稱之為倒排文件，倒排文件是存儲倒排索引的物理文件。
  • 倒排列表(PostingList)：倒排列表記載了出現過某個單詞的所有文檔的文檔列表及單詞在該文檔中出現的位置信息，每條記錄稱為一個倒排項(Posting)。根據倒排列表，即可獲知哪些文檔包含某個單詞。

倒排索引基本概念示意圖

SQL｜MySQL 深入學習（優化，事務，鎖，索引，並發）

4.倒排索引和正排索引

  • 倒排索引：索引詞->網頁
  • 正排索引：網頁->索引詞

假設使用正向索引，那麼當你搜索「SEO」的時候，搜索引擎必須檢索網頁中的每一個關鍵詞，假設一個網頁中包含成千上百個關鍵詞，可想而知，會造成大量的資源浪費。於是倒排索引應運而生。倒排索引是相對正向索引而言的，你也可以將其理解為逆向索引。 比如你搜索「SEO」，搜索引擎可以快速檢索出包含「SEO」搜索詞的網頁1和網頁2，為後續的相關度和權重計算奠定基礎，從而大大加快了返回搜索結果的速度。

10、聚集索引和非聚集索引區別是什麼？

聚集（clustered）索引，也叫聚簇索引。
定義：數據行的物理順序與列值（一般是主鍵的那一列）的邏輯順序相同，一個表中只能擁有一個聚集索引。
如果沒定義主鍵，會選擇一個唯一的非空索引代替，如果沒有這樣的索引，則會隱式定義一個主鍵作為聚簇索引

非聚集（unclustered）索引。
定義：該索引中索引的邏輯順序與磁碟上行的物理存儲順序不同，一個表中可以擁有多個非聚集索引。

總結

使用聚集索引的查詢效率要比非聚集索引的效率要高，但是如果需要頻繁去改變聚集索引的值，
寫入性能並不高，因為需要移動對應數據的物理位置。
非聚集索引在查詢的時候可以的話就避免二次查詢，這樣性能會大幅提升。
不是所有的表都適合建立索引，只有數據量大表才適合建立索引，且建立在選擇性高的列上面性能會更好。
重複度高的可能使得索引失效。
具體最前面有更詳細的。

11、schema(表結構)對性能的影響？

1.冗餘數據的處理

  • 適當的數據冗餘可以提高系統的整體查詢性能(在P2P中，在userinfo對象中有realname和idnumber);
  • 關係資料庫的三範式:
  • 第一範式（1NF）是對關係模式的基本要求，不滿足第一範式（1NF）的資料庫就不是關係資料庫，是指資料庫表的每一列都是不可分割的基本數據項，同一列中不能有多個值；
  • 第二範式（2NF）要求資料庫表中的每個實例或行必須可以被惟一地區分。 即各欄位和主鍵之間不存在部分依賴
  • 第三範式（3NF）要求一個資料庫表中不包含已在其它表中已包含的非主關鍵字信息。即在第二範式的基礎上，不存在傳遞依賴 (不允許有冗餘數據)

2.大表拆小表，有大數據的列單獨拆成小表

  • 在一個資料庫中，一般不會設計屬性過多的表;
  • 在一個資料庫中，一般不會有超過500/1000萬數據的表(拆表，按照邏輯拆分，按照業務拆分);
  • 有大數據的列單獨拆成小表(富文本編輯器，CKeditor);

3.根據需求的展示設置更合理的表結構

4.把常用屬性分離成小表

  • 在P2P項目中，我們把logininfo和userinfo和account表拆成了三張表;
  • 減少查詢常用屬性需要查詢的列;
  • 便於常用屬性的集中緩存;

12、資料庫的主從複製 ？

  1. 就算MySQL拆成了多個，也必須分出主和從，所有的寫操作都必須要在主MySQL 上完成;
  2. 所有的從MySQL的數據都來自於(同步於)主MySQL;
  3. 既然涉及到同步，那一定有延遲;有延遲，就一定可能在讀的時候產生臟數據;所以，能夠在從MySQL上進行的讀操作，一定對實時性和臟數據有一定容忍度的數據;比如，登陸日誌，後台報表，首頁統計信息來源;文章;資訊;SNS消息;
  4. 在我們的P2P中，做主從，絕大部分的讀操作，都必須在主MySQL上執行;只有(登陸日誌，報表，滿標一審列表，滿標二審列表，用戶的流水信息，充值明細，投標明細查詢類的業務可以定位到從MySQL);
  5. 【一定注意】:在MySQL主從時，如果一個業務(service中的一個方法)中，如果既有R操作，又有W操作，因為W操作一定要在主MySQL上，所以在一個事務中所有的數據來源都只能來自於一個MySQL
  6. 要完成主從同步，就必須讓在Master上執行的所有的DML和DDL能夠正確的在Salve上再執行一遍;MySQL選擇使用文件來記錄SQL;
  7. 要完成主從同步，第一個事情就是把在主伺服器上的bin-log(二進位文件)打開，bin-log文件就可以記錄在MySQL上執行的所有的DML+DDL+TCL;
  8. MySQL使用被動註冊的方式來讓從MySQL請求同步主MySQL的binlog;原因:被動請求的方式，主的MySQL不需要知道有哪些從的MySQL，我額外添加/去掉從MySQL伺服器，對主MySQL伺服器的正常運行沒有任何影響;
  9. 第二步，從MySQL後台一個線程發送一個請求，到主伺服器請求更新數據;最重要的數據(我這次請求，請求你bin-log的哪一行數據之後的數據)
  10. 第三步，主MySQL後台一個線程接收到從MySQL發送的請求，然後讀取bin-log文件中指定的內容，並放在從MySQL的請求響應中;
  11. 第四步，從MySQL的請求帶回同步的數據，然後寫在從MySQL中的relay-log(重做日誌)中;relay-log中記錄的就是從主MySQL中請求回來的哪些SQL數據;
  12. 第五步，從MySQL後台一個線程專門用於從relay-log中讀取同步回來的SQL，並寫入到從MySQL中，完成同步;
  13. MySQL的主從同步是經過高度優化的，性能非常高;

這裡東西太多，更多請參考MySQL優化的課程筆記

13、explain和join

EXPLAIN:

  1. 使用方式： explain SQL;
  2. 返回結果：
  3. ID：執行查詢的序列號；
  4. select_type：使用的查詢類型
  5. DEPENDENT SUBQUERY：子查詢中內層的第一個SELECT，依賴於外部查詢的結果集；
  6. DEPENDENT UNION：子查詢中的UNION，且為UNION 中從第二個SELECT 開始的後面所有SELECT，同樣依賴於外部查詢的結果集；
  7. PRIMARY：子查詢中的最外層查詢，注意並不是主鍵查詢；
  8. SIMPLE：除子查詢或者UNION 之外的其他查詢；
  9. SUBQUERY：子查詢內層查詢的第一個SELECT，結果不依賴於外部查詢結果集；
  10. UNCACHEABLE SUBQUERY：結果集無法緩存的子查詢；
  11. UNION：UNION 語句中第二個SELECT 開始的後面所有SELECT，第一個SELECT 為PRIMARY
  12. UNION RESULT：UNION 中的合併結果；
  13. table：這次查詢訪問的數據表；
  14. type：對錶所使用的訪問方式：
  15. all：全表掃描
  16. const：讀常量，且最多只會有一條記錄匹配，由於是常量，所以實際上只需要讀一次；
  17. eq_ref：最多只會有一條匹配結果，一般是通過主鍵或者唯一鍵索引來訪問；
  18. fulltext：全文檢索，針對full text索引列；
  19. index：全索引掃描；
  20. index_merge：查詢中同時使用兩個（或更多）索引，然後對索引結果進行merge 之後再讀取表數據；
  21. index_subquery：子查詢中的返回結果欄位組合是一個索引（或索引組合），但不是一個主鍵或者唯一索引；
  22. rang：索引範圍掃描；
  23. ref：Join 語句中被驅動表索引引用查詢；
  24. ref_or_null：與ref 的唯一區別就是在使用索引引用查詢之外再增加一個空值的查詢；
  25. system：系統表，表中只有一行數據；
  26. unique_subquery：子查詢中的返回結果欄位組合是主鍵或者唯一約束；
  27. possible_keys：可選的索引；如果沒有使用索引，為null；
  28. key：最終選擇的索引；
  29. key_len：被選擇的索引長度；
  30. ref：過濾的方式，比如const（常量），column（join），func（某個函數）；
  31. rows：查詢優化器通過收集到的統計信息估算出的查詢條數；
  32. Extra：查詢中每一步實現的額外細節信息
  33. Distinct：查找distinct 值，所以當MySQL 找到了第一條匹配的結果後，將停止該值的查詢而轉為後面其他值的查詢；
  34. Full scan on NULL key：子查詢中的一種優化方式，主要在遇到無法通過索引訪問null值的使用使用；
  35. Impossible WHERE noticed after reading const tables：MySQL Query Optimizer 通過收集到的統計信息判斷出不可能存在結果；
  36. No tables：Query 語句中使用FROM DUAL 或者不包含任何FROM 子句；
  37. Not exists：在某些左連接中MySQL Query Optimizer 所通過改變原有Query 的組成而使用的優化方法，可以部分減少數據訪問次數；
  38. Select tables optimized away：當我們使用某些聚合函數來訪問存在索引的某個欄位的時候，MySQL Query Optimizer 會通過索引而直接一次定位到所需的數據行完成整個查詢。當然，前提是在Query 中不能有GROUP BY 操作。如使用MIN()或者MAX（）的時候；
  39. Using filesort：當我們的Query 中包含ORDER BY 操作，而且無法利用索引完成排序操作的時候，MySQL Query Optimizer 不得不選擇相應的排序演算法來實現。
  40. Using index：所需要的數據只需要在Index 即可全部獲得而不需要再到表中取數據；
  41. Using index for group-by：數據訪問和Using index 一樣，所需數據只需要讀取索引即可，而當Query 中使用了GROUP BY 或者DISTINCT 子句的時候，如果分組欄位也在索引中，Extra 中的信息就會是Using index for group-by；
  42. Using temporary：當MySQL 在某些操作中必須使用臨時表的時候，在Extra 信息中就會出現Using temporary 。主要常見於GROUP BY 和ORDER BY 等操作中。
  43. Using where：如果我們不是讀取表的所有數據，或者不是僅僅通過索引就可以獲取所有需要的數據，則會出現Using where 信息；
  44. Using where with pushed condition：這是一個僅僅在NDBCluster 存儲引擎中才會出現的信息，而且還需要通過打開Condition Pushdown 優化功能才可能會被使用。控制參數為engine_condition_pushdown 。

profiling: Query Profiler是MySQL5.1之後提供的一個很方便的用於診斷Query執行的工具，能夠準確的獲取一條查詢執行過程中的CPU，IO等情況；

  1. 開啟profiling：set profiling=1
  2. 執行QUERY，在profiling過程中所有的query都可以記錄下來
  3. 查看記錄的query：show profiles
  4. 選擇要查看的profile：show profile cpu， block io for query 6

status是執行SQL的詳細過程

  • Duration：執行的具體時間
  • CPU_user：用戶CPU時間
  • CPU_system：系統CPU時間
  • Block_ops_in：IO輸入次數
  • Block_ops_out：IO輸出次數
  • profiling只對本次會話有效

JOIN的原理

在MySQL中使用Nested Loop Join來實現join； A JOIN B：通過A表的結果集作為循環基礎，一條一條的通過結果集中的數據作為過濾條件到下一個表中查詢數據，然後合併結果；

JOIN的優化原則

  1. 儘可能減少Join 語句中的Nested Loop 的循環總次數，用小結果集驅動大結果集；
  2. 優先優化Nested Loop 的內層循環；
  3. 保證Join 語句中被驅動表上Join 條件欄位已經被索引；
  4. 擴大join buffer的大小；

14、內連接、外連接、交叉連接、笛卡兒積等

  1. 內連接
  2. 只有兩個表相匹配的行才能在結果集中出現 分為三種：等值連接、自然連接、不等連接
  3. 外連接
  4. 左外連接(LEFT OUTER JOIN或LEFT JOIN) 以左邊為準，右邊沒用則為空
  5. 右外連接(RIGHT OUTER JOIN或RIGHT JOIN) 以右邊為準，左邊沒有則為空
  6. 全外連接(FULL OUTER JOIN或FULL JOIN) 左右均可能為空
  7. 交叉連接
  8. 沒有WHERE 子句，它返回連接表中所有數據行的笛卡爾積
  9. 笛卡兒積

15、死鎖怎麼解決？

產生死鎖的原因主要是

  1. 系統資源不足。
  2. 進程運行推進的順序不合適。
  3. 資源分配不當等。

如果系統資源充足，進程的資源請求都能夠得到滿足，死鎖出現的可能性就很低，否則就會因爭奪有限的資源而陷入死鎖。其次，進程運行推進順序與速度不同，也可能產生死鎖。

產生死鎖的四個必要條件

1. 互斥條件：一個資源每次只能被一個進程使用。
2. 請求與保持條件：一個進程因請求資源而阻塞時，對已獲得的資源保持不放。
3. 不剝奪條件:進程已獲得的資源，在末使用完之前，不能強行剝奪。
4. 循環等待條件:若干進程之間形成一種頭尾相接的循環等待資源關係。

這四個條件是死鎖的必要條件，只要系統發生死鎖，這些條件必然成立，而只要上述條件之一不滿足，就不會發生死鎖。

死鎖的預防和解除

理解了死鎖的原因，尤其是產生死鎖的四個必要條件，就可以最大可能地避免、預防和解除死鎖。所以，在系統設計、進程調度等方面注意如何不讓這四個必要條件成立，如何確定資源的合理分配演算法，避免進程永久佔據系統資源。此外，也要防止進程在處於等待狀態的情況下佔用資源，在系統運行過程中，對進程發出的每一個系統能夠滿足的資源申請進行動態檢查，並根據檢查結果決定是否分配資源，若分配後系統可能發生死鎖，則不予分配，否則予以分配 。因此，對資源的分配要給予合理的規劃。 如何將死鎖減至最少

雖然不能完全避免死鎖，但可以使死鎖的數量減至最少。將死鎖減至最少可以增加事務的吞吐量並減少系統開銷，因為只有很少的事務回滾，而回滾會取消事務執行的所有工作。由於死鎖時回滾而由應用程序重新提交。

下列方法有助於最大限度地降低死鎖

  • 按同一順序訪問對象。
  • 避免事務中的用戶交互。
  • 保持事務簡短並在一個批處理中。
  • 使用低隔離級別。
  • 使用綁定連接。

具體

  • 按同一順序訪問對象 -- 如果所有並發事務按同一順序訪問對象，則發生死鎖的可能性會降低。例如，如果兩個並發事務獲得 Supplier 表上的鎖，然後獲得 Part 表上的鎖，則在其中一個事務完成之前，另一個事務被阻塞在 Supplier 表上。第一個事務提交或回滾後，第二個事務繼續進行。不發生死鎖。將存儲過程用於所有的數據修改可以標準化訪問對象的順序。
  • 避免事務中的用戶交互 -- 避免編寫包含用戶交互的事務，因為運行沒有用戶交互的批處理的速度要遠遠快於用戶手動響應查詢的速度，例如答覆應用程序請求參數的提示。例如，如果事務正在等待用戶輸入，而用戶去吃午餐了或者甚至回家過周末了，則用戶將此事務掛起使之不能完成。這樣將降低系統的吞吐量，因為事務持有的任何鎖只有在事務提交或回滾時才會釋放。即使不出現死鎖的情況，訪問同一資源的其它事務也會被阻塞，等待該事務完成。
  • 保持事務簡短並在一個批處理中 -- 在同一資料庫中並發執行多個需要長時間運行的事務時通常發生死鎖。事務運行時間越長，其持有排它鎖或更新鎖的時間也就越長，從而堵塞了其它活動並可能導致死鎖。 保持事務在一個批處理中，可以最小化事務的網路通信往返量，減少完成事務可能的延遲並釋放鎖。
  • 使用低隔離級別 -- 確定事務是否能在更低的隔離級別上運行。執行提交讀允許事務讀取另一個事務已讀取（未修改）的數據，而不必等待第一個事務完成。使用較低的隔離級別（例如提交讀）而不使用較高的隔離級別（例如可串列讀）可以縮短持有共享鎖的時間，從而降低了鎖定爭奪。
  • 使用綁定連接 -- 使用綁定連接使同一應用程序所打開的兩個或多個連接可以相互合作。次級連接所獲得的任何鎖可以象由主連接獲得的鎖那樣持有，反之亦然，因此不會相互阻塞。

16、varchar和char的使用場景？

1.varchar的特點

  • 存儲變長字元串，只佔用必要的存儲空間
  • 列的長度小於255，只用額外的1個位元組來記錄長度
  • 列的長度大於255，只用額外的2個位元組來記錄長度

2.char的特點

  • 存儲定長字元串，最大為255位元組
  • 要刪除字元串末尾的空格。

3.如何區別使用常場景

  • 一、根據字元的長度來判斷。如某個欄位，像人的名字，其最長的長度也是有限的。如我們給其分配18個字元長度即可。此時雖然每個人的名字長度有可能 不同，但是即使為其分配了固定長度的字元類型，即18個字元長度，最後浪費的空間也不是很大。而如果採用NVARCHAR數據類型時，萬一以後需要改名， 而原先的存儲空間不足用來容納新的值，反而會造成一些額外的工作。在這種情況下，進行均衡時，會認為採用CHAR固定長度的數據類型更好。 在實際項目中， 如果某個欄位的字元長度比較短此時一般是採用固定字元長度。
  • 二、是考慮其長度的是否相近。如果某個欄位其長度雖然比較長，但是其長度總是近似的，如一般在90個到100個字元之間，甚至是相同的長度。此時比較 適合採用CHAR字元類型。比較典型的應用就是MD5哈希值。當利用MD5哈希值來存儲用戶密碼時，就非常使用採用CHAR字元類型。因為其長度是相同 的。另外，像用來存儲用戶的身份證號碼等等，一般也建議使用CHAR類型的數據。 另外請大家考慮一個問題，CHAR（1）與VARCHAR（1）兩這個定義，會有什麼區別呢？雖然這兩個都只能夠用來保存單個的字元，但是 VARCHAR要比CHAR多佔用一個存儲位置。這主要是因為使用VARCHAR數據類型時，會多用1個位元組用來存儲長度信息。這個管理上的開銷CHAR 字元類型是沒有的。
  • 三、從碎片角度進行考慮。使用CHAR字元型時，由於存儲空間都是一次性分配的。為此某個欄位的內容，其都是存儲在一起的。單從這個角度來講，其不 存在碎片的困擾。而可變長度的字元數據類型，其存儲的長度是可變的。當其更改前後數據長度不一致時，就不可避免的會出現碎片的問題。故使用可變長度的字元 型數據時，資料庫管理員要時不時的對碎片進行整理。如執行資料庫導出導入作業，來消除碎片。
  • 四、即使使用Varchar數據類型，也不能夠太過於慷慨。這是什麼意思呢？如現在用戶需要存儲一個地址信息。根據評估，只要使用100個字元就可 以了。但是有些資料庫管理員會認為，反正Varchar數據類型是根據實際的需要來分配長度的。還不如給其大一點的呢。為此他們可能會為這個欄位一次性分 配200個字元的存儲空間。這VARCHAR（100）與VARCHAR（200）真的相同嗎？結果是否定的。 雖然他們用來存儲90個字元的數據，其存儲 空間相同。但是對於內存的消耗是不同的。對於VARCHAR數據類型來說，硬碟上的存儲空間雖然都是根據實際字元長度來分配存儲空間的，但是對於內存來 說，則不是。其時使用固定大小的內存塊來保存值。簡單的說，就是使用字元類型中定義的長度，即200個字元空間。顯然，這對於排序或者臨時表（這些內容都 需要通過內存來實現）作業會產生比較大的不利影響。 所以如果某些欄位會涉及到文件排序或者基於磁碟的臨時表時，分配VARCHAR數據類型時仍然不能夠太 過於慷慨。還是要評估實際需要的長度，然後選擇一個最長的欄位來設置字元長度。如果為了考慮冗餘，可以留10%左右的字元長度。千萬不能認為其為根據實際 長度來分配存儲空間，而隨意的分配長度，或者說乾脆使用最大的字元長度。

17、MySQL並發情況下怎麼解決？

  1. 代碼中sql語句優化
  2. 資料庫欄位優化，索引優化
  3. 加緩存，redis/memcache等
  4. 主從，讀寫分離 集群 分流 橫向擴展
  5. 分區
  6. 垂直拆分，解耦模塊
  7. 水平切分 分片

SQL｜MySQL 深入學習（優化，事務，鎖，索引，並發）

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！

本站內容充實豐富，博大精深，小編精選每日熱門資訊，隨時更新，點擊「搶先收到最新資訊」瀏覽吧！


請您繼續閱讀更多來自 程序員小新人學習 的精彩文章:

※線上單台Eureka升級到3台Eureka高可用
※基於vue2.0 +vuex+ element-ui後台管理系統：本地調試詳細步驟

TAG:程序員小新人學習 |