mysql的索引和事務詳細解讀

這篇文章帶給大家的內容是關於mysql的索引和事務詳細解讀，有一定的參考價值，有需要的朋友可以參考一下，希望對你有幫助。

一、索引是做什麼的?

很多時候，當你的應用程式進行SQL查詢速度很慢時，應該想想是否可以建索引。

大多數MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)在B樹中儲存​​。只是空間列類型的索引使用R-樹，並且MEMORY表也支援hash索引。

索引是一個排序的列表，在這個列表中儲存著索引的值和包含這個值的資料所在行的物理位址，在資料十分龐大的時候，索引可以大大加快查詢的速度，這是因為使用索引後可以不用掃描全表來定位某行的數據，而是先透過索引表找到該行資料對應的實體位址然後存取對應的資料。

二、索引的優缺點

優點：可以快速檢索，減少I/O次數，加快檢索速度；根據索引分組和排序，可以加快分組和排序；

劣勢：索引本身也是表，因此會佔用儲存空間，一般來說，索引表佔用的空間的資料表的1.5倍；索引表的維護和建立需要時間成本，這個成本隨著資料量增加而增加；建構索引會降低資料表的修改操作（刪除，添加，修改）的效率，因為在修改資料表的同時還需要修改索引表；

三、索引的分類

常見的索引類型有：主鍵索引、唯一索引、普通索引、全文索引、組合索引

1、主鍵索引：即主索引，根據主鍵pk_clolum（length）建立索引，不允許重複，不允許空值；

ALTER TABLE 'table_name' ADD PRIMARY KEY('id')；

2、 唯一索引：用來建立索引的列的值必須是唯一的，允許空值

ALTER TABLE 'table_name' ADD UNIQUE('email')；

3、普通索引：用表中的普通列構建的索引，沒有任何限制

ALTER TABLE 'table_name' ADD INDEX index_name('description')；

4、全文索引：用大文本物件的列建構的索引（下一部分會講解）

ALTER TABLE 'table_name' ADD FULLTEXT('content')；

5、組合索引：用多個欄位組合建構的索引，這多個欄位中的值不允許有空值

ALTER TABLE 'table_name' ADD INDEX index_name('col1','col2','col3')；

遵循「最左前綴」原則，最常用作為檢索或排序的列放在最左，依序遞減，組合索引相當於建立了col1,col1col2,col1col2col3三個索引，而col2或col3是不能使用索引的。

在使用組合索引的時候可能因為列名長度過長而導致索引的key太大，導致效率降低，在允許的情況下，可以只取col1和col2的前幾個字元作為索引

ALTER TABLE 'table_name' ADD INDEX index_name(col1(4),col2（3))；

表示使用col1的前4個字元和col2的前3個字元作為索引

四、索引的實作原理

MySQL支持許多儲存引擎，而各種儲存引擎對索引的支援也各不相同，因此MySQL資料庫支援多種索引類型，如BTree索引，B Tree索引，雜湊索引，全文索引等等，

# 1.雜湊索引：

只有memory（記憶體）儲存引擎支援雜湊索引，雜湊索引用索引列的值計算該值的hashCode，然後在hashCode對應的位置存執該值所在行資料的物理位置，因為使用雜湊演算法，因此存取速度非常快，但是一個值只能對應一個hashCode，而且是雜湊的分佈方式，因此雜湊索引不支援範圍查找和排序的功能。

2、全文索引：

FULLTEXT（全文）索引，僅可用於MyISAM和InnoDB，針對較大的數據，產生全文索引非常的消耗時間和空間。對於文字的大對象，或較大的CHAR類型的數據，如果使用普通索引，那麼匹配文字前幾個字元還是可行的，但是想要匹配文字中間的幾個單詞，那麼就要使用LIKE %word%要匹配，這樣需要很長的時間來處理，回應時間會大大增加，這種情況，就可使用時FULLTEXT索引了，在產生FULLTEXT索引時，會為文字產生一份單字的清單，在索引時及根據這個單字的清單來索引。 FULLTEXT可以在創建表的時候創建，也可以在需要的時候用ALTER或CREATE INDEX來添加：

//创建表的时候添加FULLTEXT索引
CTREATE TABLE my_table(
id INT(10) PRIMARY KEY,
name VARCHAR(10) NOT NULL,
my_text text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
FULLTEXT(my_text));

//创建表以后，在需要的时候添加FULLTEXT索引
ALTER my_table ADD FULLTEXT ft_index(my_text);
CREATE INDEX ft_index ON my_table(my_text);

對於較大的資料集，把資料加到一個沒有FULLTEXT索引的表，然後再加入FULLTEXT索引的速度比把資料加到一個已經有FULLTEXT索引的表快。

MySQL自帶的全文索引只能用於MyISAM儲存引擎，如果是其它資料引擎，那麼全文索引不會生效。

在MySQL中，全文索引支隊英文有用，目前對中文還不支援。

在MySQL中，如果檢索的字串太短則無法檢索得到預期的結果，檢索的字串長度至少為4字節，此外，如果檢索的字元包括停止詞，那麼停止詞會被忽略。

3、BTree索引和B Tree索引

BTree索引

BTree是平衡搜尋多叉樹，設樹的度為d（d>1），高度為h，那麼BTree要滿足以一下條件：

每個葉子結點的高度一樣，等於h；

每個非葉子結點由n-1個key和n個指針point組成，其中d

葉子結點指標都為null；

非葉子結點的key都是[key,data]二元組，其中key表示作為索引的鍵，data為鍵值所在行的資料；

BTree的結構如下：

在BTree的機構下，就可以使用二分查找的查找方式，查找複雜度為h*log(n)，一般來說樹的高度是很小的，一般為3左右，因此BTree是一個非常高效的查找結構。

B Tree索引

B Tree是BTree的變種，設d為樹的度數，h為樹的高度，B Tree和BTree的不同主要在於：

B Tree中的非葉子結點不儲存數據，只儲存鍵值；

B Tree的葉子結點沒有指針，所有鍵值都會出現在葉子結點上，且key儲存的鍵值對應的資料的物理位址；

B Tree的結構如下：

#一般來說B Tree比BTree更適合實現外存的索引結構，因為儲存引擎的設計專家巧妙的利用了外存（磁碟）的儲存結構，即磁碟的一個磁區是整數倍的page（頁），頁是儲存中的一個單位，通常默認為4K，因此索引結構的節點被設計為一個頁的大小，然後利用外存的「預先讀取」原則，每次讀取的時候，把整個節點的資料讀取到記憶體中，然後在記憶體中查找，已知記憶體的讀取速度是外存讀取I/O速度的幾百倍，那麼提升查找速度的關鍵就在於盡可能少的磁碟I/O，那麼可以知道，每個節點中的key個數越多，那麼樹的高度越小，需要I/O的次數越少，因此一般來說B Tree比BTree更快，因為B Tree的非葉節點中不儲存data，就可以儲存更多的key。

帶有順序索引的B TREE

很多儲存引擎在B T​​ree的基礎上進行了優化，添加了指向相鄰葉節點的指針，形成了帶有順序訪問指針的B Tree，這樣做是為了提高區間查找的效率，只要找到第一個值那麼就可以順序的查找後面的值。

B Tree的結構如下：

分析了MySQL的索引結構的實作原理，然後我們來看看具體的儲存引擎怎麼實現索引結構的，MySQL中最常見的兩種儲存引擎分別是MyISAM和InnoDB，分別實作了非聚集索引和叢集索引。

首先要介紹幾個概念，在索引的分類中，我們可以按照索引的鍵是否為主鍵來分為“主索引”和“輔助索引”，使用主鍵鍵值建立的索引稱為“主索引”，其它的稱為“輔助索引”。因此主索引只能有一個，輔助索引可以有很多個。

MyISAM－非叢集索引

MyISAM儲存引擎採用的是非叢集索引，非叢集索引的主索引和輔助索引幾乎是一樣的，只是主索引不允許重複，不允許空值，他們的葉子結點的key都儲存指向鍵值對應的資料的物理位址。

非聚集索引的資料表和索引表是分開儲存的。

非聚集索引中的資料是根據資料的插入順序保存。因此非聚集索引更適合單一資料的查詢。插入順序不受鍵值影響。

只有在MyISAM中才能使用FULLTEXT索引。

最開始我一直不懂既然非聚集索引的主索引和輔助索引指向相同的內容，為什麼還要輔助索引這個東西呢，後來才明白索引不就是用來查詢的嗎，用在那些地方呢，不就是WHERE和ORDER BY 語句後面嗎，那麼如果查詢的條件不是主鍵怎麼辦呢，這個時候就需要輔助索引了。

InnoDB－叢集索引

叢集索引的主索引的葉子結點儲存的是鍵值對應的資料本身，輔助索引的葉子結點儲存的是鍵值對應的數據的主鍵鍵值。因此主鍵的值長度越小越好，類型越簡單越好。

叢集索引的資料和主鍵索引儲存在一起。

叢集索引的資料是根據主鍵的順序保存。因此適合按主鍵索引的區間查找，可以有較少的磁碟I/O，加快查詢速度。但也是因為這個原因，叢集索引的插入順序最好按照主鍵單調的順序插入，否則會頻繁的引起頁分裂，嚴重影響效能。

在InnoDB中，如果只需要尋找索引的列，就盡量不要加入其它的列，這樣會提高查詢效率。

使用主索引的时候，更适合使用聚簇索引，因为聚簇索引只需要查找一次，而非聚簇索引在查到数据的地址后，还要进行一次I/O查找数据。

因为聚簇辅助索引存储的是主键的键值，因此可以在数据行移动或者页分裂的时候降低委会成本，因为这时不用维护辅助索引。但是辅助索引会占用更多的空间。

聚簇索引在插入新数据的时候比非聚簇索引慢很多，因为插入新数据时需要减压主键是否重复，这需要遍历主索引的所有叶节点，而非聚簇索引的叶节点保存的是数据地址，占用空间少，因此分布集中，查询的时候I/O更少，但聚簇索引的主索引中存储的是数据本身，数据占用空间大，分布范围更大，可能占用好多的扇区，因此需要更多次I/O才能遍历完毕。

下图可以形象的说明聚簇索引和非聚簇索引的区别

五、索引的使用策略

什么时候要使用索引？

主键自动建立唯一索引；

经常作为查询条件在WHERE或者ORDER BY 语句中出现的列要建立索引；

作为排序的列要建立索引；

查询中与其他表关联的字段，外键关系建立索引

高并发条件下倾向组合索引；

什么时候不要使用索引？

经常增删改的列不要建立索引；

有大量重复的列不建立索引；

表记录太少不要建立索引；

在组合索引中不能有列的值为NULL，如果有，那么这一列对组合索引就是无效的；

在一个SELECT语句中，索引只能使用一次，如果在WHERE中使用了，那么在ORDER BY中就不要用了；

LIKE操作中，'%aaa%'不会使用索引，也就是索引会失效，但是‘aaa%’可以使用索引；

在索引的列上使用表达式或者函数会使索引失效，例如：select from users where YEAR(adddate) from users where adddate

在查询条件中使用正则表达式时，只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。

在查询条件中使用会导致索引失效。

在查询条件中使用IS NULL会导致索引失效。

在查询条件中使用OR连接多个条件会导致索引失效，这时应该改为两次查询，然后用UNION ALL连接起来。

尽量不要包括多列排序，如果一定要，最好为这队列构建组合索引；

只有当数据库里已经有了足够多的测试数据时，它的性能测试结果才有实际参考价值。如果在测试数据库里只有几百条数据记录，它们往往在执行完第一条查询命令之后就被全部加载到内存里，这将使后续的查询命令都执行得非常快--不管有没有使用索引。只有当数据库里的记录超过了1000条、数据总量也超过了MySQL服务器上的内存总量时，数据库的性能测试结果才有意义。

六、索引的优化

1、最左前缀

索引的最左前缀和和B+Tree中的“最左前缀原理”有关，举例来说就是如果设置了组合索引那么以下3中情况可以使用索引：col1，，，其它的列，比如，，col2，col3等等都是不能使用索引的。

根据最左前缀原则，我们一般把排序分组频率最高的列放在最左边，以此类推。

2、带索引的模糊查询优化

在上面已经提到，使用LIKE进行模糊查询的时候，'%aaa%'不会使用索引，也就是索引会失效。如果是这种情况，只能使用全文索引来进行优化（上文有讲到）。

为检索的条件构建全文索引，然后使用

SELECT * FROM tablename MATCH(index_colum) ANGAINST(‘word’);

事务介绍

首先，什么是事务？事务就是一段sql 语句的批处理，但是这个批处理是一个atom（原子），不可分割，要么都执行，要么回滚（rollback）都不执行。

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

• 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。

• 交易處理可以用來維護資料庫的完整性，保證成批的 SQL 語句要麼全部執行，要麼全部不執行。

• 交易用來管理insert,update,delete 語句

#一般來說，交易是必須滿足4個條件（ACID）： Atomicity （原子性）、Consistency（穩定性）、Isolation（隔離性）、Durability（可靠性）

• 1、事務的原子性：一組事務，要麼成功；要麼撤回。

• 2、穩定性 ：有非法資料（外鍵約束之類），交易撤回。

• 3、隔離性：交易獨立運作。一個事務處理後的結果，影響了其他事務，那麼其他事務會撤回。事務的100%隔離，需要犧牲速度。

• 4、可靠性：軟、硬體崩潰後，InnoDB資料表驅動程式會利用日誌檔案重構修改。可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit 選項 決定何時吧交易要儲存到日誌中。

交易並發並不會進行交易隔離造成的髒讀、幻讀、不可重複讀取

• #髒讀：事務A讀到未提交事務B修改的數據，如果此時事務B中途執行失敗回滾，那麼此時事務A讀取到的就是髒數據。例如事務A對money進行修改，此時事務B讀取到事務A的更新結果，但是如果後面事務A回滾，那麼事務B讀取到的就是髒數據了。

• 無法重複讀取：在同一個交易中，對同一份資料讀取的結果不一致。事務A在事務B對資料更新前進行讀取，然後事務B更新提交，事務A再次讀取，這時候兩次讀取的資料不同。

• 幻讀：（同一個交易中，同一個查詢多次傳回的結果不一樣。交易B查詢表的記錄數，然後交易A對錶插入一筆記錄，接著事務B再次查詢發現記錄數不同。注意這個解釋是不正確，網路上有很多這樣的解釋，包括我認為比較權威的專家，但是經過實驗發現並不正確。所以這是需要注意的)。可以做這樣一個實驗，事務A查詢記錄數，事務B插入一筆記錄（主鍵值為6），提交，然後事務A查詢記錄數，發現記錄數沒有改變，但是此時插入一條主鍵值為6的記錄發現衝突了，感覺像出現了幻覺。

區別

1、髒讀和不可重複讀取：髒讀是事務讀取了尚未提交交易的更新資料。不可重複讀是同一個事務中，幾次讀取的資料不同。

2、不可重複讀和幻讀的區別：都是在同一個事務中，前者是幾次讀取資料不同，後者是幾次讀取資料整體不同。

隔離等級

• 隔離級別改變影響鎖的週期

#mysql支援上面4種隔離級別，預設為可重複讀

#MySQL有三種鎖定的層級：頁級、表級、行級。

　　MyISAM和MEMORY儲存引擎採用的是表格級鎖定（table-level locking）；

　　BDB儲存引擎採用的是頁面鎖定（page-level locking），但也支援表級鎖；

　　InnoDB儲存引擎既支援行級鎖定（row-level locking），也支援表級鎖定，但預設為

採用行級鎖定。

MySQL這3種鎖的特性可大致歸納如下： 1、表級鎖：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖衝突的機率最高,並發度最低。表級鎖讓多執行緒可以同時從資料表中讀取數據，但是如果另一個執行緒想要寫資料的話，就必須先取得排他存取（預設加排他表鎖）；（共享讀鎖（Table Read Lock ）更新資料時，必須等到更新完成了，其他執行緒才能存取（讀）這個表。（獨佔寫鎖（Table Write Lock））######2、行級鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖衝突的機率最低,並發度也最高。###

3、页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

原则上数据表有一个读锁时，其它进程无法对此表进行更新操作，但在一定条件下，MyISAM表也支持查询和插入操作的并发进行。

一般MyISAM引擎的表也支持查询和插入操作的并发进行（原则上数据表有一个读锁时，其它进程无法对此表进行更新操作）

MyISAM引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别可以为0、1或2：

a、concurrent_insert为0，不允许并发插入。 　　　　
b、concurrent_insert为1，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。 　　　　
c、concurrent_insert为2，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。

如果有读写请求同时进行的话，MYSQL将会优先执行写操作。这样MyISAM表在进行大量的更新操作时（特别是更新的字段中存在索引的情况下），会造成查询操作很难获得读锁，从而导致查询阻塞。

我们还可以调整MyISAM读写的优先级别：

　　a、通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。
　　b、通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。
　　c、通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。

MyISAM使用的是 flock 类的函数，直接就是对整个文件进行锁定（叫做文件锁定），MyISAM的数据表是按照单个文件存储的，可以针对单个表文件进行锁定；

InnoDB使用的是 fcntl 类的函数，可以对文件中局部数据进行锁定（叫做行锁定），InnoDB是一整个文件，把索引、数据、结构全部保存在 ibdata 文件里，所以必须用行锁定。

事物控制语句：

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 来改变 My

SQL 的自动提交模式:

SET AUTOCOMMIT=0 禁止自动提交     
SET AUTOCOMMIT=1 开启自动提交

注意点

1、如果事务中sql正确运行，后面没有commit，结果是不会更新到数据库的，所以需要手动添加commit。

2、如果事务中部分sql语句出现错误，那么错误语句后面不会执行。而我们可能会认为正确操作会回滚撤销，但是实际上并没有撤销正确的操作，此时如果再无错情况下进行一次commit，之前的正确操作会生效，数据库会进行更新。

以上是mysql的索引和事務詳細解讀的詳細內容。更多資訊請關注PHP中文網其他相關文章！