MySQL數(shù)據(jù)庫性能優(yōu)化史詩級(jí)大總結(jié)

+ 未提交讀
+ 已提交讀（Mysql不是默認(rèn)，SQLServer和Oracle是默認(rèn)）
+ 可重復(fù)讀（InnoDB默認(rèn)）
+ 可串行化

- 存儲(chǔ)容量
- 傳輸速度
- 訪問時(shí)間
- 主軸轉(zhuǎn)速
- 物理尺寸

RAID0 是最早出現(xiàn)的模式，成為數(shù)據(jù)條帶，是最簡單的一種方式，只需要2塊以上的硬盤即可，成本較低，可以提高整個(gè)磁盤的性能的吞吐量。RAID沒有提供榮譽(yù)或者錯(cuò)誤修復(fù)能力，但是實(shí)現(xiàn)成本較高。容量是所有磁盤的總和，并且性能是之前的N倍。
RAID1 是磁盤鏡像，原理就是把一個(gè)數(shù)據(jù)鏡像到另一個(gè)磁盤上，最大限度的保證系統(tǒng)的可靠性和可修復(fù)性。
RAID5 是分布式奇偶校驗(yàn)磁盤陣列，主要就是把數(shù)據(jù)分散到多個(gè)磁盤上，如果任何一個(gè)磁盤損壞都可以快速重建，但是如果2塊失效就全部無法恢復(fù)。最好使用在以讀取為主要任務(wù)的服務(wù)器上，比如從數(shù)據(jù)庫服務(wù)器。
RAID10 分片的磁盤鏡像，對(duì)磁盤先進(jìn)行RAID1，然后對(duì)兩組RAID1進(jìn)行RAID0，對(duì)讀寫都有較好的性能，重建速度快于RAID5。

內(nèi)核相關(guān)參數(shù)：/etc/sysctl.conf
net.core.somaxconn=65535
net.core.netdev_max_backlog=65535
net.ipv4.tcp_max_syn_backlog=65535
net.ipv4.tcp_fin_timeout=10
net.ipv4.tcp_tw_reuse=1
net.ipv4.tcp_tw_recycle=1
net.core.wmem_default=87380
net.core.wmem_max=16777216
net.core.rmem_default=87380
net.core.rmem_max=16777216
net.ipv4.tcp_keepalive_time=120
net.ipv4.tcp_keepalive_intvl=30
net.ipv4.tcp_keepalive_probes=3
kernel.shmmax=4294967295    # 用于定義單個(gè)共享內(nèi)存段的最大值, 
                            # 這個(gè)參數(shù)應(yīng)該設(shè)置的足夠大, 以便能在一個(gè)共享內(nèi)存段中容納下整個(gè)InnoDB緩沖池
                            # 對(duì)于x64的系統(tǒng), 可取的最大值為物理內(nèi)存的大小減去1字節(jié)，建議值為大于物理內(nèi)存的一半
vm.swappiness=0             # 該參數(shù)當(dāng)內(nèi)存不足時(shí)對(duì)性能產(chǎn)生比較明顯的影響, 設(shè)置內(nèi)存交換區(qū)大小
                            # 0表示除非Linux內(nèi)核非虛擬內(nèi)存完全滿了, 否則就不使用交換區(qū)
資源限制：/etc/security/limit.conf
該文件是Linux PAM，也就是插入式認(rèn)證模塊的配置文件, 打開文件數(shù)的限制。
* soft nofile 65535
* hard nofile 65535

參數(shù)：
* 表示對(duì)所有用戶有效
soft 表示當(dāng)前系統(tǒng)生效的設(shè)置
hard 表示系統(tǒng)中所能設(shè)置的最大值
nofile 表示所限制的資源是打開文件的最大數(shù)目
65535 表示限制的數(shù)量

磁盤調(diào)度策略：/sys/block/devname/queue/scheduler
默認(rèn)是使用cfq策略
noop（電梯式調(diào)度策略）, 實(shí)現(xiàn)了一個(gè)FIFO隊(duì)列, 像電梯一樣對(duì)IO請(qǐng)求進(jìn)行組織, 當(dāng)有一個(gè)新的請(qǐng)求到來時(shí), 將會(huì)合并到最近的請(qǐng)求之后
以此來保證同一請(qǐng)求同一個(gè)介質(zhì)。noop傾向餓死讀而利于寫, 一次noop對(duì)于閃存設(shè)備,RAM,嵌入式系統(tǒng)是最好的選擇。
deadline（截止時(shí)間調(diào)度策略）確保在一個(gè)時(shí)間內(nèi)服務(wù)請(qǐng)求, 這個(gè)截止時(shí)間是可以調(diào)整的, 而默認(rèn)讀期限短于寫期限。
這樣就會(huì)防止寫操作因?yàn)椴荒鼙蛔x取而餓死的現(xiàn)象, deadline對(duì)數(shù)據(jù)庫類應(yīng)用是最好的選擇。
anticipatory（預(yù)料IO調(diào)度策略）本質(zhì)上與deadline一樣, 但是最后一個(gè)讀操作后要等待6毫秒才能繼續(xù)進(jìn)行其他的IO請(qǐng)求進(jìn)行調(diào)度。
寫入流合并成為一個(gè)大的寫入流, 用寫入延時(shí)換區(qū)最大的寫入吞吐量, 這種策略適合于寫入較多的環(huán)境，比如文件服務(wù)器，該策略對(duì)數(shù)據(jù)環(huán)境表現(xiàn)較差

配置文件：/etc/fstab
data=writeback|ordered|journal
wirteback對(duì)于InnoDB引擎來說是最好的
noatime和nodiratime用來禁止記錄時(shí)間

并發(fā)性與鎖級(jí)別：使用表級(jí)鎖，讀取表中數(shù)據(jù)時(shí)也需要全表加共享鎖。
表損壞修復(fù)：支持對(duì)于任意關(guān)閉和其他情況導(dǎo)致的損壞修復(fù)，對(duì)MyISAM表進(jìn)行數(shù)據(jù)恢復(fù)可能造成數(shù)據(jù)丟失，因?yàn)椴恢С质聞?wù)。
使用 check table 表名稱 來檢查，使用 repair table 表名 來進(jìn)行恢復(fù)。
支持全文索引，支持?jǐn)?shù)據(jù)壓縮，適用于只讀操作的數(shù)據(jù)表，可以通過myisampack -b -f xxx.MYI進(jìn)行壓縮。
對(duì)于已經(jīng)壓縮的表只能讀取。

在Mysql5.0之前的版本中默認(rèn)表大小為最大4GB。
存儲(chǔ)大表則需要修改MAX_Rows和AVG_ROW_LENGTH參數(shù)。
在Mysql5.0以后，默認(rèn)表大小支持256TB。
使用場(chǎng)景：
適用于非事務(wù)應(yīng)用
適用于只讀類應(yīng)用
適用于空間類應(yīng)用

具體參數(shù)配置在innodb_file_per_file。
參數(shù)值為on的時(shí)候：表空間為TableName.ibd。
參數(shù)值為off的時(shí)候：表空間存儲(chǔ)在共享表空間：ibdataN,N表示一個(gè)數(shù)字。

在Mysql5.5版本之前的innodb_file_per_file默認(rèn)是off。
在一個(gè)繁忙的系統(tǒng)中，系統(tǒng)表空間會(huì)增大，但是刪除一部分無用的數(shù)據(jù)時(shí)系統(tǒng)表空間并沒有縮小，要通過復(fù)制數(shù)據(jù)來解決，也就是說系統(tǒng)表空間無法簡單的收縮文件。
系統(tǒng)表空間由于只有一個(gè)文件，多余多個(gè)表同時(shí)操作來說，系統(tǒng)表空間會(huì)產(chǎn)生IO瓶頸。
獨(dú)立表空間可以同時(shí)向多個(gè)文件刷新數(shù)據(jù)。
在Mysql5.6之后，默認(rèn)使用獨(dú)立表空間進(jìn)行管理，建議使用獨(dú)立表空間。

使用mysqldump導(dǎo)出所有數(shù)據(jù), 存儲(chǔ)過程/觸發(fā)器/視圖也要導(dǎo)出。
使用停止Mysql服務(wù)，修改參數(shù)，刪除InnoDB相關(guān)文件。
重啟Mysql服務(wù)，重建InnoDB系統(tǒng)表空間。
重新導(dǎo)入數(shù)據(jù)。

主要針對(duì)管理共享資源的并發(fā)訪問而設(shè)計(jì)。
實(shí)現(xiàn)事務(wù)的隔離性。
分為共享鎖（讀鎖）和獨(dú)占鎖（寫鎖）。
阻塞：一個(gè)鎖需要等待另一個(gè)鎖的釋放就會(huì)導(dǎo)致阻塞。
死鎖：兩個(gè)鎖及以上相互等待對(duì)方釋放鎖。

以文件方式存儲(chǔ)
csv文件存儲(chǔ)表內(nèi)容
csm文件存儲(chǔ)元數(shù)據(jù)和數(shù)據(jù)量
frm文件存儲(chǔ)表結(jié)構(gòu)
CSV所有列不允許為NULL
不支持索引，不適合大表和OLTP
可以直接編輯
適用于作為數(shù)據(jù)交換的中間表

支持插入和查詢操作
只允許在自增ID列上加索引
以zlib對(duì)數(shù)據(jù)表進(jìn)行壓縮，磁盤IO更少
數(shù)據(jù)存儲(chǔ)在arz文件中
支持行級(jí)鎖和標(biāo)準(zhǔn)的緩沖區(qū)，支持高并發(fā)插入
適用于日志和數(shù)據(jù)采集類的應(yīng)用

也稱之為Heap存儲(chǔ)引擎，所以數(shù)據(jù)保存在內(nèi)存中。
只有一個(gè)表結(jié)構(gòu)frm文件。
支持Hash索引（默認(rèn)，適用于等值查找）和BTree索引（適用于范圍查找）。
所有字段都為定長。
不支持Blob和Text等大字段類型。
使用表級(jí)鎖。
數(shù)據(jù)表的最大大小由max_heap_table_size參數(shù)決定， 對(duì)于已經(jīng)存在的數(shù)據(jù)表是不生效的。
Memory存儲(chǔ)引擎表和臨時(shí)表的區(qū)別是臨時(shí)表對(duì)于當(dāng)前會(huì)話是可見的，而Memory臨時(shí)表對(duì)于所有會(huì)話可見。
系統(tǒng)使用臨時(shí)表如果沒有超過限制將會(huì)使用Memory表否則使用MyISAM引擎的數(shù)據(jù)表。
會(huì)話臨時(shí)表使用create temporary table來創(chuàng)建。
用于查找或者是映射表。
用于保存數(shù)據(jù)分析產(chǎn)生的中間數(shù)據(jù)。
用于緩存周期性聚合數(shù)據(jù)的結(jié)果表。

提供遠(yuǎn)程訪問其他Mysql服務(wù)器上數(shù)據(jù)表的方法。
本地不保存數(shù)據(jù)，數(shù)據(jù)全部存放到遠(yuǎn)程服務(wù)器上。
本地需要保存遠(yuǎn)程表結(jié)構(gòu)和遠(yuǎn)程服務(wù)器連接信息。
默認(rèn)是禁止的，啟動(dòng)時(shí)需要指定參數(shù)federated
適用于偶爾的數(shù)據(jù)查詢中
engine=federated connection='mysql://UserName[:PassWord]@HostName[:PortName]/DBName/TableName';
查看是否支持該存儲(chǔ)引擎：

命令行參數(shù)：mysqld_safe --datadir=/data/sql_data。
配置文件， 查看配置文件讀取順序及位置：
mysqld --help --verbose | grep -A 1 'Default options' /etc/my.cnf

全局參數(shù)：set global 參數(shù)名=參數(shù)值;和set @@global.參數(shù)名 := 參數(shù)值;
會(huì)話參數(shù)：set [session] 參數(shù)名=參數(shù)值;和set @@session.參數(shù)名=參數(shù)值;

確定可以使用的內(nèi)存上限。
確定Mysql的每個(gè)連接使用的內(nèi)存。
sort_buffer_size設(shè)置排序需要的內(nèi)存。
join_buffer_size設(shè)置連接緩沖區(qū)的內(nèi)存。
read_buffer_size設(shè)置全表掃描時(shí)需要的內(nèi)存大小。
read_rnd_buffer_size設(shè)置索引緩沖區(qū)的大小。
如何為緩存池分配內(nèi)存：
    innodb_buffer_pool_size定義了InnoDB緩存池的大小。
    總內(nèi)存-(每個(gè)線程所需要的內(nèi)存 x 連接數(shù))-系統(tǒng)保留內(nèi)存
    key_buffer_size是為MyISAM緩存池設(shè)置大小的，該引擎只會(huì)緩存索引，數(shù)據(jù)還會(huì)依賴于操作系統(tǒng)的緩存
    通過下面的查詢來確定MyISAM所占用的空間大?。?span style="color: rgb(198, 120, 221);">select sum(index_length) from information_schema.tables where engine='myisam';

innodb_log_file_size控制事務(wù)日志的大小。
innodb_log_files_in_groups控制事務(wù)日志的個(gè)數(shù)。
事務(wù)日志的總大小=innodb_log_file_size x innodb_log_files_in_groups。
innodb_log_buffer_size控制日志緩存區(qū)的大小。
innodb_flush_log_at_trx_commit控制刷新日志緩沖區(qū)的頻率：
      0表示每秒進(jìn)行一次log寫入cache，并flush log到磁盤。
      1默認(rèn)，表示在每次事務(wù)提交執(zhí)行l(wèi)og寫入cache，并flush log到磁盤。
      2建議使用該值，每次事務(wù)提交執(zhí)行l(wèi)og數(shù)據(jù)寫入到cache，每秒執(zhí)行一次flush log磁盤。
innodb_flush_method=O_DIRECT,對(duì)于Linux系統(tǒng)建議使用該選項(xiàng)，避免操作系統(tǒng)和InnoDB都對(duì)數(shù)據(jù)進(jìn)行緩存。
innodb_file_per_table=1, 控制InnoDB的表空間。
innodb_doublewrite=1, 控制是否使用雙寫緩存，啟用后稍微影響性能，但是安全性提高。

delay_key_write：
off表示每次寫操作后刷新鍵緩沖中的臟塊到磁盤。
on表示只對(duì)鍵表時(shí)指定了delay_key_write選項(xiàng)的表使用延遲刷新。
all表示對(duì)所有MyISAM表都是用延遲鍵寫入。

expire_logs_days指定自動(dòng)清理binlog的天數(shù)。
max_allowed_packet控制Mysql可以接受的包大小。
skip_name_resolve禁用DNS查找。
sysdate_is_now確保sysdate()函數(shù)返回確定性日期。
read_only禁止非super權(quán)限的用戶寫入權(quán)限。
skip_slave_start禁用slave自動(dòng)恢復(fù)。
sql_mode設(shè)置Mysql所使用的SQL模式，默認(rèn)很寬松的:
        strict_trans_tables
        no_engine_subtitution
        no_size_date
        no_zero_in_date
        only_full_group_by

其他常用參數(shù)
    sync_binlog控制Mysql如何向磁盤刷新binlog，默認(rèn)使用操作系統(tǒng)刷新策略。
    tmp_table_size和max_heap_table_size控制內(nèi)存臨時(shí)表大小。
    max_connections控制允許的最大連接數(shù), 通常設(shè)置為2000。

數(shù)據(jù)庫的結(jié)構(gòu)和SQL優(yōu)化（影響最大）

    過分的反范式化為表建立太多的列。
    過分的范式化造成太多關(guān)聯(lián)的表, 一般建議不超過10個(gè)關(guān)聯(lián)表。
    在OLTP環(huán)境中使用不恰當(dāng)?shù)姆謪^(qū)表。
    使用外鍵保證數(shù)據(jù)完整性。

總體優(yōu)化步驟：

    數(shù)據(jù)庫表結(jié)構(gòu)設(shè)計(jì)和SQL語句
    數(shù)據(jù)庫存儲(chǔ)引擎和參數(shù)配置的優(yōu)化
    系統(tǒng)優(yōu)化
    硬件升級(jí)

--auto-generate-sql,由系統(tǒng)自動(dòng)生成SQL腳本進(jìn)行測(cè)試。
--auto-generate-sql-add-autoincrement在生成的表中增加自增ID。
--auto-generate-sql-load-type指定測(cè)試找那個(gè)使用的查詢類型。
--auto-generate-sql-write-number指定初始化數(shù)據(jù)時(shí)生成的數(shù)據(jù)量。
--concurrency指定并發(fā)線程的數(shù)量，逗號(hào)分隔多個(gè)并發(fā)。
--engine指定測(cè)試表的存儲(chǔ)引擎，逗號(hào)分隔多個(gè)引擎。
--no-drop指定不清理測(cè)試數(shù)據(jù)。
--iterations指定測(cè)試運(yùn)行的次數(shù)。
--number-of-queries指定每一個(gè)線程的查詢數(shù)量。
--debug-info指定輸出額外的內(nèi)存及CPU統(tǒng)計(jì)信息。
--number-int-cols指定測(cè)試表中INT類型列的數(shù)量。
--number-char-cols指定測(cè)試表中CHAR類型列的數(shù)量。
--create-schema指定用于執(zhí)行測(cè)試的數(shù)據(jù)庫的名字。
--query用于自定義SQL的腳本。
only-print并不運(yùn)行測(cè)試腳本，而是把生成的腳本打印出來。

安裝：

使用wget進(jìn)行下載
使用unzip進(jìn)行解壓
進(jìn)入解壓目錄
運(yùn)行./autogen.sh腳本
運(yùn)行./configure --with-mysql-includes=/usr/local/mysql/include --with-mysql-libs=/usr/local/mysql/lib
編譯安裝make && make install
常用參數(shù)：

--test用于指定所要執(zhí)行的測(cè)試類型：
fileio 用于文件系統(tǒng)的IO性能測(cè)試
cpu CPU性能測(cè)試
memory 內(nèi)存性能測(cè)試
--mysql-db用于指定基準(zhǔn)測(cè)試的數(shù)據(jù)庫名。
--mysql-table-engine用于指定所使用的存儲(chǔ)引擎。
--oltp-tables-count執(zhí)行測(cè)試的表的數(shù)量。
--oltp-table-size指定每個(gè)表中的數(shù)據(jù)行數(shù)。
--num-threads指定測(cè)試的并發(fā)線程數(shù)量。
--max-time指定最大測(cè)試時(shí)間。
--report-interval指定間隔多長時(shí)間輸出一次統(tǒng)計(jì)信息。
--mysql-user指定執(zhí)行測(cè)試的Mysql用戶。
--mysql-password指定執(zhí)行測(cè)試的Mysql用戶的密碼。
prepare用于準(zhǔn)備測(cè)試數(shù)據(jù)。
run用于實(shí)際進(jìn)行測(cè)試。
cleanup用于清理測(cè)試數(shù)據(jù)。

盡量減少數(shù)據(jù)冗余。
盡量避免數(shù)據(jù)維護(hù)中出現(xiàn)的更新插入和刪除異常。
    插入異常：如果表中的某個(gè)實(shí)體隨著另一個(gè)實(shí)體而存在就會(huì)產(chǎn)生插入異常。
    更新異常：如果更新某個(gè)表中的某個(gè)實(shí)體的單獨(dú)屬性時(shí)，需要對(duì)多行進(jìn)行更新。
    刪除異常：如果刪除某個(gè)表中的實(shí)體，也會(huì)導(dǎo)致其他實(shí)體被刪除。
節(jié)約數(shù)據(jù)存儲(chǔ)空間
提高查詢的效率

需求分析：全面了解產(chǎn)品設(shè)計(jì)的存儲(chǔ)需求
存儲(chǔ)需求。
數(shù)據(jù)處理需求。
數(shù)據(jù)安全性和完整性。
邏輯設(shè)計(jì)：設(shè)計(jì)數(shù)據(jù)的邏輯存儲(chǔ)結(jié)構(gòu)
數(shù)據(jù)實(shí)體之家的邏輯關(guān)系，解決數(shù)據(jù)冗余和數(shù)據(jù)維護(hù)異常。
物理設(shè)計(jì)：
根據(jù)所使用的數(shù)據(jù)庫特點(diǎn)進(jìn)行表結(jié)構(gòu)設(shè)計(jì)
維護(hù)優(yōu)化：
根據(jù)實(shí)際情況對(duì)索引、存儲(chǔ)結(jié)構(gòu)等進(jìn)行優(yōu)化

可讀性原則
表意性原則
長名原則（盡量避免過多使用縮寫）
選擇合適的存儲(chǔ)引擎

利用二進(jìn)制日志增量進(jìn)行
不需要太多的帶寬
但是使用基于行的復(fù)制在進(jìn)行大批量的更改時(shí)會(huì)對(duì)帶寬帶來一定壓力
特別是在跨IDC環(huán)境下進(jìn)行復(fù)制
應(yīng)該分批復(fù)制

需要其他組件配合完成
利用DNS輪詢的方式把程序的讀連接到不同的備份數(shù)據(jù)庫
使用LVS、HAProxy等代理方式

利用備庫的備份來減少主庫的負(fù)載
復(fù)制并不能替代備份

1. Mysql服務(wù)層日志

二進(jìn)制日志
慢查詢?nèi)罩?br>通用日志

2. Mysql存儲(chǔ)引擎層日志

InnoDB的重做日志
InnoDB的回滾日志
二進(jìn)制日志記錄了對(duì)Mysql數(shù)據(jù)庫的修改事件，包括增刪改查事件和表結(jié)構(gòu)修改事件。

在binlog中記錄的日志都是成功執(zhí)行過的日志。

3. 二進(jìn)制日志的記錄格式

無論使用哪一種日志格式，進(jìn)行DDL操作時(shí)都是使用基于段的日志格式。

如果同一條SQL對(duì)N條數(shù)據(jù)進(jìn)行修改，基于段的日志之后記錄1條，基于行的會(huì)記錄N條。

SBR 基于段的格式binlog_format=SATEMENT（Mysql5.7之前默認(rèn)使用的格式）

優(yōu)點(diǎn)：
日志量相對(duì)較小，節(jié)約磁盤網(wǎng)絡(luò)IO。
并不強(qiáng)制要求主從數(shù)據(jù)庫的表定義的完全相同。
相對(duì)于基于行的復(fù)制更加靈活。
缺點(diǎn)：
非確定性事件、存儲(chǔ)過程、觸發(fā)器、自定義函數(shù)、UUID()或user()等函數(shù)可能造成Mysql復(fù)制的主備服務(wù)器數(shù)據(jù)不一致。
需要更多地鎖。

RBR 基于行的日志格式binlog_format=ROW
優(yōu)點(diǎn)：
使Mysql主從復(fù)制更加安全，在備用服務(wù)器上復(fù)制效率高，可以通過反向處理方式恢復(fù)某些數(shù)據(jù)。
可以應(yīng)用于任何SQL的復(fù)制。可以減少從服務(wù)器上鎖的使用。
缺點(diǎn)：
記錄日志量大。binlog_row_image=[FULL|MINIMAL|NOBLOB]
要求主從數(shù)據(jù)庫服務(wù)器的表結(jié)構(gòu)一致，否則可能造成中斷復(fù)制。
無法在從服務(wù)器上單獨(dú)執(zhí)行觸發(fā)器，因?yàn)橹苯討?yīng)用了SQL執(zhí)行后的修改。

SBR+RBR 混合日志格式binlog_format=MINED
根據(jù)SQL語句由系統(tǒng)決定基于段和基于行的日志格式進(jìn)行選擇
數(shù)據(jù)量大小根據(jù)所執(zhí)行的SQL語句決定
建議選擇混合日志格式，如果選擇為ROW格式，建議設(shè)置binlog_row_image=MINIMAL

在從服務(wù)器啟動(dòng)一個(gè)IO工作線程。
IO工作線程與主庫建立普通的連接。
在主數(shù)據(jù)庫服務(wù)器上啟動(dòng)特殊的二進(jìn)制日志轉(zhuǎn)儲(chǔ)線程，成為binlog_dump線程。
然后從服務(wù)器讀取主服務(wù)器上的事件，當(dāng)從服務(wù)器追趕上主服務(wù)器的時(shí)候從服務(wù)器會(huì)進(jìn)入sleep狀態(tài)。

在主數(shù)據(jù)庫服務(wù)器建立復(fù)制賬號(hào)：create user 'repl'@'IP地址段' identified by '密碼';。
授權(quán)用戶：grant replication slave on *.* to 'repl'@'IP地址段';。
主服務(wù)器配置BinLog：bin_log=存儲(chǔ)目錄。
主服務(wù)器配置ID：server_id=100。
從服務(wù)器配置BinLog：bin_log=存儲(chǔ)目錄。
從服務(wù)器配置ID：server_id=101。
從服務(wù)器配置RelayLog：relay_log=存儲(chǔ)目錄。
從服務(wù)器配置LogSlaveUpdate:log_slave_update=on可選。
從服務(wù)器配置ReadOnly:read_only=on可選。
常用的工具：mysqldump、xtrabackup。
從服務(wù)器啟動(dòng)復(fù)制鏈路：change master to master_host='主服務(wù)器IP地址', master_password='主服務(wù)器密碼', master_log_file='主服務(wù)器日志文件名稱', master_log_pos=偏移量;。
主服務(wù)器日志文件名稱和偏移量在主服務(wù)器中導(dǎo)出的sql文件中可以獲得。

從服務(wù)器首先會(huì)告訴主服務(wù)器從服務(wù)器已經(jīng)執(zhí)行的事務(wù)的GTID值。
主服務(wù)器返回給從服務(wù)器沒有執(zhí)行的GTID值。
同一個(gè)事務(wù)只在指定的從庫執(zhí)行一次。
主服務(wù)器配置BinLog：bin_log=存儲(chǔ)目錄。
主服務(wù)器配置ID：server_id=100。
主服務(wù)器配置GTID：gtid_mode=on。
主服務(wù)器配置：enforce-gtid-console和log-slave-updates=on。
從服務(wù)器配置ID：server_id=101。
從服務(wù)器配置RelayLog：relay_log=存儲(chǔ)目錄。
從服務(wù)器配置:gtid_mode=on和enforce-gtid-consistency。
從服務(wù)器配置:log-slave-updates=on建議。
從服務(wù)器配置:read_only=on建議。
從服務(wù)器配置:master_info_repository=表名建議。
從服務(wù)器配置:relay_log_info_repository=表名建議。
常用的工具：mysqldump、xtrabackup。
從服務(wù)器啟動(dòng)復(fù)制鏈路：change master to master_host='主服務(wù)器IP地址', master_password='主服務(wù)器密碼' , master_auto_position=偏移量;。

基于SQL段的日志是在從數(shù)據(jù)庫上重新執(zhí)行SQL
基于行的日志是在從數(shù)據(jù)庫上直接應(yīng)用修改

是Mysql最早使用的復(fù)制技術(shù)，Bug相對(duì)較少
對(duì)SQL查詢沒有任何限制
故障處理比較容易
故障轉(zhuǎn)移時(shí)重新獲取新主服務(wù)器日志點(diǎn)信息比較困難

可以很方便的進(jìn)行故障轉(zhuǎn)移
從庫不會(huì)丟失庫上的任何修改
故障處理復(fù)雜
對(duì)執(zhí)行的SQL有一定限制

所使用的Mysql版本，Mysql5.6+以上可以使用GTID。
復(fù)制架構(gòu)以及主從切換的方式。
所使用的高可用管理組件。
應(yīng)用的支持程度。

特點(diǎn)：
配置簡單。
可以用多個(gè)從庫分擔(dān)讀負(fù)載。
用途：
為不同業(yè)務(wù)使用不同的從庫。
將一臺(tái)從庫放到遠(yuǎn)程IDC中，用于災(zāi)備恢復(fù)。
分擔(dān)主庫的讀負(fù)載。

主主方式注意事項(xiàng)：

兩臺(tái)同時(shí)提供服務(wù)
兩個(gè)主中所操作的表最好能夠分開
配置一下兩個(gè)參數(shù):auto_increment_increment=2 // 控制自增列ID步長和auto_increment_offest=1|2 // 控制自增列ID起始數(shù)值，一臺(tái)為1，一臺(tái)為2
主備方式注意事項(xiàng)：

只有一臺(tái)服務(wù)器對(duì)外服務(wù)
一臺(tái)處于只讀或者是熱備狀態(tài)
確保兩臺(tái)服務(wù)器上的初始數(shù)據(jù)相同
確保兩臺(tái)服務(wù)器上已經(jīng)啟動(dòng)binlog并且有不同的server_id。
在兩臺(tái)服務(wù)器上啟用log_slave_updates參數(shù)。
初始化的備庫上啟用read_only參數(shù)。

在主從數(shù)據(jù)庫之間建立分發(fā)主庫，主數(shù)據(jù)庫數(shù)據(jù)同步到分發(fā)主庫，分發(fā)主庫向多個(gè)從庫分發(fā)。

stop slave;
set global slave_parallel_type='logical_clock';
set global slave_parallel_workers=4;
start slave;

由于數(shù)據(jù)損壞或丟失所引起的主從復(fù)制錯(cuò)誤
    主庫或從庫以外宕機(jī)引起的錯(cuò)誤
    使用跳過二進(jìn)制日志事件
    注入空事務(wù)的方式先恢復(fù)中斷的復(fù)制鏈路
    使用其他方法來對(duì)比主從服務(wù)器上的數(shù)據(jù)
主庫上的二進(jìn)制日志文件損壞
    通過change master命令來重新指定
    檢查修復(fù)數(shù)據(jù)
從庫上中繼日志損壞
在從庫上進(jìn)行了數(shù)據(jù)修改
不唯一的server_id和server_uuid, 比如多個(gè)從庫使用相同的server_uuid問題
max_allow_packet設(shè)置引起的主從復(fù)制錯(cuò)誤

磁盤耗盡
性能低的SQL
表結(jié)構(gòu)和索引沒有優(yōu)化
主從數(shù)據(jù)不一致
人為操作失誤

建立完善的監(jiān)控報(bào)警系統(tǒng)
對(duì)備份數(shù)據(jù)進(jìn)行恢復(fù)測(cè)試
正確配置數(shù)據(jù)庫環(huán)境
對(duì)不需要的數(shù)據(jù)進(jìn)行歸檔和清理

解決系統(tǒng)的單點(diǎn)故障
利用SUN共享存儲(chǔ)解決
DRDB（以Linux內(nèi)核模式塊級(jí)別的同步復(fù)制技術(shù)）磁盤復(fù)制解決
使用多寫集群，目前主要是Percona公司提供的PXC集群，所有事務(wù)提交以后才算提交完成，取決于性能最差的服務(wù)器
使用NDB集群，所有節(jié)點(diǎn)都進(jìn)行主主復(fù)制，可以在任何節(jié)點(diǎn)進(jìn)行寫入。目前NDB集群的數(shù)據(jù)都要求存儲(chǔ)在內(nèi)存中。
利用Mysql主從復(fù)制來解決，可以使用第三方的復(fù)制管理組件
    主庫切換以后如何通知應(yīng)用新的主庫的IP地址
    如何檢查Mysql主庫是否可用
    如何處理從庫和新的主庫之間的復(fù)制關(guān)系

主要作用就是監(jiān)控和管理Mysql主主復(fù)制拓?fù)洌⒃诋?dāng)前的主服務(wù)器失效時(shí)，進(jìn)行主和主備庫之間的主從切換和故障轉(zhuǎn)移。
特點(diǎn)：
MMM中同一時(shí)間只有一個(gè)主庫是對(duì)外服務(wù)的。
MMM可以監(jiān)控Mysql主從復(fù)制健康情況。
在主庫出現(xiàn)宕機(jī)時(shí)進(jìn)行故障轉(zhuǎn)移并自動(dòng)配置其他從庫對(duì)新主庫的復(fù)制。
MMM提供了主寫虛擬IP，在主庫出現(xiàn)問題時(shí)可以自動(dòng)遷移虛擬IP。

配置主主復(fù)制以及主從同步集群
安裝主從節(jié)點(diǎn)所需要的支持包
安裝及配置MMM工具集
運(yùn)行MMM監(jiān)控服務(wù)
測(cè)試

[優(yōu)點(diǎn)]使用Perl語言開發(fā)，完全開源
[優(yōu)點(diǎn)]提供了讀寫VIP，是服務(wù)器角色變更時(shí)對(duì)前端應(yīng)用透明
[優(yōu)點(diǎn)]MMM提供了從庫延遲監(jiān)控
[優(yōu)點(diǎn)]提供了主庫故障轉(zhuǎn)移后服務(wù)器對(duì)新庫的重新同步功能
[優(yōu)點(diǎn)]同時(shí)監(jiān)控多個(gè)從庫
[缺點(diǎn)]發(fā)布時(shí)間早導(dǎo)致Mysql新的復(fù)制功能支持不夠
[缺點(diǎn)]MMM對(duì)于Mysql5.6+的多線程同步不支持
[缺點(diǎn)]對(duì)于寫負(fù)載較大的情況下可能會(huì)出現(xiàn)讀VIP全部遷移到主服務(wù)器上
[缺點(diǎn)]沒有讀負(fù)載均衡
[缺點(diǎn)]在主從切換時(shí)，容易造成數(shù)據(jù)丟失
[缺點(diǎn)]MMM監(jiān)控服務(wù)存在單點(diǎn)故障

MHA基本上可以在30s之內(nèi)完成主從切換，最大程度的保證數(shù)據(jù)一致性。
只能監(jiān)控主庫是否可用。
當(dāng)主庫不可用時(shí)，可以在多個(gè)從庫中選出新的主庫。
提供主從切換和故障轉(zhuǎn)移，嘗試在故障機(jī)器保存二進(jìn)制日志。

主從切換的過程：
應(yīng)用從原生DB服務(wù)器上保存的二進(jìn)制日志。
提升備選主DB服務(wù)器為新的主DB服務(wù)器。
遷移集群中其他的從DB作為新的主DB的從服務(wù)器。

部署步驟：
配置集群內(nèi)所有主機(jī)的SSH免認(rèn)證登錄。
配置主從復(fù)制集群(推薦基于GTID的復(fù)制)。
配置HMA管理節(jié)點(diǎn)。
對(duì)配置進(jìn)行檢驗(yàn)。

特點(diǎn)：
[優(yōu)點(diǎn)]同樣適用Perl語言開發(fā)的開源工具, 也提供其他語言的接口。
[優(yōu)點(diǎn)]可以支持基于GTID的復(fù)制模式。
[優(yōu)點(diǎn)]在故障轉(zhuǎn)移時(shí)更不易產(chǎn)生數(shù)據(jù)丟失。
[優(yōu)點(diǎn)]同一個(gè)監(jiān)控節(jié)點(diǎn)可以監(jiān)控多個(gè)集群。
[缺點(diǎn)]沒有VIP的實(shí)現(xiàn)，需要自己開發(fā)相關(guān)腳本。
[缺點(diǎn)]需要基于SSH免密登錄，存在安全隱患。
[缺點(diǎn)]沒有提供多個(gè)從庫的負(fù)載均衡的功能。

為什么要進(jìn)行讀寫分離？因?yàn)閷懖僮髦荒茉谥鲙焐线M(jìn)行，而讀操作可以遷移到從庫。

方式：

由開發(fā)人員控制：直接控制什么樣的查詢?cè)趶膸爝M(jìn)行，因此比較靈活，由于程序直接連接數(shù)據(jù)庫，因此性能損耗低。但是也加大了工作量。人為控制可能出錯(cuò)較多。
中間件實(shí)現(xiàn)：比如mysql-proxy(高并發(fā)情況下可能會(huì)崩潰)或maxScale(由MariaDB公司提供)。中間件可以根據(jù)SQL的語法分析出世讀操作還是寫操作來實(shí)現(xiàn)負(fù)載均衡，但是存儲(chǔ)過程等并不能分析出來。使用中間件對(duì)程序是透明的，程序不需要進(jìn)行調(diào)整。數(shù)據(jù)庫查詢處理能力依賴于中間件。對(duì)延遲敏感業(yè)務(wù)無法自動(dòng)在主庫中執(zhí)行。
讀寫分離實(shí)現(xiàn)了，那么下一步就是讀負(fù)載均衡了。

負(fù)載均衡在軟件上可以使用LVS、HAProxy、MaxScale，硬件可以使用F5。

使用B+樹存儲(chǔ)數(shù)據(jù)
B+樹索引能夠加快數(shù)據(jù)的查詢的速度
更加適合進(jìn)行范圍查找

什么情況下用到BTree索引:
全值匹配的查詢
匹配最左前綴的查詢
匹配列前綴查詢
范圍查找
精確匹配左前列并范圍匹配另外一列
只訪問索引的查詢

如果不是按照索引最左列開始查找，那么無法使用索引。
使用索引時(shí)不能跳過索引中的列。
not in、<>、!=操作無法使用索引。
如果查詢中有某個(gè)列的范圍查詢，則其右邊的所有列都無法使用索引。

Hash索引是基于Hash表實(shí)現(xiàn)的，只有查詢條件精確匹配Hash索引中的列時(shí)才能夠使用Hash索引。
對(duì)于Hash索引中的所有列，存儲(chǔ)引擎都會(huì)為每一行計(jì)算一個(gè)Hash碼，Hash索引中存儲(chǔ)的就是Hash碼。

在Mysql配置參數(shù)中指定slow_query_log。啟動(dòng)停止記錄慢查詢?nèi)罩尽?br>在Mysql配置參數(shù)中指定slow_query_log_file。指定慢查詢?nèi)罩居涗洿鎯?chǔ)路徑及文件。
在Mysql配置參數(shù)中指定long_query_time。指定記錄慢查詢?nèi)罩?span style="color: rgb(224, 108, 117);">SQL執(zhí)行時(shí)間的閾值。默認(rèn)值為10秒。通常改為0.0001秒比較合適。
在Mysql配置參數(shù)中指定log_queries_not_using_indexs。是否記錄未使用索引的SQL。
使用慢日志查詢分析工具mysqldumpslow。
使用慢日志查詢分析工具pt-query-digest。

query_cache_type指定查詢緩存是否可用。在一個(gè)比較繁忙的系統(tǒng)中建議關(guān)閉查詢緩存。
query_cache_size指定查詢緩存的內(nèi)存大小。
query_cache_limit指定查詢緩存可用存儲(chǔ)的最大值。
query_cache_wlock_invalidate指定數(shù)據(jù)表被鎖定以后是否返回緩存中的數(shù)據(jù)。
query_cache_min_res_init指定查詢緩存分配的內(nèi)存塊最小單位。

統(tǒng)計(jì)信息的不準(zhǔn)確
執(zhí)行計(jì)劃的成功估算并不等于實(shí)際的執(zhí)行計(jì)劃的成本，因?yàn)镸ysql并不知道哪些頁面在磁盤上，哪些頁面在內(nèi)存上，哪些需要順序讀取，哪些需要隨機(jī)讀取。
Mysql查詢優(yōu)化器所認(rèn)為的最優(yōu)化的解決方案并不是開發(fā)者認(rèn)為最優(yōu)化的。因?yàn)镸ysql只是基于成本模型選擇最優(yōu)的執(zhí)行計(jì)劃。
Mysql不會(huì)考慮其他的并發(fā)查詢，可能會(huì)影響當(dāng)前的查詢速度。
Mysql有時(shí)候也會(huì)基于某些固定的規(guī)則來生成執(zhí)行計(jì)劃。
Mysql不會(huì)考慮不受控制的成本的，比如存儲(chǔ)過程，用戶定義的函數(shù)。

重新定義表的關(guān)聯(lián)順序，因?yàn)镸ysql優(yōu)化器會(huì)根據(jù)統(tǒng)計(jì)信息來決定表的關(guān)聯(lián)順序。
將外連接轉(zhuǎn)換為內(nèi)連接。
使用等價(jià)的規(guī)則變換。
優(yōu)化count()、min()、max()等函數(shù)進(jìn)行優(yōu)化。
將一個(gè)表達(dá)式轉(zhuǎn)換為一個(gè)常數(shù)。
子查詢優(yōu)化。
提前終止查詢。
對(duì)in()條件進(jìn)行優(yōu)化。

如何確定Mysql在各個(gè)階段所消耗的時(shí)間？
使用profile方法
使用set profiling=1啟動(dòng)profile，這是一個(gè)session級(jí)別的設(shè)置
執(zhí)行查詢
通過show profiles來查看每一個(gè)查詢所消耗總時(shí)間的信息
通過show profile for query ID來查詢第ID個(gè)階段所消耗的時(shí)間
使用information_schema引擎
在Mysql5.5-開銷較大，因此需要在配置文件修改。在Mysql5.7+以后就可以了。

update `setup_instruments` set enabled='YES', TIMED='YES' where name like 'stage%';
update `setup_consumers` set enabled='YES' where name like 'events%';

把一個(gè)實(shí)例中的多個(gè)數(shù)據(jù)庫拆分到多個(gè)數(shù)據(jù)庫實(shí)例中
把一個(gè)實(shí)例的數(shù)據(jù)庫的表分離到不同實(shí)例的數(shù)據(jù)庫中
對(duì)一個(gè)數(shù)據(jù)表進(jìn)行分片水平拆分到不同的數(shù)據(jù)庫實(shí)例中
1, 如何選擇分區(qū)鍵
盡可能避免數(shù)據(jù)的跨分區(qū)查詢的發(fā)生
分片鍵盡可能保證每個(gè)實(shí)例的數(shù)據(jù)是均衡的
2.如何存儲(chǔ)不用分片的表
每個(gè)分片存儲(chǔ)一份相同的數(shù)據(jù)，使用多寫的方式更新數(shù)據(jù)
使用額外節(jié)點(diǎn)統(tǒng)一存儲(chǔ)
3, 如何部署分片
每個(gè)分片使用單一的數(shù)據(jù)庫，并且數(shù)據(jù)庫名也相同
將多個(gè)分片的數(shù)據(jù)表存儲(chǔ)在一個(gè)數(shù)據(jù)庫中，并在表名中加入分片號(hào)后綴
在一個(gè)節(jié)點(diǎn)讀個(gè)數(shù)據(jù)庫，每個(gè)數(shù)據(jù)庫包含一個(gè)分片
4. 如何分配分片的數(shù)據(jù)
通過分區(qū)鍵的Hash值取模來分配分片數(shù)據(jù)
按照分區(qū)鍵的范圍來分配分片的數(shù)據(jù)
利用分區(qū)鍵和分區(qū)的映射表的方式來分配分片的數(shù)據(jù)，由于該映射表存在很大的讀取壓力，因此可以緩存該映射表
5. 如何生成分片以后的全局唯一ID
使得auto_increment_increment的值等于分片的數(shù)量，auto_increment_offset設(shè)置為不同的值
使用全局節(jié)點(diǎn)的方式來生成ID，但是該節(jié)點(diǎn)也會(huì)承受較大壓力
在redis等緩存服務(wù)器中創(chuàng)建全局ID
使用oneProxy進(jìn)行定制化開發(fā)

通過使用mysqladmin -umonitor_user -p -h ping來確認(rèn)是否可以建立網(wǎng)絡(luò)連接
使用telnet ip db_port來確認(rèn)是否可以建立網(wǎng)絡(luò)連接
通過檢查數(shù)據(jù)庫配置文件read_only來確認(rèn)是否可以讀寫

通過show variables like 'max_connections';獲取最大連接值
通過show global status like 'Threads_connected';獲取已經(jīng)連接的線程數(shù)
通過以上兩個(gè)值的百分比確定是否報(bào)警

通過檢查show slave status;來判斷Slave_IO_Running和Slave_SQL_Running監(jiān)控主從復(fù)制鏈路的狀態(tài)。
監(jiān)控主從復(fù)制延遲, 檢查Seconds_Bebind_Master，但是數(shù)據(jù)不是特別準(zhǔn)確。
通過在主庫上獲取日志文件名和偏移量和已經(jīng)在主上傳輸完成的日志文件名和偏移量獲取精確的值。
通過pt-table-checker來驗(yàn)證主從復(fù)制的數(shù)據(jù)是否一致。