MySQL 索引 與 事務

標籤：values   目錄   建立   查詢   ESS   dex   保留   唯一性   實驗   

簡介

1·索引及分類
2·建立及查看索引
3·事務的概念及特點
4·事務的操作
5·總結

拓展：

在開始瞭解索引之前，得學會安裝 MySQL 和它的一些基本的命令，可以看看之前的一片文章：MySQL安裝與基本命令

索引及分類

1·索引的概念：

什麼是索引，不用專業術語來描述，通俗的來講，就是一本書的目錄，或一本小說的目錄，有了目錄就可以快速定位我需要找到的內容。專業點的來講，就是為了提高資料庫的搜尋效率而對某寫欄位中的值建立的目錄。

2·索引的作用

知道了它的概念，那麼作用不言而喻。但是有利有弊，得到什麼就必須犧牲什麼，索引使我們可以加快查詢表中的記錄，那麼也會付出相對應的代價：1·增加了資料庫的儲存空間；2·在插入和修改資料時要花費較多的時間。
以上是代價，但是相比代價，索引的作用更為突出：
1·最主要的就是有了索引就會加快資料查詢速度
2·當庫中的表很大時，索引的作用就體現的非常明顯，一個字’快‘
3·降低資料庫的 I0 成本，就是輸出和輸入成本
4·建立唯一性索引，可以保持資料庫表中每一行資料的唯一性
5·加快表與表之間的串連

3·索引的分類

1·普通索引：這是最基本的索引類型，沒有唯一性限制
2·唯一性索引：和普通索引基本相同，唯一的區別是索引列的所有值只能是唯一的
3·主鍵索引：是唯一索引的特定類型，它必須指定為“PRIMARY KEY”。在資料庫中為表定義主鍵將自動建立主鍵索引。
4·全文索引，索引類型為FULLTEXT,全文索引可以在CHAR、VARCHAR的列上建立
5·單列索引與多列索引：索引可以是單列上建立的索引，可以是多列上建立的索引

4·建立索引的原則依據

凡是有規矩，建立索引也必須有條件的，不能亂建立，亂建立反而會增加資料庫的負擔，因為索引也會消耗系統資源。以下是一些建立的依據
1·表的主鍵、外鍵必須有索引
2·資料量超過2000 行的表應該有索引
3·經常與其他表進行串連的表，在串連欄位上需要索引
4·唯一性太差的欄位不適合建立索引
5·更新頻繁的欄位不適合建立
6·經常出現在 where 子句中的欄位，需要建立
7·應該建立選擇性較高的欄位上
8·可以在小欄位上建立索引

索引詳細資料表

顯示參數	描述
Table	表的名稱
Non_unique	索引值的唯一性，0表示唯一性，1表示非唯一性
Key_name	索引的名稱
Seq_in_index	索引中的列序號，從1開始
Column_name	列名稱

建立及查看索引

1·建立普通同索引，命令格式是：

create index 索引名字 on 表名（列名）；

2·首先需要確保庫中有表，表中有資料，這裡為了實驗效果，在之前就建立了一張表

mysql> select * from school;

3·那麼 以這張表中的 “ id ” 欄位為所有，所有的名字是 id_index 所以普通索引為：

mysql> create index id_index on school(id);

4·查看你建立的索引格式

mysql> show index from school(表名)\G;

5·建立唯一性索引、並且查看，以為例：

mysql> create unique index unique_index on school(id);

6·建立主鍵所有，它有兩種方式，一種是建立表的同時就建立了主鍵，一種是建立表時沒有指定主鍵，這裡示範第二種，它的命令格式是：

mysql> alter table school add primary key(id);

7·建立全文索引，只有在MyISAM 類型表是才有效，它可以建立在 TEXT、CHAR、VARCHAR類型欄位中。這裡只示範修改表類型時指定索引。

mysql> alter table school add fulltext(姓名);

8·建立了索引，但是在不需要的時候需要刪除，下面是示範刪除索引
mysql> drop index id_index on school; -----（直接刪除索引）
mysql> alter table school drop index unique_index; ----- （修改表時刪除所有）
mysql>alter table school drop primary key; ----- (刪除主鍵索引)

事務的概念及特點：

事務的概念

1·事務是一種機制、一個操作序列，包含了一組資料庫操作命令，並且把所有的命令作為一個整體以起向系統提交或撤銷操作請求，通俗的來說就是這一組命令要麼都執行，要麼不執行。
2·事務是一個不可分割的工作邏輯單元，在資料庫系統上執行並行作業時，事務是最小的控制單元。
3·事務適用於使用者同時操作資料庫系統的情境，比如：銀行、保險公司、證券交易系統等，通過事務的整體性保證資料的一致性
4·事務是保持了一組操作的平穩性和可預測性的技術。

總結一下：你執行的一系列操作，要麼完整的執行，要麼完全不執行。一般情況下，操作順利進行，最終操作成功。但是如果在這一系列過程中任何一個環節除了差錯，資料庫中的所有資訊都必須保持在你開始操作前的狀態。否則，資料庫的資訊將會一片混亂而不可預測。

事務的 四大特性

1·事務的原子性：事務時一個完整的操作，各個元素是不可分的。
2·事務的一致性：當完成時，資料必須處於一致狀態；在事務開始之前，資料庫中儲存的資料處於一致狀態；在進行中的事務中，資料可能處於不一致的狀態；當事務完成時，資料必須再次回到已知的一致狀態
3·隔離性：對資料進行修改的所有並發事務時彼此隔離的，這表名事務必須時獨立的，它不會以任何方式依賴於或影響其他事務。
4·持久性：不管系統是否發送了故障，交易處理的結果都是永久的。一旦事務被提交，事務的效果會被永久地保留在資料庫中。

MySQL事務操作的兩種方法：
1·使用交易處理命令控制
2·使用set設定事務的處理方式

1·交易處理命令控制：
·begin ：表示開始一個事務，後面會有多條資料庫動作陳述式執行。
·commit：表示提交一個事務，對應前面的begin操作，他們之間的資料庫動作陳述式以起完成
·rollback：表示復原一個事務，在begin和commit之間，如果某一個資料庫動作陳述式出現錯誤，執行rollback 復原，資料庫就會回到begin之前的狀態，也就是動作陳述式都沒執行。

交易處理命令控制舉例：
1·事務提交範例：

mysql> begin; ----（事務開始）
mysql> insert into school values(8,‘王總‘,91,232,‘看書‘); ------（第一個插入）
Query OK, 1 row affected (0.00 sec) -----（提示正確執行）
mysql> insert into school values(7,‘小紅‘,60,255,‘遊戲‘); -------（第二個插入）
Query OK, 1 row affected (0.01 sec) --------（提示正確執行）
mysql> commit; -------------（提交事務）
Query OK, 0 rows affected (0.01 sec) -----------（正確執行）

2·交易回復範例

mysql> begin; -----（開始事務）
Query OK, 0 rows affected (0.00 sec)

mysql> insert into school values(9,‘王力宏‘,77,888,‘唱歌‘); ----（插入一條資料）
Query OK, 1 row affected (0.00 sec)

mysql> select * from school; -----（資料沒有提交或復原之前查看，結果如）

mysql> rollback; -------（執行復原命令）
Query OK, 0 rows affected (0.01 sec)

mysql> select * from school; ---------（再次查看錶中資訊，結果如）

rollback 定義復原點

·很多時候一個事務會包含很多條語句，而出現問題需要復原時，不一定就直接回到起點，這樣的話之前寫的語句就全都白費了，那麼這時候就要使用 savepoint 定義復原點，這個復原點就相當於 VMware 虛擬機器中拍個快照，有這麼一個意思。

savepoint 舉例：
mysql> begin; ----（開始事務）
mysql> insert into school values(9,‘王力宏‘,77,888,‘唱歌‘); -----（插入一條語句）
mysql> savepoint s1; -----（定義一個復原點）
mysql> insert into school values(10,‘那英‘,88,666,‘跳舞‘); -------（插入第二條語句）
mysql> savepoint s2; ---- （再定義一個復原點）

使用rollback 復原到指定的點
mysql> rollback to savepoint s1; --------（復原到自己定義的一個點，如）

總結：

1·資料庫索引分為：普通索引、唯一索引、主鍵索引、全文索引、多列索引
2·資料庫索引可以協助快速查詢表中資料，但並不是任何欄位都需要建立索引
3·資料庫事務的四大特性：原子性、以執行、隔離性、持久性。
4·MySQL 事務命令有：begin、rollback、commit、savepoint。

MySQL 索引 與 事務