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;