MySQL總結

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;
最后編輯于
?著作權歸作者所有,轉載或內容合作請聯系作者
  • 序言:七十年代末卷要,一起剝皮案震驚了整個濱河市,隨后出現的幾起案子独榴,更是在濱河造成了極大的恐慌僧叉,老刑警劉巖,帶你破解...
    沈念sama閱讀 222,729評論 6 517
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件棺榔,死亡現場離奇詭異彪标,居然都是意外死亡,警方通過查閱死者的電腦和手機掷豺,發(fā)現死者居然都...
    沈念sama閱讀 95,226評論 3 399
  • 文/潘曉璐 我一進店門捞烟,熙熙樓的掌柜王于貴愁眉苦臉地迎上來薄声,“玉大人,你說我怎么就攤上這事题画∧妫” “怎么了?”我有些...
    開封第一講書人閱讀 169,461評論 0 362
  • 文/不壞的土叔 我叫張陵苍息,是天一觀的道長缩幸。 經常有香客問我,道長竞思,這世上最難降的妖魔是什么表谊? 我笑而不...
    開封第一講書人閱讀 60,135評論 1 300
  • 正文 為了忘掉前任,我火速辦了婚禮盖喷,結果婚禮上爆办,老公的妹妹穿的比我還像新娘。我一直安慰自己课梳,他們只是感情好距辆,可當我...
    茶點故事閱讀 69,130評論 6 398
  • 文/花漫 我一把揭開白布。 她就那樣靜靜地躺著暮刃,像睡著了一般跨算。 火紅的嫁衣襯著肌膚如雪。 梳的紋絲不亂的頭發(fā)上椭懊,一...
    開封第一講書人閱讀 52,736評論 1 312
  • 那天诸蚕,我揣著相機與錄音,去河邊找鬼氧猬。 笑死背犯,一個胖子當著我的面吹牛,可吹牛的內容都是我干的狂窑。 我是一名探鬼主播,決...
    沈念sama閱讀 41,179評論 3 422
  • 文/蒼蘭香墨 我猛地睜開眼桑腮,長吁一口氣:“原來是場噩夢啊……” “哼泉哈!你這毒婦竟也來了?” 一聲冷哼從身側響起破讨,我...
    開封第一講書人閱讀 40,124評論 0 277
  • 序言:老撾萬榮一對情侶失蹤丛晦,失蹤者是張志新(化名)和其女友劉穎,沒想到半個月后提陶,有當地人在樹林里發(fā)現了一具尸體烫沙,經...
    沈念sama閱讀 46,657評論 1 320
  • 正文 獨居荒郊野嶺守林人離奇死亡,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內容為張勛視角 年9月15日...
    茶點故事閱讀 38,723評論 3 342
  • 正文 我和宋清朗相戀三年隙笆,在試婚紗的時候發(fā)現自己被綠了锌蓄。 大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片升筏。...
    茶點故事閱讀 40,872評論 1 353
  • 序言:一個原本活蹦亂跳的男人離奇死亡,死狀恐怖瘸爽,靈堂內的尸體忽然破棺而出您访,到底是詐尸還是另有隱情,我是刑警寧澤剪决,帶...
    沈念sama閱讀 36,533評論 5 351
  • 正文 年R本政府宣布灵汪,位于F島的核電站,受9級特大地震影響柑潦,放射性物質發(fā)生泄漏享言。R本人自食惡果不足惜,卻給世界環(huán)境...
    茶點故事閱讀 42,213評論 3 336
  • 文/蒙蒙 一渗鬼、第九天 我趴在偏房一處隱蔽的房頂上張望览露。 院中可真熱鬧,春花似錦乍钻、人聲如沸肛循。這莊子的主人今日做“春日...
    開封第一講書人閱讀 32,700評論 0 25
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽多糠。三九已至,卻和暖如春浩考,著一層夾襖步出監(jiān)牢的瞬間夹孔,已是汗流浹背。 一陣腳步聲響...
    開封第一講書人閱讀 33,819評論 1 274
  • 我被黑心中介騙來泰國打工析孽, 沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留搭伤,地道東北人。 一個月前我還...
    沈念sama閱讀 49,304評論 3 379
  • 正文 我出身青樓袜瞬,卻偏偏與公主長得像怜俐,于是被迫代替她去往敵國和親。 傳聞我的和親對象是個殘疾皇子邓尤,可洞房花燭夜當晚...
    茶點故事閱讀 45,876評論 2 361

推薦閱讀更多精彩內容