MySQL索引、事務與儲存引擎

无敌小鲁班發表於2024-06-23

1.索引介紹
- （1）工作方式
- （2）建立索引的依據
2.索引型別
- （1）普通索引
- （2）唯一索引
- （3）主鍵索引
- （4）多列組合索引（單列索引與多列索引）
- （5）全文索引（FULLTEXT）
- （6）刪除和檢視索引以及各欄位的含義
  - （1）刪除索引
  - （2）檢視索引
  - （3）各欄位的含義
3.遇到 select 查詢語句執行速度慢該怎麼辦？
4.事務介紹
- （1）事務的 ACID 特性
- （2）隔離級別
  - （1）設定隔離級別
  - （2）檢視隔離級別
- （3）事務管理操作：
- （4）自動提交事務
5.儲存引擎介紹
- （1）MyISAM 和 InnoDB 的區別？
- （2）儲存引擎管理操作
- （3）死鎖

1.索引介紹

定義：索引是一個排序的列表，包含索引欄位的值和其對應的行記錄資料所在的實體地址
作用：加快表的查詢速度（主要作用），還可以對欄位排序
副作用：會額外佔用磁碟空間；更新包含索引的表會花費更多的時間

（1）工作方式

沒有索引的情況下，要查詢某行記錄資料時，需要先掃描全表，再定位某行記錄資料的位置
有了索引後，會先透過索引查詢到行記錄資料所在的實體地址，即可直接訪問相應的行記錄資料，就像透過書目錄的頁碼快速查詢書內容一樣。

（2）建立索引的依據

1）表的記錄行數較多時（一般超過三五百行時），且讀多的情況下應該要建立索引
2）建議在表的主鍵欄位、外來鍵欄位、多表連線使用的公共欄位、唯一性較好的欄位、不經常更新的欄位、where條件欄位、分組(group by)欄位、排序(order by)欄位、短小的欄位上建立索引
3）不建議在唯一性較差的欄位、更新太頻繁的欄位、大文字欄位上建立索引

2.索引型別

（1）普通索引

最基本的索引型別，沒有唯一性之類的限制。

create index 索引名 on 表名(欄位(長度));  #直接建立索引
alter table 表名 add index 索引名(欄位);   #修改表方式建立
create table 表名 (.... , index 索引名(欄位));     #建立表的時候指定索引

（2）唯一索引

與普通索引類似，但區別是唯一索引列的每個值都唯一。唯一索引允許有空值（注意和主鍵不同）。如果是用組合索引建立，則列值的組合必須唯一。新增唯一鍵將自動建立唯一索引。

create unique index 索引名 on 表名(欄位);  #直接建立唯一索引
alter table 表名 add unique 索引名(欄位);  #修改表方式建立
create table 表名 (.... , unique 索引名(欄位));  #建立表的時候指定

（3）主鍵索引

是一種特殊的唯一索引，必須指定為“PRIMARY KEY”。一個表只能有一個主鍵，不允許有空值。新增主鍵將自動建立主鍵索引。

 alter table 表名 add primary key(欄位);   #修改表方式建立
create table 表名 (.... , primary key(欄位));    #建立表的時候指定

（4）多列組合索引（單列索引與多列索引）

可以是單列上建立的索引，也可以是在多列上建立的索引。需要滿足最左原則，因為 select 語句的 where 條件是依次從左往右執行的，所以在使用 select 語句查詢時 where 條件使用的欄位順序必須和組合索引中的排序一致，否則索引將不會生效。

create index 索引名 on 表名(欄位1, 欄位2, ....);
alter table 表名 add index 索引名(欄位1, 欄位2, ....);
select 欄位列表 from 表名 where 欄位1=XX and 欄位2=XX .... ;   
	 #查詢語句使用 and 做邏輯運算子時，欄位順序要與建立多列索引的欄位順序一致（要滿足最左原則）

（5）全文索引（FULLTEXT）

適合在進行模糊查詢的時候使用，可用於在一篇文章中檢索文字資訊。在 MySQL5.6 版本以前
FULLTEXT 索引僅可用於 MyISAM 引擎，在 5.6 版本之後 innodb 引擎也支援 FULLTEXT 索引。全文索引可以在 CHAR、VARCHAR 或者 TEXT 型別的列上建立。

 create fulltext index 索引名 on 表名(欄位); #直接建立索引
          alter table 表名 add fulltext 索引名(欄位);   #修改表方式建立
          create table 表名 (.... , fulltext 索引名(欄位));  #建立表的時候指定索引
          
全文索引可用作於模糊查詢    select 欄位列表 from 表名 where match(欄位) against('單詞');

全文索引只能在型別為CHAR、VARCHAR或者TEXT的欄位上建立全文索引，只支援InnoDB和MyISAM引擎。
全文索引只支援英文全文索引，不支援中文全文索引，需要使用ngram全文解析器，用來支援中文全文索引
vim /etc/my.cnf
[mysqld]
ngram_token_size=2       #指定查詢的單詞的最小字數

create fulltext index 索引名 on 表名(欄位) WITH PARSER ngram;
alter table 表名 add fulltext 索引名(欄位) WITH PARSER ngram;
create table 表名 (.... , fulltext 索引名(欄位) WITH PARSER ngram);

select 欄位列表 from 表名 where match(欄位) against('單詞');     
#預設使用自然語言模式

select 欄位列表 from 表名 where match(欄位) against('+單詞1 -單詞2' IN BOOLEAN MODE);     
#使用BOOLEAN模式，必須包含"單詞1"，且不能包含"單詞2"

select 欄位列表 from 表名 where match(欄位) against('+單詞1 +單詞2' IN BOOLEAN MODE);     
#使用BOOLEAN模式，必須同時包含"單詞1"和"單詞2"

select 欄位列表 from 表名 where match(欄位) against('單詞1 單詞2' IN BOOLEAN MODE);       
#使用BOOLEAN模式，要麼包含"單詞1"，要麼包含"單詞2"

（6）刪除和檢視索引以及各欄位的含義

（1）刪除索引

#這兩種方法都可以用來刪除普通索引、唯一索引、全文索引、組合索引
drop index 索引名 on 表名;   #直接刪除索引       
alter table 表名 drop index 索引名;  #修改表方式刪除索引

alter table 表名 drop primary key;      #刪除主鍵索引

（2）檢視索引

show create table 表名;
show index from 表名;
show keys from 表名;

（3）各欄位的含義

欄位的含義
Table	表的名稱
Non_unique	如果索引不能包括重複詞，則為 0；如果可以，則為 1
Key_name	索引的名稱
Seq_in_index	索引中的列序號，從 1 開始
Column_name	列名稱
Collation	列以什麼方式儲存在索引中。在 MySQL 中，有值‘A’（升序）或 NULL（無分類）
Cardinality	索引中唯一值數目的估計值
Sub_part	如果列只是被部分地編入索引，則為被編入索引的字元的數目。如果整列被編入索引，則為 NULL
Packed	指示關鍵字如何被壓縮。如果沒有被壓縮，則為 NULL
Null	如果列含有 NULL，則含有 YES。如果沒有，則該列含有 NO
Index_type	用過的索引方法（BTREE, FULLTEXT, HASH, RTREE）
Comment	備註

3.遇到 select 查詢語句執行速度慢該怎麼辦？

1）升級 CPU 記憶體硬碟硬體效能
2）對 MySQL 配置進行最佳化
3）對查詢語句的結構進行最佳化，比如將巢狀子查詢最佳化成表連線查詢；或連線表時，可以先用where條件對錶進行過濾，然後做表連線
4）進行索引最佳化：
先使用 explain 分析 select 語句，判斷這個查詢語句是否正確的使用了索引
再根據查詢語句中的 where 條件欄位建立相應的單列索引或者多列組合索引（多列組合索引要滿足最左原則）

4.事務介紹

定義：事務就是一組資料庫操作序列（包含一個或多個SQL操作命令），事務會把所有操作看作是一個不可分割的整體向資料庫系統提交或撤銷操作，所有操作要麼都執行，要麼都不執行。

（1）事務的 ACID 特性

事務的ACID特點：ACID，是指在可靠8資料庫管理系統（DBMS）中，事務(transaction)應該具有的四個特性：原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）、永續性（Durability）。這是可靠資料庫所應具備的幾個特性。

事務的 ACID 特性
原子性：事務管理的基礎。把事務中的所有操作看作是一個不可分割的工作單元，要麼都執行，要麼都不執行。
一致性：事務管理的目的。保證事務開始前和事務結束後資料的完整和一致
隔離性：事務管理的手段。使多個事務併發操作同一個表資料時，每個事務都有各自獨立的資料空間，事務的執行不會受到其它事務的干擾。可透過設定隔離級別來解決不同的一致性問題。
永續性：事務管理的結果。當事務被提交以後，事務中的命令操作修改的結果會被持久化儲存，且不會吧被回滾。

（2）隔離級別

隔離級別
未提交讀 Read Uncommitted	允許髒讀不可重複讀幻讀
提交讀 Read Committed	不允許髒讀，允許不可重複讀幻讀（一般生產環境使用的隔離級別）
可重複讀 Repeatable Read	不允許髒讀不可重複讀，有條件的允許幻讀（InnoDB儲存引擎可以透過多版本併發控制MVCC解決幻讀問題）
序列讀 Serializable	都不允許，相當於表級鎖定，但是會影響資料庫的讀寫效率效能

（1）設定隔離級別

事務隔離級別的作用範圍分為兩種：
全域性級：對所有的會話有效
會話級：只對當前的會話有效

set global transaction isolation level 隔離級別名稱;      #全域性級隔離級別，可在所有會話有效，當前會話需要重新登入方可有效
set session transaction isolation level 隔離級別名稱;     #會話級隔離級別，僅在當前會話中立即有效

（2）檢視隔離級別

show global variables like '%isolation%';   #查詢全域性事務隔離級別
show session variables like '%isolation%';   #查詢會話事務隔離級別

（3）事務管理操作：

begin;               #顯式的開啟一個事務

#事務性操作
insert into   #增
update 表 set    #改
delete from      #刪     

savepoint XX;        #在事務中建立回滾點
rollback to XX;      #在事務中回滾到指定的回滾點位置
commit; 或 rollback;    #提交或回滾 結束事務

（4）自動提交事務

set global/session autocommit = 0/1       
#global全域性級別，session會話級別，0關閉自動提交，1開啟自動提交

show global/session variables like 'autocommit';

5.儲存引擎介紹

定義：儲存引擎是MySQL資料庫的元件，負責執行實際的資料IO操作（資料的儲存和提取）。
工作在檔案系統之上，資料庫的資料會先傳輸到儲存引擎，再按照儲存引擎的儲存格式儲存到檔案系統。
常用的儲存引擎：InnoDB 、 MyISAM

（1）MyISAM 和 InnoDB 的區別？

（1）MyISAM：不支援事務、外來鍵約束；支援全文索引；鎖定型別只支援表級鎖定；適合單獨的查詢和插入的操作；讀寫會相互阻塞；硬體資源佔用較小；資料檔案和索引檔案是分開儲存的，儲存成三個檔案：表結構檔案.frm、資料檔案.MYD、索引檔案.MYI
使用場景：適用於不需要事務支援，單獨的查詢或插入資料的業務場景
MyIAM支援的儲存格式：靜態表、動態表、壓縮表

（2）InnoDB：支援事務、外來鍵約束；也支援全文索引；鎖定型別支援行級鎖定（在全表掃描時仍會表級鎖定）；讀寫併發能力較好；快取能力較好可以減少磁碟IO的壓力；資料檔案也是索引檔案，儲存成：表結構檔案.frm、表空間檔案.ibd
使用場景：適用於需要事務支援，資料一致性要求較高，資料會頻繁更新，讀寫併發高的業務場景

InnoDB行鎖與索引的關係：
InnoDB的行級鎖是透過給索引項加鎖來實現的。如果對沒有索引的欄位進行操作會使用全表掃描並表級鎖定。

（2）儲存引擎管理操作

alter table 表名 engine=innodb/myisam;            #針對已存在的表修改儲存引擎
create table 表名 (....) engine=innodb/myisam;    #新建表時指定儲存引擎
set global/session default_storage_engine=innodb/myisam;     #設定預設儲存引擎

#透過修改 /etc/my.cnf 配置檔案，指定預設儲存引擎並重啟服務
vim /etc/my.cnf
default_storage_engine=INnoDB/MyISAM

設定預設儲存引擎

檢視儲存引擎

use 庫名;
show create table 表名;    #獲取資料表的表結構、索引等資訊
show table status [from 庫名] where name = '表名';  #檢視錶使用的儲存引擎

（3）死鎖

定義：死鎖是指兩個或多個事務在同一個資源上相互佔用，並請求對方鎖定的資源，從而導致相互阻塞的現象。

如何避免死鎖？
1）設定事務的鎖等待超時時間 innodb_lock_wait_timeout
2）設定開啟死鎖檢測功能 innodb_deadlock_detect
3）為表建立合理的索引，減少表鎖發生的機率
4）如果業務允許，可以降低隔離級別，比如選用提交讀 Read Committed 隔離級別，從而避免間隙鎖導致死鎖
5）建議開發人員儘量使用更合理的業務邏輯，比如多表操作時以固定順序訪問表，儘量避免同時鎖定多個資源
6）建議開發人員儘量保持事務簡短，減少對資源的佔用時間和佔用範圍
7）建議開發人員在讀多寫少的場景下采用樂觀鎖機制