索引

一. 索引的重要性

MySQL 的索引就是數(shù)據(jù)結(jié)構(gòu)，能夠?qū)Χ嗔械闹颠M行排序來更快的進行查詢數(shù)據(jù)。索引就像是一本書的目錄，通過目錄我們可以快速定位到數(shù)據(jù)所在的位置。無需掃描整張表來找數(shù)據(jù)。

雖然查詢速度變快了，但是與之而來的是讀寫操作增加了更多的IO，但是為了提高檢索速度是值得的。

二. 理解索引

要想知道為何添加索引之后可以更快的查詢數(shù)據(jù)，我們就得理解索引。

首先明確，MySQL的操作都是在內(nèi)存當中進行的，它的任務(wù)就是對數(shù)據(jù)進行CURD操作。那么數(shù)據(jù)是保存在磁盤的。MySQL與磁盤進行IO交互時通過一個個page進行的。page是內(nèi)存與磁盤交互的最小單位。page的目的在于一次性多加載，以達到減少IO的目的。相比于要多少加載多少可以有效減少IO次數(shù)。

那MySQL是怎么提升自己的查詢速度的呢？

通過索引數(shù)據(jù)結(jié)構(gòu)。MySQL采用B+樹的結(jié)構(gòu)，類似與多叉樹，根結(jié)點存儲page目錄用于定位所要信息的page處，葉子結(jié)點存儲數(shù)據(jù)信息。這些數(shù)據(jù)是通過索引進行排序的，都是有序的。

整個查找的辦法我們可以類比于找數(shù)字。例如我現(xiàn)在要找一個數(shù)字52，首先我通過樹結(jié)構(gòu)先找到50-60范圍的起始點50，然后從50向60進行遍歷找到52。

根結(jié)點存儲page頁的指針，page頁存儲著實際存儲數(shù)據(jù)的page指針，接著就是存儲數(shù)據(jù)的page。

三. 索引操作

1. 創(chuàng)建索引

創(chuàng)建索引有三種方式，第一種直接跟在列后面，第二種在最后面指定列，第三種創(chuàng)建完表后對指定列添加索引

主鍵索引案例：

唯一鍵索引案例：

普通索引案例：

2. 查詢索引

show  【索引】from【表】

案例：

3. 刪除索引

刪除主鍵索引

alter table【表】drop  primary  key；

刪除其他索引

alter  table  【表】drop  【索引名】

事務(wù)

一. 什么是事務(wù)

事務(wù)是數(shù)據(jù)庫操作的邏輯執(zhí)行單元，這些操作要么全部實現(xiàn)，要么全部失敗回滾。

為的就是保證數(shù)據(jù)的一致性和完整性。

事務(wù)必須滿足四個特性（ACID）

原子性

事務(wù)是一個整體，要么全部成功要么全部失敗回滾，例如轉(zhuǎn)賬時A賬戶扣錢和B賬戶加錢必須同時成功。

一致性

執(zhí)行前后，數(shù)據(jù)庫狀態(tài)要保持一致，如轉(zhuǎn)賬后兩個賬戶總金額不變。

隔離性

多個事務(wù)并發(fā)執(zhí)行，彼此間的操作不能互相干擾

持久性

事務(wù)一旦提交，結(jié)果就是永久性的，即使系統(tǒng)崩潰數(shù)據(jù)也不會丟失。

二. 事務(wù)提交方式

事務(wù)提交就是將事務(wù)執(zhí)行的操作永久保存到數(shù)據(jù)庫并且釋放鎖，主要有顯示提交和隱式提交

顯示提交

顯示提交是使用了 commit 進行手動提交事務(wù)

隱式提交

隱式提交涉及到MySQL的autocommit模式，當autocommit = on 時代表隱式模式開啟，每條SQL語句都會被自動提交。當 autocommit = off 時就默認為顯示提交

我們可以查看autocommit并且進行修改

show variables like ‘a(chǎn)utocommmit'
set autocommit = （1開 / 0關(guān)）

案例：

三. 事務(wù)操作方式

正常處理事務(wù)

通過begin開始事務(wù)，commit結(jié)束事務(wù)，savepoint【名字】可以暫存點，rollback【名字】可以進行回檔。

案例：

當我們開啟兩臺終端時，隔離級別為未讀提交，當我們在終端a插入數(shù)據(jù)但為commit，此時在終端b訪問表就無法查看到數(shù)據(jù)

案例：

當我們對事務(wù)進行commit后，此時中斷主機A，在主機B上查詢表可以發(fā)現(xiàn)依然能看到commit前插入的數(shù)據(jù)。說明commit將數(shù)據(jù)永久保存在數(shù)據(jù)庫中

案例：

關(guān)閉autocommit，當我們向主機A表中插入數(shù)據(jù)時，主機B也會同步更新表。當主機A在未commit下突然崩潰，表會自動進行回滾

四. 事務(wù)隔離級別

隔離性就是多個操作之間互不干擾的狀態(tài)。

事務(wù)的隔離等級分為四種：讀未提交，讀提交，可重復讀，串行化

查看與設(shè)置隔離性

查看全局隔離級別

SELECT @@global.transaction_isolation;

查看當前會話全局隔離級別

SELECT @@transaction_isolation;

當前會話只影響當前數(shù)據(jù)庫的隔離級別，全局會話會設(shè)置默認的新鏈接

案例:

設(shè)置當前會話隔離級別

set  【session/global】transaction  isolation  level  【read  uncommitted  |  read  committed  |  repeatable  read  |  serializable】

設(shè)置為全局或者當前會話，設(shè)置等級為四個

案例：

讀未提交

當我們使用讀未提交時，主機A和主機B同時begin開始事務(wù)，主機A更新數(shù)據(jù)庫一條數(shù)據(jù)，但是未commit，此時主機B訪問表可以實時查看到數(shù)據(jù)被更改

案例：

一個事務(wù)在執(zhí)行中，讀到另一個執(zhí)行中事務(wù)的更新但是未commit數(shù)據(jù)，這種現(xiàn)象叫做臟讀

讀提交

我們讓主機A和主機B同時開啟一項事務(wù)，主機A對列更新但未保存，此時主機B訪問該列拿到的是久值。主機B倘若對就值進行處理，此時主機A對該列進行commit，就會造成讀取到了不同的值

案例：

這種現(xiàn)象就是不可重讀讀

可重讀讀

同時開啟主機A和主機B的事務(wù)，主機A更改某列數(shù)據(jù)，此時主機B訪問表發(fā)現(xiàn)表仍然為原先舊數(shù)據(jù)；主機A進行commit保存數(shù)據(jù)，主機B再次訪問，發(fā)現(xiàn)仍然為原先數(shù)據(jù)；主機B進行commit保存數(shù)據(jù)，此時再進行訪問表，表才進行了更新。

案例：

倘若我們主機A更改列后不進行commit，此時主機B對該列進行修改，那么此時就會卡住，直到主機Acommit后主機B更改才能成功。

這也就導致我們插入數(shù)據(jù)不斷查詢的過程中值在不斷地變化，如同產(chǎn)生幻覺，這就是幻讀

串行化

同時開啟主機A和主機B，當主機A對表進行修改時，此時主機B訪問該表就會造成阻塞，直到主機A進行commit后，主機B才會顯示

案例：

串行化雖然說隔離級別最嚴格，但也伴隨著效率是其中最低下的那一種方式

隔離級別是一致性與性能的權(quán)衡，級別越高，一致性越好，但性能也會越差。

總結(jié)

到此這篇關(guān)于MySQL數(shù)據(jù)庫索引和事務(wù)的文章就介紹到這了,更多相關(guān)MySQL索引和事務(wù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論