MySQL 索引和事務

前言

一、MySQL 索引介紹

1. 索引概述

2. 索引作用

3. 索引的分類

3.1 普通索引

3.2 唯一索引

3.3 主鍵索引

3.4 組合索引 (最左前綴)

3.5 全文索引 (FULLTEXT)

3.6 創建索引的原則依據

3.7 查看索引

3.8 刪除索引

二、MySQL 事務

1. 事務的 ACID 原則

MYSQL 事務處理主要有兩種方法:

(1) 用BEGIN、ROLLBACK、COMMIT顯式控制事務

(2) 用SET AUTOCOMMIT修改自動提交模式

總結：

前言

在當今數據驅動的時代，數據庫的高效與可靠性是業務系統的核心支柱，而索引和事務作為數據庫的兩大基石，直接影響著數據查詢性能與操作安全性。索引是一個排序的列表，可通過存儲索引值及對應數據行的物理地址加速查詢，避免全表掃描，但其創建和維護需占用額外磁盤空間，且存在多種分類及創建原則；事務則用于處理操作量大、復雜度高的數據，通過原子性、一致性、隔離性和持久性（ACID 原則）保障數據完整性，確保成批 SQL 語句要么全部執行、要么全部不執行，其控制語句及處理方法在維護數據庫一致性方面至關重要。本課程將深入解析索引的本質、分類及優化策略，同時剖析事務的底層邏輯與應用場景，助力掌握提升數據庫效能的核心技術。

一、MySQL 索引介紹

1. 索引概述

索引是一個排序的列表，在這個列表中存儲著索引的值和包含這個值的數據所在行的物理地址。在數據十分龐大的時候，索引可以大大加快查詢的速度。這是因為使用索引后可以不用掃描全表來定位某行的數據，而是先通過索引表找到該行數據對應的物理地址然后訪問相應的數據。索引的作用類似于圖書的目錄，可以根據目錄中的頁碼快速找到所需的內容。

當數據保存在磁盤類存儲介質上時，它是作為數據塊存放。這些數據塊是被當作一個整體來訪問的，這樣可以保證操作的原子性。硬盤數據塊存儲結構類似于鏈表，都包含數據部分，以及一個指向下一個節點 (或數據塊) 的指針，不需要連續存儲。

記錄集只能在某個關鍵字段上進行排序，所以如果需要在一個無序字段上進行搜索，就要執行一個線性搜索 (Linear Search) 的過程，平均需要訪問 N/2 的數據塊，N 是表示所占據的數據塊數目。如果這個字段是一個非主鍵字段 (也就是說，不包含唯一的訪問入口), 那么需要在 N 個數據塊上搜索整個表格空間。

但是對于一個有序字段，可以運用二分查找 (Binary Search), 這樣只需要訪問 log?(N) 的數據塊。這就是為什么數據表使用索引后性能可以得到本質上提高的原因。

索引是對記錄集的多個字段進行排序的方法。在一張表中為一個字段創建一個索引，將創建另外一個數據結構，包含字段數值以及指向相關記錄的指針，然后對這個索引結構進行排序，允許在該數據上進行二分法排序。

使用索引的副作用是需要額外的磁盤空間。對于 MyISAM 引擎而言，這些索引是被統一保存在一張表中的。如果很多字段都建立了索引，那么會占用大量的磁盤空間，這個文件將很快到達底層文件系統所能夠支持的大小限制。

2. 索引作用

在索引列上，除了上面提到的有序查找之外，數據庫利用各種各樣的快速定位技術，能夠大大提高查詢效率。特別是當數據量非常大，查詢涉及多個表時，使用索引往往能使查詢速度加快成千上萬倍。

例如，有 3 個未索引的表 t1、t2、t3, 分別只包含列 c1、c2、c3, 每個表分別含有 1000 行數據組成，均為 1~1000 的數值，查找對應值相等行的查詢如下所示。

?mysql>SELECT c1, c2,c3 FROM t1,t2,t3 WHERE c1=c2 AND c1=c3; ?

此查詢結果應該為 1000 行，每行包含 3 個相等的值。在無索引的情況下處理此查詢，必須尋找 3 個表所有的組合，以便得出與 WHERE 子句相配的那些行。而可能的組合數目為 1000×1000×1000 (十億), 顯然查詢將會非常慢。

如果對每個表進行索引，就能極大地加速查詢進程，利用索引的查詢處理如下。

從表 t1 中選擇第一行，查看此行所包含的數據。
使用表 t2 上的索引，直接定位 t2 中與 t1 的值匹配的行。同理，利用表 t3 上的索引，直接定位 t3 中與 t1 的值匹配的行。
掃描表 t1 的下一行并重復前面的過程，直到遍歷 t1 中所有的行。

在此情形下，仍然對表 t1 執行了一個完全掃描，但能夠在表 t2 和 t3 上進行索引查找直接取出這些表中的行，比未用索引時要快一百萬倍。

利用索引，MySQL 加速了 WHERE 子句滿足條件行的搜索，而在多表連接查詢時、在執行連接時加快了與其他表中的行匹配的速度。

3. 索引的分類

在數據庫表中，對字段建立索引可以大大提高查詢速度。通過善用這些索引可以令 MySQL 的查詢和運行更加高效。索引是快速搜索的關鍵。MySQL 索引的建立對于 MySQL 的高效運行是非常重要的。下面介紹幾種常見的 MySQL 索引類型。

從物理存儲的角度來劃分，索引分為聚簇索引和非聚簇索引兩種，聚簇索引是按照數據存放的物理位置為順序的，而非聚簇索引就不一樣了；聚簇索引能提高多行檢索的速度，而非聚簇索引對于單行的檢索更快。

從邏輯的角度來劃分，索引分為普通索引、唯一索引、主鍵索引、組合索引和全文索引。這些索引分類的具體解釋如下所示。

3.1 普通索引

普通索引是最基本的索引，它沒有任何限制，也是大多數情況下用到的索引。它有以下幾種創建方式。

直接創建索引:

?mysql>CREATE INDEX index_name ON table_name (column(length));

column 是指定要創建索引的列名。通常可以考慮將查詢語句中在 JOIN 子句和 WHERE 子句里經常出現的列作為索引列。

其中 length 是可選項。如果忽略 length 的值，則使用整個列的值作為索引。如果指定使用列前的 length 個字符來創建索引，就是使用列的一部分來創建索引，這樣有利于減小索引文件的大小，節省索引列所占的空間。在某些情況下，只能對列的前綴進行索引。索引列的長度有一個最大上限 255 個字節 (MyISAM 和 InnoDB 表的最大上限為 1000 個字節), 如果索引列的長度超過了這個上限，就只能用列的前綴進行索引。另外，BLOB 或 TEXT 類型的列也必須使用前綴索引。column 和 length 的含義，在下面創建索引的操作語句中意義相同。

修改表結構的方式添加索引:

?mysql>ALTER TABLE table_name ADD INDEX index_name (column(length)); ?

創建表結構時，同時創建索引:

mysql> create table t3(id int(10),name char(20),age int(10),index ccc(name)) ;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

3.2 唯一索引

唯一索引與普通索引類似，不同的就是：唯一索引的索引列的值必須唯一，但允許有空值 (注意和主鍵不同)。如果是組合索引，則列值的組合必須唯一。唯一索引創建方法和普通索引類似。

創建唯一索引:

mysql> create unique index ddd on t2(id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

修改表結構的時候添加唯一索引:

mysql> alter table t1 add unique aaa(id);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

創建表的時候同時創建唯一索引:

mysql> create table t1(id int(10),name char(20),age int(10), unique aaa(name)) ;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

3.3 主鍵索引

主鍵索引是一種特殊的唯一索引，一個表只能有一個主鍵，不允許有空值。一般是在建表的時候同時創建主鍵索引。

mysql> create table t4(id int(10),name char(20),age int(10),index ccc(name),primary key(id));

3.4 組合索引 (最左前綴)

平時用的 SQL 查詢語句一般都有比較多的限制條件，所以為了進一步榨取 MySQL 的效率，就要考慮建立組合索引。在組合索引的創建中，有兩種場景，即為單列索引和多列索引。下面通過一個場景來具體說明單列索引和多列索引。

在一個 user 用戶表中，有 name,age,sex 三個字段，分別分三次建立了 INDEX 普通索引。那么在select*from user where name=?AND age=? AND sex=?;數據查詢語句中就會分別檢索三條索引，雖然掃描效率有所提升，但卻還未達到最優。這個時候就需要使用到組合索引 (即多列索引), 如下所示。

mysql> create table t5 (name varchar(20),time int(20),age int(20),index t5(name,time,age) );
Query OK, 0 rows affected, 2 warnings (0.02 sec)

在 MySQL 中，有一個知識點叫最左原則。下面的 select 語句的 where 條件是依次從左往右執行的。

若使用的是組合索引index_user(name,age,sex)。在查詢中，name、age、sex 的順序必須如組合索引中一致排序，否則索引將不會生效，例如:

mysql> select * from t5 where ('zhangsan' and 'zhangsan' and 'zhangsan');
Empty set, 1 warning (0.00 sec)

如果采用此查詢方式，這條組合索引將無效化，所以一般在建立索引時，要先想好相應的查詢業務，盡量避免雖然有索引，但是使用不上的問題。

3.5 全文索引 (FULLTEXT)

MySQL 從 3.23.23 版開始支持全文索引和全文檢索。在 MySQL5.6 版本以前，FULLTEXT 索引僅可用于 MyISAM 表，在 5.6 之后 InnoDB 引擎也支持 FULLTEXT 索引：它們可以從 CHAR、VARCHAR 或 TEXT 列中作為 CREATE TABLE 語句的一部分被創建，或是隨后使用 ALTER TABLE 或 CREATE INDEX 被添加。

對于較大的數據集，將資料輸入一個沒有 FULLTEXT 索引的表中，然后創建索引，其速度比把資料輸入現有 FULLTEXT 索引的速度更快。不過切記對于大容量的數據表，生成全文索引是一個非常消耗時間、非常消耗硬盤空間的做法。

創建表的全文索引:

mysql> create table t6 (name varchar(20),time int(20),age int(20),fulltext (name));
Query OK, 0 rows affected, 2 warnings (0.08 sec)

修改表結構添加全文索引:

mysql> alter table t1 add fulltext t1(name);
Query OK, 0 rows affected, 1 warning (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 1

直接創建索引:

ysql> create fulltext index eee on t2(name);

3.6 創建索引的原則依據

數據庫建立索引的原則:

確定針對該表的操作是大量的查詢操作還是大量的增刪改操作；
嘗試建立索引來幫助特定的查詢。檢查自己的 sql 語句，為那些頻繁在 where 子句中出現的字段建立索引；
嘗試建立復合索引來進一步提高系統性能。修改復合索引將消耗更長時間，同時復合索引也占磁盤空間；
對于小型的表，建立索引可能會影響性能；
應該避免對具有較少值的字段進行索引；
避免選擇大型數據類型的列作為索引。

索引建立的原則:

在經常用作過濾器的字段上建立索引；
在 SQL 語句中經常進行 GROUP BY、ORDER BY 的字段上建立索引；
在不同值較少的字段上不必要建立索引，如性別字段；
對于經常存取的列避免建立索引；
用于聯接的列 (主鍵 / 外鍵) 上建立索引；
在經常存取的多個列上建立復合索引，但要注意復合索引的建立順序要按照使用的頻度來確定；
缺省情況下建立的是非簇集索引，但在以下情況下最好考慮簇集索引，如：含有限數目 (不是很少) 唯一的列；進行大范圍的查詢；充分的利用索引可以減少表掃描 I/O 的次數，有效的避免對整表的搜索。當然合理的索引要建立在對各種查詢的分析和預測中，也取決于 DBA 所設計的數據庫結構。

3.7 查看索引

MySQL 數據表索引已經創建好了，那么如何才能查看剛剛創建的索引？或者怎么去查看表內已經存在的索引？有以下兩種查看當前索引的方式。

?mysql>show index from t1; ?mysql>shlename; ?ow keys from t1;

以 renyuan 表為例，查看 renyuan 表的索引內容。

mysql> show index from t1\G;
*************************** 1. row ***************************Table: t1Non_unique: 1Key_name: aaaSeq_in_index: 1Column_name: nameCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment: 
Index_comment: Visible: YESExpression: NULL
1 row in set (0.02 sec)

字段解析:

Table: 表的名稱。
Non_unique: 如果索引不能包括重復詞，則為 0; 如果可以，則為 1。
Key_name: 索引的名稱。
Seq_in_index: 索引中的列序號，從 1 開始。
Column_name: 列名稱
Collation: 列以什么方式存儲在索引中。在 MySQL 中，有值 "A'(升序) 或 NULL (無分類)。
Cardinality: 索引中唯一值數目的估計值。通過運行 ANALYZE TABLE 或 myisamchk-a 可以更新。基數根據被存儲為整數的統計數據來計數，所以即使對于小型表，該值也沒有必要是精確的。基數越大，當進行聯合時，MySQL 使用該索引的機會就越大。
Sub_part: 如果列只是被部分地編入索引，則為被編入索引的字符的數目。如果整列被編入索引，則為 NULL。
Packed: 指示關鍵字如何被壓縮。如果沒有被壓縮，則為 NULL。
Null: 如果列含有 NULL, 則含有 YES。如果沒有，則該列含有 NO。
Inde_type: 用過的索引方法 (BTREE,FULLTEXT, HASH, RTREE)。
Comment: 備注。

3.8 刪除索引

索引在創建之后，是會占用一定的磁盤空間的，因此表內如果有不再使用的索引，從數據庫性能方面考慮，最好是刪除無用索引。索引的刪除有如下兩種方法。

?DROP INDEX索引名ON表名; ?ALTER TABLE表名DROP INDEX索引名; ?

mysql> drop index aaa on t1; 
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0mysql> alter table t1 drop index t1;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

二、MySQL 事務

MySQL 事務主要用于處理操作量大，復雜度高的數據。比如說，在人員管理系統中，要刪除一個人員，即需要刪除人員的基本資料，又需要刪除和該人員相關的信息，如信箱，文章等等。這樣，這些數據庫操作語句就構成一個事務！

在 MySQL 中只有使用了 Innodb 數據庫引擎的數據庫或表才支持事務。

事務處理可以用來維護數據庫的完整性，保證成批的 SQL 語句要么全部執行，要么全部不執行。

事務用來管理 insert,update,delete 語句。

一般來說，事務是必須滿足 4 個條件 (ACID): 原子性 (Atomicity, 或稱不可分割性)、一致性 (Consistency)、隔離性 (Isolation, 又稱獨立性)、持久性 (Durability)。

1. 事務的 ACID 原則

原子性: 一個事務 (transaction) 中的所有操作，要么全部完成，要么全部不完成，不會結束在中間某個環節。事務在執行過程中發生錯誤，會被回滾 (Rollback) 到事務開始前的狀態，就像這個事務從來沒有執行過一樣；
一致性: 在事務開始之前和事務結束以后，數據庫的完整性沒有被破壞。這表示寫入的資料必須完全符合所有的預設規則，這包含資料的精確度、串聯性以及后續數據庫可以自發性地完成預定的工作；
隔離性: 數據庫允許多個并發事務同時對其數據進行讀寫和修改的能力，隔離性可以防止多個事務并發執行時由于交叉執行而導致數據的不一致。事務隔離分為不同級別，包括讀未提交 (Read uncommitted)、讀提交 (read commited)、可重復讀 (repeatable read) 和串行化 (Serializable);
持久性: 事務處理結束后，對數據的修改就是永久的，即便系統故障也不會丟失。

在 MySQL 命令行的默認設置下，事務都是自動提交的，即執行 SQL 語句后就會馬上執行 COMMIT 操作。因此要顯式地開啟一個事務必須使用命令在 MySQL 命令行的默認設置下，事務都是自動提交的，即執行 SQL 語句后就會馬上執行 COMMIT 操作。因此要顯式地開啟一個事務必須使用命令BEGIN或START TRANSACTION, 或者執行命令SET AUTOCOMMIT=0, 用來禁止使用當前會話的自動提交。

事務控制語句包含:

BEGIN或START TRANSACTION: 顯式地開啟一個事務；
COMMIT: 也可以使用COMMIT WORK, 二者等價。COMMIT 會提交事務，并使已對數據庫進行的所有修改變為永久性的；
ROLLBACK: 也可以使用ROLLBACK WORK, 二者等價。回滾會結束用戶的事務，并撤銷正在進行的所有未提交的修改；
SAVEPOINT identifier: 在事務中創建一個保存點，一個事務中可以有多個 SAVEPOINT;
RELEASE SAVEPOINT identifier: 刪除指定的事務保存點，若未指定保存點則拋出異常；
ROLLBACK TO identifier: 把事務回滾到指定的保存點；
SET TRANSACTION: 設置事務的隔離級別。InnoDB 存儲引擎支持的隔離級別包括READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ(默認) 和SERIALIZABLE。

MYSQL 事務處理主要有兩種方法:

(1) 用BEGIN、ROLLBACK、COMMIT顯式控制事務

因為現在MySQL軟件都自動提交設置，所以使用事務沒什么意義，關閉自動提交，在進行操作

(2) 用SET AUTOCOMMIT修改自動提交模式

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)mysql> insert into t2 values(4,'aaaa',23);
Query OK, 1 row affected (0.00 sec)mysql> select * from t2;
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
|    1 | zhangsan |   10 |
|    2 | wangwu   |   20 |
|    3 | liuliu   |   20 |
|    4 | aaaa     |   23 |
+------+----------+------+
4 rows in set (0.00 sec)mysql> rollback;
Query OK, 0 rows affected (0.01 sec)mysql> select * from t2;
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
|    1 | zhangsan |   10 |
|    2 | wangwu   |   20 |
+------+----------+------+
2 rows in set (0.00 sec)

總結：

MySQL索引與事務是數據庫性能與完整性的兩大核心。索引通過有序列表存儲字段值與物理地址，加速查詢但占用額外空間，包含聚簇/非聚簇索引及普通、唯一、主鍵等類型，需遵循創建原則并注意最左前綴規則。事務通過ACID原則確保數據一致性，支持顯式控制（BEGIN/COMMIT/ROLLBACK）和自動提交模式修改。合理使用索引和事務能顯著提升數據庫效率和數據可靠性。