資料庫索引、事務及儲存引擎 （續

資料庫索引、事務及儲存引擎

一、索引介紹

Ⅰ、概念

索引是一個排序的列表，在這個列表中儲存著索引的值和包含這個值的資料所在行的實體地址（類似於c語言的連結串列透過指標指向錄的記憶體地址） 。

使用索引後可以不用掃描全表來定位某行的資料，而是先透過索引表找到該行資料對應的實體地址然後訪問相應的資料，因此能t庫的查詢速度。

索引就好比是一本書的目錄，可以根據目錄中的頁碼快速找到所需的內容。

索引是表中一列或者若干列值排序的方法。

建立索引的目的是加快對錶中記錄的查詢或排序。

Ⅱ、索引的作用

設定了合適的索引之後，資料庫利用各種快速定位技術，能夠大大加快查詢速度，這是建立索引的最主要的原因。

當表很大或查詢涉及到多個表時，使用索引可以成千上萬倍地提高查詢速度。

可以降低資料庫的Io成本，並且索引還可以降低資料庫的排序成本。

透過建立唯一性索引，可以保證資料表中每一行資料的唯一性。

可以加快表與表之間的連線。

在使用分組和排序時，可大大減少分組和排序的時間。

建立索引在搜尋和恢復資料庫中的資料時能顯著提高效能

副作用：

索引需要佔用額外的磁碟空間

在插入和修改資料時要花費更多的時間，因為索引也要隨之變動

（主要作用：加快資料庫資料查詢速度，對欄位排序

如何加快：索引保持索引的值以及這個值的資料所在行的實體地址，使用索引後可以不用再掃描/遍歷全表來定位某行資料，而是先透過索引值找到對應行資料的實體地址，後訪問相應的資料）

Ⅲ、建立索引的原則依據

索引雖可以提升資料庫查詢的速度，但並不是任何情況下都適合建立索引。因為索引本身會消耗系統資源

在有索引的情況下，資料庫會

先進行索引查詢，然後定位到具體的資料行，如果索引使用不當，反而會增加資料庫的負擔。

表的主鍵、外來鍵必須有索引。因為主鍵具有唯一性，外來鍵關聯的是主表的主鍵，查詢時可以快速定位。

記錄數超過300行的表應該有索引。如果沒有索引，每次查詢都需要把表遍歷一遍，會嚴重影響資料庫的效能。

經常與其他表進行連線的表，在連線欄位上應該建立索引。

唯一性太差的欄位不適合建立索引。

更新太頻繁地欄位不適合建立索引。

經常出現在 where子句中的欄位，特別是大表的欄位，應該建立索引。

在經常進行GROUP BY、ORDER BY的欄位上建立索引;

索引應該建在選擇性高的欄位上。

索引應該建在小欄位上，對於大的文字欄位甚至超長欄位，不要建索引。

二、索引的分類和建立

create table member (id int(10),name varchar(10),cardid int(18),phone int(11),address varchar(50),remark text)

1.

Ⅰ、普通索引

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

create index 索引名 on 表名 (列名[(length)]);

(列名[(length)]：length 為可選項，如果忽略 length 的值，則使用整個列的值作為索引。

                如果指定使用列的前 length 個字元來建立索引，這樣有利於減小索引檔案的大小。

索引名建議以 “index” 結尾。

1.

2.

3.

4.

5.

Ⅱ、唯一性索引

與普通索引類似，但區別是唯一索引列的每個值都唯一。

唯一索引允許有空值（注意和主鍵不同）。如果是用組合索引建立，則列值的組合必須唯一。新增唯一鍵將自動建立唯一索引。

##直接建立唯一索引

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

例：select * from member;

create unique index address_index on member (address);

create unique index name_index on member (name);

show create table member;

1.

2.

3.

4.

5.

6.

7.

8.

##修改表方式建立

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

例：alter table member add unique cardid_index (cardid);

1.

2.

3.

4.

5.

##建立表的時候指定

CREATE TABLE 表名 (欄位1 資料型別,欄位2 資料型別[,...],UNIQUE 索引名 (列名));

例:create table amd2 (id int,name varchar(20),unique id_index (id));

show creat table amd2;

1.

2.

3.

4.

5.

6.

Ⅲ、主鍵索引

是一種特殊的唯一索引，必須指定為“PRIMARY KEY”。

一個表只能有一個主鍵，不允許有空值。 新增主鍵將自動建立主鍵索引。

##建立表的時候指定

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

例：create table test1 (id int primary key,name varchar(20));

create table test2 (id int,name varchar(20),primary key (id));

show create table test1;

show create table test2;

1.

2.

3.

4.

5.

6.

7.

8.

9.

##修改表方式建立

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

1.

2.

3.

Ⅳ、組合索引（單列索引與多列索引)

可以是單列上建立的索引，也可以是在多列上建立的索引。

CREATE TABLE 表名 (列名1 資料型別,列名2 資料型別,列名3 資料型別,INDEX 索引名 (列名1,列名2,列名3));

select * from 表名 where 列名1='...' AND 列名2='...' AND 列名3='...';

例:create table amd1 (id int not null,name varchar(20),cardid varchar(20),index index_amd (id,name));

show create table amd1;

insert into amd1 values(1,'zhangsan','123123');

select * from amd1 where name='zhangsan' and id=1;

1.

2.

3.

4.

5.

6.

7.

8.

Ⅴ、全文索引（FULLTEXT）

適合在進行模糊查詢的時候使用，可用於在一篇文章中檢索文字資訊。

在 MySQL5.6 版本以前FULLTEXT 索引僅可用於 MyISAM 引擎，在 5.6 版本之後 innodb 引擎也支援 FULLTEXT 索引。全文索引可以在 CHAR、VARCHAR 或者 TEXT 型別的列上建立。每個表只允許有一個全文索引。

##直接建立索引

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

例：select * from member;

create fulltext index remark_index on member (remark);

1.

2.

3.

4.

5.

6.

##修改表方式建立

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

1.

2.

3.

●建立表的時候指定索引

CREATE TABLE 表名 (欄位1 資料型別[,...],FULLTEXT 索引名 (列名));

1.

2.

3.

●使用全文索引查詢

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

例：select * from member where match(remark) against('this is vip');

1.

2.

3.

4.

5.

三、檢視索引

show index from 表名;

show index from 表名\G； 豎向顯示錶索引資訊

show keys from 表名;

show keys from 表名\G;

1.

2.

3.

4.

欄位 含義

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 備註

四、刪除索引

##直接刪除索引

DROP INDEX 索引名 ON 表名;

例：drop index name_index on member;

1.

2.

3.

4.

5.

##修改表方式刪除索引

ALTER TABLE 表名 DROP INDEX 索引名;

例：alter table member drop id_index;

show index from member;

1.

2.

3.

4.

5.

##刪除主鍵索引

ALTER TABLE 表名 DROP PRIMARY KEY;

1.

2.

3.

五、MySQL事務

Ⅰ、概念

●事務是一種機制、一個操作序列，包含了一組資料庫操作命令，並且把所有的命令作為一個整體一起向系統提交或撤銷操作請求，即這一組資料庫命令要麼都執行，要麼都不執行。

●事務是一個不可分割的工作邏輯單元，在資料庫系統上執行併發操作時，事務是最小的控制單元。

●事務適用於多使用者同時操作的資料庫系統的場景，如銀行、保險公司及證券交易系統等等。

●事務是透過事務的整體性以保證資料的一致性。

說白了，所謂事務，它是一個操作序列，這些操作要麼都執行，要麼都不執行，它是一個不可分割的工作單位。

Ⅱ、事務的ACID特點

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

●原子性：指事務是一個不可再分割的工作單位，事務中的操作要麼都發生，要麼都不發生。

事務是一個完整的操作，事務的各元素是不可分的。

事務中的所有元素必須作為一個整體提交或回滾。

如果事務中的任何元素失敗，則整個事務將失敗。

●一致性：指在事務開始之前和事務結束以後，資料庫的完整性約束沒有被破壞。

當事務完成時，資料必須處於一致狀態。

在事務開始前，資料庫中儲存的資料處於一致狀態。

在正在進行的事務中，資料可能處於不一致的狀態。

當事務成功完成時，資料必須再次回到已知的一致狀態。

●隔離性：指在併發環境中，當不同的事務同時操縱相同的資料時，每個事務都有各自的完整資料空間。

對資料進行修改的所有併發事務是彼此隔離的，表明事務必須是獨立的，它不應以任何方式依賴於或影響其他事務。

修改資料的事務可在另一個使用相同資料的事務開始之前訪問這些資料，或者在另一個使用相同資料的事務結束之後訪問這些資料。

●永續性：在事務完成以後，該事務所對資料庫所作的更改便持久的儲存在資料庫之中，並不會被回滾。

指不管系統是否發生故障，事務處理的結果都是永久的。

一旦事務被提交，事務的效果會被永久地保留在資料庫中。

事務之間的相互影響

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

1、髒讀：一個事務讀取了另一個事務未提交的資料，而這個資料是有可能回滾的。

2、不可重複讀：一個事務內兩個相同的查詢卻返回了不同資料。這是由於查詢時系統中其他事務修改的提交而引起的。

3、幻讀：一個事務對一個表中的資料進行了修改，這種修改涉及到表中的全部資料行。同時，另一個事務也修改這個表中的資料，這種修改是向表中插入一行新資料。那麼，操作前一個事務的使用者會發現表中還有沒有修改的資料行，就好象發生了幻覺一樣。

4、丟失更新：兩個事務同時讀取同一條記錄，A先修改記錄，B也修改記錄（B不知道A修改過），B提交資料後B的修改結果覆蓋了A的修改結果。

Ⅲ、Mysql及事物隔離級別

（1）read uncommitted : 讀取尚未提交的資料 ：不解決髒讀

（2）read committed：讀取已經提交的資料 ：可以解決髒讀

（3）repeatable read：重讀讀取：可以解決髒讀 和 不可重複讀 —mysql預設的

（4）serializable：序列化：可以解決 髒讀 不可重複讀 和 虛讀—相當於鎖表

mysql預設的事務處理級別是 repeatable read ，而Oracle和SQL Server是 read committed 。

1、查詢全域性事務隔離級別

show global variables like '%isolation%';

SELECT @@global.tx_isolation;

1.

2.

2、查詢會話事務隔離級別

show session variables like '%isolation%';

SELECT @@session.tx_isolation; 

SELECT @@tx_isolation;

1.

2.

3.

3、設定全域性事務隔離級別

set global transaction isolation level read committed;