文章目錄

• 一、MySQL 索引
• • 1.1 索引的概念
  • 1.2 索引的作用及缺點
  • • 1.2.1 優(yōu)點
    • 1.2.2 缺點
  • 1.3 創(chuàng)建索引的原則依據(jù)
  • 1.4 索引的分類和創(chuàng)建
  • • 1.4.1 普通索引
    • ●直接創(chuàng)建索引
    • ●修改表方式創(chuàng)建
    • ●創(chuàng)建表的時候指定索引
    • 1.4.2 唯一索引
    • ●直接創(chuàng)建唯一索引：
    • ●修改表方式創(chuàng)建唯一索引：
    • ●創(chuàng)建表的時候指定唯一索引：
    • 1.4.3 主鍵索引
    • ●創(chuàng)建表的時候指定主鍵索引
    • ●修改表方式創(chuàng)建主鍵索引
    • 1.4.4 組合索引（單列索引與多列索引）
    • 1.4.5 全文索引（FULLTEXT）
    • ●直接創(chuàng)建全文索引
    • ●修改表方式創(chuàng)建全文索引
    • ●創(chuàng)建表的時候指定全文索引
    • ●使用全文索引查詢
  • 1.5 查看索引
  • 1.6 刪除索引
  • ●直接刪除索引
  • ●修改表方式刪除索引
  • ●刪除主鍵索引
• 二、MySQL事務
• • 2.1 事務的概念
  • 2.2 事務的ACID特點
  • • 2.2.1 原子性
    • 2.2.2 一致性
    • 2.2.3 隔離性
    • ● 查詢?nèi)质聞崭綦x級別：
    • ● 查詢會話事務隔離級別：
    • ● 設(shè)置全局事務隔離級別：
    • ● 設(shè)置會話事務隔離級別：
    • 2.2.4 持久性
    • 2.2.5 總結(jié)
  • 2.3 事務控制語句
  • • 實例：實驗測試事務控制語句
  • 2.4 使用 set 設(shè)置控制事務
• 三、MySQL存儲引擎
• • 3.1 MyISAM 表支持的3 種不同的存儲格式
  • • 3.1.1 靜態(tài)(固定長度)表
    • 3.1.2 動態(tài)表
    • 3.1.3 壓縮表
  • 3.2 控制語句
  • • 1）查看系統(tǒng)支持的存儲引擎
    • 2）查看表使用的存儲引擎
    • 3）修改存儲引擎

一、MySQL 索引

1.1 索引的概念

索引是一個排序的列表，在這個列表中存儲著索引的值和包含這個值的數(shù)據(jù)所在行的物理地址（類似于C語言的鏈表通過指針指向數(shù)據(jù)記錄的內(nèi)存地址）。
使用索引后可以不用掃描全表來定位某行的數(shù)據(jù)，而是先通過索引表找到該行數(shù)據(jù)對應的物理地址然后訪問相應的數(shù)據(jù)，因此能加快數(shù)據(jù)庫的查詢速度。
索引就好比是一本書的目錄，可以根據(jù)目錄中的頁碼快速找到所需的內(nèi)容。
索引是表中一列或者若干列值排序的方法。
建立索引的目的是加快對表中記錄的查找或排序。

1.2 索引的作用及缺點

1.2.1 優(yōu)點

設(shè)置了合適的索引之后，數(shù)據(jù)庫利用各種快速定位技術(shù)，能夠大大加快查詢速度，這是創(chuàng)建所有的最主要的原因。
當表很大或查詢涉及到多個表時，使用索引可以成千上萬倍地提高查詢速度。
可以降低數(shù)據(jù)庫的IO成本，并且索引還可以降低數(shù)據(jù)庫的排序成本。
通過創(chuàng)建唯一性索引，可以保證數(shù)據(jù)表中每一行數(shù)據(jù)的唯一性。
可以加快表與表之間的連接。
在使用分組和排序時，可大大減少分組和排序的時間。

1.2.2 缺點

索引需要占用額外的磁盤空間。
對于 MyISAM 引擎而言，索引文件和數(shù)據(jù)文件是分離的，索引文件用于保存數(shù)據(jù)記錄的地址。
而 InnoDB 引擎的表數(shù)據(jù)文件本身就是索引文件。
在插入和修改數(shù)據(jù)時要花費更多的時間，因為索引也要隨之變動。

1.3 創(chuàng)建索引的原則依據(jù)

索引隨可以提升數(shù)據(jù)庫查詢的速度，但并不是任何情況下都適合創(chuàng)建索引。因為索引本身會消耗系統(tǒng)資源，在有索引的情況下，數(shù)據(jù)庫會先進行索引查詢，然后定位到具體的數(shù)據(jù)行，如果索引使用不當，反而會增加數(shù)據(jù)庫的負擔。

表的主鍵、外鍵必須有索引。因為主鍵具有唯一性，外鍵關(guān)聯(lián)的是子表的主鍵，查詢時可以快速定位。
記錄數(shù)超過300行的表應該有索引。如果沒有索引，需要把表遍歷一遍，會嚴重影響數(shù)據(jù)庫的性能。
經(jīng)常與其他表進行連接的表，在連接字段上應該建立索引。
唯一性太差的字段不適合建立索引。
更新太頻繁地字段不適合創(chuàng)建索引。
經(jīng)常出現(xiàn)在 where 子句中的字段，特別是大表的字段，應該建立索引。
索引應該建在選擇性高的字段上。
索引應該建在小字段上，對于大的文本字段甚至超長字段，不要建索引。

1.4 索引的分類和創(chuàng)建

1.4.1 普通索引

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

●直接創(chuàng)建索引

CREATE INDEX 索引名 ON 表名 (列名[(length)]);

#(列名(length))：length是可選項，下同。如果忽略 length 的值，則使用整個列的值作為索引。如果指定使用列前的 length 個字符來創(chuàng)建索引，這樣有利于減小索引文件的大小。
#索引名建議以“_index”結(jié)尾。

●修改表方式創(chuàng)建

ALTER TABLE 表名 ADD INDEX 索引名 (列名);

●創(chuàng)建表的時候指定索引

CREATE TABLE 表名 ( 字段1 數(shù)據(jù)類型,字段2 數(shù)據(jù)類型[,…],INDEX 索引名 (列名));

1.4.2 唯一索引

與普通索引類似，但區(qū)別是唯一索引列的每個值都唯一。唯一索引允許有空值（注意和主鍵不同）。如果是用組合索引創(chuàng)建，則列值的組合必須唯一。添加唯一鍵將自動創(chuàng)建唯一索引。

●直接創(chuàng)建唯一索引：

CREATE UNIQUE INDEX 索引名 ON 表名(列名);

●修改表方式創(chuàng)建唯一索引：

ALTER TABLE 表名 ADD UNIQUE 索引名 (列名);

●創(chuàng)建表的時候指定唯一索引：

CREATE TABLE 表名 (字段1 數(shù)據(jù)類型,字段2 數(shù)據(jù)類型[,…],UNIQUE 索引名 (列名));

1.4.3 主鍵索引

是一種特殊的唯一索引，必須指定為“PRIMARY KEY”。一個表只能有一個主鍵，不允許有空值。 添加主鍵將自動創(chuàng)建主鍵索引。

●創(chuàng)建表的時候指定主鍵索引

CREATE TABLE 表名 ([…],PRIMARY KEY (列名));

●修改表方式創(chuàng)建主鍵索引

ALTER TABLE 表名 ADD PRIMARY KEY (列名);

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

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

CREATE TABLE 表名 (列名1 數(shù)據(jù)類型,列名2 數(shù)據(jù)類型,列名3 數(shù)據(jù)類型,INDEX 索引名 (列名1,列名2,列名3));

select * from 表名 where 列名1=’…’ AND 列名2=’…’ AND 列名3=’…’;

1.4.5 全文索引（FULLTEXT）

適合在進行模糊查詢的時候使用，可用于在一篇文章中檢索文本信息。在 MySQL5.6 版本以前。

●直接創(chuàng)建全文索引

CREATE FULLTEXT INDEX 索引名 ON 表名 (列名);

●修改表方式創(chuàng)建全文索引

ALTER TABLE 表名 ADD FULLTEXT 索引名 (列名);

●創(chuàng)建表的時候指定全文索引

CREATE TABLE 表名 (字段1 數(shù)據(jù)類型[,…],FULLTEXT 索引名 (列名));

#數(shù)據(jù)類型可以為 CHAR、VARCHAR 或者 TEXT

●使用全文索引查詢

SELECT * FROM 表名 WHERE MATCH(列名) AGAINST('查詢內(nèi)容');

1.5 查看索引

show index from 表名;
show keys from 表名;

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

1.6 刪除索引

●直接刪除索引

DROP INDEX 索引名 ON 表名;

●修改表方式刪除索引

ALTER TABLE 表名 DROP INDEX 索引名;

●刪除主鍵索引

ALTER TABLE 表名 DROP PRIMARY KEY;

二、MySQL事務

2.1 事務的概念

事務是一種機制、一個操作序列，包含了一組數(shù)據(jù)庫操作命令，并且把所有的命令作為一個整體一起向系統(tǒng)提交或撤銷操作請求，即這一組數(shù)據(jù)庫命令要么都執(zhí)行，要么都不執(zhí)行。
事務是一個不可分割的工作邏輯單元，在數(shù)據(jù)庫系統(tǒng)上執(zhí)行并發(fā)操作時，事務是最小的控制單元。
事務適用于多用戶同時操作的數(shù)據(jù)庫系統(tǒng)的場景，如銀行、保險公司及證券交易系統(tǒng)等等。
事務通過事務的整體性以保證數(shù)據(jù)的一致性。

總的來說，事務就是是一個操作序列，這些操作要么都執(zhí)行，要么都不執(zhí)行，它是一個不可分割的工作單位。

2.2 事務的ACID特點

ACID，是指在可靠數(shù)據(jù)庫管理系統(tǒng)（DBMS）中，事務(transaction)應該具有的四個特性：原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）、持久性（Durability）。這是可靠數(shù)據(jù)庫所應具備的幾個特性。

2.2.1 原子性

指事務是一個不可再分割的工作單位，事務中的操作要么都發(fā)生，要么都不發(fā)生。

事務是一個完整的操作，事務的各元素是不可分的。
事務中的所有元素必須作為一個整體提交或回滾。
如果事務中的任何元素失敗，則整個事務將失敗。

2.2.2 一致性

指在事務開始之前和事務結(jié)束以后，數(shù)據(jù)庫的完整性約束沒有被破壞。

當事務完成時，數(shù)據(jù)必須處于一致狀態(tài)。
在事務開始前，數(shù)據(jù)庫中存儲的數(shù)據(jù)處于一致狀態(tài)。
在正在進行的事務中，數(shù)據(jù)可能處于不一致的狀態(tài)。
當事務成功完成時，數(shù)據(jù)必須再次回到已知的一致狀態(tài)。

2.2.3 隔離性

指在并發(fā)環(huán)境中，當不同的事務同時操縱相同的數(shù)據(jù)時，每個事務都有各自的完整數(shù)據(jù)空間。

對數(shù)據(jù)進行修改的所有并發(fā)事務是彼此隔離的，表明事務必須是獨立的，它不應以任何方式依賴于或影響其他事務。
修改數(shù)據(jù)的事務可在另一個使用相同數(shù)據(jù)的事務開始之前訪問這些數(shù)據(jù)，或者在另一個使用相同數(shù)據(jù)的事務結(jié)束之后訪問這些數(shù)據(jù)。

事務之間的相互影響分為幾種，分別為：

（1）臟讀：一個事務讀取了另一個事務未提交的數(shù)據(jù)，而這個數(shù)據(jù)是有可能回滾的。

（2）不可重復讀：一個事務內(nèi)兩個相同的查詢卻返回了不同數(shù)據(jù)。這是由于查詢時系統(tǒng)中其他事務修改的提交而引起的。

（3）幻讀：一個事務對一個表中的數(shù)據(jù)進行了修改，這種修改涉及到表中的全部數(shù)據(jù)行。同時，另一個事務也修改這個表中的數(shù)據(jù)，這種修改是向表中插入一行新數(shù)據(jù)。那么，操作前一個事務的用戶會發(fā)現(xiàn)表中還有沒有修改的數(shù)據(jù)行，就好象發(fā)生了幻覺一樣。

（4）丟失更新：兩個事務同時讀取同一條記錄，A先修改記錄，B也修改記錄（B不知道A修改過），B提交數(shù)據(jù)后B的修改結(jié)果覆蓋了A的修改結(jié)果。
Mysql 及事物隔離級別：

（1）read_uncommitted : 讀取尚未提交的數(shù)據(jù) ：不解決臟讀

（2）read_committed：讀取已經(jīng)提交的數(shù)據(jù) ：可以解決臟讀

（3）repeatable_read：重讀讀取：可以解決臟讀 和 不可重復讀 —mysql默認的

（4）serializable：串行化：可以解決 臟讀 不可重復讀 和 虛讀—相當于鎖表

mysql默認的事務處理級別是 repeatable read ，而Oracle和SQL Server是 read committed 。
事務隔離級別 臟讀 不可重復讀 幻讀 第一類丟失更新 第二類丟失更新
read uncommitted 允許 允許 允許 禁止 允許
read committed 禁止 允許 允許 禁止 允許
repeatable read 禁止 禁止 允許 禁止 禁止
serializable 禁止 禁止 禁止 禁止 禁止

● 查詢?nèi)质聞崭綦x級別：

show global variables like ‘%isolation%’;
SELECT @@global.tx_isolation;

● 查詢會話事務隔離級別：

show session variables like ‘%isolation%’;
SELECT @@session.tx_isolation;
SELECT @@tx_isolation;

● 設(shè)置全局事務隔離級別：

set global transaction isolation level read committed;

● 設(shè)置會話事務隔離級別：

set session transaction isolation level read committed;

2.2.4 持久性

在事務完成以后，該事務所對數(shù)據(jù)庫所作的更改便持久的保存在數(shù)據(jù)庫之中，并不會被回滾。

指不管系統(tǒng)是否發(fā)生故障，事務處理的結(jié)果都是永久的。
一旦事務被提交，事務的效果會被永久地保留在數(shù)據(jù)庫中。

2.2.5 總結(jié)

在事務管理中，原子性是基礎(chǔ)，隔離性是手段，一致性是目的，持久性是結(jié)果。

2.3 事務控制語句

BEGIN 或 START TRANSACTION：顯式地開啟一個事務。

COMMIT 或 COMMIT WORK：提交事務，并使已對數(shù)據(jù)庫進行的所有修改變?yōu)橛谰眯缘摹?/p>

ROLLBACK 或 ROLLBACK WORK：回滾會結(jié)束用戶的事務，并撤銷正在進行的所有未提交的修改。

SAVEPOINT S1：使用 SAVEPOINT 允許在事務中創(chuàng)建一個回滾點，一個事務中可以有多個 SAVEPOINT；“S1”代表回滾點名稱。

ROLLBACK TO [SAVEPOINT] S1：把事務回滾到標記點。

實例：實驗測試事務控制語句

第一步：創(chuàng)建表和數(shù)據(jù)

1）測試begin和commit（開始事務和提交事務）

2）測試事務未提交的回滾

3）測試創(chuàng)建回滾點并進行回滾

2.4 使用 set 設(shè)置控制事務

SET AUTOCOMMIT=0; #禁止自動提交（狀態(tài)為OFF）

SET AUTOCOMMIT=1; #開啟自動提交，Mysql默認為1（狀態(tài)為ON）

SHOW VARIABLES LIKE ‘AUTOCOMMIT’; #查看Mysql中的AUTOCOMMIT值

如果沒有開啟自動提交，當前會話連接的mysql的所有操作都會當成一個事務直到你輸入rollback|commit;當前事務才算結(jié)束。當前事務結(jié)束前新的mysql連接時無法讀取到任何當前會話的操作結(jié)果。

如果開起了自動提交，mysql會把每個sql語句當成一個事務，然后自動的commit

當然無論開啟與否，begin; commit|rollback; 都是獨立的事務。

三、MySQL存儲引擎

3.1 MyISAM 表支持的3 種不同的存儲格式

3.1.1 靜態(tài)(固定長度)表

靜態(tài)表是默認的存儲格式。靜態(tài)表中的字段都是非可變字段，這樣每個記錄都是固定長度的，這種存儲方式的優(yōu)點是存儲非常迅速，容易緩存，出現(xiàn)故障容易恢復；缺點是占用的空間通常比動態(tài)表多。

3.1.2 動態(tài)表

動態(tài)表包含可變字段，記錄不是固定長度的，這樣存儲的優(yōu)點是占用空間較少，但是頻繁的更新、刪除記錄會產(chǎn)生碎片，需要定期執(zhí)行 OPTIMIZE TABLE 語句或 myisamchk -r 命令來改善性能，并且出現(xiàn)故障的時候恢復相對比較困難。

3.1.3 壓縮表

壓縮表由 myisamchk 工具創(chuàng)建，占據(jù)非常小的空間，因為每條記錄都是被單獨壓縮的，所以只有非常小的訪問開支。

3.2 控制語句

1）查看系統(tǒng)支持的存儲引擎

show engines;

2）查看表使用的存儲引擎

方法一：

show table status from 庫名 where name=‘表名’\g

方法二：

use 庫名;
show create table 表名;

3）修改存儲引擎

① 通過 alter table 修改

use 庫名;
alter table 表名 engine=MyISAM;

② 通過修改 /etc/my.cnf 配置文件，指定默認存儲引擎并重啟服務

vim /etc/my.cnf
…
[mysqld]
…
default-storage-engine=INNODB
systemctl restart mysql.service

注意：此方法只對修改了配置文件并重啟mysql服務后新創(chuàng)建的表有效，已經(jīng)存在的表不會有變更。

③ 通過 create table 創(chuàng)建表時指定存儲引擎

use 庫名;
create table 表名(字段1 數(shù)據(jù)類型,…) engine=MyISAM;

總結(jié)

以上是生活随笔為你收集整理的MySQL(4) 索引、事务与存储引擎的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。