1.索引的本質(zhì)

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

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

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

缺點(diǎn)

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

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

雖然有這些缺點(diǎn),但是他能解決我們的主要矛盾,在軟件開(kāi)發(fā)中會(huì)經(jīng)常遇到這樣的問(wèn)題.一般的都沒(méi)有那個(gè)方法能解決所有問(wèn)題,需要進(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)

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

還有unique,foreign key 的列也會(huì)自動(dòng)創(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)

此時(shí)就有兩個(gè)索引,針對(duì)name新加了一個(gè)索引

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

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)

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

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

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

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

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

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

3.1B樹(shù)

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

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

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

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

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

3.2B+樹(shù)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

4.事務(wù)

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

4.1事物的回滾(rollback)

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

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

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

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

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

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

4.2.1 原子性

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

4.2.2 一致性

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

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

4.2.3 持久性

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

4.2.4 隔離性

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

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

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

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

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

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

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

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

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

這次給''讀操作''加鎖,讀的時(shí)候數(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)行讀取時(shí)，發(fā)現(xiàn)了事務(wù)B 新插入的數(shù)據(jù) 稱為幻讀

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

6.MySQL的隔離級(jí)別

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

6.1 read uncommitted

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

6.2 read committed

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

6.3 repeatable read

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

還存在幻讀問(wèn)題

6.4 serializable

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

看這張圖比較直觀

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

最新評(píng)論