表

原文：http://yingminxing.com/mysql%E6%8A%80%E6%9C%AF%E5%86%85%E5%B9%95innodb%E5%AD%98%E5%82%A8%E5%BC%95%E6%93%8E%EF%BC%8D%E8%A1%A8%E7%B4%A2%E5%BC%95%E7%AE%97%E6%B3%95%E5%92%8C%E9%94%81/

4.1、innodb存儲(chǔ)引擎表類(lèi)型

innodb表類(lèi)似oracle的IOT表(索引聚集表-indexorganized table)，在innodb表中每張表都會(huì)有一個(gè)主鍵，如果在創(chuàng)建表時(shí)沒(méi)有顯示的定義主鍵則innodb如按照如下方式選擇或者創(chuàng)建主鍵。

首先表中是否有唯一非空索引(unique not null)，如果有則該列即為主鍵。

不符合上述條件，innodb存儲(chǔ)引擎會(huì)自動(dòng)創(chuàng)建一個(gè)6字節(jié)大小的指針，rowid()。

4.2、innodb邏輯存儲(chǔ)結(jié)構(gòu)

innodb的邏輯存儲(chǔ)單元由大到小分別是 tablespace,segment,extent,page(block)組成

4.2.1、表空間(tablespace)

所有數(shù)據(jù)都是存放在表空間中的，啟用了參數(shù)innodb_file_per_table，則每張表內(nèi)的數(shù)據(jù)可以單獨(dú)放到一個(gè)表空間中，每張表空間內(nèi)存放的只是數(shù)據(jù)，索引和插入緩沖，其他類(lèi)的數(shù)據(jù)，如undo信息，系統(tǒng)事務(wù)信息，二次寫(xiě)緩沖等還是存放在原來(lái)你的共享表空間。

4.2.2、段(segment)

常見(jiàn)的segment有數(shù)據(jù)段、索引段、回滾段。innodb是索引聚集表，所以數(shù)據(jù)就是索引，索引就是數(shù)據(jù)，那么數(shù)據(jù)段即是B+樹(shù)的頁(yè)節(jié)點(diǎn)(leaf node segment)，索引段即為B+樹(shù)的非索引節(jié)點(diǎn)(non-leaf node segment)。而且段的管理是由引擎本身完成的。

4.2.3、區(qū)(extend)

區(qū)是由64個(gè)連續(xù)的頁(yè)主成，每個(gè)頁(yè)大小為16K，即每個(gè)區(qū)的大小為(64*16K)=1MB,對(duì)于大的數(shù)據(jù)段，mysql每次最多可以申請(qǐng)4個(gè)區(qū)，以此保證數(shù)據(jù)的順序性能。

4.2.4、頁(yè)(page)

頁(yè)是innodb磁盤(pán)管理最小的單位，innodb每個(gè)頁(yè)的大小是16K，且不可更改。常見(jiàn)的類(lèi)型有：數(shù)據(jù)頁(yè) B-tree Node；undo頁(yè) Undo Log Page；系統(tǒng)頁(yè) System Page；事務(wù)數(shù)據(jù)頁(yè) Transaction system Page；插入緩沖位圖頁(yè) Insert Buffer Bitmap；插入緩沖空閑列表頁(yè) Insert Buffer freeBitmap；未壓縮的二進(jìn)制大對(duì)象頁(yè)Uncompressed BLOB Page；壓縮的二進(jìn)制大對(duì)象頁(yè) Compressed BLOB Page。

4.2.5、行

innodb存儲(chǔ)引擎是面向行的(row-oriented),也就是說(shuō)數(shù)據(jù)的存放按行進(jìn)行存放。每個(gè)頁(yè)最多可以存放16K/2～200行,也就是7992個(gè)行。

4.3、innodb物理存儲(chǔ)結(jié)構(gòu)

innodb引擎由共享表空間，日志文件(redo log)，表結(jié)構(gòu)定義文件組成。

4.4、innodb行記錄格式

mysql從5.1開(kāi)始，innodb提供了compact和redundant(為了兼容以前版本)兩種格式來(lái)存放行記錄數(shù)據(jù)。

4.4.1、compact行記錄格式

Compact行記錄的設(shè)計(jì)目標(biāo)是能高效存放數(shù)據(jù)。不管是char還是varchar類(lèi)型，NULL指是不占用存儲(chǔ)空間的。行記錄中還包括兩個(gè)隱藏列 事務(wù)ID列(6字節(jié))和回滾指針列(7字節(jié)) 若沒(méi)有定義的PrimaryKey 會(huì)增加一個(gè)6字節(jié)的RowID列。InnoDB在頁(yè)內(nèi)部是通過(guò)一種鏈表方式串聯(lián)各個(gè)行記錄的。

4.4.2、redundant行記錄格式

Redundant行記錄格式為了兼容以前版本。每個(gè)行最多有1023個(gè)列，因?yàn)榱械臄?shù)量占用了10位。對(duì)于varchar的NULL值，它不占用任何存儲(chǔ)空間，而對(duì)于類(lèi)型char的NULL值需要占用空間。

4.4.3、行溢出數(shù)據(jù)

innoDB存儲(chǔ)引擎可以將一條記錄中的某些數(shù)據(jù)存儲(chǔ)在真正的數(shù)據(jù)頁(yè)面之外，作為行溢出數(shù)據(jù)。Varchar(N)中的N指的是字符的長(zhǎng)度，官方手冊(cè)中定義的65535長(zhǎng)度是指所有VARCHAR列的長(zhǎng)度總合。

數(shù)據(jù)一般都是存放在B-tree Node的頁(yè)類(lèi)型中，但是發(fā)生行溢出的時(shí)，存放行溢出的頁(yè)類(lèi)型為Uncompress BLOB Page。如果一個(gè)頁(yè)中至少放入兩行的數(shù)據(jù)，那varchar就不會(huì)存放到BLOB頁(yè)中，閥值長(zhǎng)度為8098。對(duì)于TEXT或者BLOB的數(shù)據(jù)類(lèi)型，我們總是以為它們是放在Uncompressed BLOB Page中的，其實(shí)這也是不準(zhǔn)確的，放在數(shù)據(jù)頁(yè)還是BLOB頁(yè)同樣和前面討論的VARCHAR一樣。

4.4.4、compressed與dynamic記錄格式

InnoDB Plugin引入了新的文件格式成為Barracuda文件格式，它擁有兩種新的行記錄格式Compressed和Dynamic兩種，它對(duì)于存放BLOB的數(shù)據(jù)采用了安全的行溢出方式。

4.4.5、char的行結(jié)構(gòu)存儲(chǔ)

從mysql4.1開(kāi)始CHR(n)，中N指定的是字符的長(zhǎng)度，而不是之前版本的字節(jié)長(zhǎng)度。也就是說(shuō)在不同字符集下，CHAR的內(nèi)部存儲(chǔ)不是定長(zhǎng)的數(shù)據(jù)。可以通過(guò)select a,char_length(a),length(a) from t;查看字符和字節(jié)數(shù)。所以在多字符集下，char和varchar占用a空間是一樣的。

4.5、innodb數(shù)據(jù)頁(yè)結(jié)構(gòu)

InnoDB數(shù)據(jù)頁(yè)由七部分組成：

File Header：文件頭( 38 bytes )

Page Header：頁(yè)頭( 56 bytes )

Infimum + Supremum Records：頁(yè)中上/下界記錄

Users Records：用戶記錄，即行記錄

Free Space：空閑空間

Page Directory：葉目錄

File Trailer：文件結(jié)尾信息

4.6、named file formats

innodb存儲(chǔ)引擎通過(guò)named file formats機(jī)制來(lái)解決不同版本下頁(yè)結(jié)構(gòu)兼容性問(wèn)題。之前的版本定義為Antelope(包括Compact和Redudant文件格式)，最新定義為Barracuda(包括Compressed和Dynamic文件格式)。使用參數(shù)innodb_file_format指定文件格式。

4.7、約束

4.7.1、數(shù)據(jù)完整性

innodb提供了以下四種約束：Primary key，Unique Key，Foreign Key，Default，Not NULL。

4.7.2、約束的創(chuàng)建和查找

創(chuàng)建時(shí)候定義，或者使用alter table定義。

4.7.3、約束和索引的區(qū)別

primary key和unique key既是約束也是主鍵。約束是一個(gè)邏輯的概念，用來(lái)保證數(shù)據(jù)完整性，而索引是一個(gè)數(shù)據(jù)結(jié)構(gòu)，有邏輯上的概念，在數(shù)據(jù)庫(kù)中更是一個(gè)物理存儲(chǔ)的方式。

4.7.4、對(duì)于錯(cuò)誤數(shù)據(jù)的約束

可以通過(guò)修改sql_mode來(lái)保證約束的強(qiáng)制性。

4.7.5、ENUM和SET約束

由于mysql不支持check約束，所以可以通過(guò)ENUM和SET來(lái)實(shí)現(xiàn)部分需求，還可以通過(guò)觸發(fā)器來(lái)實(shí)現(xiàn)check約束，注意需要修改sql_mode=’strict_trans_tables’; 只能限于對(duì)離散數(shù)值的約束，對(duì)于ENUM 若插入非法值將插入空字符串作為特殊錯(cuò)誤值。

4.7.6、觸發(fā)器與約束

觸發(fā)器的作用是在insert,delete和update命令之前或之后自動(dòng)調(diào)用sql命令或者存儲(chǔ)過(guò)程。所以一個(gè)表最多可以建立6個(gè)觸發(fā)器。

4.7.7、外鍵

4.8、視圖

4.8.1、視圖的作用

4.8.2、物化視圖

Oracle數(shù)據(jù)庫(kù)支持物化視圖—該視圖不是基于基表的虛表，而是根據(jù)基表實(shí)際存在的實(shí)表，物化視圖可以用于預(yù)先計(jì)算并保存表鏈接或聚集等耗時(shí)較多的操作結(jié)果。在MS中，這種視圖為索引視圖。當(dāng)基表發(fā)生了DML操作后，物化視圖采用ON DEMAND和ON COMMIT方式刷新進(jìn)行同步。Mysql的視圖不支持物化視圖，都是虛擬的。

4.9、分區(qū)表

4.9.1、分區(qū)表的概述

分區(qū)表不是在存儲(chǔ)引擎曾完成的，所以不止innodb支持分區(qū)表功能。myisma，ndb等都支持。mysql的分區(qū)表是水平分區(qū)，并不是垂直分區(qū)，mysql的分區(qū)表是局部分區(qū)索引，一個(gè)分區(qū)中既存儲(chǔ)數(shù)據(jù)又存放索引。當(dāng)前mysql數(shù)據(jù)庫(kù)支持以下幾種類(lèi)型的分區(qū)：

Range分區(qū)，行數(shù)據(jù)基于屬于一個(gè)給定連續(xù)區(qū)間的列值放入分區(qū)，這個(gè)值只能是整數(shù)。VALUE LESS THAN需指定MAXVALUE值的分區(qū)，主要用于日期列的分區(qū)。對(duì)于RANGE分區(qū)的查詢，優(yōu)化器只能對(duì)YEAR() TO_DAYS() TO_SECONDS()和UNIX_TIMESTAMP()函數(shù)進(jìn)行優(yōu)化選擇。

LIST分區(qū)和range類(lèi)似，只是list分區(qū)里面是離散的值，這個(gè)值只能是整數(shù)。(VALUE IN對(duì)于未定義的插入，MySQL會(huì)拋出異常。對(duì)于多條記錄同時(shí)插入過(guò)程中存在未定義的值時(shí)，MyISAM分區(qū)會(huì)允許之前的行數(shù)據(jù)插入，而拒絕之后的行數(shù)據(jù)插入，但是InnoDB將其視為一個(gè)事務(wù)從而ROLLBACK整個(gè)插入。

HASH分區(qū)，根據(jù)用戶自定義的表達(dá)式的返回值 返回值不為負(fù)(PARTITION BY HASH (expr) 將數(shù)據(jù)均勻分布還可按LINEAR HASH分區(qū)區(qū)別在于算法不同)。hash分區(qū)的目的是將數(shù)據(jù)均勻的分布到預(yù)先定義的各個(gè)分區(qū)中，保證各分區(qū)的數(shù)據(jù)量大致一致。

KEY分區(qū)，根據(jù)mysql數(shù)據(jù)庫(kù)提供的哈西函數(shù)進(jìn)行分區(qū)。key分區(qū)和hash分區(qū)相似，不同在于hash分區(qū)是用戶自定義函數(shù)進(jìn)行分區(qū)，key分區(qū)使用mysql數(shù)據(jù)庫(kù)提供的函數(shù)進(jìn)行分區(qū)。

columns分區(qū)，mysql-5.5開(kāi)始支持COLUMNS分區(qū)，可視為RANGE和LIST分區(qū)的進(jìn)化，COLUMNS分區(qū)可以直接使用非整形數(shù)據(jù)進(jìn)行分區(qū)。RANGE COLUMNS分區(qū)可對(duì)多個(gè)列的值進(jìn)行分區(qū)。

不論什么類(lèi)型的分區(qū)，如果表中存在主鍵和唯一索引，那么分區(qū)列必須是主鍵或者唯一索引的一個(gè)組成部分。否則回報(bào)錯(cuò)。

4.9.2、子分區(qū)

mysql允許在RANGE和LIST分區(qū)上再進(jìn)行HASH或者key的子分區(qū)。每個(gè)分區(qū)上的子分區(qū)數(shù)量必須相同。在每個(gè)分區(qū)內(nèi)，子分區(qū)的名稱(chēng)是唯一的，分區(qū)可以放到不同磁盤(pán)上。

4.9.3、分區(qū)中的NULL值

RANGE,HASH,KEY分區(qū)如果插入null值，mysql會(huì)把它放入最左邊的分區(qū)，如果刪除最左邊的分區(qū)，null值不會(huì)被刪除，他會(huì)記錄到新的最左邊的分區(qū)。LIST分區(qū)如果沒(méi)有指定NULL值的存放位置，那么就會(huì)報(bào)錯(cuò)。

4.9.4、分區(qū)的性能

OLTP(在線事務(wù)處理，如博客，電子商務(wù)，網(wǎng)絡(luò)游戲)系統(tǒng)不適合使用分區(qū)表，如果磁盤(pán)空間和磁盤(pán)IO沒(méi)出現(xiàn)瓶頸，也不建議使用分區(qū)表。而OLAP(在線分析處理，如數(shù)據(jù)倉(cāng)庫(kù)，數(shù)據(jù)集市)比較適合分區(qū)操作。

索引和算法

索引和開(kāi)銷(xiāo)是需要找一個(gè)平衡點(diǎn)，過(guò)多或者過(guò)少都會(huì)影響性能，從而導(dǎo)致負(fù)載過(guò)高，浪費(fèi)硬件資源。而且索引應(yīng)該一開(kāi)始就需要添加上，事后添加的話需要DBA根據(jù)監(jiān)控大量SQL語(yǔ)句，耗費(fèi)大量時(shí)間。

5.1、innodb存儲(chǔ)引擎概述

innodb支持常見(jiàn)的兩種索引，B+樹(shù)索引和hash索引。hash索引是自適應(yīng)的，不能認(rèn)為干預(yù)。B+樹(shù)是由平衡二叉樹(shù)演化而來(lái)，但是B+樹(shù)不是一個(gè)二叉樹(shù)。

B+樹(shù)并不能直接找到具體的行，B+樹(shù)索引只能找到數(shù)據(jù)行所在的頁(yè)，然后數(shù)據(jù)庫(kù)通過(guò)把頁(yè)讀入內(nèi)存，再在內(nèi)存中進(jìn)行查找。

5.2、二分查找法

頁(yè)中的具體行就是通過(guò)二分法查找的。1946年發(fā)明的二分查找法，直到1962年才出現(xiàn)完整正確的二分查找法。

5.3、平衡二叉樹(shù)

平衡二叉樹(shù)(左節(jié)點(diǎn)鍵值

5.4、B+樹(shù)

B+樹(shù)是從B樹(shù)和索引順序訪問(wèn)方法演化而來(lái)。在B+樹(shù)中，所有記錄節(jié)點(diǎn)都是按鍵值的大小順序存放在同一層的葉節(jié)點(diǎn)中，各頁(yè)節(jié)點(diǎn)指針進(jìn)行鏈接。同時(shí)它們的父節(jié)點(diǎn)只是作為索引節(jié)點(diǎn)使用。

5.4.1、B+樹(shù)的插入操作

B+樹(shù)總會(huì)保持平衡，但是對(duì)于新插入的值可能需要大量拆分，這樣會(huì)消耗大量磁盤(pán)資源，所以B+樹(shù)有了旋轉(zhuǎn)(rotation)功能，旋轉(zhuǎn)發(fā)生在leat page已經(jīng)滿了，但是其左右節(jié)點(diǎn)沒(méi)有滿的情況下，這時(shí)B+樹(shù)并不會(huì)著急去拆分頁(yè)的操作，而且是將記錄轉(zhuǎn)移到所在頁(yè)的兄弟節(jié)點(diǎn)上，通常左兄弟先被檢查。具體操作看書(shū)。

5.4.2、B+樹(shù)的刪除操作

B+樹(shù)使用填充因子(fill factor)來(lái)控制樹(shù)的刪除變化，50%是填充因子可設(shè)的最小值。B+樹(shù)的刪除操作同樣必須保證刪除后頁(yè)節(jié)點(diǎn)中的記錄依然排序。具體操作看書(shū)。

5.5、B+樹(shù)索引

B+樹(shù)索引在數(shù)據(jù)庫(kù)中有一個(gè)特點(diǎn)是高扇出性(fan out),B+樹(shù)的高度一般是2-3層。B+樹(shù)索引可以分為聚集索引(clustered index)和輔助聚集索引(secondary index)，其內(nèi)部都是B+樹(shù)，葉節(jié)點(diǎn)存放著所有的數(shù)據(jù)。它們不同的是：葉節(jié)點(diǎn)存放的是否是一整行的信息。

聚集索引：即表中數(shù)據(jù)按照主鍵順序存放，而聚集索引就是按照每張表的主鍵構(gòu)造一顆B+樹(shù)，并且葉節(jié)點(diǎn)中存放著整張表的行記錄數(shù)據(jù)。聚集索引的存儲(chǔ)并不是物理上的連續(xù)，而是邏輯上的連續(xù)。它的另一個(gè)好處是：對(duì)于主鍵的排序查找和范圍查找速度非常快。

輔助索引：也稱(chēng)為非聚集索引，葉級(jí)別不包含行的全部數(shù)據(jù)，葉節(jié)點(diǎn)除了包行鍵值以外，每個(gè)葉級(jí)別中的索引行中還包含了一個(gè)書(shū)簽，該書(shū)簽就是對(duì)應(yīng)行數(shù)據(jù)的聚集索引鍵。

5.5.1、B+樹(shù)索引的管理

索引可以索引整個(gè)列的數(shù)據(jù)，也可以只索引一個(gè)列的開(kāi)頭部分?jǐn)?shù)據(jù)。InnoDB Plugin支持一種稱(chēng)為快速索引創(chuàng)建方法，這種方法只限定于輔助索引，創(chuàng)建索引會(huì)對(duì)表加上一個(gè)S鎖，刪除時(shí)只需將輔助索引的空間標(biāo)記為可用，并刪除內(nèi)部視圖上的對(duì)該表的索引定義即可。

5.6、B+樹(shù)索引的使用

5.6.1、什么時(shí)候使用B+樹(shù)索引

當(dāng)某個(gè)字段的取值范圍很廣，幾乎沒(méi)有重復(fù)，即高選擇性，則使用B+樹(shù)索引是最適合的。根據(jù)筆者經(jīng)驗(yàn)，一般取出數(shù)據(jù)占整個(gè)的20%時(shí)，優(yōu)化器就不會(huì)使用索引，而是全表掃描。

5.6.2、順序讀，隨機(jī)讀與預(yù)讀取

順序讀是指根據(jù)索引的葉節(jié)點(diǎn)數(shù)據(jù)就能順序地讀取所需要的行數(shù)據(jù)，只是邏輯地順序讀在物理磁盤(pán)上可能還是隨機(jī)讀取。

隨機(jī)讀是指一般需要根據(jù)輔助索引葉節(jié)點(diǎn)中的主鍵尋找實(shí)際行數(shù)據(jù)，而輔助索引和主鍵所在的數(shù)據(jù)段不同，因此訪問(wèn)方式是隨機(jī)的。

為提高讀取性能，InnoDB采用預(yù)讀取方式將所需數(shù)據(jù)讀入內(nèi)存，包括隨機(jī)預(yù)讀取 random read ahead 和線性預(yù)讀取 linear read ahead。但是自InnoDB Plugin1.0.4起,隨機(jī)訪問(wèn)的預(yù)讀取被取消了,保留了線性預(yù)讀取,并加入了innodb_read_ahead_threshold參數(shù)。它控制一個(gè)區(qū)中多少頁(yè)被順序訪問(wèn)時(shí)，InnoDB才啟用預(yù)讀取，預(yù)讀取下一個(gè)頁(yè)中所有的頁(yè)。

5.7、hash索引

innodb存儲(chǔ)引擎中自適應(yīng)hash索引使用的是散列表(hash table)的數(shù)據(jù)結(jié)構(gòu)。但是散列表不只存在于自適應(yīng)hash中，每個(gè)數(shù)據(jù)庫(kù)中都存在，用來(lái)加速內(nèi)存中數(shù)據(jù)的查找。

5.7.1哈西表(hash table)

hash table又叫散列表，由直接尋址表改進(jìn)而來(lái)。利用哈希函數(shù)解決了直接尋址遇到的問(wèn)題，同時(shí)又使用鏈接發(fā)解決了碰撞問(wèn)題。

5.7.2自適應(yīng)哈西索引

它是數(shù)據(jù)庫(kù)系統(tǒng)自己創(chuàng)建并使用的，DBA本身并不能對(duì)其進(jìn)行干預(yù)。需要注意的是，哈希索引只能用來(lái)搜素等值的查詢，對(duì)于其它的查找是不能使用哈希索引的。我們只能通過(guò)參數(shù)innodb_adaptive_hash_index來(lái)禁用或啟動(dòng)此特性。

鎖

鎖是區(qū)別文件系統(tǒng)和數(shù)據(jù)庫(kù)系統(tǒng)的一個(gè)關(guān)鍵特性。

6.1、什么是鎖?

鎖是用來(lái)管理對(duì)共享文件的并發(fā)訪問(wèn)。innodb會(huì)在行級(jí)別上對(duì)數(shù)據(jù)庫(kù)上鎖。不過(guò)innodb存儲(chǔ)引擎會(huì)在數(shù)據(jù)庫(kù)內(nèi)部其他多個(gè)地方使用鎖，從而允許對(duì)不同資源提供并發(fā)訪問(wèn)。例如操作緩沖池中的LRU列表，刪除，添加，移動(dòng)LRU列表中的元素，為了保證一致性，必須有鎖的介入。

6.2、innodb存儲(chǔ)引擎中的鎖

6.2.1、鎖的類(lèi)型

S lock 共享鎖允許事務(wù)讀一行數(shù)據(jù)。

X lock 排它鎖允許事務(wù)刪除或者更新一條數(shù)據(jù)。

IS lock 意向共享鎖事務(wù)想要獲得一個(gè)表中某幾行的共享鎖。

IX lock 意向拍他所事務(wù)想要獲得一個(gè)表中某幾行的排它鎖。

因?yàn)镮nnoDB存儲(chǔ)引擎支持的是行級(jí)別的鎖，所以意向鎖其實(shí)不會(huì)阻塞除全表掃描以外的任何請(qǐng)求。

6.2.2、一致性的非鎖定讀操作

一致性非鎖定讀(consistent nonlocking read)是指innodb通過(guò)多版本控制(multi versioning)的方式來(lái)讀取當(dāng)前執(zhí)行時(shí)間數(shù)據(jù)庫(kù)中行的數(shù)據(jù)。非鎖定讀的機(jī)制大大提高了數(shù)據(jù)讀取的并發(fā)性，在InnoDB引擎中為默認(rèn)的讀取方法，即讀取不會(huì)占用和等代表上的鎖。

多版本控制是通過(guò)快照實(shí)現(xiàn)的，快照數(shù)據(jù)其實(shí)就是當(dāng)前數(shù)據(jù)之前的歷史版本，可能有多個(gè)版本。這種技術(shù)稱(chēng)為行多版本技術(shù)，由此帶來(lái)的并發(fā)控制叫做多半本并發(fā)控制(multi version concurrency control,MVCC).

在Read Committed和Repeatable Read(innodb默認(rèn)的事務(wù)隔離級(jí)別)下，innodb存儲(chǔ)引擎使用非鎖定的一致性讀。但是對(duì)于快照數(shù)據(jù)的定義卻不同。在Read Commited級(jí)別，對(duì)于快照數(shù)據(jù)，非一致性讀總是讀取被鎖定行的最新一份快照。在Repeatable級(jí)別下，對(duì)于快照數(shù)據(jù)，非一致性讀總是讀取事務(wù)開(kāi)始時(shí)的行數(shù)據(jù)版本。

6.2.3、SELECT…FOR UPDATE &SELECT…LOCK IN SHARE MODE

SELECT…FOR UPDATE 可以獲得一個(gè)X鎖。

SELECT…LOCK IN SHARE MODE 可以獲得一個(gè)S鎖。

注意上述操作時(shí)必須使用顯示提交方式，即加上begin,start transaction或者set autocommit = 0。

6.2.4、自增長(zhǎng)和鎖

對(duì)于含有子增長(zhǎng)計(jì)數(shù)器的表進(jìn)行插入時(shí)，會(huì)執(zhí)行”SELECT MAX(auto_inc_col) FROM t FOR UPDATE;”插入操作會(huì)更具這個(gè)自增長(zhǎng)的計(jì)數(shù)器值加1賦予自增長(zhǎng)列。這個(gè)實(shí)現(xiàn)方式叫做AUTO-INC Locking。這是一種特殊的鎖，為了提高并發(fā)，它不會(huì)在事務(wù)執(zhí)行完才釋放，只是在語(yǔ)句執(zhí)行后立即釋放。

從mysql-5.1.22版本開(kāi)始，innodb引擎提供了一種輕量級(jí)互斥量的自增長(zhǎng)實(shí)現(xiàn)機(jī)制，這種機(jī)制大大提高了子增長(zhǎng)值插入的性能。并且mysql-5.1.22開(kāi)始，innodb引擎提供了一個(gè)參數(shù)innodb_autoinc_lock_mode，默認(rèn)的值為1。在討論新的增長(zhǎng)方式之前我們需要對(duì)自增長(zhǎng)實(shí)現(xiàn)方式分類(lèi)：

1.INSERT-LIKE：指所有的插入語(yǔ)句，比如 INSERT、REPLACE、INSERT…SELECT、REPLACE…SELECT,LOAD DATA等。

2.Simple insert：指在插入前就能確定插入行數(shù)的語(yǔ)句，包括INSERT、REPLACE，不包含INSERT…ON DUPLICATE KEY UPDATE這類(lèi)語(yǔ)句。

3.Bulk inserts：指在插入前不能確定得到插入行的語(yǔ)句。如INSERT…SELECT,REPLACE…SELECT,LOAD DATA.

4.Mixed-mode inserts:指其中一部分是子增長(zhǎng)的，有一部分是確定的。

現(xiàn)在有SIMPLE INSERT、BULK INSERTS、MIXED-MODE INSERTS三種類(lèi)型的INSERT語(yǔ)句，有AUTO-inc locking(最早的)和輕量級(jí)互斥量的自增長(zhǎng)兩種auto—increment鎖。

1.innodb_autoinc_lock_mode=0 5.1.22之前的方式，也就是所有類(lèi)型的insert都用AUTO-inc locking。

2.innodb_autoinc_lock_mode=1 這個(gè)參數(shù)是5.1.22之后出現(xiàn)的也是之后的默認(rèn)值，對(duì)于SIMPLE INSERT，使用輕量級(jí)互斥量的鎖，對(duì)于BULK INSERT，使用AUTO-inc locking。

3.innodb_autoinc_lock_mode=2 指不管什么情況都使用輕量級(jí)互斥的鎖，效率最高，但是復(fù)制只能使用row-basereplication，因?yàn)閟tatement-base replication會(huì)出現(xiàn)問(wèn)題。

另外就是innodb和myisam的一個(gè)區(qū)別，innodb下，自增長(zhǎng)必須是索引，而且必須是索引的第一個(gè)列，不然會(huì)報(bào)錯(cuò)，myisam不會(huì)出現(xiàn)這個(gè)問(wèn)題。

6.2.5、外鍵和鎖

外鍵主要用于引用完整性的約束檢查。innodb中，對(duì)于一個(gè)外鍵列，如果沒(méi)有顯示的對(duì)這個(gè)列加索引，innodb就自動(dòng)的對(duì)其加一個(gè)索引。

6.3、鎖的算法

1.Record Lock，單行記錄上的鎖，鎖住索引記錄。

2.GapLock，間隙鎖能鎖定一個(gè)范圍，但不包括記錄本身如 < 6 時(shí)，依然可以插入6。

3.Next-KeyLock：Gap Lock + Record Lock，鎖定一個(gè)范圍并且鎖定記錄本身，如 < 6，插入6時(shí)會(huì)被阻塞。

在REPEATABLE READ模式下 Next-KeyLock算法是默認(rèn)的行記錄鎖定算法。

6.4、鎖問(wèn)題

本來(lái)鎖問(wèn)題會(huì)導(dǎo)致的是更新丟失、幻讀、臟讀、不可重復(fù)讀，但是innodb作者卻只寫(xiě)出了三種問(wèn)題,可能是幻讀通過(guò)innodb Next-key Lock解決了，作者就沒(méi)有提及。這幾個(gè)鎖問(wèn)題對(duì)應(yīng)事務(wù)隔離的4個(gè)安全級(jí)別:

READ UNCOMMITTED(事務(wù)隔離最低的級(jí)別，有事務(wù)隔離就能解決更新丟失，但是存在臟讀的問(wèn)題)。

READ COMMITED(ORACLE和SQL SERVER默認(rèn)的隔離級(jí)別，解決了臟讀，但是一個(gè)事務(wù)多次讀取的內(nèi)容不同，出現(xiàn)了不可重復(fù)讀的問(wèn)題)。

READ REPEATABLE(可重復(fù)讀，innodb引擎的默認(rèn)事務(wù)隔離級(jí)別，解決了不可重復(fù)讀的問(wèn)題，但是產(chǎn)生了幻讀，innodb通過(guò)Next-key lock解決了幻讀)。

SERIALIZABLE(可串行話，通過(guò)強(qiáng)制事務(wù)排序解決幻讀問(wèn)題，會(huì)降低性能)總的看來(lái)innodb默認(rèn)的 READ REPEATABLE是非常棒的。

6.5、阻塞

innodb中需要其他事務(wù)的鎖釋放它鎖占用的資源，這個(gè)時(shí)候就會(huì)發(fā)生鎖等待，這就是阻塞。innodb引擎有兩個(gè)相關(guān)參數(shù)：innodb_lock_wait_timeout 用來(lái)設(shè)定等待的時(shí)間，默認(rèn)是50秒，這是一個(gè)動(dòng)態(tài)參數(shù)，可以隨時(shí)調(diào)整；innodb_rollback_on_timeout用來(lái)設(shè)定是否在等待超時(shí)時(shí)對(duì)進(jìn)行中的事務(wù)進(jìn)行回滾操作，默認(rèn)是OFF，代表不回滾，這是一個(gè)靜態(tài)參數(shù)。

6.6、死鎖

死鎖會(huì)產(chǎn)生阻塞，所以可以通過(guò)6.5的參數(shù)，讓超時(shí)的阻塞回滾。還有就是開(kāi)發(fā)的時(shí)候，每個(gè)事務(wù)對(duì)表，字段，行的操作，都是順序的，這樣可以很大程度上避免死鎖。

總結(jié)

以上是生活随笔為你收集整理的mysql技术内幕innodb存储引擎——表索引算法和锁_(转)Mysql技术内幕InnoDB存储引擎－表索引算法和锁...的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。