MySQL索引與事務定義到使用詳解
1.索引的本質
索引的本質就相當於"書的目錄",通過目錄就能快速定位到我們需要的某個章節的位置
索引的主要作用就是為瞭加快查找的速度
在數據庫操作中,查詢的頻率是非常高的,使用索引可以幫助我們快速查找到所需要的信息
缺點
1.數據庫索引提高查詢速度的同時也增加瞭增加刪除修改操作的開銷,進行增刪改操作之後,調整數據之後還要修改索引,因此增加瞭其他開銷,但是這是次要矛盾,主要矛盾是查詢的速度,相比之下還是很值得的
2.不僅如此,索引還提高瞭空間的開銷,構造索引需要額外的硬盤空間來保存
雖然有這些缺點,但是他能解決我們的主要矛盾,在軟件開發中會經常遇到這樣的問題.一般的都沒有那個方法能解決所有問題,需要進行取舍,解決主要矛盾
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)
如果表裡有主鍵,主鍵這列就會自動創建索引
還有unique,foreign key 的列也會自動創建索引
2.2創建索引
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新加瞭一個索引
在創建索引的時候,最好是在表創建的時候就把索引創建好,否則,如果這個表的記錄十分多瞭,再創建索引,就很危險瞭!!是因為此時創建索引會花很長的時間,占用瞭大量的的磁盤IO,此時是無法對數據庫進行訪問的的,也無法正常使用,那帶來的損失就太大瞭
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)
此時隻剩一個索引瞭,和剛剛創建索引相似的是,刪除索引也會有較大的開銷,所以在創建表的時候我們就要規劃好索引,一旦表裡有大量的數據瞭,再進行操作就需要慎重考慮瞭!!
那麼創建好瞭索引,是怎麼使用索引的呢?
創建好索引之後,是不需要手動的調用的,SQL是通過數據庫的執行引擎來執行的,涉及到一些優化操作,執行引擎會自動評估哪種方案成本最低速度最快,可以使用explain關鍵字顯示出查詢過程中索引的具體使用情況,結果分析還是比較復雜的
3.索引的數據結構
MySQL中索引的數據結構是什麼呢?
索引既然能極大提高搜索的效率,我們肯定能先想到的數據結構就是哈希表,哈希表的查詢時間復雜度是O(1),但是哈希表不適合做數據庫的索引,原因在於哈希表隻能比較相等,無法進行范圍查詢,像<>這樣的操作都不行
3.1B樹
其次,二叉搜索樹查詢元素的時間復雜度是O(N),相比於哈希表,二叉搜索樹好像可以進行范圍查詢瞭,但是還存在一個問題,當元素數太多時,樹的高度就會比較高,而數的高度又決定瞭樹查詢的時候比較的次數,數據庫比較的時候需要讀取硬盤,因此更希望書的高度能降低一點,那麼就考慮使用N叉搜索樹瞭
N叉搜索樹,每個節點有很多個值,同時有很多的分叉,降低瞭樹的高度,減少瞭比較的次數
一種典型的實現N叉搜索樹的方式就是B樹
我們看一下B樹的結構
這種結構降低瞭樹的高度,沒有減少比較次數(但是在一個節點上比較多次瞭),減少瞭對硬盤的讀寫次數,節點都是保存在硬盤上的,能一定程度的解決問題,適合做索引
3.2B+樹
還有種更適合做索引的數據結構,就是B+樹
B+樹的特點:
1.B+樹也是一個N叉樹,增加瞭新的特點,每個節點上包含N個Key,N個Key劃分出N個區間,每個區間的最後一個key就是最大值
2.父元素的Key會在子元素中出現並且為最大值,重復出現導致瞭,葉子節點就包含瞭所有數據的全集!
那麼非葉子結點的所有元素都在葉子節點中體現
3.葉子節點用類似於鏈表的形式相連起來,構成瞭B+樹
B+樹這個數據結構做索引好處太明顯瞭
1.既有B樹高度比較低的特點,又更適合范圍查詢,比如查找>6且<15的元素,結果集非常容易取得,效率很高
2.對於所有的查詢,都要落在葉子節點上,中間的比較次數是差不多的,查詢操作比較均衡
對B樹來說,在根節點或者深度不深的元素查詢快,別的地方查詢慢,不均衡,B+樹都是一樣的,都落在葉子節點上瞭
3.由於所有的Key都會在葉子節點中出現,因此非葉子節點不用存表的真實記錄,隻要把說有的數據行放在葉子節點上即可,非葉子節點隻用存索引列的值,比如id這些,非葉子節點占用的空間就很小瞭,有可能在內存中放進去緩存瞭,更進一步降低瞭硬盤IO,提高瞭查詢的速度
綜上,B+樹是非常適合作為索引的數據結構的
有的表不隻是有主鍵索引,還有別的非主鍵列也有索引,此時會構造另一個B+樹,非葉子節點裡面存儲這一列的Key,到瞭葉子節點這一層不再存儲完整的數據行瞭,而是存儲主鍵索引的id,那麼使用主鍵索引查詢時隻用查一次B+樹就好瞭,使用非主鍵列索引要先查一遍另外構造的B+樹,然後查一次主鍵列的B+樹(這個操作稱為回表操作)
當前B+樹這個結構適用於MySQL的InnoDB這個數據引擎,不同的數據庫,不同的引擎存儲數據的數據結構還是有差異的
4.事務
事務指邏輯上的一組操作，組成這組操作的各個單元，要麼全部成功，要麼全部失敗。在不同的環境中，都可以有事務。對應在數據庫中，就是數據庫事務。
4.1事物的回滾(rollback)
當一個事務在執行時,執行中間出錯瞭,就讓它恢復成原來的樣子
涉及到的操作就是回滾,具體實現是把執行過的操作逆向恢復回去
數據庫會把執行的每個操作都記錄下來,如果某個操作出錯瞭,就會把事務中之前的操作進行回滾,根據之前的操作,進行逆操作(前面插入回滾就是刪除之前插入的)
有瞭這個操作,那麼刪表刪庫是不是就不危險瞭呢?反正可以回滾麼,事實當然不是這樣的,回滾的操作是有很大開銷的,可以保執行的操作,但也不能無限保存,最多就是保存正在執行的事務,當數據量特別大時,更不可能保存每個數據如何得到,因此刪表刪庫仍然是很危險的操作!!
4.2事務的四大特性(ACID)
事務的四大特性主要是：原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）、持久性（Durability）
4.2.1 原子性
原子性是指事務是一個不可分割的工作單位，事務中的操作要麼全部成功，要麼全部失敗。比如在同一個事務中的SQL語句，要麼全部執行成功，要麼全部執行失敗
4.2.2 一致性
事務必須使數據庫從一個一致性狀態變換到另外一個一致性狀態,事物的執行前後數據是合法的
比如銀行轉賬時,A給B轉瞭100,A轉出100,B卻沒有收到100,這時就出現瞭數據不合法,沒有達到一致性
4.2.3 持久性
持久性是指一個事務一旦被提交，它對數據庫中數據的改變就是永久性的，接下來即使數據庫發生故障也不應該對其有任何影響,保證事務對數據庫的改變是生效的
4.2.4 隔離性
一個數據庫服務器同時執行多個事務的時候,事物之間的相互影響的程度
隔離性越高,事務之間並發程度越低,執行效率慢,但是數據準確性高,像銀行轉賬…..
隔離性越低,事務之間並發程度越高,執行效率快,但是數據準確性低,像點贊數…..
5.並發引起的問題
5.1 "讀臟數據"
當一個事務修改某個數據後，另一事務對該數據進行瞭讀取，由於某種原因前一事務撤銷瞭對數據的修改（即將修改過的數據恢復原值），那麼後一事務讀到的數據與數據庫中的數據不一致，這稱之為讀臟數據
為瞭解決這個問題,要降低並發性,提高隔離性,具體操作就是給''寫操作''加鎖,寫的時候不能被讀取,降低瞭一定的效率,但是提高瞭數據的準確性
5.2 "不可重復讀"
當一個事務讀取某個數據後，另一事務執行瞭對該數據的更新，當前事務再次讀取該數據（希望與第一次讀取的是相同的值）時，得到的數據與前一次的不一樣，這是由於第一次讀取數據後，事務B對其做瞭修改，導致再次讀取數據時與第一次讀取的數據不相同
這次給''讀操作''加鎖,讀的時候數據不能被修改,並發程度進一步降低,隔離性進一步增加,運行速度變慢,數據準確性進一步提高瞭
5.3 "幻讀"
事務A 按照一定條件進行數據讀取， 期間事務B 插入瞭相同搜索條件的新數據，事務A再次按照原先條件進行讀取時，發現瞭事務B 新插入的數據 稱為幻讀
為瞭解決這個問題,需要徹底舍棄並發,進行串行化操作,在讀的時候不進行其他的操作
6.MySQL的隔離級別
MySQl為瞭控制並發程度的高低,引入瞭四個隔離級別,通過修改配置文件就可以改變隔離級別
6.1 read uncommitted
不做任何處理,事務間隨意並發,當然上面的三個問題都存在,隔離性最低,並發程度最高
6.2 read committed
對寫操作加鎖,解決瞭讀臟數據問題,還存在另外兩個問題
6.3 repeatable read
對讀寫操作加鎖,解決瞭讀臟數據問題,不可重復讀問題
還存在幻讀問題
6.4 serializable
嚴格串行化,解決瞭三個由並發引起的問題,並發程度最低,隔離性是最高的
看這張圖比較直觀
到此這篇關於MySQL索引與事務定義到使用詳解的文章就介紹到這瞭,更多相關MySQL索引與事務內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！