目錄

1、索引

1.1、概念

1.2、索引的作用

1.3、 索引的缺點

1.4、數(shù)據(jù)庫中實現(xiàn)索引的數(shù)據(jù)結構

1.4.1、B樹/B-樹

1.4.2、B+樹?

?1.4.3、回表

1.5、使用場景

1.6、索引的使用?

1.6.1、查看索引

1.6.2、創(chuàng)建索引?

1.6.3、 刪除索引

1.7、索引的分類

2、事務

2.1、為什么使用事務

2.2、事務的概念

2.3、回滾機制（rollback）?

?2.4、事務的使用

2.5、事務的特性

?2.6、事務的隔離級別

?2.6.1、MySQL中的四種事務隔離級別

2.7、臟讀問題?

2.8、不可重復度問題

2.9、幻讀問題

1、索引

索引的創(chuàng)建會占用一定的物理空間，索引值是存放在磁盤空間中的，但是為了減少IO訪問次數(shù)，提高程序的效率，在訪問數(shù)據(jù)的時候他會先從磁盤空間中預讀一定長度的索引數(shù)據(jù)放在緩存中。

1.1、概念

在MySQL中，索引（index）也叫做鍵（key）

數(shù)據(jù)庫的索引就是為了提高數(shù)據(jù)的查詢速率的方法。數(shù)據(jù)庫的索引類似于書籍的目錄。在書籍中，目錄允許用戶不必翻閱整本書就能迅速找到所需要的信息。在數(shù)據(jù)庫中同理，索引也允許數(shù)據(jù)庫程序迅速的找到表中的數(shù)據(jù)，而不必掃描整個數(shù)據(jù)庫。

1.2、索引的作用

• 我們的數(shù)據(jù)庫的數(shù)據(jù)是存儲在硬盤中的，而查詢數(shù)據(jù)的時候要從硬盤中讀取數(shù)據(jù)。而在硬盤中讀取數(shù)據(jù)相比于在內存中讀取數(shù)據(jù)是慢了不止一個數(shù)量級。
• 如果我們通過遍歷在硬盤空間中查找數(shù)據(jù)，每查找一次數(shù)據(jù)都需要程序與硬盤進行一次交互（數(shù)據(jù)的交互，也就是硬盤的IO），而一段程序中最拖慢程序速度的就是硬盤的IO，可想而知程序的速度會非常慢。
• 但是我們通過索引的方式來查找數(shù)據(jù)，我們將索引值存在內存空間中，通過查找內存空間的值，來找到索引所對應的表中的完整數(shù)據(jù)。這樣就減少了程序與硬盤空間的交互（減少了硬盤的IO次數(shù)）。這樣就提高了程序的效率。這就是我們常說的以空間換時間。

1.3、 索引的缺點

• 創(chuàng)建索引和維護索引需要耗費時間，這種時間隨著數(shù)據(jù)量的增加而增加。
• 需要付出額外的空間代價來保存索引數(shù)據(jù)
• 當對表中的數(shù)據(jù)進行增加、刪除和修改的時候，索引也會跟著改變，降低了數(shù)據(jù)的維護速度，也可以理解為拖慢了增加、刪除和修改數(shù)據(jù)的速度。

1.4、數(shù)據(jù)庫中實現(xiàn)索引的數(shù)據(jù)結構

• 我們在數(shù)據(jù)結構中學習過二叉搜索樹、紅黑樹、avl樹和哈希表這樣的數(shù)據(jù)結構，二叉搜索樹在平衡的情況下，可以實現(xiàn)快速查找數(shù)據(jù)。哈希表可以更快的查找數(shù)據(jù)。
• 哈希表查找的時間復雜度為O（1），二叉搜索樹在樹平衡的時候它的時間復雜度為書的高度。但是在最壞的情況下，它的時間復雜度為O(N).

上述說到的這些數(shù)據(jù)結構都不能作為數(shù)據(jù)庫中實現(xiàn)索引的數(shù)據(jù)結構。

• 二叉搜索樹、avl樹、紅黑樹。他們都是單個存儲的，將一個完整的數(shù)據(jù)存入結點中，而數(shù)據(jù)庫中完整的數(shù)據(jù)就是一張表中的一條記錄。這樣每個結點占用的空間就比較大，所以不能將這些結點存在內存中，就需要存入硬盤中，而每查找一個數(shù)據(jù)就需要一個結點一個結點的比對，每比對一次結點中的數(shù)據(jù)，就相當于在硬盤中進行了程序與硬盤的交互（也就是硬盤的IO）?。這樣程序的效率就非常低。
• 而哈希表是一個非?？焖俚牟檎覕?shù)據(jù)的數(shù)據(jù)結構，但是它只能進行值相等的比較，但是像大于，小于這樣的范圍比較，還有模糊匹配，他都是不能實現(xiàn)的。

所以數(shù)據(jù)庫使用的是B+樹

1.4.1、B樹/B-樹

我們在學習B+樹的時候，先來了解一下它的前身B樹。

B樹，也叫B-樹，此處的（-），不是減號，而是一個連接符。

每個結點中存放一定量的數(shù)據(jù)表中的每行數(shù)據(jù)（id,name,score,....），當結點中達到了規(guī)定的元素個數(shù)是，采取調整，B樹就可以解決樹過高的問題也就是IO訪問次數(shù)過多的問題。但是這個結構MySQL中沒有采用，而是使用B+樹作為數(shù)據(jù)庫數(shù)據(jù)存儲的一種結構。?

1.4.2、B+樹?

?B+樹的特點：?

1. 一個結點，可以存儲N個key，同時N個key劃分出了N個區(qū)間。
2. 每個結點中的key的值，都會在子節(jié)點中存在，同時該key是子節(jié)點的最大值
3. B+樹的葉子節(jié)點，是首位相連，類似于一個鏈表
4. 由于葉子節(jié)點是一個完整的數(shù)據(jù)集合，所以只在葉子節(jié)點這里存儲數(shù)據(jù)表中的每一行的數(shù)據(jù)，而非葉子結點只存儲key值本身即可

B+樹的優(yōu)勢：

1. 當前一個結點保存更多的key，最終樹的高度更矮。查詢的時候減少了IO訪問（這里指硬盤訪問次數(shù)）次數(shù)（和B樹相同）
2. 所有的查詢最終都會落在葉子節(jié)點上。（查詢任何一個數(shù)據(jù)，經(jīng)過的IO訪問次數(shù)是一樣的，因為葉子節(jié)點保存完整的數(shù)據(jù)） 。所以B+樹的IO訪問次數(shù)是更穩(wěn)定的。這樣就可以對程序的執(zhí)行效率有一個跟穩(wěn)定的評估。
3. B+樹的所有的葉子節(jié)點，構成了鏈表，此時比較方便進行范圍查詢。（比如查詢學號5~11的同學）
4. 由于數(shù)據(jù)都在葉子結點上，非葉子節(jié)點只存儲key，導致非葉子結點占用空間是比較小的，這些非葉子結點就可能在內存中緩存（或者緩存一部分）。進一步減少了IO訪問次數(shù)。

?1.4.3、回表

????上述B+ 樹這個結構，我們默認id是表的主鍵了。如果一個表中存在多個索引查詢的時候該怎樣查呢？針對id 有主鍵索引，name又存在一個索引。

---

???表的數(shù)據(jù)還是按照id為主鍵，構建出B+樹，通過葉子節(jié)點組織所有的數(shù)據(jù)行，其次，針對name這一列，會構建另外一個B+樹。但是這個B+樹的葉子結點就不再存儲這一行的完整數(shù)據(jù)，而是存主鍵id。此時如果根據(jù)name來查詢，查到葉子結點得到的只是主鍵id，還需要通過主鍵id去主鍵的B+樹里再查一次。所以這里就需要查兩次B+樹了。這個操作就叫做回表

1.5、使用場景

要考慮對數(shù)據(jù)庫表的某列或某幾列創(chuàng)建索引，需要考慮以下幾點

• 數(shù)據(jù)量較大，且經(jīng)常對這些列進行條件查詢
• 該數(shù)據(jù)看表的插入操作，及對這些列的修改操作頻率較低
• 索引會占用額外的磁盤空間?

滿足以上條件時，考慮對表中的這些字段創(chuàng)建索引，以提高查詢效率。 反之，如果非條件查詢列，或經(jīng)常做插入、修改操作，或磁盤空間不足時，不考慮創(chuàng)建索引。

1.6、索引的使用?

1.6.1、查看索引

格式：

show index from 表名；

?查詢學生表中額索引，學生表中并沒有手動創(chuàng)建索引，我們再設置主鍵的時候，自帶的索引。

?

1.6.2、創(chuàng)建索引?

對于非主鍵，非唯一約束、非外鍵的字段，可以創(chuàng)建普通索引（列名/字段名可以有多個，用逗號隔開，一個索引包含多個字段就叫做組合索引）

格式

create index 索引名 on 表名(列名)；
create index 索引名 on 表名(列名1，列名2...);

?案例：我們基于name這一列創(chuàng)建一個普通索引。(相當于student表有基于name這一列創(chuàng)建出一個目錄。)

create index  index_student_name on student(name);

????創(chuàng)建索引時因注意的事項：

• 當一個表已經(jīng)創(chuàng)建好了，并且里面存在大量數(shù)據(jù)，這時候再根據(jù)表的某一列創(chuàng)建索引，這個操作有可能就很危險。表中的數(shù)據(jù)很大，建立索引的開銷也就很大?？梢韵胂笠幌掠幸槐竞芎竦臅?，現(xiàn)在讓你手動寫一個目錄出來，這個工作量就會很大。
• 好的做法就是再創(chuàng)建表的時候，就設定好索引。
• 如果表中存在很多數(shù)據(jù)了，索引就不要動了。

1.6.3、 刪除索引

格式

drop index 索引名 on 表名；

?案例：刪除student表中的index_student_name索引。

??? 注意：刪除索引也存在風險。

1.7、索引的分類

MySQL目前的主要索引類型有：普通索引，唯一索引，主鍵索引，組合索引，全文索引，聚簇索引，非聚簇索引等。

1. 普通索引：就是在數(shù)據(jù)表的字段設立索引的時候，不需要添加任何額外的限制條件（唯一，非空等的限制），該類型的索引可以創(chuàng)建再任何數(shù)據(jù)類型的字段中。
2. 唯一索引：在數(shù)據(jù)表中設立索引的時候，限制索引修飾的字段必須具有唯一性，根據(jù)這個唯一索引可以比普通索引更快的查詢到數(shù)據(jù)庫中的某條記錄。
3. 主鍵索引：當創(chuàng)建表的時設立了主鍵約束，這張表也就存在了索引。也就是主鍵索引，也叫做聚簇索引。
4. 全文索引：主要是對字符串類型建立的索引，基于分詞的索引，主要是基于char，varchar,text類型的字段上，以便于能夠更快的查詢到數(shù)據(jù)量較大的字符串類型字段。
5. 聚簇索引：聚簇索引一般指的是主鍵索引（表中存在主鍵索引的情況下）。
6. 非聚簇索引：非聚簇索引的葉子節(jié)點仍然是索引值。如上邊說到的存在主鍵索引（id）的情況下，又創(chuàng)建了一個普通索引（針對name），我們根據(jù)name查找數(shù)據(jù)的時候，會在name對應的B+樹的葉子節(jié)點處找到索引值，再去id的B+樹中找數(shù)據(jù)。

????注意：如果一張表中沒有主鍵，MySQL會每一行記錄生成一個唯一的字段，使用這個字段作為索引。

2、事務

2.1、為什么使用事務

1??現(xiàn)在轉賬的操作已經(jīng)很普遍了，這里有一個賬戶表（account表），第一個用戶又1000元，第二個用戶沒有錢。

?2??現(xiàn)在第一個用戶個第二個用戶轉500元。這時候兩步操作實現(xiàn)這個過程。

通過給2的賬戶加500，給1的賬戶減500實現(xiàn)。

update account set balance = balance - 500 where id = 1;
update account set balance = balance + 500 where id = 2;

假設，在執(zhí)行轉賬這個操作的時候，第一步操作執(zhí)行完成之后，數(shù)據(jù)庫奔潰了或者主機宕機了。此時只執(zhí)行了第一句代碼，第二句代碼還沒有被執(zhí)行。呈現(xiàn)的一個情況就是1用戶的錢扣了，但是2的錢還沒有到賬。顯然這種操作是不現(xiàn)實的。

要讓這種情況不發(fā)生，就需要使用事務來控制，保證讓這兩個SQL要么全部執(zhí)行，要么執(zhí)行失敗。

2.2、事務的概念

• 本質就是把多個SQL語句打包成一個整體，要么全部執(zhí)行成功，要么就一個都不執(zhí)行，不會出現(xiàn)執(zhí)行一半這樣的中間狀態(tài)。

• 在不同的環(huán)境中，都可以有事務。對應在數(shù)據(jù)庫中，就是數(shù)據(jù)庫事務。

2.3、回滾機制（rollback）?

上面說到的，將多個SQL語句打包成一個整體，有么執(zhí)行成功，要么不執(zhí)行。這里的"不執(zhí)行"并不是真的不執(zhí)行，而是執(zhí)行了，中間出了錯誤。選擇了恢復現(xiàn)場，把數(shù)據(jù)還原到未執(zhí)行之前的狀態(tài)了。就像我們使用的Ctrl+Z?。我們把這個恢復數(shù)據(jù)的操作，稱為"回滾"（rollback）.事務的原子性就是通過回滾這個機制來保證的。

????這里又存在一個問題，進行回滾的時候，咋知道回滾是要恢復到什么狀態(tài)呢？

數(shù)據(jù)庫中存在日志文件，他是專門用來記錄事務的操作步驟的。這個日志文件是保存在硬盤空間的

?2.4、事務的使用

三步操作

1. 開啟事務：start transaction;
2. 執(zhí)行多條SQL語句
3. 回滾或提交rollback/commit;

rollback即是全部失敗，commit即是全部成功。

案例：

start transaction;//開啟事務
//執(zhí)行多條SQL語句
update account set balance = balance - 500 where id = 1;
update account set balance = balance + 500 where id = 2;
commit;//提交事務

2.5、事務的特性

1、原子性（Atomicity）【核心特點】

將多個SQL語句打包成一個整體，這樣的操作就叫做事務的原子性。表示事務的不可分割的最小單位。事務的出現(xiàn)就是為了解決原子性的問題。

2.一致性

事務執(zhí)行之前于執(zhí)行之后，要保持正確的結果。就像銀行轉賬一樣，1用戶少了五百，2用戶相應的是多了500，而不能是多了5000.

3、持久性

事務修改的內容是寫到硬盤上的，持久存在的。重啟服務器也不會丟失。

4、隔離性

隔離性是為了解決"并發(fā)"執(zhí)行事務，所引起的問題。確保多個事務執(zhí)行的過程中不互相干擾。

?2.6、事務的隔離級別

MySQL服務器可以支持多個客戶端的訪問，如果多個客戶端對多個數(shù)據(jù)表進行數(shù)據(jù)操作，不會產生問題，但是多個客戶端對一個數(shù)據(jù)表中的數(shù)據(jù)進行操作。那么就會產生很多問題。這里就需要不同的隔離級別對可能出現(xiàn)的問題進行解決。

隔離級別越低，可以支持同時訪問同一個數(shù)據(jù)表的客戶端就越多，數(shù)據(jù)的安全性就越低；如果隔離級別越高，那么可以支持同時訪問同一個數(shù)據(jù)表的客戶端就越少，性能變低，數(shù)據(jù)安全性變高。

?2.6.1、MySQL中的四種事務隔離級別

1. read uncommitted (讀未提交)：沒有進行任何限制，并發(fā)高（效率最高），隔離性最低（準確性最低）（臟讀，不可重復讀、幻讀的問題都有可能出現(xiàn)）
2. read committed(讀提交)：給寫加鎖，并發(fā)程度降低，隔離性提高了（不可重復讀和幻讀可能出現(xiàn)）
3. repeatable read (可重復讀)：給寫和讀都加鎖，并發(fā)程度有降低了，隔離性有提高了（幻讀的問題可能出現(xiàn)）
4. serializable(串行化)：并發(fā)程度最低，隔離性最高。

2.7、臟讀問題?

一個事務A正在對數(shù)據(jù)進行修改的過程中，結果還沒有提交之前，另外一個事務B，也對同一個數(shù)據(jù)進行了讀取。讀取的數(shù)據(jù)并不是提交后的數(shù)據(jù)。此時B的操作就成為"臟讀"，讀到的數(shù)據(jù)也稱為"臟數(shù)據(jù)"。這里的臟表示的意思是無效。

為什么說是無效的數(shù)據(jù)，事務A在對數(shù)據(jù)進行修改的時候，發(fā)生了回滾。那之前修改的數(shù)據(jù)，全都無效了。

???解決臟讀問題：給寫操作加鎖?

給寫操作的事務A加上一把鎖，在事務A開始進行寫操作的時候加鎖，事務提交或者回滾的時候釋放鎖，被鎖的事務不能與其他事務共存，寫鎖也叫排他鎖，這樣寫操作與讀操作就不能同時執(zhí)行了。

?

?這個給寫加鎖的操作，就降低了并發(fā)程度（降低了效率），提高了隔離性（提高了數(shù)據(jù)的準確性）。

2.8、不可重復度問題

臟讀問題被解決了，又出現(xiàn)了新的問題。有三個事務A，B，C，事務A讀取數(shù)據(jù)，事務B，C對同一條數(shù)據(jù)進行修改?，F(xiàn)在事務B對這條數(shù)據(jù)進行了修改，并且提交了，在執(zhí)行事務A的時候，對這一條數(shù)據(jù)進行了讀取。得到了數(shù)據(jù)B。然后事務C對數(shù)據(jù)進行了修改，并提交了，現(xiàn)在事務B在執(zhí)行過程中，又需要讀取這條數(shù)據(jù)，現(xiàn)在讀取的數(shù)據(jù)為數(shù)據(jù)C。這時就意味著同一個事務A之內，多次讀取數(shù)據(jù)，讀出來的結果是不相同的，我們預期是一個事務中，多次讀取的結果得是一致的。這就叫做"不可重復讀"。

解決不可重復讀問題：給讀操作加鎖。

在讀的事務也加鎖，但是這個鎖是一把（共享鎖），多個讀鎖可以共存，但是由于寫鎖是排他鎖，所以讀鎖不能與寫鎖共存，也就是說，在加了讀鎖之后，不能進行寫操作。

通過這個讀加鎖，有進一步的降低了事務的并發(fā)處理能力（處理效率也降低），提高了事務的隔離性（數(shù)據(jù)的準確性有提高了）。

2.9、幻讀問題

當前已經(jīng)進行了寫加鎖和讀加鎖。簡單來說就是在寫操作的時候不能進行其他操作，在讀的時候，不能進行寫操作。讀加鎖這時針對一條記錄來說，在讀的時候別的事務不能對這條記錄進行修改，但是可以在這個數(shù)據(jù)表中添加別的記錄。幻讀就是一個事務用一樣的 SQL 多次查詢，結果每次查詢都會發(fā)現(xiàn)查到一些之前沒看到過的數(shù)據(jù)。之前讀到的數(shù)據(jù)在這次讀到的數(shù)據(jù)中沒有發(fā)生變化，但是它的結果集發(fā)生了變化。

??舉個例子：

你一個事務 A，先發(fā)送一條 SQL 語句，里面有一個條件，要查詢一批數(shù)據(jù)出來，如?SELECT * FROM table WHERE id > 10。然后呢，它一開始查詢出來了 10 條數(shù)據(jù)。接著這個時候，別的事務 B往表里插了幾條數(shù)據(jù)，而且事務 B 還提交了，此時多了幾行數(shù)據(jù)。

接著事務 A 此時第二次查詢，再次按照之前的一模一樣的條件執(zhí)行?SELECT * FROM table WHERE id > 10?這條 SQL 語句，由于其他事務插入了幾條數(shù)據(jù)，導致這次它查詢出來了 12 條數(shù)據(jù)

?解決幻讀問題：串行化

?數(shù)據(jù)庫使用"串行化"這樣的方式來解決幻讀。徹底放棄并發(fā)處理事務，一個接一個的串行的處理事務。這樣做并發(fā)的程度是最低的（效率是最慢的），隔離性是最高的（準確性也是最高的）文章來源地址http://www.zghlxwxcb.cn/news/detail-433656.html

到了這里，關于【數(shù)據(jù)庫】索引與事務的文章就介紹完了。如果您還想了解更多內容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關文章，希望大家以后多多支持TOY模板網(wǎng)！