MySQL的存儲(chǔ)引擎

概述

數(shù)據(jù)庫(kù)存儲(chǔ)引擎是數(shù)據(jù)庫(kù)底層軟件組織，數(shù)據(jù)庫(kù)管理系統(tǒng)（DBMS）使用數(shù)據(jù)引擎進(jìn)行創(chuàng)建、查詢、更新和刪除數(shù)據(jù)。

不同的存儲(chǔ)引擎提供不同的存儲(chǔ)機(jī)制、索引技巧、鎖定水平等功能?，F(xiàn)在許多不同的數(shù)據(jù)庫(kù)管理系統(tǒng)都支持多種不同的數(shù)據(jù)引擎。MySQL的核心就是存儲(chǔ)引擎。

用戶可以根據(jù)不同的需求為數(shù)據(jù)表選擇不同的存儲(chǔ)引擎

可以使用 SHOW ENGINES 命令查看Mysql的所有執(zhí)行引擎，默認(rèn)的執(zhí)行引擎是InnoDB，支持事務(wù)，行級(jí)鎖定和外鍵。

介紹

MyISAM：Mysql 5.5之前的默認(rèn)數(shù)據(jù)庫(kù)引擎，最為常用。擁有較高的插入，查詢速度，但不支持事務(wù)

InnoDB：事務(wù)型速記的首選引擎，支持ACID事務(wù)，支持行級(jí)鎖定，MySQL5.5成為默認(rèn)數(shù)據(jù)庫(kù)引擎

Memory： 所有數(shù)據(jù)置于內(nèi)存的存儲(chǔ)引擎，擁有極高的插入，更新和查詢效率。但是會(huì)占用和數(shù)據(jù)量成正比的內(nèi)存空間。并且其內(nèi)容會(huì)在MYSQL重新啟動(dòng)是會(huì)丟失。

Archive ：非常適合存儲(chǔ)大量的獨(dú)立的，作為歷史記錄的數(shù)據(jù)。因?yàn)樗鼈儾唤?jīng)常被讀取。Archive 擁有高效的插入速度，但其對(duì)查詢的支持相對(duì)較差

Federated ：將不同的 MySQL 服務(wù)器聯(lián)合起來(lái)，邏輯上組成一個(gè)完整的數(shù)據(jù)庫(kù)。非常適合分布式應(yīng)用

CSV ：邏輯上由逗號(hào)分割數(shù)據(jù)的存儲(chǔ)引擎。它會(huì)在數(shù)據(jù)庫(kù)子目錄里為每個(gè)數(shù)據(jù)表創(chuàng)建一個(gè) .csv 文件。這是一種普通文本文件，每個(gè)數(shù)據(jù)行占用一個(gè)文本行。CSV 存儲(chǔ)引擎不支持索引。

BlackHole： 黑洞引擎，寫(xiě)入的任何數(shù)據(jù)都會(huì)消失，一般用于記錄 binlog 做復(fù)制的中繼

ERFORMANCE_SCHEMA存儲(chǔ)引擎該引擎主要用于收集數(shù)據(jù)庫(kù)服務(wù)器性能參數(shù)。

Mrg_Myisam Merge存儲(chǔ)引擎，是一組MyIsam的組合，也就是說(shuō)，他將MyIsam引擎的多個(gè)表聚合起來(lái)，但是他的內(nèi)部沒(méi)有數(shù)據(jù)，真正的數(shù)據(jù)依然是MyIsam引擎的表中，但是可以直接進(jìn)行查詢、刪除更新等操作。

MyISAM、Memory、InnoDB三種存儲(chǔ)引擎的比較

操作

• 查詢當(dāng)前數(shù)據(jù)庫(kù)支持的存儲(chǔ)引擎

SHOW ENGINES;

• 查看當(dāng)前的默認(rèn)存儲(chǔ)引擎

SHOW VARIABLES LIKE '%storage_engine%';

查看某個(gè)表用了什么引擎(在顯示結(jié)果里參數(shù)engine后面的就表示該表當(dāng)前用的存儲(chǔ)引擎):

SHOW CREATE TABLE student;

• 創(chuàng)建新表時(shí)指定存儲(chǔ)引擎

語(yǔ)法

CREATE TABLE(...) ENGINE=MYISAM;

CREATE TABLE student(
	id INT,
	NAME VARCHAR(20)
)ENGINE=MYISAM;

• 修改數(shù)據(jù)庫(kù)引擎

語(yǔ)法

ALTER TABLE 表名 ENGINE = 存儲(chǔ)引擎;

ALTER TABLE student ENGINE = INNODB;
ALTER TABLE student ENGINE = MYISAM;

修改MySQL默認(rèn)存儲(chǔ)引擎方法

• 關(guān)閉mysql服務(wù)
• 找到mysql安裝目錄下的my.ini文件：
• 找到default-storage-engine=INNODB 改為目標(biāo)引擎， 如：default-storage-engine=MYISAM
• 啟動(dòng)mysql服務(wù)

事務(wù)

MySQL8.x版本查詢隔離級(jí)別（在Mysql8中，tx_isolation變量已修改為transaction_isolation）

語(yǔ)法

select @@transaction_isolation;

同時(shí)，修改事務(wù)隔離級(jí)別的操作可以修改為（當(dāng)前會(huì)話立即生效，不需要重啟或再開(kāi)）

語(yǔ)法

SET transaction_isolation = '隔離級(jí)別';
或
SET SESSION transaction_isolation = '隔離級(jí)別';

SET transaction_isolation = 'READ-UNCOMMITTED';
或
SET SESSION transaction_isolation = 'READ-UNCOMMITTED';

查看隔離級(jí)別

語(yǔ)法

SHOW VARIABLES LIKE '%isolation%'; 
或
SELECT @@transaction_isolation;

MySQL的鎖機(jī)制

• 鎖是計(jì)算機(jī)協(xié)調(diào)多個(gè)進(jìn)程或線程并發(fā)訪問(wèn)某一資源的機(jī)制（避免爭(zhēng)搶?zhuān)?
• 在數(shù)據(jù)庫(kù)中，除傳統(tǒng)的計(jì)算資源（如 CPU、RAM、I/O 等）的爭(zhēng)用以外，數(shù)據(jù)也是一種供許多用戶共享的資源。如何保證數(shù)據(jù)并發(fā)訪問(wèn)的一致性、有效性是所有數(shù)據(jù)庫(kù)必須解決的一個(gè)問(wèn)題，鎖沖突也是影響數(shù)據(jù)庫(kù)并發(fā)訪問(wèn)性能的一個(gè)重要因素。從這個(gè)角度來(lái)說(shuō)，鎖對(duì)數(shù)據(jù)庫(kù)而言顯得尤其重要，也更加復(fù)雜。

• 從對(duì)數(shù)據(jù)操作的粒度分 ：

1） 表鎖：操作時(shí)，會(huì)鎖定整個(gè)表。

2） 行鎖：操作時(shí)，會(huì)鎖定當(dāng)前操作行。

• 對(duì)數(shù)據(jù)操作的類(lèi)型分：

1） 讀鎖（共享鎖）：針對(duì)同一份數(shù)據(jù)，多個(gè)讀操作可以同時(shí)進(jìn)行而不會(huì)互相影響。 2） 寫(xiě)鎖（排它鎖）：當(dāng)前操作沒(méi)有完成之前，它會(huì)阻斷其他寫(xiě)鎖和讀鎖。

相對(duì)其他數(shù)據(jù)庫(kù)而言，MySQL的鎖機(jī)制比較簡(jiǎn)單，其最顯著的特點(diǎn)是不同的存儲(chǔ)引擎支持不同的鎖機(jī)制。下表中羅列出了各存儲(chǔ)引擎對(duì)鎖的支持情況：

MySQL鎖的特性可大致歸納如下

從上述特點(diǎn)可見(jiàn)，很難籠統(tǒng)地說(shuō)哪種鎖更好，只能就具體應(yīng)用的特點(diǎn)來(lái)說(shuō)哪種鎖更合適。

僅從鎖的角度來(lái)說(shuō)：表級(jí)鎖更適合于以查詢?yōu)橹?，只有少量按索引條件更新數(shù)據(jù)的應(yīng)用，如Web 應(yīng)用。

而行級(jí)鎖則更適合于有大量按索引條件并發(fā)更新少量不同數(shù)據(jù)，同時(shí)又有并查詢的應(yīng)用，如一些在線事務(wù)處理（OLTP）系統(tǒng)。

MyISAM 表鎖

概述

• MyISAM 存儲(chǔ)引擎只支持表鎖。

如何加表鎖：

• MyISAM 在執(zhí)行查詢語(yǔ)句（SELECT）前，會(huì)自動(dòng)給涉及的所有表加讀鎖，在執(zhí)行更新操作（UPDATE、DELETE、INSERT 等）前，會(huì)自動(dòng)給涉及的表加寫(xiě)鎖，這個(gè)過(guò)程并不需要用戶干預(yù)，因此，用戶一般不需要直接用 LOCK TABLE 命令給 MyISAM 表顯式加鎖。

加鎖語(yǔ)法

加讀鎖 ： lock table 表名 read;

加寫(xiě)鎖 ： lock table 表名 write；

解鎖

unlock tables;

表鎖特點(diǎn)

• 對(duì)MyISAM 表的讀操作，不會(huì)阻塞其他用戶對(duì)同一表的讀請(qǐng)求，但會(huì)阻塞對(duì)同一表的寫(xiě)請(qǐng)求，且加鎖后不能訪問(wèn)其他表。
• 對(duì)MyISAM 表的寫(xiě)操作，則會(huì)阻塞其他用戶對(duì)同一表的讀和寫(xiě)操作，寫(xiě)鎖只有一個(gè)。

簡(jiǎn)而言之，就是讀鎖會(huì)阻塞寫(xiě)，但是不會(huì)阻塞讀。而寫(xiě)鎖，則既會(huì)阻塞讀，又會(huì)阻塞寫(xiě)。

此外，MyISAM 的讀寫(xiě)鎖調(diào)度是寫(xiě)優(yōu)先，這也是MyISAM不適合做寫(xiě)為主的表的存儲(chǔ)引擎的原因。因?yàn)閷?xiě)鎖后，其他線程不能做任何操作，大量的更新會(huì)使查詢很難得到鎖，從而造成永遠(yuǎn)阻塞。

InnoDB行鎖

行鎖特點(diǎn)

• 行鎖特點(diǎn) ：偏向InnoDB 存儲(chǔ)引擎，開(kāi)銷(xiāo)大，加鎖慢；會(huì)出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低,并發(fā)度也最高。
• InnoDB 與 MyISAM 的最大不同有兩點(diǎn)：一是支持事務(wù)；二是 采用了行級(jí)鎖。

行鎖模式

InnoDB 實(shí)現(xiàn)了以下兩種類(lèi)型的行鎖：

• 共享鎖（S）：又稱(chēng)為讀鎖，簡(jiǎn)稱(chēng)S鎖，共享鎖就是多個(gè)事務(wù)對(duì)于同一數(shù)據(jù)可以共享一把鎖，都能訪問(wèn)到數(shù)據(jù)，但是只能讀不能修改。
• 排他鎖（X）：又稱(chēng)為寫(xiě)鎖，簡(jiǎn)稱(chēng)X鎖，排他鎖就是不能與其他鎖并存，如一個(gè)事務(wù)獲取了一個(gè)數(shù)據(jù)行的排他鎖，其他事務(wù)就不能再獲取該行的其他鎖，包括共享鎖和排他鎖，但是獲取排他鎖的事務(wù)是可以對(duì)數(shù)據(jù)就行讀取和修改。

對(duì)于UPDATE、DELETE和INSERT語(yǔ)句，InnoDB會(huì)自動(dòng)給涉及數(shù)據(jù)集加排他鎖（X)。

對(duì)于普通SELECT語(yǔ)句，InnoDB不會(huì)加任何鎖。

可以通過(guò)以下語(yǔ)句顯示給記錄集加共享鎖或排他鎖：

共享鎖（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE 
排他鎖（X) ：SELECT * FROM table_name WHERE ... FOR UPDATE

MySQL的日志

在任何一種數(shù)據(jù)庫(kù)中，都會(huì)有各種各樣的日志，記錄著數(shù)據(jù)庫(kù)工作的方方面面，以幫助數(shù)據(jù)庫(kù)管理員追蹤數(shù)據(jù)庫(kù)曾經(jīng)發(fā)生過(guò)的各種事件。MySQL 也不例外。

日志分類(lèi)

• 錯(cuò)誤日志
• 二進(jìn)制日志
• 查詢?nèi)罩?
• 慢查詢?nèi)罩?

錯(cuò)誤日志

錯(cuò)誤日志是 MySQL 中最重要的日志之一，它記錄了當(dāng) mysqld 啟動(dòng)和停止時(shí)，以及服務(wù)器在運(yùn)行過(guò)程中發(fā)生任何嚴(yán)重錯(cuò)誤時(shí)的相關(guān)信息。當(dāng)數(shù)據(jù)庫(kù)出現(xiàn)任何故障導(dǎo)致無(wú)法正常使用時(shí)，可以首先查看此日志。

該日志是默認(rèn)開(kāi)啟的，默認(rèn)存放目錄為 mysql 的數(shù)據(jù)目錄，默認(rèn)的日志文件名為 hostname.err（hostname是主機(jī)名）。

查看日志位置指令：

show variables like 'log_error%';

二進(jìn)制日志——binlog

概述

二進(jìn)制日志（BINLOG）記錄了所有的 DDL（數(shù)據(jù)定義語(yǔ)言）語(yǔ)句和 DML（數(shù)據(jù)操縱語(yǔ)言）語(yǔ)句，但是不包括數(shù)據(jù)查詢語(yǔ)句。此日志對(duì)于災(zāi)難時(shí)的數(shù)據(jù)恢復(fù)起著極其重要的作用，MySQL的主從復(fù)制， 就是通過(guò)該binlog實(shí)現(xiàn)的。

二進(jìn)制日志，MySQl8.0默認(rèn)已經(jīng)開(kāi)啟，低版本的MySQL的需要通過(guò)配置文件開(kāi)啟，并配置MySQL日志的格式。

#配置開(kāi)啟binlog日志， 日志的文件前綴為 mysqlbin -----> 
生成的文件名如 : mysqlbin.000001,mysqlbin.000002 log_bin=mysqlbin

#配置二進(jìn)制日志的格式 binlog_format=STATEMENT

日志格式

• STATEMENT：

該日志格式在日志文件中記錄的都是SQL語(yǔ)句（statement），每一條對(duì)數(shù)據(jù)進(jìn)行修改的SQL都會(huì)記錄在日志文件中，通過(guò)Mysql提供的mysqlbinlog工具，可以清晰的查看到每條語(yǔ)句的文本。主從復(fù)制的時(shí)候，從庫(kù)（slave）會(huì)將日志解析為原文本，并在從庫(kù)重新執(zhí)行一次。

• ROW：（MySQL8.0默認(rèn)）

該日志格式在日志文件中記錄的是每一行的數(shù)據(jù)變更，而不是記錄SQL語(yǔ)句。比如，執(zhí)行SQL語(yǔ)句 ： update tb_book set status='1' , 如果是STATEMENT 日志格式，在日志中會(huì)記錄一行SQL文件； 如果是ROW，由于是對(duì)全表進(jìn)行更新，也就是每一行記錄都會(huì)發(fā)生變更，ROW 格式的日志中會(huì)記錄每一行的數(shù)據(jù)變更。

• MIXED：

混合了STATEMENT 和 ROW兩種格式。

相關(guān)操作

-- 查看MySQL是否開(kāi)啟了binlog日志
show variables like 'log_bin';
 
 
-- 查看binlog日志的格式
show variables like 'binlog_format';
 
-- 查看所有日志
show binlog events;
 
-- 查看最新的日志
show master status;
 
-- 查詢指定的binlog日志
show binlog events in 'binlog.000010';

-- 從指定的位置開(kāi)始,查看指定的Binlog日志
show binlog events in 'binlog.000010' from 156;
 
-- 從指定的位置開(kāi)始,查看指定的Binlog日志,限制查詢的條數(shù)
show binlog events in 'binlog.000010' from 156 limit 2;

--從指定的位置開(kāi)始，帶有偏移，查看指定的Binlog日志,限制查詢的條數(shù)
show binlog events in 'binlog.000010' from 666 limit 1, 2;
 
-- 清空所有的 binlog 日志文件
reset master
-----------------------------------
?著作權(quán)歸作者所有：來(lái)自51CTO博客作者奇見(jiàn)瘋123的原創(chuàng)作品，請(qǐng)聯(lián)系作者獲取轉(zhuǎn)載授權(quán)，否則將追究法律責(zé)任
MySQL的存儲(chǔ)引擎、事務(wù)補(bǔ)充、MySQL的鎖機(jī)制、MySQL的日志
https://blog.51cto.com/u_16078425/8249717

查詢?nèi)罩?

概述

查詢?nèi)罩局杏涗浟丝蛻舳说乃胁僮髡Z(yǔ)句，而二進(jìn)制日志不包含查詢數(shù)據(jù)的SQL語(yǔ)句。

默認(rèn)情況下，查詢?nèi)罩臼俏撮_(kāi)啟的。如果需要開(kāi)啟查詢?nèi)罩?，可以設(shè)置以下配置

#該選項(xiàng)用來(lái)開(kāi)啟查詢?nèi)罩?， 可選值 ： 0 或者 1 ； 0 代表關(guān)閉， 1 代表開(kāi)啟 
general_log=1

#設(shè)置日志的文件名 ， 如果沒(méi)有指定， 默認(rèn)的文件名為 host_name.log 
general_log_file=file_name

相關(guān)操作

-- 查看MySQL是否開(kāi)啟了查詢?nèi)罩?show variables like 'general_log';
 
-- 開(kāi)啟查詢?nèi)罩?set global  general_log=1;

慢查詢?nèi)罩?

概述

• 慢查詢?nèi)罩居涗浟怂袌?zhí)行時(shí)間超過(guò)參數(shù) long_query_time 設(shè)置值并且掃描記錄數(shù)不小于 min_examined_row_limit 的所有的SQL語(yǔ)句的日志。
• long_query_time 默認(rèn)為 10 秒，最小為 0， 精度可以到微秒

# 該參數(shù)用來(lái)控制慢查詢?nèi)罩臼欠耖_(kāi)啟， 可取值： 1 和 0 ， 1 代表開(kāi)啟， 0 代表關(guān)閉
slow_query_log=1

 # 該參數(shù)用來(lái)指定慢查詢?nèi)罩镜奈募?slow_query_log_file=slow_query.log

# 該選項(xiàng)用來(lái)配置查詢的時(shí)間限制， 超過(guò)這個(gè)時(shí)間將認(rèn)為值慢查詢， 將需要進(jìn)行日志記錄， 默認(rèn)10s

long_query_time=10