【MySQL】索引與事務

在這里插入圖片描述

👑專欄內容：MySQL
?個人主頁：子夜的星的主頁
💕座右銘：前路未遠，步履不停

一、索引

索引可以簡單理解為是一本書的目錄。
索引是一種特殊的文件，包含著對數據表里所有記錄的引用指針。可以對表中的一列或多列創建索引，并指定索引的類型，各類索引有各自的數據結構實現。

一個表中有很多數據，在查詢數據的時候，最基本的方法就是遍歷表，一條條的進行篩選。因此，就可以給這個表建立索引，來提高查找速度。索引是以“列”為維度進行建立的。

1、使用場景

索引是用來提高查詢效率的，但是也有一定的缺陷：

消耗額外的空間。
有可能會拖慢增刪改的速度。因為，新增的時候，不光要往表里面插入數據，還要修改索引。但是如果涉及到索引列的刪除/修改，這個時候也需要同時維護索引。

2、使用索引

創建索引

create table student (id int primary key , name varchar(20));create table student (id int unique , name varchar(20));create table student (id int primary key , name varchar(20),classId int ,foreign key (classId) references class (classId));

當表中存在主鍵的時候，內部就會自動給這個列創建索引。
因為主鍵不允許重復，因此進行插入或者修改，就需要先進行查詢，看看插入/修改的結果是不是已經存在。所以，建立索引后就會加快頻繁的查詢速度。
前面的都是自動創建的索引，下面我們了解一下手動創建索引的方法。

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

create index student_name on students(name);

手動創建索引操作，可能會非常危險。如果表是空的或者表里面包含的數據本身就不多，這個時候創建索引就沒有事。但是，如果表非空，并且里面包含了非常多的數據，那么創建索引會引起非常大規模的硬盤 IO 操作，進一步導致數據庫被卡死。

查看索引

show index from 表名;

刪除索引

drop index 索引名 on 表名;

刪除索引，只能針對手動創建的索引。自動生成的索引，是不能被刪除的。
刪除索引的操作也是非常危險的，因為也會出現大規模的硬盤 IO
【問題】如果現在確實需要給一個已經有很多數據的表創建/刪除索引，并且這個數據庫還是生產環境的數據庫，怎么辦？
數據庫服務器往往不是單臺服務器，為了系統的可靠性，往往會搞多個 MySQL 服務器節點，這些節點的數據都是一樣的，能夠提供相同的服務。所以，我們可以先準備一臺新的服務器，把表和索引都創建好，然后把數據都導入過來，再把要替換的 MySQL 服務器關閉，把新的 MySQL 服務器替換上去就行了。

3、底層數據結構（非常重要）

MySQL 的索引的數據結構到底是什么樣的數據結構，并不是定式。這取決于 MySQL 使用那個存儲引擎（MySQL 這個程序包含很多模塊，用來存儲數據的模塊就是存儲引擎）。具體如何存儲數據，MySQL 支持多種存儲方案，當下最主流的方式是 innodb。

以往的數據結構都是在內存中的，數據庫這塊組織數據使用的數據結構則是在硬盤上的。內存上的數據結構，對訪問操作來說不敏感。找數據的過程花費的時間多，真正訪問的時間不多。硬盤上的數據結構，對于訪問操作來說，比較敏感。而讀寫一次硬盤開銷又遠大于內存。

索引主要目的是為了進行快速查找，hash 不能夠進行范圍查詢，不能夠進行模糊匹配。而紅黑樹，能進行范圍查詢和模糊匹配，但是會引入較多的硬盤 IO 操作。所以，innodb 這個存儲引擎的底層數據結構是B+樹。B +樹是 B 樹的一種改進。B 樹的核心思路和之前介紹過的二叉搜索樹差不多，B 樹本質上是一個 N 叉搜索樹。
MySQL索引的底層數據結構主要是B+樹和哈希表。

B+樹索引：這是MySQL中最常用的索引類型，尤其是在InnoDB存儲引擎中。B+樹索引適用于全值匹配、匹配列前綴、范圍查找和排序操作。其特點是所有的值都是有序的，并且葉子節點之間是相連的，這使得范圍查詢變得非常高效。
哈希索引：哈希索引是基于哈希表實現的，它適用于等值比較查詢，如=, IN()等操作。哈希索引的優勢在于快速的查找速度，但它不支持范圍查找。在MySQL中，哈希索引主要被Memory存儲引擎使用。

除此之外，MySQL還支持全文索引和空間索引等，但這些索引的底層數據結構和B+樹及哈希表有所不同。全文索引用于文本數據的搜索，而空間索引用于地理空間數據。
使用 B+樹作為主要索引的關鍵優勢：

有效的范圍查詢和排序：B+樹的葉子節點包含了所有鍵值，并且這些葉子節點是相互鏈接的。這使得執行范圍查詢（如檢索一定范圍內的所有記錄）和排序操作更加高效，因為可以簡單地在葉子節點間順序遍歷。
高效的磁盤讀寫：B+樹的結構使得磁盤I/O操作更加高效。它的節點通常有很多子節點，這意味著樹的高度較低，從而減少了磁盤訪問次數。在數據庫操作中，磁盤I/O通常是性能瓶頸，因此減少I/O操作可以顯著提高性能。
頁的分裂和合并效率：當B+樹的節點（頁）滿時，它們可以被有效地分裂。相反，當節點的利用率低下時，可以將它們合并以節省空間。這種靈活性是B+樹特別適合動態數據庫環境的一個重要原因。
全鍵值存儲在葉子節點：在B+樹中，所有的數據都存儲在葉子節點中，而非葉子節點僅存儲鍵值和指向子節點的指針。這種結構使得每個葉子節點可以被用作數據層，從而簡化了數據的訪問和管理。
更好的緩存利用率：因為B+樹的結構使得大量的查詢能夠通過訪問樹的頂部幾層就能完成，這些常訪問的節點容易被緩存在內存中，從而減少了磁盤訪問次數。
適應性強：B+樹可以很好地適應各種類型的查詢，包括點查詢和范圍查詢，這使得它成為通用數據庫系統中的理想選擇。

二、事務

1、概念

事務指邏輯上的一組操作，組成這組操作的各個單元，要么全部成功，要么全部失敗。在不同的環境中，都可以有事務。對應在數據庫中，就是數據庫事務。事務的本質就是為了把多個操作打包成一個操作來完成。（打包成一個操作：要么全部都執行成功，要么一個都不執行）
【注意】一個都不執行，并不是真的不執行。執行不執行成功，執行了才知道。
假設事務中有 3 個操作：先執行 1 再執行 2 最后執行 3，如果執行到中間出錯了，就需要自動把前面已經成功執行的操作還原回最初沒有執行的模樣。本質上，這里的一個都不執行，并不是沒有執行，而是看起來跟沒執行一樣。這個還原數據的過程，叫做回滾（rollback）。
【問題】回滾是咋實現的？
只需要把事務中執行的每個操作，都記錄下來（通過特定的日志）。如果需要回滾，就按照之前的操作進行逆操作即可。

2、使用

（1）開啟事務：start transaction;
（2）執行多條SQL語句
（3）回滾或提交：rollback/commit;
說明：rollback 即是全部失敗，commit 即是全部成功。一個事務，必須以 commit 或 rollback 這兩個操作結尾。如果沒有這兩個操作，接下來的各種 sql 操作都會被認為是事務的一部分。

3、特性（非常重要）

**原子性：**事務是一個不可分割的工作單元，事務中的操作要么全部完成，要么全部不完成。如果事務中的一個操作失敗，整個事務將回滾到事務開始之前的狀態，就像這些操作從未被執行過一樣。
**一致性：**事務必須保持數據庫的一致性。這意味著事務的執行結果必須使數據庫從一個一致的狀態轉換到另一個一致的狀態。一致性包括數據的完整性、業務規則、關系約束等。
**隔離性：**并發執行事務的適合，隔離性會在執行效率和數據可靠之間做出權衡。“隔離”描述的是同時執行的事務之間，相互影響。隔離性越高，并發就越低，數據越可靠，性能就越低。隔離性通過不同的隔離級別實現，如可串行化、讀已提交、讀未提交和可重復讀，每個級別都有不同的并發控制方法和可能遇到的問題（如臟讀、不可重復讀、幻讀）。
【問題】什么是臟讀、不可重復讀、幻讀？

臟讀（Dirty Read）：
定義：臟讀發生在一個事務讀取了另一個未提交事務的數據。如果那個未提交的事務最終失敗并回滾，那么第一個事務讀取的數據就是不一致的，因為它讀取了永遠不會被提交的數據。
例子：事務 A 修改了一條記錄，但還沒有提交。與此同時，事務 B 讀取了同一條記錄。如果 A 回滾了它的改變，B 讀取的數據就是無效的。
解決：寫加鎖，提交之前不可讀。

不可重復讀（Non-repeatable Read）：
定義：不可重復讀指的是在同一個事務中，兩次讀取同一數據集合時，由于其他事務的修改操作，導致兩次讀取的數據不一致。
例子：事務 A 讀取了一條記錄，隨后事務 B 修改了這條記錄并提交。當事務 A 再次讀取同一記錄時，發現數據已經發生變化。
解決：讀加鎖，讀的適合不可寫。

幻讀（Phantom Read）：
定義：幻讀與不可重復讀類似，但它涉及到數據集合的數量變化。一個事務在讀取某個范圍的記錄時，另一個事務插入或刪除了該范圍內的記錄，導致第一個事務再次讀取時發現有“幻影”數據。
例子：事務 A 讀取了一個范圍內的所有記錄。同時，事務 B 插入了一個新的記錄到這個范圍內。當事務 A 再次讀取這個范圍的記錄時，會發現一個之前未見的新記錄。
解決：徹底串行化，完全放棄并發執行。