MySQL 數據庫 索引和事務

Posted on by

1. 索引

1.1 概念

  • 索引是為瞭加速對表中數據行的檢索而創建的一種分散的存儲結構。索引是針對表而建立的，它是由數據頁面以外的索引頁面組成的，每個索引頁面中的行都會含有邏輯指針，以便加速檢索物理數據。
  • 在數據庫關系圖中，可以在選定表的“索引/鍵”屬性頁中創建、編輯或刪除每個索引類型。當保存索引所附加到的表，或保存該表所在的關系圖時，索引將保存在數據庫中。

通俗的講，索引與數據庫中的表和數據的關系就類似於書架上的圖書（表）、書籍內容（數據）和書籍目錄（索引）的關系

1.2 作用

在數據庫系統中建立索引主要有以下作用：

  • 快速取數據
  • 保證數據記錄的唯一性
  • 實現表與表之間的參照完整性
  • 在使用 order by、group by 子句進行數據檢索時，利用索引可以減少排序和分組的時間

1.3 索引的原理

1.3.1 減少磁盤的訪問次數是構建索引的核心思想

索引的目的是方便進行查詢。
MySQL 的查詢主要是 select，select 基本執行的過程包括遍歷表、依次取出每個記錄、根據 where 字句的條件進行篩選
由於 MySQL 是把數據儲存在硬盤上，因此進行查詢時，每次取出記錄都意味著要訪問硬盤
而 IO 設備對硬盤的訪問效率遠低於對內存的訪問，因此減少磁盤的訪問次數就可以提高查詢的效率，也就是構建索引的核心思路

1.3.2 B+ 樹適用實現索引的底層

減少對數據的訪問次數就是實現索引時的重要思想，接下來將會對幾種數據結構進行分析，以找到更適合的實現索引的數據結構

二叉搜索樹：

由於二叉搜索樹可能是一個單支樹，時間復雜度為 O(N)

AVL 樹：

  • AVL 樹本質是一個二叉平衡搜索樹，是對二叉搜索樹的一個改進，它會保證瞭左右子樹的高度差不超過1，即不會存在單支樹的結構，查找時間復雜度為 O(logN)
  • 因為需要滿足左右子樹高度差不超過1的條件，所以插入或者刪除操作會破壞 AVL 樹的結構。因此需要隨時對樹進行調整。雖然查詢效率得到瞭滿足，但是降低瞭插入和刪除操作的效率，插入刪除時間復雜度為 O(logN)

紅黑樹：

  • 紅黑樹本質是一個放松瞭規則的 AVL 樹，即不要強制左右子樹高度差不超過1，會降低要求，以此來保證插入和刪除操作的效率。
  • 整體和 AVL 樹差異不大，查詢插入刪除的時間復雜度為 O(logN)

哈希表：

  • 哈希表可以做到查詢、插入、刪除的時間復雜度都是 O(1)
  • 但是哈希表的一個關鍵是必須要比較相等，但是形如大於、小於之類的條件無法做到，這就和實際查詢的情況不符合瞭

到這裡為止，好像隻有 AVL 樹或者紅黑樹更加適合用作 MySQL 的索引的實現，而這兩個數據結構的查找效率直接是由樹的高度決定的，因此數據增多的話，樹的高度也會增加。

為瞭進一步優化，就可以使用 N 叉搜索樹來降低樹的高度，即減少磁盤 IO，以提高查找效率

B 樹：

B 樹是 N 叉搜索樹的一種

B 樹示例結構：

在這裡插入圖片描述

用在索引中，每個結點都表示一個記錄

B 樹的特點：

  • 每個結點可能包含 N 個子樹
  • 每個結點上都可能存在多個值
  • 左子樹的值都小於根節點的對應值，右子樹的值都大於根節點的對應值

B+ 樹：

B+ 樹是一種特殊的 N 叉搜索樹，是 B 樹的改進版

B+ 樹示例結構：

在這裡插入圖片描述

 B+ 樹相對於 B 樹的改進：

  • 葉子節點存儲每行記錄，非葉子節點隻要存儲每行的索引值即可
  • 非葉子節點的值存在重復，使得葉子結點這一層是完整的數據集合
  • 可以通過類似於鏈表的方式，把所有的葉子節點連接起來

B+ 樹的優勢：

  • 善於進行范圍查找
  • 由於所有的查詢都是落在葉子結點上，故查詢的速度是比較穩定的
  • 由於葉子結點是數據的全集，因此就可以把葉子結點存到硬盤上，非葉子結點直接存到內存中，大大降低瞭讀取硬盤的次數

1.4 適用場景

  • 查找的次數比較多，插入刪除的次數較少適合用索引
  • 由於索引本身也占據一定空間，如果磁盤緊張就不太適合用索引
  • 索引是指定某個列來建立的，當某列的區分度比較大的時候，適合用索引，例如自增主鍵

1.5 使用語句

補充：

創建主鍵約束（primary key）、唯一約束（unique）、外鍵約束（foreign key）時，會自動創建對應列的索引

1.5.1 查看索引

語法：

show index from 表名;

示例：

在這裡插入圖片描述

1.5.2 創建索引

語法：

create index 索引名 on 表名(字段名);

示例：

在這裡插入圖片描述

1.5.3 刪除索引

語法：

drop index 索引名 on 表名;

示例：

在這裡插入圖片描述

註意：

主索引不能刪除，刪除會報錯

2. 事務

2.1 概念

事物：是屬於計算機中一個很廣泛的概念，一般是指要做的或所做的事情。在關系數據庫中，一個事務可以是一條 SQL 語句或者一組 SQL 語句或整個程序。

通俗的講，比如銀行轉賬的操作，A 轉給 B 500元，那麼這個操作其實包含瞭 A 賬戶餘額減少500元和 B 賬戶餘額增加500元兩個操作。

事物就相當於將這一連串的動作給打包，使其成為一個整體，要麼全都不做，要麼全都做完

2.2 為什麼使用事務

用上述銀行轉賬的例子為例，假設 A 賬戶減少500元的操作成功瞭，但 B 賬戶增加500元的賬戶沒有成功，那麼這個轉賬的操作是失敗的。

事物的核心特點就是： 把一系列操作給打包到一起，構成一個整體，要麼全都做完，要麼一個都不做。

全都不做是指： 如果某個操作失敗瞭，那麼就會將此時的中間狀態偷偷還原回去

因此使用事物的話就可以保證，某一系列的操作，不會隻完成其中一部分，它要麼完全完成，要麼都沒有完成

2.3 四大屬性

事務是恢復和並發控制的基本單位，它具有四個屬性：原子性、一致性、持久性、隔離性

事物的核心是原子性

2.3.1 原子性

概念：

一個事務是一個不可分割的工作單位，事務中包括的操作要麼都做，要麼都不做。

事物的核心是原子性，原子性的核心是回退為中間狀態，回退為中間狀態核心就是回滾，回滾的核心就是記住每步的操作

2.3.2 一致性

概念：

事務必須是使數據庫從一個一致性狀態變到另一個一致性狀態。一致性與原子性是密切相關的。

執行事物之前和執行事物完成後，當前表裡的數據都是合理的狀態

2.3.3 持久性

概念：

持久性也稱永久性（permanence），指一個事務一旦提交，它對數據庫中數據的改變就應該是永久性的。接下來的其他操作或故障不應該對其有任何影響。

事物操作的數據都是直接操作硬盤，而硬盤的數據都是持久化的

2.3.4 隔離性

概念：

一個事務的執行不能被其他事務幹擾。即一個事務內部的操作及使用的數據對並發的其他事務是隔離的，並發執行的各個事務之間不能互相幹擾。

2.4 使用方法

開啟事物：

start transaction;

執行多條 SQL 語句

回滾或提交

-- 回滾：表示上述 SQL 語句全部失敗
rollback;

-- 提交：表示上述 SQL 語句全部成功
commit;

到此這篇關於MySQL 數據庫 索引和事務的文章就介紹到這瞭,更多相關MySQL 索引和事務內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：