1.索引的本質(zhì)

索引的本質(zhì)就相當(dāng)于"書的目錄",通過目錄就能快速定位到我們需要的某個章節(jié)的位置

索引的主要作用就是為了加快查找的速度

在數(shù)據(jù)庫操作中,查詢的頻率是非常高的,使用索引可以幫助我們快速查找到所需要的信息

缺點(diǎn)

1.數(shù)據(jù)庫索引提高查詢速度的同時也增加了增加刪除修改操作的開銷,進(jìn)行增刪改操作之后,調(diào)整數(shù)據(jù)之后還要修改索引,因此增加了其他開銷,但是這是次要矛盾,主要矛盾是查詢的速度,相比之下還是很值得的

2.不僅如此,索引還提高了空間的開銷,構(gòu)造索引需要額外的硬盤空間來保存

雖然有這些缺點(diǎn),但是他能解決我們的主要矛盾,在軟件開發(fā)中會經(jīng)常遇到這樣的問題.一般的都沒有那個方法能解決所有問題,需要進(jìn)行取舍,解決主要矛盾

2.索引的使用

2.1查看索引

mysql> show index from student3;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student3 |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

如果表里有主鍵,主鍵這列就會自動創(chuàng)建索引

還有unique,foreign key 的列也會自動創(chuàng)建索引

2.2創(chuàng)建索引

mysql> create index  index_name on student3(name);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> show index from student3;
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student3 |          0 | PRIMARY    |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| student3 |          1 | index_name |            1 | name        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

此時就有兩個索引,針對name新加了一個索引

在創(chuàng)建索引的時候,最好是在表創(chuàng)建的時候就把索引創(chuàng)建好,否則,如果這個表的記錄十分多了,再創(chuàng)建索引,就很危險了!!是因?yàn)榇藭r創(chuàng)建索引會花很長的時間,占用了大量的的磁盤IO,此時是無法對數(shù)據(jù)庫進(jìn)行訪問的的,也無法正常使用,那帶來的損失就太大了

2.3刪除索引

mysql> drop index index_name on student3;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show index from student3;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student3 |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

此時只剩一個索引了,和剛剛創(chuàng)建索引相似的是,刪除索引也會有較大的開銷,所以在創(chuàng)建表的時候我們就要規(guī)劃好索引,一旦表里有大量的數(shù)據(jù)了,再進(jìn)行操作就需要慎重考慮了!!

那么創(chuàng)建好了索引,是怎么使用索引的呢?

創(chuàng)建好索引之后,是不需要手動的調(diào)用的,SQL是通過數(shù)據(jù)庫的執(zhí)行引擎來執(zhí)行的,涉及到一些優(yōu)化操作,執(zhí)行引擎會自動評估哪種方案成本最低速度最快,可以使用explain關(guān)鍵字顯示出查詢過程中索引的具體使用情況,結(jié)果分析還是比較復(fù)雜的

3.索引的數(shù)據(jù)結(jié)構(gòu)

MySQL中索引的數(shù)據(jù)結(jié)構(gòu)是什么呢?

索引既然能極大提高搜索的效率,我們肯定能先想到的數(shù)據(jù)結(jié)構(gòu)就是哈希表,哈希表的查詢時間復(fù)雜度是O(1),但是哈希表不適合做數(shù)據(jù)庫的索引,原因在于哈希表只能比較相等,無法進(jìn)行范圍查詢,像<>這樣的操作都不行

3.1B樹

其次,二叉搜索樹查詢元素的時間復(fù)雜度是O(N),相比于哈希表,二叉搜索樹好像可以進(jìn)行范圍查詢了,但是還存在一個問題,當(dāng)元素?cái)?shù)太多時,樹的高度就會比較高,而數(shù)的高度又決定了樹查詢的時候比較的次數(shù),數(shù)據(jù)庫比較的時候需要讀取硬盤,因此更希望書的高度能降低一點(diǎn),那么就考慮使用N叉搜索樹了

N叉搜索樹,每個節(jié)點(diǎn)有很多個值,同時有很多的分叉,降低了樹的高度,減少了比較的次數(shù)

一種典型的實(shí)現(xiàn)N叉搜索樹的方式就是B樹

我們看一下B樹的結(jié)構(gòu)

這種結(jié)構(gòu)降低了樹的高度,沒有減少比較次數(shù)(但是在一個節(jié)點(diǎn)上比較多次了),減少了對硬盤的讀寫次數(shù),節(jié)點(diǎn)都是保存在硬盤上的,能一定程度的解決問題,適合做索引

3.2B+樹

還有種更適合做索引的數(shù)據(jù)結(jié)構(gòu),就是B+樹

B+樹的特點(diǎn):

1.B+樹也是一個N叉樹,增加了新的特點(diǎn),每個節(jié)點(diǎn)上包含N個Key,N個Key劃分出N個區(qū)間,每個區(qū)間的最后一個key就是最大值

2.父元素的Key會在子元素中出現(xiàn)并且為最大值,重復(fù)出現(xiàn)導(dǎo)致了,葉子節(jié)點(diǎn)就包含了所有數(shù)據(jù)的全集!

那么非葉子結(jié)點(diǎn)的所有元素都在葉子節(jié)點(diǎn)中體現(xiàn)

3.葉子節(jié)點(diǎn)用類似于鏈表的形式相連起來,構(gòu)成了B+樹

B+樹這個數(shù)據(jù)結(jié)構(gòu)做索引好處太明顯了

1.既有B樹高度比較低的特點(diǎn),又更適合范圍查詢,比如查找>6且<15的元素,結(jié)果集非常容易取得,效率很高

2.對于所有的查詢,都要落在葉子節(jié)點(diǎn)上,中間的比較次數(shù)是差不多的,查詢操作比較均衡

對B樹來說,在根節(jié)點(diǎn)或者深度不深的元素查詢快,別的地方查詢慢,不均衡,B+樹都是一樣的,都落在葉子節(jié)點(diǎn)上了

3.由于所有的Key都會在葉子節(jié)點(diǎn)中出現(xiàn),因此非葉子節(jié)點(diǎn)不用存表的真實(shí)記錄,只要把說有的數(shù)據(jù)行放在葉子節(jié)點(diǎn)上即可,非葉子節(jié)點(diǎn)只用存索引列的值,比如id這些,非葉子節(jié)點(diǎn)占用的空間就很小了,有可能在內(nèi)存中放進(jìn)去緩存了,更進(jìn)一步降低了硬盤IO,提高了查詢的速度

綜上,B+樹是非常適合作為索引的數(shù)據(jù)結(jié)構(gòu)的

有的表不只是有主鍵索引,還有別的非主鍵列也有索引,此時會構(gòu)造另一個B+樹,非葉子節(jié)點(diǎn)里面存儲這一列的Key,到了葉子節(jié)點(diǎn)這一層不再存儲完整的數(shù)據(jù)行了,而是存儲主鍵索引的id,那么使用主鍵索引查詢時只用查一次B+樹就好了,使用非主鍵列索引要先查一遍另外構(gòu)造的B+樹,然后查一次主鍵列的B+樹(這個操作稱為回表操作)

當(dāng)前B+樹這個結(jié)構(gòu)適用于MySQL的InnoDB這個數(shù)據(jù)引擎,不同的數(shù)據(jù)庫,不同的引擎存儲數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)還是有差異的

4.事務(wù)

事務(wù)指邏輯上的一組操作，組成這組操作的各個單元，要么全部成功，要么全部失敗。在不同的環(huán)境中，都可以有事務(wù)。對應(yīng)在數(shù)據(jù)庫中，就是數(shù)據(jù)庫事務(wù)。

4.1事物的回滾(rollback)

當(dāng)一個事務(wù)在執(zhí)行時,執(zhí)行中間出錯了,就讓它恢復(fù)成原來的樣子

涉及到的操作就是回滾,具體實(shí)現(xiàn)是把執(zhí)行過的操作逆向恢復(fù)回去

數(shù)據(jù)庫會把執(zhí)行的每個操作都記錄下來,如果某個操作出錯了,就會把事務(wù)中之前的操作進(jìn)行回滾,根據(jù)之前的操作,進(jìn)行逆操作(前面插入回滾就是刪除之前插入的)

有了這個操作,那么刪表刪庫是不是就不危險了呢?反正可以回滾么,事實(shí)當(dāng)然不是這樣的,回滾的操作是有很大開銷的,可以保執(zhí)行的操作,但也不能無限保存,最多就是保存正在執(zhí)行的事務(wù),當(dāng)數(shù)據(jù)量特別大時,更不可能保存每個數(shù)據(jù)如何得到,因此刪表刪庫仍然是很危險的操作!!

4.2事務(wù)的四大特性(ACID)

事務(wù)的四大特性主要是：原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）、持久性（Durability）

4.2.1 原子性

原子性是指事務(wù)是一個不可分割的工作單位，事務(wù)中的操作要么全部成功，要么全部失敗。比如在同一個事務(wù)中的SQL語句，要么全部執(zhí)行成功，要么全部執(zhí)行失敗

4.2.2 一致性

事務(wù)必須使數(shù)據(jù)庫從一個一致性狀態(tài)變換到另外一個一致性狀態(tài),事物的執(zhí)行前后數(shù)據(jù)是合法的

比如銀行轉(zhuǎn)賬時,A給B轉(zhuǎn)了100,A轉(zhuǎn)出100,B卻沒有收到100,這時就出現(xiàn)了數(shù)據(jù)不合法,沒有達(dá)到一致性

4.2.3 持久性

持久性是指一個事務(wù)一旦被提交，它對數(shù)據(jù)庫中數(shù)據(jù)的改變就是永久性的，接下來即使數(shù)據(jù)庫發(fā)生故障也不應(yīng)該對其有任何影響,保證事務(wù)對數(shù)據(jù)庫的改變是生效的

4.2.4 隔離性

一個數(shù)據(jù)庫服務(wù)器同時執(zhí)行多個事務(wù)的時候,事物之間的相互影響的程度

隔離性越高,事務(wù)之間并發(fā)程度越低,執(zhí)行效率慢,但是數(shù)據(jù)準(zhǔn)確性高,像銀行轉(zhuǎn)賬.....

隔離性越低,事務(wù)之間并發(fā)程度越高,執(zhí)行效率快,但是數(shù)據(jù)準(zhǔn)確性低,像點(diǎn)贊數(shù).....

5.并發(fā)引起的問題

5.1 "讀臟數(shù)據(jù)"

當(dāng)一個事務(wù)修改某個數(shù)據(jù)后，另一事務(wù)對該數(shù)據(jù)進(jìn)行了讀取，由于某種原因前一事務(wù)撤銷了對數(shù)據(jù)的修改（即將修改過的數(shù)據(jù)恢復(fù)原值），那么后一事務(wù)讀到的數(shù)據(jù)與數(shù)據(jù)庫中的數(shù)據(jù)不一致，這稱之為讀臟數(shù)據(jù)

為了解決這個問題,要降低并發(fā)性,提高隔離性,具體操作就是給''寫操作''加鎖,寫的時候不能被讀取,降低了一定的效率,但是提高了數(shù)據(jù)的準(zhǔn)確性

5.2 "不可重復(fù)讀"

當(dāng)一個事務(wù)讀取某個數(shù)據(jù)后，另一事務(wù)執(zhí)行了對該數(shù)據(jù)的更新，當(dāng)前事務(wù)再次讀取該數(shù)據(jù)（希望與第一次讀取的是相同的值）時，得到的數(shù)據(jù)與前一次的不一樣，這是由于第一次讀取數(shù)據(jù)后，事務(wù)B對其做了修改，導(dǎo)致再次讀取數(shù)據(jù)時與第一次讀取的數(shù)據(jù)不相同

這次給''讀操作''加鎖,讀的時候數(shù)據(jù)不能被修改,并發(fā)程度進(jìn)一步降低,隔離性進(jìn)一步增加,運(yùn)行速度變慢,數(shù)據(jù)準(zhǔn)確性進(jìn)一步提高了

5.3 "幻讀"

事務(wù)A 按照一定條件進(jìn)行數(shù)據(jù)讀取， 期間事務(wù)B 插入了相同搜索條件的新數(shù)據(jù)，事務(wù)A再次按照原先條件進(jìn)行讀取時，發(fā)現(xiàn)了事務(wù)B 新插入的數(shù)據(jù) 稱為幻讀

為了解決這個問題,需要徹底舍棄并發(fā),進(jìn)行串行化操作,在讀的時候不進(jìn)行其他的操作

6.MySQL的隔離級別

MySQl為了控制并發(fā)程度的高低,引入了四個隔離級別,通過修改配置文件就可以改變隔離級別

6.1 read uncommitted

不做任何處理,事務(wù)間隨意并發(fā),當(dāng)然上面的三個問題都存在,隔離性最低,并發(fā)程度最高

6.2 read committed

對寫操作加鎖,解決了讀臟數(shù)據(jù)問題,還存在另外兩個問題

6.3 repeatable read

對讀寫操作加鎖,解決了讀臟數(shù)據(jù)問題,不可重復(fù)讀問題

還存在幻讀問題

6.4 serializable

嚴(yán)格串行化,解決了三個由并發(fā)引起的問題,并發(fā)程度最低,隔離性是最高的

看這張圖比較直觀

到此這篇關(guān)于MySQL索引與事務(wù)定義到使用詳解的文章就介紹到這了,更多相關(guān)MySQL索引與事務(wù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論