http://www.importnew.com/22083.html
MySQL 事務(wù)
MySQL索引背后的數(shù)據(jù)結(jié)構(gòu)及算法原理
MySQL索引類型
MySQL鎖詳解
事務(wù)用于處理操作量大躲惰，復(fù)雜度高的數(shù)據(jù)豪嚎。
- 在mysql中只有使用了Innodb引擎的數(shù)據(jù)庫或表才支持事務(wù)
- 事務(wù)處理可以用來維護(hù)數(shù)據(jù)庫的完整性窒所，保證成批的語句要么執(zhí)行要么全部不執(zhí)行岛抄。
ACID 數(shù)據(jù)庫事務(wù)必須滿足的四個條件：
- 原子性（Atomicity）：整個事務(wù)要么全部完成要么全部不完成。執(zhí)行事務(wù)的過程如果發(fā)生錯誤险毁，會- 回滾到事務(wù)開始之前，當(dāng)做沒發(fā)生過這個事務(wù)。
- 一致性（Consistency）：事務(wù)結(jié)束后系統(tǒng)的狀態(tài)是一致的韧拒，不會出現(xiàn)數(shù)據(jù)不一致的情況
- 隔離性（Isolation）：防止多個事務(wù)并發(fā)執(zhí)行由于交叉執(zhí)行導(dǎo)致的數(shù)據(jù)的不一致。事務(wù)隔離級別
- 持久性（Durability）：事務(wù)完成后的改動都會被持久化十性，即使系統(tǒng)故障也不會丟失叛溢，通過日志和同步備份可以恢復(fù)數(shù)據(jù)。
多個事務(wù)訪問同一個數(shù)據(jù)出現(xiàn)的問題
- 臟讀：事務(wù)A修改完數(shù)據(jù)后回滾劲适，期間這個修改了的數(shù)據(jù)(臟數(shù)據(jù))被其他事務(wù)讀取到楷掉。（AupdateBqueryArollback）
- 不可重復(fù)讀：事務(wù)A修改完數(shù)據(jù)后提交。事務(wù)B在A修改前讀取該數(shù)據(jù)霞势，A提交后又一次讀取該數(shù)據(jù)烹植，結(jié)果與之前的不一樣（重復(fù)讀數(shù)據(jù)不一致）（BqueryAcommitBquery）
- 幻讀：事務(wù)A兩次查詢，B在兩次查詢中間插入了數(shù)據(jù)并提交支示。所以A第二次查詢結(jié)果多了一些東西刊橘。（AselectBinsertAselect）
- 第一類丟失更新，事務(wù)A回滾把已經(jīng)提交的事務(wù)B的更新數(shù)據(jù)覆蓋了颂鸿。（ArollbaskBcommit）
- 第二類丟失更新促绵，事務(wù)A覆蓋了事務(wù)B的數(shù)據(jù)，事務(wù)B的操作丟失嘴纺。（ABcommit）
自動鎖機(jī)制败晴，事務(wù)隔離級別
| 隔離級別 | 臟讀 | 不可重復(fù)讀 | 幻讀 | 第一類丟失更新 | 第二類丟失更新 |
|---|---|---|---|---|---|
| READ UNCOMMITED 讀未提交 | √ | √ | √ | X | √ |
| READ COMMITTED 讀提交 | X | √ | √ | X | √ |
| REPEATABLE READ 可重復(fù)讀 | X | X | √ | X | X |
| SERIALIZABLE 串行化 | X | X | X | X | X |
事務(wù)隔離級別越高，并發(fā)性就越差栽渴。
回滾不會覆蓋其他事務(wù)更新了的數(shù)據(jù)
mysql默認(rèn)可重復(fù)讀
數(shù)據(jù)庫實(shí)現(xiàn)事務(wù)隔離的方式尖坤，一般分為兩種:
- 一種是在讀取數(shù)據(jù)前，對其加鎖闲擦，阻止其他事務(wù)對數(shù)據(jù)進(jìn)行修改慢味。
- 另一種是不用加任何鎖场梆，通過一定機(jī)制生成一個數(shù)據(jù)請求時間點(diǎn)的一致性數(shù)據(jù)快照，并用這個快照來提供一定級別（語句級或事務(wù)級）的一致性讀取纯路。從用戶的角度來看或油，好像是數(shù)據(jù)庫可以提供統(tǒng)一數(shù)據(jù)的多個版本，因此這種技術(shù)叫做數(shù)據(jù)多版本并發(fā)控制（MultiVersion Concurrency Control驰唬，簡稱MVCC或MCC）顶岸，也經(jīng)常稱為多版本數(shù)據(jù)庫。
事務(wù)控制語句
mysql默認(rèn)事務(wù)自動提交
begin/start transaction/set autocommit=0：禁止當(dāng)前會話的自動提交叫编。
set transaction 設(shè)置事務(wù)的隔離級別
JDBC操作
加載驅(qū)動
Class.forName("com.mysql.jdbc.Driver");創(chuàng)建連接
Connection conn = DriverManager.getConnection(url,username,password);創(chuàng)建語句
PrepareStatement ps = (PrepareStatement) conn.getPrepareStatement();
ps.setString();
...執(zhí)行語句
ResultSet rs = ps.executeQuery(sql);處理結(jié)果
int cols = rs.getMetaData.getColumnCount();
while(rs.next()){
for(int i=0;i<cols;i++){
rs.getString(i);
}
}關(guān)閉連接
rs.close()
ps.close()
MySQL索引背后的數(shù)據(jù)結(jié)構(gòu)及算法原理
MySQL索引類型
主鍵將自動創(chuàng)建主鍵索引
innodb 不支持全文索引
主鍵索引和唯一索引的區(qū)別辖佣？
mysql什么時候哪些語句會使用到索引？
BTree索引搓逾、哈希索引卷谈？？
聚集索引恃逻、非聚集索引雏搂、覆蓋索引？寇损？
- 普通索引，沒有任何限制
- 唯一索引
- 索引列的值必須唯一裳食；
- 允許有空值
- 組合索引的話列值組合必須唯一
- 主鍵索引
- 不允許空值
- 主鍵將自動創(chuàng)建索引
primary key('id')
- 組合索引
- 多個字段
- 在查詢條件中使用了索引的第一個字段矛市，索引才會被使用。
- 最左前綴集合
- 全文索引
- 更像是搜索引擎
- 必須是myisam引擎
- match() against('') 語法
缺點(diǎn)：
- 因?yàn)閷Ρ磉M(jìn)行insert诲祸、update和delete更新的時候不進(jìn)要保存數(shù)據(jù)浊吏，還要更新索引文件，所以雖然索引提高了查詢速度救氯，但是會降低表的更新速度找田。
- 建立索引會占用磁盤空間
注意：
- 索引不包含null值的行？着憨？
- 使用短索引墩衙，指定前綴長度，提高查詢速度甲抖，節(jié)省磁盤空間和I/O操作漆改。
- 多個列排序最好創(chuàng)建復(fù)合索引？准谚？
- like 只有“a%”可以使用索引
- 在列上進(jìn)行運(yùn)算會導(dǎo)致索引失效挫剑，那應(yīng)該怎么做？柱衔？
- 不用not in樊破、 <>愉棱？？哲戚？
索引本質(zhì)是數(shù)據(jù)結(jié)構(gòu)（以某種方式指向數(shù)據(jù)）羽氮，使查詢的復(fù)雜度更小。
如二叉查找樹惫恼，每個結(jié)點(diǎn)包含索引鍵值對和指向?qū)?yīng)數(shù)據(jù)記錄物理地址的指針档押。
實(shí)際的數(shù)據(jù)庫使用B樹。
BTree（B-Tree）
度d（d>1）祈纯，約束結(jié)點(diǎn)內(nèi)值的數(shù)量
高度h令宿，所有葉子結(jié)點(diǎn)深度都是樹高
每個非葉子結(jié)點(diǎn)有n-1個key和n個指針組成，其中d<=n<=2d
（key與指針相互間隔腕窥，節(jié)點(diǎn)兩端是指針粒没，指針數(shù)在度和2倍度之間）
葉子結(jié)點(diǎn)指針都是null
key從做到右遞增排列
最左指針指向結(jié)點(diǎn)的所有key都小于右邊key
中間指針指向結(jié)點(diǎn)的所有key都大于左邊key、小于右邊key
最右指針指向結(jié)點(diǎn)的所有key都大于左邊key
樹高上限logd ((N+1)/2)
查找節(jié)點(diǎn)O(logd N)
變種B+Tree
指針上限是2d+1（最左是數(shù)據(jù)最右是指針簇爆，最左的數(shù)據(jù)和父節(jié)點(diǎn)的一樣）
非葉子節(jié)點(diǎn)不存值data只存key
葉子節(jié)點(diǎn)不存儲指針癞松，存key和data
比BTree更適合實(shí)現(xiàn)外存儲索引結(jié)構(gòu)
因?yàn)榭梢杂懈蟮某龆?br>
- 主存存取原理
- 磁盤存取原理，需要機(jī)械運(yùn)動入蛆，旋轉(zhuǎn)响蓉、尋道，耗費(fèi)時間大哨毁。
- 局部性原理和磁盤預(yù)讀枫甲，每次磁盤I/O從找到的位置開始預(yù)讀頁（4k）的倍數(shù)的數(shù)據(jù)。
（缺頁扼褪，LRU算法） - 利用磁盤預(yù)讀原理想幻，將一個節(jié)點(diǎn)的大小設(shè)為一個頁，這樣每個節(jié)點(diǎn)只需要一次I/O就可以完全載入话浇。
- 根節(jié)點(diǎn)常駐內(nèi)存脏毯。
- O(h)=O(logd N) 出度越大高度越小,索引性能越好
- 出度取決于節(jié)點(diǎn)大小
d=pagesize/(keysize+datasize+pointsize)
myisam 數(shù)據(jù)文件和文件分開，樹的結(jié)點(diǎn)的data存的是物理地址（非聚集索引）
innodb 樹的結(jié)點(diǎn)存的是數(shù)據(jù)
主鍵索引文件存的數(shù)據(jù)是完整的數(shù)據(jù)（聚集索引（數(shù)據(jù)聚集幔崖？食店？））
輔助索引（二級索引）data存的是主鍵值所以需要兩次索引，通過檢索輔助索引獲得主鍵后再通過檢索主鍵索引來獲得數(shù)據(jù)岖瑰。
- 必須有主鍵叛买，沒有的話也會創(chuàng)建一個隱含字段當(dāng)作主鍵
- 主鍵字段不能太大，否則會使輔助索引過大
- 因?yàn)椴迦胄实吞６员M量用自增字段做主鍵率挣。自增只需要添加在樹后面。插入需要挪數(shù)據(jù)露戒，甚至目標(biāo)頁面可能已經(jīng)被寫回磁盤從緩存里清掉了椒功，得從磁盤上讀回來捶箱，增加了很多開銷。
OPTIMIZE TABLE來重建表并優(yōu)化填充頁面动漾。
不建議建索引
- 表記錄少
- 重復(fù)索引值多
索引失效：
- 條件中有or丁屎，每個列都有索引才能用索引
- like查詢，以%開頭
- 條件中對應(yīng)列的字符串不用引號引起來
- 若mysql估計使用全表掃描要比索引快旱眯，則不使用索引
- 對索引進(jìn)行運(yùn)算導(dǎo)致索引列失效
- 使用內(nèi)部函數(shù)導(dǎo)致索引失效晨川，應(yīng)當(dāng)創(chuàng)建基于函數(shù)的索引
- b樹，is null不會用删豺，is not null 會用共虑。
創(chuàng)建復(fù)合索引（A，B呀页，C）時妈拌，相當(dāng)于創(chuàng)建了（A，B蓬蝶，C）尘分、（A、B）丸氛、（A）這叫最佳左前綴特性培愁。因此創(chuàng)建復(fù)合索引時，應(yīng)將最常用的放在最左邊雪位。
mysiam：
- 默認(rèn)的存儲引擎
- 提供高速存儲和檢索竭钝，
- 以及全文搜索能力
- 表級鎖
- 損壞不能恢復(fù)數(shù)據(jù)
- 不支持事務(wù)
查詢頻繁、做很多count的計算時可使用
innodb：
- 支持事務(wù)和外鍵
- 索引多雹洗，主鍵應(yīng)該盡可能小
- 行級鎖
使用情況：
更新查詢頻繁，多重并發(fā)
要求事務(wù)卧波，可靠性要求高
外鍵約束时肿，只有innodb支持
一般需要事務(wù)支持，有較高的并發(fā)讀取頻率是可選擇innodb
memory：
數(shù)據(jù)存在內(nèi)存
archive：
支持壓縮數(shù)據(jù)
適合存儲大量日志港粱、歷史數(shù)據(jù)
blackhole：
csv：
《高性能mysql第三版》
一些關(guān)于b樹（b+）索引的限制
它只適用于全鍵值螃成、鍵值范圍或鍵前綴查找，其中鍵前綴查找只適用于根據(jù)最左前綴的查找查坪。
- 如果不是按照索引的最左列開始查找寸宏，則無法使用索引。如偿曙，索引為(lastname,firstname,age)氮凝，那么查找firstname為bill的查詢無法用到這個索引，因?yàn)閒irstname不是最左數(shù)據(jù)列望忆。
- 不能跳過索引中的列罩阵。如竿秆，不能用這個索引查lastname和age
- 如果查詢有某個列的范圍查詢，則其右邊所有列都無法使用索引優(yōu)化查找稿壁。如果范圍查詢列值的數(shù)量有限幽钢，那么可以通過使用多個等于條件來代替范圍條件。
以上限制都和索引列的順序有關(guān)傅是。在優(yōu)化性能時可能需要使用相同的列但順序不同的索引來滿足不同類型的查詢需求匪燕。
哈希索引
對于每一行數(shù)據(jù)，存儲引擎都會對所有的索引列計算一個哈希碼喧笔，哈希索引將所有的哈希碼存儲在索引中帽驯，同時在哈希表中保存指向每個數(shù)據(jù)行的指針。
在mysql中溃斋，只有memory引擎顯示支持哈希索引界拦。（非唯一哈希索引）
限制：
- 只包含哈希值和行指針
- 無序，無法用于排序
- 不支持部分索引列匹配查找
- 只支持等值比較查詢
- 哈希沖突
自適應(yīng)哈希索引：當(dāng)innoDB注意到某些索引值被使用得非常頻繁時梗劫，它會在內(nèi)存中基于btree索引再建一個哈希索引享甸。
自定義哈希索引：還是用btree進(jìn)行查找，但是使用哈希值而不是鍵本身進(jìn)行索引查找梳侨。需要在查詢語句中手動指定使用哈希函數(shù)蛉威。例如，需要存儲大量的url走哺，并需要根據(jù)url進(jìn)行搜索查找蚯嫌。如果使用btree來存儲url，存儲的內(nèi)容會很大丙躏。
crc32做哈希函數(shù)择示，不用sha1和md5是因?yàn)樗麄冇嬎愠鰜淼墓Ｖ凳欠浅ｉL的字符串，浪費(fèi)大量空間晒旅。
處理哈希沖突栅盲。在where中包含常量值，以免返回多行結(jié)果废恋。
正確使用索引
- 索引列不能是表達(dá)式的一部分谈秫，也不能是函數(shù)的參數(shù)。應(yīng)該始終將索引列單獨(dú)放在比較符號的一側(cè)鱼鼓。
- 前綴索引拟烫，有時需要索引很長的字符列會讓索引變得大且慢，這時除了模擬哈希索引還可以只索引開始的部分字符迄本，但是這樣會降低索引的選擇性（不重復(fù)的索引值數(shù)量和記錄總數(shù)的比值）硕淑。索引的選擇性越高則查詢效率越高。
可通過計算完整列的選擇性，和前綴的選擇性來相互比較喜颁。 - 多列索引稠氮。
索引合并策略，5.0版本 - 選擇合適的索引順序
可先預(yù)測一下哪個列的選擇性更高半开，select sum(x1=2),sum(x2=5) from xx
然后將選擇性高的列放在前面
但是如果這樣子根據(jù)具體的值來優(yōu)化會對其他的查詢不公平 - 聚簇索引隔披，行數(shù)據(jù)都在葉子結(jié)點(diǎn)中，在innodb中按主鍵順序插入行
- 覆蓋索引寂拆，一個索引包含所有需要查詢的字段的值奢米，就稱之為“覆蓋索引”
優(yōu)化
- in代替范圍查詢
- 排序和分頁，用延遲關(guān)聯(lián)纠永，先用索引拿到主鍵鬓长，再通過主鍵去表里取行數(shù)據(jù)
總結(jié)
- 單行訪問很慢，如果服務(wù)器從存儲（硬盤）中讀取一個數(shù)據(jù)塊（頁）只是為了獲取其中一行尝江，那么就浪費(fèi)了很多資源涉波。最好讀取出來的塊中能包含盡可能多所需要的行。使用索引可以創(chuàng)建位置引用以提升效率炭序。
- 按順序訪問范圍數(shù)據(jù)是很快的啤覆，因?yàn)轫樞騣/o不需要多次磁盤尋道，所以比隨機(jī)i/o要快得多
- 索引覆蓋查詢是很快的惭聂，因?yàn)椴恍枰倩乇聿檎倚写吧＿@避免了大量的單行訪問。
