1澈蝙、總結mysql常見的存儲引擎以及特點。
1裸影、1 MyISAM存儲引擎
不支持事務
表級鎖定
讀寫相互阻塞蝶念，寫入不能讀，讀時不能寫
只緩存索引
不支持外鍵約束
不支持聚簇索引
讀取數據較快熬拒，占用資源較少
不支持MVCC（多版本并發(fā)控制機制）高并發(fā)
崩潰恢復性較差
MySQL5.5.5前默認的數據庫引擎
MyISAM存儲引擎適用場景
- 只讀（或者寫較少）
- 表較小（可以接受長時間進行修復操作）
MyISAM引擎文件
tbl_name.frm 表格式定義
tbl_name.MYD 數據文件
tbl_name.MYI 索引文件
1、2 InnoDB引擎
InnoDB引擎特點
- 行級鎖
- 支持事務儿子，適合處理大量短期事務
- 讀寫阻塞與事務隔離級別相關
- 可緩存數據和索引
- 支持聚簇索引
- 崩潰恢復性更好
- 支持MVCC高并發(fā)
- 從MySQL5.5后支持全文索引
- 從MySQL5.5.5開始為默認的數據庫引擎
InnoDB數據庫文件
- 所有InnoDB表的數據和索引放置于同一個表空間中
數據文件：ibdata1, ibdata2,存放在datadir定義的目錄下
表格式定義：tb_name.frm,存放在datadir定義的每個數據庫對應的目錄下
- 每個表單獨使用一個表空間存儲表的數據和索引 兩類文件放在對應每個數據庫獨立目錄中
數據文件(存儲數據和索引)：tb_name.ibd
表格式定義：tb_name.frm
1、3 其它存儲引擎
- Performance_Schema：Performance_Schema數據庫使用
- Memory ：將所有數據存儲在RAM中砸喻，以便在需要快速查找參考和其他類似數據的環(huán)境中進行快速訪問柔逼。適用存放臨時數據。引擎以前被稱為HEAP引擎
- MRG_MyISAM：使MySQL DBA或開發(fā)人員能夠對一系列相同的MyISAM表進行邏輯分組恩够，并將它們作為一個對象引用卒落。適用于VLDB(Very Large Data Base)環(huán)境，如數據倉庫
- Archive ：為存儲和檢索大量很少參考的存檔或安全審核信息蜂桶，只支持SELECT和INSERT操作儡毕；支持行級鎖和專用緩存區(qū)
- Federated聯合：用于訪問其它遠程MySQL服務器一個代理，它通過創(chuàng)建一個到遠程MySQL服務器的客戶端連接，并將查詢傳輸到遠程服務器執(zhí)行腰湾，而后完成數據存取雷恃，提供鏈接單獨MySQL服務器的能力，以便從多個物理服務器創(chuàng)建一個邏輯數據庫费坊。非常適合分布式或數據集市環(huán)境
- BDB：可替代InnoDB的事務引擎倒槐，支持COMMIT、ROLLBACK和其他事務特性
- Cluster/NDB：MySQL的簇式數據庫引擎附井，尤其適合于具有高性能查找要求的應用程序讨越，這類查找需求還要求具有最高的正常工作時間和可用性
- CSV：CSV存儲引擎使用逗號分隔值格式將數據存儲在文本文件中∮酪悖可以使用CSV引擎以CSV格式導入和導出其他軟件和應用程序之間的數據交換
- BLACKHOLE ：黑洞存儲引擎接受但不存儲數據把跨，檢索總是返回一個空集。該功能可用于分布式數據庫設計沼死，數據自動復制着逐，但不是本地存儲
- example：“stub”引擎，它什么都不做意蛀∷时穑可以使用此引擎創(chuàng)建表，但不能將數據存儲在其中或從中檢索县钥。目的是作為例子來說明如何開始編寫新的存儲引擎
2秀姐、總結MySQL查詢緩存優(yōu)化總結。
2魁蒜、1查詢緩存原理
緩存SELECT操作或預處理查詢的結果集和SQL語句囊扳，當有新的SELECT語句或預處理查詢語句請求，先去查詢緩存兜看，判斷是否存在可用的記錄集锥咸，判斷標準：與緩存的SQL語句，是否完全一樣细移，區(qū)分大小寫
優(yōu)缺點
- 不需要對SQL語句做任何解析和執(zhí)行搏予，當然語法解析必須通過在先，直接從Query Cache中獲得查詢結果弧轧，提高查詢性能
- 查詢緩存的判斷規(guī)則雪侥，不夠智能，也即提高了查詢緩存的使用門檻精绎，降低效率
- 查詢緩存的使用速缨，會增加檢查和清理Query Cache中記錄集的開銷
哪些查詢可能不會被緩存
- 查詢語句中加了SQL_NO_CACHE參數
- 查詢語句中含有獲得值的函數，包含:自定義函數代乃，如：NOW() 旬牲，CURDATE()仿粹、GET_LOCK()、RAND()原茅、CONVERT_TZ()等
- 對系統數據庫的查詢：mysql吭历、information_schema 查詢語句中使用SESSION級別變量或存儲過
程中的局部變量 - 查詢語句中使用了LOCK IN SHARE MODE、FOR UPDATE的語句擂橘，查詢語句中類似SELECT …INTO 導出數據的語句
- 對臨時表的查詢操作
- 存在警告信息的查詢語句
- 不涉及任何表或視圖的查詢語句
- 某用戶只有列級別權限的查詢語句
- 事務隔離級別為Serializable時晌区，所有查詢語句都不能緩存
2、2查詢緩存相關的服務器變量
- query_cache_min_res_unit：查詢緩存中內存塊的最小分配單位通贞，默認4k朗若，較小值會減少浪費，
但會導致更頻繁的內存分配操作昌罩，較大值會帶來浪費捡偏，會導致碎片過多，內存不足 - query_cache_limit：單個查詢結果能緩存的最大值峡迷，單位字節(jié)，默認為1M你虹，對于查詢結果過大而
無法緩存的語句绘搞，建議使用SQL_NO_CACHE - query_cache_size：查詢緩存總共可用的內存空間；單位字節(jié)傅物，必須是1024的整數倍夯辖，最小值
40KB，低于此值有警報 - query_cache_wlock_invalidate：如果某表被其它的會話鎖定董饰，是否仍然可以從查詢緩存中返回結
果蒿褂，默認值為OFF，表示可以在表被其它會話鎖定的場景中繼續(xù)從緩存返回數據卒暂；ON則表示不允
許 - query_cache_type：是否開啟緩存功能啄栓，取值為ON, OFF, DEMAND
2、3 SELECT語句的緩存控制
- SQL_CACHE：顯式指定存儲查詢結果于緩存之中
- SQL_NO_CACHE：顯式查詢結果不予緩存
- query_cache_type參數變量
- query_cache_type的值為OFF或0時也祠，查詢緩存功能關閉
- query_cache_type的值為ON或1時昙楚，查詢緩存功能打開，SELECT的結果符合緩存條件即會緩存诈嘿，
否則堪旧，不予緩存，顯式指定SQL_NO_CACHE奖亚，不予緩存淳梦，此為默認值 - query_cache_type的值為DEMAND或2時，查詢緩存功能按需進行昔字，顯式指定SQL_CACHE的SELECT語句才會緩存爆袍；其它均不予緩存
2、4 查詢緩存相關的狀態(tài)變量
SHOW GLOBAL STATUS LIKE 'Qcache%';
- Qcache_free_blocks：處于空閑狀態(tài) Query Cache中內存 Block 數
- Qcache_total_blocks：Query Cache 中總Block ，當Qcache_free_blocks相對此值較大時螃宙，可能用內存碎片蛮瞄，執(zhí)行FLUSH QUERY CACHE清理碎片
- Qcache_free_memory：處于空閑狀態(tài)的 Query Cache 內存總量
- Qcache_hits：Query Cache 命中次數
- Qcache_inserts：向 Query Cache 中插入新的 Query Cache 的次數，即沒有命中的次數
- Qcache_lowmem_prunes：記錄因為內存不足而被移除出查詢緩存的查詢數
- Qcache_not_cached：沒有被 Cache 的 SQL 數谆扎，包括無法被 Cache 的 SQL 以及由于query_cache_type 設置的不會被 Cache 的 SQL語句
- Qcache_queries_in_cache：在 Query Cache 中的 SQL 數量
2挂捅、5 命中率和內存使用率估算
-
查詢緩存中內存塊的最小分配單位query_cache_min_res_unit ：
(query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache -
查詢緩存命中率 ：
Qcache_hits / ( Qcache_hits + Qcache_inserts ) * 100% -
查詢緩存內存使用率：
(query_cache_size – qcache_free_memory) / query_cache_size * 100%
注意：
MySQL8.0 取消查詢緩存的功能
盡管MySQL Query Cache旨在提高性能，但它存在嚴重的可伸縮性問題堂湖，并且很容易成為嚴重的瓶頸闲先。
自MySQL 5.6（2013）以來，默認情況下已禁用查詢緩存无蜂，其不能與多核計算機上在高吞吐量工作負載情況下進行擴展伺糠。
另外有時因為查詢緩存往往弊大于利。比如:查詢緩存的失效非常頻繁斥季，只要有對一個表的更新训桶，這個表上的所有的查詢緩存都會被清空。因此很可能你費勁地把結果存起來酣倾，還沒使用呢舵揭，就被一個更新全清空了。對于更新壓力大的數據庫來說躁锡，查詢緩存的命中率會非常低午绳。除非你的業(yè)務有一張靜態(tài)表，很長時間更新一次映之，比如系統配置表拦焚，那么這張表的查詢才適合做查詢緩存。
目前大多數應用都把緩存做到了應用邏輯層杠输，比如:使用redis或者memcache
3赎败、MySQL日志各類總結
MySQL 支持豐富的日志類型，如下：
事務日志：transaction log
-
事務日志的寫入類型為“追加”蠢甲，因此其操作為“順序IO”螟够；通常也被稱為：預寫式日志 write ahead logging
事務日志文件：ib_logfile0， ib_logfile1
錯誤日志：error log
通用日志：general log
慢查詢日志：slow query log
二進制日志：binary log
中繼日志：reley log峡钓，在主從復制架構中妓笙，從服務器用于保存從主服務器的二進制日志中讀取的事件
3、1 事務日志
事務日志：transaction log
事務型存儲引擎自行管理和使用能岩，建議和數據文件分開存放寞宫，redo log和undo log
Innodb事務日志相關配置：
show variables like '%innodb_log%';
innodb_log_file_size 50331648 每個日志文件大小
innodb_log_files_in_group 2 日志組成員個數
innodb_log_group_home_dir ./ 事務文件路徑
innodb_flush_log_at_trx_commit 默認為1
事務日志性能優(yōu)化
innodb_flush_log_at_trx_commit=0|1|2
1 此為默認值，日志緩沖區(qū)將寫入日志文件拉鹃，并在每次事務后執(zhí)行刷新到磁盤辈赋。 這是完全遵守ACID特性
0 提交時沒有寫磁盤的操作; 而是每秒執(zhí)行一次將日志緩沖區(qū)的提交的事務寫入刷新到磁盤鲫忍。 這樣可提供更好的性能，但服務器崩潰可能丟失最后一秒的事務
2 每次提交后都會寫入OS的緩沖區(qū)钥屈，但每秒才會進行一次刷新到磁盤文件中悟民。 性能比0略差一些，但操作系統或停電可能導致最后一秒的交易丟失
高并發(fā)業(yè)務行業(yè)最佳實踐篷就，是使用第三種折衷配置（=2）：
1.配置為2和配置為0射亏，性能差異并不大，因為將數據從Log Buffer拷貝到OS cache竭业，雖然跨越用戶態(tài)與內核態(tài)智润，但畢竟只是內存的數據拷貝，速度很快
2.配置為2和配置為0未辆，安全性差異巨大窟绷，操作系統崩潰的概率相比MySQL應用程序崩潰的概率，小很多咐柜，設置為2兼蜈，只要操作系統不奔潰，也絕對不會丟數據
說明：
設置為1拙友，同時sync_binlog = 1表示最高級別的容錯
innodb_use_global_flush_log_at_trx_commit=0 時饭尝，將不能用SET語句重置此變量（ MariaDB 10.2.6
后廢棄）
3、2 錯誤日志
錯誤日志
- mysqld啟動和關閉過程中輸出的事件信息
- mysqld運行中產生的錯誤信息
- event scheduler運行一個event時產生的日志信息
- 在主從復制架構中的從服務器上啟動從服務器線程時產生的信息
錯誤文件路徑
SHOW GLOBAL VARIABLES LIKE 'log_error'
記錄哪些警告信息至錯誤日志文件
#CentOS7 mariadb 5.5 默認值為1
#CentOS8 mariadb 10.3 默認值為2
log_warnings=0|1|2|3...
3献宫、3 通用日志
- 通用日志：記錄對數據庫的通用操作，包括:錯誤的SQL語句
- 通用日志可以保存在：file（默認值）或 table（mysql.general_log表）
通用日志相關設置
general_log=ON|OFF
general_log_file=HOSTNAME.log
log_output=TABLE|FILE|NONE
3实撒、4慢查詢日志
慢查詢日志：記錄執(zhí)行查詢時長超出指定時長的操作
慢查詢相關變量
slow_query_log=ON|OFF #開啟或關閉慢查詢姊途，支持全局和會話，只有全局設置才會生成慢查詢文件
long_query_time=N #慢查詢的閥值知态，單位秒
slow_query_log_file=HOSTNAME-slow.log #慢查詢日志文件
log_slow_filter = admin,filesort,filesort_on_disk,full_join,full_scan,
query_cache,query_cache_miss,tmp_table,tmp_table_on_disk
#上述查詢類型且查詢時長超過long_query_time捷兰，則記錄日志
log_queries_not_using_indexes=ON #不使用索引或使用全索引掃描，不論是否達到慢查詢閥值的語
句是否記錄日志负敏，默認OFF贡茅，即不記錄
log_slow_rate_limit = 1 #多少次查詢才記錄，mariadb特有
log_slow_verbosity= Query_plan,explain #記錄內容
log_slow_queries = OFF #同slow_query_log其做，MariaDB 10.0/MySQL 5.6.1 版后已刪除
2顶考、5 二進制日志(備份)
- 記錄導致數據改變或潛在導致數據改變的SQL語句
- 記錄已提交的日志
- 不依賴于存儲引擎類型
功能：通過“重放”日志文件中的事件來生成數據副本
注意：建議二進制日志和數據文件分開存放
二進制日志記錄三種格式
- 基于“語句”記錄：statement，記錄語句妖泄，默認模式（ MariaDB 10.2.3 版本以下 ）驹沿，日志量較少
- 基于“行”記錄：row，記錄數據蹈胡，日志量較大渊季，更加安全朋蔫，建議使用的格式
- 混合模式：mixed, 讓系統自行判定該基于哪種方式進行，默認模式（ MariaDB 10.2.4及版本以上）
二進制日志文件的構成
有兩類文件
1.日志文件：mysql|mariadb-bin.文件名后綴却汉，二進制格式,如： mariadb-bin.000001
2.索引文件：mysql|mariadb-bin.index驯妄，文本格式
二進制日志相關的服務器變量：
sql_log_bin=ON|OFF：#是否記錄二進制日志，默認ON合砂，支持動態(tài)修改青扔，系統變量，而非服務器選項
log_bin=/PATH/BIN_LOG_FILE：#指定文件位置既穆；默認OFF赎懦，表示不啟用二進制日志功能，上述兩項都開
啟才可以
binlog_format=STATEMENT|ROW|MIXED：#二進制日志記錄的格式幻工，默認STATEMENT
max_binlog_size=1073741824：#單個二進制日志文件的最大體積励两，到達最大值會自動滾動，默認為1G
#說明：文件達到上限時的大小未必為指定的精確值
binlog_cache_size=4m #此變量確定在每次事務中保存二進制日志更改記錄的緩存的大心衣（每次連接）
max_binlog_cache_size=512m #限制用于緩存多事務查詢的字節(jié)大小当悔。
sync_binlog=1|0：#設定是否啟動二進制日志即時同步磁盤功能，默認0踢代，由操作系統負責同步日志到磁盤
expire_logs_days=N：#二進制日志可以自動刪除的天數盲憎。 默認為0，即不自動刪除
二進制日志相關配置
查看mariadb自行管理使用中的二進制日志文件列表胳挎，及大小
SHOW {BINARY | MASTER} LOGS
查看使用中的二進制日志文件
SHOW MASTER STATUS
在線查看二進制文件中的指定內容
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
mysqlbinlog：二進制日志的客戶端命令工具饼疙，支持離線查看二進制日志
命令格式：
mysqlbinlog [OPTIONS] log_file…
--start-position= # 指定開始位置
--stop-position= #
--start-datetime= #時間格式：YYYY-MM-DD hh:mm:ss
--stop-datetime=
--base64-output[=name]
-v -vvv
二進制日志事件的格式：
# at 328
#151105 16:31:40 server id 1 end_log_pos 431 Query thread_id=1
exec_time=0 error_code=0
use `mydb`/*!*/;
SET TIMESTAMP=1446712300/*!*/;
CREATE TABLE tb1 (id int, name char(30))
/*!*/;
事件發(fā)生的日期和時間：151105 16:31:40
事件發(fā)生的服務器標識：server id 1
事件的結束位置：end_log_pos 431
事件的類型：Query
事件發(fā)生時所在服務器執(zhí)行此事件的線程的ID：thread_id=1
語句的時間戳與將其寫入二進制文件中的時間差：exec_time=0
錯誤代碼：error_code=0
事件內容：
GTID：Global Transaction ID，mysql5.6以mariadb10以上版本專屬屬性：GTID
清除指定二進制日志
PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }
刪除所有二進制日志慕爬，index文件重新記數
刪除所有二進制日志窑眯，index文件重新記數
RESET MASTER [TO #]; #刪除所有二進制日志文件，并重新生成日志文件医窿，文件名從#開始記數磅甩，默認從
1開始，一般是master主機第一次啟動時執(zhí)行姥卢，MariaDB 10.1.6開始支持TO #
切換日志文件
FLUSH LOGS;
