MySQL 的索引和事務

Gargenone發表於2024-10-13
MySql索引

MySQL 的索引事務

1. 索引

首先知道兩個事情

  1. 索引 是一種 特殊的檔案 ，包含對資料表所有記錄的 指標 像目錄
  2. 一個表是可以產生多個索引的
  3. 關鍵字是 index

索引的作用：

  1. 用於快速定位和檢索

    我們都知道 mySQL 每次的查詢都會 遍歷一遍這個列 利用好索引可以快速的 定位檢索資料

  2. 資料庫中 表 資料 索引之間的關係 類似於 書架中 圖書 書中內容 目錄 之間的作用

  3. 索引是一種 犧牲空間 提高時間的 一種 效率工具

1.1 使用

建立表時 建立主鍵約束 唯一約束 外來鍵約束 都會自動建立 對應列的 索引

  • 檢視索引

    show index from 表名;

  • 建立索引

    create index 索引名 on 表名(欄位名);

    建立班級表中 name欄位的索引

    create index idx_classes_name on classes(name);

    建立索引的時候，需要針對現有資料，進行大規模的重新整理

    所以建立索引操作也是一個 危險操作

    建立索引 都是在建立表的時候規劃好的，一旦表已經使用很久 有很多資料了 再修改索引 就要慎重了

  • 刪除索引

    drop index 索引名 on 表名;

​ 刪除的時候 只能刪除 手動建立的索引 不能刪除 自動刪除的索引

移花接木

如果真的有需要 修改索引 刪除或者建立

我們可以先搞一個新的機器 建立空表 把舊機器的 資料匯入到新機器 然後再新機器替換舊機器

1.2 索引的本質

資料庫的索引 是一種 改進的 樹形結構 B+樹

首先大致瞭解一下 B 樹 （N叉搜尋樹）

每個節點都有 m 個 key 劃分出 m+1 個區間

進行查詢的時候就可以直接從根結點出發 ， 判定當前要查的資料 在節點上的 哪一個區間中 決定下一步往哪裡走

進行新增/刪除元素的時候 就涉及到了 結點的拆分和 節點的合併

B + 樹 是 B 樹的改進

也是一個 N 叉搜尋樹

  1. 每個節點都有 N 個 key 劃分為 N 個 區間

  2. 每個節點上的最後一個 相當於 當前子樹 的最大值

  3. 父節點上的每一個 key 都會以最大值的身份出現在相對應的區間子樹中 （key 會重複出現） 也就意味著 葉子這一層包含了所有整個樹 的資料集

  4. B+樹 用類似於連結串列的結構 將葉子節點串聯起來

    此時就可以很方便的完成資料集合的遍歷

    並且 按照所需範圍 從資料集合中取出一個 子集

image-20241012104537463

B + 樹的優點：（相對於 B樹）

  1. N叉搜尋樹 書的高度優先 降低了 Io次數

  2. 非常擅長範圍查詢

  3. 所有查詢最後都會落到葉子中 查詢和查詢之間的時間開銷是穩定的

    對於 B 樹來說 要查詢的元素 再 層次比較高的 節點 就很快 如果在比較下面的層次 就比較慢

  4. 行資料 儲存到 葉子節點上 所以葉子節點會 非常佔用空間

    把 非葉子節點 快取在 記憶體中 可以進一步減少 io的訪問次數

2. 事務

事務可以把 多個 sql 打包成整體

可以保證這些 sql 要麼全部都執行 要麼就 “一個都不執行”

看起來是 “一個都不執行” 其實是 執行了 發現出錯後 對資料進行了 回滾的 操作

可以理解成是 翻新

這樣的特點 也稱為 “原子性” 表示 不可拆分的情況

事務的特性：

  1. 原子性 ：打包資料 可以透過回滾的方式 回到初狀態

  2. 一致性 ：讓資料不會太離譜 透過資料庫的約束 來完成

  3. 永續性 ：事務所做出的修改 都是在硬碟中 持久儲存的 重啟伺服器，資料依舊存在

  4. 隔離性 ：

    併發執行：多個客戶端會讓資料庫執行事務 當客戶端1 提交事務1 執行了一般 客戶端2 提交的事務2 也來了

    資料庫這時 需要同時 處理 這兩個事務

    併發程度越高，整體效率就越高

    但同時 也存在一定的問題 導致資料錯誤的情況

    併發執行可能出現的問題：

    1. 髒讀問題

      一個事務A正在寫資料的過程中，另一個事務讀取了同一個資料

      接下來 事務A 又修改了資料 導致 B 之前讀到的資料 是一個 無效的資料/過時的資料

      （如果一個一個執行 就不會出現問題）

      解決髒讀問題：針對 寫操作 進行加鎖 ，讓 寫操作 寫完以後 再讀 ，併發程度降低

    2. 不可重複讀

      併發執行的過程中，如果事務A在內部多次讀取同一個資料的時候，出現了不同的情況 原因是：事務A在兩次讀之間 有一個 事務B 修改了資料並提交了事務，這就是 不可重複讀。

      解決不可重複讀問題：針對 讀操作 進行枷鎖，讓 讀的過程中不能寫

      併發程度有進一步降低了，隔離性也進一步提高了，效率降低了，資料準性又提高了

    3. 幻讀（是不是問題 要具體場景具體分析）

      在 鎖了 寫操作和 讀操作 之後 一個事務A執行的過程中 ，兩次讀取操作，資料內容沒變但是 結果集變了（可以理解成 多了一份不同的資料集）

      解決：映入序列化的方式 ，保持一個絕對的序列執行事務，此時完全沒有併發了，效率是最低的 資料是最準確的 同時 隔離性是最高的

2.1 使用

  1. 開始 事務

    start transaction;

    開啟事務後 後面所寫的 sql 都會被打包 擁有“原子性”

  2. 執行多條 sql 語句

  3. 回滾/提交 ： rollback/commit

    這裡是 主動出發回滾 一般要搭配一些 條件判斷邏輯來使用的

​ 回滾 是怎麼做到的？

​ 日誌的方式 ，記錄事務中的關鍵操作，這樣的紀律就是回滾的依據

3. 隔離級別

效率 正確性 ====> 不同的需求場景就有不同的需求

比如 轉賬 支付 這一些列和錢相關的場景 寧願犧牲效率 也要保證正確性

短影片 轉發 點贊 這些 數量要求不高 就可以 犧牲正確性來 增加效率

mySQL 伺服器也提供了“隔離級別“讓我們針對隔離成都進行設定

-- 併發程度最高，速度最快，隔離性最低，準確性最低
read uncommitted;-- 讀未提交

-- 引入寫枷鎖 只能讀寫完之後的版本 解決髒讀問題 
-- 併發程度降低，速度降低，隔離性提高，準確性也提高
read committed; -- 讀已提交

-- 引入讀枷鎖以及寫枷鎖 寫的時候不可讀 讀的時候不可寫 解決不可重複讀問題
-- 併發程度進一步降低，速度降低，隔離性提高，準確性也提高
repeatable read; -- 可重複讀

-- 嚴格按照 序列 的方式進行 一個一個的執行事務
-- 沒有併發，速度最低，隔離性最高，準確性也最高
serializable; -- 序列化

相關文章