MySQL 數(shù)據(jù)庫 索引和事務
1. 索引
1.1 概念
- 索引是為了加速對表中數(shù)據(jù)行的檢索而創(chuàng)建的一種分散的存儲結構。索引是針對表而建立的,它是由數(shù)據(jù)頁面以外的索引頁面組成的,每個索引頁面中的行都會含有邏輯指針,以便加速檢索物理數(shù)據(jù)。
- 在數(shù)據(jù)庫關系圖中,可以在選定表的“索引/鍵”屬性頁中創(chuàng)建、編輯或刪除每個索引類型。當保存索引所附加到的表,或保存該表所在的關系圖時,索引將保存在數(shù)據(jù)庫中。
通俗的講,索引與數(shù)據(jù)庫中的表和數(shù)據(jù)的關系就類似于書架上的圖書(表)、書籍內(nèi)容(數(shù)據(jù))和書籍目錄(索引)的關系
1.2 作用
在數(shù)據(jù)庫系統(tǒng)中建立索引主要有以下作用:
- 快速取數(shù)據(jù)
- 保證數(shù)據(jù)記錄的唯一性
- 實現(xiàn)表與表之間的參照完整性
在使用 order by、
group by 子句進行數(shù)據(jù)檢索時,利用索引可以減少排序和分組的時間
1.3 索引的原理
1.3.1 減少磁盤的訪問次數(shù)是構建索引的核心思想
索引的目的是方便進行查詢。
MySQL 的查詢主要是 select
,select
基本執(zhí)行的過程包括遍歷表、依次取出每個記錄、根據(jù) where 字句的條件進行篩選
由于 MySQL 是把數(shù)據(jù)儲存在硬盤上,因此進行查詢時,每次取出記錄都意味著要訪問硬盤
而 IO 設備對硬盤的訪問效率遠低于對內(nèi)存的訪問,因此減少磁盤的訪問次數(shù)就可以提高查詢的效率,也就是構建索引的核心思路
1.3.2 B+ 樹適用實現(xiàn)索引的底層
減少對數(shù)據(jù)的訪問次數(shù)就是實現(xiàn)索引時的重要思想,接下來將會對幾種數(shù)據(jù)結構進行分析,以找到更適合的實現(xiàn)索引的數(shù)據(jù)結構
二叉搜索樹:
由于二叉搜索樹可能是一個單支樹,時間復雜度為 O(N)
AVL 樹:
- AVL 樹本質是一個二叉平衡搜索樹,是對二叉搜索樹的一個改進,它會保證了左右子樹的高度差不超過1,即不會存在單支樹的結構,查找時間復雜度為 O(logN)
- 因為需要滿足左右子樹高度差不超過1的條件,所以插入或者刪除操作會破壞 AVL 樹的結構。因此需要隨時對樹進行調整。雖然查詢效率得到了滿足,但是降低了插入和刪除操作的效率,插入刪除時間復雜度為 O(logN)
紅黑樹:
- 紅黑樹本質是一個放松了規(guī)則的 AVL 樹,即不要強制左右子樹高度差不超過1,會降低要求,以此來保證插入和刪除操作的效率。
- 整體和 AVL 樹差異不大,查詢插入刪除的時間復雜度為 O(logN)
哈希表:
- 哈希表可以做到查詢、插入、刪除的時間復雜度都是 O(1)
- 但是哈希表的一個關鍵是必須要比較相等,但是形如大于、小于之類的條件無法做到,這就和實際查詢的情況不符合了
到這里為止,好像只有 AVL 樹或者紅黑樹更加適合用作 MySQL 的索引的實現(xiàn),而這兩個數(shù)據(jù)結構的查找效率直接是由樹的高度決定的,因此數(shù)據(jù)增多的話,樹的高度也會增加。
為了進一步優(yōu)化,就可以使用 N 叉搜索樹來降低樹的高度,即減少磁盤 IO,以提高查找效率
B 樹:
B 樹是 N 叉搜索樹的一種
B 樹示例結構:
用在索引中,每個結點都表示一個記錄
B 樹的特點:
- 每個結點可能包含 N 個子樹
- 每個結點上都可能存在多個值
- 左子樹的值都小于根節(jié)點的對應值,右子樹的值都大于根節(jié)點的對應值
B+ 樹:
B+ 樹是一種特殊的 N 叉搜索樹,是 B 樹的改進版
B+ 樹示例結構:
?B+ 樹相對于 B 樹的改進:
- 葉子節(jié)點存儲每行記錄,非葉子節(jié)點只要存儲每行的索引值即可
- 非葉子節(jié)點的值存在重復,使得葉子結點這一層是完整的數(shù)據(jù)集合
- 可以通過類似于鏈表的方式,把所有的葉子節(jié)點連接起來
B+ 樹的優(yōu)勢:
- 善于進行范圍查找
- 由于所有的查詢都是落在葉子結點上,故查詢的速度是比較穩(wěn)定的
- 由于葉子結點是數(shù)據(jù)的全集,因此就可以把葉子結點存到硬盤上,非葉子結點直接存到內(nèi)存中,大大降低了讀取硬盤的次數(shù)
1.4 適用場景
- 查找的次數(shù)比較多,插入刪除的次數(shù)較少適合用索引
- 由于索引本身也占據(jù)一定空間,如果磁盤緊張就不太適合用索引
- 索引是指定某個列來建立的,當某列的區(qū)分度比較大的時候,適合用索引,例如自增主鍵
1.5 使用語句
補充:
創(chuàng)建主鍵約束(primary key
)、唯一約束(unique)、外鍵約束(foreign key)時,會自動創(chuàng)建對應列的索引
1.5.1 查看索引
語法:
show index from 表名;
示例:
1.5.2 創(chuàng)建索引
語法:
create index 索引名 on 表名(字段名);
示例:
1.5.3 刪除索引
語法:
drop index 索引名 on 表名;
示例:
注意:
主索引不能刪除,刪除會報錯
2. 事務
2.1 概念
事物:是屬于計算機中一個很廣泛的概念,一般是指要做的或所做的事情。在關系數(shù)據(jù)庫中,一個事務可以是一條 SQL 語句或者一組 SQL 語句或整個程序。
通俗的講,比如銀行轉賬的操作,A 轉給 B 500元,那么這個操作其實包含了 A 賬戶余額減少500元和 B 賬戶余額增加500元兩個操作。
事物就相當于將這一連串的動作給打包,使其成為一個整體,要么全都不做,要么全都做完
2.2 為什么使用事務
用上述銀行轉賬的例子為例,假設 A 賬戶減少500元的操作成功了,但 B 賬戶增加500元的賬戶沒有成功,那么這個轉賬的操作是失敗的。
事物的核心特點就是: 把一系列操作給打包到一起,構成一個整體,要么全都做完,要么一個都不做。
全都不做是指: 如果某個操作失敗了,那么就會將此時的中間狀態(tài)偷偷還原回去
因此使用事物的話就可以保證,某一系列的操作,不會只完成其中一部分,它要么完全完成,要么都沒有完成
2.3 四大屬性
事務是恢復和并發(fā)控制的基本單位,它具有四個屬性:原子性、一致性、持久性、隔離性
事物的核心是原子性
2.3.1 原子性
概念:
一個事務是一個不可分割的工作單位,事務中包括的操作要么都做,要么都不做。
事物的核心是原子性,原子性的核心是回退為中間狀態(tài),回退為中間狀態(tài)核心就是回滾,回滾的核心就是記住每步的操作
2.3.2 一致性
概念:
事務必須是使數(shù)據(jù)庫從一個一致性狀態(tài)變到另一個一致性狀態(tài)。一致性與原子性是密切相關的。
執(zhí)行事物之前和執(zhí)行事物完成后,當前表里的數(shù)據(jù)都是合理的狀態(tài)
2.3.3 持久性
概念:
持久性也稱永久性(permanence
),指一個事務一旦提交,它對數(shù)據(jù)庫中數(shù)據(jù)的改變就應該是永久性的。接下來的其他操作或故障不應該對其有任何影響。
事物操作的數(shù)據(jù)都是直接操作硬盤,而硬盤的數(shù)據(jù)都是持久化的
2.3.4 隔離性
概念:
一個事務的執(zhí)行不能被其他事務干擾。即一個事務內(nèi)部的操作及使用的數(shù)據(jù)對并發(fā)的其他事務是隔離的,并發(fā)執(zhí)行的各個事務之間不能互相干擾。
2.4 使用方法
開啟事物:
start transaction;
執(zhí)行多條 SQL 語句
回滾或提交
-- 回滾:表示上述 SQL 語句全部失敗 rollback; -- 提交:表示上述 SQL 語句全部成功 commit;
到此這篇關于MySQL 數(shù)據(jù)庫 索引和事務的文章就介紹到這了,更多相關MySQL 索引和事務內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家!
相關文章
MySql查詢某個時間段內(nèi)的數(shù)據(jù)實例(前一周、前三個月、前一年等)
在實際工作中需要整理一份時間在規(guī)定時間前后的統(tǒng)計表,下面這篇文章主要給大家介紹了關于MySql查詢某個時間段內(nèi)的數(shù)據(jù)的相關資料,包括前一周、前三個月、前一年等,需要的朋友可以參考下2022-10-10MySQL中文漢字轉拼音的自定義函數(shù)和使用實例(首字的首字母)
這篇文章主要介紹了MySQL中文漢字轉拼音的自定義函數(shù)和使用實例,需要的朋友可以參考下2014-06-06數(shù)據(jù)庫設計工具MySQL?Workbench使用教程(超級詳細!)
MySQL?Workbench為數(shù)據(jù)庫管理員、程序開發(fā)者和系統(tǒng)規(guī)劃師提供可視化的Sql開發(fā)、數(shù)據(jù)庫建模、以及數(shù)據(jù)庫管理功能,下面這篇文章主要給大家介紹了關于MySQL設計工具Workbench使用的相關資料,需要的朋友可以參考下2023-02-02