MySQL 索引和事務

一、MySQL 索引介紹

1、索引概述

2、索引作用

3、索引的分類

（1）普通索引

（2）唯一索引

（3）主鍵索引

（4）組合索引（最左前綴）

（5）全文索引（FULLTEXT）

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

（5）查看索引

（6）刪除索引

二、MySQL 事務

一、MySQL 索引介紹

索引是一個排序的列表，在這個列表中存儲著索引的值和包含這個值的數據所在行的物理地址。在數據十分龐大的時候，索引可以大大加快查詢的速度。這是因為使用索引后可以不用掃描全表來定位某行的數據,而是先通過索引表找到該行數據對應的物理地址然后訪問相應的數據。索引的作用類似于圖書的目錄，可以根據目錄中的頁碼快速找到所需的內容。

1、索引概述

當數據保存在磁盤類存儲介質上時，它是作為數據塊存放。這些數據塊是被當作一個整體來訪問的，這樣可以保證操作的原子性。硬盤數據塊存儲結構類似于鏈表，都包含數據部分，以及一個指向下一個節點(或數據塊)的指針，不需要連續存儲。

記錄集只能在某個關鍵字段上進行排序，所以如果需要在一個無序字段上進行搜索，就要執行一個線性搜索(Linear Search)的過程,平均需要訪問 N/2 的數據塊，N 是表示所占據的數據塊數目。如果這個字段是一個非主鍵字段(也就是說，不包含唯一的訪問入口)，那么需要在N個數據塊上搜索整個表格空間。

但是對于一個有序字段，可以運用二分查找(BinarySearch)，這樣只需要訪問 log2(N)的數據塊。這就是為什么數據表使用索引后性能可以得到本質上提高的原因。

索引是對記錄集的多個字段進行排序的方法。在一張表中為一個字段創建個索引，將創建另外一個數據結構，包含字段數值以及指向相關記錄的指針，然后對這個索引結構進行排序，允許在該數據上進行二分法排序。

使用索引的副作用是需要額外的磁盤空間。對于 MyISAM 引擎而言，這些索引是被統一保存在一張表中的。如果很多字段都建立了索引，那么會占用大量的磁盤空間，這個文件將很快到達底層文件系統所能夠支持的大小限制。

2、索引作用

在索引列上,除了上面提到的有序查找之外,數據庫利用各種各樣的快速定位技術,能夠大大提高查詢效率。特別是當數據量非常大,查詢涉及多個表時, 使用索引往往能使查詢速度加快成千上萬倍。

例如,有3個未索引的表t1、t2、t3,分別只包含列c1、c2、c3,每個表分別含有1000行數據組成,均為1~1000的數值,查找對應值相等行的查詢如下所示。

mysqI> SELECT c1, c2,c3 FROM t1,t2,t3 WHERE c1=c2 AND c1=c3;

此查詢結果應該為1000行,每行包含3個相等的值。在無索引的情況下處理此查詢,必須尋找3個表所有的組合,以便得出與WHERE子句相配的那些行。而可能的組合數目為1000X1000X1000(十億),顯然查詢將會非常慢。

如果對每個表進行索引,就能極大地加速查詢進程,利用索引的查詢處理如下。

從表t1中選擇第一行,查看此行所包含的數據。

使用表t2上的索引,直接定位t2中與t1的值匹配的行。同理,利用表t3 上的索引,直接定位t3中與t1的值匹配的行。

掃描表t1的下一行并重復前面的過程,直到遍歷t1中所有的行。

在此情形下,仍然對表t1執行了一個完全掃描,但能夠在表t2和t3 上進行索引查找直接取出這些表中的行,比未用索引時要快一百萬倍。

利用索引,MySQL加速了WHERE子句滿足條件行的搜索,而在多表連接查詢時、在執行連接時加快了與其他表中的行匹配的速度。

3、索引的分類

在數據庫表中,對字段建立索引可以大大提高查詢速度。通過善用這些索引可以令MySQL的查詢和運行更加高效。索引是快速搜索的關鍵。MySQL索引的建立對于MySQL的高效運行是非常重要的。

從物理存儲的角度來劃分,索引分為聚簇索引和非聚簇索引。

聚簇索引是按照數據存放的物理位置為順序的,而非聚簇索引就不一樣了;聚簇索引能提高多行檢索的速度,而非聚簇索引對于單行的檢索更快。

從邏輯的角度來劃分,索引分為普通索引、唯一索引、主鍵索引、組合索引和全文索引。

（1）普通索引

普通索引是最基本的索引,它沒有任何限制,也是大多數情況下用到的索引。它有以下幾種創建方式。

直接創建索引：

CREATE INDEX index_name ON table_name(column(length));

column是指定要創建索引的列名。通常可以考慮將查詢語句中在JOIN子句和 WHERE子句里經常出現的列作為索引列。

length是可選項。如果忽略length的值,則使用整個列的值作為索引。如果指定使用列前的length個字符來創建索引,就是使用列的一部分來創建索引,這樣有利于減小索引文件的大小,節省索引列所占的空間。在某些情況下, 只能對列的前綴進行索引。索引列的長度有一個最大上限255個字節(MyISAM 和InnoDB表的最大上限為1000個字節),如果索引列的長度超過了這個上限, 就只能用列的前綴進行索引。另外,BLOB或TEXT 類型的列也必須使用前綴索引。column和length的含義,在下面創建索引的操作語句中意義相同。

mysql> create database auth;    #創建庫
mysql> use auth;    #進入庫
mysql> create table users (id int(10),user_name char(20),user_pass char(50));    #創建表
mysql> create index aaa on users(user_name(20));    #創建索引

修改表結構的方式添加索引：

ALTER TABLE table_name ADD INDEX index_name(column(length));

mysql> alter table users add index bbb (user_pass(50));

創建表結構時，同時創建索引：

CREATE TABLE 'table' (

'id' int(11) NOT NULL AUTO_INCREMENT,

'title' char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,

'content' text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,

'time' int(10) NULL DEFAULT NULL,

PRIMARY KEY ('id'),

INDEX index_name (title(length)));

（2）唯一索引

唯一索引與普通索引類似，不同的就是：唯一索引的索引列的值必須唯一，但允許有空值(注意和主鍵不同)。如果是組合索引，則列值的組合必須唯一。唯一索引創建方法和普通索引類似。

創建唯一索引：

?CREATE UNIQUE INDEX index_name ON table_name(column(length));

mysql> create unique index ccc on users(id);

修改表結構的時候添加唯一索引：

ALTER TABLE table_name ADD UNIQUE index_name(column(length));

mysql> alter table  users add unique ddd(user_name);

創建表的時候同時創建唯一索引：

CREATE TABLE 'table' (

'id' int(11) NOT NULL AUTO_INCREMENT,

'title' char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,

'content' text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,

'time' int(10) NULL DEFAULT NULL,

PRIMARY KEY ('id'),

UNIQUE indexName (title(length)));

（3）主鍵索引

主鍵索引是一種特殊的唯一索引，一個表只能有一個主鍵，不允許有空值。一般是在建表的時候同時創建主鍵索引。

CREATE TABLE 'table' (

'id' int(11) NOT NULL AUTO_INCREMENT,

'title' char(255)?NOT NULL,

PRIMARY KEY ('id'));

mysql> create table t1 (id int(10),title char(25),time int(10),primary key (id),index aaa (title(25));

（4）組合索引（最左前綴）

平時用的 SQL 查詢語句一般都有比較多的限制條件，所以為了進一步榨取MySQL 的效率，就要考慮建立組合索引。在組合索引的創建中，有兩種場景，即為單列索引和多列索引。下面通過一個場景來具體說明單列索引和多列索引。

在一個 t2?表中，有name，age，sex 三個字段，分別分三次建立了INDEX 普通索引。那么在 select * from t2?where name =’’ AND age =‘’ AND sex=’’；數據查詢語句中就會分別檢索三條索引，雖然掃描效率有所提升但卻還未達到最優。這個時候就需要使用到組合索引(即多列索引)，如下所示。

mysql> create table t2 (name char(10),age int(3),sex tinyint(1),index ddd(name,age,sex));

在 MySQL 中，有一個知識點叫最左原則。下面的 select 語句的 where 條件是依次從左往右執行的。

mysql> select * from t2 where name='' and age='' and sex='';

若使用的是組合索引 index t2(name，age，sex)。在查詢中，name、age、sex 的順序必須如組合索引中一致排序，否則索引將不會生效，例如：

mysql> select * from t2 where age='' and name='' and sex='';

如果采用以上查詢方式，這條組合索引將無效化，所以一般在建立索引時，要先想好相應的查詢業務，盡量避免雖然有索引，但是使用不上的問題。

（5）全文索引（FULLTEXT）

對于較大的數據集，將資料輸入一個沒有FULLTEXT索引的表中，然后創建索引，其速度比把資料輸入現有FULLTEXT索引的速度更快。不過切記對于大容量的數據表，生成全文索引是一個非常消耗時間、非常消耗硬盤空間的做法。

創建表的全文索引：

CREATE TABLE 'table' (

'id' int(11) NOT NULL AUTO_INCREMENT,

'title' char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,

'content' text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,

'time' int(10) NULL DEFAULT NULL,

PRIMARY KEY ('id'),

FULLTEXT (content));

修改表結構添加全文索引：

ALTER TABLE article ADD FULLTEXT index_content(content);

直接創建索引：

CREATE FULLTEXT INDEX index_content ON article(content);

mysql> create fulltext index eee on users(user_name);

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

數據庫建立索引的原則：

確定針對該表的操作是大量的查詢操作還是大量的增刪改操作。
嘗試建立索引來幫助特定的查詢。檢查自己的sql語句，為那些頻繁在where 子句中出現的字段建立索。
嘗試建立復合索引來進一步提高系統性能。修改復合索引將消耗更長時間同時復合索引也占磁盤空間。
對于小型的表，建立索引可能會影響性能。
應該避免對具有較少值的字段進行索引。
避免選擇大型數據類型的列作為索引。

索引建立的原則：

索引查詢是數據庫中重要的記錄查詢方法，要不要建立索引以及在那些字段上建立索引都要和實際數據庫系統的查詢要求結合來考慮，下面給出實際生產環境中的一些通用的原則：

在經常用作過濾器的字段上建立索引。
在 SQL 語句中經常進行 GROUP BY、ORDER BY的字段上建立索引。
在不同值較少的字段上不必要建立索引，如性別字段。
對于經常存取的列避免建立索引。
用于聯接的列(主健/外健)上建立索引。
在經常存取的多個列上建立復合索引，但要注意復合索引的建立順序要按照使用的頻度來確定。
缺省情況下建立的是非簇集索引，但在以下情況下最好考慮簇集索引，如：含有限數目(不是很少)唯一的列;進行大范圍的查詢;充分的利用索引可以減少表掃描 I/0的次數，有效的避免對整表的搜索。當然合理的索引要建立在對各種查詢的分析和預測中，也取決于 DBA 所設計的數據庫結構。

（5）查看索引

show index?ftom tablename\G

show keys ftom tablename\G

mysql> show index from t1\G;
mysql> show keys ftom t1\G

以 t1 表為例，查看t1 表的索引內容

mysql> show index from t1\G;
*************************** 1. row ***************************Table: t1Non_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: idCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: Index_type: BTREEComment: 
Index_comment: Visible: YESExpression: NULL

字段解析：

Table	表的名稱。
Non_unique	如果索引不能包括重復詞，則為0;如果可以，則為1。
Key name	索引的名稱。
Seq_in_index	索引中的列序號，從 1 開始。
Column_name	列名稱。Collation:列以什么方式存儲在索引中。在 MySQL 中，有值'A’(升序)或 NULL(無分類)。
Cardinality	索引中唯一值數目的估計值。通過運行 ANALYZE TABLE 或myisamchk-a 可以更新。基數根據被存儲為整數的統計數據來計數，所以即使對于小型表，該值也沒有必要是精確的。基數越大，當進行聯合時，MySQL 使用該索引的機會就越大。
Sub_part	如果列只是被部分地編入索引，則為被編入索引的字符的數目。如果整列被編入索引，則為 NULL。
Packed	指示關鍵字如何被壓縮。如果沒有被壓縮，則為NULL。Null:如果列含有NULL，則含有YES。如果沒有，則該列含有NO。
Index_type	用過的索引方法(BTREE,FULLTEXT, HASH,RTREE)
Comment	備注。

（6）刪除索引

索引在創建之后,是會占用一定的磁盤空間的,因此表內如果有不再使用的索引從數據庫性能方面考慮，最好是刪除無用索引。

DROP INDEEX 索引名 ON 表名;

ALTEER TABLE 表名 DROP INDEX 索引名;

mysql> drop index aaa on users;
mysql> alter table users drop index bbb;

二、MySQL 事務

MySQL 事務主要用于處理操作量大，復雜度高的數據。比如說，在人員管理系統中，要刪除一個人員，即需要刪除人員的基本資料，又需要刪除和該人員相關的信息，如信箱，文章等等。這樣，這些數據庫操作語句就構成一個事務!

在 MySQL 中只有使用了 Innodb 數據庫引擎的數據庫或表才支持事務。
事務處理可以用來維護數據庫的完整性,保證成批的SQL 語句要么全部執行要么全部不執行。
事務用來管理 insert,update,delete 語句

一般來說，事務是必須滿足4個條件(ACID)：原子性(Atomicity，或稱不可分割性)、一致性(Consistency)、隔離性(Isolation，又稱獨立性)、持久性(Durability)。

原子性：一個事務(transaction)中的所有操作，要么全部完成，要么全部不完成，不會結束在中間某個環節。事務在執行過程中發生錯誤，會被回滾(Rol1back)到事務開始前的狀態，就像這個事務從來沒有執行過一樣;
一致性：在事務開始之前和事務結束以后，數據庫的完整性沒有被破壞。這表示寫入的資料必須完全符合所有的預設規則，這包含資料的精確度、串聯性以及后續數據庫可以自發性地完成預定的工作;
隔離性：數據庫允許多個并發事務同時對其數據進行讀寫和修改的能力，隔離性可以防止多個事務并發執行時由于交叉執行而導致數據的不一致。事務隔離分為不同級別，包括讀未提交(Readuncommitted)、讀提交(read committed)、可重復讀(repeatable read)和串行化(Serializable);
持久性：事務處理結束后，對數據的修改就是永久的，即便系統故障也不會丟失。

在 MySQL 命令行的默認設置下，事務都是自動提交的，即執行SQL 語句后就會馬上執行COMMIT操作。因此要顯式地開啟一個事務必須使用命令BEGIN或 START TRANSACTION，或者執行命令 SET AUTOCOMMIT=0，用來禁止使用當前會話的自動提交。

事務控制語句包含：

BEGIN或START TRANSACTION	顯式地開啟一個事務;
COMMIT	也可以使用COMMIT WORK，不過二者是等價的。COMMIT會提交事務，并使已對數據庫進行的所有修改變為永久性的;
ROLLBACK	又可以使用ROLLBACK WORK，不過二者是等價的。回滾會結束用戶的事務，并撤銷正在進行的所有未提交的修改;
SAVEPOINT identifier	SAVEPOINT 允許在事務中創建一個保存點，一個事務中可以有多個 SAVEPOINT;
RELEASE SAVEPOINT identifier	刪除一個事務的保存點，當沒有指定的保存點時，執行該語句會拋出一個異常;
ROLLBACK TO identifier	把事務回滾到標記點;
SET TRANSACTION	用來設置事務的隔離級別。InnoDB 存儲引擎提供事務的隔離級別READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。

MYSQL 事務處理主要有兩種方法：

(1)用 BEGIN，ROLLBACK，COMMIT 來實現

BEGIN? ? ? ? #開始一個事務
ROLLBACK? ? ? ? #事務回滾
COMMIT? ? ? ? #事務確認

(2)直接用 SET 來改變 MySQL 的自動提交模式

SET AUTOCOMMIT=0? ? ? ? #禁止自動提交
SET AUTOCOMMIT=1? ? ? ? #開啟自動提交

示例：

mysql> select * from users;    #查看表內容
Empty set (0.00 sec)mysql> insert into users values(1,'zhangsan','111');    #添加表內容
Query OK, 1 row affected (0.01 sec)mysql> select * from users;    #查看是否有內容
+------+-----------+-----------+
| id   | user_name | user_pass |
+------+-----------+-----------+
|    1 | zhangsan  | 111       |
+------+-----------+-----------+
1 row in set (0.00 sec)mysql> rollback;    #回滾
Query OK, 0 rows affected (0.00 sec)mysql> select * from users;		#查看
+------+-----------+-----------+
| id   | user_name | user_pass |
+------+-----------+-----------+
|    1 | zhangsan  | 111       |
+------+-----------+-----------+
1 row in set (0.00 sec)mysql> set autocommit=0;		#關掉自動提交
Query OK, 0 rows affected (0.00 sec)mysql> insert into users values(2,'lisi','111');
Query OK, 1 row affected (0.00 sec)mysql> rollback;
Query OK, 0 rows affected (0.00 sec)mysql> select * from users;
+------+-----------+-----------+
| id   | user_name | user_pass |
+------+-----------+-----------+
|    1 | zhangsan  | 111       |
+------+-----------+-----------+
1 row in set (0.00 sec)

mysql> begin;	#事務的開始
Query OK, 0 rows affected (0.00 sec)mysql> insert into users values(4,'wangwu','111');
Query OK, 1 row affected (0.00 sec)mysql> commit;    #事務的提交
Query OK, 0 rows affected (0.01 sec)mysql> select * from users;
+------+-----------+-----------+
| id   | user_name | user_pass |
+------+-----------+-----------+
|    1 | zhangsan  | 111       |
|    4 | wangwu    | 111       |
+------+-----------+-----------+
2 rows in set (0.00 sec)