當前位置:網站首頁>Linux Mysql 索引 事務 存儲引擎

Linux Mysql 索引 事務 存儲引擎

2022-01-27 07:49:04 Dark_wWw

目錄

Linux Mysql 索引 事務 存儲引擎

一、Mysql 索引

索引的概念

索引的作用及缺點

創建索引的原則依據

索引的分類和創建

 二、Mysql 事務

事務的概念

事務的 ACID 特點

事務控制語句

三、Mysql 存儲引擎

MyISAM 錶支持的3 種不同的存儲格式

控制語句


Linux Mysql 索引 事務 存儲引擎

一、Mysql 索引

索引的概念

索引是一個排序的列錶,在這個列錶中存儲著索引的值和包含這個值的數據所在行的物理地址(類似於C語言的鏈錶通過指針指向數據記錄的內存地址)。

使用索引後可以不用掃描全錶來定比特某行的數據,而是先通過索引錶找到該行數據對應的物理地址然後訪問相應的數據,因此能加快數據庫的查詢速度。

索引就好比是一本書的目錄,可以根據目錄中的頁碼快速找到所需的內容。

索引是錶中一列或者若幹列值排序的方法。

建立索引的目的是加快對錶中記錄的查找或排序

索引的作用及缺點

優點

設置了合適的索引之後,數據庫利用各種快速定比特技術,能够大大加快查詢速度,這是創建所有的最主要的原因

當錶很大或查詢涉及到多個錶時,使用索引可以成千上萬倍地提高查詢速度

可以降低數據庫的IO成本,並且索引還可以降低數據庫的排序成本

通過創建唯一性索引,可以保證數據錶中每一行數據的唯一性

可以加快錶與錶之間的連接。

在使用分組和排序時,可大大减少分組和排序的時間

缺點

索引需要占用額外的磁盤空間

對於 MyISAM 引擎而言,索引文件和數據文件是分離的,索引文件用於保存數據記錄的地址。 而 InnoDB 引擎的錶數據文件本身就是索引文件

在插入和修改數據時要花費更多的時間,因為索引也要隨之變動

創建索引的原則依據

索引隨可以提昇數據庫查詢的速度,但並不是任何情况下都適合創建索引。因為索引本身會消耗系統資源,在有索引的情况下,數據庫會先進行索引查詢,然後定比特到具體的數據行,如果索引使用不當,反而會增加數據庫的負擔

錶的主鍵、外鍵必須有索引。因為主鍵具有唯一性,外鍵關聯的是子錶的主鍵,查詢時可以快速定比特

記錄數超過300行的錶應該有索引。如果沒有索引,需要把錶遍曆一遍,會嚴重影響數據庫的性能

經常與其他錶進行連接的錶,在連接字段上應該建立索引

唯一性太差的字段不適合建立索引

更新太頻繁地字段不適合創建索引

經常出現在 where 子句中的字段,特別是大錶的字段,應該建立索引

索引應該建在選擇性高的字段上

索引應該建在小字段上,對於大的文本字段甚至超長字段,不要建索引

索引的分類和創建

普通索引

最基本的索引類型,沒有唯一性之類的限制

直接創建索引

CREATE INDEX 索引名 ON 錶名 (列名[(length)]);

 (列名(length)):length是可選項,下同。如果忽略 length 的值,則使用整個列的值作為索引。如果指定使用列前的 length 個字符來創建索引,這樣有利於减小索引文件的大小

索引名建議以“_index”結尾

修改錶的方式創建

ALTER TABLE 錶名 ADD INDEX 索引名 (列名);

創建錶的時候指定索引

CREATE TABLE 錶名 ( 字段1 數據類型,字段2 數據類型[,...],INDEX 索引名 (列名));
 create table info2 (id int(4),name char (30),genter char (10),age int (4),height decimal (3,1),address char (100),index index_address(address) );

desc info2 ;

 唯一索引

與普通索引類似,但區別是唯一索引列的每個值都唯一。唯一索引允許有空值(注意和主鍵不同)

如果是用組合索引創建,則列值的組合必須唯一。添加唯一鍵將自動創建唯一索引

CREATE UNIQUE INDEX 索引名 ON 錶名(列名);

 修改錶方式創建唯一索引

ALTER TABLE 錶名 ADD UNIQUE 索引名 (列名);

 創建錶的時候指定唯一索引

CREATE TABLE 錶名 (字段1 數據類型,字段2 數據類型[,...],UNIQUE 索引名 (列名));

 主鍵索引

是一種特殊的唯一索引,必須指定為“PRIMARY KEY”。一個錶只能有一個主鍵,不允許有空值。 添加主鍵將自動創建主鍵索引

創建錶的時候指定主鍵索引

CREATE TABLE 錶名 ([...],PRIMARY KEY (列名));

 修改錶方式創建主鍵索引

 組合索引(單列索引與多列索引)

可以是單列上創建的索引,也可以是在多列上創建的索引。需要滿足最左原則,因為 select 語句的 where 條件是依次從左往右執行的,所以在使用 select 語句查詢時 where 條件使用的字段順序必須和組合索引中的排序一致,否則索引將不會生效

CREATE TABLE 錶名 (列名1 數據類型,列名2 數據類型,列名3 數據類型,INDEX 索引名 (列名1,列名2,列名3));

select * from 錶名 where 列名1='...' AND 列名2='...' AND 列名3='...';

全文索引(FULLTEXT)

適合在進行模糊查詢的時候使用,可用於在一篇文章中檢索文本信息。在 MySQL5.6 版本以前

直接創建全文索引

CREATE FULLTEXT INDEX 索引名 ON 錶名 (列名);

 修改錶方式鍵全文索引

ALTER TABLE 錶名 ADD FULLTEXT 索引名 (列名);

 創建錶的時候指定全文索引

CREATE TABLE 錶名 (字段1 數據類型[,...],FULLTEXT 索引名 (列名));

 數據類型可以為 CHAR、VARCHAR 或者 TEXT

使用全文索引查詢

SELECT * FROM 錶名 WHERE MATCH(列名) AGAINST('查詢內容');

查看索引

show index from 錶名;
show keys from 錶名;

 字段                                            含義
Table                                         錶的名稱
Non_unique          如果索引不能包括重複詞,則為0;如果可以,則為1
Key_name            索引的名稱
seq_in_index        索引中的列序號,從1開始
column_name       列名稱
collation                列以什麼方式存儲在索引中。在 MySQL中,有值'A(昇序)或 NULL(無分類)
Cardinality            索引中唯一值數目的估計值
sub_part             如果列只是被部分地編入索引,則為被編入索引的字符的數目。如果整列被編 入                              索引,則為NULL
Packed                    指示關鍵字如何被壓縮。如果沒有被壓縮,則為NULL
Null                        如果列含有NULL,則含有YES。如果沒有,則該列含有NO
lndex_type             用過的索引方法(BTREE,FULLTEXT,HASH,RTREE)
comment                備注

删除索引

DROP INDEX 索引名 ON 錶名;

 修改錶方式删除索引

ALTER TABLE 錶名 DROP INDEX 索引名;

 删除主鍵索引

ALTER TABLE 錶名 DROP PRIMARY KEY;

 二、Mysql 事務

事務的概念

事務是一種機制、一個操作序列,包含了一組數據庫操作命令,並且把所有的命令作為一個整體一起向系統提交或撤銷操作請求,即這一組數據庫命令要麼都執行,要麼都不執行

事務是一個不可分割的工作邏輯單元,在數據庫系統上執行並發操作時,事務是最小的控制單元

事務適用於多用戶同時操作的數據庫系統的場景,如銀行、保險公司及證券交易系統等等。

事務通過事務的整體性以保證數據的一致性

總的來說,事務就是是一個操作序列,這些操作要麼都執行,要麼都不執行,它是一個不可分割的工作單比特

事務的 ACID 特點

ACID,是指在可靠數據庫管理系統(DBMS)中,事務(transaction)應該具有的四個特性:原子性(Atomicity)、一致性(Consistency)、隔離性(Isolation)、持久性(Durability)。這是可靠數據庫所應具備的幾個特性。

原子性

指事務是一個不可再分割的工作單比特,事務中的操作要麼都發生,要麼都不發生。

事務是一個完整的操作,事務的各元素是不可分的

事務中的所有元素必須作為一個整體提交或回滾

如果事務中的任何元素失敗,則整個事務將失敗

一致性

指在事務開始之前和事務結束以後,數據庫的完整性約束沒有被破壞

當事務完成時,數據必須處於一致狀態

在事務開始前,數據庫中存儲的數據處於一致狀態

在正在進行的事務中,數據可能處於不一致的狀態

當事務成功完成時,數據必須再次回到已知的一致狀態

隔離性

指在並發環境中,當不同的事務同時操縱相同的數據時,每個事務都有各自的完整數據空間。

對數據進行修改的所有並發事務是彼此隔離的,錶明事務必須是獨立的,它不應以任何方式依賴於或影響其他事務。

修改數據的事務可在另一個使用相同數據的事務開始之前訪問這些數據,或者在另一個使用相同數據的事務結束之後訪問這些數據

事務之間的相互影響分為幾種,分別為

髒讀:一個事務讀取了另一個事務未提交的數據,而這個數據是有可能回滾的。

不可重複讀:一個事務內兩個相同的查詢卻返回了不同數據。這是由於查詢時系統中其他事務修改的提交而引起的。

幻讀:一個事務對一個錶中的數據進行了修改,這種修改涉及到錶中的全部數據行。同時,另一個事務也修改這個錶中的數據,這種修改是向錶中插入一行新數據。那麼,操作前一個事務的用戶會發現錶中還有沒有修改的數據行,就好象發生了幻覺一樣。

丟失更新:兩個事務同時讀取同一條記錄,A先修改記錄,B也修改記錄(B不知道A修改過),B提交數據後B的修改結果覆蓋了A的修改結果。

Mysql 及事物隔離級別

read_uncommitted : 讀取尚未提交的數據 :不解决髒讀

read_committed:讀取已經提交的數據 :可以解决髒讀

repeatable_read:重讀讀取:可以解决髒讀 和 不可重複讀 —mysql默認的

serializable:串行化:可以解决 髒讀 不可重複讀 和 虛讀—相當於鎖錶

mysql默認的事務處理級別是 repeatable read ,而Oracle和SQL Server是 read committed 

事務隔離級別 髒讀 不可重複讀 幻讀 第一類丟失更新 第二類丟失更新
read uncommitted 允許 允許 允許 禁止 允許
read committed 禁止 允許 允許 禁止 允許
repeatable read 禁止 禁止 允許 禁止 禁止
serializable 禁止 禁止 禁止 禁止 禁止

查詢全局事務隔離級別

show global variables like '%isolation%';
SELECT @@global.tx_isolation;

 查詢會話事務隔離級別

show session variables like '%isolation%';
SELECT @@session.tx_isolation; 
SELECT @@tx_isolation;

 設置全局事務隔離級別

set global transaction isolation level serializable;

 設置會話事務隔離級別

set session transaction isolation level read committed;

 持久性

在事務完成以後,該事務所對數據庫所作的更改便持久的保存在數據庫之中,並不會被回滾。

指不管系統是否發生故障,事務處理的結果都是永久的

一旦事務被提交,事務的效果會被永久地保留在數據庫中

結:在事務管理中,原子性是基礎,隔離性是手段,一致性是目的,持久性是結果

事務控制語句

BEGIN 或 START TRANSACTION:顯式地開啟一個事務

COMMIT 或 COMMIT WORK:提交事務,並使已對數據庫進行的所有修改變為永久性的。

ROLLBACK 或 ROLLBACK WORK:回滾會結束用戶的事務,並撤銷正在進行的所有未提交的修改

SAVEPOINT S1:使用 SAVEPOINT 允許在事務中創建一個回滾點,一個事務中可以有多個 SAVEPOINT;“S1”代錶回滾點名稱

ROLLBACK TO [SAVEPOINT] S1:把事務回滾到標記點

測試begin和commit(開始事務和提交事務)

 測試事務未提交的回滾

 測試創建回滾點並進行回滾

 使用 set 設置控制事務

#禁止自動提交(狀態為OFF)
SET AUTOCOMMIT=0; 

#開啟自動提交,Mysql默認為1(狀態為ON)
SET AUTOCOMMIT=1; 

#查看Mysql中的AUTOCOMMIT值 
show variables like 'autocommit';

 注意:

如果沒有開啟自動提交,當前會話連接的mysql的所有操作都會當成一個事務直到你輸入rollback|commit;當前事務才算結束。當前事務結束前新的mysql連接時無法讀取到任何當前會話的操作結果

如果開起了自動提交,mysql會把每個sql語句當成一個事務,然後自動的commit

當然無論開啟與否,begin; commit|rollback; 都是獨立的事務

三、Mysql 存儲引擎

MyISAM 錶支持的3 種不同的存儲格式

 靜態(固定長度)錶

靜態錶是默認的存儲格式。靜態錶中的字段都是非可變字段,這樣每個記錄都是固定長度的,這種存儲方式的優點是存儲非常迅速,容易緩存,出現故障容易恢複;缺點是占用的空間通常比動態錶多

動態錶

動態錶包含可變字段,記錄不是固定長度的,這樣存儲的優點是占用空間較少,但是頻繁的更新、删除記錄會產生碎片,需要定期執行 OPTIMIZE TABLE 語句或 myisamchk -r 命令來改善性能,並且出現故障的時候恢複相對比較困難

壓縮錶

壓縮錶由 myisamchk 工具創建,占據非常小的空間,因為每條記錄都是被單獨壓縮的,所以只有非常小的訪問開支

控制語句

查看系統支持的存儲引擎

show engines;

 查看錶使用的存儲引擎

法 一 :

show table status from 庫名 where name='錶名'\G;

 法 二 :

use 庫名;
show create table 錶名;

 修改存儲引擎

通過 alter table 修改

use 庫名;
alter table 錶名 engine=MyISAM;

 通過修改 /etc/my.cnf 配置文件,指定默認存儲引擎並重啟服務

vim /etc/my.cnf
......
[mysqld]
......
default-storage-engine=INNODB
 
systemctl restart mysqld

 注意:此方法只對修改了配置文件並重啟mysql服務後新創建的錶有效,已經存在的錶不會有變更

通過 create table 創建錶時指定存儲引擎

use 庫名;
create table 錶名(字段1 數據類型,...) engine=MyISAM;

 

版權聲明
本文為[Dark_wWw]所創,轉載請帶上原文鏈接,感謝
https://cht.chowdera.com/2022/01/202201270749035944.html

隨機推薦