MySQL的存儲引擎、事務補充、MySQL的鎖機制、MySQL的日志
MySQL的存儲引擎
概述
數據庫存儲引擎是數據庫底層軟件組織，數據庫管理系統（DBMS）使用數據引擎進行創建、查詢、更新和刪除數據。
不同的存儲引擎提供不同的存儲機制、索引技巧、鎖定水平等功能。現在許多不同的數據庫管理系統都支持多種不同的數據引擎。MySQL的核心就是存儲引擎。
用戶可以根據不同的需求為數據表選擇不同的存儲引擎
可以使用 SHOW ENGINES 命令查看Mysql的所有執行引擎，默認的執行引擎是InnoDB，支持事務，行級鎖定和外鍵。
介紹
MyISAM：Mysql 5.5之前的默認數據庫引擎，最為常用。擁有較高的插入，查詢速度，但不支持事務
InnoDB：事務型速記的首選引擎，支持ACID事務，支持行級鎖定，MySQL5.5成為默認數據庫引擎
Memory： 所有數據置于內存的存儲引擎，擁有極高的插入，更新和查詢效率。但是會占用和數據量成正比的內存空間。并且其內容會在MYSQL重新啟動是會丟失。
Archive ：非常適合存儲大量的獨立的，作為歷史記錄的數據。因為它們不經常被讀取。Archive 擁有高效的插入速度，但其對查詢的支持相對較差
Federated ：將不同的 MySQL 服務器聯合起來，邏輯上組成一個完整的數據庫。非常適合分布式應用
CSV ：邏輯上由逗號分割數據的存儲引擎。它會在數據庫子目錄里為每個數據表創建一個 .csv 文件。這是一種普通文本文件，每個數據行占用一個文本行。CSV 存儲引擎不支持索引。
BlackHole： 黑洞引擎，寫入的任何數據都會消失，一般用于記錄 binlog 做復制的中繼
ERFORMANCE_SCHEMA存儲引擎該引擎主要用于收集數據庫服務器性能參數。
Mrg_Myisam Merge存儲引擎，是一組MyIsam的組合，也就是說，他將MyIsam引擎的多個表聚合起來，但是他的內部沒有數據，真正的數據依然是MyIsam引擎的表中，但是可以直接進行查詢、刪除更新等操作。
MyISAM、Memory、InnoDB三種存儲引擎的比較
操作
- 查詢當前數據庫支持的存儲引擎
SHOW ENGINES;- 查看當前的默認存儲引擎
SHOW VARIABLES LIKE '%storage_engine%';查看某個表用了什么引擎(在顯示結果里參數engine后面的就表示該表當前用的存儲引擎):
SHOW CREATE TABLE student;
- 創建新表時指定存儲引擎
語法
CREATE TABLE(...) ENGINE=MYISAM;
CREATE TABLE student(
id INT,
NAME VARCHAR(20)
)ENGINE=MYISAM;- 修改數據庫引擎
語法
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的鎖機制
- 鎖是計算機協調多個進程或線程并發訪問某一資源的機制（避免爭搶）。
- 在數據庫中，除傳統的計算資源（如 CPU、RAM、I/O 等）的爭用以外，數據也是一種供許多用戶共享的資源。如何保證數據并發訪問的一致性、有效性是所有數據庫必須解決的一個問題，鎖沖突也是影響數據庫并發訪問性能的一個重要因素。從這個角度來說，鎖對數據庫而言顯得尤其重要，也更加復雜。
- 從對數據操作的粒度分 ：
1） 表鎖：操作時，會鎖定整個表。
2） 行鎖：操作時，會鎖定當前操作行。
- 對數據操作的類型分：
1） 讀鎖（共享鎖）：針對同一份數據，多個讀操作可以同時進行而不會互相影響。 2） 寫鎖（排它鎖）：當前操作沒有完成之前，它會阻斷其他寫鎖和讀鎖。
相對其他數據庫而言，MySQL的鎖機制比較簡單，其最顯著的特點是不同的存儲引擎支持不同的鎖機制。下表中羅列出了各存儲引擎對鎖的支持情況：
MySQL鎖的特性可大致歸納如下
從上述特點可見，很難籠統地說哪種鎖更好，只能就具體應用的特點來說哪種鎖更合適。
僅從鎖的角度來說：表級鎖更適合于以查詢為主，只有少量按索引條件更新數據的應用，如Web 應用。
而行級鎖則更適合于有大量按索引條件并發更新少量不同數據，同時又有并查詢的應用，如一些在線事務處理（OLTP）系統。
MyISAM 表鎖
概述
- MyISAM 存儲引擎只支持表鎖。
如何加表鎖：
- MyISAM 在執行查詢語句（SELECT）前，會自動給涉及的所有表加讀鎖，在執行更新操作（UPDATE、DELETE、INSERT 等）前，會自動給涉及的表加寫鎖，這個過程并不需要用戶干預，因此，用戶一般不需要直接用 LOCK TABLE 命令給 MyISAM 表顯式加鎖。
加鎖語法
加讀鎖 ： lock table 表名 read;
加寫鎖 ： lock table 表名 write；解鎖
unlock tables;表鎖特點
- 對MyISAM 表的讀操作，不會阻塞其他用戶對同一表的讀請求，但會阻塞對同一表的寫請求，且加鎖后不能訪問其他表。
- 對MyISAM 表的寫操作，則會阻塞其他用戶對同一表的讀和寫操作，寫鎖只有一個。
簡而言之，就是讀鎖會阻塞寫，但是不會阻塞讀。而寫鎖，則既會阻塞讀，又會阻塞寫。
此外，MyISAM 的讀寫鎖調度是寫優先，這也是MyISAM不適合做寫為主的表的存儲引擎的原因。因為寫鎖后，其他線程不能做任何操作，大量的更新會使查詢很難得到鎖，從而造成永遠阻塞。
InnoDB行鎖
行鎖特點
- 行鎖特點 ：偏向InnoDB 存儲引擎，開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖沖突的概率最低,并發度也最高。
- InnoDB 與 MyISAM 的最大不同有兩點：一是支持事務；二是 采用了行級鎖。
行鎖模式
InnoDB 實現了以下兩種類型的行鎖：
- 共享鎖（S）：又稱為讀鎖，簡稱S鎖，共享鎖就是多個事務對于同一數據可以共享一把鎖，都能訪問到數據，但是只能讀不能修改。
- 排他鎖（X）：又稱為寫鎖，簡稱X鎖，排他鎖就是不能與其他鎖并存，如一個事務獲取了一個數據行的排他鎖，其他事務就不能再獲取該行的其他鎖，包括共享鎖和排他鎖，但是獲取排他鎖的事務是可以對數據就行讀取和修改。
對于UPDATE、DELETE和INSERT語句，InnoDB會自動給涉及數據集加排他鎖（X)。
對于普通SELECT語句，InnoDB不會加任何鎖。
可以通過以下語句顯示給記錄集加共享鎖或排他鎖：
共享鎖（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
排他鎖（X) ：SELECT * FROM table_name WHERE ... FOR UPDATE
MySQL的日志
在任何一種數據庫中，都會有各種各樣的日志，記錄著數據庫工作的方方面面，以幫助數據庫管理員追蹤數據庫曾經發生過的各種事件。MySQL 也不例外。
日志分類
- 錯誤日志
- 二進制日志
- 查詢日志
- 慢查詢日志
錯誤日志
錯誤日志是 MySQL 中最重要的日志之一，它記錄了當 mysqld 啟動和停止時，以及服務器在運行過程中發生任何嚴重錯誤時的相關信息。當數據庫出現任何故障導致無法正常使用時，可以首先查看此日志。
該日志是默認開啟的，默認存放目錄為 mysql 的數據目錄，默認的日志文件名為 hostname.err（hostname是主機名）。
查看日志位置指令：
show variables like 'log_error%';二進制日志——binlog
概述
二進制日志（BINLOG）記錄了所有的 DDL（數據定義語言）語句和 DML（數據操縱語言）語句，但是不包括數據查詢語句。此日志對于災難時的數據恢復起著極其重要的作用，MySQL的主從復制， 就是通過該binlog實現的。
二進制日志，MySQl8.0默認已經開啟，低版本的MySQL的需要通過配置文件開啟，并配置MySQL日志的格式。
#配置開啟binlog日志， 日志的文件前綴為 mysqlbin ----->
生成的文件名如 : mysqlbin.000001,mysqlbin.000002 log_bin=mysqlbin
#配置二進制日志的格式 binlog_format=STATEMENT日志格式
- STATEMENT：
該日志格式在日志文件中記錄的都是SQL語句（statement），每一條對數據進行修改的SQL都會記錄在日志文件中，通過Mysql提供的mysqlbinlog工具，可以清晰的查看到每條語句的文本。主從復制的時候，從庫（slave）會將日志解析為原文本，并在從庫重新執行一次。
- ROW：（MySQL8.0默認）
該日志格式在日志文件中記錄的是每一行的數據變更，而不是記錄SQL語句。比如，執行SQL語句 ： update tb_book set status='1' , 如果是STATEMENT 日志格式，在日志中會記錄一行SQL文件； 如果是ROW，由于是對全表進行更新，也就是每一行記錄都會發生變更，ROW 格式的日志中會記錄每一行的數據變更。
- 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日志,限制查詢的條數
show binlog events in 'binlog.000010' from 156 limit 2;
--從指定的位置開始，帶有偏移，查看指定的Binlog日志,限制查詢的條數
show binlog events in 'binlog.000010' from 666 limit 1, 2;
-- 清空所有的 binlog 日志文件
reset master
-----------------------------------
?著作權歸作者所有：來自51CTO博客作者奇見瘋123的原創作品，請聯系作者獲取轉載授權，否則將追究法律責任
MySQL的存儲引擎、事務補充、MySQL的鎖機制、MySQL的日志
https://blog.51cto.com/u_16078425/8249717
查詢日志
概述
查詢日志中記錄了客戶端的所有操作語句，而二進制日志不包含查詢數據的SQL語句。
默認情況下，查詢日志是未開啟的。如果需要開啟查詢日志，可以設置以下配置
#該選項用來開啟查詢日志 ， 可選值 ： 0 或者 1 ； 0 代表關閉， 1 代表開啟
general_log=1
#設置日志的文件名 ， 如果沒有指定， 默認的文件名為 host_name.log
general_log_file=file_name相關操作
-- 查看MySQL是否開啟了查詢日志
show variables like 'general_log';
-- 開啟查詢日志
set global general_log=1;慢查詢日志
概述
- 慢查詢日志記錄了所有執行時間超過參數 long_query_time 設置值并且掃描記錄數不小于 min_examined_row_limit 的所有的SQL語句的日志。
- long_query_time 默認為 10 秒，最小為 0， 精度可以到微秒
# 該參數用來控制慢查詢日志是否開啟， 可取值： 1 和 0 ， 1 代表開啟， 0 代表關閉
slow_query_log=1
# 該參數用來指定慢查詢日志的文件名
slow_query_log_file=slow_query.log
# 該選項用來配置查詢的時間限制， 超過這個時間將認為值慢查詢， 將需要進行日志記錄， 默認10s
long_query_time=10
