电脑知识|欧美黑人一区二区三区|软件|欧美黑人一级爽快片淫片高清|系统|欧美黑人狂野猛交老妇|数据库|服务器|编程开发|网络运营|知识问答|技术教程文章 - 好吧啦网

您的位置:首頁技術文章
文章詳情頁

MySQL高效可靠處理持久化數據的教程指南

瀏覽:4日期:2023-07-12 19:49:57
目錄一、SQL 執行過程連接器--客戶端與 MySQL 服務器的連接優化器--基于成本的優化方式選擇索引,生成執行計劃存儲引擎二、crash-saferedologbinlog兩階段提交三、性能日志與磁盤IO的關系組提交臟頁刷盤(InnoDB Buffer Pool)

本文閱讀前提:需要比較熟悉 MySQL 的基本功能,有使用 MySQL 的相關經驗

一、SQL 執行過程

MySQL 中是如何執行 SQL 的,過程如下:

連接器->(查詢緩存)->分析器->優化器->執行器->存儲引擎

查詢緩存模塊,8.0 中已做廢此功能。

連接器--客戶端與 MySQL 服務器的連接

配置文件中,max_connections 用于控制最大連接數,默認值151。

需要注意的是,連接操作比較耗資源,所以一般使用長連接,不過長連接有內存占用的問題。解決方案定時斷開或者在大查詢之后斷開重連(5.7之后可以調用 MySQL_reset_connection(),在不斷開的情況下釋放資源)

mysql_reset_connection()優化器--基于成本的優化方式選擇索引,生成執行計劃

優化器選擇索引的依據

是否排序是否使用臨時表回表開消掃描行數(估計值,采樣,索引的區分度(不同的值))

MySQL 的優化器不是很完美,有時候需要優化 SQL,需要用 explain 分析索引使用情況

優化方向:

修改 SQL加索引刪索引force index-- 強制指定使用索引aselect * from t force index(a) where a between 10000 and 20000;存儲引擎

MySQL支持多種存儲引擎,同一個數據庫不同的表可以設置不同的引擎

InnoDB:5.5 后默認,支持事務,外鍵,適合大部分場景

MyISAM:5.5 之前默認

MEMORY:內存,速度快,不能持久化,主從同步的有問題

二、crash-safe

實現 crash-safe:MySQL 異常重啟,數據不會丟失

實現方式:WAL(預寫式日志) 技術,先寫日志再把數據寫磁盤,保證數據操作的原子性和持久性

redolog

InnoDB 存儲引擎功能,記錄的是物理日志,記錄的是數據頁的物理修改(指 InooDB 中同一數據頁在磁盤上和內存中的差異,因為是順序IO,性能會比隨機IO快,這種數據頁叫臟頁)

記錄方式,循環覆寫,固定的大小,不能用于歸檔。

binlog

MySQL 基本功能,不限存儲引擎,只記錄數據的變化,是邏輯日志

歸檔日志,可用于服務器之間主從同步,備份恢復

兩階段提交

redolog 保證了 MySQL 數據不丟失

binlog 用于歸檔,保證了數據庫可以備份每一次事務提交

如何保證歸檔的數據和當前的數據一致,也就是如果讓 “當前數據” 與 “備份數據” 一致或主機與從機數據一致。

MySQL 的實現方案,過程如下:

執行SQL->更新內存中的數據頁(如果沒有從磁盤中加載到內存)-> 寫入redolog(redolog prepare 狀態) -> 寫binlog->提交事務(redolog commit 狀態)

寫日志執行過程為兩階段提交 prepare->commit ,保證了數據邏輯上的一致性

三、性能日志與磁盤IO的關系

數據庫的主要性能瓶頸就是磁盤IO,相對于內存來說,磁盤太慢了。要提高性能就需要減少刷盤次數。

配置文件中,innodb_flush_log_at_trx_commit 用于控制 redolog 日志持久化策略

設置為1,redolog 每次事務提交都直接持久化到磁盤設置為2,每次只寫到 page cache

后臺有一個線程每秒寫盤(write + fsync),刷盤的時候可以順帶把其它未提交的事務數據刷新,有數據丟失的風險,最多丟失1秒的數據,但是IO性能會更好。

將某個臟頁刷新到磁盤前,會先保證該臟頁對應的 redolog 刷新到磁盤中

此外,如果遇到大事務 redolog 緩存占用到了 innodb_log_buffer_size 的一半,會主動寫盤(只write)

配置文件中,sync_binlog 用于控制 binlog 日志持久化策略

設置為1,binlog 在每次事務提交都直接持久化到磁盤設置為N,表示每次只 write 到文件系統的 page cache,N個事務后 fsync。MySQL 崩潰不會丟數據,但是系統斷電會丟最多N個數據,一般設置為100-1000

上面兩階段提交,需要注意的是,redolog 刷盤時機是在兩階段中的 prepare,commit 其實不需要刷盤(重啟后校驗 binlog 完整性)

正常情況下,數據庫需要保證crash-safe, 必須配置成配置雙1(innodb_flush_log_at_trx_commit=1和sync_binlog=1),此時一個事務的提交會刷盤兩次(redolog 和 binlog)

配置文件中,innodb_log_buffer_size redolog 緩存大小,如果有大事務,可以設置大一些,默認16M

有時候為了提高性能,MySQL 會設置為非雙1,比如:

業務高峰備庫延遲追主庫恢復備份批量導入組提交

在 redolog 中有 LSN 日志邏輯序列號,記錄 redolog 寫入點,每次遞增日志的寫入長度。通過 LSN 可以判斷,在事務提交的時候可能其它事務已經幫你刷盤了,不需要重復刷盤。 讓 redolog 的刷盤盡量靠后,可以更好的利用組提交。

InnoDB 做了一個兩階段提交優化,redolog 的 fsync 放在 binlog write 之后

binlog 也有組提交,不過一般用不上,因為 redolog 的 fsync 步驟比較快,達不到積累binlog日志的效果。

在 sync_binlog 設置為1時如果你想提升 binlog 組提交的效果,可以修改如下配置:

配置文件中 binlog_group_commit_sync_delay 參數,表示延遲多少微秒后才調用 fsync;

配置文件中 binlog_group_commit_sync_no_delay_count 參數,表示累積多少次事務以后才調用 fsync。

以上兩個參數是或的關系,滿足一個就會 fsync。

這兩個參數原理是延遲 commit success 的返回,最長延遲 binlog_group_commit_sync_delay 微秒,所以上面兩個參數是無損的(對 crash-safe 沒有影響),但是會增加語句的響應時間

需要注意 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 的邏輯先走。等到滿足了這兩個條件之一,再進入 sync_binlog 參數控制的階段。

臟頁刷盤(InnoDB Buffer Pool)

配置文件中,innodb_buffer_pool_size innodb 緩存大小,建議設置為設置為主機內存的60-80%

配置文件中,innodb_change_buffer_max_size 設置 change buffer 占用 buffer pool 的比例,默認是50%

InnoDB 并沒有使用操作系統中文件系統自帶的緩存,而是自己實現緩存,InnoDB 中數據以頁為單位從磁盤中加載到內存,頁的大小一般為 16 KB,為減少磁盤 IO 負擔,當內存數據頁數據有修改時,并不會立即更新到磁盤中,而是等待特定的時機刷新。

記錄內存數據頁和磁盤數據頁之間物理差異就是 redolog 的工作, 臟頁的刷新和 redolog 息息相關。

刷臟頁(這里指內存數據頁與磁盤不一致,相反的叫干凈頁)的過程叫 flush,flush 時機有以下幾種情況:

redolog 滿了, 此時 MySQL 不能處理任何寫操作,這也是 MySQL 卡死的主要原因之一系統內存不足,干凈頁/臟頁都可能釋放,如果是臟頁,會觸發 flush空閑時正常開閉程序時

配置文件中,innodb_io_capacity 這個參數告訴 InnoDB 磁盤的性能,控制全力刷新磁盤時的性能,默認200

可以使用用 fio 測試磁盤的 IOPS,機械硬盤建議100,SSD 建議200或更高,比如1000,最高不建議超過20000

?touch /tmp/test_io?fio -filename=/tmp/test_io -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest

實際使用時,不可能讓磁盤一直全力去刷新,所以需要控制不同程度下動態處理使用不同的速度百分比

動態處理由兩個因素決定,兩個維度取更大的值刷新

臟頁比例redolog 生成速度(checkpoint 和 write pos差值)

配置文件中,innodb_max_dirty_pages_pct 用于設置臟頁百分比上限,默認90,意思是 InnoDB 會避免達到90這個值,如果達到了些值 InnoDB 會全力刷新

?-- 查詢臟頁比例?select VARIABLE_VALUE into @a from `performance_schema`.global_status where VARIABLE_NAME = 'innodb_buffer_pool_pages_dirty';?select VARIABLE_VALUE into @b from `performance_schema`.global_status where VARIABLE_NAME = 'innodb_buffer_pool_pages_total';?select @a/@b;

checkpoint 是 redolog 中記錄要擦除的LSN序號 InnoDB 每次寫入的日志都有一個LSN序號 write pos 直接刷臟頁是不會動 redolog 的,等后續應用 redolog 的時候,會根據LSN 的大小來判斷這個頁有沒有應用到這條 log

配置文件中,innodb_flush_neighbors 表示如果臟頁旁邊也是臟頁,是否一起flush,用于機械盤設置為1,用于 ssd 建議設置為0(MySQL8的默認值)

到此這篇關于MySQL高效可靠處理持久化數據的教程指南的文章就介紹到這了,更多相關MySQL處理持久化數據內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網!

標簽: MySQL 數據庫
主站蜘蛛池模板: 运动木地板价格,篮球馆体育运动木地板生产厂家_欧氏地板 | 3d打印服务,3d打印汽车,三维扫描,硅胶复模,手板,快速模具,深圳市精速三维打印科技有限公司 | 超声波焊接机,振动摩擦焊接机,激光塑料焊接机,超声波焊接模具工装-德召尼克(常州)焊接科技有限公司 | 北京晚会活动策划|北京节目录制后期剪辑|北京演播厅出租租赁-北京龙视星光文化传媒有限公司 | 无锡门窗-系统门窗-阳光房-封阳台-断桥铝门窗厂[窗致美] | 道康宁消泡剂-瓦克-大川进口消泡剂供应商 | 充气膜专家-气膜馆-PTFE膜结构-ETFE膜结构-商业街膜结构-奥克金鼎 | 流量检测仪-气密性检测装置-密封性试验仪-东莞市奥图自动化科技有限公司 | 食品级焦亚硫酸钠_工业级焦亚硫酸钠_焦亚硫酸钠-潍坊邦华化工有限公司 | 艺术生文化课培训|艺术生文化课辅导冲刺-济南启迪学校 | 臻知网大型互动问答社区-你的问题将在这里得到解答!-无锡据风网络科技有限公司 | 胜为光纤光缆_光纤跳线_单模尾纤_光纤收发器_ODF光纤配线架厂家直销_北京睿创胜为科技有限公司 - 北京睿创胜为科技有限公司 | PVC地板|PVC塑胶地板|PVC地板厂家|地板胶|防静电地板-无锡腾方装饰材料有限公司-咨询热线:4008-798-128 | 合肥网带炉_安徽箱式炉_钟罩炉-合肥品炙装备科技有限公司 | 偏心半球阀-电动偏心半球阀-调流调压阀-旋球阀-上欧阀门有限公司 | 聚丙烯酰胺_厂家_价格-河南唐达净水材料有限公司 | 聚合氯化铝_喷雾聚氯化铝_聚合氯化铝铁厂家_郑州亿升化工有限公司 | 机械加工_绞车配件_立式离心机_减速机-洛阳三永机械厂 | FFU_空气初效|中效|高效过滤器_空调过滤网-广州梓净净化设备有限公司 | 广州冷却塔维修厂家_冷却塔修理_凉水塔风机电机填料抢修-广东康明节能空调有限公司 | 蒜肠网-动漫,二次元,COSPLAY,漫展以及收藏型模型,手办,玩具的新媒体.(原变形金刚变迷TF圈) | 北京律师事务所_房屋拆迁律师_24小时免费法律咨询_云合专业律师网 | 全自动包装秤_全自动上袋机_全自动套袋机_高位码垛机_全自动包装码垛系统生产线-三维汉界机器(山东)股份有限公司 | 江苏全风,高压风机,全风环保风机,全风环形高压风机,防爆高压风机厂家-江苏全风环保科技有限公司(官网) | 耐高温电缆厂家-远洋高温电缆 | 卫生纸复卷机|抽纸机|卫生纸加工设备|做卫生纸机器|小型卫生纸加工需要什么设备|卫生纸机器设备多少钱一台|许昌恒源纸品机械有限公司 | 耐高温风管_耐高温软管_食品级软管_吸尘管_钢丝软管_卫生级软管_塑料波纹管-东莞市鑫翔宇软管有限公司 | 车牌识别道闸_停车场收费系统_人脸识别考勤机_速通门闸机_充电桩厂家_中全清茂官网 | 仿古建筑设计-仿古建筑施工-仿古建筑公司-汉匠古建筑设计院 | 塑木弯曲试验机_铜带拉伸强度试验机_拉压力测试台-倾技百科 | 沈阳缠绕包装机厂家直销-沈阳海鹞托盘缠绕包装机价格 | 专业广州网站建设,微信小程序开发,一物一码和NFC应用开发、物联网、外贸商城、定制系统和APP开发【致茂网络】 | 北京环球北美考试院【官方网站】|北京托福培训班|北京托福培训 | SDG吸附剂,SDG酸气吸附剂,干式酸性气体吸收剂生产厂家,超过20年生产使用经验。 - 富莱尔环保设备公司(原名天津市武清县环保设备厂) | 耳模扫描仪-定制耳机设计软件-DLP打印机-asiga打印机-fitshape「飞特西普」 | 【铜排折弯机,钢丝折弯成型机,汽车发泡钢丝折弯机,线材折弯机厂家,线材成型机,铁线折弯机】贝朗折弯机厂家_东莞市贝朗自动化设备有限公司 | 上海橡胶接头_弹簧减震器_金属软接头厂家-上海淞江集团 | 欧景装饰设计工程有限公司-无锡欧景装饰官网 | TTCMS自助建站_网站建设_自助建站_免费网站_免费建站_天天向上旗下品牌 | 两头忙,井下装载机,伸缩臂装载机,30装载机/铲车,50装载机/铲车厂家_价格-莱州巨浪机械有限公司 | pbootcms网站模板|织梦模板|网站源码|jquery建站特效-html5模板网 |