1、簡(jiǎn)介

相當(dāng)于Linux文件系統(tǒng)咏窿，只不過(guò)比文件系統(tǒng)強(qiáng)大

2斟或、功能了解

數(shù)據(jù)讀寫(xiě)數(shù)據(jù)安全和一致性提高性能熱備份自動(dòng)故障恢復(fù)高可用方面支持等.

3、存儲(chǔ)引擎種類(lèi)（筆試）

3.1 介紹(Oracle MySQL)

InnoDBMyISAMMEMORYARCHIVEFEDERATEDEXAMPLEBLACKHOLEMERGENDBCLUSTERCSV

3.2 引擎種類(lèi)查看

show engines;存儲(chǔ)引擎是作用在表上的集嵌，也就意味著萝挤，不同的表可以有不同的存儲(chǔ)引擎類(lèi)型御毅。PerconaDB:默認(rèn)是XtraDBMariaDB:默認(rèn)是InnoDB其他的存儲(chǔ)引擎支持:TokuDB? ? RocksDBMyRocks以上三種存儲(chǔ)引擎的共同點(diǎn):壓縮比較高,數(shù)據(jù)插入性能極高現(xiàn)在很多的NewSQL,使用比較多的功能特性.

3.3 簡(jiǎn)歷案例---zabbix監(jiān)控系統(tǒng)架構(gòu)整改

環(huán)境:zabbix 3.2? ? mariaDB 5.5? centos 7.3現(xiàn)象:zabbix卡的要死,每隔3-4個(gè)月,都要重新搭建一遍zabbix,存儲(chǔ)空間經(jīng)常爆滿(mǎn).問(wèn)題:1. zabbix 版本 2. 數(shù)據(jù)庫(kù)版本3. zabbix數(shù)據(jù)庫(kù)500G,存在一個(gè)文件里優(yōu)化建議:1.數(shù)據(jù)庫(kù)版本升級(jí)到5.7版本,zabbix升級(jí)更高版本2.存儲(chǔ)引擎改為tokudb3.監(jiān)控?cái)?shù)據(jù)按月份進(jìn)行切割(二次開(kāi)發(fā):zabbix 數(shù)據(jù)保留機(jī)制功能重寫(xiě),數(shù)據(jù)庫(kù)分表)4.關(guān)閉binlog和雙15.參數(shù)調(diào)整....優(yōu)化結(jié)果:監(jiān)控狀態(tài)良好為什么?1. 原生態(tài)支持TokuDB,另外經(jīng)過(guò)測(cè)試環(huán)境,5.7要比5.5 版本性能 高? 2-3倍2.TokuDB:insert數(shù)據(jù)比Innodb快的多，數(shù)據(jù)壓縮比要Innodb高3.監(jiān)控?cái)?shù)據(jù)按月份進(jìn)行切割,為了能夠truncate每個(gè)分區(qū)表,立即釋放空間4.關(guān)閉binlog ----->減少無(wú)關(guān)日志的記錄.5.參數(shù)調(diào)整...----->安全性參數(shù)關(guān)閉,提高性能.

3.4 InnoDB個(gè)MyISAM存儲(chǔ)引擎的替換

環(huán)境:centos 5.8,MySQL 5.0版本,MyISAM存儲(chǔ)引擎,網(wǎng)站業(yè)務(wù)(LNMP),數(shù)據(jù)量50G左右現(xiàn)象問(wèn)題:業(yè)務(wù)壓力大的時(shí)候,非沉洌卡;經(jīng)歷過(guò)宕機(jī),會(huì)有部分?jǐn)?shù)據(jù)丟失.問(wèn)題分析:1.MyISAM存儲(chǔ)引擎表級(jí)鎖,在高并發(fā)時(shí),會(huì)有很高鎖等待2.MyISAM存儲(chǔ)引擎不支持事務(wù),在斷電時(shí),會(huì)有可能丟失數(shù)據(jù)職責(zé)1.監(jiān)控鎖的情況:有很多的表鎖等待2.存儲(chǔ)引擎查看:所有表默認(rèn)是MyISAM解決方案:1.升級(jí)MySQL 5.6.10版本2. 遷移所有表到新環(huán)境3. 開(kāi)啟雙1安全參數(shù)

4端蛆、InnoDB存儲(chǔ)引擎介紹

image.png

在MySQL5.5版本之后，默認(rèn)的存儲(chǔ)引擎酥泛，提供高可靠性和高性能今豆。

4.1 優(yōu)點(diǎn)

1、事務(wù)（Transaction）2揭璃、MVCC（Multi-Version Concurrency Control多版本并發(fā)控制）3晚凿、行級(jí)鎖(Row-level Lock)4亭罪、ACSR（Auto Crash Safey Recovery）自動(dòng)的故障安全恢復(fù)5瘦馍、支持熱備份(Hot Backup)6、Replication:Group Commit,GTID(GlobalTransactionID),多線程(Multi-Threads-SQL)

4.2 筆試題

請(qǐng)你列舉MySQL InnoDB存儲(chǔ)優(yōu)點(diǎn)应役？請(qǐng)你列舉 InooDB和MyIsam的區(qū)別情组？

5. 存儲(chǔ)引擎查看

5.1 使用 SELECT 確認(rèn)會(huì)話(huà)存儲(chǔ)引擎

SELECT @@default_storage_engine;## 5.2 存儲(chǔ)引擎(不代表生產(chǎn)操作)會(huì)話(huà)級(jí)別:setdefault_storage_engine=myisam;全局級(jí)別(僅影響新會(huì)話(huà)):setglobaldefault_storage_engine=myisam;重啟之后,所有參數(shù)均失效.如果要永久生效:寫(xiě)入配置文件vim/etc/my.cnf[mysqld]default_storage_engine=myisam存儲(chǔ)引擎是表級(jí)別的,每個(gè)表創(chuàng)建時(shí)可以指定不同的存儲(chǔ)引擎,但是我們建議統(tǒng)一為innodb.

5.3 SHOW 確認(rèn)每個(gè)表的存儲(chǔ)引擎：

SHOW CREATE TABLE City\G;SHOW TABLE STATUS LIKE 'CountryLanguage'\G

5.4 INFORMATION_SCHEMA 確認(rèn)每個(gè)表的存儲(chǔ)引擎

[world]>selecttable_schema,table_name,enginefrominformation_schema.tableswheretable_schema notin('sys','mysql','information_schema','performance_schema');Master[world]>show table status;Master[world]>show create table city;

5.5 修改一個(gè)表的存儲(chǔ)引擎

db01 [oldboy]>alter table t1 engine innodb;注意：此命令我們經(jīng)常使用他，進(jìn)行innodb表的碎片整理

5.6 平常處理過(guò)的MySQL問(wèn)題--碎片處理

環(huán)境:centos7.4,MySQL 5.7.20,InnoDB存儲(chǔ)引擎業(yè)務(wù)特點(diǎn):數(shù)據(jù)量級(jí)較大,經(jīng)常需要按月刪除歷史數(shù)據(jù).問(wèn)題:磁盤(pán)空間占用很大,不釋放處理方法:以前:將數(shù)據(jù)邏輯導(dǎo)出,手工drop表,然后導(dǎo)入進(jìn)去現(xiàn)在:對(duì)表進(jìn)行按月進(jìn)行分表(partition,中間件)業(yè)務(wù)替換為truncate方式

5.6? 擴(kuò)展:如何批量修改

需求:將zabbix庫(kù)中的所有表,innodb替換為tokudbselectconcat("alter table zabbix.",table_name," engine tokudb;")frominformation_schema.tableswheretable_schema='zabbix'intooutfile'/tmp/tokudb.sql';

6箩祥、InnoDB存儲(chǔ)引擎物理存儲(chǔ)結(jié)構(gòu)

6.0 最直觀的存儲(chǔ)方式(/data/mysql/data)

ibdata1：系統(tǒng)數(shù)據(jù)字典信息(統(tǒng)計(jì)信息)院崇，UNDO表空間等數(shù)據(jù)ib_logfile0 ~ ib_logfile1: REDO日志文件，事務(wù)日志文件袍祖。ibtmp1： 臨時(shí)表空間磁盤(pán)位置底瓣，存儲(chǔ)臨時(shí)表frm：存儲(chǔ)表的列信息ibd：表的數(shù)據(jù)行和索引

6.1 表空間(Tablespace)

6.1.1、共享表空間

需要將所有數(shù)據(jù)存儲(chǔ)到同一個(gè)表空間中 蕉陋，管理比較混亂5.5版本出現(xiàn)的管理模式捐凭，也是默認(rèn)的管理模式。5.6版本以凳鬓，共享表空間保留茁肠，只用來(lái)存儲(chǔ):數(shù)據(jù)字典信息,undo,臨時(shí)表。5.7 版本,臨時(shí)表被獨(dú)立出來(lái)了8.0版本,undo也被獨(dú)立出去了

具體變化參考官方文檔:

https://dev.mysql.com/doc/refman/5.6/en/innodb-architecture.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html

https://dev.mysql.com/doc/refman/5.8/en/innodb-architecture.html

6.1.2 共享表空間設(shè)置

共享表空間設(shè)置(在搭建MySQL時(shí)缩举，初始化數(shù)據(jù)之前設(shè)置到參數(shù)文件中)[(none)]>select@@innodb_data_file_path;[(none)]>show variables like'%extend%';innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextendinnodb_autoextend_increment=64

6.1.3 獨(dú)立表空間

從5.6垦梆，默認(rèn)表空間不再使用共享表空間，替換為獨(dú)立表空間仅孩。主要存儲(chǔ)的是用戶(hù)數(shù)據(jù)存儲(chǔ)特點(diǎn)為：一個(gè)表一個(gè)ibd文件托猩，存儲(chǔ)數(shù)據(jù)行和索引信息基本表結(jié)構(gòu)元數(shù)據(jù)存儲(chǔ)：xxx.frm最終結(jié)論：? ? ? 元數(shù)據(jù)? ? ? ? ? ? 數(shù)據(jù)行+索引mysql表數(shù)據(jù)=（ibdataX+frm）+ibd(段、區(qū)辽慕、頁(yè))DDL? ? ? ? ? ? DML+DQLMySQL的存儲(chǔ)引擎日志：Redo Log:ib_logfile0? ib_logfile1站刑，重做日志Undo Log:ibdata1ibdata2(存儲(chǔ)在共享表空間中)，回滾日志臨時(shí)表:ibtmp1鼻百，在做joinunion操作產(chǎn)生臨時(shí)數(shù)據(jù)绞旅，用完就自動(dòng)

6.1.4 獨(dú)立表空間設(shè)置問(wèn)題

db01[(none)]>select @@innodb_file_per_table;+-------------------------+|@@innodb_file_per_table|+-------------------------+|1|+-------------------------+alter table city dicard tablespace;alter table cityimporttablespace;

6.1.5 真實(shí)的學(xué)生案例

案例背景:

硬件及軟件環(huán)境:聯(lián)想服務(wù)器（IBM） 磁盤(pán)500G 沒(méi)有raidcentos 6.8mysql 5.6.33? innodb引擎? 獨(dú)立表空間備份沒(méi)有摆尝，日志也沒(méi)開(kāi)開(kāi)發(fā)用戶(hù)專(zhuān)用庫(kù):jira(bug追蹤)、confluence(內(nèi)部知識(shí)庫(kù))------>LNMT

故障描述:

斷電了因悲，啟動(dòng)完成后“/” 只讀fsck? 重啟,系統(tǒng)成功啟動(dòng),mysql啟動(dòng)不了堕汞。結(jié)果：confulence庫(kù)在? ， jira庫(kù)不見(jiàn)了

學(xué)員求助內(nèi)容:

求助：這種情況怎么恢復(fù)晃琳？我問(wèn)：有備份沒(méi)求助：連二進(jìn)制日志都沒(méi)有讯检，沒(méi)有備份，沒(méi)有主從我說(shuō)：沒(méi)招了卫旱，jira需要硬盤(pán)恢復(fù)了人灼。求助：1、jira問(wèn)題拉倒中關(guān)村了2顾翼、能不能暫時(shí)把confulence庫(kù)先打開(kāi)用著將生產(chǎn)庫(kù)confulence投放，拷貝到1:1虛擬機(jī)上/var/lib/mysql,直接訪問(wèn)時(shí)訪問(wèn)不了的問(wèn)：有沒(méi)有工具能直接讀取ibd我說(shuō)：我查查，最后發(fā)現(xiàn)沒(méi)有

我想出一個(gè)辦法來(lái)：

表空間遷移:create table xxxalter table? confulence.t1 discard tablespace;alter table confulence.t1 import tablespace;虛擬機(jī)測(cè)試可行适贸。

處理問(wèn)題思路:

confulence庫(kù)中一共有107張表灸芳。1、創(chuàng)建107和和原來(lái)一模一樣的表拜姿。他有2016年的歷史庫(kù)烙样，我讓他去他同時(shí)電腦上 mysqldump備份confulence庫(kù)mysqldump-uroot-ppassw0rd-Bconfulence--no-data>test.sql拿到你的測(cè)試庫(kù)，進(jìn)行恢復(fù)到這步為止蕊肥，表結(jié)構(gòu)有了谒获。2、表空間刪除壁却。selectconcat('alter table ',table_schema,'.'table_name,' discard tablespace;')frominformation_schema.tableswheretable_schema='confluence'intooutfile'/tmp/discad.sql';source/tmp/discard.sql執(zhí)行過(guò)程中發(fā)現(xiàn)批狱，有20-30個(gè)表無(wú)法成功。主外鍵關(guān)系很絕望儒洛，一個(gè)表一個(gè)表分析表結(jié)構(gòu)精耐，很痛苦。setforeign_key_checks=0跳過(guò)外鍵檢查琅锻。把有問(wèn)題的表表空間也刪掉了卦停。3、拷貝生產(chǎn)中confulence庫(kù)下的所有表的ibd文件拷貝到準(zhǔn)備好的環(huán)境中selectconcat('alter table ',table_schema,'.'table_name,' import tablespace;')frominformation_schema.tableswheretable_schema='confluence'intooutfile'/tmp/discad.sql';4恼蓬、驗(yàn)證數(shù)據(jù)表都可以訪問(wèn)了惊完，數(shù)據(jù)挽回到了出現(xiàn)問(wèn)題時(shí)刻的狀態(tài)（2-8）

8、事務(wù)的ACID特性

Atomic（原子性）

所有語(yǔ)句作為一個(gè)單元全部成功執(zhí)行或全部取消处硬。不能出現(xiàn)中間狀態(tài)小槐。

Consistent（一致性）

如果數(shù)據(jù)庫(kù)在事務(wù)開(kāi)始時(shí)處于一致?tīng)顟B(tài)，則在執(zhí)行該事務(wù)期間將保留一致?tīng)顟B(tài)。

Isolated（隔離性）

事務(wù)之間不相互影響凿跳。

Durable（持久性）

事務(wù)成功完成后件豌，所做的所有更改都會(huì)準(zhǔn)確地記錄在數(shù)據(jù)庫(kù)中。所做的更改不會(huì)丟失控嗜。

9茧彤、事務(wù)的生命周期（事務(wù)控制語(yǔ)句）

9.1 事務(wù)的開(kāi)始

begin說(shuō)明:在5.5以上的版本，不需要手工begin疆栏，只要你執(zhí)行的是一個(gè)DML曾掂，會(huì)自動(dòng)在前面加一個(gè)begin命令。

9.2 事務(wù)的結(jié)束

commit：提交事務(wù)完成一個(gè)事務(wù)壁顶，一旦事務(wù)提交成功 珠洗，就說(shuō)明具備ACID特性了。rollback ：回滾事務(wù)將內(nèi)存中若专，已執(zhí)行過(guò)的操作许蓖，回滾回去

9.3 自動(dòng)提交策略（autocommit）

db01[(none)]>select@@autocommit;db01[(none)]>setautocommit=0;db01[(none)]>setglobalautocommit=0;注：自動(dòng)提交是否打開(kāi)，一般在有事務(wù)需求的MySQL中富岳，將其關(guān)閉不管有沒(méi)有事務(wù)需求蛔糯，我們一般也都建議設(shè)置為0拯腮，可以很大程度上提高數(shù)據(jù)庫(kù)性能(1)setautocommit=0;setglobalautocommit=0;(2)vim/etc/my.cnfautocommit=0

9.4? 隱式提交語(yǔ)句

用于隱式提交的SQL語(yǔ)句：beginabbeginSETAUTOCOMMIT=1導(dǎo)致提交的非事務(wù)語(yǔ)句：DDL語(yǔ)句： （ALTER窖式、CREATE和DROP）DCL語(yǔ)句： （GRANT、REVOKE和SETPASSWORD）鎖定語(yǔ)句：（LOCKTABLES和UNLOCKTABLES）導(dǎo)致隱式提交的語(yǔ)句示例：TRUNCATETABLELOADDATAINFILESELECTFORUPDATE

9.5 開(kāi)始事務(wù)流程：

1动壤、檢查autocommit是否為關(guān)閉狀態(tài)select@@autocommit;或者：show variables like'autocommit';2萝喘、開(kāi)啟事務(wù),并結(jié)束事務(wù)begindeletefromstudentwherename='alexsb';update studentsetname='alexsb'wherename='alex';rollback;begindeletefromstudentwherename='alexsb';update studentsetname='alexsb'wherename='alex';commit;

10. InnoDB 事務(wù)的ACID如何保證?

10.0 一些概念

redolog--->重做日志 ib_logfile0~150M,輪詢(xún)使用redolog buffer--->redo內(nèi)存區(qū)域ibd---->存儲(chǔ) 數(shù)據(jù)行和索引 buffer pool--->緩沖區(qū)池,數(shù)據(jù)和索引的緩沖LSN:日志序列號(hào) 磁盤(pán)數(shù)據(jù)頁(yè),redo文件,buffer pool,redobufferMySQL每次數(shù)據(jù)庫(kù)啟動(dòng),都會(huì)比較磁盤(pán)數(shù)據(jù)頁(yè)和redolog的LSN,必須要求兩者LSN一致數(shù)據(jù)庫(kù)才能正常啟動(dòng)WAL:write ahead log 日志優(yōu)先寫(xiě)的方式實(shí)現(xiàn)持久化臟頁(yè):內(nèi)存臟頁(yè),內(nèi)存中發(fā)生了修改,沒(méi)寫(xiě)入到磁盤(pán)之前,我們把內(nèi)存頁(yè)稱(chēng)之為臟頁(yè).CKPT:Checkpoint,檢查點(diǎn),就是將臟頁(yè)刷寫(xiě)到磁盤(pán)的動(dòng)作TXID:事務(wù)號(hào),InnoDB會(huì)為每一個(gè)事務(wù)生成一個(gè)事務(wù)號(hào),伴隨著整個(gè)事務(wù).

need-to-insert-img

image

10.1 redo log

10.1.1 Redo是什么？

redo,顧名思義“重做日志”琼懊，是事務(wù)日志的一種阁簸。

10.1.2 作用是什么？

在事務(wù)ACID過(guò)程中哼丈，實(shí)現(xiàn)的是“D”持久化的作用启妹。對(duì)于AC也有相應(yīng)的作用

10.1.3 redo日志位置

redo的日志文件：iblogfile0 iblogfile1

10.1.4 redo buffer

redo的buffer:數(shù)據(jù)頁(yè)的變化信息+數(shù)據(jù)頁(yè)當(dāng)時(shí)的LSN號(hào)LSN：日志序列號(hào)? 磁盤(pán)數(shù)據(jù)頁(yè)、內(nèi)存數(shù)據(jù)頁(yè)醉旦、redobuffer饶米、redolog

10.1.5 redo的刷新策略

commit;刷新當(dāng)前事務(wù)的redobuffer到磁盤(pán)還會(huì)順便將一部分redobuffer中沒(méi)有提交的事務(wù)日志也刷新到磁盤(pán)

10.1.6 MySQL CSR——前滾

MySQL:在啟動(dòng)時(shí),必須保證redo日志文件和數(shù)據(jù)文件LSN必須一致,如果不一致就會(huì)觸發(fā)CSR,最終保證一致情況一:我們做了一個(gè)事務(wù),begin;update;commit.1.在begin,會(huì)立即分配一個(gè)TXID=tx_01.2.update時(shí),會(huì)將需要修改的數(shù)據(jù)頁(yè)(dp_01,LSN=101),加載到data buffer中3.DBWR線程,會(huì)進(jìn)行dp_01數(shù)據(jù)頁(yè)修改更新,并更新LSN=1024.LOGBWR日志寫(xiě)線程,會(huì)將dp_01數(shù)據(jù)頁(yè)的變化+LSN+TXID存儲(chǔ)到redobuffer5.執(zhí)行commit時(shí),LGWR日志寫(xiě)線程會(huì)將redobuffer信息寫(xiě)入redolog日志文件中,基于WAL原則,在日志完全寫(xiě)入磁盤(pán)后,commit命令才執(zhí)行成功,(會(huì)將此日志打上commit標(biāo)記)6.假如此時(shí)宕機(jī),內(nèi)存臟頁(yè)沒(méi)有來(lái)得及寫(xiě)入磁盤(pán),內(nèi)存數(shù)據(jù)全部丟失7.MySQL再次重啟時(shí),必須要redolog和磁盤(pán)數(shù)據(jù)頁(yè)的LSN是一致的.但是,此時(shí)dp_01,TXID=tx_01磁盤(pán)是LSN=101,dp_01,TXID=tx_01,redolog中LSN=102MySQL此時(shí)無(wú)法正常啟動(dòng),MySQL觸發(fā)CSR.在內(nèi)存追平LSN號(hào),觸發(fā)ckpt,將內(nèi)存數(shù)據(jù)頁(yè)更新到磁盤(pán),從而保證磁盤(pán)數(shù)據(jù)頁(yè)和redologLSN一值.這時(shí)MySQL正長(zhǎng)啟動(dòng)以上的工作過(guò)程,我們把它稱(chēng)之為基于REDO的"前滾操作"

11.2 undo 回滾日志

11.2.1 undo是什么？

undo,顧名思義“回滾日志”

11.2.2 作用是什么车胡？

在事務(wù)ACID過(guò)程中檬输，實(shí)現(xiàn)的是“A” 原子性的作用另外CI也依賴(lài)于Undo在rolback時(shí),將數(shù)據(jù)恢復(fù)到修改之前的狀態(tài)在CSR實(shí)現(xiàn)的是,將redo當(dāng)中記錄的未提交的時(shí)候進(jìn)行回滾.undo提供快照技術(shù),保存事務(wù)修改之前的數(shù)據(jù)狀態(tài).保證了MVCC,隔離性,mysqldump的熱備

11.3 概念性的東西:

redo怎么應(yīng)用的undo怎么應(yīng)用的CSR(自動(dòng)故障恢復(fù))過(guò)程LSN:日志序列號(hào)TXID:事務(wù)IDCKPT(Checkpoint)

11.4 鎖

“鎖”顧名思義就是鎖定的意思⌒偌“鎖”的作用是什么丧慈？在事務(wù)ACID過(guò)程中，“鎖”和“隔離級(jí)別”一起來(lái)實(shí)現(xiàn)“I”隔離性和"C"一致性(redo也有參與).悲觀鎖:行級(jí)鎖定(行鎖)誰(shuí)先操作某個(gè)數(shù)據(jù)行,就會(huì)持有<這行>的(X)鎖.樂(lè)觀鎖:沒(méi)有鎖

11.5 隔離級(jí)別

影響到數(shù)據(jù)的讀取,默認(rèn)的級(jí)別是 RR模式.transaction_isolation? 隔離級(jí)別(參數(shù))負(fù)責(zé)的是,MVCC,讀一致性問(wèn)題RU:讀未提交,可臟讀,一般部議敘出現(xiàn)RC:讀已提交,可能出現(xiàn)幻讀,可以防止臟讀.RR:可重復(fù)讀,功能是防止"幻讀"現(xiàn)象,利用的是undo的快照技術(shù)+GAP(間隙鎖)+NextLock(下鍵鎖)SR:可串行化,可以防止死鎖,但是并發(fā)事務(wù)性能較差補(bǔ)充:在RC級(jí)別下,可以減輕GAP+NextLock鎖的問(wèn)題,但是會(huì)出現(xiàn)幻讀現(xiàn)象,一般在為了讀一致性會(huì)在正常select后添加forupdate語(yǔ)句.但是,請(qǐng)記住執(zhí)行完一定要commit 否則容易出現(xiàn)所等待比較嚴(yán)重.例如:[world]>select*fromcitywhereid=999forupdate;[world]>commit;

11.6 架構(gòu)改造項(xiàng)目

項(xiàng)目背景:2臺(tái)? IBM X3650? 32G,原來(lái)主從關(guān)系,2年多沒(méi)有主從了,"小問(wèn)題"不斷(鎖,宕機(jī)后的安全)MySQL 5.1.77? 默認(rèn)存儲(chǔ)引擎 MyISAM數(shù)據(jù)量:60G左右,每周全備,沒(méi)有開(kāi)二進(jìn)制日志架構(gòu)方案:1. 升級(jí)數(shù)據(jù)庫(kù)版本到5.7.20? ? 2. 更新所有業(yè)務(wù)表的存儲(chǔ)引擎為InnoDB? ? 3. 重新設(shè)計(jì)備份策略為熱備份,每天全備,并備份日志? ? 4. 重新構(gòu)建主從結(jié)果:1.性能? ? 2.安全方面? ? 3.快速故障處理

12 InnoDB存儲(chǔ)引擎核心特性-參數(shù)補(bǔ)充

12.1 存儲(chǔ)引擎相關(guān)

12.1.1 查看

show engines;show variables like'default_storage_engine';select @@default_storage_engine;

12.1.2 如何指定和修改存儲(chǔ)引擎

(1) 通過(guò)參數(shù)設(shè)置默認(rèn)引擎(2) 建表的時(shí)候進(jìn)行設(shè)置(3) alter table t1 engine=innodb;

12.2. 表空間

12.2.1 共享表空間

innodb_data_file_path一般是在初始化數(shù)據(jù)之前就設(shè)置好例子:innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend

12.2.2 獨(dú)立表空間

show variables like'innodb_file_per_table';

12.3. 緩沖區(qū)池

12.3.1 查詢(xún)

select @@innodb_buffer_pool_size;show engine innodb status\Ginnodb_buffer_pool_size 一般建議最多是物理內(nèi)存的 75-80%

12.4. innodb_flush_log_at_trx_commit? (雙一標(biāo)準(zhǔn)之一)

12.4.1 作用

主要控制了innodb將log buffer中的數(shù)據(jù)寫(xiě)入日志文件并flush磁盤(pán)的時(shí)間點(diǎn)主卫，取值分別為0逃默、1鹃愤、2三個(gè)。

12.4.2 查詢(xún)

select @@innodb_flush_log_at_trx_commit;

12.4.3 參數(shù)說(shuō)明:

1完域，每次事物的提交都會(huì)引起日志文件寫(xiě)入昼浦、flush磁盤(pán)的操作，確保了事務(wù)的ACID筒主；flush? 到操作系統(tǒng)的文件系統(tǒng)緩存? fsync到物理磁盤(pán).0关噪，表示當(dāng)事務(wù)提交時(shí)，不做日志寫(xiě)入操作乌妙，而是每秒鐘將log buffer中的數(shù)據(jù)寫(xiě)入文件系統(tǒng)緩存并且秒fsync磁盤(pán)一次使兔；2，每次事務(wù)提交引起寫(xiě)入文件系統(tǒng)緩存,但每秒鐘完成一次fsync磁盤(pán)操作藤韵。--------The default setting of 1 is required for full ACID compliance. Logs are written and flushed to disk at each transaction commit.With a setting of 0, logs are written and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.With a setting of 2, logs are written after each transaction commit and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.-------

12.5. Innodb_flush_method=(O_DIRECT, fdatasync)

image

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_method

12.5.1 作用

控制的是,log buffer 和data buffer,刷寫(xiě)磁盤(pán)的時(shí)候是否經(jīng)過(guò)文件系統(tǒng)緩存

12.5.2 查看

show variables like'%innodb_flush%';

12.5.3 參數(shù)值說(shuō)明

O_DIRECT? :數(shù)據(jù)緩沖區(qū)寫(xiě)磁盤(pán),不走OS bufferfsync :日志和數(shù)據(jù)緩沖區(qū)寫(xiě)磁盤(pán),都走OS bufferO_DSYNC? :日志緩沖區(qū)寫(xiě)磁盤(pán),不走 OS buffer

12.5.4 使用建議

最高安全模式innodb_flush_log_at_trx_commit=1Innodb_flush_method=O_DIRECT最高性能:innodb_flush_log_at_trx_commit=0Innodb_flush_method=fsync

12.6. redo日志有關(guān)的參數(shù)

innodb_log_buffer_size=16777216innodb_log_file_size=50331648innodb_log_files_in_group = 3

13.擴(kuò)展(自己擴(kuò)展虐沥，建議是官方文檔。)

RR模式(對(duì)索引進(jìn)行刪除時(shí)):GAP:間隙鎖next-lock:下一鍵鎖定例子:id（有索引）123456GAP：在對(duì)3這個(gè)值做變更時(shí)泽艘，會(huì)產(chǎn)生兩種鎖欲险，一種是本行的行級(jí)鎖，另一種會(huì)在2和4索引鍵上進(jìn)行枷鎖next-lock：對(duì)第六行變更時(shí)匹涮，一種是本行的行級(jí)鎖天试，在索引末尾鍵進(jìn)行加鎖，6以后的值在這時(shí)是不能被插入的然低∠裁浚總之：GAP、nextlock都是為了保證RR模式下雳攘，不會(huì)出現(xiàn)幻讀带兜，降低隔離級(jí)別或取消索引，這兩種鎖都不會(huì)產(chǎn)生吨灭。IXISXS是什么?

17.小結(jié)

17.1 存儲(chǔ)引擎的類(lèi)型

17.2 InnoDB和MyISAM

17.3 事務(wù)ACID

17.4 redo

17.5 undo

17.6 鎖

17.7 隔離級(jí)別

17.8 核心參數(shù)

作者：MySQL_oldguo

鏈接：http://www.reibang.com/p/4a344bb75d36

來(lái)源：簡(jiǎn)書(shū)

著作權(quán)歸作者所有刚照。商業(yè)轉(zhuǎn)載請(qǐng)聯(lián)系作者獲得授權(quán)，非商業(yè)轉(zhuǎn)載請(qǐng)注明出處喧兄。