1.主從復制binlog_format要使用row，statement會導致主從數據不一致(基于語句，rand或uuid等函數沒法恢復)

2.如果使用binlog恢復數據，標準做法是用mysqlbinlog工具把binlog中內容解析出來，然后把解析結果發給mysql執行

3.innodb_buffer_pool_size是緩存用戶表及索引數據的最主要緩存空間，建議可以設置到50%到80%的內存大小，調整這個參數期間會阻塞用戶的請求，直到調整完畢，建議在低峰期調整

4.對于某些工作負載，如使用like和%的范圍查詢以及高并發的joins,不適合使用自適應哈希索引，維護哈希索引結構的額外開銷會帶來嚴重性能損耗，這種情況建議關掉，set global innodb_adaptive_hash_index=off/on命令

5.innodb_flush_log_at_trx_commit的參數取值

設為1：最安全，數據肯定一致，配合sync_binlog=1

設為2：適合數據一致性和完整性要求不高的

設為0：只要求性能，高并發日志服務

6.對于可能產生大量更新記錄的大事務，增加innodb_log_buffer_size的大小，可以避免在事務提交前就執行不必要的日志寫入磁盤操作，提高事務處理性能

7.關于query_cache，任何更新操作都會導致其失效，并發高的時候也有影響，還有bug,所有大部分情況下只是雞肋，建議全面禁用，5.7中默認關閉

8.當發生鎖等待情況時，可以通過語句select * from sys.innodb_lock_waits \G來在線查看，最主要是看waiting_pid等待事務的線程pid、waiting_query等待鎖釋放的語句、blocking_pid阻塞事務的pid、blocking_query阻塞事務的SQL語句這4個參數，pid對應show full processlist命令里面輸出的線程id號；阻塞事務語句顯示可能為NULL，想要找到相應id對應的SQL可以使用select SQL_TEXT from performance_schema.events_statements_current where thread_id in (select thread_id from performance_schema.threads where processlist_id = 相應pid)

9.Innodb會自動檢測死鎖，并立即回滾其中某個事務，并且返回一個錯誤。偶然發生死鎖不必擔心，當死鎖頻繁出現是要引起注意，檢查代碼，調整SQL操作順序，或者縮短事務長度

10.避免死鎖建議：

1.)不同程序并發存取多個表或者涉及多行記錄時，盡量約定以相同的順序訪問表

2.)把大事務盡量分解成多個小事務，使所更快釋放

3.)在同一個事務中，盡可能做到一次鎖定所需要的所有資源

4.)盡量使用索引，不用索引會為每一行添加鎖

11.通過show full processlist是為了查看當前MySQL是否有壓力、都在跑什么語句、當前語句耗時多久了，從中可以看到總共有多少鏈接數、哪些線程有問題，然后把有問題的線程kill掉，臨時解決一些突發性問題

12.通過show engine innodb status命令來查看是否存在鎖表的情況

13.事務和鎖信息記錄在information_schema數據庫中，主要涉及innodb_trx、innodb_locks、innodb_lock_waits,可以幫我們方便地監控當前的事務并分析可能存在的鎖問題

innodb_trx：查看事務情況

trx_id:唯一的事務id號

trx_state:當前事務的狀態

trx_wait_started:事務開始等待的時間

trx_mysql_thread_id:線程id與show full processlist相對應

trx_query:事務運行的SQL語句

trx_operation_state:事務運行的狀態

innodb_locks:查詢事務鎖情況，包括事務正在申請加的鎖和事務加上的鎖

innodb_lock_waits查看鎖阻塞情況

requesting_trx_id:請求鎖的事務ID(等待方)

blocking_trx_id:阻塞該鎖的事務ID(當前持有方，待釋放)

14.查詢語句避免使用select *且加上limit限制

15.使用索引盡量避免事后才想起添加索引

16.開啟ICP(Index Condition Pushdown)可以過濾掉大量的數據，減少IO，提高查詢語句性能，開啟方法：set optimizer_switch="index_condition_pushdown=on"

17.開啟MRR(Multi-Range Read Optimization),優化器將隨機IO轉化為順序IO，目的是減少磁盤的隨機訪問，降低查詢過程中的IO開銷，對IO-bound類型的SQL語句性能帶來極大的提升；開啟方式set optimizer_switch="mrr=on,mrr_cost_based=on",cost base=on表示當發現優化后的代價高時就不會使用該優化項

18.開啟BKA(batched key access),提高表join性能，開啟方式set optimizer_switcher="mrr=on,mrr_cost_based=off,batched_key_access=on",BKA必須使用MRR，所以mrr_cost_based必須關閉

19.慢SQL優化思路：開啟慢日志，設置超過幾秒為慢SQL語句，抓取慢SQL語句，通過explain查看執行計劃，對慢SQL語句分析，創建索引并調整語句，再查看執行計劃，對比調優結果

1.抓取慢SQL:可以使用slow_query_log_file,可以使用mysqldumpslow進行分析，命令為mysqldumpslow -t 10 xxx/xxx/xxx.log,顯示出日志中最慢的10條SQL

2.通過explain分析語句，其中type字段有ALL(代表全表掃描，沒有用到index)、index、range(優化目標至少到range級別)、ref、eq_ref、const、system、NULL(從左到右，性能從差到好)；key字段出現NULL，代表沒有使用索引；rows字段值越大意味著需要掃描的行數越多，相應耗時越長；extra字段出現Using filesort，一般是因為order by后的條件導致索引失效，最好進行優化；extra字段出現Using join buffer,應該注意，根據查詢的具體情況可能需要添加索引來改進；extra列要避免出現Using filesort或Using temporary，很影響性能

3.show profiles也有助于分析慢SQL

20.索引使用原則

1.)表一定要有主鍵，顯示定義主鍵且采用與業務無關的列以避免修改，建議采用自增列來使數據順序插入

2.)經常被查詢的列、經常用于表連接的列、經常排序分組的列，需要創建索引

3.)組合索引區分度高的放在最左邊，字段數不建議超過5個，如果5個字段還不能極大地縮小row范圍，那么設計肯定有問題

4.) 單張表的索引數量建議控制在5個以內

5.)注意隱式類型轉換會導致索引失效

21.通過設置innodb_buffer_pool_dump_pct的值可以在重啟時自動預熱，提高訪問性能(尤其是在業務高峰時)

22.innodb_undo_log_tablespaces建議設置為3，避免在自動清除undo log時系統處于不可用狀態，2也可以，但3更安全

23.硬件建議：

1.使用SSD或者PCIe SSD設備，至少獲得百倍甚至萬倍的IOPS提升

2.購置陣列卡同時配備Cache及BBU模塊，有陣列卡時設置陣列寫策略為WB，甚至FORCE WB,嚴禁使用WT策略

3.盡可能選用RAID10，而非RAID 5

3.建議在BIOS層面關閉NUMA，CPU設置為最大性能模式，選擇performance per watt optimized來充分發揮CPU的最大功耗性能，同時建議關閉節能選項

24.Linux操作系統層面優化

1.防火墻(iptables)和SElinux需要關閉

2.關于IO調度，建議使用deadline或者noop模式，不要使用cfg模式，會影響數據庫性能

3.設置內核參數vm.swappiness=1,盡量避免使用swap(交換)分區

4.推薦使用xfs文件系統，其次選擇ext4文件系統，放棄ext3;CentOS 7將xfs作為默認的文件系統

25.MySQL配置參數優化

query_cache_type=0,query_cache_size=0 關閉查詢緩存

innodb_buffer_pool_size一般設置為物理內存的50%-80%

innodb_io_capacity與innodb_io_capacity_max:取決于硬盤IOPS，即每秒的輸入輸出量(或讀寫次數)，capacity一般建議設置如下：SAS:200,SSD：3000，PCI-E：10000-50000,capacity_max為capacity的2倍，也可以通過sysbench或其他基準工具來測試磁盤的吞吐量

innodb_log_file_size:redo日志大小，通常應用是頻繁寫入的，可以設置為2G

innodb_flush_method:默認是fdatasync,當服務器硬件有SSD硬盤、RAID控制器、斷電保護、采取write-back緩存機制的時候，可以改為0_DIRECT，避免了buffer pool和系統buffer的雙緩存，效率更高

innodb_max_dirty_pages_pct:臟頁占buffer pool的比例，建議調整為50，表示當臟塊達到innodb_buffer_pool_size的50%時觸發檢查點，寫磁盤

binlog_format建議使用row模式，數據更加安全可靠，在主從復制過程中不會丟失數據

26.設計規范建議

1)字符集統一使用utf8mb4,降低亂碼風險，初始化時就設置好，修改后只會對修改后創建的表生效

2)小數字段推薦使用decimal類型，float/double精度不夠

3)避免使用text/blob來存儲大段文本、二進制數據、圖片、文件等內容，應該保存成磁盤文件，數據庫中保存其索引即可

4)不使用外鍵，在應用層實現外鍵邏輯，外鍵會降低性能，容易產生死鎖

5)字段盡量定義為NOT NULL并加上默認值，NULL會給SQL開發帶來很多問題，從而導致用不了索引，對NULL計算時只能用IS NULL和IS NOT NULL來判斷

6)不要使用存儲過程、觸發器、視圖、函數等高級功能，移植性和可擴展性較差

7.)varchar是可變長字符串，不預先分配存儲空間，長度不超過5000，如果大于此值，定義為text類型，獨立出來一張表，用主鍵來對應

27.SQL規范建議

1.)禁止使用insert into t values(xx),必須顯示指定插入的列屬性，避免表結構變動導致數據出錯

2.)禁止使用select *,必須指定查詢字段

3.)join時必須讓小表做驅動表，join列必須字符集一致并且都建有索引

28.主從復制建議

1.)在從庫上master_info_repository="TABLE"和relay_log_info_repository=“TABLE”和relay_log_recovery=1確保在slave上和復制相關的元數據表也采用innodb引擎，受到innodb事務安全的保護，并開啟relay-log自動修復機制

2.)主庫上innodb_flush_log_at_trx_commit=1且sync_binlog=1確保數據高安全

3.)從節點上授權只讀模式set global read_only=1避免人為誤操作在從庫中修改數據，導致主從不一致，對應擁有super權限用戶使用set global super_read_only=ON

4.)建議使用和主庫規格一樣好的硬件設備作為slave,存儲采用PCIE-SSD才是王道

5.)建議使用GTID復制模式(待定)

6.)工具使用perconna-toolkit,可以跳過錯誤pt-slave-restart、主從一致性檢查pt-table-checksum、修復不一致數據pt-table-sync

7.)主從延遲排查show slave status 看Seconds_Behind_Master參數的值來判斷，該值為0表示主從復制良好，如果是正值表示已出現延遲，數字越大延遲越嚴重或者用percona-toolkit的pt-heartbeat來監測延遲情況

8.)主從延遲解決建議

a.)硬件配置和主庫一致，強烈建議使用SSD硬盤，并且修改配置參數innodb_flush_method為0_DIRECT提升寫入性能

b.)適當增大從庫innodb_buffer_pool_size，減少IO壓力

c.)從庫不需要太高的數據安全，可以將sync_binlog設置為0或者500，且innodb_flushlog_at_trx_commit設置成2，減少磁盤IO壓力

d.)開啟并行復制MTS，最少升級到5.7.19版本，修復了很多bug

29.高可用方案

MGR 單寫 HAProxy+自定義腳本 proxySQL 秒級切換

MHA

PXC

keepalived+雙主復制 云服務器上keepalived只能設置單播，通常不支持浮動IP，往往需要用戶提請工單來開通高可用虛擬IP

30.分庫分表

數據遷移：先讀出歷史數據，然后按照分片規則寫入，計算需要多少分片，建議單分片單表容量不超過1000w,數據盡可能均勻分布到各個節點

總結

以上是生活随笔為你收集整理的mysql版本不一致会导致uuid_MySQL性能优化和高可用架构建议的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。