MySQL數(shù)據(jù)庫索引、事務(wù)及其他詳解

下文內(nèi)容主要給大家?guī)?a title="MySQL" target="_blank" href="http://kemok4.com/mysql/">MySQL數(shù)據(jù)庫索引、事務(wù)及其他詳解，這里所講到的知識，與書籍略有不同，都是億速云專業(yè)技術(shù)人員在與用戶接觸過程中，總結(jié)出來的，具有一定的經(jīng)驗分享價值，希望給廣大讀者帶來幫助。

索引的概念

數(shù)據(jù)庫中的索引與書籍中的目錄類似：

1.-本書中，無須閱讀整本書,利用目錄就可以快速查找所需信息
2.書中的目錄是一個詞語列表，其中注明了包含各個詞的頁碼

數(shù)據(jù)庫索引：

1.在數(shù)據(jù)庫中，索弓|使數(shù)據(jù)庫程序無須對整個表進(jìn)行掃描，就可以在其中找到所需數(shù)據(jù)
2.數(shù)據(jù)庫中的索弓|是某個表中一-列或者若干列值的集合，以及物理標(biāo)識這些值的數(shù)據(jù)頁的邏輯指針清單

索引的作用：

1.設(shè)置了合適的索引之后，數(shù)據(jù)庫利用各種快速的定位技術(shù)，能夠大大加快查詢速率
2.特別是當(dāng)表很大時，或者查詢涉及到多個表時，使用索引可使查詢加快成干倍
3.可以降低數(shù)據(jù)庫的IO成本，并且索引還可以降低數(shù)據(jù)庫的排序成本
4.通過創(chuàng)建唯一性索引保證數(shù)據(jù)表數(shù)據(jù)的唯一性
5.可以加快表與表之間的連接
6.在使用分組和排序時，可大大減少分組和排序時間

索引的分類

普通索引：

是最基本的索引|類型，而且它沒有唯一-性之類的限制

唯一性索引：

這種索弓|和前面的“普通索引”基本相同，但有一個區(qū)別:索引列的所有值都只能出現(xiàn)一次，即必須唯一

主鍵：

主鍵是一種唯一性索引，但它必須指定為”PRIMARY KEY"

全文索引：

MySQL從3.23.23版開始支持全文索引和全文檢索。在MySQL中全文索引的索引類型為FULLTEXT,全文索引可以在VARCHAR或者TEXT類型的列上創(chuàng)建

單列索引與多列索引：

索引可以是單列上創(chuàng)建的索引，也可以是在多列上創(chuàng)建的索引

創(chuàng)建索引的原則依據(jù)

1.表的主鍵、外鍵必須有索引
2.數(shù)據(jù)量超過300行的表應(yīng)該有索引
3.經(jīng)常與其他表進(jìn)行連接的表，在連接字段.上應(yīng)該建立索引
4.唯一性太差的字段不適合建立索引
5.更新太頻繁地字段不適合創(chuàng)建索引6.經(jīng)常出現(xiàn)在Where子句中的字段，特別是大表的字段,應(yīng)該建立索引
7.索引應(yīng)該建在選擇性高的字段上
8.索引應(yīng)該建在小字段上，對于大的文本字段甚至超長字段，不要建索引

事務(wù)

事務(wù)的概念

1.事務(wù)是一種機(jī)制、一個操作序列，包含了一組數(shù)據(jù)庫操作命令，并且把所有的命令作為- -個整體-起向系統(tǒng)提交或撤銷操作請求，即這一組數(shù)據(jù)庫命令要么都執(zhí)行,要么都不執(zhí)行
2.事務(wù)是一個不可分割的工作邏輯單元，在數(shù)據(jù)庫系統(tǒng)上執(zhí)行并發(fā)操作時，事務(wù)是最小的控制單元
3.適用于多用戶同時操作的數(shù)據(jù)庫系統(tǒng)的場景，如銀行、保險公司及證券交易系統(tǒng)等等
4.通過事務(wù)的整體性以保證數(shù)據(jù)的一致性

事務(wù)的ACID特點

1.事務(wù)是一個完整的操作，事務(wù)的各元素是不可分的(原子的)
2.事務(wù)中的所有元素必須作為一個整體提交或回滾
3.如果事務(wù)中的任何元素失敗，則整個事務(wù)將失敗

務(wù)完成時，數(shù)據(jù)必須處于一 致狀態(tài):在事務(wù)開始之前，數(shù)據(jù)庫中存儲的數(shù)據(jù)處于一致狀態(tài);在正在進(jìn)行的事務(wù)中，數(shù)據(jù)可能處于不一致的狀態(tài);當(dāng)事務(wù)成功完成時，數(shù)據(jù)必須再次回到已知的一致狀態(tài)

1.對數(shù)據(jù)進(jìn)行修改的所有并發(fā)事務(wù)是彼此隔離的，這表明事務(wù)必須是獨立的，它不應(yīng)以任何方式依賴于或影響其他事務(wù)
2.修改數(shù)據(jù)的事務(wù)可以在另一個使用相同數(shù)據(jù)的事務(wù)開始之前訪問這些數(shù)據(jù)，或者在另一個使用相同數(shù)據(jù)的事務(wù)結(jié)束之后訪問這些數(shù)據(jù)

1.事務(wù)持久性指不管系統(tǒng)是否發(fā)生故障，事務(wù)處理的結(jié)果都是永久的
2.一旦事務(wù)被提交，事務(wù)的效果會被永久地保留在數(shù)據(jù)庫中

事務(wù)的操作

1.默認(rèn)情況下MySQL的事務(wù)是自動提交的，當(dāng)sql語句提交時事務(wù)便自動提交

2.手動對事務(wù)進(jìn)行控制的方法：

①事務(wù)處理命令控制
②使用set設(shè)置事務(wù)處理方式

3.事務(wù)處理命令控制事務(wù)：

①begin：開始一個事務(wù)
②commit：提交一個事務(wù)
②rollback：回滾一-個事務(wù)

4.使用set命令進(jìn)行控制：

①set autocommit=0：禁止自動提交
②set autocommit=1：開啟自動提交

存儲引擎概念介紹

MyISAM（適合讀）
InnoDB（適合寫）

MyISAM的介紹

①ISAM執(zhí)行讀取操作的速度很快,
②它不支持事務(wù)處理
③而且不占用大量的內(nèi)存和存儲資源
④不能夠容錯

表定義文件
表數(shù)據(jù)存儲文件
表索引文件

MyISAM適用的生產(chǎn)場景舉例：

1.公司業(yè)務(wù)不需要事務(wù)的支持
2.一般單方面讀取數(shù)據(jù)比較多的業(yè)務(wù)，或單方面寫入數(shù)據(jù)比較多的業(yè)務(wù)
3.MyISAM存儲引擎數(shù)據(jù)讀寫都比較頻繁場景不適合
4.使用讀寫并發(fā)訪問相對較低的業(yè)務(wù)
5.數(shù)據(jù)修改相對較少的業(yè)務(wù)
6.對數(shù)據(jù)業(yè)務(wù)一致性要求不是非常高的業(yè)務(wù)
7.服務(wù)器硬件資源相對比較差

InnoDB特點介紹

1.支持事務(wù):支持4個事務(wù)隔離級別
2.行級鎖定，但是全表掃描仍然會是表級鎖定
3.讀寫阻塞與事務(wù)隔離級別相關(guān)
4.具有非常高效的緩存特性:能緩存索引，也能緩存數(shù)據(jù)
5.表與主鍵以簇的方式存儲
6.支持分區(qū)、表空間，類似oracle數(shù)據(jù)庫
7.支持外鍵約束，5.5以前不支持全文索引，5.5版本以后支持全文索引
8.對硬件資源要求還是比較高的場合

InnoDB適用生產(chǎn)場景分析

1.業(yè)務(wù)需要事務(wù)的支持
2.行級鎖定對高并發(fā)有很好的適應(yīng)能力，但需確保查詢是通過索引來完成
3.業(yè)務(wù)數(shù)據(jù)更新較為頻繁的場景， 如:論壇，微博等
4.業(yè)務(wù)數(shù)據(jù)一致性要求較高，例如:銀行業(yè)務(wù)
5.硬件設(shè)備內(nèi)存較大， 利用Innodb較好的緩存能力來提高內(nèi)存利用率，減少磁盤IO的壓力

配置存儲引擎

1.在企業(yè)中選擇好合適的存儲引擎之后，就可以進(jìn)行修改了

2.修改步驟：

①查看數(shù)據(jù)庫可配置的存儲引擎
②查看表正在使用的存儲引擎
③配置存儲引擎為所選擇的類型

3.使用show engines查看系統(tǒng)支持的存儲引擎查看表使用的存儲引擎

方法1: show table status from庫名where name='表名;
方法2: show create table表名

對于以上關(guān)于MySQL數(shù)據(jù)庫索引、事務(wù)及其他詳解，如果大家還有更多需要了解的可以持續(xù)關(guān)注我們億速云的行業(yè)推新，如需獲取專業(yè)解答，可在官網(wǎng)聯(lián)系售前售后的，希望該文章可給大家?guī)硪欢ǖ闹R更新。