Mysql中索引、事物及存儲引擎的詳細介紹

下文主要給大家帶來Mysql中索引、事物及存儲引擎的詳細介紹，希望Mysql中索引、事物及存儲引擎的詳細介紹這些內容能夠帶給大家實際用處，這也是我編輯這篇文章的主要目的。好了，廢話不多說，大家直接看下文吧。

一、索引：

（1）數據庫索引：

1、在數據庫中，索引使數據程序無須對整個表進行掃描，就可以在其中找到所需數據；

2、數據庫中的索引是某個表中一列或者若干列值的集合，以及物理標識這些值的數據頁的邏輯指針清單。

（2）索引的作用：

1、數據庫能夠大大加快查詢速率；

2、降低數據庫的 IO 成本，并且索引還可以降低數據庫的排序成本；

3、通過創建唯一性索引保證數據表數據的唯一性；

4、加快表與表之間的連接；

5、分組和排序的時候，可以大大減少分組和排序時間

（3）索引分類：

1、普通索引 ：最基本的索引類型，而且沒有唯一性之類的限制；

2、唯一性索引 ：與普通索引基本相似，區別在于，索引列的所有值都只能出現一次，即必須唯一；

3、主鍵 ：主鍵是一種唯一性索引，必須指定為 “ primary key ” ；

4、全文索引 ：mysql 從3.23.23版本開始支持全文索引和全文檢索，在mysql 中，全文索引的索引類型為 fulltext ，全文索引可以在 varchar 或者 text 類型的列上創建；

5、單列索引與多列索引：索引可以是單列上創建的索引，也可以是在多列上創建的索引。

（4）創建索引的原則依據：

表的主鍵、外鍵必須有索引；

數據量超過 300 行的表應該有索引；

經常與其他表進行連接的表，在連接字段上應該建立索引；

唯一性太差的字段不適合建立索引；

更新太頻繁的字段不適合創建索引；

經常出現在 where 子句中的字段，特別是大表的字段，應該建立索引；

索引應該建立在選擇性高的字段上；

索引應該建立在小字段上，對于大的文本字段甚至超長字段，不要建立索引。

（5）創建索引的辦法：

1、創建普通索引：

例如，我們針對下面這張表，給年齡這列創建一個索引：

2、創建唯一性索引：

3、創建主鍵索引：

4、創建全文索引：

create fulltext index 索引名稱 on 表名(列的列表);

5、創建組合索引：

create index 索引名稱 on 表名 (列的列表1，列的列表2.....);

6、查看索引：

show index from tablename;   
show keys from tablename;

二、事務：

（1）事務概念：

簡單的說就是把所有的命令作為一個整體一起提交或者撤銷，要么一起都執行，要么一起都不執行。

（2）事務的四大特性：

1、原子性：所有元素都必須作為一個整體提交或回滾，任何元素失敗，則整個事務都失敗；

例如，我們平時下載軟件等，如果中途遇到故障等導致安裝失敗，就會退回到安裝軟件前的狀態。

2、一致性：事務開始之前，數據處于統一、一致的狀態；事務完成之后，再次回到一致轉態。

3、隔離性：所有并發事務彼此獨立，互不相干、影響。

4、持久性：一旦事務被提交，就會被永久地保留在數據庫中。

（3）事務的操作：

默認情況下，mysql 的事務都是自動提交的，當 sql 語句提交時事務便自動提交；

手動對事務進行控制的辦法（事務處理命令控制或者使用 set命令控制）：

1、事務處理命令控制事務：

• begin ：開始一個事務；

• commit ：提交一個事務；

• rollback ：回滾一個事務；

接下來，我們開始做回滾操作，想回到哪個節點，就回到那個存檔點即可：

要想回滾到最初狀態，直接使用 rollback 命令：

2、使用 set 命令進行控制：

• set autocommit=0 ：禁止自動提交；

• set autocommit=1 ：開啟自動提交；

三、存儲引擎：

（1）存儲引擎概念：

mysql 中的數據用各種不同的技術存儲在文件中，每一種技術都使用不同的存儲機制、索引技巧、鎖定水平并最終提供不同的功能和能力，這些不同的技術以及配套功能在 mysql 中稱為存儲引擎。

存儲引擎就是 mysql 將數據存儲在文件系統中的存儲方式或者存儲格式；

目前 Mysql 常用的兩種存儲引擎：

• MyISAM （輕量級）

• InnoDB

mysql 存儲引擎是 mysql 數據庫云服務器中的組件，負責為數據庫執行實際的數據 I/O 操作；

使用特殊存儲引擎的主要優點之一在于，僅需提供特殊應用的特性，數據庫中的數據開銷較小，具有更有效和更高的數據庫性能；

mysql 系統中，存儲引擎處于文件系統之上，在數據保存到數據文件之前會傳輸到到存儲引擎，之后按照各個存儲引擎的存儲格式進行存儲。

1、MyISAM 介紹：

是5.5版本之前的默認存儲引擎，前身是 ISAM；

①.特點：

• ISAM 執行讀取操作的速度很快，

• 不占用大量的內存和存儲資源；

• 但是不支持事務處理；

• 不能夠容錯；

• MyISAM在磁盤.上存儲的文件；

②.數據存儲位置：

• 表定義文件；

• 表數據存儲文件；

• 表索引文件；

.frm文件存儲表定義

數據文件的擴展名為.MYD (MYData)

索引文件的擴展名是.MYI (MYIndex)

③.MyISAM適用的生產場景舉例：

• 公司業務不需要事務的支持；

• 數據修改相對較少的業務；

• 一般單方面讀取數據比較多的業務，或單方面寫入數據比較多的業務；

• 對數據業務一致性要求不是非常高的業務云服務器硬件資源相對比較差。

2、InnoDB

①.特點：

• 支持4個事務隔離級別；

• 具有非常高效的緩存特性：能緩存索引，也能緩存數據；

• 支持分區、表空間，類似oracle數據庫；

• 表與主鍵以簇的方式存儲；

• 行級鎖定，但是全表掃描仍然會是表級鎖定讀寫阻塞與事務隔離級別相關；

• 適用于對硬件資源要求還是比較高的場合。

②.適用的生產場景：

• 業務需要事務的支持；

• 業務數據更新較為頻繁的場景；

• 業務數據一致性要求較高；

• 件設備內存較大，利用Innodb較好的緩存能力來提高內存利用率，減少磁盤I0的壓力；

（2）企業選擇存儲引擎依據 ：

• 需要考慮每個存儲引擎提供了哪些不同的核心功能及應用場景；

• 支持的字段和數據類型；

• 鎖定類型（表鎖定，行鎖定）；

（3）存儲引擎相關命令：

1、查看存儲引擎的命令：

show engines        //查看系統支持的存儲引擎

2、查看表使用的存儲引擎：

show table status from 庫名 where name='表名';
show create table 表名;

3、修改存儲引擎：

方法一：使用 alter table 修改

alter  table  table_name engine=引擎;

• 方法二：修改 my.cnf 文件，指定默認存儲引擎并重啟服務

default-storage-engine=InnoDB

方法三：create table 創建表時指定存儲引擎

create  table   表名（字段） engine=引擎

四、兩表相連查詢：

例如，下面有兩張表，第一張名為 user；第二種名為 hob，因為這兩張表中有部分信息時相關聯的，如何操作：

輸入以下命令，將兩張表相關聯：

1、直接關聯：

mysql> select * from user inner join hob on user.hobby=hob.id;

2、還可以按條件關聯：

3、如果感覺表名有點長，還可以起別名：

4、創建視圖查詢：

mysql> create view view_user as select u.name,h.hob_name from user u inner join hob h on u.hobby=h.id;

注意：視圖是一個虛擬的東西，數據并不存在其中，只是方便用戶查詢，其作用類似于window桌面的快捷方式。

對于以上關于Mysql中索引、事物及存儲引擎的詳細介紹，大家是不是覺得非常有幫助。如果需要了解更多內容，請繼續關注我們的行業資訊，相信你會喜歡上這些內容的。