mysql筆記

一五芝、索引

(04篇)
1、為什么使用 N+樹刊咳？

• 樹的高度越高彪见，需要訪問的數(shù)據(jù)塊就越多，所需的讀取時間就越長娱挨，所以使用N+樹(B+)來減少高度余指。
  B+ 樹能夠很好地配合磁盤的讀寫特性，減少單次查詢的磁盤訪問
• 問：為什么索引數(shù)據(jù)越多查詢越慢跷坝？ 答：索引數(shù)據(jù)多了酵镜，樹的高度就高了，磁盤讀取的時候就要去別的數(shù)據(jù)塊讀取數(shù)據(jù)了

2柴钻、索引是在存儲引擎中實現(xiàn)淮韭，而存儲引擎是可替換的，所以索引的實現(xiàn)方式有很多贴届。

• 在InnoDB中使用B+樹來實現(xiàn)

3靠粪、索引類型分為主鍵索引(聚簇索引)和非主鍵索引(二級索引)

• 主鍵索引中保存的值是整行的數(shù)據(jù)足丢，而非主鍵索引保存的是主鍵的值。(見下圖)

  
  
  image

• 具體的查詢時：

  如果語句是 select * from T where ID=500庇配，即主鍵查詢方式斩跌，則只需要搜索 ID 這棵 B+ 樹；
  如果語句是 select * from T where k=5捞慌，即普通索引查詢方式耀鸦，則需要先搜索 k 索引樹，得到 ID 的值為 500啸澡，再到 ID 索引樹搜索一次袖订。這個過程稱為回表。

• 索引的維護：由于B+樹是有順序的嗅虏，所以當插入一個中間大小的索引時洛姑，需要先移動后面的數(shù)據(jù)，空出一個位置在插入(如果當前數(shù)據(jù)頁已經(jīng)滿了皮服，就要重新申請新的數(shù)據(jù)頁楞艾，再把數(shù)據(jù)移動過去，這就是頁分裂)龄广。
  所以盡量用自增主鍵硫眯，就不會出現(xiàn)頁分裂的現(xiàn)象了。

(05)

• 覆蓋索引：可以減少樹的搜索次數(shù)(減少回表)(因為在索引中就已經(jīng)存在了需要的值)

  如何使用覆蓋索引：建立聯(lián)合索引

• 最左前綴原則：只要滿足最左前綴择同，就可以利用索引來加速檢索两入，這個前綴可以是聯(lián)合索引的最左N個字段，也可以是字符串索引的最左M個字符敲才。

  例如： like '張%' 這就滿足最左前綴原則裹纳，可以用到索引; like '%張%' 這就不滿足了(但是會用索引快速定位記錄,不算用了索引)

• 聯(lián)合索引：
  寫法 KEY `name_age` (`name`,`age`)
  生成索引結構如圖.....
  

  image

  如果索引為(a,b)，索引中會先根據(jù)a排序紧武，在根據(jù)b排序(b只有在a相同的時候排序有規(guī)律)剃氧。
  例子：(1,2), (2,3), (2,4), (3,1), (4,2)
  所以當只有基于b的查詢條件時,無法使用(a,b)的聯(lián)合索引,因為b不是規(guī)律的

• 索引下推：

  在MySQL5.6引入了索引下推優(yōu)化。

  可以在索引便利過程中脏里，對索引中包含的字段先做判斷她我，直接過濾掉不滿足的條件的記錄虹曙，減少回表次數(shù)迫横。

  例子：建立聯(lián)合索引(name,age) 使用 like '張%’and age >10 檢索，MySQL5.6版本之前酝碳，會先根據(jù)索引找到符合張%的列,再對匹配的數(shù)據(jù)進行回表查詢矾踱。5.6版本后，根據(jù)索引找出符合的數(shù)據(jù)后,會根據(jù)聯(lián)合索引中存儲的age的值在篩選一遍疏哗，排除不符合的lie(age <= 10)再進行回表查詢呛讲，減少回表率，提升檢索速度。 如下圖.....

  image

二贝搁、全局鎖吗氏、表鎖(06)

1、全局鎖：對整個數(shù)據(jù)庫實例加鎖

使用場景：全庫邏輯備份雷逆。(推薦使用–single-transaction方法)

2弦讽、表級鎖：分為表鎖和MDL(metadata lock)

• 表鎖：一般只有在沒有行級鎖的時候才是用

• MDL：不需要顯示調用,在訪問一個表的時候會自動加上。 作用：保證讀寫的正確性
  MySQL5.6中加入MDL膀哲，當對一個表增刪改查時,加MDL讀鎖; 當對表做結構變更操作時,加MDL寫鎖往产。

  讀鎖不互斥:可以多線程對表CURD
  讀寫鎖、寫鎖之間互斥:用來保證變更表結構的安全性

三某宪、行鎖(07)

1仿村、兩階段鎖協(xié)議：在InnoDB事務中,行鎖是需要的時候才加上的,要等到事務結束才釋放

• 優(yōu)化點：如果事務中需要所多個行，要把最可能造成鎖沖突兴喂、最可能影響并發(fā)度的鎖放在后面執(zhí)行蔼囊。

2、死鎖和死鎖檢測：當兩個事務互相等待就進入死鎖

• 解決方案：

  1衣迷、進入等待直到超時压真，參數(shù)：innodb_lock_wait_timeout。
  2蘑险、發(fā)起死鎖檢測滴肿，發(fā)現(xiàn)死鎖后，主動回滾死鎖鏈中的某一個事務佃迄，參數(shù)：innodb_deadlock_detect設置為on(默認為on開啟) 死鎖檢測會消耗大量的CPU資源泼差，所以遇到熱點行跟新的時候，最好控制并發(fā)度呵俏。

3堆缘、innodb行級鎖是通過索引記錄實現(xiàn)的，如果更新的列沒有建索引就會鎖住整個表普碎。

四吼肥、事務(行級鎖和事務隔離級別的原理)(08)

• 1、一致性讀視圖(consistent read view): InnoDB在實現(xiàn)MVCC時會用到 ,用于支持RC(讀提交)和RR(可重復讀)隔離級別的實現(xiàn),它沒有物理結構麻车，作用是事務執(zhí)行期間用來定義"我能看到什么數(shù)據(jù)"缀皱。

• 2、InnoDB 的行數(shù)據(jù)有多個版本动猬，每個數(shù)據(jù)版本有自己的 row trx_id啤斗，每個事務或者語句有自己的一致性視圖。普通查詢語句是一致性讀赁咙，一致性讀會根據(jù) row trx_id 和一致性視圖確定數(shù)據(jù)版本的可見性钮莲。

  在可重復讀隔離級別下免钻，只需要在事務開始的時候創(chuàng)建一致性視圖，之后事務里的其他查詢都共用這個一致性視圖崔拥。
  在讀提交隔離級別下极舔，每一個語句執(zhí)行前都會重新算出一個新的視圖。

• 3链瓦、當前讀:(current read)：更新數(shù)據(jù)都是先讀后寫的姆怪，而這個讀，只能讀當前的值(數(shù)據(jù)庫中最新的值)澡绩。

  select 語句如果加鎖稽揭，也是當前讀。

  例子：如圖.....

  image

五肥卡、普通索引和唯一索引(09)

• 1溪掀、唯一索引：列中所有的值不重復，不能為null步鉴。
  ?????普通索引：列中可以有重復的值揪胃。

• 2、change buffer：

  當跟新一個數(shù)據(jù)時氛琢，如果該數(shù)據(jù)頁在內存中就直接更新內存(同時寫redo log日志)喊递。如果數(shù)據(jù)頁沒在內存中,會將更新操作緩存在change buffer中，在下次查詢訪問時才將數(shù)據(jù)讀入內存阳似，然后執(zhí)行change buffer中與這個頁相關的操作骚勘。

• 3、InnoDB數(shù)據(jù)的讀寫：

  會先以數(shù)據(jù)頁為單位撮奏，將一頁數(shù)據(jù)頁都讀到內存中俏讹，然后在內存中讀取。每個數(shù)據(jù)頁默認大小(16KB)

• 4畜吊、InnoDB數(shù)據(jù)更新：和change buffer原理類似泽疆。

• 數(shù)據(jù)更新例子：

  mysql> insert into t(id,k) values(id1,k1),(id2,k2);

  1、Page 1 在內存中玲献，直接更新內存殉疼；
  2、Page 2 沒有在內存中捌年，就在內存的 change buffer 區(qū)域瓢娜，記錄下“我要往 Page 2 插入一行”這個信息
  3、將上述兩個動作記入 redo log 中延窜。

• 5恋腕、redo log 主要節(jié)省的是隨機寫磁盤的 IO 消耗（轉成順序寫），而 change buffer 主要節(jié)省的則是隨機讀磁盤的 IO 消耗逆瑞。