目錄

一 MYSQL 索引介紹

1.索引概念

2.索引作用

3.索引的分類

3.1普通索引

3.2唯一索引

3.3組合索引（最左前綴）

3.4全文索引

4.3查看索引

4.4刪除索引

二 MYSQL事務

---

一：MYSQL索引介紹

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

1.索引概念

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

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

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

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

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

2.索引作用

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

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

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

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

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

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

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

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

3.索引的分類

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

(1.從物理存儲的角度來劃分,索引分為聚簇索引和非聚簇索引兩種,聚簇索是按照數據存放的物理位置為順序的,而非聚簇索引就不一樣了;聚簇索引能提高多行檢索的速度,非聚簇索引對于單行的檢索更快。

（2.從邏輯的角度來劃分,索引分為普通索引、唯一索引、主鍵索引、組合索引和全文索引。這些索引分類的具體解釋如下所示。

3.1普通索引

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

直接創建索引:

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

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

修改表結構添加索引：

創建表結構 同時創建索引

查看一下t1?

3.2唯一索引

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

創建唯一索引:

修改表街斗添加唯一索引

創建表同時創建唯一索引

3.3組合索引（最左前綴）

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

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

如果采用“select * from user where age=''AND name='‘AND sex =’’;”查詢方式,這條組合索引將無效化,所以一般在建立索引時,要先想好相應的查詢業務,盡量避免雖然有索引,但是使用不上的問題。

3.4全文索引

MySQL 從 3.23.23版開始支持全文索引和全文檢索。在MySQL5.6 版本以前FULLTEXT索引僅可用于MyISAM表,在5.6之后innodb 引擎也支持 FULLTEXT 索引;他們可以從 CHAR、VARCHAR 或 TEXT 列中作為CREATE TABLE 語句的一部分被創建,或是隨后使用 ALTER TABLECREATEINDEX 被添加。

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

創建表全文索引

4.2 創建索引原則依據

數據庫建立索引原則

確定針對該表的操作是大量的查詢操作還是大量的增刪改操作;

嘗試建立索引來幫助特定的查詢。檢查自己的 sql 語句,為那些頻繁在where 子句中出現的字段建立索引;

嘗試建立復合索引來進一步提高系統性能。修改復合索引將消耗更長時間,同時復合索引也占磁盤空間;

對于小型的表,建立索引可能會影響性能;

應該避免對具有較少值的字段進行索引;

避免選擇大型數據類型的列作為索引。

索引建立原則

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

在經常用作過濾器的字段上建立索引;

在 SQL 語句中經常進行 GROUP BY、ORDER BY 的字段上建立索引;

在不同值較少的字段上不必要建立索引,如性別字段;

對于經常存取的列避免建立索引;

用于聯接的列(主健/外健)上建立索引；

在經常存取的多個列上建立復合索引,但要注意復合索引的建立順序要按照使用的頻度來確定;

缺省情況下建立的是非簇集索引,但在以下情況下最好考慮簇集索引,如:含有限數目(不是很少)唯一的列;進行大范圍的查詢;充分的利用索引可以減少表掃描 I/0 的次數,有效的避免對整表的搜索。當然合理的索引要建立在對各種查詢的分析和預測中,也取決于 DBA 所設計的數據庫結構。

4.3查看索引

MySQL 數據表索引已經創建好了,那么如何才能查看剛剛創建的索引?或者怎么去查看表內已經存在的索引?有以下兩種查看當前索引的方式。

字段解析:
1.Table:表的名稱。
2.Non_unique:如果索引不能包括重復詞,則為0;如果可以,則為1。
3.Key_name:索引的名稱。
4.Seq_in_index:索引中的列序號,從1開始。
5.Column_name:列名稱。
6.Collation:列以什么方式存儲在索引中。在 MySQL中,有值‘A’(升序)或 NULL(無分類)。
7.Cardinality:索引中唯一值數目的估計值。通過運行 ANALYZE TABLE 或myisamchk -a 可以新。基數根據被存儲為整數的統計數據來計數,所以即使對于小型表,該值也沒有必要是精確的。基數大,當進行聯合時,MySQL 使用該索引的機會就越大。
8.Sub_part:如果列只是被部分地編入索引,則為被編入索引的字符的數目。如果整列被編入索引,則為NULL。
9.Packed:指示關鍵字如何被壓縮。如果沒有被壓縮,則為 NULL。

10.Null:如果列含有 NULL,則含有 YES。如果沒有,則該列含有 NO。
11.Index_type:用過的索引方法(BTREE,FULLTEXT, HASH, RTREE)。

12.Comment:備注。

4.4刪除索引

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

二 MYSQL事務

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

在MySQL 中只有使用了Innodb 數據庫引擎的數據庫或表才支持事務。

事務處理可以用來維護數據庫的完整性,保證成批的SQL 語句要么全部執行,要么全部不執行。

事務用來管理 insert,update,delete 語句。

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

原子性:一個事務(transaction)中的所有操作,要么全部完成,要么全部不完成,不會結束在中間某個環節。事務在執行過程中發生錯誤,會被回滾(Rollback)到事務開始前的狀態,就像這個事務從來沒有執行過一樣;

一致性:在事務開始之前和事務結束以后,數據庫的完整性沒有被破壞。這表示寫入的資料必須完全符合所有的預設規則,這包含資料的精確度、串聯性以及后續數據庫可以自發性地完成預定的工作;

隔離性:數據庫允許多個并發事務同時對其數據進行讀寫和修改的能力,隔離性可以防止多個事務并發執行時由于交叉執行而導致數據的不一致。事務離分為不同級別,包括讀未提交(Read uncommitted)、讀提交(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=O 禁止自動提交
SET AUTOCOMMIT=1 開啟自動提交