MySQL的存儲引擎

概述

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

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

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

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

介紹

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

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

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

Archive ：非常適合存儲大量的獨立的，作為歷史記錄的數(shù)據(jù)。因為它們不經(jīng)常被讀取。Archive 擁有高效的插入速度，但其對查詢的支持相對較差

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

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

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

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

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

MyISAM、Memory、InnoDB三種存儲引擎的比較

操作

• 查詢當前數(shù)據(jù)庫支持的存儲引擎

SHOW ENGINES;

• 查看當前的默認存儲引擎

SHOW VARIABLES LIKE '%storage_engine%';

查看某個表用了什么引擎(在顯示結果里參數(shù)engine后面的就表示該表當前用的存儲引擎):

SHOW CREATE TABLE student;

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

語法

CREATE TABLE(...) ENGINE=MYISAM;

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

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

語法

ALTER TABLE 表名 ENGINE = 存儲引擎;

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

修改MySQL默認存儲引擎方法

• 關閉mysql服務
• 找到mysql安裝目錄下的my.ini文件：
• 找到default-storage-engine=INNODB 改為目標引擎， 如：default-storage-engine=MYISAM
• 啟動mysql服務

事務

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

語法

select @@transaction_isolation;

同時，修改事務隔離級別的操作可以修改為（當前會話立即生效，不需要重啟或再開）

語法

SET transaction_isolation = '隔離級別';
或
SET SESSION transaction_isolation = '隔離級別';

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

查看隔離級別

語法

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

MySQL的鎖機制

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

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

1） 表鎖：操作時，會鎖定整個表。

2） 行鎖：操作時，會鎖定當前操作行。

• 對數(shù)據(jù)操作的類型分：

1） 讀鎖（共享鎖）：針對同一份數(shù)據(jù)，多個讀操作可以同時進行而不會互相影響。 2） 寫鎖（排它鎖）：當前操作沒有完成之前，它會阻斷其他寫鎖和讀鎖。

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

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

從上述特點可見，很難籠統(tǒng)地說哪種鎖更好，只能就具體應用的特點來說哪種鎖更合適。

僅從鎖的角度來說：表級鎖更適合于以查詢?yōu)橹鳎挥猩倭堪此饕龡l件更新數(shù)據(jù)的應用，如Web 應用。

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

MyISAM 表鎖

概述

• MyISAM 存儲引擎只支持表鎖。

如何加表鎖：

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

加鎖語法

加讀鎖 ： lock table 表名 read;

加寫鎖 ： lock table 表名 write；

解鎖

unlock tables;

表鎖特點

• 對MyISAM 表的讀操作，不會阻塞其他用戶對同一表的讀請求，但會阻塞對同一表的寫請求，且加鎖后不能訪問其他表。
• 對MyISAM 表的寫操作，則會阻塞其他用戶對同一表的讀和寫操作，寫鎖只有一個。

簡而言之，就是讀鎖會阻塞寫，但是不會阻塞讀。而寫鎖，則既會阻塞讀，又會阻塞寫。

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

InnoDB行鎖

行鎖特點

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

行鎖模式

InnoDB 實現(xiàn)了以下兩種類型的行鎖：

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

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

對于普通SELECT語句，InnoDB不會加任何鎖。

可以通過以下語句顯示給記錄集加共享鎖或排他鎖：

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

MySQL的日志

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

日志分類

• 錯誤日志
• 二進制日志
• 查詢?nèi)罩?
• 慢查詢?nèi)罩?

錯誤日志

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

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

查看日志位置指令：

show variables like 'log_error%';

二進制日志——binlog

概述

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

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

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

#配置二進制日志的格式 binlog_format=STATEMENT

日志格式

• STATEMENT：

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

• ROW：（MySQL8.0默認）

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

• MIXED：

混合了STATEMENT 和 ROW兩種格式。

相關操作

-- 查看MySQL是否開啟了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';

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

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

查詢?nèi)罩?

概述

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

默認情況下，查詢?nèi)罩臼俏撮_啟的。如果需要開啟查詢?nèi)罩荆梢栽O置以下配置

#該選項用來開啟查詢?nèi)罩?， 可選值 ： 0 或者 1 ； 0 代表關閉， 1 代表開啟 
general_log=1

#設置日志的文件名 ， 如果沒有指定， 默認的文件名為 host_name.log 
general_log_file=file_name

相關操作

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

慢查詢?nèi)罩?

概述

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

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

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

# 該選項用來配置查詢的時間限制， 超過這個時間將認為值慢查詢， 將需要進行日志記錄， 默認10s

long_query_time=10